御不凡
对.NET中导出数据到EXCEL的几种方法探讨
最近在做一个报表系统的时候，需要把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文件中
      
[csharp] view plaincopy
  1.  public static void exportToExcelByDataset(string filePath, DataSet ds,XmlNode node)  
  2.   
  3.   {  
  4.   
  5.    string sqlstr;  
  6.   
  7.   
  8.   
  9.   
  10.   
  11.   
  12.   
  13.    if(fi.Exists)  
  14.   
  15.    {  
  16.   
  17.     fi.Delete();  
  18.   
  19.     //     throw new Exception("文件删除失败");  
  20.   
  21.    }  
  22.   
  23.    else  
  24.   
  25.    {  
  26.   
  27.     fi.Create();  
  28.   
  29.    }  
  30.   
  31.      
  32.   
  33.    string sqlcon=@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended ProPerties=Excel 8.0;";  
  34.   
  35.    OleDbConnection olecon = new OleDbConnection(sqlcon);  
  36.   
  37.    OleDbCommand olecmd = new OleDbCommand();  
  38.   
  39.    olecmd.Connection = olecon;  
  40.   
  41.    olecmd.CommandType = CommandType.Text;  
  42.   
  43.    
  44.   
  45.   
  46.   
  47.    try  
  48.   
  49.    {  
  50.   
  51.     olecon.Open();  
  52.   
  53.               
  54.   
  55.   
  56.   
  57.     XmlNode nodec=node.SelectSingleNode("./Method/ShowField");  
  58.   
  59.     int ii = 0;  
  60.   
  61.     sqlstr = "CREATE TABLE sheet1(";  
  62.   
  63.     foreach(XmlNode xnode in nodec.ChildNodes )  
  64.   
  65.     {  
  66.   
  67.      if(ii == nodec.ChildNodes.Count - 1)  
  68.   
  69.      {  
  70.   
  71.       if(xnode.Attributes["type"].Value.ToLower() == "int"||xnode.Attributes["type"].Value.ToLower() == "decimal")  
  72.   
  73.       {  
  74.   
  75.        sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " number)";         
  76.   
  77.       }  
  78.   
  79.       else  
  80.   
  81.       {  
  82.   
  83.        sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " text)";  
  84.   
  85.       }  
  86.   
  87.       //      sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " text)";  
  88.   
  89.      }  
  90.   
  91.      else  
  92.   
  93.      {  
  94.   
  95.       if(xnode.Attributes["type"].Value.ToLower() == "int"||xnode.Attributes["type"].Value.ToLower() == "decimal")  
  96.   
  97.       {  
  98.   
  99.        sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " number,";         
  100.   
  101.       }  
  102.   
  103.       else  
  104.   
  105.       {  
  106.   
  107.        sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " text,";  
  108.   
  109.       }  
  110.   
  111.          
  112.   
  113.      }  
  114.   
  115.      //     sqlstr =sqlstr + xnode.Attributes["displayname"].Value + " text";         
  116.   
  117.      ii++;  
  118.   
  119.     }  
  120.   
  121.     olecmd.CommandText = sqlstr;  
  122.   
  123.     olecmd.ExecuteNonQuery();  
  124.   
  125.     for(int i=0;i<ds.Tables[0].Rows.Count;i++)  
  126.   
  127.     {  
  128.   
  129.      sqlstr = "INSERT INTO sheet1 VALUES(";  
  130.   
  131.      int jj=0;  
  132.   
  133.   
  134.   
  135.      foreach(XmlNode inode in nodec.ChildNodes )  
  136.   
  137.      {  
  138.   
  139.       if(jj == nodec.ChildNodes.Count-1)  
  140.   
  141.       {  
  142.   
  143.        if(inode.Attributes["type"].Value.ToLower() == "int"||inode.Attributes["type"].Value.ToLower() == "decimal")  
  144.   
  145.        {  
  146.   
  147.         sqlstr = sqlstr + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString()) + ")" ;  
  148.   
  149.           
  150.   
  151.        }  
  152.   
  153.        else  
  154.   
  155.        {  
  156.   
  157.         sqlstr = sqlstr + "'" + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString().Replace("'","''")) + "')" ;  
  158.   
  159.        }  
  160.   
  161.       }  
  162.   
  163.       else  
  164.   
  165.       {  
  166.   
  167.        if(inode.Attributes["type"].Value.ToLower() == "int"||inode.Attributes["type"].Value.ToLower() == "decimal")  
  168.   
  169.        {  
  170.   
  171.         sqlstr = sqlstr + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString()) + "," ;  
  172.   
  173.           
  174.   
  175.        }  
  176.   
  177.        else  
  178.   
  179.        {  
  180.   
  181.         sqlstr = sqlstr + "'" + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString().Replace("'","''")) + "'," ;  
  182.   
  183.        }  
  184.   
  185.       }  
  186.   
  187.       jj++;  
  188.   
  189.      }  
  190.   
  191.      olecmd.CommandText = sqlstr;  
  192.   
  193.      olecmd.ExecuteNonQuery();  
  194.   
  195.        
  196.   
  197.     }    
  198.   
  199.     MessageBox.Show(@"Excel文件：" + filePath + " 导出成功！");  
  200.   
  201.    }  
  202.   
  203.    catch(Exception ex)  
  204.   
  205.    {  
  206.   
  207.     MessageBox.Show(ex.Message);  
  208.   
  209.    }  
  210.   
  211.    finally  
  212.   
  213.    {  
  214.   
  215.     olecmd.Dispose();  
  216.   
  217.     olecon.Close();  
  218.   
  219.     olecon.Dispose();  
  220.   
  221.                      
  222.   
  223.    }  
  224.   
  225.   }  
  226.   
  227. /// <summary>  
  228.   
  229. /// change to string "null" if input is null  
  230.   
  231. /// </summary>  
  232.   
  233. /// <param name="obj"></param>  
  234.   
  235. /// <returns></returns>  
  236.   
  237.    
  238.   
  239.   private static string isnull(string obj)  
  240.   
  241.   {  
  242.   
  243.    if(obj.Length >0)  
  244.   
  245.    {  
  246.   
  247.     return obj;  
  248.   
  249.    }  
  250.   
  251.    else  
  252.   
  253.    {  
  254.   
  255.     return "null";  
  256.   
  257.    }  
  258.   
  259.   }  
       总结：这个方法是最好的，速度飞快，比上面两种提高不止10倍，而且关键是不需要用到office组 件，所以我正在用着这种方法，客户也满意。当然这个也有它不好的地方，有时候会受到导入的数据不符的异常困扰，而且为了赶时间，代码写的不好，一句话，能 用但要改进的地方很多：）
      

 ///2007-03-02

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

