C#从我自己的XML文件创建一个excel文件

本文关键字：一个 excel 文件 我自己 自己的 XML 文件创建 | 更新日期: 2023-09-27 18:01:13

我编写了一个程序，从数据库中进行查询，并创建一个包含查询结果的xml文件。如何制作一个启用宏的excel文件，其中包含xml文件中的元素？

下面是一个xml文件的简短示例，它位于我的驱动器C:(我用XXXX替换了一些部件(

<JobSvc environment="TEST" dateTo="6/22/2015" dateFrom="2015-06-15T00:00:00+08:00">
  <process server="XXXX" type="XXXX" name="XXXX">
    <processId duration="2421" eventTime="6/1/2015 11:41:26 AM" source="TEST SOURCE" num="3">PROCESS ID</processId>
  </process>
</JobSvc>

以下是我从xml文件生成excel文件的尝试(我从互联网上得到它，不幸的是，它不起作用，但也不会产生异常或错误，因为我的程序会为异常创建日志。(：

public void xmlToExcel()
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;
            DataSet ds = new DataSet();
            XmlReader xmlFile;
            int i = 0;
            int j = 0;
            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            xmlFile = XmlReader.Create(@"C:'PerformanceMonitorXML " + DateTime.Now.ToString("dd_MM_yyyy") + ".xml", new XmlReaderSettings());
            ds.ReadXml(xmlFile);
            for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
            {
                for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
                {
                    xlWorkSheet.Cells[i + 1, j + 1] = ds.Tables[0].Rows[i].ItemArray[j].ToString();
                }
            }
            xlWorkBook.SaveAs("C''PerformanceMonitorExcel " + DateTime.Now.ToString("dd_MM_yyyy") + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();
            releaseObject(xlApp);
            releaseObject(xlWorkBook);
            releaseObject(xlWorkSheet);

        }
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
            }
            finally
            {
                GC.Collect();
            }
        }

C#从我自己的XML文件创建一个excel文件

很可能问题出在您的特权上。如果你想在C上保存，你必须以管理员权限运行VS。另外，在另存为中也有一个拼写错误。此外，我建议不要逐个单元格，而是在对象数组中写入excel。下面的代码对我有效>

Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
DataSet ds = new DataSet();
XmlReader xmlFile;
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xmlFile = XmlReader.Create(@"C:'example.xml", new XmlReaderSettings());
ds.ReadXml(xmlFile);
object[,] objects = new object[ds.Tables[0].Rows.Count, ds.Tables[0].Columns.Count];
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
    for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
    {
        objects[i, j] = ds.Tables[0].Rows[i][j].ToString();
    }
}
Microsoft.Office.Interop.Excel.Range range = xlWorkSheet.get_Range("A1", "D3");
range.Value = objects;
releaseObject(range);
xlWorkBook.SaveAs("C:''PerformanceMonitorExcel " + DateTime.Now.ToString("dd_MM_yyyy") + ".xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlApp);
releaseObject(xlWorkBook);
releaseObject(xlWorkSheet);
MessageBox.Show("finished");

示例xml是>

<root>
  <JobSvc environment="TEST" dateTo="6/22/2015" dateFrom="2015-06-15T00:00:00+08:00">
    <process server="XXXX" type="XXXX" name="XXXX">
      <processId duration="2421" eventTime="6/1/2015 11:41:26 AM" source="TEST SOURCE" num="3">PROCESS ID</processId>
    </process>
  </JobSvc>
  <JobSvc environment="TEST" dateTo="6/23/2015" dateFrom="2015-06-15T00:00:00+08:00">
    <process server="XXXX" type="XXXX" name="XXXX">
      <processId duration="2421" eventTime="6/1/2015 11:41:26 AM" source="TEST SOURCE" num="3">PROCESS ID</processId>
    </process>
  </JobSvc>
  <JobSvc environment="TEST" dateTo="6/24/2015" dateFrom="2015-06-15T00:00:00+08:00">
    <process server="XXXX" type="XXXX" name="XXXX">
      <processId duration="2421" eventTime="6/1/2015 11:41:26 AM" source="TEST SOURCE" num="3">PROCESS ID</processId>
    </process>
  </JobSvc>
</root>

Br，Márton

相关文章：