/// <summary>
/// Inserts a row into a Worksheet
/// </summary>
/// <param name="worksheetPart">OpenXML's WorksheetPart object</param>
/// <returns>Returns the generated Row</returns>
public static Row InsertRow(WorksheetPart worksheetPart)
{
Row created = null;
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
Row lastRow = sheetData.Elements <Row>().LastOrDefault();
if (lastRow != null)
{
created = sheetData.InsertAfter(new Row()
{
RowIndex = (lastRow.RowIndex + 1)
}, lastRow);
}
else
{
created = new Row()
{
RowIndex = 1
};
sheetData.Append(created);
return(created);
}
return(created);
}
// Given a worksheet and a row index, return the row.
private static Row GetRow(Worksheet worksheet, uint rowIndex)
{
Row row;
SheetData sheetData = worksheet.GetFirstChild <SheetData>();
if (sheetData.Elements <Row>().Where(item => item.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements <Row>().Where(item => item.RowIndex == rowIndex).First();
}
else
{
Row previousRow = null;
row = new Row()
{
RowIndex = rowIndex
};
for (uint counter = rowIndex - 1; counter > 0; counter--)
{
previousRow = sheetData.Elements <Row>().Where(item => item.RowIndex == counter).FirstOrDefault();
if (previousRow != null)
{
break;
}
}
sheetData.InsertAfter(row, previousRow);
}
return(row);
}
private void InsertDataTable2WorkSheet(DataTable dt, WorksheetPart worksheetPart, DataTable xmldt, int startrow)
{
int count = dt.Rows.Count;
if (count == 0)
{
return;
}
int iend = int.MaxValue;
Row rr = GetRow(worksheetPart.Worksheet, (uint)startrow);
List <Row> li = new List <Row>();//all row will be add
string valuetype = "";
int i = 0;
Row rAdd;
for (i = 0; i < dt.Rows.Count; i++)
{
rAdd = (Row)rr.Clone();
foreach (DataRow xmlRow in xmldt.Rows)
{
valuetype = xmlRow["valuetype"].ToString();
if (valuetype == "table")
{
//process based on value
updateCell(rAdd, xmlRow, dt.Rows[i]);
}
}
updateCell(rAdd, getDataRow(xmldt, "order"), (i + 1).ToString());
updateReferenceCells(rAdd, i);
li.Add(rAdd);
}
//update for all
SheetData sd = worksheetPart.Worksheet.GetFirstChild <SheetData>();
//upate all row
//Move forward countStaff rows (without copying the format)
foreach (Row updateLink in sd.Elements <Row>())
{
if (updateLink.RowIndex > startrow && updateLink.RowIndex <= iend)
{
updateReferenceCells(updateLink, count - 1);
}
}
// insert all list
//rr = GetRow(worksheetPart.Worksheet, (uint)startrow);
//for (i = 0; i < count;i++ )
//{
// sd.InsertAt(li[i], i + startrow);
//}
// insert all list
for (i = li.Count - 1; i >= 0; i--)
{
sd.InsertAfter(li[i], rr);
}
}
private static DocumentFormat.OpenXml.Spreadsheet.Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
{
DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild <SheetData>();
string cellReference = columnName + rowIndex;
DocumentFormat.OpenXml.Spreadsheet.Row lastRow = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().LastOrDefault();
// If the worksheet does not contain a row with the specified row index, insert one.
DocumentFormat.OpenXml.Spreadsheet.Row row;
if (sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(r => r.RowIndex == rowIndex).First();
//set auto height -- don't know how this line is worked
sheetData.InsertAfter(new DocumentFormat.OpenXml.Spreadsheet.Row()
{
RowIndex = (lastRow.RowIndex + 1)
}, lastRow);
}
else
{
row = new DocumentFormat.OpenXml.Spreadsheet.Row()
{
RowIndex = rowIndex
};
sheetData.Append(row);
}
// If there is not a cell with the specified column name, insert one.
if (row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
{
return(row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(c => c.CellReference.Value == cellReference).First());
}
else
{
// Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
DocumentFormat.OpenXml.Spreadsheet.Cell refCell = null;
foreach (DocumentFormat.OpenXml.Spreadsheet.Cell cell in row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>())
{
if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
{
refCell = cell;
break;
}
}
DocumentFormat.OpenXml.Spreadsheet.Cell newCell = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
CellReference = cellReference
};
row.InsertBefore(newCell, refCell);
worksheet.Save();
return(newCell);
}
}
/// <summary>
/// Inserts new rows at index. It adds multiple rows and multiple columns for each row.
/// </summary>
/// <param name="rowIndex">Index of the row that will be the first of the inserted rows</param>
/// <param name="sheetData"></param>
/// <param name="howManyRows">How many rows to insert</param>
/// <param name="howManyColumns">How many columns to insert for each row</param>
private static void InsertNewRows(uint rowIndex, SheetData sheetData, int howManyRows, int howManyColumns)
{
Row RefRow = GetRow(sheetData.Parent as Worksheet, rowIndex);
var styleIndex = RefRow.Elements <Cell>().First().StyleIndex;
List <Row> RowsToAdd = new List <Row>();
for (int i = 0; i < howManyRows; ++i)
{
int newRowIndex = (int)rowIndex + i;
Row rowToAdd = new Row()
{
RowIndex = new UInt32Value((uint)newRowIndex)
};
for (uint cellColumnId = 0; cellColumnId <= howManyColumns; ++cellColumnId)
{
Cell cellToAdd = new Cell()
{
CellReference = new StringValue(GetExcelColumnName(cellColumnId) + newRowIndex),
CellFormula = new CellFormula(),
DataType = CellValues.String,
StyleIndex = styleIndex
};
rowToAdd.Append(cellToAdd);
}
RowsToAdd.Add(rowToAdd);
}
// Insert 1st row above given row index. Insert all others below that.
for (int i = 0; i < RowsToAdd.Count; ++i)
{
Row row = RowsToAdd[i];
if (i == 0)
{
sheetData.InsertBefore(row, RefRow);
}
else
{
sheetData.InsertAfter(row, RowsToAdd[i - 1]);
}
}
// Row that had previously row index of the 1st row inserted must now update its cell references and row index.
uint newIndexForFirstRow = RefRow.RowIndex.Value + (uint)howManyRows;
foreach (Cell cell in RefRow.Elements <Cell>())
{
cell.CellReference = new StringValue(cell.CellReference.Value.Replace(RefRow.RowIndex.Value.ToString(), newIndexForFirstRow.ToString()));
}
RefRow.RowIndex = new UInt32Value(newIndexForFirstRow);
}
public static void ReplicateRow(SheetData sheetData, int refRowIndex, int count)
{
IEnumerable<Row> rows = sheetData.Descendants<Row>().Where(r => r.RowIndex.Value > refRowIndex);
foreach (Row row in rows)
IncrementIndexes(row, count);
Row refRow = GetRow(sheetData, refRowIndex);
for (int i = 0; i < count; i++)
{
Row newRow = (Row)refRow.Clone();
IncrementIndexes(newRow, i + 1);
sheetData.InsertAfter(newRow, GetRow(sheetData, refRowIndex + i));
}
}
public static Row GetRow(SheetData sheetData, uint line, bool createIfDoesntExists = false)
{
var row = sheetData?.Elements <Row>().FirstOrDefault(r => r.RowIndex == line);
// Se existir a linha retorna.
if (row != null)
{
row.Spans = null;
return(row);
}
else if (!createIfDoesntExists)
{
return(null);
}
// Senao cria uma nova linha.
row = new Row {
RowIndex = line
};
var rows = sheetData.Elements <Row>().ToList();
// Caso nao exista linhas pode simplesmente inserir
if (!rows.Any())
{
sheetData.AppendChild(row);
return(row);
}
// Insere a linha na order correta.
var before = rows.Where(w => w.RowIndex < line).OrderBy(o => o.RowIndex.Value).LastOrDefault();
if (before != null) // Existem linhas anteriores a que sera inserida.
{
sheetData.InsertAfter(row, before);
}
else // Nao existem nenhuma linha anterior a que sera inserida.
{
var after = rows.Where(w => w.RowIndex > line).OrderBy(o => o.RowIndex.Value).FirstOrDefault();
// Insere antes do primeiro.
sheetData.InsertBefore(row, after);
}
return(row);
}
/// <summary>
/// Add new MembershipData into Excel file as new rows after the header
/// </summary>
public static int AddInputDataIntoExcelFile(string excelFileName, List <MembershipData> inputDataList)
{
int addedRows = 0;
using (SpreadsheetDocument document = SpreadsheetDocument.Open(excelFileName, true))
{
var workSheets = document.WorkbookPart.Workbook.Descendants <Sheet>();
var sharedStrings = document.WorkbookPart.SharedStringTablePart.SharedStringTable;
var firstSheetId = workSheets.First().Id;
var firstSheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(firstSheetId);
var anyCellAdded = false;
foreach (var membershipData in inputDataList.Where(m => m.ExistsInExcelFile == false))
{
SheetData sheetData = firstSheetPart.Worksheet.GetFirstChild <SheetData>();
Row lastRow = sheetData.Elements <Row>().LastOrDefault();
var newRow = sheetData.InsertAfter(new Row()
{
RowIndex = (lastRow.RowIndex + 1)
}, lastRow);
foreach (var membershipDataValues in membershipData.GetNoEmptyCsvNewValueMembershipDataValues())
{
var newCell = InsertCellInWorksheet(membershipDataValues.ExcelFileColumnIndex, newRow.RowIndex, firstSheetPart);
newCell.CellValue = new CellValue(membershipDataValues.CsvNewValue);
newCell.DataType = new EnumValue <CellValues>(CellValues.String);
anyCellAdded = true;
}
if (anyCellAdded)
{
// Save the worksheet.
firstSheetPart.Worksheet.Save();
document.Save();
addedRows++;
}
}
return(addedRows);
}
}
public static void DuplicateRow(WorkbookPart workbookPart, string sheetName, uint sourceRowIndex, uint destRowIndex)
{
WorksheetPart worksheetPart = workbookPart.GetPartById(workbookPart.Workbook.Descendants <Sheet>().Where(e => e.Name.ToString().ToUpper().Equals(sheetName.ToUpper())).First().Id.Value) as WorksheetPart;
Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild <SheetData>();
// If the worksheet does not contain a row with the specified row index, insert one.
Row currentRow = sheetData.Elements <Row>().Where(r => r.RowIndex == sourceRowIndex).FirstOrDefault();
Row newRow = new Row();
newRow = currentRow.CloneNode(true) as Row;
foreach (Cell cell in newRow.Elements <Cell>())
{
// Update the references for the rows cells.
cell.CellReference = new StringValue(cell.CellReference.Value.Replace(sourceRowIndex.ToString(), destRowIndex.ToString()));
}
newRow.RowIndex = destRowIndex;
IEnumerable <Row> rows = worksheetPart.Worksheet.Descendants <Row>().Where(r => r.RowIndex.Value >= destRowIndex);
foreach (Row row in rows)
{
uint newIndex = row.RowIndex + 1;
string curRowIndex = row.RowIndex.ToString();
string newRowIndex = newIndex.ToString();
foreach (Cell cell in row.Elements <Cell>())
{
// Update the references for the rows cells.
cell.CellReference = new StringValue(cell.CellReference.Value.Replace(curRowIndex, newRowIndex));
}
row.RowIndex = newIndex;
}
sheetData.InsertAfter(newRow, sheetData.Elements <Row>().Where(r => r.RowIndex == (destRowIndex - 1)).FirstOrDefault());
//worksheet.Save();
}
public Row GetRow(SheetData sheetData)
{
Row lastRow = sheetData.Elements <Row>().LastOrDefault();
if (lastRow != null)
{
Row row = new Row()
{
RowIndex = (lastRow.RowIndex + 1)
};
sheetData.InsertAfter(row, lastRow);
}
else
{
sheetData.Append(new Row()
{
RowIndex = 1
});
}
return(sheetData.Elements <Row>().LastOrDefault());
}
/// <summary>
/// Obtiene una fila especifica de una hoja según el numero indicado
/// Si la fila no existe la crea y la inserta en la hoja
/// </summary>
/// <param name="datosHoja"></param>
/// <param name="numFila"></param>
/// <returns></returns>
private static Row ObtenerFila(SheetData datosHoja, uint numFila)
{
var Fila = datosHoja.Elements <Row>()
.Where(r => r.RowIndex == numFila)
.FirstOrDefault();
if (Fila == null)
{
Fila = new Row()
{
RowIndex = numFila
};
var FilaRef = datosHoja.Elements <Row>()
.Where(r => r.RowIndex == numFila - 1)
.FirstOrDefault();
datosHoja.InsertAfter(Fila, FilaRef);
}
return(Fila);
}
public static int InsertRow(WorksheetPart worksheetPart)
{
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
Row lastRow = sheetData.Elements <Row>().LastOrDefault();
if (lastRow != null)
{
sheetData.InsertAfter(new Row()
{
RowIndex = (lastRow.RowIndex + 1)
}, lastRow);
return((int)UInt32Value.ToUInt32(lastRow.RowIndex));
}
else
{
sheetData.InsertAt(new Row()
{
RowIndex = 0
}, 0);
return(0);
}
}
Row InsertRowFromTemplate(RowSetDef rd, ref UInt32 count)
{
Row lastRow = null;
if (rd.Rows == null)
{
// строки еще нет, нужно ее найти
var row = _sheetData.Elements <Row>().First <Row>(r => r.RowIndex == rd.FirstRow);
rd.Rows = new List <Row>();
rd.RowsForClone = new List <Row>();
for (Int32 i = 0; i < rd.RowCount; i++)
{
rd.Rows.Add(row);
rd.RowsForClone.Add(row.Clone() as Row); // and for cloning too!
row = row.NextSibling <Row>();
lastRow = row;
}
}
else
{
// The line was already there, you need to clone it and insert it below
lastRow = rd.Rows[rd.Rows.Count - 1];
// next row index
UInt32 nri = rd.Rows[0].RowIndex.Value + rd.RowCount;
for (Int32 i = 0; i < rd.Rows.Count; i++)
{
var sr = rd.RowsForClone[i];
var nr = sr.Clone() as Row;
nr.RowIndex = nri++;
CorrectCellAddresses(nr);
_sheetData.InsertAfter <Row>(nr, lastRow);
count++;
rd.Rows[i] = nr;
lastRow = nr; // the last one is already inserted
}
}
return(lastRow);
}
Row InsertRowFromTemplate(RowSetDef rd, ref UInt32 count)
{
Row lastRow = null;
if (rd.Rows == null)
{
// строки еще нет, нужно ее найти
var row = _sheetData.Elements <Row>().First <Row>(r => r.RowIndex == rd.FirstRow);
rd.Rows = new List <Row>();
rd.RowsForClone = new List <Row>();
for (Int32 i = 0; i < rd.RowCount; i++)
{
rd.Rows.Add(row);
rd.RowsForClone.Add(row.Clone() as Row); // и для клонирования тоже!
row = row.NextSibling <Row>();
lastRow = row;
}
}
else
{
// Строка уже была, нужно ее клонировать и вставить ниже
lastRow = rd.Rows[rd.Rows.Count - 1];
// индекс следующей вставляемой строки
UInt32 nri = rd.Rows[0].RowIndex.Value + rd.RowCount;
for (Int32 i = 0; i < rd.Rows.Count; i++)
{
var sr = rd.RowsForClone[i];
var nr = sr.Clone() as Row;
nr.RowIndex = nri++;
CorrectCellAddresses(nr);
_sheetData.InsertAfter <Row>(nr, lastRow);
count++; // вставили строку
rd.Rows[i] = nr; // Для следующей вставки делаем
lastRow = nr; // последняя уже вставлена
}
}
return(lastRow);
}
/// <summary>
/// Add row moi vao sheetdata
/// </summary>
/// <param name="sheetName"></param>
/// <param name="workbookPart"></param>
/// <returns></returns>
public Row RowAddNew(SheetData sheetData)
{
Row lastRow = sheetData.Elements <Row>().LastOrDefault();
Row newRow;
if (lastRow != null)
{
newRow = new Row()
{
RowIndex = (lastRow.RowIndex + 1)
};
sheetData.InsertAfter(newRow, lastRow);
}
else
{
newRow = new Row()
{
RowIndex = 0
};
sheetData.InsertAt(newRow, 0);
}
return(newRow);
}
public static void ReplicateRow(SheetData sheetData, int refRowIndex, int count)
{
Repository.Utility.WriteLog("ReplicateRow started", System.Diagnostics.EventLogEntryType.Information);
IEnumerable<Row> rows = sheetData.Descendants<Row>().Where(r => r.RowIndex.Value > refRowIndex);
foreach (Row row in rows)
IncrementIndexes(row, count);
Row refRow = GetRow(sheetData, refRowIndex);
for (int i = 0; i < count; i++)
{
Row newRow = (Row)refRow.Clone();
IncrementIndexes(newRow, i + 1);
//sheetData.InsertAt(newRow, i + 1);
sheetData.InsertAfter(newRow, GetRow(sheetData, refRowIndex + i));
}
Repository.Utility.WriteLog("ReplicateRow completed successfully", System.Diagnostics.EventLogEntryType.Information);
}
/// <summary>
/// Sets a cell value. The row and the cell are created if they do not exist. If the cell exists, the contents of the cell is overwritten
/// </summary>
private static bool SetCellValue(SpreadsheetDocument spreadsheet, Worksheet worksheet, uint columnIndex, uint rowIndex, CellValues valueType, string value, uint?styleIndex, bool save = true)
{
SheetData sheetData = worksheet.GetFirstChild <SheetData>();
Row row;
Row previousRow = null;
Cell cell;
Cell previousCell = null;
Columns columns;
Column previousColumn = null;
string cellAddress = Excel.ColumnNameFromIndex(columnIndex) + rowIndex;
// Check if the row exists, create if necessary
if (sheetData.Elements <Row>().Where(item => item.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements <Row>().Where(item => item.RowIndex == rowIndex).First();
}
else
{
row = new Row()
{
RowIndex = rowIndex
};
//sheetData.Append(row);
for (uint counter = rowIndex - 1; counter > 0; counter--)
{
previousRow = sheetData.Elements <Row>().Where(item => item.RowIndex == counter).FirstOrDefault();
if (previousRow != null)
{
break;
}
}
sheetData.InsertAfter(row, previousRow);
}
// Check if the cell exists, create if necessary
if (row.Elements <Cell>().Where(item => item.CellReference.Value == cellAddress).Count() > 0)
{
cell = row.Elements <Cell>().Where(item => item.CellReference.Value == cellAddress).First();
}
else
{
// Find the previous existing cell in the row
for (uint counter = columnIndex - 1; counter > 0; counter--)
{
previousCell = row.Elements <Cell>().Where(item => item.CellReference.Value == Excel.ColumnNameFromIndex(counter) + rowIndex).FirstOrDefault();
if (previousCell != null)
{
break;
}
}
cell = new Cell()
{
CellReference = cellAddress
};
row.InsertAfter(cell, previousCell);
}
// Check if the column collection exists
columns = worksheet.Elements <Columns>().FirstOrDefault();
if (columns == null)
{
columns = worksheet.InsertAt(new Columns(), 0);
}
// Check if the column exists
if (columns.Elements <Column>().Where(item => item.Min == columnIndex).Count() == 0)
{
// Find the previous existing column in the columns
for (uint counter = columnIndex - 1; counter > 0; counter--)
{
previousColumn = columns.Elements <Column>().Where(item => item.Min == counter).FirstOrDefault();
if (previousColumn != null)
{
break;
}
}
columns.InsertAfter(
new Column()
{
Min = columnIndex,
Max = columnIndex,
CustomWidth = true,
Width = 9
}, previousColumn);
}
// Add the value
cell.CellValue = new CellValue(value);
if (styleIndex != null)
{
cell.StyleIndex = styleIndex;
}
if (valueType != CellValues.Date)
{
cell.DataType = new EnumValue <CellValues>(valueType);
}
if (save)
{
worksheet.Save();
}
return(true);
}
private static void ExportToOxml(bool firstTime, DataTable ResultsData)
{
const string fileName = @"D:\MyExcel.xlsx";
//Delete the file if it exists.
if (firstTime && File.Exists(fileName))
{
File.Delete(fileName);
}
uint sheetId = 1; //Start at the first sheet in the Excel workbook.
if (firstTime)
{
//This is the first time of creating the excel file and the first sheet.
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
Create(fileName, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
var sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
var bold1 = new Bold();
CellFormat cf = new CellFormat();
// Add Sheets to the Workbook.
Sheets sheets;
sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild <Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
var sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = sheetId,
Name = "Sheet" + sheetId
};
sheets.Append(sheet);
//Add Header Row.
var headerRow = new Row();
foreach (DataColumn column in ResultsData.Columns)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(column.ColumnName)
};
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow row in ResultsData.Rows)
{
var newRow = new Row();
foreach (DataColumn col in ResultsData.Columns)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(row[col].ToString())
};
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbookpart.Workbook.Save();
spreadsheetDocument.Close();
sheetData.Remove();
spreadsheetDocument.Dispose();
//Process currentProcess = System.Diagnostics.Process.GetCurrentProcess();
//long totalBytesOfMemoryUsed = currentProcess.WorkingSet64;
// WriteEventLog("Before: " + totalBytesOfMemoryUsed.ToString());
GC.Collect();
GC.WaitForPendingFinalizers();
// currentProcess = System.Diagnostics.Process.GetCurrentProcess();
//totalBytesOfMemoryUsed = currentProcess.WorkingSet64;
// WriteEventLog("After: " + totalBytesOfMemoryUsed.ToString());
}
else
{
// Open the Excel file that we created before, and start to add sheets to it.
var spreadsheetDocument = SpreadsheetDocument.Open(fileName, true);
var workbookpart = spreadsheetDocument.WorkbookPart;
//if (workbookpart.Workbook == null)
// workbookpart.Workbook = new Workbook();
var sheet = workbookpart.Workbook.Sheets.First();
Worksheet worksheet1 = workbookpart.WorksheetParts.First().Worksheet;
SheetData sheetData = (SheetData)worksheet1.First();
var worksheetPart = workbookpart.WorksheetParts;// AddNewPart<WorksheetPart>();
// var sheetData = new SheetData();
//var worksheet = worksheetPart.First();
//worksheet.Worksheet = new Worksheet(sheetData);
//var sheets = spreadsheetDocument.WorkbookPart.Workbook.Sheets;
//if (sheets.Elements<Sheet>().Any())
//{
// //Set the new sheet id
// sheetId = sheets.Elements<Sheet>().Max(s => s.SheetId.Value) + 1;
//}
//else
//{
// sheetId = 1;
//}
//// Append a new worksheet and associate it with the workbook.
//var sheet = new Sheet()
//{
// Id = spreadsheetDocument.WorkbookPart.
// GetIdOfPart(worksheetPart),
// SheetId = sheetId,
// Name = "Sheet" + sheetId
//};
//sheets.Append(sheet);
////Add the header row here.
//var headerRow = new Row();
//foreach (DataColumn column in ResultsData.Columns)
//{
// var cell = new Cell
// {
// DataType = CellValues.String,
// CellValue = new CellValue(column.ColumnName)
// };
// headerRow.AppendChild(cell);
//}
//sheetData.AppendChild(headerRow);
foreach (DataRow row in ResultsData.Rows)
{
var newRow = new Row();
foreach (DataColumn col in ResultsData.Columns)
{
var cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(row[col].ToString())
};
newRow.AppendChild(cell);
}
sheetData.InsertAfter(newRow, sheetData.LastChild);
}
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
sheetData.Remove();
spreadsheetDocument.Dispose();
//Process currentProcess = System.Diagnostics.Process.GetCurrentProcess();
//long totalBytesOfMemoryUsed = currentProcess.WorkingSet64;
//WriteEventLog("Before: " + totalBytesOfMemoryUsed.ToString());
GC.Collect();
GC.WaitForPendingFinalizers();
//currentProcess = System.Diagnostics.Process.GetCurrentProcess();
//totalBytesOfMemoryUsed = currentProcess.WorkingSet64;
//WriteEventLog("After: " + totalBytesOfMemoryUsed.ToString());
}
}