.NET-Conversion项目记录(3)：C#导入DataTable到Excel

命名空间

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] + "\r\n";
        }
    }

    xlApp.DisplayAlerts = false;
    xlApp.AlertBeforeOverwriting = false;
    xlBook.SaveAs(file_name);
    xlApp.Quit();

    return true;
}

整体的思路是

1. 创建Excel工作表xlSheet
2. 将DataTable中的数据（包括列名行）存入到一个Object数组中
3. 根据数组的大小划定xlSheet的范围xlRang
4. 将数组的值赋给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