SpringMVC使用poi生成Excel文件并下载

SpringMVC使用poi生成Excel文件并下载

废话不多说直接上代码

首先你需要一个工具栏WriteExcel

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

public class WriteExcel{

    /**
     * 生成Excel表格
     *
     * @param sheetName sheet名称
     * @param titleList 表头列表
     * @param dataList  数据列表
     * @return HSSFWorkbook对象
     */
    public static HSSFWorkbook createExcel(String sheetName,
                                           List<String> titleList, List dataList) throws IllegalAccessException {
        //创建HSSFWorkbook对象
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建sheet对象
        HSSFSheet sheet = wb.createSheet(sheetName);
        //在sheet里创建第一行，这里即是表头
        HSSFRow rowTitle = sheet.createRow(0);

        //写入表头的每一个列
        for (int i = 0; i < titleList.size(); i++) {
            //创建单元格
            rowTitle.createCell(i).setCellValue(titleList.get(i));
        }

        //写入每一行的记录
        for (int i = 0; i < dataList.size(); i++) {
            //创建新的一行，递增
            HSSFRow rowData = sheet.createRow(i + 1);

            //通过反射，获取POJO对象
            Class cl = dataList.get(i).getClass();
            //获取类的所有字段
            Field[] fields = cl.getDeclaredFields();
            for (int j = 0; j < titleList.size(); j++) {
                //设置字段可见，否则会报错，禁止访问
                fields[j].setAccessible(true);
                //创建单元格
                if (!fields[j].getType().toString().contains("String")) {
                    rowData.createCell(j).setCellValue(Double.parseDouble(fields[j].get(dataList.get(i)).toString()));
                } else {
                    rowData.createCell(j).setCellValue(String.valueOf(fields[j].get(dataList.get(i))));
                }
            }
        }
        return wb;
    }
}

关于工具类的补充

1.如何设置样式

HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);// 左右居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
        style.setWrapText(true);
        sheet.setDefaultColumnWidth(20);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);

2.如何合并单元格

//开始行，结束行，开始列，结束列
  CellRangeAddress region = new CellRangeAddress(0，0，0，0);
            sheet.addMergedRegion(region);
            //给合并后的单元格添加边框
            RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet);
            RegionUtil.setBorderLeft(BorderStyle.THIN, region, sheet);
            RegionUtil.setBorderRight(BorderStyle.THIN, region, sheet);
            RegionUtil.setBorderTop(BorderStyle.THIN, region, sheet);

SpringMvc下载生成的Excel文件

这里只写的Controller中的代码，实际上应该将调用WriteExcel类的方法，构建数据等工作放在在Service中

 @RequestMapping(value = "/outexcel.json", method = RequestMethod.GET)
    public @ResponseBody
    String outexcel(String param, HttpServletResponse response) throws IOException, IllegalAccessException {

        String fileName = "文件名";
        //这里List的数据就是你要生成Excel表格中的数据，根据实际业务去获取，我这里就不写了
        List titleList=new Arraylist();
        List datalist=new Arraylist();
        HSSFWorkbook workbook = null;
        try {
            workbook = (HSSFWorkbook) WriteExcel.createExcel(fileName,titleList,datalist);
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        OutputStream output = response.getOutputStream();
        response.reset();
        //中文名称要进行编码处理
        response.setHeader("Content-disposition", "attachment; filename=" + java.net.URLEncoder.encode(fileName + ".xls", "UTF-8"));
        response.setContentType("application/x-xls");
        // response.setContentType("application/vnd.ms-excel");
        //response.setHeader("Content-Disposition", "attachment;" + "filename=\"" + fileName + "\"");
        //response.setContentType("application/octet-stream");
        workbook.write(output);
        output.close();
        return null;
    }