C#与EXCEL的数据交互（二）

 

    本文假设大家已经熟悉C#对Excel的调用，如果不熟悉，请参考MSDN中的《使用Excel对象模型实现Excel自动化》

    上一篇，对比了DataTable导出到Excel中几种方式，利用二维数组的方式比较快，也是比较灵活的。有朋友说在导出的过程中需要对数据进行进一步的处理，那么使用二维数组的方式也是最恰当的。本篇接着上回，看看DataTable导出到Excel中还能做些什么？

    二、给DataTableToExcel添加些智能

    1、问题提出：数据导出时DataTable包含了一些不需要的数据行，需要进行进一步的筛选；导出时不能只要数据，每个字段的标题还要包括。在导出到Excel表时，有时并不是从第一行第一列开始，可能还有个“XXX明细表”、序号列之类的附加行列。

    我的解决方案：1）、利用DataTable.Select（string filterExpression）方法进行筛选；

                 2）、先设置好DataTable每列的Caption，然后导出Caption即可。

                 3）、添加起始行列的索引参数。

还是看代码：

2、问题提出：很多人对长数字串（如身份证号码）导出到Excel表时变成科学计数法或日期型字段变成了数字烦恼，网上一般给的解决方法是在数字串前加“’”，这是一种治标不治本的方法，不仅处理上麻烦不灵活，还不具有扩展通用性。

我的解决方案：根据DataTable中字段的数据类型（dt.Columns[i].DataType）设置EXCEL中相应区域的格式。

代码：

3、问题提出：DataTable中有些列不需要导出（如自增列），有些列的前后顺序根据要求可能也不相同，怎么办？

我的解决方案：加入一个int[]数组(如new int[]{5,2,6,4,3,8},数组中元素表示DataTable中第几列)，根据数组中列的序号和位置进行筛选和排序。

让我们扩展上面的代码：

写道这儿，DataTableToExcel也算是基本满足要求了，一些个性化的功能可以根据自己的需要增加，比如，可以返回导入到Excel最后一行的行号，方便接着导入下一个表；由于Excel有行数限制或者某些要求一个sheet不能超过多少行，这时可以采用多个sheet分页的方式导入，这是更加深入的话题了。

以上功能的实现，都是因为工作中实际需要产生的，不是每个人都需要的，可以根据情况进行重构。

参考资料：

1、 MSDN中的《用Office编程》：

2、 MSDN中的《使用Excel对象模型实现Excel自动化》.

public void DataTableToExcel(DataTable dt，string sFilter，int startRow,int startCol，int[] columns)
{       
    DataRow[] drs = dt.Select(sFilter);//根据筛选条件筛选。
    int rowCount = drs.Length;
    int colCount = columns. Length;
    object[,] dataArray = new object[rowCount+1,colCount];//二维数组定义是多一个标题行。
    for (int j = 0; j <colCount; j++)
    {
        dataArray[0,j] = dt.Columns[columns[j]].Caption;//导出字段标题。
//根据各列的数据类型设置Excel的格式。
        switch（dt.Columns[columns[j]].DataType.ToString()）
        {
            case “System.String”: excelSheet.get_Range(excelSheet.Cells[startRow, startCol+j],  excelSheet.Cells[rowCount+startRow, startCol+j]).NumberFormatLocal = “@”;break;
            case “System.DateTime”: excelSheet.get_Range(excelSheet.Cells[startRow, startCol+j], excelSheet.Cells[rowCount+startRow, startCol+j]).NumberFormatLocal = “yyyy-mm-dd”;break;
         //可以根据自己的需要扩展。
            default: excelSheet.get_Range(excelSheet.Cells[startRow, startCol+j], excelSheet.Cells[rowCount+startRow, startCol+j]).NumberFormatLocal = “G/通用格式”;break;
        }
        for (int i=0;i<rowCount;i++)
        {
            dataArray[i+1, j] = drs[i][columns[j]];
         }
    }
    excelSheet.get_Range(excelSheet.Cells[startRow, startCol], excelSheet.Cells[rowCount+startRow, colCount+startCol-1]).Value2 = dataArray;
}

public void DataTableToExcel(DataTable dt，string sFilter，int startRow,int startCol)
{       
    DataRow[] drs = dt.Select(sFilter);//根据筛选条件筛选。
    int rowCount = drs.Length;
    int colCount = dt.Columns.Count;
    object[,] dataArray = new object[rowCount+1,colCount];//二维数组定义是多一个标题行。
    for (int j = 0; j <colCount; j++)
    {
        dataArray[0,j] = dt.Columns[j].Caption;//导出字段标题。

//根据各列的数据类型设置Excel的格式。
        switch（dt.Columns[i].DataType.ToString()）
        {
            case “System.String”: excelSheet.get_Range(excelSheet.Cells[startRow, startCol+j],  excelSheet.Cells[rowCount+startRow, startCol+j]).NumberFormatLocal = “@”;break;
            case “System.DateTime”: excelSheet.get_Range(excelSheet.Cells[startRow, startCol+j], excelSheet.Cells[rowCount+startRow, startCol+j]).NumberFormatLocal = “yyyy-mm-dd”;break;
         //可以根据自己的需要扩展。
            default: excelSheet.get_Range(excelSheet.Cells[startRow, startCol+j], excelSheet.Cells[rowCount+startRow, startCol+j]).NumberFormatLocal = “G/通用格式”;break;
        }
        for (int i=0;i<rowCount;i++)
        {
            dataArray[i+1, j] = drs[i][j];
         }
    }
    excelSheet.get_Range(excelSheet.Cells[startRow, startCol], excelSheet.Cells[rowCount+startRow, colCount+startCol-1]).Value2 = dataArray;
}

 

public void DataTableToExcel(DataTable dt，string sFilter，int startRow,int startCol)

{       
    DataRow[] drs = dt.Select(sFilter);//根据筛选条件筛选。
    int rowCount = drs.Length;
    int colCount = dt.Columns.Count;
    object[,] dataArray = new object[rowCount+1,colCount];//二维数组定义是多一个标题行。
    for (int j = 0; j <colCount; j++)
    {
        dataArray[0,j] = dt.Columns[j].Caption;//导出字段标题。
         for (int i=0;i<rowCount;i++)
        {
            dataArray[i+1, j] = drs[i][j];
         }
    }
    excelSheet.get_Range(excelSheet.Cells[startRow, startCol], excelSheet.Cells[rowCount+startRow, colCount+startCol-1]).Value2 = dataArray;
}