public class ExcelExportUtils
{
/// <summary>
/// 页面导出Excel
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="fileName">导出的文件名</param>
/// <param name="list">数据</param>
/// <param name="expItems">导出的列</param>
/// <param name="fileNameAddDate">文件名自动添加导出日期</param>
public static void ExportExcel<T>(string fileName, IList<T> list, ICollection<ExcelItem> expItems, bool fileNameAddDate = true)
{
if (list != null && list.Count > 0)
{
var dt = DataSetModel.FillDataTable<T>(list);
ExportExcel(fileName, dt, expItems, fileNameAddDate);
}
}
/// <summary>
/// 页面导出Excel
/// </summary>
/// <param name="fileName">导出的文件名</param>
/// <param name="dt">dt</param>
/// <param name="expItems">expItems</param>
/// <param name="fileNameAddDate">文件名是否追加日期</param>
/// <example>
/// var dt = DataSetModel.FillDataTable(list);
/// string fileName = "账户";
/// var expItems = new List《ExportItem》();
/// expItems.Add(new ExportItem("编号", "AppId"));
/// expItems.Add(new ExportItem("名称", "AppName"));
/// expItems.Add(new ExportItem("余额", "ScoreBalance"));
/// expItems.Add(new ExportItem("状态", "State", AppState));
/// expItems.Add(new ExportItem("创建时间", "CreateTime"));
/// base.ExportExcel(fileName, dt, expItems);
/// </example>
public static void ExportExcel(string fileName, DataTable dt, ICollection<ExcelItem> expItems, bool fileNameAddDate = true)
{
HttpContext context = System.Web.HttpContext.Current;
bool isFireFox = context.Request.ServerVariables["http_user_agent"].ToLower().IndexOf("firefox") > -1 ? true : false;
if (dt.Rows.Count > 0)
{
//移除不需要导出的列
var delCol = new List<string>();
foreach (DataColumn col in dt.Columns)
{
var flag = expItems.Any(m => m.DataName.ToLower() == col.ColumnName.ToLower());
if (!flag) delCol.Add(col.ColumnName);
}
delCol.ForEach(m=>dt.Columns.Remove(m));
if (fileNameAddDate == true)
{
fileName += DateTime.Now.ToString("yyyyMMddHHmmss");
}
// 设置编码和附件格式
context.Response.ContentType = "application/vnd.ms-excel";
context.Response.ContentEncoding = Encoding.UTF8;
context.Response.Charset = "";
if (isFireFox)
{
context.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + """ + fileName + ".xls" + """);
}
else
{
context.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8) + ".xls");
}
context.Response.BinaryWrite(Export(dt, expItems, "1").GetBuffer());
context.ApplicationInstance.CompleteRequest();
dt.Clear();
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="expItems">表头文本</param>
/// <param name="sheetName">工作薄名称</param>
/// <returns>返回</returns>
private static MemoryStream Export(DataTable dtSource, ICollection<ExcelItem> expItems, string sheetName)
{
//设置导出列名
StringBuilder headers = new StringBuilder();
foreach (var col in expItems)
{
headers.Append(col.Title);
headers.Append(",");
}
headers.Remove(headers.Length - 1, 1);
#region 设置excel属性
HSSFWorkbook workbook = new HSSFWorkbook();
//设置工作薄名称
ISheet sheet = workbook.CreateSheet(sheetName);
sheet.TabColorIndex = HSSFColor.Red.Index;
//右击文件 属性信息
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "公司";
dsi.Category = "业务导出";//类别
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "技术支持部"; //填加xls文件作者信息
si.ApplicationName = "服务平台"; //填加xls文件创建程序信息
si.LastAuthor = "技术支持部"; //填加xls文件最后保存者信息
si.Comments = "如有疑问请询问在线客服"; //填加xls文件作者信息
si.Title = headers.ToString(); //填加xls文件标题信息
si.Subject = "业务导出";//填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
#endregion
//时间格式化格式
HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle;
HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd HH:mm:ss");
//取得列宽
int[] arrColWidth = new int[expItems.Count];
for (int i = 0; i < dtSource.Rows.Count; i++)
{
int j = 0;
foreach (var item in expItems)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][item.DataName].ToString()).Length;
if (intTemp > arrColWidth[j])
arrColWidth[j] = intTemp;
if (arrColWidth[j] < 7) arrColWidth[j] = 7;
j++;
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet() as HSSFSheet;
}
//列头及样式
//把列头提到第一行
//HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow;
HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle;
headStyle.Alignment = HorizontalAlignment.Center;
HSSFFont font = workbook.CreateFont() as HSSFFont;
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.IsLocked = true;
headStyle.SetFont(font);
//设置每列的文字
int i = 0;
foreach (var item in expItems)
{
//headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.CreateCell(i).SetCellValue(item.Title);
headerRow.GetCell(i).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
i++;
}
//让列头不动
//sheet.CreateFreezePane(0, 2, 0, dtSource.Columns.Count - 1);
sheet.CreateFreezePane(0, 1, 0, 1);
//把内容提到第二行
// rowIndex = 2;
rowIndex = 1;
}
#endregion
//填充内容
HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
var j = 0;
foreach (var item in expItems)
{
HSSFCell newCell = dataRow.CreateCell(j) as HSSFCell;
string drValue;
Type type;
if (item.HasConverter)
{
drValue = item.Converter(row[item.DataName]);
type = typeof(string);
}
else
{
drValue = row[item.DataName].ToString();
type = dtSource.Columns[item.DataName].DataType;
}
switch (type.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
if(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;
}
j++;
}
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return ms;
}
}
}
使用
ICollection<ExcelItem> expItems = new List<ExcelItem>();
expItems.Add(new ExcelItem("用户名", "UserName"));
ExcelExportUtils.ExportExcel("用户", list, expItems);