using EnumType;
|
using NPOI.SS.UserModel;
|
using OfficeOpenXml;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.IO;
|
using System.Linq;
|
using System.Reflection;
|
using System.Text;
|
using System.Threading.Tasks;
|
using System.Web;
|
|
namespace IWareDataAccess.Helper
|
{
|
public static class ExcelHelper
|
{
|
|
public static bool UploadFiles(HttpPostedFile file, string hostpath, out string msg)//
|
{
|
try
|
{
|
msg = "";
|
if (file == null)
|
{
|
msg="上传的文件为空，请重新上传";
|
return false;
|
}
|
string filename = Path.GetFileName(file.FileName);
|
string fileEx = Path.GetExtension(filename);//获取上传文件的扩展名
|
string NoFileName = Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名
|
string FileType = ".xls,.xlsx";//定义上传文件的类型字符串
|
if (!FileType.Contains(fileEx))
|
{
|
msg="无效的文件类型，只支持.xls和.xlsx文件";
|
return false;
|
}
|
|
//源数据
|
MemoryStream msSource = new MemoryStream();
|
var fstream = file.InputStream;
|
byte[] b = new byte[fstream.Length];
|
fstream.Read(b, 0, b.Length);
|
msSource.Write(b, 0, b.Length);
|
msSource.Seek(0, SeekOrigin.Begin);
|
DataTable sourceExcel = ReadStreamToDataTable(msSource, "", true);
|
|
//模板数据
|
string dataDir = hostpath;//获得当前服务器程序的运行目录
|
dataDir = Path.Combine(dataDir, "ExcelTemplate");
|
var path = dataDir + "//冲压计划.xlsx";
|
MemoryStream msModel = new MemoryStream();
|
FileStream stream = new FileStream(path, FileMode.Open);
|
stream.CopyTo(msModel);
|
msModel.Seek(0, SeekOrigin.Begin);
|
DataTable templateExcel = ReadStreamToDataTable(stream, "", true);
|
//验证是否同模板相同
|
string columnName = templateExcel.Columns[0].ColumnName;
|
if (columnName != sourceExcel.Columns[0].ColumnName)
|
{
|
msg="上传的模板文件不正确";
|
return false;
|
}
|
// 处理后台逻辑 执行 插入操作
|
|
|
return true;
|
}
|
catch (Exception ex)
|
{
|
msg="上传的模板文件不正确";
|
return false;
|
}
|
}
|
|
|
public static DataTable ReadStreamToDataTable(Stream fileStream, string sheetName = null, bool isFirstRowColumn = true)
|
{
|
//定义要返回的datatable对象
|
DataTable data = new DataTable();
|
//excel工作表
|
ISheet sheet = null;
|
//数据开始行(排除标题行)
|
int startRow = 0;
|
try
|
{
|
//根据文件流创建excel数据结构,NPOI的工厂类WorkbookFactory会自动识别excel版本，创建出不同的excel数据结构
|
IWorkbook workbook = WorkbookFactory.Create(fileStream);
|
//如果有指定工作表名称
|
if (!string.IsNullOrEmpty(sheetName))
|
{
|
sheet = workbook.GetSheet(sheetName);
|
//如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
|
if (sheet == null)
|
{
|
sheet = workbook.GetSheetAt(0);
|
}
|
}
|
else
|
{
|
//如果没有指定的sheetName，则尝试获取第一个sheet
|
sheet = workbook.GetSheetAt(0);
|
}
|
if (sheet != null)
|
{
|
IRow firstRow = sheet.GetRow(0);
|
//一行最后一个cell的编号 即总的列数
|
int cellCount = firstRow.LastCellNum;
|
//如果第一行是标题列名
|
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 || row.FirstCellNum < 0) continue; //没有数据的行默认是null
|
|
DataRow dataRow = data.NewRow();
|
for (int j = row.FirstCellNum; j < cellCount; ++j)
|
{
|
//同理，没有数据的单元格都默认是null
|
ICell cell = row.GetCell(j);
|
if (cell != null)
|
{
|
if (cell.CellType == CellType.Numeric)
|
{
|
//判断是否日期类型
|
if (DateUtil.IsCellDateFormatted(cell))
|
{
|
dataRow[j] = row.GetCell(j).DateCellValue;
|
}
|
else
|
{
|
dataRow[j] = row.GetCell(j).ToString().Trim();
|
}
|
}
|
else
|
{
|
dataRow[j] = row.GetCell(j).ToString().Trim();
|
}
|
}
|
}
|
data.Rows.Add(dataRow);
|
}
|
}
|
return data;
|
}
|
catch (Exception ex)
|
{
|
throw ex;
|
}
|
|
}
|
|
|
public static List<object> FromExcelByEPPlus<T>(string path, string sheetname,T t,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>>();
|
var package = new ExcelPackage(new FileInfo(path));
|
|
var worksheet = package.Workbook.Worksheets[sheetname];
|
|
if (worksheet == null || !sheetname.Equals(worksheet.Name))
|
{
|
msg = string.Format("未找到Excel工作页{0}", sheetname);
|
return null;
|
}
|
|
Type type = t.GetType();
|
var tProps = type.GetProperties(BindingFlags.Public | BindingFlags.Instance).ToList();
|
var propNames = new List<string>();
|
var tPropDic = new Dictionary<string, PropertyInfo>();
|
|
|
//遍历S的所有属性,将符合修改的存入字典中
|
tProps.ForEach(p => { propNames.Add(p.Name); tPropDic.Add(p.Name, p); });
|
|
var datas = new List<object>();
|
|
var 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 < propNames.Count; j++)
|
{
|
var workCell = tPropDic[propNames[j]];
|
|
object value = ((ExcelRangeBase)worksheet.Cells[dataStartLine + lineIndex, j + 1]).Value;
|
var pty = workCell.PropertyType.FullName;//获得属性的类型
|
if (value != null && !string.IsNullOrEmpty(value.ToString()))
|
{
|
nullFlag = false;
|
if (pty == typeof(int).FullName)
|
{
|
value = Convert.ToInt32(value.ToString().Trim());
|
}
|
else if (pty == typeof(double).FullName)
|
{
|
value = Convert.ToDouble(value.ToString().Trim());
|
}
|
else if (pty == typeof(DateTime).FullName)
|
{
|
value = Convert.ToDateTime(value.ToString().Trim());
|
}
|
else if (pty == typeof(string).FullName)
|
{
|
value = value.ToString().Trim();
|
if (value.ToString().EndsWith("\r\n"))
|
{
|
value = value.ToString().Substring(0, value.ToString().Length - 2);
|
}
|
}
|
tPropDic[propNames[j]].SetValue(data, value, null);
|
}
|
else
|
{
|
if (pty == typeof(int).FullName)
|
{
|
value = 0;
|
|
}
|
else if (pty == typeof(double).FullName)
|
{
|
value = 0.0;
|
}
|
else if (pty == typeof(DateTime).FullName)
|
{
|
value = DateTime.Now; ;
|
}
|
else if (pty == typeof(string).FullName)
|
{
|
value = "";
|
}
|
|
tPropDic[propNames[j]].SetValue(data, value, null);
|
}
|
}
|
if (!nullFlag)
|
{
|
datas.Add(data);
|
lineIndex++;
|
continue;
|
}
|
endOfLine = true;
|
}
|
|
|
return datas;
|
}
|
catch (Exception ex)
|
{
|
msg = ex.Message;
|
return null;
|
}
|
}
|
|
}
|
}
|
