List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getCustomPalette
public HSSFPalette getCustomPalette()
From source file:at.spardat.xma.mdl.grid.GridPOIAdapter.java
License:Open Source License
/** * Transfers the spreadsheet data from the POI <code>HSSFWorkbook</code> into the <code>IGridWMServer</code>. * Only the sheet on the given sheetIndex is copied. //from w w w . j a va 2 s . c o m * @param igrid the XMA model where to copy the data * @param book the POI represntation of the data * @param sheetIndex the index of the sheet to copy * @return a list containing all SysExceptions describing problems with individual cell formulas or ranges */ public static List poi2xma(IGridWM igrid, HSSFWorkbook book, int sheetIndex) { GridWM grid = (GridWM) igrid; try { List errorList = new ArrayList(); grid.setSheetName(book.getSheetName(sheetIndex)); grid.colors.clear(); grid.initBuildInColors(); short ic = GridWM.HSSF_FIRST_COLOR_INDEX; HSSFPalette palette = book.getCustomPalette(); for (HSSFColor color = palette.getColor(ic); ic < 64 && color != null; color = palette.getColor(++ic)) { grid.colors.add(ic, new GridColor(color.getTriplet())); } grid.fonts.clear(); int numFonts = book.getNumberOfFonts(); if (numFonts > 4) { // adjust for "There is no 4" see code of org.apache.poi.hssf.model.Workbook.getFontRecordAt() numFonts += 1; } for (short i = 0; i < numFonts; i++) { HSSFFont font = book.getFontAt(i); byte fontstyle = GridFont.FONT_NORML; if (font.getBoldweight() >= HSSFFont.BOLDWEIGHT_BOLD) fontstyle |= GridFont.FONT_BOLD; if (font.getItalic()) fontstyle |= GridFont.FONT_ITALIC; grid.fonts.add(i, new GridFont(font.getFontName(), fontstyle, font.getColor())); } grid.styles.clear(); for (short i = 0, numStyles = book.getNumCellStyles(); i < numStyles; i++) { HSSFCellStyle style = book.getCellStyleAt(i); grid.styles.add(i, new GridCellStyle(style.getFontIndex(), style.getFillForegroundColor())); } grid.namedRanges.clear(); for (int i = 0, numRanges = book.getNumberOfNames(); i < numRanges; i++) { HSSFName name = book.getNameAt(i); String rangeName = name.getNameName(); String rangeRef = null; try { // ranges not defined but referenced by formulas have a name but no reference in HSSF rangeRef = name.getReference(); } catch (Exception exc) { errorList.add(new SysException(exc, ((GridWM) grid).getMessage("inconsistentRange", rangeName)) .setCode(GridWM.CODE_inconsistentRange)); } if (rangeRef != null) { try { GridRange range = grid.getJeksDelegate().toRange(rangeRef); range.setKey(rangeName); grid.namedRanges.put(rangeName, range); } catch (Exception exc) { errorList.add(new SysException(exc, ((GridWM) grid).getMessage("unsupportedReference", rangeName, rangeRef)) .setCode(GridWM.CODE_unsupportedReference)); } } } grid.rows.clear(); grid.cols.clear(); grid.cells.clear(); grid.delegate = new GridJeksDelegate(grid); HSSFSheet sheet = book.getSheetAt(sheetIndex); int firstColNum = Integer.MAX_VALUE; int lastColNum = Integer.MIN_VALUE; for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { HSSFRow row = sheet.getRow(i); if (row == null) continue; if (row.getFirstCellNum() >= 0) firstColNum = Math.min(firstColNum, row.getFirstCellNum()); lastColNum = Math.max(lastColNum, row.getLastCellNum()); if (lastColNum > 255) lastColNum = 255; for (short j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) { HSSFCell hssfcell = row.getCell(j); if (hssfcell == null) continue; GridCell gridcell = grid.getOrCreateCell(i, j); switch (hssfcell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: break; case HSSFCell.CELL_TYPE_BOOLEAN: gridcell.setValue(hssfcell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: // TODO: recherche error text byte errorCode = hssfcell.getErrorCellValue(); // gridcell.setValue(errorCode); gridcell.setValue("#ERROR"); errorList.add(new SysException(((GridWM) grid).getMessage("errorRecord", grid.getJeksDelegate().toExcelRef(i, j), Byte.toString(errorCode))) .setCode(GridWM.CODE_errorRecord)); break; case HSSFCell.CELL_TYPE_FORMULA: String formula = null; try { formula = hssfcell.getCellFormula(); gridcell.setFormula(formula); } catch (SysException e) { if (formula != null) gridcell.setValue("=" + formula); //set it as text without interpretation errorList.add(e); } break; case HSSFCell.CELL_TYPE_NUMERIC: if (isDateCell(book, hssfcell)) { gridcell.setValue(hssfcell.getDateCellValue()); } else { gridcell.setValue(hssfcell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_STRING: gridcell.setValue(hssfcell.getStringCellValue()); break; default: throw new SysException("unknown cell type " + hssfcell.getCellType()); } gridcell.setEditable(!hssfcell.getCellStyle().getLocked()); gridcell.setStyle(hssfcell.getCellStyle().getIndex()); } } final int scalefactor = 256 / 7; // empirically testet // int width = sheet.getDefaultColumnWidth(); // returns nonsense // width = width/scalefactor; // grid.setDefaultColumnWidth(width); for (short i = (short) firstColNum; i <= lastColNum; i++) { int width = sheet.getColumnWidth(i); width = width / scalefactor; grid.getOrCreateColumn(i).setWidth(width); } if (firstColNum == Integer.MAX_VALUE) firstColNum = 0; if (lastColNum == Integer.MIN_VALUE) lastColNum = 0; grid.setMaxRange( new GridRange(grid, sheet.getFirstRowNum(), firstColNum, sheet.getLastRowNum(), lastColNum)); grid.setVisibleRange(grid.getMaxRange()); return errorList; } finally { grid.handle(grid.new GridReloadEvent()); } }
From source file:br.com.tecsinapse.dataio.util.WorkbookUtil.java
License:LGPL
private void replaceColorsPallete(Map<HSSFColor, HSSFColor> colorsReplaceMap, Workbook wb) { if (!(wb instanceof HSSFWorkbook)) { return;//from w ww . j ava 2 s . com } HSSFWorkbook hssfWb = (HSSFWorkbook) wb; final HSSFPalette customPalette = hssfWb.getCustomPalette(); for (Entry<HSSFColor, HSSFColor> e : colorsReplaceMap.entrySet()) { short[] rgb = e.getValue().getTriplet(); customPalette.setColorAtIndex(e.getKey().getIndex(), (byte) rgb[0], (byte) rgb[1], (byte) rgb[2]); } }
From source file:cc.landking.converter.office.excel.HSSFHtmlHelper.java
License:Apache License
public HSSFHtmlHelper(HSSFWorkbook wb) { this.wb = wb; // If there is no custom palette, then this creates a new one that is // a copy of the default colors = wb.getCustomPalette(); }
From source file:com.abacus.reports.ExcelBuilder.java
@Override protected void buildExcelDocument(Map<String, Object> map, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { // get data model which is passed by the Spring container List headerlist = (List) map.get("header"); List<Object[]> data = (List) map.get("data"); String reportname = String.valueOf(map.get("report_name")); // create a new Excel sheet HSSFSheet sheet = workbook.createSheet(reportname); response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-disposition", "attachment; filename=" + reportname + ".xls"); OutputStream outStream = response.getOutputStream(); sheet.setDefaultColumnWidth(30);// w ww . j a va 2 s . co m // create style for header cells CellStyle style = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); HSSFPalette palette = workbook.getCustomPalette(); HSSFColor color = palette.findSimilarColor(152, 35, 17); short paindex = color.getIndex(); font.setFontName("Trebuchet MS"); style.setFillForegroundColor(paindex); style.setFillPattern(CellStyle.SOLID_FOREGROUND); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.WHITE.index); style.setFont(font); // create header row HSSFRow header = sheet.createRow(0); int row = 0; for (Object headerlist1 : headerlist) { header.createCell(row).setCellValue(String.valueOf(headerlist1)); header.getCell(row).setCellStyle(style); row++; } CellStyle style2 = workbook.createCellStyle(); HSSFFont font2 = workbook.createFont(); font2.setFontName("Trebuchet MS"); style2.setFont(font2); System.out.println("data.size(): " + data.size()); int rownum = 1; // create data rows for (int rowCount = 0; rowCount < data.size(); rowCount++) { HSSFRow aRow = sheet.createRow(rownum); Object[] value = data.get(rowCount); int col = 0; for (Object value1 : value) { HSSFCell cell = aRow.createCell(col++); cell.setCellStyle(style2); if (value1 instanceof java.lang.String) cell.setCellValue(String.valueOf(value1)); if (value1 instanceof java.lang.Integer) cell.setCellValue(Integer.parseInt(String.valueOf(value1))); if (value1 instanceof java.lang.Boolean) cell.setCellValue(Integer.parseInt(String.valueOf(value1))); if (value1 instanceof java.lang.Double) cell.setCellValue(Double.parseDouble(String.valueOf(value1))); if (value1 instanceof java.lang.Float) cell.setCellValue(Float.parseFloat(String.valueOf(value1))); } rownum++; } workbook.write(outStream); outStream.close(); }
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.LabelPrintingServiceImpl.java
License:Open Source License
@Override public String generateXlSLabels(List<StudyTrialInstanceInfo> trialInstances, UserLabelPrinting userLabelPrinting, ByteArrayOutputStream baos) throws MiddlewareQueryException { int pageSizeId = Integer.parseInt(userLabelPrinting.getSizeOfLabelSheet()); int numberOfLabelPerRow = Integer.parseInt(userLabelPrinting.getNumberOfLabelPerRow()); int numberofRowsPerPageOfLabel = Integer.parseInt(userLabelPrinting.getNumberOfRowsPerPageOfLabel()); int totalPerPage = numberOfLabelPerRow * numberofRowsPerPageOfLabel; String leftSelectedFields = userLabelPrinting.getLeftSelectedLabelFields(); String rightSelectedFields = userLabelPrinting.getRightSelectedLabelFields(); String barcodeNeeded = userLabelPrinting.getBarcodeNeeded(); String firstBarcodeField = userLabelPrinting.getFirstBarcodeField(); String secondBarcodeField = userLabelPrinting.getSecondBarcodeField(); String thirdBarcodeField = userLabelPrinting.getThirdBarcodeField(); String currentDate = DateUtil.getCurrentDate(); //String fileName = currentDate + ".xls"; String fileName = userLabelPrinting.getFilenameDLLocation(); try {// w ww . jav a 2 s.com HSSFWorkbook workbook = new HSSFWorkbook(); String sheetName = cleanSheetName(userLabelPrinting.getName()); if (sheetName == null) sheetName = "Labels"; Sheet labelPrintingSheet = workbook.createSheet(sheetName); 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 Row row = labelPrintingSheet.createRow(rowIndex++); //we add all the selected fields header StringTokenizer token = new StringTokenizer(leftSelectedFields, ","); while (token.hasMoreTokens()) { String headerId = token.nextToken(); String headerName = getHeader(headerId); Cell summaryCell = row.createCell(columnIndex++); summaryCell.setCellValue(headerName); summaryCell.setCellStyle(labelStyle); } token = new StringTokenizer(rightSelectedFields, ","); while (token.hasMoreTokens()) { String headerId = token.nextToken(); String headerName = getHeader(headerId); Cell summaryCell = row.createCell(columnIndex++); summaryCell.setCellValue(headerName); summaryCell.setCellStyle(labelStyle); } //we populate the info now int i = 0; for (StudyTrialInstanceInfo trialInstance : trialInstances) { FieldMapTrialInstanceInfo fieldMapTrialInstanceInfo = trialInstance.getTrialInstance(); Map<String, String> moreFieldInfo = new HashMap<String, String>(); moreFieldInfo.put("locationName", fieldMapTrialInstanceInfo.getLocationName()); moreFieldInfo.put("blockName", fieldMapTrialInstanceInfo.getBlockName()); moreFieldInfo.put("selectedName", trialInstance.getFieldbookName()); moreFieldInfo.put("trialInstanceNumber", fieldMapTrialInstanceInfo.getTrialInstanceNo()); for (FieldMapLabel fieldMapLabel : fieldMapTrialInstanceInfo.getFieldMapLabels()) { row = labelPrintingSheet.createRow(rowIndex++); columnIndex = 0; i++; token = new StringTokenizer(leftSelectedFields, ","); while (token.hasMoreTokens()) { String headerId = token.nextToken(); String leftText = getSpecificInfo(moreFieldInfo, fieldMapLabel, headerId); Cell summaryCell = row.createCell(columnIndex++); summaryCell.setCellValue(leftText); //summaryCell.setCellStyle(labelStyle); } token = new StringTokenizer(rightSelectedFields, ","); while (token.hasMoreTokens()) { String headerId = token.nextToken(); String rightText = getSpecificInfo(moreFieldInfo, fieldMapLabel, headerId); Cell summaryCell = row.createCell(columnIndex++); summaryCell.setCellValue(rightText); //summaryCell.setCellStyle(labelStyle); } } } for (int columnPosition = 0; columnPosition < columnIndex; columnPosition++) { labelPrintingSheet.autoSizeColumn((short) (columnPosition)); } //Write the excel file FileOutputStream fileOutputStream = new FileOutputStream(fileName); //workbook.write(baos); workbook.write(fileOutputStream); fileOutputStream.close(); //return fileOutputStream; } catch (Exception e) { LOG.error(e.getMessage(), e); } return fileName; }
From source file:com.efficio.fieldbook.web.nursery.service.impl.ExcelExportStudyServiceImpl.java
License:Open Source License
private CellStyle getHeaderStyle(HSSFWorkbook xlsBook, int c1, int c2, int c3) { HSSFPalette palette = xlsBook.getCustomPalette(); HSSFColor color = palette.findSimilarColor(c1, c2, c3); short colorIndex = color.getIndex(); HSSFFont whiteFont = xlsBook.createFont(); whiteFont.setColor(new HSSFColor.WHITE().getIndex()); CellStyle cellStyle = xlsBook.createCellStyle(); cellStyle.setFillForegroundColor(colorIndex); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setFont(whiteFont);// w w w. j a v a2 s. c o m return cellStyle; }
From source file:com.eryansky.core.excelTools.ExcelUtils.java
License:Apache License
private static short findColor(short index, HSSFWorkbook srcwb, HSSFWorkbook destwb) { Integer id = new Integer(index); if (HSSFColor.getIndexHash().containsKey(id)) return index; if (index == HSSFColor.AUTOMATIC.index) return index; HSSFColor color = srcwb.getCustomPalette().getColor(index); if (color == null) { return index; }// w ww. j a v a 2 s.c om HSSFColor ncolor = destwb.getCustomPalette().findColor((byte) color.getTriplet()[0], (byte) color.getTriplet()[1], (byte) color.getTriplet()[2]); if (ncolor != null) return ncolor.getIndex(); destwb.getCustomPalette().setColorAtIndex(index, (byte) color.getTriplet()[0], (byte) color.getTriplet()[1], (byte) color.getTriplet()[2]); return index; }
From source file:com.eryansky.core.excelTools.ExcelUtils.java
License:Apache License
public static HSSFCellStyle findStyle(HSSFCellStyle style, HSSFWorkbook srcwb, HSSFWorkbook destwb) { HSSFPalette srcpalette = srcwb.getCustomPalette(); HSSFPalette destpalette = destwb.getCustomPalette(); for (short i = 0; i < destwb.getNumCellStyles(); i++) { HSSFCellStyle old = destwb.getCellStyleAt(i); if (old == null) continue; if (style.getAlignment() == old.getAlignment() && style.getBorderBottom() == old.getBorderBottom() && style.getBorderLeft() == old.getBorderLeft() && style.getBorderRight() == old.getBorderRight() && style.getBorderTop() == old.getBorderTop() && isSameColor(style.getBottomBorderColor(), old.getBottomBorderColor(), srcpalette, destpalette)// ww w . j ava2 s . co m && style.getDataFormat() == old.getDataFormat() && isSameColor(style.getFillBackgroundColor(), old.getFillBackgroundColor(), srcpalette, destpalette) && isSameColor(style.getFillForegroundColor(), old.getFillForegroundColor(), srcpalette, destpalette) && style.getFillPattern() == old.getFillPattern() && style.getHidden() == old.getHidden() && style.getIndention() == old.getIndention() && isSameColor(style.getLeftBorderColor(), old.getLeftBorderColor(), srcpalette, destpalette) && style.getLocked() == old.getLocked() && isSameColor(style.getRightBorderColor(), old.getRightBorderColor(), srcpalette, destpalette) && style.getRotation() == old.getRotation() && isSameColor(style.getTopBorderColor(), old.getTopBorderColor(), srcpalette, destpalette) && style.getVerticalAlignment() == old.getVerticalAlignment() && style.getWrapText() == old.getWrapText()) { HSSFFont oldfont = destwb.getFontAt(old.getFontIndex()); HSSFFont font = srcwb.getFontAt(style.getFontIndex()); if (oldfont.getBoldweight() == font.getBoldweight() && oldfont.getItalic() == font.getItalic() && oldfont.getStrikeout() == font.getStrikeout() && oldfont.getCharSet() == font.getCharSet() && isSameColor(oldfont.getColor(), font.getColor(), srcpalette, destpalette) && oldfont.getFontHeight() == font.getFontHeight() && oldfont.getFontName().equals(font.getFontName()) && oldfont.getTypeOffset() == font.getTypeOffset() && oldfont.getUnderline() == font.getUnderline()) { return old; } } } return null; }
From source file:com.griffinslogistics.excel.BDLGenerator.java
private static Map<String, CellStyle> createStyles(Workbook workbook) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style;//from w w w . j av a 2s . c o m Font titleFont = workbook.createFont(); titleFont.setFontHeightInPoints((short) 26); titleFont.setFontName("Calibri"); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = workbook.createCellStyle(); style.setFont(titleFont); style.setAlignment(CellStyle.ALIGN_CENTER); styles.put("title", style); titleFont = workbook.createFont(); titleFont.setFontHeightInPoints((short) 18); titleFont.setFontName("Calibri"); style = workbook.createCellStyle(); style.setFont(titleFont); style.setAlignment(CellStyle.ALIGN_JUSTIFY); styles.put("pulsioName", style); titleFont = workbook.createFont(); titleFont.setFontHeightInPoints((short) 14); titleFont.setFontName("Calibri"); style = workbook.createCellStyle(); style.setFont(titleFont); style.setAlignment(CellStyle.ALIGN_JUSTIFY); styles.put("contacts", style); CellStyle footerStyle = workbook.createCellStyle(); Font footerFont = workbook.createFont(); footerFont.setFontHeightInPoints((short) 14); footerFont.setFontName("Calibri"); footerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); footerStyle.setFont(footerFont); footerStyle.setAlignment(CellStyle.ALIGN_JUSTIFY); styles.put("footer", footerStyle); titleFont = workbook.createFont(); titleFont.setFontHeightInPoints((short) 14); titleFont.setFontName("Calibri"); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); titleFont.setItalic(true); style = workbook.createCellStyle(); style.setFont(titleFont); style.setAlignment(CellStyle.ALIGN_CENTER); style.setWrapText(true); // Override 25% grey to lighter grey HSSFWorkbook hssfWorkbook = (HSSFWorkbook) workbook; HSSFPalette palette = hssfWorkbook.getCustomPalette(); palette.setColorAtIndex(HSSFColor.GREY_25_PERCENT.index, (byte) 242, //RGB red (0-255) (byte) 242, //RGB green (byte) 242 //RGB blue ); style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setBorderLeft(CellStyle.BORDER_MEDIUM); style.setBorderTop(CellStyle.BORDER_MEDIUM); style.setBorderRight(CellStyle.BORDER_THIN); style.setBorderBottom(CellStyle.BORDER_THIN); styles.put("tableHeadersLeft", style); CellStyle headerRowMiddleCellStyle = workbook.createCellStyle(); headerRowMiddleCellStyle.cloneStyleFrom(style); headerRowMiddleCellStyle.setBorderLeft(CellStyle.BORDER_THIN); styles.put("tableHeadersMiddle", headerRowMiddleCellStyle); CellStyle headerRowRightCellStyle = workbook.createCellStyle(); headerRowRightCellStyle.cloneStyleFrom(style); headerRowRightCellStyle.setBorderRight(CellStyle.BORDER_MEDIUM); styles.put("tableHeadersRight", headerRowRightCellStyle); CellStyle footerRowRightCellStyle = workbook.createCellStyle(); footerRowRightCellStyle.cloneStyleFrom(style); footerRowRightCellStyle.setFillPattern(CellStyle.NO_FILL); footerRowRightCellStyle.setBorderRight(CellStyle.BORDER_MEDIUM); footerRowRightCellStyle.setBorderBottom(CellStyle.BORDER_MEDIUM); styles.put("tableFooters", footerRowRightCellStyle); CellStyle bodyRowLeftCellStyle = workbook.createCellStyle(); bodyRowLeftCellStyle.cloneStyleFrom(style); Font titleBodyFont = workbook.createFont(); titleBodyFont.setFontHeightInPoints((short) 14); titleBodyFont.setFontName("Calibri"); bodyRowLeftCellStyle.setFont(titleBodyFont); bodyRowLeftCellStyle.setBorderTop(CellStyle.BORDER_THIN); bodyRowLeftCellStyle.setFillPattern(CellStyle.NO_FILL); styles.put("tableBodyLeft", bodyRowLeftCellStyle); CellStyle bodyRowMiddleCellStyle = workbook.createCellStyle(); bodyRowMiddleCellStyle.cloneStyleFrom(bodyRowLeftCellStyle); bodyRowMiddleCellStyle.setBorderLeft(CellStyle.BORDER_THIN); styles.put("tableBodyMiddle", bodyRowMiddleCellStyle); CellStyle bodyRowRightCellStyle = workbook.createCellStyle(); bodyRowRightCellStyle.cloneStyleFrom(bodyRowMiddleCellStyle); bodyRowRightCellStyle.setBorderRight(CellStyle.BORDER_MEDIUM); styles.put("tableBodyRight", bodyRowRightCellStyle); return styles; }