关于.net导出数据到excel/word【占位符替换】

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;