none
Corrupt file generated albeit Open XML SDK Productivity Tool showing no errors RRS feed

  • Question

  • Hi Guys,

    I tear my hair out. What is wrong with my file? MS Excel manages to repair it (altough still error message ) and its fine but Open Office fails and data such aus "Auftragsnummer" is missing.

    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.
    Monday, July 19, 2010 5:44 PM

Answers

  • Hi Ji,

     

    as I said, it is NOT about the MS excel it is about the coding I am doing. In my case, I simply forgot to define the data types of cells AND assigned the values wrong. here is the answer:

    For a normal number based value you can simply just set the Text in the CellValue but with those other types of values you need to set the DataType and/or StyleIndex of the Cell too.  Not to mention string values are usually stored in the SharedStringTable.  Just reflect (in te Open XML SDK 2.0 Productivity Tool) a spreadsheet you know has the type of values you're trying to store and see how you should create them.

    Here's all you'd need to do to set a normal numeric value:
    cellD4.CellValue = new CellValue { Text = "555" };
    Or, in VB:

    cellD4.CellValue = New CellValue() With { _
    .Text = "555" _
    }
    • Marked as answer by polynaux2010 Friday, July 30, 2010 9:06 AM
    Friday, July 30, 2010 9:06 AM

All replies

  • I noticed that when I take all the insert cell stuff out the file is fine and not corrupted BUT as soon as I put the first insert in the file gets corrupted:

     

     Dim cellD4 As Cell = InsertCellInWorksheet("D", 4, worksheetPart)
               

                    '' Set the value of cells.
                    cellD4.CellValue = New CellValue(CDate(System.DateTime.Today))

    Tuesday, July 20, 2010 9:31 AM
  • this is the instercell function as provided by 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
    
    Wednesday, July 21, 2010 5:01 AM
  • Hello,

    If Excel reports it corruption, you can use the OpenXMLSDK tool to open your file and click the Validate button in the toolbar. The tool will give us where Excel complains the file.

    Best regards,
    Ji Zhou - MSFT
    Microsoft Online Community Support


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Wednesday, July 21, 2010 12:31 PM
    Moderator
  • Thanks dude :) I did that already (refer to title of this topic"...albeit Open XML SDK Productivity Tool showing no errors". The validation works. Its my code that causes the error.

    Wednesday, July 21, 2010 1:01 PM
  • Hello,

    Sorry for missing that information from title. Based on my understanding, the validation from Excel and the Productivity Tool should have the same effect.

    So it is strange that Producitity Tool does not report the error while Excel complains the file. Can we see your generated file? You can share it through www.skydrive.com

     

    Best regards,
    Ji Zhou - MSFT
    Microsoft Online Community Support

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Monday, July 26, 2010 10:32 AM
    Moderator
  • Hi Ji,

     

    as I said, it is NOT about the MS excel it is about the coding I am doing. In my case, I simply forgot to define the data types of cells AND assigned the values wrong. here is the answer:

    For a normal number based value you can simply just set the Text in the CellValue but with those other types of values you need to set the DataType and/or StyleIndex of the Cell too.  Not to mention string values are usually stored in the SharedStringTable.  Just reflect (in te Open XML SDK 2.0 Productivity Tool) a spreadsheet you know has the type of values you're trying to store and see how you should create them.

    Here's all you'd need to do to set a normal numeric value:
    cellD4.CellValue = new CellValue { Text = "555" };
    Or, in VB:

    cellD4.CellValue = New CellValue() With { _
    .Text = "555" _
    }
    • Marked as answer by polynaux2010 Friday, July 30, 2010 9:06 AM
    Friday, July 30, 2010 9:06 AM