Modifying existing cell value using openxmlwriter RRS feed

  • Question

  • Need to update huge spreadsheets, perhaps with 100's of thousands of rows, and many columns. Excel contain 4 worksheet and out of four,two of them contains 1,000,000 rows and 10 columns will contain upwards of 20,000,000 nodes in the worksheet part.  This presents a problem when using a DOM approach.  Both XmlDocument and LINQ to XML allocate too much memory.  It is not possible to keep the entire XML tree in memory.  so used a streaming approach and it is faster than before but still taking too much too update cell.   

    I have to find the cell data which is zero value and update its new value and  colour.I have followe above  DOM approach to get the result but its too much time consuming. How to maket it faster. 

    Here is the code i have used to ypdate the cell.

    Step1:Read the entire sheet and pulled cell having zero value and create a generic list.

    Dim zroCellList As New List(OfDocumentFormat.OpenXml.Spreadsheet.Cell)

    Step one  is ok for me.

    Step 2:



    For Each c As String In blankCellList

    rowNo =    OpenXMLHelper.GetRowIndexForOuside(c)  

    colNo = OpenXMLHelper.GetColumnIndexFromName(OpenXMLHelper.GetColumnNameByAddress(c))


    'This below line is taking too much too execute,need alternative to update the cell value. 

    DirectCast(cfDocument.WorkbookPart.GetPartById((cfDocument.WorkbookPart.Workbook.Descendants(Of Sheet)().Where(Function(s) s.Name = "SE Factors").FirstOrDefault()).Id),WorksheetPart).Worksheet.Descendants(Of Row).ElementAt(rowNo - 1).Elements(Of Cell).ElementAt(colNo).StyleIndex = YelloCellStyleIndex




    DirectCast(cfDocument.WorkbookPart.GetPartById((cfDocument.WorkbookPart.Workbook.Descendants(Of Sheet)().Where(Function(s) s.Name = "SE Factors").FirstOrDefault()).Id), WorksheetPart).Worksheet.Save()


    Friday, November 16, 2012 6:14 PM


All replies