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(); }