poi报表的模板下载

poi报表的模板下载

工具类

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;

public class DownloadUtils {
    public void download(ByteArrayOutputStream byteArrayOutputStream, HttpServletResponse response, String returnName) throws IOException {
        response.setContentType("application/octet-stream");
        returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1"));			//保存的文件名,必须和页面编码一致,否则乱码
        response.addHeader("content-disposition","attachment;filename=" + returnName);
        response.setContentLength(byteArrayOutputStream.size());
        ServletOutputStream outputstream = response.getOutputStream();	//取得输出流
        byteArrayOutputStream.writeTo(outputstream);					//写到输出流
        byteArrayOutputStream.close();									//关闭
        outputstream.flush();											//刷数据
    }
}

模板生成代码

    /**
     * 当月人事报表导出
     *  参数：
     *      年月-月（2018-02%）
     */
    @RequestMapping(value = "/export", method = RequestMethod.GET)
    public void export(String month) throws Exception {
        //1.获取数据报表内容
        List<String> list = new ArrayList<>();

        //2.加载模板:模板文件
        Resource resource = new ClassPathResource("excel-template/test.xlsx");
        FileInputStream fis = new FileInputStream(resource.getFile());

        //3.根据模板创建工作簿
        Workbook wb = new XSSFWorkbook(fis);

        //4.读取工作表
        Sheet sheet = wb.getSheetAt(0); //第一张表

        //5.抽取公共样式
        Row row = sheet.getRow(2);//从第三行开始读取模板
        if (row==null){
            return;
        }

        //6.将第三行数组进行保存
        CellStyle styles [] = new CellStyle[row.getLastCellNum()];

        for (int i = 0; i <row.getLastCellNum() ; i++) {
            Cell cell = row.getCell(i);
            //对数组样式进行==递进赋值
            styles[i]=cell.getCellStyle();
        }
        //6.构造单元格
        int rowIndex=2;
        Cell cell = null;

        for (String s : list) {
            row = sheet.createRow(rowIndex++);
            //编号
            cell = row.createCell(0);
            cell.setCellValue(s);
            cell.setCellStyle(styles[0]);
        }

        //7.下载
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        wb.write(os);
        new DownloadUtils().download(os,response,month+"人事报表.xlsx");

    }