poi生成word文档,word表格,将echar报表生成到word
项目中用到生成word报表,报表中有表格的合并 、页眉、表格中会有报表图片。然后查找了网上的资料,利用echar生成柱状图,然后已base64串的方式发给后台,在后台解析成字节数组 ,利用poi生成到word文档中。
先来张效果图:
代码下载地址:
代码下载地址
项目的主要代码:
pom.xml 主要需要引入
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
</dependencies>
java代码:
EchartsController.java
package com.lyq.web;
import com.lyq.util.ExportWordUtil;
import org.apache.commons.io.FileUtils;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import sun.misc.BASE64Decoder;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Map;
/**
*
*
* 创建人:lyq
* 创建时间:2017-9-13 下午12:49:52
* 修改备注:
* @version
*
*/
@Controller
@RequestMapping("/echarts")
public class EchartsController {
private static final Logger logger = LoggerFactory.getLogger(EchartsController.class);
@RequestMapping(value="/viewEcharts")
@ResponseBody
public Map<String, Object> echartsMap(HttpServletRequest request, HttpServletResponse response) {
Map<String, Object> map = new HashMap<String, Object>();
map.put("category", new String[]{"70年代人数","80年代人数","90年代人数","00年代人数"});
map.put("child", new int[] {10000,20000,30000,40000});
map.put("old", new int[] {30000,25000,20000,15000});
map.put("man", new int[]{1000000,1500000,1550000,1600000});
return map;
}
@RequestMapping("/goEchart")
public String goEchart(HttpServletRequest request, HttpServletResponse response) {
return "/echart";
}
/**
* 报警分析报表下载
*/
@RequestMapping("/exportReport")
@ResponseBody
public Map<String,String> exportReport(HttpServletResponse response, HttpServletRequest request, String picBase64Info1) {
Map<String,String> map = new HashMap();
try {
byte[] base64Info1 = decodeBase64(picBase64Info1);
XWPFDocument xdoc = new ExportWordUtil().export(base64Info1);
Calendar c = Calendar.getInstance();
String fileName = "生成分析报告" + c.get(Calendar.YEAR) + to2String(String.valueOf((c.get(Calendar.MONTH) + 1)))
+ c.get(Calendar.DAY_OF_MONTH) + c.get(Calendar.HOUR_OF_DAY) + c.get(Calendar.MILLISECOND) + ".docx";
//获取存放路径
String classPath = FileUtils.class.getClassLoader().getResource("/").getPath();
String os_name = System.getProperties().get("os.name").toString().toLowerCase();
if (os_name.indexOf("windows") != -1) {
classPath = classPath.substring(1, classPath.indexOf("/WEB-INF/classes")) + "/upload/";
} else if (os_name.indexOf("linux") != -1) {
classPath = FileUtils.class.getProtectionDomain().getCodeSource().getLocation().getPath();
classPath = "/" + classPath.substring(1, classPath.indexOf("/WEB-INF/")) + "/upload/";
}
logger.info("==============report================" + classPath + fileName);
FileOutputStream fos = new FileOutputStream(classPath + fileName);
xdoc.write(fos);
fos.close();
map.put("ret", "/upload/" + fileName);
return map;
} catch (Exception e) {
logger.error(e.getMessage(), e);
map.put("ret", "faild");
return map;
}
}
/**
* 解析base64,返回图片所在路径
*
* @param base64Info
* @return
*/
private byte[] decodeBase64(String base64Info) {
if (StringUtils.isEmpty(base64Info)) {
return null;
}
BASE64Decoder decoder = new BASE64Decoder();
if (!base64Info.contains("base64,"))
return null;
String[] arr = base64Info.split("base64,");
// 数据中:data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAABI4AAAEsCAYAAAClh/jbAAA
// ... 在"base64,"之后的才是图片信息
try {
return decoder.decodeBuffer(arr[1]);
} catch (IOException e) {
logger.info(e.getMessage(), e);
return null;
}
}
private String to2String(String str) {
if (str.length() > 2) {
str = str.substring(0, 2);
} else {
str = "0" + str;
}
return str;
}
}
ExportWordUtil.java
/** * */ package com.lyq.util; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.xwpf.model.XWPFHeaderFooterPolicy; import org.apache.poi.xwpf.usermodel.*; import org.openxmlformats.schemas.wordprocessingml.x2006.main.*; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import javax.imageio.stream.FileImageInputStream; import java.io.ByteArrayOutputStream; import java.io.File; import java.io.FileNotFoundException; import java.io.IOException; import java.math.BigInteger; import java.text.DecimalFormat; /** * 导出Excel公共方法 * * @author lyq */ public class ExportWordUtil { private static final Logger logger = LoggerFactory.getLogger(ExportWordUtil.class); /** * 导出word * @param base64Info1 报表图片数据 * @return */ public XWPFDocument export(byte[] base64Info1) { try { CustomXWPFDocument xdoc = new CustomXWPFDocument(); // 创建页眉 createCtp(xdoc); // 标题 createTitle(xdoc); XWPFTable dTable = xdoc.createTable(4, 3); createBaseInfoTable(dTable, xdoc, "未来科技", "谢谢侬", "1024", "生成报表201709120056251"); // 标题一、未来科技数据统计分析 createTitle(xdoc, "一、 未来科技数据统计分析"); // 报表数据分析 XWPFTable dataReportTable = xdoc.createTable(4, 2); createDataReportTable(dataReportTable, xdoc, base64Info1); return xdoc; } catch (Exception e) { logger.error(e.getMessage(), e); throw new RuntimeException("生成文件失败"); } } /** * 在cell 里面插入图片 * @param xdoc * @param paragraph * @param imageByte */ private void createPic(CustomXWPFDocument xdoc, XWPFParagraph paragraph, byte[] imageByte) { try { xdoc.addPictureData(imageByte, XWPFDocument.PICTURE_TYPE_JPEG); } catch (InvalidFormatException e) { e.printStackTrace(); } xdoc.createPicture(paragraph, xdoc.getAllPictures().size() - 1, 300, 200, " "); } // 图片到byte数组 public byte[] image2byte(String path) { byte[] data = null; FileImageInputStream input = null; try { input = new FileImageInputStream(new File(path)); ByteArrayOutputStream output = new ByteArrayOutputStream(); byte[] buf = new byte[1024]; int numBytesRead = 0; while ((numBytesRead = input.read(buf)) != -1) { output.write(buf, 0, numBytesRead); } data = output.toByteArray(); output.close(); input.close(); } catch (FileNotFoundException ex1) { ex1.printStackTrace(); } catch (IOException ex1) { ex1.printStackTrace(); } return data; } /** * 创建标题 */ private void createTitle(CustomXWPFDocument xdoc) { // 标题 XWPFParagraph titleMes = xdoc.createParagraph(); titleMes.setAlignment(ParagraphAlignment.CENTER); XWPFRun r1 = titleMes.createRun(); r1.setBold(true); r1.setFontFamily("华文仿宋"); r1.setText("未来科技平台统计分析报告");// 活动名称 r1.setFontSize(18); r1.setColor("333333"); r1.setBold(true); } /** * 生成页眉 */ public void createCtp(CustomXWPFDocument document) { CTSectPr sectPr = document.getDocument().getBody().addNewSectPr(); XWPFHeaderFooterPolicy policy = new XWPFHeaderFooterPolicy(document, sectPr); // 添加页眉 CTP ctpHeader = CTP.Factory.newInstance(); CTR ctrHeader = ctpHeader.addNewR(); CTText ctHeader = ctrHeader.addNewT(); String headerText = "我的报表我做主 在我的地盘听我的"; ctHeader.setStringValue(headerText); XWPFParagraph headerParagraph = new XWPFParagraph(ctpHeader, document); // 设置为左对齐 headerParagraph.setAlignment(ParagraphAlignment.BOTH); XWPFParagraph[] parsHeader = new XWPFParagraph[1]; parsHeader[0] = headerParagraph; try { policy.createHeader(XWPFHeaderFooterPolicy.DEFAULT, parsHeader); } catch (IOException e) { e.printStackTrace(); } } /** * 生成基础信息Table * * @param table * @param xdoc */ public void createBaseInfoTable(XWPFTable table, CustomXWPFDocument xdoc, String dateStr, String unitName, String machineNum, String reportNo) { String bgColor = "111111"; CTTbl ttbl = table.getCTTbl(); CTTblPr tblPr = ttbl.getTblPr() == null ? ttbl.addNewTblPr() : ttbl.getTblPr(); CTTblWidth tblWidth = tblPr.isSetTblW() ? tblPr.getTblW() : tblPr.addNewTblW(); tblWidth.setW(new BigInteger("8600")); tblWidth.setType(STTblWidth.AUTO); // STTblWidth.AUTO 自动长度 mergeCellsVertically(table, 0, 0, 3); setCellText(xdoc, getCellHight(table, 0, 0, 2400), "基 础 信 息", bgColor, 600, 14, "仿宋"); setCellText(xdoc, getCellHight(table, 0, 1, 600), "报告周期", bgColor, 1800, 14, "仿宋"); setCellText(xdoc, getCellHight(table, 0, 2, 600), dateStr, bgColor, 6200, 14, "仿宋"); setCellText(xdoc, getCellHight(table, 1, 1, 600), "单位名称", bgColor, 1800, 14, "仿宋"); if (unitName == null) unitName = ""; setCellText(xdoc, getCellHight(table, 1, 2, 600), unitName, bgColor, 6200, 14, "仿宋"); setCellText(xdoc, getCellHight(table, 2, 1, 600), "主机数量", bgColor, 1800, 14, "仿宋"); setCellText(xdoc, getCellHight(table, 2, 2, 600), machineNum, bgColor, 6200, 14, "仿宋"); setCellText(xdoc, getCellHight(table, 3, 1, 600), "报告编号", bgColor, 1800, 14, "仿宋"); setCellText(xdoc, getCellHight(table, 3, 2, 600), reportNo, bgColor, 6200, 14, "仿宋"); } /** * 生成标题 * * @param xdoc * @param titleText */ public void createTitle(CustomXWPFDocument xdoc, String titleText) { XWPFParagraph headLine2 = xdoc.createParagraph(); headLine2.setAlignment(ParagraphAlignment.CENTER); XWPFRun runHeadLine2 = headLine2.createRun(); runHeadLine2.setText(titleText); runHeadLine2.setFontSize(16); runHeadLine2.setFontFamily("华文仿宋"); runHeadLine2.setBold(true); runHeadLine2.setColor("333333"); } /** * 报表数据分析 * * @param table * @param xdoc */ public void createDataReportTable(XWPFTable table, CustomXWPFDocument xdoc, byte[] base64Info1) { String bgColor = "111111"; CTTbl ttbl = table.getCTTbl(); CTTblPr tblPr = ttbl.getTblPr() == null ? ttbl.addNewTblPr() : ttbl.getTblPr(); CTTblWidth tblWidth = tblPr.isSetTblW() ? tblPr.getTblW() : tblPr.addNewTblW(); tblWidth.setW(new BigInteger("8600")); tblWidth.setType(STTblWidth.AUTO); // STTblWidth.AUTO 自动长度 // mergeCellsVertically(table, 0, 0, 3); mergeCellsHorizontal(table, 0, 0, 1); String str = ""; Double sss = 0.6666666666666666; DecimalFormat df = new DecimalFormat("0.00"); str = "(一)报告时间内误报率"+df.format((sss * 100))+"%"; setCellText(xdoc, getCellHight(table, 0, 0, 1200), str, bgColor, 8600, 14, "仿宋"); setCellText(xdoc, getCellHight(table, 1, 0, 1200), "报表数据", bgColor, 4300, 14, "仿宋"); setCellText(xdoc, getCellHight(table, 1, 1, 1200), "报表数据", bgColor, 4300, 14, "仿宋"); // 自动报警数量环比 报表 if(base64Info1 == null || base64Info1.length < 100){ setCellText(xdoc, getCellHight(table, 2, 0, 1200), "暂无数据", bgColor, 4300, 14, "仿宋"); } else { setCellPic(xdoc, getCellHight(table, 2, 0, 1200), base64Info1); } // 自动报警数量同比 报表 if(base64Info1 == null || base64Info1.length < 100){ setCellText(xdoc, getCellHight(table, 2, 1, 1200), "暂无数据", bgColor, 4300, 14, "仿宋"); } else { setCellPic(xdoc, getCellHight(table, 2, 1, 1200), base64Info1); } mergeCellsHorizontal(table, 3, 0, 1); setCellText(xdoc, getCellHight(table, 3, 0, 1200), "数据汇总\n1:@@@@@@@@@@@@@@@@@@@@@\n2!!!!!!!!!!!!!!!!!!!!!!!!", bgColor, 4300, 14, "仿宋", ParagraphAlignment.LEFT,true); } // 设置表格高度 private XWPFTableCell getCellHight(XWPFTable xTable, int rowNomber, int cellNumber, int hight) { XWPFTableRow row = null; row = xTable.getRow(rowNomber); row.setHeight(hight); XWPFTableCell cell = null; cell = row.getCell(cellNumber); cell.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER); return cell; } /** * 创建图片 */ private void setCellPic(CustomXWPFDocument xdoc, XWPFTableCell cell, byte[] imageByte) { createPic(xdoc, cell.addParagraph(), imageByte); } private void setCellText(CustomXWPFDocument xDocument, XWPFTableCell cell, String text, String bgcolor, int width, int fontSize, String textType) { setCellText(xDocument, cell, text, bgcolor, width, fontSize, textType, ParagraphAlignment.CENTER); } /** * * @param xDocument * @param cell * @param text * @param bgcolor * @param width */ private void setCellText(CustomXWPFDocument xDocument, XWPFTableCell cell, String text, String bgcolor, int width, int fontSize, String textType, ParagraphAlignment align) { setCellText(xDocument, cell, text, bgcolor, width, fontSize, textType, align, false); } private void setCellText(CustomXWPFDocument xDocument, XWPFTableCell cell, String text, String bgcolor, int width, int fontSize, String textType, ParagraphAlignment align, boolean isBold) { CTTc cttc = cell.getCTTc(); CTTcPr cellPr = cttc.addNewTcPr(); cellPr.addNewTcW().setW(BigInteger.valueOf(width)); XWPFParagraph pIO = cell.addParagraph(); if (null == align) { pIO.setAlignment(ParagraphAlignment.CENTER); } else { pIO.setAlignment(align); } cell.removeParagraph(0); if (text.contains("\n")) { String[] myStrings = text.split("\n"); for (int i = 0; i < myStrings.length; i++) { String temp = myStrings[i]; if (isBold) { if (i == 0) { setTextStyle(pIO, textType, bgcolor, fontSize, temp, true, true); } else { setTextStyle(pIO, textType, bgcolor, fontSize, " " + temp, true, false); } } else { setTextStyle(pIO, textType, bgcolor, fontSize, temp, true, false); } } } else { setTextStyle(pIO, textType, bgcolor, fontSize, text, false, false); } } private void setTextStyle(XWPFParagraph pIO, String textType, String bgcolor, int fontSize, String text, boolean isEntery, boolean isBold) { XWPFRun rIO = pIO.createRun(); if (textType == null || textType.equals("")) { rIO.setFontFamily("微软雅黑"); } else { rIO.setFontFamily(textType); } if (bgcolor == null || bgcolor.equals("")) { rIO.setColor("000000"); } else { rIO.setColor(bgcolor); } rIO.setFontSize(fontSize); rIO.setText(text); if (isBold) rIO.setBold(true); if (isEntery) rIO.addBreak(); } // 设置表格间的空行 public void setEmptyRow(CustomXWPFDocument xdoc, XWPFRun r1) { XWPFParagraph p1 = xdoc.createParagraph(); p1.setAlignment(ParagraphAlignment.CENTER); p1.setVerticalAlignment(TextAlignment.CENTER); r1 = p1.createRun(); } // word跨列合并单元格 public void mergeCellsHorizontal(XWPFTable table, int row, int fromCell, int toCell) { for (int cellIndex = fromCell; cellIndex <= toCell; cellIndex++) { XWPFTableCell cell = table.getRow(row).getCell(cellIndex); if (cellIndex == fromCell) { // The first merged cell is set with RESTART merge value cell.getCTTc().addNewTcPr().addNewHMerge().setVal(STMerge.RESTART); } else { // Cells which join (merge) the first one, are set with CONTINUE cell.getCTTc().addNewTcPr().addNewHMerge().setVal(STMerge.CONTINUE); } } } // word跨行并单元格 public void mergeCellsVertically(XWPFTable table, int col, int fromRow, int toRow) { for (int rowIndex = fromRow; rowIndex <= toRow; rowIndex++) { XWPFTableCell cell = table.getRow(rowIndex).getCell(col); if (rowIndex == fromRow) { // The first merged cell is set with RESTART merge value cell.getCTTc().addNewTcPr().addNewVMerge().setVal(STMerge.RESTART); } else { // Cells which join (merge) the first one, are set with CONTINUE cell.getCTTc().addNewTcPr().addNewVMerge().setVal(STMerge.CONTINUE); } } } }
CustomXWPFDocument.java
echar.jsp代码:package com.lyq.util; /** * Created by lyq on 2017/9/7. * POI 导出图片bug修复 */ import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.xwpf.usermodel.XWPFDocument; import org.apache.poi.xwpf.usermodel.XWPFParagraph; import org.apache.xmlbeans.XmlException; import org.apache.xmlbeans.XmlToken; import org.openxmlformats.schemas.drawingml.x2006.main.CTNonVisualDrawingProps; import org.openxmlformats.schemas.drawingml.x2006.main.CTPositiveSize2D; import org.openxmlformats.schemas.drawingml.x2006.wordprocessingDrawing.CTInline; import java.io.IOException; import java.io.InputStream; /** * */ public class CustomXWPFDocument extends XWPFDocument { public CustomXWPFDocument(InputStream in) throws IOException { super(in); } public CustomXWPFDocument() { super(); } /** * @param pkg * @throws IOException */ public CustomXWPFDocument(OPCPackage pkg) throws IOException { super(pkg); } // picAttch 图片后面追加的字符串 可以是空格 public void createPicture(XWPFParagraph paragraph,int id, int width, int height,String picAttch) { final int EMU = 9525; width *= EMU; height *= EMU; String blipId = getAllPictures().get(id).getPackageRelationship() .getId(); CTInline inline = paragraph.createRun().getCTR() .addNewDrawing().addNewInline(); paragraph.createRun().setText(picAttch); String picXml = "" + "<a:graphic xmlns:a=\"http://schemas.openxmlformats.org/drawingml/2006/main\">" + " <a:graphicData uri=\"http://schemas.openxmlformats.org/drawingml/2006/picture\">" + " <pic:pic xmlns:pic=\"http://schemas.openxmlformats.org/drawingml/2006/picture\">" + " <pic:nvPicPr>" + " <pic:cNvPr id=\"" + id + "\" name=\"Generated\"/>" + " <pic:cNvPicPr/>" + " </pic:nvPicPr>" + " <pic:blipFill>" + " <a:blip r:embed=\"" + blipId + "\" xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\"/>" + " <a:stretch>" + " <a:fillRect/>" + " </a:stretch>" + " </pic:blipFill>" + " <pic:spPr>" + " <a:xfrm>"代码下载地址 + " <a:off x=\"0\" y=\"0\"/>" + " <a:ext cx=\"" + width + "\" cy=\"" + height + "\"/>" + " </a:xfrm>" + " <a:prstGeom prst=\"rect\">" + " <a:avLst/>" + " </a:prstGeom>" + " </pic:spPr>" + " </pic:pic>" + " </a:graphicData>" + "</a:graphic>"; inline.addNewGraphic().addNewGraphicData(); XmlToken xmlToken = null; try { xmlToken = XmlToken.Factory.parse(picXml); } catch (XmlException xe) { xe.printStackTrace(); } inline.set(xmlToken); // graphicData.set(xmlToken); inline.setDistT(0); inline.setDistB(0); inline.setDistL(0); inline.setDistR(0); CTPositiveSize2D extent = inline.addNewExtent(); extent.setCx(width); extent.setCy(height); CTNonVisualDrawingProps docPr = inline.addNewDocPr(); docPr.setId(id); docPr.setName("图片" + id); docPr.setDescr(""); } }
<%@ page contentType="text/html;charset=UTF-8" language="java" %> <%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <% String path = request.getContextPath(); String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/"; %> <html> <head> <link type="text/css" rel="stylesheet" media="all" href="<%=path %>/static/styles/styles.css" /> <script type="text/javascript" src="<%=path %>/js/jquery-3.2.1.min.js"></script> <script type="text/javascript" src="<%=path %>/js/echarts.min.js"></script> </head> <style> *{ margin:0px; padding:0px; } #form { padding-top:600px; width:400px; height:500px; } #btn { padding:8px 20px; color:#fff; background:#141414; border-radius:4px; transition: all 1s ease; } #btn:hover{ background:#60c;transition: all 1s ease; } </style> <body> <!-- 为ECharts准备一个具备大小(宽高)的Dom --> <div id="echart" style="height:400px;width:500px"> <h1>欢迎来到echart首页</h1> </div> <input type ="button" value="下载" οnclick="fn()"/> <!-- ECharts单文件引入 --> </body> <script type="text/javascript"> var myChart ; $(function(){ //获得后台数据 var category;//年代 var child;//孩子 var old;//老人 var man;//大人 $.ajax({ url:'<%=path%>/echarts/viewEcharts.html', async:false, dataType: "json", cache: false, success: function(data){ category=data.category; child=data.child; old=data.old; man=data.man; // 基于准备好的dom,初始化echarts图表 myChart = echarts.init(document.getElementById('echart'),"echart"); var option = { tooltip: {//提示框,鼠标悬浮交互时的信息提示 show: true }, toolbox:{//定义工具按钮 show : true, feature : { dataView : {show: true, readOnly: false},//数据信息按钮 magicType : {show: true, //显示折线、柱状图等切换按钮 type: ['line', 'bar','stack', 'tiled']} } }, legend: {//图例 data:['孩子','老人','大人'] }, xAxis : [//x轴设置 { type : 'category',//x轴显示类别 data : category//年代 } ], yAxis : [//y轴设置 { type : 'value'///y轴显示数据值 } ], series : [ { "name":"孩子", "type":"bar", "data":child }, { "name":"老人", "type":"bar", "data":old }, { "name":"大人", "type":"bar", "data":man } ] }; // 为echarts对象加载数据 myChart.setOption(option); } }) }); function fn () { var data = {}; data.picBase64Info1 = myChart.getDataURL(); $.ajax({ url:'<%=path%>/echarts/exportReport.html', type:"post", data:data, dataType:"json", success:function(data1){ if('faild' == data1.ret){ alert('下载失败请重试...'); }else{ window.location.href = '../'+data1.ret; } } }); } // function down(ec){ // var data={}; // var myChart = ec.init(document.getElementById('echart'),"dark"); // // } </script> </html>
主要代码如上所示,没有啥好说的,没啥难得,看看例子就能明白。
代码下载地址