最近在做的一個項目中需要生成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, 0, 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(936).GetBytes(item.ColumnName.ToString()).Length;
}
//獲取每一列的最大列寬
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表頭，填充列頭，樣式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表頭及樣式
for (int i = 0; i < headerTextList.Count; i++)
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(i);
headerRow.HeightInPoints = 18;
headerRow.CreateCell(0).SetCellValue(headerTextList[i]);
HSSFCellStyle headerStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headerStyle.Alignment = HorizontalAlignment.Left;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 14;
//font.Boldweight = 700;
headerStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headerStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
#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 = 10;
font.Boldweight = 700;
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] + 1) * 256);
}
}
#endregion
rowIndex = headerTextList.Count + 1;
}
#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 = 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;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
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, 0);
}
/// <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, 1, 1);
}
/// <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, -1, startColNum, -1);
}
/// <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 < 0 || sheetNum > sheetCount - 1 ? 0 : sheetNum;
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheetNum);
HSSFRow headerRow = null;
#region 行列號范圍驗證
startColNum = startColNum < 0 ? 0 : startColNum;
startRowNum = startRowNum < 1 ? 1 : startRowNum;
headerRow = (HSSFRow)sheet.GetRow(startRowNum - 1);
endColNum = (endColNum > headerRow.LastCellNum || endColNum < 1) ? headerRow.LastCellNum : endColNum;
endRowNum = (endRowNum - 1 > sheet.LastRowNum || endRowNum < 0) ? sheet.LastRowNum + 1 : endColNum;
#endregion
//添加列
for (int j = startColNum - 1; j < endColNum; j++)
{
HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
//添加行
for (int i = startRowNum; i <= endRowNum - 1; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = startColNum - 1; j < endColNum; j++)
{
if (row.GetCell(j) != null)
dataRow[j - startColNum + 1] = 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;
si.LastSaveDateTime = this.LastSaveDateTime ?? DateTime.Now;
workbook.SummaryInformation = si;
}
}
這些都是些高級的了
建議可以到論壇你求助，哪的高手比較集中
同問啊。。。。。。。。
