using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
namespace SmartIdentification
{
public class ExcelHelper
{
/// <summary>
/// 工作薄
/// </summary>
public HSSFWorkbook WorkBook { get; set; }
/// <summary>
/// 表
/// </summary>
public ISheet Sheet { get; set; }
/// <summary>
/// 路径
/// </summary>
public string ExcelPath { get; set; }
public ExcelHelper()
{
//WorkBook = new HSSFWorkbook();
//Sheet = WorkBook.CreateSheet();
}
public ExcelHelper(string filePath)
{
//把xls文件中的数据写入wk中
using (FileStream fs = File.OpenRead(filePath))
{
WorkBook = new HSSFWorkbook(fs);
Sheet = WorkBook.GetSheetAt(0);
ExcelPath = filePath;
}
}
public ExcelHelper(string path, string fileName)
{
//把xls文件中的数据写入wk中
using (FileStream fs = File.OpenRead(path + "/" + fileName + ".xls"))
{
WorkBook = new HSSFWorkbook(fs);
Sheet = WorkBook.GetSheetAt(0);
ExcelPath = path + "/" + fileName + ".xls";
}
}
public ExcelHelper(string path, string fileName, string sheetName)
{
using (FileStream fs = File.OpenRead(path + "/" + fileName + ".xls"))
{
WorkBook = new HSSFWorkbook(fs);
Sheet = WorkBook.GetSheet(sheetName);
ExcelPath = path + "/" + fileName + ".xls";
}
}
/// <summary>
/// 创建xls文件
/// </summary>
/// <param name="path"></param>
/// <param name="fileName"></param>
public void Create(string path, string fileName, string sheetName)
{
//创建工作薄
WorkBook = new HSSFWorkbook();
//创建一个表
Sheet = WorkBook.CreateSheet(sheetName);
using (FileStream fs = File.OpenWrite(path + "/" + fileName + ".xls")) //打开一个xls文件,如果没有则自行创建,如果存在myxls.xls文件则在创建是不要打开该文件!
{
WorkBook.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。
}
}
public void SetValue(int rowIndex, int columnIndex)
{
}
public DataTable ToDataTable(int index, bool header)
{
return ToDataTable(ExcelPath,index,header);
}
public DataTable ToDataTable(string excel, int index, bool header)
{
DataTable dt = new DataTable(Path.GetFileNameWithoutExtension(excel) + "_Sheet" + index);
IWorkbook workbook;
using (FileStream file = new FileStream(excel, FileMode.Open, FileAccess.Read))
{
workbook = new HSSFWorkbook(file);
}
ISheet sheet = workbook.GetSheetAt(index);
var rows = sheet.GetRowEnumerator();
rows.MoveNext();
IRow row = (IRow)rows.Current;
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
string columnName = header ? cell.StringCellValue : i.ToString();
dt.Columns.Add(columnName, typeof(string));
}
if (!header)
{
DataRow first = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
first[i] = cell.StringCellValue;
} dt.Rows.Add(first);
}
while (rows.MoveNext())
{
row = (IRow)rows.Current;
DataRow dataRow = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
if (cell != null)
{
dataRow[i] = cell.ToString();
#region
//try
//{
// dataRow[i] = cell.StringCellValue;
//}
//catch
//{
// try
// {
// dataRow[i] = cell.NumericCellValue;
// }
// catch
// {
// try
// {
// dataRow[i] = cell.BooleanCellValue;
// }
// catch
// {
// try
// {
// dataRow[i] = cell.DateCellValue;
// }
// catch
// {
// try
// {
// dataRow[i] = cell.ErrorCellValue;
// }
// catch
// {
// dataRow[i] = cell.RichStringCellValue;
// }
// }
// }
// }
//}
#endregion
}
}
dt.Rows.Add(dataRow);
}
return dt;
}
public bool ToExcel(string savePath,DataTable table, bool header)
{
//创建工作薄
HSSFWorkbook wk = new HSSFWorkbook();
//创建一个名称为mySheet的表
ISheet tb = wk.CreateSheet(Sheet.SheetName);
int rowIndex = 0;
if (header)
{
rowIndex = 1;
IRow row = tb.CreateRow(0);//创建一行
for (int i = 0; i < table.Columns.Count; i++)
{
ICell cell = row.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
cell.SetCellValue(table.Columns[i].ToString());//循环往第二行的单元格中添加数据
}
}
for (int i = 0; i < table.Rows.Count; i++)
{
IRow row = tb.CreateRow(rowIndex+i);//创建一行
for (int c = 0; c < table.Columns.Count; c++)
{
ICell cell = row.GetCell(c, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
cell.SetCellValue(table.Rows[i][c].ToString());//循环往第二行的单元格中添加数据
}
}
using (FileStream fs = File.OpenWrite(savePath)) //打开一个xls文件,如果没有则自行创建,如果存在myxls.xls文件则在创建是不要打开该文件!
{
wk.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。
}
return true;
}
public void SetValue(int rowIndex, int columnIndex, string value)
{
IRow row = Sheet.GetRow(rowIndex); //读取当前行数据
ICell cell = row.GetCell(columnIndex);
cell.SetCellValue(value);
}
#region 私有方法
/// <summary>
/// 获取一行,如果没有则创建
/// </summary>
/// <param name="rowIndex"></param>
/// <returns></returns>
private IRow GetRowByExtend(int rowIndex)
{
IRow row = Sheet.GetRow(rowIndex);
if (row == null)
row = Sheet.CreateRow(rowIndex);
return row;
}
private string GetCellValue(ICell cell)
{
if (cell == null)
return string.Empty;
switch (cell.CellType)
{
case CellType.BLANK:
return string.Empty;
case CellType.BOOLEAN:
return cell.BooleanCellValue.ToString();
case CellType.ERROR:
return cell.ErrorCellValue.ToString();
case CellType.NUMERIC:
case CellType.Unknown:
default:
return cell.ToString();//This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number
case CellType.STRING:
return cell.StringCellValue;
case CellType.FORMULA:
try
{
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
e.EvaluateInCell(cell);
return cell.ToString();
}
catch
{
return cell.NumericCellValue.ToString();
}
}
}
#endregion
}
}
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
namespace SmartIdentification
{
public class ExcelHelper
{
/// <summary>
/// 工作薄
/// </summary>
public HSSFWorkbook WorkBook { get; set; }
/// <summary>
/// 表
/// </summary>
public ISheet Sheet { get; set; }
/// <summary>
/// 路径
/// </summary>
public string ExcelPath { get; set; }
public ExcelHelper()
{
//WorkBook = new HSSFWorkbook();
//Sheet = WorkBook.CreateSheet();
}
public ExcelHelper(string filePath)
{
//把xls文件中的数据写入wk中
using (FileStream fs = File.OpenRead(filePath))
{
WorkBook = new HSSFWorkbook(fs);
Sheet = WorkBook.GetSheetAt(0);
ExcelPath = filePath;
}
}
public ExcelHelper(string path, string fileName)
{
//把xls文件中的数据写入wk中
using (FileStream fs = File.OpenRead(path + "/" + fileName + ".xls"))
{
WorkBook = new HSSFWorkbook(fs);
Sheet = WorkBook.GetSheetAt(0);
ExcelPath = path + "/" + fileName + ".xls";
}
}
public ExcelHelper(string path, string fileName, string sheetName)
{
using (FileStream fs = File.OpenRead(path + "/" + fileName + ".xls"))
{
WorkBook = new HSSFWorkbook(fs);
Sheet = WorkBook.GetSheet(sheetName);
ExcelPath = path + "/" + fileName + ".xls";
}
}
/// <summary>
/// 创建xls文件
/// </summary>
/// <param name="path"></param>
/// <param name="fileName"></param>
public void Create(string path, string fileName, string sheetName)
{
//创建工作薄
WorkBook = new HSSFWorkbook();
//创建一个表
Sheet = WorkBook.CreateSheet(sheetName);
using (FileStream fs = File.OpenWrite(path + "/" + fileName + ".xls")) //打开一个xls文件,如果没有则自行创建,如果存在myxls.xls文件则在创建是不要打开该文件!
{
WorkBook.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。
}
}
public void SetValue(int rowIndex, int columnIndex)
{
}
public DataTable ToDataTable(int index, bool header)
{
return ToDataTable(ExcelPath,index,header);
}
public DataTable ToDataTable(string excel, int index, bool header)
{
DataTable dt = new DataTable(Path.GetFileNameWithoutExtension(excel) + "_Sheet" + index);
IWorkbook workbook;
using (FileStream file = new FileStream(excel, FileMode.Open, FileAccess.Read))
{
workbook = new HSSFWorkbook(file);
}
ISheet sheet = workbook.GetSheetAt(index);
var rows = sheet.GetRowEnumerator();
rows.MoveNext();
IRow row = (IRow)rows.Current;
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
string columnName = header ? cell.StringCellValue : i.ToString();
dt.Columns.Add(columnName, typeof(string));
}
if (!header)
{
DataRow first = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
first[i] = cell.StringCellValue;
} dt.Rows.Add(first);
}
while (rows.MoveNext())
{
row = (IRow)rows.Current;
DataRow dataRow = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
if (cell != null)
{
dataRow[i] = cell.ToString();
#region
//try
//{
// dataRow[i] = cell.StringCellValue;
//}
//catch
//{
// try
// {
// dataRow[i] = cell.NumericCellValue;
// }
// catch
// {
// try
// {
// dataRow[i] = cell.BooleanCellValue;
// }
// catch
// {
// try
// {
// dataRow[i] = cell.DateCellValue;
// }
// catch
// {
// try
// {
// dataRow[i] = cell.ErrorCellValue;
// }
// catch
// {
// dataRow[i] = cell.RichStringCellValue;
// }
// }
// }
// }
//}
#endregion
}
}
dt.Rows.Add(dataRow);
}
return dt;
}
public bool ToExcel(string savePath,DataTable table, bool header)
{
//创建工作薄
HSSFWorkbook wk = new HSSFWorkbook();
//创建一个名称为mySheet的表
ISheet tb = wk.CreateSheet(Sheet.SheetName);
int rowIndex = 0;
if (header)
{
rowIndex = 1;
IRow row = tb.CreateRow(0);//创建一行
for (int i = 0; i < table.Columns.Count; i++)
{
ICell cell = row.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
cell.SetCellValue(table.Columns[i].ToString());//循环往第二行的单元格中添加数据
}
}
for (int i = 0; i < table.Rows.Count; i++)
{
IRow row = tb.CreateRow(rowIndex+i);//创建一行
for (int c = 0; c < table.Columns.Count; c++)
{
ICell cell = row.GetCell(c, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
cell.SetCellValue(table.Rows[i][c].ToString());//循环往第二行的单元格中添加数据
}
}
using (FileStream fs = File.OpenWrite(savePath)) //打开一个xls文件,如果没有则自行创建,如果存在myxls.xls文件则在创建是不要打开该文件!
{
wk.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。
}
return true;
}
public void SetValue(int rowIndex, int columnIndex, string value)
{
IRow row = Sheet.GetRow(rowIndex); //读取当前行数据
ICell cell = row.GetCell(columnIndex);
cell.SetCellValue(value);
}
#region 私有方法
/// <summary>
/// 获取一行,如果没有则创建
/// </summary>
/// <param name="rowIndex"></param>
/// <returns></returns>
private IRow GetRowByExtend(int rowIndex)
{
IRow row = Sheet.GetRow(rowIndex);
if (row == null)
row = Sheet.CreateRow(rowIndex);
return row;
}
private string GetCellValue(ICell cell)
{
if (cell == null)
return string.Empty;
switch (cell.CellType)
{
case CellType.BLANK:
return string.Empty;
case CellType.BOOLEAN:
return cell.BooleanCellValue.ToString();
case CellType.ERROR:
return cell.ErrorCellValue.ToString();
case CellType.NUMERIC:
case CellType.Unknown:
default:
return cell.ToString();//This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number
case CellType.STRING:
return cell.StringCellValue;
case CellType.FORMULA:
try
{
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
e.EvaluateInCell(cell);
return cell.ToString();
}
catch
{
return cell.NumericCellValue.ToString();
}
}
}
#endregion
}
}