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) + ")");
}
}
}
}
