对.NET中导出数据到EXCEL的几种方法探讨 C1TrueDBGrid

最近在做一个报表系统的时候，需要把DATASET中的数据导到EXCEL当中，于是在网上找了一遍，发现了好几种方法，本来以为应该差不多，但后来经过一一试用后，发现在性能上真的差别很大，现在就介绍一下，同时有不对的希望可以指正：
 
1． 原理：利用office组件把dataset中的数据填充到excel文件当中。
这里我不贴出全部代码了，只把关键部分贴出来：
         ///<summary>
         ///方法，导出C1TrueDBGrid中的数据到Excel文件
         ///</summary>
         ///<param name="c1grid">C1TrueDBGrid</param>
         ///<param name="FileName">Excel文件名</param>
         public void ExportToExcel(C1.Win.C1TrueDBGrid.C1TrueDBGrid c1grid,string FileName)
         {
              if(FileName.Trim() == "") return;            //验证strFileName是否为空或值无效
 
              int rowscount = c1grid.Splits[0].Rows.Count; //定义表格内数据的行数
              int colscount = c1grid.Columns.Count;        //定义表格内数据的列数
 
              //行数必须大于0
              if (rowscount <= 0)
              {
                   MessageBox.Show("没有数据可供保存","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
                   return;
              }
 
              //列数必须大于0
              if (colscount <= 0)
              {
                   MessageBox.Show("没有数据可供保存","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
                   return;
              }
 
              //行数不可以大于65536
              if (rowscount > 65536)
              {
                   MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
                   return;
              }
 
              //列数不可以大于255
              if (colscount > 255)
              {
                   MessageBox.Show("数据记录行数太多，不能保存","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
                   return;
              }
        
              //将文件保存到工作路径的子目录“/Excel”下，如果路径不存在创建它
              string n_path = Directory.GetCurrentDirectory() + "//Excel";
              if (Directory.Exists(n_path) == false)
              {
                   Directory.CreateDirectory(n_path);
              }
 
              //验证以strFileName命名的文件是否存在，如果存在删除它
              FileInfo fi = new FileInfo(n_path + "//" + FileName + ".xls");
              if(fi.Exists)
              {
                   try
                   {
                       fi.Delete();
                   }
                   catch(Exception fie)
                   {
                       MessageBox.Show(fie.Message,"删除失败", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                       return;
                   }
              }
        
              Excel.ApplicationClass excel = null;
 
              try
              {
                   //显示进度条
                   KP.PublicForm.ProgressBar pb = new PublicForm.ProgressBar("导出进度");
                   pb.Show();
                   pb.Refresh();
 
                  //新建Excel应用，新建Workbook文件
                   excel = new Excel.ApplicationClass ( ) ;
                   Excel.XlSaveAsAccessMode savemode = new Excel.XlSaveAsAccessMode();
                   excel.Application.Workbooks.Add (true) ;
 
                   //向Excel中写入表格的表头
                   int i = 1;
                   for(int c = 0;c < colscount; c++)
                   {
                       if(c1grid.Splits[0].DisplayColumns[c].Visible)
                       {
                            excel.Cells[1,i] = c1grid.Columns[c].Caption;
                            i++;
                       }
                   }
        
                   //向Excel中逐行逐列写入表格中的数据
                   for(int r = 0; r < rowscount; r++)
                   {
                       Application.DoEvents();
                       pb.SetProgressBarValue(r+1, rowscount);
                       if(pb.Cancel)
                       {
                            break;
                       }
 
                       int j = 1;
                       for(int c = 0;c < colscount; c++)
                       {
                            if(c1grid.Splits[0].DisplayColumns[c].Visible)
                            {
                                 excel.Cells[r + 2,j] = c1grid.Columns[c].CellText(r);
                                 j++;
                            }
                       }
                   }
 
                   //向Excel中写入表格的脚
                   if(c1grid.ColumnFooters)
                   {
                       int col = 1;
                       for(int c = 0;c < colscount; c++)
                       {
                            if(c1grid.Splits[0].DisplayColumns[c].Visible)
                            {
                                 if(c1grid.Columns[c].FooterText != null && c1grid.Columns[c].FooterText.Trim() != "")
                                 {
                                     excel.Cells[rowscount + 2,col] = c1grid.Columns[c].FooterText;
                                 }
                                 col++;
                            }
                       }
                   }
 
                   //关闭进度条
                   pb.Close();
 
                   //设置Excel的默认保存路径为当前路径下的Excel子文件夹
                   excel.DefaultFilePath = n_path;
 
                   //保存文件
                   excel.ActiveWorkbook.SaveAs(FileName + ".xls",excel.ActiveWorkbook.FileFormat,"","",excel.ActiveWorkbook.ReadOnlyRecommended,excel.ActiveWorkbook.CreateBackup,savemode,excel.ActiveWorkbook.ConflictResolution,false,"","");
        
              }
              catch(Exception e1)
              {
                   MessageBox.Show(e1.Message, "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                   return;
              }
              finally
              {
                   //关闭Excel应用
                   if(excel != null) excel.Quit();
              }
              MessageBox.Show(FileName + "导出完毕，在" + Application.StartupPath + "//Excel文件夹下","提示", MessageBoxButtons.OK,MessageBoxIcon.Information);
//       }
 
}
 
       总结：这个方法是可以解决问题，但效率最差，3000条长点的record就用了6分钟，晕~~~~
 
2．   原理：利用office组件，同时把dataset的数据导到Clipboard中，然后通过粘贴到excel中。
         Excel.XlSaveAsAccessMode savemode = new Excel.XlSaveAsAccessMode();
                   xlApp.Application.Workbooks.Add (true) ;
                   xlApp.DefaultFilePath = @"c:/";
                   xlApp.ActiveWorkbook.SaveAs("exportExcel.xls",xlApp.ActiveWorkbook.FileFormat,"","",xlApp.ActiveWorkbook.ReadOnlyRecommended,xlApp.ActiveWorkbook.CreateBackup,savemode,xlApp.ActiveWorkbook.ConflictResolution,false,"","","");
        
             
 
 
                   Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath, oMissing, oMissing, oMissing, oMissing, oMissing,
                       oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
                       oMissing,oMissing,oMissing);
                  
                   Excel.Worksheet xlWorksheet;
 
      
                   // 循环所有DataTable
                   for( int i=0; i<ds.Tables.Count; i++ )
                   {
                       xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.Add(oMissing,oMissing,1,oMissing);
                       // 以TableName作为新加的Sheet页名。
                       xlWorksheet.Name = ds.Tables[i].TableName;
                       // 取出这个DataTable中的所有值,暂存于stringBuffer中。
                       string stringBuffer = "";
                       //向Excel中写入表格的表头
                       if(node != null)
                       {
                            XmlNode nodec=node.SelectSingleNode("./Method/ShowField");
                            int ii = 1;
                            foreach(XmlNode xnode in nodec.ChildNodes )
                            {
                                 xlApp.Cells[1,ii] =xnode.Attributes["displayname"].Value;                               
                                 ii++;
                            }
                      
                           
                            for( int j=0; j<ds.Tables[i].Rows.Count; j++ )
                            {
                                 for( int k=0; k<ds.Tables[i].Columns.Count; k++ )
                                 {
             
                                     stringBuffer += ds.Tables[i].Rows[j][k].ToString();
                                     if( k < ds.Tables[i].Columns.Count - 1 )
                                          stringBuffer += "/t";
                                 }
                                 stringBuffer += "/n";
                            }
                           
                       }
                       else
                       {
                            int ii = 1;
                            for(int c = 0;c<ds.Tables[i].Columns.Count; c++)
                            {
                                 xlApp.Cells[1,ii] = ds.Tables[i].Columns[c].Caption;
                                 ii++;
                            }
                           
                            for( int j=0; j<ds.Tables[i].Rows.Count; j++ )
                            {
                                 for( int k=0; k<ds.Tables[i].Columns.Count; k++ )
                                 {
             
                                     stringBuffer += ds.Tables[i].Rows[j][k].ToString();
                                     if( k < ds.Tables[i].Columns.Count - 1 )
                                          stringBuffer += "/t";
                                 }
                                 stringBuffer += "/n";
                            }
                       }
 
                       System.Windows.Forms.Clipboard.SetDataObject("");
                       // 将stringBuffer放入剪切板。
                       System.Windows.Forms.Clipboard.SetDataObject(stringBuffer);
                       // 选中这个sheet页中的第一个单元格
                       ((Excel.Range)xlWorksheet.Cells[2,1]).Select();
                       // 粘贴！
                       xlWorksheet.Paste(oMissing,oMissing);
                       // 清空系统剪切板。
                       System.Windows.Forms.Clipboard.SetDataObject("");
                  
 
 
                  
                   }
                   // 保存并关闭这个工作簿。
                  
             
 
             
                           
                   xlApp.ActiveWorkbook.Close( Excel.XlSaveAction.xlSaveChanges, oMissing, oMissing );
                   //                 xlWorkbook.Close( Excel.XlSaveAction.xlSaveChanges, oMissing, oMissing );
                   System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);
                   xlWorkbook = null;
                   MessageBox.Show(@"Excel文件：C:/exportExcel.xls 导出成功！");
              }
              catch(Exception ex)
              {
                   MessageBox.Show(ex.Message);
              }
              finally
              {
                   // 释放...
                   xlApp.Quit();
                   System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
                   xlApp = null;
                   GC.Collect();
          }
 
       总结：这个方法比上面的方法性能好点，但还是很不好用，比原来的提高了2倍左右。
 
3． 原理：利用OLEDB，以excel为数据库，把dataset中的数据导入到excel文件中
       public static void exportToExcelByDataset(string filePath, DataSet ds,XmlNode node)
  {
   string sqlstr;

   if(fi.Exists)
   {
    fi.Delete();
    //     throw new Exception("文件删除失败");
   }
   else
   {
    fi.Create();
   }
  
   string sqlcon=@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended ProPerties=Excel 8.0;";
   OleDbConnection olecon = new OleDbConnection(sqlcon);
   OleDbCommand olecmd = new OleDbCommand();
   olecmd.Connection = olecon;
   olecmd.CommandType = CommandType.Text;
 

   try
   {
    olecon.Open();
           
    XmlNode nodec=node.SelectSingleNode("./Method/ShowField");
    int ii = 0;
    sqlstr = "CREATE TABLE sheet1(";
    foreach(XmlNode xnode in nodec.ChildNodes )
    {
     if(ii == nodec.ChildNodes.Count - 1)
     {
      if(xnode.Attributes["type"].Value.ToLower() == "int"||xnode.Attributes["type"].Value.ToLower() == "decimal")
      {
       sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " number)";      
      }
      else
      {
       sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " text)";
      }
      //      sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " text)";
     }
     else
     {
      if(xnode.Attributes["type"].Value.ToLower() == "int"||xnode.Attributes["type"].Value.ToLower() == "decimal")
      {
       sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " number,";      
      }
      else
      {
       sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " text,";
      }
      
     }
     //     sqlstr =sqlstr + xnode.Attributes["displayname"].Value + " text";      
     ii++;
    }
    olecmd.CommandText = sqlstr;
    olecmd.ExecuteNonQuery();
    for(int i=0;i<ds.Tables[0].Rows.Count;i++)
    {
     sqlstr = "INSERT INTO sheet1 VALUES(";
     int jj=0;
     foreach(XmlNode inode in nodec.ChildNodes )
     {
      if(jj == nodec.ChildNodes.Count-1)
      {
       if(inode.Attributes["type"].Value.ToLower() == "int"||inode.Attributes["type"].Value.ToLower() == "decimal")
       {
        sqlstr = sqlstr + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString()) + ")" ;
       
       }
       else
       {
        sqlstr = sqlstr + "'" + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString().Replace("'","''")) + "')" ;
       }
      }
      else
      {
       if(inode.Attributes["type"].Value.ToLower() == "int"||inode.Attributes["type"].Value.ToLower() == "decimal")
       {
        sqlstr = sqlstr + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString()) + "," ;
       
       }
       else
       {
        sqlstr = sqlstr + "'" + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString().Replace("'","''")) + "'," ;
       }
      }
      jj++;
     }
     olecmd.CommandText = sqlstr;
     olecmd.ExecuteNonQuery();
    
    } 
    MessageBox.Show(@"Excel文件：" + filePath + " 导出成功！");
   }
   catch(Exception ex)
   {
    MessageBox.Show(ex.Message);
   }
   finally
   {
    olecmd.Dispose();
    olecon.Close();
    olecon.Dispose();
                  
   }
  }
