使用 NPOI/DocX 二次封裝Office（Word、Excel）幫助類
簡介
工作中需要大量生成匯出報表或合同證照檔案，原理為使用Excel或Word模板，批量替換標籤以達到效果。
設計
由於原類庫都屬於基礎方法，二次封裝後具有更簡易的使用方式，可直接傳入生成的資料集或標籤替換集合。
引用庫介紹
由於微軟預設推薦的類庫 Microsoft.Office.Interop.Word 與 Microsoft.Office.Interop.Excel 需要電腦安裝 Microsoft Office 並引用COM元件才可以使用（已知呼叫印表機需引用COM元件），所以選用類庫可獨立於Office元件，在任意一臺電腦也可以執行。
NPOI：POI Java專案的.NET版本。可以非常輕鬆地讀/寫Office 2003/2007檔案。
DocX：DocX是一個.NET庫，允許開發人員以簡單直觀的方式操作Word檔案。
Excel檔案操作
ExcelHelper提供建立檔案（2003/2007）及Sheet分頁建立編輯，讀取Excel檔案至記憶體DataSet及反向DataSet儲存至Excel檔案。僅顯示最外層引用方法，詳細呼叫請在幫助類種檢視！
/// <summary>
/// Excel所有分頁轉換為DataSet
/// </summary>
/// <param name="strDataSourcePath">Excel檔案路徑</param>
/// <returns>成功返回Excel的DataSet,失敗返回NULL</returns>
public static DataSet ExcelConversionDataSet(string strDataSourcePath)
{
try
{
if (string.IsNullOrEmpty(strDataSourcePath) || !File.Exists(strDataSourcePath))
{
return null;
}
DataSet dsTargetData = new DataSet();
Dictionary<int, string> dicAllSheet = GetExcelAllSheet(strDataSourcePath);
foreach (var vAllSheet in dicAllSheet)
{
DataTable dtTargetData = new DataTable();
dtTargetData.TableName = vAllSheet.Value;
dtTargetData = ExcelConversionDataTable(strDataSourcePath, vAllSheet.Value);
if (dtTargetData == null)
{
continue;
}
dsTargetData.Tables.Add(dtTargetData);
}
return dsTargetData;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return null;
}
}
/// <summary>
/// DataSet轉換為Excel
/// 存在檔案則新建DataTableName的分頁(如果分頁名衝突則或為空則使用預設名稱)
/// 不存在檔案則新建(Excel,名稱為DataTableName,如果沒有則使用預設名稱)
/// </summary>
/// <param name="strDataSourcePath">Excel檔案路徑</param>
/// <param name="dsSourceData">DataTable資料</param>
/// <returns>成功返回true,失敗返回false</returns>
public static bool DataSetConversionExcel(string strDataSourcePath, DataSet dsSourceData)
{
try
{
if (string.IsNullOrEmpty(strDataSourcePath) || dsSourceData.Tables.Count < 1)
{
return false;
}
foreach (DataTable dtSourceData in dsSourceData.Tables)
{
Dictionary<int, string> dicAllSheet = GetExcelAllSheet(strDataSourcePath);
string strTableName = string.IsNullOrEmpty(dtSourceData.TableName) ? string.Format("Sheet{0}", dicAllSheet.Count + 1) : dtSourceData.TableName;
if (dicAllSheet.ContainsValue(dtSourceData.TableName))
{
RemoveExcelSheet(strDataSourcePath, dtSourceData.TableName);
}
if (!FillDataTable(strDataSourcePath, strTableName, dtSourceData, true, 0, 0))
{
return false;
}
}
return true;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return false;
}
}
根據公司專案需要，把多個Excel的Sheet頁的內容及樣式合併為一個檔案，Microsoft.Office.Interop.Excel提供拷貝分頁方法，但是需要安裝Microsoft Office，所以用NPOI類庫實現了一個拷貝方法。
/// <summary>
/// 拷貝Sheet頁到另一個Sheet頁
/// </summary>
/// <param name="strSourceExcelPath">源Excel路徑</param>
/// <param name="strFromSheetName">源Excel拷貝Sheet</param>
/// <param name="strTargetExcelPath">目標Excel路徑</param>
/// <param name="strToSheetName">目標Excel拷貝Sheet</param>
/// <returns>成功返回true,失敗返回false</returns>
public static bool CopySheet(string strSourceExcelPath, string strFromSheetName, string strTargetExcelPath, string strToSheetName)
{
try
{
if (string.IsNullOrEmpty(strSourceExcelPath) || string.IsNullOrEmpty(strTargetExcelPath) || !File.Exists(strSourceExcelPath))
{
TXTHelper.Logs(string.Format("源資料和目標資料引數為空或檔案不存在!"));
return false;
}
if (string.IsNullOrEmpty(strFromSheetName) || string.IsNullOrEmpty(strToSheetName))
{
TXTHelper.Logs(string.Format("源Sheet頁和目標Sheet頁引數為空!"));
return false;
}
//獲得源資料和目標資料的Sheet頁
IWorkbook iSourceWorkbook = null;
ISheet iSourceSheet = GetExcelSheetAt(strSourceExcelPath, strFromSheetName, out iSourceWorkbook);
IWorkbook iTargetWorkbook = null;
ISheet iTargetSheet = null;
if (iSourceSheet == null)
{
TXTHelper.Logs(string.Format("指定源資料Sheet頁為空!"));
return false;
}
if (!File.Exists(strTargetExcelPath))
{
//如果檔案不存在則建立Excel
if (System.IO.Path.GetExtension(strTargetExcelPath) == ".xls")
{
bool bCreare = CreateExcel_Office2003(strTargetExcelPath, strToSheetName);
}
else if (System.IO.Path.GetExtension(strTargetExcelPath) == ".xlsx")
{
bool bCreare = CreateExcel_Office2007(strTargetExcelPath, strToSheetName);
}
else
{
TXTHelper.Logs(string.Format("指定目標Excel檔案路徑格式錯誤!"));
return false;
}
iTargetSheet = GetExcelSheetAt(strTargetExcelPath, strToSheetName, out iTargetWorkbook);
}
else
{
//如果檔案存在則判斷是否存在執行Sheet
Dictionary<int, string> dicAllSheet = GetExcelAllSheet(strTargetExcelPath);
if (dicAllSheet.ContainsValue(strToSheetName))
{
iTargetSheet = GetExcelSheetAt(strTargetExcelPath, strToSheetName, out iTargetWorkbook);
}
else
{
iTargetSheet = CreateExcelSheetAt(strTargetExcelPath, strToSheetName, out iTargetWorkbook);
}
}
//呼叫Sheet拷貝Sheet方法
bool bCopySheet = CopySheetAt(iSourceWorkbook, iSourceSheet, iTargetWorkbook, iTargetSheet);
if (bCopySheet)
{
if (System.IO.Path.GetExtension(strTargetExcelPath) == ".xls")
{
FileStream fileStream2003 = new FileStream(Path.ChangeExtension(strTargetExcelPath, "xls"), FileMode.Create);
iTargetWorkbook.Write(fileStream2003);
fileStream2003.Close();
iTargetWorkbook.Close();
}
else if (System.IO.Path.GetExtension(strTargetExcelPath) == ".xlsx")
{
FileStream fileStream2007 = new FileStream(Path.ChangeExtension(strTargetExcelPath, "xlsx"), FileMode.Create);
iTargetWorkbook.Write(fileStream2007);
fileStream2007.Close();
iTargetWorkbook.Close();
}
return true;
}
else
{
TXTHelper.Logs(string.Format("拷貝失敗!"));
return false;
}
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return false;
}
}
// <summary>
/// 拷貝Sheet頁到另一個Sheet頁(淺拷貝,不提供儲存方法)
/// Office2003單Sheet頁僅支援4000個樣式
/// </summary>
/// <param name="iSourceWorkbook">源Excel工作簿</param>
/// <param name="iFromSheet">源Sheet頁</param>
/// <param name="iTargetWorkbook">目標Excel工作簿</param>
/// <param name="iToSheet">目標Sheet頁</param>
/// <returns>成功返回true,失敗返回false</returns>
public static bool CopySheetAt(IWorkbook iSourceWorkbook, ISheet iFromSheet, IWorkbook iTargetWorkbook, ISheet iToSheet)
{
try
{
//拷貝資料
DataTable dtExcelFromData = GetDataTable(iFromSheet, false, 0, 0, 0, 0);
iToSheet = FillDataTable(iToSheet, dtExcelFromData, false, 0, 0);
//拷貝單元格合併
for (int iMergedRegions = 0; iMergedRegions < iFromSheet.NumMergedRegions; iMergedRegions++)
{
iToSheet.AddMergedRegion(iFromSheet.GetMergedRegion(iMergedRegions));
}
//拷貝樣式(遍歷Sheet頁行)
List<ICellStyle> listCellStyle = new List<ICellStyle>();
for (int iRowNum = 0; iRowNum <= iFromSheet.LastRowNum; iRowNum++)
{
IRow iFromRowData = iFromSheet.GetRow(iRowNum);
IRow iToRowData = iToSheet.GetRow(iRowNum);
if (iFromRowData == null || iToRowData == null)
{
continue;
}
//設定行高
short sFromHeight = iFromRowData.Height;
iToRowData.Height = sFromHeight;
//遍歷Sheet頁列
for (int iRowCell = 0; iRowCell <= iFromRowData.LastCellNum; iRowCell++)
{
//設定列寬
int iFromColumnWidth = iFromSheet.GetColumnWidth(iRowNum) / 256;
iToSheet.SetColumnWidth(iRowNum, iFromColumnWidth * 256);
//複製資料
ICell iFromCell = iFromRowData.GetCell(iRowCell);
if (iFromCell != null)
{
//獲得源Sheet頁的樣式
ICellStyle iFromCellStyle = iFromCell.CellStyle;
//獲得目標Excel指定Cell
ICell iToCell = iToRowData.GetCell(iRowCell);
if (iToCell == null) continue;
#region 複製單元格樣式
//指定Cell創新目標Excel工作簿新樣式
ICellStyle iToNewCellStyle = null;
foreach (ICellStyle vCellStyle in listCellStyle)
{
IFont iVToFont = vCellStyle.GetFont(iTargetWorkbook);
IFont iFromFont = iFromCellStyle.GetFont(iSourceWorkbook);
if (vCellStyle.Alignment == iFromCellStyle.Alignment &&
vCellStyle.BorderBottom == iFromCellStyle.BorderBottom &&
vCellStyle.BorderLeft == iFromCellStyle.BorderLeft &&
vCellStyle.BorderRight == iFromCellStyle.BorderRight &&
vCellStyle.BorderTop == iFromCellStyle.BorderTop &&
vCellStyle.BottomBorderColor == iFromCellStyle.BottomBorderColor &&
vCellStyle.DataFormat == iFromCellStyle.DataFormat &&
vCellStyle.FillBackgroundColor == iFromCellStyle.FillBackgroundColor &&
vCellStyle.FillForegroundColor == iFromCellStyle.FillForegroundColor &&
vCellStyle.FillPattern == iFromCellStyle.FillPattern &&
vCellStyle.Indention == iFromCellStyle.Indention &&
vCellStyle.IsHidden == iFromCellStyle.IsHidden &&
vCellStyle.IsLocked == iFromCellStyle.IsLocked &&
vCellStyle.LeftBorderColor == iFromCellStyle.LeftBorderColor &&
vCellStyle.RightBorderColor == iFromCellStyle.RightBorderColor &&
vCellStyle.Rotation == iFromCellStyle.Rotation &&
vCellStyle.TopBorderColor == iFromCellStyle.TopBorderColor &&
vCellStyle.VerticalAlignment == iFromCellStyle.VerticalAlignment &&
vCellStyle.WrapText == iFromCellStyle.WrapText &&
//字型比對
iVToFont.Color == iFromFont.Color &&
iVToFont.FontHeightInPoints == iFromFont.FontHeightInPoints &&
iVToFont.FontName == iFromFont.FontName &&
iVToFont.IsBold == iFromFont.IsBold &&
iVToFont.IsItalic == iFromFont.IsItalic &&
iVToFont.IsStrikeout == iFromFont.IsStrikeout &&
iVToFont.Underline == iFromFont.Underline)
{
iToNewCellStyle = vCellStyle;
break;
}
}
if (iToNewCellStyle == null)
{
//建立新樣式
iToNewCellStyle = iTargetWorkbook.CreateCellStyle();
//複製樣式
iToNewCellStyle.Alignment = iFromCellStyle.Alignment;//對齊
iToNewCellStyle.BorderBottom = iFromCellStyle.BorderBottom;//下邊框
iToNewCellStyle.BorderLeft = iFromCellStyle.BorderLeft;//左邊框
iToNewCellStyle.BorderRight = iFromCellStyle.BorderRight;//右邊框
iToNewCellStyle.BorderTop = iFromCellStyle.BorderTop;//上邊框
iToNewCellStyle.BottomBorderColor = iFromCellStyle.BottomBorderColor;//下邊框顏色
iToNewCellStyle.DataFormat = iFromCellStyle.DataFormat;//資料格式
iToNewCellStyle.FillBackgroundColor = iFromCellStyle.FillBackgroundColor;//填充背景色
iToNewCellStyle.FillForegroundColor = iFromCellStyle.FillForegroundColor;//填充前景色
iToNewCellStyle.FillPattern = iFromCellStyle.FillPattern;//填充圖案
iToNewCellStyle.Indention = iFromCellStyle.Indention;//壓痕
iToNewCellStyle.IsHidden = iFromCellStyle.IsHidden;//隱藏
iToNewCellStyle.IsLocked = iFromCellStyle.IsLocked;//鎖定
iToNewCellStyle.LeftBorderColor = iFromCellStyle.LeftBorderColor;//左邊框顏色
iToNewCellStyle.RightBorderColor = iFromCellStyle.RightBorderColor;//右邊框顏色
iToNewCellStyle.Rotation = iFromCellStyle.Rotation;//旋轉
iToNewCellStyle.TopBorderColor = iFromCellStyle.TopBorderColor;//上邊框顏色
iToNewCellStyle.VerticalAlignment = iFromCellStyle.VerticalAlignment;//垂直對齊
iToNewCellStyle.WrapText = iFromCellStyle.WrapText;//文字換行
//複製字型
IFont iFromFont = iFromCellStyle.GetFont(iSourceWorkbook);
IFont iToFont = iTargetWorkbook.CreateFont();
iToFont.Color = iFromFont.Color;//顏色
iToFont.FontHeightInPoints = iFromFont.FontHeightInPoints;//字號
iToFont.FontName = iFromFont.FontName;//字型
iToFont.IsBold = iFromFont.IsBold;//加粗
iToFont.IsItalic = iFromFont.IsItalic;//斜體
iToFont.IsStrikeout = iFromFont.IsStrikeout;//刪除線
iToFont.Underline = iFromFont.Underline;//下劃線
iToNewCellStyle.SetFont(iToFont);
//儲存到快取集合中
listCellStyle.Add(iToNewCellStyle);
}
//複製樣式到指定表格中
iToCell.CellStyle = iToNewCellStyle;
#endregion
}
}
}
return true;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return false;
}
}
完整版
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Data;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using NPOI.Helper.TXT;
using NPOI.SS.Util;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
namespace NPOI.Helper.Excel
{
/// <summary>
/// Excel幫助類
/// 建立日期:2017年5月27日
/// </summary>
public class ExcelHelper
{
/// <summary>
/// 建立Excel(Office2003)
/// </summary>
/// <param name="strDataSourcePath">新建Excel的路徑.xls</param>
/// <param name="strSheetName">Sheet名稱,如果為空則建立三個預設Sheet頁</param>
/// <returns>成功返回true,失敗返回false</returns>
public static bool CreateExcel_Office2003(string strDataSourcePath, string strSheetName)
{
try
{
if (string.IsNullOrEmpty(strDataSourcePath))
{
return false;
}
HSSFWorkbook WorkBook2003 = new HSSFWorkbook();
if (string.IsNullOrEmpty(strSheetName))
{
WorkBook2003.CreateSheet("Sheet1");
WorkBook2003.CreateSheet("Sheet2");
WorkBook2003.CreateSheet("Sheet3");
}
else
{
WorkBook2003.CreateSheet(strSheetName);
}
FileStream fileStream2003 = new FileStream(Path.ChangeExtension(strDataSourcePath, "xls"), FileMode.Create);
WorkBook2003.Write(fileStream2003);
fileStream2003.Close();
WorkBook2003.Close();
return true;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return false;
}
}
/// <summary>
/// 建立Excel(Office2007)
/// </summary>
/// <param name="strDataSourcePath">新建Excel的路徑.xlsx</param>
/// <param name="strSheetName">Sheet名稱,如果為空則建立三個預設Sheet頁</param>
/// <returns>成功返回true,失敗返回false</returns>
public static bool CreateExcel_Office2007(string strDataSourcePath, string strSheetName)
{
try
{
if (string.IsNullOrEmpty(strDataSourcePath))
{
return false;
}
XSSFWorkbook WorkBook2007 = new XSSFWorkbook();
if (string.IsNullOrEmpty(strSheetName))
{
WorkBook2007.CreateSheet("Sheet1");
WorkBook2007.CreateSheet("Sheet2");
WorkBook2007.CreateSheet("Sheet3");
}
else
{
WorkBook2007.CreateSheet(strSheetName);
}
FileStream fileStream2007 = new FileStream(Path.ChangeExtension(strDataSourcePath, "xlsx"), FileMode.Create);
WorkBook2007.Write(fileStream2007);
fileStream2007.Close();
WorkBook2007.Close();
return true;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return false;
}
}
/// <summary>
/// 在指定Excel中新增分頁
/// </summary>
/// <param name="strDataSourcePath">Excel檔案路徑</param>
/// <param name="strSheetName">需要新增的Sheet名稱</param>
/// <returns>成功返回true,失敗返回false</returns>
public static bool CreateExcelSheet(string strDataSourcePath, string strSheetName)
{
try
{
if (string.IsNullOrEmpty(strDataSourcePath) || string.IsNullOrEmpty(strSheetName) || !File.Exists(strDataSourcePath))
{
return false;
}
IWorkbook iWorkBook = null;
FileStream fileStream = new FileStream(strDataSourcePath, FileMode.Open, FileAccess.Read);
if (System.IO.Path.GetExtension(strDataSourcePath) == ".xls")
{
iWorkBook = new HSSFWorkbook(fileStream);
iWorkBook.CreateSheet(strSheetName);
FileStream fileStream2003 = new FileStream(Path.ChangeExtension(strDataSourcePath, "xls"), FileMode.Create);
iWorkBook.Write(fileStream2003);
fileStream2003.Close();
iWorkBook.Close();
}
else if (System.IO.Path.GetExtension(strDataSourcePath) == ".xlsx")
{
iWorkBook = new XSSFWorkbook(fileStream);
iWorkBook.CreateSheet(strSheetName);
FileStream fileStream2007 = new FileStream(Path.ChangeExtension(strDataSourcePath, "xlsx"), FileMode.Create);
iWorkBook.Write(fileStream2007);
fileStream2007.Close();
iWorkBook.Close();
}
else
{
return false;
}
return true;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return false;
}
}
/// <summary>
/// 在指定Excel中新增分頁
/// </summary>
/// <param name="strDataSourcePath">Excel檔案路徑</param>
/// <param name="strSheetName">需要新增的Sheet名稱</param>
/// <returns>成功返回Excel工作表,失敗返回null</returns>
public static ISheet CreateExcelSheetAt(string strDataSourcePath, string strSheetName, out IWorkbook iWorkBook)
{
try
{
iWorkBook = null;
if (string.IsNullOrEmpty(strDataSourcePath) || string.IsNullOrEmpty(strSheetName) || !File.Exists(strDataSourcePath))
{
return null;
}
ISheet iSheet = null;
FileStream fileStream = new FileStream(strDataSourcePath, FileMode.Open, FileAccess.Read);
if (System.IO.Path.GetExtension(strDataSourcePath) == ".xls")
{
iWorkBook = new HSSFWorkbook(fileStream);
iSheet = iWorkBook.CreateSheet(strSheetName);
}
else if (System.IO.Path.GetExtension(strDataSourcePath) == ".xlsx")
{
iWorkBook = new XSSFWorkbook(fileStream);
iSheet = iWorkBook.CreateSheet(strSheetName);
}
else
{
return null;
}
return iSheet;
}
catch (Exception ex)
{
iWorkBook = null;
TXTHelper.Logs(ex.ToString());
return null;
}
}
/// <summary>
/// 在指定Excel中刪除分頁(至少有一個Sheet分頁檔案才能開啟)
/// </summary>
/// <param name="strDataSourcePath">Excel檔案路徑</param>
/// <param name="strSheetName">需要刪除的Sheet名稱</param>
/// <returns>成功返回true,失敗返回false</returns>
public static bool RemoveExcelSheet(string strDataSourcePath, string strSheetName)
{
try
{
if (string.IsNullOrEmpty(strDataSourcePath) || string.IsNullOrEmpty(strSheetName) || !File.Exists(strDataSourcePath))
{
return false;
}
IWorkbook iWorkBook = null;
FileStream fileStream = new FileStream(strDataSourcePath, FileMode.Open, FileAccess.Read);
if (System.IO.Path.GetExtension(strDataSourcePath) == ".xls")
{
iWorkBook = new HSSFWorkbook(fileStream);
iWorkBook.RemoveSheetAt(iWorkBook.GetSheetIndex(strSheetName));
FileStream fileStream2003 = new FileStream(Path.ChangeExtension(strDataSourcePath, "xls"), FileMode.Create);
iWorkBook.Write(fileStream2003);
fileStream2003.Close();
iWorkBook.Close();
}
else if (System.IO.Path.GetExtension(strDataSourcePath) == ".xlsx")
{
iWorkBook = new XSSFWorkbook(fileStream);
iWorkBook.RemoveSheetAt(iWorkBook.GetSheetIndex(strSheetName));
FileStream fileStream2007 = new FileStream(Path.ChangeExtension(strDataSourcePath, "xlsx"), FileMode.Create);
iWorkBook.Write(fileStream2007);
fileStream2007.Close();
iWorkBook.Close();
}
else
{
return false;
}
return true;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return false;
}
}
/// <summary>
/// 獲得指定Excel中所有Sheet
/// </summary>
/// <param name="strDataSourcePath">Excel檔案路徑</param>
/// <returns>Excel中所有Sheet字典(序號,Sheet名)</returns>
public static Dictionary<int, string> GetExcelAllSheet(string strDataSourcePath)
{
try
{
if (string.IsNullOrEmpty(strDataSourcePath) || !File.Exists(strDataSourcePath))
{
return new Dictionary<int, string>();
}
Dictionary<int, string> dicAllSheet = new Dictionary<int, string>();
IWorkbook iWorkBook = null;
FileStream fileStream = new FileStream(strDataSourcePath, FileMode.Open, FileAccess.Read);
if (System.IO.Path.GetExtension(strDataSourcePath) == ".xls")
{
iWorkBook = new HSSFWorkbook(fileStream);
}
else if (System.IO.Path.GetExtension(strDataSourcePath) == ".xlsx")
{
iWorkBook = new XSSFWorkbook(fileStream);
}
else
{
return new Dictionary<int, string>();
}
for (int iNumberOfSheets = 0; iNumberOfSheets < iWorkBook.NumberOfSheets; iNumberOfSheets++)
{
dicAllSheet.Add(iNumberOfSheets, iWorkBook.GetSheetName(iNumberOfSheets));
}
iWorkBook.Close();
return dicAllSheet;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return new Dictionary<int, string>();
}
}
/// <summary>
/// 獲得指定Excel中的指定Sheet頁
/// </summary>
/// <param name="strDataSourcePath">Excel檔案路徑</param>
/// <param name="strSheetName">Excel中所有Sheet名</param>
/// <returns>成功返回Excel工作表,失敗返回null</returns>
public static ISheet GetExcelSheetAt(string strDataSourcePath, string strSheetName, out IWorkbook iWorkBook)
{
try
{
iWorkBook = null;
if (string.IsNullOrEmpty(strDataSourcePath) || !File.Exists(strDataSourcePath) || string.IsNullOrEmpty(strSheetName))
{
return null;
}
FileStream fileStream = new FileStream(strDataSourcePath, FileMode.Open, FileAccess.Read);
if (System.IO.Path.GetExtension(strDataSourcePath) == ".xls")
{
iWorkBook = new HSSFWorkbook(fileStream);
}
else if (System.IO.Path.GetExtension(strDataSourcePath) == ".xlsx")
{
iWorkBook = new XSSFWorkbook(fileStream);
}
else
{
return null;
}
return iWorkBook.GetSheet(strSheetName);
}
catch (Exception ex)
{
iWorkBook = null;
TXTHelper.Logs(ex.ToString());
return null;
}
}
/// <summary>
/// 獲得指定Excel中的指定Sheet頁
/// </summary>
/// <param name="strDataSourcePath">Excel檔案路徑</param>
/// <param name="iNumberOfSheet">Excel中所有Sheet序號</param>
/// <returns>成功返回Excel工作表,失敗返回null</returns>
public static ISheet GetExcelSheetAt(string strDataSourcePath, int iNumberOfSheet, out IWorkbook iWorkBook)
{
try
{
iWorkBook = null;
if (string.IsNullOrEmpty(strDataSourcePath) || !File.Exists(strDataSourcePath) || iNumberOfSheet < 0)
{
return null;
}
FileStream fileStream = new FileStream(strDataSourcePath, FileMode.Open, FileAccess.Read);
if (System.IO.Path.GetExtension(strDataSourcePath) == ".xls")
{
iWorkBook = new HSSFWorkbook(fileStream);
}
else if (System.IO.Path.GetExtension(strDataSourcePath) == ".xlsx")
{
iWorkBook = new XSSFWorkbook(fileStream);
}
else
{
return null;
}
return iWorkBook.GetSheetAt(iNumberOfSheet);
}
catch (Exception ex)
{
iWorkBook = null;
TXTHelper.Logs(ex.ToString());
return null;
}
}
/// <summary>
/// 在指定Excel中指定Sheet指定位置填充文字
/// </summary>
/// <param name="strDataSourcePath">Excel檔案路徑(如果檔案不存在則重新建立)</param>
/// <param name="strSheetName">需要填充的Sheet名稱(如果沒有則新增,如果衝突則使用衝突Sheet)</param>
/// <param name="strTXT">需要填充的文字</param>
/// <param name="iRows">填充行</param>
/// <param name="iColumn">填充列</param>
/// <returns>成功返回true,失敗返回false</returns>
public static bool FillString(string strDataSourcePath, string strSheetName, string strTXT, int iRows, int iColumn)
{
try
{
if (string.IsNullOrEmpty(strDataSourcePath) || string.IsNullOrEmpty(strSheetName) || string.IsNullOrEmpty(strTXT))
{
return false;
}
if (File.Exists(strDataSourcePath))
{
FileStream fileStream = new FileStream(strDataSourcePath, FileMode.Open, FileAccess.Read);
Dictionary<int, string> dicAllSheet = GetExcelAllSheet(strDataSourcePath);
if (System.IO.Path.GetExtension(strDataSourcePath) == ".xls")
{
//獲取指定Sheet頁
IWorkbook iWorkBook = new HSSFWorkbook(fileStream);
ISheet iSheet = null;
if (dicAllSheet.ContainsValue(strSheetName))
{
iSheet = iWorkBook.GetSheet(strSheetName);
}
else
{
iSheet = iWorkBook.CreateSheet(strSheetName);
}
//獲取指定單元格
IRow iRow = iSheet.GetRow(iRows);
ICell iCell = null;
if (iRow == null)
{
//如果沒有搜尋到指定行則建立單元格
iRow = iSheet.CreateRow(iRows);
iCell = iRow.CreateCell(iColumn);
}
else
{
iCell = iRow.GetCell(iColumn);
}
iCell.SetCellValue(strTXT);
FileStream fileStream2003 = new FileStream(Path.ChangeExtension(strDataSourcePath, "xls"), FileMode.Create);
iWorkBook.Write(fileStream2003);
fileStream2003.Close();
iWorkBook.Close();
}
else if (System.IO.Path.GetExtension(strDataSourcePath) == ".xlsx")
{
//獲取指定Sheet頁
IWorkbook iWorkBook = new XSSFWorkbook(fileStream);
ISheet iSheet = null;
if (dicAllSheet.ContainsValue(strSheetName))
{
iSheet = iWorkBook.GetSheet(strSheetName);
}
else
{
iSheet = iWorkBook.CreateSheet(strSheetName);
}
//獲取指定單元格
IRow iRow = iSheet.GetRow(iRows);
ICell iCell = null;
if (iRow == null)
{
//如果沒有搜尋到指定行則建立單元格
iRow = iSheet.CreateRow(iRows);
iCell = iRow.CreateCell(iColumn);
}
else
{
iCell = iRow.GetCell(iColumn);
}
iCell.SetCellValue(strTXT);
FileStream fileStream2007 = new FileStream(Path.ChangeExtension(strDataSourcePath, "xlsx"), FileMode.Create);
iWorkBook.Write(fileStream2007);
fileStream2007.Close();
iWorkBook.Close();
}
else
{
return false;
}
}
else
{
if (System.IO.Path.GetExtension(strDataSourcePath) == ".xls")
{
bool bCreare = CreateExcel_Office2003(strDataSourcePath, strSheetName);
bool bFill = FillString(strDataSourcePath, strSheetName, strTXT, iColumn, iRows);
if (bCreare && bFill)
{
return true;
}
else
{
return false;
}
}
else if (System.IO.Path.GetExtension(strDataSourcePath) == ".xlsx")
{
bool bCreare = CreateExcel_Office2007(strDataSourcePath, strSheetName);
bool bFill = FillString(strDataSourcePath, strSheetName, strTXT, iColumn, iRows);
if (bCreare && bFill)
{
return true;
}
else
{
return false;
}
}
else
{
return false;
}
}
return true;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return false;
}
}
/// <summary>
/// 在指定Excel中指定Sheet指定位置填充DataTable(僅拷貝,不提供儲存方法)
/// </summary>
/// <param name="iDataSourceSheet">指定Excel後設資料Sheet頁</param>
/// <param name="dtSourceData">DataTable資料</param>
/// <param name="WhetherThereFieldName">是否有列名(true保留DataTable欄位名)</param>
/// <param name="iRows">起始行</param>
/// <param name="iColumn">起始列</param>
/// <returns>成功返回拷貝後的Sheet頁,失敗返回null</returns>
public static ISheet FillDataTable(ISheet iDataSourceSheet, DataTable dtSourceData, bool WhetherThereFieldName, int iRows, int iColumn)
{
try
{
if (iDataSourceSheet == null)
{
return null;
}
if (WhetherThereFieldName)
{
IRow rowDataTableField = iDataSourceSheet.CreateRow(iRows);
for (int iDataTableColumns = 0; iDataTableColumns < dtSourceData.Columns.Count; iDataTableColumns++)
{
ICell cellErrstatist = rowDataTableField.CreateCell(iDataTableColumns + iColumn);
cellErrstatist.SetCellValue(dtSourceData.Columns[iDataTableColumns].ColumnName);
}
for (int iDataTableRows = 0; iDataTableRows < dtSourceData.Rows.Count; iDataTableRows++)
{
IRow rowDataTable = iDataSourceSheet.CreateRow(iDataTableRows + iRows + 1);
for (int iDataTableColumns = 0; iDataTableColumns < dtSourceData.Columns.Count; iDataTableColumns++)
{
ICell cellErrstatist = rowDataTable.CreateCell(iDataTableColumns + iColumn);
string strSourceData = dtSourceData.Rows[iDataTableRows][iDataTableColumns].ToString();
Regex regexIsNumeric = new Regex(@"^(-?\d+)(\.\d+)?$");
if (regexIsNumeric.IsMatch(strSourceData))
{
cellErrstatist.SetCellValue(double.Parse(strSourceData));
}
else
{
cellErrstatist.SetCellValue(strSourceData);
}
}
}
}
else
{
for (int iDataTableRows = 0; iDataTableRows < dtSourceData.Rows.Count; iDataTableRows++)
{
IRow rowDataTable = iDataSourceSheet.CreateRow(iDataTableRows + iRows);
for (int iDataTableColumns = 0; iDataTableColumns < dtSourceData.Columns.Count; iDataTableColumns++)
{
ICell cellErrstatist = rowDataTable.CreateCell(iDataTableColumns + iColumn);
string strSourceData = dtSourceData.Rows[iDataTableRows][iDataTableColumns].ToString();
Regex regexIsNumeric = new Regex(@"^(-?\d+)(\.\d+)?$");
if (regexIsNumeric.IsMatch(strSourceData))
{
cellErrstatist.SetCellValue(double.Parse(strSourceData));
}
else
{
cellErrstatist.SetCellValue(strSourceData);
}
}
}
}
return iDataSourceSheet;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return null;
}
}
/// <summary>
/// 在指定Excel中指定Sheet指定位置填充DataTable
/// </summary>
/// <param name="strDataSourcePath">Excel檔案路徑(如果檔案不存在則重新建立)</param>
/// <param name="strSheetName">需要填充的Sheet名稱(如果沒有則新增,如果衝突則使用衝突Sheet)</param>
/// <param name="dtSourceData">DataTable資料</param>
/// <param name="WhetherThereFieldName">是否有列名(true保留DataTable欄位名)</param>
/// <param name="iRows">起始行</param>
/// <param name="iColumn">起始列</param>
/// <returns>成功返回true,失敗返回false</returns>
public static bool FillDataTable(string strDataSourcePath, string strSheetName, DataTable dtSourceData, bool WhetherThereFieldName, int iRows, int iColumn)
{
try
{
if (string.IsNullOrEmpty(strDataSourcePath) || string.IsNullOrEmpty(strSheetName) || dtSourceData.Rows.Count < 1)
{
return false;
}
if (File.Exists(strDataSourcePath))
{
FileStream fileStream = new FileStream(strDataSourcePath, FileMode.Open, FileAccess.Read);
Dictionary<int, string> dicAllSheet = GetExcelAllSheet(strDataSourcePath);
if (System.IO.Path.GetExtension(strDataSourcePath) == ".xls")
{
IWorkbook iWorkBook = new HSSFWorkbook(fileStream);
ISheet iSheet = null;
if (dicAllSheet.ContainsValue(strSheetName))
{
iSheet = iWorkBook.GetSheet(strSheetName);
}
else
{
iSheet = iWorkBook.CreateSheet(strSheetName);
}
if (WhetherThereFieldName)
{
IRow rowDataTableField = iSheet.CreateRow(iRows);
for (int iDataTableColumns = 0; iDataTableColumns < dtSourceData.Columns.Count; iDataTableColumns++)
{
ICell cellErrstatist = rowDataTableField.CreateCell(iDataTableColumns + iColumn);
cellErrstatist.SetCellValue(dtSourceData.Columns[iDataTableColumns].ColumnName);
}
for (int iDataTableRows = 0; iDataTableRows < dtSourceData.Rows.Count; iDataTableRows++)
{
IRow rowDataTable = iSheet.CreateRow(iDataTableRows + iRows + 1);
for (int iDataTableColumns = 0; iDataTableColumns < dtSourceData.Columns.Count; iDataTableColumns++)
{
ICell cellErrstatist = rowDataTable.CreateCell(iDataTableColumns + iColumn);
string strSourceData = dtSourceData.Rows[iDataTableRows][iDataTableColumns].ToString();
Regex regexIsNumeric = new Regex(@"^(-?\d+)(\.\d+)?$");
if (regexIsNumeric.IsMatch(strSourceData))
{
cellErrstatist.SetCellValue(double.Parse(strSourceData));
}
else
{
cellErrstatist.SetCellValue(strSourceData);
}
}
}
}
else
{
for (int iDataTableRows = 0; iDataTableRows < dtSourceData.Rows.Count; iDataTableRows++)
{
IRow rowDataTable = iSheet.CreateRow(iDataTableRows + iRows);
for (int iDataTableColumns = 0; iDataTableColumns < dtSourceData.Columns.Count; iDataTableColumns++)
{
ICell cellErrstatist = rowDataTable.CreateCell(iDataTableColumns + iColumn);
string strSourceData = dtSourceData.Rows[iDataTableRows][iDataTableColumns].ToString();
Regex regexIsNumeric = new Regex(@"^(-?\d+)(\.\d+)?$");
if (regexIsNumeric.IsMatch(strSourceData))
{
cellErrstatist.SetCellValue(double.Parse(strSourceData));
}
else
{
cellErrstatist.SetCellValue(strSourceData);
}
}
}
}
FileStream fileStream2003 = new FileStream(Path.ChangeExtension(strDataSourcePath, "xls"), FileMode.Create);
iWorkBook.Write(fileStream2003);
fileStream2003.Close();
iWorkBook.Close();
}
else if (System.IO.Path.GetExtension(strDataSourcePath) == ".xlsx")
{
IWorkbook iWorkBook = new XSSFWorkbook(fileStream);
ISheet iSheet = null;
if (dicAllSheet.ContainsValue(strSheetName))
{
iSheet = iWorkBook.GetSheet(strSheetName);
}
else
{
iSheet = iWorkBook.CreateSheet(strSheetName);
}
if (WhetherThereFieldName)
{
IRow rowDataTableField = iSheet.CreateRow(iRows);
for (int iDataTableColumns = 0; iDataTableColumns < dtSourceData.Columns.Count; iDataTableColumns++)
{
ICell cellErrstatist = rowDataTableField.CreateCell(iDataTableColumns + iColumn);
cellErrstatist.SetCellValue(dtSourceData.Columns[iDataTableColumns].ColumnName);
}
for (int iDataTableRows = 0; iDataTableRows < dtSourceData.Rows.Count; iDataTableRows++)
{
IRow rowDataTable = iSheet.CreateRow(iDataTableRows + iRows + 1);
for (int iDataTableColumns = 0; iDataTableColumns < dtSourceData.Columns.Count; iDataTableColumns++)
{
ICell cellErrstatist = rowDataTable.CreateCell(iDataTableColumns + iColumn);
string strSourceData = dtSourceData.Rows[iDataTableRows][iDataTableColumns].ToString();
Regex regexIsNumeric = new Regex(@"^(-?\d+)(\.\d+)?$");
if (regexIsNumeric.IsMatch(strSourceData))
{
cellErrstatist.SetCellValue(double.Parse(strSourceData));
}
else
{
cellErrstatist.SetCellValue(strSourceData);
}
}
}
}
else
{
for (int iDataTableRows = 0; iDataTableRows < dtSourceData.Rows.Count; iDataTableRows++)
{
IRow rowDataTable = iSheet.CreateRow(iDataTableRows + iRows);
for (int iDataTableColumns = 0; iDataTableColumns < dtSourceData.Columns.Count; iDataTableColumns++)
{
ICell cellErrstatist = rowDataTable.CreateCell(iDataTableColumns + iColumn);
string strSourceData = dtSourceData.Rows[iDataTableRows][iDataTableColumns].ToString();
Regex regexIsNumeric = new Regex(@"^(-?\d+)(\.\d+)?$");
if (regexIsNumeric.IsMatch(strSourceData))
{
cellErrstatist.SetCellValue(double.Parse(strSourceData));
}
else
{
cellErrstatist.SetCellValue(strSourceData);
}
}
}
}
FileStream fileStream2007 = new FileStream(Path.ChangeExtension(strDataSourcePath, "xlsx"), FileMode.Create);
iWorkBook.Write(fileStream2007);
fileStream2007.Close();
iWorkBook.Close();
}
else
{
return false;
}
}
else
{
if (System.IO.Path.GetExtension(strDataSourcePath) == ".xls")
{
bool bCreare = CreateExcel_Office2003(strDataSourcePath, strSheetName);
bool bFill = FillDataTable(strDataSourcePath, strSheetName, dtSourceData, WhetherThereFieldName, iColumn, iRows);
if (bCreare && bFill)
{
return true;
}
else
{
return false;
}
}
else if (System.IO.Path.GetExtension(strDataSourcePath) == ".xlsx")
{
bool bCreare = CreateExcel_Office2007(strDataSourcePath, strSheetName);
bool bFill = FillDataTable(strDataSourcePath, strSheetName, dtSourceData, WhetherThereFieldName, iColumn, iRows);
if (bCreare && bFill)
{
return true;
}
else
{
return false;
}
}
else
{
return false;
}
}
return true;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return false;
}
}
/// <summary>
/// DataTable轉換為Excel
/// 存在檔案則新建DataTableName的分頁(如果分頁名衝突則或為空則使用預設名稱)
/// 不存在檔案則新建(Excel,名稱為DataTableName,如果沒有則使用預設名稱)
/// </summary>
/// <param name="strDataSourcePath">Excel檔案路徑</param>
/// <param name="dtSourceData">DataTable資料</param>
/// <returns>成功返回true,失敗返回false</returns>
public static bool DataTableConversionExcel(string strDataSourcePath, DataTable dtSourceData)
{
try
{
if (string.IsNullOrEmpty(strDataSourcePath) || dtSourceData.Rows.Count < 1)
{
return false;
}
Dictionary<int, string> dicAllSheet = GetExcelAllSheet(strDataSourcePath);
string strTableName = string.IsNullOrEmpty(dtSourceData.TableName) ? string.Format("Sheet{0}", dicAllSheet.Count + 1) : dtSourceData.TableName;
if (dicAllSheet.ContainsValue(dtSourceData.TableName))
{
RemoveExcelSheet(strDataSourcePath, dtSourceData.TableName);
}
if (FillDataTable(strDataSourcePath, strTableName, dtSourceData, true, 0, 0))
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return false;
}
}
/// <summary>
/// DataSet轉換為Excel
/// 存在檔案則新建DataTableName的分頁(如果分頁名衝突則或為空則使用預設名稱)
/// 不存在檔案則新建(Excel,名稱為DataTableName,如果沒有則使用預設名稱)
/// </summary>
/// <param name="strDataSourcePath">Excel檔案路徑</param>
/// <param name="dsSourceData">DataTable資料</param>
/// <returns>成功返回true,失敗返回false</returns>
public static bool DataSetConversionExcel(string strDataSourcePath, DataSet dsSourceData)
{
try
{
if (string.IsNullOrEmpty(strDataSourcePath) || dsSourceData.Tables.Count < 1)
{
return false;
}
foreach (DataTable dtSourceData in dsSourceData.Tables)
{
Dictionary<int, string> dicAllSheet = GetExcelAllSheet(strDataSourcePath);
string strTableName = string.IsNullOrEmpty(dtSourceData.TableName) ? string.Format("Sheet{0}", dicAllSheet.Count + 1) : dtSourceData.TableName;
if (dicAllSheet.ContainsValue(dtSourceData.TableName))
{
RemoveExcelSheet(strDataSourcePath, dtSourceData.TableName);
}
if (!FillDataTable(strDataSourcePath, strTableName, dtSourceData, true, 0, 0))
{
return false;
}
}
return true;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return false;
}
}
/// <summary>
/// 獲得指定Excel指定分頁指定起始終止位置的DataTable
/// </summary>
/// <param name="iDataSourceSheet">指定Excel後設資料Sheet頁</param>
/// <param name="WhetherThereFieldName">是否有列名(true保留DataTable欄位名)</param>
/// <param name="iStartRows">起始行</param>
/// <param name="iStartColumn">起始列</param>
/// <param name="iStopRows">終止行(如果小於等於0則預設Length)</param>
/// <param name="iStopColumn">終止列(如果小於等於0則預設Length)</param>
/// <returns>成功返回Excel的DataTable,失敗返回NULL</returns>
public static DataTable GetDataTable(ISheet iDataSourceSheet, bool WhetherThereFieldName, int iStartRows, int iStartColumn, int iStopRows, int iStopColumn)
{
try
{
if (iDataSourceSheet == null)
{
return null;
}
DataTable dtTargetData = new DataTable();
if (WhetherThereFieldName)
{
//構建DataTable列(第一行為列名)
IRow iRowFirst = iDataSourceSheet.GetRow(iStartRows);
for (int iFirst = iStartColumn; iFirst <= (iStopColumn <= 0 ? (iRowFirst.LastCellNum) : iStopColumn); iFirst++)
{
ICell iCell = iRowFirst.GetCell(iFirst);
if (iCell != null)
{
if (iCell.StringCellValue != null)
{
DataColumn dColumn = new DataColumn(iCell.StringCellValue);
dtTargetData.Columns.Add(dColumn);
}
}
}
//構建DataTable行(第二行往下為資料)
for (int iRowNum = iStartRows + 1; iRowNum <= (iStopRows <= 0 ? iDataSourceSheet.LastRowNum : iStopRows); iRowNum++)
{
IRow iRowData = iDataSourceSheet.GetRow(iRowNum);
if (iRowData == null) continue;
DataRow drTargetData = dtTargetData.NewRow();
for (int iRowCell = iStartColumn; iRowCell <= (iStopColumn <= 0 ? (iRowFirst.LastCellNum) : iStopColumn); iRowCell++)
{
ICell iCell = iRowData.GetCell(iRowCell);
if (iCell != null)
{
iCell.SetCellType(CellType.String);
if (iCell.StringCellValue != null)
{
int iNumberIsColums = iRowCell - iStartColumn;
if (iNumberIsColums < dtTargetData.Columns.Count)
{
DataColumn dColumn = new DataColumn(iCell.StringCellValue);
drTargetData[iNumberIsColums] = dColumn;
}
}
}
}
dtTargetData.Rows.Add(drTargetData);
}
}
else
{
//構建DataTable列,以讀取第一行的長度填充列名(使用預設命名初始化列名Column1)
IRow iRowFirst = iDataSourceSheet.GetRow(iStartRows);
for (int iFirst = iStartColumn; iFirst <= (iStopColumn <= 0 ? (iRowFirst.LastCellNum) : iStopColumn); iFirst++)
{
ICell iCell = iRowFirst.GetCell(iFirst);
if (iCell != null)
{
if (iCell.StringCellValue != null)
{
dtTargetData.Columns.Add(string.Format("Column{0}", iFirst - iStartColumn));
}
}
}
//構建DataTable行(第一行往下為資料)
for (int iRowNum = iStartRows; iRowNum <= (iStopRows <= 0 ? iDataSourceSheet.LastRowNum : iStopRows); iRowNum++)
{
IRow iRowData = iDataSourceSheet.GetRow(iRowNum);
if (iRowData == null) continue;
DataRow drTargetData = dtTargetData.NewRow();
for (int iRowCell = iStartColumn; iRowCell <= (iStopColumn <= 0 ? (iRowFirst.LastCellNum) : iStopColumn); iRowCell++)
{
ICell iCell = iRowData.GetCell(iRowCell);
if (iCell != null)
{
iCell.SetCellType(CellType.String);
if (iCell.StringCellValue != null)
{
int iNumberIsColums = iRowCell - iStartColumn;
if (iNumberIsColums < dtTargetData.Columns.Count)
{
DataColumn dColumn = new DataColumn(iCell.StringCellValue);
drTargetData[iNumberIsColums] = dColumn;
}
}
}
}
dtTargetData.Rows.Add(drTargetData);
}
}
return dtTargetData;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return null;
}
}
/// <summary>
/// 獲得指定Excel指定分頁指定起始終止位置的DataTable
/// </summary>
/// <param name="strDataSourcePath">Excel檔案路徑</param>
/// <param name="strSheetName">分頁Sheet名稱</param>
/// <param name="WhetherThereFieldName">是否有列名(true保留DataTable欄位名)</param>
/// <param name="iStartRows">起始行</param>
/// <param name="iStartColumn">起始列</param>
/// <param name="iStopRows">終止行(如果小於等於0則預設Length)</param>
/// <param name="iStopColumn">終止列(如果小於等於0則預設Length)</param>
/// <returns>成功返回Excel的DataTable,失敗返回NULL</returns>
public static DataTable GetDataTable(string strDataSourcePath, string strSheetName, bool WhetherThereFieldName, int iStartRows, int iStartColumn, int iStopRows, int iStopColumn)
{
try
{
if (string.IsNullOrEmpty(strDataSourcePath) || !File.Exists(strDataSourcePath))
{
return null;
}
DataTable dtTargetData = new DataTable();
FileStream fileStream = new FileStream(strDataSourcePath, FileMode.Open, FileAccess.Read);
IWorkbook iWorkBook = null;
if (System.IO.Path.GetExtension(strDataSourcePath) == ".xls")
{
iWorkBook = new HSSFWorkbook(fileStream);
}
else if (System.IO.Path.GetExtension(strDataSourcePath) == ".xlsx")
{
iWorkBook = new XSSFWorkbook(fileStream);
}
ISheet iSheet = null;
if (string.IsNullOrEmpty(strSheetName))
{
Dictionary<int, string> dicAllSheet = GetExcelAllSheet(strDataSourcePath);
iSheet = iWorkBook.GetSheet(dicAllSheet[0]);
dtTargetData.TableName = dicAllSheet[0];
}
else
{
iSheet = iWorkBook.GetSheet(strSheetName);
dtTargetData.TableName = strSheetName;
}
if (WhetherThereFieldName)
{
//構建DataTable列(第一行為列名)
IRow iRowFirst = iSheet.GetRow(iStartRows);
for (int iFirst = iStartColumn; iFirst <= (iStopColumn <= 0 ? (iRowFirst.LastCellNum) : iStopColumn); iFirst++)
{
ICell iCell = iRowFirst.GetCell(iFirst);
if (iCell != null)
{
if (iCell.StringCellValue != null)
{
DataColumn dColumn = new DataColumn(iCell.StringCellValue);
dtTargetData.Columns.Add(dColumn);
}
}
}
//構建DataTable行(第二行往下為資料)
for (int iRowNum = iStartRows + 1; iRowNum <= (iStopRows <= 0 ? iSheet.LastRowNum : iStopRows); iRowNum++)
{
IRow iRowData = iSheet.GetRow(iRowNum);
if (iRowData == null) continue;
DataRow drTargetData = dtTargetData.NewRow();
for (int iRowCell = iStartColumn; iRowCell <= (iStopColumn <= 0 ? (iRowFirst.LastCellNum) : iStopColumn); iRowCell++)
{
ICell iCell = iRowData.GetCell(iRowCell);
if (iCell != null)
{
iCell.SetCellType(CellType.String);
if (iCell.StringCellValue != null)
{
int iNumberIsColums = iRowCell - iStartColumn;
if (iNumberIsColums < dtTargetData.Columns.Count)
{
DataColumn dColumn = new DataColumn(iCell.StringCellValue);
drTargetData[iNumberIsColums] = dColumn;
}
}
}
}
dtTargetData.Rows.Add(drTargetData);
}
}
else
{
//構建DataTable列,以讀取第一行的長度填充列名(使用預設命名初始化列名Column1)
IRow iRowFirst = iSheet.GetRow(iStartRows);
for (int iFirst = iStartColumn; iFirst <= (iStopColumn <= 0 ? (iRowFirst.LastCellNum) : iStopColumn); iFirst++)
{
ICell iCell = iRowFirst.GetCell(iFirst);
if (iCell != null)
{
if (iCell.StringCellValue != null)
{
dtTargetData.Columns.Add(string.Format("Column{0}", iFirst - iStartColumn));
}
}
}
//構建DataTable行(第一行往下為資料)
for (int iRowNum = iStartRows; iRowNum <= (iStopRows <= 0 ? iSheet.LastRowNum : iStopRows); iRowNum++)
{
IRow iRowData = iSheet.GetRow(iRowNum);
if (iRowData == null) continue;
DataRow drTargetData = dtTargetData.NewRow();
for (int iRowCell = iStartColumn; iRowCell <= (iStopColumn <= 0 ? (iRowFirst.LastCellNum) : iStopColumn); iRowCell++)
{
ICell iCell = iRowData.GetCell(iRowCell);
if (iCell != null)
{
iCell.SetCellType(CellType.String);
if (iCell.StringCellValue != null)
{
int iNumberIsColums = iRowCell - iStartColumn;
if (iNumberIsColums < dtTargetData.Columns.Count)
{
DataColumn dColumn = new DataColumn(iCell.StringCellValue);
drTargetData[iNumberIsColums] = dColumn;
}
}
}
}
dtTargetData.Rows.Add(drTargetData);
}
}
return dtTargetData;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return null;
}
}
/// <summary>
/// Excel指定分頁轉換為DataTable(如果分頁為空,預設第一個分頁)
/// </summary>
/// <param name="strDataSourcePath">Excel檔案路徑</param>
/// <param name="strSheetName">分頁Sheet名稱</param>
/// <returns>成功返回Excel的DataTable,失敗返回NULL</returns>
public static DataTable ExcelConversionDataTable(string strDataSourcePath, string strSheetName)
{
try
{
if (string.IsNullOrEmpty(strDataSourcePath) || !File.Exists(strDataSourcePath))
{
return null;
}
return GetDataTable(strDataSourcePath, strSheetName, true, 0, 0, 0, 0);
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return null;
}
}
/// <summary>
/// Excel所有分頁轉換為DataSet
/// </summary>
/// <param name="strDataSourcePath">Excel檔案路徑</param>
/// <returns>成功返回Excel的DataSet,失敗返回NULL</returns>
public static DataSet ExcelConversionDataSet(string strDataSourcePath)
{
try
{
if (string.IsNullOrEmpty(strDataSourcePath) || !File.Exists(strDataSourcePath))
{
return null;
}
DataSet dsTargetData = new DataSet();
Dictionary<int, string> dicAllSheet = GetExcelAllSheet(strDataSourcePath);
foreach (var vAllSheet in dicAllSheet)
{
DataTable dtTargetData = new DataTable();
dtTargetData.TableName = vAllSheet.Value;
dtTargetData = ExcelConversionDataTable(strDataSourcePath, vAllSheet.Value);
if (dtTargetData == null)
{
continue;
}
dsTargetData.Tables.Add(dtTargetData);
}
return dsTargetData;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return null;
}
}
/// <summary>
/// 拷貝Sheet頁到另一個Sheet頁(淺拷貝,不提供儲存方法)
/// Office2003單Sheet頁僅支援4000個樣式
/// </summary>
/// <param name="iSourceWorkbook">源Excel工作簿</param>
/// <param name="iFromSheet">源Sheet頁</param>
/// <param name="iTargetWorkbook">目標Excel工作簿</param>
/// <param name="iToSheet">目標Sheet頁</param>
/// <returns>成功返回true,失敗返回false</returns>
public static bool CopySheetAt(IWorkbook iSourceWorkbook, ISheet iFromSheet, IWorkbook iTargetWorkbook, ISheet iToSheet)
{
try
{
//拷貝資料
DataTable dtExcelFromData = GetDataTable(iFromSheet, false, 0, 0, 0, 0);
iToSheet = FillDataTable(iToSheet, dtExcelFromData, false, 0, 0);
//拷貝單元格合併
for (int iMergedRegions = 0; iMergedRegions < iFromSheet.NumMergedRegions; iMergedRegions++)
{
iToSheet.AddMergedRegion(iFromSheet.GetMergedRegion(iMergedRegions));
}
//拷貝樣式(遍歷Sheet頁行)
List<ICellStyle> listCellStyle = new List<ICellStyle>();
for (int iRowNum = 0; iRowNum <= iFromSheet.LastRowNum; iRowNum++)
{
IRow iFromRowData = iFromSheet.GetRow(iRowNum);
IRow iToRowData = iToSheet.GetRow(iRowNum);
if (iFromRowData == null || iToRowData == null)
{
continue;
}
//設定行高
short sFromHeight = iFromRowData.Height;
iToRowData.Height = sFromHeight;
//遍歷Sheet頁列
for (int iRowCell = 0; iRowCell <= iFromRowData.LastCellNum; iRowCell++)
{
//設定列寬
int iFromColumnWidth = iFromSheet.GetColumnWidth(iRowNum) / 256;
iToSheet.SetColumnWidth(iRowNum, iFromColumnWidth * 256);
//複製資料
ICell iFromCell = iFromRowData.GetCell(iRowCell);
if (iFromCell != null)
{
//獲得源Sheet頁的樣式
ICellStyle iFromCellStyle = iFromCell.CellStyle;
//獲得目標Excel指定Cell
ICell iToCell = iToRowData.GetCell(iRowCell);
if (iToCell == null) continue;
#region 複製單元格樣式
//指定Cell創新目標Excel工作簿新樣式
ICellStyle iToNewCellStyle = null;
foreach (ICellStyle vCellStyle in listCellStyle)
{
IFont iVToFont = vCellStyle.GetFont(iTargetWorkbook);
IFont iFromFont = iFromCellStyle.GetFont(iSourceWorkbook);
if (vCellStyle.Alignment == iFromCellStyle.Alignment &&
vCellStyle.BorderBottom == iFromCellStyle.BorderBottom &&
vCellStyle.BorderLeft == iFromCellStyle.BorderLeft &&
vCellStyle.BorderRight == iFromCellStyle.BorderRight &&
vCellStyle.BorderTop == iFromCellStyle.BorderTop &&
vCellStyle.BottomBorderColor == iFromCellStyle.BottomBorderColor &&
vCellStyle.DataFormat == iFromCellStyle.DataFormat &&
vCellStyle.FillBackgroundColor == iFromCellStyle.FillBackgroundColor &&
vCellStyle.FillForegroundColor == iFromCellStyle.FillForegroundColor &&
vCellStyle.FillPattern == iFromCellStyle.FillPattern &&
vCellStyle.Indention == iFromCellStyle.Indention &&
vCellStyle.IsHidden == iFromCellStyle.IsHidden &&
vCellStyle.IsLocked == iFromCellStyle.IsLocked &&
vCellStyle.LeftBorderColor == iFromCellStyle.LeftBorderColor &&
vCellStyle.RightBorderColor == iFromCellStyle.RightBorderColor &&
vCellStyle.Rotation == iFromCellStyle.Rotation &&
vCellStyle.TopBorderColor == iFromCellStyle.TopBorderColor &&
vCellStyle.VerticalAlignment == iFromCellStyle.VerticalAlignment &&
vCellStyle.WrapText == iFromCellStyle.WrapText &&
//字型比對
iVToFont.Color == iFromFont.Color &&
iVToFont.FontHeightInPoints == iFromFont.FontHeightInPoints &&
iVToFont.FontName == iFromFont.FontName &&
iVToFont.IsBold == iFromFont.IsBold &&
iVToFont.IsItalic == iFromFont.IsItalic &&
iVToFont.IsStrikeout == iFromFont.IsStrikeout &&
iVToFont.Underline == iFromFont.Underline)
{
iToNewCellStyle = vCellStyle;
break;
}
}
if (iToNewCellStyle == null)
{
//建立新樣式
iToNewCellStyle = iTargetWorkbook.CreateCellStyle();
//複製樣式
iToNewCellStyle.Alignment = iFromCellStyle.Alignment;//對齊
iToNewCellStyle.BorderBottom = iFromCellStyle.BorderBottom;//下邊框
iToNewCellStyle.BorderLeft = iFromCellStyle.BorderLeft;//左邊框
iToNewCellStyle.BorderRight = iFromCellStyle.BorderRight;//右邊框
iToNewCellStyle.BorderTop = iFromCellStyle.BorderTop;//上邊框
iToNewCellStyle.BottomBorderColor = iFromCellStyle.BottomBorderColor;//下邊框顏色
iToNewCellStyle.DataFormat = iFromCellStyle.DataFormat;//資料格式
iToNewCellStyle.FillBackgroundColor = iFromCellStyle.FillBackgroundColor;//填充背景色
iToNewCellStyle.FillForegroundColor = iFromCellStyle.FillForegroundColor;//填充前景色
iToNewCellStyle.FillPattern = iFromCellStyle.FillPattern;//填充圖案
iToNewCellStyle.Indention = iFromCellStyle.Indention;//壓痕
iToNewCellStyle.IsHidden = iFromCellStyle.IsHidden;//隱藏
iToNewCellStyle.IsLocked = iFromCellStyle.IsLocked;//鎖定
iToNewCellStyle.LeftBorderColor = iFromCellStyle.LeftBorderColor;//左邊框顏色
iToNewCellStyle.RightBorderColor = iFromCellStyle.RightBorderColor;//右邊框顏色
iToNewCellStyle.Rotation = iFromCellStyle.Rotation;//旋轉
iToNewCellStyle.TopBorderColor = iFromCellStyle.TopBorderColor;//上邊框顏色
iToNewCellStyle.VerticalAlignment = iFromCellStyle.VerticalAlignment;//垂直對齊
iToNewCellStyle.WrapText = iFromCellStyle.WrapText;//文字換行
//複製字型
IFont iFromFont = iFromCellStyle.GetFont(iSourceWorkbook);
IFont iToFont = iTargetWorkbook.CreateFont();
iToFont.Color = iFromFont.Color;//顏色
iToFont.FontHeightInPoints = iFromFont.FontHeightInPoints;//字號
iToFont.FontName = iFromFont.FontName;//字型
iToFont.IsBold = iFromFont.IsBold;//加粗
iToFont.IsItalic = iFromFont.IsItalic;//斜體
iToFont.IsStrikeout = iFromFont.IsStrikeout;//刪除線
iToFont.Underline = iFromFont.Underline;//下劃線
iToNewCellStyle.SetFont(iToFont);
//儲存到快取集合中
listCellStyle.Add(iToNewCellStyle);
}
//複製樣式到指定表格中
iToCell.CellStyle = iToNewCellStyle;
#endregion
}
}
}
return true;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return false;
}
}
/// <summary>
/// 拷貝Sheet頁到另一個Sheet頁
/// </summary>
/// <param name="strSourceExcelPath">源Excel路徑</param>
/// <param name="strFromSheetName">源Excel拷貝Sheet</param>
/// <param name="strTargetExcelPath">目標Excel路徑</param>
/// <param name="strToSheetName">目標Excel拷貝Sheet</param>
/// <returns>成功返回true,失敗返回false</returns>
public static bool CopySheet(string strSourceExcelPath, string strFromSheetName, string strTargetExcelPath, string strToSheetName)
{
try
{
if (string.IsNullOrEmpty(strSourceExcelPath) || string.IsNullOrEmpty(strTargetExcelPath) || !File.Exists(strSourceExcelPath))
{
TXTHelper.Logs(string.Format("源資料和目標資料引數為空或檔案不存在!"));
return false;
}
if (string.IsNullOrEmpty(strFromSheetName) || string.IsNullOrEmpty(strToSheetName))
{
TXTHelper.Logs(string.Format("源Sheet頁和目標Sheet頁引數為空!"));
return false;
}
//獲得源資料和目標資料的Sheet頁
IWorkbook iSourceWorkbook = null;
ISheet iSourceSheet = GetExcelSheetAt(strSourceExcelPath, strFromSheetName, out iSourceWorkbook);
IWorkbook iTargetWorkbook = null;
ISheet iTargetSheet = null;
if (iSourceSheet == null)
{
TXTHelper.Logs(string.Format("指定源資料Sheet頁為空!"));
return false;
}
if (!File.Exists(strTargetExcelPath))
{
//如果檔案不存在則建立Excel
if (System.IO.Path.GetExtension(strTargetExcelPath) == ".xls")
{
bool bCreare = CreateExcel_Office2003(strTargetExcelPath, strToSheetName);
}
else if (System.IO.Path.GetExtension(strTargetExcelPath) == ".xlsx")
{
bool bCreare = CreateExcel_Office2007(strTargetExcelPath, strToSheetName);
}
else
{
TXTHelper.Logs(string.Format("指定目標Excel檔案路徑格式錯誤!"));
return false;
}
iTargetSheet = GetExcelSheetAt(strTargetExcelPath, strToSheetName, out iTargetWorkbook);
}
else
{
//如果檔案存在則判斷是否存在執行Sheet
Dictionary<int, string> dicAllSheet = GetExcelAllSheet(strTargetExcelPath);
if (dicAllSheet.ContainsValue(strToSheetName))
{
iTargetSheet = GetExcelSheetAt(strTargetExcelPath, strToSheetName, out iTargetWorkbook);
}
else
{
iTargetSheet = CreateExcelSheetAt(strTargetExcelPath, strToSheetName, out iTargetWorkbook);
}
}
//呼叫Sheet拷貝Sheet方法
bool bCopySheet = CopySheetAt(iSourceWorkbook, iSourceSheet, iTargetWorkbook, iTargetSheet);
if (bCopySheet)
{
if (System.IO.Path.GetExtension(strTargetExcelPath) == ".xls")
{
FileStream fileStream2003 = new FileStream(Path.ChangeExtension(strTargetExcelPath, "xls"), FileMode.Create);
iTargetWorkbook.Write(fileStream2003);
fileStream2003.Close();
iTargetWorkbook.Close();
}
else if (System.IO.Path.GetExtension(strTargetExcelPath) == ".xlsx")
{
FileStream fileStream2007 = new FileStream(Path.ChangeExtension(strTargetExcelPath, "xlsx"), FileMode.Create);
iTargetWorkbook.Write(fileStream2007);
fileStream2007.Close();
iTargetWorkbook.Close();
}
return true;
}
else
{
TXTHelper.Logs(string.Format("拷貝失敗!"));
return false;
}
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return false;
}
}
}
}
Word檔案操作
WordHelper提供建立檔案（2003/2007）及替換段落表格標籤（匹配替換’{標籤}’,’#標籤#’），替換圖片功能。僅顯示最外層引用方法，詳細呼叫請在幫助類種檢視！
/// <summary>
/// 替換文字標籤
/// </summary>
/// <param name="strDataSourcePath">Word檔案路徑</param>
/// <param name="strLabelName">標籤名稱(帶標籤符號)</param>
/// <param name="strReplaceLabel">替換標籤文字</param>
/// <returns>成功返回替換數量,失敗返回-1</returns>
public static int ReplaceTextLabel(string strDataSourcePath, string strLabelName, string strReplaceLabel)
{
try
{
if (string.IsNullOrEmpty(strDataSourcePath) || !File.Exists(strDataSourcePath) || string.IsNullOrEmpty(strLabelName) || string.IsNullOrEmpty(strReplaceLabel))
{
return -1;
}
int iNumber = 0;
FileStream fileStreamOpen = new FileStream(strDataSourcePath, FileMode.Open, FileAccess.Read);
XWPFDocument wordDocument = new XWPFDocument(fileStreamOpen);
foreach (XWPFParagraph wordParagraph in wordDocument.Paragraphs)
{
if (wordParagraph.ParagraphText.IndexOf(strLabelName) >= 0)
{
string strReplaceTextLabel = wordParagraph.ParagraphText.Replace(strLabelName, strReplaceLabel);
foreach (XWPFRun wordRun in wordParagraph.Runs)
{
wordRun.SetText(string.Empty, 0);
}
wordParagraph.CreateRun().SetText(strReplaceTextLabel, 0);
iNumber++;
}
}
foreach (XWPFTable wordTable in wordDocument.Tables)
{
foreach (XWPFTableRow wordTableRow in wordTable.Rows)
{
foreach (XWPFTableCell wordTableCell in wordTableRow.GetTableCells())
{
foreach (XWPFParagraph wordParagraph in wordTableCell.Paragraphs)
{
if (wordParagraph.ParagraphText.IndexOf(strLabelName) >= 0)
{
string strReplaceTextLabel = wordParagraph.ParagraphText.Replace(strLabelName, strReplaceLabel);
foreach (XWPFRun wordRun in wordParagraph.Runs)
{
wordRun.SetText(string.Empty, 0);
}
wordParagraph.CreateRun().SetText(strReplaceTextLabel, 0);
iNumber++;
}
}
}
}
}
FileStream fileStreamSave = new FileStream(strDataSourcePath, FileMode.Create);
wordDocument.Write(fileStreamSave);
fileStreamSave.Close();
wordDocument.Close();
return iNumber;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return -1;
}
}
/// <summary>
/// 替換表格標籤(DataTable替換)
/// </summary>
/// <param name="strDataSourcePath">Word檔案路徑</param>
/// <param name="strLabelName">標籤名稱(帶標籤符號)</param>
/// <param name="dtReplaceLabel">替換標籤DataTable</param>
/// <returns>成功返回1,失敗返回-1</returns>
public static int ReplaceDataTableLabel(string strDataSourcePath, string strLabelName, DataTable dtReplaceLabel)
{
try
{
if (string.IsNullOrEmpty(strDataSourcePath) || !File.Exists(strDataSourcePath) || string.IsNullOrEmpty(strLabelName) || dtReplaceLabel == null || dtReplaceLabel.Rows.Count < 1)
{
return -1;
}
FileStream fileStreamOpen = new FileStream(strDataSourcePath, FileMode.Open, FileAccess.Read);
XWPFDocument wordDocument = new XWPFDocument(fileStreamOpen);
int iLableRowPosition = -1;
int iLableCellPosition = -1;
foreach (XWPFTable wordTable in wordDocument.Tables)
{
for (int iTableRow = 0; iTableRow < wordTable.Rows.Count; iTableRow++)
{
for (int iTableCell = 0; iTableCell < wordTable.Rows[iTableRow].GetTableCells().Count; iTableCell++)
{
foreach (XWPFParagraph wordParagraph in wordTable.Rows[iTableRow].GetTableCells()[iTableCell].Paragraphs)
{
if (wordParagraph.ParagraphText.IndexOf(strLabelName) >= 0)
{
if (iLableRowPosition < 0 && iLableCellPosition < 0)
{
iLableRowPosition = iTableRow;
iLableCellPosition = iTableCell;
}
}
if (iLableRowPosition >= 0 && iLableCellPosition >= 0)
{
int iCurrentRow = iTableRow - iLableRowPosition;
int iCurrentCell = iTableCell - iLableCellPosition;
if ((iCurrentRow < dtReplaceLabel.Rows.Count && iCurrentRow >= 0) && (iCurrentCell < dtReplaceLabel.Columns.Count && iCurrentCell >= 0))
{
foreach (XWPFRun wordRun in wordParagraph.Runs)
{
wordRun.SetText(string.Empty, 0);
}
wordParagraph.CreateRun().SetText(dtReplaceLabel.Rows[iCurrentRow][iCurrentCell].ToString(), 0);
}
}
}
}
}
}
FileStream fileStreamSave = new FileStream(strDataSourcePath, FileMode.Create);
wordDocument.Write(fileStreamSave);
fileStreamSave.Close();
wordDocument.Close();
return 1;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return -1;
}
}
/// <summary>
/// 替換圖片標籤(使用DocX.dll類庫,呼叫這個方法後NPOI無法讀取文件)
/// </summary>
/// <param name="strDataSourcePath">Word檔案路徑</param>
/// <param name="strLabelName">標籤名稱(帶標籤符號)</param>
/// <param name="strImagePath">替換的圖片路徑</param>
/// <param name="iImageWidth">替換的圖片寬度(小於0則顯示原圖寬度)</param>
/// <param name="iImageHeight">替換的圖片高度(小於0則顯示原圖高度)</param>
/// <returns>成功返回替換數量,失敗返回-1</returns>
public static int ReplaceImageLabel(string strDataSourcePath, string strLabelName, string strImagePath, int iImageWidth, int iImageHeight)
{
try
{
if (string.IsNullOrEmpty(strDataSourcePath) || !File.Exists(strDataSourcePath) || string.IsNullOrEmpty(strLabelName) || string.IsNullOrEmpty(strImagePath) || !File.Exists(strImagePath))
{
return -1;
}
int iNumber = 0;
//使用DocX.dll類庫
DocX mDocX = DocX.Load(strDataSourcePath);
//遍歷段落
foreach (Paragraph wordParagraph in mDocX.Paragraphs)
{
if (wordParagraph.Text.IndexOf(strLabelName) >= 0)
{
//新增圖片
Novacode.Image pImag = mDocX.AddImage(strImagePath);
Picture pPicture = pImag.CreatePicture();
//如果傳入寬度小於0,則以原始大小插入
if (iImageWidth >= 0)
{
pPicture.Width = iImageWidth;
}
//如果傳入高度小於0,則以原始大小插入
if (iImageHeight >= 0)
{
pPicture.Height = iImageHeight;
}
//將影像插入到段落後面
wordParagraph.InsertPicture(pPicture);
//清空文字(清空放在前面會導致替換失敗文字消失)
wordParagraph.ReplaceText(strLabelName, string.Empty);
iNumber++;
}
}
mDocX.SaveAs(strDataSourcePath);
return iNumber;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return -1;
}
}
完整檔案
using System;
using System.Collections.Generic;
using System.IO;
using System.Xml;
using System.Linq;
using System.Data;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using NPOI.Helper.TXT;
using NPOI.XWPF.UserModel;
using NPOI.OpenXmlFormats.Dml.WordProcessing;
using NPOI.OpenXmlFormats.Dml;
using Novacode;
namespace NPOI.Helper.Word
{
/// <summary>
/// Word幫助類
/// 建立日期:2017年6月2日
/// </summary>
public class WordHelper
{
/// <summary>
/// 建立Word(Office2003)
/// </summary>
/// <param name="strDataSourcePath">新建Word的路徑.doc</param>
/// <returns>成功返回true,失敗返回false</returns>
public static bool CreateWord_Office2003(string strDataSourcePath)
{
try
{
if (string.IsNullOrEmpty(strDataSourcePath))
{
return false;
}
XWPFDocument Word2003 = new XWPFDocument();
FileStream fileStream2003 = new FileStream(Path.ChangeExtension(strDataSourcePath, "doc"), FileMode.Create);
Word2003.Write(fileStream2003);
fileStream2003.Close();
Word2003.Close();
return true;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return false;
}
}
/// <summary>
/// 建立Word(Office2007)
/// </summary>
/// <param name="strDataSourcePath">新建Word的路徑.doc</param>
/// <returns>成功返回true,失敗返回false</returns>
public static bool CreateWord_Office2007(string strDataSourcePath)
{
try
{
if (string.IsNullOrEmpty(strDataSourcePath))
{
return false;
}
XWPFDocument Word2007 = new XWPFDocument();
FileStream fileStream2007 = new FileStream(Path.ChangeExtension(strDataSourcePath, "docx"), FileMode.Create);
Word2007.Write(fileStream2007);
fileStream2007.Close();
Word2007.Close();
return true;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return false;
}
}
/// <summary>
/// 獲得Word文件中所有段落
/// </summary>
/// <param name="strDataSourcePath">Word檔案路徑</param>
/// <returns>段落標籤List</returns>
public static List<string> GetWordParagraphText(string strDataSourcePath)
{
try
{
if (string.IsNullOrEmpty(strDataSourcePath) || !File.Exists(strDataSourcePath))
{
return null;
}
List<string> listParagraphText = new List<string>();
FileStream fileStream = new FileStream(strDataSourcePath, FileMode.Open, FileAccess.Read);
XWPFDocument wordDocument = new XWPFDocument(fileStream);
foreach (XWPFParagraph wordParagraph in wordDocument.Paragraphs)
{
listParagraphText.Add(wordParagraph.ParagraphText);
}
return listParagraphText;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return null;
}
}
/// <summary>
/// 獲得Word文件中所有表格
/// </summary>
/// <param name="strDataSourcePath">Word檔案路徑</param>
/// <returns>段落標籤List</returns>
public static List<string> GetWordTableText(string strDataSourcePath)
{
try
{
if (string.IsNullOrEmpty(strDataSourcePath) || !File.Exists(strDataSourcePath))
{
return null;
}
List<string> listTableText = new List<string>();
FileStream fileStream = new FileStream(strDataSourcePath, FileMode.Open, FileAccess.Read);
XWPFDocument wordDocument = new XWPFDocument(fileStream);
foreach (XWPFTable wordTable in wordDocument.Tables)
{
foreach (XWPFTableRow wordTableRow in wordTable.Rows)
{
foreach (XWPFTableCell wordTableCell in wordTableRow.GetTableCells())
{
foreach (XWPFParagraph wordParagraph in wordTableCell.Paragraphs)
{
listTableText.Add(wordParagraph.ParagraphText);
}
}
}
}
return listTableText;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return null;
}
}
/// <summary>
/// 獲得Word文件中所有文字
/// </summary>
/// <param name="strDataSourcePath">Word檔案路徑</param>
/// <returns>所有文字List</returns>
public static List<string> GetWordAllText(string strDataSourcePath)
{
try
{
if (string.IsNullOrEmpty(strDataSourcePath) || !File.Exists(strDataSourcePath))
{
return null;
}
List<string> listAllText = new List<string>();
List<string> listParagraphText = GetWordParagraphText(strDataSourcePath);
List<string> listTableText = GetWordTableText(strDataSourcePath);
if (listParagraphText != null && listParagraphText.Count >= 1)
{
listAllText = listAllText.Union(listParagraphText).ToList<string>();
}
if (listTableText != null && listTableText.Count >= 1)
{
listAllText = listAllText.Union(listTableText).ToList<string>();
}
return listAllText;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return null;
}
}
/// <summary>
/// 獲得Word文件中所有替換標籤('{標籤}','#標籤#')
/// </summary>
/// <param name="strDataSourcePath">Word檔案路徑</param>
/// <returns>替換標籤Dictionary(帶標籤,不帶標籤)</returns>
public static Dictionary<string, string> GetWordAllLable(string strDataSourcePath)
{
try
{
if (string.IsNullOrEmpty(strDataSourcePath) || !File.Exists(strDataSourcePath))
{
return null;
}
Dictionary<string, string> dicAllLabel = new Dictionary<string, string>();
List<string> listAllText = GetWordAllText(strDataSourcePath);
foreach (string strAllText in listAllText)
{
Dictionary<string, string> dicMatchingLabelK = MatchingReplaceLabel(strAllText, "{", "}");
Dictionary<string, string> dicMatchingLabelS = MatchingReplaceLabel(strAllText, "#", "#");
if (dicMatchingLabelK != null && dicMatchingLabelK.Count >= 1)
{
dicAllLabel = dicAllLabel.Union(dicMatchingLabelK).ToDictionary(k => k.Key, v => v.Value);
}
if (dicMatchingLabelS != null && dicMatchingLabelS.Count >= 1)
{
dicAllLabel = dicAllLabel.Union(dicMatchingLabelS).ToDictionary(k => k.Key, v => v.Value);
}
}
return dicAllLabel;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return null;
}
}
/// <summary>
/// 匹配替換標籤
/// </summary>
/// <param name="strText">需要匹配的文字</param>
/// <param name="strStartSymbol">起始符號</param>
/// <param name="strStopSymbol">終止符號</param>
/// <returns>匹配成功的標籤(帶標籤,不帶標籤)</returns>
public static Dictionary<string, string> MatchingReplaceLabel(string strText, string strStartSymbol, string strStopSymbol)
{
try
{
if (string.IsNullOrEmpty(strText))
{
return null;
}
Dictionary<string, string> dicMatchingLabel = new Dictionary<string, string>();
string strRegularExpression = string.Format(@"[^{0}]+(?=\{1})", strStartSymbol, strStopSymbol);
foreach (Match matchText in Regex.Matches(strText, strRegularExpression))
{
if (!dicMatchingLabel.ContainsKey(matchText.Value))
{
dicMatchingLabel.Add(string.Format("{0}{1}{2}", strStartSymbol, matchText.Value, strStopSymbol), string.Format("{0}", matchText.Value));
}
}
return dicMatchingLabel;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return null;
}
}
/// <summary>
/// 替換文字標籤
/// </summary>
/// <param name="strDataSourcePath">Word檔案路徑</param>
/// <param name="strLabelName">標籤名稱(帶標籤符號)</param>
/// <param name="strReplaceLabel">替換標籤文字</param>
/// <returns>成功返回替換數量,失敗返回-1</returns>
public static int ReplaceTextLabel(string strDataSourcePath, string strLabelName, string strReplaceLabel)
{
try
{
if (string.IsNullOrEmpty(strDataSourcePath) || !File.Exists(strDataSourcePath) || string.IsNullOrEmpty(strLabelName) || string.IsNullOrEmpty(strReplaceLabel))
{
return -1;
}
int iNumber = 0;
FileStream fileStreamOpen = new FileStream(strDataSourcePath, FileMode.Open, FileAccess.Read);
XWPFDocument wordDocument = new XWPFDocument(fileStreamOpen);
foreach (XWPFParagraph wordParagraph in wordDocument.Paragraphs)
{
if (wordParagraph.ParagraphText.IndexOf(strLabelName) >= 0)
{
string strReplaceTextLabel = wordParagraph.ParagraphText.Replace(strLabelName, strReplaceLabel);
foreach (XWPFRun wordRun in wordParagraph.Runs)
{
wordRun.SetText(string.Empty, 0);
}
wordParagraph.CreateRun().SetText(strReplaceTextLabel, 0);
iNumber++;
}
}
foreach (XWPFTable wordTable in wordDocument.Tables)
{
foreach (XWPFTableRow wordTableRow in wordTable.Rows)
{
foreach (XWPFTableCell wordTableCell in wordTableRow.GetTableCells())
{
foreach (XWPFParagraph wordParagraph in wordTableCell.Paragraphs)
{
if (wordParagraph.ParagraphText.IndexOf(strLabelName) >= 0)
{
string strReplaceTextLabel = wordParagraph.ParagraphText.Replace(strLabelName, strReplaceLabel);
foreach (XWPFRun wordRun in wordParagraph.Runs)
{
wordRun.SetText(string.Empty, 0);
}
wordParagraph.CreateRun().SetText(strReplaceTextLabel, 0);
iNumber++;
}
}
}
}
}
FileStream fileStreamSave = new FileStream(strDataSourcePath, FileMode.Create);
wordDocument.Write(fileStreamSave);
fileStreamSave.Close();
wordDocument.Close();
return iNumber;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return -1;
}
}
/// <summary>
/// 替換表格標籤(DataTable替換)
/// </summary>
/// <param name="strDataSourcePath">Word檔案路徑</param>
/// <param name="strLabelName">標籤名稱(帶標籤符號)</param>
/// <param name="dtReplaceLabel">替換標籤DataTable</param>
/// <returns>成功返回1,失敗返回-1</returns>
public static int ReplaceDataTableLabel(string strDataSourcePath, string strLabelName, DataTable dtReplaceLabel)
{
try
{
if (string.IsNullOrEmpty(strDataSourcePath) || !File.Exists(strDataSourcePath) || string.IsNullOrEmpty(strLabelName) || dtReplaceLabel == null || dtReplaceLabel.Rows.Count < 1)
{
return -1;
}
FileStream fileStreamOpen = new FileStream(strDataSourcePath, FileMode.Open, FileAccess.Read);
XWPFDocument wordDocument = new XWPFDocument(fileStreamOpen);
int iLableRowPosition = -1;
int iLableCellPosition = -1;
foreach (XWPFTable wordTable in wordDocument.Tables)
{
for (int iTableRow = 0; iTableRow < wordTable.Rows.Count; iTableRow++)
{
for (int iTableCell = 0; iTableCell < wordTable.Rows[iTableRow].GetTableCells().Count; iTableCell++)
{
foreach (XWPFParagraph wordParagraph in wordTable.Rows[iTableRow].GetTableCells()[iTableCell].Paragraphs)
{
if (wordParagraph.ParagraphText.IndexOf(strLabelName) >= 0)
{
if (iLableRowPosition < 0 && iLableCellPosition < 0)
{
iLableRowPosition = iTableRow;
iLableCellPosition = iTableCell;
}
}
if (iLableRowPosition >= 0 && iLableCellPosition >= 0)
{
int iCurrentRow = iTableRow - iLableRowPosition;
int iCurrentCell = iTableCell - iLableCellPosition;
if ((iCurrentRow < dtReplaceLabel.Rows.Count && iCurrentRow >= 0) && (iCurrentCell < dtReplaceLabel.Columns.Count && iCurrentCell >= 0))
{
foreach (XWPFRun wordRun in wordParagraph.Runs)
{
wordRun.SetText(string.Empty, 0);
}
wordParagraph.CreateRun().SetText(dtReplaceLabel.Rows[iCurrentRow][iCurrentCell].ToString(), 0);
}
}
}
}
}
}
FileStream fileStreamSave = new FileStream(strDataSourcePath, FileMode.Create);
wordDocument.Write(fileStreamSave);
fileStreamSave.Close();
wordDocument.Close();
return 1;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return -1;
}
}
/// <summary>
/// 替換圖片標籤(使用DocX.dll類庫,呼叫這個方法後NPOI無法讀取文件)
/// </summary>
/// <param name="strDataSourcePath">Word檔案路徑</param>
/// <param name="strLabelName">標籤名稱(帶標籤符號)</param>
/// <param name="strImagePath">替換的圖片路徑</param>
/// <param name="iImageWidth">替換的圖片寬度(小於0則顯示原圖寬度)</param>
/// <param name="iImageHeight">替換的圖片高度(小於0則顯示原圖高度)</param>
/// <returns>成功返回替換數量,失敗返回-1</returns>
public static int ReplaceImageLabel(string strDataSourcePath, string strLabelName, string strImagePath, int iImageWidth, int iImageHeight)
{
try
{
if (string.IsNullOrEmpty(strDataSourcePath) || !File.Exists(strDataSourcePath) || string.IsNullOrEmpty(strLabelName) || string.IsNullOrEmpty(strImagePath) || !File.Exists(strImagePath))
{
return -1;
}
int iNumber = 0;
//使用DocX.dll類庫
DocX mDocX = DocX.Load(strDataSourcePath);
//遍歷段落
foreach (Paragraph wordParagraph in mDocX.Paragraphs)
{
if (wordParagraph.Text.IndexOf(strLabelName) >= 0)
{
//新增圖片
Novacode.Image pImag = mDocX.AddImage(strImagePath);
Picture pPicture = pImag.CreatePicture();
//如果傳入寬度小於0,則以原始大小插入
if (iImageWidth >= 0)
{
pPicture.Width = iImageWidth;
}
//如果傳入高度小於0,則以原始大小插入
if (iImageHeight >= 0)
{
pPicture.Height = iImageHeight;
}
//將影像插入到段落後面
wordParagraph.InsertPicture(pPicture);
//清空文字(清空放在前面會導致替換失敗文字消失)
wordParagraph.ReplaceText(strLabelName, string.Empty);
iNumber++;
}
}
mDocX.SaveAs(strDataSourcePath);
return iNumber;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return -1;
}
}
/// <summary>
/// 替換Word文件標籤
/// </summary>
/// <param name="strDataSourcePath">Word檔案路徑</param>
/// <param name="listReplaceLabel">替換標籤對應的替換內容(不帶標籤,替換內容Model)</param>
/// <returns>成功返回替換行數,失敗返回-1</returns>
public static int ReplaceLabel(string strDataSourcePath, List<ReplaceLabelModel> listReplaceLabel)
{
try
{
if (string.IsNullOrEmpty(strDataSourcePath) || !File.Exists(strDataSourcePath) || listReplaceLabel == null || listReplaceLabel.Count < 1)
{
return -1;
}
int iNumber = 0;
//由於替換完圖片之後會導致NPOI無法讀取,所以暫時儲存替換圖片的標籤
Dictionary<string, ReplaceLabelModel> dicReplaceImageLabel = new Dictionary<string, ReplaceLabelModel>();
//遍歷文件中的標籤(先替換文字和表格)
foreach (var vAllLabel in GetWordAllLable(strDataSourcePath))
{
//獲得與文件中匹配的標籤
List<ReplaceLabelModel> listMatchingLabel = listReplaceLabel.Where(x => x.strLabelName == vAllLabel.Value).ToList();
if (listMatchingLabel.Count >= 1)
{
//使用第一個匹配到的標籤替換
ReplaceLabelModel ReplaceLabel = listMatchingLabel[0];
if (ReplaceLabel.lableType == ReplaceLabelType.LabelType.Text)
{
ReplaceTextLabel(strDataSourcePath, vAllLabel.Key, ReplaceLabel.strReplaceText);
iNumber++;
}
else if (ReplaceLabel.lableType == ReplaceLabelType.LabelType.DataTable)
{
ReplaceDataTableLabel(strDataSourcePath, vAllLabel.Key, ReplaceLabel.dtReplaceData);
iNumber++;
}
else if (ReplaceLabel.lableType == ReplaceLabelType.LabelType.Image)
{
dicReplaceImageLabel.Add(vAllLabel.Key, ReplaceLabel);
}
}
}
//替換圖片,這裡儲存的都是確定Word中存在標籤
foreach (var vReplaceLabel in dicReplaceImageLabel)
{
//預設原圖大小,替換前修改圖片大小
ReplaceImageLabel(strDataSourcePath, vReplaceLabel.Key, vReplaceLabel.Value.strReplaceImagePath, -1, -1);
iNumber++;
}
return iNumber;
}
catch (Exception ex)
{
TXTHelper.Logs(ex.ToString());
return -1;
}
}
}
}
相關文章
- 使用NPOI讀寫Excel、WordExcel
- 基於NPOI封裝匯出Excel方法封裝Excel
- DataGrid 匯出到 Excel 的幫助類Excel
- NPOI匯出和匯入Excel,Word和PDFExcel
- 使用WeihanLi.Npoi操作ExcelExcel
- 使用 SAP ABAP 封裝的 Office Integration class 訪問本地 Excel 檔案封裝Excel
- 使用POI讀寫word docx檔案
- Microsoft Office Word、Excel 和 PowerPoint 2007 檔案格式相容包 docx 開啟在office 2003中 docROSExcel
- asp.net 利用NPOI匯出Excel通用類ASP.NETExcel
- 使用Java poi編輯word.docx文件Java
- NPOI之使用EXCEL模板建立報表Excel
- java輔助開發的兩個封裝類Java封裝
- 字元操作普通幫助類字元
- 資料庫幫助類資料庫
- C# HTTP幫助類C#HTTP
- FMDB 二次封裝工具類，讓你快速學會封裝，整合資料庫封裝資料庫
- Excel操作-NPOI截圖Excel
- office 2003 excel插入word文件小記Excel
- MySQL幫助使用MySql
- Python Excel工具類封裝, 給excel表頭搞點顏色PythonExcel封裝
- Excelize - Golang 操作 Office Excel 文件類庫ExcelizeGolang
- Pgsql幫助類 netcoreSQLNetCore
- 關於DAO的封裝，請板橋幫助，歡迎大家討論封裝
- Flutter Dio二次封裝Flutter封裝
- axios二次封裝iOS封裝
- 二次封裝WebDriverWait封裝WebAI
- NPOI讀取Excel官方demoExcel
- .NET Core 2.0 開源Office元件 NPOI元件
- Vue二次封裝axios為外掛使用Vue封裝iOS
- win10系統office（word/excel）打字卡頓如何解決Win10Excel
- Web方式預覽Office/Word/Excel/pdf檔案解決方案WebExcel
- 利用Word來幫助再版書的校對翻譯
- axios的二次封裝iOS封裝
- elment dialog二次封裝封裝
- OpenResty Redis操作二次封裝RESTRedis封裝
- FMDB 二次封裝，面向模型封裝模型
- 如何優雅地使用幫助類檔案 helpers.phpPHP
- vue全家桶 ---axios的使用和二次封裝VueiOS封裝