C#与EXCEL的数据交互

首先说明,我不是程序员,也不是科班出身,只是因为工作中需要管理比较多的数据,而数据又需要留存备查,公司也没有符合要求的系统,于是萌发了开发一个数据管理系统的想法。根据自身的爱好,在不需公司投入资金的情况下,选择了VC# 2005 EXPRESS作为开发工具,SQL SERVER 2005 EXPRESS作为存储数据库。由于是在局域网环境下,使用人员也就是几个人,系统就做成了C/S的。平常工作最常用的是EXCEL来处理数据,包括数据的预处理和打印等,与其他部门的联系也是使用EXCEL,所以要求管理系统方便与EXCEL交互,最重要的就是DataTable(DataGridView)与EXCEL之间的导入导出,也因此开始研究C#2005如何操作EXCEL。好,废话少说,下面先说说数据从DataTable导出到EXCEL中,有不成熟的地方,请各位大侠指点。
  本文假设大家已经熟悉C#对Excel的调用,如果不熟悉,请参考MSDN中的《使用Excel对象模型实现Excel自动化》
  
  一、DataTable To Excel
  
  先定义一下:
  
  private Excel.Application excelApp= new Excel.ApplicationClass();//Application与ApplicationClass的区别我没有明白。
  
   private Excel.Workbook excelBook = excelApp.Workbooks.Add(Type.Missing);
  
  private Excel.Worksheet excelSheet = (Excel.Worksheet)excelBook.ActiveSheet;
  
  excelApp.Visible = true;
  
  1、让我们看看在网上流传最多的解决方案:
  
   public void DataTableToExcel(DataTable dt)
   {
   for (int i = 0; i <dt.Rows.Count; i++)
   {
   for (int j=0;j<dt.Columns.Count;j++)
   {
   excelSheet.Cells[i+1,j+1] = dt.Rows[i][j].ToString();
   }
   }
   }
  不可否认这段程序会不出错误的运行,但是效率就比较低了,会随着数据行和列的增加而增加,主要的时间都耗在了Excel单元格的读取上。
  
  让我们改进一下,利用DataRow.ItemArray:
  
  public void DataTableToExcel(DataTable dt)
  
  {
  
  int colCount = dt.Columns.Count;
  
  for (int i = 0; i <dt.Rows.Count; i++)
  
  {
  
   excelSheet.get_Range(excelSheet.Cells[i+1,1], excelSheet.Cells[i+1, colCount]).Value2 = dt.Rows[i].ItemArray;
  
   }
  }
  
  这就有了很大进步,效率与数据列的多少已经无关了,对于这个改进我在网上还没有看到,为此我还曾沾沾自喜,但是这个方法的效率依然不高,尤其是灵活性不够。
  
  以上效率的损失主要是每导出一条记录都要与Excel交互一次,如果我们把数据准备好一次性写入Excel是否效率更快?看如下程序:
  
  public void DataTableToExcel(DataTable dt)
  
  {
  
   int rowCount = dt.Rows.Count;
  
  int colCount = dt.Columns.Count;
  
  object[,] dataArray = new object[rowCount,colCount];
  
  for (int i = 0; i <rowCount; i++)
  
  {
  
  for (int j=0;j<colCount;j++)
  
  {
  
   dataArray[i, j] = dt.Rows[i][j];
  
   }
  
  }
  
  excelSheet.get_Range("A1", excelSheet.Cells[rowCount, colCount]).Value2 = dataArray;
  
  }
  
  由于二维数组是在内存中实现,所以速度很快,由于是一次性写入到Excel中,使我们几乎感觉不到延迟,在Excel打开后数据马上就出来了,感觉很爽。
  
  这一篇先写这些,下一篇中主要讨论一下字段标题的设置技巧,行列的筛选与排序等的技巧。
  
  另,在上述学习过程中,我曾想能否利用dt.Rows[i].ItemArray来组建二维数组,也就是多个相同结构的一维数组简单快速的合并成一个二维数组,当然不是利用for循环。由于我初学,还是个新手,我一直没有找到相关资料,不知哪位大侠指点一下。 
   
    本文假设大家已经熟悉C#对Excel的调用,如果不熟悉,请参考MSDN中的《使用Excel对象模型实现Excel自动化》


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

    二、给DataTableToExcel添加些智能

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

    我的解决方案:1)、利用DataTable.Select(string filterExpression)方法进行筛选;

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

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

还是看代码:

 

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

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

代码:


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;
}
3、问题提出:DataTable中有些列不需要导出(如自增列),有些列的前后顺序根据要求可能也不相同,怎么办?

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

让我们扩展上面的代码:


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

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


参考资料:

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

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

 

(编辑:IT资讯之家 www.it55.com

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值