4.本示例是用于将ListView中的内容倒入到Excel 与常用的逐单元格写不同的是，本例子采用数据写入到range的方法。该方法效率明显较高

[csharp] view plaincopy
  1. Excel.Application app = new Excel.ApplicationClass();      
  2.   
  3. if( app == null)      
  4.   
  5. {  
  6.   
  7.      MessageBox.Show("Excel无法启动");  
  8.   
  9.      return;      
  10.   
  11. }  
  12.   
  13.     app.Visible = true;  
  14.   
  15.     Excel.Workbooks wbs = app.Workbooks;  
  16.   
  17.     Excel.Workbook wb = wbs.Add(Missing.Value);  
  18.   
  19.     Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];  
  20.   
  21.     Excel.Range r = ws.get_Range("A1","H1");  
  22.   
  23.     object [] objHeader = {"标题1","标题2","标题3","标题4","标题5","标题6","标题7","标题8"};  
  24.   
  25.     r.Value = objHeader;  
  26.   
  27.     if (lv.Items.Count >0)  
  28.   
  29.     {  
  30.   
  31.      r = ws.get_Range("A2",Missing.Value);  
  32.   
  33.        object [,] objData = new Object[this.lv.Items.Count,8];  
  34.   
  35.      foreach(ListViewItem lvi in lv.Items)  
  36.   
  37.      {  
  38.   
  39.       objData[lvi.Index,0] = lvi.Text;  
  40.   
  41.       objData[lvi.Index,1] = lvi.SubItems[1].Text;  
  42.   
  43.       objData[lvi.Index,2] = lvi.SubItems[2].Text;  
  44.   
  45.       objData[lvi.Index,3] = lvi.SubItems[3].Text;  
  46.   
  47.       objData[lvi.Index,4] = lvi.SubItems[4].Text;  
  48.   
  49.       objData[lvi.Index,5] = lvi.SubItems[5].Text;  
  50.   
  51.       objData[lvi.Index,6] = lvi.SubItems[6].Text;  
  52.   
  53.       objData[lvi.Index,7] = lvi.SubItems[7].Text;  
  54.   
  55.      }  
  56.   
  57.      r = r.get_Resize(lv.Items.Count,8);  
  58.   
  59.      r.Value = objData;  
  60.   
  61.      r.EntireColumn.AutoFit();  
  62.   
  63.     }  
  64.   
  65.     app = null;  

 

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

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

