public MemoryStream Export(DataTable data, string title = "No title", string subtitle = "No subtitle")
{
var converter = new ExcelConverter();
var worksheet = converter.CreateSheet();
var columns = new List <DataColumn>();
for (var i = 0; i < data.Columns.Count; i++)
{
if (data.Columns[i].ExtendedProperties.ContainsKey("ExcelIgnore"))
{
continue;
}
columns.Add(data.Columns[i]);
}
var ttrow = worksheet.CreateRow(0);
ttrow.HeightInPoints = 30;
for (var i = 0; i < columns.Count; i++)
{
var ttcell = ttrow.CreateCell(i);
ttcell.SetCellValue(new XSSFRichTextString(i == 0 ? title : ""));
ttcell.CellStyle = converter.Title;
}
worksheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, columns.Count - 1));
var strow = worksheet.CreateRow(1);
strow.HeightInPoints = 20;
for (var i = 0; i < columns.Count; i++)
{
var stcell = strow.CreateCell(i);
stcell.SetCellValue(new XSSFRichTextString(i == 0 ? subtitle : ""));
stcell.CellStyle = converter.SubTitle;
}
worksheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, columns.Count - 1));
var ctrow = worksheet.CreateRow(2);
ctrow.HeightInPoints = 20;
for (var i = 0; i < columns.Count; i++)
{
var cell = ctrow.CreateCell(i);
var properties = columns[i].ExtendedProperties;
cell.SetCellValue(properties.ContainsKey("ExcelDisplayName") ? properties["ExcelDisplayName"].ToString() : columns[i].ColumnName);
cell.CellStyle = converter.CellTitle;
//set cell column width
worksheet.SetColumnWidth(i, 25 * 150);
}
for (int k = 0; k < data.Rows.Count; k++)
{
var row = (XSSFRow)worksheet.CreateRow(3 + k);
row.HeightInPoints = 18;
for (int g = 0; g < columns.Count; g++)
{
var cell = row.CreateCell(g);
var column = columns[g];
var type = column.DataType;
var value = data.Rows[k][column.ColumnName];
cell.CellStyle = converter.GetCellStyle(Color.Empty, false);
if (value == null)
{
continue;
}
if (type == typeof(Int16) ||
type == typeof(Int32) ||
type == typeof(Int64) ||
type == typeof(Single) ||
type == typeof(Double))
{
cell.SetCellValue(Convert.ToDouble(value));
}
else if (type == typeof(Boolean))
{
cell.SetCellValue((Boolean)value);
}
else if (type == typeof(DateTime))
{
cell.SetCellValue((DateTime)value);
}
else
{
cell.SetCellValue(new XSSFRichTextString(value.ToString()));
}
}
}
var ms = new MemoryStream();
converter.Workbook.Write(ms);
return(ms);
}
public MemoryStream Export <T>(List <T> data, string title = "No title", string subtitle = "No subtitle")
{
var converter = new ExcelConverter();
var worksheet = converter.CreateSheet();
PropertyInfo backgroundAttribute = null;
var colorAttributes = new List <string>();
var boolAttributes = new List <Kv <PropertyInfo, ExcelBooleanNameAttribute> >();
var dataAttributes = new List <Kv <PropertyInfo, string> >();
var props = typeof(T).GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);
foreach (var prop in props)
{
if (prop.IsDefined(typeof(ExcelIgnoreAttribute), true))
{
continue;
}
if (prop.IsDefined(typeof(ExcelBackgroundAttribute), true))
{
if (prop.PropertyType == typeof(Color))
{
backgroundAttribute = prop;
}
continue;
}
if (prop.IsDefined(typeof(ExcelColorAttribute), true))
{
colorAttributes.Add(prop.Name);
}
if (prop.IsDefined(typeof(ExcelBooleanNameAttribute), true))
{
if (prop.PropertyType == typeof(Boolean))
{
var boolean = (ExcelBooleanNameAttribute)prop.GetCustomAttributes(typeof(ExcelBooleanNameAttribute), true)[0];
boolAttributes.Add(new Kv <PropertyInfo, ExcelBooleanNameAttribute> {
Key = prop, Value = boolean
});
}
}
if (prop.IsDefined(typeof(ExcelDisplayNameAttribute), true))
{
var display = (ExcelDisplayNameAttribute)prop.GetCustomAttributes(typeof(ExcelDisplayNameAttribute), true)[0];
dataAttributes.Add(new Kv <PropertyInfo, string>(prop, display.DisplayName));
}
else
{
dataAttributes.Add(new Kv <PropertyInfo, string>(prop, prop.Name));
}
}
var ttrow = worksheet.CreateRow(0);
ttrow.HeightInPoints = 30;
for (var i = 0; i < dataAttributes.Count; i++)
{
var ttcell = ttrow.CreateCell(i);
ttcell.SetCellValue(new XSSFRichTextString(i == 0 ? title : ""));
ttcell.CellStyle = converter.Title;
}
worksheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dataAttributes.Count - 1));
var strow = worksheet.CreateRow(1);
strow.HeightInPoints = 20;
for (var i = 0; i < dataAttributes.Count; i++)
{
var stcell = strow.CreateCell(i);
stcell.SetCellValue(new XSSFRichTextString(i == 0 ? subtitle : ""));
stcell.CellStyle = converter.SubTitle;
}
worksheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, dataAttributes.Count - 1));
var ctrow = worksheet.CreateRow(2);
ctrow.HeightInPoints = 20;
for (var i = 0; i < dataAttributes.Count; i++)
{
var cell = ctrow.CreateCell(i);
cell.SetCellValue(dataAttributes[i].Value);
cell.CellStyle = converter.CellTitle;
//set cell column width
worksheet.SetColumnWidth(i, 25 * 150);
}
for (int k = 0; k < data.Count; k++)
{
var row = (XSSFRow)worksheet.CreateRow(3 + k);
row.HeightInPoints = 18;
var background = Color.Empty;
if (backgroundAttribute != null)
{
background = (Color)backgroundAttribute.GetValue(data[k]);
}
for (int g = 0; g < dataAttributes.Count; g++)
{
var cell = row.CreateCell(g);
var prop = dataAttributes[g].Key;
var draw = colorAttributes.Contains(prop.Name);
var name = prop.Name;
var type = prop.PropertyType;
var value = prop.GetValue(data[k]);
if (value == null)
{
cell.CellStyle = converter.GetCellStyle(background, draw);
continue;
}
if (type == typeof(Int16) ||
type == typeof(Int32) ||
type == typeof(Int64) ||
type == typeof(Single) ||
type == typeof(Double))
{
cell.CellStyle = converter.GetCellStyle(background, draw);
cell.SetCellValue(Convert.ToDouble(value));
}
else if (type == typeof(Boolean))
{
cell.CellStyle = converter.GetCellStyle(background, draw);
var boolAttribute = boolAttributes.Find(b => b.Key.Equals(prop));
if (boolAttribute != null)
{
cell.SetCellValue(new XSSFRichTextString((Boolean)value ? boolAttribute.Value.True : boolAttribute.Value.False));
}
else
{
cell.SetCellValue((Boolean)value);
}
}
else if (type == typeof(DateTime))
{
cell.CellStyle = converter.GetDateCellStyle(background, draw);
cell.SetCellValue((DateTime)value);
}
else
{
cell.CellStyle = converter.GetCellStyle(background, draw);
cell.SetCellValue(new XSSFRichTextString(value.ToString()));
}
}
}
var ms = new MemoryStream();
converter.Workbook.Write(ms);
return(ms);
}
