Re: SDK InsertCellInWorksheet causes corruption - SpreadsheetML - Formats

Hi Guys,

I noticed that when I take all the insert cell stuff out the file, it is fine and not corrupted (ok, but useless) BUT as soon as I put the first insert in the file gets corrupted:

What do I do wrong?

For Each Row As DataRow In TempTable.Rows

        Dim Details As New Data.DataTable
        Details = DA_Layer.GetAlleWareneingangsAttribute_InputIsWENummer(Row.Item("Wareneingangsnummer").ToString)

        'Access the main Workbook part, which contains all references. 
        Dim workbookPart As WorkbookPart = myWorkbook.WorkbookPart
        'Get the first worksheet.  
        Dim worksheetPart As WorksheetPart = workbookPart.WorksheetParts.ElementAt(i)



        Dim cellD4 As Cell = InsertCellInWorksheet("D", 4, worksheetPart)
        Dim cellD5 As Cell = InsertCellInWorksheet("D", 5, worksheetPart)
        Dim cellD6 As Cell = InsertCellInWorksheet("D", 6, worksheetPart)
        Dim cellD7 As Cell = InsertCellInWorksheet("D", 7, worksheetPart)
        Dim cellD8 As Cell = InsertCellInWorksheet("D", 8, worksheetPart)
        Dim cellD9 As Cell = InsertCellInWorksheet("D", 9, worksheetPart)
        Dim cellD10 As Cell = InsertCellInWorksheet("D", 10, worksheetPart)


        ' Set the value of cells.
        cellD4.CellValue = New CellValue(CDate(System.DateTime.Today))
        cellD5.CellValue = New CellValue(Details.Rows(0).Item("Charge").ToString())
        cellD6.CellValue = New CellValue(CDate(Details.Rows(0).Item("WareneingangsDatum").ToString()))
        cellD7.CellValue = New CellValue(CStr(TempTable.Rows(0).Item("Auftragsnummer").ToString()))
        cellD8.CellValue = New CellValue(CStr(Details.Rows(0).Item("Werkstoffbezeichnung").ToString()))
        cellD9.CellValue = New CellValue(CStr(TempTable.Rows(0).Item("Zeichnungsnummer").ToString() + TempTable.Rows(0).Item("IndexDerZeichnung").ToString()))
        cellD10.CellValue = New CellValue(TempTable.Rows(0).Item("BestellteStueckzahlDesKunden").ToString())
        i = i + 1
      Next
      Response.ContentType = "application/vnd.ms-excel"
      Response.AddHeader("Content-Disposition", String.Format("attachment;filename={0}", "Begleitscheine_" + LabelAuftragsnummer.Text + ".xlsx"))
      myWorkbook.Close()
      ms.WriteTo(Response.OutputStream)
      Response.Flush()
      Response.End()
    End Using

Moreoever, how can I start with the first sheet and not the second sheet? - it seems the first is skipped.

From the SDK

Private Function InsertCellInWorksheet(ByVal columnName As String, ByVal rowIndex As UInteger, ByVal worksheetPart As WorksheetPart) As Cell
    Dim worksheet As Worksheet = worksheetPart.Worksheet
    Dim sheetData As SheetData = worksheet.GetFirstChild(Of SheetData)()
    Dim cellReference As String = (columnName + rowIndex.ToString())

    ' If the worksheet does not contain a row with the specified row index, insert one.
    Dim row As Row
    If (sheetData.Elements(Of Row).Where(Function(r) r.RowIndex.Value = rowIndex).Count() <> 0) Then
      row = sheetData.Elements(Of Row).Where(Function(r) r.RowIndex.Value = rowIndex).First()
    Else
      row = New Row()
      row.RowIndex = rowIndex
      sheetData.Append(row)
    End If

    ' If there is not a cell with the specified column name, insert one.
    If (row.Elements(Of Cell).Where(Function(c) c.CellReference.Value = columnName + rowIndex.ToString()).Count() > 0) Then
      Return row.Elements(Of Cell).Where(Function(c) c.CellReference.Value = cellReference).First()
    Else
      ' Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
      Dim refCell As Cell = Nothing
      For Each cell As Cell In row.Elements(Of Cell)()
        If (String.Compare(cell.CellReference.Value, cellReference, True) > 0) Then
          refCell = cell
          Exit For
        End If
      Next

      Dim newCell As Cell = New Cell
      newCell.CellReference = cellReference

      row.InsertBefore(newCell, refCell)
      worksheet.Save()

      Return newCell
    End If
End Function

Well to solve your first problem (choosing the sheet you want) try this:
// Find the sheet with the supplied name, and then use that Sheet object
// to retrieve a reference to the appropriate worksheet.
Sheet theSheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).FirstOrDefault();

//Get the relevant WorksheetPart
WorksheetPart wsPart = (WorksheetPart)(spreadsheetDocument.WorkbookPart.GetPartById(theSheet.Id));

And here it is converted to VB (no guarantees it works):

' Find the sheet with the supplied name, and then use that Sheet object
' to retrieve a reference to the appropriate worksheet.
Dim theSheet As Sheet = workbookPart.Workbook.Descendants(Of Sheet)().Where(Function(s) s.Name = sheetName).FirstOrDefault()

'Get the relevant WorksheetPart
Dim wsPart As WorksheetPart = DirectCast(spreadsheetDocument.WorkbookPart.GetPartById(theSheet.Id), WorksheetPart)

I will follow up with your other issue in another post