在vb.net中使用XML从datagridview导出到excel

Question

我正在尝试使用XML将数据从datagridview导出到excel文件。下面是我写的代码

Dim fs As New IO.StreamWriter(FileName, False)
With fs
.WriteLine("<?xml version=""1.0""?>")
.WriteLine("<?mso-application progid=""Excel.Sheet""?>")
.WriteLine("<ss:Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet"">")
.WriteLine("    <ss:Styles>")
.WriteLine("        <ss:Style ss:ID=""1"">")
.WriteLine("            <ss:Font ss:Bold=""1""/>")
.WriteLine("        <ss:/Style>")
.WriteLine("    <ss:Worksheet ss:Name=""WCRPaymentLog"">")
.WriteLine("        <ss:Table>")

For x As Integer = 0 To dgReport.Columns.Count - 1
.WriteLine("            <ss:Column ss:Width=""{0}""/>", dgReport.Columns.Item(x).Width)
Next
.WriteLine("            <ss:Row ss:StyleID=""1"">")
For x As Integer = 0 To dgReport.Columns.Count - 1
.WriteLine("                <ss:Cell>")
.WriteLine(String.Format("                  <ss:Data ss:Type=""String"">{0}</ss:Data>", dgReport.Columns.Item(x).HeaderText.Trim))
.WriteLine("                </ss:Cell>")
Next
.WriteLine("            </ss:Row>")
For intRow As Integer = 0 To dgReport.RowCount - 2
.WriteLine(String.Format("          <ss:Row ss:Height=""{0}"">", dgReport.Rows(intRow).Height))
For intCol As Integer = 0 To dgReport.Columns.Count - 1
.WriteLine("                <ss:Cell>")
.WriteLine(String.Format("                  <ss:Data ss:Type=""String"">{0}</ss:Data>", dgReport.Item(intCol, intRow).Value.ToString.Trim))
.WriteLine("                </ss:Cell>")
Next
.WriteLine("            </ss:Row>")
Next
.WriteLine("        </ss:Table>")
.WriteLine("    </ss:Worksheet>")
.WriteLine("</ss:Workbook>")
.Close()
End With

当我试图运行此代码时，它会正确执行，但在打开生成的文件时，它给出了错误：Strict Parse error。

错误日志生成如下：XML PARSE错误：未定义的命名空间此元素堆栈处或以下发生错误：(堆栈为空--错误发生在顶层元素或其下方。(

在我犯错误的地方，有人能帮我吗。

此外，如果文件存在，我想这样编码，然后它会在代码中添加一个新的工作表，并在该工作表中写入表。有人能帮我知道怎么做吗？因为这段代码覆盖了现有的文件。

Answer 1

尝试使用Xml Linq:

Imports System.Xml
Imports System.Xml.Linq
Public Class Form1
Const FILENAME As String = "c:temptest.xml"
Sub New()
' This call is required by the designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
End Sub
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Dim xml As String = "<?xml version=""1.0""?><?mso-application progid=""Excel.Sheet""?><ss:Workbook xmlns:ss=""abc"" xmlns:urn=""schemas-microsoft-com:office:spreadsheet"">" & _
"<ss:Styles><ss:Style ss:ID=""1""><ss:Font ss:Bold=""1""/></ss:Style></ss:Styles ></ss:Workbook>"
Dim doc As XDocument = XDocument.Parse(xml)
Dim workbook As XElement = doc.Root
Dim ssNs As XNamespace = workbook.GetNamespaceOfPrefix("ss")
Dim worksheet = New XElement(ssNs + "Worksheet", New XAttribute(ssNs + "Name", "WCRPaymentLog"))
workbook.Add(worksheet)
Dim table = New XElement(ssNs + "table")
worksheet.Add(table)
For x As Integer = 0 To dgReport.Columns.Count - 1
Dim column As XElement = New XElement(ssNs + "Column", New XAttribute(ssNs + "Width", dgReport.Columns.Item(x).Width))
TAB()
Next
Dim row As XElement = New XElement(ssNs + "Row", New XAttribute(ssNs + "StyleID", 1))
table.Add(row)
Dim cell As XElement
For x As Integer = 0 To dgReport.Columns.Count - 1
cell = New XElement(ssNs + "Cell", New XElement(ssNs + "Data", New Object() {New XAttribute("Type", "String"), dgReport.Columns.Item(x).HeaderText.Trim}))
row.Add(cell)
Next
For intRow As Integer = 0 To dgReport.RowCount - 2
row = New XElement(ssNs + "Row", New XAttribute(ssNs + "Height", dgReport.Rows(intRow).Height))
table.Add(row)
For intCol As Integer = 0 To dgReport.Columns.Count - 1
cell = New XElement(ssNs + "Cell", New Object() {New XAttribute("Type", "String"), dgReport.Item(intCol, intRow).Value.ToString.Trim})
row.Add(cell)
Next
Next
doc.Save(FILENAME)
End Sub
End Class

Answer 2

您似乎正在尝试创建Excel 2003 SpreadsheetML文件。

您表示在Excel 中打开文件时收到此错误

XML PARSE错误：未定义的命名空间错误发生在此元素堆栈处或以下：(堆栈为空--错误发生在顶级元素处或以下。(

这让我想到了这一行：

.WriteLine("<ss:Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet"">")

这里您使用的是名称空间别名"ss:"，但它没有定义。此外，该标签应如下所示。

<Workbook xmlns="urn:schemas-microsoft-com:office:Spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:Spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">

有关更多信息，请参阅：

OfficeTalk:深入电子表格ML(第1部分，共2部分(

OfficeTalk:深入电子表格ML(第2部分，共2部分(

编辑：

您还可以发现使用VB的Xml Literals是构造文件的一种更简单的方法。教程视频[如何使用LINQ to XML创建Excel电子表格？可能有利于复习。