List of usage examples for org.apache.poi.ss.usermodel Sheet setFitToPage
void setFitToPage(boolean value);
From source file:br.com.algoritmo.compilacao.CompilaXlsx.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;//from w w w . j ava 2 s . co m Map<Integer, Object[]> data = new TreeMap<Integer, Object[]>(); data.put(0, new Object[] { 0, "Luiz Carlos Miyadaira Ribeiro Junior", "Base", "0468265522433921", "SOFTWARE", null, null, null }); data.put(1, new Object[] { 1, "Sergio Antnio Andrade de Freitas", "Destino 1", "0395549254894676", "SOFTWARE", null, null, null }); data.put(2, new Object[] { 2, "Andre Luiz Aquere de Cerqueira e Souza", "Destino 2", "8424412648258970", "CIVIL", null, null, null }); data.put(3, new Object[] { 3, "Edson Mintsu Hung Destino", "Destino 3", "6753551743147880", "ELETRNICA", null, null, null }); data.put(4, new Object[] { 4, "Edgard Costa Oliveira", "Destino 4", "1196380808351110", "SOFTWARE", null, null, null }); data.put(5, new Object[] { 5, "Edson Alves da Costa Jnior", "Destino 5", "2105379147123450", "SOFTWARE", null, null, null }); data.put(6, new Object[] { 6, "Andr Barros de Sales", "Destino 6", "7610669796869660", "SOFTWARE", null, null, null }); data.put(7, new Object[] { 7, "Giovanni Almeida dos Santos", "Destino 7", "0580891429319047", "SOFTWARE", null, null, null }); data.put(8, new Object[] { 8, "Cristiane Soares Ramos", "Destino 8", "9950213660160160", "SOFTWARE", null, null, null }); data.put(9, new Object[] { 9, "Fabricio Ataides Braz", "Destino 9", "1700216932505000", "SOFTWARE", null, null, null }); data.put(10, new Object[] { 10, "Alexandre Srgio de Arajo Bezerra", "Destino 10", "0255998976169051", "MEDICINA", null, null, null }); data.put(11, new Object[] { 11, "Eduardo Stockler Tognetti", "Destino 11", "2443108673822680", "ELTRICA", null, null, null }); data.put(12, new Object[] { 12, "Jan Mendona Correa", "Destino 12", "7844006017790570", "CINCIA DA COMPUTAO", null, null, null }); data.put(13, new Object[] { 13, "Rejane Maria da Costa Figueiredo", "Destino 13", "2187680174312042", "SOFTWARE", null, null, null }); data.put(14, new Object[] { 14, "Augusto Csar de Mendona Brasil", "Destino 14", "0571960641751286", "ENERGIA", null, null, null }); data.put(15, new Object[] { 15, "Fbio Macdo Mendes", "Destino 15", "8075435338067780", "F?SICA", null, null, null }); if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet aba1 = wb.createSheet("Percentual de similaridade 1"); PrintSetup printSetup = aba1.getPrintSetup(); printSetup.setLandscape(true); aba1.setFitToPage(true); aba1.setHorizontallyCenter(true); Sheet aba2 = wb.createSheet("Percentual de similaridade 2"); PrintSetup printSetup2 = aba2.getPrintSetup(); printSetup2.setLandscape(true); aba1.setFitToPage(true); aba1.setHorizontallyCenter(true); //title row Row titleRow = aba1.createRow(0); titleRow.setHeightInPoints(15); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue( "Resultado da aplicao do algoritmo de clculo do percentual de similaridade entre os indivduos"); titleCell.setCellStyle(styles.get("title")); aba1.addMergedRegion(CellRangeAddress.valueOf("$A$1:$H$1")); //header row Row headerRow = aba1.createRow(1); headerRow.setHeightInPoints(15); Cell headerCell; for (int i = 1; i <= titles.length; i++) { headerCell = headerRow.createCell(i); headerCell.setCellValue(titles[i - 1]); headerCell.setCellStyle(styles.get("header")); } Row headerBase = aba1.createRow(2); headerBase.setHeightInPoints(15); Cell headerCellBase; for (int i = 1; i <= base.length; i++) { headerCellBase = headerBase.createCell(i); headerCellBase.setCellValue(base[i - 1]); headerCellBase.setCellStyle(styles.get("header1")); } Row headerDestino = aba1.createRow(4); headerDestino.setHeightInPoints(15); Cell headerCellDestino; for (int i = 1; i <= destino.length; i++) { headerCellDestino = headerDestino.createCell(i); headerCellDestino.setCellValue(destino[i - 1]); headerCellDestino.setCellStyle(styles.get("header1")); } /*int rownum = 2; for (int i = 0; i < 10; i++) { Row row = sheet.createRow(rownum++); for (int j = 0; j < titles.length; j++) { Cell cell = row.createCell(j); if(j == 9){ //the 10th cell contains sum over week days, e.g. SUM(C3:I3) String ref = "C" +rownum+ ":I" + rownum; cell.setCellFormula("SUM("+ref+")"); cell.setCellStyle(styles.get("formula")); } else if (j == 11){ cell.setCellFormula("J" +rownum+ "-K" + rownum); cell.setCellStyle(styles.get("formula")); } else { cell.setCellStyle(styles.get("cell")); } } } rownum = 3; for (int i = 0; i < 10; i++) { Row row = sheet.createRow(rownum++); for (int j = 0; j < titles1.length; j++) { Cell cell = row.createCell(j); if(j == 9){ //the 10th cell contains sum over week days, e.g. SUM(C3:I3) String ref = "C" +rownum+ ":I" + rownum; cell.setCellFormula("SUM("+ref+")"); cell.setCellStyle(styles.get("formula")); } else if (j == 11){ cell.setCellFormula("J" +rownum+ "-K" + rownum); cell.setCellStyle(styles.get("formula")); } else { cell.setCellStyle(styles.get("cell")); } } } */ //set sample data //Iterate over data and write to sheet Set<Integer> keyset = data.keySet(); int rownum = 0; for (Integer key : keyset) { Row row = aba1.createRow(3 + rownum++); Object[] objArr = data.get(key); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Integer) cell.setCellValue((Integer) obj); } if (row.getRowNum() == 3) { rownum++; } } //finally set column widths, the width is measured in units of 1/256th of a character width aba1.setColumnWidth(0, 2 * 256); //2 characters wide aba1.setColumnWidth(1, 26 * 256); //26 characters wide aba1.setColumnWidth(2, 20 * 256); //20 characters wide aba1.setColumnWidth(3, 18 * 256); //18 characters wide aba1.setColumnWidth(4, 20 * 256); //20 characters wide for (int i = 5; i < 9; i++) { aba1.setColumnWidth(i, 15 * 256); //6 characters wide } // Write the output to a file String file = "Sada/Percentual de similaridade.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:br.com.techne.gluonsoft.eowexport.builder.ExcelBuilder.java
License:Apache License
/** * mtodo cria bytes de documento Excel/* w w w. ja v a 2 s . c o m*/ * @param titles * @param columnIndex * @param dataRows * @param locale * @return * @throws Exception */ public static byte[] createExcelBytes(String[] titles, String[] columnIndex, List<HashMap<String, Object>> dataRows, Locale locale) throws Exception { //Workbook wb = new HSSFWorkbook(); XSSFWorkbook wb = new XSSFWorkbook(); byte[] outBytes; try { HashMap<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Tab 1"); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); //the following three statements are required only for HSSF sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); //the header row: centered text in 48pt font Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(12.75f); for (int indexColumn = 0; indexColumn < titles.length; indexColumn++) { Cell cell = headerRow.createCell(indexColumn); cell.setCellValue(titles[indexColumn]); if ((titles.length - 1) < indexColumn) { cell.setCellValue(""); } else cell.setCellValue(titles[indexColumn]); cell.setCellStyle(styles.get("header")); } //freeze the first row sheet.createFreezePane(0, 1); Row row; Cell cell; int rownum = 1;//devido constar titulo, comea do indice 1 ValueCellUtil vcutil = new ValueCellUtil(locale); for (int indexRow = 0; indexRow < dataRows.size(); indexRow++, rownum++) { row = sheet.createRow(rownum); HashMap<String, Object> dataRow = dataRows.get(indexRow); if (dataRow == null) continue; List<String> keysAttribs = null; if (columnIndex.length == 0) { keysAttribs = Arrays.asList(dataRow.keySet().toArray(new String[0])); Collections.reverse(keysAttribs); } else { keysAttribs = Arrays.asList(columnIndex); } int colCt = 0; for (String keyAttrib : keysAttribs) { cell = row.createCell(colCt); String styleName; cell.setCellValue(vcutil.parseValue(dataRow.get(keyAttrib)).toString()); //zebrando tabela if (indexRow % 2 == 0) { // even row styleName = "cell_normal_even"; } else { // odd row styleName = "cell_normal_odd"; } if (indexRow == 0) { //setando auto ajuste sheet.autoSizeColumn(colCt); } cell.setCellStyle(styles.get(styleName)); colCt++; } } sheet.setZoom(75); //75% scale // Write the output to a file // write for return byte[] ByteArrayOutputStream out = new ByteArrayOutputStream(); try { wb.write(out); outBytes = out.toByteArray(); } finally { out.close(); } } finally { wb.close(); } return outBytes; }
From source file:com.actelion.research.spiritapp.report.AbstractReport.java
License:Open Source License
/** * Creates a new sheet, ensuring that the name is safe and unique * @param workbook/*w w w . j a va 2s .co m*/ * @param sheetName * @return */ public Sheet createSheet(Workbook workbook, String sheetName) { Set<String> names = new HashSet<String>(); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { names.add(workbook.getSheetName(i)); } String safe = WorkbookUtil.createSafeSheetName(sheetName); String name; for (int i = 0;; i++) { name = safe + (i == 0 ? "" : " (" + i + ")"); if (!names.contains(name)) break; } Sheet sheet = wb.createSheet(name); sheet.setAutobreaks(true); sheet.setMargin(Sheet.LeftMargin, 1); sheet.setMargin(Sheet.RightMargin, 1); sheet.setMargin(Sheet.BottomMargin, .5); sheet.setMargin(Sheet.TopMargin, .5); sheet.setFitToPage(true); sheet.getPrintSetup().setLandscape(true); sheet.getPrintSetup().setFitWidth((short) 1); sheet.getPrintSetup().setFitHeight((short) 99); // Footer footer = sheet.getFooter(); // footer.setRight( "Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages() ); return sheet; }
From source file:com.actelion.research.spiritapp.report.SamplesLocationReport.java
License:Open Source License
@Override protected void populateWorkBook() throws Exception { boolean showWithoutLocation = getParameter(SHOW_WITHOUT_LOCATION_PARAMETER) == Boolean.TRUE; boolean showResults = getParameter(SHOW_RESULTS_PARAMETER) == Boolean.TRUE; SpiritUser user = SpiritFrame.getUser(); //Load the samples and their results List<Biosample> allSamples = DAOBiosample .queryBiosamples(BiosampleQuery.createQueryForStudyIds(study.getStudyId()), user); Map<Biosample, List<Result>> sample2results = new HashMap<Biosample, List<Result>>(); if (showResults) { List<Result> results = DAOResult .queryResults(ResultQuery.createQueryForBiosampleIds(JPAUtil.getIds(allSamples)), user); sample2results = Result.mapBiosample(results); }/*w w w . j av a 2 s .c om*/ Collections.sort(allSamples, Biosample.HIERARCHY_COMPARATOR); if (allSamples.size() == 0) throw new Exception( "There are no samples to be reported. Make sure you have a sampling template with some required weighings."); //Loop through each sample and display the data Sheet sheet = createSheet(wb, "Sample Locations"); sheet.setFitToPage(true); createHeadersWithTitle(sheet, study, "Sample locations"); /////////////// // 0 1 2 3 4 5 6 7 8 9 10 //5 Locat. CType CId Group Phase TopId SId Biotype Meta. Comment Owner //Write headers int y = 3; int x = 0; set(sheet, y, x++, "Location", Style.S_TH_LEFT); set(sheet, y, x++, "ContainerType", Style.S_TH_LEFT); set(sheet, y, x++, "ContainerId", Style.S_TH_LEFT); set(sheet, y, x++, "Group", Style.S_TH_LEFT); set(sheet, y, x++, "Phase", Style.S_TH_LEFT); set(sheet, y, x++, "ParticipantId", Style.S_TH_LEFT); set(sheet, y, x++, "SampleId", Style.S_TH_LEFT); set(sheet, y, x++, "Biotype", Style.S_TH_LEFT); set(sheet, y, x++, "SampleName", Style.S_TH_LEFT); set(sheet, y, x++, "Metadata", Style.S_TH_LEFT); set(sheet, y, x++, "Comments", Style.S_TH_LEFT); set(sheet, y, x++, "CreatedBy", Style.S_TH_LEFT); set(sheet, y, x++, "Date", Style.S_TH_LEFT); if (showResults) { set(sheet, y, x++, "Results", Style.S_TH_LEFT); } int nCols = x - 1; Biosample previous = null; drawLineAbove(sheet, y, 0, nCols, (short) 1); for (Biosample b : allSamples) { if (!showWithoutLocation && b.getLocation() == null) continue; if (previous == null) { drawLineUnder(sheet, y, 0, nCols, (short) 1); } else if (b.getTopParent() != previous.getTopParent()) { drawLineUnder(sheet, y, 0, nCols, previous.getTopParent() == null || previous.getTopParent().getInheritedGroup() != b.getInheritedGroup() ? (short) 5 : (short) 1); } else if (b.getInheritedPhase() != previous.getInheritedPhase()) { drawLineUnder(sheet, y, 0, nCols, (short) 4); } previous = b; y++; x = 0; set(sheet, y, x++, b.getLocationString(LocationFormat.FULL_POS, user), Style.S_TD_LEFT); set(sheet, y, x++, b.getContainerType() == null ? "" : b.getContainerType().getName(), Style.S_TD_LEFT); set(sheet, y, x++, b.getContainerId(), Style.S_TD_LEFT); set(sheet, y, x++, b.getInheritedGroupString(user.getUsername()), Style.S_TD_LEFT); set(sheet, y, x++, b.getInheritedPhase() == null ? "" : b.getInheritedPhase().getShortName(), Style.S_TD_LEFT); set(sheet, y, x++, b.getTopParent().getSampleId(), b.getTopParent() == b ? Style.S_TD_BOLD_LEFT : Style.S_TD_LEFT); set(sheet, y, x++, b.getSampleId(), Style.S_TD_BOLD_LEFT); set(sheet, y, x++, b.getBiotype().getName(), Style.S_TD_LEFT); set(sheet, y, x++, b.getSampleName(), Style.S_TD_LEFT); set(sheet, y, x++, b.getMetadataAsString(), Style.S_TD_LEFT); set(sheet, y, x++, b.getComments(), Style.S_TD_LEFT); set(sheet, y, x++, b.getCreUser(), Style.S_TD_LEFT); set(sheet, y, x++, FormatterUtils.formatDate(b.getCreDate()), Style.S_TD_LEFT); if (showResults) { List<Result> results = sample2results.get(b); if (results == null || results.size() == 0) { set(sheet, y, x++, "", Style.S_TD_SMALL); } else { Collections.sort(results); StringBuilder sb = new StringBuilder(); for (Result r : results) { sb.append((sb.length() > 0 ? "\n" : "") + r.getDetailsWithoutSampleId()); } set(sheet, y, x++, sb.toString(), Style.S_TD_SMALL); } } } POIUtils.autoSizeColumns(sheet, 15000, false); if (wb.getNumberOfSheets() == 0) throw new Exception("There are no samplings to be reported"); }
From source file:com.actelion.research.spiritapp.report.StatusReport.java
License:Open Source License
@Override protected void populateWorkBook() throws Exception { //Create Header Sheet sheet = createSheet(wb, "Living Status"); sheet.setFitToPage(true); createHeadersWithTitle(sheet, study, "Living Status"); sheet.createRow(4).setHeightInPoints(23f); int col = 0;//from w w w. j av a 2s . com set(sheet, 5, col++, "Participant", Style.S_TH_CENTER); set(sheet, 5, col++, "No.", Style.S_TH_CENTER); set(sheet, 5, col++, "ContainerId", Style.S_TH_CENTER); set(sheet, 5, col++, "Group", Style.S_TH_CENTER); set(sheet, 5, col++, "St.", Style.S_TH_CENTER); set(sheet, 5, col++, "Status", Style.S_TH_CENTER); set(sheet, 5, col++, "Phase", Style.S_TH_CENTER); set(sheet, 5, col++, "Observation", Style.S_TH_CENTER); int maxCol = col - 1; //Group separator? int line = 5; String cageBefore = null; Group groupBefore = null; DAOResult.attachOrCreateStudyResultsToTops(study, study.getParticipants(), null, null); Test observationTest = DAOTest.getTest(DAOTest.OBSERVATION_TESTNAME); if (observationTest == null) throw new Exception("The test " + DAOTest.OBSERVATION_TESTNAME + " does not exist"); for (Biosample a : study.getParticipantsSorted()) { if (CompareUtils.compare(groupBefore, a.getInheritedGroup()) != 0) { drawLineUnder(sheet, line, 0, maxCol, (short) 2); } else if (CompareUtils.compare(cageBefore, a.getContainerId()) != 0) { drawLineUnder(sheet, line, 0, maxCol, (short) 1); } Pair<Status, Phase> actionStatus = a.getLastActionStatus(); Phase lastPhase = actionStatus.getSecond(); Result lastObservation = lastPhase == null ? null : a.getAuxResult(observationTest, lastPhase); Group g = a.getInheritedGroup(); line++; col = 0; set(sheet, line, col++, a.getSampleName(), Style.S_TD_CENTER); set(sheet, line, col++, a.getSampleId(), Style.S_TD_CENTER); set(sheet, line, col++, a.getContainerId(), Style.S_TD_CENTER); set(sheet, line, col++, g == null ? "" : g.getBlindedName(SpiritFrame.getUsername()), Style.S_TD_LEFT); set(sheet, line, col++, g == null || g.getNSubgroups() <= 1 ? "" : (a.getInheritedSubGroup() + 1), Style.S_TD_CENTER); set(sheet, line, col++, g == null ? "" : actionStatus.getFirst() == null ? "" : actionStatus.getFirst().getName(), Style.S_TD_LEFT); set(sheet, line, col++, g == null ? "" : lastPhase == null ? "" : lastPhase.getAbsoluteDateAndName(), Style.S_TD_LEFT); set(sheet, line, col++, g == null ? "" : lastObservation == null ? "" : lastObservation.getFirstValue(), Style.S_TD_LEFT); cageBefore = a.getContainerId(); groupBefore = a.getInheritedGroup(); } drawLineUnder(sheet, line, 0, maxCol, (short) 1); POIUtils.autoSizeColumns(sheet); if (wb.getNumberOfSheets() == 0) throw new Exception("There was no randomization fone for " + study); }
From source file:com.actelion.research.spiritapp.report.StudyGroupAssignmentReport.java
License:Open Source License
@Override protected void populateWorkBook() throws Exception { List<Biosample> topBiosamples = new ArrayList<>(study.getParticipants()); Biotype biotype = Biosample.getBiotype(topBiosamples); List<Phase> phases = new ArrayList<>(); phases.addAll(study.getPhases());//from w w w .ja va2 s .c o m phases.add(null); //Rnd Data for (Phase phase : phases) { List<AttachedBiosample> samples; int nData; if (phase == null) { //Current Status nData = 0; samples = new ArrayList<>(); int count = 0; for (Biosample biosample : topBiosamples) { AttachedBiosample sample = new AttachedBiosample(); sample.setBiosample(biosample); sample.setContainerId(biosample.getContainerId()); sample.setGroup(biosample.getInheritedGroup()); sample.setNo(++count); sample.setSampleId(biosample.getTopParent().getSampleId()); sample.setSampleName(biosample.getSampleName()); sample.setSubGroup(biosample.getInheritedSubGroup()); samples.add(sample); } } else { //Intermediate phase if (!phase.hasRandomization()) continue; //Load data Randomization rnd = phase.getRandomization(); DAOStudy.loadBiosamplesFromStudyRandomization(rnd); samples = rnd.getSamples(); if (samples.size() == 0) continue; nData = rnd.getNData(); } Collections.sort(samples, new Comparator<AttachedBiosample>() { @Override public int compare(AttachedBiosample o1, AttachedBiosample o2) { int c = CompareUtils.compare(o1.getGroup(), o2.getGroup()); if (c != 0) return c; c = CompareUtils.compare(o1.getSubGroup(), o2.getSubGroup()); if (c != 0) return c; c = CompareUtils.compare(o1.getSampleName(), o2.getSampleName()); return c; } }); //Create Header Sheet sheet = createSheet(wb, "Group Assignment - " + (phase == null ? "Final" : phase.getShortName())); sheet.setFitToPage(true); sheet.createRow(4).setHeightInPoints(23f); createHeadersWithTitle(sheet, study, "Group assignment" + (phase == null ? " - Final" : " done at " + phase.getShortName())); int col = 0; set(sheet, 5, col++, "No.", Style.S_TH_CENTER); set(sheet, 5, col++, "BW [g]", Style.S_TH_CENTER); for (int i = 0; i < nData; i++) set(sheet, 5, col++, "Data" + (i + 1), Style.S_TH_CENTER); set(sheet, 4, 0, "Before", Style.S_TH_CENTER, 1, col); set(sheet, 5, col++, "AnimalId", Style.S_TH_CENTER); set(sheet, 5, col++, "New No.", Style.S_TH_CENTER); set(sheet, 5, col++, "Cage", Style.S_TH_CENTER); set(sheet, 5, col++, "Group", Style.S_TH_CENTER); set(sheet, 5, col++, "St.", Style.S_TH_CENTER); if (biotype != null) { for (BiotypeMetadata bm : biotype.getMetadata()) { set(sheet, 5, col++, bm.getName(), Style.S_TH_CENTER); } } set(sheet, 5, col++, "Treatment", Style.S_TH_CENTER); set(sheet, 4, 2, "After", Style.S_TH_CENTER, 1, col - 2); int maxCol = col - 1; //Group separator? int line = 5; String cageBefore = null; Group groupBefore = null; for (AttachedBiosample r : samples) { if (r.getBiosample() == null || r.getBiosample().getId() <= 0) continue; if (CompareUtils.compare(groupBefore, r.getGroup()) != 0) { drawLineUnder(sheet, line, 0, maxCol, (short) 2); } else if (CompareUtils.compare(cageBefore, r.getContainerId()) != 0) { drawLineUnder(sheet, line, 0, maxCol, (short) 1); } line++; Group g = r.getGroup(); Biosample b = r.getBiosample(); col = 0; set(sheet, line, col++, r.getNo(), Style.S_TD_CENTER); set(sheet, line, col++, r.getWeight(), Style.S_TD_CENTER); for (int i = 0; i < nData; i++) set(sheet, line, col++, r.getDataList() != null && i < r.getDataList().size() ? r.getDataList().get(i) : null, Style.S_TD_CENTER); set(sheet, line, col++, r.getSampleId(), Style.S_TD_CENTER); set(sheet, line, col++, r.getSampleName(), Style.S_TD_CENTER); set(sheet, line, col++, r.getContainerId(), Style.S_TD_CENTER); set(sheet, line, col++, g == null ? "" : g.getBlindedName(SpiritFrame.getUsername()), Style.S_TD_LEFT); set(sheet, line, col++, g == null || g.getNSubgroups() <= 1 ? "" : (r.getSubGroup() + 1), Style.S_TD_CENTER); if (biotype != null) { for (BiotypeMetadata bm : biotype.getMetadata()) { set(sheet, line, col++, b.getMetadataValue(bm), Style.S_TD_CENTER); } } set(sheet, line, col++, g == null ? "" : g.getTreatmentDescription(), Style.S_TD_LEFT); cageBefore = r.getContainerId(); groupBefore = r.getGroup(); } drawLineUnder(sheet, line, 0, maxCol, (short) 1); POIUtils.autoSizeColumns(sheet, 30000, true); } if (wb.getNumberOfSheets() == 0) throw new Exception("There was no randomization fone for " + study); }
From source file:com.actelion.research.spiritapp.ui.util.POIUtils.java
License:Open Source License
@SuppressWarnings("rawtypes") public static void exportToExcel(String[][] table, ExportMode exportMode) throws IOException { Class[] types = getTypes(table); Workbook wb = new XSSFWorkbook(); Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style;//from w w w. j av a2 s . c om DataFormat df = wb.createDataFormat(); Font font = wb.createFont(); font.setFontName("Serif"); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 15); style = wb.createCellStyle(); style.setFont(font); styles.put("title", style); font = wb.createFont(); font.setFontName("Serif"); font.setFontHeightInPoints((short) 10); style = wb.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(font); style.setWrapText(true); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); styles.put("th", style); font = wb.createFont(); font.setFontName("Serif"); font.setFontHeightInPoints((short) 9); style = wb.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_LEFT); style.setFont(font); style.setWrapText(true); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); styles.put("td", style); font = wb.createFont(); font.setFontName("Serif"); font.setFontHeightInPoints((short) 9); style = wb.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_LEFT); style.setFont(font); style.setWrapText(true); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); styles.put("td-border", style); font = wb.createFont(); font.setFontName("Serif"); font.setFontHeightInPoints((short) 9); style = wb.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(font); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); styles.put("td-double", style); font = wb.createFont(); font.setFontName("Serif"); font.setFontHeightInPoints((short) 9); style = wb.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(font); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); styles.put("td-right", style); font = wb.createFont(); font.setFontName("Serif"); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setFontHeightInPoints((short) 9); style = wb.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(font); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); styles.put("td-bold", style); font = wb.createFont(); font.setFontName("Serif"); font.setFontHeightInPoints((short) 9); style = wb.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setFont(font); style.setDataFormat(df.getFormat("d.mm.yyyy h:MM")); style.setVerticalAlignment(CellStyle.VERTICAL_TOP); styles.put("td-date", style); Sheet sheet = wb.createSheet(); sheet.setFitToPage(true); Cell cell; int maxRows = 0; for (int r = 0; r < table.length; r++) { Row row = sheet.createRow(r); if (r == 0) { row.setRowStyle(styles.get("th")); } int rows = 1; for (int c = 0; c < table[r].length; c++) { cell = row.createCell(c); String s = table[r][c]; if (s == null) continue; rows = Math.max(rows, s.split("\n").length); try { if (exportMode == ExportMode.HEADERS_TOP && r == 0) { cell.setCellStyle(styles.get("th")); cell.setCellValue(s); } else if (exportMode == ExportMode.HEADERS_TOPLEFT && (r == 0 || c == 0)) { if (r == 0 && c == 0) { cell.setCellStyle(styles.get("td")); } else { cell.setCellStyle(styles.get("th")); } cell.setCellValue(s); } else if (types[c] == Double.class) { cell.setCellStyle(styles.get("td-double")); cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(Double.parseDouble(s)); } else if (types[c] == String.class) { cell.setCellStyle( styles.get(exportMode == ExportMode.HEADERS_TOPLEFT ? "td-border" : "td")); cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(s); } else { cell.setCellStyle(styles.get("td-right")); cell.setCellValue(s); } } catch (Exception e) { cell.setCellStyle(styles.get("td")); cell.setCellValue(s); } } maxRows = Math.max(maxRows, rows); row.setHeightInPoints(rows * 16f); } // Add footer notes if (footerData.size() > 0) { Row row = sheet.createRow(table.length); row.setHeightInPoints((footerData.size() * sheet.getDefaultRowHeightInPoints())); cell = row.createCell(0); sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), //first row (0-based) row.getRowNum(), //last row (0-based) 0, //first column (0-based) table[0].length - 1 //last column (0-based) )); //for ( String data : footerData ) { style = wb.createCellStyle(); style.setWrapText(true); cell.setCellStyle(style); cell.setCellValue(MiscUtils.flatten(footerData, "\n")); //} } footerData.clear(); autoSizeColumns(sheet); if (table.length > 0) { for (int c = 0; c < table[0].length; c++) { if (sheet.getColumnWidth(c) > 10000) sheet.setColumnWidth(c, 3000); } } if (exportMode == ExportMode.HEADERS_TOPLEFT) { for (int r = 1; r < table.length; r++) { sheet.getRow(r).setHeightInPoints(maxRows * 16f); } } File reportFile = IOUtils.createTempFile("export_", ".xlsx"); FileOutputStream out = new FileOutputStream(reportFile); wb.write(out); wb.close(); out.close(); Desktop.getDesktop().open(reportFile); }
From source file:com.b510.excel.client.BusinessPlan.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;/* ww w. j a va 2 s .c o m*/ if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Business Plan"); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); //the following three statements are required only for HSSF sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); //the header row: centered text in 48pt font Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(12.75f); for (int i = 0; i < titles.length; i++) { Cell cell = headerRow.createCell(i); cell.setCellValue(titles[i]); cell.setCellStyle(styles.get("header")); } //columns for 11 weeks starting from 9-Jul Calendar calendar = Calendar.getInstance(); int year = calendar.get(Calendar.YEAR); calendar.setTime(fmt.parse("9-Jul")); calendar.set(Calendar.YEAR, year); for (int i = 0; i < 11; i++) { Cell cell = headerRow.createCell(titles.length + i); cell.setCellValue(calendar); cell.setCellStyle(styles.get("header_date")); calendar.roll(Calendar.WEEK_OF_YEAR, true); } //freeze the first row sheet.createFreezePane(0, 1); Row row; Cell cell; int rownum = 1; for (int i = 0; i < data.length; i++, rownum++) { row = sheet.createRow(rownum); if (data[i] == null) continue; for (int j = 0; j < data[i].length; j++) { cell = row.createCell(j); String styleName; boolean isHeader = i == 0 || data[i - 1] == null; switch (j) { case 0: if (isHeader) { styleName = "cell_b"; cell.setCellValue(Double.parseDouble(data[i][j])); } else { styleName = "cell_normal"; cell.setCellValue(data[i][j]); } break; case 1: if (isHeader) { styleName = i == 0 ? "cell_h" : "cell_bb"; } else { styleName = "cell_indented"; } cell.setCellValue(data[i][j]); break; case 2: styleName = isHeader ? "cell_b" : "cell_normal"; cell.setCellValue(data[i][j]); break; case 3: styleName = isHeader ? "cell_b_centered" : "cell_normal_centered"; cell.setCellValue(Integer.parseInt(data[i][j])); break; case 4: { calendar.setTime(fmt.parse(data[i][j])); calendar.set(Calendar.YEAR, year); cell.setCellValue(calendar); styleName = isHeader ? "cell_b_date" : "cell_normal_date"; break; } case 5: { int r = rownum + 1; String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")"; cell.setCellFormula(fmla); styleName = isHeader ? "cell_bg" : "cell_g"; break; } default: styleName = data[i][j] != null ? "cell_blue" : "cell_normal"; } cell.setCellStyle(styles.get(styleName)); } } //group rows for each phase, row numbers are 0-based sheet.groupRow(4, 6); sheet.groupRow(9, 13); sheet.groupRow(16, 18); //set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(0, 256 * 6); sheet.setColumnWidth(1, 256 * 33); sheet.setColumnWidth(2, 256 * 20); sheet.setZoom(3, 4); // Write the output to a file String file = "businessplan.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:com.b510.excel.client.CalendarDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Calendar calendar = Calendar.getInstance(); boolean xlsx = true; for (int i = 0; i < args.length; i++) { if (args[i].charAt(0) == '-') { xlsx = args[i].equals("-xlsx"); } else {/*from w w w .jav a2s . com*/ calendar.set(Calendar.YEAR, Integer.parseInt(args[i])); } } int year = calendar.get(Calendar.YEAR); Workbook wb = xlsx ? new XSSFWorkbook() : new HSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); for (int month = 0; month < 12; month++) { calendar.set(Calendar.MONTH, month); calendar.set(Calendar.DAY_OF_MONTH, 1); //create a sheet for each month Sheet sheet = wb.createSheet(months[month]); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); //the following three statements are required only for HSSF sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); //the header row: centered text in 48pt font Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(80); Cell titleCell = headerRow.createCell(0); titleCell.setCellValue(months[month] + " " + year); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1")); //header with month titles Row monthRow = sheet.createRow(1); for (int i = 0; i < days.length; i++) { //set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(i * 2, 5 * 256); //the column is 5 characters wide sheet.setColumnWidth(i * 2 + 1, 13 * 256); //the column is 13 characters wide sheet.addMergedRegion(new CellRangeAddress(1, 1, i * 2, i * 2 + 1)); Cell monthCell = monthRow.createCell(i * 2); monthCell.setCellValue(days[i]); monthCell.setCellStyle(styles.get("month")); } int cnt = 1, day = 1; int rownum = 2; for (int j = 0; j < 6; j++) { Row row = sheet.createRow(rownum++); row.setHeightInPoints(100); for (int i = 0; i < days.length; i++) { Cell dayCell_1 = row.createCell(i * 2); Cell dayCell_2 = row.createCell(i * 2 + 1); int day_of_week = calendar.get(Calendar.DAY_OF_WEEK); if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) { dayCell_1.setCellValue(day); calendar.set(Calendar.DAY_OF_MONTH, ++day); if (i == 0 || i == days.length - 1) { dayCell_1.setCellStyle(styles.get("weekend_left")); dayCell_2.setCellStyle(styles.get("weekend_right")); } else { dayCell_1.setCellStyle(styles.get("workday_left")); dayCell_2.setCellStyle(styles.get("workday_right")); } } else { dayCell_1.setCellStyle(styles.get("grey_left")); dayCell_2.setCellStyle(styles.get("grey_right")); } cnt++; } if (calendar.get(Calendar.MONTH) > month) break; } } // Write the output to a file String file = "calendar.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:com.b510.excel.client.LoanCalculator.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;//from w ww . j a va 2s . c o m if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Loan Calculator"); sheet.setPrintGridlines(false); sheet.setDisplayGridlines(false); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); sheet.setColumnWidth(0, 3 * 256); sheet.setColumnWidth(1, 3 * 256); sheet.setColumnWidth(2, 11 * 256); sheet.setColumnWidth(3, 14 * 256); sheet.setColumnWidth(4, 14 * 256); sheet.setColumnWidth(5, 14 * 256); sheet.setColumnWidth(6, 14 * 256); createNames(wb); Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(35); for (int i = 1; i <= 7; i++) { titleRow.createCell(i).setCellStyle(styles.get("title")); } Cell titleCell = titleRow.getCell(2); titleCell.setCellValue("Simple Loan Calculator"); sheet.addMergedRegion(CellRangeAddress.valueOf("$C$1:$H$1")); Row row = sheet.createRow(2); Cell cell = row.createCell(4); cell.setCellValue("Enter values"); cell.setCellStyle(styles.get("item_right")); row = sheet.createRow(3); cell = row.createCell(2); cell.setCellValue("Loan amount"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_$")); cell.setAsActiveCell(); row = sheet.createRow(4); cell = row.createCell(2); cell.setCellValue("Annual interest rate"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_%")); row = sheet.createRow(5); cell = row.createCell(2); cell.setCellValue("Loan period in years"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_i")); row = sheet.createRow(6); cell = row.createCell(2); cell.setCellValue("Start date of loan"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_d")); row = sheet.createRow(8); cell = row.createCell(2); cell.setCellValue("Monthly payment"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Monthly_Payment,\"\")"); cell.setCellStyle(styles.get("formula_$")); row = sheet.createRow(9); cell = row.createCell(2); cell.setCellValue("Number of payments"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Loan_Years*12,\"\")"); cell.setCellStyle(styles.get("formula_i")); row = sheet.createRow(10); cell = row.createCell(2); cell.setCellValue("Total interest"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Total_Cost-Loan_Amount,\"\")"); cell.setCellStyle(styles.get("formula_$")); row = sheet.createRow(11); cell = row.createCell(2); cell.setCellValue("Total cost of loan"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Monthly_Payment*Number_of_Payments,\"\")"); cell.setCellStyle(styles.get("formula_$")); // Write the output to a file String file = "loan-calculator.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }