Example usage for org.apache.poi.ss.usermodel Sheet setColumnWidth

List of usage examples for org.apache.poi.ss.usermodel Sheet setColumnWidth

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. setColumnWidth

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Sheet setColumnWidth.

Prototype

void setColumnWidth(int columnIndex, int width);

Source Link

Document

Set the width (in units of 1/256th of a character width)

The maximum column width for an individual cell is 255 characters.

Usage

From source file:org.spdx.spdxspreadsheet.ExternalRefsSheet.java

License:Apache License

/**
 * @param wb/*from  w  w w.j av a  2  s . c o m*/
 * @param externalRefsSheetName
 */
public static void create(Workbook wb, String externalRefsSheetName) {
    int sheetNum = wb.getSheetIndex(externalRefsSheetName);
    if (sheetNum >= 0) {
        wb.removeSheetAt(sheetNum);
    }
    Sheet sheet = wb.createSheet(externalRefsSheetName);
    CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
    CellStyle centerStyle = AbstractSheet.createCenterStyle(wb);
    CellStyle wrapStyle = AbstractSheet.createLeftWrapStyle(wb);
    Row row = sheet.createRow(0);
    for (int i = 0; i < HEADER_TITLES.length; i++) {
        sheet.setColumnWidth(i, COLUMN_WIDTHS[i] * 256);
        if (LEFT_WRAP[i]) {
            sheet.setDefaultColumnStyle(i, wrapStyle);
        } else if (CENTER_NOWRAP[i]) {
            sheet.setDefaultColumnStyle(i, centerStyle);
        }
        Cell cell = row.createCell(i);
        cell.setCellStyle(headerStyle);
        cell.setCellValue(HEADER_TITLES[i]);
    }
}

From source file:org.spdx.spdxspreadsheet.NonStandardLicensesSheet.java

License:Apache License

public static void create(Workbook wb, String sheetName) {
    int sheetNum = wb.getSheetIndex(sheetName);
    if (sheetNum >= 0) {
        wb.removeSheetAt(sheetNum);/*from www  .  j  ava  2s.  c  o m*/
    }
    Sheet sheet = wb.createSheet(sheetName);
    CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
    CellStyle centerStyle = AbstractSheet.createCenterStyle(wb);
    CellStyle wrapStyle = AbstractSheet.createLeftWrapStyle(wb);

    Row row = sheet.createRow(0);
    for (int i = 0; i < HEADER_TITLES.length; i++) {
        sheet.setColumnWidth(i, COLUMN_WIDTHS[i] * 256);
        if (LEFT_WRAP[i]) {
            sheet.setDefaultColumnStyle(i, wrapStyle);
        } else if (CENTER_NOWRAP[i]) {
            sheet.setDefaultColumnStyle(i, centerStyle);
        }
        Cell cell = row.createCell(i);
        cell.setCellStyle(headerStyle);
        cell.setCellValue(HEADER_TITLES[i]);
    }
}

From source file:org.spdx.spdxspreadsheet.OriginsSheet.java

License:Apache License

public static void create(Workbook wb, String sheetName) {
    int sheetNum = wb.getSheetIndex(sheetName);
    if (sheetNum >= 0) {
        wb.removeSheetAt(sheetNum);//from  ww w.  j  ava2  s.  c om
    }

    CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
    CellStyle centerStyle = AbstractSheet.createCenterStyle(wb);
    CellStyle wrapStyle = AbstractSheet.createLeftWrapStyle(wb);
    Sheet sheet = wb.createSheet(sheetName);
    Row row = sheet.createRow(0);
    for (int i = 0; i < HEADER_TITLES.length; i++) {
        sheet.setColumnWidth(i, COLUMN_WIDTHS[i] * 256);
        if (LEFT_WRAP[i]) {
            sheet.setDefaultColumnStyle(i, wrapStyle);
        } else if (CENTER_NOWRAP[i]) {
            sheet.setDefaultColumnStyle(i, centerStyle);
        }
        Cell cell = row.createCell(i);
        cell.setCellStyle(headerStyle);
        cell.setCellValue(HEADER_TITLES[i]);
    }
    Row dataRow = sheet.createRow(1);
    Cell ssVersionCell = dataRow.createCell(SPREADSHEET_VERSION_COL);
    ssVersionCell.setCellValue(CURRENT_VERSION);
}

