List of usage examples for org.apache.poi.ss.usermodel Sheet createRow
Row createRow(int rownum);
From source file:com.actelion.research.spiritapp.report.AbstractReport.java
License:Open Source License
protected Cell setFormula(Sheet sheet, int row, int col, String text, Style style) { Row r = sheet.getRow(row);// ww w.j av a 2 s .co m if (r == null) r = sheet.createRow(row); Cell c = r.getCell(col); if (c == null) c = r.createCell(col); c.setCellStyle(styles.get(style)); c.setCellType(Cell.CELL_TYPE_STRING); try { c.setCellFormula(text); } catch (Exception e) { e.printStackTrace(); c.setCellValue("Err. " + e.getMessage()); } return c; }
From source file:com.actelion.research.spiritapp.report.AbstractReport.java
License:Open Source License
protected void drawLineUnder(Sheet sheet, int row, int colMin, int colMax, short thickness) { Row r = sheet.getRow(row);/* w w w .j a v a 2s .c om*/ if (r == null) r = sheet.createRow(row); for (int col = colMin; col <= colMax; col++) { Cell c = r.getCell(col); if (c == null) c = r.createCell(col); CellStyle style = styleWithBordersUnder.get((c.getCellStyle().getIndex() << 4) + thickness); if (style == null) { style = sheet.getWorkbook().createCellStyle(); style.cloneStyleFrom(c.getCellStyle()); style.setBorderBottom(thickness); styleWithBordersUnder.put((c.getCellStyle().getIndex() << 4) + thickness, style); } c.setCellStyle(style); } }
From source file:com.actelion.research.spiritapp.report.AbstractReport.java
License:Open Source License
protected void drawLineAbove(Sheet sheet, int row, int colMin, int colMax, short thickness) { Row r = sheet.getRow(row);/* w ww . ja v a 2s . com*/ if (r == null) r = sheet.createRow(row); for (int col = colMin; col <= colMax; col++) { Cell c = r.getCell(col); if (c == null) c = r.createCell(col); CellStyle style = styleWithBordersAbove.get(c.getCellStyle().getIndex() << 4 + thickness); if (style == null) { style = sheet.getWorkbook().createCellStyle(); style.cloneStyleFrom(c.getCellStyle()); style.setBorderTop(thickness); styleWithBordersAbove.put(c.getCellStyle().getIndex() << 4 + thickness, style); } c.setCellStyle(style); } }
From source file:com.actelion.research.spiritapp.report.AbstractReport.java
License:Open Source License
protected void createHeadersWithTitle(Sheet sheet, Study study, String title) { sheet.createRow(0).setHeightInPoints(22f); set(sheet, 0, 0, study.getStudyId() + (study.getLocalId() != null ? " / " + study.getLocalId() : "") + (title == null ? "" : ": " + title), Style.S_TITLE14BLUE); }
From source file:com.actelion.research.spiritapp.report.AbstractReport.java
License:Open Source License
protected void createHeadersWithTitleSubtitle(Sheet sheet, Study study, String title, String subtitle) { sheet.createRow(0).setHeightInPoints(22f); set(sheet, 0, 0, study.getStudyId() + (study.getLocalId() != null ? " / " + study.getLocalId() : "") + (title == null ? "" : ": " + title), Style.S_TITLE14); sheet.createRow(1).setHeightInPoints(20f); set(sheet, 1, 0, subtitle, Style.S_TITLE14BLUE); // sheet.createRow(0).setHeightInPoints(22f); // sheet.createRow(1).setHeightInPoints(21f); // sheet.createRow(2).setHeightInPoints(21f); // set(sheet, 0, 0, study.getStudyId(), Style.S_TITLE14); // set(sheet, 1, 0, (study.getLocalId()!=null? " (" + study.getLocalId() + ")":""), Style.S_TITLE12); // set(sheet, 2, 0, FormatterUtils.formatDateTimeShort(new Date()), Style.S_TITLE12); // set(sheet, 0, 2, title, Style.S_TITLE14); // set(sheet, 1, 2, subtitle, Style.S_TITLE14BLUE); }
From source file:com.actelion.research.spiritapp.report.FoodWaterReport.java
License:Open Source License
protected void createWorkBookNew(Workbook wb) throws Exception { if (study.getPhases().size() == 0) throw new Exception("You cannot generate a FoodWater report if you don't have phases in your study"); Row row;/*from ww w . ja va 2s . c o m*/ DAOResult.attachOrCreateStudyResultsToTops(study, study.getParticipantsSorted(), null, null); List<FoodWater> fws = DAOFoodWater.getFoodWater(study, null); List<Phase> phases = FoodWater.getPhases(fws); Map<Biosample, List<Biosample>> mapHistory = DAORevision.getHistories(study.getParticipantsSorted()); boolean hasCageChanges = false; for (Biosample top : study.getParticipantsSorted()) { Set<String> containerIds = new HashSet<>(); for (Phase phase : phases) { String containerId = getContainerAt(top, phase, fws, mapHistory.get(top)); if (containerId != null && containerId.length() > 0) containerIds.add(containerId); } if (containerIds.size() > 1) { hasCageChanges = true; break; } } boolean displayCageEachPhase = study.getFirstDate() != null && study.getPhaseFormat() == PhaseFormat.DAY_MINUTES && hasCageChanges; //Loop through Food (i=0) and water (i==1) reports for (int i = 0; i < 2; i++) { //Check if we have some data boolean hasData = false; for (FoodWater fw : fws) { if (i == 0 && (fw.getFoodTare() != null || fw.getFoodWeight() != null)) hasData = true; else if (i == 1 && (fw.getWaterTare() != null || fw.getWaterWeight() != null)) hasData = true; } if (!hasData) continue; Sheet sheet = createSheet(wb, i == 0 ? "Food" : "Water"); createHeadersWithTitle(sheet, study, i == 0 ? "Food Consumption [g/animal/day]" : "Water Consumption [ml/animal/day]"); //Create Table Header row = sheet.createRow(5); row.setHeightInPoints(21f); row = sheet.createRow(6); row.setHeightInPoints(21f); int x = 0; set(sheet, 6, x++, "Group", Style.S_TH_CENTER); set(sheet, 6, x++, "Id", Style.S_TH_CENTER); set(sheet, 6, x++, "No", Style.S_TH_CENTER); if (!displayCageEachPhase) { set(sheet, 6, x++, "Container", Style.S_TH_CENTER); } Phase previousPhase = null; for (Phase phase : phases) { if (previousPhase == null) { set(sheet, 5, x, phase.getShortName() + (phase.getAbsoluteDate() != null ? "[" + FormatterUtils.formatDate(phase.getAbsoluteDate()) + "]" : ""), Style.S_TH_CENTER, 1, 1); if (displayCageEachPhase) set(sheet, 6, x++, "Container", Style.S_TH_CENTER); if (!displayCageEachPhase) set(sheet, 6, x++, "newTare", Style.S_TH_CENTER); } else { set(sheet, 5, x, " -> " + phase.getShortName() + (phase.getAbsoluteDate() != null ? " [" + FormatterUtils.formatDate(phase.getAbsoluteDate()) + "]" : ""), Style.S_TH_CENTER, 1, 2 + (displayCageEachPhase ? 4 : 2)); if (displayCageEachPhase) set(sheet, 6, x++, "ContainerId", Style.S_TH_CENTER); if (displayCageEachPhase) set(sheet, 6, x++, "Tare", Style.S_TH_CENTER); if (displayCageEachPhase) set(sheet, 6, x++, "Weight", Style.S_TH_CENTER); if (displayCageEachPhase) set(sheet, 6, x++, "days", Style.S_TH_CENTER); if (!displayCageEachPhase) set(sheet, 6, x++, "oldTare", Style.S_TH_CENTER); if (!displayCageEachPhase) set(sheet, 6, x++, "newTare", Style.S_TH_CENTER); set(sheet, 6, x++, "n", Style.S_TH_CENTER); set(sheet, 6, x++, "Cons.", Style.S_TH_CENTER); } previousPhase = phase; } int maxX = x - 1; // //Create table data int y = 7; Group previousGroup = null; ListHashMap<Group, Integer> group2Lines = new ListHashMap<>(); for (Biosample b : study.getParticipantsSorted()) { Group gr = b.getInheritedGroup(); group2Lines.add(gr, y); x = 0; set(sheet, y, x++, b.getInheritedGroupString(SpiritFrame.getUsername()), Style.S_TD_BOLD_LEFT); set(sheet, y, x++, b.getSampleId(), Style.S_TD_CENTER); set(sheet, y, x++, b.getSampleName(), Style.S_TD_CENTER); String containerId = null; if (!displayCageEachPhase) { //Display cage at time of death containerId = b.getContainerId(); if (containerId == null || containerId.length() == 0) { List<Biosample> history = mapHistory.get(b); for (Biosample h : history) { if (h.getContainerId() != null && h.getContainerId().length() > 0) { containerId = h.getContainerId(); break; } } } set(sheet, y, x++, containerId, Style.S_TD_BOLD_CENTER); } //data: Loop through phases previousPhase = null; for (Phase phase : phases) { if (displayCageEachPhase) { //Find the containerId of this sample at this date: containerId = getContainerAt(b, phase, fws, mapHistory.get(b)); } if (containerId == null || containerId.length() == 0) containerId = "??"; FoodWater fw = FoodWater.extract(fws, containerId, phase); FoodWater previousFW = fw == null ? null : fw.getPreviousFromList(fws, i == 1); Result r = b.getAuxResult(DAOTest.getTest(DAOTest.FOODWATER_TESTNAME), phase); ResultValue val = r == null || r.getOutputResultValues().size() < 2 ? null : i == 0 ? r.getOutputResultValues().get(0) : r.getOutputResultValues().get(1); String value = val == null ? null : val.getValue(); if (previousPhase == null) { if (displayCageEachPhase) set(sheet, y, x++, containerId, Style.S_TD_BOLD_CENTER); if (!displayCageEachPhase) set(sheet, y, x++, fw == null ? null : i == 0 ? fw.getFoodTare() : fw.getWaterTare(), Style.S_TD_DOUBLE1); } else { if (displayCageEachPhase) set(sheet, y, x++, containerId, Style.S_TD_BOLD_CENTER); if (displayCageEachPhase) set(sheet, y, x++, previousFW == null ? null : i == 0 ? previousFW.getFoodTare() : previousFW.getWaterTare(), Style.S_TD_DOUBLE1); set(sheet, y, x++, fw == null ? null : i == 0 ? fw.getFoodWeight() : fw.getWaterWeight(), Style.S_TD_DOUBLE1); if (displayCageEachPhase) set(sheet, y, x++, previousFW == null ? null : fw.getPhase().getDays() - previousFW.getPhase().getDays(), Style.S_TD_DOUBLE0); if (!displayCageEachPhase) set(sheet, y, x++, fw == null ? null : i == 0 ? fw.getFoodTare() : fw.getWaterTare(), Style.S_TD_DOUBLE1); set(sheet, y, x++, fw == null ? null : fw.getNAnimals(), Style.S_TD_DOUBLE0); set(sheet, y, x++, value, Style.S_TD_DOUBLE1_BLUE); } previousPhase = phase; } if (previousGroup != null && !previousGroup.equals(gr)) { drawLineAbove(sheet, y, 0, maxX, (short) 1); } previousGroup = gr; y++; } // y++; ////////////////////// AVERAGES x = 0; set(sheet, y + 1, x++, "Averages", Style.S_TH_CENTER); set(sheet, y + 1, x++, "", Style.S_TH_CENTER); set(sheet, y + 1, x++, "", Style.S_TH_CENTER); if (!displayCageEachPhase) set(sheet, y + 1, x++, "", Style.S_TH_CENTER); previousPhase = null; for (Phase phase : phases) { if (previousPhase == null) { set(sheet, y, x, phase.getShortName() + (phase.getAbsoluteDate() != null ? " [" + FormatterUtils.formatDate(phase.getAbsoluteDate()) + "]" : ""), Style.S_TH_CENTER); set(sheet, y + 1, x++, "", Style.S_TH_CENTER); } else { set(sheet, y, x, " -> " + phase.getShortName() + (phase.getAbsoluteDate() != null ? " [" + FormatterUtils.formatDate(phase.getAbsoluteDate()) + "]" : ""), Style.S_TH_CENTER, 1, 2 + (displayCageEachPhase ? 4 : 2)); for (int k = 0; k < (displayCageEachPhase ? 4 : 2); k++) { set(sheet, y + 1, x++, "", Style.S_TH_CENTER); } set(sheet, y + 1, x++, "", Style.S_TH_CENTER); set(sheet, y + 1, x++, "Cons.", Style.S_TH_CENTER); } previousPhase = phase; } y += 2; for (Group gr : study.getGroups()) { if (study.getParticipants(gr).size() == 0) continue; x = 0; set(sheet, y, x++, gr == null ? "N/A" : gr.getBlindedName(SpiritFrame.getUsername()), Style.S_TD_BOLD_LEFT); set(sheet, y, x++, "", Style.S_TD_LEFT); set(sheet, y, x++, "", Style.S_TD_LEFT); if (!displayCageEachPhase) set(sheet, y, x++, "", Style.S_TD_BOLD_LEFT); //data: Loop through phases previousPhase = null; for (Phase phase : phases) { List<Integer> lines = group2Lines.get(gr); if (previousPhase == null) { set(sheet, y, x, "", Style.S_TD_DOUBLE1); x += 1; } else { for (int k = 0; k < (displayCageEachPhase ? 4 : 2); k++) { set(sheet, y, x++, "", Style.S_TD_CENTER); } if (lines != null && lines.size() > 0) { int valcol = x + 1; set(sheet, y, x++, "", Style.S_TD_CENTER); setFormula(sheet, y, x++, "IF(COUNT(" + convertLinesToCells(lines, valcol) + ")>0, AVERAGE(" + convertLinesToCells(lines, valcol) + "), \"\")", Style.S_TD_DOUBLE1_BLUE); } else { set(sheet, y, x++, "", Style.S_TD_CENTER); set(sheet, y, x++, "", Style.S_TD_CENTER); } } previousPhase = phase; } y++; } POIUtils.autoSizeColumns(sheet); } }
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);/*from w ww. java 2s . com*/ createHeadersWithTitle(sheet, study, "Living Status"); sheet.createRow(4).setHeightInPoints(23f); int col = 0; 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 ww w . ja v a 2 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;// w w w. jav a 2s .c o m 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.admin.poi.ExcelUtils.java
License:Apache License
/** * excel//www . j av a2s .co m * * @param excelSheet sheet ? * @param workbook * @param outputStream ? */ private static void export(ExcelSheet excelSheet, Workbook workbook, OutputStream outputStream) throws IOException, InvocationTargetException, IllegalAccessException { Sheet sheet; sheet = workbook.createSheet(); workbook.setSheetOrder(sheet.getSheetName(), excelSheet.getSheetIndex()); // write head writeHead(excelSheet, sheet); // sheet int writeRowIndex = excelSheet.getStartRowIndex(); if (excelSheet.getDataList() != null && !excelSheet.getDataList().isEmpty()) { for (Object rowData : excelSheet.getDataList()) { // proc row Row row = Optional.ofNullable(sheet.getRow(writeRowIndex)).orElse(sheet.createRow(writeRowIndex)); writeRow(excelSheet, row, rowData); writeRowIndex++; } } workbook.write(outputStream); }