#region 生成CXCEL文件
//将要生成的Excel文件
string ExcelFileName = Server.MapPath(totalPath + "/") + FileName + ".xls";
if (File.Exists(ExcelFileName))
File.Delete(ExcelFileName);
using (writer = new StreamWriter(ExcelFileName, false))
{
writer.WriteLine("<?xml version=/"1.0/"?>");
writer.WriteLine("<?mso-application progid=/"Excel.Sheet/"?>");
writer.WriteLine("<Workbook xmlns=/"urn:schemas-microsoft-com:office:spreadsheet/"");
writer.WriteLine(" xmlns:o=/"urn:schemas-microsoft-com:office:office/"");
writer.WriteLine(" xmlns:x=/"urn:schemas-microsoft-com:office:excel/"");
writer.WriteLine(" xmlns:ss=/"urn:schemas-microsoft-com:office:spreadsheet/"");
writer.WriteLine(" xmlns:html=/"http://www.w3.org/TR/REC-html40//">");
writer.WriteLine(" <DocumentProperties xmlns=/"urn:schemas-microsoft-com:office:office/">");
writer.WriteLine(" <Author>Automated Report Generator Example</Author>");
writer.WriteLine(string.Format(" <Created>{0}T{1}Z</Created>", DateTime.Now.ToString("yyyy-mm-dd"), DateTime.Now.ToString("HH:MM:SS")));
writer.WriteLine(" <Company>minyou</Company>");
writer.WriteLine(" <Version>11.6408</Version>");
writer.WriteLine(" </DocumentProperties>");
writer.WriteLine(" <ExcelWorkbook xmlns=/"urn:schemas-microsoft-com:office:excel/">");
writer.WriteLine(" <WindowHeight>8955</WindowHeight>");
writer.WriteLine(" <WindowWidth>11355</WindowWidth>");
writer.WriteLine(" <WindowTopX>480</WindowTopX>");
writer.WriteLine(" <WindowTopY>15</WindowTopY>");
writer.WriteLine(" <ProtectStructure>False</ProtectStructure>");
writer.WriteLine(" <ProtectWindows>False</ProtectWindows>");
writer.WriteLine(" </ExcelWorkbook>");
#region Excel样式
ss:Color=/"#0000FF/" ss:Underline=/"Single/"/>");
//writer.WriteLine(" </Style>");
writer.WriteLine(" <Styles> ");
writer.WriteLine(" <Style ss:ID=/"Default/" ss:Name=/"Normal/"> ");
writer.WriteLine(" <Alignment ss:Vertical=/"Bottom/"/> ");
writer.WriteLine(" <Borders/> ");
writer.WriteLine(" <Font ss:FontName=/"Arial/" x:Family=/"Swiss/"/> ");
writer.WriteLine(" <Interior/> ");
writer.WriteLine(" <Protection/> ");
writer.WriteLine(" </Style> ");
writer.WriteLine(" <Style ss:ID=/"s62/" ss:Name=/"超链接/"> ");
writer.WriteLine(" <Font ss:FontName=/"宋体/" x:CharSet=/"134/" ss:Size=/"12/" ss:Color=/"#0000FF/" ");
writer.WriteLine(" ss:Underline=/"Single/"/> ");
writer.WriteLine(" </Style> ");
writer.WriteLine(" <Style ss:ID=/"s68/"> ");
writer.WriteLine(" <Alignment ss:Horizontal=/"Center/" ss:Vertical=/"Bottom/"/> ");
writer.WriteLine(" <Borders> ");
writer.WriteLine(" <Border ss:Position=/"Bottom/" ss:LineStyle=/"Continuous/" ss:Weight=/"1/"/> ");
writer.WriteLine(" <Border ss:Position=/"Left/" ss:LineStyle=/"Continuous/" ss:Weight=/"1/"/> ");
writer.WriteLine(" <Border ss:Position=/"Right/" ss:LineStyle=/"Continuous/" ss:Weight=/"1/"/> ");
writer.WriteLine(" <Border ss:Position=/"Top/" ss:LineStyle=/"Continuous/" ss:Weight=/"1/"/> ");
writer.WriteLine(" </Borders> ");
writer.WriteLine(" <Interior ss:Color=/"#EEECE1/" ss:Pattern=/"Solid/"/> ");
writer.WriteLine(" </Style> ");
writer.WriteLine(" <Style ss:ID=/"s69/"> ");
writer.WriteLine(" <Alignment ss:Horizontal=/"Center/" ss:Vertical=/"Bottom/"/> ");
writer.WriteLine(" <Borders> ");
writer.WriteLine(" <Border ss:Position=/"Bottom/" ss:LineStyle=/"Continuous/" ss:Weight=/"1/"/> ");
writer.WriteLine(" <Border ss:Position=/"Left/" ss:LineStyle=/"Continuous/" ss:Weight=/"1/"/> ");
writer.WriteLine(" <Border ss:Position=/"Right/" ss:LineStyle=/"Continuous/" ss:Weight=/"1/"/> ");
writer.WriteLine(" <Border ss:Position=/"Top/" ss:LineStyle=/"Continuous/" ss:Weight=/"1/"/> ");
writer.WriteLine(" </Borders> ");
writer.WriteLine(" </Style> ");
writer.WriteLine(" <Style ss:ID=/"s70/" ss:Parent=/"s62/"> ");
writer.WriteLine(" <Alignment ss:Horizontal=/"Center/" ss:Vertical=/"Bottom/"/> ");
writer.WriteLine(" <Borders> ");
writer.WriteLine(" <Border ss:Position=/"Bottom/" ss:LineStyle=/"Continuous/" ss:Weight=/"1/"/> ");
writer.WriteLine(" <Border ss:Position=/"Left/" ss:LineStyle=/"Continuous/" ss:Weight=/"1/"/> ");
writer.WriteLine(" <Border ss:Position=/"Right/" ss:LineStyle=/"Continuous/" ss:Weight=/"1/"/> ");
writer.WriteLine(" <Border ss:Position=/"Top/" ss:LineStyle=/"Continuous/" ss:Weight=/"1/"/> ");
writer.WriteLine(" </Borders> ");
writer.WriteLine(" <Font ss:FontName=/"宋体/" x:CharSet=/"134/" ss:Size=/"12/" ss:Color=/"#0000FF/" ");
writer.WriteLine(" ss:Underline=/"Single/"/> ");
writer.WriteLine(" </Style> ");
writer.WriteLine(" </Styles> ");
#endregion
for (int tableCount = 0; tableCount < dsBook.Tables.Count; tableCount++)
{
System.Data.DataTable dt = dsBook.Tables[tableCount];
string tableName = dt.TableName;
//表头
writer.WriteLine(" <Worksheet ss:Name=/"" + tableName + "/">");
writer.WriteLine(string.Format(" <Table ss:ExpandedColumnCount=/"{0}/" ss:ExpandedRowCount=/"{1}/" x:FullColumns=/"1/"", (dt.Columns.Count).ToString(), (dt.Rows.Count + 1).ToString()));
writer.WriteLine(" x:FullRows=/"1/">");
//生成标题
writer.WriteLine("<Row>");
foreach (DataColumn eachCloumn in dt.Columns)
{
writer.Write("<Cell ss:StyleID=/"s68/"><Data ss:Type=/"String/">");
writer.Write(eachCloumn.ColumnName.ToString());
writer.WriteLine("</Data></Cell>");
}
writer.WriteLine("</Row>");
foreach (DataRow eachRow in dt.Rows)
//生成数据记录
{
writer.WriteLine("<Row ss:Height=/"15/">");
for (int currentRow = 0; currentRow < dt.Columns.Count; currentRow++)
{
writer.Write("<Cell ss:StyleID=/"s69/"><Data ss:Type=/"String/">");
writer.Write(eachRow[currentRow].ToString());
writer.WriteLine("</Data></Cell>");
}
writer.WriteLine("</Row>");
}
writer.WriteLine(" </Table>");
writer.WriteLine(" <WorksheetOptions xmlns=/"urn:schemas-microsoft-com:office:excel/">");
if (tableCount == 0)
{
writer.WriteLine(" <Selected/>");
writer.WriteLine(" <Panes>");
writer.WriteLine(" <Pane>");
writer.WriteLine(" <Number>3</Number>");
writer.WriteLine(" <ActiveRow>1</ActiveRow>");
writer.WriteLine(" </Pane>");
writer.WriteLine(" </Panes>");
writer.WriteLine(" <ProtectObjects>False</ProtectObjects>");
}
writer.WriteLine(" <ProtectScenarios>False</ProtectScenarios>");
writer.WriteLine(" </WorksheetOptions>");
writer.WriteLine(" </Worksheet>");
}
writer.WriteLine("</Workbook>");
writer.Close();