JAVA生成甘特图Excel导出

JAVA生成甘特图EXCEL

场景：以甘特图的方式，可以直观的看到任务的进展情况，资源的利用率等等，它也能帮助你考虑人力、资源、日期、项目中重复的要素和关键的部分
领域：如今甘特图不单单被应用到生产管理领域，随着生产管理的发展、项目管理的扩展，它被应用到了各个领域，如：建筑、IT软件、汽车等等
样例效果：

核心代码实现

 int two_cell = 0;
        Cell cell = two_row.createCell(two_cell);
        cell.setCellValue("序号");
        setTitleCellStyle(cell, workbook, (short) 10, "黑体", HorizontalAlignment.CENTER, IndexedColors.GREY_25_PERCENT.index,false);
        sheet.addMergedRegion(setMergedRegion(2, 3, 0, 0));
        sheet.setColumnWidth(two_cell, 4 * 256);
        two_cell++;

        cell = two_row.createCell(two_cell);
        cell.setCellValue("分项工程");
        setTitleCellStyle(cell, workbook, (short) 10, "黑体", HorizontalAlignment.CENTER, IndexedColors.GREY_25_PERCENT.index,false);
        sheet.addMergedRegion(setMergedRegion(2, 3, 1, 1));
        two_cell++;

        cell = two_row.createCell(two_cell);
        cell.setCellValue("天数");
        setTitleCellStyle(cell, workbook, (short) 10, "黑体", HorizontalAlignment.CENTER, IndexedColors.GREY_25_PERCENT.index,false);
        sheet.addMergedRegion(setMergedRegion(2, 3, 2, 2));
        sheet.setColumnWidth(two_cell, 4 * 256);
        two_cell++;

        Row three_row = sheet.createRow(3);
        createEmptyCell(three_row, workbook, 0, 3, IndexedColors.GREY_25_PERCENT.index);

        int three_cell = two_cell;
        int monthDiff = getDifMonth(endDate, startDate);
        int mergeIndex = 3;
        boolean bool = false;
        for (int i = 0; i <= monthDiff; i++) {
            if (i == monthDiff) {
                int day = endCalendar.get(Calendar.DAY_OF_MONTH);
                if(buildDay > 100){
                    if(day % 2 != 0 && i == 0){
                        day++;
                    }
                    if(i != 0){
                        if(bool && day%2 != 0){
                            day ++;
                        }
                        if(!bool && day%2 != 0){
                            day ++;
                        }
                    }
                }

                Cell cell2 = two_row.createCell(two_cell);
                cell2.setCellValue(dateFormat.format(calendar.getTime()));
                setTitleCellStyle(cell2, workbook, (short) 10, "黑体", HorizontalAlignment.CENTER, IndexedColors.GREY_25_PERCENT.index,false);
                sheet.addMergedRegion(setMergedRegion(2, 2, mergeIndex, mergeIndex + day - 1));
                createEmptyCell(two_row, workbook, mergeIndex + 1, mergeIndex + day, IndexedColors.WHITE.index);
                mergeIndex += day;
                for (int j = 1; j <= day; j++) {
                    Cell cell1 = three_row.createCell(three_cell);
                    cell1.setCellValue(j);
                    sheet.setColumnWidth(three_cell, 4 * 168);
                    setTitleCellStyle(cell1, workbook, (short) 10, "黑体", HorizontalAlignment.CENTER, IndexedColors.GREY_25_PERCENT.index,false);
                    if(buildDay > 100){
                        sheet.setColumnWidth(three_cell, 2 * 155);
                        sheet.addMergedRegion(setMergedRegion(3, 3,two_cell+2*j-2, two_cell+2*j-1));
                    }
                    three_cell++;
                }

            } else {
                int actualMaximum = calendar.getActualMaximum(Calendar.DAY_OF_MONTH);
                if(actualMaximum % 2 != 0 && !bool){
                    bool = true;
                }
                Cell cell1 = two_row.createCell(two_cell);
                cell1.setCellValue(dateFormat.format(calendar.getTime()));
                setTitleCellStyle(cell1, workbook, (short) 10, "黑体", HorizontalAlignment.CENTER, IndexedColors.GREY_25_PERCENT.index,false);
                sheet.addMergedRegion(setMergedRegion(2, 2, mergeIndex, mergeIndex + actualMaximum - 1));
                createEmptyCell(two_row, workbook, mergeIndex + 1, mergeIndex + actualMaximum, IndexedColors.WHITE.index);
                mergeIndex += actualMaximum;
                two_cell = mergeIndex;
                for (int j = 1; j <= actualMaximum; j++) {

                    Cell cell2 = three_row.createCell(three_cell);
                    cell2.setCellValue(j);
                    sheet.setColumnWidth(three_cell, 4 * 168);
                    if(buildDay > 100){
                        sheet.setColumnWidth(three_cell, 2 * 155);

                        if(actualMaximum % 2 == 0 && j!= 1 ){
                            if(j%2 ==0){
                                System.out.println(three_cell-1);
                                System.out.println(three_cell);
                                sheet.addMergedRegion(setMergedRegion(3, 3,three_cell-1, three_cell));
                            }
                        }else{
                            if( j!= 1 && j%2 != 0 ){
                                System.out.println(three_cell-2);
                                System.out.println(three_cell-1);
                                sheet.addMergedRegion(setMergedRegion(3, 3,three_cell-2, three_cell-1));
                            }
                        }
                    }
                    setTitleCellStyle(cell2, workbook, (short) 10, "黑体", HorizontalAlignment.CENTER, IndexedColors.GREY_25_PERCENT.index,false);
                    three_cell++;

                }
                int currentMonth =calendar.get(Calendar.MONTH) + 1;
                int currentDay =calendar.get(Calendar.DAY_OF_MONTH);
                if(currentMonth == 1 && currentDay > 27){
                    calendar.add(Calendar.DATE, 27);
                    System.out.println(dateFormat.format(calendar.getTime()));
                }else{
                    calendar.set(Calendar.MONTH, calendar.get(Calendar.MONTH)+1);
                }

            }

        }
        writeHead(workbook, sheet, mergeIndex);
        writeBody(workbook, sheet, mergeIndex, startDate, calendar);//导出主体数据

        FileOutputStream fileOutputStream = new FileOutputStream("E:\\gtExcel.xlsx");
        workbook.write(fileOutputStream);
        workbook.close();
    }

    private static void createEmptyCell(Row row, Workbook workbook, int mergeIndex, int endIndex, short color) {
        for (int i = mergeIndex; i < endIndex; i++) {
            Cell cell = row.createCell(i);
            setTitleCellStyle(cell, workbook, (short) 10, "黑体", HorizontalAlignment.CENTER, color,false);
        }
    }

    public static void writeHead(Workbook workbook, Sheet sheet, int mergeIndex) {
        //设置标题，详情
        Row row_title = sheet.createRow(0);
        Cell cell3 = row_title.createCell(0);
        cell3.setCellValue(title);
        setHeadCellStyle(cell3, workbook, (short) 16, "黑体", HorizontalAlignment.CENTER);
        sheet.addMergedRegion(setMergedRegion(0, 0, 0, mergeIndex - 1));
        Row row_detail = sheet.createRow(1);
        Cell cell4 = row_detail.createCell(0);
        cell4.setCellValue(detail);
        setHeadCellStyle(cell4, workbook, (short) 12, "黑体", HorizontalAlignment.LEFT);
        sheet.addMergedRegion(setMergedRegion(1, 1, 0, mergeIndex - 1));
    }

    public static void writeBody(Workbook workbook, Sheet sheet, int mergeIndex, Date startDate, Calendar calendar) {
        int row = 4;
        int SerialNumber = 0;
        if (sarrayList.size() > 0) {
            for (SubProject subProject : sarrayList) {
                Row row1 = sheet.createRow(row);
                int cellValue = 0;
                Cell cell1 = row1.createCell(cellValue);
                cell1.setCellValue(SerialNumber);
                setTitleCellStyle(cell1, workbook, (short) 8, "黑体", HorizontalAlignment.CENTER, IndexedColors.WHITE.index,false);
                SerialNumber++;
                cellValue++;

                cell1 = row1.createCell(cellValue);
                cell1.setCellValue(subProject.getProjectName());
                sheet.setColumnWidth(cellValue, 20 * 256);
                if(YesNoEnum.no.getIndex().equals(subProject.getIsNode())){
                    setTitleCellStyle(cell1, workbook, (short) 10, "黑体", HorizontalAlignment.LEFT, IndexedColors.WHITE.index,true);
                }else{
                    setTitleCellStyle(cell1, workbook, (short) 10, "黑体", HorizontalAlignment.LEFT, IndexedColors.WHITE.index,false);
                }

                cellValue++;

                cell1 = row1.createCell(cellValue);
                cell1.setCellValue(subProject.getDays());
                setTitleCellStyle(cell1, workbook, (short) 8, "黑体", HorizontalAlignment.CENTER, IndexedColors.WHITE.index,false);
                cellValue++;

                if (subProject.getDays() == 0) {
                    createEmptyCell(row1, workbook, 3, mergeIndex, IndexedColors.WHITE.index);
                    row++;
                    continue;
                }
                int subMonthDiff = getDifMonth(subProject.getStartDay(), startDate);
                int index = 3;
                calendar.setTime(startDate);
                for (int i = 0; i < subMonthDiff; i++) {
                    calendar.add(Calendar.MONTH,i);
                    int actualMaximum = calendar.getActualMaximum(Calendar.DAY_OF_MONTH);
                    index += actualMaximum;
                }
                Calendar instance = Calendar.getInstance();
                instance.setTime(subProject.getStartDay());
                index += instance.get(Calendar.DAY_OF_MONTH);

                for (int j = 3; j < mergeIndex; j++) {
                    Integer days = subProject.getDays();
                    Cell cell2 = row1.createCell(j);
                    if (j >= index - 1 && j < index + days - 1) {
                        System.out.println(LocalDateUtils.addDays(instance.getTime(),j-index+1));
                        setTitleCellStyle(cell2, workbook, (short) 10, "黑体", HorizontalAlignment.CENTER, IndexedColors.PINK.index,false);
                    } else {
                        setTitleCellStyle(cell2, workbook, (short) 10, "黑体", HorizontalAlignment.CENTER, IndexedColors.WHITE.index,false);
                    }
                }
                row++;
            }
        }
    }

    public static void setHeadCellStyle(Cell cell, Workbook workbook, short fontSize, String fontName, HorizontalAlignment type) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(type);
        Font font = workbook.createFont();
        font.setFontName(fontName);
        font.setFontHeightInPoints(fontSize);
        cellStyle.setFont(font);
        cell.setCellStyle(cellStyle);
    }

    public static void setTitleCellStyle(Cell cell, Workbook workbook, short fontSize, String fontName, HorizontalAlignment type, short color,boolean bool) {
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillForegroundColor(color);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setAlignment(type);
        Font font = workbook.createFont();
        font.setFontName(fontName);
        font.setFontHeightInPoints(fontSize);
        font.setBold(bool);
        cellStyle.setFont(font);
        setBorder(cellStyle);
        cell.setCellStyle(cellStyle);
    }


    public static void setBorder(CellStyle cellStyle) {
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle.setBorderRight(BorderStyle.THIN);//右边框
    }

    public static CellRangeAddress setMergedRegion(int firstRow, int lastRow, int firstCol, int lastCol) {
        CellRangeAddress cellRangeAddress = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        return cellRangeAddress;
    }

---

提示：完整代码可私信
创作不易,转载请写出处