using Genersoft.Platform.Controls.WinForms;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Collections;
using Genersoft.GS.HIS.ZW.SPI;
using NPOI.HSSF.Util;
using NPOI.SS.Util;
namespace Genersoft.GS.HIS.ZW.Controller
{
public class ExcelHelper : IDisposable
{
#region 变量属性
private IWorkbook workbook = null;
private FileStream fs = null;
private bool disposed;
private string fileName = string.Empty;
#endregion
#region 构造函数
public ExcelHelper()
{
disposed = false;
}
#endregion
#region 方法
#region 将DataTable数据导入到excel中
/// <summary>
/// 将DataTable数据导入到excel中
/// </summary>
/// <param name="data">要导入的数据</param>
/// <param name="isColumnWritten">DataTable的列名是否要导入</param>
/// <param name="sheetName">要导入的excel的sheet的名称</param>
/// <returns>导入数据行数(包含列名那一行)</returns>
public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten, out string fileName)
{
int i = 0;
int j = 0;
int count = 0;
ISheet sheet = null;
fileName = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Desktop) + "\\" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; ; //文件名
fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook();
else if (fileName.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook();
try
{
if (workbook != null)
{
sheet = workbook.CreateSheet(sheetName);
}
else
{
return -1;
}
if (isColumnWritten == true) //写入DataTable的列名
{
IRow row = sheet.CreateRow(0);
for (j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
}
count = 1;
}
else
{
count = 0;
}
for (i = 0; i < data.Rows.Count; ++i)
{
IRow row = sheet.CreateRow(count);
for (j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
}
++count;
}
workbook.Write(fs); //写入到excel
fs.Close();
return count;
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return -1;
}
}
#endregion
#region 将excel中的数据导入到DataTable中
/// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <returns>返回的DataTable</returns>
public DataSet ExcelToDataTable(string sheetName, bool isFirstRowColumn, string excelPath)
{
DataSet ds = new DataSet();
ISheet sheet = null;
DataTable data = new DataTable();
int startRow = 0;
try
{
fs = new FileStream(excelPath, FileMode.Open);
long left = fs.Length;
byte[] bytes = new byte[1024];
int maxLength = bytes.Length;
int start = 0;
int num = 0;
while (left > 0)
{
fs.Position = start;
num = 0;
if (left < maxLength)
num = fs.Read(bytes, 0, Convert.ToInt32(left));
else
num = fs.Read(bytes, 0, maxLength);
if (num == 0)
break;
start += num;
left -= num;
}
fs.Seek(0, SeekOrigin.Begin);
if (excelPath.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook(fs);
else if (excelPath.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook(fs);
if (sheetName != null)
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
}
}
}
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow = sheet.FirstRowNum;
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null) continue; //没有数据的行默认是null
//处理空行数据
bool isBlankRow = true;
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null) //对象实例不为null
{
if (!string.IsNullOrWhiteSpace(row.GetCell(j).ToString()))//判断单元格是否为空
{
isBlankRow = false;
}
}
}
if (isBlankRow)
{
continue;
}
//增加行数据
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
dataRow[j] = row.GetCell(j).ToString();
}
data.Rows.Add(dataRow);
}
}
if (data != null)
{
DataView dv = data.DefaultView;
dv.Sort = "合同编号 Asc";
DataTable dtSort = dv.ToTable();
ds.Tables.Add(dtSort);
return ds;
}
else
{
return null;
}
}
catch (Exception ex)
{
UMessageBox.Information(ex.Message);
return null;
}
}
#endregion
#region 将excel中的数据导入到DataTable中
/// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <returns>返回的DataTable</returns>
public DataSet BDExcelToDataTable(string sheetName, bool isFirstRowColumn, string excelPath)
{
DataSet ds = new DataSet();
ISheet sheet = null;
DataTable data = new DataTable();
int startRow = 0;
try
{
fs = new FileStream(excelPath, FileMode.Open);
long left = fs.Length;
byte[] bytes = new byte[1024];
int maxLength = bytes.Length;
int start = 0;
int num = 0;
while (left > 0)
{
fs.Position = start;
num = 0;
if (left < maxLength)
num = fs.Read(bytes, 0, Convert.ToInt32(left));
else
num = fs.Read(bytes, 0, maxLength);
if (num == 0)
break;
start += num;
left -= num;
}
fs.Seek(0, SeekOrigin.Begin);
if (excelPath.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook(fs);
else if (excelPath.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook(fs);
if (sheetName != null)
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
}
}
}
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow = sheet.FirstRowNum;
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null) continue; //没有数据的行默认是null
//处理空行数据
bool isBlankRow = true;
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null) //对象实例不为null
{
if (!string.IsNullOrWhiteSpace(row.GetCell(j).ToString()))//判断单元格是否为空
{
isBlankRow = false;
}
}
}
if (isBlankRow)
{
continue;
}
//增加行数据
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
dataRow[j] = row.GetCell(j).ToString();
}
data.Rows.Add(dataRow);
}
}
if (data != null)
{
DataView dv = data.DefaultView;
DataTable dtSort = dv.ToTable();
ds.Tables.Add(dtSort);
return ds;
}
else
{
return null;
}
}
catch (Exception ex)
{
UMessageBox.Information(ex.Message);
return null;
}
}
#endregion
#region 将DataTable数据导入到excel中
/// <summary>
/// 将DataTable数据导入到excel中
/// </summary>
/// <param name="data">要导入的数据</param>
/// <param name="isColumnWritten">DataTable的列名是否要导入</param>
/// <param name="sheetName">要导入的excel的sheet的名称</param>
/// <returns>导入数据行数(包含列名那一行)</returns>
public int DataTableToExcelForDirectory(DataTable data, string sheetName, bool isColumnWritten, string fileName, DJType type, string description = "")
{
int i = 0;
int j = 0;
int count = 0;
ISheet sheet = null;
fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook();
else if (fileName.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook();
try
{
if (workbook != null)
{
sheet = workbook.CreateSheet(sheetName);
}
else
{
return -1;
}
if (!string.IsNullOrEmpty(description))//添加描述信息
{
IRow row = sheet.CreateRow(count);
ICellStyle cellStyle = workbook.CreateCellStyle();
ICell cell = row.CreateCell(0);
cell.SetCellValue(description);
cellStyle.WrapText = true;//自动换行
cellStyle.BorderBottom = cellStyle.BorderLeft = cellStyle.BorderRight = cellStyle.BorderTop = BorderStyle.Thin;
cell.CellStyle = cellStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(count, count, 0, data.Columns.Count - 1));
row.Height = 30 * 20;
count++;
}
if (isColumnWritten == true) //写入DataTable的列名
{
IRow row = sheet.CreateRow(count);
IDataFormat format = workbook.CreateDataFormat();
for (j = 0; j < data.Columns.Count; ++j)
{
ICell cell = row.CreateCell(j);
ICellStyle cellStyle = workbook.CreateCellStyle();
if (data.Columns[j].DataType.ToString() == "System.Decimal")
{
cellStyle.DataFormat = format.GetFormat("0.00");
}
else
{
cellStyle.DataFormat = format.GetFormat("@");
}
HSSFFont ffont = (HSSFFont)workbook.CreateFont();
ffont.FontHeight = 16 * 16;
ffont.FontName = "宋体";
cellStyle.SetFont(ffont);
cellStyle.BorderBottom = cellStyle.BorderLeft = cellStyle.BorderRight = cellStyle.BorderTop = BorderStyle.Thin;
cell.CellStyle = cellStyle;
cell.SetCellValue(data.Columns[j].ColumnName);
row.Cells[j] = cell;
/*设置列宽*/
sheet.SetColumnWidth(j, (data.Columns[j].ColumnName.Length) * 3 * 256);
switch (type)
{
case DJType.JXCZWPZInfo:
if (data.Columns[j].ColumnName.Trim().ToUpper() == "业务分类")
{
//设置生成下拉框的行和列
var cellRegions = new CellRangeAddressList(count++, 65535, j, j);
//设置 下拉框内容
DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(
new string[] { "入库", "出库" });
//绑定下拉框和作用区域，并设置错误提示信息
HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
dataValidate.CreateErrorBox("输入不合法", "请输入下拉列表中的值。");
dataValidate.ShowPromptBox = true;
sheet.AddValidationData(dataValidate);
}
break;
case DJType.DBZWPZInfo:
if (data.Columns[j].ColumnName.Trim().ToUpper() == "业务分类")
{
//设置生成下拉框的行和列
var cellRegions = new CellRangeAddressList(count++, 65535, j, j);
//设置 下拉框内容
DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(
new string[] { "调拨" });
//绑定下拉框和作用区域，并设置错误提示信息
HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
dataValidate.CreateErrorBox("输入不合法", "请输入下拉列表中的值。");
dataValidate.ShowPromptBox = true;
sheet.AddValidationData(dataValidate);
}
break;
case DJType.SRZWPZInfo:
if (data.Columns[j].ColumnName.Trim().ToUpper() == "业务分类")
{
//设置生成下拉框的行和列
var cellRegions = new CellRangeAddressList(count++, 65535, j, j);
//设置 下拉框内容
DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(
new string[] { "门诊收入", "住院收入" });
//绑定下拉框和作用区域，并设置错误提示信息
HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
dataValidate.CreateErrorBox("输入不合法", "请输入下拉列表中的值。");
dataValidate.ShowPromptBox = true;
sheet.AddValidationData(dataValidate);
}
break;
case DJType.FYZWPZInfo:
if (data.Columns[j].ColumnName.Trim().ToUpper() == "业务分类")
{
//设置生成下拉框的行和列
var cellRegions = new CellRangeAddressList(count++, 65535, j, j);
//设置 下拉框内容
DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(
new string[] { "门诊收入", "住院收入" });
//绑定下拉框和作用区域，并设置错误提示信息
HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
dataValidate.CreateErrorBox("输入不合法", "请输入下拉列表中的值。");
dataValidate.ShowPromptBox = true;
sheet.AddValidationData(dataValidate);
}
break;
}
if (data.Columns[j].ColumnName.Trim().ToUpper() == "推送状态")
{
//设置生成下拉框的行和列
var cellRegions = new CellRangeAddressList(count++, 65535, j, j);
//设置 下拉框内容
DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(
new string[] { "新增", "编辑", "删除" });
//绑定下拉框和作用区域，并设置错误提示信息
HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint);
dataValidate.CreateErrorBox("输入不合法", "请输入下拉列表中的值。");
dataValidate.ShowPromptBox = true;
sheet.AddValidationData(dataValidate);
}
}
count++;
}
for (i = 0; i < data.Rows.Count; ++i)
{
IRow row = sheet.CreateRow(count);
for (j = 0; j < data.Columns.Count; ++j)
{
ICell cell = row.CreateCell(j);
cell.SetCellValue(data.Rows[i][j].ToString());
ICellStyle cellStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
cellStyle.BorderBottom = cellStyle.BorderLeft = cellStyle.BorderRight = cellStyle.BorderTop = BorderStyle.Thin;
if (data.Columns[j].DataType.ToString() == "System.Decimal")
{
cellStyle.DataFormat = format.GetFormat("0.00");
}
else
{
cellStyle.DataFormat = format.GetFormat("@");
}
HSSFFont ffont = (HSSFFont)workbook.CreateFont();
ffont.FontHeight = 14 * 14;
ffont.FontName = "宋体";
cellStyle.SetFont(ffont);
cell.CellStyle = cellStyle;
cell.SetCellValue(data.Columns[j].ColumnName);
row.Cells[j] = cell;
}
++count;
}
workbook.Write(fs); //写入到excel
fs.Close();
return count;
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return -1;
}
}
#endregion
#region 资源释放方法
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
protected virtual void Dispose(bool disposing)
{
if (!this.disposed)
{
if (disposing)
{
if (fs != null)
fs.Close();
}
fs = null;
disposed = true;
}
}
#endregion
#region 检查DataTable列是否存在
/// <summary>
/// 检查DataTable列是否存在
/// </summary>
/// <param name="dataTable"></param>
/// <param name="arrayList"></param>
public static bool CheckColumn(DataTable dataTable, ArrayList arrayList, out string message)
{
message = "";
foreach (var item in arrayList)
{
if (!dataTable.Columns.Contains(item.ToString()))
{
message = "列 【" + item.ToString() + "】不存在，请检查！";
return false;
}
}
return true;
}
#endregion
#endregion
}
}
版权属于：
Laughing
作品采用：
《
署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)
》许可协议授权
好
测试下，谢谢