JavaWeb - Excel报表导出（1）

前段时间做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