Monday, February 11, 2013

OpenXML-Update excel cell value

Code snippet for helping to update the specific cell in excel using the OpenXML SDK 2.0. When you updates the specific cell make sure to test with isNull or Empty otherwise write some default value in the sell and then use the below code to update. Because OpenXML ignores the empty cell.

 using (SpreadsheetDocument document = SpreadsheetDocument.Open("D:\\template.xlsx", true))
                WorkbookPart wbPart = document.WorkbookPart;
                Sheet theSheet = wbPart.Workbook.Descendants().
                Where(s => s.Name == "Sheet1").FirstOrDefault();
                Worksheet ws = ((WorksheetPart)(wbPart.GetPartById(theSheet.Id))).Worksheet;
                WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(theSheet.Id));
                SheetData sheetData = ws.GetFirstChild();
                var theRow = sheetData.Elements(). Where(r => r.RowIndex.Value == 6).FirstOrDefault();
                Cell refCell = theRow.Elements().
                Where(c => c.CellReference == "G6").FirstOrDefault();
                refCell.CellValue = new CellValue("TestComes");
                refCell.DataType = new EnumValue(CellValues.String);