欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-04-08 17:40:08
1】excel的占位符替换 效果如图 关键代码: ///savedFilePath需要保存的路径 templateDocPath模板路径 替换的关键字和值 格式 [姓名]$%$小王 public static void ReadExcel(string savedFilePath, string t ......

1】excel的占位符替换

效果如图

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

 

关键代码:

关于.net导出数据到excel/word【占位符替换】
///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的占位符替换

关于.net导出数据到excel/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的占位符替换=》多字段

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

关于.net导出数据到excel/word【占位符替换】
        /// <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;