Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook createSheet

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook createSheet

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. createSheet

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFWorkbook createSheet.

Prototype

@Override
public XSSFSheet createSheet()

Source Link

Document

Create an XSSFSheet for this workbook, adds it to the sheets and returns the high level representation.

Usage

From source file:packtest.CreatePivotTable.java

License:Apache License

public static void main(String[] args) throws FileNotFoundException, IOException, InvalidFormatException {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet();

    //Create some data to build the pivot table on
    setCellData(sheet);/*from ww  w.ja  va2s  .c  o m*/

    XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:D4"), new CellReference("H5"));
    //Configure the pivot table
    //Use first column as row label
    pivotTable.addRowLabel(0);
    //Sum up the second column
    pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 1);
    //Set the third column as filter
    pivotTable.addColumnLabel(DataConsolidateFunction.AVERAGE, 2);
    //Add filter on forth column
    pivotTable.addReportFilter(3);

    FileOutputStream fileOut = new FileOutputStream(Utils.getPath("ooxml-pivottable.xlsx"));
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}

From source file:packtest.WorkingWithRichText.java

License:Apache License

public static void main(String[] args) throws Exception {

    XSSFWorkbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
    try {// ww w  .ja v a 2s  .  com
        XSSFSheet sheet = wb.createSheet();
        XSSFRow row = sheet.createRow((short) 2);

        XSSFCell cell = row.createCell(1);
        XSSFRichTextString rt = new XSSFRichTextString("The quick brown fox");

        XSSFFont font1 = wb.createFont();
        font1.setBold(true);
        font1.setColor(new XSSFColor(new java.awt.Color(255, 0, 0)));
        rt.applyFont(0, 10, font1);

        XSSFFont font2 = wb.createFont();
        font2.setItalic(true);
        font2.setUnderline(XSSFFont.U_DOUBLE);
        font2.setColor(new XSSFColor(new java.awt.Color(0, 255, 0)));
        rt.applyFont(10, 19, font2);

        XSSFFont font3 = wb.createFont();
        font3.setColor(new XSSFColor(new java.awt.Color(0, 0, 255)));
        rt.append(" Jumped over the lazy dog", font3);

        cell.setCellValue(rt);

        // Write the output to a file
        OutputStream fileOut = new FileOutputStream(Utils.getPath("xssf-richtext.xlsx"));
        try {
            wb.write(fileOut);
        } finally {
            fileOut.close();
        }
    } finally {
        wb.close();
    }
}

From source file:poi.xslf.usermodel.PieChartDemo.java

License:Apache License

public static void main(String[] args) throws Exception {
    if (args.length < 2) {
        usage();// w w  w.  ja va2s  .co  m
        return;
    }

    BufferedReader modelReader = new BufferedReader(new FileReader(args[1]));

    String chartTitle = modelReader.readLine(); // first line is chart title

    XMLSlideShow pptx = new XMLSlideShow(new FileInputStream(args[0]));
    XSLFSlide slide = pptx.getSlides()[0];

    // find chart in the slide
    XSLFChart chart = null;
    for (POIXMLDocumentPart part : slide.getRelations()) {
        if (part instanceof XSLFChart) {
            chart = (XSLFChart) part;
            break;
        }
    }

    if (chart == null)
        throw new IllegalStateException("chart not found in the template");

    // embedded Excel workbook that holds the chart data
    POIXMLDocumentPart xlsPart = chart.getRelations().get(0);
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet();

    CTChart ctChart = chart.getCTChart();
    CTPlotArea plotArea = ctChart.getPlotArea();

    CTPieChart pieChart = plotArea.getPieChartArray(0);
    //Pie Chart Series
    CTPieSer ser = pieChart.getSerArray(0);

    // Series Text
    CTSerTx tx = ser.getTx();
    tx.getStrRef().getStrCache().getPtArray(0).setV(chartTitle);
    sheet.createRow(0).createCell(1).setCellValue(chartTitle);
    String titleRef = new CellReference(sheet.getSheetName(), 0, 1, true, true).formatAsString();
    tx.getStrRef().setF(titleRef);

    // Category Axis Data
    CTAxDataSource cat = ser.getCat();
    CTStrData strData = cat.getStrRef().getStrCache();

    // Values
    CTNumDataSource val = ser.getVal();
    CTNumData numData = val.getNumRef().getNumCache();

    strData.setPtArray(null); // unset old axis text
    numData.setPtArray(null); // unset old values

    // set model
    int idx = 0;
    int rownum = 1;
    String ln;
    while ((ln = modelReader.readLine()) != null) {
        String[] vals = ln.split("\\s+");
        CTNumVal numVal = numData.addNewPt();
        numVal.setIdx(idx);
        numVal.setV(vals[1]);

        CTStrVal sVal = strData.addNewPt();
        sVal.setIdx(idx);
        sVal.setV(vals[0]);

        idx++;
        XSSFRow row = sheet.createRow(rownum++);
        row.createCell(0).setCellValue(vals[0]);
        row.createCell(1).setCellValue(Double.valueOf(vals[1]));
    }
    numData.getPtCount().setVal(idx);
    strData.getPtCount().setVal(idx);

    String numDataRange = new CellRangeAddress(1, rownum - 1, 1, 1).formatAsString(sheet.getSheetName(), true);
    val.getNumRef().setF(numDataRange);
    String axisDataRange = new CellRangeAddress(1, rownum - 1, 0, 0).formatAsString(sheet.getSheetName(), true);
    cat.getStrRef().setF(axisDataRange);

    // updated the embedded workbook with the data
    OutputStream xlsOut = xlsPart.getPackagePart().getOutputStream();
    wb.write(xlsOut);
    xlsOut.close();

    // save the result
    FileOutputStream out = new FileOutputStream("pie-chart-demo-output.pptx");
    pptx.write(out);
    out.close();
}

