1】excel的占位符替換 效果如圖 關鍵代碼： ///savedFilePath需要保存的路徑 templateDocPath模板路徑 替換的關鍵字和值 格式 [姓名]$%$小王 public static void ReadExcel(string savedFilePath, string t ...
1】excel的占位符替換
效果如圖
關鍵代碼：
///savedFilePath需要保存的路徑 templateDocPath模板路徑 替換的關鍵字和值 格式 [姓名]$%$小王 public static void ReadExcel(string savedFilePath, string templateDocPath, List<string> ReArray) { try { //載入可讀可寫文件流 using (FileStream stream = new FileStream(templateDocPath, FileMode.Open, FileAccess.Read)) { IWorkbook workbook = WorkbookFactory.Create(stream);//使用介面，自動識別excel2003/2007格式 ISheet sheet = workbook.GetSheetAt(0);//得到裡面第一個sheet IRow row = null; ICell cell = null; //1讀取符合條件的 Regex reg = new Regex(@"\[\S+?\]", RegexOptions.Singleline); List<string> getList = new List<string>(); for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++) { row = sheet.GetRow(i); for (int j = row.FirstCellNum; j < row.LastCellNum; j++) { cell = row.GetCell(j); if (cell != null) { if (cell.CellType == NPOI.SS.UserModel.CellType.String) { var currentCellVal = cell.StringCellValue; if (reg.IsMatch(currentCellVal)) { MatchCollection listsCollection = reg.Matches(currentCellVal); for (int jNum = 0; jNum < listsCollection.Count; jNum++) { var aa = listsCollection[jNum].Value; getList.Add(aa); } } } } } } //2替換 for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++) { row = sheet.GetRow(i); for (int j = row.FirstCellNum; j < row.LastCellNum; j++) { cell = row.GetCell(j); if (cell != null) { foreach (var item in getList) { string getX = cell.StringCellValue; if (getX.Contains(item)) { foreach (var itemRa in ReArray) { var getValue = itemRa.Split(new string[] { "$%$" }, StringSplitOptions.None); if (item == getValue[0]) { getX = getX.Replace(item, getValue[1]); cell.SetCellValue(getX); } } } } //刪除沒有的數據 此處是excel中需要替換的關鍵字，但是資料庫替換中卻沒有的，用空值代替原來“[關鍵字]” string getXNull = cell.StringCellValue; MatchCollection listsCollection = reg.Matches(getXNull); if (listsCollection.Count > 0) { var valNull = getXNull; getXNull = getXNull.Replace(valNull, ""); cell.SetCellValue(getXNull); } } } } //新建一個文件流，用於替換後的excel保存文件。 FileStream success = new FileStream(savedFilePath, FileMode.Create); workbook.Write(success); success.Close(); } } catch (Exception ex) { } finally { } }View Code
2】word的占位符替換
/// <summary> /// world自定義模板導出 /// </summary> /// <param name="savedFilePath">保存路勁</param> /// <param name="templateDocPath">獲取模板的路徑</param> /// <param name="ReArray">需要替換的值 [姓名]$%$張三</param> /// public static void ReadWord(string savedFilePath, string templateDocPath, List<string> ReArray) { try { #region 進行替換 Aspose.Words.Document doc = new Aspose.Words.Document(templateDocPath); DocumentBuilder builder = new DocumentBuilder(doc); foreach (var item in ReArray) { var reA = item.Split(new string[] { "$%$" }, StringSplitOptions.None); string oneValue = reA[0]; string towValue = ToDBC(reA[1]).Replace("\r", "<br/>");//\r和中文符號必須替換否則報錯 doc.Range.Replace(oneValue, towValue, false, false); } doc.Save(savedFilePath);//也可以保存為1.doc 相容03-07 #endregion } catch (Exception ex) { throw; } }View Code
3】excel的占位符替換=》多欄位
效果圖
/// <summary> /// 根據模版導出Excel /// </summary> /// <param name="templateFile">模版路徑（包含尾碼） 例："/Template/Exceltest.xls"</param> /// <param name="strFileName">文件名稱（不包含尾碼） 例："Excel測試"</param> /// <param name="source">源DataTable</param> /// <param name="cellKes">需要導出的對應的列欄位 例：string[] cellKes = { "name","sex" };</param> /// <param name="rowIndex">從第幾行開始創建數據行,第一行為0</param> /// <returns>是否導出成功</returns> public static string ExportScMeeting(string templateFile, string strFileName, DataTable source, List<string> cellKes, int rowIndex) { templateFile = HttpContext.Current.Server.MapPath(templateFile); int cellCount = cellKes.Count();//總列數，第一列為0 IWorkbook workbook = null; try { using (FileStream file = new FileStream(templateFile, FileMode.Open, FileAccess.Read)) { workbook = WorkbookFactory.Create(file); //if (Path.GetExtension(templateFile) == ".xls") // workbook = new HSSFWorkbook(file); //else if (Path.GetExtension(templateFile) == ".xlsx") // workbook = new XSSFWorkbook(file); } ISheet sheet = workbook.GetSheetAt(0); if (sheet != null && source != null && source.Rows.Count > 0) { IRow row; ICell cell; //獲取需插入數據的首行樣式 IRow styleRow = sheet.GetRow(rowIndex); if (styleRow == null) { for (int i = 0, len = source.Rows.Count; i < len; i++) { row = sheet.CreateRow(rowIndex); //創建列並插入數據 for (int index = 0; index < cellCount; index++) { row.CreateCell(index) .SetCellValue(!(source.Rows[i][cellKes[index]] is DBNull) ? source.Rows[i][cellKes[index]].ToString() : string.Empty); } rowIndex++; } } else { for (int i = 0, len = source.Rows.Count; i < len; i++) { row = sheet.CreateRow(rowIndex); row.HeightInPoints = styleRow.HeightInPoints; row.Height = styleRow.Height; //創建列並插入數據 for (int index = 0; index < cellCount; index++) { var tx = source.Rows[i][cellKes[index]]; var tc = styleRow.GetCell(index).CellType; cell = row.CreateCell(index, styleRow.GetCell(index).CellType); cell.CellStyle = styleRow.GetCell(index).CellStyle; cell.SetCellValue(!(source.Rows[i][cellKes[index]] is DBNull) ? source.Rows[i][cellKes[index]].ToString() : string.Empty); } rowIndex++; } } } return NPOIExport(strFileName + "." + templateFile.Split('.')[templateFile.Split('.').Length - 1], workbook); } catch (Exception ex) { return ex.Message; } } public static string NPOIExport(string fileName, IWorkbook workbook) { try { System.IO.MemoryStream ms = new System.IO.MemoryStream(); workbook.Write(ms); HttpContext.Current.Response.Clear(); HttpContext.Current.Response.ClearHeaders(); HttpContext.Current.Response.Cache.SetCacheability(System.Web.HttpCacheability.Private); HttpContext.Current.Response.Buffer = true; HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8; HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", fileName)); HttpContext.Current.Response.ContentType = "application/ms-excel"; HttpContext.Current.Response.BinaryWrite(ms.ToArray()); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.End(); ms.Close(); ms.Dispose(); return "導出成功"; } catch (Exception ex) { return "導出失敗"; } }View Code
另外，需要引用的using也一同貼圖
using Aspose.Words; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using System; using System.Collections; using System.Collections.Generic; using System.Data; using System.IO; using System.Linq; using System.Reflection; using System.Text.RegularExpressions; using System.Web;
