List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet autoSizeColumn
@Override public void autoSizeColumn(int column)
This process can be relatively slow on large sheets, so this should normally only be called once per column, at the end of your processing.
From source file:org.eclipse.titanium.markers.export.XlsProblemExporter.java
License:Open Source License
/** * Create the summary sheet in the exported document. * /*from w w w . j av a 2 s .co m*/ * @param workbook the workbook to work in. * */ private void createTimeSheet(final HSSFWorkbook workbook) { final HSSFSheet timeSheet = workbook.createSheet("Repair times"); workbook.setSheetOrder("Repair times", 1); final Row headerRow = timeSheet.createRow(0); headerRow.createCell(1).setCellValue("Minimal repair time"); headerRow.createCell(2).setCellValue("Average repair time"); headerRow.createCell(3).setCellValue("Maximal repair time"); int summaryRow = 4; Cell label; for (final TaskType t : TaskType.values()) { final Row row2 = timeSheet.createRow(summaryRow); label = row2.createCell(0); label.setCellValue(t.getHumanReadableName()); final Cell minTimeCell = row2.createCell(1); minTimeCell.setCellType(HSSFCell.CELL_TYPE_FORMULA); minTimeCell.setCellFormula(t.getMinRepairTime() + "*Summary!$B" + (summaryRow + 1)); final Cell avgTimeCell = row2.createCell(2); avgTimeCell.setCellType(HSSFCell.CELL_TYPE_FORMULA); avgTimeCell.setCellFormula(t.getAvgRepairTime() + "*Summary!$B" + (summaryRow + 1)); final Cell maxTimeCell = row2.createCell(3); maxTimeCell.setCellType(HSSFCell.CELL_TYPE_FORMULA); maxTimeCell.setCellFormula(t.getMaxRepairTime() + "*Summary!$B" + (++summaryRow)); } for (final CodeSmellType t : CodeSmellType.values()) { final Row row2 = timeSheet.createRow(summaryRow); label = row2.createCell(0); label.setCellValue(t.getHumanReadableName()); final Cell minTimeCell = row2.createCell(1); minTimeCell.setCellType(HSSFCell.CELL_TYPE_FORMULA); minTimeCell.setCellFormula(t.getMinRepairTime() + "*Summary!$B" + (summaryRow + 1)); final Cell avgTimeCell = row2.createCell(2); avgTimeCell.setCellType(HSSFCell.CELL_TYPE_FORMULA); avgTimeCell.setCellFormula(t.getAvgRepairTime() + "*Summary!$B" + (summaryRow + 1)); final Cell maxTimeCell = row2.createCell(3); maxTimeCell.setCellType(HSSFCell.CELL_TYPE_FORMULA); maxTimeCell.setCellFormula(t.getMaxRepairTime() + "*Summary!$B" + (++summaryRow)); } final Row totalRow = timeSheet.createRow(1); totalRow.createCell(0).setCellValue("Total"); final Cell cell1 = totalRow.createCell(1); cell1.setCellType(HSSFCell.CELL_TYPE_FORMULA); cell1.setCellFormula("SUM($B4:$B" + summaryRow + ")"); final Cell cell2 = totalRow.createCell(2); cell2.setCellType(HSSFCell.CELL_TYPE_FORMULA); cell2.setCellFormula("SUM($C4:$C" + summaryRow + ")"); final Cell cell3 = totalRow.createCell(3); cell3.setCellType(HSSFCell.CELL_TYPE_FORMULA); cell3.setCellFormula("SUM($D4:$D" + summaryRow + ")"); timeSheet.autoSizeColumn(0); timeSheet.autoSizeColumn(1); timeSheet.autoSizeColumn(2); timeSheet.autoSizeColumn(3); }
From source file:org.eclipse.titanium.markers.export.XlsProblemExporter.java
License:Open Source License
/** * Create a page for a task type.//ww w . j a v a2 s .c o m * * @param workbook the workbook to work in. * @param t the task type to export. * @param markers the list of markers. * */ private void createTaskSheet(final HSSFWorkbook workbook, final TaskType t, final List<IMarker> markers) { if (markers.isEmpty()) { return; } int currentRow = 1; final String sheetName = t.getHumanReadableName(); final HSSFSheet sheet = workbook.createSheet(sheetName); Row row = sheet.createRow(0); row.createCell(0).setCellValue("Description"); row.createCell(1).setCellValue("Resource"); row.createCell(2).setCellValue("Location"); Cell label; Cell numberCell; for (final IMarker m : markers) { row = sheet.createRow(currentRow); label = row.createCell(0); try { label.setCellValue(m.getAttribute(IMarker.MESSAGE).toString()); } catch (CoreException e) { label.setCellValue("<unknown>"); } label = row.createCell(1); label.setCellValue(m.getResource().getName()); numberCell = row.createCell(2); try { numberCell.setCellValue(Double.parseDouble(m.getAttribute(IMarker.LINE_NUMBER).toString())); } catch (CoreException e) { // Do nothing } ++currentRow; } sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); }
From source file:org.eclipse.titanium.markers.export.XlsProblemExporter.java
License:Open Source License
/** * Create a page for a code smell./*from w w w . j av a 2s.c om*/ * * @param workbook the workbook to work in. * @param mh the markerhandler object knowing the occurences of the given code smell * @param t the codesmell type to export * */ private void createCodeSmellSheet(final HSSFWorkbook workbook, final MarkerHandler mh, final CodeSmellType t) { if (mh.get(t).isEmpty()) { return; } final String sheetName = t.name(); final HSSFSheet sheet = workbook.createSheet(sheetName); Row row = sheet.createRow(0); row.createCell(0).setCellValue("Description"); row.createCell(1).setCellValue("Resource"); row.createCell(2).setCellValue("Location"); int currentRow = 1; Cell label; for (final Marker m : mh.get(t)) { if (m.getLine() == -1 || m.getResource() == null) { // TODO this might need a second thought continue; } try { row = sheet.createRow(currentRow); label = row.createCell(0); label.setCellValue(m.getMessage()); label = row.createCell(1); label.setCellValue(m.getResource().getName()); label = row.createCell(2); label.setCellValue(m.getLine()); ++currentRow; } catch (Exception e) { ErrorReporter.logWarning("Only " + currentRow + " rows were written: the limit has been reached."); break; } } sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); }
From source file:org.emmanet.controllers.statsSpreadsheetController.java
License:Apache License
public Boolean populateReqShipSheets(Map arg0, int irowStart, HSSFSheet sheetID, String state, HSSFWorkbook wb) {//from w w w .ja v a2s. c o m //state can equal either Reqs or Ship // GREY background STYLES NOT WORKING??? HSSFCellStyle style = wb.createCellStyle(); style.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); // style.setFillPattern(CellStyle.BIG_SPOTS); List results = (List) arg0.get("EUCOMM" + state.toLowerCase() + "MonthYear");///## String sheetTitle = ""; if (state.equals("Ship")) { sheetTitle = "Strains Shipped"; } if (state.equals("Reqs")) { sheetTitle = "Strains Requested"; } Map map = new HashMap(); Object O = arg0.get("Centres"); int irow = irowStart; HSSFRow rowTitle = sheetID.createRow(irow - 1); HSSFRichTextString titleData = new HSSFRichTextString(sheetTitle); //rowTitle.createCell((short) 0).setCellValue(titleData); HSSFCell cell = rowTitle.createCell((short) 0); cell.setCellValue(titleData); if (sheetTitle.equals("Strains Requested")) { sheetID.autoSizeColumn((short) 0); } cell.setCellStyle((HSSFCellStyle) style); map = (Map) O; System.out.println("Object [0])" + O.toString()); Iterator it = map.keySet().iterator(); while (it.hasNext()) { String key = (String) it.next(); HSSFRow row = sheetID.createRow(irow); HSSFRichTextString data = new HSSFRichTextString(key); row.createCell((short) 0).setCellValue(data); /*use same row to create cell + results list value and count*/ double[] CNB = (double[]) arg0.get("EUCOMM" + state + "ByMonthYearCentre-CNB"); double[] CNR = (double[]) arg0.get("EUCOMM" + state + "ByMonthYearCentre-CNR"); double[] HMGU = (double[]) arg0.get("EUCOMM" + state + "ByMonthYearCentre-HMGU"); double[] ICS = (double[]) arg0.get("EUCOMM" + state + "ByMonthYearCentre-ICS"); double[] MRC = (double[]) arg0.get("EUCOMM" + state + "ByMonthYearCentre-MRC"); double[] SANG = (double[]) arg0.get("EUCOMM" + state + "ByMonthYearCentre-SANG"); double[] CNRS = (double[]) arg0.get("CNRS" + state + "ByMonthYearCentre-CNRS"); for (int ii = 0; ii < results.size(); ii++) { String colData = ""; if (key.equals("CNB")) { int iDbleVal = (int) CNB[ii]; System.out.println("CNB Val is:" + iDbleVal); colData = "" + iDbleVal + ""; } if (key.equals("CNR")) { int iDbleVal = (int) CNR[ii]; System.out.println("CNR Val is:" + iDbleVal); colData = "" + iDbleVal + ""; } if (key.equals("HMGU")) { int iDbleVal = (int) HMGU[ii]; System.out.println("HMGU Val is:" + iDbleVal); colData = "" + iDbleVal + ""; } if (key.equals("ICS")) { int iDbleVal = (int) ICS[ii]; System.out.println("ICS Val is:" + iDbleVal); colData = "" + iDbleVal + ""; } if (key.equals("MRC")) { int iDbleVal = (int) MRC[ii]; System.out.println("MRC Val is:" + iDbleVal); colData = "" + iDbleVal + ""; } if (key.equals("SANG")) { int iDbleVal = (int) SANG[ii]; System.out.println("SANG Val is:" + iDbleVal); colData = "" + iDbleVal + ""; } /* if (key.equals("CNRS")) { int iDbleVal = (int) CNRS[ii]; System.out.println("CNRS Val is:" + iDbleVal); colData = "" + iDbleVal + ""; }*/ HSSFRichTextString Data = new HSSFRichTextString(colData); int iCell = ii + 1; row.createCell((short) iCell).setCellValue(Data); } System.out.println("key value=" + key); irow++; } // HSSFRow row = sheet2.getRow(1); HSSFRow row = sheet2.createRow(irowStart - 1); System.out.println("results size" + results.size() + " row " + row); int iCol = 1; for (Iterator it1 = results.listIterator(); it1.hasNext();) { Object[] o = (Object[]) it1.next(); HSSFRichTextString data = new HSSFRichTextString(o[0].toString() + " " + o[1].toString()); row.createCell((short) iCol).setCellValue(data); iCol++; //System.out.println("\t" + sendDataCNB[j] + "\t" + sendDataCNR[j] + "\t" + sendDataHMGU[j] + "\t" + sendDataICS[j] + "\t" + sendDataMRC[j] + "\t" + sendDataSANG[j] + "\t"); } //shipped counts int iirow = 0; int iirow1 = 0; int[] lists = null; int[] lists1 = null; // boolean reqsSet = false;///# HSSFRichTextString header = new HSSFRichTextString(""); HSSFRichTextString header1 = new HSSFRichTextString(""); if (state.equals("Ship")) { iirow = 36;//29; header = new HSSFRichTextString("Shipped Totals"); lists = (int[]) arg0.get(state.toLowerCase() + "Counts"); } else if (state.equals("Reqs")) { iirow = 12;//0; // reqsSet = true;//# header = new HSSFRichTextString("Request Totals"); lists = (int[]) arg0.get(state.toLowerCase() + "Counts"); if (state.equals("Reqs")) {//# iirow1 = 12;//0;//# header1 = new HSSFRichTextString("Requests Cancelled Totals");//# lists1 = (int[]) arg0.get("reqsCountsCanc"); } } ///// int[] lists = (int[]) arg0.get(state.toLowerCase() + "Counts"); /* now base on Reqs set new data list if not empty then dump it out or construct a method * * to write extra out */ HSSFRow rowHeader = sheet2.createRow(iirow - 1); rowHeader.createCell((short) 1).setCellValue(header); it = map.keySet().iterator(); int iCodeRow = iirow; while (it.hasNext()) { String key = (String) it.next(); HSSFRow rowSiteCodes = sheet2.createRow(iCodeRow); HSSFRichTextString dataSiteCodes = new HSSFRichTextString(key); if (state.equals("CancReqs")) {//# rowSiteCodes.createCell((short) 4).setCellValue(dataSiteCodes);//NEEDS CHANGING FOR CANC } else { rowSiteCodes.createCell((short) 0).setCellValue(dataSiteCodes);//NEEDS CHANGING FOR CANC } iCodeRow++; } for (int i = 0; i < lists.length; i++) { Object o = (Object) lists[i]; HSSFRow rowLists = sheet2.createRow(iirow); HSSFRichTextString data = new HSSFRichTextString(o.toString()); rowLists.createCell((short) 1).setCellValue(data); iirow++; } // cancelled request counts. if (lists1 != null) { HSSFRow rowHeader1 = sheet2.createRow(iirow1 - 1); rowHeader1.createCell((short) 5).setCellValue(header1); for (int i = 0; i < lists1.length; i++) { Object o = (Object) lists1[i]; HSSFRow rowLists = sheet2.createRow(iirow1); HSSFRichTextString data = new HSSFRichTextString(o.toString()); rowLists.createCell((short) 5).setCellValue(data); iirow1++; } } //end shipped counts return true; }
From source file:org.emmanet.controllers.statsSpreadsheetController.java
License:Apache License
public boolean populateDataCells(HSSFSheet arg1, List results, int startRow, boolean freezeFrame) { System.out.println("results size" + results.size()); int irow = startRow;//0 row set to header already for (Iterator it = results.listIterator(); it.hasNext();) { Object[] o = (Object[]) it.next(); HSSFRow row = arg1.createRow(irow); //System.out.println("O length is::" + o.length); for (int i = 0; i < o.length; i++) { if (o[i] == null) { o[i] = ""; }//from w ww .j a va 2s . c o m HSSFRichTextString data = new HSSFRichTextString(o[i].toString()); Pattern pattern = Pattern.compile(PATTERN); Matcher matcher = pattern.matcher(o[i].toString()); boolean matchFound = matcher.matches(); if (matchFound) { //this is a numeric data field format as such double value = Double.parseDouble(o[i].toString()); row.createCell((short) i, 0).setCellValue(value); } else { row.createCell((short) i).setCellValue(data); } //System.out.println(i + ". result value= " + o.length + " ---- " +o[i].toString()); } irow++; // System.out.println(); } // AUTOSIZE COLUMNS TO DATA for (int i = 0; i <= headerTitle.length; i++) { arg1.autoSizeColumn((short) i); } if (freezeFrame) { arg1.createFreezePane(0, 1, 0, 1); } return true; }
From source file:org.generationcp.breeding.manager.crossingmanager.util.CrossingManagerExporter.java
License:Open Source License
public FileOutputStream exportCrossingManagerExcel(String filename) throws CrossingManagerExporterException { //create workbook HSSFWorkbook wb = new HSSFWorkbook(); //create two worksheets - Description and Observations HSSFSheet descriptionSheet = wb.createSheet("Description"); HSSFSheet observationSheet = wb.createSheet("Observation"); Map<Germplasm, Name> crossesMap = crossesMade.getCrossesMap(); if (crossesMap != null && !crossesMap.isEmpty()) { HashMap<String, CellStyle> sheetStyles = createStyles(wb); //write Description sheet int lastRow = 0; lastRow = writeStudyDetailsSection(sheetStyles, descriptionSheet, 1); lastRow = writeConditionSection(sheetStyles, descriptionSheet, lastRow + 1); lastRow = writeFactorSection(sheetStyles, descriptionSheet, lastRow + 2); //two rows before section lastRow = writeConstantsSection(sheetStyles, descriptionSheet, lastRow + 1); lastRow = writeVariateSection(sheetStyles, descriptionSheet, lastRow + 1); //write Observation sheet writeObservationsSheet(sheetStyles, observationSheet, 1); }/*from www . j a va 2 s . co m*/ //adjust column widths of description sheet to fit contents for (int ctr = 0; ctr < NUM_OF_COLUMNS; ctr++) { descriptionSheet.autoSizeColumn(ctr); } //adjust column widths of observation sheet to fit contents for (int ctr = 0; ctr < NUM_OF_COLUMNS; ctr++) { observationSheet.autoSizeColumn(ctr); } try { //write the excel file FileOutputStream fileOutputStream = new FileOutputStream(filename); wb.write(fileOutputStream); fileOutputStream.close(); return fileOutputStream; } catch (Exception ex) { throw new CrossingManagerExporterException("Error writing file to: " + filename, ex); } }
From source file:org.generationcp.breeding.manager.nurserytemplate.util.NurseryTemplateManagerExporter.java
License:Open Source License
public FileOutputStream exportNurseryTemplateManagerExcel(String filename) throws CrossingManagerExporterException { //create workbook HSSFWorkbook wb = new HSSFWorkbook(); //create two worksheets - Description and Observations HSSFSheet descriptionSheet = wb.createSheet("Description"); HSSFSheet observationSheet = wb.createSheet("Observation"); // Map<Germplasm, Name> crossesMap = crossesMade.getCrossesMap(); // if (crossesMap != null && !crossesMap.isEmpty()){ HashMap<String, CellStyle> sheetStyles = createStyles(wb); //write Description sheet int lastRow = 0; lastRow = writeStudyDetailsSection(sheetStyles, descriptionSheet, 1); lastRow = writeConditionSection(sheetStyles, descriptionSheet, lastRow + 1); lastRow = writeFactorSection(sheetStyles, descriptionSheet, lastRow + 2); //two rows before section lastRow = writeConstantsSection(sheetStyles, descriptionSheet, lastRow + 1); lastRow = writeVariateSection(sheetStyles, descriptionSheet, lastRow + 1); //write Observation sheet writeObservationsSheet(sheetStyles, observationSheet, 1); // }/*w w w . ja v a 2s. co m*/ //adjust column widths of description sheet to fit contents for (int ctr = 0; ctr < NUM_OF_COLUMNS; ctr++) { descriptionSheet.autoSizeColumn(ctr); } //adjust column widths of observation sheet to fit contents for (int ctr = 0; ctr < NUM_OF_COLUMNS; ctr++) { observationSheet.autoSizeColumn(ctr); } try { //write the excel file FileOutputStream fileOutputStream = new FileOutputStream(filename); wb.write(fileOutputStream); fileOutputStream.close(); return fileOutputStream; } catch (Exception ex) { throw new CrossingManagerExporterException("Error writing file to: " + filename, ex); } }
From source file:org.goobi.production.flow.helper.SearchResultHelper.java
License:Open Source License
@SuppressWarnings("deprecation") public HSSFWorkbook getResult(List<SearchColumn> columnList, String filter, String order, boolean showClosedProcesses, boolean showArchivedProjects) { List<SearchColumn> sortedList = new ArrayList<>(columnList.size()); for (SearchColumn sc : columnList) { if (!sc.getTableName().startsWith("metadata")) { sortedList.add(sc);// w w w .j av a 2s.co m } } for (SearchColumn sc : columnList) { if (sc.getTableName().startsWith("metadata")) { sortedList.add(sc); } } columnList = sortedList; @SuppressWarnings("rawtypes") List list = search(columnList, filter, order, showClosedProcesses, showArchivedProjects); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Search results"); // create title row int titleColumnNumber = 0; HSSFRow title = sheet.createRow(0); int columnNumber = 0; for (SearchColumn sc : columnList) { HSSFCell titleCell = title.createCell(titleColumnNumber++); titleCell.setCellValue(Helper.getTranslation(sc.getValue())); HSSFCellStyle cellStyle = wb.createCellStyle(); HSSFFont cellFont = wb.createFont(); cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(cellFont); titleCell.setCellStyle(cellStyle); } int rowNumber = 1; for (Object obj : list) { Object[] objArr = (Object[]) obj; HSSFRow row = sheet.createRow(rowNumber++); columnNumber = 0; for (Object entry : objArr) { HSSFCell cell = row.createCell(columnNumber++); if (entry != null) { cell.setCellValue(((String) entry).replace("\"", "")); } else { cell.setCellValue(""); } } } sheet.createFreezePane(0, 1); for (int i = 0; i < columnList.size(); i++) { sheet.autoSizeColumn(i); if (sheet.getColumnWidth(i) > 15000) { sheet.setColumnWidth(i, 15000); } } return wb; }
From source file:org.ism.view.TableView.java
/** * ************************************************************************ * Gestion des document au format XLS/* w w w. ja va 2s .c om*/ * * @param document * *********************************************************************** */ public void handlePostProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0); HSSFRow header = sheet.getRow(0); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { header.getCell(i).setCellStyle(cellStyle); sheet.autoSizeColumn(i); } }
From source file:org.ivan.service.ExcelExporter.java
public <T extends Object> File createExcel(List<T> objects, String fileName, String sheetName) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(sheetName); HSSFFont font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle style = workbook.createCellStyle(); style.setFont(font);//from ww w . ja v a2 s . c o m int cellNum = 0; int rowNum = 0; List<String> headers = getHeadersFromGetMethods(objects.get(0)); Row row = sheet.createRow(rowNum++); for (int i = 0; i < headers.size(); i++) { String cup = headers.get(i); Cell cell = row.createCell(cellNum++); cell.setCellValue(cup); cell.setCellStyle(style); } for (T obj : objects) { cellNum = 0; List<String> parameters = getValuesRecursive(obj); row = sheet.createRow(rowNum++); for (String parameter : parameters) { Cell cell = row.createCell(cellNum++); cell.setCellValue(parameter); sheet.autoSizeColumn(cellNum); } } try { FileOutputStream out = new FileOutputStream(new File(fileName)); workbook.write(out); out.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return null; }