From source file:poi.xssf.usermodel.examples.AligningCells.java

License:Apache License

public static void main(String[] args) throws IOException {
    XSSFWorkbook wb = new XSSFWorkbook();

    XSSFSheet sheet = wb.createSheet();
    XSSFRow row = sheet.createRow((short) 2);
    row.setHeightInPoints(30);//from w  w  w.  j a  v a  2  s.c om
    for (int i = 0; i < 8; i++) {
        //column width is set in units of 1/256th of a character width
        sheet.setColumnWidth(i, 256 * 15);
    }

    createCell(wb, row, (short) 0, XSSFCellStyle.ALIGN_CENTER, XSSFCellStyle.VERTICAL_BOTTOM);
    createCell(wb, row, (short) 1, XSSFCellStyle.ALIGN_CENTER_SELECTION, XSSFCellStyle.VERTICAL_BOTTOM);
    createCell(wb, row, (short) 2, XSSFCellStyle.ALIGN_FILL, XSSFCellStyle.VERTICAL_CENTER);
    createCell(wb, row, (short) 3, XSSFCellStyle.ALIGN_GENERAL, XSSFCellStyle.VERTICAL_CENTER);
    createCell(wb, row, (short) 4, XSSFCellStyle.ALIGN_JUSTIFY, XSSFCellStyle.VERTICAL_JUSTIFY);
    createCell(wb, row, (short) 5, XSSFCellStyle.ALIGN_LEFT, XSSFCellStyle.VERTICAL_TOP);
    createCell(wb, row, (short) 6, XSSFCellStyle.ALIGN_RIGHT, XSSFCellStyle.VERTICAL_TOP);

    //center text over B4, C4, D4
    row = sheet.createRow((short) 3);
    centerAcrossSelection(wb, row, (short) 1, (short) 3, XSSFCellStyle.VERTICAL_CENTER);

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("xssf-align.xlsx");
    wb.write(fileOut);
    fileOut.close();
}

From source file:poi.xssf.usermodel.examples.WorkingWithRichText.java

License:Apache License

public static void main(String[] args) throws Exception {

    XSSFWorkbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();

    XSSFSheet sheet = wb.createSheet();
    XSSFRow row = sheet.createRow((short) 2);

    XSSFCell cell = row.createCell(1);// w ww  .j  a  v a  2 s . c  o m
    XSSFRichTextString rt = new XSSFRichTextString("The quick brown fox");

    XSSFFont font1 = wb.createFont();
    font1.setBold(true);
    font1.setColor(new XSSFColor(new java.awt.Color(255, 0, 0)));
    rt.applyFont(0, 10, font1);

    XSSFFont font2 = wb.createFont();
    font2.setItalic(true);
    font2.setUnderline(XSSFFont.U_DOUBLE);
    font2.setColor(new XSSFColor(new java.awt.Color(0, 255, 0)));
    rt.applyFont(10, 19, font2);

    XSSFFont font3 = wb.createFont();
    font3.setColor(new XSSFColor(new java.awt.Color(0, 0, 255)));
    rt.append(" Jumped over the lazy dog", font3);

    cell.setCellValue(rt);

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("xssf-richtext.xlsx");
    wb.write(fileOut);
    fileOut.close();
}

From source file:poisample2.POIsample2.java

/**
 * @param args the command line arguments
 *///from  w  w  w  . j a  va2  s .c  o m
public static void main(String[] args) {
    // TODO code application logic here
    try {
        XSSFWorkbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet();
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        cell.setCellValue("Hi there");
        OutputStream out;
        out = new FileOutputStream(new File("testout.xlsx"));
        wb.write(out);
        out.flush();
        out.close();
    } catch (Exception e) {
        e.printStackTrace();
    }

}

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  .j a v a 2  s  .  c o m
    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:punchcardrecords.ui.PunchCardRecordsMainFrame.java

License:Open Source License

/**
 * Excel/*from   w w  w  .  j av  a  2s.c om*/
 * @param year 
 */
