List of usage examples for org.apache.poi.ss.usermodel Sheet addMergedRegion
int addMergedRegion(CellRangeAddress region);
From source file:org.seasar.fisshplate.core.element.AbstractCell.java
License:Apache License
private void mergeCell(FPContext context) { int columnFrom = context.getCurrentCellNum(); int rowFrom = context.getCurrentRowNum(); CellRangeAddress reg = new CellRangeAddress(rowFrom, rowFrom + relativeMergedRowNumTo, columnFrom, columnFrom + relativeMergedColumnTo); Sheet hssfSheet = context.getOutSheet(); hssfSheet.addMergedRegion(reg); }
From source file:org.tiefaces.components.websheet.utility.CellUtility.java
License:MIT License
/** * Copy rows./*from w w w .j a v a 2s.c o m*/ * * @param srcSheet * the src sheet * @param destSheet * the dest sheet * @param srcRowStart * the src row start * @param srcRowEnd * the src row end * @param destRow * the dest row * @param checkLock * the check lock * @param setHiddenColumn * the set hidden column */ public static void copyRows(final Sheet srcSheet, final Sheet destSheet, final int srcRowStart, final int srcRowEnd, final int destRow, final boolean checkLock, final boolean setHiddenColumn) { int length = srcRowEnd - srcRowStart + 1; if (length <= 0) { return; } destSheet.shiftRows(destRow, destSheet.getLastRowNum(), length, true, false); for (int i = 0; i < length; i++) { copySingleRow(srcSheet, destSheet, srcRowStart + i, destRow + i, checkLock, setHiddenColumn); } // If there are are any merged regions in the source row, copy to new // row for (int i = 0; i < srcSheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = srcSheet.getMergedRegion(i); if ((cellRangeAddress.getFirstRow() >= srcRowStart) && (cellRangeAddress.getLastRow() <= srcRowEnd)) { int targetRowFrom = cellRangeAddress.getFirstRow() - srcRowStart + destRow; int targetRowTo = cellRangeAddress.getLastRow() - srcRowStart + destRow; CellRangeAddress newCellRangeAddress = new CellRangeAddress(targetRowFrom, targetRowTo, cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); destSheet.addMergedRegion(newCellRangeAddress); } } }
From source file:org.waterforpeople.mapping.dataexport.GraphicalSurveySummaryExporter.java
License:Open Source License
/** * Writes the report as an XLS document//from w w w.j a v a2s .com */ private void writeSummaryReport(Map<QuestionGroupDto, List<QuestionDto>> questionMap, SummaryModel summaryModel, String sector, Workbook wb) throws Exception { String title = sector == null ? SUMMARY_LABEL.get(locale) : sector; Sheet sheet = null; int sheetCount = 2; String curTitle = WorkbookUtil.createSafeSheetName(title); while (sheet == null) { sheet = wb.getSheet(curTitle); if (sheet == null) { sheet = wb.createSheet(WorkbookUtil.createSafeSheetName(curTitle)); } else { sheet = null; curTitle = title + " " + sheetCount; sheetCount++; } } CreationHelper creationHelper = wb.getCreationHelper(); Drawing patriarch = sheet.createDrawingPatriarch(); int curRow = 0; Row row = getRow(curRow++, sheet); if (sector == null) { createCell(row, 0, REPORT_HEADER.get(locale), headerStyle); } else { createCell(row, 0, sector + " " + REPORT_HEADER.get(locale), headerStyle); } for (QuestionGroupDto group : orderedGroupList) { if (questionMap.get(group) != null) { for (QuestionDto question : questionMap.get(group)) { if (!(QuestionType.OPTION == question.getType() || QuestionType.NUMBER == question.getType())) { continue; } else { if (summaryModel.getResponseCountsForQuestion(question.getKeyId(), sector).size() == 0) { // if there is no data, skip the question continue; } } // for both options and numeric, we want a pie chart and // data table for numeric, we also want descriptive // statistics int tableTopRow = curRow++; int tableBottomRow = curRow; row = getRow(tableTopRow, sheet); // span the question heading over the data table sheet.addMergedRegion(new CellRangeAddress(curRow - 1, curRow - 1, 0, 2)); createCell(row, 0, getLocalizedText(question.getText(), question.getTranslationMap()), headerStyle); DescriptiveStats stats = summaryModel.getDescriptiveStatsForQuestion(question.getKeyId(), sector); if (stats != null && stats.getSampleCount() > 0) { sheet.addMergedRegion(new CellRangeAddress(curRow - 1, curRow - 1, 4, 5)); createCell(row, 4, getLocalizedText(question.getText(), question.getTranslationMap()), headerStyle); } row = getRow(curRow++, sheet); createCell(row, 1, FREQ_LABEL.get(locale), headerStyle); createCell(row, 2, PCT_LABEL.get(locale), headerStyle); // now create the data table for the option count Map<String, Long> counts = summaryModel.getResponseCountsForQuestion(question.getKeyId(), sector); int sampleTotal = 0; List<String> labels = new ArrayList<String>(); List<String> values = new ArrayList<String>(); int firstOptRow = curRow; for (Entry<String, Long> count : counts.entrySet()) { row = getRow(curRow++, sheet); String labelText = count.getKey(); if (labelText == null) { labelText = ""; } StringBuilder builder = new StringBuilder(); if (QuestionType.OPTION == question.getType() && !DEFAULT_LOCALE.equals(locale)) { String[] tokens = labelText.split("\\|"); // see if we have a translation for this option for (int i = 0; i < tokens.length; i++) { if (i > 0) { builder.append("|"); } if (question.getOptionContainerDto() != null && question.getOptionContainerDto().getOptionsList() != null) { boolean found = false; for (QuestionOptionDto opt : question.getOptionContainerDto() .getOptionsList()) { if (opt.getText() != null && opt.getText().trim().equalsIgnoreCase(tokens[i])) { builder.append(getLocalizedText(tokens[i], opt.getTranslationMap())); found = true; break; } } if (!found) { builder.append(tokens[i]); } } } } else { builder.append(labelText); } createCell(row, 0, builder.toString(), null); createCell(row, 1, count.getValue().toString(), null); labels.add(builder.toString()); values.add(count.getValue().toString()); sampleTotal += count.getValue(); } row = getRow(curRow++, sheet); createCell(row, 0, TOTAL_LABEL.get(locale), null); createCell(row, 1, sampleTotal + "", null); for (int i = 0; i < values.size(); i++) { row = getRow(firstOptRow + i, sheet); if (sampleTotal > 0) { createCell(row, 2, PCT_FMT.format((Double.parseDouble(values.get(i)) / sampleTotal)), null); } else { createCell(row, 2, PCT_FMT.format(0), null); } } tableBottomRow = curRow; if (stats != null && stats.getSampleCount() > 0) { int tempRow = tableTopRow + 1; row = getRow(tempRow++, sheet); createCell(row, 4, "N", null); createCell(row, 5, sampleTotal + "", null); row = getRow(tempRow++, sheet); createCell(row, 4, MEAN_LABEL.get(locale), null); createCell(row, 5, stats.getMean() + "", null); row = getRow(tempRow++, sheet); createCell(row, 4, STD_E_LABEL.get(locale), null); createCell(row, 5, stats.getStandardError() + "", null); row = getRow(tempRow++, sheet); createCell(row, 4, MEDIAN_LABEL.get(locale), null); createCell(row, 5, stats.getMedian() + "", null); row = getRow(tempRow++, sheet); createCell(row, 4, MODE_LABEL.get(locale), null); createCell(row, 5, stats.getMode() + "", null); row = getRow(tempRow++, sheet); createCell(row, 4, STD_D_LABEL.get(locale), null); createCell(row, 5, stats.getStandardDeviation() + "", null); row = getRow(tempRow++, sheet); createCell(row, 4, VAR_LABEL.get(locale), null); createCell(row, 5, stats.getVariance() + "", null); row = getRow(tempRow++, sheet); createCell(row, 4, RANGE_LABEL.get(locale), null); createCell(row, 5, stats.getRange() + "", null); row = getRow(tempRow++, sheet); createCell(row, 4, MIN_LABEL.get(locale), null); createCell(row, 5, stats.getMin() + "", null); row = getRow(tempRow++, sheet); createCell(row, 4, MAX_LABEL.get(locale), null); createCell(row, 5, stats.getMax() + "", null); if (tableBottomRow < tempRow) { tableBottomRow = tempRow; } } curRow = tableBottomRow; if (labels.size() > 0) { boolean hasVals = false; if (values != null) { for (String val : values) { try { if (val != null && new Double(val.trim()) > 0D) { hasVals = true; break; } } catch (Exception e) { // no-op } } } // only insert the image if we have at least 1 non-zero // value if (hasVals && generateCharts) { // now insert the graph int indx = wb.addPicture(JFreechartChartUtil.getPieChart(labels, values, getLocalizedText(question.getText(), question.getTranslationMap()), CHART_WIDTH, CHART_HEIGHT), Workbook.PICTURE_TYPE_PNG); ClientAnchor anchor = creationHelper.createClientAnchor(); anchor.setDx1(0); anchor.setDy1(0); anchor.setDx2(0); anchor.setDy2(255); anchor.setCol1(6); anchor.setRow1(tableTopRow); anchor.setCol2(6 + CHART_CELL_WIDTH); anchor.setRow2(tableTopRow + CHART_CELL_HEIGHT); anchor.setAnchorType(2); patriarch.createPicture(anchor, indx); if (tableTopRow + CHART_CELL_HEIGHT > tableBottomRow) { curRow = tableTopRow + CHART_CELL_HEIGHT; } } } // add a blank row between questions getRow(curRow++, sheet); // flush the sheet so far to disk; we will not go back up ((SXSSFSheet) sheet).flushRows(0); // retain 0 last rows and // flush all others } } } }
From source file:output.ExcelM3Upgrad.java
private void writeMigration() { Sheet sheet = workbook.getSheetAt(0); workbook.setSheetName(0, "Migration"); sheet.setDisplayGridlines(false);/*from w w w . j a v a2 s . c o m*/ sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); styles = createStyles(workbook); int rownum = beginROW; int cellnum = beginCOL; Row row = sheet.createRow(rownum++); for (int k = 0; k < model.getListColumn().length; k++) { Cell cell = row.createCell(cellnum++); cell.setCellValue(i18n.Language.getLabel(model.getListColumn()[k].getIdLng())); cell.setCellStyle(styles.get("header")); sheet.setColumnHidden(cell.getColumnIndex(), model.getListColumn()[k].isHidden()); sheet.autoSizeColumn(k); dialStatus(); } ArrayList<Integer> listHeader = new ArrayList<>(); for (int i = 0; i < M3UpdObjModel.header.length; i++) { listHeader.add(M3UpdObjModel.header[i]); } String[] listLevel = i18n.Language.traduce(Ressource.listLevel) .toArray(new String[Ressource.listLevel.length]); data = model.getData(); for (int i = 0; i < data.length; i++) { busyDial.setText("Alimentation de la ligne " + (i + 1) + " sur " + data.length); row = sheet.createRow(rownum++); Object[] objArr = data[i]; cellnum = beginCOL; boolean first = true; int j = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); if (obj instanceof Date) { cell.setCellValue((Date) obj); } else if (obj instanceof Boolean) { if (first) { first = false; if ((Boolean) obj) { cell.setCellValue("Oui"); } else { cell.setCellValue("Non"); } } else { if ((Boolean) obj) { cell.setCellValue("OK"); } else { cell.setCellValue("KO"); } } } else if (obj instanceof String) { cell.setCellValue((String) obj); } else if (obj instanceof Double) { cell.setCellValue((Double) obj); } if (listHeader.indexOf(218) == j) { try { int n = Integer.parseInt(obj.toString().trim()); if (n == -1) { cell.setCellValue("ERROR"); } else { cell.setCellValue(listLevel[n]); } } catch (NumberFormatException ex) { cell.setCellValue(""); } } if (j < objArr.length - 3) { cell.setCellStyle(styles.get("cell_b_centered_locked")); } else { cell.setCellStyle(styles.get("cell_b_centered")); } j++; dialStatus(); } dialStatus(); } dialStatus(); busyDial.setText("Formatage du document"); CellRangeAddressList userList = new CellRangeAddressList(beginROW + 1, beginROW + data.length, beginCOL + data[0].length - 1, beginCOL + data[0].length - 1); DataValidationConstraint userConstraint; DataValidation userValidation; if (type == 0) { userConstraint = DVConstraint.createExplicitListConstraint((String[]) model.getM3UserModel() .getListUserSelect().toArray(new String[model.getM3UserModel().getListUserSelect().size()])); userValidation = new HSSFDataValidation(userList, userConstraint); } else { XSSFDataValidationHelper userHelper = new XSSFDataValidationHelper((XSSFSheet) sheet); userConstraint = (XSSFDataValidationConstraint) userHelper .createExplicitListConstraint((String[]) model.getM3UserModel().getListUserSelect() .toArray(new String[model.getM3UserModel().getListUserSelect().size()])); userValidation = (XSSFDataValidation) userHelper.createValidation(userConstraint, userList); } sheet.addValidationData(userValidation); CellRangeAddressList migList = new CellRangeAddressList(beginROW + 1, beginROW + data.length, beginCOL + data[0].length - 2, beginCOL + data[0].length - 2); DataValidationConstraint migConstraint; DataValidation migValidation; if (type == 0) { migConstraint = DVConstraint.createExplicitListConstraint(new String[] { "OK", "KO" }); migValidation = new HSSFDataValidation(migList, migConstraint); } else { XSSFDataValidationHelper migHelper = new XSSFDataValidationHelper((XSSFSheet) sheet); migConstraint = (XSSFDataValidationConstraint) migHelper .createExplicitListConstraint(new String[] { "OK", "KO" }); migValidation = (XSSFDataValidation) migHelper.createValidation(migConstraint, migList); } sheet.addValidationData(migValidation); CellRangeAddressList levelList = new CellRangeAddressList(beginROW + 1, beginROW + data.length, beginCOL + data[0].length - 3, beginCOL + data[0].length - 3); DataValidationConstraint levelConstraint; DataValidation levelValidation; ArrayList<String> listNameLevel = new ArrayList<>(); listNameLevel.add("ERROR"); listNameLevel.addAll(i18n.Language.traduce(Ressource.listLevel));//.toArray(new String[Ressource.listLevel.length]) if (type == 0) { levelConstraint = DVConstraint .createExplicitListConstraint(listNameLevel.toArray(new String[listNameLevel.size()])); levelValidation = new HSSFDataValidation(levelList, levelConstraint); } else { XSSFDataValidationHelper levelHelper = new XSSFDataValidationHelper((XSSFSheet) sheet); levelConstraint = (XSSFDataValidationConstraint) levelHelper.createExplicitListConstraint( i18n.Language.traduce(Ressource.listLevel).toArray(new String[Ressource.listLevel.length])); levelValidation = (XSSFDataValidation) levelHelper.createValidation(levelConstraint, levelList); } sheet.addValidationData(levelValidation); int irow = beginROW; int icol = beginCOL + model.getListColumn().length + 2; row = sheet.getRow(irow); Cell cell = row.createCell(icol); sheet.addMergedRegion(new CellRangeAddress(irow, irow, icol, icol + 1)); cell.setCellValue("Estimation de la charge"); cell.setCellStyle(styles.get("header")); irow++; row = sheet.getRow(irow); int cpt = 0; ArrayList<String> listStringLevel = i18n.Language.traduce(Ressource.listLevel); for (String s : listStringLevel) { cell = row.createCell(icol); cell.setCellValue(s); cell.setCellStyle(styles.get("cell_b_centered_locked")); cell = row.createCell(icol + 1); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); String columnLetter = CellReference.convertNumToColString(listHeader.indexOf(218) + beginCOL); cell.setCellFormula( "COUNTIF(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":" + columnLetter + (beginROW + data.length + 1) + ",\"" + s + "\")*" + Ressource.listWeightLevel[cpt]); cell.setCellStyle(styles.get("cell_b_centered_locked")); irow++; row = sheet.getRow(irow); cpt++; } row = sheet.getRow(irow); cell = row.createCell(icol); cell.setCellValue("Total des charges"); cell.setCellStyle(styles.get("cell_b_centered_locked")); cell = row.createCell(icol + 1); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); String columnLetter = CellReference.convertNumToColString(listHeader.indexOf(icol + 1)); cell.setCellFormula("SUM(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":" + columnLetter + (beginROW + Ressource.listLevel.length + 1) + ")"); cell.setCellStyle(styles.get("cell_b_centered_locked")); for (int k = 0; k < model.getListColumn().length + 3; k++) { sheet.autoSizeColumn(k); } sheet.protectSheet("3kles2014"); }
From source file:output.ExcelM3Upgrad.java
private void writeGraph() { busyDial.setText("Gnration des graphiques statistiques"); Sheet s = workbook.getSheetAt(1); workbook.setSheetName(1, "Statistiques"); ArrayList<Integer> listHeader = new ArrayList<>(); for (int i = 0; i < M3UpdObjModel.header.length; i++) { listHeader.add(M3UpdObjModel.header[i]); }//from w ww .j av a 2s. c o m int irow = 4; Row row = s.createRow(irow); Cell cell = row.createCell(2); s.addMergedRegion(new CellRangeAddress(irow, irow, 2, 7)); cell.setCellValue("Rpartition des spcifiques"); cell.setCellStyle(styles.get("cell_centered_locked")); irow = 8; row = s.createRow(irow); for (int i = 0; i < com.app.main.Ressource.listTypeM3Entity.length; i++) { cell = row.createCell(3); cell.setCellValue(com.app.main.Ressource.listTypeM3Entity[i]); cell = row.createCell(4); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); String columnLetter = CellReference.convertNumToColString(beginCOL); cell.setCellFormula("COUNTIF(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":" + columnLetter + (beginROW + data.length + 1) + ",\"" + com.app.main.Ressource.listTypeM3Entity[i] + "\")"); irow++; row = s.createRow(irow); dialStatus(); } irow = 4; row = s.getRow(irow); cell = row.createCell(10); s.addMergedRegion(new CellRangeAddress(irow, irow, 10, 15)); cell.setCellValue("Existance des sources"); cell.setCellStyle(styles.get("cell_centered_locked")); int posVal = listHeader.indexOf(199); posVal += beginCOL; irow = 8; row = s.getRow(irow); cell = row.createCell(12); cell.setCellValue("OK"); cell = row.createCell(13); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); String columnLetter = CellReference.convertNumToColString(posVal); cell.setCellFormula("COUNTIF(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":" + columnLetter + (beginROW + data.length + 1) + ",\"" + "Oui" + "\")"); irow++; row = s.getRow(irow); cell = row.createCell(12); cell.setCellValue("NOK"); cell = row.createCell(13); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); // columnLetter = CellReference.convertNumToColString(posVal); cell.setCellFormula("COUNTIF(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":" + columnLetter + (beginROW + data.length + 1) + ",\"" + "Non" + "\")"); irow = 24; row = s.createRow(irow); cell = row.createCell(2); s.addMergedRegion(new CellRangeAddress(irow, irow, 2, 7)); cell.setCellValue("Synthse de migration"); cell.setCellStyle(styles.get("cell_centered_locked")); int posMig = listHeader.indexOf(201); posMig += beginCOL; int posUser = listHeader.indexOf(202); posUser += beginCOL; irow = 28; row = s.createRow(irow); cell = row.createCell(3); cell.setCellValue("OK+USER"); cell = row.createCell(4); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); String columnMig = CellReference.convertNumToColString(posMig); String columnUser = CellReference.convertNumToColString(posUser); cell.setCellFormula("SUMPRODUCT((" + workbook.getSheetName(0) + "!" + columnMig + (beginROW + 2) + ":" + columnMig + (beginROW + data.length + 1) + "=\"" + "OK" + "\")*(" + workbook.getSheetName(0) + "!" + columnUser + (beginROW + 2) + ":" + columnUser + (beginROW + data.length + 1) + "<>\"" + "" + "\"))"); irow++; row = s.createRow(irow); cell = row.createCell(3); cell.setCellValue("OK"); cell = row.createCell(4); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); cell.setCellFormula("SUMPRODUCT((" + workbook.getSheetName(0) + "!" + columnMig + (beginROW + 2) + ":" + columnMig + (beginROW + data.length + 1) + "=\"" + "OK" + "\")*(" + workbook.getSheetName(0) + "!" + columnUser + (beginROW + 2) + ":" + columnUser + (beginROW + data.length + 1) + "=\"" + "" + "\"))"); irow++; row = s.createRow(irow); cell = row.createCell(3); cell.setCellValue("NOK"); cell = row.createCell(4); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); cell.setCellFormula("COUNTIF(" + workbook.getSheetName(0) + "!" + columnMig + (beginROW + 2) + ":" + columnMig + (beginROW + data.length + 1) + ",\"KO\")"); irow++; row = s.createRow(irow); cell = row.createCell(3); cell.setCellValue("Somme"); cell = row.createCell(4); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); String posSum = CellReference.convertNumToColString(4); cell.setCellFormula("SUM(" + posSum + (irow - 2) + ":" + posSum + (irow) + ")"); posVal = listHeader.indexOf(217); posVal += beginCOL; irow = 24; row = s.getRow(irow); cell = row.createCell(10); s.addMergedRegion(new CellRangeAddress(irow, irow, 10, 15)); cell.setCellValue("Analyse des objets instanciables"); cell.setCellStyle(styles.get("cell_centered_locked")); irow = 28; row = s.getRow(irow); cell = row.createCell(12); cell.setCellValue("Class OK"); cell = row.createCell(13); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); columnLetter = CellReference.convertNumToColString(posVal); cell.setCellFormula("COUNTIF(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":" + columnLetter + (beginROW + data.length + 1) + ",\"" + "" + "\")"); irow++; row = s.getRow(irow); cell = row.createCell(12); cell.setCellValue("Class NOK"); cell = row.createCell(13); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); columnLetter = CellReference.convertNumToColString(posVal); //cell.setCellFormula("COUNTIF(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":" + columnLetter + (beginROW + data.length + 1) + ",\"" + "<>" + "\"&\"" + "*" + "\")"); cell.setCellFormula("E32-N29"); s.protectSheet("3kles2014"); }
From source file:OutputStyles.DiffExcelDefault.java
private void SetHeaderRow(Sheet sheet, TreeSet<String> sampleSet, TreeSet<String> comparisonSet) { // Merged upper row Row FHeaderRow = sheet.createRow(0); FHeaderRow.setHeightInPoints(20f);/*from w w w. j ava 2 s. com*/ Cell locCell = FHeaderRow.createCell(0); locCell.setCellValue("Gene and Location Data"); locCell.setCellStyle(this.headerStyles.get("grey")); CellRangeAddress first = new CellRangeAddress(0, 0, 0, 4); Cell sampCell = FHeaderRow.createCell(5); sampCell.setCellValue("Sample RPKM values"); sampCell.setCellStyle(this.headerStyles.get("grey")); CellRangeAddress second = new CellRangeAddress(0, 0, 5, sampleSet.size() + 4); sheet.addMergedRegion(first); sheet.addMergedRegion(second); //int col = 5 + sampleSet.size(); Iterator<String> compItr = comparisonSet.descendingIterator(); for (int i = 5 + sampleSet.size(); compItr.hasNext(); i += 6) { Cell temp = FHeaderRow.createCell(i); String s = compItr.next(); temp.setCellValue(s); temp.setCellStyle(this.headerStyles.get(s)); sheet.addMergedRegion(new CellRangeAddress(0, 0, i, i + 5)); //col += 6; } //CellRangeAddress third = new CellRangeAddress(0,0, 5 + sampleSet.size(), col - 6); // Non-merged second row Row SHeaderRow = sheet.createRow(1); for (int i = 0; i < this.coordheaders.length; i++) { Cell temp = SHeaderRow.createCell(i); temp.setCellValue(coordheaders[i]); temp.setCellStyle(headerStyles.get("grey")); } Iterator<String> samps = sampleSet.descendingIterator(); for (int i = coordheaders.length; i < sampleSet.size() + coordheaders.length; i++) { Cell temp = SHeaderRow.createCell(i); temp.setCellValue(samps.next()); temp.setCellStyle(headerStyles.get("grey")); } int op = 0; for (int i = coordheaders.length + sampleSet.size(); i < coordheaders.length + sampleSet.size() + (comparisonSet.size() * 6); i++) { Cell temp = SHeaderRow.createCell(i); temp.setCellValue(this.diffheaders[op]); temp.setCellStyle(headerStyles.get("grey")); op++; if (op >= 6) op = 0; } System.err.println("[DIFF OUT] Created Header Row for output"); }
From source file:packtest.MergingCells.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); Sheet sheet = wb.createSheet("new sheet"); Row row = sheet.createRow((short) 1); Cell cell = row.createCell((short) 1); cell.setCellValue(new XSSFRichTextString("This is a test of merging")); sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 2)); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("merging_cells.xlsx"); wb.write(fileOut);//from ww w. j av a 2 s. co m fileOut.close(); }
From source file:pl.softech.knf.ofe.opf.members.xls.export.XlsMembersWritter.java
License:Apache License
private void buildHeader(final List<Date> dates, final Sheet sheet, final int rowIdx, final int colIdx) { final Workbook wb = sheet.getWorkbook(); final CreationHelper createHelper = wb.getCreationHelper(); final CellStyle dateCellStyle = wb.createCellStyle(); dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("mm-yyyy")); Row row = sheet.createRow(rowIdx);/* w w w . jav a 2s.c o m*/ Cell cell = row.createCell(colIdx); cell.setCellValue("Open Pension Fund"); final CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyle.setFont(createHeaderFont(wb, (short) 12)); cell.setCellStyle(cellStyle); cell = row.createCell(colIdx + 1); cell.setCellValue("Number of members"); cell.setCellStyle(cellStyle); row = sheet.createRow(rowIdx + 1); sheet.addMergedRegion(new CellRangeAddress(// merge Open Pension Fund rowIdx, // first row (0-based) rowIdx + 1, // last row (0-based) colIdx, // first column (0-based) colIdx // last column (0-based) )); sheet.addMergedRegion(new CellRangeAddress(// merge Number of members rowIdx, // first row (0-based) rowIdx, // last row (0-based) colIdx + 1, // first column (0-based) colIdx + dates.size() // last column (0-based) )); int colIt = colIdx + 1; for (final Date date : dates) { cell = row.createCell(colIt++); cell.setCellValue(date); cell.setCellStyle(dateCellStyle); } }
From source file:poitest.SSPerformance.java
License:Apache License
public static void main(String[] args) { System.out.println("Apache POI Version: " + Version.getVersion()); args = new String[] { "XSSF", "50000", "50", "0" }; if (args.length != 4) usage("need four command arguments"); String type = args[0];//from w w w . ja va 2s . c om long timeStarted = System.currentTimeMillis(); Workbook workBook = createWorkbook(type); boolean isHType = workBook instanceof HSSFWorkbook; int rows = parseInt(args[1], "Failed to parse rows value as integer"); int cols = parseInt(args[2], "Failed to parse cols value as integer"); boolean saveFile = parseInt(args[3], "Failed to parse saveFile value as integer") != 0; Map<String, CellStyle> styles = createStyles(workBook); Sheet sheet = workBook.createSheet("Main Sheet"); Cell headerCell = sheet.createRow(0).createCell(0); headerCell.setCellValue("Header text is spanned across multiple cells"); headerCell.setCellStyle(styles.get("header")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$F$1")); int sheetNo = 0; int rowIndexInSheet = 1; double value = 0; Calendar calendar = Calendar.getInstance(); for (int rowIndex = 0; rowIndex < rows; rowIndex++) { if (isHType && sheetNo != rowIndex / 0x10000) { sheet = workBook.createSheet("Spillover from sheet " + (++sheetNo)); headerCell.setCellValue("Header text is spanned across multiple cells"); headerCell.setCellStyle(styles.get("header")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$F$1")); rowIndexInSheet = 1; } Row row = sheet.createRow(rowIndexInSheet); for (int colIndex = 0; colIndex < cols; colIndex++) { Cell cell = row.createCell(colIndex); String address = new CellReference(cell).formatAsString(); switch (colIndex) { case 0: // column A: default number format cell.setCellValue(value++); break; case 1: // column B: #,##0 cell.setCellValue(value++); cell.setCellStyle(styles.get("#,##0.00")); break; case 2: // column C: $#,##0.00 cell.setCellValue(value++); cell.setCellStyle(styles.get("$#,##0.00")); break; case 3: // column D: red bold text on yellow background cell.setCellValue(address); cell.setCellStyle(styles.get("red-bold")); break; case 4: // column E: boolean // TODO booleans are shown as 1/0 instead of TRUE/FALSE cell.setCellValue(rowIndex % 2 == 0); break; case 5: // column F: date / time cell.setCellValue(calendar); cell.setCellStyle(styles.get("m/d/yyyy")); calendar.roll(Calendar.DAY_OF_YEAR, -1); break; case 6: // column F: formula // TODO formulas are not yet supported in SXSSF //cell.setCellFormula("SUM(A" + (rowIndex+1) + ":E" + (rowIndex+1)+ ")"); //break; default: cell.setCellValue(value++); break; } } rowIndexInSheet++; } if (saveFile) { String fileName = type + "_" + rows + "_" + cols + "." + getFileSuffix(args[0]); try { FileOutputStream out = new FileOutputStream(fileName); workBook.write(out); out.close(); } catch (IOException ioe) { System.err .println("Error: failed to write to file \"" + fileName + "\", reason=" + ioe.getMessage()); } } long timeFinished = System.currentTimeMillis(); System.out.println("Elapsed " + (timeFinished - timeStarted) / 1000 + " seconds"); }
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;// ww w. ja v a2 s . co 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()); } }