把数据导出excel的应用很广泛，如果使用依赖于excel的com方法，则难度就很大，而且还必须安装excel，否则就不能导出。使用NPOI导出excel很简单，只需要添加一个程序集NPOI.dll的引用就可以，而且不依赖于excel，也就是不需要安装excel。自然比依赖于excel的com方法好很多。
核心代码
public static void ExportByWeb(List<JqueryEasyuiTreeGridNode> list, string strFileName)
{
strFileName = DateTime.Now.ToString("yyyyMMddHHmmss") + "-" + strFileName + "-.xls";
//using (MemoryStream ms = Export(list,false))//生成文件
//{
// using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
// {
// byte[] data = ms.ToArray();
// fs.Write(data, 0, data.Length);
// fs.Flush();
// }
//}
HttpContext curContext = HttpContext.Current;//web系统的下载
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
curContext.Response.BinaryWrite(Export(list).GetBuffer());
curContext.Response.End();
}
private static MemoryStream Export(List<JqueryEasyuiTreeGridNode> list)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();//创建Sheet页
List<string> ColumnNames = new List<string>();
ColumnNames.Add("产品编号");
ColumnNames.Add("产品线名称");
ColumnNames.Add("可否独立运行");
ColumnNames.Add("基本功能价格(元)");
ColumnNames.Add("高级功能价格(元)");
ColumnNames.Add("合计(元)");
//取得列宽
int[] arrColumnWidth = new int[ColumnNames.Count];
int count = 0;
foreach (string item in ColumnNames)
{
arrColumnWidth[count] = System.Text.Encoding.GetEncoding(936).GetBytes(item).Length * 2;
count++;
}
int rowIndex = 0;
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (HSSFSheet)workbook.CreateSheet();
}
#region 表头及样式
AddTitle(workbook, sheet, ColumnNames.Count, "产品线报价汇总");
#endregion
#region 列头及样式
AddColumnTitle(workbook, sheet, ColumnNames, arrColumnWidth);
#endregion
rowIndex = 2;
}
#endregion
WriteDataRows(list, workbook, ColumnNames.Count, sheet, rowIndex);
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return ms;
}
}
/// <summary>
/// 添加第一行的标题
/// </summary>
/// <param name="workbook"></param>
/// <param name="sheet"></param>
/// <param name="maxColumnCount"></param>
/// <param name="strHeaderText"></param>
private static void AddTitle(HSSFWorkbook workbook, HSSFSheet sheet, int maxColumnCount, string strHeaderText)
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);//添加行
headerRow.HeightInPoints = 25;//设置高度字体
headerRow.CreateCell(0).SetCellValue(strHeaderText);//设置单元格内容
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();//设置单元格样式
headStyle.Alignment = HorizontalAlignment.CENTER;//文字居中
HSSFFont font = (HSSFFont)workbook.CreateFont();//设置字体
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.Region(0, 0, 0, maxColumnCount - 1));//合并单元格
}
private static void AddColumnTitle(HSSFWorkbook workbook, HSSFSheet sheet, List<string> ColumnNames, int[] arrColumnWidth)
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;//文字居中
headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GOLD.index;//设置前景色
headStyle.FillPattern = FillPatternType.ALT_BARS;//
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
int count = 0; ;
foreach (string item in ColumnNames)
{
headerRow.CreateCell(count).SetCellValue(item);
headerRow.GetCell(count).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(count, (arrColumnWidth[count] + 1) * 256);//设置列宽度
count++;
}
}
private static List<JqueryEasyuiTreeGridNode> WriteDataRows(List<JqueryEasyuiTreeGridNode> parentList, HSSFWorkbook workbook, int maxColumnCount, HSSFSheet sheet, int rowIndex)
{
List<JqueryEasyuiTreeGridNode> listnext = new List<JqueryEasyuiTreeGridNode>();
CellStyle style2 = workbook.CreateCellStyle();//设置单元格的样式边框
style2.BorderBottom = CellBorderType.THIN;//
style2.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index;
style2.BorderLeft = CellBorderType.THIN;
style2.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index;
style2.BorderRight = CellBorderType.THIN;
style2.RightBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index;
style2.BorderTop = CellBorderType.THIN;
style2.TopBorderColor = NPOI.HSSF.Util.HSSFColor.BLACK.index;
style2.WrapText = true;//根据文本自动调整高度
// HSSFDataFormat formatDate = (HSSFDataFormat)workbook.CreateDataFormat();
// style2.DataFormat = formatDate.GetFormat("yyyy-mm-dd");
// HSSFDataFormat formatMoney = (HSSFDataFormat)workbook.CreateDataFormat();
// style2.DataFormat = formatMoney.GetFormat("#,##0_");
foreach (JqueryEasyuiTreeGridNode parent in parentList)
{
JqueryEasyuiTreeGridNode row = parent;
int count = 0;
#region 填充内容
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
count = 0;
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(count);
newCell.CellStyle = style2;
string drValue = row.PL_Code;
newCell.SetCellValue(drValue);
count++;
newCell = (HSSFCell)dataRow.CreateCell(count);
newCell.CellStyle = style2;
drValue = row.PL_Name;
newCell.SetCellValue(drValue);
count++;
newCell = (HSSFCell)dataRow.CreateCell(count);
newCell.CellStyle = style2;
drValue = row.PL_RunFlag;
newCell.SetCellValue(drValue);
count++;
newCell = (HSSFCell)dataRow.CreateCell(count);
newCell.CellStyle = style2;
newCell.SetCellType(CellType.NUMERIC);
if (row.PL_PRICEDesc.HasValue)
{
newCell.SetCellValue(row.PL_PRICEDesc.Value);
}
else
{
newCell.SetCellValue("");
}
count++;
newCell = (HSSFCell)dataRow.CreateCell(count);
newCell.CellStyle = style2;
newCell.SetCellType(CellType.NUMERIC);
if (row.P_PriceHigh.HasValue)
{
newCell.SetCellValue(row.P_PriceHigh.Value);
}
else
{
newCell.SetCellValue("");
}
count++;
newCell = (HSSFCell)dataRow.CreateCell(count);
newCell.CellStyle = style2;
newCell.SetCellType(CellType.NUMERIC);
newCell.SetCellValue(row.Total_Price);
count++;
//}
#endregion
rowIndex++;
}
return listnext;
}
导出结果
更有通用性的代码
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
public static class ExportToFile<TModel>{
//测试代码1
/* BoBase<HouseType, HouseType> bll = new BoBase<HouseType, HouseType>();
List<HouseType> list=new List<HouseType> ();
list.AddRange(bll.GetList(string.Empty));
Dictionary<string, string> FieldNames=new Dictionary<string,string> ();
FieldNames.Add("HT_Code","宿舍类型编码");
FieldNames.Add("HT_ActiveFlag","状态");
FieldNames.Add("HT_Name","宿舍类型名称");
ExportToFile<HouseType>.Export(list, "测试", Path.Combine(AppDomain.CurrentDomain.BaseDirectory, DateTime.Now.ToString()+".xls"), FieldNames);
* */
//测试代码2
/* BoBase<SurveyPerformance, SurveyPerformance> bl2l = new BoBase<SurveyPerformance, SurveyPerformance>();
List<SurveyPerformance> list2 = new List<SurveyPerformance>();
list2.AddRange(bl2l.GetList(string.Empty));
Dictionary<string, string> FieldNames2 = new Dictionary<string, string>();
FieldNames2.Add("SP_Code", "级别编码");
FieldNames2.Add("SP_Name", "级别名称");
FieldNames2.Add("SP_Status", "级别状态");
FieldNames2.Add("SP_ID", "编号");
ExportToFile<SurveyPerformance>.Export(list2, "测试", Path.Combine(AppDomain.CurrentDomain.BaseDirectory, DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"), FieldNames2);
* * */
/// <summary>
/// DataTable导出到Excel文件
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="strFileName">保存位置</param>
/// <Author>柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41</Author>
public static void Export(List<TModel> list, string strHeaderText, string strFileName, Dictionary<string, string> FieldNames)
{
using (MemoryStream ms = Export(list, strHeaderText,FieldNames))
{
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
}
}
/// <summary>
/// 用于Web导出
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="strFileName">文件名</param>
/// <Author>柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41</Author>
public static void ExportByWeb(List<TModel> list, string strHeaderText, string strFileName, Dictionary<string, string> FieldNames)
{
HttpContext curContext = HttpContext.Current;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
curContext.Response.AppendHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
curContext.Response.BinaryWrite(Export(list, strHeaderText, FieldNames).GetBuffer());
curContext.Response.End();
}
private static MemoryStream Export(List<TModel> list, string strHeaderText, Dictionary<string, string> FieldNames)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[FieldNames.Count];
int count = 0;
foreach (KeyValuePair<string, string> item in FieldNames)
{
arrColWidth[count] = Encoding.GetEncoding(936).GetBytes(item.Value).Length;
count++;
}
int rowIndex = 0;
foreach (TModel row in list)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (HSSFSheet)workbook.CreateSheet();
}
#region 表头及样式
AddFirstRow(workbook, sheet, FieldNames, strHeaderText);
#endregion
#region 列头及样式
AddSecondRow(workbook, sheet, FieldNames, arrColWidth);
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
count = 0;
foreach (KeyValuePair<string, string> column in FieldNames)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(count);
PropertyInfo pinfo = typeof(TModel).GetProperty(column.Key);
string drValue = "";
string dateytype= "";
if (pinfo != null)
{
drValue = pinfo.GetValue(row, null) == null ? "" : pinfo.GetValue(row, null).ToString();
dateytype = pinfo.PropertyType.ToString();
}
switch (dateytype){
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
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":
case "System.Single":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue(drValue);
break;
}
count++;
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return ms;
}
}
private static void AddFirstRow(HSSFWorkbook workbook, HSSFSheet sheet, Dictionary<string, string> FieldNames, string strHeaderText)
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new Region(0, 0, 0, FieldNames.Count - 1));
}
private static void AddSecondRow(HSSFWorkbook workbook, HSSFSheet sheet, Dictionary<string, string> FieldNames, int[] arrColWidth)
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
int count = 0; ;
foreach (KeyValuePair<string, string> item in FieldNames)
{
// arrColWidth[count] = Encoding.GetEncoding(936).GetBytes(item.Value).Length;
headerRow.CreateCell(count).SetCellValue(item.Value);
headerRow.GetCell(count).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(count, (arrColWidth[count] + 1) * 256);
count++;
}
}
}
自适应列宽度：
sheet.autoSizeColumn(1); sheet.autoSizeColumn(1, true);
这两种方式都是自适应列宽度，但是注意这个方法在后边的版本才提供，poi的版本不要太老。 注意：第一个方法在合并单元格的的单元格并不好使，必须用第二个方法。
还有在自适应宽度的时候，有时候遇到单元格是公式单元格，自适应不起作用，那是因为单元格存的是公式，并不是真正的数据，解决方法：
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet.getWorkbook());
CellValue cell71Val = evaluator.evaluate(cell71);
cell71.setCellValue(cell71Val.getNumberValue());
将格式化后的数据再次set进去，就是真正的值了。
NPOI依赖程序集下载
扫一扫
165
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
