最近在做的一个项目中需要生成Excel，通过学习使用NPOI实现了相关需求，写了一个简便操作的类，记录如下：
public class NPOIHelperForExcel
{
#region excel文件属性 //作者
public string Author { get; set; } //标题
public string Title { get; set; } //主题
public string Subject { get; set; } //标记
public string Keywords { get; set; } //创建程序信息
public string ApplicationName { get; set; } //最后一次保存者
public string LastAuthor { get; set; } //备注
public string Comments { get; set; } //创建内容的时间
public DateTime? CreateDateTime { get; set; } //最后一次打印的时间
public DateTime? LastPrinted { get; set; } //最后一次保存的时间
public DateTime? LastSaveDateTime { get; set; } //公司
public string Company { get; set; } //管理者
public string Manager { get; set; } //比例
public bool Scale { get; set; } #endregion #region 导出，将DataTable导出为Excel文件
/// <summary>
/// DataTable导出到Excel文件
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="headerTextList">表头摘要信息</param>
/// <param name="strFileName">保存位置</param>
public void Export(DataTable dtSource, List<String> headerTextList, string strFileName)
{
using (MemoryStream ms = Export(dtSource, headerTextList))
{
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, , data.Length);
fs.Flush();
}
}
} /// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="headerTextList">表头摘要信息</param>
public MemoryStream Export(DataTable dtSource, List<String> headerTextList)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("sheet1"); //设置Excel文件属性信息
SetFileProperty(workbook); HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //计算列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding().GetBytes(item.ColumnName.ToString()).Length;
}
//获取每一列的最大列宽
for (int i = ; i < dtSource.Rows.Count; i++)
{
for (int j = ; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding().GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
} int rowIndex = ;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式 if (rowIndex == || rowIndex == )
{
if (rowIndex != )
{
sheet = workbook.CreateSheet();
} #region 表头及样式
for (int i = ; i < headerTextList.Count; i++)
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(i);
headerRow.HeightInPoints = ;
headerRow.CreateCell().SetCellValue(headerTextList[i]); HSSFCellStyle headerStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headerStyle.Alignment = HorizontalAlignment.Left;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = ;
//font.Boldweight = 700;
headerStyle.SetFont(font);
headerRow.GetCell().CellStyle = headerStyle;
sheet.AddMergedRegion(new CellRangeAddress(, , , dtSource.Columns.Count - ));
}
#endregion #region 列头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(headerTextList.Count);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = ;
font.Boldweight = ;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + ) * );
}
}
#endregion rowIndex = headerTextList.Count + ;
} #endregion #region 填充表格内容 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString())
{
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 = ;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = ;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
} } #endregion rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = ; return ms;
} } /// <summary>
/// 用于Web导出
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="headerTextList">表头摘要信息</param>
/// <param name="strFileName">文件名</param>
public void ExportByWeb(DataTable dtSource, List<String> headerTextList, string strFileName)
{
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(dtSource, headerTextList).GetBuffer());
curContext.Response.End();
}
#endregion #region 导入，将excel读取到DataTable中
/// <summary>
/// 读取excel,默认第一行为表头
/// </summary>
/// <param name="strFileName">excel文件路径</param>
/// <returns>DataTable</returns>
public DataTable Import(string strFileName)
{
return Import(strFileName, );
} /// <summary>
/// 读取excel
/// </summary>
/// <param name="strFileName">excel文件路径</param>
/// <param name="sheetNum">sheet索引，以0开始</param>
/// <returns>DataTable</returns>
public DataTable Import(string strFileName, int sheetNum)
{
return Import(strFileName, sheetNum, , );
} /// <summary>
/// 读取excel
/// </summary>
/// <param name="strFileName">excel文件路径</param>
/// <param name="sheetNum">sheet索引，以0开始</param>
/// <param name="startRowNum">起始行号，即：表头在Excel中的行号</param>
/// <param name="startColNum">起始列号</param>
/// <returns>DataTable</returns>
public DataTable Import(string strFileName, int sheetNum, int startRowNum, int startColNum)
{
return Import(strFileName, sheetNum, startRowNum, -, startColNum, -);
} /// <summary>
/// 读取excel
/// sheet.LastRowNum属性获取的是Excel中该工作表(sheet)的末行行号减1；
/// headerRow.LastCellNum属性获取的是Excel中该行的列数
/// </summary>
/// <param name="strFileName">excel文档路径</param>
/// <param name="sheetNum">工作表索引，以0开始</param>
/// <param name="startRowNum">起始行号，即：表头在Excel中的行号</param>
/// <param name="endRowNum">结束行号</param>
/// <param name="startColNum">起始列号</param>
/// <param name="endColNum">结束列号</param>
/// <returns>DataTable</returns>
public DataTable Import(string strFileName, int sheetNum, int startRowNum, int endRowNum, int startColNum, int endColNum)
{
DataTable dt = new DataTable(); HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
} int sheetCount = hssfworkbook.NumberOfSheets; sheetNum = sheetNum < || sheetNum > sheetCount - ? : sheetNum; HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheetNum); HSSFRow headerRow = null; #region 行列号范围验证 startColNum = startColNum < ? : startColNum; startRowNum = startRowNum < ? : startRowNum; headerRow = (HSSFRow)sheet.GetRow(startRowNum - ); endColNum = (endColNum > headerRow.LastCellNum || endColNum < ) ? headerRow.LastCellNum : endColNum; endRowNum = (endRowNum - > sheet.LastRowNum || endRowNum < ) ? sheet.LastRowNum + : endColNum; #endregion //添加列
for (int j = startColNum - ; j < endColNum; j++)
{
HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
} //添加行
for (int i = startRowNum; i <= endRowNum - ; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
DataRow dataRow = dt.NewRow(); for (int j = startColNum - ; j < endColNum; j++)
{
if (row.GetCell(j) != null)
dataRow[j - startColNum + ] = row.GetCell(j).ToString();
} dt.Rows.Add(dataRow);
}
return dt;
}
#endregion #region 单元格写入
///// <summary>
///// 给指定单元格写入内容
///// </summary>
///// <param name="workBook"></param>
///// <param name="sheetName"></param>
///// <param name="rowNum"></param>
///// <param name="colNum"></param>
///// <param name="content"></param>
//public void WriteCell(HSSFWorkbook workBook, string sheetName, int rowNum, int colNum, string content)
//{
// if (workBook == null)
// {
// throw new Exception("workBook不能为null");
// }
// WriteCell(workBook, workBook.GetSheetIndex(sheetName), rowNum, colNum, content);
//} ///// <summary>
///// 给指定单元格写入内容
///// </summary>
///// <param name="workBook"></param>
///// <param name="sheetNum"></param>
///// <param name="rowNum"></param>
///// <param name="colNum"></param>
///// <param name="content"></param>
///// <returns></returns>
//public void WriteCell(HSSFWorkbook workBook, int sheetNum, int rowNum, int colNum, string content)
//{
// if (workBook == null)
// {
// throw new Exception("workBook不能为null");
// } // if (workBook.NumberOfSheets < sheetNum || sheetNum < 0)
// {
// throw new Exception("指定的sheet不存在");
// } // ISheet sheet = workBook.GetSheetAt(sheetNum - 1); // HSSFRow row = (HSSFRow)sheet.GetRow(rowNum) ?? (HSSFRow)sheet.CreateRow(rowNum - 1);
// HSSFCell cell = (HSSFCell)row.CreateCell(6);
// cell.SetCellValue(content); // //using (MemoryStream ms = new MemoryStream())
// //{
// // workBook.Write(ms);
// // ms.Flush();
// // ms.Position = 0; // // using (FileStream fs = new FileStream("测试行列写入.xls", FileMode.Create, FileAccess.Write))
// // {
// // byte[] data = ms.ToArray();
// // fs.Write(data, 0, data.Length);
// // fs.Flush();
// // }
// //}
// //return workBook;
//}
#endregion /// <summary>
/// 设置Excel文件属性信息
/// </summary>
/// <param name="workbook"></param>
private void SetFileProperty(HSSFWorkbook workbook)
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = this.Company ?? "";
dsi.Scale = this.Scale;
dsi.Manager = this.Manager ?? "";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = this.Author ?? "";
si.ApplicationName = this.ApplicationName ?? "";
si.LastAuthor = this.LastAuthor ?? "";
si.Comments = this.Comments ?? "";
si.Title = this.Title ?? "";
si.Subject = this.Subject ?? "";
si.CreateDateTime = this.CreateDateTime ?? DateTime.Now;
si.Keywords = this.Keywords ?? "";
si.LastAuthor = this.LastAuthor ?? "";
si.LastPrinted = this.LastPrinted ?? DateTime.Now;
si.LastSaveDateTime = this.LastSaveDateTime ?? DateTime.Now;
//上面的属性必须赋值，不能为null，不然执行到Export方法的"workbook.Write(ms);"处会导致空引用错误。
workbook.SummaryInformation = si;
}
}