.net 数据下载为Excel文件

List<Model> list = HttpPost<List<Model>>(" ", paramModel);
if (list != null && list.Count > 0)
{

DataTable tblDatas = new DataTable("Datas");
DataColumn dc = null;
dc = tblDatas.Columns.Add("ID", Type.GetType("System.Int32"));
dc.AutoIncrement = true;//自动增加
dc.AutoIncrementSeed = 1;//起始为1
dc.AutoIncrementStep = 1;//步长为1
dc.AllowDBNull = false;//

dc = tblDatas.Columns.Add("推广位ID", typeof(string));

foreach (var item in list)
{

DataRow newRow;
newRow = tblDatas.NewRow();
newRow["推广位ID"] = item.PositionId;

 tblDatas.Rows.Add(newRow);

}

var result = NPOIHelper.ExportDataTableToExcel(tblDatas);

return File(result, "application/ms-excel", "统计.xls");

}

 

public static Stream ExportDataTableToExcel(DataTable sourceTable)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
ISheet sheet = workbook.CreateSheet(); //创建工作表
IRow headerRow = sheet.CreateRow(0);
//列名
List<string> arrHeader = new List<string>();//获取以分隔符，取得的列名（多个列名字符串）如(ID|编号,Name|名称)
foreach (DataColumn item in sourceTable.Columns)
{
arrHeader.Add(item.ColumnName);
}
string[] arrParam = new string[arrHeader.Count];//存储单个列名字符串的数组
string[] arrName = new string[arrParam.Length];//数据库列名
string[] arrType = new string[arrParam.Length];//Excel中单元格的类型
for (int i = 0; i < arrHeader.Count; i++)
{
arrParam = arrHeader[i].Split('|');////获取以分隔符1取得单个列名及中文名称的字符串数组 如(ID,编号）
headerRow.CreateCell(i).SetCellValue(arrParam[0]);//设置列名
arrName[i] = arrParam[0].ToString();//将数据库列名加入数组中

//是否传了字段类型
if (arrParam.Length > 2)
{
arrType[i] = arrParam[2].ToString();//字段类型
}
}
//数据
int rowIndex = 1;

//单元格格式
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");

if (sourceTable != null && sourceTable.Rows.Count > 0)
{
foreach (DataRow row in sourceTable.Rows)//循环数据行
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)//循环列，取出每行中的每列
{
for (int i = 0; i < arrName.Length; i++)//循环要显示的字段，取出放在构造dataRow中
{
if (column.ColumnName.ToLower() == arrName[i].ToLower())
{
//如果传了字段类型
if (arrParam.Length > 2)
{
ICell cell = dataRow.CreateCell(i);
switch (arrType[i].Replace("\r\n", "").Trim())
{
case "int":
if (!string.IsNullOrEmpty(row[column].ToString()))
{
cell.SetCellValue(Convert.ToInt64(row[column].ToString()));
}
else
{
cell.SetCellValue(row[column].ToString());
}
break;
case "string":
cell.SetCellValue(row[column].ToString());
break;
case "datetime":
cell.SetCellValue(row[column].ToString());
break;
case "decimal":
if (!string.IsNullOrEmpty(row[column].ToString()))
{
cell.SetCellValue(Convert.ToInt64(Convert.ToDecimal(row[column].ToString())));
}
else
{
cell.SetCellValue(row[column].ToString());
}

cell.CellStyle = cellStyle;
break;
}
}
else
{
dataRow.CreateCell(i).SetCellValue(row[column].ToString());//将要显示的列的数据加入Excel行中
}
break;
}
}
}
rowIndex++;
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
headerRow = null;
workbook = null;
return ms;
}