关于.net导出数据到excel/word【占位符替换】
2024-08-24 15:39:58
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();//得到里面第一个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 = ; 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[])
{
getX = getX.Replace(item, getValue[]);
cell.SetCellValue(getX);
} } }
}
//删除没有的数据 此处是excel中需要替换的关键字，但是数据库替换中却没有的，用空值代替原来“[关键字]”
string getXNull = cell.StringCellValue;
MatchCollection listsCollection = reg.Matches(getXNull);
if (listsCollection.Count > )
{
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
{
}
}
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[];
string towValue = ToDBC(reA[]).Replace("\r", "<br/>");//\r和中文符号必须替换否则报错
doc.Range.Replace(oneValue, towValue, false, false);
}
doc.Save(savedFilePath);//也可以保存为1.doc 兼容03-07 #endregion }
catch (Exception ex)
{ throw;
}
}
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();
if (sheet != null && source != null && source.Rows.Count > )
{
IRow row; ICell cell;
//获取需插入数据的首行样式
IRow styleRow = sheet.GetRow(rowIndex);
if (styleRow == null)
{
for (int i = , len = source.Rows.Count; i < len; i++)
{
row = sheet.CreateRow(rowIndex);
//创建列并插入数据
for (int index = ; 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 = , len = source.Rows.Count; i < len; i++)
{
row = sheet.CreateRow(rowIndex);
row.HeightInPoints = styleRow.HeightInPoints;
row.Height = styleRow.Height;
//创建列并插入数据
for (int index = ; 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 - ], 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 "导出失败";
}
}
另外，需要引用的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;
4】关于换行符
此处 如果 Environment.NewLine 、<br/> \n 无效，则建议使用下列换行符 ，但先必须引用 using Aspose.Words;
using Aspose.Words;
换行符：ControlChar.LineBreak
列：string ts="测试1"+ControlChar.LineBreak +"测试2";
最新文章
- 用scikit-learn进行LDA降维
- Cocos2d Android 环境搭建
- MDK5 STM32编译问题汇总
- 利用CSOM向列表添加文件夹
- spring环境的搭建及作用和定义<一>
- ASP.NET获取客户端及服务器的信息
- javascript中创建插入元素
- 【转】提示框第三方库之MBProgressHUD iOS toast效果 动态提示框效果
- “微信应用号对行业影响”之一,app开发速来围观
- hihoCoder挑战赛14 A,B,C题解
- Swift - 纯代码实现页面segue跳转，以及参数传递
- PHP提取字符串中的所有汉字
- Python创建二维数组(关于list的一个小坑)
- mysql新建数据库，并设置charset为utf8，使用utf8_general_ci字符集校验结果
- List Set Map 的区别 用法以及特点（转载）
- 免费Git客户端：sourcetree详细介绍
- Linux 安装composer
- Chrome_查看 webSocket 连接信息
- [Oracle][DATAGUARD] PHYSICAL STANDBY环境里，11.2.0.4 , 也可以使用Pfile来运行Primary和Standby（虽然很少有人用）
- The declared package does not match the expected package Java