大部分功能逻辑都在，少量自定义异常类和扩展方法 ，可用类似代码自己替换
//EpPlus读取生成Excel帮助类+读取csv帮助类，epplus只支持开放的Excel文件格式：xlsx，不支持 xls格式
/* ==============================================================================
* 功能描述：EppHelper
* 创 建 者：蒲奎民
* 创建日期：2016-07-21 14:30:35
* CLR Version :4.0.30319.42000
* ==============================================================================*/
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using YCF.HRMS.Utilities.ExceptionHeper;
using YCF.HRMS.Utilities.ExtensionHelper;
/*
* 引用文件：
* packagesEPPlus.4.1.0lib
et40EPPlus.dll
* packagesEPPlus.Extensions.1.0.0.0lib
et40EPPlus.Extensions.dll
*/
namespace YCF.HRMS.Utilities.CommomHelper
{
/// <summary>
/// EpPlus读取Excel帮助类+读取csv帮助类
/// </summary>
public class EppHelper
{
#region 由List创建简单Exel.列头取字段的Description或字段名
/// <summary>
/// 由List创建简单Exel.列头取字段的Description或字段名
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="filePath">The file path.</param>
/// <param name="dataList">The data list.</param>
public static void CreateExcelByList<T>(string filePath, List<T> dataList) where T : class
{
string dirPath = Path.GetDirectoryName(filePath);
string fileName = Path.GetFileName(filePath);
FileInfo newFile = new FileInfo(filePath);
if (newFile.Exists)
{
newFile.Delete(); // ensures we create a new workbook
newFile = new FileInfo(filePath);
}
PropertyInfo[] properties = null;
if (dataList.Count > 0)
{
Type type = dataList[0].GetType();
properties = type.GetProperties(BindingFlags.Instance | BindingFlags.Public);
var filedDescriptions = CommonFunctions.GetPropertyDescriptions<T>(true);//字段与excel列名对应关系
using (ExcelPackage package = new ExcelPackage(newFile))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("sheet1");
//设置表头单元格格式
using (var range = worksheet.Cells[1, 1, 1, properties.Length])
{
range.Style.Font.Bold = true;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
range.Style.Font.Color.SetColor(Color.White);
}
int row = 1, col;
object objColValue;
string colValue;
//表头
for (int j = 0; j < properties.Length; j++)
{
row = 1;
col = j + 1;
var description = filedDescriptions.Where(o => o.Key == properties[j].Name).Select(o => o.Value).FirstOrDefault();
worksheet.Cells[row, col].Value = (description == null || description.Description.IsNullOrEmpty()) ? properties[j].Name : description.Description;
}
worksheet.View.FreezePanes(row + 1, 1); //冻结表头
//各行数据
for (int i = 0; i < dataList.Count; i++)
{
row = i + 2;
for (int j = 0; j < properties.Length; j++)
{
col = j + 1;
objColValue = properties[j].GetValue(dataList[i], null);
colValue = objColValue == null ? "" : objColValue.ToString();
worksheet.Cells[row, col].Value = colValue;
}
}
package.Save();
}
}
}
#endregion
#region 读取Excel数据到DataSet
/// <summary>
/// 读取Excel数据到DataSet
/// </summary>
/// <param name="filePath">The file path.</param>
/// <returns></returns>
public static DataSet ReadExcelToDataSet(string filePath)
{
DataSet ds = new DataSet("ds");
DataRow dr;
object objCellValue;
string cellValue;
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite))
using (ExcelPackage package = new ExcelPackage())
{
package.Load(fs);
foreach (var sheet in package.Workbook.Worksheets)
{
if (sheet.Dimension == null) continue;
var columnCount = sheet.Dimension.End.Column;
var rowCount = sheet.Dimension.End.Row;
if (rowCount > 0)
{
DataTable dt = new DataTable(sheet.Name);
for (int j = 0; j < columnCount; j++)//设置DataTable列名
{
objCellValue = sheet.Cells[1, j + 1].Value;
cellValue = objCellValue == null ? "" : objCellValue.ToString();
dt.Columns.Add(cellValue, typeof(string));
}
for (int i = 2; i <= rowCount; i++)
{
&nbs