NPOI导出模板样式

/// <summary>
/// 导出多种车辆统计表格
/// </summary>
/// <returns></returns>
[ActionName("MoreAllImport")]
public void MoreAllImport()
{
List<CarMoreStatistics> list = TempData["MoreAllExcel"] as List<CarMoreStatistics>;
if (list == null)
{
return;
}
DataTable dt = TRSLMS.Common.Reflection.GetDisplayNameToDataTable(list);
string path = Server.MapPath("~/downloads/excel/车辆整合统计表（样表）.xls");
string exName = "各种车辆统计表";
GetSheel(dt, path, exName, 4);
}

/// <summary>
/// 导出Excel数据
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="path">模板路径</param>
/// <param name="exName">标题名称</param>
/// <param name="Line">第几行开始</param>
private static void GetSheel(DataTable dt, string path, string exName, int Line = 0)
{
using (FileStream fs = System.IO.File.OpenRead(path))
{
HSSFWorkbook wk = new HSSFWorkbook(fs);
fs.Close();
ISheet sheet = wk.GetSheetAt(0);
sheet.GetRow(0).GetCell(0).SetCellValue(Common.SessionManage.GROUP.NAME + exName);
IRow row = sheet.GetRow(1);
ICell cell = row.GetCell(0);
cell.SetCellType(CellType.String);
cell.SetCellValue("统计时间：" + DateTime.Now.ToString("yyyy年MM月dd日"));
NPOIHelp.FillHssfWorkbookByDataTable(dt, Line, 0, wk, 0);
NPOIHelp.ExportHSSFWorkbookByWeb(wk, exName + ".xls");
}
}
#endregion

 

 

/// <summary>
/// NPOI导出Excel
/// </summary>
public class NPOIHelp
{
/// <summary>
/// 根据数据源和偏移量填充工作表指定索引的工作薄
/// </summary>
/// <param name="dataSource">数据源</param>
/// <param name="rowOffset">行偏移量</param>
/// <param name="colOffset">列偏移量</param>
/// <param name="hssfworkbook">工作表</param>
/// <param name="sheetIndex">工作薄索引</param>
public static void FillHssfWorkbookByDataTable(DataTable dataSource, int rowOffset, int colOffset, HSSFWorkbook hssfworkbook, int sheetIndex)
{
ISheet sheet = hssfworkbook.GetSheetAt(sheetIndex);

ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
//设置单元格上下左右边框线
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;

for (int i = 0; i < dataSource.Rows.Count; i++)
{
for (int j = 0; j < dataSource.Columns.Count; j++)
{
IRow row = sheet.GetRow(i + rowOffset);

if (row == null)
row = sheet.CreateRow(i + rowOffset);
ICell newCell = row.GetCell(j + colOffset);

if (newCell == null)
{
newCell = row.CreateCell(j + colOffset);
newCell.CellStyle = cellStyle;
}
string drValue = dataSource.Rows[i][j].ToString();
string drType = dataSource.Columns[j].DataType.ToString();
switch (drType)
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);

//newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
}
}

/// <summary>
/// 将指定的HSSFWorkbook输出到流
/// </summary>
/// <param name="workbook"></param>
/// <param name="strFileName">文件名</param>
public static void ExportHSSFWorkbookByWeb(HSSFWorkbook workbook, string strFileName)
{
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;

HttpContext curContext = HttpContext.Current;

// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.Default;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Content-Disposition",
//"attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.Default));
"attachment;filename=" + strFileName);
curContext.Response.BinaryWrite(ms.GetBuffer());
curContext.Response.End();
}

}
}