public byte[] ExcelTemplate(List <Columns_Excel> columns) { var stream = new MemoryStream(); var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook); var workbookpart = document.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); var worksheetPart = workbookpart.AddNewPart <WorksheetPart>(); var sheetData = new SheetData(); worksheetPart.Worksheet = new Worksheet(); //----------------------custom width cols----------------------------------- Columns cols = new Columns(); UInt32Value i = 1; foreach (var header in columns) { cols.Append(new Column() { Min = i, Max = i, Width = header.Width, CustomWidth = true }); i++; } worksheetPart.Worksheet.Append(cols); worksheetPart.Worksheet.Append(sheetData); var sheets = document.WorkbookPart.Workbook. AppendChild <Sheets>(new Sheets()); var sheet = new Sheet() { Id = document.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Template" }; sheets.AppendChild(sheet); var stylesPart = workbookpart.AddNewPart <WorkbookStylesPart>(); stylesPart.Stylesheet = CreateStylesheet(); //new Stylesheet(); // Add header UInt32 rowIdex = 0; var row = new Row { RowIndex = ++rowIdex }; sheetData.AppendChild(row); var cellIdex = 0; var dropdownIdex_start = columns.Count + 40; //----------------------create text for headers----------------------------------- foreach (var header in columns) { row.AppendChild(CreateTextCell(ColumnLetter(cellIdex++), rowIdex, header.Colum_name)); } int drops_index = 2; int general_index = 1; DataValidations dataValidations = new DataValidations(); bool put_validations = false; //----------------------generate Dropdowns----------------------------------- foreach (var drop in columns) { if (drop.DropDown_List != null) { put_validations = true; var dropdownIdex = dropdownIdex_start; row = new Row { RowIndex = ++rowIdex }; sheetData.AppendChild(row); // Add sheet data foreach (var rowData in drop.DropDown_List) { var cell = CreateTextCell(ColumnLetter(dropdownIdex++), rowIdex, rowData); row.AppendChild(cell); } string start_letter = "$" + ColumnLetter(dropdownIdex_start) + "$" + drops_index; string end_letter = "$" + ColumnLetter(dropdownIdex - 1) + "$" + drops_index; string formual = "Template!" + start_letter + ":" + end_letter; //DefinedNames definedNamesCol = new DefinedNames(); //Create the collection //DefinedName definedName = new DefinedName() //{ Name = drop.Colum_name, Text = formual }; // Create a new range //definedNamesCol.Append(definedName); // Add it to the collection //workbookpart.Workbook.Append(definedNamesCol); // Add collection to the workbook string col_name = ColumnLetter(general_index - 1); DataValidation dataValidation = new DataValidation() { Formula1 = new Formula1(formual), Type = DataValidationValues.List, AllowBlank = true, ShowInputMessage = true, ShowErrorMessage = true, SequenceOfReferences = new ListValue <StringValue>() { InnerText = col_name + ":" + col_name } }; dataValidations.Append(dataValidation); drops_index++; } general_index++; } if (put_validations) { worksheetPart.Worksheet.Append(dataValidations); } workbookpart.Workbook.Save(); document.Close(); return(stream.ToArray()); }
public static void CreatingExcelAndDrowownInExcel() { var filepath = @"C:\Test.xlsx"; OpenXMLWindowsApp app = new OpenXMLWindowsApp(); //app.UpdateSheet(filepath); SpreadsheetDocument myWorkbook = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook); //SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(filepath,true); WorkbookPart workbookpart = myWorkbook.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); WorksheetPart worksheetPart2 = workbookpart.AddNewPart <WorksheetPart>(); worksheetPart2.Worksheet = new Worksheet(new SheetData()); Sheets sheets = myWorkbook.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets()); Worksheet worksheet1 = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } }; worksheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); worksheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006"); worksheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"); Worksheet worksheet2 = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } }; worksheet2.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); worksheet2.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006"); worksheet2.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"); SheetDimension sheetDimension1 = new SheetDimension() { Reference = "A1" }; Sheet sheet = new Sheet() { Id = myWorkbook.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "DropDownContainingSheet" }; Sheet sheet1 = new Sheet() { Id = myWorkbook.WorkbookPart.GetIdOfPart(worksheetPart2), SheetId = 2, Name = "DropDownDataContainingSheet" }; sheets.Append(sheet); sheets.Append(sheet1); SheetData sheetData = new SheetData(); SheetData sheetData1 = new SheetData(); int Counter1 = 1; foreach (var value in DataInSheet.GetDataOfSheet1()) { Row contentRow = CreateRowValues(Counter1, value); Counter1++; sheetData.AppendChild(contentRow); } worksheet1.Append(sheetData); int Counter2 = 1; foreach (var value in DataInSheet.GetDataOfSheet2()) { Row contentRow = CreateRowValues(Counter2, value); Counter2++; sheetData1.AppendChild(contentRow); } worksheet2.Append(sheetData1); DataValidation dataValidation = new DataValidation { Type = DataValidationValues.List, AllowBlank = true, SequenceOfReferences = new ListValue <StringValue>() { InnerText = "A1" }, Formula1 = new Formula1("'DropDownDataContainingSheet'!$A$1:$A$8") }; DataValidations dataValidations = worksheet1.GetFirstChild <DataValidations>(); if (dataValidations != null) { dataValidations.Count = dataValidations.Count + 1; dataValidations.Append(dataValidation); } else { DataValidations newdataValidations = new DataValidations(); newdataValidations.Append(dataValidation); newdataValidations.Count = 1; worksheet1.Append(newdataValidations); } worksheetPart.Worksheet = worksheet1;; worksheetPart2.Worksheet = worksheet2; workbookpart.Workbook.Save(); myWorkbook.Close(); }
public static void UpdateCell(string docName, string text, uint rowIndex, string columnName) { //Worksheet worksheet1 = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } }; //worksheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); //worksheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006"); //worksheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"); //Worksheet worksheet2 = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } }; //worksheet2.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); //worksheet2.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006"); //worksheet2.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"); Worksheet worksheet1 = new Worksheet(); ExcelOperations ex = new ExcelOperations(); // Open the document for editing. using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true)) { WorksheetPart worksheetPart = GetWorksheetPartByName(spreadSheet, "DropDownContainingSheet"); if (worksheetPart != null) { worksheet1 = worksheetPart.Worksheet; SheetData sheetData = new SheetData(); SheetData sheetData1 = new SheetData(); int Counter1 = 1; foreach (var value in DataInSheet.GetDataOfSheet1()) { Row contentRow = ExcelOperations.CreateRowValues(Counter1, value); Counter1++; sheetData.AppendChild(contentRow); } worksheet1.Append(sheetData); int Counter2 = 1; //foreach (var value in DataInSheet.GetDataOfSheet2()) //{ // Row contentRow = ExcelOprations.CreateRowValues(Counter2, value); // Counter2++; // sheetData1.AppendChild(contentRow); //} //worksheet2.Append(sheetData1); DataValidation dataValidation = new DataValidation { Type = DataValidationValues.List, AllowBlank = true, SequenceOfReferences = new ListValue <StringValue>() { InnerText = "A1" }, Formula1 = new Formula1("'DropDownDataContainingSheet'!$B$1:$B$5") }; DataValidations dataValidations = worksheet1.GetFirstChild <DataValidations>(); if (dataValidations != null) { dataValidations.Count = dataValidations.Count + 1; dataValidations.Append(dataValidation); } else { DataValidations newdataValidations = new DataValidations(); newdataValidations.Append(dataValidation); newdataValidations.Count = 1; worksheet1.Append(newdataValidations); } Cell cell = GetCell(worksheetPart.Worksheet, columnName, rowIndex); cell.CellValue = new CellValue(text); cell.DataType = new EnumValue <CellValues>(CellValues.Number); // Save the worksheet. worksheetPart.Worksheet.Append(worksheet1); // worksheetPart.Worksheet=(worksheet2); worksheetPart.Worksheet.Save(); } } }
private uint WriteNodes(DotForm form, SheetData sheetData, DataValidations dataValidations) { uint rowNumber = 21; // Write table parts foreach (DotNode node in form.Nodes) { var estimates = node.Estimates.OrderBy(n => n.WaterLevel).ToArray(); AddRow(sheetData, StyleSheetLibrary.DefaultStyleIndex, rowNumber++); // TODO: merge all cells above table AddRow(sheetData, StyleSheetLibrary.DefaultStyleIndex, rowNumber++, ConstructCell(node.NodeName, CellValues.String, StyleSheetLibrary.TableHeaderStyleIndex)); AddRow(sheetData, StyleSheetLibrary.DefaultStyleIndex, rowNumber++, ConstructCell("Waterstand", CellValues.String, StyleSheetLibrary.TableHeaderStyleIndex), ConstructCell("Frequentie", CellValues.String, StyleSheetLibrary.TableHeaderStyleIndex), ConstructCell("Onder", CellValues.String, StyleSheetLibrary.TableHeaderStyleIndex), ConstructCell("Gemiddeld", CellValues.String, StyleSheetLibrary.TableHeaderStyleIndex), ConstructCell("Boven", CellValues.String, StyleSheetLibrary.TableHeaderStyleIndex), ConstructCell("Weergave", CellValues.String, StyleSheetLibrary.TableHeaderStyleIndex), ConstructCell("Toelichting", CellValues.String, StyleSheetLibrary.TableHeaderStyleIndex)); var styleIndex = StyleSheetLibrary.TableBodyStyleNormalIndex; foreach (var estimate in estimates) { AddRow(sheetData, StyleSheetLibrary.DefaultStyleIndex, rowNumber++, ConstructCell(estimate.WaterLevel, CellValues.Number, styleIndex), ConstructCell(estimate.Frequency, CellValues.Number, styleIndex), ConstructCell(estimate.LowerEstimate == 0 ? double.NaN : estimate.LowerEstimate, CellValues.Number, styleIndex), ConstructCell(estimate.BestEstimate == 0 ? double.NaN : estimate.BestEstimate, CellValues.Number, styleIndex), ConstructCell(estimate.UpperEstimate == 0 ? double.NaN : estimate.UpperEstimate, CellValues.Number, styleIndex), ConstructCell(double.NaN, CellValues.Number, styleIndex), ConstructCell("", CellValues.String, styleIndex)); dataValidations.Append(new DataValidation { AllowBlank = true, Type = DataValidationValues.List, Formula1 = new Formula1(elicitationCodeCellRange), SequenceOfReferences = new ListValue <StringValue> { InnerText = "E" + (rowNumber - 1) } }); dataValidations.Append(new DataValidation { AllowBlank = true, Type = DataValidationValues.List, Formula1 = new Formula1(elicitationCodeCellRange), SequenceOfReferences = new ListValue <StringValue> { InnerText = "F" + (rowNumber - 1) } }); dataValidations.Append(new DataValidation { AllowBlank = true, Type = DataValidationValues.List, Formula1 = new Formula1(elicitationCodeCellRange), SequenceOfReferences = new ListValue <StringValue> { InnerText = "G" + (rowNumber - 1) } }); styleIndex = styleIndex == StyleSheetLibrary.TableBodyStyleNormalIndex ? StyleSheetLibrary.TableAlternateBodyStyleIndex : StyleSheetLibrary.TableBodyStyleNormalIndex; } } return(rowNumber); }