C#操作Excel，套用模板并对数据进行分页

Demo文件下载

  1 using  System;
  2 using  System.IO;
  3 using  System.Data;
  4 using  System.Reflection;
  5 using  System.Diagnostics;
  6 using  cfg  =  System.Configuration;
  7 // using Excel;
  8
  9 namespace  ExcelHelperTest
 10 {
 11    /**//// <summary>
 12    /// 功能说明：套用模板输出Excel，并对数据进行分页
 13    /// 作    者：Lingyun_k
 14    /// 创建日期：2005-7-12
 15    /// </summary>
 16    public class ExcelHelper
 17    {
 18        protected string templetFile = null;
 19        protected string outputFile = null;
 20        protected object missing = Missing.Value;
 21
 22        /**//// <summary>
 23        /// 构造函数，需指定模板文件和输出文件完整路径
 24        /// </summary>
 25        /// <param name="templetFilePath">Excel模板文件路径</param>
 26        /// <param name="outputFilePath">输出Excel文件路径</param>
 27        public ExcelHelper(string templetFilePath,string outputFilePath)
 28        {
 29            if(templetFilePath == null)
 30                throw new Exception("Excel模板文件路径不能为空！");
 31
 32            if(outputFilePath == null)
 33                throw new Exception("输出Excel文件路径不能为空！");
 34
 35            if(!File.Exists(templetFilePath))
 36                throw new Exception("指定路径的Excel模板文件不存在！");
 37
 38            this.templetFile = templetFilePath;
 39            this.outputFile = outputFilePath;
 40
 41        }
 42
 43        /**//// <summary>
 44        /// 将DataTable数据写入Excel文件（套用模板并分页）
 45        /// </summary>
 46        /// <param name="dt">DataTable</param>
 47        /// <param name="rows">每个WorkSheet写入多少行数据</param>
 48        /// <param name="top">行索引</param>
 49        /// <param name="left">列索引</param>
 50        /// <param name="sheetPrefixName">WorkSheet前缀名，比如：前缀名为“Sheet”，那么WorkSheet名称依次为“Sheet-1，Sheet-2”</param>
 51        public void DataTableToExcel(DataTable dt,int rows,int top,int left,string sheetPrefixName)
 52        {
 53            int rowCount = dt.Rows.Count;        //源DataTable行数
 54            int colCount = dt.Columns.Count;    //源DataTable列数
 55            int sheetCount = this.GetSheetCount(rowCount,rows);    //WorkSheet个数
 56            DateTime beforeTime;    
 57            DateTime afterTime;
 58            
 59            if(sheetPrefixName == null || sheetPrefixName.Trim() == "")
 60                sheetPrefixName = "Sheet";
 61
 62            //创建一个Application对象并使其可见
 63            beforeTime = DateTime.Now;
 64            Excel.Application app = new Excel.ApplicationClass();
 65            app.Visible = true;
 66            afterTime = DateTime.Now;
 67
 68            //打开模板文件，得到WorkBook对象
 69            Excel.Workbook workBook = app.Workbooks.Open(templetFile,missing,missing,missing,missing,missing,
 70                                missing,missing,missing,missing,missing,missing,missing);
 71
 72            //得到WorkSheet对象
 73            Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
 74
 75            //复制sheetCount-1个WorkSheet对象
 76            for(int i=1;i<sheetCount;i++)
 77            {
 78                ((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing,workBook.Worksheets[i]);
 79            }
 80
 81            将源DataTable数据写入Excel#region 将源DataTable数据写入Excel
 82            for(int i=1;i<=sheetCount;i++)
 83            {
 84                int startRow = (i - 1) * rows;        //记录起始行索引
 85                int endRow = i * rows;            //记录结束行索引
 86
 87                //若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
 88                if(i == sheetCount)
 89                    endRow = rowCount;
 90
 91                //获取要写入数据的WorkSheet对象，并重命名
 92                Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
 93                sheet.Name = sheetPrefixName + "-" + i.ToString();
 94
 95                //将dt中的数据写入WorkSheet
 96                for(int j=0;j<endRow-startRow;j++)
 97                {
 98                    for(int k=0;k<colCount;k++)
 99                    {
100                        sheet.Cells[top + j,left + k] = dt.Rows[startRow + j][k].ToString();
101                    }
102                }
103
104                //写文本框数据
105                Excel.TextBox txtAuthor = (Excel.TextBox)sheet.TextBoxes("txtAuthor");
106                Excel.TextBox txtDate = (Excel.TextBox)sheet.TextBoxes("txtDate");
107                Excel.TextBox txtVersion = (Excel.TextBox)sheet.TextBoxes("txtVersion");
108
109                txtAuthor.Text = "KLY.NET的Blog";
110                txtDate.Text = DateTime.Now.ToShortDateString();
111                txtVersion.Text = "1.0.0.0";
112            }
113            #endregion
114
115            //输出Excel文件并退出
116            try
117            {
118                workBook.SaveAs(outputFile,missing,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);
119                workBook.Close(null,null,null);
120                app.Workbooks.Close();
121                app.Application.Quit();
122                app.Quit();
123
124                System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
125                System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
126                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
127
128                workSheet=null;
129                workBook=null;
130                app=null;
131
132                GC.Collect();
133            }
134            catch(Exception e)
135            {
136                throw e;
137            }
138            finally
139            {
140                Process[] myProcesses;
141                DateTime startTime;
142                myProcesses = Process.GetProcessesByName("Excel");
143
144                //得不到Excel进程ID，暂时只能判断进程启动时间
145                foreach(Process myProcess in myProcesses)
146                {
147                    startTime = myProcess.StartTime;
148
149                    if(startTime > beforeTime && startTime < afterTime)
150                    {
151                        myProcess.Kill();
152                    }
153                }
154            }
155            
156        }
157
158        
159        /**//// <summary>
160        /// 获取WorkSheet数量
161        /// </summary>
162        /// <param name="rowCount">记录总行数</param>
163        /// <param name="rows">每WorkSheet行数</param>
164        private int GetSheetCount(int rowCount,int rows)
165        {
166            int n = rowCount % rows;        //余数
167
168            if(n == 0)
169                return rowCount / rows;
170            else
171                return Convert.ToInt32(rowCount / rows) + 1;
172        }
173
174
175        /**//// <summary>
176        /// 将二维数组数据写入Excel文件（套用模板并分页）
177        /// </summary>
178        /// <param name="arr">二维数组</param>
179        /// <param name="rows">每个WorkSheet写入多少行数据</param>
180        /// <param name="top">行索引</param>
181        /// <param name="left">列索引</param>
182        /// <param name="sheetPrefixName">WorkSheet前缀名，比如：前缀名为“Sheet”，那么WorkSheet名称依次为“Sheet-1，Sheet-2”</param>
183        public void ArrayToExcel(string[,] arr,int rows,int top,int left,string sheetPrefixName)
184        {
185            int rowCount = arr.GetLength(0);        //二维数组行数（一维长度）
186            int colCount = arr.GetLength(1);    //二维数据列数（二维长度）
187            int sheetCount = this.GetSheetCount(rowCount,rows);    //WorkSheet个数
188            DateTime beforeTime;    
189            DateTime afterTime;
190            
191            if(sheetPrefixName == null || sheetPrefixName.Trim() == "")
192                sheetPrefixName = "Sheet";
193
194            //创建一个Application对象并使其可见
195            beforeTime = DateTime.Now;
196            Excel.Application app = new Excel.ApplicationClass();
197            app.Visible = true;
198            afterTime = DateTime.Now;
199
200            //打开模板文件，得到WorkBook对象
201            Excel.Workbook workBook = app.Workbooks.Open(templetFile,missing,missing,missing,missing,missing,
202                missing,missing,missing,missing,missing,missing,missing);
203
204            //得到WorkSheet对象
205            Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
206
207            //复制sheetCount-1个WorkSheet对象
208            for(int i=1;i<sheetCount;i++)
209            {
210                ((Excel.Worksheet)workBook.Worksheets.get_Item(i)).Copy(missing,workBook.Worksheets[i]);
211            }
212
213            将二维数组数据写入Excel#region 将二维数组数据写入Excel
214            for(int i=1;i<=sheetCount;i++)
215            {
216                int startRow = (i - 1) * rows;        //记录起始行索引
217                int endRow = i * rows;            //记录结束行索引
218
219                //若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数
220                if(i == sheetCount)
221                    endRow = rowCount;
222
223                //获取要写入数据的WorkSheet对象，并重命名
224                Excel.Worksheet sheet = (Excel.Worksheet)workBook.Worksheets.get_Item(i);
225                sheet.Name = sheetPrefixName + "-" + i.ToString();
226
227                //将二维数组中的数据写入WorkSheet
228                for(int j=0;j<endRow-startRow;j++)
229                {
230                    for(int k=0;k<colCount;k++)
231                    {
232                        sheet.Cells[top + j,left + k] = arr[startRow + j,k];
233                    }
234                }
235
236                Excel.TextBox txtAuthor = (Excel.TextBox)sheet.TextBoxes("txtAuthor");
237                Excel.TextBox txtDate = (Excel.TextBox)sheet.TextBoxes("txtDate");
238                Excel.TextBox txtVersion = (Excel.TextBox)sheet.TextBoxes("txtVersion");
239
240                txtAuthor.Text = "KLY.NET的Blog";
241                txtDate.Text = DateTime.Now.ToShortDateString();
242                txtVersion.Text = "1.0.0.0";
243            }
244            #endregion
245
246            //输出Excel文件并退出
247            try
248            {
249                workBook.SaveAs(outputFile,missing,missing,missing,missing,missing,Excel.XlSaveAsAccessMode.xlExclusive,missing,missing,missing,missing);
250                workBook.Close(null,null,null);
251                app.Workbooks.Close();
252                app.Application.Quit();
253                app.Quit();
254
255                System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
256                System.Runtime.InteropServices.Marshal.ReleaseComObject(workBook);
257                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
258
259                workSheet=null;
260                workBook=null;
261                app=null;
262
263                GC.Collect();
264            }
265            catch(Exception e)
266            {
267                throw e;
268            }
269            finally
270            {
271                Process[] myProcesses;
272                DateTime startTime;
273                myProcesses = Process.GetProcessesByName("Excel");
274
275                //得不到Excel进程ID，暂时只能判断进程启动时间
276                foreach(Process myProcess in myProcesses)
277                {
278                    startTime = myProcess.StartTime;
279
280                    if(startTime > beforeTime && startTime < afterTime)
281                    {
282                        myProcess.Kill();
283                    }
284                }
285            }
286            
287        }
288    }
289}
290