public void SetCellStyle(string cell, object style)
{
if (read_only)
{
throw new Exception("Excel file is opened in read only mode and can't be modified!");
}
var p = sheet_data.IndexOf("r=\"" + cell + "\"");
var p2 = sheet_data.IndexOf("s=\"", p).Add(3);
var p3 = sheet_data.IndexOf("\"", p2 + 1);
var p4 = sheet_data.IndexOf(">", p);
if (p4 < p2)
{
}
else
{
sheet_data = sheet_data.Remove(p2, p3 - p2);
sheet_data = sheet_data.Insert(p2, style + "");
}
}
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());
}
}