none
Open XML | How to add values to existing worksheet RRS feed

  • Question

  • Hi

    I have a spreadsheet with two tabs "Requests" and "Results". Functionality I want to achieve is read data from "Requests" worksheets, process the data and add results of processing in "Results" worksheet.

    I have below code but i dont see any data being added to the worksheet

     using (SpreadsheetDocument ssDoc =
                   SpreadsheetDocument.Open(filename, true))
                {
                    WorkbookPart workbookPart = ssDoc.WorkbookPart;
                    IEnumerable<Sheet> sheets = ssDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
                    string relationshipId = sheets.FirstOrDefault(s => string.Compare(s.Name, "Results", true) == 0).Id;
                    WorksheetPart worksheetPart = (WorksheetPart)ssDoc.WorkbookPart.GetPartById(relationshipId);
                    Worksheet workSheet = new Worksheet();
                    SheetData sheetData = new SheetData();
                    DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                    List<String> columns = new List<string>();
                    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);
                    }
                    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);
                    }
                    ssDoc.WorkbookPart.Workbook.Save();
                    ssDoc.Close();
                    
                }

    Can anybody please help me in correcting this issue?

    Regards,

    RK


    Radhakrishna

    Wednesday, May 15, 2013 2:34 AM

Answers

  • ///<summary>
            ///Given a spreadsheet reference and text, writes any value to the row and column specified.
            ///</summary>
            public static Cell PasteValue(SpreadsheetDocument spreadsheet, WorksheetPart worksheetPart, string column, uint row, string value, CellValues type, SpreadsheetStyle style)
            {
                //Get the cell, or insert a new one
                Cell cell = FindCell(column, row, worksheetPart);
    
                //If shared text then get the SharedStringTablePart. 
                //Create one in Excel by adding text, saving, then removing the text again.
                if (type == CellValues.SharedString)
                {
                    SharedStringTablePart shareStringPart = spreadsheet.WorkbookPart.SharedStringTablePart;
                    if (shareStringPart == null) throw new ApplicationException("Template does not contain a shared string table.");
    
                    // Insert the text into the SharedStringTablePart.
                    uint index = SpreadsheetWriter.GetSharedStringItem(value, shareStringPart);
    
                    // Set the value of cell.
                    cell.CellValue = new CellValue(index.ToString());
                    cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                }
                else
                {
                    cell.CellValue = new CellValue(value);
                    cell.DataType = new EnumValue<CellValues>(type);
                }
    
                //Set the style of the cell
                if (style != null) cell.StyleIndex = GetStyleIndex(style, SpreadsheetReader.GetWorkbookStyles(spreadsheet));
    
                return cell;
            }


    Regards,
    Bubu
    http://zsvipullo.blogspot.it

    Please mark my answer if it helped you, I would greatly appreciate it.

    Wednesday, May 15, 2013 12:33 PM
  • Hi Bubu,

    Thanks for the response, could you please provide me implementation details on FindCell() method.

    In meanwhile, I was able to add data in existing worksheet and below is the code

       WorkbookPart workbookPart = spreadSheet.WorkbookPart;
                    IEnumerable<Sheet> sheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
                    string relationshipId = sheets.FirstOrDefault(s => string.Compare(s.Name, "Results", 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>();
                    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);
                    }
                    
                    //sheetPart.Worksheet.Save(); 
                    worksheetPart.Worksheet.ReplaceChild<SheetData>(sheetData, worksheetPart.Worksheet.Elements<SheetData>().FirstOrDefault());
                    spreadSheet.Close();

    Confusing part of the code is below

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

    I am not able to understand why we need replace existing sheetData object with new one?

    I tried to get the sheetData object from the existing worksheet like below

    var sheetData = workSheetPart.Worksheet.Elements<SheetData>().FirstOrDefault();

    Update it with new rows and cells but no data was added to "Results" sheet.

    Regards,

    RK


    Radhakrishna


    • Edited by RK Naik Wednesday, May 15, 2013 2:22 PM Updated description of the issue
    • Marked as answer by Quist ZhangModerator Tuesday, June 4, 2013 8:28 AM
    Wednesday, May 15, 2013 2:19 PM

All replies

  • ///<summary>
            ///Given a spreadsheet reference and text, writes any value to the row and column specified.
            ///</summary>
            public static Cell PasteValue(SpreadsheetDocument spreadsheet, WorksheetPart worksheetPart, string column, uint row, string value, CellValues type, SpreadsheetStyle style)
            {
                //Get the cell, or insert a new one
                Cell cell = FindCell(column, row, worksheetPart);
    
                //If shared text then get the SharedStringTablePart. 
                //Create one in Excel by adding text, saving, then removing the text again.
                if (type == CellValues.SharedString)
                {
                    SharedStringTablePart shareStringPart = spreadsheet.WorkbookPart.SharedStringTablePart;
                    if (shareStringPart == null) throw new ApplicationException("Template does not contain a shared string table.");
    
                    // Insert the text into the SharedStringTablePart.
                    uint index = SpreadsheetWriter.GetSharedStringItem(value, shareStringPart);
    
                    // Set the value of cell.
                    cell.CellValue = new CellValue(index.ToString());
                    cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
                }
                else
                {
                    cell.CellValue = new CellValue(value);
                    cell.DataType = new EnumValue<CellValues>(type);
                }
    
                //Set the style of the cell
                if (style != null) cell.StyleIndex = GetStyleIndex(style, SpreadsheetReader.GetWorkbookStyles(spreadsheet));
    
                return cell;
            }


    Regards,
    Bubu
    http://zsvipullo.blogspot.it

    Please mark my answer if it helped you, I would greatly appreciate it.

    Wednesday, May 15, 2013 12:33 PM
  • Hi Bubu,

    Thanks for the response, could you please provide me implementation details on FindCell() method.

    In meanwhile, I was able to add data in existing worksheet and below is the code

       WorkbookPart workbookPart = spreadSheet.WorkbookPart;
                    IEnumerable<Sheet> sheets = spreadSheet.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
                    string relationshipId = sheets.FirstOrDefault(s => string.Compare(s.Name, "Results", 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>();
                    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);
                    }
                    
                    //sheetPart.Worksheet.Save(); 
                    worksheetPart.Worksheet.ReplaceChild<SheetData>(sheetData, worksheetPart.Worksheet.Elements<SheetData>().FirstOrDefault());
                    spreadSheet.Close();

    Confusing part of the code is below

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

    I am not able to understand why we need replace existing sheetData object with new one?

    I tried to get the sheetData object from the existing worksheet like below

    var sheetData = workSheetPart.Worksheet.Elements<SheetData>().FirstOrDefault();

    Update it with new rows and cells but no data was added to "Results" sheet.

    Regards,

    RK


    Radhakrishna


    • Edited by RK Naik Wednesday, May 15, 2013 2:22 PM Updated description of the issue
    • Marked as answer by Quist ZhangModerator Tuesday, June 4, 2013 8:28 AM
    Wednesday, May 15, 2013 2:19 PM