-
NPOI Excel Helper
复制代码
/// <summary>
/// NPOIExcel帮助类
/// </summary>
public class NPOIExcelHelper
{
/// <summary>
/// DataTable导出到Excel文件
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="strFileName">保存位置</param>
public static void DataTableToExcel(DataTable dtSource, string strHeaderText, string strFileName)
{
ExcelFileSuffixType suffix = default(ExcelFileSuffixType);
if (strFileName.IndexOf(".xlsx") > 0) // 2007版本
suffix = ExcelFileSuffixType.xlsx;
else if (strFileName.IndexOf(".xls") > 0) // 2003版本
suffix = ExcelFileSuffixType.xls;
using (MemoryStream ms = DataTableToExcelStream(dtSource, strHeaderText, suffix))
{
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
}
}
/// <summary>
/// DataGridView导出到Excel文件
/// </summary>
/// <param name="dtSource">源DataTGridview</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="strFileName">保存位置</param>
public static void DataGridViewToExcel(DataGridView myDgv, string strHeaderText, string strFileName)
{
ExcelFileSuffixType suffix = default(ExcelFileSuffixType);
if (strFileName.IndexOf(".xlsx") > 0) // 2007版本
suffix = ExcelFileSuffixType.xlsx;
else if (strFileName.IndexOf(".xls") > 0) // 2003版本
suffix = ExcelFileSuffixType.xls;
using (MemoryStream ms = DataGridViewToExcelStream(myDgv, strHeaderText, suffix))
{
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
}
}
/// <summary>
/// 将excel文件导入至DataTable
/// </summary>
/// <param name="strFileName">excel文件全路径</param>
/// <returns></returns>
public static DataTable ExcelToDataTable(string strFileName)
{
DataTable dt = new DataTable();
IWorkbook workbook = null;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
#if New_NPOI
if (strFileName.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook(file);
else if (strFileName.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook(file);
#endif
#if Old_NPOI
workbook = new HSSFWorkbook(file);
#endif
}
ISheet sheet = workbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
IRow headerRow = sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int j = 0; j < cellCount; j++)
{
ICell cell = headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
dataRow[j] = row.GetCell(j).ToString();
}
dt.Rows.Add(dataRow);
}
return dt;
}
#region private
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="strHeaderText">excel文件后缀类型</param>
private static MemoryStream DataTableToExcelStream(DataTable dtSource, string strHeaderText, ExcelFileSuffixType suffix)
{
ISheet sheet = null;
IWorkbook workbook = null;
ICellStyle cellStyle = null;
IDataFormat dataFormat = null;
#if New_NPOI
if (suffix == ExcelFileSuffixType.xls)
workbook = new HSSFWorkbook();
else if (suffix == ExcelFileSuffixType.xlsx)
workbook = new XSSFWorkbook();
#endif
#if Old_NPOI
workbook = new HSSFWorkbook();
#endif
sheet = workbook.CreateSheet();
cellStyle = workbook.CreateCellStyle();
dataFormat = workbook.CreateDataFormat();
cellStyle.DataFormat = dataFormat.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 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
#if New_NPOI
font.IsBold = true;
#endif
#if Old_NPOI
font.Boldweight = 700;
#endif
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
//sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
#if New_NPOI
font.IsBold = true;
#endif
#if Old_NPOI
font.Boldweight = 700;
#endif
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
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String"://字符串类型
if (drValue.Length > 30000)
drValue = drValue.Substring(0, 30000);
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
System.DateTime dateV;
System.DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = cellStyle;//格式化显示
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++;
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
#if New_NPOI
workbook.Close();
#endif
return ms;
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="myDgv">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="strHeaderText">excel文件后缀类型</param>
private static MemoryStream DataGridViewToExcelStream(DataGridView myDgv, string strHeaderText, ExcelFileSuffixType suffix)
{
ISheet sheet = null;
IWorkbook workbook = null;
ICellStyle cellStyle = null;
IDataFormat dataFormat = null;
#if New_NPOI
if (suffix == ExcelFileSuffixType.xls)
workbook = new HSSFWorkbook();
else if (suffix == ExcelFileSuffixType.xlsx)
workbook = new XSSFWorkbook();
#endif
#if Old_NPOI
workbook = new HSSFWorkbook();
#endif
sheet = workbook.CreateSheet();
cellStyle = workbook.CreateCellStyle();
dataFormat = workbook.CreateDataFormat();
cellStyle.DataFormat = dataFormat.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[myDgv.Columns.Count];
foreach (DataGridViewColumn item in myDgv.Columns)
{
arrColWidth[item.Index] = Encoding.GetEncoding(936).GetBytes(item.HeaderText.ToString()).Length;
}
for (int i = 0; i < myDgv.Rows.Count; i++)
{
for (int j = 0; j < myDgv.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(myDgv.Rows[i].Cells[j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataGridViewRow row in myDgv.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
#if New_NPOI
font.IsBold = true;
#endif
#if Old_NPOI
font.Boldweight = 700;
#endif
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
// sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
#if New_NPOI
font.IsBold = true;
#endif
#if Old_NPOI
font.Boldweight = 700;
#endif
headStyle.SetFont(font);
foreach (DataGridViewColumn column in myDgv.Columns)
{
headerRow.CreateCell(column.Index).SetCellValue(column.HeaderText);
headerRow.GetCell(column.Index).CellStyle = headStyle;
//设置列宽
//sheet.SetColumnWidth(column.Index, (arrColWidth[column.Index] + 1) * 256);
}
// headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
if (row.Index > 0)
{
foreach (DataGridViewColumn column in myDgv.Columns)
{
ICell newCell = dataRow.CreateCell(column.Index);
string drValue = myDgv[column.Index, row.Index - 1].Value.ToString();
switch (column.ValueType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
System.DateTime dateV;
System.DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = cellStyle;//格式化显示
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;
}
}
}
else
{ rowIndex--; }
#endregion
rowIndex++;
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
#if New_NPOI
workbook.Close();
#endif
return ms;
}
#endregion
}
/// <summary>
/// excel文件后缀格式
/// </summary>
enum ExcelFileSuffixType
{
/// <summary>
/// Excel 2007及以上版本支持
/// </summary>
xls,
/// <summary>
/// Excel 2003及以上版本兼容支持
/// </summary>
xlsx
}
复制代码
注，测试NPOI版本为：2.5.1.0，包含的文件及依赖：
出处：https://www.cnblogs.com/seanyan/p/13813865.html
栏目列表
最新更新
nodejs爬虫
Python正则表达式完全指南
爬取豆瓣Top250图书数据
shp 地图文件批量添加字段
爬虫小试牛刀（爬取学校通知公告）
【python基础】函数-初识函数
【python基础】函数-返回值
HTTP请求：requests模块基础使用必知必会
Python初学者友好丨详解参数传递类型
如何有效管理爬虫流量？
2个场景实例讲解GaussDB(DWS)基表统计信息估
常用的 SQL Server 关键字及其含义
动手分析SQL Server中的事务中使用的锁
openGauss内核分析：SQL by pass & 经典执行
一招教你如何高效批量导入与更新数据
天天写SQL，这些神奇的特性你知道吗？
openGauss内核分析：执行计划生成
[IM002]Navicat ODBC驱动器管理器 未发现数据
初入Sql Server 之 存储过程的简单使用
SQL Server -- 解决存储过程传入参数作为s
关于JS定时器的整理
JS中使用Promise.all控制所有的异步请求都完
js中字符串的方法
import-local执行流程与node模块路径解析流程
检测数据类型的四种方法
js中数组的方法,32种方法
前端操作方法
数据类型
window.localStorage.setItem 和 localStorage.setIte
如何完美解决前端数字计算精度丢失与数