From source file:org.spdx.spdxspreadsheet.OriginsSheetV0d9d4.java

License:Apache License

public static void create(Workbook wb, String sheetName) {
    int sheetNum = wb.getSheetIndex(sheetName);
    if (sheetNum >= 0) {
        wb.removeSheetAt(sheetNum);// w  w w  .j a v a 2  s  . c om
    }

    CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
    CellStyle centerStyle = AbstractSheet.createCenterStyle(wb);
    CellStyle wrapStyle = AbstractSheet.createLeftWrapStyle(wb);
    Sheet sheet = wb.createSheet(sheetName);
    Row row = sheet.createRow(0);
    for (int i = 0; i < HEADER_TITLES.length; i++) {
        sheet.setColumnWidth(i, COLUMN_WIDTHS[i] * 256);
        if (LEFT_WRAP[i]) {
            sheet.setDefaultColumnStyle(i, wrapStyle);
        } else if (CENTER_NOWRAP[i]) {
            sheet.setDefaultColumnStyle(i, centerStyle);
        }
        Cell cell = row.createCell(i);
        cell.setCellStyle(headerStyle);
        cell.setCellValue(HEADER_TITLES[i]);
    }
    Row dataRow = sheet.createRow(1);
    Cell ssVersionCell = dataRow.createCell(SPREADSHEET_VERSION_COL);
    ssVersionCell.setCellValue(SPDXSpreadsheet.CURRENT_VERSION);
}

From source file:org.spdx.spdxspreadsheet.PackageInfoSheetV09d2.java

License:Apache License

public static void create(Workbook wb, String sheetName) {
    int sheetNum = wb.getSheetIndex(sheetName);
    if (sheetNum >= 0) {
        wb.removeSheetAt(sheetNum);/*from w  w w.j a  v a 2  s  .c om*/
    }
    Sheet sheet = wb.createSheet(sheetName);
    CellStyle headerStyle = AbstractSheet.createHeaderStyle(wb);
    CellStyle defaultStyle = AbstractSheet.createLeftWrapStyle(wb);
    Row row = sheet.createRow(0);
    for (int i = 0; i < HEADER_TITLES.length; i++) {
        sheet.setColumnWidth(i, COLUMN_WIDTHS[i] * 256);
        sheet.setDefaultColumnStyle(i, defaultStyle);
        Cell cell = row.createCell(i);
        cell.setCellStyle(headerStyle);
        cell.setCellValue(HEADER_TITLES[i]);
    }
}

From source file:org.xianairlines.action.staffs.StaffsList.java

public void exportStaffsByColumNames() throws UnsupportedEncodingException {
    ServletOutputStream os = null;/*from w  ww. ja va2s. c  o m*/
    try {
        final HttpServletResponse response = (HttpServletResponse) extCtx.getResponse();
        os = response.getOutputStream();
        response.setContentType("application/x-download");
        final String newFileName = encodeFileName("??.xls");
        response.addHeader("Content-disposition", "attachment;filename=" + newFileName + ";charset=UTF-8");
        Workbook wb = new HSSFWorkbook();
        Sheet sheet1 = wb.createSheet("sheet1");
        Row row = null;
        Cell cell = null;
        CellStyle cellStyle = wb.createCellStyle();
        // ?
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);
        // ?
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        // Font
        Font font = wb.createFont();
        font.setFontName("");
        font.setColor(HSSFColor.BLUE.index);
        font.setItalic(true);
        font.setFontHeight((short) 300);
        row = sheet1.createRow(0);
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
        for (int i = 0; i < columNames.length; i++) {
            sheet1.setColumnWidth(i, (short) 6000);
            String[] colums = columNames[i].split(",");
            cell = row.createCell(i);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(colums[1]);
        }
        List<Staffs> list = this.getResultList();
        for (int i = 1; i <= list.size(); i = i + 1) {
            row = sheet1.createRow(i);
            row.setHeightInPoints(20);
            for (int j = 0; j < columNames.length; j++) {
                String[] colums = columNames[j].split(",");
                cell = row.createCell(j);
                cell.setCellStyle(cellStyle);
                Object value = this.getStaffsFieldValue((Staffs) list.get(i - 1), colums[0]);
                if (value == null) {
                    cell.setCellValue("");
                } else if (value instanceof java.util.Date) {
                    String cellValue = dateFormat.format((java.util.Date) value);
                    cell.setCellValue(cellValue);
                } else {
                    cell.setCellValue(value.toString());
                }

            }
        }
        wb.write(os);
        os.flush();
    } catch (Exception e) {
    } finally {
        if (os != null) {
            try {
                os.close();
            } catch (IOException e) {
            }
        }
        facesContext.responseComplete();
    }

}

