From cb776a0d9f1257047ae39c740b06e6ab487b1bfc Mon Sep 17 00:00:00 2001
From: yl <ykxkd@outlook.com>
Date: 星期二, 01 十一月 2022 18:15:42 +0800
Subject: [PATCH] 设备清单导入接口开发，安灯响应接口修改
---
VueWebApi/Tools/ImportExcel.cs | 352 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 352 insertions(+), 0 deletions(-)
diff --git a/VueWebApi/Tools/ImportExcel.cs b/VueWebApi/Tools/ImportExcel.cs
index f7470ad..e8dc1c2 100644
--- a/VueWebApi/Tools/ImportExcel.cs
+++ b/VueWebApi/Tools/ImportExcel.cs
@@ -271,6 +271,170 @@
return list;
}
+
+ /// <summary>
+ /// Excel 3涓猄heet瀵煎叆鎴怐atable
+ /// </summary>
+ /// <param name="file">瀵煎叆璺緞(鍖呭惈鏂囦欢鍚嶄笌鎵╁睍鍚�)</param>
+ /// <returns></returns>
+ public static List<DataTable> ExcelToThreeTableList(string file)
+ {
+ List<DataTable> list = new List<DataTable>();
+ IWorkbook Workbook;
+ try
+ {
+ using (FileStream fileStream = new FileStream(file, FileMode.Open, FileAccess.Read))
+ {
+ //XSSFWorkbook 閫傜敤XLSX鏍煎紡锛孒SSFWorkbook 閫傜敤XLS鏍煎紡
+ string fileExt = Path.GetExtension(file).ToLower();
+ if (fileExt == ".xls")
+ {
+ Workbook = new HSSFWorkbook(fileStream);
+ }
+ else if (fileExt == ".xlsx")
+ {
+ Workbook = new XSSFWorkbook(fileStream);
+ }
+ else
+ {
+ Workbook = null;
+ }
+ }
+ }
+ catch (Exception ex)
+ {
+ throw ex;
+ }
+ int count = Workbook.NumberOfSheets; //鑾峰彇鎵�鏈塖heetName
+ if (count < 3)
+ {
+ DataTable table = new DataTable();
+ //瀹氫綅鍦ㄧ涓�涓猻heet
+ ISheet sheet = Workbook.GetSheetAt(0);
+ //绗竴琛屼负鏍囬琛�
+ IRow headerRow = sheet.GetRow(0);
+ if (headerRow == null)
+ {
+ return list;
+ }
+ int cellCount = headerRow.LastCellNum;
+ int rowCount = sheet.LastRowNum;
+
+ //寰幆娣诲姞鏍囬鍒�
+ 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 && row.Cells.Count > 0)
+ {
+ for (int j = row.FirstCellNum; j < cellCount; j++)
+ {
+ if (row.GetCell(j) != null)
+ {
+ dataRow[j] = GetCellValue(row.GetCell(j)).Trim();
+ }
+ }
+ }
+ table.Rows.Add(dataRow);
+ }
+ //娓呴櫎鏈�鍚庣殑绌鸿
+ for (int i = 0; i < table.Rows.Count; i++)
+ {
+ bool isnull = true;
+ for (int j = 0; j < table.Columns.Count; j++)
+ {
+ if (table.Rows[i][j].ToString() != null && table.Rows[i][j].ToString() != "")
+ {
+ if (table.Rows[i][j].ToString().Trim() != "" && table.Rows[i][j].ToString().Trim() != null)
+ {
+ isnull = false;
+ break;
+ }
+ }
+ }
+ if (isnull)
+ {
+ table.Rows[i].Delete();
+ i = 0;
+ }
+ }
+ list.Add(table);
+ }
+ else
+ {
+ for (int m = 0; m < count; m++)
+ {
+ DataTable table = new DataTable();
+ //瀹氫綅鍦ㄧ涓�涓猻heet
+ ISheet sheet = Workbook.GetSheetAt(m);
+ //绗竴琛屼负鏍囬琛�
+ IRow headerRow = sheet.GetRow(0);
+ if (headerRow == null)
+ {
+ return list;
+ }
+ int cellCount = headerRow.LastCellNum;
+ int rowCount = sheet.LastRowNum;
+
+ //寰幆娣诲姞鏍囬鍒�
+ 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 && row.Cells.Count > 0)
+ {
+ for (int j = row.FirstCellNum; j < cellCount; j++)
+ {
+ if (row.GetCell(j) != null)
+ {
+ dataRow[j] = GetCellValue(row.GetCell(j)).Trim();
+ }
+ }
+ }
+ table.Rows.Add(dataRow);
+ }
+ //娓呴櫎鏈�鍚庣殑绌鸿
+ for (int i = 0; i < table.Rows.Count; i++)
+ {
+ bool isnull = true;
+ for (int j = 0; j < table.Columns.Count; j++)
+ {
+ if (table.Rows[i][j].ToString() != null && table.Rows[i][j].ToString() != "")
+ {
+ if (table.Rows[i][j].ToString().Trim() != "" && table.Rows[i][j].ToString().Trim() != null)
+ {
+ isnull = false;
+ break;
+ }
+ }
+ }
+ if (isnull)
+ {
+ table.Rows[i].Delete();
+ i = 0;
+ }
+ }
+ list.Add(table);
+ }
+ }
+ return list;
+ }
+
+
/// <summary>
/// 鑾峰彇鍗曞厓鏍肩被鍨�
/// </summary>
@@ -700,7 +864,195 @@
return list;
}
+ /// <summary>
+ /// Excel 3涓猄heet 鏁版嵁楠岃瘉
+ /// </summary>
+ /// <param name="file">瀵煎叆璺緞(鍖呭惈鏂囦欢鍚嶄笌鎵╁睍鍚�)</param>
+ /// <returns></returns>
+ public static List<ExcelErro> ExcelToThreeTableListErro(string file)
+ {
+ List<ExcelErro> list = new List<ExcelErro>();
+ IWorkbook Workbook;
+ bool iscell = false;
+ int iscellCount = 0;
+ try
+ {
+ using (FileStream fileStream = new FileStream(file, FileMode.Open, FileAccess.Read))
+ {
+ //XSSFWorkbook 閫傜敤XLSX鏍煎紡锛孒SSFWorkbook 閫傜敤XLS鏍煎紡
+ string fileExt = Path.GetExtension(file).ToLower();
+ if (fileExt == ".xls")
+ {
+ Workbook = new HSSFWorkbook(fileStream);
+ }
+ else if (fileExt == ".xlsx")
+ {
+ Workbook = new XSSFWorkbook(fileStream);
+ }
+ else
+ {
+ Workbook = null;
+ }
+ }
+ }
+ catch (Exception ex)
+ {
+ throw ex;
+ }
+ for (int mm = 0; mm < 3; mm++)
+ {
+ DataTable table = new DataTable();
+ //瀹氫綅鍦ㄧ涓�涓猻heet
+ ISheet sheet = Workbook.GetSheetAt(mm);
+ //sheet.ShiftRows(sheet.FirstRowNum-1, sheet.LastRowNum,1);
+ //绗竴琛屼负鏍囬琛�
+ IRow headerRow = sheet.GetRow(0);
+ int cellCount = headerRow.LastCellNum;
+ int rowCount0 = sheet.FirstRowNum;
+ //int rowCount = sheet.LastRowNum;
+ int rowCount = Rownum(headerRow, cellCount, sheet, sheet.LastRowNum); //鑾峰彇鍒犻櫎绌鸿涔嬪悗鐨勬暟鎹 (鍙拡瀵逛簬鏈�鍚庢暟鎹」鏈夌┖琛岀殑)
+ if (rowCount - rowCount0 >= 1)
+ {
+ //-------------鍒ゆ柇蹇呭～椤规槸鍚︿负绌�----------------//
+ //寰幆鏁版嵁琛�
+ for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
+ {
+ iscell = false;
+ iscellCount = 0;
+ IRow row = sheet.GetRow(i);
+ //鍒ゆ柇鏄惁鏈夌┖琛�
+ if (row == null)
+ {
+ ExcelErro erro = new ExcelErro();
+ erro.RoeNumber = (i + 1).ToString();
+ erro.ErrorField = "绌鸿";
+ erro.ErrorCont = "绌鸿";
+ list.Add(erro);
+ continue;
+ }
+ //鍒ゆ柇鏄惁瀛樺湪绌烘暟鎹
+ for (int c = 0; c < cellCount; c++)
+ {
+ if (GetCellValue(row.GetCell(c)).Trim() != null && GetCellValue(row.GetCell(c)).Trim() != "")
+ {
+ iscell = false;
+ }
+ else
+ {
+ iscell = true;
+ iscellCount++;
+ }
+ }
+ if (iscell == true && iscellCount == cellCount) //绌鸿
+ {
+ continue;
+ }
+ else
+ {
+ //寰幆鏍囬鍒�
+ for (int j = headerRow.FirstCellNum; j < cellCount; j++)
+ {
+ //鑾峰彇鍗曞厓鏍肩殑濉厖鑹�
+ string rgb = headerRow.Cells[j].CellStyle.FillForegroundColor.ToString();
+ if (rgb == "51") //蹇呭～椤瑰瓧娈靛垪澶村～鍏呰壊
+ {
+ if (sheet.GetRow(i) == null || sheet.GetRow(i).ToString() == "") //瀛樺湪绌鸿
+ {
+ ExcelErro erro = new ExcelErro();
+ erro.RoeNumber = i.ToString();
+ erro.ErrorField = "{绗�" + i.ToString() + "琛寎";
+ erro.ErrorCont = "{绗�" + i.ToString() + "}琛屼负绌�";
+ list.Add(erro);
+ break;
+ }
+ else
+ {
+ string cellValue = GetCellValue(sheet.GetRow(i).GetCell(j)).Trim(); //GetCell:杩斿洖鍖呮嫭绌哄垪鍊� Cells:鍙繑鍥炴湁鍊肩殑鍒�
+ if (cellValue == "" || cellValue == null)
+ {
+ ExcelErro erro = new ExcelErro();
+ erro.RoeNumber = (sheet.GetRow(i).RowNum + 1).ToString();
+ erro.ErrorField = "{" + headerRow.Cells[j].StringCellValue + "}";
+ erro.ErrorCont = mm == 0 ? "涓昏〃涓細" + "{" + headerRow.Cells[j].StringCellValue + "}瀛楁涓嶈兘涓虹┖" : "瀛愯〃涓細" + "{" + headerRow.Cells[j].StringCellValue + "}瀛楁涓嶈兘涓虹┖";
+ list.Add(erro);
+ }
+ }
+ }
+ }
+ }
+ }
+ //-------------鍒ゆ柇鍞竴椤规槸鍚﹂噸澶�----------------//
+ //寰幆娣诲姞鏍囬鍒�
+ 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)).Trim();
+ }
+ }
+ }
+ table.Rows.Add(dataRow);
+ }
+ //娓呴櫎鏈�鍚庣殑绌鸿
+ for (int i = 0; i < table.Rows.Count; i++)
+ {
+ bool isnull = true;
+ for (int j = 0; j < table.Columns.Count; j++)
+ {
+ if (table.Rows[i][j].ToString() != null && table.Rows[i][j].ToString() != "")
+ {
+ if (table.Rows[i][j].ToString().Trim() != "" && table.Rows[i][j].ToString().Trim() != null)
+ {
+ isnull = false;
+ break;
+ }
+ }
+ }
+ if (isnull)
+ {
+ table.Rows[i].Delete();
+ i = 0;
+ }
+ }
+ DataView view = new DataView(table);
+ //鑾峰彇鎵�鏈夊敮涓�鍒�
+ List<string> list0 = new List<string>();
+ //寰幆鏍囬鍒�
+ for (int j = headerRow.FirstCellNum; j < cellCount; j++)
+ {
+ bool isContain = headerRow.Cells[j].StringCellValue.Contains("鍞竴"); //鏍囬鍒椾腑鐨勫繀濉垪
+ if (isContain == true)
+ {
+ //
+ if (view.ToTable(true, new string[] { "" + headerRow.Cells[j].StringCellValue + "" }).Rows.Count < table.Rows.Count)
+ {
+ ExcelErro erro = new ExcelErro();
+ erro.RoeNumber = "/";
+ erro.ErrorField = "{" + headerRow.Cells[j].StringCellValue + "}";
+ erro.ErrorCont = mm == 0 ? "涓昏〃涓細" + "{" + headerRow.Cells[j].StringCellValue + "}瀛楁鏈夐噸澶嶆暟鎹�" : "瀛愯〃涓細" + "{" + headerRow.Cells[j].StringCellValue + "}瀛楁鏈夐噸澶嶆暟鎹�";
+ list.Add(erro);
+ }
+ }
+ }
+ }
+ }
+ List<ExcelErro> stuList = list.OrderBy(s => s.RoeNumber).ToList<ExcelErro>();
+ return list;
+ }
--
Gitblit v1.9.3