CODEWITHSUNDEEP
excelopenxml-sdk
user3035874
user3035874

Reputation:

Adding data to existing excel using open xml sdk 2.5

I tried to read the existing excel file and copied to another path, and then i tried to insert data to the file i cloned. But I can' insert data. I don't know where I made mistake. But Here I've given my code.

Error occured in the line sheetData.InsertAt<Row>(row,++rowIndex);. Please help me out.

Package spreadsheetPackage = Package.Open(destinationFile, FileMode.Open, FileAccess.ReadWrite);

                using (var document = SpreadsheetDocument.Open(spreadsheetPackage))
                {
                    foreach (System.Data.DataTable table in ds.Tables)
                    {

var workbookPart = document.WorkbookPart;
                            var workbook = workbookPart.Workbook;

                            var sheet = workbookPart.Workbook.Descendants<Sheet>().FirstOrDefault();
                            Worksheet ws = ((WorksheetPart)(workbookPart.GetPartById(sheet.Id))).Worksheet;


                            SheetData sheetData = ws.GetFirstChild<SheetData>();


                            //Sheet sheet = sheets.FirstOrDefault();

                            if(sheet==null)
                                throw new Exception("No sheed found in the template file. Please add the sheet");

                            int rowIndex = 10, colIndex = 0;
                            bool flag = false;

                            var worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
                            var sharedStringPart = workbookPart.SharedStringTablePart;
                            var values = sharedStringPart.SharedStringTable.Elements<SharedStringItem>().ToArray();
                            var rows = worksheetPart.Worksheet.Descendants<Row>();

                            List<String> columns = new List<string>();
                            foreach (System.Data.DataColumn column in table.Columns)
                            {
                                columns.Add(column.ColumnName);
                            }

                            foreach (System.Data.DataRow dsrow in table.Rows)
                            {
                                Row row = new Row();
                                foreach (String col in columns)
                                {
                                    DocumentFormat.OpenXml.Spreadsheet.Cell cell = new Cell();
                                    cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                                    cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString());
                                    row.AppendChild<Cell>(cell);
                                }

                                sheetData.InsertAt<Row>(row,++rowIndex);
                            }

}

Upvotes: 3

Views: 7365

Answers (1)

Jeeva J
Jeeva J

Reputation: 3253

Before you append Row, you should mentioned the row index to the instance...

row.RowIndex = (UInt32)rowIndex++;

Upvotes: 2

Related Questions