From source file:packtest.ConditionalFormats.java

License:Apache License

/**
 * Color Scales / Colour Scales / Colour Gradients allow you shade the
 *  background colour of the cell based on the values, eg from Red to
 *  Yellow to Green./*from   ww w.  j  a v a  2s.  c o m*/
 */
static void colourScales(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue("Colour Scales");
    Row r = sheet.createRow(1);
    r.createCell(0).setCellValue("Red-Yellow-Green");
    for (int i = 1; i <= 7; i++) {
        r.createCell(i).setCellValue((i - 1) * 5);
    }
    r = sheet.createRow(2);
    r.createCell(0).setCellValue("Red-White-Blue");
    for (int i = 1; i <= 9; i++) {
        r.createCell(i).setCellValue((i - 1) * 5);
    }
    r = sheet.createRow(3);
    r.createCell(0).setCellValue("Blue-Green");
    for (int i = 1; i <= 16; i++) {
        r.createCell(i).setCellValue((i - 1));
    }
    sheet.setColumnWidth(0, 5000);

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    CellRangeAddress[] regions = { CellRangeAddress.valueOf("B2:H2") };
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingColorScaleRule();
    ColorScaleFormatting cs1 = rule1.getColorScaleFormatting();
    cs1.getThresholds()[0].setRangeType(RangeType.MIN);
    cs1.getThresholds()[1].setRangeType(RangeType.PERCENTILE);
    cs1.getThresholds()[1].setValue(50d);
    cs1.getThresholds()[2].setRangeType(RangeType.MAX);
    ((ExtendedColor) cs1.getColors()[0]).setARGBHex("FFF8696B");
    ((ExtendedColor) cs1.getColors()[1]).setARGBHex("FFFFEB84");
    ((ExtendedColor) cs1.getColors()[2]).setARGBHex("FF63BE7B");
    sheetCF.addConditionalFormatting(regions, rule1);

    regions = new CellRangeAddress[] { CellRangeAddress.valueOf("B3:J3") };
    ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingColorScaleRule();
    ColorScaleFormatting cs2 = rule2.getColorScaleFormatting();
    cs2.getThresholds()[0].setRangeType(RangeType.MIN);
    cs2.getThresholds()[1].setRangeType(RangeType.PERCENTILE);
    cs2.getThresholds()[1].setValue(50d);
    cs2.getThresholds()[2].setRangeType(RangeType.MAX);
    ((ExtendedColor) cs2.getColors()[0]).setARGBHex("FFF8696B");
    ((ExtendedColor) cs2.getColors()[1]).setARGBHex("FFFCFCFF");
    ((ExtendedColor) cs2.getColors()[2]).setARGBHex("FF5A8AC6");
    sheetCF.addConditionalFormatting(regions, rule2);

    regions = new CellRangeAddress[] { CellRangeAddress.valueOf("B4:Q4") };
    ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingColorScaleRule();
    ColorScaleFormatting cs3 = rule3.getColorScaleFormatting();
    cs3.setNumControlPoints(2);
    cs3.getThresholds()[0].setRangeType(RangeType.MIN);
    cs3.getThresholds()[1].setRangeType(RangeType.MAX);
    ((ExtendedColor) cs3.getColors()[0]).setARGBHex("FF5A8AC6");
    ((ExtendedColor) cs3.getColors()[1]).setARGBHex("FF63BE7B");
    sheetCF.addConditionalFormatting(regions, rule3);
}

From source file:packtest.ConditionalFormats.java

License:Apache License

