java web根据excel word模板生成前台页面，自动求和

最新推荐文章于 2022-06-21 10:42:24 发布

println小强

最新推荐文章于 2022-06-21 10:42:24 发布

阅读量2.6k

点赞数

分类专栏：工作经验文章标签： java 文本编辑 poi excel word

本文链接：https://blog.csdn.net/sunguoqiang1213/article/details/53039857

版权

工作经验专栏收录该内容

45 篇文章 0 订阅

订阅专栏

</pre>项目要求根据excel制作前台，并得出结果，开始的时候想用table套嵌input接收数据，后台运算，或者jqurey计算，数了一下excel表，如果那样的话80多个字段，而且还只是一个表，一共三张表，请教大神。还真找到解决办法：<p></p><p>用到的工具：jsoup、poi、前台插件summernote（前台只要是自己用的顺手的富文本编辑器就可以）</p><p>基本思路：先根据excel或者doc生成html保存到项目，将excel或者doc模板保存到项目，将整个提交String数据提交到后台，交给jsoup处理，因为table无法加入数据，使用poi读取模板，将前台添加数据用jsoup拿出来，添加到模板，根据添加好的模板生成html，交给前台。</p><p></p><p>上代码，根据doc模板生成html</p><p>关闭输入输出流通用方法</p><p></p><pre name="code" class="html">    /** 
     * 关闭输入流 
     * @param is 
     */  
    private void closeStream(InputStream is) {  
       if (is != null) {  
          try {  
             is.close();  
          } catch (IOException e) {  
             e.printStackTrace();  
          }  
       }  
    }  
    
    /** 
     * 关闭输出流 
     * @param os 
     */  
    private void closeStream(OutputStream os) {  
       if (os != null) {  
          try {  
             os.close();  
          } catch (IOException e) {  
             e.printStackTrace();  
          }  
       }  
    }

根据doc生成html

  
<pre name="code" class="html">    /** 
     *  
     * @param path 所在的文件夹 
     * @param inFileName 输入Word文档 
     * @return String
     * @throws Throwable 
     */  
    public String wordToHtml(String path) throws Throwable { 
        return hWPFToHtml(wordToHWPF(path));
    }

