POI的Excel表格导出教程

一、jsp页面

 1.导出按钮

<button type="button" id="export">导出</button>

2.搜索栏条件

<div class="input-group pull-right">
    <div class="input-group">
        <select id="deptId" name="deptId" class="input-sm form-control"  style="width:50%;">
            <option value>全部部门</option>
            <c:forEach items="${depts}" var="dept">
                <option value="${dept.id}">${dept.name}</option>
            </c:forEach>
        </select>
        <input id="name" type="text" name="name" placeholder="姓名" class="input-sm form-control" style="width:50%;">
        <span class="input-group-btn">
          <button class="btn btn-sm btn-default action-refresh"
                  type="button">搜索
          </button>
        </span>
    </div>

二、js代码（将搜索栏的搜索条件通过js生成的表单提交给后台）

$('body').delegate("#export",'click',function () {
   var form = $('<form>');
   form.hide();
   form.attr('target','')
   form.attr("method",'post');
   form.attr('action','wage/export');
   form.append($('input[name= "name"]').clone());
   form.append($("select[name = 'deptId']").clone());
   $('body').append(form);
   form.submit();
   form.remove();
});

三、后台controller

@RequestMapping("export")
public void export(HttpServletRequest request, HttpServletResponse response ,Integer deptId ,String name){
    String fileName = "月工资明细";
    try {
        response.setContentType(request.getServletContext().getMimeType(fileName));
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
        ExportWageToExcel.export(wageService.findByPageAndCondition(deptId,name,null,null), response.getOutputStream());
    }catch (Exception e){
        logger.error(e.getMessage(),e);
    }
}

说明：

wageService.findByPageAndCondition(deptId,name,null,null)返回的是要导出为Excel表的数据集合List<WageDTO> list;

四、ExportWageToExcel类

import com.jchvip.rch.dto.WageDTO;
import com.jchvip.rch.exception.RCHException;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;

import java.io.OutputStream;
import java.util.List;

/**
 * @author Lix
 * @date 2018/1/24 15:20
 */
public class ExportWageToExcel extends BaseExportToExcel {
    Logger logger = Logger.getLogger(ExportWageToExcel.class);
    static String[][] headers;
    @Override
    protected String[] getSheetNames() {
        return new String[]{"月工资明细"};
    }

    @Override
    protected  String[][] getHeaders() {
         headers = new String[][] {{" 所属部门 ","姓名","基薪","岗位","效益","职称津贴","奖金","补发工资","加班工资",
        "应发工资","扣失业保险","扣医疗保险","扣大额医疗","扣养老保险","扣住房公积金","扣企业年金（税前）",
        "扣个税","扣企业年金（税后）","实发工资"}};
        return headers;
    }

    public static void export(List<WageDTO> list, OutputStream os) throws RCHException {
        ExportWageToExcel self = new ExportWageToExcel();
        self.export();//画工作簿整体框架
        HSSFSheet sheet = self.sheets[0];//获取工作表
        int rows = 1;//工作表第0行是表头，所以内容的第一行的索引为1
        //把list的内容画入工作表
        for(WageDTO dto : list){
            HSSFRow row = sheet.createRow(rows);
            addColumn(row,0,dto.getDeptNames());
            addColumn(row,1,dto.getStaffName());
            addColumn(row,2,dto.getBackPay()+"");
            addColumn(row,3,dto.getJob()+"");
            addColumn(row,4,dto.getMerit()+"");
            addColumn(row,5,dto.getPositional()+"");
            addColumn(row,6,dto.getBonus()+"");
            addColumn(row,7,dto.getBackPay()+"");
            addColumn(row,8,dto.getOvertime()+"");
            addColumn(row,9,dto.getTotal()+"");
            addColumn(row,10,dto.getUnemployment()+"");
            addColumn(row,11,dto.getMedical()+"");
            addColumn(row,12,dto.getLargeMedical()+"");
            addColumn(row,13,dto.getEndowment()+"");
            addColumn(row,14,dto.getHouse()+"");
            addColumn(row,15,dto.getAnnuityBeforTax()+"");
            addColumn(row,16,dto.getTax()+"");
            addColumn(row,17,dto.getAnnuityAfterTax()+"");
            addColumn(row,18,dto.getNetPay()+"");

            rows++;
        }
        for(int i = 0; i < headers[0].length; i++){
//            sheet.autoSizeColumn(i,true); 自动列宽
            //中文时自动列宽不起作用可用下面方法  headers[0][i]是列名
            sheet.setColumnWidth(i, headers[0][i].getBytes().length*2*256);
        }
        try{
            self.wb.write(os);
            os.close();
            self.wb.close();
        }catch (Exception e){
            e.printStackTrace();
            throw new RCHException("导出月工资明细失败！");
        }
    }
}

五、BaseExportToExcel类

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;

public abstract class BaseExportToExcel {
    protected HSSFSheet[] sheets;//工作表数组
    protected HSSFWorkbook wb;//工作簿
    /*
    *画工作簿整体框架
    *
    * */
    protected void export() {
        wb = new HSSFWorkbook();//创建工作簿
        String[] sheetNames = getSheetNames();//获得工作表表名称
        String[][] headers = getHeaders();//获得每个工作表对应的列名

        if (ValidateUtil.objectIsNull(sheets))
            sheets = new HSSFSheet[sheetNames.length];//根据工作表名字的个数创建存放相应个数的工作表数组

        for (int i = 0; i < sheetNames.length; i++) {
            HSSFSheet sheet = wb.createSheet(sheetNames[i]);//创建工作表
            sheet.setDefaultRowHeight((short) (sheet.getDefaultRowHeight() * 2));//设置行高
            createHeader(sheet, headers[i]);//根据列名创建各个列
            sheets[i] = sheet;
        }
    }

    protected abstract String[] getSheetNames();

    protected abstract String[][] getHeaders();
    /*
    * 根据列名数组创建各个列
    * */
    protected void createHeader(HSSFSheet sheet, String[] header) {
        HSSFRow row = sheet.createRow(0);//创建第一行
        for (int i = 0; i < header.length; i++) {
            addColumn(row, i, header[i]);//根据列名创建列
        }
    }
    /*
    * 合并单元格
    * */
    protected static void addRegion(HSSFSheet sheet, HSSFRow row, int rowFrom, int columnFrom, int rowTo, int columnTo, String value) {
        CellRangeAddress region = new CellRangeAddress(rowFrom, rowTo, columnFrom
                , columnTo);
        sheet.addMergedRegion(region);
    }

    protected static void addColumn(HSSFRow r, int column, String value) {
        HSSFCell cell = r.createCell(column);//创建单元格

        HSSFCellStyle style = cell.getCellStyle();//创建单元格样式添加器
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//设置垂直样式、垂直居中
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置单元格内容为字符串
        cell.setCellValue(value);//设置单元格内容
    }
}