poi导出excel

非模板

controller

@PostMapping("/export")
    @ApiOperation("导出排班表")
    public R export(@RequestBody OaDutyUser entity, HttpServletResponse response) {
        try {
            dutyUserService.export(entity,response);
            return R.ok();
        } catch (Exception e) {
            e.printStackTrace();
            log.error(e.getMessage(), e);
            return R.failed(Constant.EXCEPTION_INFO_PUBLIC);
        }
    }

service

 /**
     * 导出
     *
     * @param scheduleSet
     * @param response
     */
    @Override
    public void exportExcel(OaScheduleSet scheduleSet, HttpServletResponse response) {
        //查询符合条件的数据
        List<OaScheduleSet> list = this.getOaScheduleSetList(scheduleSet);
        //设置文件名
        String filename = "test";
        //获取工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        //获取表格
        XSSFSheet sheet = workbook.createSheet(filename);
        sheet.setColumnWidth(0,3000);
        sheet.setColumnWidth(1,7000);
        sheet.setColumnWidth(2,3000);
        sheet.setColumnWidth(3,3000);
        sheet.setColumnWidth(4,3000);
        sheet.setColumnWidth(5,3000);
        //设置行和列的坐标
        int nRow = 0;
        Row row = null;
        Cell rowCell = null;
        XSSFCellStyle style = null;
        //第一行
        row = sheet.createRow(nRow++);
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,5));
        rowCell = row.createCell(0);
        rowCell.setCellValue("排班管理");
        style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        rowCell.setCellStyle(style);
        //第二行
        row = sheet.createRow(nRow++);
        //排班名称
        rowCell = row.createCell(0);
        rowCell.setCellValue("排班名称");
        rowCell.setCellStyle(cellStyle(style));
        //排班时间
        rowCell = row.createCell(1);
        rowCell.setCellValue("排班时间");
        rowCell.setCellStyle(cellStyle(style));
        //排班类型
        rowCell = row.createCell(2);
        rowCell.setCellValue("排班类型");
        rowCell.setCellStyle(cellStyle(style));
        //是否三级排班
        rowCell = row.createCell(3);
        rowCell.setCellValue("是否三级排班");
        rowCell.setCellStyle(cellStyle(style));
        //设置排班时间
        rowCell = row.createCell(4);
        rowCell.setCellValue("设置排班时间");
        rowCell.setCellStyle(cellStyle(style));
        //设置人
        rowCell = row.createCell(5);
        rowCell.setCellValue("设置人");
        rowCell.setCellStyle(cellStyle(style));
        //第三行开始循环
        for(OaScheduleSet oaScheduleSet:list){
            row = sheet.createRow(nRow++);
            //排班名称
            rowCell = row.createCell(0);
            rowCell.setCellValue(oaScheduleSet.getScTaskName());
            rowCell.setCellStyle(cellStyle(style));
            //排班时间
            rowCell = row.createCell(1);
            rowCell.setCellValue(oaScheduleSet.getScStartDate()+"~"+oaScheduleSet.getScEndDate());
            rowCell.setCellStyle(cellStyle(style));
            //排班类型
            rowCell = row.createCell(2);
            rowCell.setCellValue(oaScheduleSet.getDutyTypeId());
            rowCell.setCellStyle(cellStyle(style));
            //是否三级排班
            rowCell = row.createCell(3);
            rowCell.setCellValue(oaScheduleSet.getIsThreeLevel());
            rowCell.setCellStyle(cellStyle(style));
            //设置排班时间
            rowCell = row.createCell(4);
            rowCell.setCellValue(oaScheduleSet.getCreateDate());
            rowCell.setCellStyle(cellStyle(style));
            //设置人
            rowCell = row.createCell(5);
            rowCell.setCellValue(oaScheduleSet.getCreateUserName());
            rowCell.setCellStyle(cellStyle(style));
        }
        //下载
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            workbook.write(os);
            DownloadUtil.download(os, response, filename + ".xlsx");
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
    }
    /**
     * 设置单元格样式
     * @param style
     * @return
     */
    public static CellStyle cellStyle(XSSFCellStyle style){
        //设置边框线
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        //设置居中
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        return style;
    }

util

