/// <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();

}

}

}