/**
 * DataBars / Data-Bars allow you to have bars shown vary
 *  based on the values, from full to empty
 *//*from   w w w. j  av a 2  s  .com*/
static void dataBars(Sheet sheet) {
    sheet.createRow(0).createCell(0).setCellValue("Data Bars");
    Row r = sheet.createRow(1);
    r.createCell(1).setCellValue("Green Positive");
    r.createCell(2).setCellValue("Blue Mix");
    r.createCell(3).setCellValue("Red Negative");
    r = sheet.createRow(2);
    r.createCell(1).setCellValue(0);
    r.createCell(2).setCellValue(0);
    r.createCell(3).setCellValue(0);
    r = sheet.createRow(3);
    r.createCell(1).setCellValue(5);
    r.createCell(2).setCellValue(-5);
    r.createCell(3).setCellValue(-5);
    r = sheet.createRow(4);
    r.createCell(1).setCellValue(10);
    r.createCell(2).setCellValue(10);
    r.createCell(3).setCellValue(-10);
    r = sheet.createRow(5);
    r.createCell(1).setCellValue(5);
    r.createCell(2).setCellValue(5);
    r.createCell(3).setCellValue(-5);
    r = sheet.createRow(6);
    r.createCell(1).setCellValue(20);
    r.createCell(2).setCellValue(-10);
    r.createCell(3).setCellValue(-20);
    sheet.setColumnWidth(0, 3000);
    sheet.setColumnWidth(1, 5000);
    sheet.setColumnWidth(2, 5000);
    sheet.setColumnWidth(3, 5000);

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    ExtendedColor color = sheet.getWorkbook().getCreationHelper().createExtendedColor();
    color.setARGBHex("FF63BE7B");
    CellRangeAddress[] regions = { CellRangeAddress.valueOf("B2:B7") };
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(color);
    DataBarFormatting db1 = rule1.getDataBarFormatting();
    db1.getMinThreshold().setRangeType(RangeType.MIN);
    db1.getMaxThreshold().setRangeType(RangeType.MAX);
    sheetCF.addConditionalFormatting(regions, rule1);

    color = sheet.getWorkbook().getCreationHelper().createExtendedColor();
    color.setARGBHex("FF5A8AC6");
    regions = new CellRangeAddress[] { CellRangeAddress.valueOf("C2:C7") };
    ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(color);
    DataBarFormatting db2 = rule2.getDataBarFormatting();
    db2.getMinThreshold().setRangeType(RangeType.MIN);
    db2.getMaxThreshold().setRangeType(RangeType.MAX);
    sheetCF.addConditionalFormatting(regions, rule2);

    color = sheet.getWorkbook().getCreationHelper().createExtendedColor();
    color.setARGBHex("FFF8696B");
    regions = new CellRangeAddress[] { CellRangeAddress.valueOf("D2:D7") };
    ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingRule(color);
    DataBarFormatting db3 = rule3.getDataBarFormatting();
    db3.getMinThreshold().setRangeType(RangeType.MIN);
    db3.getMaxThreshold().setRangeType(RangeType.MAX);
    sheetCF.addConditionalFormatting(regions, rule3);
}

From source file:preprocessing.EnemyInfoGenerator.java