/// <summary>
/// change to string "null" if input is null
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
 
  private static string isnull(string obj)
  {
   if(obj.Length >0)
   {
    return obj;
   }
   else
   {
    return "null";
   }
  }
       总结：这个方法是最好的，速度飞快，比上面两种提高不止10倍，而且关键是不需要用到office组件，所以我正在用着这种方法，客户也满意。当然这个也有它不好的地方，有时候会受到导入的数据不符的异常困扰，而且为了赶时间，代码写的不好，一句话，能用但要改进的地方很多：）
      

 ///2007-03-02

最近发现几个导出到EXCEL的方法,这里先记录下来

4.本示例是用于将ListView中的内容倒入到Excel 与常用的逐单元格写不同的是，本例子采用数据写入到range的方法。该方法效率明显较高
Excel.Application app = new Excel.ApplicationClass();   
if( app == null)   
{
     MessageBox.Show("Excel无法启动");
     return;   
}
    app.Visible = true;
    Excel.Workbooks wbs = app.Workbooks;
    Excel.Workbook wb = wbs.Add(Missing.Value);
    Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];
    Excel.Range r = ws.get_Range("A1","H1");
    object [] objHeader = {"标题1","标题2","标题3","标题4","标题5","标题6","标题7","标题8"};
    r.Value = objHeader;
    if (lv.Items.Count >0)
    {
     r = ws.get_Range("A2",Missing.Value);
       object [,] objData = new Object[this.lv.Items.Count,8];
     foreach(ListViewItem lvi in lv.Items)
     {
      objData[lvi.Index,0] = lvi.Text;
      objData[lvi.Index,1] = lvi.SubItems[1].Text;
      objData[lvi.Index,2] = lvi.SubItems[2].Text;
      objData[lvi.Index,3] = lvi.SubItems[3].Text;
      objData[lvi.Index,4] = lvi.SubItems[4].Text;
      objData[lvi.Index,5] = lvi.SubItems[5].Text;
      objData[lvi.Index,6] = lvi.SubItems[6].Text;
      objData[lvi.Index,7] = lvi.SubItems[7].Text;
     }
     r = r.get_Resize(lv.Items.Count,8);
     r.Value = objData;
     r.EntireColumn.AutoFit();
    }
    app = null;

