I have a document library wuth several excel files. I want to update the contents in the excel files programmatically and save it back to document library. I am usign openxsml concept.
I am not able to make changes in the data of excel File also unable to save it Back to document Library. It would be great if somebody can help on with this.
Below is the code i have written
SPFile sourceFile = web.GetFile("/subsite/mylibrary/myfile.xlsx");
using (Stream sourceFileStream = sourceFile.OpenBinaryStream())
{
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(sourceFileStream, true))
{
IEnumerable<DocumentFormat.OpenXml.Spreadsheet.Sheet> sheets1 = spreadSheet.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Rules");
WorksheetPart worksheetPart = (WorksheetPart)spreadSheet.WorkbookPart.GetPartById(sheets1.First().Id);
DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
IEnumerable<Row> rows = worksheet.Descendants<Row>();
foreach (Row row in rows)
{
for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
{
Cell cell = row.Descendants<Cell>().ElementAt(2);
if (cell.CellValue != null)
{
cell.CellValue.Text = "Hello";
}
}
spreadSheet.WorkbookPart.Workbook.Save();
file.SaveBinary(newstream);