由于项目使用的是easyui，现在需要做一个导出Excel功能，Excel内包含表格内容和图片信息。查看easyui的官网发现有扩展datagrid-export.js可以在前端导出excel，于是拿来使用，发现无法导出多级表头，改写源码后可以导出多级表头的excel，但是没法在前端导出echarts的base64编码的图片。因此使用Java后台采用poi来导出excel。考虑到前面已经在前端由table导出excel成功，想着可不可以直接传table到后台导出excel，网上搜索一翻，发现这位老哥做了，真是好人，于是借鉴一下，同时加上导出图片，在此记录一下。提醒一下，导出的Excel有可能在本地的可以打开，服务器上的显示打开损坏，可以参考此篇文章。
poi依赖包
<!-- poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
跨行元素元数据类
public class CrossRangeCellMeta {
public CrossRangeCellMeta(int firstRowIndex, int firstColIndex, int rowSpan, int colSpan) {
super();
this.firstRowIndex = firstRowIndex;
this.firstColIndex = firstColIndex;
this.rowSpan = rowSpan;
this.colSpan = colSpan;
}
private int firstRowIndex;
private int firstColIndex;
private int rowSpan;// 跨越行数
private int colSpan;// 跨越列数
public int getFirstRow() {
return firstRowIndex;
}
public int getLastRow() {
return firstRowIndex + rowSpan - 1;
}
public int getFirstCol() {
return firstColIndex;
}
public int getLastCol() {
return firstColIndex + colSpan - 1;
}
public int getColSpan(){
return colSpan;
}
}
主程序table转excel
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
import javax.imageio.ImageIO;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
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.hssf.util.HSSFColor.HSSFColorPredefined;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
public class ConvertHtml2Excel {
/**
* html表格转excel
*
* @param tableHtml 如
* <table>
* ..
* </table>
* @return
*/
@SuppressWarnings("unchecked")
public static HSSFWorkbook table2Excel(String tableHtml, String base64Img, String picPath) {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
List<CrossRangeCellMeta> crossRowEleMetaLs = new ArrayList<>();
int rowIndex = 0;
try {
Document data = DocumentHelper.parseText(tableHtml);
// 生成表头
Element thead = data.getRootElement().element("thead");
HSSFCellStyle titleStyle = getTitleStyle(wb);
if (thead != null) {
List<Element> trLs = thead.elements("tr");
for (Element trEle : trLs) {
HSSFRow row = sheet.createRow(rowIndex);
List<Element> thLs = trEle.elements("th");
makeRowCell(thLs, rowIndex, row, 0, titleStyle, crossRowEleMetaLs);
row.setHeightInPoints(17);
rowIndex++;
}
}
// 生成表体
Element tbody = data.getRootElement().element("tbody");
if (tbody != null) {
HSSFCellStyle contentStyle = getContentStyle(wb);
List<Element> trLs = tbody.elements("tr");
for (Element trEle : trLs) {
HSSFRow row = sheet.createRow(rowIndex);
List<Element> thLs = trEle.elements("th");
int cellIndex = makeRowCell(thLs, rowIndex, row, 0, titleStyle, crossRowEleMetaLs);
List<Element> tdLs = trEle.elements("td");
makeRowCell(tdLs, rowIndex, row, cellIndex, contentStyle, crossRowEleMetaLs);
row.setHeightInPoints(18);
rowIndex++;
}
}
// 合并表头
for (CrossRangeCellMeta crcm : crossRowEleMetaLs) {
CellRangeAddress region = new CellRangeAddress(crcm.getFirstRow(), crcm.getLastRow(), crcm.getFirstCol(), crcm.getLastCol());
sheet.addMergedRegion(region);
}
} catch (DocumentException e) {
e.printStackTrace();
}
//自动调整列宽
for (int i = 0; i < sheet.getRow(0).getPhysicalNumberOfCells(); i++) {
sheet.autoSizeColumn((short)i);
}
insertImg2Excel(sheet, wb, base64Img, picPath);
return wb;
}
@SuppressWarnings("static-access")
public static void insertImg2Excel(HSSFSheet sheet, HSSFWorkbook wb, String imgUrl, String picPath) {
BufferedImage bufferImg = null;
try {
String[] imgUrlArr = imgUrl.split("base64,");//拆分base64编码后部分
org.bouncycastle.util.encoders.Base64 decode = new org.bouncycastle.util.encoders.Base64();
byte[] buffer = decode.decode(imgUrlArr[1]);
File file = new File(picPath);//图片文件
//生成图片
OutputStream out = new FileOutputStream(file);//图片输出流
out.write(buffer);
out.flush();//清空流
out.close();//关闭流
ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
// bufferImg = ImageIO.read(new File("D:/WatchFileTest/下载.png"));
bufferImg = ImageIO.read(new File(picPath));
ImageIO.write(bufferImg, "PNG", byteArrayOut);
//画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
//anchor主要用于设置图片的属性
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0,(short) 1, sheet.getPhysicalNumberOfRows()+2, (short) 23, sheet.getPhysicalNumberOfRows()+30);
//合并图片所在区域的单元格
CellRangeAddress region = new CellRangeAddress(sheet.getPhysicalNumberOfRows()+1, sheet.getPhysicalNumberOfRows()+30, 0, 23);
sheet.addMergedRegion(region);
//插入图片
patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
// 写入excel文件
System.out.println("----Excle插入图片已生成------");
if(file.exists()) {
file.delete();
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 生产行内容
*
* @return 最后一列的cell index
*/
/**
* @param tdLs th或者td集合
* @param rowIndex 行号
* @param row POI行对象
* @param startCellIndex
* @param cellStyle 样式
* @param crossRowEleMetaLs 跨行元数据集合
* @return
*/
@SuppressWarnings("deprecation")
private static int makeRowCell(List<Element> tdLs, int rowIndex, HSSFRow row, int startCellIndex, HSSFCellStyle cellStyle,
List<CrossRangeCellMeta> crossRowEleMetaLs) {
int i = startCellIndex;
for (int eleIndex = 0; eleIndex < tdLs.size(); i++, eleIndex++) {
int captureCellSize = getCaptureCellSize(rowIndex, i, crossRowEleMetaLs);
while (captureCellSize > 0) {
for (int j = 0; j < captureCellSize; j++) {// 当前行跨列处理（补单元格）
row.createCell(i);
i++;
}
captureCellSize = getCaptureCellSize(rowIndex, i, crossRowEleMetaLs);
}
Element thEle = tdLs.get(eleIndex);
String val = thEle.getTextTrim();
if (StringUtils.isBlank(val)) {
Element e = thEle.element("a");
if (e != null) {
val = e.getTextTrim();
}
}
HSSFCell c = row.createCell(i);
if (NumberUtils.isNumber(val)) {
c.setCellValue(Double.parseDouble(val));
c.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
} else {
c.setCellValue(val);
}
c.setCellStyle(cellStyle);
int rowSpan = NumberUtils.toInt(thEle.attributeValue("rowspan"), 1);
int colSpan = NumberUtils.toInt(thEle.attributeValue("colspan"), 1);
if (rowSpan > 1 || colSpan > 1) { // 存在跨行或跨列
crossRowEleMetaLs.add(new CrossRangeCellMeta(rowIndex, i, rowSpan, colSpan));
}
if (colSpan > 1) {// 当前行跨列处理（补单元格）
for (int j = 1; j < colSpan; j++) {
i++;
row.createCell(i);
}
}
}
return i;
}
/**
* 获得因rowSpan占据的单元格
*
* @param rowIndex 行号
* @param colIndex 列号
* @param crossRowEleMetaLs 跨行列元数据
* @return 当前行在某列需要占据单元格
*/
private static int getCaptureCellSize(int rowIndex, int colIndex, List<CrossRangeCellMeta> crossRowEleMetaLs) {
int captureCellSize = 0;
for (CrossRangeCellMeta crossRangeCellMeta : crossRowEleMetaLs) {
if (crossRangeCellMeta.getFirstRow() < rowIndex && crossRangeCellMeta.getLastRow() >= rowIndex) {
if (crossRangeCellMeta.getFirstCol() <= colIndex && crossRangeCellMeta.getLastCol() >= colIndex) {
captureCellSize = crossRangeCellMeta.getLastCol() - colIndex + 1;
}
}
}
return captureCellSize;
}
/**
* 获得标题样式
*
* @param workbook
* @return
*/
private static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook) {
short fontSize = 12;
String fontName = "宋体";
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFFont font = workbook.createFont();
font.setFontName(fontName);
font.setFontHeightInPoints(fontSize);
// 粗体显示
font.setBold(true);
style.setFont(font);
style.setFillForegroundColor(HSSFColorPredefined.LIGHT_YELLOW.getIndex());
return style;
}
/**
* 获得内容样式
*
* @param wb
* @return
*/
private static HSSFCellStyle getContentStyle(HSSFWorkbook wb) {
short fontSize = 12;
String fontName = "宋体";
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFFont font = wb.createFont();
font.setFontName(fontName);
font.setFontHeightInPoints(fontSize);
style.setFont(font);
return style;
}
public static void main(String[] args) {
String c = new String("<table id=\"targetTable\">\n" +
" <thead>\n" +
" <tr align=\"center\">\n" +
" <th>名次</th>\n" +
" <th>姓名</th>\n" +
" <th>成绩</th>\n" +
" </tr>\n" +
" </thead>\n" +
" <tbody>\n" +
" <tr align=\"center\">\n" +
" <td>1</td>\n" +
" <td>小明</td>\n" +
" <td>100</td>\n" +
" </tr>\n" +
" <tr align=\"center\">\n" +
" <td>2</td>\n" +
" <td>小红</td>\n" +
" <td>95.5</td>\n" +
" </tr>\n" +
" </tbody>\n" +
"</table>");
String base64Img = "这个数据太长了，就不放了，可以自己在网上找一下";
String picPath = "D:/WatchFileTest"+ "/"+ UUID.randomUUID().toString() +".png";
HSSFWorkbook wb = table2Excel(c, base64Img, picPath);
try {
FileOutputStream fos = new FileOutputStream(new File("D:/1.xls"));
wb.write(fos);
fos.flush();
fos.close();
System.out.println("表格导出成功！！！");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
Controller类
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.UUID;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
@Controller
@RequestMapping("/file/export")
public class ExportCheckExcelController {
@RequestMapping(value = "/html2Excel", method = RequestMethod.POST)
@ResponseBody
public void html2Excel(HttpServletRequest request, HttpServletResponse response) throws Exception{
request.setCharacterEncoding("utf-8");
//临时存放图片文件path
String picPath = request.getSession().getServletContext().getRealPath("")+UUID.randomUUID().toString() +".png";
picPath = picPath.replaceAll("\\\\", "/");
System.out.println(picPath);
String excelName = request.getParameter("excelName");
String base64Img = request.getParameter("base64Img");
String tableHtml = request.getParameter("tableHtml");
OutputStream out = null;
HSSFWorkbook wb = ConvertHtml2Excel.table2Excel(tableHtml, base64Img, picPath);
try {
excelName = URLEncoder.encode(excelName, "utf-8");
} catch (UnsupportedEncodingException e1) {
e1.printStackTrace();
}
try {
out = response.getOutputStream();
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
response.setHeader("content-disposition", "attachment;filename=" + excelName + ".xls;filename*=utf-8''" + excelName + ".xls");
wb.write(out);
System.out.println("表格导出成功！！！");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
改写后的datagrid-export.js
(function($){
function getRows(target){
var state = $(target).data('datagrid');
if (state.filterSource){
return state.filterSource.rows;
} else {
return state.data.rows;
}
}
function getFooterRows(target){
var state = $(target).data('datagrid');
return state.data.footer || [];
}
//主要修改部分
function toHtml(target, region, rows, footer, caption){
rows = rows || getRows(target);
rows = rows.concat(footer||getFooterRows(target));
var dg = $(target);
var data = ['<table border="1" rull="all" style="border-collapse:collapse">'];
var firstHeads = dg.datagrid("options").frozenColumns[0].concat(dg.datagrid("options").columns[0]);
var twoHeads = dg.datagrid("options").columns[1];
var fields = dg.datagrid('getColumnFields',true).concat(dg.datagrid('getColumnFields',false));
var trStyle = 'height:32px';
var tdStyle0 = 'vertical-align:middle;padding:0 4px';
if (caption){
data.push('<caption>'+caption+'</caption>');
}
data.push('<thead>');
data.push('<tr style="'+trStyle+'">');
for(var i=0; i<firstHeads.length; i++){
data.push('<th colspan="'+firstHeads[i].colspan+'" rowspan="'+firstHeads[i].rowspan+'" style="'+tdStyle0+'">'+firstHeads[i].title+'</th>');
}
data.push('</tr>');
data.push('<tr style="'+trStyle+'">');
for(var i=0; i<twoHeads.length; i++){
var col = dg.datagrid('getColumnOption', twoHeads[i].field);
var tdStyle = tdStyle0 + ';width:'+col.width;
tdStyle += ';text-align:'+(col.halign||col.align||'');
data.push('<th style="'+tdStyle+'">'+col.title+'</th>');
}
data.push('</tr>');
data.push('</thead>');
data.push('<tbody>');
$.map(rows, function(row){
data.push('<tr style="'+trStyle+'">');
for(var i=0; i<fields.length; i++){
var field = fields[i];
var col = dg.datagrid('getColumnOption', field);
var value = row[field];
if (value == undefined){
value = '-';
}
if(field=="regionName"){
value = region;
}else if(field=="forecastName"){
value = region;
}
var tdStyle = tdStyle0;
tdStyle += ';text-align:'+(col.align||'');
data.push(
'<td style="'+tdStyle+'">'+value+'</td>'
);
}
data.push('</tr>');
});
data.push('</tbody>');
data.push('</table>');
return data.join('');
}
function toArray(target, rows){
rows = rows || getRows(target);
var dg = $(target);
var fields = dg.datagrid('getColumnFields',true).concat(dg.datagrid('getColumnFields',false));
var data = [];
var r = [];
for(var i=0; i<fields.length; i++){
var col = dg.datagrid('getColumnOption', fields[i]);
r.push(col.title);
}
data.push(r);
$.map(rows, function(row){
var r = [];
for(var i=0; i<fields.length; i++){
r.push(row[fields[i]]);
}
data.push(r);
});
return data;
}
function print(target, param){
var title = null;
var rows = null;
var footer = null;
var caption = null;
if (typeof param == 'string'){
title = param;
} else {
title = param['title'];
rows = param['rows'];
footer = param['footer'];
caption = param['caption'];
}
var newWindow = window.open('', '', 'width=800, height=500');
var document = newWindow.document.open();
var content =
'<!doctype html>' +
'<html>' +
'<head>' +
'<meta charset="utf-8">' +
'<title>'+title+'</title>' +
'</head>' +
'<body>' + toHtml(target, rows, footer, caption) + '</body>' +
'</html>';
document.write(content);
document.close();
newWindow.print();
}
function b64toBlob(data){
var sliceSize = 512;
var chars = atob(data);
var byteArrays = [];
for(var offset=0; offset<chars.length; offset+=sliceSize){
var slice = chars.slice(offset, offset+sliceSize);
var byteNumbers = new Array(slice.length);
for(var i=0; i<slice.length; i++){
byteNumbers[i] = slice.charCodeAt(i);
}
var byteArray = new Uint8Array(byteNumbers);
byteArrays.push(byteArray);
}
return new Blob(byteArrays, {
type: ''
});
}
function toExcel(target, param){
var filename = null;
var rows = null;
var footer = null;
var caption = null;
var worksheet = 'Worksheet';
var regions = null;
if (typeof param == 'string'){
filename = param;
} else {
filename = param.filename;
//rows = param['rows'];
//footer = param['footer'];
caption = param.caption;
//worksheet = param['worksheet'] || 'Worksheet';
regions = param.regions;
}
var dg = $(target);
var uri = 'data:application/vnd.ms-excel;base64,'
, template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><meta http-equiv="content-type" content="application/vnd.ms-excel; charset=UTF-8"><head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body>{table}</body></html>'
, base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))) }
, format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) }
var table = toHtml(target, region, rows, footer, caption);
// console.log(table);
var ctx = { worksheet: worksheet, table: table };
var data = base64(format(template, ctx));
if (window.navigator.msSaveBlob){
var blob = b64toBlob(data);
window.navigator.msSaveBlob(blob, filename);
} else {
var alink = $('<a style="display:none"></a>').appendTo('body');
alink[0].href = uri + data;
alink[0].download = filename;
alink[0].click();
alink.remove();
}
}
$.extend($.fn.datagrid.methods, {
toHtml: function(jq, rows){
return toHtml(jq[0], rows);
},
toArray: function(jq, rows){
return toArray(jq[0], rows);
},
toExcel: function(jq, param){
return jq.each(function(){
toExcel(this, param);
});
},
print: function(jq, param){
return jq.each(function(){
print(this, param);
});
}
});
})(jQuery);
前端请求导出excel
function httpPost(URL, PARAMS) {
var temp = document.createElement("form");
temp.action = URL;
temp.method = "post";
temp.style.display = "none";
for (var x in PARAMS) {
var opt = document.createElement("textarea");
opt.name = x;
opt.value = PARAMS[x];
temp.appendChild(opt);
}
document.body.appendChild(temp);
temp.submit();
document.body.removeChild(temp);
}
// 导出Excel
function exportExcel(){
// 获取table
var table = $('#tt').datagrid('toHtml', regionForeTypeName, null, null, '名字');
var param = {
excelName: '名字',
base64Img: myChartInstance.getDataURL(), // 获取echarts的图片信息
tableHtml: table,
}
httpPost("${contextPath}/file/export/html2Excel", param);
}
结果
扫一扫
364
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
