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);
|
||
}
|
||
|
||
}
|
||
}
|