You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
899 lines
36 KiB
899 lines
36 KiB
using NPOI.HSSF.UserModel;
|
|
using NPOI.SS.UserModel;
|
|
using NPOI.SS.Util;
|
|
using NPOI.XSSF.UserModel;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Globalization;
|
|
using System.IO;
|
|
using System.Linq;
|
|
using System.Text;
|
|
|
|
namespace WebMvcEF.Extensions
|
|
{
|
|
public static class NpoiExtension
|
|
{
|
|
/// <summary>
|
|
/// 建出物件及名稱並傳回路徑
|
|
/// </summary>
|
|
/// <param name="templateName"></param>
|
|
/// <param name="report"></param>
|
|
/// <param name="timeout"></param>
|
|
/// <returns></returns>
|
|
public static string Report(string templateName, Action<ISheet> report)
|
|
{
|
|
string sNewTemplateName = null;
|
|
|
|
if (report != null)
|
|
{
|
|
string template = string.Format(CultureInfo.CurrentCulture, "App_Data\\{0}.xlsx", templateName);
|
|
|
|
string sPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, template);
|
|
IWorkbook workbook = LoadWorkbook(sPath);
|
|
sNewTemplateName = string.Format(CultureInfo.CurrentCulture, "temp\\uploads\\{0}{1}.xlsx", templateName, DateTime.Now.ToString("yyyyMMddhhmmss", CultureInfo.CurrentCulture));
|
|
report.Invoke(workbook.GetSheetAt(0));
|
|
|
|
using FileStream fs = new FileStream(AppDomain.CurrentDomain.BaseDirectory + sNewTemplateName, FileMode.Create, FileAccess.Write);
|
|
workbook.Write(fs);
|
|
}
|
|
return sNewTemplateName;
|
|
|
|
}
|
|
|
|
#region 產生Excel
|
|
/// <summary>
|
|
/// 根據型別產生試算表
|
|
/// </summary>
|
|
/// <param name="type"></param>
|
|
/// <returns></returns>
|
|
public static IWorkbook CreateWorkbook(string type)
|
|
{
|
|
if (type != null)
|
|
{
|
|
type = type.TrimStart('.').ToLower(CultureInfo.CurrentCulture);
|
|
}
|
|
|
|
return type switch
|
|
{
|
|
"xls" => new HSSFWorkbook(),
|
|
"xlsx" => new XSSFWorkbook(),
|
|
_ => throw new ArgumentNullException("Undefined type: " + type),
|
|
};
|
|
}
|
|
|
|
/// <summary>
|
|
/// 根據傳進來的路徑載入試算表
|
|
/// </summary>
|
|
/// <param name="path"></param>
|
|
/// <returns></returns>
|
|
public static IWorkbook LoadWorkbook(string path)
|
|
{
|
|
var type = Path.GetExtension(path);
|
|
using var fs = new FileStream(path, FileMode.Open);
|
|
if (type != null)
|
|
{
|
|
type = type.TrimStart('.').ToLower(CultureInfo.CurrentCulture);
|
|
}
|
|
|
|
return type switch
|
|
{
|
|
"xls" => new HSSFWorkbook(fs),
|
|
"xlsx" => new XSSFWorkbook(fs),
|
|
_ => throw new ArgumentNullException("Undefined type: " + type),
|
|
};
|
|
}
|
|
|
|
/// <summary>
|
|
/// 判斷型態是否為xlsx
|
|
/// </summary>
|
|
/// <param name="type"></param>
|
|
/// <returns></returns>
|
|
public static bool IsXlsx(string type)
|
|
{
|
|
return "xlsx".Equals(type, StringComparison.OrdinalIgnoreCase);
|
|
}
|
|
#endregion
|
|
|
|
/// <summary>
|
|
/// 取得目標儲存格
|
|
/// </summary>
|
|
/// <param name="sheet">工作表</param>
|
|
/// <param name="rowNumber">行號</param>
|
|
/// <param name="cellNumber">欄號</param>
|
|
/// <returns></returns>
|
|
public static ICell Cell(this ISheet sheet, int rowNumber, int cellNumber)
|
|
{
|
|
return sheet?.GetRow(rowNumber).GetCell(cellNumber);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 取得目標儲存格 (特別注意: 本方法行號等同顯示行號，例如第1行請輸入1而非0)
|
|
/// </summary>
|
|
/// <param name="sheet">工作表</param>
|
|
/// <param name="rowNumber">行號 (第1行請輸入0)</param>
|
|
/// <param name="column">用字元指定欄位 (第一欄請輸入'A')</param>
|
|
/// <returns></returns>
|
|
public static ICell Cell(this ISheet sheet, int rowNumber, char column)
|
|
{
|
|
var cellNumber = char.ToUpper(column, CultureInfo.CurrentCulture) - 'A';
|
|
return sheet?.GetRow(rowNumber - 1).GetCell(cellNumber);
|
|
}
|
|
|
|
#region 擴充儲存格輸入格式
|
|
/// <summary>
|
|
/// 設定儲存格數字 (decimal)
|
|
/// </summary>
|
|
/// <param name="cell">儲存格</param>
|
|
/// <param name="value">填入值</param>
|
|
public static void SetCellValue(this ICell cell, decimal value)
|
|
{
|
|
if (cell != null)
|
|
{
|
|
cell.SetCellValue(Convert.ToDouble(value));
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 設定儲存格數字 (decimal?)
|
|
/// 如果傳入null，不寫值進去
|
|
/// </summary>
|
|
/// <param name="cell">儲存格</param>
|
|
/// <param name="value">填入值</param>
|
|
public static void SetCellValue(this ICell cell, decimal? value)
|
|
{
|
|
if (cell != null && value.HasValue)
|
|
{
|
|
cell.SetCellValue(Convert.ToDouble(value, CultureInfo.CurrentCulture));
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 設定儲存格數字 (int?)
|
|
/// 如果傳入null，不寫值進去
|
|
/// </summary>
|
|
/// <param name="cell">儲存格</param>
|
|
/// <param name="value">填入值</param>
|
|
public static void SetCellValue(this ICell cell, int? value)
|
|
{
|
|
if (cell != null && value.HasValue)
|
|
{
|
|
cell.SetCellValue(Convert.ToDouble(value, CultureInfo.CurrentCulture));
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 設定儲存格數字 (double?)
|
|
/// 如果傳入null，不寫值進去
|
|
/// </summary>
|
|
/// <param name="cell">儲存格</param>
|
|
/// <param name="value">填入值</param>
|
|
public static void SetCellValue(this ICell cell, double? value)
|
|
{
|
|
if (cell != null && value.HasValue)
|
|
{
|
|
cell.SetCellValue(Convert.ToDouble(value, CultureInfo.CurrentCulture));
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 設定儲存格日期 (DateTime?)
|
|
/// 如果傳入null，不寫值進去
|
|
/// </summary>
|
|
/// <param name="cell">儲存格</param>
|
|
/// <param name="value">填入值</param>
|
|
public static void SetCellValue(this ICell cell, DateTime? value)
|
|
{
|
|
if (cell != null && value.HasValue)
|
|
{
|
|
cell.SetCellValue(value.Value);
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 設定文字，簡寫string.Format方法
|
|
/// </summary>
|
|
/// <param name="cell">儲存格</param>
|
|
/// <param name="format">文字格式</param>
|
|
/// <param name="args">參數</param>
|
|
public static void SetCellValue(this ICell cell, string format, params object[] args)
|
|
{
|
|
if (cell != null)
|
|
{
|
|
cell.SetCellValue(string.Format(CultureInfo.CurrentCulture, format, args));
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 將該格原始文字當作format參數，填入指定項目
|
|
/// </summary>
|
|
/// <param name="cell"></param>
|
|
/// <param name="args">format參數</param>
|
|
public static void SetFormatValue(this ICell cell, params object[] args)
|
|
{
|
|
if (cell != null)
|
|
{
|
|
cell.SetCellValue(string.Format(CultureInfo.CurrentCulture, cell.StringCellValue, args));
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
#region 產生Excel公式
|
|
/// <summary>
|
|
/// 取得Excel column的名稱
|
|
/// </summary>
|
|
/// <param name="columnNumber"></param>
|
|
/// <returns></returns>
|
|
private static string GetColumnName(int columnNumber)
|
|
{
|
|
var dividend = columnNumber;
|
|
var columnName = string.Empty;
|
|
int modulo;
|
|
while (dividend > 0)
|
|
{
|
|
modulo = (dividend - 1) % 26;
|
|
columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
|
|
dividend = (dividend - modulo) / 26;
|
|
}
|
|
return columnName;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 取得Excel欄位位置
|
|
/// </summary>
|
|
/// <param name="rowNumber"></param>
|
|
/// <param name="columnNumber"></param>
|
|
/// <returns></returns>
|
|
public static string GetColumnAddress(int rowNumber, int columnNumber)
|
|
{
|
|
return GetColumnName(columnNumber + 1) + (rowNumber + 1).ToString();
|
|
}
|
|
|
|
/// <summary>
|
|
/// 產生Sum公式，請用SetCellFormula填入
|
|
/// </summary>
|
|
/// <param name="startRow">起始列</param>
|
|
/// <param name="startColumn">起始儲存格</param>
|
|
/// <param name="endRow">結束列</param>
|
|
/// <param name="endColumn">結束儲存格</param>
|
|
/// <returns></returns>
|
|
public static string GenerateSumFormula(int startRow, int startColumn, int endRow, int endColumn)
|
|
{
|
|
return string.Format("SUM({0}:{1})", GetColumnAddress(startRow, startColumn), GetColumnAddress(endRow, endColumn));
|
|
}
|
|
|
|
/// <summary>
|
|
/// 產生計算公式，請用SetCellFormula填入
|
|
/// </summary>
|
|
/// <param name="formula"></param>
|
|
/// <param name="addresses"></param>
|
|
/// <returns></returns>
|
|
public static string GenerateCalcFormula(string formula, ExcelAddress[] addresses)
|
|
{
|
|
var list = new List<string>();
|
|
foreach (var address in addresses)
|
|
{
|
|
list.Add(GetColumnAddress(address.Row, address.Column));
|
|
}
|
|
return string.Format(formula, list.ToArray());
|
|
}
|
|
#endregion
|
|
|
|
/// <summary>
|
|
/// 插入資料欄(複製被插入的那欄Style)，可以插入一個範圍的欄，目前不包含合併儲存格
|
|
/// </summary>
|
|
/// <param name="sheet">工作表</param>
|
|
/// <param name="rowNumber">行號</param>
|
|
/// <param name="columnNumber">欄號</param>
|
|
/// <param name="rowRange">行範圍</param>
|
|
/// <param name="columnRange">欄範圍</param>
|
|
/// <param name="columnCount">次數，丟幾次產幾次</param>
|
|
/// <param name="targetColumnNumber">指定插入位置</param>
|
|
public static void InsertColumn(this ISheet sheet, int rowNumber, int columnNumber, int rowRange = 1, int columnRange = 1, int columnCount = 1, int targetColumnNumber = 0)
|
|
{
|
|
// 範圍和次數大於0才會繼續執行
|
|
if (rowRange > 0 && columnCount > 0 && columnRange > 0)
|
|
{
|
|
// 起始位置
|
|
var startNumber = targetColumnNumber > 0 ? targetColumnNumber : columnNumber + columnRange;
|
|
var addColumnCount = columnCount * columnRange;
|
|
|
|
// 紀錄已經修改過style的column
|
|
var columnList = new List<int>();
|
|
|
|
// 每個資料列去處理
|
|
for (var r = 0; r < rowRange; r++)
|
|
{
|
|
// 取得原始資料列
|
|
var sourceRow = sheet.GetRow(rowNumber + r);
|
|
|
|
// 將需要往後推的Cell向後推
|
|
for (var c = sourceRow.LastCellNum; c >= startNumber; c--)
|
|
{
|
|
// 目標Cell
|
|
var targetCellNum = c + addColumnCount;
|
|
|
|
// 如果Cell不等於空就移過去
|
|
var sourceCell = sourceRow.GetCell(c);
|
|
if (sourceCell != null)
|
|
{
|
|
// 確保目標的Cell可以使用
|
|
if (sourceRow.GetCell(targetCellNum) == null)
|
|
{
|
|
sourceRow.CreateCell(targetCellNum);
|
|
}
|
|
|
|
// 複製Cell
|
|
sourceRow.CopyCell(c, targetCellNum);
|
|
|
|
// 移除原本的Cell
|
|
sourceRow.RemoveCell(sourceCell);
|
|
|
|
// 複製Column的width
|
|
if (!columnList.Contains(targetCellNum))
|
|
{
|
|
columnList.Add(targetCellNum);
|
|
sheet.SetColumnWidth(targetCellNum, sheet.GetColumnWidth(c));
|
|
sheet.SetColumnWidth(c, sheet.DefaultColumnWidth);
|
|
}
|
|
}
|
|
}
|
|
|
|
// 從目標位置開始複製
|
|
for (var c = columnNumber; c < columnNumber + columnRange; c++)
|
|
{
|
|
// 如果Cell不等於空就移過去
|
|
var sourceCell = sourceRow.GetCell(c);
|
|
if (sourceCell != null)
|
|
{
|
|
for (var x = 1; x <= columnCount; x++)
|
|
{
|
|
// 目標Cell
|
|
var targetCellNum = c + (x * columnRange);
|
|
|
|
// 確保目標的Cell可以使用
|
|
if (sourceRow.GetCell(targetCellNum) == null)
|
|
{
|
|
sourceRow.CreateCell(targetCellNum);
|
|
}
|
|
|
|
// 複製Cell
|
|
sourceRow.CopyCell(c, targetCellNum);
|
|
|
|
// 複製Column的width
|
|
if (!columnList.Contains(targetCellNum))
|
|
{
|
|
columnList.Add(targetCellNum);
|
|
sheet.SetColumnWidth(targetCellNum, sheet.GetColumnWidth(c));
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 插入資料列(複製被插入的那列Style)，可以插入一個範圍的列，公式請自行輸入
|
|
/// </summary>
|
|
/// <param name="sheet">工作表</param>
|
|
/// <param name="rowNumber">行號</param>
|
|
/// <param name="range">範圍</param>
|
|
/// <param name="count">次數，丟幾次產幾次</param>
|
|
/// <param name="targetRowNumber">指定插入位置</param>
|
|
public static void InsertRow(this ISheet sheet, int rowNumber, int range = 1, int count = 1, int targetRowNumber = 0)
|
|
{
|
|
// 範圍和次數大於0才會繼續執行
|
|
if (range > 0 && count > 0)
|
|
{
|
|
// 從選取範圍後往下移動幾行
|
|
var addRowCount = range * count;
|
|
var startNumber = targetRowNumber > 0 ? targetRowNumber : rowNumber + range;
|
|
|
|
if (startNumber <= sheet.LastRowNum)
|
|
{
|
|
sheet.ShiftRows(startNumber, sheet.LastRowNum, addRowCount, true, false);
|
|
}
|
|
|
|
// 產生資料列
|
|
for (var r = 0; r < range; r++)
|
|
{
|
|
// 取得原始資料列
|
|
var sourceRow = sheet.GetRow(rowNumber + r);
|
|
|
|
for (var t = 0; t < count; t++)
|
|
{
|
|
// 產生新資料列
|
|
var newRow = sheet.CreateRow(startNumber + r + t * range);
|
|
|
|
// 沒有來源則跳過
|
|
if (sourceRow == null) continue;
|
|
|
|
// 產生新儲存格並填入資料
|
|
for (var c = 0; c < sourceRow.LastCellNum; c++)
|
|
{
|
|
var newCell = newRow.CreateCell(c);
|
|
var oldCell = sourceRow.Cells[c];
|
|
|
|
// 如果該儲存格為Null則跳過這格
|
|
if (oldCell == null) continue;
|
|
|
|
// 複製內容
|
|
switch (oldCell.CellType)
|
|
{
|
|
case CellType.Blank:
|
|
newCell.SetCellValue(oldCell.StringCellValue);
|
|
break;
|
|
case CellType.Boolean:
|
|
newCell.SetCellValue(oldCell.BooleanCellValue);
|
|
break;
|
|
case CellType.Error:
|
|
newCell.SetCellErrorValue(oldCell.ErrorCellValue);
|
|
break;
|
|
case CellType.Numeric:
|
|
newCell.SetCellValue(oldCell.NumericCellValue);
|
|
break;
|
|
case CellType.String:
|
|
newCell.SetCellValue(oldCell.RichStringCellValue);
|
|
break;
|
|
case CellType.Unknown:
|
|
newCell.SetCellValue(oldCell.StringCellValue);
|
|
break;
|
|
case CellType.Formula:
|
|
newCell.CellFormula = oldCell.CellFormula;
|
|
break;
|
|
default:
|
|
break;
|
|
}
|
|
|
|
// 複製Cell Style
|
|
newCell.CellStyle = oldCell.CellStyle;
|
|
}
|
|
|
|
// 複製Row Style
|
|
if (sourceRow.RowStyle != null)
|
|
{
|
|
newRow.RowStyle = sourceRow.RowStyle;
|
|
}
|
|
|
|
// 複製Row 高度
|
|
newRow.HeightInPoints = sourceRow.HeightInPoints;
|
|
}
|
|
}
|
|
|
|
#region 分頁符號處理
|
|
if (sheet.RowBreaks.Length > 0)
|
|
{
|
|
startNumber = targetRowNumber > 0 ? targetRowNumber : rowNumber + range;
|
|
var cloneBreaks = sheet.RowBreaks.Where(r => startNumber > r && r >= rowNumber);
|
|
var resetBreaks = sheet.RowBreaks.Where(r => r >= startNumber);
|
|
var newRowBreaks = new List<int>();
|
|
|
|
// 產生新的分頁符號
|
|
foreach (var cloneBreak in cloneBreaks)
|
|
{
|
|
for (var t = 1; t <= count; t++)
|
|
{
|
|
var newRowBreak = cloneBreak + (range * t);
|
|
newRowBreaks.Add(newRowBreak);
|
|
}
|
|
}
|
|
|
|
// 重新設定舊的分頁符號
|
|
foreach (var resetBreak in resetBreaks)
|
|
{
|
|
// 產生新的分頁符號
|
|
var newRowBreak = resetBreak + (range * count);
|
|
newRowBreaks.Add(newRowBreak);
|
|
|
|
// 移除舊的分頁符號
|
|
sheet.RemoveRowBreak(resetBreak);
|
|
}
|
|
|
|
// 不按照順序插入會無法正常顯示分頁符號
|
|
newRowBreaks = newRowBreaks.OrderBy(r => r).ToList();
|
|
|
|
// 將產生的分頁符號新增進資料表
|
|
foreach (var newRowBreak in newRowBreaks)
|
|
{
|
|
sheet.SetRowBreak(newRowBreak);
|
|
}
|
|
}
|
|
#endregion 分頁符號處理
|
|
|
|
#region 合併儲存格處理
|
|
if (sheet.NumMergedRegions > 0)
|
|
{
|
|
startNumber = targetRowNumber > 0 ? targetRowNumber : rowNumber + range;
|
|
for (var m = sheet.NumMergedRegions - 1; 0 <= m; m--)
|
|
{
|
|
var address = sheet.GetMergedRegion(m);
|
|
|
|
// 清除沒用處的合併儲存格資訊
|
|
if (address == null)
|
|
{
|
|
sheet.RemoveMergedRegion(m);
|
|
continue;
|
|
}
|
|
|
|
// 產生新增範圍內應有的合併儲存格
|
|
var rowRange = address.LastRow - address.FirstRow;
|
|
if (address.FirstRow >= rowNumber && rowNumber + range > address.FirstRow)
|
|
{
|
|
for (var t = 0; t < count; t++)
|
|
{
|
|
var startRow = startNumber + range * t + address.FirstRow - rowNumber;
|
|
var endRow = startRow + rowRange;
|
|
sheet.AddMergedRegion(new CellRangeAddress(startRow, endRow, address.FirstColumn, address.LastColumn));
|
|
}
|
|
}
|
|
}
|
|
}
|
|
#endregion 合併儲存格處理
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 清除資料列，可選範圍
|
|
/// </summary>
|
|
/// <param name="sheet">工作表</param>
|
|
/// <param name="rowNumber">行號</param>
|
|
/// <param name="range"></param>
|
|
/// <param name="shift">是否將向下所有資料往上移</param>
|
|
public static void ClearRow(this ISheet sheet, int rowNumber, int range = 1, bool shift = false)
|
|
{
|
|
if (sheet != null)
|
|
{
|
|
for (var i = 0; i < range; i++)
|
|
{
|
|
var removeRow = sheet.GetRow(rowNumber + i);
|
|
if (removeRow != null)
|
|
{
|
|
sheet.RemoveRow(removeRow);
|
|
}
|
|
}
|
|
|
|
// 如果資料要往上移則
|
|
if (shift)
|
|
{
|
|
#region 分頁符號處理
|
|
// 如果有分頁符號則重新設定分頁符號
|
|
if (sheet.RowBreaks.Length > 0)
|
|
{
|
|
var rowBreaks = sheet.RowBreaks;
|
|
var newRowBreaks = new List<int>();
|
|
|
|
// 儲存需要的分頁符號並刪除資料表中不需要的分頁符號
|
|
foreach (var rowBreak in rowBreaks)
|
|
{
|
|
if (rowBreak >= rowNumber + range)
|
|
{
|
|
var newRowBreak = rowBreak - range;
|
|
newRowBreaks.Add(newRowBreak);
|
|
}
|
|
|
|
if (rowBreak > rowNumber)
|
|
{
|
|
sheet.RemoveRowBreak(rowBreak);
|
|
}
|
|
}
|
|
|
|
// 不按照順序插入會無法正常顯示分頁符號
|
|
newRowBreaks = newRowBreaks.OrderBy(r => r).ToList();
|
|
|
|
// 將需要的分頁符號新增進資料表
|
|
foreach (var newRowBreak in newRowBreaks)
|
|
{
|
|
sheet.SetRowBreak(newRowBreak);
|
|
}
|
|
}
|
|
#endregion 分頁符號處理
|
|
|
|
#region 合併儲存格處理
|
|
// 因為shiftRow會將合併儲存格往上往下移但不會判斷是否要移除多的合併儲存格，故自行重新設定合併儲存格
|
|
var newMergedRegions = new List<CellRangeAddress>();
|
|
var rangeLastRow = rowNumber + range - 1;
|
|
for (var i = sheet.NumMergedRegions - 1; 0 <= i; i--)
|
|
{
|
|
var address = sheet.GetMergedRegion(i);
|
|
|
|
// 清空因shiftRows而產出的垃圾
|
|
if (address == null)
|
|
{
|
|
sheet.RemoveMergedRegion(i);
|
|
continue;
|
|
}
|
|
|
|
// 若該合併儲存格的row在應被刪除的row底下則重新設定該合併儲存格
|
|
if (rowNumber <= address.LastRow)
|
|
{
|
|
CellRangeAddress newAddress = null;
|
|
|
|
// 根據情形重新設定合併儲存格（拆開比較容易理解）
|
|
if (rowNumber < address.FirstRow && rangeLastRow < address.FirstRow)
|
|
{
|
|
// 若該刪除的row沒有覆蓋到合併儲存格的row時，則單純將該合併儲存格往上拉回
|
|
newAddress = new CellRangeAddress(address.FirstRow - range, address.LastRow - range, address.FirstColumn, address.LastColumn);
|
|
}
|
|
else
|
|
{
|
|
// 算應該刪除的row數量
|
|
var removeStartRow = rowNumber < address.FirstRow ? address.FirstRow : rowNumber;
|
|
var removeEndRow = rangeLastRow > address.LastRow ? address.LastRow : rangeLastRow;
|
|
var removeRowCount = removeEndRow - removeStartRow + 1;
|
|
|
|
// 若該合併儲存格的row總數比應該刪除的row數量還多才進行新增
|
|
var totalRow = address.LastRow - address.FirstRow + 1;
|
|
if (totalRow > removeRowCount)
|
|
{
|
|
var addressFirstRow = address.FirstRow;
|
|
var addressLastRow = address.LastRow - removeRowCount;
|
|
|
|
// 若起始row比合併儲存格的row還小的時候則往上拉回
|
|
if (rowNumber < address.FirstRow)
|
|
{
|
|
var shiftRowCount = address.FirstRow - rowNumber;
|
|
addressFirstRow -= shiftRowCount;
|
|
addressLastRow -= shiftRowCount;
|
|
}
|
|
newAddress = new CellRangeAddress(addressFirstRow, addressLastRow, address.FirstColumn, address.LastColumn);
|
|
}
|
|
}
|
|
|
|
// 刪除原本的合併儲存格
|
|
sheet.RemoveMergedRegion(i);
|
|
|
|
// 新增新的合併儲存格
|
|
if (newAddress != null)
|
|
{
|
|
newMergedRegions.Add(newAddress);
|
|
}
|
|
}
|
|
}
|
|
#endregion 合併儲存格處理
|
|
|
|
// 將向下所有資料往上移
|
|
if (rowNumber < sheet.LastRowNum)
|
|
{
|
|
sheet.ShiftRows(rowNumber + range, sheet.LastRowNum, -range, true, false);
|
|
}
|
|
|
|
// 因為Shift往上移時候位置會偏移所以在這邊才將合併儲存格新增回去
|
|
foreach (var newMergedRegion in newMergedRegions)
|
|
{
|
|
sheet.AddMergedRegion(newMergedRegion);
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 重新設定列高功能
|
|
/// </summary>
|
|
/// <param name="sheet">工作表</param>
|
|
/// <param name="rowNumber">行號</param>
|
|
public static void AutoHeight(this ISheet sheet, int rowNumber)
|
|
{
|
|
short height = -1;
|
|
|
|
if (sheet != null)
|
|
{
|
|
var row = sheet.GetRow(rowNumber);
|
|
var lastCellNum = row.LastCellNum;
|
|
|
|
for (var i = 0; i < lastCellNum; i++)
|
|
{
|
|
var columnWidth = sheet.GetColumnWidth(i); // 儲存格可以容納的最大長度
|
|
|
|
// 如果有被合併的儲存格則會重新設定最大長度
|
|
for (var j = 0; j < sheet.NumMergedRegions; j++)
|
|
{
|
|
var address = sheet.GetMergedRegion(j);
|
|
|
|
// 過濾空值
|
|
if (address == null)
|
|
{
|
|
continue;
|
|
}
|
|
|
|
if (address.FirstRow == rowNumber)
|
|
{
|
|
for (var k = i + 1; k <= address.LastColumn; k++)
|
|
{
|
|
columnWidth += sheet.GetColumnWidth(k);
|
|
}
|
|
}
|
|
}
|
|
// 計算新的高度，目前缺少LineBreak的判斷
|
|
var texts = row.GetCell(i).ToString().Split(new string[] { "\r\n" }, StringSplitOptions.None);
|
|
var fontHeight = row.Cells[i].CellStyle.GetFont(sheet.Workbook).FontHeight;
|
|
var byteCount = 0;
|
|
foreach (var text in texts)
|
|
{
|
|
byteCount += Encoding.Default.GetByteCount(text);
|
|
}
|
|
var textWidth = byteCount * fontHeight / 2;
|
|
var scalingRatio = textWidth > columnWidth ? textWidth / columnWidth * fontHeight : fontHeight;
|
|
var newHeight = row.Height - fontHeight + ((texts.Length - 1) * fontHeight) + scalingRatio * 3;
|
|
if (newHeight > height)
|
|
{
|
|
height = (short)newHeight;
|
|
}
|
|
}
|
|
row.Height = height;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 平均設定列高功能
|
|
/// </summary>
|
|
/// <param name="sheet">工作表</param>
|
|
/// <param name="rowNumber">行號</param>
|
|
/// <param name="range">範圍</param>
|
|
public static void AverageHeight(this ISheet sheet, int rowNumber, int range)
|
|
{
|
|
var totalHeight = 0f;
|
|
if (sheet != null)
|
|
{
|
|
// 第一次迴圈先計算總高度
|
|
for (var i = 0; i < range; i++)
|
|
{
|
|
totalHeight += sheet.GetRow(rowNumber + i).HeightInPoints;
|
|
}
|
|
// 第二次迴圈將回傳平均高度回去
|
|
var averageHeight = totalHeight / range;
|
|
for (var i = 0; i < range; i++)
|
|
{
|
|
sheet.GetRow(rowNumber + i).HeightInPoints = averageHeight;
|
|
}
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 將資料填到Excel資料表
|
|
/// </summary>
|
|
/// <typeparam name="T"></typeparam>
|
|
/// <param name="sheet"></param>
|
|
/// <param name="data">要塞入的資料</param>
|
|
/// <param name="sourceRowNum">要當作範本的行數(從0開始算)</param>
|
|
/// <param name="rowHeight">行高設定</param>
|
|
/// <param name="action"></param>
|
|
public static void Fill<T>(this ISheet sheet, IEnumerable<T> data, int sourceRowNum, RowHeight rowHeight, Action<IRow, T> action)
|
|
{
|
|
if (sheet != null && data != null)
|
|
{
|
|
var sourceRow = sheet.GetRow(sourceRowNum);
|
|
var cellStyles = sourceRow.Cells.Select(x => x.CellStyle).ToArray();
|
|
var cellNum = sourceRow.LastCellNum;
|
|
var endRowNum = sourceRowNum + data.Count();
|
|
var sourceRowHeight = sourceRow.HeightInPoints;
|
|
var dataCount = data.Count();
|
|
|
|
var rowNum = sourceRowNum;
|
|
if (sourceRowNum < sheet.LastRowNum && dataCount >= 2)
|
|
{
|
|
sheet.ShiftRows(sourceRowNum + 1, sheet.LastRowNum, dataCount - 1); // 把範本後的內容往下推，避免被蓋過
|
|
}
|
|
|
|
foreach (var item in data)
|
|
{
|
|
IRow row = null;
|
|
if (rowNum < endRowNum)
|
|
{
|
|
row = sheet.CreateRow(rowNum);
|
|
for (var c = 0; c < cellNum; c++)
|
|
{
|
|
var cell = row.CreateCell(c);
|
|
cell.CellStyle = cellStyles[c];
|
|
}
|
|
}
|
|
else
|
|
{
|
|
row = sheet.GetRow(rowNum);
|
|
}
|
|
action.Invoke(row, item);
|
|
if (rowHeight == RowHeight.Auto)
|
|
{
|
|
sheet.AutoHeight(rowNum);
|
|
}
|
|
else if (rowHeight == RowHeight.Fixed)
|
|
{
|
|
row.HeightInPoints = sourceRowHeight;
|
|
}
|
|
rowNum++;
|
|
}
|
|
}
|
|
}
|
|
|
|
public static void AutoWidth(this ISheet sheet)
|
|
{
|
|
if (sheet != null)
|
|
{
|
|
for (var cell = 0; cell < sheet.GetRow(0).LastCellNum; cell++)
|
|
{
|
|
sheet.AutoSizeColumn(cell);
|
|
}
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 設定自訂屬性
|
|
/// </summary>
|
|
/// <param name="workbook"></param>
|
|
/// <param name="name"></param>
|
|
/// <param name="value"></param>
|
|
public static void SetCustomProperty(this IWorkbook workbook, string name, bool value)
|
|
{
|
|
if (workbook is HSSFWorkbook)
|
|
{
|
|
(workbook as HSSFWorkbook).DocumentSummaryInformation.CustomProperties.Put(name, value);
|
|
}
|
|
else if (workbook is XSSFWorkbook)
|
|
{
|
|
(workbook as XSSFWorkbook).GetProperties().CustomProperties.AddProperty(name, value);
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 取得自訂屬性
|
|
/// </summary>
|
|
/// <param name="workbook"></param>
|
|
/// <param name="name"></param>
|
|
/// <returns></returns>
|
|
public static bool GetCustomProperty(this IWorkbook workbook, string name)
|
|
{
|
|
if (workbook is HSSFWorkbook)
|
|
{
|
|
var props = (workbook as HSSFWorkbook).DocumentSummaryInformation.CustomProperties;
|
|
if (props.ContainsKey(name))
|
|
{
|
|
return (bool)props[name];
|
|
}
|
|
}
|
|
else if (workbook is XSSFWorkbook)
|
|
{
|
|
var props = (workbook as XSSFWorkbook).GetProperties().CustomProperties;
|
|
if (props.Contains(name))
|
|
{
|
|
return (bool)props.GetProperty(name).Item;
|
|
}
|
|
}
|
|
return false;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 每行高度要如何調整 (LibreOffice無法自動換行，但Excel可)
|
|
/// </summary>
|
|
public enum RowHeight
|
|
{
|
|
/// <summary>
|
|
/// 不處理
|
|
/// (文字過長時轉成PDF有可能超出格子)
|
|
/// </summary>
|
|
None,
|
|
/// <summary>
|
|
/// 使用自製方法判斷高度
|
|
/// (有誤差)
|
|
/// </summary>
|
|
Auto,
|
|
/// <summary>
|
|
/// 使用範本高度固定設定每一行
|
|
/// (文字過長時轉成PDF有可能超出格子，可設高點)
|
|
/// </summary>
|
|
Fixed
|
|
}
|
|
|
|
/// <summary>
|
|
/// Excel位置
|
|
/// </summary>
|
|
public class ExcelAddress
|
|
{
|
|
public ExcelAddress(int row, int column)
|
|
{
|
|
Row = row;
|
|
Column = column;
|
|
}
|
|
public int Row { get; set; }
|
|
public int Column { get; set; }
|
|
}
|
|
}
|