public MemoryStream CreateExcelDoc(List <TransactionExcelModel> transactions, MemoryStream mem)
{
SpreadsheetDocument document = SpreadsheetDocument.Create(mem, SpreadsheetDocumentType.Workbook);
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
// Adding style
WorkbookStylesPart stylePart = workbookPart.AddNewPart <WorkbookStylesPart>();
stylePart.Stylesheet = GenerateStylesheet();
stylePart.Stylesheet.Save();
// Setting up columns
Columns columns = new Columns(
new Column // Id column
{
Min = 1,
Max = 1,
Width = 4,
CustomWidth = true
},
new Column // Name and Birthday columns
{
Min = 2,
Max = 2,
Width = 15,
CustomWidth = true
},
new Column // Name and Birthday columns
{
Min = 3,
Max = 3,
Width = 10,
CustomWidth = true
},
new Column // Name and Birthday columns
{
Min = 4,
Max = 4,
Width = 15,
CustomWidth = true
},
new Column // Name and Birthday columns
{
Min = 5,
Max = 5,
Width = 35,
CustomWidth = true
}
,
new Column // Name and Birthday columns
{
Min = 6,
Max = 7,
Width = 10,
CustomWidth = true
},
new Column // Name and Birthday columns
{
Min = 8,
Max = 8,
Width = 20,
CustomWidth = true
});
worksheetPart.Worksheet.AppendChild(columns);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Remittance"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
row.Append(
ConstructCell("Id", CellValues.String, 2),
ConstructCell("Дата Транзакции", CellValues.String, 2),
ConstructCell("Сумма", CellValues.String, 2),
ConstructCell("Проект", CellValues.String, 2),
ConstructCell("Операция", CellValues.String, 2),
ConstructCell("Тип операции", CellValues.String, 2),
ConstructCell("Счет", CellValues.String, 2),
ConstructCell("Контрагент", CellValues.String, 2));
// Insert the header row to the Sheet Data
sheetData.AppendChild(row);
// Inserting each employee
foreach (var transaction in transactions)
{
row = new Row();
row.Append(
ConstructCell(transaction.Id.ToString(), CellValues.Number, 1),
ConstructCell(transaction.ActionDate.ToString("yyyy/MM/dd"), CellValues.String, 1),
ConstructCell(transaction.Sum.ToString(), CellValues.String, 1),
ConstructCell(transaction.ProjectName, CellValues.String, 1),
ConstructCell(transaction.OperationName, CellValues.String, 1),
ConstructCell(transaction.TransactionType, CellValues.String, 1),
ConstructCell(transaction.Score, CellValues.String, 1),
ConstructCell(transaction.CounterPartyName, CellValues.String, 1)
);
sheetData.AppendChild(row);
}
worksheetPart.Worksheet.Save();
document.Close();
return(mem);
}
/// <summary>
/// Creates an Xlsx document from a list of questions and stores it in a stream
/// </summary>
/// <param name="questions">the list of questions</param>
/// <param name="stream">the target stream</param>
public static void XlsxFromQuestions(IList <AnswerItem> questions, Stream stream)
{
// Open the document for editing.
using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook, true))
{
// Add a WorkbookPart to the document.
var workbookpart = spreadSheet.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
var worksheetPart = InsertWorksheet(workbookpart);
Worksheet worksheet = worksheetPart.Worksheet;
//AT
Columns lstColumns = worksheetPart.Worksheet.GetFirstChild <Columns>();
bool needToInsertColumns = false;
if (lstColumns == null)
{
lstColumns = new Columns();
needToInsertColumns = true;
}
// Min = 1, Max = 1 ==> Apply this to column 1 (A)
// Min = 2, Max = 2 ==> Apply this to column 2 (B)
// Width = 25 ==> Set the width to 25
// CustomWidth = true ==> Tell Excel to use the custom width
lstColumns.Append(new Column()
{
Min = 1, Max = 1, Width = 70, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 2, Max = 2, Width = 70, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 3, Max = 3, Width = 25, CustomWidth = true
});
// Only insert the columns if we had to create a new columns element
if (needToInsertColumns)
{
_ = worksheetPart.Worksheet.InsertAt(lstColumns, 0);
}
WorkbookStylesPart workStylePart = workbookpart.AddNewPart <WorkbookStylesPart>();
workStylePart.Stylesheet = CreateStylesheet();
workStylePart.Stylesheet.Save();
//AT
var sharedStringPart = workbookpart.AddNewPart <SharedStringTablePart>();
// If the part does not contain a SharedStringTable, create one.
if (sharedStringPart.SharedStringTable == null)
{
sharedStringPart.SharedStringTable = new SharedStringTable();
}
var sharedStringTable = sharedStringPart.SharedStringTable;
SetCelText(worksheet, "A", 1, "Questions", sharedStringTable);
SetCelText(worksheet, "B", 1, "Answer", sharedStringTable);
SetCelText(worksheet, "C", 1, "Metadata", sharedStringTable);
for (int i = 0; i < questions.Count; i++)
{
SetCelText(worksheet, "A", (uint)i + 2, questions[i].Question, sharedStringTable);
SetCelText(worksheet, "B", (uint)i + 2, questions[i].Answer, sharedStringTable);
SetCelText(worksheet, "C", (uint)i + 2, questions[i].Metadata, sharedStringTable);
}
// Save the new worksheet.
worksheetPart.Worksheet.Save();
}
}
/// <summary>
/// Настройка стилей для файла
/// </summary>
/// <param name="workbookpart"></param>
private static void CreateStyles(WorkbookPart workbookpart)
{
WorkbookStylesPart sp = workbookpart.AddNewPart <WorkbookStylesPart>();
sp.Stylesheet = new Stylesheet();
Fonts fonts = new Fonts()
{
Count = (UInt32Value)2U, KnownFonts = true
};
Font fontUsual = new Font();
fontUsual.Append(new FontSize()
{
Val = 12D
});
fontUsual.Append(new DocumentFormat.OpenXml.Office2010.Excel.Color()
{
Theme
= (UInt32Value)1U
});
fontUsual.Append(new FontName()
{
Val = "Times New Roman"
});
fontUsual.Append(new FontFamilyNumbering()
{
Val = 2
});
fontUsual.Append(new FontScheme()
{
Val = FontSchemeValues.Minor
});
Font fontTitle = new Font();
fontTitle.Append(new Bold());
fontTitle.Append(new FontSize()
{
Val = 14D
});
fontTitle.Append(new DocumentFormat.OpenXml.Office2010.Excel.Color()
{
Theme
= (UInt32Value)1U
});
fontTitle.Append(new FontName()
{
Val = "Times New Roman"
});
fontTitle.Append(new FontFamilyNumbering()
{
Val = 2
});
fontTitle.Append(new FontScheme()
{
Val = FontSchemeValues.Minor
});
fonts.Append(fontUsual);
fonts.Append(fontTitle);
Fills fills = new Fills()
{
Count = (UInt32Value)2U
};
Fill fill1 = new Fill();
fill1.Append(new PatternFill()
{
PatternType = PatternValues.None
});
Fill fill2 = new Fill();
fill2.Append(new PatternFill()
{
PatternType = PatternValues.Gray125
});
fills.Append(fill1);
fills.Append(fill2);
Borders borders = new Borders()
{
Count = (UInt32Value)2U
};
Border borderNoBorder = new Border();
borderNoBorder.Append(new LeftBorder());
borderNoBorder.Append(new RightBorder());
borderNoBorder.Append(new TopBorder());
borderNoBorder.Append(new BottomBorder());
borderNoBorder.Append(new DiagonalBorder());
Border borderThin = new Border();
LeftBorder leftBorder = new LeftBorder()
{
Style = BorderStyleValues.Thin
};
leftBorder.Append(new DocumentFormat.OpenXml.Office2010.Excel.Color()
{
Indexed = (UInt32Value)64U
});
RightBorder rightBorder = new RightBorder()
{
Style = BorderStyleValues.Thin
};
rightBorder.Append(new DocumentFormat.OpenXml.Office2010.Excel.Color()
{
Indexed = (UInt32Value)64U
});
TopBorder topBorder = new TopBorder()
{
Style = BorderStyleValues.Thin
};
topBorder.Append(new DocumentFormat.OpenXml.Office2010.Excel.Color()
{
Indexed = (UInt32Value)64U
});
BottomBorder bottomBorder = new BottomBorder()
{
Style =
BorderStyleValues.Thin
};
bottomBorder.Append(new DocumentFormat.OpenXml.Office2010.Excel.Color()
{
Indexed = (UInt32Value)64U
});
borderThin.Append(leftBorder);
borderThin.Append(rightBorder);
borderThin.Append(topBorder);
borderThin.Append(bottomBorder);
borderThin.Append(new DiagonalBorder());
borders.Append(borderNoBorder);
borders.Append(borderThin);
CellStyleFormats cellStyleFormats = new CellStyleFormats()
{
Count =
(UInt32Value)1U
};
CellFormat cellFormatStyle = new CellFormat()
{
NumberFormatId =
(UInt32Value)0U,
FontId = (UInt32Value)0U,
FillId = (UInt32Value)0U,
BorderId =
(UInt32Value)0U
};
cellStyleFormats.Append(cellFormatStyle);
CellFormats cellFormats = new CellFormats()
{
Count = (UInt32Value)3U
};
CellFormat cellFormatFont = new CellFormat()
{
NumberFormatId =
(UInt32Value)0U,
FontId = (UInt32Value)0U,
FillId = (UInt32Value)0U,
BorderId =
(UInt32Value)0U,
FormatId = (UInt32Value)0U,
ApplyFont = true
};
CellFormat cellFormatFontAndBorder = new CellFormat()
{
NumberFormatId =
(UInt32Value)0U,
FontId = (UInt32Value)0U,
FillId = (UInt32Value)0U,
BorderId =
(UInt32Value)1U,
FormatId = (UInt32Value)0U,
ApplyFont = true,
ApplyBorder = true
};
CellFormat cellFormatTitle = new CellFormat()
{
NumberFormatId =
(UInt32Value)0U,
FontId = (UInt32Value)1U,
FillId = (UInt32Value)0U,
BorderId =
(UInt32Value)0U,
FormatId = (UInt32Value)0U,
Alignment = new Alignment()
{
Vertical =
VerticalAlignmentValues.Center,
WrapText = true,
Horizontal =
HorizontalAlignmentValues.Center
},
ApplyFont = true
};
cellFormats.Append(cellFormatFont);
cellFormats.Append(cellFormatFontAndBorder);
cellFormats.Append(cellFormatTitle);
CellStyles cellStyles = new CellStyles()
{
Count = (UInt32Value)1U
};
cellStyles.Append(new CellStyle()
{
Name = "Normal",
FormatId =
(UInt32Value)0U,
BuiltinId = (UInt32Value)0U
});
DocumentFormat.OpenXml.Office2013.Excel.DifferentialFormats
differentialFormats = new DocumentFormat.OpenXml.Office2013.Excel.DifferentialFormats()
{
Count = (UInt32Value)0U
};
TableStyles tableStyles = new TableStyles()
{
Count = (UInt32Value)0U,
DefaultTableStyle = "TableStyleMedium2",
DefaultPivotStyle = "PivotStyleLight16"
};
StylesheetExtensionList stylesheetExtensionList = new
StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension()
{
Uri =
"{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtension1.AddNamespaceDeclaration("x14",
"http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
stylesheetExtension1.Append(new SlicerStyles()
{
DefaultSlicerStyle =
"SlicerStyleLight1"
});
StylesheetExtension stylesheetExtension2 = new StylesheetExtension()
{
Uri =
"{9260A510-F301-46a8-8635-F512D64BE5F5}"
};
stylesheetExtension2.AddNamespaceDeclaration("x15",
"http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
stylesheetExtension2.Append(new TimelineStyles()
{
DefaultTimelineStyle =
"TimeSlicerStyleLight1"
});
stylesheetExtensionList.Append(stylesheetExtension1);
stylesheetExtensionList.Append(stylesheetExtension2);
sp.Stylesheet.Append(fonts);
sp.Stylesheet.Append(fills);
sp.Stylesheet.Append(borders);
sp.Stylesheet.Append(cellStyleFormats);
sp.Stylesheet.Append(cellFormats);
sp.Stylesheet.Append(cellStyles);
sp.Stylesheet.Append(differentialFormats);
sp.Stylesheet.Append(tableStyles);
sp.Stylesheet.Append(stylesheetExtensionList);
}
void initStylesPart(WorkbookStylesPart stylesPart)
{
stylesPart.Stylesheet = new Stylesheet()
{
Fonts = new Fonts()
};
#region Fonts
//=================================================
stylesPart.Stylesheet.Fonts.AppendChild(new Font());
//=================================================
#endregion
#region Fills
//=================================================
stylesPart.Stylesheet.Fills = new Fills();
stylesPart.Stylesheet.Fills.AppendChild(new Fill {
PatternFill = new PatternFill {
PatternType = PatternValues.None
}
});
//=================================================
#endregion
#region Borders
//=================================================
stylesPart.Stylesheet.Borders = new Borders();
stylesPart.Stylesheet.Borders.AppendChild(new Border());
//=================================================
#endregion
#region CellStyles
//=================================================
stylesPart.Stylesheet.CellStyles = new CellStyles();
stylesPart.Stylesheet.CellStyles.Append(new CellStyle()
{
FormatId = 0
});
stylesPart.Stylesheet.CellStyles.Append(new CellStyle()
{
FormatId = 1
});
stylesPart.Stylesheet.CellStyles.Append(new CellStyle()
{
FormatId = 2
});
//=================================================
#endregion
#region CellStyleFormats
//=================================================
stylesPart.Stylesheet.CellStyleFormats = new CellStyleFormats();
stylesPart.Stylesheet.CellStyleFormats.Append(new CellFormat());
stylesPart.Stylesheet.CellStyleFormats.Append(new CellFormat());
stylesPart.Stylesheet.CellStyleFormats.Append(new CellFormat());
//=================================================
#endregion
#region CellFormats
//=================================================
stylesPart.Stylesheet.CellFormats = new CellFormats();
stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat());
stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat()
{
FormatId = 1,
FontId = 0,
BorderId = 0,
FillId = 0,
NumberFormatId = 14,
});
stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat()
{
FormatId = 2,
FontId = 0,
BorderId = 0,
FillId = 0,
NumberFormatId = 2,
});
//=================================================
#endregion
stylesPart.Stylesheet.Save();
}
private void ExporttoExcelXML(DataSet ds)
{
string strPathReports = GetPathUploadReports();
string strNamefile = DateTime.Now.ToString("ddMMyyyyhhmmss") + ".xlsx";
string strFullPath = Server.MapPath(strPathReports) + strNamefile;
var stream = new MemoryStream();
var document = SpreadsheetDocument.Create(strFullPath, SpreadsheetDocumentType.Workbook);
var workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
Worksheet ws = new Worksheet();
WorkbookStylesPart wbsp = workbookpart.AddNewPart <WorkbookStylesPart>();
// add styles to sheet
wbsp.Stylesheet = CreateStylesheet();
wbsp.Stylesheet.Save();
var sheetData = new SheetData();
worksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);
var sheets = document.WorkbookPart.Workbook.
AppendChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
var sheet = new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Resumen General" //data.SheetName ?? "Sheet 1"
};
sheets.AppendChild(sheet);
UInt32 rowIdex = 0;
var row = new Row {
RowIndex = ++rowIdex
};
Fonts fts = new Fonts();
DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
Bold fbld = new Bold();
FontName ftn = new FontName();
ftn.Val = "Calibri";
DocumentFormat.OpenXml.Spreadsheet.FontSize ftsz = new DocumentFormat.OpenXml.Spreadsheet.FontSize();
ftsz.Val = 11;
ft.FontName = ftn;
ft.FontSize = ftsz;
ft.Bold = fbld;
fts.Append(ft);
fts.Count = (uint)fts.ChildElements.Count;
row.Append(fts);
sheetData.AppendChild(row);
row.StyleIndex = (UInt32Value)1U;
var cellIdex = 0;
int intCount = 0;
string strX = string.Empty;
string strY = string.Empty;
foreach (System.Data.DataTable table in ds.Tables)
{
for (int i = 1; i < table.Columns.Count + 1; i++)
{
//excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName.ToString().ToUpper();
Cell cel = CreateTextCellHeader(ColumnLetter(cellIdex++), rowIdex, table.Columns[i - 1].ColumnName.ToString().ToUpper() ?? string.Empty);
if (i < 9)
{
cel.StyleIndex = (UInt32Value)1U;
}
if (i > 10 && i < 15)
{
cel.StyleIndex = (UInt32Value)2U;
}
if (i > 15 && i < 20)
{
cel.StyleIndex = (UInt32Value)4;
}
if (i > 20 && i < 25)
{
cel.StyleIndex = (UInt32Value)5U;
}
if (i > 30 && i < 35)
{
cel.StyleIndex = (UInt32Value)6U;
}
row.AppendChild(cel);
//((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[1, i]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray);
//((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[1, i]).Font.Bold = true;
//((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[1, i]).Font.Name = "Calibir";
//((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[1, i]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
// REQUIERE TC Y TP
if ((table.Columns[i - 1].ColumnName.ToString().ToUpper() == "REQUIERE TC Y TP")
||
(table.Columns[i - 1].ColumnName.ToString().ToUpper() == "REQUIERE REUBICACIÓN")
&&
intCount == 0)
{
intCount = i;
}
//((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[1, i]).Style.Name = "Normal";
}
for (int j = 0; j < table.Rows.Count; j++)
{
//rowIdex = 1;
row = new Row {
RowIndex = ++rowIdex
};
sheetData.AppendChild(row);
cellIdex = 0;
for (int k = 0; k < table.Columns.Count; k++)
{
if ((k == 2))
{
//((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[j + 2, k + 1]).NumberFormat = "#####";
}
if ((k == 4))
{
// ((Microsoft.Office.Interop.Excel.Range)excelWorkSheet.Cells[j + 2, k + 1]).NumberFormat = "##,###.##";
}
//excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();
row.AppendChild(CreateTextCell(ColumnLetter(cellIdex++), rowIdex, table.Rows[j].ItemArray[k].ToString() ?? string.Empty));
}
}
}
workbookpart.Workbook.Save();
document.Close();
string strUrl = "Bajarresumengral.aspx?n=" + strNamefile;
Response.Redirect(strUrl, true);
}
/// <summary>
/// Generates content of workbookStylesPart
/// </summary>
/// <param name="workbookStylesPart">WorkbookStylesPart Object</param>
private void CreateWorkBookStylesPart(WorkbookStylesPart workbookStylesPart)
{
// Define Style of Sheet in workbook
Stylesheet stylesheet1 = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac"
}
};
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
// Initialize an instance of fonts
Fonts fonts = new Fonts()
{
Count = (UInt32Value)2U, KnownFonts = true
};
// Initialize an instance of font,fontsize,color
Font font = new Font();
FontSize fontSize = new FontSize()
{
Val = 14D
};
Color color = new Color()
{
Theme = (UInt32Value)1U
};
FontName fontName = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme = new FontScheme()
{
Val = FontSchemeValues.Minor
};
// Add elements to font
font.Append(fontSize);
font.Append(color);
font.Append(fontName);
font.Append(fontFamilyNumbering);
font.Append(fontScheme);
fonts.Append(font);
// Define the StylesheetExtensionList Class. When the object is serialized out as xml, its qualified name is x:extLst
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
// Define the StylesheetExtension Class
StylesheetExtension stylesheetExtension1 = new StylesheetExtension()
{
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles slicerStyles1 = new DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles()
{
DefaultSlicerStyle = "SlicerStyleLight1"
};
stylesheetExtension1.Append(slicerStyles1);
stylesheetExtensionList1.Append(stylesheetExtension1);
// Add elements to stylesheet
stylesheet1.Append(fonts);
stylesheet1.Append(stylesheetExtensionList1);
// Set the style of workbook
workbookStylesPart.Stylesheet = stylesheet1;
}
private static WorkbookStylesPart AddStyleSheet(SpreadsheetDocument spreadsheet)
{
WorkbookStylesPart stylesheet = spreadsheet.WorkbookPart.AddNewPart <WorkbookStylesPart>();
Stylesheet workbookstylesheet = new Stylesheet();
Font font0 = new Font(); // Default font
Font font1 = new Font(); // Bold font
Bold bold = new Bold();
font1.Append(bold);
Font font2 = new Font();
Fonts fonts = new Fonts(); // <APENDING Fonts>
fonts.Append(font0);
fonts.Append(font1);
fonts.Append(font2);
// <Fills>
Fill fill0 = new Fill(); // Default fill
Fills fills = new Fills(); // <APENDING Fills>
fills.Append(fill0);
// <Borders>
Border border0 = new Border(); // Default border
Borders borders = new Borders(); // <APENDING Borders>
borders.Append(border0);
//alignments
Alignment alg1 = new Alignment();
alg1.Horizontal = HorizontalAlignmentValues.Center;
Alignment alg2 = new Alignment();
alg2.Horizontal = HorizontalAlignmentValues.Center;
// <CellFormats>
CellFormat cellformat0 = new CellFormat();
cellformat0.FontId = 0;
cellformat0.FillId = 0;
cellformat0.BorderId = 0; // Default style : Mandatory | Style ID =0
CellFormat cellformat1 = new CellFormat();
cellformat1.FontId = 1; // Style with Bold text ; Style ID = 1
cellformat1.Alignment = alg1;
CellFormat cellformat2 = new CellFormat();
cellformat2.FontId = 2; // Style with Bold text ; Style ID = 1
cellformat2.Alignment = alg2;
// <APENDING CellFormats>
CellFormats cellformats = new CellFormats();
cellformats.Append(cellformat0);
cellformats.Append(cellformat1);
cellformats.Append(cellformat2);
// Append FONTS, FILLS , BORDERS & CellFormats to stylesheet <Preserve the ORDER>
workbookstylesheet.Append(fonts);
workbookstylesheet.Append(fills);
workbookstylesheet.Append(borders);
workbookstylesheet.Append(cellformats);
// Finalize
stylesheet.Stylesheet = workbookstylesheet;
stylesheet.Stylesheet.Save();
return(stylesheet);
}
public void SetStyle(WorkbookStylesPart stylesPart, UInt32Value index)
{
DocumentFormat.OpenXml.Spreadsheet.CellFormat cellFormat;
NumberingFormat numberingFormat;
Alignment aligment;
cellFormat = new DocumentFormat.OpenXml.Spreadsheet.CellFormat();
#region FormatsSwitch
switch (Format)
{
case CellFormat.Header:
break;
case CellFormat.String:
break;
case CellFormat.StringWrap:
break;
case CellFormat.NumberInt:
cellFormat.NumberFormatId = 3; // #,##0
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
break;
case CellFormat.NumberFract:
cellFormat.NumberFormatId = 4; // #,##0.00
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
break;
case CellFormat.NumberFractLong:
numberingFormat = new NumberingFormat();
numberingFormat.NumberFormatId = 164;
numberingFormat.FormatCode = StringValue.FromString("#0.000");
stylesPart.Stylesheet.NumberingFormats.AppendChild(numberingFormat);
cellFormat.NumberFormatId = numberingFormat.NumberFormatId; // 22; // m/d/yy h:mm
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
//cellFormat.NumberFormatId = 4; // #,##0.00 4
//cellFormat.ApplyNumberFormat = DocumentFormat.OpenXml.BooleanValue.FromBoolean(true);
break;
case CellFormat.PercentInt:
cellFormat.NumberFormatId = 9; // 0%
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
break;
case CellFormat.PercentFract:
cellFormat.NumberFormatId = 10; // 0.00%
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
break;
case CellFormat.Date:
numberingFormat = new NumberingFormat();
numberingFormat.NumberFormatId = 165;
numberingFormat.FormatCode = StringValue.FromString("dd.mm.yyyy");
stylesPart.Stylesheet.NumberingFormats.AppendChild(numberingFormat);
cellFormat.NumberFormatId = numberingFormat.NumberFormatId; // 22; // m/d/yy h:mm
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
break;
case CellFormat.DateTime:
numberingFormat = new NumberingFormat();
numberingFormat.NumberFormatId = 166;
numberingFormat.FormatCode = StringValue.FromString("dd.mm.yyyy hh:mm");
stylesPart.Stylesheet.NumberingFormats.AppendChild(numberingFormat);
cellFormat.NumberFormatId = numberingFormat.NumberFormatId; // 22; // m/d/yy h:mm
cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
break;
case CellFormat.Bool:
break;
default:
break;
}
#endregion
//if (!IsLock)
//{
// cellFormat.Protection = new Protection() { Locked = false };
// cellFormat.ApplyProtection = true;
// // = new DocumentFormat.OpenXml.Spreadsheet.CellFormat() { ApplyProtection = true, Protection = new Protection() { Locked = false } };
//}
if (IsBorder)
{
cellFormat.BorderId = 1;
cellFormat.ApplyBorder = true;
}
if (Font != null)
{
cellFormat.FontId = Font.FontIndex;
}
else
{
cellFormat.FontId = 0;
}
cellFormat.ApplyFont = true;
if (Fill != null)
{
cellFormat.FillId = Fill.FillIndex;
}
else
{
cellFormat.FillId = 0;
}
cellFormat.ApplyFill = true;
aligment = new Alignment();
aligment.Vertical = Vertical;
aligment.Horizontal = Horizontal;
aligment.WrapText = IsWordWrap;
cellFormat.AppendChild(aligment);
stylesPart.Stylesheet.CellFormats.AppendChild(cellFormat);
StyleIndex = index;
}
//------ NumberFormatId upto 164 is "In-Built"---------------//
//public void ExportDataSet(DataSet ds, string destination, string fileName)
//{
// try
// {
// if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
// {
// using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
// {
// var workbookPart = workbook.AddWorkbookPart();
// workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
// workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
// WorkbookStylesPart stylesPart = workbook.WorkbookPart.AddNewPart<WorkbookStylesPart>();
// stylesPart.Stylesheet = GenerateStyleSheet();
// stylesPart.Stylesheet.Save();
// foreach (System.Data.DataTable table in ds.Tables)
// {
// var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
// var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
// sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);
// DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
// string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
// uint sheetId = 1;
// if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
// {
// sheetId =
// sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
// }
// DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName };
// sheets.Append(sheet);
// DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
// List<String> columns = new List<string>();
// foreach (System.Data.DataColumn column in table.Columns)
// {
// columns.Add(column.ColumnName);
// DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
// cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
// cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
// cell.StyleIndex = 3;
// headerRow.AppendChild(cell);
// }
// sheetData.AppendChild(headerRow);
// foreach (System.Data.DataRow dsrow in table.Rows)
// {
// DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
// foreach (String col in columns)
// {
// DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
// cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
// cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
// newRow.AppendChild(cell);
// }
// sheetData.AppendChild(newRow);
// }
// }
// }
// downloadfile(destination, fileName);
// }
// }
// catch (Exception ex)
// {
// }
//}
/// <summary>
///
/// </summary>
/// <param name="dsHeader">Custom Header DataSet</param>
/// <param name="ds">Actual dataset with values</param>
/// <param name="customHeader">True: Use custom header dataset, False: used table header</param>
/// <param name="destination">File location with name</param>
public void ExportDataSet(DataSet dsHeader, DataSet ds, bool customHeader, string filename, string exportFileName)
{
string dbFilePath = "/DownloadExcel/";
string strFileName = GenerateFileName(filename) + "xlsx";
if (!Directory.Exists(System.Web.HttpContext.Current.Server.MapPath("~" + dbFilePath)))
{
Directory.CreateDirectory(System.Web.HttpContext.Current.Server.MapPath("~" + dbFilePath));
}
string destination = System.Web.HttpContext.Current.Server.MapPath("~" + dbFilePath) + strFileName;
try
{
if ((dsHeader.Tables.Count == ds.Tables.Count && customHeader) || !customHeader)
{
List <KeyValuePair <string, string> > mergecellsdictionary = new List <KeyValuePair <string, string> >();
using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
var workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
WorkbookStylesPart stylesPart = workbook.WorkbookPart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = GenerateStyleSheet();
stylesPart.Stylesheet.Save();
for (int i = 0; i < ds.Tables.Count; i++)
{
var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>();
var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>();
string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
uint sheetId = 1;
if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
{
sheetId = sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = ds.Tables[i].TableName
};
sheets.Append(sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
List <String> columns = new List <string>();
if (customHeader)
{
foreach (System.Data.DataColumn column in dsHeader.Tables[i].Columns)
{
columns.Add(column.ColumnName);
}
int rIndex = 1;
foreach (System.Data.DataRow dsrow in dsHeader.Tables[i].Rows)
{
headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (String col in columns)
{
string[] strval = Convert.ToString(dsrow[col]).Split(',');
if (strval.Length == 4)
{
// Check for rowspan
if (!string.IsNullOrEmpty(strval[2]) && strval[2].All(Char.IsDigit))
{
if (Convert.ToInt32(strval[2]) > 1)
{
int rpos = (columns.IndexOf(col)) + 1;
mergecellsdictionary.Add(new KeyValuePair <string, string>(sheet.Name, getColumnNameFromIndex(rpos) + rIndex.ToString() + "," + getColumnNameFromIndex(rpos) + (rIndex + Convert.ToInt32(strval[2]) - 1).ToString()));
}
}
// Check for colspan
if (!string.IsNullOrEmpty(strval[3]) && strval[3].All(Char.IsDigit))
{
if (Convert.ToInt32(strval[3]) > 1)
{
int cpos = (columns.IndexOf(col)) + 1;
mergecellsdictionary.Add(new KeyValuePair <string, string>(sheet.Name, getColumnNameFromIndex(cpos) + rIndex.ToString() + "," + getColumnNameFromIndex(cpos + Convert.ToInt32(strval[3]) - 1) + rIndex.ToString()));
}
}
}
else if (strval.Length == 1)
{
string str1 = strval[0];
strval = new string[] { str1, "1" };
}
int indexNum = (strval[1].All(Char.IsDigit)) ? Convert.ToInt32(strval[1]) : 1;
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(strval[0]);
cell.StyleIndex = (UInt32)indexNum;
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
rIndex = rIndex + 1;
}
}
else
{
foreach (System.Data.DataColumn column in ds.Tables[i].Columns)
{
columns.Add(column.ColumnName);
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
cell.StyleIndex = 1; // The light gray Fill
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
}
foreach (System.Data.DataRow dsrow in ds.Tables[i].Rows)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (String col in columns)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
//cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
if (IsDouble(dsrow[col].ToString()))
{
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
}
else
{
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
}
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
}
}
if (mergecellsdictionary.Count > 0)
{
foreach (KeyValuePair <string, string> entry in mergecellsdictionary)
{
string[] strcells = entry.Value.Split(',');
mergeCell(destination, entry.Key, strcells[0], strcells[1]);
}
}
downloadfile(destination, exportFileName);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (System.IO.File.Exists(destination))
{
System.IO.File.Delete(destination);
}
}
}
public void CreateExcelDoc(string fileName)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook)) {
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
// Adding style
WorkbookStylesPart stylePart = workbookPart.AddNewPart <WorkbookStylesPart>();
stylePart.Stylesheet = GenerateStylesheet();
stylePart.Stylesheet.Save();
// Setting up columns
Columns columns = new Columns(
new Column // Id column
{
Min = 1,
Max = 1,
Width = 4,
CustomWidth = true
},
new Column // Name and Birthday columns
{
Min = 2,
Max = 3,
Width = 15,
CustomWidth = true
},
new Column // Salary column
{
Min = 4,
Max = 4,
Width = 8,
CustomWidth = true
});
worksheetPart.Worksheet.AppendChild(columns);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Employees"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
List <Employee> employees = Employees.EmployeesList;
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
row.Append(
ConstructCell("Id", CellValues.String, 2),
ConstructCell("Name", CellValues.String, 2),
ConstructCell("Birth Date", CellValues.String, 2),
ConstructCell("Salary", CellValues.String, 2));
// Insert the header row to the Sheet Data
sheetData.AppendChild(row);
// Inserting each employee
foreach (var employee in employees)
{
row = new Row();
row.Append(
ConstructCell(employee.Id.ToString(), CellValues.Number, 2),
ConstructCell(employee.Name, CellValues.String, 2),
ConstructCell(employee.DOB.ToString("yyyy/MM/dd"), CellValues.String, 3),
ConstructCell(employee.Salary.ToString(), CellValues.Number, 3));
sheetData.AppendChild(row);
}
worksheetPart.Worksheet.Save();
}
}
public void Main(List <MetrologicalServiseListObjectDTO> aObjects, string excelTitle, string filename)
{
//Создаем новый документ
using (SpreadsheetDocument document = SpreadsheetDocument.Create(filename + ".xlsx", SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
FileVersion fv = new FileVersion {
ApplicationName = "Microsoft Office Excel"
};
worksheetPart.Worksheet = new Worksheet(new SheetData());
WorkbookStylesPart wbsp = workbookPart.AddNewPart <WorkbookStylesPart>();
// Добавляем в документ набор стилей
wbsp.Stylesheet = OpenXMLCustomMethods.GenerateStyleSheet();
wbsp.Stylesheet.Save();
// Задаем колонки и их ширину
Columns lstColumns = worksheetPart.Worksheet.GetFirstChild <Columns>();
bool needToInsertColumns = false;
if (lstColumns == null)
{
lstColumns = new Columns();
needToInsertColumns = true;
}
lstColumns.Append(new Column()
{
Min = 1, Max = 1, Width = 5, CustomWidth = false
});
lstColumns.Append(new Column()
{
Min = 2, Max = 2, Width = 23, CustomWidth = false
});
lstColumns.Append(new Column()
{
Min = 3, Max = 3, Width = 19, CustomWidth = false
});
lstColumns.Append(new Column()
{
Min = 4, Max = 4, Width = 16, CustomWidth = false
});
lstColumns.Append(new Column()
{
Min = 5, Max = 6, Width = 8, CustomWidth = false
});
lstColumns.Append(new Column()
{
Min = 7, Max = 7, Width = 10, CustomWidth = false
});
lstColumns.Append(new Column()
{
Min = 8, Max = 8, Width = 15, CustomWidth = false
});
lstColumns.Append(new Column()
{
Min = 9, Max = 9, Width = 12, CustomWidth = false
});
lstColumns.Append(new Column()
{
Min = 10, Max = 10, Width = 18, CustomWidth = false
});
if (needToInsertColumns)
{
worksheetPart.Worksheet.InsertAt(lstColumns, 0);
}
// Width = 5, Cu
//Width = 28,
//Width = 11,
//Width = 19,
//Width = 6, C
// Width = 14,
// Width = 22,
// Width = 12,
// , Width = 15,
//Создаем лист в книге
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "График"
};
sheets.Append(sheet);
Worksheet worksheet = OpenXMLCustomMethods.GetWorksheet(document, sheet.Name);
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
//Добавим заголовки в первую строку
uint regular = 5;
uint head = 6;
uint regular_rotate = 4;
Row row = new Row()
{
RowIndex = 1
};
sheetData.Append(row);
OpenXMLCustomMethods.InsertCell(row, "График поверки, калибровки и аттестации средств измерений АО «Атомтехэнерго»", CellValues.String, head);
OpenXMLCustomMethods.MergeTwoCells(worksheet, "A1", "J1");
row = new Row()
{
RowIndex = 2
};
sheetData.Append(row);
OpenXMLCustomMethods.InsertCell(row, "№ п.п.", CellValues.String, regular);
OpenXMLCustomMethods.MergeTwoCells(worksheet, "A2", "A3");
OpenXMLCustomMethods.InsertCell(row, "Наименование, тип, заводской №", CellValues.String, regular);
OpenXMLCustomMethods.MergeTwoCells(worksheet, "B2", "B3");
OpenXMLCustomMethods.InsertCell(row, "Метрологические характеристики", CellValues.String, regular);
OpenXMLCustomMethods.InsertCellEmpty(row, 1, regular);
OpenXMLCustomMethods.MergeTwoCells(worksheet, "C2", "D2");
OpenXMLCustomMethods.InsertCell(row, "МПИ, месяцы", CellValues.String, regular_rotate);
OpenXMLCustomMethods.MergeTwoCells(worksheet, "E2", "E3");
OpenXMLCustomMethods.InsertCell(row, "Вид работ", CellValues.String, regular_rotate);
OpenXMLCustomMethods.MergeTwoCells(worksheet, "F2", "F3");
OpenXMLCustomMethods.InsertCell(row, "Дата последней работы", CellValues.String, regular_rotate);
OpenXMLCustomMethods.MergeTwoCells(worksheet, "G2", "G3");
OpenXMLCustomMethods.InsertCell(row, "Место проведения работ", CellValues.String, regular_rotate);
OpenXMLCustomMethods.MergeTwoCells(worksheet, "H2", "H3");
OpenXMLCustomMethods.InsertCell(row, "Сроки проведения следующей поверки", CellValues.String, regular_rotate);
OpenXMLCustomMethods.MergeTwoCells(worksheet, "I2", "I3");
OpenXMLCustomMethods.InsertCell(row, "Подразделение", CellValues.String, regular);
OpenXMLCustomMethods.MergeTwoCells(worksheet, "J2", "J3");
row = new Row()
{
RowIndex = 3
};
sheetData.Append(row);
OpenXMLCustomMethods.InsertCellEmpty(row, 2, regular);
OpenXMLCustomMethods.InsertCell(row, "Класс точности, погрешность", CellValues.String, regular);
OpenXMLCustomMethods.InsertCell(row, "Пределы (диапазоны); измерений", CellValues.String, regular);
OpenXMLCustomMethods.InsertCellEmpty(row, 6, regular);
int index = 0;
for (UInt32Value i = 4; index < aObjects.Count; i++, index++)
{
row = new Row()
{
RowIndex = i
};
sheetData.Append(row);
OpenXMLCustomMethods.InsertCell(row, (index + 1).ToString(), CellValues.String, regular);
OpenXMLCustomMethods.InsertCell(row, aObjects[index].TypeAOTitle + ", " +
aObjects[index].SignTypeTitle + ", " +
aObjects[index].FactoryNumber, CellValues.String, regular);
OpenXMLCustomMethods.InsertCell(row, aObjects[index].Accuracy, CellValues.String, regular);
OpenXMLCustomMethods.InsertCell(row, aObjects[index].MesuareLimits, CellValues.String, regular);
OpenXMLCustomMethods.InsertCell(row, aObjects[index].MetrologicalWorkPeriod.ToString(), CellValues.String, regular);
OpenXMLCustomMethods.InsertCell(row, aObjects[index].WorkTypeTitle, CellValues.String, regular);
OpenXMLCustomMethods.InsertCell(row, aObjects[index].MetrologicalWorkDateStr, CellValues.String, regular);
OpenXMLCustomMethods.InsertCell(row, aObjects[index].WorkEnterpriseTitle, CellValues.String, regular);
OpenXMLCustomMethods.InsertCell(row, aObjects[index].MetrologicalWorkDatePlanStr, CellValues.String, regular);
OpenXMLCustomMethods.InsertCell(row, aObjects[index].OwnerDepartmentTitle, CellValues.String, regular);
}
OpenXMLCustomMethods.OrientationLandscape(worksheetPart);
workbookPart.Workbook.Save();
document.Close();
}
}
public void CreateFile(string nameFile, string nameOfSheet)
{
// using (SpreadsheetDocument document =
// SpreadsheetDocument.Create("TestReport.xlsx", SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
FileVersion fv = new FileVersion();
//fv.ApplicationName = "Microsoft Office Excel";
worksheetPart.Worksheet = new Worksheet(new SheetData());
WorkbookStylesPart wbsp = workbookPart.AddNewPart <WorkbookStylesPart>();
// Добавляем в документ набор стилей
wbsp.Stylesheet = GenerateStyleSheet();
wbsp.Stylesheet.Save();
// Задаем колонки и их ширину
Columns lstColumns = worksheetPart.Worksheet.GetFirstChild <Columns>();
Boolean needToInsertColumns = false;
if (lstColumns == null)
{
lstColumns = new Columns();
needToInsertColumns = true;
}
lstColumns.Append(new Column()
{
Min = 1, Max = 10, Width = 20, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 2, Max = 10, Width = 20, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 3, Max = 10, Width = 20, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 4, Max = 10, Width = 20, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 5, Max = 10, Width = 20, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 6, Max = 10, Width = 20, CustomWidth = true
});
lstColumns.Append(new Column()
{
Min = 7, Max = 10, Width = 20, CustomWidth = true
});
if (needToInsertColumns)
{
worksheetPart.Worksheet.InsertAt(lstColumns, 0);
}
//Создаем лист в книге
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = nameOfSheet
};
sheets.Append(sheet);
sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
//Добавим заголовки в первую строку
Row row = new Row()
{
RowIndex = 1
};
sheetData.Append(row);
InsertCell(row, 1, "Time", CellValues.String, 5);
InsertCell(row, 2, "TestCaseName", CellValues.String, 5);
InsertCell(row, 3, "TestName", CellValues.String, 5);
InsertCell(row, 4, "TestCommand", CellValues.String, 5);
InsertCell(row, 5, "ResultsOfTest", CellValues.String, 5);
InsertCell(row, 6, "DataFromFile", CellValues.String, 5);
InsertCell(row, 7, "DataFromBoard", CellValues.String, 5);
InsertCell(row, 8, "nameOfCAN", CellValues.String, 5);
InsertCell(row, 9, "TX_msg_cnt", CellValues.String, 5);
InsertCell(row, 10, "RX_msg_cnt", CellValues.String, 5);
InsertCell(row, 11, "Error_Count", CellValues.String, 5);
InsertCell(row, 12, "Lost_RX_q", CellValues.String, 5);
InsertCell(row, 13, "Lost_RX_o", CellValues.String, 5);
InsertCell(row, 14, "Lost_TX_q", CellValues.String, 5);
InsertCell(row, 15, "RX_queue", CellValues.String, 5);
InsertCell(row, 16, "TX_queue", CellValues.String, 5);
InsertCell(row, 17, "B_O", CellValues.String, 5);
InsertCell(row, 18, "UsedRX", CellValues.String, 5);
InsertCell(row, 19, "UsedTX", CellValues.String, 5);
//InsertCell(row, 5, "Стиль 5", CellValues.String, 5);
//InsertCell(row, 6, "Стиль 6", CellValues.String, 5);
//InsertCell(row, 7, "Стиль 7", CellValues.String, 5);
// Добавляем в строку все стили подряд.
//row = new Row() { RowIndex = 2 };
//sheetData.Append(row);
//InsertCell(row, 1, "1", CellValues.Number, 1);
//InsertCell(row, 2, ReplaceHexadecimalSymbols("Тест"), CellValues.String, 2);
//InsertCell(row, 3, ReplaceHexadecimalSymbols("Тест"), CellValues.String, 3);
//InsertCell(row, 4, ReplaceHexadecimalSymbols("Тест"), CellValues.String, 4);
//InsertCell(row, 5, ReplaceHexadecimalSymbols("Тест"), CellValues.String, 5);
//InsertCell(row, 6, ReplaceHexadecimalSymbols("01.01.2017"), CellValues.String, 6);
//InsertCell(row, 7, ReplaceHexadecimalSymbols("123"), CellValues.String, 7);
workbookPart.Workbook.Save();
//document.Close();
}
}
public static void ExportAdvancedMode(Stream stream, List <ExcelSheet> excelSheets)
{
// La clase SpreadsheetDocument representa un paquete de documentos de Excel.
// Para crear un documento de Excel, cree una instancia de la clase SpreadsheetDocument y rellénela con partes.
// Como mínimo, el documento debe tener una parte de libro que sirva como contenedor para el documento y una parte de hoja de cálculo.
// Por defecto AutoSave = true, Editable = true, y Type = xlsx.
using (var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
{
// Añadimos un elemenro de libro al documento (es obligatorio)
var wbp = document.AddWorkbookPart();
wbp.Workbook = new Workbook();
// Añadimos hojas al libro
Sheets sheets = document.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
foreach (var excelSheet in excelSheets)
{
var sheetName = excelSheet.SheetName.ToLower();
// START
WorksheetPart wsp = wbp.AddNewPart <WorksheetPart>();
Worksheet ws = new Worksheet();
SheetData sd = new SheetData();
// END START
#region DATA
#region Style configuration
// Intentamos obtener el estilo
WorkbookStylesPart styles = wbp.WorkbookStylesPart;
#region Estilos por defecto
if (styles == null)
{
// Creamos un nuevo estilo
styles = wbp.AddNewPart <WorkbookStylesPart>();
styles.Stylesheet = new Stylesheet();
// Build the formatted header style
styles.Stylesheet.Fonts = new Fonts {
Count = 1
};
styles.Stylesheet.Fonts.AppendChild(new Font()); // required, reserved by Excel
styles.Stylesheet.Fills = new Fills {
Count = 2
};
styles.Stylesheet.Fills.AppendChild(new Fill {
PatternFill = new PatternFill {
PatternType = PatternValues.None
}
}); // required, reserved by Excel
styles.Stylesheet.Fills.AppendChild(new Fill {
PatternFill = new PatternFill {
PatternType = PatternValues.Gray125
}
}); // required, reserved by Excel
styles.Stylesheet.Borders = new Borders {
Count = 1
};
styles.Stylesheet.Borders.AppendChild(new Border()); // required, reserved by Excel
styles.Stylesheet.CellStyleFormats = new CellStyleFormats {
Count = 1
};
styles.Stylesheet.CellStyleFormats.AppendChild(new CellFormat()); // required, reserved by Excel
styles.Stylesheet.NumberingFormats = new NumberingFormats {
Count = 0
};
//styles.Stylesheet.NumberingFormats.AppendChild(new NumberingFormat {NumberFormatId = 0, FormatCode = "."}); // required, reserved by Excel
styles.Stylesheet.CellFormats = new CellFormats {
Count = 1
};
styles.Stylesheet.CellFormats.AppendChild(new CellFormat()); // required, reserved by Excel
}
#endregion
#region Estilo de cabecera
List <ExcelStyleRelation> styleHeaderRelation = new List <ExcelStyleRelation>();
if (excelSheet.HeaderStyles.Any())
{
foreach (ExcelStyleConfig styleConfig in excelSheet.HeaderStyles.Where(w => w.SheetNameStyle.Equals(sheetName, StringComparison.InvariantCultureIgnoreCase)))
{
// Convertimos todas las lineas a minusculas
styleConfig.SheetNameStyle = styleConfig.SheetNameStyle.ToLower();
styleConfig.HeaderName = styleConfig.HeaderName.ConvertAll(d => d.ToLower());
UInt32Value headerFontIndex = 0;
UInt32Value headerFillIndex = 0;
UInt32Value headerBorderIndex = 0;
UInt32Value headerStyleFormatIndex = 0;
UInt32Value headerStyleIndex = 0;
if (styleConfig.GetFont() != null)
{
headerFontIndex = CreateFont(styles.Stylesheet, styleConfig.GetFont());
}
if (styleConfig.GetFill() != null)
{
headerFillIndex = CreateFill(styles.Stylesheet, styleConfig.GetFill());
}
if (styleConfig.GetBorder() != null)
{
headerBorderIndex = CreateBorder(styles.Stylesheet, styleConfig.GetBorder());
}
headerStyleIndex = CreateCellFormat(styles.Stylesheet, headerStyleFormatIndex, headerFontIndex, headerBorderIndex, headerFillIndex, null, styleConfig.GetAlignment());
styleHeaderRelation.Add(new ExcelStyleRelation(styleConfig.SheetNameStyle, styleConfig.HeaderName, headerStyleIndex));
}
}
#endregion
#region Estilo de filas
var styleRowsRelation = new List <ExcelStyleRelation>();
if (excelSheet.RowsStyles.Any())
{
foreach (var styleConfig in excelSheet.RowsStyles.Where(w => w.SheetNameStyle.Equals(sheetName, StringComparison.InvariantCultureIgnoreCase)))
{
// Convertimos todas las lineas a minusculas
styleConfig.SheetNameStyle = styleConfig.SheetNameStyle.ToLower();
styleConfig.HeaderName = styleConfig.HeaderName.ConvertAll(d => d.ToLower());
UInt32Value rowFontIndex = 0;
UInt32Value rowFillIndex = 0;
UInt32Value rowBorderIndex = 0;
UInt32Value rowNumberFormatIndex = 0;
UInt32Value rowStyleFormatIndex = 0;
UInt32Value rowStyleIndex = 0;
if (styleConfig.GetFont() != null)
{
rowFontIndex = CreateFont(styles.Stylesheet, styleConfig.GetFont());
}
if (styleConfig.GetFill() != null)
{
rowFillIndex = CreateFill(styles.Stylesheet, styleConfig.GetFill());
}
if (styleConfig.GetBorder() != null)
{
rowBorderIndex = CreateBorder(styles.Stylesheet, styleConfig.GetBorder());
}
if (styleConfig.GetNumberingFormat() != null)
{
rowNumberFormatIndex = CreateNumberFormat(styles.Stylesheet, styleConfig.GetNumberingFormat());
}
rowStyleIndex = CreateCellFormat(styles.Stylesheet, rowStyleFormatIndex, rowFontIndex, rowBorderIndex, rowFillIndex, rowNumberFormatIndex, styleConfig.GetAlignment());
styleRowsRelation.Add(new ExcelStyleRelation(styleConfig.SheetNameStyle, styleConfig.HeaderName, rowStyleIndex, styleConfig.ApplyOnlyWhenValue));
}
}
#endregion
// Save styles
styles.Stylesheet.Save();
#endregion
#region Header data
// Generamos la línea de cabecera
var headerRow = new Row
{
RowIndex = (uint)1
};
int columnIndex = 1;
foreach (DataColumn column in excelSheet.Table.Columns)
{
// Comprobamos si vienen datos en la configuración de las columnas
var dataColumn = excelSheet.ColumnsConfiguration.FirstOrDefault(f => column.ColumnName.Equals(f.ColumnName, StringComparison.InvariantCultureIgnoreCase));
if (dataColumn == null)
{
dataColumn = new ExcelColumnConfig(column.ColumnName);
excelSheet.ColumnsConfiguration.Add(dataColumn);
}
// Si es visible, añadimos, si no, nos la saltamos, tanto aquí, como en las filas
if (dataColumn.ColumnVisible)
{
var cellHeader = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(dataColumn.ColumnDisplayName),
CellReference = GetColumnName(columnIndex) + 1,
};
#region Aplicación de estilos de cabecera
var headerStyleFound = styleHeaderRelation
.Where(s => s.ApplyStyleOnSheetName.Equals(sheetName) && (s.ApplyStyleOnColumns.Contains(column.ColumnName.ToLower()) || s.ApplyStyleOnColumns.Contains("*")))
.ToList();
if (headerStyleFound.Any())
{
if (headerStyleFound.Count == 1)
{
cellHeader.StyleIndex = headerStyleFound.Single().StyleIndex;
}
else
{
var relation = headerStyleFound.First(w => !w.ApplyStyleOnColumns.Contains("*"));
cellHeader.StyleIndex = relation.StyleIndex;
}
}
#endregion
headerRow.AppendChild(cellHeader);
columnIndex++;
}
}
// Añadimos la línea de cabecera a la hoja
sd.AppendChild(headerRow);
#endregion
#region Rows data
// Generamos cada una de las filas
foreach (DataRow row in excelSheet.Table.Rows)
{
// Creamos una nueva fila
var newRow = new Row();
// Para cada columna, vamos rellenando los datos
foreach (DataColumn col in excelSheet.Table.Columns)
{
// Comprobamos si vienen datos en la configuración de las columnas
var dataColumn = excelSheet.ColumnsConfiguration.First(f => col.ColumnName.ToLower().Equals(f.ColumnName.ToLower()));
// Si es visible, añadimos, si no, nos la saltamos, tanto aquí, como en las filas
if (!dataColumn.ColumnVisible)
{
continue;
}
var cell = GetCellValues(row, col, dataColumn.ColumnDateFormat);
var haveValue = !string.IsNullOrEmpty(cell.CellValue.Text);
// Buscamos todos los estilos que apliquen a esta hoja de excel, y ademas apliquen a esta columna o a todas
var dataStyleFound = styleRowsRelation
.Where(s => s.ApplyStyleOnSheetName.Equals(sheetName) && (s.ApplyStyleOnColumns.Contains(col.ColumnName.ToLower()) || s.ApplyStyleOnColumns.Contains("*")))
.ToList();
if (!dataStyleFound.Any())
{
newRow.AppendChild(cell);
continue;
}
#region Aplicación de estilos de filas
if (dataStyleFound.Count == 1)
{
var relation = dataStyleFound.Single();
if (haveValue && relation.ApplyOnlyWhenValue)
{
// Si este estilo aplica solamente cuando tiene valor y la celda tiene valor...
cell.StyleIndex = relation.StyleIndex;
}
else if (!relation.ApplyOnlyWhenValue)
{
// Si este estilo no aplica solamente cuando tiene valor, se lo asignamos
cell.StyleIndex = relation.StyleIndex;
}
}
else
{
// Tiene varios estilos aplicando a esta columna
// La prioridad va en este orden:
// 1 - Si aplica a la columna y solo cuando tenga valor
// 2 - Si aplica a la columna
// 3 - Si aplica a todas las columnas
var relation = dataStyleFound.FirstOrDefault(f => f.ApplyOnlyWhenValue && !f.ApplyStyleOnColumns.Contains("*"));
if (relation != null && haveValue)
{
cell.StyleIndex = relation.StyleIndex;
}
else
{
relation = dataStyleFound.FirstOrDefault(f => !f.ApplyStyleOnColumns.Contains("*"));
if (relation != null)
{
cell.StyleIndex = relation.StyleIndex;
}
else
{
relation = dataStyleFound.First();
cell.StyleIndex = relation.StyleIndex;
}
}
}
#endregion
newRow.AppendChild(cell);
}
// Añadimos la fila a la hoja
sd.AppendChild(newRow);
}
#endregion
#endregion
// END
ws.Append(sd);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheet sheet = new Sheet
{
Name = excelSheet.SheetName,
SheetId = (UInt32Value)excelSheet.SheetId,
Id = wbp.GetIdOfPart(wsp)
};
sheets.Append(sheet);
// END END
}
// Guardamos el libro
wbp.Workbook.Save();
// Cerramos el documento
document.Close();
}
stream.Seek(0, SeekOrigin.Begin);
}
// создает ситили форматы
private void GenWorkbookStylesPart(WorkbookStylesPart workbookStylesPart)
{
Stylesheet stylesheet = new Stylesheet();
Fonts fonts1 = new Fonts();
Font font1 = new Font();
fonts1.Append(font1);
Fills fills1 = new Fills();
Fill fill1 = new Fill();
Fill fill2 = new Fill();
Fill fill3 = new Fill();
PatternFill patternFill3 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor1 = new ForegroundColor()
{
Rgb = "FFFFFF00"
};
patternFill3.Append(foregroundColor1);
fill3.Append(patternFill3);
fills1.Append(fill1);
fills1.Append(fill2);
fills1.Append(fill3);
Borders borders1 = new Borders();
Border border1 = new Border();
borders1.Append(border1);
NumberingFormats numberingFormats1 = new NumberingFormats()
{
Count = (UInt32Value)2U
};
NumberingFormat numberingFormat1 = new NumberingFormat()
{
NumberFormatId = (UInt32Value)164U, FormatCode = "[$-F800]dddd\\,\\ mmmm\\ dd\\,\\ yyyy"
};
NumberingFormat numberingFormat2 = new NumberingFormat()
{
NumberFormatId = (UInt32Value)166U, FormatCode = "#,##0\"р.\""
};
numberingFormats1.Append(numberingFormat1);
numberingFormats1.Append(numberingFormat2);
CellFormats cellFormats1 = new CellFormats()
{
Count = (UInt32Value)5U
};
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U
}; // Формат № 0 по умолчанию общий
CellFormat cellFormat3 = new CellFormat()
{
NumberFormatId = (UInt32Value)164U
}; // Формат № 1 для дат FormatCode = "[$-F800]dddd\\,\\ mmmm\\ dd\\,\\ yyyy"
CellFormat cellFormat4 = new CellFormat()
{
NumberFormatId = (UInt32Value)166U
}; //формат № 2 для денег FormatCode = "#,##0.00\"р.\""
CellFormat cellFormat5 = new CellFormat()
{
NumberFormatId = (UInt32Value)14U
}; //формат № 3 для дат * 14.03.2015
CellFormat cellFormat6 = new CellFormat()
{
NumberFormatId = (UInt32Value)2U
}; //формат № 4 для чисел
cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
cellFormats1.Append(cellFormat4);
cellFormats1.Append(cellFormat5);
cellFormats1.Append(cellFormat6);
stylesheet.Append(numberingFormats1);
stylesheet.Append(fonts1);
stylesheet.Append(fills1);
stylesheet.Append(borders1);
stylesheet.Append(cellFormats1);
workbookStylesPart.Stylesheet = stylesheet;
}
public void Main(List <MetrologyObjectDTO> aObjects, string excelTitle, ParametrsAOList[] Parameters, string filename)
{
//Создаем новый документ
using (SpreadsheetDocument document = SpreadsheetDocument.Create(filename + ".xlsx", SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
FileVersion fv = new FileVersion {
ApplicationName = "Microsoft Office Excel"
};
worksheetPart.Worksheet = new Worksheet(new SheetData());
WorkbookStylesPart wbsp = workbookPart.AddNewPart <WorkbookStylesPart>();
// Добавляем в документ набор стилей
wbsp.Stylesheet = OpenXMLCustomMethods.GenerateStyleSheet();
wbsp.Stylesheet.Save();
// Задаем колонки и их ширину
Columns lstColumns = worksheetPart.Worksheet.GetFirstChild <Columns>();
bool needToInsertColumns = false;
if (lstColumns == null)
{
lstColumns = new Columns();
needToInsertColumns = true;
}
lstColumns.Append(new Column()
{
Min = 1, Max = 1, Width = 10, CustomWidth = true
});
UInt32Value CountColumn = Convert.ToUInt32(Parameters.Length + 1);
lstColumns.Append(new Column()
{
Min = 2, Max = CountColumn, Width = 20, CustomWidth = true
});
if (needToInsertColumns)
{
worksheetPart.Worksheet.InsertAt(lstColumns, 0);
}
//Создаем лист в книге
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "График"
};
sheets.Append(sheet);
Worksheet worksheet = OpenXMLCustomMethods.GetWorksheet(document, sheet.Name);
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
//Добавим заголовки в первую строку
Row row = new Row()
{
RowIndex = 1
};
uint regular = 1;
sheetData.Append(row);
PropertyInfo[] properties = typeof(MetrologyObjectDTO).GetProperties();
List <PropertyInfo> propertiesUses = new List <PropertyInfo>();
OpenXMLCustomMethods.InsertCell(row, "№ п.п.", CellValues.String, regular);
for (int i = 0; i < Parameters.Length; i++)
{
OpenXMLCustomMethods.InsertCell(row, Parameters[i].Title, CellValues.String, regular);
for (int j = 0; j < properties.Length; j++)
{
if (properties[j].Name == Parameters[i].Name)
{
propertiesUses.Add(properties[j]);
}
}
}
int index = 0;
for (UInt32Value i = 2; index < aObjects.Count; i++, index++)
{
row = new Row()
{
RowIndex = i
};
sheetData.Append(row);
OpenXMLCustomMethods.InsertCell(row, (index + 1).ToString(), CellValues.String, regular);
foreach (PropertyInfo property in propertiesUses)
{
OpenXMLCustomMethods.InsertCell(row, (property.GetValue(aObjects[index]) ?? "").ToString(), CellValues.String, regular);
}
}
OpenXMLCustomMethods.OrientationLandscape(worksheetPart);
workbookPart.Workbook.Save();
document.Close();
}
}
// Generates content of workbookStyles.
private void GenerateWorkbookStylesContent(WorkbookStylesPart workbookStyles)
{
Stylesheet stylesheet1 = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac"
}
};
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
NumberingFormats numberingFormats1 = new NumberingFormats()
{
Count = (UInt32Value)1U
};
NumberingFormat numberingFormat1 = new NumberingFormat()
{
NumberFormatId = (UInt32Value)43U, FormatCode = "_(* #,##0.00_);_(* \\(#,##0.00\\);_(* \"-\"??_);_(@_)"
};
numberingFormats1.Append(numberingFormat1);
Fonts fonts1 = new Fonts()
{
Count = (UInt32Value)4U, KnownFonts = true
};
Font font1 = new Font();
FontSize fontSize1 = new FontSize()
{
Val = 11D
};
Color color1 = new Color()
{
Theme = (UInt32Value)1U
};
FontName fontName1 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme1 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontScheme1);
Font font2 = new Font();
Bold bold1 = new Bold();
FontSize fontSize2 = new FontSize()
{
Val = 11D
};
Color color2 = new Color()
{
Theme = (UInt32Value)1U
};
FontName fontName2 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme2 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font2.Append(bold1);
font2.Append(fontSize2);
font2.Append(color2);
font2.Append(fontName2);
font2.Append(fontFamilyNumbering2);
font2.Append(fontScheme2);
Font font3 = new Font();
Bold bold2 = new Bold();
Italic italic1 = new Italic();
FontSize fontSize3 = new FontSize()
{
Val = 11D
};
Color color3 = new Color()
{
Rgb = "FFFF0000"
};
FontName fontName3 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering3 = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme3 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font3.Append(bold2);
font3.Append(italic1);
font3.Append(fontSize3);
font3.Append(color3);
font3.Append(fontName3);
font3.Append(fontFamilyNumbering3);
font3.Append(fontScheme3);
Font font4 = new Font();
Italic italic2 = new Italic();
FontSize fontSize4 = new FontSize()
{
Val = 11D
};
Color color4 = new Color()
{
Rgb = "FFFF0000"
};
FontName fontName4 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering4 = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme4 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font4.Append(italic2);
font4.Append(fontSize4);
font4.Append(color4);
font4.Append(fontName4);
font4.Append(fontFamilyNumbering4);
font4.Append(fontScheme4);
fonts1.Append(font1);
fonts1.Append(font2);
fonts1.Append(font3);
fonts1.Append(font4);
Fills fills1 = new Fills()
{
Count = (UInt32Value)4U
};
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill()
{
PatternType = PatternValues.None
};
fill1.Append(patternFill1);
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill()
{
PatternType = PatternValues.Gray125
};
fill2.Append(patternFill2);
Fill fill3 = new Fill();
PatternFill patternFill3 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor1 = new ForegroundColor()
{
Theme = (UInt32Value)0U, Tint = -4.9989318521683403E-2D
};
BackgroundColor backgroundColor1 = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill3.Append(foregroundColor1);
patternFill3.Append(backgroundColor1);
fill3.Append(patternFill3);
Fill fill4 = new Fill();
PatternFill patternFill4 = new PatternFill()
{
PatternType = PatternValues.Solid
};
ForegroundColor foregroundColor2 = new ForegroundColor()
{
Rgb = "FFFFFFCC"
};
BackgroundColor backgroundColor2 = new BackgroundColor()
{
Indexed = (UInt32Value)64U
};
patternFill4.Append(foregroundColor2);
patternFill4.Append(backgroundColor2);
fill4.Append(patternFill4);
fills1.Append(fill1);
fills1.Append(fill2);
fills1.Append(fill3);
fills1.Append(fill4);
Borders borders1 = new Borders()
{
Count = (UInt32Value)2U
};
Border border1 = new Border();
LeftBorder leftBorder1 = new LeftBorder();
RightBorder rightBorder1 = new RightBorder();
TopBorder topBorder1 = new TopBorder();
BottomBorder bottomBorder1 = new BottomBorder();
DiagonalBorder diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
Border border2 = new Border();
LeftBorder leftBorder2 = new LeftBorder();
RightBorder rightBorder2 = new RightBorder();
TopBorder topBorder2 = new TopBorder()
{
Style = BorderStyleValues.Thin
};
Color color5 = new Color()
{
Indexed = (UInt32Value)64U
};
topBorder2.Append(color5);
BottomBorder bottomBorder2 = new BottomBorder();
DiagonalBorder diagonalBorder2 = new DiagonalBorder();
border2.Append(leftBorder2);
border2.Append(rightBorder2);
border2.Append(topBorder2);
border2.Append(bottomBorder2);
border2.Append(diagonalBorder2);
borders1.Append(border1);
borders1.Append(border2);
CellStyleFormats cellStyleFormats1 = new CellStyleFormats()
{
Count = (UInt32Value)1U
};
CellFormat cellFormat1 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U
};
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats()
{
Count = (UInt32Value)16U
};
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
CellFormat cellFormat3 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyAlignment = true
};
Alignment alignment1 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Center
};
cellFormat3.Append(alignment1);
CellFormat cellFormat4 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true
};
CellFormat cellFormat5 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyAlignment = true
};
Alignment alignment2 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Left
};
cellFormat5.Append(alignment2);
CellFormat cellFormat6 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyAlignment = true
};
Alignment alignment3 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Center
};
cellFormat6.Append(alignment3);
CellFormat cellFormat7 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyAlignment = true
};
Alignment alignment4 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Center
};
cellFormat7.Append(alignment4);
CellFormat cellFormat8 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyAlignment = true
};
Alignment alignment5 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Right
};
cellFormat8.Append(alignment5);
CellFormat cellFormat9 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true, ApplyAlignment = true
};
Alignment alignment6 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Left
};
cellFormat9.Append(alignment6);
CellFormat cellFormat10 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true, ApplyAlignment = true
};
Alignment alignment7 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Center
};
cellFormat10.Append(alignment7);
CellFormat cellFormat11 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)3U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyAlignment = true
};
Alignment alignment8 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Center
};
cellFormat11.Append(alignment8);
CellFormat cellFormat12 = new CellFormat()
{
NumberFormatId = (UInt32Value)43U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true
};
CellFormat cellFormat13 = new CellFormat()
{
NumberFormatId = (UInt32Value)9U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFill = true, ApplyAlignment = true
};
Alignment alignment9 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Left
};
cellFormat13.Append(alignment9);
CellFormat cellFormat14 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true, ApplyAlignment = true
};
Alignment alignment10 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Center
};
cellFormat14.Append(alignment10);
CellFormat cellFormat15 = new CellFormat()
{
NumberFormatId = (UInt32Value)37U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true
};
Alignment alignment11 = new Alignment()
{
Horizontal = HorizontalAlignmentValues.Center
};
cellFormat15.Append(alignment11);
CellFormat cellFormat16 = new CellFormat()
{
NumberFormatId = (UInt32Value)43U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true, ApplyFont = true, ApplyFill = true, ApplyBorder = true
};
CellFormat cellFormat17 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)2U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true
};
cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
cellFormats1.Append(cellFormat4);
cellFormats1.Append(cellFormat5);
cellFormats1.Append(cellFormat6);
cellFormats1.Append(cellFormat7);
cellFormats1.Append(cellFormat8);
cellFormats1.Append(cellFormat9);
cellFormats1.Append(cellFormat10);
cellFormats1.Append(cellFormat11);
cellFormats1.Append(cellFormat12);
cellFormats1.Append(cellFormat13);
cellFormats1.Append(cellFormat14);
cellFormats1.Append(cellFormat15);
cellFormats1.Append(cellFormat16);
cellFormats1.Append(cellFormat17);
CellStyles cellStyles1 = new CellStyles()
{
Count = (UInt32Value)1U
};
CellStyle cellStyle1 = new CellStyle()
{
Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U
};
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats()
{
Count = (UInt32Value)0U
};
TableStyles tableStyles1 = new TableStyles()
{
Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16"
};
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension()
{
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles slicerStyles1 = new DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles()
{
DefaultSlicerStyle = "SlicerStyleLight1"
};
stylesheetExtension1.Append(slicerStyles1);
//StylesheetExtension stylesheetExtension2 = new StylesheetExtension(){ Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}" };
//stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
//X15.TimelineStyles timelineStyles1 = new X15.TimelineStyles(){ DefaultTimelineStyle = "TimeSlicerStyleLight1" };
//stylesheetExtension2.Append(timelineStyles1);
stylesheetExtensionList1.Append(stylesheetExtension1);
//stylesheetExtensionList1.Append(stylesheetExtension2);
stylesheet1.Append(numberingFormats1);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);
workbookStyles.Stylesheet = stylesheet1;
}
/// <summary>
/// 样式
/// </summary>
/// <returns></returns>
private void GenerateWorkbookStylesPart(WorkbookStylesPart workbookStylesPart)
{
Stylesheet styleSheet = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac x16r2 xr"
}
};
styleSheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
styleSheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
styleSheet.AddNamespaceDeclaration("x16r2", "http://schemas.microsoft.com/office/spreadsheetml/2015/02/main");
styleSheet.AddNamespaceDeclaration("xr", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision");
// 字体
Fonts fonts = new Fonts(
// 0 默认
new Font(),
// 1 表头
new Font(
new Bold()
));
// 填充
Fills fills = new Fills(
new Fill(new PatternFill()
{
PatternType = PatternValues.None
}), // 0 默认
new Fill(new PatternFill()
{
PatternType = PatternValues.Gray125
}) // 0 默认
);
// 边框
Borders borders = new Borders(
// 0 默认，无边框
new Border(
new LeftBorder(),
new RightBorder(),
new TopBorder(),
new BottomBorder(),
new DiagonalBorder()),
new Border( // 1 四周边框
new LeftBorder(new Color()
{
Auto = true
})
{
Style = BorderStyleValues.Thin
},
new RightBorder(new Color()
{
Auto = true
})
{
Style = BorderStyleValues.Thin
},
new TopBorder(new Color()
{
Auto = true
})
{
Style = BorderStyleValues.Thin
},
new BottomBorder(new Color()
{
Auto = true
})
{
Style = BorderStyleValues.Thin
},
new DiagonalBorder())
);
// 表格总体样式格式
CellStyleFormats cellStyleFormats = new CellStyleFormats(
new CellFormat()
{
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0
}
);
// 表格格式
CellFormats cellFormats = new CellFormats(
new CellFormat()
{
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0
}, // 默认
new CellFormat()
{
NumberFormatId = 0, FontId = 1, FillId = 0, BorderId = 1, FormatId = 0, ApplyFont = true, ApplyBorder = true
}, // 表头
new CellFormat()
{
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 1, FormatId = 0, ApplyBorder = true
}, // 文本内容
new CellFormat()
{
NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 1, FormatId = 0, ApplyNumberFormat = true, ApplyBorder = true
} // 数字内容
);
// 表格样式
CellStyles cellStyles = new CellStyles(
new CellStyle()
{
FormatId = 0, BuiltinId = 0
}
);
StylesheetExtensionList stylesheetExtensionList = new StylesheetExtensionList();
StylesheetExtension stylesheetExtensionX15 = new StylesheetExtension()
{
Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}"
};
stylesheetExtensionX15.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
OpenXmlUnknownElement openXmlUnknownElementX15 = OpenXmlUnknownElement.CreateOpenXmlUnknownElement("<x15:timelineStyles defaultTimelineStyle=\"TimeSlicerStyleLight1\" xmlns:x15=\"http://schemas.microsoft.com/office/spreadsheetml/2010/11/main\" />");
stylesheetExtensionX15.Append(openXmlUnknownElementX15);
stylesheetExtensionList.Append(stylesheetExtensionX15);
styleSheet.Append(fonts);
styleSheet.Append(fills);
styleSheet.Append(borders);
styleSheet.Append(cellStyleFormats);
styleSheet.Append(cellFormats);
styleSheet.Append(cellStyles);
styleSheet.Append(stylesheetExtensionList);
workbookStylesPart.Stylesheet = styleSheet;
}
private static void WriteExcelFile(DataSet ds, SpreadsheetDocument spreadsheet)
{
// Create the Excel file contents. This function is used when creating an Excel file either writing
// to a file, or writing to a MemoryStream.
spreadsheet.AddWorkbookPart();
spreadsheet.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
// to prevent crashes in Excel 2010
spreadsheet.WorkbookPart.Workbook.Append(new BookViews(new WorkbookView()));
// If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
WorkbookStylesPart workbookStylesPart = spreadsheet.WorkbookPart.AddNewPart <WorkbookStylesPart>("rIdStyles");
Stylesheet stylesheet = new Stylesheet();
workbookStylesPart.Stylesheet = stylesheet;
// 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();
//KD Change
//bool p = false;
//if(p)
//{
//}
string worksheetName = dt.TableName;
WorksheetPart newWorksheetPart = spreadsheet.WorkbookPart.AddNewPart <WorksheetPart>();
newWorksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet();
// create sheet data
newWorksheetPart.Worksheet.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.SheetData());
// save worksheet
WriteDataTableToExcelWorksheet(dt, newWorksheetPart);
// To set column width in the worksheet. Created by Apt
int numberOfColumns = dt.Columns.Count;
for (int colInx = 1; colInx <= numberOfColumns; colInx++)
{
SetColumnWidth(newWorksheetPart.Worksheet, colInx, 30);
}
//End
newWorksheetPart.Worksheet.Save();
// create the worksheet to workbook relation
if (worksheetNumber == 1)
{
spreadsheet.WorkbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
}
spreadsheet.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>().AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = spreadsheet.WorkbookPart.GetIdOfPart(newWorksheetPart),
SheetId = (uint)worksheetNumber,
Name = worksheetName.Replace(':', '_').Replace('/', '_')///replacing special characters
});
worksheetNumber++;
}
spreadsheet.WorkbookPart.Workbook.Save();
}
/// <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 async Task <IActionResult> GeneratePayratesFile(UnitTutorsViewModel model)
{
// set file name
var fileName = "StaffPayrates_" + model.UnitCode + ".xlsx";
var filePath = Path.Combine(_hostingEnvironment.WebRootPath, fileName);
using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
{
// create woorkbookpart
WorkbookPart workbookPart = spreadSheetDocument.AddWorkbookPart();
spreadSheetDocument.WorkbookPart.Workbook = new Workbook();
spreadSheetDocument.WorkbookPart.Workbook.Sheets = new Sheets();
// styles part
WorkbookStylesPart stylesPart = spreadSheetDocument.WorkbookPart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = GenerateStyleSheet();
stylesPart.Stylesheet.Save();
//create worksheetPart Teaching events
WorksheetPart worksheetPartTeachingEvents = workbookPart.AddNewPart <WorksheetPart>();
SheetData sheetDataTeachingEvents = new SheetData();
worksheetPartTeachingEvents.Worksheet = new Worksheet();//sheetDataTeachingEvents);
// set column widths
Columns colsTeachingEvents = TeachingEventsColumns();
worksheetPartTeachingEvents.Worksheet.Append(colsTeachingEvents);
worksheetPartTeachingEvents.Worksheet.Append(sheetDataTeachingEvents);
// create merge cells for O1-T1
MergeCells mergeCells = new MergeCells();
mergeCells.Append(new MergeCell()
{
Reference = new StringValue("O1:T1")
});
worksheetPartTeachingEvents.Worksheet.Append(mergeCells);
// create sheet
Sheets sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>();
Sheet sheetTeachingEvents = new Sheet()
{
Id = spreadSheetDocument.WorkbookPart.GetIdOfPart(worksheetPartTeachingEvents),
SheetId = 1,
Name = "1. Teaching Events"
};
sheets.Append(sheetTeachingEvents);
// create worksheetPart New or Edited Staff Details
WorksheetPart worksheetPartNewStaff = workbookPart.AddNewPart <WorksheetPart>();
SheetData sheetDataNewStaff = new SheetData();
worksheetPartNewStaff.Worksheet = new Worksheet();
Columns colsNewStaff = NewStaffColumns();
worksheetPartNewStaff.Worksheet.Append(colsNewStaff);
worksheetPartNewStaff.Worksheet.Append(sheetDataNewStaff);
// create merge cells for O1-T1
MergeCells mergeCellsNewStaff = new MergeCells();
mergeCellsNewStaff.Append(new MergeCell()
{
Reference = new StringValue("A1:G1")
});
worksheetPartNewStaff.Worksheet.Append(mergeCellsNewStaff);
// create sheet
Sheet sheetNewStaff = new Sheet()
{
Id = spreadSheetDocument.WorkbookPart.GetIdOfPart(worksheetPartNewStaff),
SheetId = 2,
Name = "2. New or Edited Staff Details"
};
sheets.Append(sheetNewStaff);
// header row Teaching Events
Row headers = new Row();
// column A
headers.Append(StringCell("Subject Code", 3));
// column B
headers.Append(StringCell("Class Type", 3));
// column C (blank column), need to change format
headers.Append(EmptyCell(6));
// column D
headers.Append(StringCell("Day of week", 3));
// column E
headers.Append(StringCell("Start Time", 3));
// column F
headers.Append(StringCell("Duration", 3));
// column G (blank column)
headers.Append(EmptyCell(6));
// column H
headers.Append(StringCell("Staff Name", 3));
// column I (blank column)
headers.Append(EmptyCell(6));
// column J
headers.Append(StringCell("Weeks", 3));
// column K
headers.Append(StringCell("Pay rate", 3));// payrate code
// column L (blank column)
headers.Append(EmptyCell(6));
// column M
headers.Append(StringCell("Staff Status", 3));
// column N (blank column)
headers.Append(EmptyCell(6));
// column O
headers.Append(StringCell("FACULTY SUPPORT STAFF USE ONLY - PLEASE REFRAIN FROM AMENDING COLUMNS O-T", 7));
// add the headers to the sheet
sheetDataTeachingEvents.AppendChild(headers);
List <int> blackCol = new List <int> {
3, 7, 9, 12, 14
};
// Row 2 displays headers for faculty staff use
Row row2 = new Row();
for (int i = 0; i < 14; i++) // column A-N
{
if (blackCol.Contains(i + 1))
{
row2.Append(EmptyCell(6));
}
else
{
row2.Append(EmptyCell(3));
}
}
// column O
row2.Append(StringCell("Pay Rate", 1));
// column P
row2.Append(StringCell("No. of sessions", 1));
// column Q
row2.Append(StringCell("Hours", 1));
// column R
row2.Append(StringCell("Cost", 1));
// column S
row2.Append(StringCell("Cost inc. on-costs", 1));
// column T
row2.Append(StringCell("Notes/Comments", 1));
// add the row to the sheet
sheetDataTeachingEvents.AppendChild(row2);
// warning row for NewStaff sheet
Row warningRow = new Row();
warningRow.Append(StringCell("**ONLY COMPLETE THIS SECTION FOR NEW SESSIONAL STAFF WHO ARE NOT ON SWINBURNE'S PAYROLL", 13));
sheetDataNewStaff.AppendChild(warningRow);
// Header data for NewStaff sheet
Row headersNewStaff = new Row();
// Column A
headersNewStaff.Append(StringCell("Surname", 4));
// Column B
headersNewStaff.Append(StringCell("FirstName", 4));
// Column C
headersNewStaff.Append(StringCell("Email", 4));
// Column D
headersNewStaff.Append(StringCell("Address", 4));
// Column E
headersNewStaff.Append(StringCell("Suburb", 4));
// Column F
headersNewStaff.Append(StringCell("Post Code", 4));
// Column G
headersNewStaff.Append(StringCell("Home Phone", 4));
// Column H
headersNewStaff.Append(StringCell("Work Phone", 4));
// Column I
headersNewStaff.Append(StringCell("Mobile Phone", 4));
sheetDataNewStaff.AppendChild(headersNewStaff);
foreach (TutorPayrateViewModel tutor in model.Tutors.Values)
{
// get weeks that tutor is teaching
List <int> teachingWeeks = new List <int>();
foreach (var kv in tutor.Weeks)
{
if (kv.Value)
{
teachingWeeks.Add(kv.Key);
}
}
Row row = new Row();
// column A
row.Append(StringCell(model.UnitCode, 2));
// column B
row.Append(StringCell(tutor.ClassType, 2));
// column C (blank column)
row.Append(EmptyCell(6));
// column D
row.Append(StringCell(tutor.ClassDayOfWeek, 2));
// column E
row.Append(StringCell(tutor.ClassStartTime.ToString(@"hh\:mm"), 2));
// column F
row.Append(NumberCell(tutor.ClassDuration.TotalMinutes.ToString(), 2));
// column G (blank column)
row.Append(EmptyCell(6));
// column H
row.Append(StringCell(tutor.TutorFullName, 2));
// column I (blank column)
row.Append(EmptyCell(6));
// column J
row.Append(StringCell(string.Join(',', teachingWeeks.ToArray()), 2));
// column K
row.Append(StringCell(tutor.PayrateCode, 2));
// column L (blank column)
row.Append(EmptyCell(6));
// column M
row.Append(StringCell(tutor.StaffStatus, 2));
// column N (blank column)
row.Append(EmptyCell(6));
// column O
Payrate payrate = _context.Payrate.Where(c => c.Code == tutor.PayrateCode).FirstOrDefault();
row.Append(NumberCell(payrate.Rate.ToString(), 11));
// add formating to next 5 rows
for (int i = 0; i < 5; i++)
{
row.Append(EmptyCell(8));
}
sheetDataTeachingEvents.AppendChild(row);
// new staff
if (tutor.NewStaff)
{
Row rowNewStaff = new Row();
// Column A
rowNewStaff.Append(StringCell(tutor.TutorLastName));
// Column B
rowNewStaff.Append(StringCell(tutor.TutorFirstName));
// Column C
rowNewStaff.Append(StringCell(tutor.TutorEmail));
// Column D
rowNewStaff.Append(StringCell(tutor.TutorAddress));
// Column E
rowNewStaff.Append(StringCell(tutor.TutorSuburb));
// Column F
rowNewStaff.Append(StringCell(tutor.TutorPostCode));
// Column G
rowNewStaff.Append(EmptyCell());
// Column H
rowNewStaff.Append(EmptyCell());
// Column I
rowNewStaff.Append(StringCell(tutor.TutorMobileNumber));
sheetDataNewStaff.AppendChild(rowNewStaff);
}
}
// empty row between staff details and total stuff
Row emptyRow = new Row();
sheetDataTeachingEvents.AppendChild(emptyRow);
Row TotalRow = new Row();
for (int i = 0; i < 14; i++) // column A-N
{
TotalRow.Append(EmptyCell());
}
// column O
TotalRow.Append(StringCell("TOTAL", 9));
// column P
Cell noSessions = NumberCell("0.00", 10);
int totalStaff = model.Tutors.Count;
CellFormula noSessionsFormula = new CellFormula();
noSessionsFormula.Text = "SUM(P3:P" + (3 + totalStaff - 1) + ")";
noSessions.CellFormula = noSessionsFormula;
TotalRow.Append(noSessions);
// column Q
Cell hours = NumberCell("0.00", 10);
CellFormula hoursFormula = new CellFormula();
hoursFormula.Text = "SUM(Q3:Q" + (3 + totalStaff - 1) + ")";
hours.CellFormula = hoursFormula;
TotalRow.Append(hours);
// column R
Cell cost = NumberCell("0.00", 12);
CellFormula costFormula = new CellFormula();
costFormula.Text = "SUM(R3:R" + (3 + totalStaff - 1) + ")";
cost.CellFormula = costFormula;
TotalRow.Append(cost);
// column S
Cell costInc = NumberCell("0.00", 12);
CellFormula costIncFormula = new CellFormula();
costIncFormula.Text = "SUM(S3:S" + (3 + totalStaff - 1) + ")";
costInc.CellFormula = costIncFormula;
TotalRow.Append(costInc);
sheetDataTeachingEvents.AppendChild(TotalRow);
}
// Download the created file
if (fileName != null)
{
var memory = new MemoryStream();
using (var stream = new FileStream(filePath, FileMode.Open))
{
await stream.CopyToAsync(memory);
}
memory.Position = 0;
System.IO.File.Delete(filePath);
return(File(memory, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName));
}
return(RedirectToAction(nameof(Index)));
}
public static void CreateExcelFile(string outPutFileDirectory)
{
List <Country> data = DataGenerator.GetCountries().Result;
var dateTime = DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss");
var fileName = Path.Combine(outPutFileDirectory, $"output_{dateTime}.xlsx");
// Create a spreadsheet document using the file name.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart with a workbook object to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart with a worksheet to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
// Create a Sheets object and append it to workbook.
Sheets sheets = workbookpart.Workbook.AppendChild(new Sheets());
// Create a Sheet object and append it to Sheets.
Sheet sheet = new Sheet()
{
Id = workbookpart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet-1"
};
sheets.Append(sheet);
//Create Column object to set properties like column width
Columns columns = new Columns();
columns.Append(new Column()
{
Min = 1, Max = 1, Width = 15, CustomWidth = true
});
columns.Append(new Column()
{
Min = 2, Max = 3, Width = 40, CustomWidth = true
});
worksheetPart.Worksheet.Append(columns);
//Create a StyleSheet object to set properties like fonts, fills, bordres
WorkbookStylesPart stylesPart = spreadsheetDocument.WorkbookPart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = GenerateStyleSheet();
stylesPart.Stylesheet.Save();
// Get the sheetData cell table.
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
GenerateSheetData(sheetData, data);
// Create CalculationProperties to calculate formulas automaticly
SetAutoCalculation(workbookpart);
// Close the document.
spreadsheetDocument.Close();
}
// Generates content of workbookStylesPart1.
// (borders needed for user to resize tables). Other style content skipped.
private static void GenerateWorkbookStylesContent(WorkbookStylesPart workbookStylesPart1)
{
Stylesheet stylesheet1 = new Stylesheet();
// if your data includes a table, this renders the blue borders to make it easier to see.
DifferentialFormats differentialFormats1 = new DifferentialFormats()
{
Count = (UInt32Value)2U
};
DifferentialFormat differentialFormat1 = new DifferentialFormat();
NumberingFormat numberingFormat1 = new NumberingFormat()
{
NumberFormatId = (UInt32Value)1U, FormatCode = "0"
};
differentialFormat1.Append(numberingFormat1);
DifferentialFormat differentialFormat2 = new DifferentialFormat();
Border border3 = new Border()
{
DiagonalUp = false, DiagonalDown = false
};
LeftBorder leftBorder3 = new LeftBorder()
{
Style = BorderStyleValues.Thin
};
Color color5 = new Color()
{
Indexed = (UInt32Value)12U
};
leftBorder3.Append(color5);
RightBorder rightBorder3 = new RightBorder()
{
Style = BorderStyleValues.Thin
};
Color color6 = new Color()
{
Indexed = (UInt32Value)12U
};
rightBorder3.Append(color6);
TopBorder topBorder3 = new TopBorder()
{
Style = BorderStyleValues.Thin
};
Color color7 = new Color()
{
Indexed = (UInt32Value)12U
};
topBorder3.Append(color7);
BottomBorder bottomBorder3 = new BottomBorder()
{
Style = BorderStyleValues.Thin
};
Color color8 = new Color()
{
Indexed = (UInt32Value)12U
};
bottomBorder3.Append(color8);
border3.Append(leftBorder3);
border3.Append(rightBorder3);
border3.Append(topBorder3);
border3.Append(bottomBorder3);
differentialFormat2.Append(border3);
differentialFormats1.Append(differentialFormat1);
differentialFormats1.Append(differentialFormat2);
stylesheet1.Append(differentialFormats1); // border used for table
workbookStylesPart1.Stylesheet = stylesheet1;
}
// Create an excel file with a sheet named [sheetName] and current data the of recordset
public static bool SaveExcelFile(this RSFrame rs, string filePath, string sheetName = "Sheet 1", bool showColumnNames = true)
{
// Initialize vars
int _row = 1;
int _col = 1;
// Create an empty excel file and get some objects from it
SpreadsheetDocument _excelDoc = Create(filePath, sheetName);
WorkbookPart _wbPart = _excelDoc.WorkbookPart;
Sheet _sheet = _wbPart.Workbook.Descendants <Sheet>().Where((s) => s.Name == sheetName).FirstOrDefault();
Worksheet _ws = ((WorksheetPart)(_wbPart.GetPartById(_sheet.Id))).Worksheet;
SharedStringTablePart _stringTablePart = _wbPart.GetPartsOfType <SharedStringTablePart>().FirstOrDefault();
SheetData _sheetData = _ws.GetFirstChild <SheetData>();
Row _excelRow;
_excelDoc.WorkbookPart.AddNewPart <WorkbookStylesPart>();
WorkbookStylesPart stylesPart = _excelDoc.WorkbookPart.WorkbookStylesPart;
stylesPart.Stylesheet = new Stylesheet();
stylesPart.Stylesheet.Fonts = new Fonts();
Font _fontBody = new Font(new FontSize()
{
Val = 12
}, new Color()
{
Rgb = new HexBinaryValue()
{
Value = "000000"
}
}, new FontName()
{
Val = "Tahoma"
});
stylesPart.Stylesheet.Fonts.Append(_fontBody);
UInt32Value fontBodyId = Convert.ToUInt32(stylesPart.Stylesheet.Fonts.ChildElements.Count - 1);
/* stylesPart.Stylesheet.CellFormats = new CellFormats();
* stylesPart.Stylesheet.CellFormats.Append(new CellFormat() { FontId = fontBodyId, FillId = 0, BorderId = 0, ApplyFont = true });
*/
//UInt32Value fontBodyId = 0;
if (!showColumnNames)
{
Font _fontTitle = new Font(new Bold(), new FontSize()
{
Val = 12
}, new Color()
{
Rgb = new HexBinaryValue()
{
Value = "FF0000"
}
}, new FontName()
{
Val = "Tahoma"
});
stylesPart.Stylesheet.Fonts.InsertBefore(_fontTitle, null);
UInt32Value fontTitleId = Convert.ToUInt32(stylesPart.Stylesheet.Fonts.ChildElements.Count - 1);
// stylesPart.Stylesheet.CellFormats.Append(new CellFormat() { FontId = fontTitleId, FillId = 0, BorderId = 0, ApplyFont = true });
_excelRow = new Row();
_excelRow.RowIndex = (uint)_row;
_sheetData.Append(_excelRow);
rs.Fields.ToList().ForEach(_field =>
{
AddCell(_sheetData, _stringTablePart, _ws, _wbPart, _excelRow, _col, _field.ToString(), fontTitleId, true);
_col++;
});
_row++;
}
// Go over the recordset to fill the excel cells
rs.ToList().ForEach(_rowdata =>
{
_col = 1;
_excelRow = new Row();
_excelRow.RowIndex = (uint)_row;
_sheetData.Append(_excelRow);
_rowdata.ItemArray.ToList().ForEach(_columndata =>
{
AddCell(_sheetData, _stringTablePart, _ws, _wbPart, _excelRow, _col, _columndata.ToString(), fontBodyId, true);
_col++;
});
_row++;
});
//// Save and close the document
_excelDoc.WorkbookPart.Workbook.Save();
_excelDoc.Close();
return(true);
}
/// <summary>
/// Create a new excel file, insert data from SQL-Server from a list.
/// along with formating the first row which is field names
/// </summary>
/// <param name="pFileName">Path and file name to create</param>
/// <param name="pSheetName">Work sheet data will be placed</param>
/// <param name="pList">Person list</param>
public void CreateExcelDocPopulateWithPeople(string pFileName, string pSheetName, List <Person> pList)
{
mHasException = false;
try
{
using (var document = SpreadsheetDocument.Create(pFileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart wbp = document.AddWorkbookPart();
wbp.Workbook = new Workbook();
var wsp = wbp.AddNewPart <WorksheetPart>();
wsp.Worksheet = new Worksheet();
var sheets = wbp.Workbook.AppendChild(new Sheets());
var sheet = new Sheet()
{
Id = wbp.GetIdOfPart(wsp),
SheetId = 1,
Name = "People"
};
// ReSharper disable once PossiblyMistakenUseOfParamsMethod
sheets?.Append(sheet);
wbp.Workbook.Save();
WorkbookStylesPart stylePart = wbp.AddNewPart <WorkbookStylesPart>();
stylePart.Stylesheet = CreateStylesheet();
stylePart.Stylesheet.Save();
var sheetData = wsp.Worksheet.AppendChild(new SheetData());
var headerRow = sheetData.AppendChild(new Row());
headerRow.AppendChild(new Cell()
{
CellValue = new CellValue("Id"),
DataType = CellValues.String,
StyleIndex = 2
});
headerRow.AppendChild(new Cell()
{
CellValue = new CellValue("First Name"),
DataType = CellValues.String,
StyleIndex = 2
});
headerRow.AppendChild(new Cell()
{
CellValue = new CellValue("Last Name"),
DataType = CellValues.String,
StyleIndex = 2
});
headerRow.AppendChild(new Cell()
{
CellValue = new CellValue("Gender"),
DataType = CellValues.String,
StyleIndex = 2
});
headerRow.AppendChild(new Cell()
{
CellValue = new CellValue("Birthday"),
DataType = CellValues.String,
StyleIndex = 2
});
// insert people data
foreach (var person in pList)
{
var row = new Row();
row.Append(
ConstructCell(person.Id.ToString(), CellValues.Number),
ConstructCell(person.FirstName, CellValues.String),
ConstructCell(person.LastName, CellValues.String),
ConstructCell(person.Role, CellValues.String),
ConstructCell(person.BirthDay.ToString("MM/dd/yyyy"), CellValues.String));
sheetData.AppendChild(row);
}
wsp.Worksheet.Save();
}
}
catch (Exception ex)
{
mHasException = true;
mLastException = ex;
}
}
private static void GenerateWorkbookStylesPartContent(WorkbookStylesPart workbookStylesPart1)
{
Stylesheet stylesheet1 = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac x16r2 xr"
}
};
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
stylesheet1.AddNamespaceDeclaration("x16r2", "http://schemas.microsoft.com/office/spreadsheetml/2015/02/main");
stylesheet1.AddNamespaceDeclaration("xr", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision");
Fonts fonts1 = new Fonts()
{
Count = (UInt32Value)1U, KnownFonts = true
};
Font font1 = new Font();
FontSize fontSize1 = new FontSize()
{
Val = 11D
};
Color color1 = new Color()
{
Theme = (UInt32Value)1U
};
FontName fontName1 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme1 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontScheme1);
fonts1.Append(font1);
Fills fills1 = new Fills()
{
Count = (UInt32Value)2U
};
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill()
{
PatternType = PatternValues.None
};
fill1.Append(patternFill1);
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill()
{
PatternType = PatternValues.Gray125
};
fill2.Append(patternFill2);
fills1.Append(fill1);
fills1.Append(fill2);
Borders borders1 = new Borders()
{
Count = (UInt32Value)1U
};
Border border1 = new Border();
LeftBorder leftBorder1 = new LeftBorder();
RightBorder rightBorder1 = new RightBorder();
TopBorder topBorder1 = new TopBorder();
BottomBorder bottomBorder1 = new BottomBorder();
DiagonalBorder diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
borders1.Append(border1);
CellStyleFormats cellStyleFormats1 = new CellStyleFormats()
{
Count = (UInt32Value)1U
};
CellFormat cellFormat1 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U
};
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats()
{
Count = (UInt32Value)2U
};
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U
};
CellFormat cellFormat3 = new CellFormat()
{
NumberFormatId = (UInt32Value)14U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true
};
cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
CellStyles cellStyles1 = new CellStyles()
{
Count = (UInt32Value)1U
};
CellStyle cellStyle1 = new CellStyle()
{
Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U
};
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats()
{
Count = (UInt32Value)0U
};
TableStyles tableStyles1 = new TableStyles()
{
Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16"
};
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension()
{
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
StylesheetExtension stylesheetExtension2 = new StylesheetExtension()
{
Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}"
};
stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
OpenXmlUnknownElement openXmlUnknownElement4 = OpenXmlUnknownElement.CreateOpenXmlUnknownElement("<x15:timelineStyles defaultTimelineStyle=\"TimeSlicerStyleLight1\" xmlns:x15=\"http://schemas.microsoft.com/office/spreadsheetml/2010/11/main\" />");
stylesheetExtension2.Append(openXmlUnknownElement4);
stylesheetExtensionList1.Append(stylesheetExtension1);
stylesheetExtensionList1.Append(stylesheetExtension2);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);
workbookStylesPart1.Stylesheet = stylesheet1;
}
public static WorkbookStylesPart addStylesheet(WorkbookPart mainpart)
{
//Add a new workbooksylepart for the style of sheets
WorkbookStylesPart stylesPart = mainpart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = new Stylesheet();
// blank font list
stylesPart.Stylesheet.Fonts = new DocumentFormat.OpenXml.Spreadsheet.Fonts();
stylesPart.Stylesheet.Fonts.Count = 1;
stylesPart.Stylesheet.Fonts.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Font());
// create fills
stylesPart.Stylesheet.Fills = new Fills();
// create a solid pattern
var solid = new PatternFill()
{
PatternType = PatternValues.Solid
};
solid.ForegroundColor = new ForegroundColor {
Rgb = HexBinaryValue.FromString("FFFF0000")
};
solid.BackgroundColor = new BackgroundColor {
Indexed = 64
};
stylesPart.Stylesheet.Fills.AppendChild(new Fill {
PatternFill = new PatternFill {
PatternType = PatternValues.None
}
}); // required, reserved by Excel
stylesPart.Stylesheet.Fills.AppendChild(new Fill {
PatternFill = new PatternFill {
PatternType = PatternValues.Gray125
}
}); // required, reserved by Excel
stylesPart.Stylesheet.Fills.AppendChild(new Fill {
PatternFill = solid
});
stylesPart.Stylesheet.Fills.Count = 3;
// blank border list
stylesPart.Stylesheet.Borders = new Borders();
stylesPart.Stylesheet.Borders.Count = 1;
stylesPart.Stylesheet.Borders.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Border());
// blank cell format list
stylesPart.Stylesheet.CellStyleFormats = new CellStyleFormats();
stylesPart.Stylesheet.CellStyleFormats.Count = 1;
stylesPart.Stylesheet.CellStyleFormats.AppendChild(new CellFormat());
// cell format list
stylesPart.Stylesheet.CellFormats = new CellFormats();
// empty one for index 0, seems to be required
stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat());
// cell format references style format 0, font 0, border 0, fill 2 and applies the fill
stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat {
FormatId = 0, FontId = 0, BorderId = 0, FillId = 2, ApplyFill = true
}).AppendChild(new Alignment {
Horizontal = HorizontalAlignmentValues.Center
});
stylesPart.Stylesheet.CellFormats.Count = 2;
stylesPart.Stylesheet.Save();
return(stylesPart);
}
private static void GenerateWorkbookStylesPartContent(WorkbookStylesPart workbookStylesPart1)
{
Stylesheet stylesheet1 = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac"
}
};
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
Fonts fonts1 = new Fonts()
{
Count = 2U, KnownFonts = true
};
Font font1 = new Font();
FontSize fontSize1 = new FontSize()
{
Val = 11D
};
Color color1 = new Color()
{
Theme = 1U
};
FontName fontName1 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering1 = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme1 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontScheme1);
Font font2 = new Font();
Bold bold1 = new Bold();
FontSize fontSize2 = new FontSize()
{
Val = 11D
};
Color color2 = new Color()
{
Theme = 1U
};
FontName fontName2 = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering2 = new FontFamilyNumbering()
{
Val = 2
};
FontScheme fontScheme2 = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font2.Append(bold1);
font2.Append(fontSize2);
font2.Append(color2);
font2.Append(fontName2);
font2.Append(fontFamilyNumbering2);
font2.Append(fontScheme2);
fonts1.Append(font1);
fonts1.Append(font2);
Fills fills1 = new Fills()
{
Count = 2U
};
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill()
{
PatternType = PatternValues.None
};
fill1.Append(patternFill1);
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill()
{
PatternType = PatternValues.Gray125
};
fill2.Append(patternFill2);
fills1.Append(fill1);
fills1.Append(fill2);
Borders borders1 = new Borders()
{
Count = 2U
};
Border border1 = new Border();
LeftBorder leftBorder1 = new LeftBorder();
RightBorder rightBorder1 = new RightBorder();
TopBorder topBorder1 = new TopBorder();
BottomBorder bottomBorder1 = new BottomBorder();
DiagonalBorder diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
Border border2 = new Border();
LeftBorder leftBorder2 = new LeftBorder()
{
Style = BorderStyleValues.Thin
};
Color color3 = new Color()
{
Indexed = 64U
};
leftBorder2.Append(color3);
RightBorder rightBorder2 = new RightBorder()
{
Style = BorderStyleValues.Thin
};
Color color4 = new Color()
{
Indexed = 64U
};
rightBorder2.Append(color4);
TopBorder topBorder2 = new TopBorder()
{
Style = BorderStyleValues.Thin
};
Color color5 = new Color()
{
Indexed = 64U
};
topBorder2.Append(color5);
BottomBorder bottomBorder2 = new BottomBorder()
{
Style = BorderStyleValues.Thin
};
Color color6 = new Color()
{
Indexed = 64U
};
bottomBorder2.Append(color6);
DiagonalBorder diagonalBorder2 = new DiagonalBorder();
border2.Append(leftBorder2);
border2.Append(rightBorder2);
border2.Append(topBorder2);
border2.Append(bottomBorder2);
border2.Append(diagonalBorder2);
borders1.Append(border1);
borders1.Append(border2);
CellStyleFormats cellStyleFormats1 = new CellStyleFormats()
{
Count = 1U
};
CellFormat cellFormat1 = new CellFormat()
{
NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U
};
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new CellFormats()
{
Count = 3U
};
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 0U, FormatId = 0U
};
CellFormat cellFormat3 = new CellFormat()
{
NumberFormatId = 0U, FontId = 0U, FillId = 0U, BorderId = 1U, FormatId = 0U, ApplyBorder = true
};
CellFormat cellFormat4 = new CellFormat()
{
NumberFormatId = 0U, FontId = 1U, FillId = 0U, BorderId = 1U, FormatId = 0U, ApplyFont = true, ApplyBorder = true
};
cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
cellFormats1.Append(cellFormat4);
CellStyles cellStyles1 = new CellStyles()
{
Count = 1U
};
CellStyle cellStyle1 = new CellStyle()
{
Name = "Normal", FormatId = 0U, BuiltinId = 0U
};
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new DifferentialFormats()
{
Count = 0U
};
TableStyles tableStyles1 = new TableStyles()
{
Count = 0U, DefaultTableStyle = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleLight16"
};
StylesheetExtensionList stylesheetExtensionList1 = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension()
{
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtension1.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
X14.SlicerStyles slicerStyles1 = new X14.SlicerStyles()
{
DefaultSlicerStyle = "SlicerStyleLight1"
};
stylesheetExtension1.Append(slicerStyles1);
StylesheetExtension stylesheetExtension2 = new StylesheetExtension()
{
Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}"
};
stylesheetExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.TimelineStyles timelineStyles1 = new X15.TimelineStyles()
{
DefaultTimelineStyle = "TimeSlicerStyleLight1"
};
stylesheetExtension2.Append(timelineStyles1);
stylesheetExtensionList1.Append(stylesheetExtension1);
stylesheetExtensionList1.Append(stylesheetExtension2);
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
stylesheet1.Append(stylesheetExtensionList1);
workbookStylesPart1.Stylesheet = stylesheet1;
}
/// <summary>
/// Создает стилевую составляющую документа
/// </summary>
/// <param name="workbookStylesPart">Стилевая часть книги</param>
private void GenerateStyles(WorkbookStylesPart workbookStylesPart)
{
Stylesheet stylesheet = new Stylesheet()
{
MCAttributes = new MarkupCompatibilityAttributes()
{
Ignorable = "x14ac"
}
};
stylesheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
#region Fonts
Fonts fonts = new Fonts()
{
Count = 1U,
KnownFonts = true
};
Font font = new Font();
FontSize fontSize = new FontSize()
{
Val = 11D
};
Color color1 = new Color()
{
Theme = 1U
};
FontName fontName = new FontName()
{
Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering = new FontFamilyNumbering()
{
Val = 2
};
FontCharSet fontCharSet = new FontCharSet()
{
Val = 204
};
FontScheme fontScheme = new FontScheme()
{
Val = FontSchemeValues.Minor
};
font.Append(fontSize);
font.Append(color1);
font.Append(fontName);
font.Append(fontFamilyNumbering);
font.Append(fontCharSet);
font.Append(fontScheme);
fonts.Append(font);
#endregion
#region Fills
Fills fills = new Fills()
{
Count = 2U
};
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill()
{
PatternType = PatternValues.None
};
fill1.Append(patternFill1);
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill()
{
PatternType = PatternValues.Gray125
};
fill2.Append(patternFill2);
fills.Append(fill1);
fills.Append(fill2);
#endregion
#region Borders
Borders borders = new Borders()
{
Count = 1U
};
Border border1 = new Border();
LeftBorder leftBorder1 = new LeftBorder();
RightBorder rightBorder1 = new RightBorder();
TopBorder topBorder1 = new TopBorder();
BottomBorder bottomBorder = new BottomBorder();
DiagonalBorder diagonalBorder1 = new DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder);
border1.Append(diagonalBorder1);
borders.Append(border1);
#endregion
#region Cell Styles And Formats
CellStyleFormats cellStyleFormats = new CellStyleFormats()
{
Count = 1U
};
CellFormat cellFormat1 = new CellFormat()
{
NumberFormatId = 0U,
FontId = 0U,
FillId = 0U,
BorderId = 0U
};
cellStyleFormats.Append(cellFormat1);
CellFormats cellFormats = new CellFormats()
{
Count = 1U
};
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = 0U,
FontId = 0U,
FillId = 0U,
BorderId = 0U,
FormatId = 0U
};
cellFormats.Append(cellFormat2);
CellStyles cellStyles = new CellStyles()
{
Count = 1U
};
CellStyle cellStyle = new CellStyle()
{
Name = "Обычный",
FormatId = 0U,
BuiltinId = 0U
};
cellStyles.Append(cellStyle);
#endregion
DifferentialFormats differentialFormats = new DifferentialFormats()
{
Count = 0U
};
TableStyles tableStyles = new TableStyles()
{
Count = 0U,
DefaultTableStyle = "TableStyleMedium2",
DefaultPivotStyle = "PivotStyleLight16"
};
#region Stylesheet Extensions
StylesheetExtensionList stylesheetExtensionList = new StylesheetExtensionList();
StylesheetExtension stylesheetExtension1 = new StylesheetExtension()
{
Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
};
stylesheetExtension1.AddNamespaceDeclaration("x14",
"http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
X14.SlicerStyles slicerStyles = new X14.SlicerStyles()
{
DefaultSlicerStyle = "SlicerStyleLight1"
};
stylesheetExtension1.Append(slicerStyles);
StylesheetExtension stylesheetExtension2 = new StylesheetExtension()
{
Uri = "{9260A510-F301-46a8-8635-F512D64BE5F5}"
};
stylesheetExtension2.AddNamespaceDeclaration("x15",
"http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
OpenXmlUnknownElement unknownElement =
OpenXmlUnknownElement.CreateOpenXmlUnknownElement(
"<x15:timelineStyles defaultTimelineStyle=\"TimeSlicerStyleLight1\" xmlns:x15=\"http://schemas.microsoft.com/office/spreadsheetml/2010/11/main\" />");
stylesheetExtension2.Append(unknownElement);
stylesheetExtensionList.Append(stylesheetExtension1);
stylesheetExtensionList.Append(stylesheetExtension2);
#endregion
stylesheet.Append(fonts);
stylesheet.Append(fills);
stylesheet.Append(borders);
stylesheet.Append(cellStyleFormats);
stylesheet.Append(cellFormats);
stylesheet.Append(cellStyles);
stylesheet.Append(differentialFormats);
stylesheet.Append(tableStyles);
stylesheet.Append(stylesheetExtensionList);
workbookStylesPart.Stylesheet = stylesheet;
}
public WorkbookManager(string wkbookFile)
{
OriginalFile = wkbookFile;
Document = SpreadsheetDocument.Open(wkbookFile, true);
WorkbookPart workbookPart = Document.WorkbookPart;
var sheetId = (workbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>()).FirstOrDefault().Id; // Antar at det alltid er det første arket vi skal prosessere
FormResultsWkSheet = ((WorksheetPart)workbookPart.GetPartById(sheetId)).Worksheet;
// Sett opp styles. Hent ut exi styleparts, sjekk om vi har styles allerede/legg til og reg idx
WorkbookStylesPart wbpst = workbookPart.GetPartsOfType <WorkbookStylesPart>().Single();
int fills;
if (wbpst.Stylesheet.Fills.Count <= 2)
{ // Det finnes bare to defaultstyles, har vi mer har vi lagt til allerede.
wbpst.Stylesheet.Fills.Append(
new Fill(new PatternFill(new ForegroundColor()
{
Rgb = new HexBinaryValue()
{
Value = "FF00FF00"
}
})
{
PatternType = PatternValues.Solid
}), //4; grønn
new Fill(new PatternFill(new ForegroundColor()
{
Rgb = new HexBinaryValue()
{
Value = "FFFFFF00"
}
})
{
PatternType = PatternValues.Solid
}), //3; gul
new Fill(new PatternFill(new ForegroundColor()
{
Rgb = new HexBinaryValue()
{
Value = "FFFF0000"
}
})
{
PatternType = PatternValues.Solid
}) //2; rød
);
fills = wbpst.Stylesheet.Fills.Count();
CellFormat cfGreen = new CellFormat()
{
FillId = Convert.ToUInt32(fills - 3)
};
CellFormat cfYellow = new CellFormat()
{
FillId = Convert.ToUInt32(fills - 2)
};
CellFormat cfRed = new CellFormat()
{
FillId = Convert.ToUInt32(fills - 1)
};
wbpst.Stylesheet.CellFormats.Append(cfGreen, cfYellow, cfRed);
}
wbpst.Stylesheet.Save();
fills = Convert.ToInt32((UInt32)wbpst.Stylesheet.CellFormats.ChildElements.Count);
REDSTYLEIDX = fills - 1;
YELLOWSTYLEIDX = fills - 2;
GREENSTYLEIDX = fills - 3;
}
public ExcelDocument(string filename)
{
if (File.Exists(filename))
{
document = SpreadsheetDocument.Open(filename, true);
if (document.WorkbookPart.GetPartsOfType <WorkbookStylesPart>().Count() == 0)
{
WorkbookStylesPart stylespart = document.WorkbookPart.AddNewPart <WorkbookStylesPart>();
stylespart.Stylesheet = GetStylesheet();
DateTypeIndex = 1;
stylesheet = stylespart.Stylesheet;
}
else
{
WorkbookStylesPart stylespart = document.WorkbookPart.GetPartsOfType <WorkbookStylesPart>().First();
IEnumerable <CellFormat> cellformats = stylespart.Stylesheet.CellFormats.Elements <CellFormat>();
if (cellformats.Where(f => f.NumberFormatId == 14).Count() > 0)
{
uint dateformatindex = 0;
foreach (CellFormat format in cellformats)
{
if (format.NumberFormatId == 14)
{
break;
}
else
{
dateformatindex++;
}
}
DateTypeIndex = dateformatindex;
}
else
{
stylespart.Stylesheet.CellFormats.Append(new CellFormat()
{
BorderId = 0,
FillId = 0,
FontId = 0,
NumberFormatId = 14,
FormatId = 0,
ApplyNumberFormat = true
});
stylespart.Stylesheet.CellFormats.Count = (uint)stylespart.Stylesheet.CellFormats.ChildElements.Count;
DateTypeIndex = stylespart.Stylesheet.CellFormats.Count - 1;
}
stylesheet = stylespart.Stylesheet;
}
}
else
{
document = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook);
WorkbookPart workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
WorkbookStylesPart stylespart = workbookpart.AddNewPart <WorkbookStylesPart>();
stylespart.Stylesheet = GetStylesheet();
DateTypeIndex = 1;
stylesheet = stylespart.Stylesheet;
}
Sheets = new ExcelSheetCollection(this);
}
