一个操作Excel模板的类代码,CSharp代码库,德仔网

代码语言
代码分类
【CSharp】一个操作Excel模板的类
作者:Dezai.CN / 发布于2011/2/9/ 590
一个操作Excel模板的类实现多表头已经使用过并调用dsoframer显示
<div>using System;
using System.Collections.Generic;
using System.Linq;//VS 08中
using System.Text;
using System.IO;
using System.Data;
using System.Reflection;
using System.Diagnostics;
using cfg = System.Configuration;
using Excel = Microsoft.Office.Interop.Excel;//需要添加引用</div>
<div></div>
<div>
namespace WindowsFormsApplication2.BLL
{
 class ExcelHelper
 {
 protected string templetFile = null;//模板路径
 protected string outputFile = null;//输出文件路径
 protected object missing = Missing.Value;//????</div>
<div>
 /// <summary>
 /// 构造函数，需指定模板文件和输出文件完整路径
 /// </summary>
 public ExcelHelper(string templetFilePath,string outputFilePath)
 {
 if(templetFilePath == null)
 throw new Exception("Excel模板文件路径不能为空！");</div>
<div>
 if(outputFilePath == null)
 throw new Exception("输出Excel文件路径不能为空！");</div>
<div>
 if(!File.Exists(templetFilePath))
 throw new Exception("指定路径的Excel模板文件不存在！");</div>
<div>
 this.templetFile = templetFilePath;
 this.outputFile = outputFilePath;
 }
 
 ///<summary>
 ///将DataTable数据写入Excel文件 套用模板
 ///
 ///</summary>
 /// <param name="rows">每个WorkSheet写入多少行数据</param>
 /// <param name="top">行索引</param>
 /// <param name="left">列索引</param>
 /// <param name="sheetPrefixName">WorkSheet前缀名，比如：前缀名为&ldquo;Sheet&rdquo;，那么WorkSheet名称依次为&ldquo;Sheet-1，Sheet-2&rdquo;</param>
 public void DataTableToExcel(DataTable dt, int rows, int top, int left, string sheetPrefixName)
 {
 int rowCount = dt.Rows.Count; //源DataTable行数
 int colCount = dt.Columns.Count; //源DataTable列数
 int sheetCount = this.GetSheetCount(rowCount, rows); //WorkSheet个数
 DateTime beforeTime;
 DateTime afterTime;
 if(sheetPrefixName==null||sheetPrefixName.Trim()=="")
 {
 sheetPrefixName = "sheet";</div>
<div>
 }
 //创建一个Application对象并使其可见
 beforeTime = DateTime.Now;
 Excel.Application app = new Excel.ApplicationClass();
 app.Visible = true;
 afterTime = DateTime.Now;
 //打开模板文件，得到WorkBook对象
 Excel.Workbook workBook = app.Workbooks.Open(templetFile, missing, missing, missing, missing, missing, missing, missing
 , missing, missing, missing, missing, missing, missing, missing);
 //得到WorkSheet对象
 Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
 
 //复制sheetCount-1个WorkSheet对象
 for (int i = 1; i < sheetCount;i++ )
 {
 ((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing, workBook.Worksheets[i]);
 }
 #region 将源DataTable数据写入Excel
 for (int i = 1; i <= sheetCount; i++)
 {
 int startRow = (i - 1) * rows;
 int endRow = i * rows;//记录结束行索引
 //若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
 if (i == sheetCount)
 {
 endRow = rowCount;</div>
<div>
 }
 //获取要写入数据的WorkSheet对象，并重命名
 Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);</div>
<div>
 sheet.Name = sheetPrefixName + "-" + i.ToString();</div>
<div>
 //将dt中的数据写入WorkSheet
 for (int j = 0; j < endRow - startRow; j++)
 {
 for (int k = 0; k < colCount;k++ )
 {
 sheet.Cells[top + j, left + k] = dt.Rows[startRow + j][k].ToString();
 }
 }
<div>
 }
 
 #endregion</div>
<div>
 try
 {
 
 workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);
 workBook.Close(null, null, null);
 app.Workbooks.Close();
 app.Application.Quit();
 app.Quit();</div>
<div>
 System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
 System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
 System.Runtime.InteropServices.Marshal.ReleaseComObject(app);</div>
<div></div>
<div>
 workSheet = null;
 workSheet = null;
 app = null;
 GC.Collect();
 }
 catch (Exception e)
 {</div>
<div>
 throw e;
 }
 finally
 {</div>
<div>
 Process[] myProcesses;
 DateTime startTime;
 myProcesses = Process.GetProcessesByName("Excel");
 //得不到Excel进程ID，暂时只能判断进程启动时间
 foreach(Process p in myProcesses)
 {
 startTime = p.StartTime;
 if (startTime > beforeTime && startTime < afterTime)
 {
 p.Kill();
 }
 }
<div>
 
 }
 }
 
 /// <summary>
 /// 获取WorkSheet数量 用于分页显示
 /// </summary>
 /// <param name="rowCount">记录总行数</param>
 /// <param name="rows">每WorkSheet行数</param>
 private int GetSheetCount(int rowCount,int rows)
 {
 int n = rowCount % rows; //余数</div>
<div>
 if(n == 0)
 return rowCount / rows;
 else
 return Convert.ToInt32(rowCount / rows) + 1;
 }
<div></div>
<div>
 }
}
<div></div>
<div>
窗体中调用如下
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using WindowsFormsApplication2.BLL;
using System.Threading;</div>
<div>
namespace WindowsFormsApplication2
{
 public partial class Form1 : Form
 {</div>
<div>
 public Form1()
 {
 InitializeComponent();
 }
<div>
 private void Form1_Load(object sender, EventArgs e)
 {
 
 //this.axFramerControl1.CreateNew("Excel.Sheet");
 string constr = Properties.Settings.Default.ConnectOrcl;
 OleDbConnection con = new OleDbConnection(constr);
 con.Open();
 OleDbCommand cmd = new OleDbCommand("select * from TESTTABLE", con);
 OleDbDataAdapter da = new OleDbDataAdapter(cmd);
 DataSet ds = new DataSet();
 da.Fill(ds);
 
 
 ExcelHelper eh = new ExcelHelper("d:\\我的文档\\桌面\\采油模板.xlt", "d:\\我的文档\\桌面\\采油.xls");
 eh.DataTableToExcel(ds.Tables[0], 5, 3, 2, "sheet");
 // MessageBox.Show("ok");
 
 this.axFramerControl1.Open( "d:\\我的文档\\桌面\\采油.xls");</div>
<div>
 
 }
 }
}
</div>
试试其它关键字
　操作Excel　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)