From 2b25f973bb6d72ce6971d6f9c3cdccf51b7962ab Mon Sep 17 00:00:00 2001
From: wangxinhui <wangxinhui@hnkhzn.com>
Date: 星期六, 06 九月 2025 14:27:08 +0800
Subject: [PATCH] Merge branch 'master' of http://115.159.85.185:8098/r/ZhiHuiQiCe/LongDeLiLiKu
---
项目代码/WCS/WCSServices/WIDESEAWCS_Common/Utilities/ImportExcelHelper.cs | 291 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 291 insertions(+), 0 deletions(-)
diff --git "a/\351\241\271\347\233\256\344\273\243\347\240\201/WCS/WCSServices/WIDESEAWCS_Common/Utilities/ImportExcelHelper.cs" "b/\351\241\271\347\233\256\344\273\243\347\240\201/WCS/WCSServices/WIDESEAWCS_Common/Utilities/ImportExcelHelper.cs"
new file mode 100644
index 0000000..5a2103a
--- /dev/null
+++ "b/\351\241\271\347\233\256\344\273\243\347\240\201/WCS/WCSServices/WIDESEAWCS_Common/Utilities/ImportExcelHelper.cs"
@@ -0,0 +1,291 @@
+锘縰sing NPOI.HSSF.UserModel;
+using NPOI.SS.UserModel;
+using NPOI.XSSF.UserModel;
+using System;
+using System.Collections.Generic;
+using System.Data;
+using System.IO;
+using System.Linq;
+using System.Text;
+using System.Threading.Tasks;
+
+namespace WIDESEAWCS_Common.Utilities
+{
+ public class ImportExcelHelper : IDisposable
+ {
+ private string fileName = null; //鏂囦欢鍚�
+ private IWorkbook workbook = null;
+ private FileStream fs = null;
+ private bool disposed;
+
+ public ImportExcelHelper(string fileName)
+ {
+ this.fileName = fileName;
+ disposed = false;
+ }
+
+ /// <summary>
+ /// 灏咲ataTable鏁版嵁瀵煎叆鍒癳xcel涓�
+ /// </summary>
+ /// <param name="data">瑕佸鍏ョ殑鏁版嵁</param>
+ /// <param name="isColumnWritten">DataTable鐨勫垪鍚嶆槸鍚﹁瀵煎叆</param>
+ /// <param name="sheetName">瑕佸鍏ョ殑excel鐨剆heet鐨勫悕绉�</param>
+ /// <returns>瀵煎叆鏁版嵁琛屾暟(鍖呭惈鍒楀悕閭ｄ竴琛�)</returns>
+ public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten)
+ {
+ int i = 0;
+ int j = 0;
+ int count = 0;
+ ISheet sheet = null;
+
+ fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
+ if (fileName.IndexOf(".xlsx") > 0) // 2007鐗堟湰
+ workbook = new XSSFWorkbook();
+ else if (fileName.IndexOf(".xls") > 0) // 2003鐗堟湰
+ workbook = new HSSFWorkbook();
+
+ try
+ {
+ if (workbook != null)
+ {
+ sheet = workbook.CreateSheet(sheetName);
+ }
+ else
+ {
+ return -1;
+ }
+
+ if (isColumnWritten == true) //鍐欏叆DataTable鐨勫垪鍚�
+ {
+ IRow row = sheet.CreateRow(0);
+ for (j = 0; j < data.Columns.Count; ++j)
+ {
+ row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
+ }
+ count = 1;
+ }
+ else
+ {
+ count = 0;
+ }
+
+ for (i = 0; i < data.Rows.Count; ++i)
+ {
+ IRow row = sheet.CreateRow(count);
+ for (j = 0; j < data.Columns.Count; ++j)
+ {
+ row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
+ }
+ ++count;
+ }
+ workbook.Write(fs, true); //鍐欏叆鍒癳xcel
+ return count;
+ }
+ catch (Exception ex)
+ {
+ Console.WriteLine("Exception: " + ex.Message);
+ return -1;
+ }
+ }
+
+ /// <summary>
+ /// 灏唀xcel涓殑鏁版嵁瀵煎叆鍒癉ataTable涓�
+ /// </summary>
+ /// <param name="sheetName">excel宸ヤ綔钖剆heet鐨勫悕绉�</param>
+ /// <param name="isFirstRowColumn">绗竴琛屾槸鍚︽槸DataTable鐨勫垪鍚�</param>
+ /// <returns>杩斿洖鐨凞ataTable</returns>
+ //public DataTable ExcelToDataTable(string sheetName = null, bool isFirstRowColumn = true)
+ //{
+ // ISheet sheet = null;
+ // DataTable data = new DataTable();
+ // int startRow = 0;
+ // try
+ // {
+ // fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
+ // if (fileName.IndexOf(".xlsx") > 0) // 2007鐗堟湰
+ // workbook = new XSSFWorkbook(fs);
+ // else if (fileName.IndexOf(".xls") > 0) // 2003鐗堟湰
+ // workbook = new HSSFWorkbook(fs);
+
+ // if (sheetName != null)
+ // {
+ // sheet = workbook.GetSheet("Sheet1");
+ // }
+ // else
+ // {
+ // sheet = workbook.GetSheetAt(0);
+ // }
+ // if (sheet != null)
+ // {
+ // IRow firstRow = sheet.GetRow(0);
+ // int cellCount = firstRow.LastCellNum; //涓�琛屾渶鍚庝竴涓猚ell鐨勭紪鍙� 鍗虫�荤殑鍒楁暟
+
+ // if (isFirstRowColumn)
+ // {
+ // for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
+ // {
+ // DataColumn column = new DataColumn(firstRow.GetCell(i).StringCellValue);
+ // 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) continue; //娌℃湁鏁版嵁鐨勮榛樿鏄痭ull銆�銆�銆�銆�銆�銆�銆�
+
+ // DataRow dataRow = data.NewRow();
+ // for (int j = row.FirstCellNum; j < cellCount; ++j)
+ // {
+ // if (row.GetCell(j) != null) //鍚岀悊锛屾病鏈夋暟鎹殑鍗曞厓鏍奸兘榛樿鏄痭ull
+ // dataRow[j] = row.GetCell(j).ToString();
+ // }
+ // data.Rows.Add(dataRow);
+ // }
+ // }
+
+ // return data;
+ // }
+ // catch (Exception ex)
+ // {
+ // Console.WriteLine("Exception: " + ex.Message);
+ // return null;
+ // }
+ //}
+
+ public DataTable ExcelToDataTable(string sheetName = null, bool isFirstRowColumn = true)
+ {
+ ISheet sheet = null;
+ DataTable data = new DataTable();
+ int startRow = 0;
+
+ try
+ {
+ using (fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
+ {
+ if (fileName.IndexOf(".xlsx", StringComparison.OrdinalIgnoreCase) > 0)
+ workbook = new XSSFWorkbook(fs);
+ else if (fileName.IndexOf(".xls", StringComparison.OrdinalIgnoreCase) > 0)
+ workbook = new HSSFWorkbook(fs);
+
+ // 淇sheet閫夋嫨閫昏緫
+ if (!string.IsNullOrEmpty(sheetName))
+ {
+ sheet = workbook.GetSheet(sheetName); // 浣跨敤浼犲叆鐨剆heetName
+ }
+ else
+ {
+ sheet = workbook.GetSheetAt(0); // 榛樿绗竴涓猻heet
+ }
+
+ if (sheet == null)
+ {
+ Console.WriteLine("鏈壘鍒版寚瀹氱殑宸ヤ綔琛�");
+ return null;
+ }
+
+ IRow firstRow = sheet.GetRow(0);
+ if (firstRow == null)
+ {
+ Console.WriteLine("宸ヤ綔琛ㄤ负绌�");
+ return null;
+ }
+
+ int cellCount = firstRow.LastCellNum;
+
+ if (isFirstRowColumn)
+ {
+ for (int i = firstRow.FirstCellNum; i < cellCount; i++)
+ {
+ ICell cell = firstRow.GetCell(i);
+ string columnName = (cell != null) ? cell.ToString() : $"Column{i}";
+ data.Columns.Add(columnName);
+ }
+ startRow = 1; // 浠庣浜岃寮�濮嬶紙璺宠繃鏍囬琛岋級
+ }
+ else
+ {
+ for (int i = 0; i < cellCount; i++)
+ {
+ data.Columns.Add($"Column{i}");
+ }
+ startRow = 0;
+ }
+
+ // 璇诲彇鏁版嵁琛�
+ for (int i = startRow; i <= sheet.LastRowNum; i++)
+ {
+ IRow row = sheet.GetRow(i);
+ if (row == null) continue;
+
+ DataRow dataRow = data.NewRow();
+ for (int j = 0; j < cellCount; j++)
+ {
+ ICell cell = row.GetCell(j);
+ if (cell != null)
+ {
+ // 鏍规嵁鍗曞厓鏍肩被鍨嬫纭鐞嗘暟鎹�
+ switch (cell.CellType)
+ {
+ case CellType.String:
+ dataRow[j] = cell.StringCellValue;
+ break;
+ case CellType.Numeric:
+ if (DateUtil.IsCellDateFormatted(cell))
+ dataRow[j] = cell.DateCellValue;
+ else
+ dataRow[j] = cell.NumericCellValue;
+ break;
+ case CellType.Boolean:
+ dataRow[j] = cell.BooleanCellValue;
+ break;
+ case CellType.Formula:
+ dataRow[j] = cell.ToString(); // 鎴栬�呭鐞嗗叕寮忕粨鏋�
+ break;
+ default:
+ dataRow[j] = cell.ToString();
+ break;
+ }
+ }
+ }
+ data.Rows.Add(dataRow);
+ }
+ }
+ return data;
+ }
+ catch (Exception ex)
+ {
+ Console.WriteLine($"璇诲彇Excel寮傚父: {ex.Message}");
+ return null;
+ }
+ }
+
+ public void Dispose()
+ {
+ Dispose(true);
+ GC.SuppressFinalize(this);
+ }
+
+ protected virtual void Dispose(bool disposing)
+ {
+ if (!this.disposed)
+ {
+ if (disposing)
+ {
+ if (fs != null)
+ fs.Close();
+ }
+
+ fs = null;
+ disposed = true;
+ }
+ }
+ }
+}
--
Gitblit v1.9.3