页面代码如下:

[csharp] view plaincopy
  1. @ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>  
  2.   
  3.   
  4.   
  5. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
  6.   
  7.   
  8.   
  9. <html xmlns="http://www.w3.org/1999/xhtml" >  
  10.   
  11. <head runat="server">  
  12.   
  13.     <title>XML转换Excel演示</title>  
  14.   
  15. </head>  
  16.   
  17. <body>  
  18.   
  19.     <form id="form1" runat="server">  
  20.   
  21.     <div>  
  22.   
  23.         <div>  
  24.   
  25.             <asp:Button ID="btnChange" runat="server" Font-Bold="True" Font-Size="18pt" ForeColor="Black"  
  26.   
  27.                 Height="38px" OnClick="btnChange_Click" Text="开始转换" Width="203px" /></div>  
  28.   
  29.       
  30.   
  31.     </div>  
  32.   
  33.     </form>  
  34.   
  35. </body>  
  36.   
  37. </html>  

后台代码:

[csharp] view plaincopy
  1. using System;  
  2.   
  3. using System.Data;  
  4.   
  5. using System.Configuration;  
  6.   
  7. using System.Collections;  
  8.   
  9. using System.Web;  
  10.   
  11. using System.Web.Security;  
  12.   
  13. using System.Web.UI;  
  14.   
  15. using System.Web.UI.WebControls;  
  16.   
  17. using System.Web.UI.WebControls.WebParts;  
  18.   
  19. using System.Web.UI.HtmlControls;  
  20.   
  21. using System.IO;  
  22.   
  23.   
  24.   
  25. public partial class _Default : System.Web.UI.Page   
  26.   
  27. {  
  28.   
  29.     protected void Page_Load(object sender, EventArgs e)  
  30.   
  31.     {  
  32.   
  33.   
  34.   
  35.     }  
  36.   
  37.     protected void btnChange_Click(object sender, EventArgs e)  
  38.   
  39.     {  
  40.   
  41.         try  
  42.   
  43.         {  
  44.   
  45.             //要转换的XML文件  
  46.   
  47.             string XMLFileName = Path.Combine(Request.PhysicalApplicationPath, "kfcccer.xml");  
  48.   
  49.             DataSet dsBook = new DataSet();  
  50.   
  51.             dsBook.ReadXml(XMLFileName);  
  52.   
  53.             int rows = dsBook.Tables[0].Rows.Count + 1;  
  54.   
  55.             int cols = dsBook.Tables[0].Columns.Count;  
  56.   
  57.   
  58.   
  59.             //将要生成的Excel文件  
  60.   
  61.             string ExcelFileName = Path.Combine(Request.PhysicalApplicationPath, "kfcccer.xls");  
  62.   
  63.             if (File.Exists(ExcelFileName))  
  64.   
  65.             {  
  66.   
  67.                 File.Delete(ExcelFileName);  
  68.   
  69.             }  
  70.   
  71.             StreamWriter writer = new StreamWriter(ExcelFileName, false);  
  72.   
  73.             writer.WriteLine("<?xml version="1.0"?>");  
  74.   
  75.             writer.WriteLine("<?mso-application progid="Excel.Sheet"?>");  
  76.   
  77.             writer.WriteLine("<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"");  
  78.   
  79.             writer.WriteLine(" xmlns:o="urn:schemas-microsoft-com:office:office"");  
  80.   
  81.             writer.WriteLine(" xmlns:x="urn:schemas-microsoft-com:office:excel"");  
  82.   
  83.             writer.WriteLine(" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"");  
  84.   
  85.             writer.WriteLine(" xmlns:html="http://www.w3.org/TR/REC-html40/">");  
  86.   
  87.             writer.WriteLine(" <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">");  
  88.   
  89.             writer.WriteLine("  <Author>Automated Report Generator Example</Author>");  
  90.   
  91.             writer.WriteLine(string.Format("  <Created>{0}T{1}Z</Created>", DateTime.Now.ToString("yyyy-mm-dd"), DateTime.Now.ToString("HH:MM:SS")));  
  92.   
  93.             writer.WriteLine("  <Company>51aspx.com</Company>");  
  94.   
  95.             writer.WriteLine("  <Version>11.6408</Version>");  
  96.   
  97.             writer.WriteLine(" </DocumentProperties>");  
  98.   
  99.             writer.WriteLine(" <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">");  
  100.   
  101.             writer.WriteLine("  <WindowHeight>8955</WindowHeight>");  
  102.   
  103.             writer.WriteLine("  <WindowWidth>11355</WindowWidth>");  
  104.   
  105.             writer.WriteLine("  <WindowTopX>480</WindowTopX>");  
  106.   
  107.             writer.WriteLine("  <WindowTopY>15</WindowTopY>");  
  108.   
  109.             writer.WriteLine("  <ProtectStructure>False</ProtectStructure>");  
  110.   
  111.             writer.WriteLine("  <ProtectWindows>False</ProtectWindows>");  
  112.   
  113.             writer.WriteLine(" </ExcelWorkbook>");  
  114.   
  115.             writer.WriteLine(" <Styles>");  
  116.   
  117.             writer.WriteLine("  <Style ss:ID="Default" ss:Name="Normal">");  
  118.   
  119.             writer.WriteLine("   <Alignment ss:Vertical="Bottom"/>");  
  120.   
  121.             writer.WriteLine("   <Borders/>");  
  122.   
  123.             writer.WriteLine("   <Font/>");  
  124.   
  125.             writer.WriteLine("   <Interior/>");  
  126.   
  127.             writer.WriteLine("   <Protection/>");  
  128.   
  129.             writer.WriteLine("  </Style>");  
  130.   
  131.             writer.WriteLine("  <Style ss:ID="s21">");  
  132.   
  133.             writer.WriteLine("   <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>");  
  134.   
  135.             writer.WriteLine("  </Style>");  
  136.   
  137.             writer.WriteLine(" </Styles>");  
  138.   
  139.             writer.WriteLine(" <Worksheet ss:Name="MyReport">");  
  140.   
  141.             writer.WriteLine(string.Format("  <Table ss:ExpandedColumnCount="{0}" ss:ExpandedRowCount="{1}" x:FullColumns="1"", cols.ToString(), rows.ToString()));  
  142.   
  143.             writer.WriteLine("   x:FullRows="1">");  
  144.   
  145.   
  146.   
  147.             //生成标题  
  148.   
  149.             writer.WriteLine("<Row>");  
  150.   
  151.             foreach (DataColumn eachCloumn in dsBook.Tables[0].Columns)  
  152.   
  153.             {  
  154.   
  155.                 writer.Write("<Cell ss:StyleID="s21"><Data ss:Type="String">");  
  156.   
  157.                 writer.Write(eachCloumn.ColumnName.ToString());  
  158.   
  159.                 writer.WriteLine("</Data></Cell>");  
  160.   
  161.             }  
  162.   
  163.             writer.WriteLine("</Row>");  
  164.   
  165.   
  166.   
  167.             //生成数据记录  
  168.   
  169.             foreach (DataRow eachRow in dsBook.Tables[0].Rows)  
  170.   
  171.             {  
  172.   
  173.                 writer.WriteLine("<Row>");  
  174.   
  175.                 for (int currentRow = 0; currentRow != cols; currentRow++)  
  176.   
  177.                 {  
  178.   
  179.                     writer.Write("<Cell ss:StyleID="s21"><Data ss:Type="String">");  
  180.   
  181.                     writer.Write(eachRow[currentRow].ToString());  
  182.   
  183.                     writer.WriteLine("</Data></Cell>");  
  184.   
  185.                 }  
  186.   
  187.                 writer.WriteLine("</Row>");  
  188.   
  189.             }  
  190.   
  191.             writer.WriteLine("  </Table>");  
  192.   
  193.             writer.WriteLine("  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">");  
  194.   
  195.             writer.WriteLine("   <Selected/>");  
  196.   
  197.             writer.WriteLine("   <Panes>");  
  198.   
  199.             writer.WriteLine("    <Pane>");  
  200.   
  201.             writer.WriteLine("     <Number>3</Number>");  
  202.   
  203.             writer.WriteLine("     <ActiveRow>1</ActiveRow>");  
  204.   
  205.             writer.WriteLine("    </Pane>");  
  206.   
  207.             writer.WriteLine("   </Panes>");  
  208.   
  209.             writer.WriteLine("   <ProtectObjects>False</ProtectObjects>");  
  210.   
  211.             writer.WriteLine("   <ProtectScenarios>False</ProtectScenarios>");  
  212.   
  213.             writer.WriteLine("  </WorksheetOptions>");  
  214.   
  215.             writer.WriteLine(" </Worksheet>");  
  216.   
  217.             writer.WriteLine(" <Worksheet ss:Name="Sheet2">");  
  218.   
  219.             writer.WriteLine("  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">");  
  220.   
  221.             writer.WriteLine("   <ProtectObjects>False</ProtectObjects>");  
  222.   
  223.             writer.WriteLine("   <ProtectScenarios>False</ProtectScenarios>");  
  224.   
  225.             writer.WriteLine("  </WorksheetOptions>");  
  226.   
  227.             writer.WriteLine(" </Worksheet>");  
  228.   
  229.             writer.WriteLine(" <Worksheet ss:Name="Sheet3">");  
  230.   
  231.             writer.WriteLine("  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">");  
  232.   
  233.             writer.WriteLine("   <ProtectObjects>False</ProtectObjects>");  
  234.   
  235.             writer.WriteLine("   <ProtectScenarios>False</ProtectScenarios>");  
  236.   
  237.             writer.WriteLine("  </WorksheetOptions>");  
  238.   
  239.             writer.WriteLine(" </Worksheet>");  
  240.   
  241.             writer.WriteLine("</Workbook>");  
  242.   
  243.             writer.Close();  
  244.   
  245.             Response.Write("<script language="javascript">" + "alert('" + "转换成功! 转换后的Excel文件名为: kfcccer.xls')" + "</script>");  
  246.   
  247.         }  
  248.   
  249.         catch (Exception ex)  
  250.   
  251.         {  
  252.   
  253.             Response.Write("<script language="javascript">" + "alert('" + "操作失败! 出错信息: " + ex.Message + "')" + "</script>");  
  254.   
  255.         }  
  256.   
  257.     }  
  258.   
  259. }  

6.以模版形式导出为EXCEL文件 最近在项目中看到同事的一个导出的解决方案，感觉很不错，以前我如果碰到有些客户要按某些格式把数据导出为EXCEL时，就会感觉很麻烦，有段时间会用水 晶报表导出EXCEL的形式来做，但效果很不好，有些控件会提供对应的方法，但这不是我们这些人所喜欢的，我喜欢能自己编，而不喜欢给人已经封装好的东 西，除非他提供原代码也可以，呵呵。其实这个方案说起来很简单，就是先做好一个EXCEL模版，然后用代码把对应的数据填进去，这样就可以很好的满足客户 的按格式导出的需求，而且还可以让客户来提供模版，这样效果更佳，下面就贴些关键代码：

  

[csharp] view plaincopy
  1.     ApplicationClass oExcel = null;  
  2.   
  3.       Workbook wb = null;  
  4.   
  5.       Sheets sheets = null;  
  6.   
  7.       _Worksheet worksheet = null;  
  8.   
  9.       Range range = null;  
  10.   
  11.   
  12.   
  13. 。。。  
  14.   
  15. public void showBookingData(DataSet ds)  
  16.   
  17.       {  
  18.   
  19.           showBrHeard(ds.Tables["BR_HEAD"]);  
  20.   
  21.           showReference(ds.Tables["BR_REFERENCE"]);  
  22.   
  23.           showBrConta(ds.Tables["BR_CONTAINER"]);  
  24.   
  25.           showBrCargo(ds.Tables["BR_CARGO"]);  
  26.   
  27.           showBrParties(ds.Tables["BR_PARTIES"]);  
  28.   
  29.           showBrLocation(ds.Tables["BR_LOCATION"]);  
  30.   
  31.           showBrDoor(ds.Tables["BR_DOOR"]);  
  32.   
  33.       }  
  34.  
  35.  
  36.  
  37.       #region 显示Booking数据  
  38.   
  39.       private void showBrHeard(System.Data.DataTable dt)  
  40.   
  41.       {  
  42.   
  43.           if (dt.Rows.Count > 0)  
  44.   
  45.           {  
  46.   
  47.               DataRow dRow = dt.Rows[0];  
  48.   
  49.               if (bkType != "temp")  
  50.   
  51.               {  
  52.   
  53.                   this.range = this.worksheet.get_Range("A2", Type.Missing);  
  54.   
  55.                   this.range.Value2 = "'"+dRow["CMC_BR_NUMBER"].ToString();  
  56.   
  57.               }  
  58.   
  59.               this.range = this.worksheet.get_Range("G2", Type.Missing);  
  60.   
  61.               this.range.Value2 = Utility.GetCarrier(dRow["SCAC_CODE"].ToString());  
  62.   
  63.               this.range = this.worksheet.get_Range("F11", Type.Missing);  
  64.   
  65.               String tfc = dRow["TRAFFIC_MODE"].ToString();  
  66.   
  67.               this.range.Value2 = Utility.GetTrafficById(Convert.ToDecimal(tfc));  
  68.   
  69.               String drm = dRow["PICKUP_DELIVERY"].ToString();  
  70.   
  71.               this.range = this.worksheet.get_Range("H11", Type.Missing);  
  72.   
  73.               this.range.Value2 = Utility.GetDragModeById(Convert.ToDecimal(drm)).ToUpper();  
  74.   
  75.   
  76.   
  77.               if (dRow["VESSEL_NAME"].ToString().Trim().Length != 0)  
  78.   
  79.               {  
  80.   
  81.                   this.range = this.worksheet.get_Range("A23", Type.Missing);  
  82.   
  83.                   this.range.Value2 = dRow["VESSEL_NAME"].ToString() + "/" + dRow["VESSEL_VOYAGE"].ToString();  
  84.   
  85.               }  
  86.   
  87.               else  
  88.   
  89.               {  
  90.   
  91.                   if (dRow["EST_DEPARTDATE"].ToString().Trim().Length != 0)  
  92.   
  93.                   {  
  94.   
  95.                       this.range = this.worksheet.get_Range("D23", Type.Missing);  
  96.   
  97.                       this.range.Value2 = Convert.ToDateTime(dRow["EST_DEPARTDATE"]).ToString("yyyy-MM-dd");  
  98.   
  99.                   }  
  100.   
  101.                   else if (dRow["EST_ARRIVDATE"].ToString().Trim().Length != 0)  
  102.   
  103.                   {  
  104.   
  105.                       this.range = this.worksheet.get_Range("C23", Type.Missing);  
  106.   
  107.                       this.range.Value2 = Convert.ToDateTime(dRow["EST_ARRIVDATE"]).ToString("yyyy-MM-dd");  
  108.   
  109.                   }  
  110.   
  111.               }  
  112.   
  113.               writeStrDate("A", 41, dRow["REMARK"].ToString() + "/n");  
  114.   
  115.               if (bkType != "temp")  
  116.   
  117.               {  
  118.   
  119.                   if (dRow["BR_NUMBER"] != null && dRow["BR_NUMBER"].ToString() != "")  
  120.   
  121.                   {  
  122.   
  123.                       strRN += "Booking No." + dRow["BR_NUMBER"].ToString() + "/n";  
  124.   
  125.                   }  
  126.   
  127.               }  
  128.   
  129.               if (dRow["RATE_REFNO"] != null && dRow["RATE_REFNO"].ToString() != "")  
  130.   
  131.               {  
  132.   
  133.                   strRN += "Contact No." + dRow["RATE_REFNO"].ToString() + "/n";  
  134.   
  135.               }  
  136.   
  137.           }  
  138.   
  139.       }  
  140.   
  141.   
  142.   
  143. 。。。。。。  

做法简单，但效果很好。唯一缺点就是速度比较慢，而且受限制比较多。

 

 

参考地址:http://www.cnblogs.com/kfcccer/archive/2007/05/20/753441.html

7.按XML定义格式把DATASET导出EXCEL    这个方法我没用过,是在CSDN上看到的,主要需求是说在DATESET导出为EXCEL时,需要对其中的数据进行定义,不然导出后与现有格式对不上,比 如时间的,导出后会和原来的数据有出入等等一类的问题.   我感觉其实和第五个比较相近,但因为最近比较忙.我也没时间要验证,如果谁用了希望可以反馈给我,谢谢了.下面是代码:

 private static void DataSetExportToExcel(DataSet source, string fileName)

    {

 

        System.IO.StreamWriter excelDoc;

 

        excelDoc = new System.IO.StreamWriter(fileName);

        const string startExcelXML = @"<xml version> <Workbook " +

              "xmlns=/"urn:schemas-microsoft-com:office:spreadsheet/" " +

              " xmlns:o=/"urn:schemas-microsoft-com:office:office/"  " +

              "xmlns:x=/"urn:schemas-    microsoft-com:office:" +

              "excel/"  xmlns:ss=/"urn:schemas-microsoft-com:" +

              "office:spreadsheet/">  <Styles>  " +

              "<Style ss:ID=/"Default/" ss:Name=/"Normal/">  " +

              "<Alignment ss:Vertical=/"Bottom/"/>  <Borders/>" +

              "  <Font/>  <Interior/>  <NumberFormat/>" +

              "  <Protection/>  </Style>  " +

              "<Style ss:ID=/"BoldColumn/">  <Font " +

              "x:Family=/"Swiss/" ss:Bold=/"1/"/>  </Style>  " +

              "<Style     ss:ID=/"StringLiteral/">  <NumberFormat" +

              " ss:Format=/"@/"/>  </Style>  <Style " +

              "ss:ID=/"Decimal/">  <NumberFormat " +

              "ss:Format=/"0.0000/"/>  </Style>  " +

              "<Style ss:ID=/"Integer/">  <NumberFormat " +

              "ss:Format=/"0/"/>  </Style>  <Style " +

              "ss:ID=/"DateLiteral/">  <NumberFormat " +

              "ss:Format=/"mm/dd/yyyy;@/"/>  </Style>  " +

              "</Styles>  ";

        const string endExcelXML = "</Workbook>";

 

        int rowCount = 0;

        int sheetCount = 1;

      

        excelDoc.Write(startExcelXML);

        excelDoc.Write("<Worksheet ss:Name=/"Sheet" + sheetCount + "/">");

        excelDoc.Write("<Table>");

        excelDoc.Write("<Row>");

        for (int x = 0; x < source.Tables[0].Columns.Count; x++)

        {

            excelDoc.Write("<Cell ss:StyleID=/"BoldColumn/"><Data ss:Type=/"String/">");

            excelDoc.Write(source.Tables[0].Columns[x].ColumnName);

            excelDoc.Write("</Data></Cell>");

        }

        excelDoc.Write("</Row>");

        foreach (DataRow x in source.Tables[0].Rows)

        {

            rowCount++;

            //if the number of rows is > 64000 create a new page to continue output

            if (rowCount == 64000)

            {

                rowCount = 0;

                sheetCount++;

                excelDoc.Write("</Table>");

                excelDoc.Write(" </Worksheet>");

                excelDoc.Write("<Worksheet ss:Name=/"Sheet" + sheetCount + "/">");

                excelDoc.Write("<Table>");

            }

            excelDoc.Write("<Row>"); //ID=" + rowCount + "

            for (int y = 0; y < source.Tables[0].Columns.Count; y++)

            {

                System.Type rowType;

                rowType = x[y].GetType();

                switch (rowType.ToString())

               {

                    case "System.String":

                        string XMLstring = x[y].ToString();

                        XMLstring = XMLstring.Trim();

                        XMLstring = XMLstring.Replace("&", "&");

                        XMLstring = XMLstring.Replace(">", ">");

                        XMLstring = XMLstring.Replace("<", "<");

                        excelDoc.Write("<Cell ss:StyleID=/"StringLiteral/">" +

                                       "<Data ss:Type=/"String/">");

                        excelDoc.Write(XMLstring);

                        excelDoc.Write("</Data></Cell>");

                        break;

                    case "System.DateTime":

                        //Excel has a specific Date Format of YYYY-MM-DD followed by 

                        //the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000

                        //The Following Code puts the date stored in XMLDate

                        //to the format above

                        DateTime XMLDate = (DateTime)x[y];

                        string XMLDatetoString = ""; //Excel Converted Date

                        XMLDatetoString = XMLDate.Year.ToString() +

                             "-" +

                             (XMLDate.Month < 10 ? "0" +

                             XMLDate.Month.ToString() : XMLDate.Month.ToString()) +

                             "-" +

                             (XMLDate.Day < 10 ? "0" +

                             XMLDate.Day.ToString() : XMLDate.Day.ToString()) +

                             "T" +

                             (XMLDate.Hour < 10 ? "0" +

                             XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +

                             ":" +

                             (XMLDate.Minute < 10 ? "0" +

                             XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) +

                             ":" +

                             (XMLDate.Second < 10 ? "0" +

                             XMLDate.Second.ToString() : XMLDate.Second.ToString()) +

                             ".000";

                        excelDoc.Write("<Cell ss:StyleID=/"DateLiteral/">" +

                                     "<Data ss:Type=/"DateTime/">");

                        excelDoc.Write(XMLDatetoString);

                        excelDoc.Write("</Data></Cell>");

                        break;

                    case "System.Boolean":

                        excelDoc.Write("<Cell ss:StyleID=/"StringLiteral/">" +

                                    "<Data ss:Type=/"String/">");

                        excelDoc.Write(x[y].ToString());

                        excelDoc.Write("</Data></Cell>");

                        break;

                    case "System.Int16":

                    case "System.Int32":

                    case "System.Int64":

                    case "System.Byte":

                        excelDoc.Write("<Cell ss:StyleID=/"Integer/">" +

                                "<Data ss:Type=/"Number/">");

                        excelDoc.Write(x[y].ToString());

                        excelDoc.Write("</Data></Cell>");

                        break;

                    case "System.Decimal":

                    case "System.Double":

                        excelDoc.Write("<Cell ss:StyleID=/"Decimal/">" +

                              "<Data ss:Type=/"Number/">");

                        excelDoc.Write(x[y].ToString());

                        excelDoc.Write("</Data></Cell>");

                        break;

                    case "System.DBNull":

                        excelDoc.Write("<Cell ss:StyleID=/"StringLiteral/">" +

                              "<Data ss:Type=/"String/">");

                        excelDoc.Write("");

                        excelDoc.Write("</Data></Cell>");

                        break;

                    default:

                        throw (new Exception(rowType.ToString() + " not handled."));

                }

            }

            excelDoc.Write("</Row>");

        }

        excelDoc.Write("</Table>");

        excelDoc.Write(" </Worksheet>");

        excelDoc.Write(endExcelXML);

        excelDoc.Close();

    }

 

原文转自：http://blog.csdn.net/simonllf/article/details/1441672

 

posted on 2014-02-17 10:04  御不凡  阅读(625)  评论(0)  编辑  收藏  举报

Copyright © 2024 御不凡
Powered by .NET 9.0 on Kubernetes Powered By博客园