在做项目中，可能会遇到这样的需求，根据数据库里的数据生成相应格式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属性是跨列属性：
<Cell ss:StyleID=\"header\" ss:MergeAcross=\"2\"><Data ss:Type=\"String\">
横跨两列
形成这个效果。
通过这些设置，就可以基本形成类似于模板图表的效果了。
完整的代码：
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关键是设计结构。至于解析数据，学过编程的都会。
