using iWareCommon.Common.EnumType;
|
using iWareCommon.Utils;
|
using iWareExcel.EXCEL.Entity;
|
using iWareExcel.Properties;
|
using OfficeOpenXml;
|
using OfficeOpenXml.Style;
|
using System;
|
using System.Collections.Generic;
|
using System.IO;
|
using System.Linq;
|
using System.Reflection;
|
using System.Text;
|
using System.Threading.Tasks;
|
|
namespace iWareExcel.Utils
|
{
|
public class ExcelHelper
|
{
|
|
|
/// <summary>
|
/// 许艺潇
|
/// 将数据导出到excel(通过EPPlus插件)
|
/// </summary>
|
/// <param name="workBook">导出的样式</param>
|
/// <param name="datas">以工作页名为键，工作页数据为值的字典</param>
|
/// <param name="msg">异常错误信息</param>
|
/// <returns>导出excel的地址</returns>
|
public static string ToExcelByEPPlus(WorkBookEntity workBook, Dictionary<string, List<object>> datas, out string msg)
|
{
|
|
if (workBook.NeedTemplate == (int)EYesOrNo.是)
|
{
|
if (!File.Exists(workBook.TemplatePath))
|
{
|
msg = string.Format("没有找到模板文件：{0}", workBook.TemplatePath);
|
return "";
|
}
|
}
|
var folder = string.IsNullOrEmpty(workBook.ServerPath) ? @"c:\excel\" : workBook.ServerPath;
|
if (!folder.EndsWith(@"\"))
|
{
|
folder += @"\";
|
}
|
|
var now = DateTime.Now;
|
|
folder += string.Format(@"{0}\{1}\{2}\{3}\", now.Year, now.Month, now.Day, (long)(now - Convert.ToDateTime("1970-01-01 08:00:00")).TotalMilliseconds);
|
|
if (!Directory.Exists(folder))
|
{
|
Directory.CreateDirectory(folder);
|
}
|
var fileName = string.IsNullOrEmpty(workBook.ServerFileName) ? "default.xlsx" : workBook.ServerFileName;
|
|
|
//拼装服务器上的路径
|
var path = folder + fileName;
|
try
|
{
|
if (workBook.NeedTemplate == (int)EYesOrNo.是)
|
{
|
File.Copy(workBook.TemplatePath, path, true);
|
}
|
|
if ((File.Exists(path) && workBook.NeedTemplate == (int)EYesOrNo.是) || workBook.NeedTemplate == (int)EYesOrNo.否)
|
{
|
using (var package = new ExcelPackage(new FileInfo(path)))
|
{
|
LogTextHelper.WriteLog(Resources.LogDir, "ExcelHelper", "开始装数据", path);
|
for (int i = 0; i < workBook.WorkSheets.Count; i++)
|
{
|
var worksheet = workBook.NeedTemplate == (int)EYesOrNo.否 ? (ExcelWorksheet)package.Workbook.Worksheets.Add(workBook.WorkSheets[i].Name) : (ExcelWorksheet)package.Workbook.Worksheets[workBook.WorkSheets[i].Name];
|
|
var workSheet = workBook.WorkSheets[i];
|
|
worksheet.Name = workBook.WorkSheets[i].Name;
|
|
var headStartLine = workBook.NeedTemplate == (int)EYesOrNo.是 ? workSheet.HeadStartLine : 1;
|
|
|
for (int j = 0; j < workSheet.WorkCells.Count; j++)
|
{
|
var workCell = workSheet.WorkCells[j];
|
worksheet.Cells[headStartLine, j + 1].Value = workCell.DisplayName;
|
worksheet.Cells[headStartLine, j + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
|
worksheet.Cells[headStartLine, j + 1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;//边框常规粗细
|
worksheet.Cells[headStartLine, j + 1].Style.Border.Left.Style = ExcelBorderStyle.Thin;//边框常规粗细
|
worksheet.Cells[headStartLine, j + 1].Style.Border.Right.Style = ExcelBorderStyle.Thin;//边框常规粗细
|
worksheet.Cells[headStartLine, j + 1].Style.Border.Top.Style = ExcelBorderStyle.Thin;//边框常规粗细
|
//worksheet.Cells[headStartLine, j + 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));//边框常规粗细
|
}
|
|
if (datas.ContainsKey(workSheet.Name))
|
{
|
Type type = Type.GetType(workSheet.ClassName);
|
|
var tProps = type.GetProperties(BindingFlags.Public | BindingFlags.Instance).ToList();
|
|
var propNames = new List<string>();
|
|
workSheet.WorkCells.ForEach(x => propNames.Add(x.PropName));
|
|
var tPropDic = new Dictionary<string, PropertyInfo>();
|
|
|
//遍历S的所有属性,将符合修改的存入字典中
|
tProps.ForEach(p => { if (propNames.Contains(p.Name)) { tPropDic.Add(p.Name, p); } });
|
|
|
var ds = datas[workSheet.Name];
|
|
var dataStartLine = workBook.NeedTemplate == (int)EYesOrNo.是 ? workSheet.DataStartLine : 2;
|
|
for (int j = 0; j < ds.Count; j++)
|
{
|
var data = ds[j];
|
|
for (int k = 0; k < workSheet.WorkCells.Count; k++)
|
{
|
var workCell = workSheet.WorkCells[k];
|
|
var value = tPropDic[workCell.PropName].GetValue(data, null);
|
|
worksheet.Cells[j + dataStartLine, k + 1].Value = workCell.IsEnum == (int)EYesOrNo.是 ? Enum.Parse(Type.GetType(workCell.EnumClass), value.ToString().Trim()).ToString() : (value == null ? "" : value.ToString().Trim());
|
worksheet.Cells[j + dataStartLine, k + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
|
worksheet.Cells[j + dataStartLine, k + 1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;//边框常规粗细
|
worksheet.Cells[j + dataStartLine, k + 1].Style.Border.Left.Style = ExcelBorderStyle.Thin;//边框常规粗细
|
worksheet.Cells[j + dataStartLine, k + 1].Style.Border.Right.Style = ExcelBorderStyle.Thin;//边框常规粗细
|
worksheet.Cells[j + dataStartLine, k + 1].Style.Border.Top.Style = ExcelBorderStyle.Thin;//边框常规粗细
|
}
|
}
|
}
|
}
|
package.Save();
|
|
}
|
}
|
msg = "";
|
return path;
|
}
|
catch (Exception ex)
|
{
|
msg = ex.Message;
|
LogTextHelper.WriteLog(Resources.LogDir, "ExcelHelper", "ToExcelByEPPlus", msg);
|
return path;
|
}
|
}
|
|
|
/// <summary>
|
/// 许艺潇
|
/// 将excel的表转为数据集列表(通过EPPlus插件)
|
/// </summary>
|
/// <param name="path">导入的excel文件路径</param>
|
/// <param name="workBook">excel的表</param>
|
/// <param name="msg">异常错误信息</param>
|
/// <returns>转换后的数据集列表</returns>
|
public static List<List<object>> FromExcelByEPPlus(string path, WorkBookEntity workBook, out string msg)
|
{
|
msg = "";
|
|
//文件不存在
|
if (!File.Exists(path))
|
{
|
msg = "需要的excel文件不存在";
|
return null;
|
}
|
|
//文件后缀不是xls或xlsx
|
var fileSuffix = Path.GetExtension(path).ToUpper();
|
|
if (!".XLS".Equals(fileSuffix) && !".XLSX".Equals(fileSuffix))
|
{
|
msg = "该文件不是excel文件";
|
return null;
|
}
|
|
try
|
{
|
var result = new List<List<object>>();
|
ExcelPackage package = new ExcelPackage(new FileInfo(path));
|
|
for (int i = 0; i < workBook.WorkSheets.Count; i++)
|
{
|
var workSheet = workBook.WorkSheets[i];
|
var worksheet = package.Workbook.Worksheets[workSheet.Name];
|
//(Worksheet)workbook.Worksheets[i + 1];
|
|
if (!workSheet.Name.Equals(worksheet.Name))
|
{
|
msg = "导入的文件不正确";
|
return null;
|
}
|
|
Type type = Type.GetType(workSheet.ClassName);
|
var tProps = type.GetProperties(BindingFlags.Public | BindingFlags.Instance).ToList();
|
var propNames = new List<string>();
|
workSheet.WorkCells.ForEach(x => propNames.Add(x.PropName));
|
var tPropDic = new Dictionary<string, PropertyInfo>();
|
|
|
//遍历S的所有属性,将符合修改的存入字典中
|
tProps.ForEach(p => { if (propNames.Contains(p.Name)) { tPropDic.Add(p.Name, p); } });
|
|
var datas = new List<object>();
|
|
var dataStartLine = workBook.NeedTemplate == (int)EYesOrNo.是 ? workSheet.DataStartLine : 2;
|
|
//是否到最后一行
|
bool endOfLine = false;
|
|
//当前行数
|
int lineIndex = 0;
|
|
while (!endOfLine)
|
{
|
//判断是否为空数据
|
bool nullFlag = true;
|
|
var data = type.Assembly.CreateInstance(type.ToString());
|
|
for (int j = 0; j < workSheet.WorkCells.Count; j++)
|
{
|
var workCell = workSheet.WorkCells[j];
|
|
object value = ((ExcelRangeBase)worksheet.Cells[dataStartLine + lineIndex, j + 1]).Value;
|
|
if (value != null && !string.IsNullOrEmpty(value.ToString()))
|
{
|
nullFlag = false;
|
value = workCell.IsEnum == (int)EYesOrNo.是 ? ((int)Enum.Parse(Type.GetType(workCell.EnumClass), value.ToString())).ToString() : value.ToString();
|
switch (workCell.DataType)
|
{
|
case (int)EDataType.字符串类型:
|
value = value.ToString().Trim();
|
if (value.ToString().EndsWith("\r\n"))
|
{
|
value = value.ToString().Substring(0, value.ToString().Length - 2);
|
}
|
break;
|
case (int)EDataType.整数类型:
|
value = Convert.ToInt32(value.ToString().Trim());
|
break;
|
case (int)EDataType.小数类型:
|
value = Convert.ToDecimal(value.ToString().Trim());
|
break;
|
case (int)EDataType.布尔类型:
|
value = Convert.ToBoolean(value.ToString().Trim());
|
break;
|
case (int)EDataType.日期类型:
|
|
value = Convert.ToDateTime(value.ToString().Trim());
|
break;
|
default:
|
break;
|
}
|
tPropDic[workCell.PropName].SetValue(data, value, null);
|
}
|
else
|
{
|
switch (workCell.DataType)
|
{
|
case (int)EDataType.字符串类型:
|
value = "";
|
break;
|
case (int)EDataType.整数类型:
|
value = 0;
|
break;
|
case (int)EDataType.小数类型:
|
value = 0.0M;
|
break;
|
case (int)EDataType.布尔类型:
|
value = false;
|
break;
|
case (int)EDataType.日期类型:
|
|
value = DateTime.Now;
|
break;
|
default:
|
break;
|
}
|
tPropDic[workCell.PropName].SetValue(data, value, null);
|
}
|
}
|
|
|
if (!nullFlag)
|
{
|
datas.Add(data);
|
lineIndex++;
|
continue;
|
}
|
endOfLine = true;
|
}
|
|
result.Add(datas);
|
}
|
return result;
|
}
|
catch (Exception ex)
|
{
|
msg = ex.Message;
|
LogTextHelper.WriteLog(Resources.LogDir, "ExcelHelper", "FromExcelByEPPlus", ex.Message);
|
return null;
|
}
|
}
|
|
}
|
}
|
