using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace FerexTestSystem.Common
{
public static class ExcelHelper
{
/// <summary>
/// Excel导入成DataTable
/// </summary>
/// <param name="file">导入路径(包含文件名与扩展名)</param>
/// <returns></returns>
public static DataTable ExcelToTable(string file)
{
if (file == "") return null;
DataTable dt = new DataTable();
IWorkbook workbook;//工作簿接口
string fileExt = Path.GetExtension(file).ToLower();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
//XSSFWorkbook 适用XLSX格式,HSSFWorkbook 适用XLS格式
if (fileExt == ".xlsx")//新版本excel2007
{
workbook = new XSSFWorkbook(fs);
}
else if (fileExt == ".xls")//早期版本excel2003
{
workbook = new HSSFWorkbook(fs);
}
else
{
workbook = null;
}
if (workbook == null) { return null; }
ISheet sheet = workbook.GetSheetAt(0);//下标为零的工作簿
//创建表头 FirstRowNum:获取第一个有数据的行好(默认0)
IRow header = sheet.GetRow(sheet.FirstRowNum);//第一行是头部信息
List<int> columns = new List<int>();
for (int i = 0; i < header.LastCellNum; i++)//LastCellNum 获取列的条数
{
object obj = GetValueType(header.GetCell(i));
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));//如果excel没有列头就自定义
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));//获取excel列头
columns.Add(i);
}
//构建数据 sheet.FirstRowNum + 1 表示去掉列头信息
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)//LastRowNum最后一条数据的行号
{
DataRow dr = dt.NewRow();//编号 名称 价格 描述
bool hasValue = false;//判断是否有值
foreach (int j in columns)
{
//if (sheet.GetRow(i) == null) continue;//如果没数据
dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
if (dr[j] != null && dr[j].ToString() != string.Empty)//判断至少一列有值
{
hasValue = true;//不是空的
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return dt;
}
/// <summary>
/// Datable导出成Excel
/// </summary>
/// <param name="dt"></param>
/// <param name="file">导出路径(包括文件名与扩展名)</param>
public static void TableToExcel(DataTable dt, string file)
{
IWorkbook workbook;
string fileExt = Path.GetExtension(file).ToLower();
if (fileExt == ".xlsx")
{ workbook = new XSSFWorkbook(); }
else if (fileExt == ".xls")
{ workbook = new HSSFWorkbook(); }
else { workbook = null; }
if (workbook == null) { return; }
ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);
#region 添加一行合并头部并居中显示的列
//ICellStyle style = workbook.CreateCellStyle();//创建头部列样式
单独合并头部列
//IRow h_row = sheet.CreateRow(0);
//ICell h_cell = h_row.CreateCell(0);
//h_cell.SetCellValue("测试头部列");//设置单元格细信息
//h_row.Height = 30 * 20;
//sheet.SetColumnWidth(0, 30 * 256);//设置单元格的宽度
//sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 10));//合并列
//style.Alignment = HorizontalAlignment.Center;//居中显示
新建一个字体样式对象
//IFont font = workbook.CreateFont();//设置字体加粗样式
//font.Boldweight = short.MaxValue;//使用SetFont方法将字体样式添加到单元格样式中
//style.SetFont(font);//将新的样式赋给单元格
//h_cell.CellStyle = style;
#endregion
//表头
IRow row = sheet.CreateRow(0);//去头部修改
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);//去头部修改
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
//转为字节数组
MemoryStream stream = new MemoryStream();//读写内存的对象
workbook.Write(stream);
var buf = stream.ToArray();//字节数组
//保存为Excel文件
using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);//100.1MB 1MB
fs.Flush();//缓冲区在内存中有个临时区域 盆 两个水缸 //缓冲区装满才会自动提交
}
}
/// <summary>
/// 获取单元格类型
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueType(ICell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.Boolean: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.Numeric: //NUMERIC:
if (DateUtil.IsCellDateFormatted(cell))//判断是否日期 6位数字
return cell.DateCellValue.ToString("yyyy/MM/dd");
else
return cell.NumericCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.String: //STRING:
default:
return cell.StringCellValue;
}
}
}
}