DocumentFormat.OpenXml Make Excel
//ExcelHelper.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
namespace Admin
{
public class ExcelHelper
{
/// <summary>
/// Write excel file of a list of object as T
/// Assume that maximum of 24 columns
/// </summary>
/// <typeparam name="T">Object type to pass in</typeparam>
/// <param name="fileName">Full path of the file name of excel spreadsheet</param>
/// <param name="objects">list of the object type</param>
/// <param name="sheetName">Sheet names of Excel File</param>
/// <param name="headerNames">Header names of the object</param>
public void Create<T>(
string fileName,
List<T> objects,
string sheetName,
List<string> headerNames)
{
//Open the copied template workbook.
using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = myWorkbook.AddWorkbookPart();
var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
// Create Styles and Insert into Workbook
var stylesPart = myWorkbook.WorkbookPart.AddNewPart<WorkbookStylesPart>();
Stylesheet styles = new CustomStylesheet();
styles.Save(stylesPart);
string relId = workbookPart.GetIdOfPart(worksheetPart);
var workbook = new Workbook();
var fileVersion = new FileVersion { ApplicationName = "Microsoft Office Excel" };
var worksheet = new Worksheet();
int numCols = headerNames.Count;
var columns = new Columns();
for (int col = 0; col < numCols; col++)
{
int width = headerNames[col].Length + 5;
Column c = new CustomColumn((UInt32)col + 1, (UInt32)numCols + 1, width);
columns.Append(c);
}
worksheet.Append(columns);
var sheets = new Sheets();
var sheet = new Sheet { Name = sheetName, SheetId = 1, Id = relId };
sheets.Append(sheet);
workbook.Append(fileVersion);
workbook.Append(sheets);
SheetData sheetData = CreateSheetData(objects, headerNames);
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
myWorkbook.WorkbookPart.Workbook = workbook;
myWorkbook.WorkbookPart.Workbook.Save();
myWorkbook.Close();
}
}
/// <summary>
///
/// </summary>
/// <typeparam name="T">Object type to pass in</typeparam>
/// <param name="objects">list of the object type</param>
/// <param name="headerNames">Header names of the object</param>
/// <returns></returns>
private static SheetData CreateSheetData<T>(List<T> objects, List<string> headerNames)
{
var sheetData = new SheetData();
if (objects != null)
{
//Get fields names of object
List<string> fields = GetPropertyInfo<T>();
//Get a list of A to Z
var az = new List<Char>(Enumerable.Range('A', 'Z' - 'A' + 1).Select(i => (Char)i).ToArray());
//A to E number of columns
List<Char> headers = az.GetRange(0, fields.Count);
int numRows = objects.Count;
int numCols = fields.Count;
var header = new Row();
int index = 1;
header.RowIndex = (uint)index;
for (int col = 0; col < numCols; col++)
{
var c = new HeaderCell(headers[col].ToString(), headerNames[col], index);
header.Append(c);
}
sheetData.Append(header);
for (int i = 0; i < numRows; i++)
{
index++;
var obj1 = objects[i];
var r = new Row { RowIndex = (uint)index };
for (int col = 0; col < numCols; col++)
{
string fieldName = fields[col];
PropertyInfo myf = obj1.GetType().GetProperty(fieldName);
if (myf != null)
{
object obj = myf.GetValue(obj1, null);
if (obj != null)
{
if (obj.GetType() == typeof(string))
{
var c = new TextCell(headers[col].ToString(), obj.ToString(), index);
r.Append(c);
}
else if (obj.GetType() == typeof(bool))
{
string value = (bool)obj ? "Yes" : "No";
var c = new TextCell(headers[col].ToString(), value, index);
r.Append(c);
}
else if (obj.GetType() == typeof(DateTime))
{
var c = new DateCell(headers[col].ToString(), (DateTime)obj, index);
r.Append(c);
}
else if (obj.GetType() == typeof(decimal) || obj.GetType() == typeof(double))
{
var c = new FormatedNumberCell(headers[col].ToString(), obj.ToString(), index);
r.Append(c);
}
else
{
long value;
if (long.TryParse(obj.ToString(), out value))
{
var c = new NumberCell(headers[col].ToString(), obj.ToString(), index);
r.Append(c);
}
else
{
var c = new TextCell(headers[col].ToString(), obj.ToString(), index);
r.Append(c);
}
}
}
}
}
sheetData.Append(r);
}
index++;
//Row total = new Row();
//total.RowIndex = (uint)index;
//for (int col = 0; col < numCols; col++)
//{
// var obj1 = objects[0];
// string fieldName = fields[col];
// PropertyInfo myf = obj1.GetType().GetProperty(fieldName);
// if (myf != null)
// {
// object obj = myf.GetValue(obj1, null);
// if (obj != null)
// {
// if (col == 0)
// {
// var c = new TextCell(headers[col].ToString(), "Total", index);
// c.StyleIndex = 10;
// total.Append(c);
// }
// else if (obj.GetType() == typeof(decimal) || obj.GetType() == typeof(double))
// {
// string headerCol = headers[col].ToString();
// string firstRow = headerCol + "2";
// string lastRow = headerCol + (numRows + 1);
// string formula = "=SUM(" + firstRow + " : " + lastRow + ")";
// //Console.WriteLine(formula);
// var c = new FomulaCell(headers[col].ToString(), formula, index);
// c.StyleIndex = 9;
// total.Append(c);
// }
// else
// {
// var c = new TextCell(headers[col].ToString(), string.Empty, index);
// c.StyleIndex = 10;
// total.Append(c);
// }
// }
// }
//}
//sheetData.Append(total);
}
return sheetData;
}
private static List<string> GetPropertyInfo<T>()
{
PropertyInfo[] propertyInfos = typeof(T).GetProperties();
// write property names
return propertyInfos.Select(propertyInfo => propertyInfo.Name).ToList();
}
}
public class CustomColumn : Column
{
public CustomColumn(UInt32 startColumnIndex, UInt32 endColumnIndex, double columnWidth)
{
this.Min = startColumnIndex;
this.Max = endColumnIndex;
this.Width = columnWidth;
this.CustomWidth = true;
}
}
public class CustomStylesheet : Stylesheet
{
public CustomStylesheet()
{
var fonts = new Fonts();
var font = new DocumentFormat.OpenXml.Spreadsheet.Font();
var fontName = new FontName { Val = StringValue.FromString("Arial") };
var fontSize = new FontSize { Val = DoubleValue.FromDouble(11) };
font.FontName = fontName;
font.FontSize = fontSize;
fonts.Append(font);
//Font Index 1
font = new DocumentFormat.OpenXml.Spreadsheet.Font();
fontName = new FontName { Val = StringValue.FromString("Arial") };
fontSize = new FontSize { Val = DoubleValue.FromDouble(12) };
font.FontName = fontName;
font.FontSize = fontSize;
font.Bold = new Bold();
fonts.Append(font);
fonts.Count = UInt32Value.FromUInt32((uint)fonts.ChildElements.Count);
var fills = new Fills();
var fill = new Fill();
var patternFill = new PatternFill { PatternType = PatternValues.None };
fill.PatternFill = patternFill;
fills.Append(fill);
fill = new Fill();
patternFill = new PatternFill { PatternType = PatternValues.Gray125 };
fill.PatternFill = patternFill;
fills.Append(fill);
//Fill index 2
fill = new Fill();
patternFill = new PatternFill { PatternType = PatternValues.Solid, ForegroundColor = new ForegroundColor() };
patternFill.ForegroundColor = TranslateForeground(System.Drawing.Color.LightBlue);
patternFill.BackgroundColor = new BackgroundColor { Rgb = patternFill.ForegroundColor.Rgb };
fill.PatternFill = patternFill;
fills.Append(fill);
//Fill index 3
fill = new Fill();
patternFill = new PatternFill { PatternType = PatternValues.Solid, ForegroundColor = new ForegroundColor() };
patternFill.ForegroundColor = TranslateForeground(System.Drawing.Color.DodgerBlue);
patternFill.BackgroundColor = new BackgroundColor { Rgb = patternFill.ForegroundColor.Rgb };
fill.PatternFill = patternFill;
fills.Append(fill);
fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
var borders = new Borders();
var border = new Border
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder(),
BottomBorder = new BottomBorder(),
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
//All Boarder Index 1
border = new Border
{
LeftBorder = new LeftBorder { Style = BorderStyleValues.Thin },
RightBorder = new RightBorder { Style = BorderStyleValues.Thin },
TopBorder = new TopBorder { Style = BorderStyleValues.Thin },
BottomBorder = new BottomBorder { Style = BorderStyleValues.Thin },
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
//Top and Bottom Boarder Index 2
border = new Border
{
LeftBorder = new LeftBorder(),
RightBorder = new RightBorder(),
TopBorder = new TopBorder { Style = BorderStyleValues.Thin },
BottomBorder = new BottomBorder { Style = BorderStyleValues.Thin },
DiagonalBorder = new DiagonalBorder()
};
borders.Append(border);
borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
var cellStyleFormats = new CellStyleFormats();
var cellFormat = new CellFormat { NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0 };
cellStyleFormats.Append(cellFormat);
cellStyleFormats.Count = UInt32Value.FromUInt32((uint)cellStyleFormats.ChildElements.Count);
uint iExcelIndex = 164;
var numberingFormats = new NumberingFormats();
var cellFormats = new CellFormats();
cellFormat = new CellFormat { NumberFormatId = 0, FontId = 0, FillId = 0, BorderId = 0, FormatId = 0 };
cellFormats.Append(cellFormat);
var nformatDateTime = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss")
};
numberingFormats.Append(nformatDateTime);
var nformat4Decimal = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("#,##0.0000")
};
numberingFormats.Append(nformat4Decimal);
var nformat2Decimal = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++),
FormatCode = StringValue.FromString("#,##0.00")
};
numberingFormats.Append(nformat2Decimal);
var nformatForcedText = new NumberingFormat
{
NumberFormatId = UInt32Value.FromUInt32(iExcelIndex),
FormatCode = StringValue.FromString("@")
};
numberingFormats.Append(nformatForcedText);
// index 1
// Cell Standard Date format
cellFormat = new CellFormat
{
NumberFormatId = 14,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 2
// Cell Standard Number format with 2 decimal placing
cellFormat = new CellFormat
{
NumberFormatId = 4,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 3
// Cell Date time custom format
cellFormat = new CellFormat
{
NumberFormatId = nformatDateTime.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 4
// Cell 4 decimal custom format
cellFormat = new CellFormat
{
NumberFormatId = nformat4Decimal.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 5
// Cell 2 decimal custom format
cellFormat = new CellFormat
{
NumberFormatId = nformat2Decimal.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 6
// Cell forced number text custom format
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 7
// Cell text with font 12
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 1,
FillId = 0,
BorderId = 0,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 8
// Cell text
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
FillId = 0,
BorderId = 1,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 9
// Coloured 2 decimal cell text
cellFormat = new CellFormat
{
NumberFormatId = nformat2Decimal.NumberFormatId,
FontId = 0,
FillId = 2,
BorderId = 2,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 10
// Coloured cell text
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 0,
FillId = 2,
BorderId = 2,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
// Index 11
// Coloured cell text
cellFormat = new CellFormat
{
NumberFormatId = nformatForcedText.NumberFormatId,
FontId = 1,
FillId = 3,
BorderId = 2,
FormatId = 0,
ApplyNumberFormat = BooleanValue.FromBoolean(true)
};
cellFormats.Append(cellFormat);
numberingFormats.Count = UInt32Value.FromUInt32((uint)numberingFormats.ChildElements.Count);
cellFormats.Count = UInt32Value.FromUInt32((uint)cellFormats.ChildElements.Count);
this.Append(numberingFormats);
this.Append(fonts);
this.Append(fills);
this.Append(borders);
this.Append(cellStyleFormats);
this.Append(cellFormats);
var css = new CellStyles();
var cs = new CellStyle { Name = StringValue.FromString("Normal"), FormatId = 0, BuiltinId = 0 };
css.Append(cs);
css.Count = UInt32Value.FromUInt32((uint)css.ChildElements.Count);
this.Append(css);
var dfs = new DifferentialFormats { Count = 0 };
this.Append(dfs);
var tss = new TableStyles
{
Count = 0,
DefaultTableStyle = StringValue.FromString("TableStyleMedium9"),
DefaultPivotStyle = StringValue.FromString("PivotStyleLight16")
};
this.Append(tss);
}
private static ForegroundColor TranslateForeground(System.Drawing.Color fillColor)
{
return new ForegroundColor()
{
Rgb = new HexBinaryValue()
{
Value =
System.Drawing.ColorTranslator.ToHtml(
System.Drawing.Color.FromArgb(
fillColor.A,
fillColor.R,
fillColor.G,
fillColor.B)).Replace("#", "")
}
};
}
}
public class TextCell : Cell
{
public TextCell(string header, string text, int index)
{
this.DataType = CellValues.InlineString;
this.CellReference = header + index;
//Add text to the text cell.
this.InlineString = new InlineString { Text = new Text { Text = text } };
}
}
public class NumberCell : Cell
{
public NumberCell(string header, string text, int index)
{
this.DataType = CellValues.Number;
this.CellReference = header + index;
this.CellValue = new CellValue(text);
}
}
public class FormatedNumberCell : NumberCell
{
public FormatedNumberCell(string header, string text, int index)
: base(header, text, index)
{
this.StyleIndex = 2;
}
}
public class DateCell : Cell
{
public DateCell(string header, DateTime dateTime, int index)
{
this.DataType = CellValues.Date;
this.CellReference = header + index;
this.StyleIndex = 1;
this.CellValue = new CellValue { Text = dateTime.ToOADate().ToString() }; ;
}
}
public class FomulaCell : Cell
{
public FomulaCell(string header, string text, int index)
{
this.CellFormula = new CellFormula { CalculateCell = true, Text = text };
this.DataType = CellValues.Number;
this.CellReference = header + index;
this.StyleIndex = 2;
}
}
public class HeaderCell : TextCell
{
public HeaderCell(string header, string text, int index)
: base(header, text, index)
{
this.StyleIndex = 11;
}
}
}
public class Package
{
public string Company { get; set; }
public double Weight { get; set; }
public long TrackingNumber { get; set; }
public DateTime DateOrder { get; set; }
public bool HasCompleted { get; set; }
}
void MakeHelperExcel()
{
List<Package> packages =
new List<Package>
{ new Package { Company = "Coho Vineyard", Weight = 25.2, TrackingNumber = 89453312L, DateOrder = DateTime.Today, HasCompleted = false },
new Package { Company = "Lucerne Publishing", Weight = 18.7, TrackingNumber = 89112755L, DateOrder = DateTime.Today, HasCompleted = false },
new Package { Company = "Wingtip Toys", Weight = 6.0, TrackingNumber = 299456122L, DateOrder = DateTime.Today, HasCompleted = false },
new Package { Company = "Adventure Works", Weight = 33.8, TrackingNumber = 4665518773L, DateOrder = DateTime.Today.AddDays(-4), HasCompleted = true },
new Package { Company = "Test Works", Weight = 35.8, TrackingNumber = 4665518774L, DateOrder = DateTime.Today.AddDays(-2), HasCompleted = true },
new Package { Company = "Good Works", Weight = 48.8, TrackingNumber = 4665518775L, DateOrder = DateTime.Today.AddDays(-1), HasCompleted = true },
};
List<string> headerNames = new List<string> { "Company", "Weight", "Tracking Number", "Date Order", "Completed" };
Admin.ExcelHelper excelFacade = new Admin.ExcelHelper();
excelFacade.Create(@"D:\Admin\Example\MakeHelperExcel.xlsx", packages, "Packages", headerNames);
}