十年河东，十年河西，莫欺少年穷！
NPOI支持对 Word 和 Excel 文件的操作！
针对 Word 的操作一般用于打印技术！说白了就是利用 Word 文件作为模板，生成各种不同的打印！具体用到的技术是：Word 关键字替换操作，Word 图片插入操作， Word 表格填充操作，Word 图表生成操作等等，在此就不一一介绍了，有兴趣的小虎斑可以参考鄙人博客：专业 web 打印组件 及 C# web项目利用docx文档作为模板~为打印专做的解决方案
针对 Excel 文件的操作一般常用的就两种：
1、将数据库数据导入至Excel
2、将 Excel 中数据导入至数据库
当然，针对Excel文件的操作也有其他优秀的组件实现，譬如：Spire.XLS，关于这个组件的运用，大家可参考我的博客：Spire.XLS，生成Excel文件、加载Excel文件
好了，说了这么多，咱们进入正题：
其实说到正题，也没什么可说的，都是一些代码，会调用就行了。
源代码如下：
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Eval;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web;namespace KMHC.Infrastructure.Excel
{
public class ExcelHelper
{
#region DataTable导出至Excel
/// <summary>
/// 导出至Excel
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="templateName">Xls文件名 仅支持Xls扩展名</param>
/// <returns></returns>
public static string LoadForToExcel(DataTable dt, string templateName)
{
string mapPath = HttpContext.Current.Server.MapPath(VirtualPathUtility.GetDirectory("~"));
string path = string.Format(@"{0}Templates\{1}.xls", mapPath, templateName);
GridToExcelByNPOI(dt, path);
return path;
}
/// <summary>
/// 导出至Excel具体实现
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="strExcelFileName">文件路径</param>
private static void GridToExcelByNPOI(DataTable dt, string strExcelFileName )
{
HSSFWorkbook workbook = new HSSFWorkbook();
try
{
ISheet sheet = workbook.CreateSheet("Sheet1"); ICellStyle HeadercellStyle = workbook.CreateCellStyle();
HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//字体
NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
HeadercellStyle.SetFont(headerfont); //用column name 作为列名
int icolIndex = ;
IRow headerRow = sheet.CreateRow();
foreach (DataColumn item in dt.Columns)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(item.ColumnName);
cell.CellStyle = HeadercellStyle;
icolIndex++;
} ICellStyle cellStyle = workbook.CreateCellStyle(); //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont); //建立内容行
int iRowIndex = ;
int iCellIndex = ;
foreach (DataRow Rowitem in dt.Rows)
{
IRow DataRow = sheet.CreateRow(iRowIndex);
foreach (DataColumn Colitem in dt.Columns)
{ ICell cell = DataRow.CreateCell(iCellIndex);
cell.SetCellValue(Rowitem[Colitem].ToString());
cell.CellStyle = cellStyle;
iCellIndex++;
}
iCellIndex = ;
iRowIndex++;
} //自适应列宽度
for (int i = ; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
} //写Excel
FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate);
workbook.Write(file);//将Excel文件保存到项目中
file.Flush();
file.Close(); //Log.WriteLog("导出Excel成功！");
}
catch (Exception ex)
{
//Log.WriteLog("导出Excel异常：", ex);
}
finally
{
workbook = null;
} }
#endregion #region Excel文件数据导出至DataTable
/// <summary>
/// Excel数据导出至DataTable
/// </summary>
/// <param name="templateName">文件名</param>
/// <param name="strTableName"></param>
/// <param name="iSheetIndex">第几个Sheet的数据</param>
public static DataTable LoadForToDataTable(string templateName, string strTableName, int iSheetIndex)
{
string mapPath = HttpContext.Current.Server.MapPath(VirtualPathUtility.GetDirectory("~"));//获取项目根目录
string path = string.Format(@"{0}Templates\{1}.xls", mapPath, templateName);//Templates 构造根目录路径 Templates 是项目的一个文件夹
return XlSToDataTable(path, strTableName, iSheetIndex);
}
/// <summary>
/// Excel文件导成Datatable
/// </summary>
/// <param name="strFilePath">Excel文件目录地址</param>
/// <param name="strTableName">Datatable表名</param>
/// <param name="iSheetIndex">Excel sheet index</param>
/// <returns></returns>
private static DataTable XlSToDataTable(string strFilePath, string strTableName, int iSheetIndex)
{ string strExtName = Path.GetExtension(strFilePath); DataTable dt = new DataTable();
if (!string.IsNullOrEmpty(strTableName))
{
dt.TableName = strTableName;
} if (strExtName.Equals(".xls") || strExtName.Equals(".xlsx"))
{
using (FileStream file = new FileStream(strFilePath, FileMode.Open, FileAccess.Read))
{
HSSFWorkbook workbook = new HSSFWorkbook(file);
ISheet sheet = workbook.GetSheetAt(iSheetIndex); //列头
foreach (ICell item in sheet.GetRow(sheet.FirstRowNum).Cells)
{
dt.Columns.Add(item.ToString(), typeof(string));
} //写入内容
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
while (rows.MoveNext())
{
IRow row = (HSSFRow)rows.Current;
if (row.RowNum == sheet.FirstRowNum)
{
continue;
} DataRow dr = dt.NewRow();
foreach (ICell item in row.Cells)
{
switch (item.CellType)
{
case CellType.Boolean:
dr[item.ColumnIndex] = item.BooleanCellValue;
break;
case CellType.Error:
dr[item.ColumnIndex] = ErrorEval.GetText(item.ErrorCellValue);
break;
case CellType.Formula:
switch (item.CachedFormulaResultType)
{
case CellType.Boolean:
dr[item.ColumnIndex] = item.BooleanCellValue;
break;
case CellType.Error:
dr[item.ColumnIndex] = ErrorEval.GetText(item.ErrorCellValue);
break;
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(item))
{
dr[item.ColumnIndex] = item.DateCellValue.ToString("yyyy-MM-dd hh:MM:ss");
}
else
{
dr[item.ColumnIndex] = item.NumericCellValue;
}
break;
case CellType.String:
string str = item.StringCellValue;
if (!string.IsNullOrEmpty(str))
{
dr[item.ColumnIndex] = str.ToString();
}
else
{
dr[item.ColumnIndex] = null;
}
break;
case CellType.Unknown:
case CellType.Blank:
default:
dr[item.ColumnIndex] = string.Empty;
break;
}
break;
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(item))
{
dr[item.ColumnIndex] = item.DateCellValue.ToString("yyyy-MM-dd hh:MM:ss");
}
else
{
dr[item.ColumnIndex] = item.NumericCellValue;
}
break;
case CellType.String:
string strValue = item.StringCellValue;
if (!string.IsNullOrEmpty(strValue))
{
dr[item.ColumnIndex] = strValue.ToString();
}
else
{
dr[item.ColumnIndex] = null;
}
break;
case CellType.Unknown:
case CellType.Blank:
default:
dr[item.ColumnIndex] = string.Empty;
break;
}
}
dt.Rows.Add(dr);
}
}
} return dt;
}
#endregion
}
}
具体调用如下：
public string Export()
{////自己构造泛型：AllList
//打印开始 自己构造泛型：AllList
DataTable dt = new DataTable();
dt.Columns.Add("所属", typeof(string));
dt.Columns.Add("消费日期", typeof(string));
dt.Columns.Add("消费项", typeof(string));
dt.Columns.Add("类别", typeof(string));
dt.Columns.Add("会员卡", typeof(string));
dt.Columns.Add("现金", typeof(string));
dt.Columns.Add("支付宝", typeof(string));
dt.Columns.Add("微信", typeof(string));
dt.Columns.Add("一卡通", typeof(string));
foreach (var item in AllList)
{
DataRow dr = dt.NewRow();
dr["所属"] = "";
if (item.ServiceType.HasValue)
{
var dicModelForService = dicListForService.FirstOrDefault(A => A.ItemCode == item.ServiceType.ToString());
if (dicModelForService != null)
{
dr["所属"] = dicModelForService.ItemName;
}
}
try
{
dr["消费日期"] = item.FeeDate.ToString("yyyy-MM-dd");
}
catch
{
dr["消费日期"] = "";
}
dr["消费项"] = item.FeeName;
dr["类别"] = "";
if (item.FeeType.HasValue)
{
var dicModel = dicList.FirstOrDefault(A => A.ItemCode == item.FeeType.ToString());
if (dicModel != null)
{
dr["类别"] = dicModel.ItemName;
}
}
//
dr["会员卡"] = item.ResidentCard.HasValue ? Convert.ToDouble(-item.ResidentCard).ToString("0.00") : "";
dr["现金"] = item.Cash.HasValue ? Convert.ToDouble(-item.Cash).ToString("0.00") : "";
dr["支付宝"] = item.Alipay.HasValue ? Convert.ToDouble(-item.Alipay).ToString("0.00") : "";
dr["微信"] = item.WeChatpay.HasValue ? Convert.ToDouble(-item.WeChatpay).ToString("0.00") : "";
dr["一卡通"] = item.AllPurposeCard.HasValue ? Convert.ToDouble(-item.AllPurposeCard).ToString("0.00") : "";
dt.Rows.Add(dr);
} return ExcelHelper.LoadForToExcel(dt, "FeeDetails");
}
前端Action视图代码如下：
public ActionResult Export()
{
IReportManageService service = IOCContainer.Instance.Resolve<IReportManageService>();
string pth = service.Export();
System.Web.HttpContext.Current.Response.Charset = "GB2312";
System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.AddHeader("Content-type", "application/ms-excel");
Response.AddHeader("Accept-Ranges", "bytes");
Response.AddHeader("Content-Length", new System.IO.FileInfo(pth).Length.ToString());
Response.AddHeader("Content-Disposition", "attachment;filename=" + DateTime.Now.ToString("yyyyMMddhhssmm") + ".xls");
Response.WriteFile(pth);
Response.End();
return View();
}
以上便是所有源代码，希望大家喜欢！
截图如下：
@陈卧龙的博客