private static void writeToSheet(XSSFWorkbook book, List<EnemyInfo> enemyList,
        HashMap<String, List<String[]>> skillMap, HashMap<String, List<String[]>> skillRoleMap,
        HashMap<String, String[]> enemyAiOrderMap, HashMap<String, String[]> enemyLvUpMap) {
    // Write data into sheet.
    Sheet sheet = book.createSheet();
    CellRangeAddress cra;/*from   w  w  w. ja  v  a 2 s .  c om*/
    Row row;
    Cell cell;
    int rowNum, colNum;
    createCellStyles(book);

    for (int i = 0; i < COL_WIDTH.length; i++) {
        sheet.setColumnWidth(i, COL_WIDTH[i]);
    }

    row = getNotNullRow(sheet, 0);
    cell = getNotNullCell(row, 0);
    cell.setCellValue("?");
    cell.setCellStyle(headerStyle);
    for (int j = 1; j < 11; j++) {
        cell = getNotNullCell(row, 0);
        cell.setCellStyle(headerStyle);
    }
    cra = new CellRangeAddress(0, 0, 0, LAST_COL_NUM);
    sheet.addMergedRegion(cra);

    colNum = 0;
    row = getNotNullRow(sheet, 1);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("");
    cell.setCellStyle(headerStyle);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("??");
    cell.setCellStyle(headerStyle);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("");
    cell.setCellStyle(headerStyle);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("HP");
    cell.setCellStyle(headerStyle);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("ATK");
    cell.setCellStyle(headerStyle);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("INT");
    cell.setCellStyle(headerStyle);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("MND");
    cell.setCellStyle(headerStyle);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("DEF");
    cell.setCellStyle(headerStyle);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("MDF");
    cell.setCellStyle(headerStyle);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("");
    cell.setCellStyle(headerStyle);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("?");
    cell.setCellStyle(headerStyle);

    rowNum = 2;
    for (int i = 0; i < enemyList.size(); i++) {
        colNum = 0;
        row = getNotNullRow(sheet, rowNum);
        cell = getNotNullCell(row, colNum++);
        cell.setCellStyle(contentStyle);
        cell = getNotNullCell(row, colNum++);
        cell.setCellValue(enemyList.get(i).getName());
        cell.setCellStyle(contentStyle);
        cell = getNotNullCell(row, colNum++);
        cell.setCellValue(EnumType.getNameById(enemyList.get(i).getType()));
        cell.setCellStyle(contentStyle);
        for (int j = 0; j < 6; j++) {
            cell = getNotNullCell(row, colNum++);
            cell.setCellValue(enemyList.get(i).getCurrentAttr(j));
            cell.setCellStyle(contentStyle);
        }
        String[] enemyPartLvUpArray = enemyLvUpMap.get(enemyList.get(i).getId());
        String findTypeRate = ((BattlePrepare) UIUtil.getBattlePrepare()).findTypeRate(enemyPartLvUpArray,
                enemyList.get(i).getType());
        cell = getNotNullCell(row, colNum++);
        cell.setCellValue(findTypeRate);
        cell.setCellStyle(contentStyle);
        cell = getNotNullCell(row, colNum++);
        cell.setCellValue(enemyList.get(i).getActionPoint());
        cell.setCellStyle(contentStyle);
        rowNum++;
    }
    cra = new CellRangeAddress(1, rowNum - 1, 0, 0);
    sheet.addMergedRegion(cra);

    row = getNotNullRow(sheet, rowNum++);
    cell = getNotNullCell(row, 0);
    cell.setCellValue("");
    cell.setCellStyle(headerStyle);
    cra = new CellRangeAddress(rowNum - 1, rowNum - 1, 1, LAST_COL_NUM);
    sheet.addMergedRegion(cra);

    row = getNotNullRow(sheet, rowNum++);
    cell = getNotNullCell(row, 0);
    cell.setCellValue("");
    cell.setCellStyle(headerStyle);
    cra = new CellRangeAddress(rowNum - 1, rowNum - 1, 0, LAST_COL_NUM);
    sheet.addMergedRegion(cra);

    colNum = 11;
    row = getNotNullRow(sheet, rowNum);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("ID");
    cell.setCellStyle(contentStyle);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("AI?ID");
    cell.setCellStyle(contentStyle);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("");
    cell.setCellStyle(contentStyle);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("");
    cell.setCellStyle(contentStyle);
    cell = getNotNullCell(row, colNum++);
    cell.setCellValue("?");
    cell.setCellStyle(contentStyle);

    for (int p = 0; p < enemyAiOrderTitle.length; p++) {
        cell = getNotNullCell(row, colNum++);
        cell.setCellStyle(contentStyle);
        cell.setCellValue(enemyAiOrderTitle[p]);
    }

    for (int n = 0; n < enemyList.size(); n++) {
        Integer oriRowNum = rowNum;
        row = getNotNullRow(sheet, rowNum++);
        cell = getNotNullCell(row, 0);
        cell.setCellValue(enemyList.get(n).getName());
        cell.setCellStyle(headerStyle);
        cell = getNotNullCell(row, 1);
        cell.setCellValue("??");
        cell.setCellStyle(headerStyle);
        cra = new CellRangeAddress(rowNum - 1, rowNum - 1, 1, 4);
        sheet.addMergedRegion(cra);
        cell = getNotNullCell(row, 5);
        cell.setCellValue("??");
        cell.setCellStyle(headerStyle);
        cra = new CellRangeAddress(rowNum - 1, rowNum - 1, 5, 8);
        sheet.addMergedRegion(cra);
        cell = getNotNullCell(row, 9);
        cell.setCellValue("");
        cell.setCellStyle(headerStyle);
        cell = getNotNullCell(row, 10);
        cell.setCellValue("");
        cell.setCellStyle(headerStyle);

        if (!enemyList.get(n).getPassiveSkill().isEmpty()) {
            row = getNotNullRow(sheet, rowNum++);
            cell = getNotNullCell(row, 9);
            cell.setCellValue("");
            cell.setCellStyle(contentStyle);
            cell = getNotNullCell(row, 10);
            cell.setCellValue("-");
            cell.setCellStyle(contentStyle);
            cell = getNotNullCell(row, 11);
            cell.setCellValue(enemyList.get(n).getPassiveSkill());
            cell.setCellStyle(contentStyle);
        }
        for (int i = 0; i < enemyList.get(n).getSkills().size(); i++) {
            EnemySkill skill = enemyList.get(n).getSkills().get(i);
            row = getNotNullRow(sheet, rowNum++);
            cell = getNotNullCell(row, 1);
            cell.setCellValue("");
            cell.setCellStyle(contentStyle);
            cra = new CellRangeAddress(rowNum - 1, rowNum - 1, 1, 4);
            sheet.addMergedRegion(cra);
            cell = getNotNullCell(row, 5);
            cell.setCellValue("");
            cell.setCellStyle(contentStyle);
            cra = new CellRangeAddress(rowNum - 1, rowNum - 1, 5, 8);
            sheet.addMergedRegion(cra);
            cell = getNotNullCell(row, 9);
            cell.setCellValue(skill.getCost());
            cell.setCellStyle(contentStyle);
            cell = getNotNullCell(row, 10);
            cell.setCellValue(skill.getPriority());
            cell.setCellStyle(contentStyle);

            colNum = 11;
            cell = getNotNullCell(row, colNum++);
            cell.setCellValue(Long.parseLong(skill.getSkillId()));
            cell.setCellStyle(contentStyle);
            cell = getNotNullCell(row, colNum++);
            cell.setCellValue(Long.parseLong(skill.getAiOrderId()));
            cell.setCellStyle(contentStyle);
            cell = getNotNullCell(row, colNum++);
            cell.setCellValue(skill.getTarget());
            cell.setCellStyle(contentStyle);
            cell = getNotNullCell(row, colNum++);
            cell.setCellValue(skill.getMaxTimes());
            cell.setCellStyle(contentStyle);
            cell = getNotNullCell(row, colNum++);
            cell.setCellValue(skill.getSuccessRate());
            cell.setCellStyle(contentStyle);

            String[] aiOrder = enemyAiOrderMap.get(skill.getAiOrderId());
            for (int p = 0; p < ENEMY_AI_ORDER_INDEX.length; p++) {
                if (aiOrder.length > ENEMY_AI_ORDER_INDEX[p]) {
                    cell = getNotNullCell(row, colNum++);
                    cell.setCellStyle(contentStyle);
                    if (aiOrder[ENEMY_AI_ORDER_INDEX[p]].matches("[\\d]+")) {
                        cell.setCellValue(Integer.parseInt(aiOrder[ENEMY_AI_ORDER_INDEX[p]]));
                    } else {
                        cell.setCellValue(aiOrder[ENEMY_AI_ORDER_INDEX[p]]);
                    }
                }
            }

        }
        cra = new CellRangeAddress(oriRowNum, rowNum - 1, 0, 0);
        sheet.addMergedRegion(cra);
    }
    Integer oriRowNum = rowNum;

    row = getNotNullRow(sheet, rowNum++);
    cell = getNotNullCell(row, 0);
    cell.setCellValue("");
    cell.setCellStyle(headerStyle);
    cra = new CellRangeAddress(rowNum - 1, rowNum - 1, 0, LAST_COL_NUM);
    sheet.addMergedRegion(cra);

    row = getNotNullRow(sheet, rowNum++);
    cell = getNotNullCell(row, 0);
    cell.setCellValue("");
    cell.setCellStyle(headerStyle);

    for (int i = 0; i < 11; i++) {
        row = getNotNullRow(sheet, rowNum);
        cell = getNotNullCell(row, 0);
        cell.setCellStyle(headerStyle);
        if (roundName[i].matches("[\\d]+")) {
            cell.setCellValue(Integer.parseInt(roundName[i]));
        } else {
            cell.setCellValue(roundName[i]);
        }
        cra = new CellRangeAddress(rowNum, rowNum + 2, 0, 0);
        sheet.addMergedRegion(cra);
        rowNum += 3;
    }
    cra = new CellRangeAddress(rowNum - 3, rowNum - 1, 1, LAST_COL_NUM);
    sheet.addMergedRegion(cra);

    TreeSet<String> skillSet = new TreeSet<>();
    TreeSet<String> skillRoleSet = new TreeSet<>();
    for (int n = 0; n < enemyList.size(); n++) {
        if (!enemyList.get(n).getPassiveSkill().isEmpty()) {
            String skillId = enemyList.get(n).getPassiveSkill();
            skillSet.add(skillId);
        }
        for (int i = 0; i < enemyList.get(n).getSkills().size(); i++) {
            String skillId = enemyList.get(n).getSkills().get(i).getSkillId();
            skillSet.add(skillId);
        }
    }

    rowNum = oriRowNum;
    rowNum++;
    row = getNotNullRow(sheet, rowNum++);
    colNum = 11;
    for (int p = 0; p < skillArrayTitle.length; p++) {
        cell = getNotNullCell(row, colNum++);
        cell.setCellStyle(contentStyle);
        cell.setCellValue(skillArrayTitle[p]);
    }
    colNum = 19;
    for (int p = 0; p < skillRoleArrayTitle.length; p++) {
        cell = getNotNullCell(row, colNum++);
        cell.setCellStyle(contentStyle);
        cell.setCellValue(skillRoleArrayTitle[p]);
    }
    String skillId;
    while ((skillId = skillSet.pollFirst()) != null) {
        List<String[]> skills = skillMap.get(skillId);
        for (String[] skill : skills) {
            row = getNotNullRow(sheet, rowNum);
            skillRoleSet.add(skill[SKILL_SKILLROLE_COL]);
            List<String[]> skillRoles = skillRoleMap.get(skill[SKILL_SKILLROLE_COL]);
            colNum = 11;
            for (int p = 0; p < SKILL_ARRAY_INDEX.length; p++) {
                if (skill.length > SKILL_ARRAY_INDEX[p]) {
                    cell = getNotNullCell(row, colNum++);
                    cell.setCellStyle(contentStyle);
                    if (skill[SKILL_ARRAY_INDEX[p]].matches("[\\d]+")) {
                        cell.setCellValue(Integer.parseInt(skill[SKILL_ARRAY_INDEX[p]]));
                    } else {
                        cell.setCellValue(skill[SKILL_ARRAY_INDEX[p]]);
                    }
                }
            }
            for (String[] skillRole : skillRoles) {
                row = getNotNullRow(sheet, rowNum);
                colNum = 19;
                for (int p = 0; p < skillRoleArrayIndex.length; p++) {
                    if (skillRole.length > skillRoleArrayIndex[p]) {
                        cell = getNotNullCell(row, colNum++);
                        cell.setCellStyle(contentStyle);
                        if (skillRole[skillRoleArrayIndex[p]].matches("[\\d]+")) {
                            cell.setCellValue(Integer.parseInt(skillRole[skillRoleArrayIndex[p]]));
                        } else {
                            cell.setCellValue(skillRole[skillRoleArrayIndex[p]]);
                        }
                    }
                }
                rowNum++;
            }
        }
    }

    rowNum++;
    //        row = getNotNullRow(sheet, rowNum++);
    //        colNum = 11;
    //        for (int p = 0; p < skillRoleArrayTitle.length; p++) {
    //            cell = getNotNullCell(row, colNum++);
    //            cell.setCellStyle(contentStyle);
    //            cell.setCellValue(skillRoleArrayTitle[p]);
    //        }
    //        String skillRoleId;
    //        while ((skillRoleId = skillRoleSet.pollFirst()) != null) {
    //            List<String[]> skillRoles = skillRoleMap.get(skillRoleId);
    //            for (String[] skillRole : skillRoles) {
    //                row = getNotNullRow(sheet, rowNum);
    //                colNum = 11;
    //                for (int p = 0; p < skillRoleArrayIndex.length; p++) {
    //                    if (skillRole.length > skillRoleArrayIndex[p]) {
    //                        cell = getNotNullCell(row, colNum++);
    //                        cell.setCellStyle(contentStyle);
    //                        if (skillRole[skillRoleArrayIndex[p]].matches("[\\d]+")) {
    //                            cell.setCellValue(Integer.parseInt(skillRole[skillRoleArrayIndex[p]]));
    //                        } else {
    //                            cell.setCellValue(skillRole[skillRoleArrayIndex[p]]);
    //                        }
    //                    }
    //                }
    //                rowNum++;
    //            }
    //        }

    for (int i = 0; i < rowNum + 30; i++) {
        getNotNullRow(sheet, i).setHeight(ROW_HEIGHT.shortValue());
    }

}

