使用OpenXML创建Excel文件，内容不可读

2 回答

• 0

  不确定为什么上面的代码不起作用，但是这段代码确实如此：http://blogs.msdn.com/b/chrisquon/archive/2009/07/22/creating-an-excel-spreadsheet-from-scratch-using-openxml.aspx

  Try
          mConn = New SqlConnection(My.Settings.SqlConnection)
          cmd = New SqlCommand(mstrSQL, mConn)
          cmd.CommandType = CommandType.StoredProcedure
          cmd.CommandTimeout = 120
          da = New SqlDataAdapter(cmd)
          da.Fill(ds)
          table = ds.Tables(0)
  
          If File.Exists(fileName) Then
              File.Delete(fileName)
          End If
  
          ' create the workbook
          spreadSheet = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook)
          spreadSheet.AddWorkbookPart()
          spreadSheet.WorkbookPart.Workbook = New Workbook()
          spreadSheet.WorkbookPart.AddNewPart(Of WorksheetPart)()
          spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet = New Worksheet()
  
          ' create sheet data
          spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.AppendChild(New SheetData())
  
          ' create header row
          For Each column As DataColumn In table.Columns
              columns.Add(column.ColumnName)
              Dim cell = New Cell()
              cell.DataType = CellValues.String
              cell.CellValue = New CellValue(column.ColumnName)
              headerRow.AppendChild(cell)
          Next
          spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().AppendChild(headerRow)
  
          ' create data rows
          For Each dsrow As DataRow In table.Rows
              Dim newRow = New Row()
              For Each col As String In columns
                  Dim cell = New Cell()
                  cell.DataType = CellValues.String
                  cell.CellValue = New CellValue(dsrow(col).ToString())
                  newRow.AppendChild(cell)
              Next
              spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.First().AppendChild(newRow)
          Next
  
          ' save worksheet
          spreadSheet.WorkbookPart.WorksheetParts.First().Worksheet.Save()
  
          ' create the worksheet to workbook relation
          spreadSheet.WorkbookPart.Workbook.AppendChild(New Sheets())
          Dim s = New Sheet()
          s.Id = spreadSheet.WorkbookPart.GetIdOfPart(spreadSheet.WorkbookPart.WorksheetParts.First())
          s.SheetId = 1
          s.Name = "test"
          spreadSheet.WorkbookPart.Workbook.GetFirstChild(Of Sheets)().AppendChild(s)
  
          ' save workbook
          spreadSheet.WorkbookPart.Workbook.Save()
  
      Catch ex As Exception
          'do stuff
      Finally
          spreadSheet.Close()
          spreadSheet.Dispose()
  
      End Try
  

  回复于 2024-04-29T04:18:41+08:00
• 0

  感谢Mike的代码/答案 .

  基于他的回答，我不得不改变它以使其工作，所以想要把它放下以防其他人需要它 .

  显然，您可以将任何内容添加到您想要的Excel文档中，但为了使代码正常工作，我只使用了一个字符串（） .

  Dim filepath As String = My.Application.Info.DirectoryPath & "\Cust.xlsx"
  
              ' Create a spreadsheet document by supplying the filepath.
              ' By default, AutoSave = true, Editable = true, and Type = xlsx.
              Dim spreadsheetDocument As SpreadsheetDocument =
              spreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook)
  
              ' Add a WorkbookPart to the document.
              Dim workbookpart As WorkbookPart = spreadsheetDocument.AddWorkbookPart
              workbookpart.Workbook = New Workbook
  
              ' Add a WorksheetPart to the WorkbookPart.
              Dim worksheetPart As WorksheetPart = workbookpart.AddNewPart(Of WorksheetPart)()
              worksheetPart.Worksheet = New Worksheet(New SheetData())
  
              ' Add Sheets to the Workbook.
              Dim sheets As Sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(Of Sheets)(New Sheets())
  
              ' Append a new worksheet and associate it with the workbook.
              Dim sheet As Sheet = New Sheet
              sheet.Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart)
              sheet.SheetId = 1
              sheet.Name = "Sheet1"
              sheets.Append(sheet)
  
              Dim HeaderString As String = "Customer ID, Name, Address, Address2, Address3, Address4, Address5, Contact Number, EmailAddress, Alt Contact, Notes"
  
              ' Split string based on spaces.
              Dim HeaderWords As String() = HeaderString.Split(New Char() {","c})
  
              'create row
              Dim Row = New Row()
  
              For Each Header In HeaderWords
                  'Columns.Add(Column.ColumnName)
                  Dim cell = New Cell()
                  cell.DataType = CellValues.String
                  cell.CellValue = New CellValue(Trim(Header))
                  Row.AppendChild(cell)
  
              Next
              spreadsheetDocument.WorkbookPart.WorksheetParts.First().Worksheet.First().AppendChild(Row)
  
              Dim DataString As String = "1,2,3,4,5,6,7,8,9,10,11"
  
              ' Split string based on spaces.
              Dim DataWords As String() = DataString.Split(New Char() {","c})
  
              ' create data rows
              For Each DataPart In DataWords
                  Dim newRow = New Row()
                  For Each col As String In HeaderWords
                      Dim cell = New Cell()
                      cell.DataType = CellValues.String
                      cell.CellValue = New CellValue(DataPart.ToString())
                      newRow.AppendChild(cell)
                  Next
                  spreadsheetDocument.WorkbookPart.WorksheetParts.First().Worksheet.First().AppendChild(newRow)
              Next
  
              WorkbookPart.Workbook.Save()
  
              ' Close the document.
              SpreadsheetDocument.Close()
  

  回复于 2024-04-29T04:18:41+08:00