C#利用XML创建Excel文档的实现方法
一般来说C#在不安装Excel软件的情况下,可以通过XML来创建Excel文档。因此,运行本文所述代码您无需安装Excel程序。本文原例子是使用VB.Net写的,以下的用C#改写的代码,分享给大家,供大家参考。
具体代码如下:
DataSetmDSData=newDataSet(); mDSData.Tables.Add("myTable"); mDSData.Tables["myTable"].Columns.Add("ID"); mDSData.Tables["myTable"].Columns.Add("Name"); mDSData.Tables["myTable"].Columns.Add("PassWord"); for(inti=0;i<10;i++) { DataRowdr=mDSData.Tables["myTable"].NewRow(); dr["ID"]=i; dr["Name"]=i; dr["PassWord"]=i; mDSData.Tables["myTable"].Rows.Add(dr); } SaveFileDialogdialog1=newSaveFileDialog(); dialog1.AddExtension=true; dialog1.CheckPathExists=true; dialog1.Filter="ExcelWorkbooks(*.xls)|*.xls"; dialog1.OverwritePrompt=true; dialog1.Title="SaveExcelFormattedReport"; if(dialog1.ShowDialog()==DialogResult.OK) { intnum2=0; intnum3=mDSData.Tables[0].Rows.Count+1; intnum1=mDSData.Tables[0].Columns.Count; num2=0; stringtext1=dialog1.FileName; if(File.Exists(text1)) { File.Delete(text1); } StreamWriterwriter1=newStreamWriter(text1,false); StreamWriterwriter2=writer1; writer2.WriteLine("<?xmlversion=\"1.0\"?>"); writer2.WriteLine("<?mso-applicationprogid=\"Excel.Sheet\"?>"); writer2.WriteLine("<Workbookxmlns=\"urn:schemas-microsoft-com:office:spreadsheet\""); writer2.WriteLine("xmlns:o=\"urn:schemas-microsoft-com:office:office\""); writer2.WriteLine("xmlns:x=\"urn:schemas-microsoft-com:office:excel\""); writer2.WriteLine("xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\""); writer2.WriteLine("xmlns:html=\"http://www.w3.org/TR/REC-html40\">"); writer2.WriteLine("<DocumentPropertiesxmlns=\"urn:schemas-microsoft-com:office:office\">"); writer2.WriteLine("<Author>AutomatedReportGeneratorExample</Author>"); writer2.WriteLine(string.Format("<Created>{0}T{1}Z</Created>",DateTime.Now.ToString("yyyy-mm-dd"),DateTime.Now.ToString("HH:MM:SS"))); writer2.WriteLine("<Company>YourCompanyHere</Company>"); writer2.WriteLine("<Version>11.6408</Version>"); writer2.WriteLine("</DocumentProperties>"); writer2.WriteLine("<ExcelWorkbookxmlns=\"urn:schemas-microsoft-com:office:excel\">"); writer2.WriteLine("<WindowHeight>8955</WindowHeight>"); writer2.WriteLine("<WindowWidth>11355</WindowWidth>"); writer2.WriteLine("<WindowTopX>480</WindowTopX>"); writer2.WriteLine("<WindowTopY>15</WindowTopY>"); writer2.WriteLine("<ProtectStructure>False</ProtectStructure>"); writer2.WriteLine("<ProtectWindows>False</ProtectWindows>"); writer2.WriteLine("</ExcelWorkbook>"); writer2.WriteLine("<Styles>"); writer2.WriteLine("<Styless:ID=\"Default\"ss:Name=\"Normal\">"); writer2.WriteLine("<Alignmentss:Vertical=\"Bottom\"/>"); writer2.WriteLine("<Borders/>"); writer2.WriteLine("<Font/>"); writer2.WriteLine("<Interior/>"); writer2.WriteLine("<Protection/>"); writer2.WriteLine("</Style>"); writer2.WriteLine("<Styless:ID=\"s21\">"); writer2.WriteLine("<Alignmentss:Vertical=\"Bottom\"ss:WrapText=\"1\"/>"); writer2.WriteLine("</Style>"); writer2.WriteLine("</Styles>"); writer2.WriteLine("<Worksheetss:Name=\"MyReport\">"); writer2.WriteLine(string.Format("<Tabless:ExpandedColumnCount=\"{0}\"ss:ExpandedRowCount=\"{1}\"x:FullColumns=\"1\"",num1.ToString(),num3.ToString())); writer2.WriteLine("x:FullRows=\"1\">"); foreach(DataRowrow1inmDSData.Tables[0].Rows) { writer2.WriteLine("<Row>"); for(num2=0;num2!=num1;num2++) { writer2.Write("<Cellss:StyleID=\"s21\"><Datass:Type=\"String\">"); writer2.Write(row1[num2].ToString()); writer2.WriteLine("</Data></Cell>"); } writer2.WriteLine("</Row>"); } writer2.WriteLine("</Table>"); writer2.WriteLine("<WorksheetOptionsxmlns=\"urn:schemas-microsoft-com:office:excel\">"); writer2.WriteLine("<Selected/>"); writer2.WriteLine("<Panes>"); writer2.WriteLine("<Pane>"); writer2.WriteLine("<Number>3</Number>"); writer2.WriteLine("<ActiveRow>1</ActiveRow>"); writer2.WriteLine("</Pane>"); writer2.WriteLine("</Panes>"); writer2.WriteLine("<ProtectObjects>False</ProtectObjects>"); writer2.WriteLine("<ProtectScenarios>False</ProtectScenarios>"); writer2.WriteLine("</WorksheetOptions>"); writer2.WriteLine("</Worksheet>"); writer2.WriteLine("<Worksheetss:Name=\"Sheet2\">"); writer2.WriteLine("<WorksheetOptionsxmlns=\"urn:schemas-microsoft-com:office:excel\">"); writer2.WriteLine("<ProtectObjects>False</ProtectObjects>"); writer2.WriteLine("<ProtectScenarios>False</ProtectScenarios>"); writer2.WriteLine("</WorksheetOptions>"); writer2.WriteLine("</Worksheet>"); writer2.WriteLine("<Worksheetss:Name=\"Sheet3\">"); writer2.WriteLine("<WorksheetOptionsxmlns=\"urn:schemas-microsoft-com:office:excel\">"); writer2.WriteLine("<ProtectObjects>False</ProtectObjects>"); writer2.WriteLine("<ProtectScenarios>False</ProtectScenarios>"); writer2.WriteLine("</WorksheetOptions>"); writer2.WriteLine("</Worksheet>"); writer2.WriteLine("</Workbook>"); writer2=null; writer1.Close(); MessageBox.Show("ReportCreated","Success",MessageBoxButtons.OK,MessageBoxIcon.Asterisk); }
这只是主要的代码,使用前需要此入using相应的命名空间,如果不知道需要哪个命名空间,可在编译时根据提示逐个添加。