1.该方法是通过poi的包导出的(如果是导出到csv则不需要引入jar包)
需要进入的jar包:
<dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-collections4</artifactId> <version>4.1</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency>
2.struts.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE struts PUBLIC "-//Apache Software Foundation//DTD Struts Configuration 2.0//EN" "http://struts.apache.org/dtds/struts-2.0.dtd"> <struts> <package name="dataQueryPackage" extends="loginPackage"> <action name="dataQuery_*" class="com.hx.pay.web.cms.action.DataQueryAction" method="{1}"> <result name="downLoadData" type="stream"> <param name="inputName">fileInputStream</param> <param name="contentType">application/vnd.ms-excel;charset=utf-8</param> <param name="contentDisposition">attachment;filename="${fileName}.xlsx"</param> <param name="bufferSize">2048</param> </result> </action> </package> </struts>
3.页面(有两种写法)
第一种方法:
下载数据的form:
<form id="downLoadForm" method="post"> <button type="button" id="downLoadTradeData">导出数据</button> </form>
JQuery:
$(function () { $('#downLoadTradeData').click(function () { document.getElementById("downLoadForm").action="tradeQuery_downLoadData.action"; document.getElementById("downLoadForm").submit(); }) })
第二种方法:
下载数据的button:
<button onclick="downLoadData()">导出数据</button>
javascript:
function downLoadData() { try { var url = "fourElementQuery_downLoadData.action"; var elemIF = document.createElement("iframe"); elemIF.src = url; elemIF.style.display = "none"; document.body.appendChild(elemIF); } catch (e) {} }
4.Controller
导出到excel
package com.hx.pay.web.cms.action; import com.hx.pay.common.page.PageParam; import com.hx.pay.common.utils.BeanUtils; import com.hx.pay.web.boss.util.DateUtil; import com.hx.pay.web.boss.util.DictUtil; import com.hx.pay.web.boss.util.Path; import com.hx.pay.web.cms.biz.TradeQueryBiz; import com.hx.pay.web.cms.entity.PayReqOrderQueryRequestVOT; import com.hx.pay.web.permission.base.PermissionBaseAction; import com.yooli.pay.gateway.facade.dto.charge.PayReqOrderQueryDTO; import com.yooli.pay.gateway.facade.dto.charge.PayReqOrderQueryRequestVO; import com.yooli.pay.gateway.facade.dto.page.PageVO; import org.apache.commons.lang.StringUtils; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import java.io.*; import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; import java.text.SimpleDateFormat; import java.util.*; public class TradeQueryAction extends PermissionBaseAction { private InputStream fileInputStream; private String fileName; /** * 数据下载 * @return */ public String downLoadData() { List<Object> datas = getDatas(); //这一步获取数据 exportToExcel(datas); return "downLoadData"; } private void exportToExcel(List<Object> datas) { //内存中只创建100个对象,写临时文件,当超过100条,就将内存中不用的对象释放 SXSSFWorkbook wb = new SXSSFWorkbook(100); //生成excel表格 createExcel(wb, datas); SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddhhmmss"); fileName = "datas-"+sdf.format(new Date()); //设置字符,防止乱码 fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1"); ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); wb.write(outputStream); byte[] ba = outputStream.toByteArray(); fileInputStream = new ByteArrayInputStream(ba); outputStream.flush(); outputStream.close(); } private void createExcel(SXSSFWorkbook wb, List<Object> datas) { if (datas!= null && !datas.isEmpty()) { Sheet sheet = wb.createSheet("datas" + (wb.getNumberOfSheets()+1)); //创建工作表,如果是多个sheet的话,sheet名字依次是datas1、datas2、datas3... Row nRow = sheet.createRow(0); //定义表头 createExcelHead(nRow); //创建表头 int excelColumnsCount = sheet.getLastRowNum(); //获取最后一行的行号 Cell nCell = null; //列对象 for (int i = 0; i < datas.size(); i ++) { Object o = datas.get(i); excelColumnsCount ++; nRow = sheet.createRow(excelColumnsCount); //循环创建行 //填充表格内容 setExcelContent(nCell, nRow, o); } datas.clear(); //创建表完以后将list清空,便于垃圾回收 } } private void setExcelContent(Cell nCell, Row nRow, Object p) { nCell = nRow.createCell(0); nCell.setCellValue(p.getPayStatusF()); nCell = nRow.createCell(1); nCell.setCellValue(p.getPayNo()); nCell = nRow.createCell(2); nCell.setCellValue(p.getOutTradeNo()); nCell = nRow.createCell(3); nCell.setCellValue(p.getChannelCode()); } private void createExcelHead(Row nRow) { Cell cel0 = nRow.createCell(0); cel0.setCellValue("第一列"); Cell cel2 = nRow.createCell(1); cel2.setCellValue("第二列"); Cell cel3 = nRow.createCell(2); cel3.setCellValue("第三列"); Cell cel4 = nRow.createCell(3); cel4.setCellValue("第四列"); } public String getFileName() { return fileName; } public void setFileName(String fileName) { this.fileName = fileName; } public InputStream getFileInputStream() { return fileInputStream; } public void setFileInputStream(InputStream fileInputStream) { this.fileInputStream = fileInputStream; } }
导出到csv
private void exportToCsv(List<Object> datas, HttpServletResponse response) {
if (datas!= null && !datas.isEmpty()) {
StringBuilder sBuilder = new StringBuilder();
sBuilder.append("第一列,第二列,第三列,第四列\n");
createCsvContent(sBuilder, datas);
try {
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddhhmmss");
String fileName = "datas-"+sdf.format(new Date()) + ".csv";
response.setHeader("content-disposition", "attachment;filename=" + fileName);
OutputStream out = response.getOutputStream();
out.write(sBuilder.toString().getBytes(Charset.forName("GBK")));
} catch (IOException e) {
logger.info("exportToCsv#生成csv文件异常#", e);
}
}
}
private void createCsvContent(StringBuilder sBuilder, List<Object> datas) {
if (datas!= null && !datas.isEmpty()) {
for (PayReqOrderQueryDTO p : voList) {
sBuilder.append(p.getPayStatusF()).append(",")
.append(p.getPayNo() + "\t").append(",") //如果内容是数字类型的字符串,则要加"\t",否则会出现科学计数法,导致不准确
.append(p.getOutTradeNo() + "\t").append(",")
.append(p.getChannelCode()).append(",")
.append("\n");
}
datas.clear();
logger.info("createCsvContent#生成csv文件结束");
} else {
logger.info("createCsvContent#数据为空#voLise:{}", datas);
}
}