泛型数据源:
public static string Export2007<T>(IList<T> sourceData)
{
if (sourceData == null) throw new ArgumentNullException("sourceData");
IWorkbook workbook = new XSSFWorkbook();
var properties = TypeDescriptor.GetProperties(typeof(T));
//一个Sheet导出的记录数
const int pageSize = 20000;
//记录总数
var rowCount = sourceData.Count;
//总Sheet数
var pageCount = (rowCount + pageSize - 1) / pageSize;
var count = 1;
while (count <= pageCount)
{
var list = count == 1
? sourceData.Skip(0).Take(pageSize).ToList()
: sourceData.Skip((count - 1)*pageSize).Take(pageSize).ToList();
if (list.Any())
{
var sheet = workbook.CreateSheet(String.Format("Sheet{0}", count));
var row = sheet.CreateRow(0);
for (var i = 0; i < properties.Count; i++)
{
var cell = row.CreateCell(i);
cell.SetCellValue(String.IsNullOrEmpty(properties[i].Description)
? properties[i].Name
: properties[i].Description);
}
for (var i = 0; i < list.Count; i++)
{
row = sheet.CreateRow(i + 1);
for (var j = 0; j < properties.Count; j++)
{
var cell = row.CreateCell(j);
var value = properties[j].GetValue(list[i]);
cell.SetCellValue(value == null ? String.Empty
: (value is DateTime ? ((DateTime)value).ToString("yyyy-MM-dd HH:mm") : value.ToString()));
}
}
}
count++;
}
var dir =
HttpContext.Current.Server.MapPath(String.Format("~/Resources/{0}/", DateTime.Now.ToString("yyyyMMdd")));
if (!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
var fileName = dir + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
var fileStream = new FileStream(fileName, FileMode.Create, FileAccess.Write);
workbook.Write(fileStream);
return fileName;
}
Datatable 数据源:
public static string RenderDataTableToExcel(DataTable SourceTable)
{
XSSFWorkbook workbook = null;
ISheet sheet = null;
XSSFRow headerRow = null;
try
{
workbook = new XSSFWorkbook();
sheet = workbook.CreateSheet();
headerRow = (XSSFRow)sheet.CreateRow(0);
foreach (DataColumn column in SourceTable.Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
int rowIndex = 1;
foreach (DataRow row in SourceTable.Rows)
{
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in SourceTable.Columns)
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
++rowIndex;
}
//列宽自适应,只对英文和数字有效
for (int i = 0; i <= SourceTable.Columns.Count; ++i)
{
sheet.AutoSizeColumn(i);
}
var dir =
HttpContext.Current.Server.MapPath(String.Format("~/Resources/{0}/", DateTime.Now.ToString("yyyyMMdd")));
if (!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
var fileName = dir + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
var fileStream = new FileStream(fileName, FileMode.Create, FileAccess.Write);
workbook.Write(fileStream);
return fileName;
}
catch (Exception ex)
{
return null;
}
finally
{
sheet = null;
headerRow = null;
workbook = null;
}
}
前台代码:
public ActionResult ExportExecl(int? pid)
{
if (!pid.HasValue) return null;
var list = _userinfoBo.GetAnswerByUser(pid.Value);
if (null == list) return null;
var newList = list.Select(question => new ResultExecl()
{
//加载数据
}).ToList();
var filePath = ExcelUtil.Export2007(newList);
var fileName = Path.GetFileName(filePath);
return File(filePath, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
}