NPOIの概要と運用
38135 ワード
最近、プロジェクトのExcelの操作部分をNPOIに変更しようとしていますが、2.0バージョンではoffice 07/10形式がサポートされていますが、まだテスト版では不安定なので、以下のコードをパッケージします. 1.1.25バージョン をサポート 2.B/SとC/S導入導出 をサポートする 3.Excelデータインポートデータベース を知っている
セルをマージするなど必要な機能は自分で追加していないでしょう.
セルをマージするなど必要な機能は自分で追加していないでしょう.
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
}
}