From 0ce538e3c3dc01153ce1bfc2d75276881206c222 Mon Sep 17 00:00:00 2001
From: 王 垚 <1402714037@qq.com>
Date: 星期五, 12 十一月 2021 13:34:35 +0800
Subject: [PATCH] 产品代码上传
---
src/BLL/Demo.Utility/NpoiHelper.cs | 307 +++-----------------------------------------------
1 files changed, 21 insertions(+), 286 deletions(-)
diff --git a/src/BLL/Demo.Utility/NpoiHelper.cs b/src/BLL/Demo.Utility/NpoiHelper.cs
index 7bae440..0a765f4 100644
--- a/src/BLL/Demo.Utility/NpoiHelper.cs
+++ b/src/BLL/Demo.Utility/NpoiHelper.cs
@@ -4,7 +4,6 @@
using System.IO;
using NPOI.HSSF.UserModel;
using System.Data;
-using System.Collections;
using System.Drawing;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
@@ -100,245 +99,6 @@
if (ds.Tables[0].Rows.Count == 0 && sb.ToString() != "") throw new Exception(sb.ToString());
return ds;
}
- public DataSet ReadExcel1(string FileName, int startRow, params NpoiDataType[] ColumnDataType)
- {
- string colNamePix = "F";
- int ertime = 0;
- int intime = 0;
- DataSet ds = new DataSet("ds");
- DataTable dt = new DataTable("dt");
- DataRow dr;
- StringBuilder sb = new StringBuilder();
- using (FileStream stream = new FileStream(@FileName, FileMode.Open, FileAccess.Read))
- {
- IWorkbook workbook = WorkbookFactory.Create(stream);//浣跨敤鎺ュ彛锛岃嚜鍔ㄨ瘑鍒玡xcel2003/2007鏍煎紡
- //for (int k = 0; k < 14; k++)
- //{
- ISheet sheet = workbook.GetSheetAt(0);//寰楀埌閲岄潰绗竴涓猻heet
- int j;
- IRow row;
- #region ColumnDataType璧嬪��
- if (ColumnDataType.Length <= 0)
- {
- row = sheet.GetRow(startRow - 1);//寰楀埌绗琲琛�
- ColumnDataType = new NpoiDataType[row.LastCellNum];
- for (int i = 0; i < row.LastCellNum; i++)
- {
- ICell hs = row.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK);
- ColumnDataType[i] = GetCellDataType(hs); //NpoiDataType.Blank;
- //if (i == 6)
- //{
- // ColumnDataType[i] = NpoiDataType.Numeric;
- //}
- }
- }
- #endregion
- for (j = 0; j < ColumnDataType.Length; j++)
- {
- Type tp = GetDataTableType(ColumnDataType[j]);
- //if (j == 6)
- //{
- // tp = typeof(double);
- //}
- dt.Columns.Add(colNamePix + (j + 1), tp);
- }
-
- ReadExcelEndRow = ReadExcelEndRow == 0 ? sheet.PhysicalNumberOfRows : ReadExcelEndRow;
- for (int i = startRow - 1; i <= ReadExcelEndRow; i++)
- {
- row = sheet.GetRow(i);//寰楀埌绗琲琛�
- if (row == null) continue;
- dr = dt.NewRow();
- bool flg = false;
- for (j = 0; j < ColumnDataType.Length; j++)
- {
-
- try
- {
- object temp = GetCellData(ColumnDataType[j], row, j);
- dr[colNamePix + (j + 1)] = temp;
- if (temp.ToString().Trim() != "")
- {
- flg = true;
- }
- }
- catch (Exception e)
- {
- ertime++;
- sb.Append(string.Format("绗瑊0}琛岀{1}鍒楁湭璇诲彇:{2}\r\n", i + 1, j + 1, e.Message));
- }
- }
- if (flg)
- {
- dt.Rows.Add(dr);
- intime++;
- }
- }
- ds.Tables.Add(dt);
- //}
- }
- if (ds.Tables[0].Rows.Count == 0 && sb.ToString() != "") throw new Exception(sb.ToString());
- return ds;
- }
- public DataSet ReadExcel(string FileName, int startRow, int endRow, params NpoiDataType[] ColumnDataType)
- {
- if (endRow > 0)
- {
- ReadExcelEndRow = endRow;
- }
- return ReadExcel(FileName, startRow);
- }
-
- public DataSet ReadExcel_double(string FileName, int startRow, int[] colnums, int LastCellNum, params NpoiDataType[] ColumnDataType)
- {
- string colNamePix = "F";
- int ertime = 0;
- int intime = 0;
- DataSet ds = new DataSet("ds");
- DataTable dt = new DataTable("dt");
- DataRow dr;
- StringBuilder sb = new StringBuilder();
- using (FileStream stream = new FileStream(@FileName, FileMode.Open, FileAccess.Read))
- {
- IWorkbook workbook = WorkbookFactory.Create(stream);//浣跨敤鎺ュ彛锛岃嚜鍔ㄨ瘑鍒玡xcel2003/2007鏍煎紡
- ISheet sheet = workbook.GetSheetAt(0);//寰楀埌閲岄潰绗竴涓猻heet
- int j;
- IRow row;
- #region ColumnDataType璧嬪��
- if (ColumnDataType.Length <= 0)
- {
- row = sheet.GetRow(startRow - 1);//寰楀埌绗琲琛�
- ColumnDataType = new NpoiDataType[LastCellNum];
- for (int i = 0; i < LastCellNum; i++)
- {
- ICell hs = row.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK);
- ColumnDataType[i] = GetCellDataType(hs); //NpoiDataType.Blank;
- }
- }
- #endregion
- for (j = 0; j < ColumnDataType.Length; j++)
- {
- Type tp = GetDataTableType(ColumnDataType[j]);
- dt.Columns.Add(colNamePix + j, tp);
- }
-
- ReadExcelEndRow = ReadExcelEndRow == 0 ? sheet.PhysicalNumberOfRows : ReadExcelEndRow;
- for (int i = startRow - 1; i <= ReadExcelEndRow; i++)
- {
- row = sheet.GetRow(i);//寰楀埌绗琲琛�
- if (row == null) continue;
- try
- {
- dr = dt.NewRow();
- bool co = false;
- for (j = 0; j < ColumnDataType.Length; j++)
- {
- co = false;
- for (int c = 0; c < colnums.Length; c++)
- {
- if (j == colnums[c])
- {
- co = true;
- break;
- }
-
- }
- if (co && i != 0)
- dr[colNamePix + j] = GetCellData_double(ColumnDataType[j], row, j);
- else
- dr[colNamePix + j] = GetCellData(ColumnDataType[j], row, j);
- }
- dt.Rows.Add(dr);
- intime++;
- }
- catch (Exception er)
- {
- ertime++;
- sb.Append(string.Format("绗瑊0}琛屽嚭閿欙細{1}\r\n", i + 1, er.Message));
- continue;
- }
- }
- ds.Tables.Add(dt);
- }
- if (ds.Tables[0].Rows.Count == 0 && sb.ToString() != "") throw new Exception(sb.ToString());
- return ds;
- }
-
- public DataSet ReadExcel_Sheet2(string FileName, int startRow, int[] colnums, params NpoiDataType[] ColumnDataType)
- {
- string colNamePix = "F";
- int ertime = 0;
- int intime = 0;
- DataSet ds = new DataSet("ds");
- DataTable dt = new DataTable("dt");
- DataRow dr;
- StringBuilder sb = new StringBuilder();
- using (FileStream stream = new FileStream(@FileName, FileMode.Open, FileAccess.Read))
- {
- IWorkbook workbook = WorkbookFactory.Create(stream);//浣跨敤鎺ュ彛锛岃嚜鍔ㄨ瘑鍒玡xcel2003/2007鏍煎紡
- ISheet sheet = workbook.GetSheetAt(1);//寰楀埌閲岄潰绗簩涓猻heet
- int j;
- IRow row;
- #region ColumnDataType璧嬪��
- if (ColumnDataType.Length <= 0)
- {
- row = sheet.GetRow(startRow - 1);//寰楀埌绗琲琛�
- ColumnDataType = new NpoiDataType[row.LastCellNum];
- for (int i = 0; i < row.LastCellNum; i++)
- {
- ICell hs = row.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK);
- ColumnDataType[i] = GetCellDataType(hs); //NpoiDataType.Blank;
- }
- }
- #endregion
- for (j = 0; j < ColumnDataType.Length; j++)
- {
- Type tp = GetDataTableType(ColumnDataType[j]);
- dt.Columns.Add(colNamePix + j, tp);
- }
-
- ReadExcelEndRow = ReadExcelEndRow == 0 ? sheet.PhysicalNumberOfRows : ReadExcelEndRow;
- for (int i = startRow - 1; i <= ReadExcelEndRow; i++)
- {
- row = sheet.GetRow(i);//寰楀埌绗琲琛�
- if (row == null) continue;
- try
- {
- dr = dt.NewRow();
- bool co = false;
- for (j = 0; j < ColumnDataType.Length; j++)
- {
- for (int c = 0; c < colnums.Length; c++)
- {
- if (j == colnums[c])
- {
- co = true;
- break;
- }
-
- }
- if (co)
- dr[colNamePix + j] = GetCellData_double(ColumnDataType[j], row, j);
- else
- dr[colNamePix + j] = GetCellData(ColumnDataType[j], row, j);
- }
- dt.Rows.Add(dr);
- intime++;
- }
- catch (Exception er)
- {
- ertime++;
- sb.Append(string.Format("绗瑊0}琛屽嚭閿欙細{1}\r\n", i + 1, er.Message));
- continue;
- }
- }
- ds.Tables.Add(dt);
- }
- if (ds.Tables[0].Rows.Count == 0 && sb.ToString() != "") throw new Exception(sb.ToString());
- return ds;
- }
-
-
#endregion
#region//璇诲彇澶氫釜sheet
public DataSet ReadExcelSub(string FileName, int startRow, params NpoiDataType[] ColumnDataType)
@@ -934,12 +694,9 @@
cell = row.CreateCell(j);//鍒涘缓绗�0琛岀殑绗琷鍒�
cell.CellStyle = style1;//鍗曞厓鏍煎紡璁剧疆鏍峰紡
- try
- {
- cell.SetCellType(CellType.String);
- cell.SetCellValue(columnValue.ToString());
- }
- catch { }
+ cell.SetCellType(CellType.String);
+ cell.SetCellValue(columnValue.ToString());
+
}
catch
@@ -1120,13 +877,8 @@
cell = row.CreateCell(j);//鍒涘缓绗�0琛岀殑绗琷鍒�
cell.CellStyle = style1;//鍗曞厓鏍煎紡璁剧疆鏍峰紡
- try
- {
- //cell.SetCellType(CellType.STRING);
- cell.SetCellValue(columnValue.ToString());
- }
- catch { }
-
+ //cell.SetCellType(CellType.STRING);
+ cell.SetCellValue(columnValue.ToString());
}
catch
{
@@ -1301,18 +1053,16 @@
{
return null;
}
- try
+
+ if (obj.ToString().Contains("鏈�") && obj.ToString().Contains("-") && objcell.DateCellValue != null)
{
- if (obj.ToString().Contains("鏈�") && obj.ToString().Contains("-") && objcell.DateCellValue != null)
- {
- datatype = NpoiDataType.Datetime;
- }
- else if (objcell.CellType == CellType.Numeric && (obj.ToString().Contains("-") || obj.ToString().Contains("/")) && objcell.DateCellValue != null)
- {
- datatype = NpoiDataType.Datetime;
- }
+ datatype = NpoiDataType.Datetime;
}
- catch { }
+ else if (objcell.CellType == CellType.Numeric && (obj.ToString().Contains("-") || obj.ToString().Contains("/")) && objcell.DateCellValue != null)
+ {
+ datatype = NpoiDataType.Datetime;
+ }
+
if (datatype == NpoiDataType.Datetime)
{
@@ -1359,23 +1109,12 @@
{
if (obj != null)
{
- try
+ obj = obj.ToString().Trim().Replace("\n", "");//鍘绘帀鍥炶溅锛宭igerui tree涓嶆敮鎸佸洖杞�
+ if (obj.ToString().StartsWith("."))
{
- obj = obj.ToString().Trim().Replace("\n","");//鍘绘帀鍥炶溅锛宭igerui tree涓嶆敮鎸佸洖杞�
- if (obj.ToString().StartsWith("."))
- {
- try
- {
- decimal.Parse("0" + obj.ToString());
- obj = "0" + obj.ToString();
- }
- catch
- {
- }
- }
- }
- catch (Exception e4)
- {
+
+ decimal.Parse("0" + obj.ToString());
+ obj = "0" + obj.ToString();
}
}
}
@@ -1411,15 +1150,11 @@
break;
case CellType.Numeric:
dtype = NpoiDataType.String;
- try
+ if (hs.NumericCellValue.ToString().Contains("-") || hs.NumericCellValue.ToString().Contains("/") || hs.ToString().Contains("-") || hs.ToString().Contains("/"))
{
- if (hs.NumericCellValue.ToString().Contains("-") || hs.NumericCellValue.ToString().Contains("/") || hs.ToString().Contains("-") || hs.ToString().Contains("/"))
- {
- hs.DateCellValue.ToString();
- dtype = NpoiDataType.Datetime;
- }
+ hs.DateCellValue.ToString();
+ dtype = NpoiDataType.Datetime;
}
- catch { }
cellvalue = hs.NumericCellValue.ToString();
break;
case CellType.String:
--
Gitblit v1.9.1