public MemoryStream GetExcel(string[] fieldsToExpose, DataTable data)
{
MemoryStream stream = new MemoryStream();
UInt32 rowcount = 0;
// Create the Excel document
var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
var workbookPart = document.AddWorkbookPart();
var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
var relId = workbookPart.GetIdOfPart(worksheetPart);
var workbook = new Workbook();
var fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };
var worksheet = new Worksheet();
var sheetData = new SheetData();
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
var sheets = new Sheets();
var sheet = new Sheet { Name = "Sheet1", SheetId = 1, Id = relId };
sheets.Append(sheet);
workbook.Append(fileVersion);
workbook.Append(sheets);
document.WorkbookPart.Workbook = workbook;
document.WorkbookPart.Workbook.Save();
// Add header to the sheet
var row = new Row { RowIndex = ++rowcount };
for (int i = 0; i < fieldsToExpose.Length; i++)
{
row.Append(CreateTextCell(ColumnLetter(i), rowcount, fieldsToExpose[i]));
}
sheetData.AppendChild(row);
worksheetPart.Worksheet.Save();
// Add data to the sheet
foreach (DataRow dataRow in data.Rows)
{
row = new Row { RowIndex = ++rowcount };
for (int i = 0; i < fieldsToExpose.Length; i++)
{
row.Append(CreateTextCell(ColumnLetter(i), rowcount, dataRow[fieldsToExpose[i]].ToString()));
}
sheetData.AppendChild(row);
}
worksheetPart.Worksheet.Save();
document.Close();
return stream;
}
public void Create(string fileName)
{
FileName = fileName;
using (SpreadsheetDocument doc = SpreadsheetDocument.Create(FileName, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
var workbookPart = doc.AddWorkbookPart();
var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
// Create Styles
var stylesPart = doc.WorkbookPart.AddNewPart<WorkbookStylesPart>();
Style = new CustomStylesheet();
LoadCustomFonts();
LoadCustomBorders();
LoadCustomStyles();
Style.Save(stylesPart);
string relId = workbookPart.GetIdOfPart(worksheetPart);
// create workbook and sheet
var workbook = new Workbook();
var worksheet = new Worksheet();
var fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };
// create columns
var columns = new Columns();
CreateColumns(columns);
worksheet.Append(columns);
// create Sheet
var sheets = new Sheets();
var sheet = new Sheet { Name = "My sheet", SheetId = 1, Id = relId };
sheets.Append(sheet);
workbook.Append(fileVersion);
workbook.Append(sheets);
var sheetData = new SheetData();
LoadData(sheetData);
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
doc.WorkbookPart.Workbook = workbook;
doc.WorkbookPart.Workbook.Save();
//doc.Close();
}
}
// Generates content of workbookPart1.
private static void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook();
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
sheets1.Append(sheet1);
workbook1.Append(sheets1);
workbookPart1.Workbook = workbook1;
}
public WorkbookPart Do(SpreadsheetDocument package, params XlsxWorksheet[] worksheets)
{
var workbook = new Workbook {
MCAttributes = new MarkupCompatibilityAttributes {
Ignorable = "x15"
}
};
workbook.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
workbook.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
workbook.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
workbook.Append(
new FileVersion {
ApplicationName = "xl", LastEdited = "6", LowestEdited = "4", BuildVersion = "14420"
},
new WorkbookProperties {
FilterPrivacy = true, DefaultThemeVersion = 124226U
},
new BookViews(
new WorkbookView {
ActiveTab = (uint)(worksheets.IndexOf(x => x.IsActive) ?? 0),
XWindow = 240,
YWindow = 105,
WindowWidth = 14805U,
WindowHeight = 8010U,
TabRatio = 845U
}
),
new Sheets(worksheets.Select((x, index) => new Sheet {
Name = x.Title,
SheetId = (uint)index + 1,
Id = "rId" + index
})),
new CalculationProperties()
{
CalculationId = 122211U
}
);
var workbookPart = package.AddWorkbookPart();
workbookPart.Workbook = workbook;
return(workbookPart);
}
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook();
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet()
{
Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1"
};
sheets1.Append(sheet1);
workbook1.Append(sheets1);
workbookPart1.Workbook = workbook1;
}
public IActionResult Post([FromBody] ExcellData data)
{
MemoryStream ms = new MemoryStream();
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(ms, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);
WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
Workbook workbook = new Workbook();
Worksheet worksheet = new Worksheet();
WorkbookStylesPart stylesPart = workbookPart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = CreateStylesheet();
stylesPart.Stylesheet.Save();
SheetData sheetData = new SheetData();
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
UInt32Value currentRowIndex;
currentRowIndex = CreateHeader(worksheet, sheetData, data);
currentRowIndex = CreateDataTables(worksheet, sheetData, data, currentRowIndex);
Sheets sheets = new Sheets();
Sheet sheet = new Sheet
{
Name = "Registro IVA corrispettivi",
SheetId = 1,
Id = workbookPart.GetIdOfPart(worksheetPart)
};
sheets.Append(sheet);
workbook.Append(sheets);
spreadsheetDocument.WorkbookPart.Workbook = workbook;
spreadsheetDocument.WorkbookPart.Workbook.Save();
spreadsheetDocument.Close();
byte[] content = ms.ToArray();
ms.Seek(0, SeekOrigin.Begin);
return(File(content, "application/vnd.openxmlformats-officedocument.spreadsheetml", "corrispettivi.xlsx"));
}
private void GenerateWorkbookPartContent(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook();
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet()
{
Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1"
};
Sheet sheet2 = new Sheet()
{
Name = "Sheet2", SheetId = (UInt32Value)1U, Id = "rId1"
};
sheets1.Append(sheet1);
sheets1.Append(sheet2);
workbook1.Append(sheets1);
workbookPart1.Workbook = workbook1;
}
public XlWorksheet[] Wss; // массив экземпляров WS для заполненых worksheet с доп процедурами
public Spreadsheet(Int32 sheetCount = 1)
{
memoryStream = new System.IO.MemoryStream();
document = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook);
workbookPart1 = document.AddWorkbookPart();
workbook = new Workbook();
workbookPart1.Workbook = workbook;
workbookStylesPart = workbookPart1.AddNewPart <WorkbookStylesPart>();
stylesheet = (new StylesPart()).GenerateWorkbookStylesPartContent();
workbookStylesPart.Stylesheet = stylesheet;
sharedStringTablePart = workbookPart1.AddNewPart <SharedStringTablePart>();
sharedStringTable = (new SstPart()).GenerateSharedStringTablePart();
sharedStringTablePart.SharedStringTable = sharedStringTable;
sheets = new Sheets();
workbook.Append(sheets);
worksheetParts = new WorksheetPart[sheetCount];
Wss = new XlWorksheet[sheetCount];
for (UInt32 i = 0; i < sheetCount; i++)
{
// создаём новый лист со всеми частями (SheetViews, Columns, SheetData, MergeCells) (пока пустыми)
Wss[i] = new XlWorksheet(stylesheet, sharedStringTable);
worksheet = Wss[i].GetWorksheet();
// создаём новый WorksheetPart в workbookPart
worksheetParts[i] = workbookPart1.AddNewPart <WorksheetPart>();
// и добавляем новый лист в его корневой элемент Worksheet
worksheetParts[i].Worksheet = worksheet;
// ссылка на новый WorksheetPart
String rId = workbookPart1.GetIdOfPart(worksheetParts[i]);
// запись о новом листе в workbook
sheet = new Sheet()
{
Name = "Sheet" + (i + 1).ToString(), SheetId = (i + 1), Id = rId
};
sheets.Append(sheet);
}
}
protected override void GenWorkbookPart(string sheetName, WorkbookPart workbookPart)
{
Workbook workbook = new Workbook();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet()
{
Name = sheetName, SheetId = (UInt32Value)1U, Id = "rId0"
};
Sheet sheet2 = new Sheet()
{
Name = "ConnectData", SheetId = (UInt32Value)2U, Id = "rId1"
};
sheets.Append(sheet);
sheets.Append(sheet2);
workbook.Append(sheets);
workbookPart.Workbook = workbook;
}
private static void CreateParts(DataTable dt, SpreadsheetDocument document)
{
WorkbookPart workbookPart = document.AddWorkbookPart();
Workbook workbook = new Workbook();
workbookPart.Workbook = workbook;
// If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart <WorkbookStylesPart>("rIdStyles");
Stylesheet stylesheet = new Stylesheet();
workbookStylesPart.Stylesheet = stylesheet;
Sheets sheets = new Sheets();
// Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
uint worksheetNumber = 1;
// foreach (DataTable dt in ds.Tables)
// {
// For each worksheet you want to create
string workSheetID = "rId" + worksheetNumber.ToString();
string worksheetName = "Table 1";
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(workSheetID);
WriteDataTableToExcelWorksheet(dt, worksheetPart);
Sheet sheet = new Sheet()
{
Name = worksheetName, SheetId = (UInt32Value)worksheetNumber, Id = workSheetID
};
sheets.Append(sheet);
worksheetNumber++;
// }
workbook.Append(sheets);
}
/// <summary>
/// Generate all inspection reports
/// </summary>
/// <param name="templatePath">Path of template</param>
/// <param name="itemsource">Item source</param>
/// <returns>MemoryStream</returns>
public static MemoryStream GenerateAllInspectionReports(ExportInspectionReportsModel itemsource,string logoPath)
{
MemoryStream ms = new MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms,SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
Workbook workbook = new Workbook();
workbookPart.Workbook = workbook;
// If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
//get and save the stylesheet
Stylesheet stylesheet = VestalisStyleSheet();
workbookStylesPart.Stylesheet = stylesheet;
workbookStylesPart.Stylesheet.Save();
int sheetId = 1;
Sheets sheets = new Sheets();
if (itemsource.IsSelectedServiceOrder)
{
//Generate service order report
GenerateServiceOrder(itemsource, workbookPart, sheets,sheetId,logoPath);
sheetId++;
}
//Generate inspection reports
GenerateAllInspectionReports(itemsource, workbookPart, sheets, sheetId, logoPath);
//add the new sheet to the report
workbook.Append(sheets);
//save all report
workbook.Save();
//close the stream.
document.Close();
}
return ms;
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x15"
}
};
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet()
{
Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1"
};
sheets1.Append(sheet1);
workbook1.Append(sheets1);
workbookPart1.Workbook = workbook1;
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook();
workbook1.AddNamespaceDeclaration("x", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
Sheets sheets1 = new Sheets();
sheets = sheets1;
Sheet sheet1 = new Sheet() { Name = "oi_th", SheetId = (UInt32Value)1U, Id = "R3c8458136e1d4b6c" };
sheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
sheet = sheet1;
sheets1.Append(sheet1);
workbook1.Append(sheets1);
workbookPart1.Workbook = workbook1;
}
public static MemoryStream GetExcel(string[] fieldsToExpose, DataTable data)
{
MemoryStream stream = new MemoryStream();
UInt32 rowcount = 0;
// Create the Excel document
var document = SpreadsheetDocument.Create
(stream, SpreadsheetDocumentType.Workbook);
var workbookPart = document.AddWorkbookPart();
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
var relId = workbookPart.GetIdOfPart(worksheetPart);
var workbook = new Workbook();
var fileVersion = new FileVersion {
ApplicationName = "Microsoft Office Excel"
};
var worksheet = new Worksheet();
var sheetData = new SheetData();
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
var sheets = new Sheets();
var sheet = new Sheet {
Name = "Sheet1", SheetId = 1, Id = relId
};
sheets.Append(sheet);
workbook.Append(fileVersion);
workbook.Append(sheets);
document.WorkbookPart.Workbook = workbook;
document.WorkbookPart.Workbook.Save();
// Add header to the sheet
var row = new Row {
RowIndex = ++rowcount
};
for (int i = 0; i < fieldsToExpose.Length; i++)
{
row.Append(CreateTextCell(ColumnLetter(i), rowcount, fieldsToExpose[i]));
}
sheetData.AppendChild(row);
worksheetPart.Worksheet.Save();
// Add data to the sheet
foreach (DataRow dataRow in data.Rows)
{
row = new Row {
RowIndex = ++rowcount
};
for (int i = 0; i < fieldsToExpose.Length; i++)
{
row.Append(CreateTextCell(ColumnLetter(i), rowcount,
dataRow[fieldsToExpose[i]].ToString()));
}
sheetData.AppendChild(row);
}
worksheetPart.Worksheet.Save();
document.Close();
return(stream);
}
private void generateWorkbookPartContent(WorkbookPart workbookPart)
{
var workbook = new Workbook();
workbook.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
var fileVersion = new FileVersion { ApplicationName = "xl", LastEdited = "5", LowestEdited = "5", BuildVersion = "9303" };
var workbookProperties = new WorkbookProperties { DefaultThemeVersion = 124226 };
var bookViews = new BookViews();
var workbookView = new WorkbookView { XWindow = 630, YWindow = 600, WindowWidth = 27495, WindowHeight = 11955 };
bookViews.Append(workbookView);
var sheets = new Sheets();
var sheet = new Sheet { Name = "Документ", SheetId = 1, Id = "rId1" };
sheets.Append(sheet);
var calculationProperties = new CalculationProperties { CalculationId = 0 };
workbook.Append(fileVersion);
workbook.Append(workbookProperties);
workbook.Append(bookViews);
workbook.Append(sheets);
workbook.Append(calculationProperties);
workbookPart.Workbook = workbook;
}
public static byte[] WriteExcel(this Dictionary <DateTime, string[, ]> lst, string excelFile)
{
var template = new FileInfo(excelFile);
bool hasData = true;
using (var templateStream = new MemoryStream())
{
using (SpreadsheetDocument spreadDocument = SpreadsheetDocument.Open(excelFile, true))
{
WorkbookPart workBookPart = spreadDocument.WorkbookPart;
Workbook workbook = workBookPart.Workbook;
var fileVersion = new FileVersion {
ApplicationName = "Microsoft Office Excel"
};
Workbook wb = new Workbook();
wb.Append(fileVersion);
Sheets sheets = null;
WorksheetPart sourceSheetPart = null;
// add sheet
foreach (KeyValuePair <DateTime, string[, ]> item in lst)
{
sheets = sheets ?? new Sheets();
var sheetId = sheets != null ? (uint)sheets.ChildElements.Count + 1 : 1;
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(HttpContext.Current.Request.MapPath("~") + "TemplateFile\\Template.xlsx", true))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
string rId = workbookPart.Workbook.Descendants <Sheet>().Where(s => s.Name.Value.Equals("sheet_name")).First().Id;
WorksheetPart wsPart = (WorksheetPart)workbookPart.GetPartById(rId);
try
{
workbookPart.ChangeIdOfPart(wsPart, "wsPart_" + sheetId);
}
catch
{
workbookPart.ChangeIdOfPart(wsPart, "rId1");
workbookPart.Workbook.Descendants <Sheet>().Where(s => s.Name.Value.Equals("sheet_name")).First().Id = "rId1";
workbookPart.ChangeIdOfPart(wsPart, "wsPart_" + sheetId);
}
workbookPart.Workbook.Descendants <Sheet>().Where(s => s.Name.Value.Equals("sheet_name")).First().Id = "wsPart_" + sheetId;
sourceSheetPart = wsPart;
WorksheetPart worksheetPart = workBookPart.AddPart <WorksheetPart>(sourceSheetPart);
Sheet copiedSheet = new Sheet
{
Name = item.Key.ToString("dd-MM-yyyy"),
Id = workBookPart.GetIdOfPart(worksheetPart)
};
copiedSheet.SheetId = sheetId;
sheets.Append(copiedSheet);
worksheetPart.Worksheet.GetFirstChild <SheetData>().UpdateSheetData(item.Key, item.Value);
workbookPart.ChangeIdOfPart(wsPart, "rId1");
workbookPart.Workbook.Descendants <Sheet>().Where(s => s.Name.Value.Equals("sheet_name")).First().Id = "rId1";
}
}
hasData = sheets != null;
if (sheets != null)
{
wb.Append(sheets);
//Save Changes
workBookPart.Workbook = wb;
wb.Save();
workBookPart.Workbook.Save();
}
spreadDocument.Close();
}
if (hasData)
{
byte[] templateBytes = File.ReadAllBytes(template.FullName);
templateStream.Write(templateBytes, 0, templateBytes.Length);
var result = templateStream.ToArray();
templateStream.Position = 0;
templateStream.Flush();
return(result);
}
else
{
return(null);
}
}
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook();
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
FileVersion fileVersion1 = new FileVersion(){ ApplicationName = "xl", LastEdited = "4", LowestEdited = "4", BuildVersion = "4505" };
WorkbookProperties workbookProperties1 = new WorkbookProperties(){ DefaultThemeVersion = (UInt32Value)124226U };
BookViews bookViews1 = new BookViews();
WorkbookView workbookView1 = new WorkbookView(){ XWindow = 240, YWindow = 45, WindowWidth = (UInt32Value)28320U, WindowHeight = (UInt32Value)12855U };
bookViews1.Append(workbookView1);
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet(){ Name = "NOMBRE DE SOLAPA", SheetId = (UInt32Value)1U, Id = "rId1" };
switch (_Estado.ToUpper())
{
case "ORDENADO":
{
sheet1.Name = "O" + _oCabecera.AnoMes + " " + _oCabecera.IdentifEspacio;
break;
}
case "ESTIMADO":
{
sheet1.Name = "E" + _eCabecera.AnoMes + " V" + _eCabecera.Version + " " + _eCabecera.IdentifEspacio;
break;
}
case "CERTIFICADO":
{
sheet1.Name = "C" + _cCabecera.AnoMes + " " + _cCabecera.IdentifEspacio + _cCabecera.IdentifOrigen;
break;
}
}
sheets1.Append(sheet1);
CalculationProperties calculationProperties1 = new CalculationProperties(){ CalculationId = (UInt32Value)124519U };
workbook1.Append(fileVersion1);
workbook1.Append(workbookProperties1);
workbook1.Append(bookViews1);
workbook1.Append(sheets1);
workbook1.Append(calculationProperties1);
workbookPart1.Workbook = workbook1;
}
public static void Create(string filename, DataSet ds)
{
// SpreadsheetDocument电子表格文档类
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook);
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
//workbookpart.SharedStringTablePart
Workbook workbook = new Workbook();
Sheets sheets = new Sheets();
#region 创建多个 sheet 页
//创建多个sheet
for (int s = 0; s < ds.Tables.Count; s++)
{
DataTable dt = ds.Tables[s];
var tname = dt.TableName;
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
Worksheet worksheet = new Worksheet();
SheetData sheetData = new SheetData();
//创建 sheet 页
Sheet sheet = new Sheet()
{
//页面关联的 WorksheetPart
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = UInt32Value.FromUInt32((uint)s + 1),
Name = tname
};
sheets.Append(sheet);
#region 创建sheet 行
Row row;
uint rowIndex = 1;
//添加表头
row = new Row()
{
RowIndex = UInt32Value.FromUInt32(rowIndex++)
};
sheetData.Append(row);
for (int i = 0; i < dt.Columns.Count; i++)
{
Cell newCell = new Cell();
newCell.CellValue = new CellValue(dt.Columns[i].ColumnName);
newCell.DataType = new EnumValue <CellValues>(CellValues.String);
row.Append(newCell);
}
//添加内容
object val = null;
for (int i = 0; i < dt.Rows.Count; i++)
{
row = new Row()
{
RowIndex = UInt32Value.FromUInt32(rowIndex++)
};
sheetData.Append(row);
for (int j = 0; j < dt.Columns.Count; j++)
{
Cell newCell = new Cell();
val = dt.Rows[i][j];
newCell.CellValue = new CellValue(val.ToString().Trim(new char[] { '0', ':' }));
newCell.DataType = new EnumValue <CellValues>(CellValues.String);
row.Append(newCell);
}
}
#endregion
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
}
#endregion
workbook.Append(sheets);
workbookpart.Workbook = workbook;
workbookpart.Workbook.Save();
spreadsheetDocument.Close();
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook();
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
Sheet sheet2 = new Sheet() { Name = "Sheet2", SheetId = (UInt32Value)2U, Id = "rId2" };
sheets1.Append(sheet1);
sheets1.Append(sheet2);
PivotCaches pivotCaches1 = new PivotCaches();
PivotCache pivotCache1 = new PivotCache() { CacheId = (UInt32Value)2U, Id = "rId4" };
pivotCaches1.Append(pivotCache1);
workbook1.Append(sheets1);
workbook1.Append(pivotCaches1);
workbookPart1.Workbook = workbook1;
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x15" } };
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
FileVersion fileVersion1 = new FileVersion(){ ApplicationName = "xl", LastEdited = "6", LowestEdited = "6", BuildVersion = "14420" };
WorkbookProperties workbookProperties1 = new WorkbookProperties();
AlternateContent alternateContent1 = new AlternateContent();
alternateContent1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
AlternateContentChoice alternateContentChoice1 = new AlternateContentChoice(){ Requires = "x15" };
X15ac.AbsolutePath absolutePath1 = new X15ac.AbsolutePath(){ Url = "D:\\Users\\dito\\Desktop\\TestDocumentResaver\\OpenXmlApiConversion\\Timeline\\" };
absolutePath1.AddNamespaceDeclaration("x15ac", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac");
alternateContentChoice1.Append(absolutePath1);
alternateContent1.Append(alternateContentChoice1);
BookViews bookViews1 = new BookViews();
WorkbookView workbookView1 = new WorkbookView(){ XWindow = 0, YWindow = 0, WindowWidth = (UInt32Value)26940U, WindowHeight = (UInt32Value)15120U };
bookViews1.Append(workbookView1);
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet(){ Name = "data", SheetId = (UInt32Value)1U, Id = "rId1" };
Sheet sheet2 = new Sheet(){ Name = "data2", SheetId = (UInt32Value)3U, Id = "rId2" };
Sheet sheet3 = new Sheet(){ Name = "Cache", SheetId = (UInt32Value)4U, Id = "rId3" };
Sheet sheet4 = new Sheet(){ Name = "Level", SheetId = (UInt32Value)2U, Id = "rId4" };
Sheet sheet5 = new Sheet(){ Name = "Caption", SheetId = (UInt32Value)5U, Id = "rId5" };
Sheet sheet6 = new Sheet(){ Name = "ShowHeader", SheetId = (UInt32Value)6U, Id = "rId6" };
Sheet sheet7 = new Sheet(){ Name = "ShowSelectionLabel", SheetId = (UInt32Value)7U, Id = "rId7" };
Sheet sheet8 = new Sheet(){ Name = "ShowTimeLevel", SheetId = (UInt32Value)8U, Id = "rId8" };
Sheet sheet9 = new Sheet(){ Name = "ShowHorizontalScrollbar", SheetId = (UInt32Value)9U, Id = "rId9" };
Sheet sheet10 = new Sheet(){ Name = "ScrollPosition", SheetId = (UInt32Value)10U, Id = "rId10" };
Sheet sheet11 = new Sheet(){ Name = "Style", SheetId = (UInt32Value)11U, Id = "rId11" };
sheets1.Append(sheet1);
sheets1.Append(sheet2);
sheets1.Append(sheet3);
sheets1.Append(sheet4);
sheets1.Append(sheet5);
sheets1.Append(sheet6);
sheets1.Append(sheet7);
sheets1.Append(sheet8);
sheets1.Append(sheet9);
sheets1.Append(sheet10);
sheets1.Append(sheet11);
DefinedNames definedNames1 = new DefinedNames();
DefinedName definedName1 = new DefinedName(){ Name = "NativeTimeline_Date" };
definedName1.Text = "#N/A";
DefinedName definedName2 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate" };
definedName2.Text = "#N/A";
DefinedName definedName3 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate1" };
definedName3.Text = "#N/A";
DefinedName definedName4 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate2" };
definedName4.Text = "#N/A";
DefinedName definedName5 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate3" };
definedName5.Text = "#N/A";
DefinedName definedName6 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate4" };
definedName6.Text = "#N/A";
DefinedName definedName7 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate5" };
definedName7.Text = "#N/A";
DefinedName definedName8 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate6" };
definedName8.Text = "#N/A";
DefinedName definedName9 = new DefinedName(){ Name = "NativeTimeline_DeliveryDate7" };
definedName9.Text = "#N/A";
definedNames1.Append(definedName1);
definedNames1.Append(definedName2);
definedNames1.Append(definedName3);
definedNames1.Append(definedName4);
definedNames1.Append(definedName5);
definedNames1.Append(definedName6);
definedNames1.Append(definedName7);
definedNames1.Append(definedName8);
definedNames1.Append(definedName9);
CalculationProperties calculationProperties1 = new CalculationProperties(){ CalculationId = (UInt32Value)152511U };
PivotCaches pivotCaches1 = new PivotCaches();
PivotCache pivotCache1 = new PivotCache(){ CacheId = (UInt32Value)0U, Id = "rId12" };
PivotCache pivotCache2 = new PivotCache(){ CacheId = (UInt32Value)1U, Id = "rId13" };
pivotCaches1.Append(pivotCache1);
pivotCaches1.Append(pivotCache2);
WorkbookExtensionList workbookExtensionList1 = new WorkbookExtensionList();
WorkbookExtension workbookExtension1 = new WorkbookExtension(){ Uri = "{79F54976-1DA5-4618-B147-4CDE4B953A38}" };
workbookExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
X14.WorkbookProperties workbookProperties2 = new X14.WorkbookProperties();
workbookExtension1.Append(workbookProperties2);
WorkbookExtension workbookExtension2 = new WorkbookExtension(){ Uri = "{D0CA8CA8-9F24-4464-BF8E-62219DCF47F9}" };
workbookExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.TimelineCacheReferences timelineCacheReferences1 = new X15.TimelineCacheReferences();
X15.TimelineCacheReference timelineCacheReference1 = new X15.TimelineCacheReference(){ Id = "rId14" };
X15.TimelineCacheReference timelineCacheReference2 = new X15.TimelineCacheReference(){ Id = "rId15" };
X15.TimelineCacheReference timelineCacheReference3 = new X15.TimelineCacheReference(){ Id = "rId16" };
X15.TimelineCacheReference timelineCacheReference4 = new X15.TimelineCacheReference(){ Id = "rId17" };
X15.TimelineCacheReference timelineCacheReference5 = new X15.TimelineCacheReference(){ Id = "rId18" };
X15.TimelineCacheReference timelineCacheReference6 = new X15.TimelineCacheReference(){ Id = "rId19" };
X15.TimelineCacheReference timelineCacheReference7 = new X15.TimelineCacheReference(){ Id = "rId20" };
X15.TimelineCacheReference timelineCacheReference8 = new X15.TimelineCacheReference(){ Id = "rId21" };
X15.TimelineCacheReference timelineCacheReference9 = new X15.TimelineCacheReference(){ Id = "rId22" };
timelineCacheReferences1.Append(timelineCacheReference1);
timelineCacheReferences1.Append(timelineCacheReference2);
timelineCacheReferences1.Append(timelineCacheReference3);
timelineCacheReferences1.Append(timelineCacheReference4);
timelineCacheReferences1.Append(timelineCacheReference5);
timelineCacheReferences1.Append(timelineCacheReference6);
timelineCacheReferences1.Append(timelineCacheReference7);
timelineCacheReferences1.Append(timelineCacheReference8);
timelineCacheReferences1.Append(timelineCacheReference9);
workbookExtension2.Append(timelineCacheReferences1);
WorkbookExtension workbookExtension3 = new WorkbookExtension(){ Uri = "{140A7094-0E35-4892-8432-C4D2E57EDEB5}" };
workbookExtension3.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.WorkbookProperties workbookProperties3 = new X15.WorkbookProperties(){ ChartTrackingReferenceBase = true };
workbookExtension3.Append(workbookProperties3);
workbookExtensionList1.Append(workbookExtension1);
workbookExtensionList1.Append(workbookExtension2);
workbookExtensionList1.Append(workbookExtension3);
workbook1.Append(fileVersion1);
workbook1.Append(workbookProperties1);
workbook1.Append(alternateContent1);
workbook1.Append(bookViews1);
workbook1.Append(sheets1);
workbook1.Append(definedNames1);
workbook1.Append(calculationProperties1);
workbook1.Append(pivotCaches1);
workbook1.Append(workbookExtensionList1);
workbookPart1.Workbook = workbook1;
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook();
FileVersion fileVersion1 = new FileVersion() { ApplicationName = "xl", LastEdited = "4", LowestEdited = "4", BuildVersion = "4506" };
WorkbookProperties workbookProperties1 = new WorkbookProperties() { DefaultThemeVersion = (UInt32Value)124226U };
BookViews bookViews1 = new BookViews();
WorkbookView workbookView1 = new WorkbookView() { XWindow = 0, YWindow = 45, WindowWidth = (UInt32Value)19155U, WindowHeight = (UInt32Value)11820U };
bookViews1.Append(workbookView1);
Sheets sheets1 = new Sheets();
//Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
//Sheet sheet2 = new Sheet() { Name = "Sheet2", SheetId = (UInt32Value)2U, Id = "rId2" };
//Sheet sheet3 = new Sheet() { Name = "Sheet3", SheetId = (UInt32Value)3U, Id = "rId3" };
//sheets1.Append(sheet1);
//sheets1.Append(sheet2);
//sheets1.Append(sheet3);
CalculationProperties calculationProperties1 = new CalculationProperties() { CalculationId = (UInt32Value)125725U };
workbook1.Append(fileVersion1);
workbook1.Append(workbookProperties1);
workbook1.Append(bookViews1);
workbook1.Append(sheets1);
workbook1.Append(calculationProperties1);
workbookPart1.Workbook = workbook1;
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x15" } };
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
FileVersion fileVersion1 = new FileVersion(){ ApplicationName = "xl", LastEdited = "6", LowestEdited = "6", BuildVersion = "14420" };
WorkbookProperties workbookProperties1 = new WorkbookProperties(){ CodeName = "ThisWorkbook", DefaultThemeVersion = (UInt32Value)153222U };
AlternateContent alternateContent1 = new AlternateContent();
alternateContent1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
AlternateContentChoice alternateContentChoice1 = new AlternateContentChoice(){ Requires = "x15" };
X15ac.AbsolutePath absolutePath1 = new X15ac.AbsolutePath(){ Url = "D:\\Users\\dito\\Desktop\\TestDocumentResaver\\OpenXmlApiConversion\\Pivot\\" };
absolutePath1.AddNamespaceDeclaration("x15ac", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac");
alternateContentChoice1.Append(absolutePath1);
alternateContent1.Append(alternateContentChoice1);
BookViews bookViews1 = new BookViews();
WorkbookView workbookView1 = new WorkbookView(){ XWindow = 0, YWindow = 0, WindowWidth = (UInt32Value)26940U, WindowHeight = (UInt32Value)15120U };
bookViews1.Append(workbookView1);
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet(){ Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
sheets1.Append(sheet1);
DefinedNames definedNames1 = new DefinedNames();
DefinedName definedName1 = new DefinedName(){ Name = "Query", LocalSheetId = (UInt32Value)0U, Hidden = true };
definedName1.Text = "Sheet1!$B$2:$I$13";
definedNames1.Append(definedName1);
CalculationProperties calculationProperties1 = new CalculationProperties(){ CalculationId = (UInt32Value)152511U };
PivotCaches pivotCaches1 = new PivotCaches();
PivotCache pivotCache1 = new PivotCache(){ CacheId = (UInt32Value)0U, Id = "rId2" };
pivotCaches1.Append(pivotCache1);
WorkbookExtensionList workbookExtensionList1 = new WorkbookExtensionList();
WorkbookExtension workbookExtension1 = new WorkbookExtension(){ Uri = "{FCE2AD5D-F65C-4FA6-A056-5C36A1767C68}" };
workbookExtension1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.DataModel dataModel1 = new X15.DataModel();
X15.ModelTables modelTables1 = new X15.ModelTables();
X15.ModelTable modelTable1 = new X15.ModelTable(){ Id = "Query_7c41ad89-7105-4c9f-ab5a-881bd3e6a1b9", Name = "Query", Connection = "DAT105 Timestamp - Foodmart 2000 account" };
modelTables1.Append(modelTable1);
dataModel1.Append(modelTables1);
workbookExtension1.Append(dataModel1);
WorkbookExtension workbookExtension2 = new WorkbookExtension(){ Uri = "{69C81A23-63F3-4edf-8378-127667AE99B5}" };
OpenXmlUnknownElement openXmlUnknownElement1 = OpenXmlUnknownElement.CreateOpenXmlUnknownElement("<x15:workbookPr15 chartTrackingRefBase=\"1\" xmlns:x15=\"http://schemas.microsoft.com/office/spreadsheetml/2010/11/main\" />");
workbookExtension2.Append(openXmlUnknownElement1);
workbookExtensionList1.Append(workbookExtension1);
workbookExtensionList1.Append(workbookExtension2);
workbook1.Append(fileVersion1);
workbook1.Append(workbookProperties1);
workbook1.Append(alternateContent1);
workbook1.Append(bookViews1);
workbook1.Append(sheets1);
workbook1.Append(definedNames1);
workbook1.Append(calculationProperties1);
workbook1.Append(pivotCaches1);
workbook1.Append(workbookExtensionList1);
workbookPart1.Workbook = workbook1;
}
/// <summary>
/// Generate an excel report dinamically
/// </summary>
/// <param name="model">Data source</param>
public static MemoryStream GenerateReportDinamically(DynamicDataGrid model, string logoPath)
{
MemoryStream report = new MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(report, SpreadsheetDocumentType.Workbook))
{
//create the new workbook
WorkbookPart workbookPart = document.AddWorkbookPart();
Workbook workbook = new Workbook();
workbookPart.Workbook = workbook;
// If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
//get and save the stylesheet
Stylesheet stylesheet = VestalisStyleSheet();
workbookStylesPart.Stylesheet = stylesheet;
workbookStylesPart.Stylesheet.Save();
//add the new workseet
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet();
SheetData sheetData1 = new SheetData();
Sheets sheets = new Sheets();
//get the number of columns in the report
Row rowTitle;
int numberOfColumnsCaption = model.Captions.Count;
//get the string name of the columns
string[] excelColumnNamesTitle = new string[numberOfColumnsCaption];
for (int n = 0; n < numberOfColumnsCaption; n++)
excelColumnNamesTitle[n] = GetExcelColumnName(n);
//build the title
for (int i = 1; i <= 6; i++)
{
rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
for (int cellval = 0; cellval < numberOfColumnsCaption; cellval++)
{
AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
}
sheetData1.Append(rowTitle);
}
MergeCells mergeCells = new MergeCells();
Row currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
//add the business application name
UpdateStringCellValue("B2", model.BusinessApplicationName, currentRowTitle, 5);
string lastColumnName = excelColumnNamesTitle.Last() + "2";
//merge all cells in the title
MergeCell mergeCell = new MergeCell();
mergeCell.Reference = "B2:" + lastColumnName;
mergeCells.Append(mergeCell);
Drawing drawing = AddLogo(logoPath, worksheetPart);
currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)4);
//add the form name
UpdateStringCellValue("B4", model.FormName, currentRowTitle,5);
lastColumnName = lastColumnName.Replace("2", "4");
//merge all cell in the form name
mergeCell = new MergeCell();
mergeCell.Reference = "B4:" + lastColumnName;
mergeCells.Append(mergeCell);
int rowIndex = 7;
//get the names of the columns
string[] excelColumnNamesCaptions = new string[numberOfColumnsCaption];
for (int n = 0; n < numberOfColumnsCaption; n++)
excelColumnNamesCaptions[n] = GetExcelColumnName(n);
Row rowCaption = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
//build column names of the report
Columns columns = new Columns();
for (int i = 0; i < model.Captions.Count; i++)
{
var caption = model.Captions[i];
AppendTextCell(excelColumnNamesCaptions[i] + rowIndex.ToString(), caption.Caption, rowCaption, 2);
columns.Append(CreateColumnData((UInt32Value)(uint)i + 1, (UInt32Value)(uint)i + 1, caption.ExcelColumnWidth));
}
sheetData1.Append(rowCaption);
//add the new row with the name of the columns
worksheet.Append(columns);
rowIndex = 8;
//write the data of the report
foreach (var item in model.DataRows)
{
int numberOfColumnsData = item.FieldValues.Count;
//get column names
string[] excelColumnNamesData = new string[numberOfColumnsData];
for (int n = 0; n < numberOfColumnsData; n++)
excelColumnNamesData[n] = GetExcelColumnName(n);
//build the data information
Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
for (int colInx = 0; colInx < numberOfColumnsData; colInx++)
{
DynamicDataRowValue col = item.FieldValues[colInx];
switch (col.FieldType)
{
case (int)FieldType.Catalogue:
case (int)FieldType.RegularExpressionText:
case (int)FieldType.Time:
case (int)FieldType.SingleTextLine:
case (int)FieldType.MultipleTextLine:
case (int)FieldType.Datepicker:
case (int)FieldType.Boolean:
case (int)FieldType.AutoComplete:
case (int)FieldType.StatusField:
AppendTextCell(excelColumnNamesData[colInx] + rowIndex.ToString(), col.FieldValue, rowData, 1);
break;
case (int)FieldType.Integer:
case (int)FieldType.Decimal:
AppendNumberCell(excelColumnNamesData[colInx] + rowIndex.ToString(), col.FieldValue, rowData, 1);
break;
default:
break;
}
}
//add the new row to the report
sheetData1.Append(rowData);
rowIndex++;
}
//add the information of the current sheet
worksheet.Append(sheetData1);
//add merged cells
worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
worksheet.Append(drawing);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
//create the new sheet for this report
Sheet sheet = new Sheet() { Name = model.FormName, SheetId = (UInt32Value)1, Id = workbookPart.GetIdOfPart(worksheetPart) };
sheets.Append(sheet);
//add the new sheet to the report
workbook.Append(sheets);
//save all report
workbook.Save();
//close the stream.
document.Close();
}
return report;
}
/// <summary>
/// Generate an excel file with the list of users
/// </summary>
/// <param name="dataSource">The list of users</param>
/// <returns>MemoryStream</returns>
public static MemoryStream GenerateUserReport(List<UserModel> dataSource, string logoPath)
{
MemoryStream ms = new MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
//create the new workbook
WorkbookPart workbookPart = document.AddWorkbookPart();
Workbook workbook = new Workbook();
workbookPart.Workbook = workbook;
// If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
//get and save the stylesheet
Stylesheet stylesheet = VocStyleSheet();
workbookStylesPart.Stylesheet = stylesheet;
workbookStylesPart.Stylesheet.Save();
//add the new workseet
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet();
SheetData sheetData1 = new SheetData();
Sheets sheets = new Sheets();
//get the number of columns in the report
Row rowTitle;
//get the string name of the columns
string[] excelColumnNamesTitle = new string[4];
for (int n = 0; n < 4; n++)
excelColumnNamesTitle[n] = GetExcelColumnName(n);
//build the title
for (int i = 1; i <= 6; i++)
{
rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
for (int cellval = 0; cellval < 4; cellval++)
{
AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
}
sheetData1.Append(rowTitle);
}
MergeCells mergeCells = new MergeCells();
Row currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
//add the business application name
UpdateStringCellValue("A2", Resources.Common.UserList, currentRowTitle, 5);
//merge all cells in the title
MergeCell mergeCell = new MergeCell();
mergeCell.Reference = "A2:D4";
mergeCells.Append(mergeCell);
Drawing drawing = AddLogo(logoPath, worksheetPart);
Columns columns = new Columns();
columns.Append(CreateColumnData((UInt32Value)(uint)1, (UInt32Value)(uint)1, 45));
columns.Append(CreateColumnData((UInt32Value)(uint)2, (UInt32Value)(uint)2, 42));
columns.Append(CreateColumnData((UInt32Value)(uint)3, (UInt32Value)(uint)3, 10));
columns.Append(CreateColumnData((UInt32Value)(uint)4, (UInt32Value)(uint)4, 32));
worksheet.Append(columns);
int rowIndex = 8;
Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex, Resources.Common.Email, rowData, 2);
AppendTextCell("B" + rowIndex, Resources.Common.FullName, rowData, 2);
AppendTextCell("C" + rowIndex, Resources.Common.Active, rowData, 2);
AppendTextCell("D" + rowIndex, Resources.Common.Role, rowData, 2);
sheetData1.Append(rowData);
rowIndex = 9;
//build the data
foreach (var item in dataSource)
{
rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex.ToString(), item.Email, rowData, 1);
AppendTextCell("B" + rowIndex.ToString(), item.FullName, rowData, 1);
AppendTextCell("C" + rowIndex.ToString(), item.IsActive, rowData, 1);
AppendTextCell("D" + rowIndex.ToString(), item.Role, rowData, 1);
sheetData1.Append(rowData);
rowIndex++;
}
//add the information of the current sheet
worksheet.Append(sheetData1);
//add merged cells
worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
worksheet.Append(drawing);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
//create the new sheet for this report
Sheet sheet = new Sheet() { Name = Resources.Common.UserList, SheetId = (UInt32Value)1, Id = workbookPart.GetIdOfPart(worksheetPart) };
sheets.Append(sheet);
//add the new sheet to the report
workbook.Append(sheets);
//save all report
workbook.Save();
//close the stream.
document.Close();
}
return ms;
}
public static void Create(string filename, DataSet ds)
{
Regex regex = new Regex(@"^(-?\d+)(\.\d+)?$");
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook);
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
Workbook workbook = new Workbook();
Sheets sheets = new Sheets();
#region 创建多个 sheet 页
//创建多个sheet
for (int s = 0; s < ds.Tables.Count; s++)
{
DataTable dt = ds.Tables[s];
var tname = dt.TableName;
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
Worksheet worksheet = new Worksheet();
SheetData sheetData = new SheetData();
//创建 sheet 页
Sheet sheet = new Sheet()
{
//页面关联的 WorksheetPart
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = UInt32Value.FromUInt32((uint)s + 1),
Name = tname
};
sheets.Append(sheet);
#region 创建sheet 行
Row row;
uint rowIndex = 1;
//添加表头
row = new Row()
{
RowIndex = UInt32Value.FromUInt32(rowIndex++)
};
sheetData.Append(row);
for (int i = 0; i < dt.Columns.Count; i++)
{
Cell newCell = new Cell();
newCell.CellValue = new CellValue(dt.Columns[i].ColumnName);
newCell.DataType = new EnumValue <CellValues>(CellValues.String);
row.Append(newCell);
}
//添加内容
object val = null;
for (int i = 0; i < dt.Rows.Count; i++)
{
row = new Row()
{
RowIndex = UInt32Value.FromUInt32(rowIndex++)
};
sheetData.Append(row);
for (int j = 0; j < dt.Columns.Count; j++)
{
Cell newCell = new Cell();
val = dt.Rows[i][j];
var str = (val?.ToString() ?? "").Trim();
if (str.Length == 0 || regex.IsMatch(str))
{
newCell.CellValue = new CellValue(str);
newCell.DataType = new EnumValue <CellValues>(CellValues.Number);
}
else
{
newCell.CellValue = new CellValue(val.ToString());
newCell.DataType = new EnumValue <CellValues>(CellValues.String);
}
row.Append(newCell);
}
}
#endregion
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
}
#endregion
workbook.Append(sheets);
workbookpart.Workbook = workbook;
workbookpart.Workbook.Save();
spreadsheetDocument.Close();
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x15" } };
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
FileVersion fileVersion1 = new FileVersion() { ApplicationName = "xl", LastEdited = "6", LowestEdited = "4", BuildVersion = "14420" };
WorkbookProperties workbookProperties1 = new WorkbookProperties() { FilterPrivacy = true, DefaultThemeVersion = (UInt32Value)124226U };
BookViews bookViews1 = new BookViews();
WorkbookView workbookView1 = new WorkbookView() { XWindow = 240, YWindow = 105, WindowWidth = (UInt32Value)14805U, WindowHeight = (UInt32Value)8010U };
bookViews1.Append(workbookView1);
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
Sheet sheet2 = new Sheet() { Name = "Sheet2", SheetId = (UInt32Value)2U, Id = "rId2" };
sheets1.Append(sheet1);
sheets1.Append(sheet2);
CalculationProperties calculationProperties1 = new CalculationProperties() { CalculationId = (UInt32Value)152511U };
workbook1.Append(fileVersion1);
workbook1.Append(workbookProperties1);
workbook1.Append(bookViews1);
workbook1.Append(sheets1);
workbook1.Append(calculationProperties1);
workbookPart1.Workbook = workbook1;
}
/// <summary>
/// Generate the report for catalogue categories
/// </summary>
/// <param name="templatePath">Path of the template</param>
/// <param name="itemSource">Item source</param>
/// <returns>MemoryStream</returns>
public static MemoryStream GenerateCatalogueValueReport(CatalogueValueSearchModel itemSource,string logoPath)
{
MemoryStream ms = new MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
//create the new workbook
WorkbookPart workbookPart = document.AddWorkbookPart();
Workbook workbook = new Workbook();
workbookPart.Workbook = workbook;
// If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
//get and save the stylesheet
Stylesheet stylesheet = VestalisStyleSheet();
workbookStylesPart.Stylesheet = stylesheet;
workbookStylesPart.Stylesheet.Save();
//add the new workseet
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet();
SheetData sheetData1 = new SheetData();
Sheets sheets = new Sheets();
//get the number of columns in the report
Row rowTitle;
//get the string name of the columns
string[] excelColumnNamesTitle = new string[2];
for (int n = 0; n < 2; n++)
excelColumnNamesTitle[n] = GetExcelColumnName(n);
//build the title
for (int i = 1; i <= 6; i++)
{
rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
for (int cellval = 0; cellval < 2; cellval++)
{
AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
}
sheetData1.Append(rowTitle);
}
MergeCells mergeCells = new MergeCells();
Row currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
//add the business application name
UpdateStringCellValue("B2", LanguageResource.CatalogueValuesReport, currentRowTitle, 5);
//merge all cells in the title
MergeCell mergeCell = new MergeCell();
mergeCell.Reference = "B2:B4";
mergeCells.Append(mergeCell);
Drawing drawing = AddLogo(logoPath, worksheetPart);
Columns columns = new Columns();
columns.Append(CreateColumnData((UInt32Value)(uint)1, (UInt32Value)(uint)1, 50));
columns.Append(CreateColumnData((UInt32Value)(uint)2, (UInt32Value)(uint)2, 71));
worksheet.Append(columns);
int rowIndex = 8;
Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex, LanguageResource.BusinessApplicationName, rowData, 2);
AppendTextCell("B" + rowIndex, itemSource.BusinessApplicatioName, rowData, 1);
sheetData1.Append(rowData);
rowIndex = 9;
rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex, LanguageResource.CatalogueName, rowData, 2);
AppendTextCell("B" + rowIndex, itemSource.CatalogueSelectedName, rowData, 1);
sheetData1.Append(rowData);
rowIndex = 11;
rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex.ToString(), LanguageResource.Value, rowData, 2);
AppendTextCell("B" + rowIndex.ToString(), LanguageResource.Description, rowData, 2);
sheetData1.Append(rowData);
rowIndex = 12;
foreach (var item in itemSource.SearchResult.Collection)
{
rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex.ToString(), item.CatalogueValueData, rowData, 1);
AppendTextCell("B" + rowIndex.ToString(), item.CatalogueValueDescription, rowData, 1);
sheetData1.Append(rowData);
rowIndex++;
}
//add the information of the current sheet
worksheet.Append(sheetData1);
//add merged cells
worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
worksheet.Append(drawing);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
//create the new sheet for this report
Sheet sheet = new Sheet() { Name = LanguageResource.Report, SheetId = (UInt32Value)1, Id = workbookPart.GetIdOfPart(worksheetPart) };
sheets.Append(sheet);
//add the new sheet to the report
workbook.Append(sheets);
//save all report
workbook.Save();
//close the stream.
document.Close();
}
return ms;
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x15" } };
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
FileVersion fileVersion1 = new FileVersion(){ ApplicationName = "xl", LastEdited = "6", LowestEdited = "6", BuildVersion = "14420" };
WorkbookProperties workbookProperties1 = new WorkbookProperties(){ DefaultThemeVersion = (UInt32Value)153222U };
AlternateContent alternateContent1 = new AlternateContent();
alternateContent1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
AlternateContentChoice alternateContentChoice1 = new AlternateContentChoice(){ Requires = "x15" };
X15ac.AbsolutePath absolutePath1 = new X15ac.AbsolutePath(){ Url = "D:\\Users\\dito\\Desktop\\TestDocumentResaver\\OpenXmlApiConversion\\Slicer\\" };
absolutePath1.AddNamespaceDeclaration("x15ac", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac");
alternateContentChoice1.Append(absolutePath1);
alternateContent1.Append(alternateContentChoice1);
BookViews bookViews1 = new BookViews();
WorkbookView workbookView1 = new WorkbookView(){ XWindow = 0, YWindow = 0, WindowWidth = (UInt32Value)26940U, WindowHeight = (UInt32Value)15120U };
bookViews1.Append(workbookView1);
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet(){ Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
sheets1.Append(sheet1);
DefinedNames definedNames1 = new DefinedNames();
DefinedName definedName1 = new DefinedName(){ Name = "Slicer_Column1" };
definedName1.Text = "#N/A";
DefinedName definedName2 = new DefinedName(){ Name = "Slicer_Column2" };
definedName2.Text = "#N/A";
DefinedName definedName3 = new DefinedName(){ Name = "Slicer_Column3" };
definedName3.Text = "#N/A";
definedNames1.Append(definedName1);
definedNames1.Append(definedName2);
definedNames1.Append(definedName3);
CalculationProperties calculationProperties1 = new CalculationProperties(){ CalculationId = (UInt32Value)152511U };
WorkbookExtensionList workbookExtensionList1 = new WorkbookExtensionList();
WorkbookExtension workbookExtension1 = new WorkbookExtension(){ Uri = "{79F54976-1DA5-4618-B147-4CDE4B953A38}" };
workbookExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
X14.WorkbookProperties workbookProperties2 = new X14.WorkbookProperties();
workbookExtension1.Append(workbookProperties2);
WorkbookExtension workbookExtension2 = new WorkbookExtension(){ Uri = "{46BE6895-7355-4a93-B00E-2C351335B9C9}" };
workbookExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.SlicerCaches slicerCaches1 = new X15.SlicerCaches();
slicerCaches1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
X14.SlicerCache slicerCache1 = new X14.SlicerCache(){ Id = "rId2" };
X14.SlicerCache slicerCache2 = new X14.SlicerCache(){ Id = "rId3" };
X14.SlicerCache slicerCache3 = new X14.SlicerCache(){ Id = "rId4" };
slicerCaches1.Append(slicerCache1);
slicerCaches1.Append(slicerCache2);
slicerCaches1.Append(slicerCache3);
workbookExtension2.Append(slicerCaches1);
WorkbookExtension workbookExtension3 = new WorkbookExtension(){ Uri = "{140A7094-0E35-4892-8432-C4D2E57EDEB5}" };
workbookExtension3.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.WorkbookProperties workbookProperties3 = new X15.WorkbookProperties(){ ChartTrackingReferenceBase = true };
workbookExtension3.Append(workbookProperties3);
workbookExtensionList1.Append(workbookExtension1);
workbookExtensionList1.Append(workbookExtension2);
workbookExtensionList1.Append(workbookExtension3);
workbook1.Append(fileVersion1);
workbook1.Append(workbookProperties1);
workbook1.Append(alternateContent1);
workbook1.Append(bookViews1);
workbook1.Append(sheets1);
workbook1.Append(definedNames1);
workbook1.Append(calculationProperties1);
workbook1.Append(workbookExtensionList1);
workbookPart1.Workbook = workbook1;
}
//создает нову книгу с листами Excel
protected virtual void GenWorkbookPart(string sheetName, WorkbookPart workbookPart)
{
Workbook workbook = new Workbook();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet() { Name = sheetName, SheetId = (UInt32Value)1U, Id = "rId1" };
sheets.Append(sheet);
workbook.Append(sheets);
workbookPart.Workbook = workbook;
}
public void convertToExcel(string padXml)
{
//maak dataset en vul met ingevoerde xml
DataSet dsXML = new DataSet();
dsXML.ReadXml(padXml);
DataTable tblXML = dsXML.Tables[2];
//opslaan
string padXlsx = Path.GetDirectoryName(padXml) + DateTime.Now.Day + "-" + DateTime.Now.Month + "-" + DateTime.Now.Year + ".xlsx";
//spreadsheet
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(padXlsx, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
Workbook workbook = new Workbook();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
Worksheet worksheet = new Worksheet();
SheetData sheetData = new SheetData();
//styles
WorkbookStylesPart stylesPart = spreadsheetDocument.WorkbookPart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = new Stylesheet();
stylesPart.Stylesheet.Save();
//kolommen
DocumentFormat.OpenXml.Spreadsheet.Columns columns = new Columns();
for (int i = 1; i < tblXML.Columns.Count; i++)
{
Column column = new Column();
column.Min = Convert.ToUInt32(i);
column.Max = Convert.ToUInt32(i + 1);
int lengte = tblXML.Rows[13][i - 1].ToString().Length;
if (lengte < 10)
{
lengte = 10;
}
else if (lengte > 35)
{
lengte = 35;
}
else
{
lengte += 5;
}
column.Width = lengte;
column.BestFit = true;
columns.Append(column);
}
worksheet.Append(columns);
//header
Row header = new Row();
header.RowIndex = (UInt32)1;
foreach (DataColumn columnInDataTable in tblXML.Columns)
{
Cell headerCell = createTextCell(tblXML.Columns.IndexOf(columnInDataTable) + 1, 1, columnInDataTable.ColumnName);
//headerCell.StyleIndex = 1;
header.AppendChild(headerCell);
}
sheetData.AppendChild(header);
// Add a row to the cell table.
for (int i = 1; i < tblXML.Rows.Count; i++)
{
Row row;
row = new Row()
{
RowIndex = Convert.ToUInt32(i) + 1
};
for (int j = 0; j < tblXML.Columns.Count; j++)
{
Cell newCell = new Cell()
{
CellReference = getColumnName(i),
DataType = CellValues.Number,
//StyleIndex = 6,
CellValue = new CellValue(tblXML.Rows[i][j].ToString()),
};
row.Append(newCell);
}
sheetData.Append(row);
}
//autofilter
string laatsteKolom = zoekLaatsteKolom(tblXML);
AutoFilter autoFilter = new AutoFilter();
autoFilter.Reference = "A1:" + laatsteKolom + "1";
//aantal auto's
Row aantalAutosRow = new Row();
aantalAutosRow.RowIndex = Convert.ToUInt32((tblXML.Rows.Count) + 2);
Cell aantalAutosCell = new Cell();
aantalAutosCell.CellReference = getColumnName(1);
aantalAutosCell.CellValue = new CellValue("Hoi");
aantalAutosCell.DataType = CellValues.String;
//CellFormula berekenAantalAutos = new CellFormula();
//berekenAantalAutos.Text = "=COUNTA(A2:A" + tblXML.Rows.Count.ToString();
//aantalAutosCell.Append(berekenAantalAutos);
aantalAutosRow.Append(aantalAutosCell);
sheetData.Append(aantalAutosRow);
worksheet.Append(sheetData);
worksheet.Append(autoFilter);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = "Voorraad";
sheet.SheetId = 1;
sheet.Id = workbookPart.GetIdOfPart(worksheetPart);
sheets.Append(sheet);
workbook.Append(fv);
workbook.Append(sheets);
spreadsheetDocument.WorkbookPart.Workbook = workbook;
spreadsheetDocument.WorkbookPart.Workbook.Save();
spreadsheetDocument.Close();
}
}
/// <summary>
/// Call this method from the Page providing the desired information
/// </summary>
/// <param name="dataTable">The records to be written in excel</param>
/// <param name="excelFilename">Name of the file</param>
/// <param name="sheetName">Name of the sheet</param>
/// <param name="filters">Search key and value based on which the datatable is generated</param>
/// <param name="columnSize">column name and size</param>
/// <returns></returns>
public static bool CreateExcelDocument(DataTable dataTable, string excelFilename, string sheetName, Dictionary <string, string> filters, Dictionary <string, int> columnSize)
{
try
{
using (SpreadsheetDocument objExcelDoc = SpreadsheetDocument.Create(excelFilename, SpreadsheetDocumentType.Workbook))
{
int cellSize;
WorkbookPart wbp = objExcelDoc.AddWorkbookPart();
WorksheetPart wsp = wbp.AddNewPart <WorksheetPart>();
Workbook wb = new Workbook();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
Worksheet workSheet = new Worksheet();
WorkbookStylesPart wbsp = wbp.AddNewPart <WorkbookStylesPart>();
wbsp.Stylesheet = CreateStylesheet();
wbsp.Stylesheet.Save();
Columns columns = new Columns();
for (int i = 1; i <= columnSize.Count(); i++)
{
columnSize.TryGetValue(columnSize.Keys.ElementAt(i - 1).ToString(), out cellSize);
columns.Append(CreateColumnData(Convert.ToUInt32(i), Convert.ToUInt32(i), cellSize));
}
workSheet.Append(columns);
SheetData sheetData = new SheetData();
for (UInt32 i = 2; i <= 1 + filters.Count(); i++)
{
sheetData.Append(CreateFilters(i, filters));
}
sheetData.Append(CreateColumnHeader(Convert.ToUInt32(filters.Count() + 3), columnSize));
UInt32 index = Convert.ToUInt32(filters.Count() + 4);
foreach (DataRow dr in dataTable.Rows)
{
sheetData.Append(CreateContent(index, dr, columnSize.Count()));
index++;
}
workSheet.Append(sheetData);
wsp.Worksheet = workSheet;
Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = sheetName;
sheet.SheetId = 1;
sheet.Id = wbp.GetIdOfPart(wsp);
sheets.Append(sheet);
wb.Append(fv);
wb.Append(sheets);
objExcelDoc.WorkbookPart.Workbook = wb;
objExcelDoc.WorkbookPart.Workbook.Save();
objExcelDoc.Close();
}
}
catch (Exception ex)
{
throw;
}
return(true);
}
public ActionResult ConvertToExcel()
{
MemoryStream ms = new MemoryStream();
SpreadsheetDocument xl = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);
WorkbookPart wbp = xl.AddWorkbookPart();
WorksheetPart wsp = wbp.AddNewPart <WorksheetPart>();
Workbook wb = new Workbook();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
Worksheet ws = new Worksheet();
SheetData sd = new SheetData();
var list = GetPerson();
Row r1 = new Row();
Cell c1 = new Cell();
Cell c2 = new Cell();
Cell c3 = new Cell();
Cell c4 = new Cell();
c1.DataType = CellValues.String;
c2.DataType = CellValues.String;
c3.DataType = CellValues.String;
c4.DataType = CellValues.String;
c1.CellValue = new CellValue("Ad");
c2.CellValue = new CellValue("Soyad");
c3.CellValue = new CellValue("Adres");
c4.CellValue = new CellValue("Email");
r1.Append(c1);
r1.Append(c2);
r1.Append(c3);
r1.Append(c4);
sd.Append(r1);
foreach (var item in list.Persons)
{
Row r2 = new Row();
Cell c5 = new Cell();
Cell c6 = new Cell();
Cell c7 = new Cell();
Cell c8 = new Cell();
c5.DataType = CellValues.String;
c6.DataType = CellValues.String;
c7.DataType = CellValues.String;
c8.DataType = CellValues.String;
c5.CellValue = new CellValue(item.Name);
c6.CellValue = new CellValue(item.Surname);
c7.CellValue = new CellValue(item.Address);
c8.CellValue = new CellValue(item.Email);
r2.Append(c5);
r2.Append(c6);
r2.Append(c7);
r2.Append(c8);
sd.Append(r2);
}
ws.Append(sd);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = "first sheet";
sheet.SheetId = 1;
sheet.Id = wbp.GetIdOfPart(wsp);
sheets.Append(sheet);
wb.Append(fv);
wb.Append(sheets);
xl.WorkbookPart.Workbook = wb;
xl.WorkbookPart.Workbook.Save();
xl.Close();
string fileName = "testOpenXml.xlsx";
Response.Clear();
byte[] dt = ms.ToArray();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", fileName));
Response.BinaryWrite(dt);
Response.Flush();
Response.End();
return(View());
}
private void GenerateWorksheetAContentSubsectorPollutionSourceFieldSheet(WorksheetPart worksheetPart, Workbook workbook, string SheetName, int SheetOrdinal, bool ActivePollutionSource)
{
BaseEnumService baseEnumService = new BaseEnumService(_TaskRunnerBaseService._BWObj.appTaskModel.Language);
TVItemService tvItemService = new TVItemService(_TaskRunnerBaseService._BWObj.appTaskModel.Language, _TaskRunnerBaseService._User);
PolSourceSiteService polSourceSiteService = new PolSourceSiteService(_TaskRunnerBaseService._BWObj.appTaskModel.Language, _TaskRunnerBaseService._User);
//PolSourceObservationService polSourceObservationService = new PolSourceObservationService(_TaskRunnerBaseService._BWObj.appTaskModel.Language, _TaskRunnerBaseService._User);
//MWQMSubsectorService mwqmSubsectorService = new MWQMSubsectorService(_TaskRunnerBaseService._BWObj.appTaskModel.Language, _TaskRunnerBaseService._User);
MergeCells mergeCells = new MergeCells();
Row row = new Row();
Cell cell = new Cell();
Hyperlinks hyperlinks = new Hyperlinks();
string Id = XlsxBase.sheetNameAndIDList[SheetOrdinal].SheetID;
XlsxBase.CurrentColumn = 0;
XlsxBase.CurrentRow = 0;
XlsxBase.CurrentColumnProp = 0;
Worksheet worksheet = new Worksheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac"
}
};
worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
worksheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
worksheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
SheetViews sheetViews = new SheetViews();
SheetView sheetView = new SheetView()
{
TabSelected = true, WorkbookViewId = (UInt32Value)0U
};
Selection selection = new Selection()
{
ActiveCell = "A1", SequenceOfReferences = new ListValue <StringValue>()
{
InnerText = "A1"
}
};
sheetView.Append(selection);
sheetViews.Append(sheetView);
SheetFormatProperties sheetFormatProperties = new SheetFormatProperties()
{
DefaultRowHeight = 15D, DyDescent = 0.25D
};
Columns columns = new Columns();
List <double?> columnWidthList = new List <double?>()
{
7D, 10D, 90D, 12D, 12D, 10D, 14D, 20D
};
foreach (double?width in columnWidthList)
{
Column colum = XlsxBase.AddColumnProp(columns, width);
}
SheetData sheetData = new SheetData();
TVItemModel tvItemModelSubsector = tvItemService.GetTVItemModelWithTVItemIDDB(_TaskRunnerBaseService._BWObj.appTaskModel.TVItemID);
//MWQMSubsectorModel mwqmSubsectorModel = mwqmSubsectorService.GetMWQMSubsectorModelWithMWQMSubsectorTVItemIDDB(_TaskRunnerBaseService._BWObj.appTaskModel.TVItemID);
XlsxBase.CurrentRowHeight = 28D;
XlsxBase.CurrentFontSize = 24;
XlsxBase.CurrentHorizontalAlignmentValue = HorizontalAlignmentValues.Center;
XlsxBase.CurrentBorderStyleValue = BorderStyleValues.Thin;
XlsxBase.CurrentBottomBorder = true;
row = XlsxBase.AddRow();
string URL = _TaskRunnerBaseService.GetUrlFromTVItem(tvItemModelSubsector);
XlsxBase.CurrentFontColor = System.Drawing.Color.Blue;
cell = XlsxBase.AddCellHyperlink(hyperlinks, row, tvItemModelSubsector.TVText, URL);
XlsxBase.CurrentFontColor = null;
//cell = XlsxBase.AddCellString(row, tvItemModel.TVText);
cell = XlsxBase.AddCellString(row, null);
sheetData.Append(row);
MergeCell mergeCell = new MergeCell()
{
Reference = "A" + XlsxBase.CurrentRow.ToString() + ":H" + XlsxBase.CurrentRow.ToString()
};
mergeCells.Append(mergeCell);
List <TVItemModel> tvItemModelPolSourceList = tvItemService.GetChildrenTVItemModelListWithTVItemIDAndTVTypeDB(_TaskRunnerBaseService._BWObj.appTaskModel.TVItemID, TVTypeEnum.PolSourceSite);
XlsxBase.CurrentRowHeight = 24D;
XlsxBase.CurrentFontSize = 18;
XlsxBase.CurrentBorderStyleValue = BorderStyleValues.Thin;
XlsxBase.CurrentBottomBorder = true;
XlsxBase.CurrentHorizontalAlignmentValue = HorizontalAlignmentValues.Center;
row = XlsxBase.AddRow();
XlsxBase.CurrentFontColor = null;
cell = XlsxBase.AddCellString(row, "Site");
cell = XlsxBase.AddCellString(row, "Type");
cell = XlsxBase.AddCellString(row, "Observation");
cell = XlsxBase.AddCellString(row, "Lat");
cell = XlsxBase.AddCellString(row, "Lng");
cell = XlsxBase.AddCellString(row, "Active");
cell = XlsxBase.AddCellString(row, "Update");
cell = XlsxBase.AddCellString(row, "Civic Address");
sheetData.Append(row);
XlsxBase.CurrentRowHeight = 160D;
XlsxBase.CurrentFontSize = 12;
XlsxBase.CurrentHorizontalAlignmentValue = HorizontalAlignmentValues.Left;
List <PolSourceSiteModel> polSourceSiteModelList = polSourceSiteService.GetPolSourceSiteModelListWithSubsectorTVItemIDDB(tvItemModelSubsector.TVItemID);
int countPolSourceSite = 0;
foreach (PolSourceSiteModel polSourceSiteModel in polSourceSiteModelList.OrderBy(c => c.Site).ToList())
{
bool IsActive = (from c in tvItemModelPolSourceList
where c.TVItemID == polSourceSiteModel.PolSourceSiteTVItemID
select c.IsActive).FirstOrDefault();
if (ActivePollutionSource != IsActive)
{
continue;
}
countPolSourceSite += 1;
PolSourceObservationModel polSourceObservationModel = polSourceSiteService._PolSourceObservationService.GetPolSourceObservationModelLatestWithPolSourceSiteTVItemIDDB(polSourceSiteModel.PolSourceSiteTVItemID);
List <MapInfoPointModel> mapInfoPointModelList = polSourceSiteService._MapInfoService._MapInfoPointService.GetMapInfoPointModelListWithTVItemIDAndTVTypeAndMapInfoDrawTypeDB(polSourceSiteModel.PolSourceSiteTVItemID, TVTypeEnum.PolSourceSite, MapInfoDrawTypeEnum.Point);
row = XlsxBase.AddRow();
if (countPolSourceSite % 5 == 0)
{
XlsxBase.CurrentBorderStyleValue = BorderStyleValues.Thin;
XlsxBase.CurrentBottomBorder = true;
}
else
{
XlsxBase.CurrentBorderStyleValue = null;
XlsxBase.CurrentBottomBorder = false;
}
XlsxBase.CurrentHorizontalAlignmentValue = HorizontalAlignmentValues.Center;
cell = XlsxBase.AddCellString(row, polSourceSiteModel.Site.ToString());
if (polSourceObservationModel != null)
{
List <PolSourceObservationIssueModel> polSourceObservationIssueModelList = polSourceSiteService._PolSourceObservationService._PolSourceObservationIssueService.GetPolSourceObservationIssueModelListWithPolSourceObservationIDDB(polSourceObservationModel.PolSourceObservationID);
string SelectedObservation = "Selected: \r\n";
int PolSourceObsInfoInt = 0;
foreach (PolSourceObservationIssueModel polSourceObservationIssueModel in polSourceObservationIssueModelList)
{
List <int> observationInfoList = polSourceObservationIssueModel.ObservationInfo.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries).ToList().Select(c => int.Parse(c)).ToList();
PolSourceObsInfoInt = observationInfoList.Where(c => (c > 10500 && c < 10599) || (c > 15200 && c < 15299)).FirstOrDefault();
foreach (PolSourceObsInfoEnum polSourceObsInfo in polSourceObservationIssueModel.PolSourceObsInfoList)
{
SelectedObservation += baseEnumService.GetEnumText_PolSourceObsInfoReportEnum(polSourceObsInfo);
}
SelectedObservation += "\r\n\r\n";
}
if (PolSourceObsInfoInt > 0)
{
cell = XlsxBase.AddCellString(row, baseEnumService.GetEnumText_PolSourceObsInfoEnum((PolSourceObsInfoEnum)PolSourceObsInfoInt));
}
else
{
cell = XlsxBase.AddCellString(row, "Empty");
}
}
XlsxBase.CurrentHorizontalAlignmentValue = HorizontalAlignmentValues.Left;
XlsxBase.WrapText = true;
if (polSourceObservationModel != null)
{
List <PolSourceObservationIssueModel> polSourceObservationIssueModelList = polSourceSiteService._PolSourceObservationService._PolSourceObservationIssueService.GetPolSourceObservationIssueModelListWithPolSourceObservationIDDB(polSourceObservationModel.PolSourceObservationID);
string SelectedObservation = "Selected: \r\n";
int PolSourceObsInfoInt = 0;
foreach (PolSourceObservationIssueModel polSourceObservationIssueModel in polSourceObservationIssueModelList)
{
List <int> observationInfoList = polSourceObservationIssueModel.ObservationInfo.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries).ToList().Select(c => int.Parse(c)).ToList();
foreach (PolSourceObsInfoEnum polSourceObsInfo in polSourceObservationIssueModel.PolSourceObsInfoList)
{
SelectedObservation += baseEnumService.GetEnumText_PolSourceObsInfoReportEnum(polSourceObsInfo);
}
SelectedObservation += "\r\n\r\n";
}
if (PolSourceObsInfoInt > 0)
{
cell = XlsxBase.AddCellString(row, "Written: \r\n" + (string.IsNullOrWhiteSpace(polSourceObservationModel.Observation_ToBeDeleted) ? "" : polSourceObservationModel.Observation_ToBeDeleted.ToString()) + "\r\n\r\n"
+ SelectedObservation);
}
else
{
cell = XlsxBase.AddCellString(row, "Written: \r\n\r\n" + SelectedObservation);
}
}
XlsxBase.CurrentHorizontalAlignmentValue = HorizontalAlignmentValues.Center;
Alignment alignment1 = new Alignment()
{
WrapText = true
};
if (mapInfoPointModelList.Count > 0)
{
cell = XlsxBase.AddCellString(row, mapInfoPointModelList[0].Lat.ToString("F5"));
cell = XlsxBase.AddCellString(row, mapInfoPointModelList[0].Lng.ToString("F5"));
}
else
{
cell = XlsxBase.AddCellString(row, "");
cell = XlsxBase.AddCellString(row, "");
}
TVItemModel tvItemModelPolSource = tvItemService.GetTVItemModelWithTVItemIDDB(polSourceSiteModel.PolSourceSiteTVItemID);
cell = XlsxBase.AddCellString(row, tvItemModelPolSource.IsActive.ToString());
cell = XlsxBase.AddCellString(row, polSourceObservationModel.ObservationDate_Local.ToString("yyyy MMM dd"));
cell = XlsxBase.AddCellString(row, "empty");
sheetData.Append(row);
}
if (countPolSourceSite > 0)
{
DefinedNames definedNames1 = new DefinedNames();
DefinedName definedName1 = new DefinedName()
{
Name = "_xlnm._FilterDatabase", LocalSheetId = (UInt32Value)0U, Hidden = true
};
definedName1.Text = "'" + SheetName + "'" + "!$A$2:$H$" + (countPolSourceSite + 2).ToString();
definedNames1.Append(definedName1);
workbook.Append(definedNames1);
AutoFilter autoFilter = new AutoFilter()
{
Reference = "a2:B" + (countPolSourceSite + 2).ToString()
};
worksheet.Append(autoFilter);
}
PageMargins pageMargins = new PageMargins()
{
Left = 0.7D, Right = 0.7D, Top = 0.75D, Bottom = 0.75D, Header = 0.3D, Footer = 0.3D
};
PageSetup pageSetup = new PageSetup()
{
Orientation = OrientationValues.Portrait, Id = "rId" + SheetOrdinal.ToString()
};
worksheet.Append(sheetViews);
worksheet.Append(sheetFormatProperties);
if (columns.ChildElements.Count > 0)
{
worksheet.Append(columns);
}
worksheet.Append(sheetData);
mergeCells.Count = (UInt32Value)((UInt32)mergeCells.ChildElements.Count);
if (mergeCells.ChildElements.Count > 0)
{
worksheet.Append(mergeCells);
}
if (XlsxBase.UsedHyperlinkList.Count > 0)
{
worksheet.Append(hyperlinks);
}
worksheet.Append(pageMargins);
worksheet.Append(pageSetup);
worksheetPart.Worksheet = worksheet;
}
private void BuildWorkbook(string filename)
{
try
{
using (SpreadsheetDocument xl = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
{
var wbp = xl.AddWorkbookPart();
var wsp = wbp.AddNewPart <WorksheetPart>();
var wb = new Workbook();
var fv = new FileVersion {
ApplicationName = "Microsoft Office Excel"
};
var ws = new Worksheet();
var sd = new SheetData();
var wbsp = wbp.AddNewPart <WorkbookStylesPart>();
wbsp.Stylesheet = CreateStylesheet();
wbsp.Stylesheet.Save();
var sImagePath = "polymathlogo.png";
var dp = wsp.AddNewPart <DrawingsPart>();
var imgp = dp.AddImagePart(ImagePartType.Png, wsp.GetIdOfPart(dp));
using (FileStream fs = new FileStream(sImagePath, FileMode.Open))
{
imgp.FeedData(fs);
}
var nvdp = new NonVisualDrawingProperties
{
Id = 1025,
Name = "Picture 1",
Description = "polymathlogo"
};
var picLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks
{
NoChangeAspect = true,
NoChangeArrowheads = true
};
var nvpdp = new NonVisualPictureDrawingProperties
{
PictureLocks = picLocks
};
var nvpp = new NonVisualPictureProperties
{
NonVisualDrawingProperties = nvdp,
NonVisualPictureDrawingProperties = nvpdp
};
var stretch = new DocumentFormat.OpenXml.Drawing.Stretch
{
FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle()
};
var blip = new DocumentFormat.OpenXml.Drawing.Blip
{
Embed = dp.GetIdOfPart(imgp),
CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print
};
var blipFill = new BlipFill
{
Blip = blip,
SourceRectangle = new DocumentFormat.OpenXml.Drawing.SourceRectangle()
};
blipFill.Append(stretch);
var offset = new DocumentFormat.OpenXml.Drawing.Offset
{
X = 0,
Y = 0
};
var t2d = new DocumentFormat.OpenXml.Drawing.Transform2D
{
Offset = offset
};
var bm = Xwt.Drawing.Image.FromFile(sImagePath).ToBitmap();
//http://en.wikipedia.org/wiki/English_Metric_Unit#DrawingML
//http://stackoverflow.com/questions/1341930/pixel-to-centimeter
//http://stackoverflow.com/questions/139655/how-to-convert-pixels-to-points-px-to-pt-in-net-c
var extents = new DocumentFormat.OpenXml.Drawing.Extents
{
Cx = (long)bm.Width * (long)((float)914400 / bm.PixelWidth),
Cy = (long)bm.Height * (long)((float)914400 / bm.PixelHeight)
};
bm.Dispose();
t2d.Extents = extents;
var prstGeom = new DocumentFormat.OpenXml.Drawing.PresetGeometry
{
Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle,
AdjustValueList = new DocumentFormat.OpenXml.Drawing.AdjustValueList()
};
var sp = new ShapeProperties
{
BlackWhiteMode = DocumentFormat.OpenXml.Drawing.BlackWhiteModeValues.Auto,
Transform2D = t2d
};
sp.Append(prstGeom);
sp.Append(new DocumentFormat.OpenXml.Drawing.NoFill());
var picture = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture
{
NonVisualPictureProperties = nvpp,
BlipFill = blipFill,
ShapeProperties = sp
};
var pos = new Position {
X = 0, Y = 0
};
Extent ext = new Extent {
Cx = extents.Cx, Cy = extents.Cy
};
var anchor = new AbsoluteAnchor
{
Position = pos,
Extent = ext
};
anchor.Append(picture);
anchor.Append(new ClientData());
var wsd = new WorksheetDrawing();
wsd.Append(anchor);
var drawing = new Drawing {
Id = dp.GetIdOfPart(imgp)
};
wsd.Save(dp);
UInt32 index;
Random rand = new Random();
sd.Append(CreateHeader(10));
sd.Append(CreateColumnHeader(11));
for (index = 12; index < 30; ++index)
{
sd.Append(CreateContent(index, ref rand));
}
ws.Append(sd);
ws.Append(drawing);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet
{
Name = "Sheet1",
SheetId = 1,
Id = wbp.GetIdOfPart(wsp)
};
sheets.Append(sheet);
wb.Append(fv);
wb.Append(sheets);
xl.WorkbookPart.Workbook = wb;
xl.WorkbookPart.Workbook.Save();
xl.Close();
}
}
catch (Exception e)
{
Debug.WriteLine(e.ToString());
}
}
public void Create <T>(
string fileName,
List <T> objects,
string sheetName,
List <string> headerNames)
{
//Open the copied template workbook.
using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = myWorkbook.AddWorkbookPart();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
// Create Styles and Insert into Workbook
WorkbookStylesPart stylesPart = myWorkbook.WorkbookPart.AddNewPart <WorkbookStylesPart>();
Stylesheet styles = new CustomStylesheet();
styles.Save(stylesPart);
string relId = workbookPart.GetIdOfPart(worksheetPart);
Workbook workbook = new Workbook();
FileVersion fileVersion = new FileVersion {
ApplicationName = "Microsoft Office Excel"
};
SheetData sheetData = CreateSheetData <T>(objects, headerNames, stylesPart);
Worksheet worksheet = new Worksheet();
int numCols = headerNames.Count;
int width = headerNames.Max(h => h.Length) + 10;
Columns columns = new Columns();
for (int col = 0; col < numCols; col++)
{
if (col == 0)
{
width = 30;
}
else if (col == 1)
{
width = 30;
}
else if (col == 2)
{
width = 40;
}
else if (col == 3)
{
width = 72;
}
else if (col == 4)
{
width = 76;
}
else if (col == 5)
{
width = 12;
}
else if (col == 6)
{
width = 15;
}
else if (col == 7)
{
width = 12;
}
else if (col == 8)
{
width = 15;
}
Column c = CreateColumnData((UInt32)col + 1, (UInt32)numCols + 1, width);
columns.AppendChild(c);
}
worksheet.Append(columns);
Sheets sheets = new Sheets();
Sheet sheet = new Sheet {
Name = sheetName, SheetId = 1, Id = relId
};
sheets.Append(sheet);
workbook.Append(fileVersion);
workbook.Append(sheets);
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
myWorkbook.WorkbookPart.Workbook = workbook;
myWorkbook.WorkbookPart.Workbook.Save();
myWorkbook.Close();
}
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="objects"></param>
/// <param name="sheetName">generated sheet name, Do not set empty value for this parameters</param>
/// <param name="headerNames"></param>
/// <returns></returns>
public Stream Do <T>(List <T> objects, string sheetName, ExcelHeaderList headerNames)
{
var stream = new MemoryStream();
sheetName = string.IsNullOrEmpty(sheetName) ? "Mayhedi_Sheet" : sheetName;
using (var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
{
var workbookPart = document.AddWorkbookPart();
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>(sheetName);
// Create Styles and Insert into Workbook
var stylesPart = document.WorkbookPart.AddNewPart <WorkbookStylesPart>();
Stylesheet styles = new CustomStylesheet();
styles.Save(stylesPart);
var relId = workbookPart.GetIdOfPart(worksheetPart);
var workbook = new Workbook();
var fileVersion = new FileVersion {
ApplicationName = "Microsoft Office Excel"
};
var data = objects.Select(o => (object)o).ToList();
if (headerNames == null)
{
headerNames = new ExcelHeaderList();
foreach (var o in ObjUtility.GetPropertyInfo(objects[0]))
{
headerNames.Add(o, o);
}
}
var sheetData = CreateSheetData(data, headerNames, stylesPart);
var worksheet = new Worksheet();
var numCols = headerNames.Count;
var width = 20;//headerNames.Max(h => h.Length) + 5;
var columns = new Columns();
for (var col = 0; col < numCols; col++)
{
var c = CreateColumnData((UInt32)col + 1, (UInt32)numCols + 1, width);
if (c != null)
{
columns.Append(c);
}
}
worksheet.Append(columns);
var sheets = new Sheets();
var sheet = new Sheet {
Name = sheetName, SheetId = 1, Id = relId
};
sheets.Append(sheet);
workbook.Append(fileVersion);
workbook.Append(sheets);
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
document.WorkbookPart.Workbook = workbook;
document.WorkbookPart.Workbook.Save();
document.Close();
}
return(stream);
}
private void HandleSaveAndClose(bool save, bool close)
{
if (save)
{
// Create dialog
SaveFileDialog saveFileDialog1 = new SaveFileDialog
{
Filter = "Excel|*.xlsx",
Title = "Save file"
};
// Open dialog
saveFileDialog1.ShowDialog();
// Save data when filename is present
if (saveFileDialog1.FileName != "")
{
if (File.Exists(saveFileDialog1.FileName))
{
File.Delete(saveFileDialog1.FileName);
}
using (FileStream fileStream = (FileStream)saveFileDialog1.OpenFile())
using (var spreadsheetDocument = SpreadsheetDocument.Create(fileStream, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
Workbook workbook = new Workbook();
workbookPart.Workbook = workbook;
Sheets sheets = new Sheets();
workbook.Append(sheets);
Sheet sheet = new Sheet()
{
SheetId = 1, Id = "rId1", Name = "Events",
};
sheets.Append(sheet);
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>("rId1");
Worksheet worksheet = new Worksheet();
SheetData sheetData = new SheetData();
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
UInt32Value rowIndex = 1;
Row row = new Row()
{
RowIndex = rowIndex
};
row.Append(new Cell()
{
CellReference = $"A{rowIndex}", DataType = CellValues.String, CellValue = new CellValue($"Timestamp")
});
row.Append(new Cell()
{
CellReference = $"B{rowIndex}", DataType = CellValues.String, CellValue = new CellValue($"ID")
});
row.Append(new Cell()
{
CellReference = $"C{rowIndex}", DataType = CellValues.String, CellValue = new CellValue($"Type")
});
row.Append(new Cell()
{
CellReference = $"D{rowIndex}", DataType = CellValues.String, CellValue = new CellValue($"EventCode")
});
row.Append(new Cell()
{
CellReference = $"E{rowIndex}", DataType = CellValues.String, CellValue = new CellValue($"EventDescription")
});
row.Append(new Cell()
{
CellReference = $"F{rowIndex}", DataType = CellValues.String, CellValue = new CellValue($"Information")
});
sheetData.Append(row);
foreach (var fiscalArchiveEvent in _fiscalArchiveEvents)
{
rowIndex++;
row = new Row()
{
RowIndex = rowIndex
};
row.Append(new Cell()
{
CellReference = $"A{rowIndex}", DataType = CellValues.String, CellValue = new CellValue($"{fiscalArchiveEvent.Timestamp:yyyy-MM-dd HH:mm:ss}")
});
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook(){ MCAttributes = new MarkupCompatibilityAttributes(){ Ignorable = "x15" } };
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
workbook1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
workbook1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
FileVersion fileVersion1 = new FileVersion(){ ApplicationName = "xl", LastEdited = "6", LowestEdited = "6", BuildVersion = "14420" };
WorkbookProperties workbookProperties1 = new WorkbookProperties(){ DefaultThemeVersion = (UInt32Value)153222U };
AlternateContent alternateContent1 = new AlternateContent();
alternateContent1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
AlternateContentChoice alternateContentChoice1 = new AlternateContentChoice(){ Requires = "x15" };
X15ac.AbsolutePath absolutePath1 = new X15ac.AbsolutePath(){ Url = "D:\\Users\\dito\\Desktop\\TestDocumentResaver\\OpenXmlApiConversion\\WorkBookPr\\" };
absolutePath1.AddNamespaceDeclaration("x15ac", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac");
alternateContentChoice1.Append(absolutePath1);
alternateContent1.Append(alternateContentChoice1);
BookViews bookViews1 = new BookViews();
WorkbookView workbookView1 = new WorkbookView(){ XWindow = 0, YWindow = 0, WindowWidth = (UInt32Value)26940U, WindowHeight = (UInt32Value)15120U };
bookViews1.Append(workbookView1);
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet(){ Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
sheets1.Append(sheet1);
CalculationProperties calculationProperties1 = new CalculationProperties(){ CalculationId = (UInt32Value)152511U };
WorkbookExtensionList workbookExtensionList1 = new WorkbookExtensionList();
WorkbookExtension workbookExtension1 = new WorkbookExtension(){ Uri = "{140A7094-0E35-4892-8432-C4D2E57EDEB5}" };
workbookExtension1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.WorkbookProperties workbookProperties2 = new X15.WorkbookProperties(){ ChartTrackingReferenceBase = true };
workbookExtension1.Append(workbookProperties2);
workbookExtensionList1.Append(workbookExtension1);
workbook1.Append(fileVersion1);
workbook1.Append(workbookProperties1);
workbook1.Append(alternateContent1);
workbook1.Append(bookViews1);
workbook1.Append(sheets1);
workbook1.Append(calculationProperties1);
workbook1.Append(workbookExtensionList1);
workbookPart1.Workbook = workbook1;
}
protected void ExcelDl_Click(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=ViewHistoryChart.xlsx");
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.Charset = "";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
Chart1.RenderControl(hw);
using (MemoryStream stream = new MemoryStream())
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
{
var fileName = HttpContext.Current.Request.PhysicalApplicationPath + @"\Charts\ChartImage.png";
WorkbookPart wbp = spreadsheetDocument.AddWorkbookPart();
WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
Workbook wb = new Workbook();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
Worksheet ws = new Worksheet();
SheetData sd = new SheetData();
// add contents
List<string> values = new List<string>();
//values.Add(lblSectionFunctionSelected.Text);
uint i = 1;
foreach (var value in values)
{
UInt32Value rowIndex = UInt32Value.FromUInt32(i);
var row = new Row { RowIndex = rowIndex }; // add a row at the top of spreadsheet
sd.Append(row);
var cell = new Cell
{
CellValue = new CellValue(value),
DataType = new EnumValue<CellValues>(CellValues.String)
};
row.InsertAt(cell, 0);
i++;
}
// add image
DrawingsPart dp = wsp.AddNewPart<DrawingsPart>();
ImagePart imgp = dp.AddImagePart(ImagePartType.Png, wsp.GetIdOfPart(dp));
using (FileStream fs = new FileStream(fileName, FileMode.Open))
{
imgp.FeedData(fs);
}
DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties nvdp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualDrawingProperties();
nvdp.Id = 1025;
nvdp.Name = "Picture 1";
nvdp.Description = "Chart";
DocumentFormat.OpenXml.Drawing.PictureLocks picLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks();
picLocks.NoChangeAspect = true;
picLocks.NoChangeArrowheads = true;
DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureDrawingProperties nvpdp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureDrawingProperties();
nvpdp.PictureLocks = picLocks;
DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureProperties nvpp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.NonVisualPictureProperties();
nvpp.NonVisualDrawingProperties = nvdp;
nvpp.NonVisualPictureDrawingProperties = nvpdp;
DocumentFormat.OpenXml.Drawing.Stretch stretch = new DocumentFormat.OpenXml.Drawing.Stretch();
stretch.FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle();
DocumentFormat.OpenXml.Drawing.Spreadsheet.BlipFill blipFill = new DocumentFormat.OpenXml.Drawing.Spreadsheet.BlipFill();
DocumentFormat.OpenXml.Drawing.Blip blip = new DocumentFormat.OpenXml.Drawing.Blip();
blip.Embed = dp.GetIdOfPart(imgp);
blip.CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print;
blipFill.Blip = blip;
blipFill.SourceRectangle = new DocumentFormat.OpenXml.Drawing.SourceRectangle();
blipFill.Append(stretch);
DocumentFormat.OpenXml.Drawing.Transform2D t2d = new DocumentFormat.OpenXml.Drawing.Transform2D();
DocumentFormat.OpenXml.Drawing.Offset offset = new DocumentFormat.OpenXml.Drawing.Offset();
offset.X = 0;
offset.Y = 0;
t2d.Offset = offset;
Bitmap bm = new Bitmap(fileName);
//http://en.wikipedia.org/wiki/English_Metric_Unit#DrawingML
//http://stackoverflow.com/questions/1341930/pixel-to-centimeter
//http://stackoverflow.com/questions/139655/how-to-convert-pixels-to-points-px-to-pt-in-net-c
DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents();
extents.Cx = (long)bm.Width * (long)((float)914400 / bm.HorizontalResolution);
extents.Cy = (long)bm.Height * (long)((float)914400 / bm.VerticalResolution);
bm.Dispose();
t2d.Extents = extents;
DocumentFormat.OpenXml.Drawing.Spreadsheet.ShapeProperties sp = new DocumentFormat.OpenXml.Drawing.Spreadsheet.ShapeProperties();
sp.BlackWhiteMode = DocumentFormat.OpenXml.Drawing.BlackWhiteModeValues.Auto;
sp.Transform2D = t2d;
DocumentFormat.OpenXml.Drawing.PresetGeometry prstGeom = new DocumentFormat.OpenXml.Drawing.PresetGeometry();
prstGeom.Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle;
prstGeom.AdjustValueList = new DocumentFormat.OpenXml.Drawing.AdjustValueList();
sp.Append(prstGeom);
sp.Append(new DocumentFormat.OpenXml.Drawing.NoFill());
DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture picture = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture();
picture.NonVisualPictureProperties = nvpp;
picture.BlipFill = blipFill;
picture.ShapeProperties = sp;
DocumentFormat.OpenXml.Drawing.Spreadsheet.Position pos = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Position();
pos.X = 0;
pos.Y = 10;
Extent ext = new Extent();
ext.Cx = extents.Cx;
ext.Cy = extents.Cy;
AbsoluteAnchor anchor = new AbsoluteAnchor();
anchor.Position = pos;
anchor.Extent = ext;
anchor.Append(picture);
anchor.Append(new ClientData());
WorksheetDrawing wsd = new WorksheetDrawing();
wsd.Append(anchor);
Drawing drawing = new Drawing();
drawing.Id = dp.GetIdOfPart(imgp);
wsd.Save(dp);
ws.Append(sd);
ws.Append(drawing);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = "history chart";
sheet.SheetId = 1;
sheet.Id = wbp.GetIdOfPart(wsp);
sheets.Append(sheet);
wb.Append(fv);
wb.Append(sheets);
spreadsheetDocument.WorkbookPart.Workbook = wb;
spreadsheetDocument.WorkbookPart.Workbook.Save();
spreadsheetDocument.Close();
}
File.WriteAllBytes(HttpContext.Current.Request.PhysicalApplicationPath + @"\Charts\temp.xlsx", stream.ToArray());
}
Response.WriteFile(HttpContext.Current.Request.PhysicalApplicationPath + @"\Charts\temp.xlsx");
Response.Flush();
Response.Close();
Response.End();
}
public static void CopySheet(string filename, string sheetName, string clonedSheetName, string destFileName)
{
//Open workbook
using (SpreadsheetDocument mySpreadsheet = SpreadsheetDocument.Open(filename, true))
{
WorkbookPart workbookPart = mySpreadsheet.WorkbookPart;
//Get the source sheet to be copied
WorksheetPart sourceSheetPart = GetWorkSheetPart(workbookPart, sheetName);
SharedStringTablePart sharedStringTable = workbookPart.SharedStringTablePart;
//Take advantage of AddPart for deep cloning
using (SpreadsheetDocument newXLFile = SpreadsheetDocument.Create(destFileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart newWorkbookPart = newXLFile.AddWorkbookPart();
SharedStringTablePart newSharedStringTable = newWorkbookPart.AddPart <SharedStringTablePart>(sharedStringTable);
WorksheetPart newWorksheetPart = newWorkbookPart.AddPart <WorksheetPart>(sourceSheetPart);
//Table definition parts are somewhat special and need unique ids...so let's make an id based on count
int numTableDefParts = sourceSheetPart.GetPartsCountOfType <TableDefinitionPart>();
tableId = numTableDefParts;
//Clean up table definition parts (tables need unique ids)
if (numTableDefParts != 0)
{
FixupTableParts(newWorksheetPart, numTableDefParts);
}
//There should only be one sheet that has focus
CleanView(newWorksheetPart);
var fileVersion = new FileVersion {
ApplicationName = "Microsoft Office Excel"
};
//Worksheet ws = newWorksheetPart.Worksheet;
Workbook wb = new Workbook();
wb.Append(fileVersion);
//Add new sheet to main workbook part
Sheets sheets = null;
//int sheetCount = wb.Sheets.Count();
if (wb.Sheets != null)
{
sheets = wb.GetFirstChild <Sheets>();
}
else
{
sheets = new Sheets();
}
Sheet copiedSheet = new Sheet
{
Name = clonedSheetName,
Id = newWorkbookPart.GetIdOfPart(newWorksheetPart)
};
if (wb.Sheets != null)
{
copiedSheet.SheetId = (uint)sheets.ChildElements.Count + 1;
}
else
{
copiedSheet.SheetId = 1;
}
sheets.Append(copiedSheet);
newWorksheetPart.Worksheet.Save();
wb.Append(sheets);
//Save Changes
newWorkbookPart.Workbook = wb;
wb.Save();
newXLFile.Close();
}
}
}
/// <summary>
/// Generate an excel file with the information of certificates
/// </summary>
/// <param name="dataSource">The list of certificates</param>
/// <returns>MemoryStream</returns>
public static MemoryStream GenerateCertificateReport(CertificateListModel model, string logoPath)
{
MemoryStream ms = new MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
//create the new workbook
WorkbookPart workbookPart = document.AddWorkbookPart();
Workbook workbook = new Workbook();
workbookPart.Workbook = workbook;
// If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
//get and save the stylesheet
Stylesheet stylesheet = VocStyleSheet();
workbookStylesPart.Stylesheet = stylesheet;
workbookStylesPart.Stylesheet.Save();
//add the new workseet
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet();
SheetData sheetData1 = new SheetData();
Sheets sheets = new Sheets();
//get the number of columns in the report
Row rowTitle;
//get the string name of the columns
string[] excelColumnNamesTitle = new string[4];
for (int n = 0; n < 4; n++)
excelColumnNamesTitle[n] = GetExcelColumnName(n);
//build the title
for (int i = 1; i <= 6; i++)
{
rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
for (int cellval = 0; cellval < 4; cellval++)
{
AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
}
sheetData1.Append(rowTitle);
}
List<CertificateDocument> dataSource = model.Certificates.Collection;
MergeCells mergeCells = new MergeCells();
Row currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
//add the title
UpdateStringCellValue("A2", Resources.Common.CertificateList, currentRowTitle, 5);
//set min date and max date in header
Row currentRowDateTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)5);
string minDate, maxDate;
//get dates
if (string.IsNullOrEmpty(model.IssuanceDateFrom) || string.IsNullOrEmpty(model.IssuanceDateTo))
{
minDate = dataSource.Select(x => x.Certificate.IssuanceDate.GetValueOrDefault()).Min().ToString("dd/MM/yyyy");
maxDate = dataSource.Select(x => x.Certificate.IssuanceDate.GetValueOrDefault()).Max().ToString("dd/MM/yyyy");
}
else
{
minDate = model.IssuanceDateFrom;
maxDate = model.IssuanceDateTo;
}
//write both dates
UpdateStringCellValue("B5", Resources.Common.IssuanceDateFrom + ": "+minDate, currentRowDateTitle, 7);
UpdateStringCellValue("C5", Resources.Common.IssuanceDateTo + ": " + maxDate, currentRowDateTitle, 7);
//merge all cells in the title
MergeCell mergeCell = new MergeCell();
mergeCell.Reference = "A2:D4";
mergeCells.Append(mergeCell);
Drawing drawing = AddLogo(logoPath, worksheetPart);
Columns columns = new Columns();
columns.Append(CreateColumnData((UInt32Value)(uint)1, (UInt32Value)(uint)1, 32));
columns.Append(CreateColumnData((UInt32Value)(uint)2, (UInt32Value)(uint)2, 30));
columns.Append(CreateColumnData((UInt32Value)(uint)3, (UInt32Value)(uint)3, 33));
columns.Append(CreateColumnData((UInt32Value)(uint)4, (UInt32Value)(uint)4, 45));
worksheet.Append(columns);
int rowIndex = 8;
Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex, Resources.Common.CertificateNumber, rowData, 2);
AppendTextCell("B" + rowIndex, Resources.Common.IssuanceDate, rowData, 2);
AppendTextCell("C" + rowIndex, Resources.Common.CertificateStatus, rowData, 2);
AppendTextCell("D" + rowIndex, Resources.Common.EntryPoint, rowData, 2);
sheetData1.Append(rowData);
rowIndex = 9;
//build the data
foreach (var item in dataSource)
{
rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex.ToString(), item.Certificate.Sequential, rowData, 1);
AppendTextCell("B" + rowIndex.ToString(), item.Certificate.IssuanceDate.HasValue ? item.Certificate.IssuanceDate.Value.ToString("dd/MM/yyyy") : "", rowData, 1);
AppendTextCell("C" + rowIndex.ToString(), item.Certificate.CertificateStatusId.ToString(), rowData, 1);
AppendTextCell("D" + rowIndex.ToString(), item.Certificate.EntryPoint != null ? item.Certificate.EntryPoint.Name : "", rowData, 1);
sheetData1.Append(rowData);
rowIndex++;
}
//add the information of the current sheet
worksheet.Append(sheetData1);
//add merged cells
worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
worksheet.Append(drawing);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
//create the new sheet for this report
Sheet sheet = new Sheet() { Name = Resources.Common.CertificateList, SheetId = (UInt32Value)1, Id = workbookPart.GetIdOfPart(worksheetPart) };
sheets.Append(sheet);
//add the new sheet to the report
workbook.Append(sheets);
//save all report
workbook.Save();
//close the stream.
document.Close();
}
return ms;
}
// Generates content of a Workbook Part.
private void GenerateWorkbookPartContent(WorkbookPart workbookPart, List<string> sheetNames)
{
Workbook workbook = new Workbook();
workbook.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
Sheets sheets = new Sheets();
Sheet sheet;
for(int ii = 0; ii < sheetNames.Count; ii++)
{
sheet = new Sheet() { Name = sheetNames[ii], SheetId = (UInt32Value)Convert.ToUInt32(ii + 1), Id = "rId" + (ii + 1) };
sheets.Append(sheet);
}
//CalculationProperties calculationProperties1 = new CalculationProperties() { CalculationId = (UInt32Value)145621U };
//workbook.Append(fileVersion);
//workbook.Append(workbookProperties);
//workbook.Append(bookViews1);
workbook.Append(sheets);
//workbook.Append(calculationProperties1);
workbookPart.Workbook = workbook;
}
public void create(string fileName)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart;
OpenXmlWriter writer;
OpenXmlWriter writer2;
workbookPart = document.AddWorkbookPart();
WorkbookStylesPart wbsp = workbookPart.AddNewPart <WorkbookStylesPart>();
SharedStringTablePart stringTable = workbookPart.AddNewPart <SharedStringTablePart>();
var workbook = new Workbook();
workbookPart.Workbook = workbook;
var fileVersion = new FileVersion()
{
ApplicationName = "Microsoft Office Excel"
};
workbook.Append(fileVersion);
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
var relationshipId = workbookPart.GetIdOfPart(worksheetPart);
var sheetPayment = new Sheet {
Name = "Payment Upload", SheetId = 1, Id = relationshipId
};
var sheets = new Sheets();
sheets.Append(sheetPayment);
writer = OpenXmlWriter.Create(worksheetPart);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
var worksheetPart2 = workbookPart.AddNewPart <WorksheetPart>();
var relationshipId2 = workbookPart.GetIdOfPart(worksheetPart2);
var sheetSuspense = new Sheet {
Name = "Suspense Upload", SheetId = 2, Id = relationshipId2
};
sheets.Append(sheetSuspense);
writer2 = OpenXmlWriter.Create(worksheetPart2);
writer2.WriteStartElement(new Worksheet());
writer2.WriteStartElement(new SheetData());
workbook.Append(sheets);
writer.WriteEndElement(); // SheetData
writer2.WriteEndElement();
writer.WriteEndElement(); // Worksheet
writer2.WriteEndElement();
writer.Close();
writer2.Close();
document.WorkbookPart.Workbook.Save();
document.Close();
}
}
/// <summary>
/// Write excel file of a list of object as T
/// Assume that maximum of 24 columns
/// </summary>
/// <typeparam name="T">Object type to pass in</typeparam>
/// <param name="fileName">Full path of the file name of excel spreadsheet</param>
/// <param name="objects">list of the object type</param>
/// <param name="sheetName">Sheet names of Excel File</param>
/// <param name="headerNames">Header names of the object</param>
///
public void setColorforHeader <T>(string fileName, //List<T> objects,
string sheetName, List <string> headerNames)
{
using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = myWorkbook.AddWorkbookPart();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
SheetData sheetData = new SheetData();
WorkbookStylesPart stylesPart = myWorkbook.WorkbookPart.AddNewPart <WorkbookStylesPart>();
Workbook workbook = new Workbook();
Stylesheet styles = new CustomStylesheet();
string relId = workbookPart.GetIdOfPart(worksheetPart);
FileVersion fileVersion = new FileVersion {
ApplicationName = "Microsoft Office Excel"
};
styles.Save(stylesPart);
List <string> fields = GetPropertyInfo <T>();
var az = new List <Char>(Enumerable.Range('A', 'Z' - 'A' + 1).Select(i => (Char)i).ToArray());
List <Char> headers = az.GetRange(0, fields.Count);
//int numRows = objects.Count;
int numCols = 2;//fields.Count;
Row header = new Row();
int index = 1;
header.RowIndex = (uint)index;
for (int col = 0; col < numCols; col++)
{
//Cell c = CreateHeaderCell(headers[col].ToString(), headerNames[col], index, stylesPart.Stylesheet);
HeaderCell c = new HeaderCell(headers[col].ToString(), headerNames[col], index, stylesPart.Stylesheet, System.Drawing.Color.Aqua, 12, true);
header.Append(c);
}
sheetData.Append(header);
Worksheet worksheet = new Worksheet();
int numColumns = headerNames.Count;
int width = 0;
Columns columns = new Columns();
for (int col = 0; col < numCols; col++)
{
if (col == 0)
{
width = 50;
}
else if (col == 1)
{
width = 30;
}
else if (col == 2)
{
width = 40;
}
//else if (col == 3)
//{
// width = 72;
//}
//else if (col == 4)
//{
// width = 16;
//}
//else if (col == 5)
//{
// width = 12;
//}
//else if (col == 6)
//{
// width = 15;
//}
Column c = CreateColumnData((UInt32)col + 1, (UInt32)numCols + 1, width);
columns.Append(c);
}
Sheets sheets = new Sheets();
Sheet sheet = new Sheet {
Name = sheetName, SheetId = 1, Id = relId
};
sheets.Append(sheet);
workbook.Append(fileVersion);
workbook.Append(sheets);
worksheet.Append(columns);
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
myWorkbook.WorkbookPart.Workbook = workbook;
myWorkbook.WorkbookPart.Workbook.Save();
myWorkbook.Close();
}
}
public void Save()
{
SpreadsheetDocument xl = SpreadsheetDocument.Create(FileOutputPath, SpreadsheetDocumentType.Workbook);
WorkbookPart wbp = xl.AddWorkbookPart();
WorksheetPart wsp = wbp.AddNewPart <WorksheetPart>();
Workbook wb = new Workbook();
FileVersion fv = new FileVersion {
ApplicationName = "Microsoft Office Excel"
};
Worksheet ws = new Worksheet();
SheetData sd = new SheetData();
uint rowIndex = 1;
Row rowHeaders = new Row {
RowIndex = rowIndex++
};
string[] headers = { "Codice Ordine", "Totale (€)", "Data & Ora" };
foreach (string header in headers)
{
Cell cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(header)
};
rowHeaders.AppendChild(cell);
}
sd.AppendChild(rowHeaders);
foreach (Order order in SavingOrders)
{
Row row = new Row {
RowIndex = rowIndex
};
Cell cell = new Cell
{
DataType = CellValues.Number,
CellValue = new CellValue(order.Id.ToString())
};
Cell cell2 = new Cell
{
DataType = CellValues.Number,
CellValue = new CellValue(order.TotalPrice.ToString("##.##")),
StyleIndex = 4
};
Cell cell3 = new Cell
{
DataType = CellValues.Date,
CellValue = new CellValue(order.DateTime.ToString("dd-MM-yyyy HH:mm:ss"))
};
row.Append(cell, cell2, cell3);
sd.AppendChild(row);
rowIndex++;
}
Row rowFine = new Row {
RowIndex = rowIndex
};
//Cell cellTextSum = new Cell
//{
// DataType = CellValues.String,
// CellValue = new CellValue("Totale (€):"),
//};
Cell cellSum = new Cell
{
DataType = CellValues.Number,
CellValue = new CellValue(SavingOrders.Sum(x => x.TotalPrice).ToString("##.##")),
CellReference = "B" + rowIndex
};
rowFine.AppendChild(cellSum);
sd.AppendChild(rowFine);
ws.AppendChild(sd);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet
{
Name = Path.GetFileNameWithoutExtension(FileOutputPath),
SheetId = 1,
Id = wbp.GetIdOfPart(wsp)
};
sheets.AppendChild(sheet);
wb.Append(fv, sheets);
xl.WorkbookPart.Workbook = wb;
xl.WorkbookPart.Workbook.Save();
xl.Close();
}
/// <summary>
/// Write excel file of a list of object as T
/// Assume that maximum of 24 columns
/// </summary>
/// <typeparam name="T">Object type to pass in</typeparam>
/// <param name="fileName">Full path of the file name of excel spreadsheet</param>
/// <param name="objects">list of the object type</param>
/// <param name="sheetName">Sheet names of Excel File</param>
/// <param name="headerNames">Header names of the object</param>
public void Create <T>(
string fileName,
List <T> objects,
string sheetName,
List <string> headerNames)
{
//Open the copied template workbook.
using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = myWorkbook.AddWorkbookPart();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
// Create Styles and Insert into Workbook
WorkbookStylesPart stylesPart = myWorkbook.WorkbookPart.AddNewPart <WorkbookStylesPart>();
Stylesheet styles = new CustomStylesheet();
styles.Save(stylesPart);
string relId = workbookPart.GetIdOfPart(worksheetPart);
Workbook workbook = new Workbook();
FileVersion fileVersion = new FileVersion {
ApplicationName = "Microsoft Office Excel"
};
SheetData sheetData = CreateSheetData <T>(objects, headerNames, stylesPart);
Worksheet worksheet = new Worksheet();
//PageMargins pageM = worksheet.GetFirstChild<PageMargins>();
//SheetProtection sheetProtection = new SheetProtection();
//sheetProtection.Sheet = true;
//sheetProtection.Objects = true;
//sheetProtection.Scenarios = true;
////add column C:Z to allow edit range, which means column A,B and after Z are locked
//ProtectedRanges pRanges = new ProtectedRanges();
//ProtectedRange pRange = new ProtectedRange();
//ListValue<StringValue> lValue = new ListValue<StringValue>();
//lValue.InnerText = "D1:Z1048576";
//pRange.SequenceOfReferences = lValue;
//pRange.Name = "AllowEditRange1";
//pRanges.Append(pRange);
//worksheet.InsertBefore(sheetProtection, pageM);
//worksheet.InsertBefore(pRanges, pageM);
int numCols = headerNames.Count;
int width = headerNames.Max(h => h.Length) + 5;
Columns columns = new Columns();
for (int col = 0; col < numCols; col++)
{
Column c = CreateColumnData((UInt32)col + 1, (UInt32)numCols + 1, width);
if (col == 0)
{
c.Hidden = BooleanValue.FromBoolean(true);
}
columns.Append(c);
}
worksheet.Append(columns);
Sheets sheets = new Sheets();
Sheet sheet = new Sheet {
Name = sheetName, SheetId = 1, Id = relId
};
sheets.Append(sheet);
workbook.Append(fileVersion);
workbook.Append(sheets);
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
myWorkbook.WorkbookPart.Workbook = workbook;
myWorkbook.WorkbookPart.Workbook.Save();
myWorkbook.Close();
}
}
/// <summary>
///
/// </summary>
/// <param name="fileStorageKeyName"></param>
/// <param name="fileName"></param>
/// <param name="ds"></param>
/// <param name="hiddenColumnNames"></param>
/// <param name="secretValues"></param>
/// <param name="lockedColumns"></param>
public void DataSetToExcel(string fileStorageKeyName, string fileName, DataSet ds, string[] hiddenColumnNames,
string[] secretValues, bool isSheetProtect, string[] editableColumns)
{
IFileProvider fileProvider = new FileSystemProvider();
DataTable dt = ds.Tables[0];
// Generate filepath with file name
// TODO: Change the blog path.
string outputFile = fileProvider.GetFilePathToSave(fileStorageKeyName, fileName);
// Create SpreadSheet document.
SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Create(outputFile, SpreadsheetDocumentType.Workbook);
try
{
// Add workbook part.
WorkbookPart workbookPart = spreadSheetDocument.AddWorkbookPart();
// Adding sheet part...
WorksheetPart sheetPart = workbookPart.AddNewPart <WorksheetPart>();
string relId = workbookPart.GetIdOfPart(sheetPart);
Workbook workbook = new Workbook();
SheetData sheetData = CreateSheetData(dt, secretValues);
Worksheet Worksheet = new Worksheet();
// Creates Columns object.
Columns columns = CreateColumns(dt, hiddenColumnNames);
Worksheet.Append(columns);
// New Sheet create.
Sheets sheets = new Sheets();
Sheet sheet = new Sheet {
Name = dt.TableName, SheetId = 1, Id = relId
};
sheets.Append(sheet);
workbook.Append(sheets);
Worksheet.Append(sheetData);
sheetPart.Worksheet = Worksheet;
// Sheet Protect with password.
if (isSheetProtect)
{
PageMargins pageM;
SheetProtection sheetProtection;
ProtectedRanges pRanges;
ProtectSheet(Worksheet, out pageM, out sheetProtection, out pRanges, editableColumns, dt);
Worksheet.InsertBefore(sheetProtection, pageM);
Worksheet.InsertBefore(pRanges, pageM);
}
// Save the worksheet.
sheetPart.Worksheet.Save();
// Assign the workbook and save then close.
spreadSheetDocument.WorkbookPart.Workbook = workbook;
spreadSheetDocument.WorkbookPart.Workbook.Save();
}
finally
{
spreadSheetDocument.Close();
spreadSheetDocument.Dispose();
GC.Collect();
GC.Collect();
}
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook();
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
FileVersion fileVersion1 = new FileVersion() { ApplicationName = "xl", LastEdited = "4", LowestEdited = "4", BuildVersion = "4505" };
WorkbookProperties workbookProperties1 = new WorkbookProperties();
BookViews bookViews1 = new BookViews();
WorkbookView workbookView1 = new WorkbookView() { XWindow = 600, YWindow = 105, WindowWidth = (UInt32Value)13995U, WindowHeight = (UInt32Value)8190U };
bookViews1.Append(workbookView1);
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet() { Name = "Hoja1", SheetId = (UInt32Value)1U, Id = "rId1" };
sheets1.Append(sheet1);
DefinedNames definedNames1 = new DefinedNames();
DefinedName definedName1 = new DefinedName() { Name = "_xlnm.Print_Area", LocalSheetId = (UInt32Value)0U };
definedName1.Text = "Hoja1!$A$1:$AK$31";
definedNames1.Append(definedName1);
CalculationProperties calculationProperties1 = new CalculationProperties() { CalculationId = (UInt32Value)124519U };
workbook1.Append(fileVersion1);
workbook1.Append(workbookProperties1);
workbook1.Append(bookViews1);
workbook1.Append(sheets1);
workbook1.Append(definedNames1);
workbook1.Append(calculationProperties1);
workbookPart1.Workbook = workbook1;
}
// Generates content of workbookPart1.
private void GenerateWorkbookPart1Content(WorkbookPart workbookPart1)
{
Workbook workbook1 = new Workbook();
workbook1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
FileVersion fileVersion1 = new FileVersion() { ApplicationName = "xl", LastEdited = "4", LowestEdited = "4", BuildVersion = "4506" };
WorkbookProperties workbookProperties1 = new WorkbookProperties() { DefaultThemeVersion = (UInt32Value)124226U };
BookViews bookViews1 = new BookViews();
WorkbookView workbookView1 = new WorkbookView() { XWindow = 360, YWindow = 150, WindowWidth = (UInt32Value)14355U, WindowHeight = (UInt32Value)4680U };
bookViews1.Append(workbookView1);
Sheets sheets1 = new Sheets();
Sheet sheet1 = new Sheet() { Name = "Sheet1", SheetId = (UInt32Value)1U, Id = "rId1" };
sheets1.Append(sheet1);
CalculationProperties calculationProperties1 = new CalculationProperties() { CalculationId = (UInt32Value)125725U };
workbook1.Append(fileVersion1);
workbook1.Append(workbookProperties1);
workbook1.Append(bookViews1);
workbook1.Append(sheets1);
workbook1.Append(calculationProperties1);
workbookPart1.Workbook = workbook1;
}
//OverRide
public void saveXLS(String testName, DataSet ds)
{
SpreadsheetDocument xl = null;
string fullPath = Properties.Settings.Default.ExportSavePath + @"\" + testName;
fullPath += ".xlsx";
try
{
xl = SpreadsheetDocument.Create(fullPath, SpreadsheetDocumentType.Workbook);
}
catch (IOException)
{
MessageBox.Show("Another Copy of the Document is open, Please close the document and retry export");
throw;
}
WorkbookPart wbp = xl.AddWorkbookPart();
WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
Workbook wb = new Workbook();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
Worksheet ws = new Worksheet();
SheetData sd = new SheetData();
WorkbookStylesPart wbsp = wbp.AddNewPart<WorkbookStylesPart>();
wbsp.Stylesheet = GenerateStyleSheet();
wbsp.Stylesheet.Save();
//save the longest width for each column.
string[] longestWordPerColumn = new string[35];
int k = 0;
//create and add header row.
Row headerRow = new Row();
//add headers for germline mutation export
string[] germlineHeaders = { "Chromosome", "Position", "Gene Name", "Ref", "Var", "Strand", "Ref Codon", "Var Codon", "Ref AA", "Var AA", "AA Name", "CDS Name", "Cosmic Details", "Shows", "History", "RefSNP", "Clinical Significance", "MAF", "Chromosome Sample Count", "Alleles", "Allepe pop %" };
foreach (string s in germlineHeaders)
{
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(s);
cell.StyleIndex = 1;
headerRow.AppendChild(cell);
longestWordPerColumn[k] = s;
k++;
}
sd.AppendChild(headerRow);
List<Mutation> mutationList = new List<Mutation>();//remove this
//create and add rows for each mutation.
//Here we go through ds
int tbllength = ds.Tables[0].Rows.Count;
String[] infoString = new String[21];
for (int rowNum = 0; rowNum < tbllength; rowNum++)//for each mutation m
{
Row newRow = new Row();
for (int j = 0; j < 21; j++)
{
infoString[j] = (string)ds.Tables[0].Rows[rowNum].ItemArray[j];
}
//string[] infoString = m.getInfoForExport();
for (int i = 0; i < infoString.Length; i++)
{
Cell cell1 = new Cell();
if (i == 1)
cell1.DataType = CellValues.Number;
else
cell1.DataType = CellValues.String;
cell1.CellValue = new CellValue(infoString[i]);
if (!infoString[12].Equals("-----"))//index 12 is the Cosmic Name postion so
cell1.StyleIndex = 2;
else
cell1.StyleIndex = 3;
newRow.AppendChild(cell1);
//if (longestWordPerColumn[i].Length < infoString[i].Length)
// longestWordPerColumn[i] = infoString[i];
}
sd.AppendChild(newRow);
}
//Sets the column width to longest width for each column.
Columns columns = new Columns();
for (int i = 0; i < 21; i++)
{
columns.Append(CreateColumnData((UInt32)i + 1, (UInt32)i + 1, GetWidth("Calibri", 11, longestWordPerColumn[i])));
}
ws.Append(columns);
ws.Append(sd);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = "Sheet1";
sheet.SheetId = 1;
sheet.Id = wbp.GetIdOfPart(wsp);
sheets.Append(sheet);
wb.Append(fv);
wb.Append(sheets);
xl.WorkbookPart.Workbook = wb;
xl.WorkbookPart.Workbook.Save();
xl.Close();
}
//Main class function, export the mutationList to XLSX file, sets file name to testName.
public static void saveXLS(String testName, List <Mutation> mutationList)
{
SpreadsheetDocument xl = null;
string fullPath = Properties.Settings.Default.ExportSavePath + @"\" + testName;
fullPath += ".xlsx";
try
{
xl = SpreadsheetDocument.Create(fullPath, SpreadsheetDocumentType.Workbook);
}
catch (IOException)
{
throw;
}
WorkbookPart wbp = xl.AddWorkbookPart();
WorksheetPart wsp = wbp.AddNewPart <WorksheetPart>();
Workbook wb = new Workbook();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
Worksheet ws = new Worksheet();
SheetData sd = new SheetData();
WorkbookStylesPart wbsp = wbp.AddNewPart <WorkbookStylesPart>();
wbsp.Stylesheet = GenerateStyleSheet();
wbsp.Stylesheet.Save();
//save the longest width for each column.
string[] longestWordPerColumn = new string[12];
int k = 0;
//create and add header row.
Row headerRow = new Row();
foreach (string s in Mutation.getHeaderForExport())
{
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(s);
cell.StyleIndex = 1;
headerRow.AppendChild(cell);
longestWordPerColumn[k] = s;
k++;
}
sd.AppendChild(headerRow);
//create and add rows for each mutation.
foreach (Mutation m in mutationList)
{
Row newRow = new Row();
string[] infoString = m.getInfoForExport();
for (int i = 0; i < infoString.Length; i++)
{
Cell cell1 = new Cell();
if (i == 1)
{
cell1.DataType = CellValues.Number;
}
else
{
cell1.DataType = CellValues.String;
}
cell1.CellValue = new CellValue(infoString[i]);
if (!m.CosmicName.Equals("-----"))
{
cell1.StyleIndex = 2;
}
else
{
cell1.StyleIndex = 3;
}
newRow.AppendChild(cell1);
if (longestWordPerColumn[i].Length < infoString[i].Length)
{
longestWordPerColumn[i] = infoString[i];
}
}
sd.AppendChild(newRow);
}
//Sets the column width to longest width for each column.
Columns columns = new Columns();
for (int i = 0; i < 12; i++)
{
columns.Append(CreateColumnData((UInt32)i + 1, (UInt32)i + 1, GetWidth("Calibri", 11, longestWordPerColumn[i])));
}
ws.Append(columns);
ws.Append(sd);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = "Sheet1";
sheet.SheetId = 1;
sheet.Id = wbp.GetIdOfPart(wsp);
sheets.Append(sheet);
wb.Append(fv);
wb.Append(sheets);
xl.WorkbookPart.Workbook = wb;
xl.WorkbookPart.Workbook.Save();
xl.Close();
}
//Main class function, export the mutationList to XLSX file, sets file name to testName.
public static void saveXLS(String testName, List<Mutation> mutationList)
{
SpreadsheetDocument xl = null;
string fullPath = Properties.Settings.Default.ExportSavePath + @"\" + testName;
fullPath += ".xlsx";
try
{
xl = SpreadsheetDocument.Create(fullPath, SpreadsheetDocumentType.Workbook);
}
catch (IOException )
{
throw ;
}
WorkbookPart wbp = xl.AddWorkbookPart();
WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
Workbook wb = new Workbook();
FileVersion fv = new FileVersion();
fv.ApplicationName = "Microsoft Office Excel";
Worksheet ws = new Worksheet();
SheetData sd = new SheetData();
WorkbookStylesPart wbsp = wbp.AddNewPart<WorkbookStylesPart>();
wbsp.Stylesheet = GenerateStyleSheet();
wbsp.Stylesheet.Save();
//save the longest width for each column.
string[] longestWordPerColumn = new string[12];
int k = 0;
//create and add header row.
Row headerRow = new Row();
foreach (string s in Mutation.getHeaderForExport())
{
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(s);
cell.StyleIndex = 1;
headerRow.AppendChild(cell);
longestWordPerColumn[k] = s;
k++;
}
sd.AppendChild(headerRow);
//create and add rows for each mutation.
foreach (Mutation m in mutationList)
{
Row newRow = new Row();
string[] infoString = m.getInfoForExport();
for (int i = 0; i < infoString.Length; i++)
{
Cell cell1 = new Cell();
if (i == 1)
cell1.DataType = CellValues.Number;
else
cell1.DataType = CellValues.String;
cell1.CellValue = new CellValue(infoString[i]);
if (!m.CosmicName.Equals("-----"))
cell1.StyleIndex = 2;
else
cell1.StyleIndex = 3;
newRow.AppendChild(cell1);
if (longestWordPerColumn[i].Length < infoString[i].Length)
longestWordPerColumn[i] = infoString[i];
}
sd.AppendChild(newRow);
}
//Sets the column width to longest width for each column.
Columns columns = new Columns();
for (int i = 0; i < 12; i++)
{
columns.Append(CreateColumnData((UInt32)i + 1, (UInt32)i + 1, GetWidth("Calibri", 11, longestWordPerColumn[i])));
}
ws.Append(columns);
ws.Append(sd);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet();
sheet.Name = "Sheet1";
sheet.SheetId = 1;
sheet.Id = wbp.GetIdOfPart(wsp);
sheets.Append(sheet);
wb.Append(fv);
wb.Append(sheets);
xl.WorkbookPart.Workbook = wb;
xl.WorkbookPart.Workbook.Save();
xl.Close();
}