62,041
社区成员
发帖
与我相关
我的任务
分享
public static MemoryStream DataTableToExcel<T>(DataTable dt) where T : class, new()
{
XSSFWorkbook fileWorkbook = new XSSFWorkbook();
ISheet sheet = fileWorkbook.CreateSheet("Sheet1");
List<int> nonDownList = new List<int>();
List<int> columnWidthList = new List<int>();
List<string> columnNameList = new List<string>();
var type = typeof(T);
int nonDown = 0;
foreach (var porp in type.GetProperties())
{
nonDown++;
if (porp.IsDefined(typeof(ShowNameAttribute), true))
{
var showNameAttribute = porp.GetCustomAttribute<ShowNameAttribute>();
columnNameList.Add(showNameAttribute.ShowName);
var columnWidthAttribute = porp.GetCustomAttribute<ColumnWidthAttribute>();
var number = columnWidthAttribute == null ? columnWidth : columnWidthAttribute.Number;
columnWidthList.Add(number);
}
else
{
nonDownList.Add(nonDown);
}
}
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < columnNameList.Count; i++)
{
ICell cell = row.CreateCell(i); //創建單元格
cell.SetCellValue(columnNameList[i]); //表頭賦值
sheet.SetColumnWidth(i, columnWidthList[i]); //表頭寬度
cell.CellStyle = excelStyle.CellHeadStyle; //表頭樣式
row.Height = 30 * 20; //行高为30
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
if (!nonDownList.Contains(j))
{
ICell cell = row1.CreateCell(j);
string drValue = dt.Rows[i][j].ToString();
CellStyle(cell, dt.Columns[j].DataType.ToString(), drValue);
}
}
}
//转为字节数组
using (MemoryStream ms = new MemoryStream())
{
fileWorkbook.Write(ms);
ms.Flush();
return ms;
}
}
public class ToExcel
{
private readonly int columnWidth = 3000;
private readonly string strSheetName = "Sheet1";
private ExcelStyle excelStyle { get;set; }
public MemoryStream DataTableToExcel(ExcelData excelData)
{
XSSFWorkbook fileWorkbook = new XSSFWorkbook();
excelStyle = new ExcelStyle(fileWorkbook);
ISheet sheet = fileWorkbook.CreateSheet();
sheet = GetExcelSheet(sheet, excelData);
//转为字节数组
using (MemoryStream ms = new MemoryStream())
{
fileWorkbook.Write(ms);
ms.Flush();
return ms;
}
}
}
public class NPOIHelper
{
private static ToDataTable toDataTable = new ToDataTable();
private static ToExcel toExcel = new ToExcel();
public static MemoryStream DataTableToExcel(ExcelData excelData)
{
return toExcel.DataTableToExcel(excelData);
}
}
public sealed class ExcelStyle
{
public ICellStyle CellStyle { get; set; }
public ICellStyle CellHeadStyle { get; set; }
public ICellStyle CellStringStyle { get; set; }
public ICellStyle CellIntStyle { get; set; }
public ICellStyle CellNumberStyle { get; set; }
public ICellStyle CellDateStyle { get; set; }
public ICellStyle CellTimeStyle { get; set; }
public ExcelStyle()
{
XSSFWorkbook fileWorkbook = new XSSFWorkbook();
//初始单元格样式
ICellStyle cellStyle = fileWorkbook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.Center; //左右居中
cellStyle.VerticalAlignment = VerticalAlignment.Center; //上下居中
cellStyle.BorderTop = BorderStyle.Thin; //单元格上边框黑色
cellStyle.BorderBottom = BorderStyle.Thin; //单元格下边框黑色
cellStyle.BorderLeft = BorderStyle.Thin; //单元格左边框黑色
cellStyle.BorderRight = BorderStyle.Thin; //单元格右边框黑色
//原始单元格样式
CellStyle = cellStyle;
//表头样式
IFont headFontStyle = fileWorkbook.CreateFont(); //表头字体样式
headFontStyle.FontHeightInPoints = 11;//字体大小
headFontStyle.Boldweight = 700; //字体加粗
headFontStyle.Color = 9; //字体颜色
CellHeadStyle = cellStyle;
CellHeadStyle.SetFont(headFontStyle); //将字体样式赋给样式对象
CellHeadStyle.FillPattern = FillPattern.SolidForeground;
CellHeadStyle.FillForegroundColor = 0;
((XSSFColor)CellHeadStyle.FillForegroundColorColor).SetRgb(new byte[] { 51, 102, 255 });
CellHeadStyle.IsLocked = true;
CellHeadStyle.WrapText = true;
//stringStyle
CellStringStyle = cellStyle;
CellStringStyle.WrapText = false;
CellStringStyle.IsLocked = false;
CellStringStyle.Alignment = HorizontalAlignment.Left;
//intStyle
CellIntStyle = cellStyle;
CellIntStyle.Alignment = HorizontalAlignment.Right;
CellIntStyle.IsLocked = false;
CellIntStyle.DataFormat = fileWorkbook.CreateDataFormat().GetFormat("0");
//numberStyle
CellNumberStyle = cellStyle;
CellNumberStyle.Alignment = HorizontalAlignment.Right;
CellNumberStyle.IsLocked = false;
CellNumberStyle.DataFormat = fileWorkbook.CreateDataFormat().GetFormat("#,##0.00");
//dateStyle
CellDateStyle = cellStyle;
CellDateStyle.DataFormat = fileWorkbook.CreateDataFormat().GetFormat("yyyy-mm-dd");
}
}