apache.poi Excel表格导出demo分享

excel表格导出

/**
     * 
     *  [导出Excel]
     *  [功能详细描述]
     *  @return
     *  @throws IOException
     *  @throws ParseException    
     * @exception/throws [违例类型] [违例说明]
     * @see [类、类#方法、类#成员]
     */
    @SuppressWarnings("unchecked")
    public JSONObject exportExcel() throws IOException, ParseException {
   		//获取数据generateData();，对接自己要打印的数据
        List<OaDutyMaininfo> list = generateData();
        //创建Excel工作薄对象
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建Excel工作表对象
        HSSFCellStyle style = workbook.createCellStyle();

        /**************************************excle第一行***********************************/
        HSSFSheet sheet = workbook.createSheet("工作表1");
        HSSFRow row = sheet.createRow(0);
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints((short) 14);//设置字号
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        style.setFont(font);
        String year = String.format("%tY", new Date());
        String month = String.format("%tm", startDate);
        HSSFCell cell = row.createCell(0);
        //此处设置标题
        cell.setCellValue("xxxxxxxx"+year+"年"+(Integer.parseInt(month))+"月份值班表");
        cell.setCellStyle(style);
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, 5);
        //创建样式，（注意：每一个需要设置的行样式都必须加入此行代码）
        sheet.addMergedRegion(region);

        /**************************************excle第二行表头***********************************/
        row = sheet.createRow(1);
        //设置样式
        font = workbook.createFont();
        font.setFontHeightInPoints((short) 14);//设置字号
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//字体加粗
        //设置水平对齐的样式为居中对齐;  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐; 
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        font.setFontName("思源宋体");//字体
        style = workbook.createCellStyle();
        region = new CellRangeAddress(1, 1, 2, 3);
        sheet.addMergedRegion(region);
        style.setWrapText(true);
        style.setFont(font);
        row.setHeightInPoints(20);
        style.setBorderTop(HSSFCellStyle. BORDER_MEDIUM);
        style.setBorderBottom(HSSFCellStyle. BORDER_MEDIUM);
        style.setBorderLeft(HSSFCellStyle. BORDER_MEDIUM);
        style.setBorderRight(HSSFCellStyle. BORDER_MEDIUM);
        style = workbook.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        
        String[] args = {"日期", "星期", "值班人员","值班人员", "带班领导", "民警"};
        //设置列宽
        int[] width = {8, 8, 16, 28, 16, 11};
        for (int i = 0; i < args.length; i++) {
            sheet.setColumnWidth(i, width[i] * 256);//设置第i列的宽度是xx个字符宽度
            cell = row.createCell(i);
            cell.setCellStyle(style);
            cell.setCellValue(args[i]);
        }
       
        /**************************************excle第n行***********************************/

        //设置数据样式
        font = workbook.createFont();
        font.setFontHeightInPoints((short) 10);//设置字号
       
        font.setFontName("思源宋体");//字体
        font.setBoldweight((short) 200);//字体
        style = workbook.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        style.setBorderTop(HSSFCellStyle. BORDER_MEDIUM);
        style.setBorderBottom(HSSFCellStyle. BORDER_MEDIUM);
        style.setBorderLeft(HSSFCellStyle. BORDER_MEDIUM);
        style.setBorderRight(HSSFCellStyle. BORDER_MEDIUM);
        
        style.setFont(font);
        //创建行的单元格，从3开始
        int i = 2;
        String lastday="";
        int dayint=0;
        for (OaDutyMaininfo oaDutyMaininfo : list) {
            //合并单元格
            String daytemp = String.format("%td", oaDutyMaininfo.getThedate());
            if (lastday.equals(daytemp) || lastday==null || StringUtil.isBlank(daytemp)) {
                lastday = daytemp;
                dayint = dayint+1;
                //判断是否是最后一天74-75(用来处理最后一天是节假日的情况)
                if (list.size()== (i-1)) {
                    region = new CellRangeAddress(i-dayint+1, i, 0, 0);
                    sheet.addMergedRegion(region);
                    region = new CellRangeAddress(i-dayint+1, i, 1, 1);
                    sheet.addMergedRegion(region);
                    region = new CellRangeAddress(i-dayint+1, i, 4, 4);
                    sheet.addMergedRegion(region);
                    region = new CellRangeAddress(i-dayint+1, i, 5, 5);
                    sheet.addMergedRegion(region);
                }
            }
            else {
                if (dayint>1) {
                    region = new CellRangeAddress(i-dayint, i-1, 0, 0);
                    sheet.addMergedRegion(region);
                    region = new CellRangeAddress(i-dayint, i-1, 1, 1);
                    sheet.addMergedRegion(region);
                    region = new CellRangeAddress(i-dayint, i-1, 4, 4);
                    sheet.addMergedRegion(region);
                    region = new CellRangeAddress(i-dayint, i-1, 5, 5);
                    sheet.addMergedRegion(region);
                }
                lastday = daytemp;
                dayint = 1;
            }
            
            row = sheet.createRow(i);
            row.setHeightInPoints(15);
            //日期
            cell = row.createCell(0);
            cell.setCellStyle(style);
            if (StringUtil.isNotBlank(oaDutyMaininfo.getThedate())) {
                String day = String.format("%td", oaDutyMaininfo.getThedate());
                cell.setCellValue(Integer.parseInt(day));
            }
            //星期
            cell = row.createCell(1);
            cell.setCellStyle(style);
            String week = dateToWeek(oaDutyMaininfo.getThedate());
            cell.setCellValue(week);
            //值班人员（班次）
            cell = row.createCell(2);
            cell.setCellStyle(style);
            cell.setCellValue(oaDutyMaininfo.getTip());
            //值班人员
            cell = row.createCell(3);
            cell.setCellStyle(style);
            cell.setCellValue(oaDutyMaininfo.get("remix").toString());
            //带班领导
            cell = row.createCell(4);
            cell.setCellStyle(style);
            cell.setCellValue(oaDutyMaininfo.get("leader").toString());
            //民警
            cell = row.createCell(5);
            cell.setCellStyle(style);
            cell.setCellValue("");
            i++;
        }
        /**************************************excle最后一行***********************************/
        row = sheet.createRow(i++);
        //设置样式
        font = workbook.createFont();
        font.setFontName("思源宋体");//字体
        style.setFont(font);
        style.setBorderTop(HSSFCellStyle. BORDER_MEDIUM);
        style.setBorderBottom(HSSFCellStyle. BORDER_MEDIUM);
        style.setBorderLeft(HSSFCellStyle. BORDER_MEDIUM);
        style.setBorderRight(HSSFCellStyle. BORDER_MEDIUM);
        style = workbook.createCellStyle();//创建新的样式
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        style.setAlignment(HSSFCellStyle.ALIGN_LEFT);//水平左对齐
        style.setFont(font);
        cell = row.createCell(0);
        cell.setCellStyle(style);
        style.setWrapText(true);
        cell.setCellValue("一.值班时间\r\n" + 
                "工作日 午班11:40-13:30（夏季11:40-14:30） 夜班17:30-次日08:30\r\n" + 
                "节假日 上午08:00-13:00 下午 13:00-18:00    夜班 18:00-次日08:00\r\n" + 
                "二.值班要求\r\n" + 
                "1.值班人员要严格按照值班工作要求，及时接听电话，妥善处理值班事务，认真填写值班日记，遇紧急情况，及时向带班领导和办公室主任王文礼汇报 xxxxxxxxx，值班电话不得长期占用处理个人事务。\r\n" + 
                "2.值班人员要按时到岗，确因事不能按时值班的，提前自行调整，并报局办公室备案；交接班时，必须等到值班人员到岗后方能离开，如接班人员未按时到岗，要电话通知接班人员或通知办公室，严禁出现漏岗现象。\r\n" + 
                "3.根据上级要求，带班领导要24小时在位，如有突发事件，带班领导要第一时间向局主要领导汇报，并及时通知分管领导赶赴现场进行处置，分管领导确有特殊情况请自行协调调整，通知局办公室备案。\r\n" + 
                "4.周六、周日（节假日）遇有市委市政府通知取紧急文件时，请值班人员通知保安人员短暂替班，由值班人员前往取回文件并及时处理。\r\n" + 
                "市委值班电话：xxxxxxxxxx 市政府应急办：xxxxxxx 市维稳办：xxxxxx");
        region = new CellRangeAddress(i-1, i+13, 0, 5);
        sheet.addMergedRegion(region);
        
        /**************************************excle最后1行i***********************************/
        
        
        String guid = UUID.randomUUID().toString();

        //文档输出
        String url = ClassPathUtil.getDeployWarPath() + "epointtemp/" + guid + ".xlsx";
        FileOutputStream out = new FileOutputStream(url);
        workbook.write(out);
        out.close();
        workbook.close();
        JSONObject jsonObject = new JSONObject();
        //导出文件路径返回前台打开
        jsonObject.put("url", "../../../epointtemp/" + guid + ".xlsx");
        return jsonObject;
    }

//前端打开下载代码： window.open(data.url);

效果图