实体数据集生成excel文件（多Sheet）

1 实体转换成字符串

 1 private StringBuilder ConvertEntity()
 2         {
 3             StringBuilder sb = new StringBuilder();
 4 
 5             const string startExcelXML = "<?xml version=\"1.0\"?>\r\n<Workbook " +
 6                   "xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n" +
 7                   " xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n " +
 8                   "xmlns:x=\"urn:schemas-    microsoft-com:office:" +
 9                   "excel\"\r\n xmlns:ss=\"urn:schemas-microsoft-com:" +
10                   "office:spreadsheet\">\r\n <Styles>\r\n " +
11                   "<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n " +
12                   "<Alignment ss:Vertical=\"Center\"/>\r\n <Borders/>" +
13                   "\r\n <Font ss:Size=\"12\"/>\r\n <Interior/>\r\n <NumberFormat/>" +
14                   "\r\n <Protection/>\r\n </Style>\r\n " +
15                   "<Style ss:ID=\"BoldColumn\">\r\n <Font " +
16                   "x:Family=\"Swiss\" ss:Bold=\"1\" ss:Size=\"12\"/>\r\n </Style>\r\n " +
17                   "<Style ss:ID=\"StringLiteral\">\r\n <NumberFormat" +
18                   " ss:Format=\"@\"/>\r\n </Style>\r\n <Style " +
19                   "ss:ID=\"Decimal\">\r\n <NumberFormat " +
20                   "ss:Format=\"0.00\"/>\r\n </Style>\r\n " +
21                   "<Style ss:ID=\"Integer\">\r\n <NumberFormat " +
22                   "ss:Format=\"0\"/>\r\n </Style>\r\n <Style " +
23                   "ss:ID=\"DateLiteral\">\r\n <NumberFormat " +
24                   "ss:Format=\"Short Date\"/>\r\n </Style>\r\n <Style " +
25                   "ss:ID=\"Linker\">\r\n <Font x:Family='Swiss' ss:Size=\"12\" ss:Color='#0000FF' ss:Underline='Single'/>\r\n <NumberFormat />\r\n </Style>" +
26                   "</Styles>\r\n ";
27             const string endExcelXML = "</Workbook>";
28 
29             sb.Append(startExcelXML);
30             System.Text.RegularExpressions.Regex reg = new System.Text.RegularExpressions.Regex(@"^[-]?\d+[.]?\d*$");
31             for (int i = 0; i < Entity.Count; i++)
32             {
33                 sb.Append("<Worksheet ss:Name=\"" + SheetNames[i] + "\">");
34                 sb.Append("<Table>");
35                 foreach (var record in Entity[i])
36                 {
37                     sb.Append("<Row>");
38                     foreach (var column in record)
39                     {
40                         if (column!=null&&reg.IsMatch(column))
41                         {
42                             sb.Append("<Cell><Data ss:Type=\"Number\">");
43                         }
44                         else
45                         {
46                             sb.Append("<Cell><Data ss:Type=\"String\">");
47                         }
48                         sb.Append(column);
49                         sb.Append("</Data></Cell>");
50                     }
51                     sb.Append("</Row>");
52                 }
53                 sb.Append("</Table>");
54                 sb.Append(" </Worksheet>");
55             }
56 
57             sb.Append(endExcelXML);
58 
59             return sb;
60         }

2 字符串生成Excel文件

1 byte[] bytes = Encoding.UTF8.GetBytes(sb.ToString());
2 
3             if (!System.IO.File.Exists(@FilePath + FileName))
4             {
5                 using (FileStream fs = new FileStream(@FilePath + FileName, FileMode.Create))
6                 {
7                     fs.Write(bytes, 0, bytes.Length);
8                 }
9             }

 

其中FileName带".xls".

 

 

转载于:https://www.cnblogs.com/Hard/archive/2012/08/08/entitylist-excel.html