/**
 * @author tuchu
 */
public class DownloadUtil {
    //下载
    public static void download(ByteArrayOutputStream byteArrayOutputStream, HttpServletResponse response, String returnName) throws IOException {
        response.setContentType("application/octet-stream;charset=utf-8");
        returnName = response.encodeURL(new String(returnName.getBytes(), "ISO_8859_1"));            //保存的文件名,必须和页面编码一致,否则乱码
        response.addHeader("Content-Disposition", "attachment;filename=" + returnName);
        response.setContentLength(byteArrayOutputStream.size());
        ServletOutputStream outputstream = response.getOutputStream();    //取得输出流
        byteArrayOutputStream.writeTo(outputstream);                    //写到输出流
        byteArrayOutputStream.close();                                    //关闭
        outputstream.flush();                                            //刷数据
    }
}

pom.xml

		 <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.0</version>
        </dependency>

模板

service

public class QuittanceUtils {
    private final static Logger log = LoggerFactory.getLogger(QuittanceUtils.class);

    public static void downloadExcel(FinancialRevenues bean,String type, HttpServletResponse response, HttpServletRequest request) {
        //设置文件名
        String filename = bean.getBillNumber() + "_收据";
        //获取订单详情集合
        String templatePath =  "/static/template/";
        File file = new File(templatePath + "quittance.xlsx");
        //获取工作簿
        XSSFWorkbook workbook = null;
        FileInputStream is = null;
        CellRangeAddress cellRangeAddress = null;//合并单元格
        try {
            is = new FileInputStream(file);
            workbook = new XSSFWorkbook(is);
            //获取表格
            XSSFSheet sheet = workbook.getSheetAt(0);
            //设置行和列的坐标
            int nRow = 0;
            Row row = null;
            Cell rowCell = null;
            XSSFCellStyle style = null;
            //第一行
            nRow++;
            //第二行
            row = sheet.getRow(nRow++);
            rowCell = row.getCell(13);
            rowCell.setCellValue(bean.getBillNumber());//发票编号
            //第三行
            Calendar cal = Calendar.getInstance();
            row = sheet.getRow(nRow++);
            rowCell = row.getCell(7);
            rowCell.setCellValue(cal.get(Calendar.YEAR));//年
            rowCell = row.getCell(10);
            rowCell.setCellValue(cal.get(Calendar.MONTH) + 1);//月
            rowCell = row.getCell(12);
            rowCell.setCellValue(cal.get(Calendar.DATE));//日
            //第四行
            row = sheet.getRow(nRow++);
            rowCell = row.getCell(1);
            rowCell.setCellValue(bean.getUnit());//交款单位
            //第五行
            row = sheet.getRow(nRow++);
            rowCell = row.getCell(1);
            rowCell.setCellValue(type);//交款摘要
            //第六行 写入订单详情
            row = sheet.getRow(nRow++);
            rowCell = row.getCell(1);
            if("swipingcard".equals(bean.getWay())){
                rowCell.setCellValue("刷卡");//收款方式
            }else  if("transfer".equals(bean.getWay())){
                rowCell.setCellValue("转账");//收款方式
            }else  if("cash".equals(bean.getWay())){
                rowCell.setCellValue("现金");//收款方式
            }
            //第七行
            row = sheet.getRow(nRow++);
            rowCell = row.getCell(2);
            String bigMoney = MoneyToCH.number2CNMontrayUnit(new BigDecimal(bean.getMoney()));
            rowCell.setCellValue(bigMoney);//人民币大写
            //第八行
            row = sheet.getRow(nRow++);
            rowCell = row.getCell(5);
            rowCell.setCellValue(type);//人民币小写 亿
            rowCell = row.getCell(6);
            rowCell.setCellValue(type);//人民币小写 千
            rowCell = row.getCell(7);
            rowCell.setCellValue(type);//人民币小写 百
            rowCell = row.getCell(8);
            rowCell.setCellValue(type);//人民币小写 十
            rowCell = row.getCell(9);
            rowCell.setCellValue(type);//人民币小写 万
            rowCell = row.getCell(10);
            rowCell.setCellValue(type);//人民币小写 千
            rowCell = row.getCell(11);
            rowCell.setCellValue(type);//人民币小写 百
            rowCell = row.getCell(12);
            rowCell.setCellValue(type);//人民币小写 十
            rowCell = row.getCell(13);
            rowCell.setCellValue(type);//人民币小写 元
            rowCell = row.getCell(14);
            rowCell.setCellValue(type);//人民币小写 角
            rowCell = row.getCell(15);
            rowCell.setCellValue(type);//人民币小写 分
            //第九行
            row = sheet.getRow(nRow++);
            SysUser sysUser = ShiroUtils.getSysUser();
            rowCell = row.getCell(0);
            rowCell.setCellValue("会计："+sysUser.getUserName());//人民币大写

            rowCell = row.getCell(2);
            rowCell.setCellValue("出纳：");//人民币大写

            rowCell = row.getCell(4);
            rowCell.setCellValue("经手人：");//人民币大写



            //下载
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            try {
                workbook.write(os);
                DownloadUtils.download(os, response, filename + ".xlsx");
            } catch (IOException e) {
                log.info("下载出错!! " + e);
            }

        } catch (
                Exception e) {
            log.info("写入excel出错!! " + e);
        } finally {
            //关闭流
            try {
                is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

    //设置字体和背景
    public static CellStyle setStyle(String str, XSSFCellStyle style, XSSFFont font, XSSFWorkbook workbook) {
        if (str != null && !str.equals("") && !str.equals("无")) {
            //颜色
            String sr = str.substring(1, 3);
            String sg = str.substring(3, 5);
            String sb = str.substring(5, 7);
            //16进制的字符串转为int
            int r = Integer.parseInt(sr, 16);
            int g = Integer.parseInt(sg, 16);
            int b = Integer.parseInt(sb, 16);
            try {
//                //设置自定义颜色
//                IndexedColorMap colorMap = workbook.getStylesSource().getIndexedColors();
//                XSSFColor grey = new XSSFColor(new java.awt.Color(r, g, b), colorMap);
//                style.setFillForegroundColor(grey);
                //设置填充方案
                style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        //设置字体
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);
        font.setBold(true);
        style.setFont(font);
        //设置边框线
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        //设置居中
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        return style;
    }

    //设置字体
    public static CellStyle setStyle(XSSFCellStyle style, XSSFFont font) {
        //设置字体
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);
        font.setBold(true);
        style.setFont(font);
        //设置边框线
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        //设置居中
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        return style;
    }

    //设置账户字体
    public static CellStyle setAccountStyle(XSSFCellStyle style, XSSFFont font) {
        //设置字体
        font.setFontName("微软雅黑");
        font.setFontHeightInPoints((short) 12);
        font.setBold(true);
        style.setFont(font);
        //设置边框线
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
//        //设置居中
//        style.setAlignment(HorizontalAlignment.LEFT);//水平居中
//        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        return style;
    }

    //设置落脚字体
    public static CellStyle setFootStyle(XSSFCellStyle style, XSSFFont font) {

        //设置字体
        font.setFontName("微软雅黑");
        font.setFontHeightInPoints((short) 12);
        font.setBold(true);
        style.setFont(font);
        //设置边框线
        style.setBorderTop(BorderStyle.THIN);
//        style.setBorderBottom(BorderStyle.THIN);
//        style.setBorderLeft(BorderStyle.THIN);
//        style.setBorderRight(BorderStyle.THIN);
        //设置居中
//        style.setAlignment(HorizontalAlignment.LEFT);//水平居中
//        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        return style;
    }

    //设置money字体
    public static CellStyle setMoneyStyle(XSSFCellStyle style, XSSFFont font) {

        //设置字体
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 11);
        font.setBold(true);
        style.setFont(font);
        //设置边框线
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        //设置居中
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        return style;
    }

    //设置合并单元格边框
    public static void setBorder(CellRangeAddress cellRangeAddress, XSSFSheet sheet) {
        RegionUtil.setBorderBottom(BorderStyle.THIN, cellRangeAddress, sheet); // 下边框
        RegionUtil.setBorderTop(BorderStyle.THIN, cellRangeAddress, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, cellRangeAddress, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, cellRangeAddress, sheet);
    }

}