报表生成(POI,jquery.table2excel.js，Echarts)
最近公司要弄个报表相关的功能，话不多说，先上图
前一种是POI 生成的，后一种是Echarts生成的。报表我想大家都不陌生，基本上在公司业务中都会使用到。先说说POI，jquery.table2excel.js，Echarts这三种使用区别吧，本篇博客主要是使用上，如果只是像表格那样标准格式的导出的话，我想用table2挺好，只要把相关的js引入就可以使用，不用像poi那样生成模板，添加样式什么的。在网上都能搜到一大堆代码，当然了能不能用就不知道了。https://blog.csdn.net/weixin_36751895/article/details/79303854 这是相关链接；
POI虽然麻烦点，但是可以随着我们的心意基本上可以实现，就如第一种那种特殊样式的报表。
POI一般分为三个步骤：
1. 首先是POI 样式/模板的生成,你需要什么样式/模板,这是相关代码:
import java.io.*;
import java.text.SimpleDateFormat;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
import org.apache.poi.ss.util.CellRangeAddress;
import java.net.HttpURLConnection;
import java.net.URL;
import com.cn.jr.model.JrUser;
import com.cn.rz.model.JrUserOrders;
public class ExportExcel {
public void getValue(JrUser jrUser, JrUserOrders jrUserOrders, String imageUrl, OutputStream outData) {
if (jrUser == null || jrUserOrders == null) {
jrUser = new JrUser();
jrUserOrders = new JrUserOrders();
}
HSSFWorkbook workbook = new HSSFWorkbook();
try {
// 1.创建工作簿
// 1.1创建合并单元格对象
CellRangeAddress callRangeAddress = new CellRangeAddress(0, 2, 0, 7);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress1 = new CellRangeAddress(3, 4, 0, 2);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress20 = new CellRangeAddress(3, 4, 3, 7);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress21 = new CellRangeAddress(5, 6, 0, 7);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress22 = new CellRangeAddress(7, 8, 0, 2);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress31 = new CellRangeAddress(7, 8, 3, 4);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress32 = new CellRangeAddress(7, 8, 5, 7);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress34 = new CellRangeAddress(9, 10, 0, 7);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress35 = new CellRangeAddress(11, 12, 0, 2);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress36 = new CellRangeAddress(11, 12, 3, 4);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress37 = new CellRangeAddress(11, 12, 5, 7);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress38 = new CellRangeAddress(13, 14, 0, 7);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress39 = new CellRangeAddress(15, 16, 0, 7);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress40 = new CellRangeAddress(17, 18, 0, 7);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress41 = new CellRangeAddress(19, 20, 0, 7);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress42 = new CellRangeAddress(21, 22, 0, 7);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress43 = new CellRangeAddress(23, 24, 0, 7);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress44 = new CellRangeAddress(25, 26, 0, 7);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress45 = new CellRangeAddress(27, 34, 0, 7);// 起始行,结束行,起始列,结束列
CellRangeAddress callRangeAddress47 = new CellRangeAddress(35, 35, 0, 7);// 起始行,结束行,起始列,结束列
HSSFCellStyle erStyle = createCellStyle(workbook, (short) 13, true, true, false);
HSSFCellStyle sanStyle = createCellStyle(workbook, (short) 10, true, false, false);
HSSFCellStyle nameStyle = createCellStyle(workbook, (short) 10, false, false, false);
HSSFCellStyle dataStyle = createCellStyle(workbook, (short) 10, false, true, false);
// 创建工作表
HSSFSheet sheet = workbook.createSheet();
// 加载合并单元格对象
sheet.addMergedRegion(callRangeAddress);
sheet.addMergedRegion(callRangeAddress1);
sheet.addMergedRegion(callRangeAddress20);
sheet.addMergedRegion(callRangeAddress21);
sheet.addMergedRegion(callRangeAddress22);
sheet.addMergedRegion(callRangeAddress31);
sheet.addMergedRegion(callRangeAddress32);
sheet.addMergedRegion(callRangeAddress34);
sheet.addMergedRegion(callRangeAddress35);
sheet.addMergedRegion(callRangeAddress36);
sheet.addMergedRegion(callRangeAddress37);
sheet.addMergedRegion(callRangeAddress38);
sheet.addMergedRegion(callRangeAddress39);
sheet.addMergedRegion(callRangeAddress40);
sheet.addMergedRegion(callRangeAddress41);
sheet.addMergedRegion(callRangeAddress42);
sheet.addMergedRegion(callRangeAddress43);
sheet.addMergedRegion(callRangeAddress44);
sheet.addMergedRegion(callRangeAddress45);
sheet.addMergedRegion(callRangeAddress47);
// 设置默认列宽
sheet.setDefaultColumnWidth(15);
// 标题
HSSFRow rower = sheet.createRow(0);
HSSFCell celler = rower.createCell(0);
celler.setCellStyle(erStyle);
celler.setCellValue("融资租赁收费确认单");
// 客户姓名,手机号码
HSSFRow rowsan = sheet.createRow(3);
HSSFCell cellsan = rowsan.createCell(0);
HSSFCell cellsan2 = rowsan.createCell(3);
cellsan.setCellStyle(sanStyle);
cellsan.setCellValue("客户姓名: " + jrUser.getRealName());
cellsan2.setCellStyle(sanStyle);
cellsan2.setCellValue("手机号码: " + jrUser.getTelphone());
// 车型
HSSFRow rowCar = sheet.createRow(5);
HSSFCell carsan = rowCar.createCell(0);
carsan.setCellStyle(sanStyle);
if ("0".equals(jrUserOrders.getCarType())) {
carsan.setCellValue("车型: " + "新车");
} else if ("1".equals(jrUserOrders.getCarType())) {
carsan.setCellValue("车型: " + "二手车");
} else {
carsan.setCellValue("车型: " + "");
}
// 车价,购置税,保险
HSSFRow rowpersion = sheet.createRow(7);
HSSFCell cellpersion = rowpersion.createCell(0);
HSSFCell cellpersion1 = rowpersion.createCell(3);
HSSFCell cellpersion2 = rowpersion.createCell(5);
cellpersion.setCellStyle(sanStyle);
cellpersion.setCellValue("车价: " + jrUserOrders.getCarPrice() + " 元");
cellpersion1.setCellStyle(sanStyle);
cellpersion1.setCellValue("购置税: " + jrUserOrders.getPurchaseTax() + " 元");
cellpersion2.setCellStyle(sanStyle);
cellpersion2.setCellValue("保险: " + jrUserOrders.getInsurance() + " 元");
// 首付贷
HSSFRow rowOne = sheet.createRow(9);
HSSFCell onesan = rowOne.createCell(0);
onesan.setCellStyle(sanStyle);
onesan.setCellValue("首付贷: " + jrUserOrders.getDownPayments() + " 元");
// 车价,购置税,保险
HSSFRow rowRz = sheet.createRow(11);
HSSFCell cellrz = rowRz.createCell(0);
HSSFCell cellqs = rowRz.createCell(3);
HSSFCell cellmonth = rowRz.createCell(5);
cellrz.setCellStyle(sanStyle);
cellrz.setCellValue("融资金额: " + jrUserOrders.getRzAmt() + " 元");
cellqs.setCellStyle(sanStyle);
cellqs.setCellValue("期数: " + jrUserOrders.getRzqs() + " 期");
cellmonth.setCellStyle(sanStyle);
cellmonth.setCellValue("月还款额: " + jrUserOrders.getMonthPay() + " 元");
// 保证金
HSSFRow rowamt = sheet.createRow(13);
HSSFCell amtsan = rowamt.createCell(0);
amtsan.setCellStyle(sanStyle);
amtsan.setCellValue("保证金: " + jrUserOrders.getBond() + " 元" + " (总额5%)");
// 手续费
HSSFRow proceduresrow = sheet.createRow(15);
HSSFCell proceduresAmt = proceduresrow.createCell(0);
proceduresAmt.setCellStyle(sanStyle);
proceduresAmt.setCellValue("手续费: " + jrUserOrders.getProceduresAmt() + " 元" + " (总额3%)");
// 续保押金
HSSFRow renewDepositrow = sheet.createRow(17);
HSSFCell renewDeposit = renewDepositrow.createCell(0);
renewDeposit.setCellStyle(sanStyle);
renewDeposit.setCellValue("续保押金: " + jrUserOrders.getRenewDeposit() + " 元");
// GPS费
HSSFRow gps = sheet.createRow(19);
HSSFCell gpssan = gps.createCell(0);
gpssan.setCellStyle(sanStyle);
gpssan.setCellValue("GPS费: " + jrUserOrders.getGpsAmt() + " 元");
// 公证费
HSSFRow notarization = sheet.createRow(21);
HSSFCell notarizationAmt = notarization.createCell(0);
notarizationAmt.setCellStyle(sanStyle);
notarizationAmt.setCellValue("公证费: " + jrUserOrders.getNotarizationAmt() + " 元");
// 家访费
HSSFRow home = sheet.createRow(23);
HSSFCell homesan = home.createCell(0);
homesan.setCellStyle(sanStyle);
homesan.setCellValue("家访费: " + jrUserOrders.getHomeVisitAmt() + " 元");
// 其他
HSSFRow other = sheet.createRow(25);
HSSFCell othersan = other.createCell(0);
othersan.setCellStyle(sanStyle);
othersan.setCellValue("其他: " + jrUserOrders.getHomeVisitAmt() + " 元");
// 签名
HSSFRow png = sheet.createRow(27);
HSSFCell pngsan = png.createCell(0);
pngsan.setCellStyle(nameStyle);
pngsan.setCellValue("以上收费标准本人已确认无误.客户签名: ");
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
drawPictureInfoExcel(workbook, patriarch, imageUrl);
// 签名日期
HSSFRow pngDate = sheet.createRow(35);
HSSFCell pngDatesan = pngDate.createCell(0);
pngDatesan.setCellStyle(dataStyle);
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
pngDatesan.setCellValue("日期: " + simpleDateFormat.format(jrUserOrders.getCreatedate()));
// 5.输出
workbook.write(outData);
// workbook.close();
// out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/** 单元格样式*/
private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontsize, boolean flagRow, boolean flagC,boolean flagDate) {
// TODO Auto-generated method stub
HSSFCellStyle style = workbook.createCellStyle();
// 是否水平居中
if (flagRow) {
style.setVerticalAlignment(VerticalAlignment.CENTER);// 水平居中
} else {
style.setVerticalAlignment(VerticalAlignment.TOP);
}
// 创建字体
HSSFFont font = workbook.createFont();
// 是否加粗字体
if (flagC) {
style.setAlignment(HorizontalAlignment.CENTER);// 垂直居中
font.setFontHeightInPoints((short) 10);
}
if (flagDate) {
style.setAlignment(HorizontalAlignment.RIGHT);// 垂直居右
font.setFontHeightInPoints((short) 8);
}
font.setFontHeightInPoints(fontsize);
// 加载字体
style.setFont(font);
return style;
}
public static void drawPictureInfoExcel(HSSFWorkbook wb, HSSFPatriarch patriarch, String imageUrl) {
try {
URL url = new URL(imageUrl);
// 打开链接
HttpURLConnection conn = (HttpURLConnection) url.openConnection();
// 设置请求方式为"GET"
conn.setRequestMethod("GET");
// 超时响应时间为5秒
conn.setConnectTimeout(5 * 1000);
// 通过输入流获取图片数据
InputStream inStream = conn.getInputStream();
byte[] data = readInputStream(inStream);
// anchor主要用于设置图片的属性
// HSSFClientAnchor hssfClientAnchor = new
// HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2);
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 0, 28, (short) 8, 35);
// 图片在单元格的位置
anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);
patriarch.createPicture(anchor, wb.addPicture(data, HSSFWorkbook.PICTURE_TYPE_JPEG));
} catch (Exception e) {
e.printStackTrace();
}
}
private static byte[] readInputStream(InputStream inStream) throws Exception {
ByteArrayOutputStream outStream = new ByteArrayOutputStream();
// 创建一个Buffer字符串
byte[] buffer = new byte[1024];
// 每次读取的字符串长度，如果为-1，代表全部读取完毕
int len = 0;
// 使用一个输入流从buffer里把数据读取出来
while ((len = inStream.read(buffer)) != -1) {
// 用输出流往buffer里写入数据，中间参数代表从哪个位置开始读，len代表读取的长度
outStream.write(buffer, 0, len);
}
// 关闭输入流
inStream.close();
// 把outStream里的数据写入内存
return outStream.toByteArray();
}
/*
* public static void main(String args[]){ //模拟部分数据 try { FileOutputStream
* fout = new FileOutputStream("I:/333.xls"); // new
* ExportExcel().getValue(fout); fout.close(); } catch (Exception e) {
* e.printStackTrace(); }
*
* }*/
}
其中单元格样式网上大部分的POI 都是老版本的，所以有许多代码没有相关的api, 上面的代码中单元格样式相关API是新版的，代码都可以直接使用。
2. 样式什么的都创建好了，接下来就是使用调用了(controller)，
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
String date = simpleDateFormat.format(new Date()).toString();
String fileName = date + jrUser.getRealName() + "/融资租赁收费确认单";
OutputStream outData = response.getOutputStream();
response.reset();
response.setContentType("application/vnd.ms-excel;charset=gbk");
response.addHeader("Content-Disposition",
"attachment;filename=" + new String((fileName + ".xls").getBytes("gbk"), "ISO-8859-1"));
ExportExcel exportExcel = new ExportExcel();
String basePath = request.getContextPath();
String imagePath = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort()
+ basePath + "/ui/rzJs/orders.png";
exportExcel.getValue(jrUser, jrUserOrders, imagePath, outData);
3. jsp当然少不了，当然了，如果你不是做成弹窗式下载，就可以不要弹窗jsp，把2中的代码改成用流写入到本地位置即可
<form id="myform" enctype="multipart/form-data" action="<%=basePath%>/rzJrUsre/batchExport" method="post" role="form">
<table>
<tr>
<td><input type="submit" value="导出" style="margin-left: 1550px;" /></td>
</tr>
</table>
</form>
以上代码都是可以直接使用的，当然了如果有什么不能使用的地方，就需要各位大神自己动手改改相关代码了，毕竟环境，需求不可能一定相同。其实POI使用原理也挺简单的，就是弄个模板，然后用流将信息写入，用流操作。明白这些使用POI也不难。
最后是Echarts，是百度的一款做报表的相关开源API，http://www.echartsjs.com/index.html 这是首页链接，使用相对于来说跟table2一样也挺简单，引入js，使用api。 当然了也有其局限性：数据量一大就不行了，不过其美观性方面确实不错。 这有一个基于Echarts开发的软件：http://www.smartbi.com.cn/spreadsheet 其做的相对很好。其中大量案例，模板，可以进去看看，再根据Echarts官方的API,文档就很容易理解，上手Echarts了。
Echarts导出有图片时，传到后台的是base64编码的字符串，所以后台接收需要做出相关处理。
function getFullCanvasDataURL(divId){
//将第一个画布作为基准。
var baseCanvas = $("#"+divId).find("canvas").first()[0];
if(!baseCanvas){
return false;
};
var width = baseCanvas.width;
var height = baseCanvas.height;
var ctx = baseCanvas.getContext("2d");
//遍历，将后续的画布添加到在第一个上
$("#"+divId).find("canvas").each(function(i,canvasObj){
if(i>0){
var canvasTmp = $(canvasObj)[0];
ctx.drawImage(canvasTmp,0,0,width,height);
}
});
//获取base64位的url
return baseCanvas.toDataURL();
}
function importBtnn() {
var data = getFullCanvasDataURL("main");
$("#img").val(data);
var img= $("#img").val();
$("#myform").submit();
};
我想有了这三种制作表格的方式足以实现业务中的需求了。有不懂的, 有问题，有建议欢迎大家随时提出，自己也是刚开始写博客。希望能坚持，努力下去，学无止境。
