341 lines
13 KiB
C#
341 lines
13 KiB
C#
|
using System;
|
|||
|
|
using System.Collections.Generic;
|
|||
|
|
using System.Collections;
|
|||
|
|
using System.IO;
|
|||
|
|
using NPOI.SS.UserModel;
|
|||
|
|
using NPOI.HSSF.UserModel;
|
|||
|
|
using System.Data;
|
|||
|
|
using System.Windows.Forms;
|
|||
|
|
using WMS.Attirubte;
|
|||
|
|
using System.ComponentModel;
|
|||
|
|
|
|||
|
|
using NPOI.XSSF.UserModel;
|
|||
|
|
using NPOI.HSSF.Record;
|
|||
|
|
|
|||
|
|
namespace WMS.Common
|
|||
|
|
{
|
|||
|
|
public class NPOIHelper<T> where T : class, new()
|
|||
|
|
{
|
|||
|
|
/// <summary>
|
|||
|
|
/// 根据Excel列类型获取列的值
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="cell">Excel列</param>
|
|||
|
|
/// <returns></returns>
|
|||
|
|
private static string GetCellValue(ICell cell)
|
|||
|
|
{
|
|||
|
|
if (cell == null)
|
|||
|
|
return string.Empty;
|
|||
|
|
switch (cell.CellType)
|
|||
|
|
{
|
|||
|
|
case CellType.Blank:
|
|||
|
|
return string.Empty;
|
|||
|
|
case CellType.Boolean:
|
|||
|
|
return cell.BooleanCellValue.ToString();
|
|||
|
|
case CellType.Error:
|
|||
|
|
return cell.ErrorCellValue.ToString();
|
|||
|
|
case CellType.Numeric:
|
|||
|
|
case CellType.Unknown:
|
|||
|
|
default:
|
|||
|
|
return cell.ToString();//This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number
|
|||
|
|
case CellType.String:
|
|||
|
|
return cell.StringCellValue;
|
|||
|
|
case CellType.Formula:
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
|
|||
|
|
e.EvaluateInCell(cell);
|
|||
|
|
return cell.ToString();
|
|||
|
|
}
|
|||
|
|
catch
|
|||
|
|
{
|
|||
|
|
return cell.NumericCellValue.ToString();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
///// <summary>
|
|||
|
|
///// 自动设置Excel列宽
|
|||
|
|
///// </summary>
|
|||
|
|
///// <param name="sheet">Excel表</param>
|
|||
|
|
//private static void AutoSizeColumns(ISheet sheet)
|
|||
|
|
//{
|
|||
|
|
// if (sheet.PhysicalNumberOfRows > 0)
|
|||
|
|
// {
|
|||
|
|
// IRow headerRow = sheet.GetRow(0);
|
|||
|
|
|
|||
|
|
// for (int i = 0, l = headerRow.LastCellNum; i < l; i++)
|
|||
|
|
// {
|
|||
|
|
// sheet.AutoSizeColumn(i);
|
|||
|
|
// }
|
|||
|
|
// }
|
|||
|
|
//}
|
|||
|
|
|
|||
|
|
public static List<T> Import(OpenFileDialog fileDialog, Hashtable ht)
|
|||
|
|
{
|
|||
|
|
DataTable table = new DataTable();
|
|||
|
|
Stream excelFileStream;
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
excelFileStream = fileDialog.OpenFile();
|
|||
|
|
}
|
|||
|
|
catch (IOException e)
|
|||
|
|
{
|
|||
|
|
if (e.ToString().Contains("正由另一进程使用"))
|
|||
|
|
{
|
|||
|
|
SystemCommon.ShowErrorMessageBox("所选Excel文件处于打开Status，请关闭后重试");
|
|||
|
|
}
|
|||
|
|
else {
|
|||
|
|
SystemCommon.ShowErrorMessageBox("文件读取失败");
|
|||
|
|
}
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
if (excelFileStream == null)
|
|||
|
|
{
|
|||
|
|
excelFileStream.Close();
|
|||
|
|
SystemCommon.ShowErrorMessageBox("文件读取失败");
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
//读取excel
|
|||
|
|
using (excelFileStream)
|
|||
|
|
{
|
|||
|
|
IWorkbook workbook = new HSSFWorkbook();
|
|||
|
|
|
|||
|
|
if (fileDialog.SafeFileName.Substring(fileDialog.SafeFileName.Length - 1, 1) == "s")
|
|||
|
|
{
|
|||
|
|
try
|
|||
|
|
{
|
|||
|
|
workbook = new HSSFWorkbook(excelFileStream);//xls
|
|||
|
|
}
|
|||
|
|
catch (LeftoverDataException)
|
|||
|
|
{
|
|||
|
|
SystemCommon.ShowErrorMessageBox("导入失败，请将所选Excel文件另存为一个新的Excel文件后再次导入");
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
catch (IOException)
|
|||
|
|
{
|
|||
|
|
SystemCommon.ShowErrorMessageBox("导入失败，请将所选Excel文件另存为一个新的Excel文件后再次导入");
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
catch (Exception)
|
|||
|
|
{
|
|||
|
|
SystemCommon.ShowErrorMessageBox("导入失败，读取Excel文件时遇到未知错误");
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
else
|
|||
|
|
{
|
|||
|
|
workbook = new XSSFWorkbook(excelFileStream);//xlsx
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
ISheet sheet = workbook.GetSheetAt(0);//取第一个表
|
|||
|
|
|
|||
|
|
IRow headerRow = sheet.GetRow(0);//第一行为标题行
|
|||
|
|
if (headerRow == null) {
|
|||
|
|
SystemCommon.ShowErrorMessageBox("导入失败，所选Excel没有数据");
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
|
|||
|
|
int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1
|
|||
|
|
|
|||
|
|
//handling header.
|
|||
|
|
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
|
|||
|
|
table.Columns.Add(column);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
|
|||
|
|
{
|
|||
|
|
IRow row = sheet.GetRow(i);
|
|||
|
|
DataRow dataRow = table.NewRow();
|
|||
|
|
|
|||
|
|
if (row != null)
|
|||
|
|
{
|
|||
|
|
for (int j = row.FirstCellNum; j < cellCount; j++)
|
|||
|
|
{
|
|||
|
|
if (row.GetCell(j) != null){
|
|||
|
|
dataRow[j] = GetCellValue(row.GetCell(j));
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
table.Rows.Add(dataRow);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
Type classType = typeof(T);
|
|||
|
|
if (classType == null)
|
|||
|
|
{
|
|||
|
|
SystemCommon.ShowErrorMessageBox("导入失败，获取Model类型失败，请联系我们");
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
foreach (var temp in classType.GetProperties())
|
|||
|
|
{
|
|||
|
|
object[] TypeobjType = temp.GetCustomAttributes(typeof(TableClmAttribute), false);
|
|||
|
|
if (TypeobjType.Length > 0)
|
|||
|
|
{
|
|||
|
|
if ((TypeobjType[0] as TableClmAttribute).NoSelect == false) {
|
|||
|
|
continue;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
string name = temp.Name;
|
|||
|
|
if (ht.ContainsKey(name))
|
|||
|
|
{
|
|||
|
|
continue;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
string disPlayName = string.Empty;
|
|||
|
|
object[] disPlay = temp.GetCustomAttributes(typeof(DisplayNameAttribute), false);
|
|||
|
|
if (disPlay.Length > 0)
|
|||
|
|
{
|
|||
|
|
disPlayName = (disPlay[0] as DisplayNameAttribute).DisplayName;
|
|||
|
|
if (string.IsNullOrEmpty(disPlayName)) {
|
|||
|
|
continue;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
ht.Add(name, disPlayName);
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//如果是商品，重量UNIT的g转为克
|
|||
|
|
if (classType.FullName == "WMS.Model.Base.GoodsInfoModel")
|
|||
|
|
{
|
|||
|
|
if (table.Columns.Contains("重量UNIT"))
|
|||
|
|
{
|
|||
|
|
foreach (DataRow tableRow in table.Rows)
|
|||
|
|
{
|
|||
|
|
if (tableRow["重量UNIT"].ToString() == "g")
|
|||
|
|
{
|
|||
|
|
tableRow["重量UNIT"] = "克";
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//读取的table转成当前项目中的Table
|
|||
|
|
List<string> remove = new List<string>();
|
|||
|
|
foreach (DataColumn item in table.Columns)
|
|||
|
|
{
|
|||
|
|
if (!ht.ContainsValue(item.ColumnName)) {
|
|||
|
|
remove.Add(item.ColumnName);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
foreach (string item in remove)
|
|||
|
|
{
|
|||
|
|
table.Columns.Remove(item);
|
|||
|
|
}
|
|||
|
|
foreach (DictionaryEntry item in ht)
|
|||
|
|
{
|
|||
|
|
string value = item.Value.ToString();
|
|||
|
|
|
|||
|
|
if (value.Length > 6) {
|
|||
|
|
if (value.Substring(value.Length - 6, 6) == "ISNAME")
|
|||
|
|
{
|
|||
|
|
if (table.Columns.Contains(value.Substring(0, value.Length - 6)))
|
|||
|
|
{
|
|||
|
|
//增加一列
|
|||
|
|
DataColumn column = new DataColumn(item.Key.ToString());
|
|||
|
|
table.Columns.Add(column);
|
|||
|
|
foreach (DataRow tableRow in table.Rows)
|
|||
|
|
{
|
|||
|
|
|
|||
|
|
tableRow[item.Key.ToString()] = tableRow[value.Substring(0, value.Length - 6)].ToString();
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
foreach (DictionaryEntry item in ht)
|
|||
|
|
{
|
|||
|
|
if (table.Columns.Contains(item.Value.ToString()))
|
|||
|
|
{
|
|||
|
|
table.Columns[item.Value.ToString()].ColumnName = item.Key.ToString();
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
#region 名称转为编号
|
|||
|
|
foreach (DictionaryEntry item in ht)
|
|||
|
|
{
|
|||
|
|
string member = item.Key.ToString();
|
|||
|
|
if (classType.GetProperty(member) == null) {
|
|||
|
|
continue;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
object[] TypeobjType = classType.GetProperty(member).GetCustomAttributes(typeof(TableClmAttribute), false);
|
|||
|
|
if (TypeobjType.Length == 0)
|
|||
|
|
{
|
|||
|
|
continue;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
//object[] disPlay = classType.GetProperty(member).GetCustomAttributes(typeof(DisplayNameAttribute), false);
|
|||
|
|
//if (disPlay.Length == 0)
|
|||
|
|
//{
|
|||
|
|
// return null;
|
|||
|
|
//}
|
|||
|
|
//string captionName = (disPlay[0] as DisplayNameAttribute).DisplayName;
|
|||
|
|
string tableName = (TypeobjType[0] as TableClmAttribute).ClmJoinTable;
|
|||
|
|
string clmValue = (TypeobjType[0] as TableClmAttribute).JoinTableClm;
|
|||
|
|
string clmDis = (TypeobjType[0] as TableClmAttribute).JoinTableClmName;
|
|||
|
|
string clmWhere = (TypeobjType[0] as TableClmAttribute).JoinTableWhere;
|
|||
|
|
string columnData = (TypeobjType[0] as TableClmAttribute).ColumnData;
|
|||
|
|
DataTable dt = new DataTable();
|
|||
|
|
if (string.IsNullOrEmpty(columnData))
|
|||
|
|
{
|
|||
|
|
dt = WMS.Business.IBussFactory<WMS.Business.Common.BussCommon>.Instance().GetCustomCtrlData(tableName, clmValue, clmDis, clmWhere, "");
|
|||
|
|
}
|
|||
|
|
else {
|
|||
|
|
//columnData.Substring(1,1)
|
|||
|
|
|
|||
|
|
DataColumn column = new DataColumn("value");
|
|||
|
|
dt.Columns.Add(column);
|
|||
|
|
column = new DataColumn("string");
|
|||
|
|
dt.Columns.Add(column);
|
|||
|
|
|
|||
|
|
DataRow dataRow = dt.NewRow();
|
|||
|
|
dataRow[0] = columnData.Substring(2, 1);
|
|||
|
|
dataRow[1] = columnData.Substring(0, 1);
|
|||
|
|
dt.Rows.Add(dataRow);
|
|||
|
|
|
|||
|
|
dataRow = dt.NewRow();
|
|||
|
|
dataRow[0] = columnData.Substring(6, 1);
|
|||
|
|
dataRow[1] = columnData.Substring(4, 1);
|
|||
|
|
dt.Rows.Add(dataRow);
|
|||
|
|
}
|
|||
|
|
foreach (DataRow tableRow in table.Rows)
|
|||
|
|
{
|
|||
|
|
foreach (DataRow row in dt.Rows)
|
|||
|
|
{
|
|||
|
|
if (table.Columns.Contains(item.Key.ToString()))
|
|||
|
|
{
|
|||
|
|
if (row[1].ToString() == tableRow[item.Key.ToString()].ToString())
|
|||
|
|
{
|
|||
|
|
tableRow[item.Key.ToString()] = string.Empty;
|
|||
|
|
tableRow[item.Key.ToString()] = row[0].ToString();
|
|||
|
|
break;
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
#endregion
|
|||
|
|
|
|||
|
|
if (table.Columns.Count == 0) {
|
|||
|
|
SystemCommon.ShowErrorMessageBox("导入失败，所选Excel没有符合要求的数据");
|
|||
|
|
return null;
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
return ConvertHelper<T>.ConvertToList(table);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
}
|
|||
|
|
}
|