DataSet,DataTable 不使用EXCEL组件直接保存为EXCEL

DataSet,DataTable 不使用EXCEL组件直接保存为EXCEL

 B/S模式下DataSet导出保存为EXCEL直接可以Respone出来，比较简单，而C/S模式下将DataSet导出到EXCEL相对比较麻烦，要使用EXCEL要引用Microsoft.Office.Interop.Excel，经常会出现一些EXCEL进程无法关闭的问题，很让人厌恶，查看了网上一些资料，其实EXCEL也是有他自己的固定的XML格式的
。具体格式如下，首先要先定义好它的头部分，显示出来的EXCEL 才不会错误。

1. public  string ExcelHeader()
2.         {
3.             System.Text.StringBuilder sb = new System.Text.StringBuilder();
4.             sb.Append("<?xml version=/"1.0/"?>/n");
5.             sb.Append("<?mso-application progid=/"Excel.Sheet/"?>/n");
6.             sb.Append(
7.               "<Workbook xmlns=/"urn:schemas-microsoft-com:office:spreadsheet/" ");
8.             sb.Append("xmlns:o=/"urn:schemas-microsoft-com:office:office/" ");
9.             sb.Append("xmlns:x=/"urn:schemas-microsoft-com:office:excel/" ");
10.             sb.Append("xmlns:ss=/"urn:schemas-microsoft-com:office:spreadsheet/" ");
11.             sb.Append("xmlns:html=/"http://www.w3.org/TR/REC-html40/">/n");
12.             sb.Append(
13.               "<DocumentProperties xmlns=/"urn:schemas-microsoft-com:office:office/">");
14.             sb.Append("</DocumentProperties>");
15.             sb.Append(
16.               "<ExcelWorkbook xmlns=/"urn:schemas-microsoft-com:office:excel/">/n");
17.             sb.Append("<ProtectStructure>False</ProtectStructure>/n");
18.             sb.Append("<ProtectWindows>False</ProtectWindows>/n");
19.             sb.Append("</ExcelWorkbook>/n");
20.             return sb.ToString();
21.         }

 

接下来就是具体的内容了，包括DATASET,DATATABLE，等数据源

 

1. public void exportToExcel(DataSet source, string fileName)
2.         {
3.             position = 0;
4.             System.IO.StreamWriter excelDoc;
6.             excelDoc = new System.IO.StreamWriter(fileName);
7.             string startExcelXML = ExcelHeader();
8.             startExcelXML += "<Styles>/r/n " +
9.                 "<Style ss:ID=/"Default/" ss:Name=/"Normal/">/r/n " +
10.                 "<Alignment ss:Vertical=/"Bottom/"/>/r/n <Borders/>" +
11.                 "/r/n <Font/>/r/n <Interior/>/r/n <NumberFormat/>" +
12.                 "/r/n <Protection/>/r/n </Style>/r/n " +
13.                 "<Style ss:ID=/"BoldColumn/">/r/n <Font " +
14.                 "x:Family=/"Swiss/" ss:Bold=/"1/"/>/r/n </Style>/r/n " +
15.                 "<Style ss:ID=/"StringLiteral/">/r/n <NumberFormat" +
16.                 " ss:Format=/"@/"/>/r/n </Style>/r/n <Style " +
17.                 "ss:ID=/"Decimal/">/r/n <NumberFormat " +
18.                 "ss:Format=/"0.0000/"/>/r/n </Style>/r/n " +
19.                 "<Style ss:ID=/"Integer/">/r/n <NumberFormat " +
20.                 "ss:Format=/"0/"/>/r/n </Style>/r/n <Style " +
21.                 "ss:ID=/"DateLiteral/">/r/n <NumberFormat " +
22.                 "ss:Format=/"mm/dd/yyyy;@/"/>/r/n </Style>/r/n " +
23.                 "</Styles>/r/n ";
24.             const string endExcelXML = "</Workbook>";
26.             int rowCount = 0;
27.             int sheetCount = 1;
28.           
29.             excelDoc.Write(startExcelXML);
30.             excelDoc.Write("<Worksheet ss:Name=/"Sheet" + sheetCount + "/">");
31.             excelDoc.Write("<Table>");
32.             excelDoc.Write("<Row>");
33.             for (int x = 0; x < source.Tables[0].Columns.Count; x++)
34.             {
35.                 excelDoc.Write("<Cell ss:StyleID=/"BoldColumn/"><Data ss:Type=/"String/">");
36.                 excelDoc.Write(source.Tables[0].Columns[x].ColumnName);
37.                 excelDoc.Write("</Data></Cell>");
38.             }
39.             excelDoc.Write("</Row>");
40.             foreach (DataRow x in source.Tables[0].Rows)
41.             {
42.                 rowCount++;
43. //Excel 最多只能显示64000条数据，在同一个WorkSheet里
44.                 if (rowCount == 64000)
45.                 {
46.                     rowCount = 0;
47.                     sheetCount++;
48.                     excelDoc.Write("</Table>");
49.                     excelDoc.Write(" </Worksheet>");
50.                     excelDoc.Write("<Worksheet ss:Name=/"Sheet" + sheetCount + "/">");
51.                     excelDoc.Write("<Table>");
52.                 }
53.                 excelDoc.Write("<Row>"); //ID=" + rowCount + "
54.                 for (int y = 0; y < source.Tables[0].Columns.Count; y++)
55.                 {
56.                     System.Type rowType;
57.                     rowType = x[y].GetType();
58.                     switch (rowType.ToString())
59.                     {
60.                         case "System.String":
61.                             string XMLstring = x[y].ToString();
62.                             XMLstring = XMLstring.Trim();
63.                             XMLstring = XMLstring.Replace("&", "&");
64.                             XMLstring = XMLstring.Replace(">", ">");
65.                             XMLstring = XMLstring.Replace("<", "<");
66.                             excelDoc.Write("<Cell ss:StyleID=/"StringLiteral/">" +
67.                                            "<Data ss:Type=/"String/">");
68.                             excelDoc.Write(XMLstring);
69.                             excelDoc.Write("</Data></Cell>");
70.                             break;
71.                         case "System.DateTime": 
73.                             DateTime XMLDate = (DateTime)x[y];
74.                             string XMLDatetoString = ""; //Excel Converted Date
75.                             XMLDatetoString = XMLDate.Year.ToString() +
76.                                  "-" +
77.                                  (XMLDate.Month < 10 ? "0" +
78.                                  XMLDate.Month.ToString() : XMLDate.Month.ToString()) +
79.                                  "-" +
80.                                  (XMLDate.Day < 10 ? "0" +
81.                                  XMLDate.Day.ToString() : XMLDate.Day.ToString()) +
82.                                  "T" +
83.                                  (XMLDate.Hour < 10 ? "0" +
84.                                  XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +
85.                                  ":" +
86.                                  (XMLDate.Minute < 10 ? "0" +
87.                                  XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) +
88.                                  ":" +
89.                                  (XMLDate.Second < 10 ? "0" +
90.                                  XMLDate.Second.ToString() : XMLDate.Second.ToString()) +
91.                                  ".000";
92.                             excelDoc.Write("<Cell ss:StyleID=/"DateLiteral/">" +
93.                                          "<Data ss:Type=/"DateTime/">");
94.                             excelDoc.Write(XMLDatetoString);
95.                             excelDoc.Write("</Data></Cell>");
96.                             break;
97.                         case "System.Boolean":
98.                             excelDoc.Write("<Cell ss:StyleID=/"StringLiteral/">" +
99.                                         "<Data ss:Type=/"String/">");
100.                             excelDoc.Write(x[y].ToString());
101.                             excelDoc.Write("</Data></Cell>");
102.                             break;
103.                         case "System.Int16":
104.                         case "System.Int32":
105.                         case "System.Int64":
106.                         case "System.Byte":
107.                             excelDoc.Write("<Cell ss:StyleID=/"Integer/">" +
108.                                     "<Data ss:Type=/"Number/">");
109.                             excelDoc.Write(x[y].ToString());
110.                             excelDoc.Write("</Data></Cell>");
111.                             break;
112.                         case "System.Decimal":
113.                         case "System.Double":
114.                             excelDoc.Write("<Cell ss:StyleID=/"Decimal/">" +
115.                                   "<Data ss:Type=/"Number/">");
116.                             excelDoc.Write(x[y].ToString());
117.                             excelDoc.Write("</Data></Cell>");
118.                             break;
119.                         case "System.DBNull":
120.                             excelDoc.Write("<Cell ss:StyleID=/"StringLiteral/">" +
121.                                   "<Data ss:Type=/"String/">");
122.                             excelDoc.Write("");
123.                             excelDoc.Write("</Data></Cell>");
124.                             break;
125.                         default:
126.                             throw (new Exception(rowType.ToString() + " not handled."));
127.                     }
128.                     position = y;
129.                     ProgressEventArgs pe = new ProgressEventArgs(position);
130.                     OnProgressChange(pe);
131.                 }
132.                 excelDoc.Write("</Row>");
133.             }
134.             excelDoc.Write("</Table>");
135.             excelDoc.Write(" </Worksheet>");
136.             excelDoc.Write(endExcelXML);
137.             excelDoc.Close();
138.         }
139. }