/// <summary>
/// 文件导出,不需要模板
/// </summary>
/// <typeparam name="T">类名</typeparam>
/// <param name="title">文件标题</param>
/// <param name="head">表头</param>
/// <param name="list">内容</param>
/// <param name="fileName">文件名</param>
/// <returns></returns>
public static MemoryStream StatisticalReport_Exl <T>(string title, List <string> head, List <T> list, string fileName)
{
IWorkbook workbook = null;
string fileExt = Path.GetExtension(fileName).ToLower();
if (fileExt == ".xlsx")
{
workbook = new XSSFWorkbook();
}
else if (fileExt == ".xls")
{
workbook = new HSSFWorkbook();
}
else
{
workbook = null;
}
if (workbook == null)
{
return(new MemoryStream());;
}
ISheet sheet = workbook.CreateSheet("Sheet0");
var titlestyle = GetDefaultStyle(workbook);
var titlefont = workbook.CreateFont();
titlefont.Boldweight = short.MaxValue;
titlefont.FontHeightInPoints = 14;
titlestyle.SetFont(titlefont);
titlestyle.Alignment = HorizontalAlignment.Center;//水平对齐
var indexcellstyle = GetDefaultStyle(workbook);
var cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Normal;
cellfont.FontHeightInPoints = 10;
indexcellstyle.SetFont(cellfont);
var valuecellstyle = GetDefaultStyle(workbook);
var cellfontv = workbook.CreateFont();
cellfontv.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Normal;
cellfontv.FontHeightInPoints = 10;
valuecellstyle.Alignment = HorizontalAlignment.Center;
valuecellstyle.SetFont(cellfontv);
int valueIndex = 0;
//标题
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
CreatAddCell(row, title, titlestyle, 0);
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, head.Count - 1));
//表头
int rowHead = 1;
IRow rowhead = sheet.CreateRow(rowHead);
foreach (string cellhead in head)
{
CreatAddCell(rowhead, cellhead, indexcellstyle, valueIndex);
valueIndex++;
}
DataTable dt = TypeHelper.ListToDataTable <T>(list);
//内容绑定
int rowIndex = 2;
foreach (DataRow dataRow in dt.Rows)
{
IRow rowContent = sheet.CreateRow(rowIndex);
valueIndex = 0;
for (var i = 0; i < dt.Columns.Count; i++)
{
var value = dataRow[i];
if (value == null)
{
value = "";
}
else
{
value = value.GetType() == typeof(DateTime) ? Convert.ToDateTime(value).ToString("yyyy-MM-dd") : value.ToString();
}
CreatAddCell(rowContent, value.ToString(), valuecellstyle, valueIndex);
valueIndex++;
}
rowIndex++;
}
NpoiMemoryStream ms = new NpoiMemoryStream();
ms.AllowClose = false;
workbook.Write(ms);
ms.Flush();
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
return(ms);
}
/// <summary>
/// Excel文件导出，引入模板
/// </summary>
/// <param name="dt">数据集</param>
/// <param name="TempletFileName">模板文件</param>
/// <param name="sheetName">sheet名称</param>
/// <param name="rowBody">内容开始行索引</param>
/// <returns></returns>
public static MemoryStream DataTableToExcel(DataTable dt, string TempletFileName, string sheetName, int rowBody)
{
try
{
FileStream file = new FileStream(TempletFileName, FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
HSSFSheet ws = (HSSFSheet)hssfworkbook.GetSheet(sheetName);
if (dt != null && dt.Rows.Count > 0)
{
int row = dt.Rows.Count;
int cell = dt.Columns.Count;
for (int i = 0; i < row; i++)
{
for (int j = 0; j < cell; j++)
{
/*
* CELL_TYPE_NUMERIC 数值型 0
* CELL_TYPE_STRING 字符串型 1
* CELL_TYPE_FORMULA 公式型 2
* CELL_TYPE_BLANK 空值 3
* CELL_TYPE_BOOLEAN 布尔型 4
* CELL_TYPE_ERROR 错误
*/
if (dt.Columns[j].DataType == typeof(decimal))
{
ws.GetRow(rowBody + i).GetCell(j).SetCellType(CellType.Numeric);
ws.GetRow(rowBody + i).GetCell(j).SetCellValue(Convert.ToDouble(dt.Rows[i][j]));
}
else if (dt.Columns[j].DataType == typeof(DateTime))
{
ws.GetRow(rowBody + i).GetCell(j).SetCellType(CellType.String);
ws.GetRow(rowBody + i).GetCell(j).SetCellValue(Convert.ToDateTime(dt.Rows[i][j]));
}
else if (dt.Columns[j].DataType == typeof(bool))
{
ws.GetRow(rowBody + i).GetCell(j).SetCellType(CellType.Boolean);
ws.GetRow(rowBody + i).GetCell(j).SetCellValue(Convert.ToBoolean(dt.Rows[i][j]));
}
else if (dt.Columns[j].DataType == typeof(byte))
{
ws.GetRow(rowBody + i).GetCell(j).SetCellType(CellType.String);
ws.GetRow(rowBody + i).GetCell(j).SetCellValue(Convert.ToByte(dt.Rows[i][j]));
}
else if (dt.Columns[j].DataType == typeof(Int16))
{
ws.GetRow(rowBody + i).GetCell(j).SetCellType(CellType.Numeric);
ws.GetRow(rowBody + i).GetCell(j).SetCellValue(Convert.ToInt16(dt.Rows[i][j]));
}
else if (dt.Columns[j].DataType == typeof(Int32))
{
ws.GetRow(rowBody + i).GetCell(j).SetCellType(CellType.Numeric);
ws.GetRow(rowBody + i).GetCell(j).SetCellValue(Convert.ToInt32(dt.Rows[i][j]));
}
else if (dt.Columns[j].DataType == typeof(Int64))
{
ws.GetRow(rowBody + i).GetCell(j).SetCellType(CellType.Numeric);
ws.GetRow(rowBody + i).GetCell(j).SetCellValue(Convert.ToInt64(dt.Rows[i][j]));
}
else
{
ws.GetRow(rowBody + i).GetCell(j).SetCellType(CellType.String);
ws.GetRow(rowBody + i).GetCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
}
}
ws.ForceFormulaRecalculation = true;
NpoiMemoryStream ms = new NpoiMemoryStream();
ms.AllowClose = false;
hssfworkbook.Write(ms);
ms.Flush();
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
return(ms);
}
catch (Exception ex)
{
return(new MemoryStream());
}
}
