最近想把项目中Excel中的操作部分改成NPOI ,由于2.0版本已经支持office07/10格式,但还处于测试版不稳定,于是封装如下代码
需要合并单元格等需求的功能没有 自己添加吧!
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.IO;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.SS.UserModel;
using System.Data.SqlClient;
/*
Vesion: 适用于 NPOI 1.25版本
Author: Irving
Description: Execl操作
Date:2012年6月25日16:17:06
UpdateDate:2012年9月26日13:03:31 修正日期与导入数据库中的性能
*
*
*
* //必须与数据源中的列一致
Dictionary<string, string> dtDictText = new Dictionary<string, string>();
dtDictText.Add("ProductID", "唯一号");
dtDictText.Add("ProductNo", "编号");
dtDictText.Add("ProductName", "姓名");
dtDictText.Add("Price", "价格");
dtDictText.Add("Memo", "备注");
NPOIHelper.ExportByWeb(dtDictText, "sheet1", "测试啦", HttpContext.Current, BLL.ProductBLL.LoadDataByDataSet());
NPOIHelper.ExportByWin(dtDictText, "sheet1", "测试啦", AppDomain.CurrentDomain.BaseDirectory, BLL.ProductBLL.LoadDataByDataSet());
* NPOIHelper.Import(Server.MapPath("/Files/" + "测试啦.xls"));
*
*
*
*/
namespace Common
{
public class NPOIHelper
{
/// <summary>
/// 读取Excel
/// </summary>
/// <param name="filePath">文件路径</param>
/// <param name="sheetNo">Sheet编号(默认为0,即sheet1,可选参数)</param>
/// <returns></returns>
public static DataTable Import(string filePath, params int[] sheetNo)
{
int sheetNO;
if (sheetNo.Length != 0)
sheetNO = sheetNo[0];
else
sheetNO = 0;
DataTable dt = new DataTable();
HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
HSSFSheet sheet = hssfworkbook.GetSheetAt(sheetNO) as HSSFSheet;
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
HSSFRow headerRow = sheet.GetRow(0) as HSSFRow;
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
HSSFCell cell = headerRow.GetCell(j) as HSSFCell;
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = sheet.GetRow(i) as HSSFRow;
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
if (row.GetCell(j).CellType.ToString() != "NUMERIC")//处理时间格式化问题
{
dataRow[j] = row.GetCell(j).ToString();
}
else
{
dataRow[j] = row.GetCell(j).DateCellValue.Date.ToString();
}
}
}
dt.Rows.Add(dataRow);
}
return dt;
}
/// <summary>
/// 导出Excel数据(Win)
/// </summary>
/// <param name="dtDictHeadText">表头数据(字段必须与数据源一致)</param>
/// <param name="sheetName">纸张名</param>
/// <param name="fileName">文件名称</param>
/// <param name="filePath">路径</param>
/// <param name="dtSource">数据源</param>
public static void ExportByWin(Dictionary<string, string> dtDictHeadText, string sheetName, string fileName, string filePath, DataTable dtSource)
{
HSSFWorkbook hssfworkbook = Export(dtDictHeadText, sheetName, fileName, dtSource);
try
{
using (Stream stream = File.Create(String.Format("{0}{1}.xls", filePath, fileName)))
{
hssfworkbook.Write(stream);
stream.Flush();
}
}
catch (Exception ex)
{
//Log4记录
throw new Exception("操作失败: " + ex.Message);
}
}
/// <summary>
/// 导出Excel数据(Web)
/// </summary>
/// <param name="dtDictHeadText">表头数据(字段必须与数据源一致)</param>
/// <param name="sheetName">纸张名</param>
/// <param name="fileName">文件名</param>
/// <param name="context">上下文对象</param>
/// <param name="dtSource">数据源</param>
public static void ExportByWeb(Dictionary<string, string> dtDictHeadText, string sheetName, string fileName, HttpContext context, DataTable dtSource)
{
HSSFWorkbook hssfworkbook = Export(dtDictHeadText, sheetName, fileName, dtSource);
try
{
using (MemoryStream ms = new MemoryStream())
{
hssfworkbook.Write(ms);
ms.Flush();
ms.Position = 0;
context.Response.ContentType = "application/vnd.ms-excel";
context.Response.ContentEncoding = Encoding.UTF8;
context.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName + ".xls", Encoding.UTF8));
context.Response.BinaryWrite(ms.ToArray());
}
}
catch (Exception ex)
{
//Log4记录
throw new Exception("操作失败: " + ex.Message);
}
}
/// <summary>
/// 读取Execl数据到数据库中
/// </summary>
/// <param name="conText">连接字符串</param>
/// <param name="tabeName">表名</param>
/// <param name="ipDict">字典(SourceColumn(数据源列名称) DestinationColumn(目标列名称))</param>
/// <param name="filePath">路径(只支持xls格式)</param>
/// <param name="sheetNo">纸张页</param>
public static void ImportExcelDBSourceIntoTable(string conText, string tabeName, Dictionary<string, string> ipDict, string filePath, params int[] sheetNo)
{
int sheetNO;
if (sheetNo.Length != 0)
sheetNO = sheetNo[0];
else
sheetNO = 0;
using (SqlConnection conn = new SqlConnection(conText))
{
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
DataTable dt = NPOIHSSFHelper.Import(filePath, sheetNO);
SqlBulkCopy blkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran) { BatchSize = dt.Rows.Count, DestinationTableName = tabeName };
foreach (KeyValuePair<string, string> item in ipDict)
{
blkCopy.ColumnMappings.Add(item.Key, item.Value).ToString().Trim();
}
try
{
blkCopy.WriteToServer(dt);
tran.Commit();
conn.Close();
blkCopy.Close();
}
catch (Exception ex)
{ //Log4
tran.Rollback();
conn.Close();
blkCopy.Close();
throw new Exception(ex.Message);
}
}
}
#region 操作
/// <summary>
/// 填充数据
/// </summary>
/// <param name="dtDictHeadText">表头字典</param>
/// <param name="sheetName">名称</param>
/// <param name="fileName">文件名称</param>
/// <param name="dtSource">数据源</param>
/// <returns></returns>
private static HSSFWorkbook Export(Dictionary<string, string> dtDictHeadText, string sheetName, string fileName, DataTable dtSource)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
SetSummaryData(hssfworkbook, fileName); //版权信息
ISheet sheet = hssfworkbook.CreateSheet(sheetName);
SetHeadData(dtSource, dtDictHeadText, hssfworkbook, sheet); //创建表头
int rowIndex = 1;
foreach (DataRow row in dtSource.Rows)
{
HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
SetContentData(dtSource, dtDictHeadText, row, dataRow, hssfworkbook);//填充数据集
rowIndex++;
}
return hssfworkbook;
}
/// <summary>
/// 属性信息
/// </summary>
/// <param name="workbook"></param>
/// <param name="fileName"></param>
private static void SetSummaryData(HSSFWorkbook workbook, string fileName)
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "Irving"; //填加xls文件作者信息
si.Title = fileName; //填加xls文件标题信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Subject = "主题信息";//填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
/// <summary>
/// 填充表头数据
/// </summary>
/// <param name="dtSource"></param>
/// <param name="dtDictHeadText"></param>
/// <param name="workbook"></param>
/// <param name="sheet"></param>
private static void SetHeadData(DataTable dtSource, Dictionary<string, string> dtDictHeadText, HSSFWorkbook workbook, ISheet sheet)
{
HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
int index = 0;
foreach (KeyValuePair<string, string> item in dtDictHeadText)
{
foreach (DataColumn column in dtSource.Columns)
{
if (column.ColumnName.Contains(item.Key))
{
headerRow.CreateCell(index).SetCellValue(item.Value);
index++;
break;
}
}
}
}
/// <summary>
/// 填充表数据
/// </summary>
/// <param name="dtSource"></param>
/// <param name="dtDictHeadText"></param>
/// <param name="row"></param>
/// <param name="dataRow"></param>
/// <param name="workbook"></param>
private static void SetContentData(DataTable dtSource, Dictionary<string, string> dtDictHeadText, DataRow row, HSSFRow dataRow, HSSFWorkbook workbook)
{
int index = 0;
foreach (KeyValuePair<string, string> item in dtDictHeadText)
{
foreach (DataColumn column in dtSource.Columns)
{
if (item.Key.ToLower() == column.ColumnName.ToLower())
{
HSSFCell newCell = dataRow.CreateCell(index) as HSSFCell;
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);
if (drValue.Contains("上午 12:00:00"))
{
drValue = dateV.ToString("yyyy/MM/dd");
}
else
{
if (!string.IsNullOrEmpty(drValue))
{
drValue = dateV.ToString();
}
}
newCell.SetCellValue(drValue);
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.Guid"://GUID
Guid guid = Guid.Empty;
Guid.TryParse(drValue, out guid);
newCell.SetCellValue(guid.ToString());
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
index++;
break;
}
}
}
}
#endregion
}
}