using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Data;
using System.Diagnostics;
using System.IO;
namespace Intesim.Common
{
/// <summary>
/// Excel 工具类
/// </summary>
public class ExcelUtil
{
public const int MAX_ROW0 = 100; // 起始最大行
public const int MAX_COL0 = 100; // 起始最大列
public const int MAX_ROWS = 2000; // 数据最大行
public const int MAX_COLS = 255; // 数据最大列
public const int MIN_ROWS = 2; // 数据最少行数
public const int MIN_COLS = 2; // 数据最少列数
public const int MAX_SHEET = 255; // 最大sheet数
/// <summary>
/// 获取EXCEL单元格数据块
/// </summary>
/// <param name="fileName">EXCEL文件名</param>
/// <param name="sheetName">EXCEL sheet名</param>
/// <param name="startRow">开始行</param>
/// <param name="startCol">开始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endCol">结束列</param>
/// <returns>单元格数据数组</returns>
public static object[,] ReadExcelBlock(string fileName, string sheetName, int startRow, int startCol, int endRow, int endCol)
{
if(!File.Exists(fileName))
{
return null;
}
FileStream fs = null;
try
{
fs = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite);
ISheet sheet = null;
if (Path.GetExtension(fileName) == ".xlsx")
{
XSSFWorkbook workbook = new XSSFWorkbook(fs);
if (string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheetAt(0);
}
else
{
sheet = workbook.GetSheet(sheetName);
}
}
if (Path.GetExtension(fileName) == ".xls")
{
HSSFWorkbook workbook = new HSSFWorkbook(fs);
if (string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheetAt(0);
}
else
{
sheet = workbook.GetSheet(sheetName);
}
}
if (sheet == null)
{
return null;
}
int rows = endRow - startRow;
int cols = endCol - startCol;
if (rows < 1 || cols < 1)
{
return null;
}
Object[,] xldata = new Object[rows, cols];
for (int i = startRow; i < endRow; i++)
{
for (int j = startCol; j < endCol; j++)
{
xldata[i - startRow, j - startCol] = sheet.GetRow(i).GetCell(j);
}
}
return xldata;
}
catch (Exception e)
{
Debug.WriteLine(string.Format("ReadExcelBlock错误,消息:{0}", e.Message));
return null;
}
finally
{
fs.Close();
}
}
/// <summary>
/// 获取EXCEL单元格数据块
/// </summary>
/// <param name="fileName">EXCEL文件名</param>
/// <param name="sheetName">EXCEL sheet名</param>
/// <param name="startRow">开始行</param>
/// <param name="startCol">开始列</param>
/// <param name="endRow">结束行</param>
/// <param name="endCol">结束列</param>
/// <returns>单元格数据数组</returns>
public static ICell[,] ReadExcelBlockXML(string fileName, string sheetName, int startRow, int startCol, int endRow, int endCol)
{
using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite))
{
XSSFWorkbook workbook = new XSSFWorkbook(fs);
ISheet sheet = null;
if (string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheetAt(0);
}
else
{
sheet = workbook.GetSheet(sheetName);
}
if (sheet == null)
{
return null;
}
int rows = endRow - startRow + 1;
int cols = endCol - startCol + 1;
if (rows < 1 || cols < 1)
{
return null;
}
ICell[,] xldata = new XSSFCell[rows, cols];
try
{
for (int i = 0; i < rows; i++)
{
for (int j = 0; j < cols; j++)
{
IRow row = sheet.GetRow(i);
if (row != null)
{
xldata[i, j] = row.GetCell(j);
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine(string.Format("获取EXCEL单元格数据块出错,消息={0},堆栈={1}", ex.Message, ex.StackTrace));
}
fs.Close();
return xldata;
}
}
/// <summary>
/// NPOI读取Excel并返回读取的行数和列数
/// </summary>
/// <param name="fileName"></param>
/// <param name="sheetName"></param>
/// <param name="rows"></param>
/// <param name="cols"></param>
/// <returns>返回的数组,索引从(1,1)开始</returns>
public static object[,] ReadExcelBlockNPOI(string fileName, string sheetName, out int rows, out int cols)
{
rows = 0;
cols = 0;
if (!File.Exists(fileName))
{
return null;
}
FileStream fs = null;
try
{
fs = new FileStream(fileName, FileMode.Open, FileAccess.ReadWrite);//获取其路径的文件内容
ISheet sheet = null;
if (Path.GetExtension(fileName) == ".xlsx")
{
XSSFWorkbook workbook = new XSSFWorkbook(fs);
if (string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheetAt(0);
}
else
{
sheet = workbook.GetSheet(sheetName);
}
}
else if (Path.GetExtension(fileName) == ".xls")
{
HSSFWorkbook workbook = new HSSFWorkbook(fs);
if (string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheetAt(0);
}
else
{
sheet = workbook.GetSheet(sheetName);
}
}
if (sheet == null)
{
return null;
}
rows = sheet.LastRowNum + 1;//总行数
object[,] xldata = new object[rows, MAX_COLS];
for (int i = 0; i < rows; i++)
{
// 遇空行结束
IRow row = sheet.GetRow(i);
if (!row.HasValue())
{
continue;
}
for (int j = 0; j < row.LastCellNum; j++)
{
xldata[i, j] = sheet.GetRow(i).GetCell(j);
if (!xldata[i, j].HasValue())
{
break;
}
if (j + 1 > cols)
{
cols = j + 1;
}
}
}
return xldata;
}
catch (Exception e)
{
Debug.WriteLine(string.Format("ReadExcelBlockNPOI错误,消息:{0}", e.Message));
return null;
}
finally
{
fs.Close();
}
}
/// <summary>
/// NPOI方式创建并写EXCEL文件
/// </summary>
/// <param name="xlFile">EXCEL文件</param>
/// <param name="sheetName">EXCEL表名称</param>
/// <param name="str">输出字符串数组</param>
/// <param name="row0">输出起始行</param>
/// <param name="col0">输出起始列</param>
/// <param name="nRow">行数</param>
/// <param name="nCol">列数</param>
/// <returns>true=输出EXCEL文件成功, false=输出EXCEL文件失败</returns>
public static bool WriteNewExcel(string xlFile, string sheetName, string[,] str, int row0, int col0, int nRow, int nCol)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet(sheetName);
for (int i = 0; i < nRow - 1; i++)
{
IRow row = sheet.CreateRow(row0 + i);
for (int j = 0; j < nCol - 1; j++)
{
row.CreateCell(col0 + j).SetCellValue(str[i, j]);
}
}
using (FileStream fs = new FileStream(xlFile, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
workbook.Write(fs);
}
return true;
}
#region 辅助函数
/// <summary>
/// object二维数组转换为DataTable
/// </summary>
/// <param name="obj"></param>
/// <param name="maxColsNum">最大列数</param>
/// <param name="isAddNumCol">是否增加序号列,从1开始</param>
/// <param name="isContainHeader">是否包含列头</param>
/// <returns></returns>
public static DataTable objectTwoDimensionToDataTable(object[,] obj , int maxColsNum , bool isAddNumCol = false, bool isContainHeader = false)
{
if(!obj.HasValue())
{
return null;
}
try
{
DataTable dt = new DataTable();
for (int i = 0; i < maxColsNum; i++)
{
DataColumn col = new DataColumn(i.ToString(), typeof(object)); //列名默认为0,1,2.....
dt.Columns.Add(col);
}
for (int i = 0; i < obj.GetLength(0); i++)
{
DataRow row = dt.NewRow();
for (int j = 0; j < maxColsNum; j++)
{
row[j] = obj[i, j];
}
dt.Rows.Add(row);
}
if (isContainHeader)
{
dt.Rows.RemoveAt(0);
}
if (isAddNumCol)
{
dt.Columns.Add("num", typeof(int)).SetOrdinal(0); //插入序号列,默认列名为"num",类型为int
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["num"] = i + 1;
}
}
return dt;
}
catch(Exception e)
{
Debug.WriteLine(string.Format("objToDataTable错误,消息:{0}", e.Message));
return null;
}
}
#endregion
// TODO:-------------------------------------------------- 重新整理 ---------------------------------------------------
/// <summary>
/// 由DataSet导出Excel
/// </summary>
/// <param name="sourceTable">要导出数据的DataTable</param>
/// <param name="sheetName">工作表名称</param>
/// <returns>Excel工作表</returns>
private static Stream ExportDataSetToExcel(DataSet sourceDs, string sheetName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
string[] sheetNames = sheetName.Split(',');
for (int i = 0; i < sheetNames.Length; i++)
{
ISheet sheet = workbook.CreateSheet(sheetNames[i]);
IRow headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in sourceDs.Tables[i].Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
// handling value.
int rowIndex = 1;
foreach (DataRow row in sourceDs.Tables[i].Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceDs.Tables[i].Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
workbook = null;
return ms;
}
/// <summary>
/// 由DataTable导出Excel
/// </summary>
/// <param name="sourceTable">要导出数据的DataTable</param>
/// <returns>Excel工作表</returns>
public static bool ExportDataTableToExcel(DataTable sourceTable, string fileName ,string sheetName)
{
if(fileName.IsEmpty())
{
return false;
}
ISheet sheet = null;
if (Path.GetExtension(fileName).Equals(".xls"))
{
HSSFWorkbook workbook = new HSSFWorkbook();
sheet = workbook.CreateSheet(sheetName);
if (!sheet.HasValue())
{
return false;
}
FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write);
IRow headerRow = sheet.CreateRow(0);
foreach (DataColumn column in sourceTable.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
int rowIndex = 1;
foreach (DataRow row in sourceTable.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
workbook.Write(fs);
fs.Close();
return true;
}
else if(Path.GetExtension(fileName).Equals(".xlsx"))
{
XSSFWorkbook workbook = new XSSFWorkbook();
sheet = workbook.CreateSheet(sheetName);
if (!sheet.HasValue())
{
return false;
}
FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write);
IRow headerRow = sheet.CreateRow(0);
foreach (DataColumn column in sourceTable.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
int rowIndex = 1;
foreach (DataRow row in sourceTable.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
workbook.Write(fs);
fs.Close();
return true;
}
return false;
}
}
}