Existing spreadsheet document file create new cell in c#

Kalyani Shevale

7y3.4kLearn iOS Programming

I have open existing spreadsheet document file. then this file inserts some cell in the particular index value.

how to insert the new cell in this open file.

DataTable dataTable = new DataTable();
using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(srcFile, false))
{
WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
IEnumerable sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild().Elements();
string relationshipId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild();
IEnumerable rows = sheetData.Descendants();
foreach (Cell cell in rows.ElementAt(0))
{
dataTable.Columns.Add(GetCellValue(spreadSheetDocument, cell));
}
ArrayList list = new ArrayList();
foreach (Row row in rows)
{
DataRow dataRow = dataTable.NewRow();
for (int i = 0; i < 1; i++)
{
list.Add(GetCellValue(spreadSheetDocument, row.Descendants().ElementAt(i)));
}
}
Row row1 = new Row() { RowIndex = 2U, Spans = new ListValue() };
Cell cell1 = new Cell()
{
CellReference = "A52",
DataType = CellValues.String,
CellValue = new CellValue("Microsoft")
};
Row r = new Row();
r.Append(cell1);
sheetData.AppendChild(r);
worksheetPart.Worksheet.Append(sheetData);
worksheetPart.Worksheet.Save();
}

problem in last 5 line how to insert new cell value in this existing file in c#

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Laxmidhar Sahoo7y ago
Accepted answer
hi the bellow code is runing but data is not saved .Please look it and convey me
private void cellchanging(string srcFile)
       {
           DataTable dataTable = new DataTable();
           using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(srcFile, true))
           {
               WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
               IEnumerable sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild().Elements();
               string relationshipId = sheets.First().Id.Value;
               WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
               DocumentFormat.OpenXml.Spreadsheet.Worksheet workSheet = worksheetPart.Worksheet;
               SharedStringItem sharedStringItem = GetSharedStringItemById(workbookPart, 4);

               SheetData sheetdata = worksheetPart.Worksheet.GetFirstChild();
               DocumentFormat.OpenXml.Spreadsheet.Row r = new DocumentFormat.OpenXml.Spreadsheet.Row();
               DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
               int RowIndexer = 1;

               r.RowIndex = (UInt32)RowIndexer;
               cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
               cell.CellReference = "C" + RowIndexer;
               cell.DataType = CellValues.InlineString;
               cell.InlineString = new InlineString(new Text("Tesing"));
               CellValue value = new CellValue("Tesing");
               cell.CellValue = value;
               cell.CellValue.Text = "Tesing";
               r.AppendChild(cell);
               sheetdata.Append(r);
               worksheetPart.Worksheet.Save();
               spreadSheetDocument.Save();



           }

       }
Laxmidhar Sahoo7y ago
1. public static SharedStringItem GetSharedStringItemById(WorkbookPart workbookPart, int id)
2. {
3. return workbookPart.SharedStringTablePart.SharedStringTable.Elements().ElementAt(id);
4. }

Kalyani Shevale7y ago

@laxmidhar

DocumentFormat.OpenXml

Laxmidhar Sahoo7y ago

what is the dll used for SpreadsheetDocument class

Jignesh Kumar7y ago

Hi Kalyani,

Please find below link

https://social.msdn.microsoft.com/Forums/office/en-US/bfbbe5bc-390f-4c6d-a408-d55ce52147cb/open-xml-how-to-add-values-to-existing-worksheet?forum=oxmlsdk

https://docs.microsoft.com/en-us/office/open-xml/how-to-insert-text-into-a-cell-in-a-spreadsheet