C#利用XML创建Excel文档的实现方法

前端技术 2023/09/07 C#

一般来说C#在不安装Excel软件的情况下，可以通过XML来创建Excel文档。因此，运行本文所述代码您无需安装Excel程序。本文原例子是使用VB.Net写的，以下的用C#改写的代码，分享给大家，供大家参考。

具体代码如下：

DataSet mDSData = new DataSet();
mDSData.Tables.Add(\"myTable\");
mDSData.Tables[\"myTable\"].Columns.Add(\"ID\");
mDSData.Tables[\"myTable\"].Columns.Add(\"Name\");
mDSData.Tables[\"myTable\"].Columns.Add(\"PassWord\");
for (int i = 0; i < 10; i++)
{
  DataRow dr = mDSData.Tables[\"myTable\"].NewRow();
  dr[\"ID\"] = i;
  dr[\"Name\"] = i;
  dr[\"PassWord\"] = i;
  mDSData.Tables[\"myTable\"].Rows.Add(dr);
}
SaveFileDialog dialog1 = new SaveFileDialog();
dialog1.AddExtension = true;
dialog1.CheckPathExists = true;
dialog1.Filter = \"Excel Workbooks (*.xls) | *.xls\";
dialog1.OverwritePrompt = true;
dialog1.Title = \"Save Excel Formatted Report\";
if (dialog1.ShowDialog() == DialogResult.OK)
{
  int num2 = 0;
  int num3 = mDSData.Tables[0].Rows.Count + 1;
  int num1 = mDSData.Tables[0].Columns.Count;
  num2 = 0;
  string text1 = dialog1.FileName;
  if (File.Exists(text1))
  {
    File.Delete(text1);
  }
  StreamWriter writer1 = new StreamWriter(text1, false);
  StreamWriter writer2 = writer1;
  writer2.WriteLine(\"<?xml version=\\\"1.0\\\"?>\");
  writer2.WriteLine(\"<?mso-application progid=\\\"Excel.Sheet\\\"?>\");
  writer2.WriteLine(\"<Workbook xmlns=\\\"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(\" <DocumentProperties xmlns=\\\"urn:schemas-microsoft-com:office:office\\\">\");
  writer2.WriteLine(\" <Author>Automated Report Generator Example</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>Your Company Here</Company>\");
  writer2.WriteLine(\" <Version>11.6408</Version>\");
  writer2.WriteLine(\" </DocumentProperties>\");
  writer2.WriteLine(\" <ExcelWorkbook xmlns=\\\"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(\" <Style ss:ID=\\\"Default\\\" ss:Name=\\\"Normal\\\">\");
  writer2.WriteLine(\"  <Alignment ss:Vertical=\\\"Bottom\\\"/>\");
  writer2.WriteLine(\"  <Borders/>\");
  writer2.WriteLine(\"  <Font/>\");
  writer2.WriteLine(\"  <Interior/>\");
  writer2.WriteLine(\"  <Protection/>\");
  writer2.WriteLine(\" </Style>\");
  writer2.WriteLine(\" <Style ss:ID=\\\"s21\\\">\");
  writer2.WriteLine(\"  <Alignment ss:Vertical=\\\"Bottom\\\" ss:WrapText=\\\"1\\\"/>\");
  writer2.WriteLine(\" </Style>\");
  writer2.WriteLine(\" </Styles>\");
  writer2.WriteLine(\" <Worksheet ss:Name=\\\"MyReport\\\">\");
  writer2.WriteLine(string.Format(\" <Table ss:ExpandedColumnCount=\\\"{0}\\\" ss:ExpandedRowCount=\\\"{1}\\\" x:FullColumns=\\\"1\\\"\", num1.ToString(), num3.ToString()));
  writer2.WriteLine(\"  x:FullRows=\\\"1\\\">\");
  foreach (DataRow row1 in mDSData.Tables[0].Rows)
  {
    writer2.WriteLine(\"<Row>\");
    for (num2 = 0; num2 != num1; num2++)
    {
      writer2.Write(\"<Cell ss:StyleID=\\\"s21\\\"><Data ss:Type=\\\"String\\\">\");
      writer2.Write(row1[num2].ToString());
      writer2.WriteLine(\"</Data></Cell>\");
    }
    writer2.WriteLine(\"</Row>\");
  }
  writer2.WriteLine(\" </Table>\");
  writer2.WriteLine(\" <WorksheetOptions xmlns=\\\"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(\" <Worksheet ss:Name=\\\"Sheet2\\\">\");
  writer2.WriteLine(\" <WorksheetOptions xmlns=\\\"urn:schemas-microsoft-com:office:excel\\\">\");
  writer2.WriteLine(\"  <ProtectObjects>False</ProtectObjects>\");
  writer2.WriteLine(\"  <ProtectScenarios>False</ProtectScenarios>\");
  writer2.WriteLine(\" </WorksheetOptions>\");
  writer2.WriteLine(\" </Worksheet>\");
  writer2.WriteLine(\" <Worksheet ss:Name=\\\"Sheet3\\\">\");
  writer2.WriteLine(\" <WorksheetOptions xmlns=\\\"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(\"Report Created\", \"Success\", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}

这只是主要的代码，使用前需要此入using相应的命名空间，如果不知道需要哪个命名空间，可在编译时根据提示逐个添加。

本文地址：https://www.stayed.cn/item/19284

转载请注明出处。

本站部分内容来源于网络,如侵犯到您的权益,请 联系我

我的博客

人生若只如初见，何事秋风悲画扇。

我的标签

随笔档案