动态解析XML生成EXCEL

在做项目中，可能会遇到这样的需求，根据数据库里的数据生成相应格式Excel表格。生成的Excel表格不是常规的表格，而是存在合并，宽度不一，长短不一这种需求的。

类似于下面的图表：

这张图表中存在合并单元格和单元格大小不一的情况，现在通过解析XML生成类似的单元格，解析XML是逻辑分析的过程，根据不同的Excel表格找到不同的规律来解析，主要是调整生成的Excel表格和模板的一致性。

解析XML不是难点，难点是生成一样的Excel，当时我遇到了很多困难，这里主要说的也是操作XML而非解析。

每个单元格的样式，就是单元格的背景色，边界的颜色，内容是不是水平居中，竖直居中，是由Style控制的

Alignment 属性决定的是内容的位置，是居左还是居右还是居中。

Interior属性控制的是背景颜色。

Borders 属性控制的边界的颜色等。

View Code

 1 writer.WriteLine("  <Style ss:ID=\"header\">");
 2                  writer.WriteLine("   <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\" ss:WrapText=\"1\"/>");
 3                  writer.WriteLine("   <Interior ss:Color=\"#00FFFF\" ss:Pattern=\"Solid\"/>");
 4                  writer.WriteLine("   <Borders>");
 5                  writer.WriteLine("    <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Color=\"#0000FF\" ss:Weight=\"1\"/>");
 6                  writer.WriteLine("    <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\"  ss:Color=\"#0000FF\" ss:Weight=\"1\"/>");
 7                  writer.WriteLine("    <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\"  ss:Color=\"#0000FF\" ss:Weight=\"1\"/>");
 8                  writer.WriteLine("    <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\"  ss:Color=\"#0000FF\" ss:Weight=\"1\"/>");
 9                  writer.WriteLine("   </Borders>");
10                  writer.WriteLine("  </Style>");
11                  writer.WriteLine(" </Styles>");

Worksheet属性：

Worksheet对应着sheet1，有几个就对应几个，可以设置sheet1的名字等属性。

在worksheet下可以设置每一列的宽度，通过Column属性设置列宽度 Index设置那一列 width设置宽度。

<Column ss:Index=\"1\" ss:Width=\"147.00\"/>设置第一列的宽度是147像素

通过Row设置行的属性，height设置行的高度。

<Row ss:Height=\"141.00\">每行高度是141像素

MergeAcross属性是跨列属性：

横跨两列形成这个效果。

通过这些设置，就可以基本形成类似于模板图表的效果了。

完整的代码：

