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