using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using System.Data;
using System.ComponentModel;
using System.Web.Mvc;
namespace Util
{
public class ExcelHelper
{
public static class ExportTool<T>
{
/// <summary>
/// List<T>转化为Excel文件,并返回FileStreamResult
/// </summary>
/// <param name="list">需要转化的List<T></param>
/// <param name="headerList">Excel标题行的List列表</param>
/// <param name="fileName">Excel的文件名</param>
/// <returns></returns>
public static FileStreamResult ExportListToExcel_MVCResult(IList<T> list, IList<String> headerList, String fileName)
{
FileStreamResult fsr = new FileStreamResult(ExportListToExcel(list, headerList, null), "application/ms-excel");
fsr.FileDownloadName = HttpUtility.UrlEncode(fileName + ".xls");
return fsr;
}
/// <summary>
/// List<T>转化为Excel文件,并返回FileStreamResult
/// </summary>
/// <param name="list">需要转化的List<T></param>
/// <param name="headerList">Excel标题行的List列表</param>
/// <param name="fileName">Excel的文件名</param>
/// <param name="sortList">指定导出List<T>中哪些属性,并按顺序排序</param>
/// <returns></returns>
public static FileStreamResult ExportListToExcel_MVCResult(IList<T> list, IList<String> headerList, String fileName, IList<String> sortList)
{
FileStreamResult fsr = new FileStreamResult(ExportListToExcel(list, headerList, sortList), "application/ms-excel");
fsr.FileDownloadName = HttpUtility.UrlEncode(fileName + ".xls");
return fsr;
}
public static MemoryStream ExportListToExcel(IList<T> list, IList<String> headerList, IList<String> sortList)
{
try
{
//文件流对象
//FileStream file = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
MemoryStream stream = new MemoryStream();
//打开Excel对象
HSSFWorkbook workbook = new HSSFWorkbook();
//Excel的Sheet对象
NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet("sheet1");
//set date format
CellStyle cellStyleDate = workbook.CreateCellStyle();
DataFormat format = workbook.CreateDataFormat();
cellStyleDate.DataFormat = format.GetFormat("yyyy年m月d日");
//使用NPOI操作Excel表
NPOI.SS.UserModel.Row row = sheet.CreateRow(0);
int count = 0;
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
//if (headerList != null && properties.Count != headerList.Count)
// throw new Exception("集合的属性个数和标题行List的个数不一致");
//如果没有自定义的行首,那么采用反射集合的属性名做行首
if (headerList == null)
{
for (int i = 0; i < properties.Count; i++) //生成sheet第一行列名
{
NPOI.SS.UserModel.Cell cell = row.CreateCell(count++);
cell.SetCellValue(String.IsNullOrEmpty(properties[i].DisplayName) ? properties[i].Name : properties[i].DisplayName);
}
}
else
{
for (int i = 0; i < headerList.Count; i++) //生成sheet第一行列名
{
NPOI.SS.UserModel.Cell cell = row.CreateCell(count++);
cell.SetCellValue(headerList[i]);
}
}
//将数据导入到excel表中
for (int i = 0; i < list.Count; i++)
{
NPOI.SS.UserModel.Row rows = sheet.CreateRow(i + 1);
count = 0;
object value = null;
//如果自定义导出属性及排序字段为空,那么走反射序号的方式
if (sortList == null)
{
for (int j = 0; j < properties.Count; j++)
{
NPOI.SS.UserModel.Cell cell = rows.CreateCell(count++);
value = properties[j].GetValue(list[i]);
cell.SetCellValue(value == null ? String.Empty : value.ToString());
}
}
else
{
for (int j = 0; j < sortList.Count; j++)
{
NPOI.SS.UserModel.Cell cell = rows.CreateCell(count++);
value = properties[sortList[j]].GetValue(list[i]);
cell.SetCellValue(value == null ? String.Empty : value.ToString());
}
}
}
//保存excel文档
sheet.ForceFormulaRecalculation = true;
workbook.Write(stream);
workbook.Dispose();
stream.Seek(0, SeekOrigin.Begin);
return stream;
}
catch
{
return new MemoryStream();
}
}
/// <summary>
/// 将DataSet数据集转换HSSFworkbook对象，并保存为Stream流
/// </summary>
/// <param name="ds"></param>
/// <returns>返回数据流Stream对象</returns>
public static MemoryStream ExportDatasetToExcel(DataSet ds)
{
try
{
//文件流对象
//FileStream file = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
MemoryStream stream = new MemoryStream();
//打开Excel对象
HSSFWorkbook workbook = new HSSFWorkbook();
//Excel的Sheet对象
NPOI.SS.UserModel.Sheet sheet = workbook.CreateSheet("sheet1");
var cellFont = workbook.CreateFont();
var cellStyle = workbook.CreateCellStyle();
//- 加粗，白色前景色
cellFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;
//- 这个是填充的模式，可以是网格、花式等。如果需要填充单色，请使用：SOLID_FOREGROUND
//cellStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND;
//- 设置这个样式的字体，如果没有设置，将与所有单元格拥有共同字体！
cellStyle.SetFont(cellFont);
cellStyle.Alignment = HorizontalAlignment.CENTER;
//set date format
CellStyle cellStyleDate = workbook.CreateCellStyle();
DataFormat format = workbook.CreateDataFormat();
cellStyleDate.DataFormat = format.GetFormat("yyyy年m月d日");
//使用NPOI操作Excel表
NPOI.SS.UserModel.Row row = sheet.CreateRow(0);
int count = 0;
for (int i = 0; i < ds.Tables[0].Columns.Count; i++) //生成sheet第一行列名
{
NPOI.SS.UserModel.Cell cell = row.CreateCell(count++);
cell.SetCellValue(ds.Tables[0].Columns[i].Caption);
cell.CellStyle = cellStyle;
}
//将数据导入到excel表中
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
NPOI.SS.UserModel.Row rows = sheet.CreateRow(i + 1);
count = 0;
for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
{
NPOI.SS.UserModel.Cell cell = rows.CreateCell(count++);
Type type = ds.Tables[0].Rows[i][j].GetType();
if (type == typeof(int) || type == typeof(Int16)
|| type == typeof(Int32) || type == typeof(Int64))
{
cell.SetCellValue((int)ds.Tables[0].Rows[i][j]);
}
else
{
if (type == typeof(float) || type == typeof(double) || type == typeof(Double))
{
cell.SetCellValue((Double)ds.Tables[0].Rows[i][j]);
}
else
{
if (type == typeof(DateTime))
{
cell.SetCellValue(((DateTime)ds.Tables[0].Rows[i][j]).ToString("yyyy-MM-dd HH:mm"));
}
else
{
if (type == typeof(bool) || type == typeof(Boolean))
{
cell.SetCellValue((bool)ds.Tables[0].Rows[i][j]);
}
else
{
cell.SetCellValue(ds.Tables[0].Rows[i][j].ToString());
}
}
}
}
}
}
//保存excel文档
sheet.ForceFormulaRecalculation = true;
workbook.Write(stream);
workbook.Dispose();
return stream;
}
catch
{
return new MemoryStream();
}
}
}
}
}
