none
Writing data in chunks to excel using OpenXml and appending to the existing rows RRS feed

  • Question

  •  public void ExportDataSet(DataSet ds, string destination)
            {

                using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(destination, true))
                {
                    WorkbookPart workbookPart = spreadSheet.WorkbookPart;
                    IEnumerable<Sheet> sheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
                    string relationshipId = sheets.FirstOrDefault(s => string.Compare(s.Name, "Raw data", true) == 0).Id;
                    WorksheetPart worksheetPart = (WorksheetPart)spreadSheet.WorkbookPart.GetPartById(relationshipId);
                    SheetData sheetData = new SheetData();

                    DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                    List<String> columns = new List<string>();
                    System.Data.DataTable dt = ds.Tables[0];
                    foreach (DataColumn column in dt.Columns)
                    {
                        columns.Add(column.ColumnName);
                        DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                        cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                        cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
                        headerRow.AppendChild(cell);
                    }
                    sheetData.AppendChild(headerRow);                
                    foreach (DataRow dsrow in dt.Rows)
                    {                  

                        DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                        foreach (String col in columns)
                        {
                            DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                            cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                            cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString());
                            newRow.AppendChild(cell);

                        }  
                         sheetData.AppendChild(newRow);           


                    }


                    worksheetPart.Worksheet.ReplaceChild<SheetData>(sheetData, worksheetPart.Worksheet.Elements<SheetData>().FirstOrDefault());         


                }
            }

    Using the above code i can only see the last set of data rows..Is it possible to append rows to the older set.

                                                                                                        
    Tuesday, September 8, 2015 10:04 AM

Answers

  • Hi sudhaMaroth,

    >>  

    SheetData sheetData = new SheetData();

    ...

         worksheetPart.Worksheet.ReplaceChild<SheetData>(sheetData, worksheetPart.Worksheet.Elements<SheetData>().FirstOrDefault());         

    <<

    Based on the code above, you were creating a new SheetData class and replace the old with new one. To append the data to the exists spreadsheets, we can find the SheetData class via code below:

     SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

    Then we can append the row data via code below:

    sheetData.AppendChild(newRow)

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, September 9, 2015 5:43 AM
    Moderator

All replies

  • Hi sudhaMaroth,

    >>  

    SheetData sheetData = new SheetData();

    ...

         worksheetPart.Worksheet.ReplaceChild<SheetData>(sheetData, worksheetPart.Worksheet.Elements<SheetData>().FirstOrDefault());         

    <<

    Based on the code above, you were creating a new SheetData class and replace the old with new one. To append the data to the exists spreadsheets, we can find the SheetData class via code below:

     SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

    Then we can append the row data via code below:

    sheetData.AppendChild(newRow)

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, September 9, 2015 5:43 AM
    Moderator
  • It worked Fei Xue. Thanks a lot
    Wednesday, September 9, 2015 6:40 AM