命名空间
using Microsoft.Office.Interop.Excel;
在电脑中查找Microsoft.Office.Interop.Excel.dll(如果安装office的话)获得文件,将其复制到工程文件的调试目录下再添加引用,具体流程可参考https://www.cnblogs.com/RicardoIsLearning/p/12103332.html。
也可以在Visual Studio中选择“项目”→“管理NuGet程序包”→搜索dll文件并选择添加即可。
输入变量
数据表DataTable存入到DataSet中
DataSet.Tables.Add(DataTable);
读取的时候用
DataTable = DataSet.Table[i]
代码如下
private bool DataTable2Excel(System.Data.DataSet ds, string[] sheet_names, string file_name) { Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { //SetHint("可能机器未安装Excel!", HintType.Error); return false; } //xlApp.Visible = true; Microsoft.Office.Interop.Excel.Workbooks xlBooks = xlApp.Workbooks; Microsoft.Office.Interop.Excel.Workbook xlBook = xlBooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); //add sheet for (int itb = 1; itb < ds.Tables.Count; itb++) {//there has one sheet, so it should add one less sheet xlBook.Sheets.Add(); } //add new sheet, namely table for (int itb = 0; itb < ds.Tables.Count; itb++) { //convert values of datatable into an array object[,] objData = new object[ds.Tables[itb].Rows.Count + 1, ds.Tables[itb].Columns.Count];//remain the first row for the col name //add column name for (int icol = 0; icol < ds.Tables[itb].Columns.Count; icol++) { objData[0, icol] = ds.Tables[itb].Columns[icol].ColumnName; } //add data for (int irow = 0; irow < ds.Tables[itb].Rows.Count; irow++) { for (int icol = 0; icol < ds.Tables[itb].Columns.Count; icol++) { objData[irow + 1, icol] = ds.Tables[itb].Rows[irow][icol]; } } //generate xlSheet Microsoft.Office.Interop.Excel.Worksheet xlSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[itb + 1]; xlSheet.Name = sheet_names[itb]; try { //get range and fill data //1.set excel column name string startCol = "A"; int numsOfloop = (ds.Tables[itb].Columns.Count / 26); string endCol_start = (numsOfloop == 0 ? "" : ((char)('A' + (numsOfloop - 1))).ToString()); string endCol = endCol_start + ((char)('A' + ds.Tables[itb].Columns.Count - numsOfloop * 26 - 1)).ToString(); //2.generate range Microsoft.Office.Interop.Excel.Range range = xlSheet.get_Range( startCol + "1", endCol + (ds.Tables[itb].Rows.Count + 1).ToString() );//- iCnt * 26 //3.fill the data into the range range.NumberFormatLocal = "@"; range.Value = objData; range.EntireColumn.AutoFit(); //column rang is set as auto xlSheet.get_Range(startCol + "1", endCol + (ds.Tables[itb].Rows.Count + 1)).Font.Name = "Consolas"; } catch { //this.textBox1.Text += sheet_names[itb] + " "; } } xlApp.DisplayAlerts = false; xlApp.AlertBeforeOverwriting = false; xlBook.SaveAs(file_name); xlApp.Quit(); return true; }
整体的思路是
- 创建Excel工作表xlSheet
- 将DataTable中的数据(包括列名行)存入到一个Object数组中
- 根据数组的大小划定xlSheet的范围xlRang
- 将数组的值赋给xlRang
首先是新建变量Application→Workbook→Sheet。这里要注意原文第13-15行代码我是想并入add datatable(第17行)中的。但是发现在第34行的位置,会影响顺序。原因是每次新加入表格,但是在原来表格之前插入的新表,因此itb+1(Excel中起始的索引是1而不是0)指向的表格永远都是后数第二张表格(创建Workbook时就有一张表)。因此第13-15行代码不能删掉。
然后是读入数据部分(第20-31行),有两点要注意。
- 其一是DataTable的行数组中默认不含有列名行,所以在将DataTable的数据存入Object数组时,创建的数组要多一行用于存入列名(参加第21行)。
- 其二是存入数据时,要注意DataTable和Object的起始索引。
再是根据数组的大小划定区域(第28-46行)。注意到Excel表的区域划定格式是“Ax:By”,其中冒号前面是左上角单元格位置,冒号后面是右下角单元格位置,这里默认是从最左上角的单元格(A1)开始。如此一来就张开了一个矩形区域作为xlRang。格式中的x和y代表的都是行索引(从1开始,参见第45行代码),字母代表列索引。注意到当字母表元素不够时,是通过叠加来增加的。如Z列之后是“AB,AC,...,AZ,BA,BC,....”,所以需要判断数据的列与字母表个数的关系,作为是否增加、以及增加到多少的依据:
int numsOfloop = (ds.Tables[itb].Columns.Count / 26);
若numsOfloop不等于0,则说明需要增加、并作为右下角列的前置字母;反之则不需要、取值为null即不需要前置字母。
string endCol_start = (numsOfloop == 0 ? "" : ((char)('A' + (numsOfloop - 1))).ToString());
之后再判断后置字母即可,再跟上数组的行数作为右下角的单元格位置(第45行)
最后就是将数组的数据赋值给xlRange(第49行)
特别要注意的是,千万不要忘记关闭进程(第60行)。
object