前段时间做Web报表导出,结果数据量太大了频繁报错,上百度一查才知道excel一个页面存放的数据是有限的,大概在6800行左右,而我的数据有上万行,想解决这个办法就只有在超出每页行数限制前新建一页进行存放,所以将工具类进行了修改。
jar包:
- commons-beanutils-1.7.0.jar
- commons-lang-2.3.jar
- jxl.jar
工具类
1、ExportReportForCommon.java
package com.wpixel.util;
import java.beans.PropertyDescriptor;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import jxl.SheetSettings;
import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.DateFormat;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import org.apache.commons.beanutils.PropertyUtilsBean;
import org.apache.commons.lang.StringUtils;
/**
* 用于报表导出
* @author Wpixel
*
*/
public class ExportReportForCommon{
public static void createReport(HttpServletRequest request,HttpServletResponse response,ReportModel report, int k) throws WriteException, IOException{
String fileName;
response.setCharacterEncoding("GBK");
fileName = new String(report.getTitle().getBytes(),"ISO-8859-1");
response.setContentType("application/force-download");
response.setContentType("application/msexcel");
response.setContentType("textml;charset=GBK");
response.setHeader("Content-Disposition","attachment; filename="+fileName+".xls");
response.setHeader("Content-Type", "application/vnd.ms-excel");
WritableWorkbook wwb;
OutputStream os = null;
DateFormat df=new jxl.write.DateFormat("yyyy/MM/dd HH:mm:ss");
int sheetNum = 1;
os = response.getOutputStream();
wwb = Workbook.createWorkbook(os);
//封装表头
WritableSheet ws = null;
String preValue="";
String nextValue="";
boolean fal=false;
int pre=0;
int b = 0;
// 从界面拿到输入的查询条件
// jdbc sql查询 操作数据库并分页,和查询符合条件的所有对象
if(report.getAvlueList() != null && report.getAvlueList().size()>0){
if(report.getAvlueList().get(0).getClass().isArray()){//数组类型
}else{//JavaBean或Map类型
int size = report.getAvlueList().size();
//每页工作表限制60000条,超出后新建工作表页面
int avg = size/60000;
for (int l = 0; l < avg + 1; l++) {
int i=0;
ws = wwb.createSheet(report.getTitle()+"("+sheetNum+")", l);
SheetSettings ss = ws.getSettings();
ss.setVerticalFreeze(1); // 设置行冻结前1行
String[] header=report.getHeader().split(",");
WritableFont font = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.RED);
WritableCellFormat cFormat = new WritableCellFormat(font);
cFormat.setBackground(Colour.LIGHT_BLUE);
if(ws.getColumns() == 0){
WritableFont wf = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD, false);
for (int k1 = 0; k1 < header.length; k1++) {
ws.setColumnView(k1, header[k1].length()*4);
Label label2 = new Label(k1,0,header[k1],cFormat);
WritableCellFormat cellFormat=new WritableCellFormat(wf);
cellFormat.setAlignment(jxl.format.Alignment.CENTRE);
cellFormat.setBackground((jxl.format.Colour.SKY_BLUE));
cellFormat.setBorder( Border.ALL, BorderLineStyle.THIN, jxl.format.Colour.GRAY_50);
label2.setCellFormat(cellFormat);
ws.addCell(label2);
}
}
int index = 0;
for (; b < report.getAvlueList().size(); b++) {
Object obj = report.getAvlueList().get(b);
Map<String, Object> map = (obj instanceof Map)?(Map<String, Object>)obj:beanToMap(obj);
int j = 0;
if (index == 60000) {
//判断index == 60000的时候跳出当前for循环
sheetNum++;
break;
}
for (String key : report.getFields()) {
String s = "";
Boolean bl = false;
if(map.get(key) != null)
if(map.get(key) instanceof Date){
s = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format((Date)map.get(key));
bl = true;
}else{
s = map.get(key).toString();
bl = false;
}
if(j == 0){
preValue = s;
pre = i;
}
Label label = new jxl.write.Label(j, i + 1,s);
WritableCellFormat cellFormat;
if(bl){
cellFormat=new WritableCellFormat(df);
}else{
cellFormat=new WritableCellFormat();
}
cellFormat.setAlignment(jxl.format.Alignment.CENTRE);
if(i%2 == 1){
//行颜色交替
cellFormat.setBackground( jxl.format.Colour.LIGHT_TURQUOISE2);
}
cellFormat.setBorder( Border.ALL, BorderLineStyle.THIN, jxl.format.Colour.GRAY_50);
label.setCellFormat(cellFormat);
ws.addCell(label);
j++;
if(preValue.equals(nextValue)){
fal=true;
}
}
nextValue=preValue;
if(fal&&k==1){
ws.mergeCells(0, pre, 0, i+1);
fal=false;
}
i++;
index++;
}
}
}
}
wwb.write();
wwb.close();
os.flush();
os.close();
os = null;
}
/**
* JavaBean转Map
* @param obj
* @return
*/
public static Map<String, Object> beanToMap(Object obj) {
Map<String, Object> params = new HashMap<String, Object>(0);
try {
PropertyUtilsBean propertyUtilsBean = new PropertyUtilsBean();
PropertyDescriptor[] descriptors = propertyUtilsBean.getPropertyDescriptors(obj);
for (int i = 0; i < descriptors.length; i++) {
String name = descriptors[i].getName();
if (!StringUtils.equals(name, "class")) {
params.put(name, propertyUtilsBean.getNestedProperty(obj, name));
}
}
} catch (Exception e) {
e.printStackTrace();
}
return params;
}
/**
* 导出数据为excel主程序<BR>
* *****************************<BR>
* 修改内容:当导出大数据量的时候进行分页功能<BR>
* Wpixel <BR>
* 修改时间2018年03月02日09:16:55<BR>
*/
public static void doExcel(List result, String excel_title, String excel_header,String allCols,
HttpServletResponse response,HttpServletRequest request) throws WriteException, IOException {
ReportModel report = new ReportModel();
report.setTitle(excel_title);
report.setHeader(excel_header);
report.setAvlueList(result);
if (allCols != null && !"".equals(allCols))
report.setFields(allCols.split(","));
ExportReportForCommon.createReport(request, response,report,0);
}
}
Spring控制层
1、NeInfoController.java
package com.wpixel.controller;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;
import com.wpixel.core.JsonResult;
import com.wpixel.service.NeInfoService;
import com.wpixel.util.ExportReportForCommon;
/**
* @ClassName: NeInfoController
* @Description: TODO
* @author: Wpixel
* @date: 2018年03月02日09:38:53
*/
@Controller
@RequestMapping("/2G3Gmanager")
public class NeInfoController {
@Autowired
private NeInfoService neInfoService;
//报表导出
@RequestMapping("/exportTable")
public void exportTable(HttpServletRequest request, HttpServletResponse response){
JsonResult j = null;
try {
//查询出来的数据
j = neInfoService.exportQueryNe(neInfo);
//表头,就是excel第一行数据,我这里就只写两列
String header = "地市,小区名称";
//对应的字段
String allCols = "area,cellName";
ExportReportForCommon.doExcel(j.getData(), "excel名称", header, allCols, response, request);
} catch (Exception e) {
e.printStackTrace();
}
}
}
导出数据OK