asp.net MVC exports the List collection or Datatable to an Excel table

Little scum · Posted on 11/13/2015 10:19:13 AM

/// <summary>
/// 将DataTable导出成Excel表格
/// </summary>
public class ExcelHelper
{
public MemoryStream RenderToExcel(DataTable table)
{
MemoryStream ms = new MemoryStream();
using (table)
{
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
IRow headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in table.Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value
// handling value.
int rowIndex = 1;
foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in table.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
}
return ms;
}
}

Copy code

public ActionResult ExportBrand(int id)
{
int aaa = id;
//查询参加会议的数据
List<BLL.Entitys.InBoardRoomPersonnel> list = BLL.Brand.Index.GetJoinBrandUser(id);
//需要填写的字段
//List<BLL.Entitys.GetDictionary> dic = BLL.Until.GetUserInfo.SelectUser(Extcredits);
//转换成list集合
List<BLL.Entitys.UserInfo> userlist = new List<UserInfo>();
foreach (BLL.Entitys.InBoardRoomPersonnel ir in list)
{
var getMS = new MemoryStream(ir.BoardRoomUser);
getMS.Position = 0;
BLL.Entitys.UserInfo u = BinarySerializerHelper.DeSerialize<BLL.Entitys.UserInfo>(getMS);
userlist.Add(u);
}
DataTable dt = userlist.FillDataTable<BLL.Entitys.UserInfo>();
dt =BLL.Until.DataTableExtensions.TranslateDataTable(dt);
BLL.Until.ExcelHelper helper = new ExcelHelper();
//bll helper = new BLL.Export.ExportHelper();
MemoryStream ms = helper.RenderToExcel(dt);
return File(ms, "xls", "Brand" + DateTime.Now.ToString("yyyyMMddhhmm") + ".xls");
}

Copy code

/// <summary>
/// 实体类转换成DataTable
/// </summary>
/// <param name="modelList">实体类列表</param>
/// <returns></returns>
public static DataTable FillDataTable<T>(this List<T> modelList)
{
if (modelList == null || modelList.Count == 0)
{
return null;
}
DataTable dt = CreateData(modelList[0]);
foreach (T model in modelList)
{
DataRow dataRow = dt.NewRow();
foreach (PropertyInfo propertyInfo in typeof(T).GetProperties())
{
dataRow[propertyInfo.Name] = propertyInfo.GetValue(model, null);
}
dt.Rows.Add(dataRow);
}
return dt;
}
/// <summary>
/// 根据实体类得到表结构
/// </summary>
/// <param name="model">实体类</param>
/// <returns></returns>
private static DataTable CreateData<T>(T model)
{
DataTable dataTable = new DataTable(typeof(T).Name);
foreach (PropertyInfo propertyInfo in typeof(T).GetProperties())
{
dataTable.Columns.Add(new DataColumn(propertyInfo.Name, propertyInfo.PropertyType));
}
return dataTable;
}
/// <summary>
/// DataTable字段翻译成中文
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public static DataTable TranslateDataTable(DataTable table)
{
DataTable dt = new DataTable();
dt.TableName = "TempTable";
if (table != null && table.Rows.Count > 0)
{
//先为dt构造列信息
foreach (DataColumn column in table.Columns)
{
string name = BLL.Until.GetUserInfo.GetBewrite(column.ColumnName);
dt.Columns.Add(name);
}
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow NewRow = dt.NewRow();
DataRow row = table.Rows[i];
for (int j = 0; j < dt.Columns.Count; j++)
{
NewRow[j] = row[j].ToString();
}
dt.Rows.Add(NewRow);
}
}
return dt;
}

Copy code

If it is a list collection, you need to convert it to datatable before exporting it.

NPOI.zip (553.95 KB, Number of downloads: 2, 售价: 2 粒MB)

[ASP.NET] asp.net MVC exports the List collection or Datatable to an Excel table

Sections viewed