From 1a1c8e71fcd14858f595029f089b2d4a00202b32 Mon Sep 17 00:00:00 2001
From: ogi <Administrator@S-OGI-PC>
Date: Fri, 05 Dec 2025 09:24:16 +0900
Subject: [PATCH] プロジェクトファイルを追加。
---
HotelPms.Share.Windows/Util/NPOIExcel.cs | 400 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 400 insertions(+), 0 deletions(-)
diff --git a/HotelPms.Share.Windows/Util/NPOIExcel.cs b/HotelPms.Share.Windows/Util/NPOIExcel.cs
new file mode 100644
index 0000000..da3c925
--- /dev/null
+++ b/HotelPms.Share.Windows/Util/NPOIExcel.cs
@@ -0,0 +1,400 @@
+using NPOI.HSSF.UserModel;
+using NPOI.SS.UserModel;
+using NPOI.XSSF.UserModel;
+
+namespace HotelPms.Share.Windows.Util
+{
+ public class NPOIExcel : IDisposable
+ {
+ #region ★★★★★ Declartions ★★★★★
+
+ private bool m_Disposed = false;
+ private bool m_IsEditting = false;
+
+ #endregion
+
+ #region ★★★★★ Property ★★★★★
+
+ /// <summary>
+ /// 絶対パス
+ /// </summary>
+ public string FileName { get; set; } = string.Empty;
+
+ /// <summary>
+ /// 現在のWorkbook
+ /// </summary>
+ public IWorkbook Workbook { get; set; } = null;
+
+ /// <summary>
+ /// 選択されたSheet
+ /// </summary>
+ public ISheet Sheet { get; set; } = null;
+
+ /// <summary>
+ /// Excel2003前？
+ /// </summary>
+ public bool XlsMode { get; set; } = false;
+
+ #endregion
+
+ #region ★★★★★ Class Event ★★★★★
+
+ public NPOIExcel(string fileName)
+ {
+ FileName = fileName;
+ FileInfo fileInfo = new FileInfo(FileName);
+ XlsMode = (fileInfo.Extension == ".xls");
+
+ if (System.IO.File.Exists(FileName))
+ {
+ using (FileStream file = new FileStream(FileName, FileMode.Open, FileAccess.Read))
+ {
+ Workbook = XlsMode ? new HSSFWorkbook(file) : new XSSFWorkbook(file);
+ }
+ Sheet = Workbook.GetSheetAt(0);
+ Sheet.ForceFormulaRecalculation = true;
+ }
+ else
+ {
+ Workbook = XlsMode ? new HSSFWorkbook() : new XSSFWorkbook();
+ Sheet = Workbook.CreateSheet("Sheet1"); //シートの作成
+ }
+ }
+
+ ~NPOIExcel()
+ {
+ Dispose(false);
+ }
+
+ protected virtual void Dispose(bool disposing)
+ {
+ if (!m_Disposed) //一回だけ
+ {
+ if (disposing)
+ {
+ if (m_IsEditting) { Save(); }
+ //Managed Resources
+ }
+
+ //Unmanaged resources
+ m_Disposed = true;
+ }
+ }
+
+ public void Dispose()
+ {
+ Dispose(true);
+ GC.SuppressFinalize(this);
+ }
+
+ #endregion
+
+ #region ★★★★★ Private Function ★★★★★
+
+ private string GetCellFormat(ICell cell)
+ {
+ IDataFormat df = XlsMode ? new HSSFDataFormat((Workbook as HSSFWorkbook).Workbook) : new XSSFDataFormat((Workbook as XSSFWorkbook).GetStylesSource());
+ return df.GetFormat(cell.CellStyle.DataFormat);
+ }
+
+
+ #endregion
+
+ #region ★★★★★ Public Function ★★★★★
+
+ /// <summary>
+ /// シート変換
+ /// </summary>
+ /// <param name="sheetIdx"></param>
+ public void SetCurrentSheet(int sheetIdx)
+ {
+ Sheet = Workbook.GetSheetAt(sheetIdx);
+ Sheet.ForceFormulaRecalculation = true;
+ }
+
+ /// <summary>
+ /// 存在しない時、null
+ /// </summary>
+ /// <param name="colRowValue"></param>
+ /// <returns></returns>
+ public ICell? GetCell(string colRowValue)
+ {
+ return GetCell(colRowValue, false);
+ }
+
+ public object? GetCellValue(string colRowValue)
+ {
+ try
+ {
+ ICell cell = GetCell(colRowValue);
+ if (cell == null) { return string.Empty; }
+ System.Diagnostics.Debug.WriteLine($"{colRowValue}.CellType={cell.CellType}");
+ if (cell.CellType == CellType.Numeric) { return cell.NumericCellValue; }
+ else if (cell.CellType == CellType.Boolean) { return cell.BooleanCellValue; }
+ else { return cell.StringCellValue; }
+ }
+ catch
+ {
+ return string.Empty;
+ }
+ }
+
+ public string? GetCellForceString(string colRowValue)
+ {
+ try
+ {
+ object ret = GetCellValue(colRowValue);
+ return ret == null ? string.Empty : ret.ToString();
+ }
+ catch
+ {
+ return string.Empty;
+ }
+ }
+
+ /// <summary>
+ /// セルの値を取得する
+ /// </summary>
+ /// <param name="colRowValue"></param>
+ /// <returns></returns>
+ public string GetCellString(string colRowValue)
+ {
+ try
+ {
+ ICell cell = GetCell(colRowValue);
+ if (cell == null) { return string.Empty; }
+ System.Diagnostics.Debug.WriteLine($"{colRowValue}={cell.StringCellValue}");
+ return cell.StringCellValue;
+ }
+ catch
+ {
+ return string.Empty;
+ }
+ }
+
+ public int GetCellInt(string colRowValue)
+ {
+ try
+ {
+ ICell cell = GetCell(colRowValue);
+ if (cell == null) { return 0; }
+ return (int)cell.NumericCellValue;
+ }
+ catch
+ {
+ return 0;
+ }
+ }
+
+ /// <summary>
+ /// 存在しない時、新規
+ /// </summary>
+ /// <param name="colRowValue"></param>
+ /// <returns></returns>
+ public ICell? GetCell(string colRowValue, bool newCell)
+ {
+ System.Drawing.Point p = GetCellPoint(colRowValue);
+ IRow row = Sheet.GetRow(p.Y);
+ if (row == null)
+ {
+ if (newCell) { row = Sheet.CreateRow(p.Y); } else { return null; }
+ }
+
+ ICell cell = row.GetCell(p.X);
+ if (cell == null)
+ {
+ if (newCell) { cell = row.CreateCell(p.X); } else { return null; }
+ }
+ return cell;
+ }
+
+ /// <summary>
+ /// フォントの作成
+ /// </summary>
+ /// <returns></returns>
+ public IFont CreateFont()
+ {
+ IFont font = Workbook.CreateFont();
+ return font;
+ }
+
+ /// <summary>
+ /// 計算式セット
+ /// </summary>
+ /// <param name="colRowValue"></param>
+ /// <param name="formula"></param>
+ public void SetCellFormula(string colRowValue, string formula)
+ {
+ m_IsEditting = true;
+ ICell cell = GetCell(colRowValue, true);
+ cell.SetCellFormula(formula);
+
+ }
+
+ /// <summary>
+ /// セルの値セット：xls専用
+ /// </summary>
+ /// <param name="colRowValue"></param>
+ /// <param name="richTextString"></param>
+ public void SetCell(string colRowValue, HSSFRichTextString richTextString)
+ {
+ m_IsEditting = true;
+ ICell cell = GetCell(colRowValue, true);
+ cell.SetCellValue(richTextString);
+ }
+
+ /// <summary>
+ /// セルの値セット：数字専用
+ /// </summary>
+ /// <param name="colRowValue"></param>
+ /// <param name="value"></param>
+ public void SetCell(string colRowValue, double value)
+ {
+ m_IsEditting = true;
+ ICell cell = GetCell(colRowValue, true);
+ cell.SetCellValue(value);
+ }
+
+ /// <summary>
+ /// セルの値セット：日付専用
+ /// </summary>
+ /// <param name="colRowValue"></param>
+ /// <param name="value"></param>
+ public void SetCell(string colRowValue, DateTime value)
+ {
+ m_IsEditting = true;
+ ICell cell = GetCell(colRowValue, true);
+ cell.SetCellValue(value);
+ }
+
+ /// <summary>
+ /// セルの値セット：Boolean
+ /// </summary>
+ /// <param name="colRowValue"></param>
+ /// <param name="value"></param>
+ public void SetCell(string colRowValue, bool value)
+ {
+ m_IsEditting = true;
+ ICell cell = GetCell(colRowValue, true);
+ cell.SetCellValue(value);
+ }
+
+ /// <summary>
+ /// 数字型変換対応
+ /// </summary>
+ /// <param name="colRowValue"></param>
+ /// <param name="value"></param>
+ public void SetCell(string colRowValue, string value)
+ {
+ m_IsEditting = true;
+ System.Drawing.Point p = GetCellPoint(colRowValue);
+ IRow row = Sheet.GetRow(p.Y);
+ if (row == null) { row = Sheet.CreateRow(p.Y); }
+
+ ICell cell = row.GetCell(p.X);
+ if (cell == null) { cell = row.CreateCell(p.X); }
+
+ string format = GetCellFormat(cell);
+ if ((format.Contains("#") && format.Contains("0")) || format.Contains("0_"))
+ {
+ double val = 0;
+ if (double.TryParse(value, out val))
+ {
+ cell.SetCellValue(val);
+ }
+ else
+ {
+ cell.SetCellValue(value);
+ }
+ }
+ else
+ {
+ cell.SetCellValue(value);
+ }
+ }
+
+ /// <summary>
+ /// 保存
+ /// </summary>
+ public void Save()
+ {
+ //Write the stream data of workbook to the root directory
+ using (FileStream file = new FileStream(FileName, FileMode.Create))
+ {
+ Workbook.Write(file, true);
+ file.Close();
+ }
+ }
+
+ /// <summary> ExcelX位置→EXCEL列文字 (1→A,2→B,3→C,27→AA)
+ /// </summary>
+ /// <param name="ColNum">索引号</param>
+ /// <returns>string</returns>
+ public static string ToColName(int ColNum)
+ {
+ int ll_mod;
+ string ColName = "";
+
+ while (ColNum > 0)
+ {
+ ll_mod = ColNum % 26;
+ if (ll_mod == 0)
+ {
+ ColName = "Z" + ColName;
+ ll_mod = 1;
+ }
+ else
+ {
+ ColName = (Convert.ToChar(ll_mod + 64)).ToString() + ColName;
+ }
+ ColNum = (ColNum - ll_mod) / 26;
+ }
+
+ return ColName;
+ }
+
+ /// <summary> Excel列文字→ExcelX位置 ("A"→1、"AA"→27)
+ /// </summary>
+ /// <param name="ColName">列名</param>
+ /// <returns>int</returns>
+ public static int ToIndex(string ColName)
+ {
+ int ColIndex = 0;
+ for (int i = ColName.Length - 1; i >= 0; i--)
+ {
+ ColIndex += Convert.ToInt32(((int)ColName[i] - 64) * Math.Pow(26, (ColName.Length - 1) - i));
+ }
+
+ return ColIndex;
+ }
+
+
+ /// <summary> Excel文字→Excel位置("A1"→(0,0)、"B2"→(1,1))(0基準)
+ /// </summary>
+ /// <param name="colRowValue"></param>
+ /// <returns></returns>
+ public static System.Drawing.Point GetCellPoint(string colRowValue)
+ {
+ System.Drawing.Point point = new System.Drawing.Point(0, 0);
+ string colstr = string.Empty;
+ string rowStr = string.Empty;
+ for (int i = 0; i < colRowValue.Length; i++)
+ {
+ if (Char.IsNumber(colRowValue[i]))
+ {
+ rowStr += colRowValue[i].ToString();
+ }
+ else
+ {
+ colstr += colRowValue[i].ToString();
+ }
+ }
+
+ point.Y = Convert.ToInt32(rowStr) - 1; //行
+ point.X = ToIndex(colstr) - 1;
+ return point;
+ }
+
+ #endregion
+ }
+}
--
Gitblit v1.10.0