352 lines
14 KiB
C#
352 lines
14 KiB
C#
using NPOI.HSSF.UserModel;
|
||
using NPOI.SS.UserModel;
|
||
using System;
|
||
using System.Collections.Generic;
|
||
using System.ComponentModel;
|
||
using System.Data;
|
||
using System.IO;
|
||
using System.Reflection;
|
||
using System.Web;
|
||
|
||
namespace WX.CRM.Common
|
||
{
|
||
public class ExportInfo<T>
|
||
{
|
||
public string sheetName { get; set; }
|
||
public int sheetSize { get; set; }
|
||
public List<T> listModel { get; set; }
|
||
}
|
||
public class ExcelHandler
|
||
{
|
||
/// <summary>
|
||
/// List反射导出Excel
|
||
/// </summary>
|
||
/// <typeparam name="T"></typeparam>
|
||
/// <param name="listModel">实体列表</param>
|
||
/// <param name="sheetName">工作表名称</param>
|
||
/// <param name="sheetSize">一个工作簿的数据量（不能超过65536）</param>
|
||
/// <returns>返回一个流</returns>
|
||
public static Stream ExportListModelToExcelMoreListModel<T>(List<ExportInfo<T>> infoList)
|
||
{
|
||
HSSFWorkbook workbook = new HSSFWorkbook();
|
||
MemoryStream ms = new MemoryStream();
|
||
string columnName = string.Empty;
|
||
int x = 0;
|
||
DisplayNameAttribute attribute = null;
|
||
foreach (var item in infoList)
|
||
{
|
||
var sheetSize = item.sheetSize;
|
||
var sheetName = item.sheetName;
|
||
var listModel = item.listModel;
|
||
|
||
if (sheetSize > 65536)
|
||
sheetSize = 65536;
|
||
int sheetCout = 1;//获取一个工作簿中多少页
|
||
for (int i = 0; i < sheetCout; i++)
|
||
{
|
||
ISheet sheet = workbook.CreateSheet(sheetName);
|
||
IRow headerRow = sheet.CreateRow(0);
|
||
// 设置表头.
|
||
|
||
x = 0;
|
||
foreach (PropertyInfo info in typeof(T).GetProperties())
|
||
{
|
||
attribute = (DisplayNameAttribute)Attribute.GetCustomAttribute(info, typeof(DisplayNameAttribute));
|
||
headerRow.CreateCell(x).SetCellValue(attribute == null ? info.Name : attribute.DisplayName);//获取类中的DisplayName属性
|
||
|
||
x++;
|
||
}
|
||
// 绑定值
|
||
int rowIndex = 1;
|
||
int mcount = (i + 1) * sheetSize > listModel.Count ? listModel.Count : (i + 1) * sheetSize;
|
||
for (int m = (i * sheetSize); m < mcount; m++)
|
||
{
|
||
x = 0;
|
||
IRow dataRow = sheet.CreateRow(rowIndex);
|
||
foreach (PropertyInfo column in listModel[m].GetType().GetProperties())
|
||
{
|
||
//dataRow.CreateCell(x).SetCellValue(string.Format("{0}", column.GetValue(listModel[m], null)));
|
||
|
||
//dataRow.CreateCell(x).SetCellValue(string.Format("{0}", column.GetValue(listModel[m], null)));
|
||
ICell icell = dataRow.CreateCell(x);
|
||
ICellValeSet(column.PropertyType.FullName, string.Format("{0}", column.GetValue(listModel[m], null)), icell);
|
||
|
||
x++;
|
||
}
|
||
rowIndex++;
|
||
}
|
||
|
||
}
|
||
}
|
||
workbook.Write(ms);
|
||
ms.Flush();
|
||
ms.Position = 0;
|
||
workbook = null;
|
||
return ms;
|
||
}
|
||
|
||
/// <summary>
|
||
/// List反射导出Excel
|
||
/// </summary>
|
||
/// <typeparam name="T"></typeparam>
|
||
/// <param name="listModel">实体列表</param>
|
||
/// <param name="sheetName">工作表名称</param>
|
||
/// <param name="sheetSize">一个工作簿的数据量（不能超过65536）</param>
|
||
/// <returns>返回一个流</returns>
|
||
public static Stream ExportListModelToExcel<T>(List<T> listModel, string sheetName, int sheetSize)
|
||
{
|
||
HSSFWorkbook workbook = new HSSFWorkbook();
|
||
MemoryStream ms = new MemoryStream();
|
||
string columnName = string.Empty;
|
||
int x = 0;
|
||
DisplayNameAttribute attribute = null;
|
||
if (sheetSize > 65536)
|
||
sheetSize = 65536;
|
||
int sheetCout = (listModel.Count / sheetSize) + 1;//获取一个工作簿中多少页
|
||
for (int i = 0; i < sheetCout; i++)
|
||
{
|
||
ISheet sheet = workbook.CreateSheet(sheetName + (i + 1));
|
||
IRow headerRow = sheet.CreateRow(0);
|
||
// 设置表头.
|
||
|
||
x = 0;
|
||
foreach (PropertyInfo info in typeof(T).GetProperties())
|
||
{
|
||
attribute = (DisplayNameAttribute)Attribute.GetCustomAttribute(info, typeof(DisplayNameAttribute));
|
||
headerRow.CreateCell(x).SetCellValue(attribute == null ? info.Name : attribute.DisplayName);//获取类中的DisplayName属性
|
||
|
||
x++;
|
||
}
|
||
// 绑定值
|
||
int rowIndex = 1;
|
||
int mcount = (i + 1) * sheetSize > listModel.Count ? listModel.Count : (i + 1) * sheetSize;
|
||
for (int m = (i * sheetSize); m < mcount; m++)
|
||
{
|
||
x = 0;
|
||
IRow dataRow = sheet.CreateRow(rowIndex);
|
||
foreach (PropertyInfo column in listModel[m].GetType().GetProperties())
|
||
{
|
||
//dataRow.CreateCell(x).SetCellValue(string.Format("{0}", column.GetValue(listModel[m], null)));
|
||
|
||
//dataRow.CreateCell(x).SetCellValue(string.Format("{0}", column.GetValue(listModel[m], null)));
|
||
ICell icell = dataRow.CreateCell(x);
|
||
ICellValeSet(column.PropertyType.FullName, string.Format("{0}", column.GetValue(listModel[m], null)), icell);
|
||
|
||
x++;
|
||
}
|
||
rowIndex++;
|
||
}
|
||
|
||
}
|
||
workbook.Write(ms);
|
||
ms.Flush();
|
||
ms.Position = 0;
|
||
workbook = null;
|
||
return ms;
|
||
}
|
||
|
||
/// <summary>
|
||
/// 由DataSet导出Excel
|
||
/// </summary>
|
||
/// <param name="sourceDs"></param>
|
||
/// <param name="fileName">指定Excel工作表名称</param>
|
||
/// <param name="sheetName"></param>
|
||
/// <returns>Excel工作表</returns>
|
||
public static void ExportDataSetToExcel(DataSet sourceDs, string fileName, string sheetName)
|
||
{
|
||
|
||
MemoryStream ms = ExportDataSetToExcel(sourceDs, sheetName) as MemoryStream;
|
||
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
|
||
HttpContext.Current.Response.BinaryWrite(ms.ToArray());
|
||
HttpContext.Current.Response.End();
|
||
ms.Close();
|
||
ms = null;
|
||
}
|
||
/// <summary>
|
||
/// 由DataSet导出Excel
|
||
/// </summary>
|
||
/// <param name="sourceDs">数据源</param>
|
||
/// <param name="folderPath">文件夹名</param>
|
||
/// <param name="fileName">文件名</param>
|
||
/// <param name="sheetName">指定Excel工作表名称</param>
|
||
public static void SaveDataSetToExcel(DataSet sourceDs, string folderPath, string fileName, string sheetName)
|
||
{
|
||
Stream stream = ExportDataSetToExcel(sourceDs, sheetName);
|
||
FileUnit.WriteWithDirectory(folderPath, fileName, stream);
|
||
}
|
||
/// <summary>
|
||
/// 由DataSet导出Excel
|
||
/// </summary>
|
||
/// <param name="sourceTable">要导出数据的DataTable</param>
|
||
/// <param name="sheetName">工作表名称</param>
|
||
/// <returns>Excel工作表</returns>
|
||
private static Stream ExportDataSetToExcel(DataSet sourceDs, string sheetName)
|
||
{
|
||
HSSFWorkbook workbook = new HSSFWorkbook();
|
||
MemoryStream ms = new MemoryStream();
|
||
string[] sheetNames = sheetName.Split(',');
|
||
int sheetSize = 65530;
|
||
int rowCount = sourceDs.Tables[0].Rows.Count;
|
||
int sheetCout = (rowCount / sheetSize) + 1;
|
||
for (int i = 0; i < sheetNames.Length; i++)
|
||
{
|
||
for (int j = 0; j < sheetCout; j++)
|
||
{
|
||
ISheet sheet = workbook.CreateSheet(sheetNames[i] + j);
|
||
IRow headerRow = sheet.CreateRow(0);
|
||
// handling header.
|
||
foreach (DataColumn column in sourceDs.Tables[i].Columns)
|
||
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
|
||
// handling value.
|
||
int rowIndex = 1;
|
||
int mcount = (j + 1) * sheetSize > rowCount ? rowCount : (j + 1) * sheetSize;
|
||
for (int m = (j * sheetSize); m < mcount; m++)
|
||
{
|
||
DataRow row = sourceDs.Tables[i].Rows[m];
|
||
IRow dataRow = sheet.CreateRow(rowIndex);
|
||
foreach (DataColumn column in sourceDs.Tables[i].Columns)
|
||
{
|
||
ICell icell = dataRow.CreateCell(column.Ordinal);
|
||
ICellValeSet(column.DataType.FullName, row[column].ToString(), icell);
|
||
//dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
|
||
}
|
||
rowIndex++;
|
||
}
|
||
}
|
||
}
|
||
workbook.Write(ms);
|
||
ms.Flush();
|
||
ms.Position = 0;
|
||
workbook = null;
|
||
return ms;
|
||
}
|
||
private static void ICellValeSet(string dbType, string dbValue, ICell icell)
|
||
{
|
||
switch (GetCellType(dbType))
|
||
{
|
||
case stylexls.数字:
|
||
int wovalue = 0;
|
||
if (int.TryParse(dbValue, out wovalue))
|
||
{
|
||
icell.SetCellValue(wovalue);
|
||
}
|
||
else
|
||
{
|
||
icell.SetCellValue(dbValue);
|
||
}
|
||
break;
|
||
case stylexls.小数:
|
||
double dobValue = 0;
|
||
if (double.TryParse(dbValue, out dobValue))
|
||
{
|
||
icell.SetCellValue(dobValue);
|
||
}
|
||
else
|
||
{
|
||
icell.SetCellValue(dbValue);
|
||
}
|
||
break;
|
||
case stylexls.时间:
|
||
DateTime timeValue;
|
||
if (DateTime.TryParse(dbValue, out timeValue))
|
||
{
|
||
icell.SetCellValue(timeValue.ToString("yyyy/MM/dd HH:mm:ss"));
|
||
}
|
||
else
|
||
{
|
||
icell.SetCellValue("");
|
||
}
|
||
break;
|
||
case stylexls.默认: icell.SetCellValue(dbValue.ToString()); break;
|
||
}
|
||
}
|
||
private static void ICellValeSet(ExcelCell cell, ICell icell)
|
||
{
|
||
switch (GetCellType(cell.type))
|
||
{
|
||
case stylexls.数字:
|
||
int wovalue = 0;
|
||
if (int.TryParse(cell.nvalue, out wovalue))
|
||
{
|
||
icell.SetCellValue(wovalue);
|
||
}
|
||
else
|
||
{
|
||
icell.SetCellValue(cell.nvalue);
|
||
}
|
||
break;
|
||
case stylexls.小数:
|
||
double dobValue = 0;
|
||
if (double.TryParse(cell.nvalue, out dobValue))
|
||
{
|
||
icell.SetCellValue(dobValue);
|
||
}
|
||
else
|
||
{
|
||
icell.SetCellValue(cell.nvalue);
|
||
}
|
||
break;
|
||
case stylexls.时间:
|
||
DateTime timeValue;
|
||
if (DateTime.TryParse(cell.nvalue, out timeValue))
|
||
{
|
||
icell.SetCellValue(timeValue.ToString("yyyy/MM/dd HH:mm:ss"));
|
||
}
|
||
else
|
||
{
|
||
icell.SetCellValue("");
|
||
}
|
||
break;
|
||
case stylexls.默认: icell.SetCellValue(cell.nvalue.ToString()); break;
|
||
}
|
||
}
|
||
private static stylexls GetCellType(string fullTypeName)
|
||
{
|
||
if (fullTypeName == null)
|
||
return stylexls.默认;
|
||
if (fullTypeName.IndexOf("System.Decimal") > -1)
|
||
return stylexls.小数;
|
||
if (fullTypeName.IndexOf("System.Int") > -1)
|
||
return stylexls.数字;
|
||
if (fullTypeName.IndexOf("System.DateTime") > -1)
|
||
return stylexls.时间;
|
||
return stylexls.默认;
|
||
}
|
||
}
|
||
#region 定义单元格常用到样式的枚举
|
||
public enum stylexls
|
||
{
|
||
头,
|
||
url,
|
||
时间,
|
||
数字,
|
||
小数,
|
||
钱,
|
||
百分比,
|
||
中文大写,
|
||
科学计数法,
|
||
默认
|
||
}
|
||
#endregion
|
||
public class ExcelCell
|
||
{
|
||
/// <summary>
|
||
/// 英文字段名称
|
||
/// </summary>
|
||
public string fild { get; set; }
|
||
/// <summary>
|
||
/// 字段的数据类型
|
||
/// </summary>
|
||
public string type { get; set; }
|
||
/// <summary>
|
||
/// 字段的中文标题
|
||
/// </summary>
|
||
public string title { get; set; }
|
||
/// <summary>
|
||
/// 字段的值
|
||
/// </summary>
|
||
public string nvalue { get; set; }
|
||
|
||
}
|
||
}
|