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