//-------------------------------------------------------------------------------------
|
// All Rights Reserved , Copyright (C) 2013 , DZD , Ltd .
|
//-------------------------------------------------------------------------------------
|
|
using System;
|
using System.Data;
|
using System.Data.OleDb;
|
using System.Diagnostics;
|
using System.IO;
|
using System.Text;
|
using NPOI.HPSF;
|
using NPOI.HSSF.UserModel;
|
using NPOI.HSSF.Util;
|
using NPOI.SS.UserModel;
|
using NPOI.XSSF.UserModel;
|
using NPOI.SS.Util;
|
|
namespace HH.ExcelHelper
|
{
|
/// <summary>
|
/// ExcelHelp
|
/// <![CDATA[2016-10-17]]>
|
/// <creator>FJ</creator>
|
/// <remarks>ÓÃÓÚµ¼ÈëºÍµ¼³öExcel£¬Ö§³Öxls,xlsx¸ñʽµÄExcelÎļþ</remarks>
|
/// </summary>
|
public class NpoiHelper
|
{
|
#region DataTableµ¼³öµ½Excel
|
/// <summary>
|
/// DataTableµ¼³öµ½Excel
|
/// </summary>
|
/// <param name="dtSource">DataTableÊý¾ÝÔ´</param>
|
/// <param name="strHeaderText">µÚÒ»ÐбíÍ·</param>
|
/// <param name="SheetName">SheetIndexÒ³Ãû³Æ£¬Ä¬ÈÏΪ"sheet1"</param>
|
/// <param name="isShowHeader">ÊÇ·ñĬÈÏÏÔʾµÚÒ»ÐеıíÍ·ÐÅÏ¢ ĬÈÏ£ºÏÔʾ</param>
|
/// <returns>MemoryStream</returns>
|
public static void DataTableToExcel(ref MemoryStream ms, DataTable dtSource, string strHeaderText, string SheetName = "sheet1", bool isShowHeader = true)
|
{
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
ISheet sheet = workbook.CreateSheet(SheetName);
|
|
#region ÓÒ»÷Îļþ ÊôÐÔÐÅÏ¢
|
{
|
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
|
dsi.Company = "http://www.yongfa365.com/";
|
workbook.DocumentSummaryInformation = dsi;
|
|
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
|
si.Author = "ºººÍÖÇÄÜ"; //Ìî¼ÓxlsÎļþ×÷ÕßÐÅÏ¢
|
si.ApplicationName = "MESϵͳÊý¾Ýµ¼³ö"; //Ìî¼ÓxlsÎļþ´´½¨³ÌÐòÐÅÏ¢
|
si.LastAuthor = "ºÏ·ÊºººÍÖÇÄÜÎïÁ÷¿Æ¼¼ÓÐÏÞ¹«Ë¾"; //Ìî¼ÓxlsÎļþ×îºó±£´æÕßÐÅÏ¢
|
si.Comments = "MESÏîÄ¿×é"; //Ìî¼ÓxlsÎļþ×÷ÕßÐÅÏ¢
|
si.Title = "MES"; //Ìî¼ÓxlsÎļþ±êÌâÐÅÏ¢
|
si.Subject = "ºÏ·ÊºººÍÖÇÄÜÎïÁ÷¿Æ¼¼ÓÐÏÞ¹«Ë¾";//Ìî¼ÓÎļþÖ÷ÌâÐÅÏ¢
|
si.CreateDateTime = DateTime.Now;
|
workbook.SummaryInformation = si;
|
}
|
#endregion
|
|
ICellStyle dateStyle = workbook.CreateCellStyle();
|
IDataFormat format = workbook.CreateDataFormat();
|
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
|
|
//È¡µÃÁпí
|
int[] arrColWidth = new int[dtSource.Columns.Count];
|
foreach (DataColumn item in dtSource.Columns)
|
{
|
int len = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length + 2;
|
arrColWidth[item.Ordinal] = len;
|
}
|
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;
|
//´´½¨Ñùʽ
|
ICellStyle rowHeadStyle = workbook.CreateCellStyle();
|
|
foreach (DataRow row in dtSource.Rows)
|
{
|
#region н¨±í£¬Ìî³ä±íÍ·£¬Ìî³äÁÐÍ·£¬Ñùʽ
|
if (rowIndex == 65535 || rowIndex == 0)
|
{
|
if (rowIndex != 0)
|
{
|
sheet = workbook.CreateSheet();
|
}
|
|
#region ±íÍ·¼°Ñùʽ
|
{
|
if (isShowHeader)
|
{
|
IRow headerRow = sheet.CreateRow(0);
|
headerRow.HeightInPoints = 25;//ÉèÖø߶È
|
|
headerRow.CreateCell(0).SetCellValue(strHeaderText);
|
|
ICellStyle headStyle = workbook.CreateCellStyle();
|
headStyle.FillBackgroundColor = 5;
|
headStyle.Alignment = HorizontalAlignment.Center; //ÉèÖþÓÖÐ
|
|
headStyle.FillPattern = FillPattern.SolidForeground;// FillPatternType.SOLID_FOREGROUND;//ÉèÖñ³¾°ÑÕÉ«
|
|
headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
|
|
IFont font = workbook.CreateFont();
|
font.FontHeightInPoints = 20;
|
font.Boldweight = 700;
|
headStyle.SetFont(font);
|
|
headerRow.GetCell(0).CellStyle = headStyle;
|
|
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
|
}
|
}
|
#endregion
|
|
#region ÁÐÍ·¼°Ñùʽ
|
{
|
int indexLine = 0;
|
if (isShowHeader)
|
{
|
indexLine = 1;
|
}
|
IRow headerRow = sheet.CreateRow(indexLine);
|
headerRow.HeightInPoints = 22;//
|
|
ICellStyle headStyle = workbook.CreateCellStyle();
|
headStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;
|
|
headStyle.FillPattern = FillPattern.SolidForeground;// FillPatternType.SOLID_FOREGROUND; ;//ÉèÖñ³¾°ÑÕÉ«
|
headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;
|
|
headStyle.Alignment = HorizontalAlignment.Center;//ÉèÖÃÁÐÍ·¾ÓÖÐ
|
headStyle.VerticalAlignment = VerticalAlignment.Center; //ÉèÖÃÉÏϾÓÖÐ
|
headStyle.BorderBottom = BorderStyle.Thin;//ÉèÖÃϱ߿ò
|
headStyle.BorderLeft = BorderStyle.Thin; //ÉèÖÃ×ó±ß¿ò
|
headStyle.BorderRight = BorderStyle.Thin;//ÉèÖÃÓұ߿ò
|
headStyle.BorderTop = BorderStyle.Thin; //ÉèÖÃÉϱ߿ò
|
|
IFont font = 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);
|
}
|
//headerRow.Dispose();
|
}
|
#endregion
|
if (isShowHeader)
|
{
|
rowIndex = 2;
|
}
|
else
|
{
|
rowIndex = 1;
|
}
|
}
|
#endregion
|
|
#region Ìî³äÄÚÈÝ
|
IRow dataRow = sheet.CreateRow(rowIndex);
|
dataRow.HeightInPoints = 18;//
|
//¶¨Òåµ¥Ôª¸ñÑùʽ
|
rowHeadStyle.VerticalAlignment = VerticalAlignment.Center;//ÉèÖÃÉÏϾÓÖÐ
|
|
foreach (DataColumn column in dtSource.Columns)
|
{
|
ICell newCell = dataRow.CreateCell(column.Ordinal);
|
|
//ÉèÖõ¥Ôª¸ñÑùʽ
|
newCell.CellStyle = rowHeadStyle;
|
|
string drValue = row[column].ToString();
|
|
switch (column.DataType.ToString())
|
{
|
case "System.String"://×Ö·û´®ÀàÐÍ
|
newCell.SetCellValue(drValue);
|
break;
|
case "System.DateTime"://ÈÕÆÚÀàÐÍ
|
DateTime dateV;
|
if (!string.IsNullOrEmpty(drValue))
|
{
|
DateTime.TryParse(drValue, out dateV);
|
newCell.SetCellValue(dateV);
|
}
|
else
|
{
|
newCell.SetCellValue("");
|
}
|
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++;
|
}
|
|
workbook.Write(ms);
|
ms.Seek(0, SeekOrigin.Begin);
|
// sheet.Dispose();
|
//workbook.Dispose();//Ò»°ãÖ»ÓÃдÕâÒ»¸ö¾ÍOKÁË£¬Ëû»á±éÀú²¢ÊÍ·ÅËùÓÐ×ÊÔ´£¬µ«µ±Ç°°æ±¾ÓÐÎÊÌâËùÒÔÖ»ÊÍ·Åsheet
|
}
|
#endregion
|
|
#region ExcelÊý¾Ýµ¼ÈëDataTable£¬Ö§³ÖËùÓÐsheetÒ³
|
/// <summary>
|
/// ExcelÊý¾Ýµ¼ÈëDataTable£¬Ö§³ÖËùÓÐsheetÒ³
|
/// </summary>
|
/// <param name="FilePath"></param>
|
/// <returns></returns>
|
public static DataTable ExcelToDataTableForSheet(string FilePath)
|
{
|
//±£´æÊý¾ÝµÄdatatable
|
DataTable dt = new DataTable();
|
|
HSSFWorkbook hssfworkbook = null;
|
XSSFWorkbook xssfworkbook = null;
|
string fileExt = Path.GetExtension(FilePath);//»ñÈ¡ÎļþµÄºó׺Ãû
|
using (FileStream file = new FileStream(FilePath, FileMode.Open, FileAccess.Read))
|
{
|
if (fileExt == ".xls")
|
hssfworkbook = new HSSFWorkbook(file);
|
else if (fileExt == ".xlsx")
|
xssfworkbook = new XSSFWorkbook(file);//³õʼ»¯Ì«ÂýÁË£¬²»ÖªµÀÕâÊÇʲôbug
|
}
|
if (hssfworkbook != null)
|
{
|
//»ñÈ¡ËùÓÐSheetName
|
int sheetCount = hssfworkbook.NumberOfSheets;
|
for (int index = 0; index < sheetCount; index++)
|
{
|
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(index);
|
if (sheet != null)
|
{
|
#region
|
//»ñµÃDataTableÁÐÍ·
|
HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
|
int cellCount = headerRow.LastCellNum;
|
for (int j = 0; j < cellCount; j++)
|
{
|
HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
|
if (!dt.Columns.Contains(cell.ToString()))
|
{
|
dt.Columns.Add(cell.ToString());
|
}
|
}
|
//sheetÒ³Ë÷Òý
|
if (!dt.Columns.Contains("SheetIndex"))
|
{
|
dt.Columns.Add("SheetIndex", typeof(string));
|
}
|
//sheetÒ³Ãû³Æ
|
if (!dt.Columns.Contains("SheetName"))
|
{
|
dt.Columns.Add("SheetName", typeof(string));
|
}
|
//»ñµÃÊý¾Ý
|
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
|
{
|
HSSFRow row = (HSSFRow)sheet.GetRow(i);
|
if (row == null)
|
{
|
continue;
|
}
|
DataRow dataRow = dt.NewRow();
|
if (dataRow == null)
|
{
|
continue;
|
}
|
|
dataRow["SheetIndex"] = index;
|
dataRow["SheetName"] = xssfworkbook.GetSheetName(index);
|
|
for (int j = 0; j < cellCount; j++)
|
{
|
ICell cell = row.GetCell(j);
|
if (cell == null)
|
{
|
//dataRow[j] = "";
|
}
|
else if (cell.CellType == CellType.Numeric)
|
{
|
if (HSSFDateUtil.IsCellDateFormatted(cell))//ÈÕÆÚÀàÐÍ
|
{
|
dataRow[j] = cell.DateCellValue;
|
}
|
else//ÆäËûÊý×ÖÀàÐÍ
|
{
|
dataRow[j] = cell.NumericCellValue;
|
}
|
}
|
else if (cell.CellType == CellType.Blank)//¿ÕÊý¾ÝÀàÐÍ
|
{
|
dataRow[j] = "";
|
}
|
else if (cell.CellType == CellType.Boolean)
|
{
|
dataRow[j] = cell.BooleanCellValue;
|
}
|
else //ÆäËûÀàÐͶ¼°´×Ö·û´®ÀàÐÍÀ´´¦Àí
|
{
|
dataRow[j] = cell.StringCellValue;
|
}
|
}
|
dt.Rows.Add(dataRow);
|
}
|
#endregion
|
}
|
}
|
}
|
else if (xssfworkbook != null)
|
{
|
//»ñÈ¡ËùÓÐSheetName
|
int sheetCount = xssfworkbook.NumberOfSheets;
|
for (int index = 0; index < sheetCount; index++)
|
{
|
XSSFSheet xSheet = (XSSFSheet)xssfworkbook.GetSheetAt(index);
|
if (xSheet != null)
|
{
|
#region
|
//ÐÂÔöDataTableÁÐÍ·
|
XSSFRow headerRow = (XSSFRow)xSheet.GetRow(0);
|
int cellCount = headerRow.LastCellNum;
|
for (int j = 0; j < cellCount; j++)
|
{
|
XSSFCell cell = (XSSFCell)headerRow.GetCell(j);
|
if (!dt.Columns.Contains(cell.ToString()))
|
{
|
dt.Columns.Add(cell.ToString());
|
}
|
}
|
//sheetÒ³Ë÷Òý
|
if (!dt.Columns.Contains("SheetIndex"))
|
{
|
dt.Columns.Add("SheetIndex", typeof(string));
|
}
|
//sheetÒ³Ãû³Æ
|
if (!dt.Columns.Contains("SheetName"))
|
{
|
dt.Columns.Add("SheetName", typeof(string));
|
}
|
|
//»ñµÃÊý¾Ý
|
for (int i = (xSheet.FirstRowNum + 1); i <= xSheet.LastRowNum; i++)
|
{
|
XSSFRow row = (XSSFRow)xSheet.GetRow(i);
|
if (row == null)
|
{
|
continue;
|
}
|
DataRow dataRow = dt.NewRow();
|
if (dataRow == null)
|
{
|
continue;
|
}
|
|
//±£´æSheetIndexλÖÃ
|
dataRow["SheetIndex"] = index;
|
dataRow["SheetName"] = xssfworkbook.GetSheetName(index);
|
|
for (int j = 0; j < cellCount; j++)
|
{
|
ICell cell = row.GetCell(j);
|
if (cell == null)
|
{
|
continue;
|
}
|
else if (cell.CellType == CellType.Blank)//¿ÕÊý¾ÝÀàÐÍ
|
{
|
dataRow[j] = "";
|
}
|
else if (cell.CellType == CellType.Numeric)
|
{
|
if (HSSFDateUtil.IsCellDateFormatted(cell))//ÈÕÆÚÀàÐÍ
|
{
|
dataRow[j] = cell.DateCellValue;
|
}
|
else//ÆäËûÊý×ÖÀàÐÍ
|
{
|
dataRow[j] = cell.NumericCellValue;
|
}
|
}
|
|
else //ÆäËûÀàÐͶ¼°´×Ö·û´®ÀàÐÍÀ´´¦Àí
|
{
|
dataRow[j] = cell.StringCellValue;
|
}
|
|
//if (row.GetCell(j) != null)
|
// dataRow[j] = row.GetCell(j).ToString();
|
}
|
dt.Rows.Add(dataRow);
|
}
|
#endregion
|
}
|
}
|
}
|
return dt;
|
}
|
#endregion
|
|
#region µ¼ÈëExcelµ½DataTableÖ§³Öxls xlsx
|
/// <summary>
|
/// µ¼ÈëExcelµ½DataTableÖ§³Öxls xlsx
|
/// </summary>
|
/// <param name="FilePath">Îļþ·¾¶+ÎļþÃû³Æ</param>
|
/// <param name="sheetIndex">¶ÁÈ¡sheetÒ³µÄË÷Òý</param>
|
/// <returns>DataTableÊý¾Ý¼¯</returns>
|
public static DataTable ExcelToDataTable(string FilePath, int sheetIndex = 0)
|
{
|
DataTable dt = new DataTable();
|
HSSFWorkbook hssfworkbook = null;
|
XSSFWorkbook xssfworkbook = null;
|
string fileExt = Path.GetExtension(FilePath);//»ñÈ¡ÎļþµÄºó׺Ãû
|
using (FileStream file = new FileStream(FilePath, FileMode.Open, FileAccess.Read))
|
{
|
if (fileExt == ".xls")
|
hssfworkbook = new HSSFWorkbook(file);
|
else if (fileExt == ".xlsx")
|
xssfworkbook = new XSSFWorkbook(file);//³õʼ»¯Ì«ÂýÁË£¬²»ÖªµÀÕâÊÇʲôbug
|
}
|
if (hssfworkbook != null)
|
{
|
HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheetIndex);
|
if (sheet != null)
|
{
|
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
|
HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
|
int cellCount = headerRow.LastCellNum;
|
for (int j = 0; j < cellCount; j++)
|
{
|
HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
|
|
dt.Columns.Add(cell.ToString());
|
}
|
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
|
{
|
HSSFRow row = (HSSFRow)sheet.GetRow(i);
|
if (row == null)
|
{
|
continue;
|
}
|
DataRow dataRow = dt.NewRow();
|
if (dataRow == null)
|
{
|
continue;
|
}
|
for (int j = row.FirstCellNum; j < cellCount; j++)
|
{
|
ICell cell = row.GetCell(j);
|
if (cell == null)
|
{
|
//dataRow[j] = "";
|
}
|
else if (cell.CellType == CellType.Numeric)
|
{
|
if (HSSFDateUtil.IsCellDateFormatted(cell))//ÈÕÆÚÀàÐÍ
|
{
|
dataRow[j] = cell.DateCellValue;
|
}
|
else//ÆäËûÊý×ÖÀàÐÍ
|
{
|
dataRow[j] = cell.NumericCellValue;
|
}
|
}
|
else if (cell.CellType == CellType.Blank)//¿ÕÊý¾ÝÀàÐÍ
|
{
|
dataRow[j] = "";
|
}
|
else if (cell.CellType == CellType.Boolean)
|
{
|
dataRow[j] = cell.BooleanCellValue;
|
}
|
else //ÆäËûÀàÐͶ¼°´×Ö·û´®ÀàÐÍÀ´´¦Àí
|
{
|
dataRow[j] = cell.StringCellValue;
|
}
|
}
|
dt.Rows.Add(dataRow);
|
}
|
}
|
}
|
else if (xssfworkbook != null)
|
{
|
XSSFSheet xSheet = (XSSFSheet)xssfworkbook.GetSheetAt(sheetIndex);
|
if (xSheet != null)
|
{
|
System.Collections.IEnumerator rows = xSheet.GetRowEnumerator();
|
XSSFRow headerRow = (XSSFRow)xSheet.GetRow(0);
|
int cellCount = headerRow.LastCellNum;
|
for (int j = 0; j < cellCount; j++)
|
{
|
XSSFCell cell = (XSSFCell)headerRow.GetCell(j);
|
dt.Columns.Add(cell.ToString());
|
}
|
for (int i = (xSheet.FirstRowNum + 1); i <= xSheet.LastRowNum; i++)
|
{
|
XSSFRow row = (XSSFRow)xSheet.GetRow(i);
|
if (row == null)
|
{
|
continue;
|
}
|
DataRow dataRow = dt.NewRow();
|
if (dataRow == null)
|
{
|
continue;
|
}
|
for (int j = row.FirstCellNum; j < cellCount; j++)
|
{
|
ICell cell = row.GetCell(j);
|
if (cell == null)
|
{
|
continue;
|
}
|
else if (cell.CellType == CellType.Blank)//¿ÕÊý¾ÝÀàÐÍ
|
{
|
dataRow[j] = "";
|
}
|
else if (cell.CellType == CellType.Numeric)
|
{
|
if (HSSFDateUtil.IsCellDateFormatted(cell))//ÈÕÆÚÀàÐÍ
|
{
|
dataRow[j] = cell.DateCellValue;
|
}
|
else//ÆäËûÊý×ÖÀàÐÍ
|
{
|
dataRow[j] = cell.NumericCellValue;
|
}
|
}
|
|
else //ÆäËûÀàÐͶ¼°´×Ö·û´®ÀàÐÍÀ´´¦Àí
|
{
|
dataRow[j] = cell.StringCellValue;
|
}
|
|
//if (row.GetCell(j) != null)
|
// dataRow[j] = row.GetCell(j).ToString();
|
}
|
dt.Rows.Add(dataRow);
|
}
|
}
|
}
|
return dt;
|
}
|
|
#endregion
|
}
|
}
|