From source file:project1.TimesheetDemo.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;//from w  ww. j a v  a2s.co m

    //     if(args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook();
    //     else 
    wb = new XSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    Sheet sheet = wb.createSheet("Timesheet");
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    //title row
    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue("Weekly Timesheet");
    titleCell.setCellStyle(styles.get("title"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));

    //header row
    Row headerRow = sheet.createRow(1);
    headerRow.setHeightInPoints(40);
    Cell headerCell;
    for (int i = 0; i < titles.length; i++) {
        headerCell = headerRow.createCell(i);
        headerCell.setCellValue(titles[i]);
        headerCell.setCellStyle(styles.get("header"));
    }

    int rownum = 2;
    for (int i = 0; i < 10; i++) {
        Row row = sheet.createRow(rownum++);
        for (int j = 0; j < titles.length; j++) {
            Cell cell = row.createCell(j);
            if (j == 9) {
                //the 10th cell contains sum over week days, e.g. SUM(C3:I3)
                String ref = "C" + rownum + ":I" + rownum;
                cell.setCellFormula("SUM(" + ref + ")");
                cell.setCellStyle(styles.get("formula"));
            } else if (j == 11) {
                cell.setCellFormula("J" + rownum + "-K" + rownum);
                cell.setCellStyle(styles.get("formula"));
            } else {
                cell.setCellStyle(styles.get("cell"));
            }
        }
    }

    //row with totals below
    Row sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(35);
    Cell cell;
    cell = sumRow.createCell(0);
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellValue("Total Hrs:");
    cell.setCellStyle(styles.get("formula"));

    for (int j = 2; j < 12; j++) {
        cell = sumRow.createCell(j);
        String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12";
        cell.setCellFormula("SUM(" + ref + ")");
        if (j >= 9)
            cell.setCellStyle(styles.get("formula_2"));
        else
            cell.setCellStyle(styles.get("formula"));
    }
    rownum++;
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Regular Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("L13");
    cell.setCellStyle(styles.get("formula_2"));
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Overtime Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("K13");
    cell.setCellStyle(styles.get("formula_2"));

    //set sample data
    for (int i = 0; i < sample_data.length; i++) {
        Row row = sheet.getRow(2 + i);
        for (int j = 0; j < sample_data[i].length; j++) {
            if (sample_data[i][j] == null)
                continue;

            if (sample_data[i][j] instanceof String) {
                row.getCell(j).setCellValue((String) sample_data[i][j]);
            } else {
                row.getCell(j).setCellValue((Double) sample_data[i][j]);
            }
        }
    }

    //finally set column widths, the width is measured in units of 1/256th of a character width
    sheet.setColumnWidth(0, 30 * 256); //30 characters wide
    for (int i = 2; i < 9; i++) {
        sheet.setColumnWidth(i, 6 * 256); //6 characters wide
    }
    sheet.setColumnWidth(10, 10 * 256); //10 characters wide

    // Write the output to a file
    String file = "timesheet.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.close();
}