List of usage examples for org.apache.poi.ss.usermodel Sheet addMergedRegion
int addMergedRegion(CellRangeAddress region);
From source file:com.compassplus.gui.MainForm.java
private void removeRow(Sheet sheet, int rowIndex, Workbook wb, boolean debug) { ArrayList<CellRangeAddress> cras = new ArrayList<CellRangeAddress>(); for (int i = 0; i < sheet.getNumMergedRegions(); i++) { cras.add(sheet.getMergedRegion(i)); }/*from w w w .ja v a 2 s . c o m*/ while (sheet.getNumMergedRegions() > 0) { sheet.removeMergedRegion(0); } int lastRowNum = sheet.getLastRowNum(); if (debug) { //System.out.println("lastRowNum = " + lastRowNum); //System.out.println("rowIndex = " + rowIndex); } if (rowIndex >= 0 && rowIndex < lastRowNum) { Row removingRow = sheet.getRow(rowIndex); sheet.removeRow(removingRow); sheet.shiftRows(rowIndex + 1, lastRowNum, -1); } if (rowIndex == lastRowNum) { Row removingRow = sheet.getRow(rowIndex); if (removingRow != null) { sheet.removeRow(removingRow); } } for (CellRangeAddress cra : cras) { if (rowIndex >= cra.getFirstRow() && rowIndex <= cra.getLastRow() && cra.getFirstRow() != cra.getLastRow()) { cra.setLastRow(cra.getLastRow() - 1); sheet.addMergedRegion(cra); } else if (rowIndex < cra.getFirstRow()) { cra.setFirstRow(cra.getFirstRow() - 1); cra.setLastRow(cra.getLastRow() - 1); sheet.addMergedRegion(cra); } else if (rowIndex > cra.getLastRow()) { sheet.addMergedRegion(cra); } } }
From source file:com.crm.webapp.util.ExcelCustomExporter.java
License:Apache License
protected void tableFacet(FacesContext context, Sheet sheet, DataTable table, int columnCount, String facetType) {/*from w w w .java 2 s. c o m*/ Map<String, UIComponent> map = table.getFacets(); UIComponent component = map.get(facetType); if (component != null) { String headerValue = null; if (component instanceof HtmlCommandButton) { headerValue = exportValue(context, component); } else if (component instanceof HtmlCommandLink) { headerValue = exportValue(context, component); } else if (component instanceof UIPanel) { String header = ""; for (UIComponent child : component.getChildren()) { headerValue = exportValue(context, child); header = header + headerValue; } headerValue = header; } else { headerValue = exportFacetValue(context, component); } int sheetRowIndex = sheet.getLastRowNum() + 1; Row row = sheet.createRow(sheetRowIndex); Cell cell = row.createCell((short) 0); cell.setCellValue(headerValue); cell.setCellStyle(facetStyle); sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based) sheetRowIndex, //last row (0-based) 0, //first column (0-based) columnCount + 1 //last column (0-based) )); } }
From source file:com.crm.webapp.util.ExcelCustomExporter.java
License:Apache License
protected void tableFacet(FacesContext context, Sheet sheet, SubTable table, int columnCount, String facetType) {/* w w w . ja v a 2s . c om*/ Map<String, UIComponent> map = table.getFacets(); UIComponent component = map.get(facetType); if (component != null) { String headerValue = null; if (component instanceof HtmlCommandButton) { headerValue = exportValue(context, component); } else if (component instanceof HtmlCommandLink) { headerValue = exportValue(context, component); } else if (component instanceof UIPanel) { String header = ""; for (UIComponent child : component.getChildren()) { headerValue = exportValue(context, child); header = header + headerValue; } headerValue = header; } else { headerValue = exportFacetValue(context, component); } int sheetRowIndex = sheet.getLastRowNum() + 1; Row row = sheet.createRow(sheetRowIndex); Cell cell = row.createCell((short) 0); cell.setCellValue(headerValue); cell.setCellStyle(facetStyle); sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based) sheetRowIndex, //last row (0-based) 0, //first column (0-based) columnCount //last column (0-based) )); } }
From source file:com.crm.webapp.util.ExcelCustomExporter.java
License:Apache License
protected void tableFacet(FacesContext context, Sheet sheet, DataList list, String facetType) { Map<String, UIComponent> map = list.getFacets(); UIComponent component = map.get(facetType); if (component != null) { String headerValue = null; if (component instanceof HtmlCommandButton) { headerValue = exportValue(context, component); } else if (component instanceof HtmlCommandLink) { headerValue = exportValue(context, component); } else {/* w w w . j a va 2s . co m*/ headerValue = exportFacetValue(context, component); } int sheetRowIndex = sheet.getLastRowNum() + 1; Row row = sheet.createRow(sheetRowIndex); Cell cell = row.createCell((short) 0); cell.setCellValue(headerValue); cell.setCellStyle(facetStyle); sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based) sheetRowIndex, //last row (0-based) 0, //first column (0-based) 1 //last column (0-based) )); } }
From source file:com.crm.webapp.util.ExcelCustomExporter.java
License:Apache License
protected void tableColumnGroup(Sheet sheet, DataTable table, String facetType) { ColumnGroup cg = table.getColumnGroup(facetType); List<UIComponent> headerComponentList = null; if (cg != null) { headerComponentList = cg.getChildren(); }//from w ww . j a v a 2 s. co m if (headerComponentList != null) { for (UIComponent component : headerComponentList) { if (component instanceof org.primefaces.component.row.Row) { org.primefaces.component.row.Row row = (org.primefaces.component.row.Row) component; int sheetRowIndex = sheet.getLastRowNum() + 1; Row xlRow = sheet.createRow(sheetRowIndex); int i = 0; for (UIComponent rowComponent : row.getChildren()) { UIColumn column = (UIColumn) rowComponent; String value = null; if (facetType.equalsIgnoreCase("header")) { value = column.getHeaderText(); } else { value = column.getFooterText(); } int rowSpan = column.getRowspan(); int colSpan = column.getColspan(); Cell cell = xlRow.getCell(i); if (rowSpan > 1 || colSpan > 1) { if (rowSpan > 1) { cell = xlRow.createCell((short) i); Boolean rowSpanFlag = false; for (int j = 0; j < sheet.getNumMergedRegions(); j++) { CellRangeAddress merged = sheet.getMergedRegion(j); if (merged.isInRange(sheetRowIndex, i)) { rowSpanFlag = true; } } if (!rowSpanFlag) { cell.setCellValue(value); cell.setCellStyle(facetStyle); sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based) sheetRowIndex + (rowSpan - 1), //last row (0-based) i, //first column (0-based) i //last column (0-based) )); } } if (colSpan > 1) { cell = xlRow.createCell((short) i); for (int j = 0; j < sheet.getNumMergedRegions(); j++) { CellRangeAddress merged = sheet.getMergedRegion(j); if (merged.isInRange(sheetRowIndex, i)) { cell = xlRow.createCell((short) ++i); } } cell.setCellValue(value); cell.setCellStyle(facetStyle); sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based) sheetRowIndex, //last row (0-based) i, //first column (0-based) i + (colSpan - 1) //last column (0-based) )); i = i + colSpan - 1; } } else { cell = xlRow.createCell((short) i); for (int j = 0; j < sheet.getNumMergedRegions(); j++) { CellRangeAddress merged = sheet.getMergedRegion(j); if (merged.isInRange(sheetRowIndex, i)) { cell = xlRow.createCell((short) ++i); } } cell.setCellValue(value); cell.setCellStyle(facetStyle); } i++; } } } } }
From source file:com.crm.webapp.util.ExcelCustomExporter.java
License:Apache License
protected void tableColumnGroup(Sheet sheet, SubTable table, String facetType) { ColumnGroup cg = table.getColumnGroup(facetType); List<UIComponent> headerComponentList = null; if (cg != null) { headerComponentList = cg.getChildren(); }// w w w . j a v a2 s . com if (headerComponentList != null) { for (UIComponent component : headerComponentList) { if (component instanceof org.primefaces.component.row.Row) { org.primefaces.component.row.Row row = (org.primefaces.component.row.Row) component; int sheetRowIndex = sheet.getLastRowNum() + 1; Row xlRow = sheet.createRow(sheetRowIndex); int i = 0; for (UIComponent rowComponent : row.getChildren()) { UIColumn column = (UIColumn) rowComponent; String value = null; if (facetType.equalsIgnoreCase("header")) { value = column.getHeaderText(); } else { value = column.getFooterText(); } int rowSpan = column.getRowspan(); int colSpan = column.getColspan(); Cell cell = xlRow.getCell(i); if (rowSpan > 1 || colSpan > 1) { if (rowSpan > 1) { cell = xlRow.createCell((short) i); Boolean rowSpanFlag = false; for (int j = 0; j < sheet.getNumMergedRegions(); j++) { CellRangeAddress merged = sheet.getMergedRegion(j); if (merged.isInRange(sheetRowIndex, i)) { rowSpanFlag = true; } } if (!rowSpanFlag) { cell.setCellStyle(cellStyle); cell.setCellValue(value); sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based) sheetRowIndex + rowSpan - 1, //last row (0-based) i, //first column (0-based) i //last column (0-based) )); } } if (colSpan > 1) { cell = xlRow.createCell((short) i); for (int j = 0; j < sheet.getNumMergedRegions(); j++) { CellRangeAddress merged = sheet.getMergedRegion(j); if (merged.isInRange(sheetRowIndex, i)) { cell = xlRow.createCell((short) ++i); } } cell.setCellStyle(cellStyle); cell.setCellValue(value); sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based) sheetRowIndex, //last row (0-based) i, //first column (0-based) i + colSpan - 1 //last column (0-based) )); i = i + colSpan - 1; } } else { cell = xlRow.createCell((short) i); for (int j = 0; j < sheet.getNumMergedRegions(); j++) { CellRangeAddress merged = sheet.getMergedRegion(j); if (merged.isInRange(sheetRowIndex, i)) { cell = xlRow.createCell((short) ++i); } } cell.setCellValue(value); cell.setCellStyle(facetStyle); } i++; } } } } }
From source file:com.dituiba.excel.BaseExcelService.java
License:Apache License
/** * sheet ?//from w w w . j a v a2 s . c om * @param sheet * @param row * @param length * @param data */ public static void addTitle(Sheet sheet, int row, int length, String data) { if (data == null || data.equals("") || data.equals("null")) { return; } Row sheetRow = sheet.createRow(row); for (int i = 0; i < length; i++) { sheetRow.createCell(i); } CellStyle style = sheet.getWorkbook().createCellStyle(); // ? style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// CellRangeAddress cellRangeAddress = new CellRangeAddress(row, row, 0, length - 1); sheet.addMergedRegion(cellRangeAddress); Cell cell = sheetRow.getCell(0); cell.setCellStyle(style); cell.setCellValue(data); }
From source file:com.dituiba.excel.ExcelUtility.java
License:Apache License
public static void copyRows(Sheet st, int startRow, int endRow, int pPosition) { int pStartRow = startRow; int pEndRow = endRow; int targetRowFrom; int targetRowTo; int columnCount; CellRangeAddress region = null;/*from w w w . ja v a2s. co m*/ int i; int j; for (i = 0; i < st.getNumMergedRegions(); i++) { region = st.getMergedRegion(i); if ((region.getFirstRow() >= pStartRow) && (region.getLastRow() <= pEndRow)) { targetRowFrom = region.getFirstRow() - pStartRow + pPosition; targetRowTo = region.getLastRow() - pStartRow + pPosition; CellRangeAddress newRegion = region.copy(); newRegion.setFirstRow(targetRowFrom); newRegion.setFirstColumn(region.getFirstColumn()); newRegion.setLastRow(targetRowTo); newRegion.setLastColumn(region.getLastColumn()); st.addMergedRegion(newRegion); } } //set the column height and value for (i = pStartRow; i <= pEndRow; i++) { Row sourceRow = st.getRow(i); columnCount = sourceRow.getLastCellNum(); if (sourceRow != null) { Row newRow = st.createRow(pPosition + i); newRow.setHeight(sourceRow.getHeight()); for (j = 0; j < columnCount; j++) { Cell templateCell = sourceRow.getCell(j); if (templateCell != null) { Cell newCell = newRow.createCell(j); copyCell(templateCell, newCell); } } } } }
From source file:com.efficio.fieldbook.service.ExportExcelServiceImpl.java
License:Open Source License
public FileOutputStream exportFieldMapToExcel(String fileName, UserFieldmap userFieldMap) throws FieldbookException { Locale locale = LocaleContextHolder.getLocale(); boolean isTrial = userFieldMap.isTrial(); // Summary of Trial/Nursery, Field and Planting Details String summaryOfFieldbookFieldPlantingDetailsLabel = messageSource .getMessage("fieldmap.header.summary.for.trial", null, locale); //SUMMARY OF TRIAL, FIELD AND PLANTING DETAILS String selectedFieldbookLabel = messageSource.getMessage("fieldmap.trial.selected.trial", null, locale); //Selected Trial: if (!isTrial) { summaryOfFieldbookFieldPlantingDetailsLabel = messageSource .getMessage("fieldmap.header.summary.for.nursery", null, locale); //SUMMARY OF NURSERY, FIELD AND PLANTING DETAILS selectedFieldbookLabel = messageSource.getMessage("fieldmap.nursery.selected.nursery", null, locale); //Selected Nursery: }//from w w w. jav a 2 s .c o m String selectedFieldbookValue = userFieldMap.getBlockName(); String orderHeader = messageSource.getMessage("fieldmap.trial.order", null, locale); String studyHeader = messageSource.getMessage((isTrial ? "fieldmap.trial" : "fieldmap.nursery"), null, locale); String instanceHeader = messageSource.getMessage("fieldmap.trial.instance", null, locale); String entriesCountHeader = messageSource.getMessage("fieldmap.trial.entry.count", null, locale); String repsCountHeader = messageSource.getMessage("fieldmap.trial.reps.count", null, locale); String plotsNeededHeader = messageSource.getMessage("fieldmap.trial.plots.needed", null, locale); String totalPlotsHeader = messageSource.getMessage("fieldmap.trial.total.number.of.plots", null, locale); String datasetNameHeader = messageSource.getMessage("fieldmap.nursery.dataset", null, locale); // Field And Block Details String fieldAndBlockDetailsLabel = messageSource.getMessage("fieldmap.trial.field.and.block.details", null, locale); //FIELD AND BLOCK DETAILS String fieldLocationLabel = messageSource.getMessage("fieldmap.label.field.location", null, locale); //Field Location String fieldLocationValue = userFieldMap.getLocationName(); String fieldNameLabel = messageSource.getMessage("fieldmap.label.field.name", null, locale); //Field Name String fieldNameValue = userFieldMap.getFieldName(); String blockNameLabel = messageSource.getMessage("fieldmap.label.block.name", null, locale); //Block Name String blockNameValue = userFieldMap.getBlockName(); // Row, Range & Plot Details String rowRangePlotDetailsLabel = messageSource.getMessage("fieldmap.trial.row.and.range.and.plot.details", null, locale); //ROW, RANGE AND PLOT DETAILS String blockCapacityLabel = messageSource.getMessage("fieldmap.label.block.capacity", null, locale); //Block Capacity String blockCapacityValue = userFieldMap.getBlockCapacityString(messageSource); //e.g. "10 Columns, 10 Ranges" String rowsPerPlotLabel = messageSource.getMessage("fieldmap.label.rows.per.plot", null, locale); //Rows per Plot int rowsPerPlotValue = userFieldMap.getNumberOfRowsPerPlot(); String columnsLabel = messageSource.getMessage("fieldmap.label.columns", null, locale); //Columns Integer columnsValue = userFieldMap.getNumberOfColumnsInBlock(); // 10 String machineCapacityLabel = messageSource.getMessage("fieldmap.label.row.capacity.machine", null, locale); //machine row capacity Integer machineCapacityValue = userFieldMap.getMachineRowCapacity(); //Planting Details String plantingDetailsLabel = messageSource.getMessage("fieldmap.header.planting.details", null, locale); //PLANTING DETAILS String startingCoordinatesLabel = messageSource.getMessage("fieldmap.label.starting.coordinates", null, locale); //Starting Coordinates String startingCoordinatesValue = userFieldMap.getStartingCoordinateString(messageSource); // Column 1, Range 1 String plantingOrderLabel = messageSource.getMessage("fieldmap.label.planting.order", null, locale); //Planting Order String plantingOrderValue = userFieldMap.getPlantingOrderString(messageSource); //"Row/Column" or "Serpentine" // FieldMap String fieldMapLabel = messageSource.getMessage("fieldmap.header.fieldmap", null, locale); //FIELD MAP String rowsLabel = messageSource.getMessage("fieldmap.label.rows", null, locale); //Rows String columnLabel = messageSource.getMessage("fieldmap.label.capitalized.column", null, locale); //Column String rangeLabel = messageSource.getMessage("fieldmap.label.capitalized.range", null, locale); //Range try { //Create workbook HSSFWorkbook workbook = new HSSFWorkbook(); String summaryLabelSheet = messageSource.getMessage("fieldmap.header.excel.summary", null, locale); Sheet summarySheet = workbook.createSheet(summaryLabelSheet); Sheet fieldMapSheet = workbook.createSheet(fieldMapLabel); CellStyle labelStyle = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); labelStyle.setFont(font); CellStyle wrapStyle = workbook.createCellStyle(); wrapStyle.setWrapText(true); wrapStyle.setAlignment(CellStyle.ALIGN_CENTER); CellStyle mainHeaderStyle = workbook.createCellStyle(); HSSFPalette palette = workbook.getCustomPalette(); // get the color which most closely matches the color you want to use HSSFColor myColor = palette.findSimilarColor(179, 165, 165); // get the palette index of that color short palIndex = myColor.getIndex(); // code to get the style for the cell goes here mainHeaderStyle.setFillForegroundColor(palIndex); mainHeaderStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); CellStyle mainSubHeaderStyle = workbook.createCellStyle(); HSSFPalette paletteSubHeader = workbook.getCustomPalette(); // get the color which most closely matches the color you want to use HSSFColor myColorSubHeader = paletteSubHeader.findSimilarColor(190, 190, 186); // get the palette index of that color short palIndexSubHeader = myColorSubHeader.getIndex(); // code to get the style for the cell goes here mainSubHeaderStyle.setFillForegroundColor(palIndexSubHeader); mainSubHeaderStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); mainSubHeaderStyle.setAlignment(CellStyle.ALIGN_CENTER); int rowIndex = 0; int columnIndex = 0; // Create Header Information // Row 1: SUMMARY OF TRIAL, FIELD AND PLANTING DETAILS CellStyle headerLabelStyle = workbook.createCellStyle(); font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerLabelStyle.setFont(font); headerLabelStyle.setAlignment(CellStyle.ALIGN_CENTER); Row row = summarySheet.createRow(rowIndex++); Cell summaryCell = row.createCell(columnIndex); summaryCell.setCellValue(summaryOfFieldbookFieldPlantingDetailsLabel); summaryCell.setCellStyle(headerLabelStyle); summarySheet.addMergedRegion(new CellRangeAddress(rowIndex - 1, //first row (0-based) rowIndex - 1, //last row (0-based) columnIndex, //first column (0-based) columnIndex + 5 //last column (0-based) )); // Row 2: Space row = summarySheet.createRow(rowIndex++); // Row 3: Fieldbook Name, Entries, Reps, Plots row = summarySheet.createRow(rowIndex++); // Selected Trial : [Fieldbook Name] TABLE SECTION Cell labelCell = row.createCell(columnIndex++); labelCell.setCellValue(selectedFieldbookLabel); row = summarySheet.createRow(rowIndex++); columnIndex = 0; Cell headerCell = row.createCell(columnIndex++); headerCell.setCellValue(orderHeader); headerCell.setCellStyle(labelStyle); headerCell = row.createCell(columnIndex++); headerCell.setCellValue(studyHeader); headerCell.setCellStyle(labelStyle); if (isTrial) { headerCell = row.createCell(columnIndex++); headerCell.setCellValue(instanceHeader); headerCell.setCellStyle(labelStyle); headerCell = row.createCell(columnIndex++); headerCell.setCellValue(entriesCountHeader); headerCell.setCellStyle(labelStyle); headerCell = row.createCell(columnIndex++); headerCell.setCellValue(repsCountHeader); headerCell.setCellStyle(labelStyle); } else { headerCell = row.createCell(columnIndex++); headerCell.setCellValue(datasetNameHeader); headerCell.setCellStyle(labelStyle); } headerCell = row.createCell(columnIndex++); headerCell.setCellValue(plotsNeededHeader); headerCell.setCellStyle(labelStyle); for (SelectedFieldmapRow rec : userFieldMap.getSelectedFieldmapList().getRows()) { row = summarySheet.createRow(rowIndex++); columnIndex = 0; row.createCell(columnIndex++).setCellValue(rec.getOrder()); row.createCell(columnIndex++).setCellValue(rec.getStudyName()); if (isTrial) { row.createCell(columnIndex++).setCellValue(rec.getTrialInstanceNo()); row.createCell(columnIndex++).setCellValue(String.valueOf(rec.getEntryCount())); row.createCell(columnIndex++).setCellValue(String.valueOf(rec.getRepCount())); row.createCell(columnIndex++).setCellValue(String.valueOf(rec.getPlotCount())); } else { row.createCell(columnIndex++).setCellValue(rec.getDatasetName()); row.createCell(columnIndex++).setCellValue(String.valueOf(rec.getEntryCount())); } } row = summarySheet.createRow(rowIndex++); columnIndex = 0; headerCell = row.createCell(columnIndex++); headerCell.setCellValue(totalPlotsHeader); headerCell.setCellStyle(labelStyle); row.createCell(columnIndex++) .setCellValue(String.valueOf(userFieldMap.getSelectedFieldmapList().getTotalNumberOfPlots())); // Row 4: Space row = summarySheet.createRow(rowIndex++); // Row 5: Header - Details Heading row = summarySheet.createRow(rowIndex++); columnIndex = 0; labelCell = row.createCell(columnIndex++); labelCell.setCellValue(fieldAndBlockDetailsLabel); labelCell.setCellStyle(headerLabelStyle); summarySheet.addMergedRegion(new CellRangeAddress(rowIndex - 1, //first row (0-based) rowIndex - 1, //last row (0-based) columnIndex - 1, //first column (0-based) columnIndex //last column (0-based) )); row.createCell(columnIndex++); labelCell = row.createCell(columnIndex++); labelCell.setCellValue(rowRangePlotDetailsLabel); labelCell.setCellStyle(headerLabelStyle); summarySheet.addMergedRegion(new CellRangeAddress(rowIndex - 1, //first row (0-based) rowIndex - 1, //last row (0-based) columnIndex - 1, //first column (0-based) columnIndex //last column (0-based) )); row.createCell(columnIndex++); labelCell = row.createCell(columnIndex++); labelCell.setCellValue(plantingDetailsLabel); labelCell.setCellStyle(headerLabelStyle); summarySheet.addMergedRegion(new CellRangeAddress(rowIndex - 1, //first row (0-based) rowIndex - 1, //last row (0-based) columnIndex - 1, //first column (0-based) columnIndex //last column (0-based) )); row.createCell(columnIndex++); //Row 6: Field Location, Block Capacity, Starting Coordinates row = summarySheet.createRow(rowIndex++); columnIndex = 0; labelCell = row.createCell(columnIndex++); labelCell.setCellValue(fieldLocationLabel); labelCell.setCellStyle(labelStyle); row.createCell(columnIndex++).setCellValue(fieldLocationValue); labelCell = row.createCell(columnIndex++); labelCell.setCellValue(blockCapacityLabel); labelCell.setCellStyle(labelStyle); row.createCell(columnIndex++).setCellValue(blockCapacityValue); labelCell = row.createCell(columnIndex++); labelCell.setCellValue(startingCoordinatesLabel); labelCell.setCellStyle(labelStyle); row.createCell(columnIndex++).setCellValue(startingCoordinatesValue); // Row 7: Field Name, Rows Per Plot, Planting Order row = summarySheet.createRow(rowIndex++); columnIndex = 0; labelCell = row.createCell(columnIndex++); labelCell.setCellValue(fieldNameLabel); labelCell.setCellStyle(labelStyle); row.createCell(columnIndex++).setCellValue(fieldNameValue); labelCell = row.createCell(columnIndex++); labelCell.setCellValue(rowsPerPlotLabel); labelCell.setCellStyle(labelStyle); row.createCell(columnIndex++).setCellValue(String.valueOf(rowsPerPlotValue)); labelCell = row.createCell(columnIndex++); labelCell.setCellValue(plantingOrderLabel); labelCell.setCellStyle(labelStyle); row.createCell(columnIndex++).setCellValue(plantingOrderValue); // Row 8: Block Name, Columns row = summarySheet.createRow(rowIndex++); columnIndex = 0; labelCell = row.createCell(columnIndex++); labelCell.setCellValue(blockNameLabel); labelCell.setCellStyle(labelStyle); row.createCell(columnIndex++).setCellValue(blockNameValue); labelCell = row.createCell(columnIndex++); labelCell.setCellValue(columnsLabel); labelCell.setCellStyle(labelStyle); row.createCell(columnIndex++).setCellValue(String.valueOf(columnsValue)); labelCell = row.createCell(columnIndex++); labelCell.setCellValue(machineCapacityLabel); labelCell.setCellStyle(labelStyle); row.createCell(columnIndex++).setCellValue(String.valueOf(machineCapacityValue)); // Row 9: Space row = summarySheet.createRow(rowIndex++); for (int columnsResize = 0; columnsResize < columnIndex; columnsResize++) { summarySheet.autoSizeColumn(columnsResize); } // Get FieldMap data //we reset the row index rowIndex = 0; // Row 10: FIELD MAP row = fieldMapSheet.createRow(rowIndex++); columnIndex = 0; labelCell = row.createCell(columnIndex++); labelCell.setCellValue(fieldMapLabel); labelCell.setCellStyle(labelStyle); // Row 11: Space row = fieldMapSheet.createRow(rowIndex++); Plot[][] plots = userFieldMap.getFieldmap(); int range = userFieldMap.getNumberOfRangesInBlock(); int col = userFieldMap.getNumberOfColumnsInBlock(); int rowsPerPlot = userFieldMap.getNumberOfRowsPerPlot(); int machineRowCapacity = userFieldMap.getMachineRowCapacity(); int rows = userFieldMap.getNumberOfRowsInBlock(); boolean isSerpentine = userFieldMap.getPlantingOrder() == 2; for (int j = range - 1; j >= 0; j--) { if (j == range - 1) { // TOP TABLE LABELS // Row 12: Rows Header rowIndex = printRowHeader(fieldMapSheet, userFieldMap.getNumberOfRowsInBlock(), rowIndex, rowsLabel, mainHeaderStyle, mainSubHeaderStyle); // Row 13: UP, DOWN Direction rowIndex = printDirectionHeader(fieldMapSheet, plots, j, rows, rowIndex, machineRowCapacity, mainHeaderStyle, mainSubHeaderStyle, isSerpentine); // Row 14: Column labels rowIndex = printColumnHeader(fieldMapSheet, col, rowIndex, columnLabel, rowsPerPlot, mainHeaderStyle, mainSubHeaderStyle); } // Rows 15 onwards: Ranges and Row Data row = fieldMapSheet.createRow(rowIndex); row.setHeightInPoints(45); columnIndex = 0; int rangeValue = j + 1; Cell cellRange = row.createCell(columnIndex++); cellRange.setCellValue(rangeLabel + " " + rangeValue); cellRange.setCellStyle(mainSubHeaderStyle); for (int i = 0; i < col; i++) { String displayString = plots[i][j].getDisplayString().replace("<br/>", "\n"); if (plots[i][j].isPlotDeleted()) { displayString = " X "; } Cell dataCell = row.createCell(columnIndex++); //dataCell.setCellValue(displayString); dataCell.setCellValue(new HSSFRichTextString(displayString)); dataCell.setCellStyle(wrapStyle); //row.createCell(columnIndex).setCellValue(""); for (int k = 0; k < rowsPerPlot - 1; k++) { row.createCell(columnIndex++).setCellValue(""); } fieldMapSheet.addMergedRegion(new CellRangeAddress(rowIndex, //first row (0-based) rowIndex, //last row (0-based) columnIndex - rowsPerPlot, //first column (0-based) columnIndex - 1 //last column (0-based) )); //columnIndex++; } rowIndex++; if (j == 0) { // BOTTOM TABLE LABELS rowIndex = printColumnHeader(fieldMapSheet, col, rowIndex, columnLabel, rowsPerPlot, mainHeaderStyle, mainSubHeaderStyle); rowIndex = printDirectionHeader(fieldMapSheet, plots, j, rows, rowIndex, machineRowCapacity, mainHeaderStyle, mainSubHeaderStyle, isSerpentine); rowIndex = printRowHeader(fieldMapSheet, userFieldMap.getNumberOfRowsInBlock(), rowIndex, rowsLabel, mainHeaderStyle, mainSubHeaderStyle); } } //Write the excel file FileOutputStream fileOutputStream = new FileOutputStream(fileName); workbook.write(fileOutputStream); fileOutputStream.close(); return fileOutputStream; } catch (FileNotFoundException e) { LOG.error(e.getMessage(), e); throw new FieldbookException("Error writing to file: " + fileName, e); } catch (IOException e) { LOG.error(e.getMessage(), e); throw new FieldbookException("Error writing to file: " + fileName, e); } }
From source file:com.efficio.fieldbook.service.ExportExcelServiceImpl.java
License:Open Source License
private int printColumnHeader(Sheet fieldMapSheet, int numberOfColumns, int rowIndex, String columnLabel, int rowsPerPlot, CellStyle mainHeader, CellStyle subHeaderStyle) { Row row = fieldMapSheet.createRow(rowIndex); int columnIndex = 0; Cell mainCell = row.createCell(columnIndex++); mainCell.setCellValue(""); mainCell.setCellStyle(mainHeader);//from w ww .jav a 2s . c o m for (int i = 0; i < numberOfColumns; i++) { int columnValue = i + 1; Cell cell = row.createCell(columnIndex++); cell.setCellValue(columnLabel + " " + columnValue); cell.setCellStyle(subHeaderStyle); for (int j = 0; j < rowsPerPlot - 1; j++) { Cell cell1 = row.createCell(columnIndex++); cell1.setCellValue(""); cell.setCellStyle(subHeaderStyle); } fieldMapSheet.addMergedRegion( new CellRangeAddress(rowIndex, rowIndex, columnIndex - rowsPerPlot, columnIndex - 1)); //columnIndex++; } rowIndex++; return rowIndex; }