private void writeYearCountResult(Map<String, double[]> yearRecords, String path) {
    XSSFWorkbook resultWorkBook = new XSSFWorkbook();
    XSSFSheet sheet = resultWorkBook.createSheet();
    Set<String> userNumNames = yearRecords.keySet();
    // 
    String[] titles = { "?", "??", "()", "(?)",
            "?(?)" };
    int col = 0;// 
    XSSFRow titleRow = sheet.createRow(0);
    for (String title : titles) {
        titleRow.createCell(col).setCellValue(title);
        sheet.autoSizeColumn((short) (col));
        col++;
    }
    int row = 1;// 
    // 
    for (String userStr : userNumNames) {
        XSSFRow recordRow = sheet.createRow(row);
        int innerCol = 0;// ?
        // ?
        String[] userInfos = userStr.split(":");
        for (String userInfo : userInfos) {
            recordRow.createCell(innerCol).setCellValue(userInfo);
            innerCol++;
        }
        // 
        double[] records = yearRecords.get(userStr);
        for (double record : records) {
            recordRow.createCell(innerCol).setCellValue(record);
            innerCol++;
        }
        // ?
        recordRow.createCell(innerCol)
                .setCellValue(new BigDecimal(records[0] == 0 ? 0 : records[1] / records[0])
                        .setScale(1, BigDecimal.ROUND_HALF_UP).doubleValue());
        row++;
    }

    // ???,??
    JFileChooser fileSaveChooser = new JFileChooser();

    fileSaveChooser.setDialogTitle("?");
    fileSaveChooser.setSelectedFile(new File(path + "/.xlsx"));
    String[] saveType = { "xlsx" };
    fileSaveChooser.setAcceptAllFileFilterUsed(false);
    fileSaveChooser.setFileFilter(new FileNameExtensionFilter("*.xlsx", saveType));
    int saveResult = fileSaveChooser.showSaveDialog(this);
    if (saveResult == JFileChooser.APPROVE_OPTION) {
        File saveFile = fileSaveChooser.getSelectedFile();

        // ???
        String saveFilePath = saveFile.getAbsolutePath();
        addMessage("?,??->" + saveFilePath);
        FileOutputStream out = null;
        try {
            out = new FileOutputStream(saveFile);
            resultWorkBook.write(out);
            out.close();
        } catch (FileNotFoundException ex) {
            Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (out != null) {
                    out.close();
                }
            } catch (IOException ex) {
                Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        Object[] options = { "", "", ",?" };
        int response = JOptionPane.showOptionDialog(this,
                "??,???", "?", JOptionPane.YES_OPTION,
                JOptionPane.QUESTION_MESSAGE, null, options, options[0]);
        if (0 == response) {// 
            // ??
            addMessage(",??");
            try {
                Desktop.getDesktop().open(saveFile);
            } catch (IOException ex) {
                Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex);
            }
        } else if (1 == response) {// 
            addMessage(",??");
            String[] cmd = new String[5];
            cmd[0] = "cmd";
            cmd[1] = "/c";
            cmd[2] = "start";
            cmd[3] = " ";
            cmd[4] = saveFile.getAbsolutePath().substring(0, saveFile.getAbsolutePath().lastIndexOf("\\"));
            try {
                Runtime.getRuntime().exec(cmd);
            } catch (IOException ex) {
                Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex);
            }
        } else {
            alert("??,?()");
        }
    } else {
        // ??,?
        clearMessage();
        fileName.setText("");
        // ???
        addMessage("??");
    }
}

From source file:ru.inkontext.poi.CreateCustomPivotTable.java

License:Apache License

public static void main(String[] args) throws IOException, InvalidFormatException {

    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet();

    //Create some data to build the pivot table on
    setCellData(sheet);/*from   w w  w  .ja  v a  2  s.co m*/

    new CustomPivotTable(sheet, "A1:D6", "F3").addRowLabel(0) // set first column as 1-th level of rows
            .excludeSubTotal(0) // excude subtotal
            .addRowLabel(1) // set second column of source as 2-th level of rows
            .addColLabel(3).setFormatPivotField(3, 9).addColumnLabel(DataConsolidateFunction.SUM, 2) // Sum up the second column
            .setFormatDataField(2, 4); //# ##0.00

    FileOutputStream fileOut = new FileOutputStream("custom-pivottable.xlsx");
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}

From source file:ru.inkontext.poi.CreatePivotTableSimple.java

License:Apache License

public static void main(String[] args) throws IOException, InvalidFormatException {

    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet();

    //Create some data to build the pivot table on
    setCellData(sheet);//w ww  .  j a  va2  s .c o m

    XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:C6", SpreadsheetVersion.EXCEL2007),
            new CellReference("E3"));

    pivotTable.addRowLabel(1); // set second column as 1-th level of rows
    setFormatPivotField(pivotTable, 1, 9); //set format numFmtId=9 0%
    pivotTable.addRowLabel(0); // set first column as 2-th level of rows
    pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 2); // Sum up the second column
    setFormatDataField(pivotTable, 2, 3); //numFmtId=3 # ##0

    FileOutputStream fileOut = new FileOutputStream("stackoverflow-pivottable.xlsx");
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}