public static void RemoveAllRows(WorksheetPart wssheatpart) { SheetData firstChild = wssheatpart.Worksheet.GetFirstChild <SheetData>(); firstChild.RemoveAllChildren(); // sheetData.Elements<Row>() }
private void AddRow(Row row) { _rowLookupCache = null; _rowLookupCacheAssociatedSheet = null; _rowLookupCacheEnumerator = null; if (_sheetData.Elements <Row>().Count() > 0) { if (_sheetData.Elements <Row>().Last <Row>() != null && _sheetData.Elements <Row>().Last <Row>().RowIndex > row.RowIndex.Value) { // Add row at the correct position in the list List <Row> sheetRows = new List <Row>(); using (IEnumerator <Row> enumerator = GetRowEnumerator()) { bool newSheetAdded = false; while (enumerator.MoveNext()) { if (enumerator.Current.RowIndex.Value > row.RowIndex.Value && !newSheetAdded) { sheetRows.Add(row); newSheetAdded = true; } sheetRows.Add(enumerator.Current); } } _sheetData.RemoveAllChildren <Row>(); foreach (Row sheetRow in sheetRows) { _sheetData.Append(sheetRow); } } else { // Add row to the end of the row list _sheetData.Append(row); } } else { // Add first row to the end of the row list _sheetData.Append(row); } }
private void CreateFileExcel(List <GetAllQuestionByArea> _data) { using (SpreadsheetDocument doc = SpreadsheetDocument.Open(_template_path, true)) { WorkbookPart workbookPart = doc.WorkbookPart; WorksheetPart worksheetPart = workbookPart.WorksheetParts.First(); SheetData sheetData = worksheetPart.Worksheet.Elements <SheetData>().First(); //Elimina todos los datos del archivo sheetData.RemoveAllChildren(); //Crea el header el archivo Row _header = new Row(); _header.Append( ConstructCell("Id", CellValues.String), ConstructCell("Pregunta", CellValues.String), ConstructCell("Región", CellValues.String), ConstructCell("Respuesta", CellValues.String)); //Guarda los cambios del header sheetData.AppendChild(_header); //Agrega lo registros, de cada fila Row _row; for (var _i = 0; _i < _data.Count; _i++) { _row = new Row(); _row.Append( ConstructCell((_i + 1).ToString(), CellValues.String), ConstructCell(_data[_i].Question.ToString(), CellValues.String), ConstructCell(_data[_i].Regions.ToString() != "" ? _data[_i].Regions.ToString() : "Global", CellValues.String), ConstructCell(_data[_i].Answer.ToString(), CellValues.String)); sheetData.AppendChild(_row); } worksheetPart.Worksheet.Save(); } }
public static void AddDataToWorkSheet(WorksheetPart wsp, DataTable dt_data, bool includeHeaderRow, bool clearSheet, bool addTotalPriceColumn) { if (dt_data.Rows.Count > 0) { Worksheet worksheet = wsp.Worksheet; SheetData sheet_data = worksheet.GetFirstChild <SheetData>(); if (clearSheet) { sheet_data.RemoveAllChildren(); } // Add header Row headerRow = new Row(); List <String> columns = new List <String>(); foreach (DataColumn column in dt_data.Columns) { columns.Add(column.ColumnName); Cell cell = new Cell(); cell.DataType = CellValues.String; cell.CellValue = new CellValue(column.ColumnName); headerRow.AppendChild(cell); } if (includeHeaderRow) { sheet_data.AppendChild(headerRow); } // Add row data double test_number = -1; foreach (DataRow dr in dt_data.Rows) { Row row = new Row(); foreach (String col in columns) { Cell cell = new Cell(); if (Double.TryParse(dr[col].ToString(), out test_number)) { cell.DataType = CellValues.Number; } else { cell.DataType = CellValues.String; } // Check if this is a formula column if (col.Contains("(F)")) { cell.CellFormula = new CellFormula(dr[col].ToString()); } else { cell.CellValue = new CellValue(dr[col].ToString()); } row.AppendChild(cell); } sheet_data.AppendChild(row); } if (addTotalPriceColumn) { // THIS APPLIES ONLY TO FINANCE DUE LISTINGS -- WILL NEED REMOVING // // Add total row int price_idx = dt_data.Columns.IndexOf("Price"); if (price_idx != -1) { Row t_row = new Row(); foreach (String col in columns) { t_row.AppendChild(new Cell() { DataType = CellValues.String, CellValue = new CellValue(String.Empty) }); } sheet_data.AppendChild(t_row); ((Cell)t_row.ChildElements[0]).CellValue = new CellValue("Totals"); ((Cell)t_row.ChildElements[price_idx]).DataType = CellValues.Number; ((Cell)t_row.ChildElements[price_idx]).CellFormula = new CellFormula("SUM(G2:G" + (dt_data.Rows.Count + 1) + ")"); ((Cell)t_row.ChildElements[(price_idx + 1)]).DataType = CellValues.Number; ((Cell)t_row.ChildElements[(price_idx + 1)]).CellFormula = new CellFormula("SUM(H2:H" + (dt_data.Rows.Count + 1) + ")"); } } } }