View Code

  1 //要转换的XML文件
  2                 creatXML();
  3                 string XMLFileName = Path.Combine(Request.PhysicalApplicationPath, "XML\\document.xml");
  4                 DataSet dsBook = new DataSet();
  5                 dsBook.ReadXml(XMLFileName);
  6                 int rows = dsBook.Tables[0].Rows.Count + 1;
  7                 int cols = 7;                
  8  
  9                  //将要生成的Excel文件
 10                 string ExcelFileName = Path.Combine(Request.PhysicalApplicationPath, "XML\\DMSExcel.xls");
 11                 if (File.Exists(ExcelFileName))
 12                 {
 13                     File.Delete(ExcelFileName);
 14                 }
 15                 StreamWriter writer = new StreamWriter(ExcelFileName, false);
 16                  writer.WriteLine("<?xml version=\"1.0\"?>");
 17                  writer.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>");
 18                  writer.WriteLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
 19                  writer.WriteLine(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
 20                  writer.WriteLine(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
 21                  writer.WriteLine(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
 22                  writer.WriteLine(" xmlns:html=\"http://www.w3.org/TR/REC-html40/\">");
 23                  writer.WriteLine(" <DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
 24                  writer.WriteLine("  <Author>Automated Report Generator Example</Author>");
 25                  writer.WriteLine(string.Format("  <Created>{0}T{1}Z</Created>", DateTime.Now.ToString("yyyy-mm-dd"), DateTime.Now.ToString("HH:MM:SS")));
 26                  writer.WriteLine("  <Company>Your Company Here</Company>");
 27                  writer.WriteLine("  <Version>11.6408</Version>");
 28                  writer.WriteLine(" </DocumentProperties>");
 29                  writer.WriteLine(" <ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">");
 30                  writer.WriteLine("  <WindowHeight>8955</WindowHeight>");
 31                  writer.WriteLine("  <WindowWidth>11355</WindowWidth>");
 32                  writer.WriteLine("  <WindowTopX>480</WindowTopX>");
 33                  writer.WriteLine("  <WindowTopY>15</WindowTopY>");
 34                  writer.WriteLine("  <ProtectStructure>False</ProtectStructure>");
 35                  writer.WriteLine("  <ProtectWindows>False</ProtectWindows>");
 36                  writer.WriteLine(" </ExcelWorkbook>");
 37                  writer.WriteLine(" <Styles>");
 38                  writer.WriteLine("  <Style ss:ID=\"Default\" ss:Name=\"Normal\">");
 39                  writer.WriteLine("   <Alignment ss:Vertical=\"Bottom\"/>");
 40                  writer.WriteLine("   <Borders/>");
 41                  writer.WriteLine("   <Font/>");
 42                  writer.WriteLine("   <Interior/>");
 43                  writer.WriteLine("   <Protection/>");
 44                  writer.WriteLine("  </Style>");
 45                  writer.WriteLine("  <Style ss:ID=\"s21\">");
 46                  writer.WriteLine("   <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\" ss:WrapText=\"1\"/>");
 47                  writer.WriteLine("   <Interior ss:Pattern=\"Solid\"/>");
 48                  writer.WriteLine("   <Borders>");
 49                  writer.WriteLine("    <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Color=\"#0000FF\" ss:Weight=\"1\"/>");
 50                  writer.WriteLine("    <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\"  ss:Color=\"#0000FF\" ss:Weight=\"1\"/>");
 51                  writer.WriteLine("    <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\"  ss:Color=\"#0000FF\" ss:Weight=\"1\"/>");
 52                  writer.WriteLine("    <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\"  ss:Color=\"#0000FF\" ss:Weight=\"1\"/>");
 53                  writer.WriteLine("   </Borders>");
 54                  writer.WriteLine("  </Style>");
 55                  
 56                  writer.WriteLine("  <Style ss:ID=\"header\">");
 57                  writer.WriteLine("   <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\" ss:WrapText=\"1\"/>");
 58                  writer.WriteLine("   <Interior ss:Color=\"#00FFFF\" ss:Pattern=\"Solid\"/>");
 59                  writer.WriteLine("   <Borders>");
 60                  writer.WriteLine("    <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Color=\"#0000FF\" ss:Weight=\"1\"/>");
 61                  writer.WriteLine("    <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\"  ss:Color=\"#0000FF\" ss:Weight=\"1\"/>");
 62                  writer.WriteLine("    <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\"  ss:Color=\"#0000FF\" ss:Weight=\"1\"/>");
 63                  writer.WriteLine("    <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\"  ss:Color=\"#0000FF\" ss:Weight=\"1\"/>");
 64                  writer.WriteLine("   </Borders>");
 65                  writer.WriteLine("  </Style>");
 66                  writer.WriteLine(" </Styles>");
 67 
 68                  writer.WriteLine(" <Worksheet ss:Name=\"MyReport\">");
 69                  
 70                  writer.WriteLine(string.Format("  <Table ss:ExpandedColumnCount=\"9\" ss:ExpandedRowCount=\"{0}\" x:FullColumns=\"1\"",rows.ToString()));
 71                  writer.WriteLine("   x:FullRows=\"1\">");
 72                  //生成标题
 73                  writer.WriteLine("<Column ss:Index=\"1\" ss:Width=\"147.00\"/>");
 74                  writer.WriteLine("<Column ss:Index=\"2\" ss:Width=\"72.00\"/>");
 75                  writer.WriteLine("<Column ss:Index=\"3\" ss:Width=\"42.89\"/>");
 76                  writer.WriteLine("<Column ss:Index=\"4\" ss:Width=\"42.89\"/>");
 77                  writer.WriteLine("<Column ss:Index=\"5\" ss:Width=\"42.89\"/>");
 78                  writer.WriteLine("<Column ss:Index=\"6\" ss:Width=\"48.25\"/>");
 79                  writer.WriteLine("<Column ss:Index=\"7\" ss:Width=\"21.44\"/>");
 80                  writer.WriteLine("<Column ss:Index=\"8\" ss:Width=\"21.44\"/>");
 81                  writer.WriteLine("<Column ss:Index=\"9\" ss:Width=\"21.44\"/>");
 82                  writer.WriteLine("<Row ss:Height=\"141.00\">");
 83                  string[] strDocumentTitle=new string[9];
 84                  int i = 0;
 85                  foreach (DataColumn eachCloumn in dsBook.Tables[0].Columns)
 86                  {                     
 87                      string strTemp = eachCloumn.ColumnName.ToString();
 88                      strDocumentTitle[i] = strTemp;
 89                      i++;
 90                  }
 91                  writer.Write("<Cell ss:StyleID=\"header\"><Data ss:Type=\"String\">");
 92                  writer.Write(strDocumentTitle[0]);
 93                  writer.WriteLine("</Data></Cell>");
 94 
 95                  writer.Write("<Cell ss:StyleID=\"header\"><Data ss:Type=\"String\">");
 96                  writer.Write(strDocumentTitle[1]);
 97                  writer.WriteLine("</Data></Cell>");
 98 
 99                  writer.Write("<Cell ss:StyleID=\"header\" ss:MergeAcross=\"2\"><Data ss:Type=\"String\">");
100                  writer.Write(strDocumentTitle[2]);
101                  writer.WriteLine("</Data></Cell>");
102 
103                  writer.Write("<Cell ss:StyleID=\"header\"><Data ss:Type=\"String\">");
104                  writer.Write(strDocumentTitle[5]);
105                  writer.WriteLine("</Data></Cell>");
106 
107                  writer.Write("<Cell ss:StyleID=\"header\"><Data ss:Type=\"String\">");
108                  writer.Write(strDocumentTitle[6]);
109                  writer.WriteLine("</Data></Cell>");
110 
111                  writer.Write("<Cell ss:StyleID=\"header\"><Data ss:Type=\"String\">");
112                  writer.Write(strDocumentTitle[7]);
113                  writer.WriteLine("</Data></Cell>");
114 
115                  writer.Write("<Cell ss:StyleID=\"header\"><Data ss:Type=\"String\">");
116                  writer.Write(strDocumentTitle[8]);
117                  writer.WriteLine("</Data></Cell>");
118 
119                  writer.WriteLine("</Row>");
120  
121                  //生成数据记录
122                  foreach (DataRow eachRow in dsBook.Tables[0].Rows)
123                 {
124                     writer.WriteLine("<Row ss:Height=\"24.00\">");
125                     for (int currentRow = 0; currentRow != 9; currentRow++)
126                     {
127                         writer.Write("<Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">");
128                         writer.Write(eachRow[currentRow].ToString());
129                         writer.WriteLine("</Data></Cell>");
130                      }
131                      writer.WriteLine("</Row>");
132                  }
133                 writer.WriteLine("  </Table>");
134                 writer.WriteLine("  <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
135                 writer.WriteLine("   <Selected/>");
136                 writer.WriteLine("   <Panes>");
137                 writer.WriteLine("    <Pane>");
138                 writer.WriteLine("     <Number>3</Number>");
139                 writer.WriteLine("     <ActiveRow>1</ActiveRow>");
140                 writer.WriteLine("    </Pane>");
141                 writer.WriteLine("   </Panes>");
142                 writer.WriteLine("   <ProtectObjects>False</ProtectObjects>");
143                 writer.WriteLine("   <ProtectScenarios>False</ProtectScenarios>");
144                 writer.WriteLine("  </WorksheetOptions>");
145                 writer.WriteLine(" </Worksheet>");
146                 writer.WriteLine(" <Worksheet ss:Name=\"Sheet2\">");
147                 writer.WriteLine("  <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
148                 writer.WriteLine("   <ProtectObjects>False</ProtectObjects>");
149                 writer.WriteLine("   <ProtectScenarios>False</ProtectScenarios>");
150                 writer.WriteLine("  </WorksheetOptions>");
151                 writer.WriteLine(" </Worksheet>");
152                 writer.WriteLine(" <Worksheet ss:Name=\"Sheet3\">");
153                 writer.WriteLine("  <WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
154                 writer.WriteLine("   <ProtectObjects>False</ProtectObjects>");
155                 writer.WriteLine("   <ProtectScenarios>False</ProtectScenarios>");
156                 writer.WriteLine("  </WorksheetOptions>");
157                 writer.WriteLine(" </Worksheet>");
158                 writer.WriteLine("</Workbook>");
159                 writer.Close();

这个代码只是一种思想，根据XML生成相应的EXCEL关键是设计结构。至于解析数据，学过编程的都会。

posted @ 2013-02-13 14:20 Tony.J 阅读(878) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Tony.J's Blog

多情自古源代码，更那堪，冷落情人节，今宵酒醒何处。电脑旁，晓风残月，此去经年，爱情硬是形同虚设。便纵有千般无奈，更与何人说？

动态解析XML生成EXCEL