转自https://www.cnblogs.com/zuowj/archive/2015/05/04/4475663.html
转别人的，做了一点点改动
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.IO;
using System.Windows.Forms;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using System.Reflection; namespace RaysUtil.Office.Excel
{
//titleRow1.Cells[0].Hyperlink = new HSSFHyperlink(HyperlinkType.Url);
public static class ExcelHelper
{
/// <summary>
/// 获取保存文件路径
/// </summary>
/// <returns></returns>
private static string GetSaveFilePath()
{
SaveFileDialog saveFileDig = new SaveFileDialog();
saveFileDig.Filter = "Excel Office97 - 2003(*.xls) | *.xls | Excel Office2007及以上(*.xlsx) | *.xlsx";
saveFileDig.Title = "导出到";
saveFileDig.OverwritePrompt = true;
saveFileDig.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
string filePath = null;
if (saveFileDig.ShowDialog() == DialogResult.OK)
{
filePath = saveFileDig.FileName;
}
return filePath;
}
/// <summary>
/// 获取要打开要导入的文件名称（含完整路径）
/// </summary>
/// <returns></returns>
private static string GetOpenFilePath()
{
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "Excel Office97-2003(*.xls)|*.xls|Excel Office2007及以上(*.xlsx)|*.xlsx";
openFileDialog.FilterIndex = ;
openFileDialog.Title = "打开";
openFileDialog.CheckFileExists = true;
openFileDialog.CheckPathExists = true;
openFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
string filePath = null;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
filePath = openFileDialog.FileName;
}
return filePath;
}
/// <summary>
/// 是否兼容模式
/// </summary>
/// <param name="filePath">文件路径</param>
/// <returns></returns>
private static bool IsCompatible(string filePath)
{
//return filePath.EndsWith(".xls", StringComparison.OrdinalIgnoreCase);
if (Path.GetExtension(filePath).ToLower() == ".xls")
{
return true;
}
else if (Path.GetExtension(filePath).ToLower() == ".xlsx")
{
return false;
}
else
{
throw new Exception("文件扩展名非法或者文件路径不正确！！！");
}
}
/// <summary>
/// 创建工作簿
/// </summary>
/// <param name="isCompatible">是否兼容模式</param>
/// <returns></returns>
private static IWorkbook CreateWorkbook(bool isCompatible)
{
if (isCompatible)
{
return new HSSFWorkbook();
}
else
{
return new XSSFWorkbook();
}
}
/// <summary>
/// 依据文件流创建工作簿
/// </summary>
/// <param name="isCompatible"></param>
/// <param name="stream"></param>
/// <returns></returns>
private static IWorkbook CreateWorkbook(bool isCompatible, dynamic stream)
{
if (isCompatible)
{
return new HSSFWorkbook(stream);
}
else
{
return new XSSFWorkbook(stream);
}
}
/// <summary>
/// 创建表格单元格样式
/// </summary>
/// <param name="workbook">当前工作簿</param>
/// <param name="borderStyleB">是否有下边框,默认True</param>
/// <param name="borderStyleL">是否有左边框,默认True</param>
/// <param name="borderStyleR">是否有右边框,默认True</param>
/// <param name="borderStyleT">是否有上边框,默认True</param>
/// <param name="borderStyle">有边框的样式,默认薄边框</param>
/// <param name="colorIndex">背景色</param>
/// <param name="isAlignment">是否横向对齐，默认True</param>
/// <param name="horizontalAlignment">横向对齐,默认横向居中</param>
/// <param name="verticalAlignment">垂直对齐，默认垂直居中</param>
/// <param name="isSetFont">是否设置字体信息,默认False</param>
/// <param name="font">字体信息,默认null</param>
/// <param name="fontSize">字体大小</param>
/// <returns></returns>
private static ICellStyle GetCellStyle(IWorkbook workbook, bool borderStyleB = true, bool borderStyleL = true, bool borderStyleR = true, bool borderStyleT = true, NPOI.SS.UserModel.BorderStyle borderStyle = NPOI.SS.UserModel.BorderStyle.Thin, short colorIndex = HSSFColor.LightGreen.Index, bool isAlignment = true, NPOI.SS.UserModel.HorizontalAlignment horizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment.Center, VerticalAlignment verticalAlignment = VerticalAlignment.Center, bool isSetFont = false, IFont font = null, short fontSize = )
{
ICellStyle style = workbook.CreateCellStyle();
style.FillPattern = FillPattern.SolidForeground;
style.FillForegroundColor = colorIndex;
//边框
style.BorderBottom = borderStyleB ? borderStyle : NPOI.SS.UserModel.BorderStyle.None;
style.BorderLeft = borderStyleL ? borderStyle : NPOI.SS.UserModel.BorderStyle.None;
style.BorderRight = borderStyleR ? borderStyle : NPOI.SS.UserModel.BorderStyle.None;
style.BorderTop = borderStyleT ? borderStyle : NPOI.SS.UserModel.BorderStyle.None;
//对齐
if (isAlignment)
{
style.Alignment = horizontalAlignment;
}
else
{
style.VerticalAlignment = verticalAlignment;
}
if (isSetFont)
{
if (font == null)
{
font = workbook.CreateFont();
font.Boldweight = short.MaxValue;
font.FontHeightInPoints = fontSize;
}
style.SetFont(font);
}
return style;
}
/// <summary>
/// 创建表格单元格样式
/// </summary>
/// <param name="workbook">当前工作簿</param>
/// <param name="cellStyle">边框样式模板</param>
/// <returns></returns>
private static ICellStyle GetCellStyle(IWorkbook workbook, CellStyleModel cellStyle)
{
if (cellStyle==null)
{
cellStyle = new CellStyleModel();
}
ICellStyle style = workbook.CreateCellStyle();
style.FillPattern = FillPattern.SolidForeground;
style.FillForegroundColor = cellStyle.ColorIndex;
//边框
style.BorderBottom = cellStyle.BorderStyleB ? cellStyle.BorderStyle : NPOI.SS.UserModel.BorderStyle.None;
style.BorderLeft = cellStyle.BorderStyleL ? cellStyle.BorderStyle : NPOI.SS.UserModel.BorderStyle.None;
style.BorderRight = cellStyle.BorderStyleR ? cellStyle.BorderStyle : NPOI.SS.UserModel.BorderStyle.None;
style.BorderTop = cellStyle.BorderStyleT ? cellStyle.BorderStyle : NPOI.SS.UserModel.BorderStyle.None;
//对齐
if (cellStyle.IsAlignment)
{
style.Alignment = cellStyle.HorizontalAlignment;
}
else
{
style.VerticalAlignment = cellStyle.VerticalAlignment;
}
if (cellStyle.IsSetFont)
{
if (cellStyle.Font == null)
{
cellStyle.Font = workbook.CreateFont();
cellStyle.Font.Boldweight = short.MaxValue;
cellStyle.Font.FontHeightInPoints = cellStyle.FontSize;
}
style.SetFont(cellStyle.Font);
}
return style;
}
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="sheet">要合并单元格所在的sheet</param>
/// <param name="rowStart">开始行的索引</param>
/// <param name="rowEnd">结束行的索引</param>
/// <param name="colStart">开始列的索引</param>
/// <param name="colEnd">结束行的索引</param>
public static void SetCellRangeAddress(ISheet sheet, int rowStart, int rowEnd, int colStart, int colEnd)
{
CellRangeAddress cellRangeAddress = new CellRangeAddress(rowStart, rowEnd, colStart, colEnd);
sheet.AddMergedRegion(cellRangeAddress);
}
/// <summary>
/// 工作表生成DataTable
/// </summary>
/// <param name="sheet"></param>
/// <param name="headerRowIndex"></param>
/// <returns></returns>
private static DataTable GetDataTableFromSheet(ISheet sheet, int headerRowIndex)
{
DataTable table = new DataTable();
#region 操作首行(标题行)
//获取首行
IRow headerRow = sheet.GetRow(headerRowIndex);
//PhysicalNumberOfCells获取不为空的列个数
//LastCellNum获取最后一个不为空的列个数
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
{
//如果标题行遇到空列，则不再向后继续读取
cellCount = i + ;//返回真实列数
break;
}
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
#endregion
#region 遍历数据行，标题行除外
//遍历数据行，标题行除外
for (int i = (headerRowIndex + ); i < sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row != null && !string.IsNullOrEmpty(row.Cells[].StringCellValue))
{
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
dataRow[i] = row.GetCellEx(j).ToString();
}
table.Rows.Add(dataRow);
}
}
#endregion
return table;
}
#region 公共导出方法
/// <summary>
/// DataSet导出Excel
/// </summary>
/// <param name="sourceDs">DataSet源</param>
/// <param name="filePath">文件保存路径</param>
/// <param name="titles">首行标题数组</param>
/// <param name="childTitles">子标题数组</param>
/// <param name="dateTimes">子标题时间</param>
/// <param name="cellStyle">样式类</param>
/// <returns>返回生成的Excel保存路径</returns>
public static string ExportToExcel(DataSet sourceDs, string filePath, string[] titles = null, string[] childTitles = null, string[] dateTimes = null)
{
if (string.IsNullOrEmpty(filePath))
{
filePath = GetSaveFilePath();
}
if (string.IsNullOrEmpty(filePath)) throw new ArgumentNullException("excel文件路径为空字符串或null");
#region 检测标题、子标题、时间
if (titles == null)
{
titles = new string[sourceDs.Tables.Count];
for (int i = ; i < titles.Length; i++)
{
titles[i] = sourceDs.Tables[i].TableName;
}
}
if (dateTimes == null)
{
dateTimes = new string[sourceDs.Tables.Count];
for (int i = ; i < dateTimes.Length; i++)
{
titles[i] = DateTime.Now.ToString("yyyy-MM-dd");
}
}
if (titles != null && (titles.Length < sourceDs.Tables.Count || titles.Length > sourceDs.Tables.Count))
{
throw new Exception(string.Format("工作表行首Title参数个数应该为{0}", sourceDs.Tables.Count));
}
if (childTitles != null && (childTitles.Length < sourceDs.Tables.Count || childTitles.Length > sourceDs.Tables.Count))
{
throw new Exception(string.Format("工作表行首Title参数个数应该为{0}", sourceDs.Tables.Count));
}
if (dateTimes != null && (dateTimes.Length < sourceDs.Tables.Count || dateTimes.Length > sourceDs.Tables.Count))
{
throw new Exception(string.Format("工作表行首Title参数个数应该为{0}", sourceDs.Tables.Count));
}
#endregion bool isCompatible = IsCompatible(filePath);
IWorkbook workbook = CreateWorkbook(isCompatible);
//表头行样式
ICellStyle headerCellStyle = GetCellStyle(workbook, colorIndex: );
//数据行样式
ICellStyle dataCellStyle = GetCellStyle(workbook, horizontalAlignment: NPOI.SS.UserModel.HorizontalAlignment.Left, colorIndex: );
#region sheet处理
for (int i = ; i < sourceDs.Tables.Count; i++)
{
DataTable table = sourceDs.Tables[i];
string sheetName = string.IsNullOrEmpty(table.TableName) ? "sheet" + i.ToString() : table.TableName;
ISheet sheet = workbook.CreateSheet(sheetName);
IRow titleRow1 = sheet.CreateRow();
IRow titleRow2 = sheet.CreateRow();
IRow headerRow = sheet.CreateRow(); #region 处理首行
foreach (DataColumn column in table.Columns)
{
ICell cell = headerRow.CreateCell(column.Ordinal);
ICell titleCell1 = titleRow1.CreateCell(column.Ordinal);
ICell titleCell2 = titleRow2.CreateCell(column.Ordinal);
cell.SetCellValue(column.ColumnName);
cell.CellStyle = headerCellStyle;
}
//标题行样式
int cellMaxIndex = titleRow1.LastCellNum - ;
SetCellRangeAddress(sheet, , , , cellMaxIndex);
SetCellRangeAddress(sheet, , , , (int)Math.Floor(cellMaxIndex / 2.0));
SetCellRangeAddress(sheet, , , (int)Math.Floor(cellMaxIndex / 2.0) + , cellMaxIndex);
titleRow1.Cells[].SetCellValue(titles[i]);
if (childTitles != null)
{
titleRow2.Cells[].SetCellValue(childTitles[i]);
}
titleRow2.Cells[(int)Math.Floor(cellMaxIndex / 2.0) + ].SetCellValue(dateTimes[i]);
titleRow1.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleB: false, colorIndex: , isSetFont: true)); titleRow2.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleT: false, colorIndex: ));
titleRow2.Cells[].CellStyle = GetCellStyle(workbook, borderStyleT: false, borderStyleR: false, colorIndex: );
titleRow2.Cells[titleRow2.LastCellNum - ].CellStyle = GetCellStyle(workbook, borderStyleT: false, borderStyleL: false, colorIndex: );
titleRow1.HeightInPoints = ;
titleRow2.HeightInPoints = ;
#endregion #region 处理数据行，首行除外
int rowIndex = ;
foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in table.Columns)
{
ICell dataCell = dataRow.CreateCell(column.Ordinal);
dataCell.SetCellValue((row[column] ?? "").ToString());
dataCell.CellStyle = dataCellStyle;
}
rowIndex++;
}
#endregion
//设置列宽
for (int k = ; k < table.Columns.Count; k++)
{
sheet.SetColumnWidth(k, (table.Columns[k].Caption.Length < ? : table.Columns[k].Caption.Length) * );
}
}
#endregion
#region 保存
using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
workbook.Write(fs);
}
workbook = null;
#endregion
return filePath;
}
/// <summary>
/// DataSet导出Excel
/// </summary>
/// <param name="sourceDs">DataSet源</param>
/// <param name="filePath">文件保存路径</param>
/// <param name="titles">首行标题数组</param>
/// <param name="childTitles">子标题数组</param>
/// <param name="dateTimes">子标题时间</param>
/// <param name="cellStyle">样式类</param>
/// <returns></returns>
public static string ExportToExcel(DataSet sourceDs, string filePath, CellStyleModel cellStyle , string[] titles = null, string[] childTitles = null, string[] dateTimes = null)
{
if (string.IsNullOrEmpty(filePath))
{
filePath = GetSaveFilePath();
}
if (string.IsNullOrEmpty(filePath)) throw new ArgumentNullException("excel文件路径为空字符串或null");
#region 检测标题、子标题、时间
if (titles == null)
{
titles = new string[sourceDs.Tables.Count];
for (int i = ; i < titles.Length; i++)
{
titles[i] = sourceDs.Tables[i].TableName;
}
}
if (dateTimes == null)
{
dateTimes = new string[sourceDs.Tables.Count];
for (int i = ; i < dateTimes.Length; i++)
{
titles[i] = DateTime.Now.ToString("yyyy-MM-dd");
}
}
if (titles != null && (titles.Length < sourceDs.Tables.Count || titles.Length > sourceDs.Tables.Count))
{
throw new Exception(string.Format("工作表行首Title参数个数应该为{0}", sourceDs.Tables.Count));
}
if (childTitles != null && (childTitles.Length < sourceDs.Tables.Count || childTitles.Length > sourceDs.Tables.Count))
{
throw new Exception(string.Format("工作表行首Title参数个数应该为{0}", sourceDs.Tables.Count));
}
if (dateTimes != null && (dateTimes.Length < sourceDs.Tables.Count || dateTimes.Length > sourceDs.Tables.Count))
{
throw new Exception(string.Format("工作表行首Title参数个数应该为{0}", sourceDs.Tables.Count));
}
#endregion bool isCompatible = IsCompatible(filePath);
IWorkbook workbook = CreateWorkbook(isCompatible);
//表头行样式
ICellStyle headerCellStyle = GetCellStyle(workbook, cellStyle ?? new CellStyleModel { ColorIndex = });
//数据行样式 ICellStyle dataCellStyle = GetCellStyle(workbook, cellStyle ?? new CellStyleModel { HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment.Left, ColorIndex = });
#region sheet处理
for (int i = ; i < sourceDs.Tables.Count; i++)
{
DataTable table = sourceDs.Tables[i];
string sheetName = string.IsNullOrEmpty(table.TableName) ? "sheet" + i.ToString() : table.TableName;
ISheet sheet = workbook.CreateSheet(sheetName);
IRow titleRow1 = sheet.CreateRow();
IRow titleRow2 = sheet.CreateRow();
IRow headerRow = sheet.CreateRow(); #region 处理首行
foreach (DataColumn column in table.Columns)
{
ICell cell = headerRow.CreateCell(column.Ordinal);
ICell titleCell1 = titleRow1.CreateCell(column.Ordinal);
ICell titleCell2 = titleRow2.CreateCell(column.Ordinal);
cell.SetCellValue(column.ColumnName);
cell.CellStyle = headerCellStyle;
}
//标题行样式
int cellMaxIndex = titleRow1.LastCellNum - ;
SetCellRangeAddress(sheet, , , , cellMaxIndex);
SetCellRangeAddress(sheet, , , , (int)Math.Floor(cellMaxIndex / 2.0));
SetCellRangeAddress(sheet, , , (int)Math.Floor(cellMaxIndex / 2.0) + , cellMaxIndex);
titleRow1.Cells[].SetCellValue(titles[i]);
if (childTitles != null)
{
titleRow2.Cells[].SetCellValue(childTitles[i]);
}
titleRow2.Cells[(int)Math.Floor(cellMaxIndex / 2.0) + ].SetCellValue(dateTimes[i]);
titleRow1.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook,
cellStyle??new CellStyleModel { BorderStyleB=false,ColorIndex=,IsSetFont=true})); titleRow2.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook,
cellStyle??new CellStyleModel { BorderStyleT=false,ColorIndex=}));
titleRow2.Cells[].CellStyle = GetCellStyle(workbook,
cellStyle??new CellStyleModel { BorderStyleT=false,BorderStyleR=false,ColorIndex=});
titleRow2.Cells[titleRow2.LastCellNum - ].CellStyle = GetCellStyle(workbook,
cellStyle??new CellStyleModel { BorderStyleT=false,BorderStyleL=false,ColorIndex=});
titleRow1.HeightInPoints = ;
titleRow2.HeightInPoints = ;
#endregion #region 处理数据行，首行除外
int rowIndex = ;
foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in table.Columns)
{
ICell dataCell = dataRow.CreateCell(column.Ordinal);
dataCell.SetCellValue((row[column] ?? "").ToString());
dataCell.CellStyle = dataCellStyle;
}
rowIndex++;
}
#endregion
//设置列宽
for (int k = ; k < table.Columns.Count; k++)
{
sheet.SetColumnWidth(k, (table.Columns[k].Caption.Length < ? : table.Columns[k].Caption.Length) * );
}
}
#endregion
#region 保存
using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
workbook.Write(fs);
}
workbook = null;
#endregion
return filePath;
}
/// <summary>
/// DataTable导出Excel
/// </summary>
/// <param name="sourceTable">DataTable源</param>
/// <param name="sheetName">工作表名称</param>
/// <param name="filePath">文件保存路径</param>
/// <param name="title">首行标题</param>
/// <param name="childTitle">子标题</param>
/// <param name="dateTime">子标题时间</param>
/// <param name="cellStyle">样式类</param>
/// <returns>返回生成的Excel保存路径</returns>
public static string ExportToExcel(DataTable sourceTable, string sheetName = "sheet1", string filePath = null, string title = "", string childTitle = "", string dateTime = "")
{
if (sourceTable.Rows.Count <= ) throw new ArgumentException("DataTable源不存在有效的数据!");
if (string.IsNullOrEmpty(filePath))
{
filePath = GetSaveFilePath();
}
if (string.IsNullOrEmpty(filePath)) throw new ArgumentNullException("excel文件路径为空字符串或null!");
bool isCompatible = IsCompatible(filePath);
IWorkbook workbook = CreateWorkbook(isCompatible);
//表头行样式
ICellStyle headerCellStyle = GetCellStyle(workbook, colorIndex: );
//数据行样式
ICellStyle dataCellStyle = GetCellStyle(workbook, horizontalAlignment: NPOI.SS.UserModel.HorizontalAlignment.Left, colorIndex: );
ISheet sheet = workbook.CreateSheet(sheetName);
IRow titleRow1 = sheet.CreateRow();
IRow titleRow2 = sheet.CreateRow();
IRow headerRow = sheet.CreateRow(); #region 处理首行
foreach (DataColumn column in sourceTable.Columns)
{
ICell cell = headerRow.CreateCell(column.Ordinal);
ICell titleCell1 = titleRow1.CreateCell(column.Ordinal);
ICell titleCell2 = titleRow2.CreateCell(column.Ordinal);
cell.SetCellValue(column.ColumnName);
cell.CellStyle = headerCellStyle;
} //标题行样式
int cellMaxIndex = titleRow1.LastCellNum - ;
SetCellRangeAddress(sheet, , , , titleRow1.LastCellNum - );
SetCellRangeAddress(sheet, , , , (int)Math.Floor(cellMaxIndex / 2.0));
SetCellRangeAddress(sheet, , , (int)Math.Floor(cellMaxIndex / 2.0) + , cellMaxIndex); titleRow1.Cells[].SetCellValue(string.IsNullOrEmpty(title) ? sourceTable.TableName : title);
titleRow2.Cells[].SetCellValue(childTitle);
titleRow2.Cells[(int)Math.Floor(cellMaxIndex / 2.0) + ].SetCellValue(string.IsNullOrEmpty(dateTime) ? DateTime.Now.ToString("yyyy-MM-dd") : dateTime); titleRow1.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleB: false, colorIndex: , isSetFont: true)); //titleRow2.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleT: false, colorIndex: 32767));
titleRow2.Cells[].CellStyle = GetCellStyle(workbook, borderStyleT: false, borderStyleR: false, colorIndex: );
titleRow2.Cells[titleRow2.LastCellNum - ].CellStyle = GetCellStyle(workbook, borderStyleT: false, borderStyleL: false, colorIndex: ); titleRow1.HeightInPoints = ;
titleRow2.HeightInPoints = ;
#endregion #region 处理数据行，首行除外
int rowIndex = ;
foreach (DataRow row in sourceTable.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
ICell dataCell = dataRow.CreateCell(column.Ordinal);
dataCell.SetCellValue((row[column] ?? "").ToString());
dataCell.CellStyle = dataCellStyle;
}
rowIndex++;
}
//设置列宽
for (int k = ; k < sourceTable.Columns.Count; k++)
{
sheet.SetColumnWidth(k, (sourceTable.Columns[k].Caption.Length < ? : sourceTable.Columns[k].Caption.Length) * );
}
#endregion
#region 保存
using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
workbook.Write(fs);
}
sheet = null;
headerRow = null;
workbook = null;
#endregion
return filePath;
}
/// <summary>
/// DataTable导出Excel
/// </summary>
/// <param name="sourceTable">DataTable源</param>
/// <param name="sheetName">工作表名称</param>
/// <param name="filePath">文件保存路径</param>
/// <param name="title">首行标题</param>
/// <param name="childTitle">子标题</param>
/// <param name="dateTime">子标题时间</param>
/// <param name="cellStyle">样式类</param>
/// <returns></returns>
public static string ExportToExcel(DataTable sourceTable, CellStyleModel cellStyle, string sheetName = "sheet1", string filePath = null, string title = "", string childTitle = "", string dateTime = "")
{
if (sourceTable.Rows.Count <= ) throw new ArgumentException("DataTable源不存在有效的数据!");
if (string.IsNullOrEmpty(filePath))
{
filePath = GetSaveFilePath();
}
if (string.IsNullOrEmpty(filePath)) throw new ArgumentNullException("excel文件路径为空字符串或null!");
bool isCompatible = IsCompatible(filePath);
IWorkbook workbook = CreateWorkbook(isCompatible);
//表头行样式
ICellStyle headerCellStyle = GetCellStyle(workbook, cellStyle??new CellStyleModel { ColorIndex=});
//数据行样式
ICellStyle dataCellStyle = GetCellStyle(workbook, cellStyle??new CellStyleModel { HorizontalAlignment=NPOI.SS.UserModel.HorizontalAlignment.Left,ColorIndex=});
ISheet sheet = workbook.CreateSheet(sheetName);
IRow titleRow1 = sheet.CreateRow();
IRow titleRow2 = sheet.CreateRow();
IRow headerRow = sheet.CreateRow(); #region 处理首行
foreach (DataColumn column in sourceTable.Columns)
{
ICell cell = headerRow.CreateCell(column.Ordinal);
ICell titleCell1 = titleRow1.CreateCell(column.Ordinal);
ICell titleCell2 = titleRow2.CreateCell(column.Ordinal);
cell.SetCellValue(column.ColumnName);
cell.CellStyle = headerCellStyle;
} //标题行样式
int cellMaxIndex = titleRow1.LastCellNum - ;
SetCellRangeAddress(sheet, , , , titleRow1.LastCellNum - );
SetCellRangeAddress(sheet, , , , (int)Math.Floor(cellMaxIndex / 2.0));
SetCellRangeAddress(sheet, , , (int)Math.Floor(cellMaxIndex / 2.0) + , cellMaxIndex); titleRow1.Cells[].SetCellValue(string.IsNullOrEmpty(title) ? sourceTable.TableName : title);
titleRow2.Cells[].SetCellValue(childTitle);
titleRow2.Cells[(int)Math.Floor(cellMaxIndex / 2.0) + ].SetCellValue(string.IsNullOrEmpty(dateTime) ? DateTime.Now.ToString("yyyy-MM-dd") : dateTime); titleRow1.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook,
cellStyle??new CellStyleModel { BorderStyleB=false,ColorIndex=,IsSetFont=true}));
//titleRow2.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleT: false, colorIndex: 32767));
titleRow2.Cells[].CellStyle = GetCellStyle(workbook,
cellStyle??new CellStyleModel { BorderStyleT=false,BorderStyleR=false,ColorIndex=});
titleRow2.Cells[titleRow2.LastCellNum - ].CellStyle = GetCellStyle(workbook,
cellStyle??new CellStyleModel { BorderStyleT=false,BorderStyleL=false,ColorIndex=});
titleRow1.HeightInPoints = ;
titleRow2.HeightInPoints = ;
#endregion #region 处理数据行，首行除外
int rowIndex = ;
foreach (DataRow row in sourceTable.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
ICell dataCell = dataRow.CreateCell(column.Ordinal);
dataCell.SetCellValue((row[column] ?? "").ToString());
dataCell.CellStyle = dataCellStyle;
}
rowIndex++;
}
//设置列宽
for (int k = ; k < sourceTable.Columns.Count; k++)
{
sheet.SetColumnWidth(k, (sourceTable.Columns[k].Caption.Length < ? : sourceTable.Columns[k].Caption.Length) * );
}
#endregion
#region 保存
using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
workbook.Write(fs);
}
sheet = null;
headerRow = null;
workbook = null;
#endregion
return filePath;
}
/// <summary>
/// List导出Excel
/// </summary>
/// <typeparam name="T">List元素类型</typeparam>
/// <param name="data">List数据源</param>
/// <param name="headerNameList">首行数据映射源</param>
/// <param name="sheetName">工作表名称</param>
/// <param name="filePath">导出的文件地址</param>
/// <param name="title">首行标题</param>
/// <param name="childTitle">子标题</param>
/// <param name="dateTime">子标题时间</param>
/// <returns></returns>
public static string ExportToExcel<T>(List<T> data, IList<KeyValuePair<string, string>> headerNameList, string sheetName = "sheet1", string filePath = null, string title = "", string childTitle = "", string dateTime = "") where T : class
{
if (data.Count <= ) throw new ArgumentException("List数据源不存在有效的数据");
if (string.IsNullOrEmpty(filePath))
{
filePath = GetSaveFilePath();
}
if (string.IsNullOrEmpty(filePath)) throw new ArgumentNullException("excel文件路径为空字符串或null");
bool isCompatible = IsCompatible(filePath);
IWorkbook workbook = CreateWorkbook(isCompatible);
//表头行样式
ICellStyle headerCellStyle = GetCellStyle(workbook, colorIndex: );
//数据行样式
ICellStyle dataCellStyle = GetCellStyle(workbook, horizontalAlignment: NPOI.SS.UserModel.HorizontalAlignment.Left, colorIndex: );
ISheet sheet = workbook.CreateSheet(sheetName);
IRow titleRow1 = sheet.CreateRow();
IRow titleRow2 = sheet.CreateRow();
IRow headerRow = sheet.CreateRow(); #region 处理首行
for (int i = ; i < headerNameList.Count; i++)
{
ICell cell = headerRow.CreateCell(i);
ICell titleCell1 = titleRow1.CreateCell(i);
ICell titleCell2 = titleRow2.CreateCell(i);
cell.SetCellValue(headerNameList[i].Value);
cell.CellStyle = headerCellStyle;
}
//标题行样式
int cellMaxIndex = titleRow1.LastCellNum - ;
SetCellRangeAddress(sheet, , , , cellMaxIndex);
SetCellRangeAddress(sheet, , , , (int)Math.Floor(cellMaxIndex / 2.0));
SetCellRangeAddress(sheet, , , (int)Math.Floor(cellMaxIndex / 2.0) + , cellMaxIndex);
//Console.WriteLine("合并后列数是{0}",titleRow1.PhysicalNumberOfCells);
titleRow1.Cells[].SetCellValue(string.IsNullOrEmpty(title) ? sheetName : title);
titleRow2.Cells[].SetCellValue(childTitle);
titleRow2.Cells[(int)Math.Floor(cellMaxIndex / 2.0) + ].SetCellValue(string.IsNullOrEmpty(dateTime) ? DateTime.Now.ToString("yyyy-MM-dd") : dateTime);
titleRow1.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleB: false, colorIndex: , isSetFont: true)); //titleRow2.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleT: false, colorIndex: 32767));
titleRow2.Cells[].CellStyle = GetCellStyle(workbook, borderStyleT: false, borderStyleR: false, colorIndex: );
titleRow2.Cells[titleRow2.LastCellNum - ].CellStyle = GetCellStyle(workbook, borderStyleT: false, borderStyleL: false, colorIndex: );
titleRow1.HeightInPoints = ;
titleRow2.HeightInPoints = ;
#endregion
#region 处理数据行
Type t = typeof(T);
int rowIndex = ;
foreach (T item in data)
{
IRow dataRow = sheet.CreateRow(rowIndex);
for (int j = ; j < headerNameList.Count; j++)
{
object pValue = t.GetProperty(headerNameList[j].Key).GetValue(item, null);
ICell dataCell = dataRow.CreateCell(j);
dataCell.SetCellValue((pValue ?? "").ToString());
dataCell.CellStyle = dataCellStyle;
}
rowIndex++;
}
//设置列宽
for (int k = ; k < headerRow.Cells.Count; k++)
{
sheet.SetColumnWidth(k, (headerRow.Cells[k].StringCellValue.Length < ? : headerRow.Cells[k].StringCellValue.Length) * );
}
#endregion
FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
workbook.Write(fs);
fs.Dispose();
sheet = null;
headerRow = null;
workbook = null;
return filePath;
}
/// <summary>
/// List导出Excel
/// </summary>
/// <typeparam name="T">List元素类型</typeparam>
/// <param name="data">List数据源</param>
/// <param name="headerNameList">首行数据映射源</param>
/// <param name="sheetName">工作表名称</param>
/// <param name="filePath">导出的文件地址</param>
/// <param name="title">首行标题</param>
/// <param name="childTitle">子标题</param>
/// <param name="dateTime">子标题时间</param>
/// <param name="cellStyle">样式类</param>
/// <returns></returns>
public static string ExportToExcel<T>(List<T> data, IList<KeyValuePair<string, string>> headerNameList, CellStyleModel cellStyle, string sheetName = "sheet1", string filePath = null, string title = "", string childTitle = "", string dateTime = "") where T : class
{
if (data.Count <= ) throw new ArgumentException("List数据源不存在有效的数据");
if (string.IsNullOrEmpty(filePath))
{
filePath = GetSaveFilePath();
}
if (string.IsNullOrEmpty(filePath)) throw new ArgumentNullException("excel文件路径为空字符串或null");
bool isCompatible = IsCompatible(filePath);
IWorkbook workbook = CreateWorkbook(isCompatible);
//表头行样式
ICellStyle headerCellStyle = GetCellStyle(workbook, cellStyle??new CellStyleModel { ColorIndex=});
//数据行样式
ICellStyle dataCellStyle = GetCellStyle(workbook,
cellStyle??new CellStyleModel { HorizontalAlignment= NPOI.SS.UserModel.HorizontalAlignment.Left,ColorIndex=});
ISheet sheet = workbook.CreateSheet(sheetName);
IRow titleRow1 = sheet.CreateRow();
IRow titleRow2 = sheet.CreateRow();
IRow headerRow = sheet.CreateRow(); #region 处理首行
for (int i = ; i < headerNameList.Count; i++)
{
ICell cell = headerRow.CreateCell(i);
ICell titleCell1 = titleRow1.CreateCell(i);
ICell titleCell2 = titleRow2.CreateCell(i);
cell.SetCellValue(headerNameList[i].Value);
cell.CellStyle = headerCellStyle;
}
//标题行样式
int cellMaxIndex = titleRow1.LastCellNum - ;
SetCellRangeAddress(sheet, , , , cellMaxIndex);
SetCellRangeAddress(sheet, , , , (int)Math.Floor(cellMaxIndex / 2.0));
SetCellRangeAddress(sheet, , , (int)Math.Floor(cellMaxIndex / 2.0) + , cellMaxIndex);
//Console.WriteLine("合并后列数是{0}",titleRow1.PhysicalNumberOfCells);
titleRow1.Cells[].SetCellValue(string.IsNullOrEmpty(title) ? sheetName : title);
titleRow2.Cells[].SetCellValue(childTitle);
titleRow2.Cells[(int)Math.Floor(cellMaxIndex / 2.0) + ].SetCellValue(string.IsNullOrEmpty(dateTime) ? DateTime.Now.ToString("yyyy-MM-dd") : dateTime);
titleRow1.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook,
cellStyle??new CellStyleModel { BorderStyleB=false,ColorIndex=,IsSetFont=true}));
//titleRow2.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleT: false, colorIndex: 32767));
titleRow2.Cells[].CellStyle = GetCellStyle(workbook,
cellStyle??new CellStyleModel { BorderStyleT=false,BorderStyleR=false,ColorIndex=});
titleRow2.Cells[titleRow2.LastCellNum - ].CellStyle = GetCellStyle(workbook,
cellStyle??new CellStyleModel { BorderStyleT=false,BorderStyleL=false,ColorIndex=});
titleRow1.HeightInPoints = ;
titleRow2.HeightInPoints = ;
#endregion
#region 处理数据行
Type t = typeof(T);
int rowIndex = ;
foreach (T item in data)
{
IRow dataRow = sheet.CreateRow(rowIndex);
for (int j = ; j < headerNameList.Count; j++)
{
object pValue = t.GetProperty(headerNameList[j].Key).GetValue(item, null);
ICell dataCell = dataRow.CreateCell(j);
dataCell.SetCellValue((pValue ?? "").ToString());
dataCell.CellStyle = dataCellStyle;
}
rowIndex++;
}
//设置列宽
for (int k = ; k < headerRow.Cells.Count; k++)
{
sheet.SetColumnWidth(k, (headerRow.Cells[k].StringCellValue.Length < ? : headerRow.Cells[k].StringCellValue.Length) * );
}
#endregion
FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
workbook.Write(fs);
fs.Dispose();
sheet = null;
headerRow = null;
workbook = null;
return filePath;
}
/// <summary>
/// DataGridView导出Excel
/// </summary>
/// <param name="grid">DataGridView数据源</param>
/// <param name="sheetName">工作表名称</param>
/// <param name="filePath">文件路径</param>
/// <param name="title">首行标题</param>
/// <param name="childTitle">子标题</param>
/// <param name="dateTime">子标题时间</param>
/// <returns></returns>
public static string ExportToExcel(DataGridView grid, string sheetName = "sheet1", string filePath = null, string title = "", string childTitle = "", string dateTime = "")
{
if (grid.Rows.Count <= ) throw new ArgumentException("DataGridView数据源不存在游侠的数据！");
if (string.IsNullOrEmpty(filePath))
{
filePath = GetSaveFilePath();
}
if (string.IsNullOrEmpty(filePath)) throw new ArgumentNullException("excel文件路径为空字符串或null!");
bool isCompatible = IsCompatible(filePath);
IWorkbook workbook = CreateWorkbook(isCompatible);
//表头行样式
ICellStyle headerCellStyle = GetCellStyle(workbook, colorIndex: );
//数据行样式
ICellStyle dataCellStyle = GetCellStyle(workbook, horizontalAlignment: NPOI.SS.UserModel.HorizontalAlignment.Left, colorIndex: );
ISheet sheet = workbook.CreateSheet(sheetName);
IRow titleRow1 = sheet.CreateRow();
IRow titleRow2 = sheet.CreateRow();
IRow headerRow = sheet.CreateRow(); for (int i = ; i < grid.Columns.Count; i++)
{
ICell cell = headerRow.CreateCell(i);
ICell titleCell1 = titleRow1.CreateCell(i);
ICell titleCell2 = titleRow2.CreateCell(i);
cell.SetCellValue(grid.Columns[i].HeaderText);
cell.CellStyle = headerCellStyle;
}
//标题行样式
int cellMaxIndex = titleRow1.LastCellNum - ;
SetCellRangeAddress(sheet, , , , titleRow1.LastCellNum - );
SetCellRangeAddress(sheet, , , , (int)Math.Floor(cellMaxIndex / 2.0));
SetCellRangeAddress(sheet, , , (int)Math.Floor(cellMaxIndex / 2.0) + , cellMaxIndex);
titleRow1.Cells[].SetCellValue(string.IsNullOrEmpty(title) ? sheetName : title);
titleRow2.Cells[].SetCellValue(childTitle);
titleRow2.Cells[(int)Math.Floor(cellMaxIndex / 2.0) + ].SetCellValue(string.IsNullOrEmpty(dateTime) ? DateTime.Now.ToString("yyyy-MM-dd") : dateTime);
titleRow1.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleB: false, colorIndex: , isSetFont: true)); //titleRow2.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleT: false, colorIndex: 32767));
titleRow2.Cells[].CellStyle = GetCellStyle(workbook, borderStyleT: false, borderStyleR: false, colorIndex: );
titleRow2.Cells[titleRow2.LastCellNum - ].CellStyle = GetCellStyle(workbook, borderStyleT: false, borderStyleL: false, colorIndex: );
titleRow1.HeightInPoints = ;
titleRow2.HeightInPoints = ; int rowIndex = ;
foreach (DataGridViewRow row in grid.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
for (int j = ; j < grid.Columns.Count; j++)
{
ICell dataCell = dataRow.CreateCell(j);
dataCell.SetCellValue((row.Cells[j].Value ?? "").ToString());
dataCell.CellStyle = dataCellStyle;
}
rowIndex++;
} //设置列宽
for (int k = ; k < grid.Columns.Count; k++)
{
sheet.SetColumnWidth(k, (grid.Columns[k].HeaderText.Length < ? : grid.Columns[k].HeaderText.Length) * );
}
FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
workbook.Write(fs);
fs.Dispose();
sheet = null;
headerRow = null;
workbook = null;
return filePath;
}
/// <summary>
/// DataGridView导出Excel
/// </summary>
/// <param name="grid">DataGridView数据源</param>
/// <param name="sheetName">工作表名称</param>
/// <param name="filePath">文件路径</param>
/// <param name="title">首行标题</param>
/// <param name="childTitle">子标题</param>
/// <param name="dateTime">子标题时间</param>
/// <param name="cellStyle">样式类</param>
/// <returns></returns>
public static string ExportToExcel(DataGridView grid, CellStyleModel cellStyle, string sheetName = "sheet1", string filePath = null, string title = "", string childTitle = "", string dateTime = "")
{
if (grid.Rows.Count <= ) throw new ArgumentException("DataGridView数据源不存在游侠的数据！");
if (string.IsNullOrEmpty(filePath))
{
filePath = GetSaveFilePath();
}
if (string.IsNullOrEmpty(filePath)) throw new ArgumentNullException("excel文件路径为空字符串或null!");
bool isCompatible = IsCompatible(filePath);
IWorkbook workbook = CreateWorkbook(isCompatible);
//表头行样式
ICellStyle headerCellStyle = GetCellStyle(workbook, cellStyle??new CellStyleModel { ColorIndex=});
//数据行样式
ICellStyle dataCellStyle = GetCellStyle(workbook,cellStyle??new CellStyleModel { HorizontalAlignment=NPOI.SS.UserModel.HorizontalAlignment.Left,ColorIndex=});
ISheet sheet = workbook.CreateSheet(sheetName);
IRow titleRow1 = sheet.CreateRow();
IRow titleRow2 = sheet.CreateRow();
IRow headerRow = sheet.CreateRow(); for (int i = ; i < grid.Columns.Count; i++)
{
ICell cell = headerRow.CreateCell(i);
ICell titleCell1 = titleRow1.CreateCell(i);
ICell titleCell2 = titleRow2.CreateCell(i);
cell.SetCellValue(grid.Columns[i].HeaderText);
cell.CellStyle = headerCellStyle;
}
//标题行样式
int cellMaxIndex = titleRow1.LastCellNum - ;
SetCellRangeAddress(sheet, , , , titleRow1.LastCellNum - );
SetCellRangeAddress(sheet, , , , (int)Math.Floor(cellMaxIndex / 2.0));
SetCellRangeAddress(sheet, , , (int)Math.Floor(cellMaxIndex / 2.0) + , cellMaxIndex);
titleRow1.Cells[].SetCellValue(string.IsNullOrEmpty(title) ? sheetName : title);
titleRow2.Cells[].SetCellValue(childTitle);
titleRow2.Cells[(int)Math.Floor(cellMaxIndex / 2.0) + ].SetCellValue(string.IsNullOrEmpty(dateTime) ? DateTime.Now.ToString("yyyy-MM-dd") : dateTime);
titleRow1.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook,
cellStyle??new CellStyleModel { BorderStyleB=false,ColorIndex=,IsSetFont=true}));
//titleRow2.Cells.ForEach(c => c.CellStyle = GetCellStyle(workbook, borderStyleT: false, colorIndex: 32767));
titleRow2.Cells[].CellStyle = GetCellStyle(workbook,
cellStyle??new CellStyleModel { BorderStyleT=false,BorderStyleR=false,ColorIndex=});
titleRow2.Cells[titleRow2.LastCellNum - ].CellStyle = GetCellStyle(workbook,
cellStyle??new CellStyleModel { BorderStyleT=false,BorderStyleL=false,ColorIndex=});
titleRow1.HeightInPoints = ;
titleRow2.HeightInPoints = ;
int rowIndex = ;
foreach (DataGridViewRow row in grid.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
for (int j = ; j < grid.Columns.Count; j++)
{
ICell dataCell = dataRow.CreateCell(j);
dataCell.SetCellValue((row.Cells[j].Value ?? "").ToString());
dataCell.CellStyle = dataCellStyle;
}
rowIndex++;
} //设置列宽
for (int k = ; k < grid.Columns.Count; k++)
{
sheet.SetColumnWidth(k, (grid.Columns[k].HeaderText.Length < ? : grid.Columns[k].HeaderText.Length) * );
}
FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
workbook.Write(fs);
fs.Dispose();
sheet = null;
headerRow = null;
workbook = null;
return filePath;
}
#endregion
#region 公共导入方法
/// <summary>
/// 由Excel导入DataTable
/// </summary>
/// <param name="excelFileStream">Excel文件流</param>
/// <param name="sheetName">工作表名称</param>
/// <param name="headerRowIndex">表头行索引</param>
/// <param name="isCompatible">是否兼容模式</param>
/// <returns>返回DataTable</returns>
public static DataTable ImportFromExcel(Stream excelFileStream, string sheetName, int headerRowIndex, bool isCompatible)
{
IWorkbook workbook = CreateWorkbook(isCompatible, excelFileStream);
ISheet sheet = null;
int sheetIndex = -;
if (int.TryParse(sheetName, out sheetIndex))
{
sheet = workbook.GetSheetAt(sheetIndex);
}
else
{
sheet = workbook.GetSheet(sheetName);
}
DataTable table = GetDataTableFromSheet(sheet, headerRowIndex);
excelFileStream.Close();
workbook = null;
sheet = null;
return table;
}
/// <summary>
/// 由Excel导入DataTable
/// </summary>
/// <param name="excelFilePath">Excel文件路径</param>
/// <param name="sheetName">工作表名称</param>
/// <param name="headerRowIndex">表头行索引</param>
/// <returns></returns>
public static DataTable ImportFromExcel(string excelFilePath, string sheetName, int headerRowIndex)
{
if (string.IsNullOrEmpty(excelFilePath))
{
excelFilePath = GetOpenFilePath();
}
if (string.IsNullOrEmpty(excelFilePath)) throw new ArgumentNullException("excel文件路径为空字符串或null");
using (FileStream fs = File.OpenRead(excelFilePath))
{
bool isCompatible = IsCompatible(excelFilePath);
return ImportFromExcel(fs, sheetName, headerRowIndex, isCompatible);
}
}
/// <summary>
/// Excel导入DataSet,如果有多个工作表，则导入多个DataTable
/// </summary>
/// <param name="excelFileStream">Excel文件流</param>
/// <param name="headRowIndex">表头行索引</param>
/// <param name="isCompatible">是否兼容模式</param>
/// <returns>返回dataSet</returns>
public static DataSet ImportFromExcel(Stream excelFileStream, int headerRowIndex, bool isCompatible)
{
DataSet ds = new DataSet();
IWorkbook workbook = CreateWorkbook(isCompatible, excelFileStream);
for (int i = ; i < workbook.NumberOfSheets; i++)
{
ISheet sheet = workbook.GetSheetAt(i);
DataTable table = GetDataTableFromSheet(sheet, headerRowIndex);
ds.Tables.Add(table);
}
excelFileStream.Close();
workbook = null;
return ds;
}
/// <summary>
/// Excel导入DataSet,如果有多个工作表，则导入多个DataTable
/// </summary>
/// <param name="excelFilePath">Excel文件路径</param>
/// <param name="headerRowIndex">表头行索引</param>
/// <returns>返回dataSet</returns>
public static DataSet ImportFromExcel(string excelFilePath, int headerRowIndex)
{
if (string.IsNullOrEmpty(excelFilePath))
{
excelFilePath = GetOpenFilePath();
}
if (string.IsNullOrEmpty(excelFilePath)) throw new ArgumentNullException("excel文件路径为空字符串或null");
using (FileStream fs = File.OpenRead(excelFilePath))
{
bool isCompatible = IsCompatible(excelFilePath);
return ImportFromExcel(fs, headerRowIndex, isCompatible);
}
}
/// <summary>
/// Excel导入List
/// </summary>
/// <typeparam name="T">实体类</typeparam>
/// <param name="excelFilePath">Excel文件全路径</param>
/// <param name="headerRowIndex">行索引</param>
/// <returns></returns>
public static IList<T> ImportFromExcel<T>(string excelFilePath, int headerRowIndex = ) where T : class, new()
{
IList<T> list = new List<T>();
if (string.IsNullOrEmpty(excelFilePath))
{
excelFilePath = GetOpenFilePath();
}
if (string.IsNullOrEmpty(excelFilePath)) throw new ArgumentNullException("excel文件路径为空字符串或null");
try
{
using (FileStream fs = File.OpenRead(excelFilePath))
{
bool isComPatible = IsCompatible(excelFilePath);
IWorkbook workbook = CreateWorkbook(isComPatible, fs);
ISheet sheet = workbook.GetSheetAt();
IRow headerRow = sheet.GetRow();
int headerCellNum = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < headerCellNum; i++)
{
if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
{
//如果标题行遇到空列，则不再向后继续读取
headerCellNum = i + ;//返回真实列数
break;
}
}
//T t = default(T);
T t = new T();
PropertyInfo[] properties = typeof(T).GetProperties();
if (properties.Count() < headerCellNum)
{
throw new Exception("对象属性与工作表字段数量不符！");
}
for (int i = headerRowIndex + ; i < sheet.LastRowNum + ; i++)
{
IRow row = sheet.GetRow(i);
if (row != null && !string.IsNullOrEmpty(row.Cells[].StringCellValue))
{
t = Activator.CreateInstance<T>();
for (int j = row.FirstCellNum; j < headerCellNum; j++)
{
//如果碰到空cell，那么创建cell后赋空字符串
var value = ValueType(properties[j].PropertyType, row.GetCellEx(j).ToString());
properties[j].SetValue(t, value, null);
}
list.Add(t); }
}
}
}
catch (Exception ex)
{
list = null;
throw new Exception(ex.Message);
}
return list;
}
/// <summary>
/// Excel导入List
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="excelFilePath">excel文件路径</param>
/// <param name="fields">实体属性的字符串表示形式的数组，顺序与excel字段保持一致</param>
/// <param name="headerRowIndex">头部索引行</param>
/// <returns></returns>
public static IList<T> ImportFromExcel<T>(ISheet sheet, string[] fields, int headerRowIndex = ) where T : class, new()
{
if (fields == null) throw new ArgumentNullException("fields", "参数不能为null!");
IList<T> list = new List<T>();
try
{
IRow headerRow = sheet.GetRow();
int headerCellNum = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < headerCellNum; i++)
{
if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
{
//如果标题行遇到空列，则不再向后继续读取
headerCellNum = i + ;//返回真实列数
break;
}
}
if (fields.Length != headerCellNum)
{
throw new Exception("指定的对象属性数量超过工作表字段数量！");
}
//T t = default(T);
T t = new T();
for (int i = headerRowIndex + ; i < sheet.LastRowNum + ; i++)
{
IRow row = sheet.GetRow(i);
if (row != null && !string.IsNullOrEmpty(row.Cells[].StringCellValue))
{
t = Activator.CreateInstance<T>();
for (int j = ; j < fields.Length; j++)
{
var property = typeof(T).GetProperty(fields[j]);
//如果碰到空cell，那么创建cell后赋空字符串
var value = ValueType(property.PropertyType, row.GetCellEx(j).ToString());
property.SetValue(t, value, null);
}
list.Add(t);
}
}
}
catch (Exception ex)
{
list = null;
throw new Exception(ex.Message);
}
return list;
}
/// <summary>
/// Excel导入List
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="excelFilePath">excel文件全路径</param>
/// <param name="fields">实体属性的字符串表示形式的数组，顺序与excel字段保持一致</param>
/// <param name="sheetName">工作表名</param>
/// <param name="headerRowIndex">首行索引，默认0</param>
/// <returns></returns>
public static IList<T> ImportFromExcel<T>(string excelFilePath, string[] fields, string sheetName, int headerRowIndex = ) where T : class, new()
{
if (string.IsNullOrEmpty(excelFilePath))
{
excelFilePath = GetOpenFilePath();
}
if (string.IsNullOrEmpty(excelFilePath)) throw new ArgumentNullException("excel文件路径为空字符串或null");
FileStream fs = null;
IWorkbook workbook = null;
IList<T> list = null;
try
{
using (fs = File.OpenRead(excelFilePath))
{
bool isComPatible = IsCompatible(excelFilePath);
workbook = CreateWorkbook(isComPatible, fs);
list = ImportFromExcel<T>(workbook.GetSheet(sheetName), fields, headerRowIndex);
}
}
catch (Exception ex)
{
workbook = null;
if (fs != null) fs.Dispose();
list = null;
throw new Exception(ex.Message);
}
return list;
}
/// <summary>
/// Excel导入List
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="excelFilePath">excel文件全路径</param>
/// <param name="fields">实体属性的字符串表示形式的数组，顺序与excel字段保持一致</param>
/// <param name="sheetIndex">第几个工作表，默认值为1</param>
/// <param name="headerRowIndex">首行索引，默认0</param>
/// <returns></returns>
public static IList<T> ImportFromExcel<T>(string excelFilePath, string[] fields, int sheetIndex = , int headerRowIndex = ) where T : class, new()
{
if (string.IsNullOrEmpty(excelFilePath))
{
excelFilePath = GetOpenFilePath();
}
if (string.IsNullOrEmpty(excelFilePath)) throw new ArgumentNullException("excel文件路径为空字符串或null");
FileStream fs = null;
IWorkbook workbook = null;
IList<T> list = null;
try
{
using (fs = File.OpenRead(excelFilePath))
{
bool isComPatible = IsCompatible(excelFilePath);
workbook = CreateWorkbook(isComPatible, fs);
list = ImportFromExcel<T>(workbook.GetSheetAt(sheetIndex - ), fields, headerRowIndex);
}
}
catch (Exception ex)
{
workbook = null;
if (fs != null) fs.Dispose();
list = null;
throw new Exception(ex.Message);
}
return list;
}
#endregion
#region 公共转换方法
/// <summary>
/// 将Excel的列索引转换为列名，列索引从0开始，列名从A开始。如第0列为A，第1列为B...
/// </summary>
/// <param name="index">列索引</param>
/// <returns>列名，如第0列为A，第1列为B...</returns>
public static string ConvertColumnIndexToColumnName(int index)
{
index++;
int letterCount = ;
char[] array = new char[];
int i = ;
while (index > )
{
int mod = index % letterCount;
if (mod == ) mod = letterCount;
array[i++] = (char)(mod - + 'A');
index = (index - ) / ;
}
StringBuilder sb = new StringBuilder(i);
for (int j = i - ; j >= ; j--)
{
sb.Append(array[j]);
}
return sb.ToString();
}
/// <summary>
/// 转化日期
/// </summary>
/// <param name="date">日期</param>
/// <returns></returns>
public static DateTime ConvertDate(object date)
{
string dateString = (date ?? "").ToString();
DateTime dt = new DateTime();
if (DateTime.TryParse(dateString, out dt))
{
return dt;
}
try
{
string spString = "";
if (dateString.Contains("-"))
{
spString = "-";
}
else if (dateString.Contains("/"))
{
spString = "/";
}
string[] time = dateString.Split(spString.ToCharArray());
int year = Convert.ToInt32(time[]);
int month = Convert.ToInt32(time[]);
int day = Convert.ToInt32(time[]);
string years = Convert.ToString(year);
string months = Convert.ToString(month);
string days = Convert.ToString(day);
if (months.Length == )
{
dt = Convert.ToDateTime(date);
}
else
{
string rq = "";
if (years.Length == )
{
years = "" + years;
}
if (months.Length == )
{
months = "" + months;
}
if (days.Length == )
{
days = "" + days;
}
rq = "" + years + "-" + months + "-" + days;
dt = Convert.ToDateTime(rq);
}
}
catch
{
throw new Exception("日期格式不正确，转换日期失败！");
}
return dt;
}
/// <summary>
/// 转化数字
/// </summary>
/// <param name="d">数字字符串</param>
/// <returns></returns>
public static decimal ConvertDecimal(object d)
{
string dString = (d ?? "").ToString();
decimal result = 0m;
if (decimal.TryParse(dString, out result))
{
return result;
}
else
{
throw new Exception("数字格式不正确,转换数字失败!");
}
}
/// <summary>
/// 转换布尔
/// </summary>
/// <param name="b">布尔值字符串</param>
/// <returns></returns>
public static bool ConvertBoolean(object b)
{
string bString = (b ?? "").ToString();
bool result = false;
if (bool.TryParse(bString, out result))
{
return result;
}
else if (bString == "" || bString == "")
{
return (bString == "");
}
else
{
throw new Exception("布尔格式不正确，转换布尔类型失败！");
}
}
/// <summary>
/// 实体类属性类型与Excel字段类型的转换
/// </summary>
/// <param name="t"></param>
/// <param name="value"></param>
/// <returns></returns>
public static object ValueType(Type t, string value)
{
object o = null;
string strType = t.Name;
if (strType == "Nullable`1")
{
strType = t.GetGenericArguments()[].Name;
}
switch (strType)
{
case "Decimal":
o = decimal.Parse(value);
break;
case "Int32":
o = int.Parse(value);
break;
case "Float":
o = float.Parse(value);
break;
case "DateTime":
o = DateTime.Parse(value);
break;
case "Char":
o = char.Parse(value);
break;
case "Boolean":
if (new string[] { "",""}.Contains(value))
{
o = Convert.ToBoolean(int.Parse(value));
}
else
{
o = bool.Parse(value);
}
break;
default:
o = value;
break;
}
return o;
}
#endregion }
}
扩展IRow的GetCell方法
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text; namespace RaysUtil.Office.Excel
{
public static class NpoiEx
{
/// <summary>
/// 如果当前单元格为空，创建新的字符串类型的单元格
/// </summary>
/// <param name="row"></param>
/// <param name="index"></param>
/// <returns></returns>
public static ICell GetCellEx(this IRow row,int index)
{
return row.FirstOrDefault(n => n.ColumnIndex == index) ?? row.CreateCell(index,CellType.String);
}
}
}
样式类
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text; namespace RaysUtil.Office.Excel
{
//==============================================================
// 作者：*
// 时间：2018/1/6 21:06:18
// 文件名：CellStyleModel
// 版本：V1.0.1
// 说明：
// 修改者：**
// 修改说明： 分离出样式，单独写一个类
//==============================================================
public class CellStyleModel
{
/// <summary>
/// 是否存在下边框，默认true
/// </summary>
public bool BorderStyleB { get; set; } = true;
/// <summary>
/// 是否存在左边框，默认true
/// </summary>
public bool BorderStyleL { get; set; } = true;
/// <summary>
/// 是否存在右边框,默认true
/// </summary>
public bool BorderStyleR { get; set; } = true;
/// <summary>
/// 是否存在上边框,默认true
/// </summary>
public bool BorderStyleT { get; set; } = true;
/// <summary>
/// 有边框的样式,默认薄边框
/// </summary>
public BorderStyle BorderStyle { get; set; } = BorderStyle.Thin;
/// <summary>
/// 背景色
/// </summary>
public short ColorIndex { get; set; } = HSSFColor.LightGreen.Index;
/// <summary>
/// 是否横向对齐，默认True
/// </summary>
public bool IsAlignment { get; set; } = true;
/// <summary>
/// 横向对齐,默认横向居中
/// </summary>
public HorizontalAlignment HorizontalAlignment { get; set; } = HorizontalAlignment.Center;
/// <summary>
/// 垂直对齐，默认垂直居中
/// </summary>
public VerticalAlignment VerticalAlignment { get; set; } = VerticalAlignment.Center;
/// <summary>
/// 是否设置字体信息,默认False
/// </summary>
public bool IsSetFont { get; set; } = false;
/// <summary>
/// 字体信息,默认null
/// </summary>
public IFont Font { get; set; } = null;
/// <summary>
/// 字体大小，默认30
/// </summary>
public short FontSize { get; set; } = ;
}
}