none
OpenXML copying columns using SAX RRS feed

  • Question

  •  

    This question is similar to a previous I have had Here (http://social.msdn.microsoft.com/Forums/en/oxmlsdk/thread/b8ecb622-6941-47b5-a9ef-45e77a284271)

    But this time I have a deadline of today :(

    I am using SAX model to write my spreadsheets, because they will be large.  And I have to write it in-memory without it being able to unzip to the local file-system (which is write-protected on a server). 

    Previous code which is crashing:

    Public Function ExportScenarioSbomToSpreadsheet() As MemoryStream
      Dim MemoryFile       As New MemoryStream()
    
      Dim Buffers() As Byte = XLSX_BLL.GetBlankSpreadsheetBytes()
      MemoryFile.Write(Buffers, 0, Buffers.Length)
      Buffers = Nothing
    
      Dim ColumnFormats As List(Of LineFormat) = Nothing
    
      Using MyDoc As SpreadsheetDocument = SpreadsheetDocument.Open(MemoryFile, True)
        Dim WorkbookPart     As WorkbookPart  = MyDoc.WorkbookPart
        Dim WorksheetPart    As WorksheetPart = WorkbookPart.WorksheetParts.First()
        Dim OrigninalSheetId As String        = WorkbookPart.GetIdOfPart(WorksheetPart)
    
        Dim ReplacementPart   As WorksheetPart = WorkbookPart.AddNewPart(Of WorksheetPart)()
        Dim ReplacementPartId As String        = WorkbookPart.GetIdOfPart(ReplacementPart)
    
        Dim Reader As OpenXmlReader = OpenXmlReader.Create(WorksheetPart)
        Dim Writer As OpenXmlWriter = OpenXmlWriter.Create(ReplacementPart)
    
        WriteRows(Reader, Writer)
    
        Reader.Close()
        Writer.Close()
    
        Dim TestColumnOne As New Column()
        TestColumnOne.Width = 6.235 ' Genated number specific to excel.
    
        ' This causes the error!!!  It has to unzip and re-zip the .xlsx file--hard to reproduce
        '   unless there is large number of written rows and the temporary directory of a user is read-only.
        AddColumnsToSpreadsheetPart(ReplacementPart, New List(Of Column)(New Column() {TestColumnOne}))
    
        Dim sheet As Sheet = WorkbookPart.Workbook.Descendants(Of Sheet)().Where(Function(s) s.Id.Value.Equals(OrigninalSheetId)).First()
        sheet.Id.Value     = ReplacementPartId
        WorkbookPart.DeletePart(WorksheetPart)
      End Using
    
      Return MemoryFile
    End Function
    
    Public Sub AddColumnsToSpreadsheetPart(worksheetPart As WorksheetPart, columnWidths As List(Of Column))
      If columnWidths Is Nothing Then
        Exit Sub
      End If
    
      Dim Columns As Columns = worksheetPart.Worksheet.GetFirstChild(Of Columns)()
    
      If Columns Is Nothing Then
        Columns = New Columns()
        worksheetPart.Worksheet.Append(Columns)
      End If
    
      Columns.Clear()
    
      For Index As Integer = 0 To columnWidths.Count - 1
        If ColumnWidths(Index) IsNot Nothing Then
          If ColumnWidths(Index).Width < 1 Then
            ColumnWidths(Index).CustomWidth = False
    	ColumnWidths(Index).Width       = 0.0
          End If
    
          Columns.Append(ColumnWidths(Index))
        End If
      Next
    End Sub
    
    Private Function WriteRows(reader As OpenXmlReader, writer As OpenXmlWriter) As List(Of List(Of String))
      While reader.Read()
        If reader.ElementType Is GetType(SheetData) Then
          If reader.IsEndElement Then
            Continue While
          End If
    
          writer.WriteStartElement(New SheetData())
    
          WriteFirstRow(writer)
          WriteBodyRows(writer)
    
          writer.WriteEndElement()
        Else
          If reader.IsStartElement Then
            writer.WriteStartElement(reader)
          ElseIf reader.IsEndElement Then
            writer.WriteEndElement()
          End If
        End If
      End While
    
      Return ReturnStrings
    End Function
    
    Private Sub WriteFirstRow(writer As OpenXmlWriter)
      writer.WriteStartElement(New Row())
    
      For ColumnIndex As Integer = 0 To 100
        WriteCell(writer, 0, "Header: " & ColumnIndex.ToString())
      Next
    
      writer.WriteEndElement()  
    End Sub
    
    Private Sub WriteFirstRow(writer As OpenXmlWriter)
      writer.WriteStartElement(New Row())
    
      For RowIndex As Integer = 0 To 144000
        For ColumnIndex As Integer = 0 To 100
          WriteCell(writer, 0, "Header: " & ColumnIndex.ToString())
        Next
      Next
    
      writer.WriteEndElement()
    End Sub
    
    Private Sub WriteCell(writer As OpenXmlWriter, columnIndex As Integer, rowIndex As Integer, value As String)
      Dim Cell As New Cell()
    
      ' Protected customer code...  Spits out "A1", "A2", ..., "B1", "B2", ..., "ZZZ999"+ based on Column and Row Index
      Cell.CellReference = GetCellName(columnIndex, rowIndex)
    
      Cell.DataType      = New EnumValue(Of CellValues)(CellValues.String)
      Cell.CellValue     = New CellValue(value)
    
      writer.WriteElement(Cell)
    End Sub
    
    
    


    I need some way of writing the Columns while writing the rows using the SAX method (Reading from one spreadsheet and writing it to a new spreadsheet with added rows).  I can add the Columns to the Blank.xlsx file being used as the reader.  But the loop below doesn't copy those columns in.

     

     While reader.Read()
      If reader.ElementType Is GetType(SheetData) Then
        If reader.IsEndElement Then
        Continue While
       End If
    
       writer.BeginSpreadsheet()
    
       WriteBody(writer)
    
       writer.EndSpreadsheet()
      Else
        If reader.IsStartElement Then
          writer.WriteStartElement(reader)
        ElseIf reader.IsEndElement Then
          writer.WriteEndElement()
        End If
      End If
     End While
    
    
    I don't know what to do inside of
    If reader.ElementType Is GetType(GetColumns) Then<br/> ' How would I copy the columns?<br/>End If
    

    I had a deadline today with only this functionality missing.  So I am hoping to have this figured out by tomorrow.  But for the life of me, I can't figure it out.  Tried several things with writing elements and such.  I just want the whole block copied over.

    I would even be OK with just taking the columns and manually writing them like the Rows are being written.  But I haven't been able to figure that one out either.

     

    Hopefully Helpful,

    TamusJRoyce


    Tuesday, June 28, 2011 6:25 PM

Answers

  • Private Sub WriteRows(reader As OpenXmlReader, writer As OpenXmlWriter) As List(Of List(Of String))
     While reader.Read()
     If reader.ElementType Is GetType(SheetData) Then
      If reader.IsEndElement Then
      Continue While
      End If
    
      writer.WriteStartElement(New SheetData())
    
      WriteFirstRow(writer)
      WriteBodyRows(writer)
    
      ' This was my error!!! It belongs after the columns...
      ' writer.WriteEndElement()
    
      Dim ColumnFormats As New List(Of Column)(New Column() {New Column(), New Column(), New Column()})
      ColumnFormats(0).Width = 6.235
    	 ColumnFormats(0).CustomWidth = True
      ColumnFormats(1).Width = 4.234
    	 ColumnFormats(1).CustomWidth = True
      ColumnFormats(2).Width = 2.233
    	 ColumnFormats(2).CustomWidth = True
    
      Dim Columns As New Columns()
      For ColumnIndex As Integer = 0 To ColumnFormats.Count - 1
      Columns.Append(ColumnFormats(ColumnIndex))
      Next
    
      writer.WriteStartElement(Columns)
    
      For Each Column As Column In Columns
      writer.WriteElement(Column)
      Next
    
      writer.WriteEndElement()
    
      ' This fixed it... The columns were being written after the SheetData.
      '  And not inside the SheetData.
      writer.WriteEndElement()
     Else If Not reader.ElementType Is GetType(Columns) AndAlso Not reader.ElementType Is GetType(Column) Then
      If reader.IsStartElement Then
     writer.WriteStartElement(reader)
      ElseIf reader.IsEndElement Then
     writer.WriteEndElement()
      End If
     End If
     End While
    End Sub

    • Marked as answer by TamusJRoyce Tuesday, June 28, 2011 8:40 PM
    Tuesday, June 28, 2011 8:40 PM

All replies

  • The code I am currently trying is:

     

    Private Sub WriteRows(reader As OpenXmlReader, writer As OpenXmlWriter) As List(Of List(Of String))
     While reader.Read()
      If reader.ElementType Is GetType(SheetData) Then
       If reader.IsEndElement Then
        Continue While
       End If
    
       writer.WriteStartElement(New SheetData())
    
       WriteFirstRow(writer)
       WriteBodyRows(writer)
    
       writer.WriteEndElement()
    
       Dim ColumnFormats As New List(Of Column)(New Column() {New Column(), New Column(), New Column()})
       ColumnFormats(0).Width  = 6.235
    	 ColumnFormats(0).CustomWidth = True
       ColumnFormats(1).Width  = 4.234
    	 ColumnFormats(1).CustomWidth = True
       ColumnFormats(2).Width  = 2.233
    	 ColumnFormats(2).CustomWidth = True
    
       Dim Columns As New Columns()
       For ColumnIndex As Integer = 0 To ColumnFormats.Count - 1
        Columns.Append(ColumnFormats(ColumnIndex))
       Next
    
       writer.WriteStartElement(Columns)
    
       For Each Column As Column In Columns
        writer.WriteElement(Column)
       Next
    
       writer.WriteEndElement()
      Else If Not reader.ElementType Is GetType(Columns) AndAlso Not reader.ElementType Is GetType(Column) Then
       If reader.IsStartElement Then
      writer.WriteStartElement(reader)
       ElseIf reader.IsEndElement Then
      writer.WriteEndElement()
       End If
      End If
     End While
    End Sub
    
    

    But it breaks, saying the document is corrupt.  I think the columns and rows are together in the same .xml file.  sheets.xml?  Inside the .xslx file, opened inside an zip/unzip program (7-zip).

     


    Tuesday, June 28, 2011 8:03 PM
  • Private Sub WriteRows(reader As OpenXmlReader, writer As OpenXmlWriter) As List(Of List(Of String))
     While reader.Read()
     If reader.ElementType Is GetType(SheetData) Then
      If reader.IsEndElement Then
      Continue While
      End If
    
      writer.WriteStartElement(New SheetData())
    
      WriteFirstRow(writer)
      WriteBodyRows(writer)
    
      ' This was my error!!! It belongs after the columns...
      ' writer.WriteEndElement()
    
      Dim ColumnFormats As New List(Of Column)(New Column() {New Column(), New Column(), New Column()})
      ColumnFormats(0).Width = 6.235
    	 ColumnFormats(0).CustomWidth = True
      ColumnFormats(1).Width = 4.234
    	 ColumnFormats(1).CustomWidth = True
      ColumnFormats(2).Width = 2.233
    	 ColumnFormats(2).CustomWidth = True
    
      Dim Columns As New Columns()
      For ColumnIndex As Integer = 0 To ColumnFormats.Count - 1
      Columns.Append(ColumnFormats(ColumnIndex))
      Next
    
      writer.WriteStartElement(Columns)
    
      For Each Column As Column In Columns
      writer.WriteElement(Column)
      Next
    
      writer.WriteEndElement()
    
      ' This fixed it... The columns were being written after the SheetData.
      '  And not inside the SheetData.
      writer.WriteEndElement()
     Else If Not reader.ElementType Is GetType(Columns) AndAlso Not reader.ElementType Is GetType(Column) Then
      If reader.IsStartElement Then
     writer.WriteStartElement(reader)
      ElseIf reader.IsEndElement Then
     writer.WriteEndElement()
      End If
     End If
     End While
    End Sub

    • Marked as answer by TamusJRoyce Tuesday, June 28, 2011 8:40 PM
    Tuesday, June 28, 2011 8:40 PM