5.由XML文件导出为EXCEL文件

目录下kfcccer.xml为原始数据XML文件，点击生成后会在同级目录下生成kfcccer.xls文件

页面代码如下:
<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>XML转换Excel演示</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <div>
            <asp:Button ID="btnChange" runat="server" Font-Bold="True" Font-Size="18pt" ForeColor="Black"
                Height="38px" OnClick="btnChange_Click" Text="开始转换" Width="203px" /></div>
   
    </div>
    </form>
</body>
</html>

后台代码:
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnChange_Click(object sender, EventArgs e)
    {
        try
        {
            //要转换的XML文件
            string XMLFileName = Path.Combine(Request.PhysicalApplicationPath, "kfcccer.xml");
            DataSet dsBook = new DataSet();
            dsBook.ReadXml(XMLFileName);
            int rows = dsBook.Tables[0].Rows.Count + 1;
            int cols = dsBook.Tables[0].Columns.Count;

            //将要生成的Excel文件
            string ExcelFileName = Path.Combine(Request.PhysicalApplicationPath, "kfcccer.xls");
            if (File.Exists(ExcelFileName))
            {
                File.Delete(ExcelFileName);
            }
            StreamWriter writer = new StreamWriter(ExcelFileName, false);
            writer.WriteLine("<?xml version="1.0"?>");
            writer.WriteLine("<?mso-application progid="Excel.Sheet"?>");
            writer.WriteLine("<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"");
            writer.WriteLine(" xmlns:o="urn:schemas-microsoft-com:office:office"");
            writer.WriteLine(" xmlns:x="urn:schemas-microsoft-com:office:excel"");
            writer.WriteLine(" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"");
            writer.WriteLine(" xmlns:html="http://www.w3.org/TR/REC-html40/">");
            writer.WriteLine(" <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">");
            writer.WriteLine("  <Author>Automated Report Generator Example</Author>");
            writer.WriteLine(string.Format("  <Created>{0}T{1}Z</Created>", DateTime.Now.ToString("yyyy-mm-dd"), DateTime.Now.ToString("HH:MM:SS")));
            writer.WriteLine("  <Company>51aspx.com</Company>");
            writer.WriteLine("  <Version>11.6408</Version>");
            writer.WriteLine(" </DocumentProperties>");
            writer.WriteLine(" <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">");
            writer.WriteLine("  <WindowHeight>8955</WindowHeight>");
            writer.WriteLine("  <WindowWidth>11355</WindowWidth>");
            writer.WriteLine("  <WindowTopX>480</WindowTopX>");
            writer.WriteLine("  <WindowTopY>15</WindowTopY>");
            writer.WriteLine("  <ProtectStructure>False</ProtectStructure>");
            writer.WriteLine("  <ProtectWindows>False</ProtectWindows>");
            writer.WriteLine(" </ExcelWorkbook>");
            writer.WriteLine(" <Styles>");
            writer.WriteLine("  <Style ss:ID="Default" ss:Name="Normal">");
            writer.WriteLine("   <Alignment ss:Vertical="Bottom"/>");
            writer.WriteLine("   <Borders/>");
            writer.WriteLine("   <Font/>");
            writer.WriteLine("   <Interior/>");
            writer.WriteLine("   <Protection/>");
            writer.WriteLine("  </Style>");
            writer.WriteLine("  <Style ss:ID="s21">");
            writer.WriteLine("   <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>");
            writer.WriteLine("  </Style>");
            writer.WriteLine(" </Styles>");
            writer.WriteLine(" <Worksheet ss:Name="MyReport">");
            writer.WriteLine(string.Format("  <Table ss:ExpandedColumnCount="{0}" ss:ExpandedRowCount="{1}" x:FullColumns="1"", cols.ToString(), rows.ToString()));
            writer.WriteLine("   x:FullRows="1">");

            //生成标题
            writer.WriteLine("<Row>");
            foreach (DataColumn eachCloumn in dsBook.Tables[0].Columns)
            {
                writer.Write("<Cell ss:StyleID="s21"><Data ss:Type="String">");
                writer.Write(eachCloumn.ColumnName.ToString());
                writer.WriteLine("</Data></Cell>");
            }
            writer.WriteLine("</Row>");

            //生成数据记录
            foreach (DataRow eachRow in dsBook.Tables[0].Rows)
            {
                writer.WriteLine("<Row>");
                for (int currentRow = 0; currentRow != cols; currentRow++)
                {
                    writer.Write("<Cell ss:StyleID="s21"><Data ss:Type="String">");
                    writer.Write(eachRow[currentRow].ToString());
                    writer.WriteLine("</Data></Cell>");
                }
                writer.WriteLine("</Row>");
            }
            writer.WriteLine("  </Table>");
            writer.WriteLine("  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">");
            writer.WriteLine("   <Selected/>");
            writer.WriteLine("   <Panes>");
            writer.WriteLine("    <Pane>");
            writer.WriteLine("     <Number>3</Number>");
            writer.WriteLine("     <ActiveRow>1</ActiveRow>");
            writer.WriteLine("    </Pane>");
            writer.WriteLine("   </Panes>");
            writer.WriteLine("   <ProtectObjects>False</ProtectObjects>");
            writer.WriteLine("   <ProtectScenarios>False</ProtectScenarios>");
            writer.WriteLine("  </WorksheetOptions>");
            writer.WriteLine(" </Worksheet>");
            writer.WriteLine(" <Worksheet ss:Name="Sheet2">");
            writer.WriteLine("  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">");
            writer.WriteLine("   <ProtectObjects>False</ProtectObjects>");
            writer.WriteLine("   <ProtectScenarios>False</ProtectScenarios>");
            writer.WriteLine("  </WorksheetOptions>");
            writer.WriteLine(" </Worksheet>");
            writer.WriteLine(" <Worksheet ss:Name="Sheet3">");
            writer.WriteLine("  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">");
            writer.WriteLine("   <ProtectObjects>False</ProtectObjects>");
            writer.WriteLine("   <ProtectScenarios>False</ProtectScenarios>");
            writer.WriteLine("  </WorksheetOptions>");
            writer.WriteLine(" </Worksheet>");
            writer.WriteLine("</Workbook>");
            writer.Close();
            Response.Write("<script language="javascript">" + "alert('" + "转换成功! 转换后的Excel文件名为: kfcccer.xls')" + "</script>");
        }
        catch (Exception ex)
        {
            Response.Write("<script language="javascript">" + "alert('" + "操作失败! 出错信息: " + ex.Message + "')" + "</script>");
        }
    }
}