using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
/*****************************
* 概要:Excel
* 设计者:DuanXuWen
* 设计时间:20180309
* 版本:0.1
* 修改者:
* 修改时间:
* ***************************/
namespace Common
{
public class ExcelHelper
{
/// <summary>
/// 无参构造
/// </summary>
public ExcelHelper(){ }
/// <summary>
/// 打开Excel(【弹窗】选择文件)
/// </summary>
/// <returns>DataSet</returns>
public DataSet GetExcelData()
{
try
{
OpenFileDialog file = new OpenFileDialog();
file.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
file.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
file.Multiselect = false;
if (file.ShowDialog() == DialogResult.Cancel)
{
return null;
}
return GetExcelData(file.FileName, null);
}
catch (Exception ex)
{
string msg = "打开Excel文件相关错误!";
string logAddress = System.Windows.Forms.Application.StartupPath + "\\Log\\ErrorMsg\\" + DateTime.Now.ToString("yyyyMMdd") + ".log";
LogHelper.WriteLog(ex, logAddress, false, msg);
return null;
}
}
/// <summary>
/// 导出Excel(【弹窗】选择保存路径)
/// </summary>
/// <param name="table">DataTable数据</param>
/// <param name="title">标题</param>
/// <returns></returns>
public bool ExportExcel(DataTable table, string fillName, string title)
{
try
{
System.Windows.Forms.FolderBrowserDialog dialog = new System.Windows.Forms.FolderBrowserDialog();
dialog.Description = "请选择保存路径";
if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
string path = dialog.SelectedPath + "\\" + fillName;
return ExportExcels(table, path, title);
}
return false;
}
catch (Exception ex)
{
string msg = "导出Excel文件选择路径相关错误!";
string logAddress = System.Windows.Forms.Application.StartupPath + "\\Log\\ErrorMsg\\" + DateTime.Now.ToString("yyyyMMdd") + ".log";
LogHelper.WriteLog(ex, logAddress, false, msg);
return false;
}
}
/// <summary>
/// 获取本机安装的office版本
/// </summary>
/// <returns></returns>
public OfficeVersion GetOfficeVersion()
{
OfficeVersion version = OfficeVersion.Office2007;
Microsoft.Win32.RegistryKey regKey = null;
try
{
regKey = Microsoft.Win32.Registry.LocalMachine.OpenSubKey(@"SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\Excel.exe");
if (regKey != null)
{
string path1 = regKey.GetValue("Path").ToString();
string path = path1.Substring(0, path1.Length - 1);
string versionName = path.Substring(path.LastIndexOf("\\") + 1).ToUpper();
switch (versionName)
{
case "OFFICE11":
version = OfficeVersion.Office2003;
break;
case "OFFICE12":
version = OfficeVersion.Office2007;
break;
case "OFFICE16":
version = OfficeVersion.Office2016;
break;
default:
break;
}
regKey.Close();
}
return version;
}
catch (System.Security.SecurityException ex)
{
throw new System.Security.SecurityException("您没有读取注册表的权限", ex);
}
catch (Exception ex)
{
string msg = "获取本机安装的office版本相关错误!";
string logAddress = System.Windows.Forms.Application.StartupPath + "\\Log\\ErrorMsg\\" + DateTime.Now.ToString("yyyyMMdd") + ".log";
LogHelper.WriteLog(ex, logAddress, false, msg);
throw ex;
}
}
/// <summary>
/// 获取文件内容
/// </summary>
/// <param name="path">文件地址</param>
/// <param name="Sheet">页:Sheet1,Sheet2等 注:可为null,默认Sheet1</param>
/// <returns>DataSet</returns>
public DataSet GetExcelData(string path, string sheet)
{
try
{
OfficeVersion version = GetOfficeVersion();
string connString = GetConnString(version, path);
//默认页Sheet1
string sheetValue = "Sheet1";
if (sheet != null)
{
sheetValue = sheet.ToString();
}
//读取文件
string sql_select = string.Format("SELECT * FROM [{0}$]", sheetValue);
return GetExcelDatas(connString, sql_select);
}
catch (Exception ex)
{
string msg = "获取Excel文件内容相关错误!详细:文件地址:" + path + "Sheet页:" + sheet;
string logAddress = System.Windows.Forms.Application.StartupPath + "\\Log\\ErrorMsg\\" + DateTime.Now.ToString("yyyyMMdd") + ".log";
LogHelper.WriteLog(ex, logAddress, false, msg);
return null;
}
}
/// <summary>
/// 获取版本连接
/// </summary>
/// <param name="version">Excel版本</param>
/// <param name="path">Excel文件地址</param>
/// <returns>string</returns>
public string GetConnString(OfficeVersion version, string path)
{
//获取文件后缀
string fileSuffix = System.IO.Path.GetExtension(path);
//判断文件是否为Excel【后缀是否包含(.xls,.xlsx)】
if (!string.IsNullOrEmpty(fileSuffix) && fileSuffix.Contains(".xls"))
{
string connString = string.Empty;
//连接串赋值
if (fileSuffix == ".xls" && version == OfficeVersion.Office2003)
{
connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
}
if (fileSuffix == ".xlsx" && version == OfficeVersion.Office2016)
{
connString = "Provider=Microsoft.Ace.OleDb.16.0;" + "data source=" + path + ";Extended Properties='Excel 16.0; HDR=YES; IMEX=1'";
}
if (fileSuffix == ".xlsx" && version == OfficeVersion.Office2007)
{
connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + path + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
}
return connString;
}
else
{
return "" + System.IO.Path.GetFileName(path) + "此文件不符合要求(.xls或.xlsx)";
}
}
/// <summary>
/// 获取文件内容
/// </summary>
/// <param name="connString">版本链接</param>
/// <param name="sqlSelect">查询语句</param>
/// <returns>DataSet</returns>
public DataSet GetExcelDatas(string connString, string sqlSelect)
{
try
{
using (DataSet set = new DataSet())
{
using (OleDbConnection conn = new OleDbConnection(connString))
using (OleDbDataAdapter cmd = new OleDbDataAdapter(sqlSelect, conn))
{
conn.Open();
cmd.Fill(set);
conn.Close();
}
return set;
}
}
catch (Exception ex)
{
string msg = "获取Excel文件内容相关错误!详细:文件版本链接:" + connString + "查询语句:" + sqlSelect;
string logAddress = System.Windows.Forms.Application.StartupPath + "\\Log\\ErrorMsg\\" + DateTime.Now.ToString("yyyyMMdd") + ".log";
LogHelper.WriteLog(ex, logAddress, false, msg);
return null;
}
}
/// <summary>
/// 获取Excel文件Sheet页集[TABLE_NAME]
/// </summary>
/// <param name="connString">文件版本链接</param>
/// <returns>DataTable</returns>
public DataTable GetSheetTable(string connString)
{
try
{
DataTable table = new DataTable();
using (OleDbConnection cn = new OleDbConnection(connString))
{
cn.Open();
table = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
cn.Close();
}
return table;
}
catch (Exception ex)
{
string msg = "获取Excel文件Sheet页集相关错误!详细:文件版本链接:" + connString;
string logAddress = System.Windows.Forms.Application.StartupPath + "\\Log\\ErrorMsg\\" + DateTime.Now.ToString("yyyyMMdd") + ".log";
LogHelper.WriteLog(ex, logAddress, false, msg);
return null;
}
}
/// <summary>
/// 获取Excel文件Sheet页集合
/// </summary>
/// <param name="connString">文件版本链接</param>
/// <returns>List<string>集合</returns>
public static List<string> GetExcelSheetList(string connString)
{
try
{
DataTable table = new DataTable();
List<string> list = new List<string>();
using (OleDbConnection cn = new OleDbConnection(connString))
{
cn.Open();
table = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
cn.Close();
}
if (table != null && table.Rows.Count > 0)
{
foreach (DataRow row in table.Rows)
{
list.Add(row["TABLE_NAME"].ToString());
}
}
return list;
}
catch (Exception ex)
{
string msg = "获取Excel文件Sheet页集相关错误!详细:文件版本链接:" + connString;
string logAddress = System.Windows.Forms.Application.StartupPath + "\\Log\\ErrorMsg\\" + DateTime.Now.ToString("yyyyMMdd") + ".log";
LogHelper.WriteLog(ex, logAddress, false, msg);
return null;
}
}
/// <summary>
/// 导出Excel文件
/// </summary>
/// <param name="table">DataTable数据</param>
/// <param name="excelFilePathName">文件路径+名称+后缀</param>
/// <param name="title">文件标题</param>
/// <returns></returns>
public bool ExportExcels(DataTable table, string excelFilePathName, string title)
{
try
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
ICellStyle HeadercellStyle = workbook.CreateCellStyle();
HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//背景色
HeadercellStyle.FillPattern = FillPattern.SolidForeground;
HeadercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightGreen.Index;
//字体
NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
HeadercellStyle.SetFont(headerfont);
//列名
int icolIndex = 0;
int firstIndex = 0;
if (title != "")
{
//插入标题
firstIndex = 1;
IRow firstRow = sheet.CreateRow(0);
for (int i = 0; i < table.Columns.Count; i++)
{
ICell firstCell = firstRow.CreateCell(i);
if (i == 0)
{
firstCell.SetCellValue(title);
ICellStyle FirstCellStyle = workbook.CreateCellStyle();
FirstCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
FirstCellStyle.SetFont(headerfont);
firstCell.CellStyle = FirstCellStyle;
}
}
//合并标题行
CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, table.Columns.Count - 1);
sheet.AddMergedRegion(cellRangeAddress);
}
//列表头
IRow headerRow = sheet.CreateRow(firstIndex);
foreach (DataColumn item in table.Columns)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(item.ColumnName);
cell.CellStyle = HeadercellStyle;
icolIndex++;
}
ICellStyle cellStyle = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont);
//写入内容
int iRowIndex = 1 + firstIndex;
int iCellIndex = 0;
foreach (DataRow Rowitem in table.Rows)
{
IRow DataRow = sheet.CreateRow(iRowIndex);
foreach (DataColumn Colitem in table.Columns)
{
ICell cell = DataRow.CreateCell(iCellIndex);
cell.SetCellValue(Rowitem[Colitem].ToString());
cell.CellStyle = cellStyle;
iCellIndex++;
}
iCellIndex = 0;
iRowIndex++;
}
//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}
//写Excel
using (FileStream file = new FileStream(excelFilePathName, FileMode.OpenOrCreate))
{
workbook.Write(file);
file.Flush();
file.Close();
file.Dispose();
}
}
catch (Exception ex)
{
string msg = "导出Excel文件相关错误!详细:DataTable数据:" + table + "文件路径:" + excelFilePathName + "标题:" + title;
string logAddress = System.Windows.Forms.Application.StartupPath + "\\Log\\ErrorMsg\\" + DateTime.Now.ToString("yyyyMMdd") + ".log";
LogHelper.WriteLog(ex, logAddress, false, msg);
return false;
}
return true;
}
}
/// <summary>
/// office版本
/// </summary>
public enum OfficeVersion
{
/// <summary>
/// office03
/// </summary>
Office2003 = 0,
/// <summary>
/// Office07
/// </summary>
Office2007 = 1,
/// <summary>
/// Office16
/// </summary>
Office2016 = 2
}
}