/** * * @param path 所在的文件夹 * @param inFileName 输入Word文档 * @return HWPFDocument * @throws Throwable */ public HWPFDocument wordToHWPF(String path) throws Throwable { InputStream input = new FileInputStream(path); HWPFDocument wordDocument = new HWPFDocument(input); this.closeStream(input); return wordDocument; } /** * * @param wordDocument 输入Word文档 * @return String * @throws Throwable */ public String hWPFToHtml(HWPFDocument wordDocument) throws Throwable{ String path = ""; //根据自己需要设置path WordToHtmlConverter wordToHtmlConverter = new WordToHtmlConverter(DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument()); wordToHtmlConverter.setPicturesManager(new PicturesManager() { public String savePicture(byte[] content, PictureType pictureType, String suggestedName, float widthInches, float heightInches) { return suggestedName; } }); wordToHtmlConverter.processDocument(wordDocument); List<Picture> pics = wordDocument.getPicturesTable().getAllPictures(); if (pics != null) { for (int i = 0; i < pics.size(); i++) { Picture pic = (Picture) pics.get(i); try { pic.writeImageContent(new FileOutputStream(path + pic.suggestFullFileName())); } catch (FileNotFoundException e) { e.printStackTrace(); } } } Document htmlDocument = wordToHtmlConverter.getDocument(); ByteArrayOutputStream outStream = new ByteArrayOutputStream(); DOMSource domSource = new DOMSource(htmlDocument); StreamResult streamResult = new StreamResult(outStream); TransformerFactory tf = TransformerFactory.newInstance(); Transformer serializer = tf.newTransformer(); serializer.setOutputProperty(OutputKeys.ENCODING, "utf-8"); serializer.setOutputProperty(OutputKeys.INDENT, "yes"); serializer.setOutputProperty(OutputKeys.METHOD, "html"); serializer.transform(domSource, streamResult); String content = new String(outStream.toByteArray()); /*FileUtils.write(new File(path, "test.html"), content, "utf-8");*/ //如果需要生成html文件需接触注释 this.closeStream(outStream); return content; }

根据excel生成html

/** 
     *  
     * @param path 所在的文件夹 
     * @param inFileName 输入Excel文档 
     * @param outFileName 输出Html文档 
     * @throws Throwable 
     */ 
    public String  excelToHtml(String path,String inFileName) throws Throwable {  
          
        InputStream input = new FileInputStream(path +"/"+ inFileName);  
        HSSFWorkbook excelBook = new HSSFWorkbook(input);  
        this.closeStream(input);
        return hSSFWorkbookToHtml(excelBook);
    }  
    /** 
     *  
     * @param path 所在的文件夹 
     * @param excelBook 输入HSSFWorkbook 
     * @throws Throwable 
     */   
    public String hSSFWorkbookToHtml(HSSFWorkbook excelBook) throws Throwable{
    	String path = PcContextLintener.class.getResource("/").getPath().substring(1);
    	path = path.substring(0, path.length()-17);
    	resetCellFormula(excelBook);  
        ExcelToHtmlConverter excelToHtmlConverter = new ExcelToHtmlConverter(DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument());  
          
        //去掉Excel头行  
        //excelToHtmlConverter.OutputColumnHeaders = false;  
        excelToHtmlConverter.setOutputColumnHeaders(false);  
        //去掉Excel行号  
        excelToHtmlConverter.setOutputRowNumbers(false);  
        excelToHtmlConverter.processWorkbook(excelBook);  
        @SuppressWarnings("rawtypes")
		List pics = excelBook.getAllPictures();  
        if (pics != null) {  
            for (int i = 0; i < pics.size(); i++) {  
                Picture pic = (Picture) pics.get(i);  
                try {  
                    pic.writeImageContent(new FileOutputStream(path  
                            + pic.suggestFullFileName()));  
                } catch (FileNotFoundException e) {  
                    e.printStackTrace();  
                }  
            }  
        }  
        Document htmlDocument = excelToHtmlConverter.getDocument();  
        ByteArrayOutputStream outStream = new ByteArrayOutputStream();  
        DOMSource domSource = new DOMSource(htmlDocument);  
        StreamResult streamResult = new StreamResult(outStream);  
        TransformerFactory tf = TransformerFactory.newInstance();  
        Transformer serializer = tf.newTransformer();  
        serializer.setOutputProperty(OutputKeys.ENCODING, "utf-8");  
        serializer.setOutputProperty(OutputKeys.INDENT, "yes");  
        serializer.setOutputProperty(OutputKeys.METHOD, "html");  
        serializer.transform(domSource, streamResult);  
        String content = new String(outStream.toByteArray());  
        /*FileUtils.write(new File("path", "test.html"), content, "utf-8");*/  //需要生成html文件，接触注释
        this.closeStream(outStream);
        return content; 
    }

    /** 
     *  
     * 重新设置单元格计算公式 
     *  
     * */  
    public void resetCellFormula(HSSFWorkbook wb) {  
        HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(wb);  
        int sheetNum = wb.getNumberOfSheets();  
        for (int i = 0; i < sheetNum; i++) {  
            HSSFSheet sheet = wb.getSheetAt(i);  
            int rows = sheet.getLastRowNum() + 1;  
            for (int j = 0; j < rows; j++) {  
                HSSFRow row = sheet.getRow(j);  
                if (row == null)  
                    continue;  
                int cols = row.getLastCellNum();  
                for (int k = 0; k < cols; k++) {  
                    HSSFCell cell = row.getCell(k);  
                    if(cell!=null)  
                        System.out.println("cell["+j+","+k+"]=:"+cell.getCellType());  
                    if (cell == null)  
                        continue;  
                    if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {  
                        cell.setCellFormula(cell.getCellFormula());  
                        System.out.println("----公式："+cell.getCellFormula());  
                        cell=e.evaluateInCell(cell);  
                        System.out.println("-----------"+cell.getNumericCellValue());  
                    }  
                }  
            }  
        }  
    }

返回结果的工具类

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hwpf.HWPFDocument;
import org.apache.poi.hwpf.usermodel.Range;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.select.Elements;

import com.dykj.alespsystem.listener.PcContextLintener;

/**
 *@author 作者 :xiaoqiang
 *@version 创建时间 :
 *@modifyUser 修改人 :
 *@modifyDate 修改时间 :
 *@clazzExplain 类说明 :
 */
public class ReadHtml {
	/**
	 * 根据文件名生成html
	 */
	public String readByName(String fileName){
		return readHtml(readBypath(fileName));
	}
	/**
	 * 文件名和项目的物理地址生成文件的物理地址
	 */
	public String readBypath(String fileName){
		String path = "";         //项目地址
		path = path + File.separator +"download"+ File.separator + fileName;
		return path;
	}
	/**
	 * 根据项目中中html模板生成返回前台的String
	 */
	private String readHtml(String path) {
		byte[] b = null;
		int size = 0;
		try {
			InputStream is = new FileInputStream(path);
			b = new byte[102400];
			size = is.read(b);
			is.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return new String(b,0,size);
	}
	/**
	 * 根据项目中的excel模板和前台传回数据生成HSSFWorkbook模板
         * @param path 所在的文件夹 
         * @param modelName项目中excel模板名称
         * @param html前台传来的String数据
<pre name="code" class="java">        * @return HWPFDocument

* @throws Throwable
*/public HSSFWorkbook addExcel(String modelName,String html) throws Throwable{HSSFWorkbook work = null;String modelPath = readBypath(modelName);try {InputStream in = new FileInputStream(modelPath);work = new HSSFWorkbook(in);Sheet sheet = work.getSheetAt(0); Document doc = Jsoup.parse(html); Elements trs = doc.select("table").select("tr"); for(int i = 0;i<trs.size();i++){ Elements tds = trs.get(i).select("td"); Row row = sheet.getRow(i); for(int j = 0;j<tds.size();j++){ String text = tds.get(j).text(); Cell cell = row.getCell(j); //由于excel格式问题对《河北省行政强制统计表model.xls》进行处理 if(modelName=="河北省行政强制统计表model.xls"&&i>=10&&j>1){ break; } //过滤excel格式 if(modelName=="河北省行政强制统计表model.xls"&&2<i&&i<6){ break; } if(modelName=="河北省行政处罚统计表model.xls"&&2<i&&i<9){ break; } cell.setCellValue(text); } } in.close();} catch (FileNotFoundException e) {// TODO Auto-generated catch blocke.printStackTrace();}catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}return work;}

        <pre name="code" class="java"><pre name="code" class="java">        /**
	 * 根据前台数据，项目中的excel模板生成计算好的String结果
         * @param add 需要求和的行号
         * @param addb 第二个需要求和的行号
         * @param adds 结果行号
         * @param work 生成的没有结果的HSSFWorkbook模<pre name="code" class="java">        * @return HSSFWorkbook

* @throws Throwable
*/private HSSFWorkbook tableAdd(int i,int k,int s,HSSFWorkbook work) throws Throwable{ PoiToHtmlUtil poiToHtmlUtil = new PoiToHtmlUtil(); Document doc = Jsoup.parse(poiToHtmlUtil.hSSFWorkbookToHtml(work)); Elements trs = doc.select("table").select("tr"); Elements tds = trs.get(i).select("td"); Elements tdsk = trs.get(k).select("td"); Sheet sheet = work.getSheetAt(0); Row row = sheet.getRow(s); for(int j = 2;j<tds.size()-1;j++){ String text = tds.get(j).text(); String textk = tdsk.get(j).text(); String add = bigDecimalAdd(text,textk); Cell cell = row.getCell(j); cell.setCellValue(add); } return work;}

        /**
	 * 根据前台数据，项目中的excel模板生成计算好的String结果
         * @param add 需要求和的行号
         * @param addb 第二个需要求和的行号
         * @param adds 结果行号
         * @param modelName 项目中excel模板名称
        * @param html前台传来的String数据
<pre name="code" class="java">        * @return String

* @throws Throwable
*/

public String addHtml(int add,int addb, int adds,String modelName,String html) throws Throwable{PoiToHtmlUtil poiToHtmlUtil = new PoiToHtmlUtil();Document doc=Jsoup.parse(html);//去掉多余占位符String str=doc.getElementsByTag("html").get(0).html().replace(" ","");//返回计算好的String结果String ss = poiToHtmlUtil.hSSFWorkbookToHtml(tableAdd(add,addb,adds,addExcel(modelName,str)));//生成的html有一个没用的h2标题如果没有可以直接返回ssdoc = Jsoup.parse(ss);doc.getElementsByTag("h2").get(0).remove();String s = doc.getElementsByTag("html").get(0).html();return s;}/***************************************************************/分割线/**************************************************************************///下班时doc求和

<pre name="code" class="java"><pre name="code" class="java"><pre name="code" class="java">        /**
	 * 求行结果
         * @param i 需要求和的行号
         * @param html 前台传来的数据<pre name="code" class="java">        * @return String

public String addTd(int i,String html){Document doc = Jsoup.parse(html);doc.getElementsByTag("html").get(0).html().replace(" ","");doc.getElementsByTag("html").get(0).html().replace(" ","");String texts = null;Elements trs = doc.select("table").select("tr");Elements tds = trs.get(i).select("td");for(int j = 1;j<tds.size()-1;j++){String text = tds.get(j).text();texts = bigDecimalAdd(text,texts);}return texts;}

<pre name="code" class="java"><pre name="code" class="java"><pre name="code" class="java"><pre name="code" class="java">        /**
	 * 求列结果
         * @param i 需要求和的列号
         * @param html 前台传来的数据<pre name="code" class="java">        * @return String

public String addTr(int j,String html){Document docs = Jsoup.parse(html);String str = docs.getElementsByTag("html").get(0).html().replace(" ","");Document doc = Jsoup.parse(str);String texts = null;Elements trs = doc.select("table").select("tr");for(int i = 1;i<trs.size()-1;i++){ Elements tds = trs.get(i).select("td"); String text = tds.get(j).text();texts = bigDecimalAdd(text,texts);}return texts;}

<pre name="code" class="java"><pre name="code" class="java"><pre name="code" class="java"><pre name="code" class="java"><pre name="code" class="java">        /**
	 * 返回带结果的模板
         * @param wordDocument 根据项目中的doc模板生成的HWPFDocument 
<pre name="code" class="java">         * @param html 前台传来的数据

        * @return HWPFDocument

public HWPFDocument docWrite(HWPFDocument wordDocument,String html){ Document doc = Jsoup.parse(html); Range range = wordDocument.getRange(); String title = doc.getElementsByClass("p1").get(0).getElementsByTag("span").get(0).html(); String subtitle = doc.getElementsByClass("p2").get(0).getElementsByTag("span").get(0).html(); String preparer = doc.getElementsByClass("s1").get(0).html(); String tableMan = doc.getElementsByClass("s1").get(1).html(); String tel = doc.getElementsByClass("s1").get(2).html(); String datas = doc.getElementsByClass("s1").get(3).html(); range.replaceText("${title}", title); range.replaceText("${subtitle}", subtitle); preparer = isnull(preparer); range.replaceText("${preparer}", preparer); tableMan = isnull(tableMan); range.replaceText("${tableMan}", tableMan); tel = isnull(tel); range.replaceText("${tel}", tel); datas = isnull(datas); range.replaceText("${datas}", datas); for(int i = 1; i<7;i++){ String addtr = addTr(i,html); if(addtr!=null&&!addtr.equals("")){ range.replaceText("${td"+i+"}", addtr); }else{ range.replaceText("${td"+i+"}", " "); } } for(int i = 1; i<9;i++){ String addtd = addTd(i,html); if(addtd!=null&&!addtd.equals("")){ range.replaceText("${tr"+i+"}", addtd); }else{ range.replaceText("${tr"+i+"}", " "); } }return docHtml(wordDocument,html); }

<pre name="code"><pre name="code"><pre name="code"><pre name="code"><pre name="code"><pre name="code">        /**	
         * 返回计算好的结果   
         * @param fileName根据项目中的doc模板名 <pre name="code">         * @param html 前台传来的数据

        * @return String

public String addDocHtml(String fileName,String html) throws Throwable{PoiToHtmlUtil poiToHtmlUtil = new PoiToHtmlUtil();HWPFDocument wordDocument = poiToHtmlUtil.wordToHWPF(readBypath(fileName));HWPFDocument addDoc = docWrite(wordDocument,html);return poiToHtmlUtil.hWPFToHtml(addDoc); }

<pre name="code"><pre name="code"><pre name="code"><pre name="code"><pre name="code"><pre name="code"><pre name="code">        /**	
         * 返回计算好的结果的HWPFDocument 
<pre name="code" class="java">         * @param fileName根据项目中的doc模板

         * @param html 前台传来的数据

        * @return String

public HWPFDocument addHWPFDocument(String fileName,String html) throws Throwable{PoiToHtmlUtil poiToHtmlUtil = new PoiToHtmlUtil();HWPFDocument wordDocument = poiToHtmlUtil.wordToHWPF(readBypath(fileName));return wordDocument; }/** * java精确计算 */private String bigDecimalAdd(String i,String j){if(i==null||i.equals("")||i.hashCode()==160){return j;}else if(j==null||j.equals("")||j.hashCode()==160){return i;}else{BigDecimal bd1 = new BigDecimal(i);BigDecimal bd2 = new BigDecimal(j);return (bd1.add(bd2).stripTrailingZeros().toPlainString());}}

<pre name="code" class="java"><pre name="code"><pre name="code"><pre name="code"><pre name="code"><pre name="code"><pre name="code"><pre name="code">        /**	
         * 如果前台有空数据使用"_"代替 
<pre name="code" class="java">         * @param name需要判断的String

        * @return String

private String isnull(String name){if(name==null||name.equals("")){return "_";}else{return name;}}

<pre name="code" class="java"><pre name="code"><pre name="code"><pre name="code"><pre name="code"><pre name="code"><pre name="code"><pre name="code">        /**	
         * 将前台的表格数据加入已经得到计算结果的模板中 
<pre name="code" class="java">         * @param wordDocument存有计算结果的模板

         * @param html 前台传来的数据

        * @return HWPFDocument

private HWPFDocument docHtml(HWPFDocument wordDocument,String html){Range range = wordDocument.getRange();Document doc = Jsoup.parse(html);doc.getElementsByTag("html").get(0).html().replace(" ","");Elements trs = doc.select("table").select("tr");for(int i = 1;i<trs.size()-1;i++){Elements tds = trs.get(i).select("td");for(int j = 1;j<tds.size()-1;j++){ String text = tds.get(j).text(); String name = isnull(text); range.replaceText("${trd"+i+j+"}", name);}}return wordDocument;}}

前台jsp代码

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
//引入前台插件
<script type="text/javascript" src="../summernote.min.js"></script>
<script type="text/javascript" src="../summernote-zh-CN.js"></script>
<script type="text/javascript">
$(function(){
              //前台富文本插件，去掉所有按钮         
             $('.summernote').summernote({
		 height: 1000, // 默认高度
		 lang:"zh-CN",//设置中文
		 toolbar: [["view", ["fullscreen"/* , "codeview" */]],
		           ["help", ["help"]]
		 ]
		 });
});
//纠偏方法
function rectifying(){
	var sHTML = $('.summernote').code();
	$("#summernote").val(sHTML);
	 var data = $("#commentForm").serialize();
	$.ajax({
		type : "post",
		url : rectifyingUrl+"?t="+Math.random(),//后台地址
		data : data,
		dataType : 'json',
		async : false,
		success : function(data) {
	        if(data.status){
	        	$('#summernote').html(data);
	        	$('.summernote').code(data);
	            }
		},  
	});
}
</script>
</head>
<body>
    <form>
        <div class="form-group">
        <input id="summernote" type="hidden">
        <div class="summernote" id="summernote" style="width:100%"> 
                  //后台传来的html数据
        </div>
     </form>
     <input type="button" οnclick="rectifying()" value="纠偏" /> 
</body>
</html>

本人技术不是很好，大牛看见别笑话，大神有什么意见欢迎指点