关于内存表导EXCEL的方法［转］

Code
  1最近在做一个报表系统的时候，需要把DATASET中的数据导到EXCEL当中，于是在网上找了一遍，发现了好几种方法，本来以为应该差不多，但后来经过一一试用后，发现在性能上真的差别很大，现在就介绍一下，同时有不对的希望可以指正：
  2 
  31． 原理：利用office组件把dataset中的数据填充到excel文件当中。
  4这里我不贴出全部代码了，只把关键部分贴出来：
  5         /**////<summary>
  6         ///方法，导出C1TrueDBGrid中的数据到Excel文件
  7         ///</summary>
  8         ///<param name="c1grid">C1TrueDBGrid</param>
  9         ///<param name="FileName">Excel文件名</param>
 10         public void ExportToExcel(C1.Win.C1TrueDBGrid.C1TrueDBGrid c1grid,string FileName)
 11         {
 12              if(FileName.Trim() == "") return;            //验证strFileName是否为空或值无效
 13 
 14              int rowscount = c1grid.Splits[0].Rows.Count; //定义表格内数据的行数
 15              int colscount = c1grid.Columns.Count;        //定义表格内数据的列数
 16 
 17              //行数必须大于0
 18              if (rowscount <= 0)
 19              {
 20                   MessageBox.Show("没有数据可供保存","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
 21                   return;
 22              }
 23 
 24              //列数必须大于0
 25              if (colscount <= 0)
 26              {
 27                   MessageBox.Show("没有数据可供保存","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
 28                   return;
 29              }
 30 
 31              //行数不可以大于65536
 32              if (rowscount > 65536)
 33              {
 34                   MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
 35                   return;
 36              }
 37 
 38              //列数不可以大于255
 39              if (colscount > 255)
 40              {
 41                   MessageBox.Show("数据记录行数太多，不能保存","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
 42                   return;
 43              }
 44         
 45              //将文件保存到工作路径的子目录“\Excel”下，如果路径不存在创建它
 46              string n_path = Directory.GetCurrentDirectory() + "\\Excel";
 47              if (Directory.Exists(n_path) == false)
 48              {
 49                   Directory.CreateDirectory(n_path);
 50              }
 51 
 52              //验证以strFileName命名的文件是否存在，如果存在删除它
 53              FileInfo fi = new FileInfo(n_path + "\\" + FileName + ".xls");
 54              if(fi.Exists)
 55              {
 56                   try 
 57                   {
 58                       fi.Delete();
 59                   } 
 60                   catch(Exception fie) 
 61                   {
 62                       MessageBox.Show(fie.Message,"删除失败", MessageBoxButtons.OK, MessageBoxIcon.Warning);
 63                       return;
 64                   }
 65              }
 66         
 67              Excel.ApplicationClass excel = null;
 68 
 69              try
 70              {
 71                   //显示进度条
 72                   KP.PublicForm.ProgressBar pb = new PublicForm.ProgressBar("导出进度");
 73                   pb.Show();
 74                   pb.Refresh();
 75 
 76                  //新建Excel应用，新建Workbook文件
 77                   excel = new Excel.ApplicationClass ( ) ;
 78                   Excel.XlSaveAsAccessMode savemode = new Excel.XlSaveAsAccessMode();
 79                   excel.Application.Workbooks.Add (true) ;
 80 
 81                   //向Excel中写入表格的表头
 82                   int i = 1;
 83                   for(int c = 0;c < colscount; c++)
 84                   {
 85                       if(c1grid.Splits[0].DisplayColumns[c].Visible)
 86                       {
 87                            excel.Cells[1,i] = c1grid.Columns[c].Caption;
 88                            i++;
 89                       }
 90                   }
 91         
 92                   //向Excel中逐行逐列写入表格中的数据
 93                   for(int r = 0; r < rowscount; r++)
 94                   {
 95                       Application.DoEvents();
 96                       pb.SetProgressBarValue(r+1, rowscount);
 97                       if(pb.Cancel)
 98                       {
 99                            break;
100                       }
101 
102                       int j = 1;
103                       for(int c = 0;c < colscount; c++)
104                       {
105                            if(c1grid.Splits[0].DisplayColumns[c].Visible)
106                            {
107                                 excel.Cells[r + 2,j] = c1grid.Columns[c].CellText(r);
108                                 j++;
109                            }
110                       }
111                   }
112 
113                   //向Excel中写入表格的脚
114                   if(c1grid.ColumnFooters)
115                   {
116                       int col = 1;
117                       for(int c = 0;c < colscount; c++)
118                       {
119                            if(c1grid.Splits[0].DisplayColumns[c].Visible)
120                            {
121                                 if(c1grid.Columns[c].FooterText != null && c1grid.Columns[c].FooterText.Trim() != "")
122                                 {
123                                     excel.Cells[rowscount + 2,col] = c1grid.Columns[c].FooterText;
124                                 }
125                                 col++;
126                            }
127                       }
128                   }
129 
130                   //关闭进度条
131                   pb.Close();
132 
133                   //设置Excel的默认保存路径为当前路径下的Excel子文件夹
134                   excel.DefaultFilePath = n_path;
135 
136                   //保存文件
137                   excel.ActiveWorkbook.SaveAs(FileName + ".xls",excel.ActiveWorkbook.FileFormat,"","",excel.ActiveWorkbook.ReadOnlyRecommended,excel.ActiveWorkbook.CreateBackup,savemode,excel.ActiveWorkbook.ConflictResolution,false,"","");
138         
139              }
140              catch(Exception e1)
141              {
142                   MessageBox.Show(e1.Message, "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
143                   return;
144              }
145              finally
146              {
147                   //关闭Excel应用
148                   if(excel != null) excel.Quit();
149              }
150              MessageBox.Show(FileName + "导出完毕，在" + Application.StartupPath + "\\Excel文件夹下","提示", MessageBoxButtons.OK,MessageBoxIcon.Information);
151//       }
152 
153}
154 
155       总结：这个方法是可以解决问题，但效率最差，3000条长点的record就用了6分钟，晕~~~~
156 
1572．   原理：利用office组件，同时把dataset的数据导到Clipboard中，然后通过粘贴到excel中。
158         Excel.XlSaveAsAccessMode savemode = new Excel.XlSaveAsAccessMode();
159                   xlApp.Application.Workbooks.Add (true) ;
160                   xlApp.DefaultFilePath = @"c:\";
161                   xlApp.ActiveWorkbook.SaveAs("exportExcel.xls",xlApp.ActiveWorkbook.FileFormat,"","",xlApp.ActiveWorkbook.ReadOnlyRecommended,xlApp.ActiveWorkbook.CreateBackup,savemode,xlApp.ActiveWorkbook.ConflictResolution,false,"","","");
162         
163              
164 
165 
166                   Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath, oMissing, oMissing, oMissing, oMissing, oMissing, 
167                       oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
168                       oMissing,oMissing,oMissing);
169                   
170                   Excel.Worksheet xlWorksheet;
171 
172       
173                   // 循环所有DataTable
174                   for( int i=0; i<ds.Tables.Count; i++ )
175                   {
176                       xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.Add(oMissing,oMissing,1,oMissing);
177                       // 以TableName作为新加的Sheet页名。
178                       xlWorksheet.Name = ds.Tables[i].TableName;
179                       // 取出这个DataTable中的所有值,暂存于stringBuffer中。
180                       string stringBuffer = "";
181                       //向Excel中写入表格的表头
182                       if(node != null)
183                       {
184                            XmlNode nodec=node.SelectSingleNode("./Method/ShowField");
185                            int ii = 1;
186                            foreach(XmlNode xnode in nodec.ChildNodes )
187                            {
188                                 xlApp.Cells[1,ii] =xnode.Attributes["displayname"].Value;                                
189                                 ii++;
190                            }
191                       
192                            
193                            for( int j=0; j<ds.Tables[i].Rows.Count; j++ )
194                            {
195                                 for( int k=0; k<ds.Tables[i].Columns.Count; k++ )
196                                 {
197              
198                                     stringBuffer += ds.Tables[i].Rows[j][k].ToString();
199                                     if( k < ds.Tables[i].Columns.Count - 1 )
200                                          stringBuffer += "\t";
201                                 }
202                                 stringBuffer += "\n";
203                            }
204                            
205                       }
206                       else
207                       {
208                            int ii = 1;
209                            for(int c = 0;c<ds.Tables[i].Columns.Count; c++)
210                            {
211                                 xlApp.Cells[1,ii] = ds.Tables[i].Columns[c].Caption;
212                                 ii++;
213                            }
214                            
215                            for( int j=0; j<ds.Tables[i].Rows.Count; j++ )
216                            {
217                                 for( int k=0; k<ds.Tables[i].Columns.Count; k++ )
218                                 {
219              
220                                     stringBuffer += ds.Tables[i].Rows[j][k].ToString();
221                                     if( k < ds.Tables[i].Columns.Count - 1 )
222                                          stringBuffer += "\t";
223                                 }
224                                 stringBuffer += "\n";
225                            }
226                       }
227 
228                       System.Windows.Forms.Clipboard.SetDataObject("");
229                       // 将stringBuffer放入剪切板。
230                       System.Windows.Forms.Clipboard.SetDataObject(stringBuffer);
231                       // 选中这个sheet页中的第一个单元格
232                       ((Excel.Range)xlWorksheet.Cells[2,1]).Select();
233                       // 粘贴！
234                       xlWorksheet.Paste(oMissing,oMissing);
235                       // 清空系统剪切板。
236                       System.Windows.Forms.Clipboard.SetDataObject("");
237                   
238 
239 
240                   
241                   }
242                   // 保存并关闭这个工作簿。
243                   
244              
245 
246              
247                            
248                   xlApp.ActiveWorkbook.Close( Excel.XlSaveAction.xlSaveChanges, oMissing, oMissing );
249                   //                 xlWorkbook.Close( Excel.XlSaveAction.xlSaveChanges, oMissing, oMissing );
250                   System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);
251                   xlWorkbook = null;
252                   MessageBox.Show(@"Excel文件：C:\exportExcel.xls 导出成功！");
253              }
254              catch(Exception ex)
255              {
256                   MessageBox.Show(ex.Message);
257              }
258              finally
259              {
260                   // 释放
261                   xlApp.Quit();
262                   System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
263                   xlApp = null;
264                   GC.Collect();
265          }
266 
267       总结：这个方法比上面的方法性能好点，但还是很不好用，比原来的提高了2倍左右。
268 
2693． 原理：利用OLEDB，以excel为数据库，把dataset中的数据导入到excel文件中
270       public static void exportToExcelByDataset(string filePath, DataSet ds,XmlNode node)
271  {
272   string sqlstr;
273
274
275   if(fi.Exists)
276   {
277    fi.Delete();
278    //     throw new Exception("文件删除失败");
279   }
280   else
281   {
282    fi.Create();
283   }
284   
285   string sqlcon=@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended ProPerties=Excel 8.0;";
286   OleDbConnection lecon = new OleDbConnection(sqlcon);
287   OleDbCommand lecmd = new OleDbCommand();
288   olecmd.Connection = olecon;
289   olecmd.CommandType = CommandType.Text;
290 
291
292   try
293   {
294    olecon.Open();
295            
296    XmlNode nodec=node.SelectSingleNode("./Method/ShowField");
297    int ii = 0;
298    sqlstr = "CREATE TABLE sheet1(";
299    foreach(XmlNode xnode in nodec.ChildNodes )
300    {
301     if(ii == nodec.ChildNodes.Count - 1)
302     {
303      if(xnode.Attributes["type"].Value.ToLower() == "int"||xnode.Attributes["type"].Value.ToLower() == "decimal")
304      {
305       sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " number)";       
306      }
307      else
308      {
309       sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " text)";
310      }
311      //      sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " text)";
312     }
313     else
314     {
315      if(xnode.Attributes["type"].Value.ToLower() == "int"||xnode.Attributes["type"].Value.ToLower() == "decimal")
316      {
317       sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " number,";       
318      }
319      else
320      {
321       sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " text,";
322      }
323       
324     }
325     //     sqlstr =sqlstr + xnode.Attributes["displayname"].Value + " text";       
326     ii++;
327    }
328    olecmd.CommandText = sqlstr;
329    olecmd.ExecuteNonQuery();
330    for(int i=0;i<ds.Tables[0].Rows.Count;i++)
331    {
332     sqlstr = "INSERT INTO sheet1 VALUES(";
333     int jj=0;
334     foreach(XmlNode inode in nodec.ChildNodes )
335     {
336      if(jj == nodec.ChildNodes.Count-1)
337      {
338       if(inode.Attributes["type"].Value.ToLower() == "int"||inode.Attributes["type"].Value.ToLower() == "decimal")
339       {
340        sqlstr = sqlstr + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString()) + ")" ;
341        
342       }
343       else
344       {
345        sqlstr = sqlstr + "'" + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString().Replace("'","''")) + "')" ;
346       }
347      }
348      else
349      {
350       if(inode.Attributes["type"].Value.ToLower() == "int"||inode.Attributes["type"].Value.ToLower() == "decimal")
351       {
352        sqlstr = sqlstr + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString()) + "," ;
353        
354       }
355       else
356       {
357        sqlstr = sqlstr + "'" + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString().Replace("'","''")) + "'," ;
358       }
359      }
360      jj++;
361     }
362     olecmd.CommandText = sqlstr;
363     olecmd.ExecuteNonQuery();
364     
365    }  
366    MessageBox.Show(@"Excel文件：" + filePath + " 导出成功！");
367   }
368   catch(Exception ex)
369   {
370    MessageBox.Show(ex.Message);
371   }
372   finally
373   {
374    olecmd.Dispose();
375    olecon.Close();
376    olecon.Dispose();
377                   
378   }
379  }
380/**//// <summary>
381/// change to string "null" if input is null
382/// </summary>
383/// <param name="obj"></param>
384/// <returns></returns>
385 
386  private static string isnull(string obj)
387  {
388   if(obj.Length >0)
389   {
390    return obj;
391   }
392   else
393   {
394    return "null";
395   }
396  }
397       总结：这个方法是最好的，速度飞快，比上面两种提高不止10倍，而且关键是不需要用到office组件，所以我正在用着这种方法，客户也满意。当然这个也有它不好的地方，有时候会受到导入的数据不符的异常困扰，而且为了赶时间，代码写的不好，一句话，能用但要改进的地方很多：）
398       
399
400 /**////2007-03-02
401
402最近发现几个导出到EXCEL的方法,这里先记录下来
403
4044.本示例是用于将ListView中的内容倒入到Excel 与常用的逐单元格写不同的是，本例子采用数据写入到range的方法。该方法效率明显较高 
405Excel.Application app = new Excel.ApplicationClass();    
406if( app == null)    
407{
408     MessageBox.Show("Excel无法启动");
409     return;    
410}
411    app.Visible = true;
412    Excel.Workbooks wbs = app.Workbooks;
413    Excel.Workbook wb = wbs.Add(Missing.Value);
414    Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];
415    Excel.Range r = ws.get_Range("A1","H1");
416    object [] bjHeader = {"标题1","标题2","标题3","标题4","标题5","标题6","标题7","标题8"};
417    r.Value = objHeader;
418    if (lv.Items.Count >0)
419    {
420     r = ws.get_Range("A2",Missing.Value);
421       object [,] bjData = new Object[this.lv.Items.Count,8];
422     foreach(ListViewItem lvi in lv.Items)
423     {
424      objData[lvi.Index,0] = lvi.Text;
425      objData[lvi.Index,1] = lvi.SubItems[1].Text;
426      objData[lvi.Index,2] = lvi.SubItems[2].Text;
427      objData[lvi.Index,3] = lvi.SubItems[3].Text;
428      objData[lvi.Index,4] = lvi.SubItems[4].Text;
429      objData[lvi.Index,5] = lvi.SubItems[5].Text;
430      objData[lvi.Index,6] = lvi.SubItems[6].Text;
431      objData[lvi.Index,7] = lvi.SubItems[7].Text;
432     }
433     r = r.get_Resize(lv.Items.Count,8);
434     r.Value = objData;
435     r.EntireColumn.AutoFit();
436    }
437    app = null;
438
4395.由XML文件导出为EXCEL文件
440
441目录下kfcccer.xml为原始数据XML文件，点击生成后会在同级目录下生成kfcccer.xls文件 
442
443页面代码如下:
444<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>
445
446<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
447
448<html xmlns="http://www.w3.org/1999/xhtml" >
449<head runat="server">
450    <title>XML转换Excel演示</title>
451</head>
452<body>
453    <form. id="form1" runat="server">
454    <div>
455        <div>
456            <asp:Button ID="btnChange" runat="server" Font-Bold="True" Font-Size="18pt" ForeColor="Black"
457                Height="38px" nClick="btnChange_Click" Text="开始转换" Width="203px" /></div>
458    
459    </div>
460    </form>
461</body>
462</html>
463
464后台代码:
465using System;
466using System.Data;
467using System.Configuration;
468using System.Collections;
469using System.Web;
470using System.Web.Security;
471using System.Web.UI;
472using System.Web.UI.WebControls;
473using System.Web.UI.WebControls.WebParts;
474using System.Web.UI.HtmlControls;
475using System.IO;
476
477public partial class _Default : System.Web.UI.Page 
478{
479    protected void Page_Load(object sender, EventArgs e)
480    {
481
482    }
483    protected void btnChange_Click(object sender, EventArgs e)
484    {
485        try
486        {
487            //要转换的XML文件
488            string XMLFileName = Path.Combine(Request.PhysicalApplicationPath, "kfcccer.xml");
489            DataSet dsBook = new DataSet();
490            dsBook.ReadXml(XMLFileName);
491            int rows = dsBook.Tables[0].Rows.Count + 1;
492            int cols = dsBook.Tables[0].Columns.Count;
493
494            //将要生成的Excel文件
495            string ExcelFileName = Path.Combine(Request.PhysicalApplicationPath, "kfcccer.xls");
496            if (File.Exists(ExcelFileName))
497            {
498                File.Delete(ExcelFileName);
499            }
500            StreamWriter writer = new StreamWriter(ExcelFileName, false);
501            writer.WriteLine("<?xml version="1.0"?>");
502            writer.WriteLine("<?mso-application progid="Excel.Sheet"?>");
503            writer.WriteLine("<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"");
504            writer.WriteLine(" xmlns:o="urn:schemas-microsoft-com:office:office"");
505            writer.WriteLine(" xmlns:x="urn:schemas-microsoft-com:office:excel"");
506            writer.WriteLine(" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"");
507            writer.WriteLine(" xmlns:html="http://www.w3.org/TR/REC-html40/">");
508            writer.WriteLine(" <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">");
509            writer.WriteLine("  <Author>Automated Report Generator Example</Author>");
510            writer.WriteLine(string.Format("  <Created>{0}T{1}Z</Created>", DateTime.Now.ToString("yyyy-mm-dd"), DateTime.Now.ToString("HH:MM:SS")));
511            writer.WriteLine("  <Company>51aspx.com</Company>");
512            writer.WriteLine("  <Version>11.6408</Version>");
513            writer.WriteLine(" </DocumentProperties>");
514            writer.WriteLine(" <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">");
515            writer.WriteLine("  <WindowHeight>8955</WindowHeight>");
516            writer.WriteLine("  <WindowWidth>11355</WindowWidth>");
517            writer.WriteLine("  <WindowTopX>480</WindowTopX>");
518            writer.WriteLine("  <WindowTopY>15</WindowTopY>");
519            writer.WriteLine("  <ProtectStructure>False</ProtectStructure>");
520            writer.WriteLine("  <ProtectWindows>False</ProtectWindows>");
521            writer.WriteLine(" </ExcelWorkbook>");
522            writer.WriteLine(" <Styles>");
523            writer.WriteLine("  <Style. ss:ID="Default" ss:Name="Normal">");
524            writer.WriteLine("   <Alignment ss:Vertical="Bottom"/>");
525            writer.WriteLine("   <Borders/>");
526            writer.WriteLine("   <Font/>");
527            writer.WriteLine("   <Interior/>");
528            writer.WriteLine("   <Protection/>");
529            writer.WriteLine("  </Style>");
530            writer.WriteLine("  <Style. ss:ID="s21">");
531            writer.WriteLine("   <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>");
532            writer.WriteLine("  </Style>");
533            writer.WriteLine(" </Styles>");
534            writer.WriteLine(" <Worksheet ss:Name="MyReport">");
535            writer.WriteLine(string.Format("  <Table ss:ExpandedColumnCount="{0}" ss:ExpandedRowCount="{1}" x:FullColumns="1"", cols.ToString(), rows.ToString()));
536            writer.WriteLine("   x:FullRows="1">");
537
538            //生成标题
539            writer.WriteLine("<Row>");
540            foreach (DataColumn eachCloumn in dsBook.Tables[0].Columns)
541            {
542                writer.Write("<Cell ss:StyleID="s21"><Data ss:Type="String">");
543                writer.Write(eachCloumn.ColumnName.ToString());
544                writer.WriteLine("</Data></Cell>");
545            }
546            writer.WriteLine("</Row>");
547
548            //生成数据记录
549            foreach (DataRow eachRow in dsBook.Tables[0].Rows)
550            {
551                writer.WriteLine("<Row>");
552                for (int currentRow = 0; currentRow != cols; currentRow++)
553                {
554                    writer.Write("<Cell ss:StyleID="s21"><Data ss:Type="String">");
555                    writer.Write(eachRow[currentRow].ToString());
556                    writer.WriteLine("</Data></Cell>");
557                }
558                writer.WriteLine("</Row>");
559            }
560            writer.WriteLine("  </Table>");
561            writer.WriteLine("  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">");
562            writer.WriteLine("   <Selected/>");
563            writer.WriteLine("   <Panes>");
564            writer.WriteLine("    <Pane>");
565            writer.WriteLine("     <Number>3</Number>");
566            writer.WriteLine("     <ActiveRow>1</ActiveRow>");
567            writer.WriteLine("    </Pane>");
568            writer.WriteLine("   </Panes>");
569            writer.WriteLine("   <ProtectObjects>False</ProtectObjects>");
570            writer.WriteLine("   <ProtectScenarios>False</ProtectScenarios>");
571            writer.WriteLine("  </WorksheetOptions>");
572            writer.WriteLine(" </Worksheet>");
573            writer.WriteLine(" <Worksheet ss:Name="Sheet2">");
574            writer.WriteLine("  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">");
575            writer.WriteLine("   <ProtectObjects>False</ProtectObjects>");
576            writer.WriteLine("   <ProtectScenarios>False</ProtectScenarios>");
577            writer.WriteLine("  </WorksheetOptions>");
578            writer.WriteLine(" </Worksheet>");
579            writer.WriteLine(" <Worksheet ss:Name="Sheet3">");
580            writer.WriteLine("  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">");
581            writer.WriteLine("   <ProtectObjects>False</ProtectObjects>");
582            writer.WriteLine("   <ProtectScenarios>False</ProtectScenarios>");
583            writer.WriteLine("  </WorksheetOptions>");
584            writer.WriteLine(" </Worksheet>");
585            writer.WriteLine("</Workbook>");
586            writer.Close();
587            Response.Write("<script. language="javascript">" + "alert('" + "转换成功! 转换后的Excel文件名为: kfcccer.xls')" + "</script>");
588        }
589        catch (Exception ex)
590        {
591            Response.Write("<script. language="javascript">" + "alert('" + "操作失败! 出错信息: " + ex.Message + "')" + "</script>");
592        }
593    }
594} 
595
596通过XML文件来实现的导入EXCEL的方法：
597
598//fileName是要保存excel文件和文件名的路径 
599
600 
601
602public static void exportToExcel(DataSet source, string fileName)
603
604{
605
606    System.IO.StreamWriter excelDoc;
607
608    excelDoc = new System.IO.StreamWriter(fileName);
609    const string startExcelXML = "<xml version>\r\n<Workbook " + 
610          "xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n" + 
611          " xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n " + 
612          "xmlns:x=\"urn:schemas-    microsoft-com:office:" + 
613          "excel\"\r\n xmlns:ss=\"urn:schemas-microsoft-com:" + 
614          "office:spreadsheet\">\r\n <Styles>\r\n " + 
615          "<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n " + 
616          "<Alignment ss:Vertical=\"Bottom\"/>\r\n <Borders/>" + 
617          "\r\n <Font/>\r\n <Interior/>\r\n <NumberFormat/>" + 
618          "\r\n <Protection/>\r\n </Style>\r\n " + 
619          "<Style ss:ID=\"BoldColumn\">\r\n <Font " + 
620          "x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n " + 
621          "<Style     ss:ID=\"StringLiteral\">\r\n <NumberFormat" + 
622          " ss:Format=\"@\"/>\r\n </Style>\r\n <Style " + 
623          "ss:ID=\"Decimal\">\r\n <NumberFormat " + 
624          "ss:Format=\"0.0000\"/>\r\n </Style>\r\n " + 
625          "<Style ss:ID=\"Integer\">\r\n <NumberFormat " + 
626          "ss:Format=\"0\"/>\r\n </Style>\r\n <Style " + 
627          "ss:ID=\"DateLiteral\">\r\n <NumberFormat " + 
628          "ss:Format=\"mm/dd/yyyy;@\"/>\r\n </Style>\r\n " + 
629          "</Styles>\r\n ";
630     const string endExcelXML = "</Workbook>";
631
632     int rowCount = 0;
633     int sheetCount = 1;
634     /**//*
635    <xml version>
636    <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
637    xmlns:o="urn:schemas-microsoft-com:office:office"
638    xmlns:x="urn:schemas-microsoft-com:office:excel"
639    xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
640    <Styles>
641    <Style ss:ID="Default" ss:Name="Normal">
642      <Alignment ss:Vertical="Bottom"/>
643      <Borders/>
644      <Font/>
645      <Interior/>
646      <NumberFormat/>
647      <Protection/>
648    </Style>
649    <Style ss:ID="BoldColumn">
650      <Font x:Family="Swiss" ss:Bold="1"/>
651    </Style>
652    <Style ss:ID="StringLiteral">
653      <NumberFormat ss:Format="@"/>
654    </Style>
655    <Style ss:ID="Decimal">
656      <NumberFormat ss:Format="0.0000"/>
657    </Style>
658    <Style ss:ID="Integer">
659      <NumberFormat ss:Format="0"/>
660    </Style>
661    <Style ss:ID="DateLiteral">
662      <NumberFormat ss:Format="mm/dd/yyyy;@"/>
663    </Style>
664    </Styles>
665    <Worksheet ss:Name="Sheet1">
666    </Worksheet>
667    </Workbook>
668    */
669    excelDoc.Write(startExcelXML);
670    excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
671    excelDoc.Write("<Table>");
672    excelDoc.Write("<Row>");
673    for(int x = 0; x < source.Tables[0].Columns.Count; x++)
674    {
675      excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");
676      excelDoc.Write(source.Tables[0].Columns[x].ColumnName);
677      excelDoc.Write("</Data></Cell>");
678    }
679    excelDoc.Write("</Row>");
680    foreach(DataRow x in source.Tables[0].Rows)
681    {
682      rowCount++;
683      //if the number of rows is > 64000 create a new page to continue output
684      if(rowCount==64000) 
685      {
686        rowCount = 0;
687        sheetCount++;
688        excelDoc.Write("</Table>");
689        excelDoc.Write(" </Worksheet>");
690        excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
691        excelDoc.Write("<Table>");
692      }
693      excelDoc.Write("<Row>"); //ID=" + rowCount + "
694      for(int y = 0; y < source.Tables[0].Columns.Count; y++)
695      {
696        System.Type rowType;
697        rowType = x[y].GetType();
698        switch(rowType.ToString())
699        {
700          case "System.String":
701             string XMLstring = x[y].ToString();
702             XMLstring = XMLstring.Trim();
703             XMLstring = XMLstring.Replace("&","&");
704             XMLstring = XMLstring.Replace(">",">");
705             XMLstring = XMLstring.Replace("<","<");
706             excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" + 
707                            "<Data ss:Type=\"String\">");
708             excelDoc.Write(XMLstring);
709             excelDoc.Write("</Data></Cell>");
710             break;
711           case "System.DateTime":
712             //Excel has a specific Date Format of YYYY-MM-DD followed by  
713             //the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000
714             //The Following Code puts the date stored in XMLDate 
715             //to the format above
716             DateTime XMLDate = (DateTime)x[y];
717             string XMLDatetoString = ""; //Excel Converted Date
718             XMLDatetoString = XMLDate.Year.ToString() +
719                  "-" + 
720                  (XMLDate.Month < 10 ? "0" + 
721                  XMLDate.Month.ToString() : XMLDate.Month.ToString()) +
722                  "-" +
723                  (XMLDate.Day < 10 ? "0" + 
724                  XMLDate.Day.ToString() : XMLDate.Day.ToString()) +
725                  "T" +
726                  (XMLDate.Hour < 10 ? "0" + 
727                  XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +
728                  ":" +
729                  (XMLDate.Minute < 10 ? "0" + 
730                  XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) +
731                  ":" +
732                  (XMLDate.Second < 10 ? "0" + 
733                  XMLDate.Second.ToString() : XMLDate.Second.ToString()) + 
734                  ".000";
735                excelDoc.Write("<Cell ss:StyleID=\"DateLiteral\">" + 
736                             "<Data ss:Type=\"DateTime\">");
737                excelDoc.Write(XMLDatetoString);
738                excelDoc.Write("</Data></Cell>");
739                break;
740              case "System.Boolean":
741                excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" + 
742                            "<Data ss:Type=\"String\">");
743                excelDoc.Write(x[y].ToString());
744                excelDoc.Write("</Data></Cell>");
745                break;
746              case "System.Int16":
747              case "System.Int32":
748              case "System.Int64":
749              case "System.Byte":
750                excelDoc.Write("<Cell ss:StyleID=\"Integer\">" + 
751                        "<Data ss:Type=\"Number\">");
752                excelDoc.Write(x[y].ToString());
753                excelDoc.Write("</Data></Cell>");
754                break;
755              case "System.Decimal":
756              case "System.Double":
757                excelDoc.Write("<Cell ss:StyleID=\"Decimal\">" + 
758                      "<Data ss:Type=\"Number\">");
759                excelDoc.Write(x[y].ToString());
760                excelDoc.Write("</Data></Cell>");
761                break;
762              case "System.DBNull":
763                excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" + 
764                      "<Data ss:Type=\"String\">");
765                excelDoc.Write("");
766                excelDoc.Write("</Data></Cell>");
767                break;
768              default:
769                throw(new Exception(rowType.ToString() + " not handled."));
770            }
771          }
772          excelDoc.Write("</Row>");
773        }
774        excelDoc.Write("</Table>");
775        excelDoc.Write(" </Worksheet>");
776        excelDoc.Write(endExcelXML);
777        excelDoc.Close();
778    }
779
780
781
782

转载于:https://www.cnblogs.com/winnxm/archive/2008/09/21/1295218.html