List of usage examples for org.apache.poi.ss.usermodel Sheet setColumnWidth
void setColumnWidth(int columnIndex, int width);
The maximum column width for an individual cell is 255 characters.
From source file:qmul.align.AlignmentTester.java
License:Open Source License
/** * Process a single dialogue/*from www . j a v a2 s . co m*/ * * @param d * the dialogue to process * @param wb * the XLS workbook to write to, or null not to bother * @return a list of {@link Double} scores, one per {@link DialogueWindower} step (e.g. dialogue turn) */ public List<Double> processDialogue(Dialogue d, Workbook wb, HashMap<String, ArrayList<Double>> speakerScores, HashMap<String, String> originalSpks, HashMap<String, ArrayList<Double>> speakerN, MetricsMap spkMetrics, MetricsMap totMetrics, Workbook wbcounts, HashMap<String, HashMap<Object, Integer>> allCounts, HashMap<String, HashMap<Object, Integer>> commonCounts, HashMap<Object, Integer> diaAllCounts, HashMap<Object, Integer> diaCommonCounts) { CreationHelper creationHelper = wb.getCreationHelper(); win.setDialogue(d); sim.reset(); ArrayList<DialogueSpeaker> spks = new ArrayList<DialogueSpeaker>(d.getSpeakers()); Collections.sort(spks); Sheet sheet = (wb == null ? null : wb.createSheet(d.getId().replaceAll(":", "-"))); Sheet sheetcounts = (wbcounts == null ? null : wbcounts.createSheet(d.getId().replaceAll(":", "-"))); int iRow = 0; if (sheet != null) { iRow = writeSheetHeader(creationHelper, sheet, iRow, d, spks); } int iCRow = 0; if (sheetcounts != null) { iCRow = writeSheetHeader(creationHelper, sheet, iCRow, d, spks); } ArrayList<Double> scores = new ArrayList<Double>(); HashSet<X> counted = new HashSet<X>(); do { List<X> left = win.getLeftWindow(); Collections.reverse(left); // windowers return things in dialogue order: we'll look progressively backwards List<X> right = win.getRightWindow(); // System.out.println("lengthS " + left.size() + " " + right.size()); double score = 0.0; double n = 0.0; for (X r : right) { String spkKey = makeSpkKey(r.getSpeaker(), d); String originalSpkKey = ""; if (r.getOriginalSpeaker() != null) { originalSpkKey = r.getOriginalSpeaker().getId(); // fix for the fact that BNC speakers are not currently given SUBdialogue ID in their ID - TODO // change that? Dialogue od = r.getOriginalDialogue(); if ((od == null) && (r instanceof DialogueSentence)) { od = ((DialogueSentence) r).getTurn().getOriginalDialogue(); } String originalDia; if (od != null) { originalDia = od.getId(); } else { originalDia = d.getId().replaceFirst("-\\d+$", ""); } if (!originalSpkKey.contains(originalDia)) { if (!originalDia.contains(":")) { throw new RuntimeException("can't find super-dialogue, no : in " + originalDia); } String originalSuperDia = originalDia.substring(0, originalDia.lastIndexOf(":")); if (originalSpkKey.contains(originalSuperDia)) { originalSpkKey = originalSpkKey.replace(originalSuperDia, originalDia); } else { throw new RuntimeException("spk key without super-dialogue " + spkKey + ", " + originalSpkKey + ", " + originalDia); } } } Row row = (wb == null ? null : sheet.createRow(iRow++)); int iCol = 0; Cell cell = (wb == null ? null : row.createCell(iCol++, Cell.CELL_TYPE_STRING)); if (cell != null) { cell.setCellValue(creationHelper.createRichTextString(r.getSpeaker().getId())); cell = row.createCell(iCol++, Cell.CELL_TYPE_STRING); cell.setCellValue(creationHelper.createRichTextString(originalSpkKey)); // cell = row.createCell(iCol++, Cell.CELL_TYPE_STRING); // cell.setCellValue(creationHelper.createRichTextString(r.getId())); cell = row.createCell(iCol++, Cell.CELL_TYPE_STRING); cell.setCellValue(creationHelper.createRichTextString(r.toString())); row.setHeightInPoints(12); sheet.setColumnWidth(iCol - 1, 2560); } if (!speakerScores.containsKey(spkKey)) { speakerScores.put(spkKey, new ArrayList<Double>()); speakerN.put(spkKey, new ArrayList<Double>()); originalSpks.put(spkKey, originalSpkKey); for (int i = 0; i < win.getLeftWindowSize(); i++) { speakerScores.get(spkKey).add(0.0); speakerN.get(spkKey).add(0.0); } Boolean isTurns = null; if (left.size() > 0) { isTurns = (left.get(0) instanceof DialogueTurn); } else if (right.size() > 0) { isTurns = (right.get(0) instanceof DialogueTurn); } spkMetrics.setNumUnits(spkKey, 0); totMetrics.setNumUnits(d.getId(), (isTurns ? d.numTurns() : d.numSents())); spkMetrics.setNumWords(spkKey, 0); totMetrics.setNumWords(d.getId(), d.numWords()); spkMetrics.setNumTokens(spkKey, 0); totMetrics.setNumTokens(d.getId(), d.numTokens()); } int iLeft = 0; double offset = Double.NaN; boolean gotOffset = false; for (X l : left) { double s = sim.similarity(l, r); // System.out.println("Siml = " + s + " for l:" + l.getId() + " r:" + r.getId()); if ((l.getOriginalId() != null) && (r.getOriginalId() != null) && l.getOriginalId().equals(r.getOriginalId())) { System.out.println("Equal IDs sim = " + s + " for l:" + l.getId() + " " + l.getOriginalId() + " r:" + r.getId() + " " + r.getOriginalId() + " d " + d.getId() + " nturns " + d.numTurns()); } if (wbcounts != null) { if (!counted.contains(l)) { MapUtil.addAll(diaAllCounts, sim.rawCountsA()); MapUtil.addAll(allCounts.get(""), sim.rawCountsA()); MapUtil.addAll(allCounts.get(d.getGenre()), sim.rawCountsA()); counted.add(l); } if (!counted.contains(r)) { MapUtil.addAll(diaAllCounts, sim.rawCountsB()); MapUtil.addAll(allCounts.get(""), sim.rawCountsB()); MapUtil.addAll(allCounts.get(d.getGenre()), sim.rawCountsB()); counted.add(r); } MapUtil.addAll(diaCommonCounts, sim.rawCountsAB()); MapUtil.addAll(commonCounts.get(""), sim.rawCountsAB()); MapUtil.addAll(commonCounts.get(d.getGenre()), sim.rawCountsAB()); } cell = (wb == null ? null : row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC)); if (cell != null) { cell.setCellValue(s); } score += s; n++; speakerScores.get(spkKey).set(iLeft, speakerScores.get(spkKey).get(iLeft) + s); speakerN.get(spkKey).set(iLeft, speakerN.get(spkKey).get(iLeft) + 1); if (!win.getClass().toString().contains("AllOther")) { // for "all other" windowers, actually // average over "window" iLeft++; } if (!gotOffset) { offset = r.getStartTime() - l.getEndTime(); gotOffset = true; // if (!Double.isNaN(offset)) { // System.out.println("Offset = " + offset + " for l:" + l.getId() + " r:" + r.getId()); // } } } // print number sents/words/tokens iCol += (win.getLeftWindowSize() - left.size() + 1); if (wb != null) { // if we are writing to a workbook cell = (wb == null ? null : row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC)); cell.setCellValue(r instanceof DialogueTurn ? ((DialogueTurn) r).getSents().size() : 1); cell = (wb == null ? null : row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC)); cell.setCellValue(r.numWords()); cell = (wb == null ? null : row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC)); cell.setCellValue(r.numTokens()); } iCol += 1; if (!Double.isNaN(offset)) { cell = (wb == null ? null : row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC)); cell.setCellValue(offset); } else { iCol++; } double wordRate = (double) (r.getEndTime() - r.getStartTime()) / (double) r.numWords(); if (r.numWords() == 0) { wordRate = Double.NaN; // on some OSs this doesn't happen in the calc above } if (!Double.isNaN(wordRate)) { cell = (wb == null ? null : row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC)); cell.setCellValue(wordRate); } else { iCol++; } // make sure we counted this one - the first one can get missed if leftWindow empty if ((wbcounts != null) && !counted.contains(r)) { sim.similarity(r, r); MapUtil.addAll(diaAllCounts, sim.rawCountsA()); MapUtil.addAll(allCounts.get(""), sim.rawCountsA()); MapUtil.addAll(allCounts.get(d.getGenre()), sim.rawCountsA()); counted.add(r); } spkMetrics.setNumUnits(spkKey, spkMetrics.getNumUnits(spkKey) + 1); spkMetrics.setNumWords(spkKey, spkMetrics.getNumWords(spkKey) + r.numWords()); spkMetrics.setNumTokens(spkKey, spkMetrics.getNumTokens(spkKey) + r.numTokens()); if (!Double.isNaN(offset)) { spkMetrics.setTurnOffset(spkKey, spkMetrics.getTurnOffset(spkKey) + offset); spkMetrics.setNumTurnOffsets(spkKey, spkMetrics.getNumTurnOffsets(spkKey) + 1); totMetrics.setTurnOffset(d.getId(), totMetrics.getTurnOffset(d.getId()) + offset); totMetrics.setNumTurnOffsets(d.getId(), totMetrics.getNumTurnOffsets(d.getId()) + 1); } if (!Double.isNaN(wordRate)) { spkMetrics.setWordRate(spkKey, spkMetrics.getWordRate(spkKey) + wordRate); spkMetrics.setNumWordRates(spkKey, spkMetrics.getNumWordRates(spkKey) + 1); totMetrics.setWordRate(d.getId(), totMetrics.getWordRate(d.getId()) + wordRate); totMetrics.setNumWordRates(d.getId(), totMetrics.getNumWordRates(d.getId()) + 1); } } scores.add((n == 0.0) ? 0.0 : (score / n)); } while (win.advance()); if (wb != null) { iRow++; for (DialogueSpeaker spk : spks) { String spkKey = makeSpkKey(spk, d); Row row = sheet.createRow(iRow++); int iCol = 0; row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(spk.getId())); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(originalSpks.get(spkKey))); row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString("Mean")); for (int i = 0; i < win.getLeftWindowSize(); i++) { if (speakerN.get(spkKey).get(i) > 0) { row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC) .setCellValue(speakerScores.get(spkKey).get(i) / speakerN.get(spkKey).get(i)); } else { iCol++; } // System.out // .println("score " + i + " for speaker " + spkKey + "=" + speakerScores.get(spkKey).get(i)); // System.out.println("N " + i + " for speaker " + spkKey + "=" + speakerN.get(spkKey).get(i)); // System.out.println("mean " + i + " for speaker " + spkKey + "=" // + (speakerScores.get(spkKey).get(i) / speakerN.get(spkKey).get(i))); } iCol++; row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue( (double) spkMetrics.getNumUnits(spkKey) / (double) spkMetrics.getNumUnits(spkKey)); row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue( (double) spkMetrics.getNumWords(spkKey) / (double) spkMetrics.getNumUnits(spkKey)); row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue( (double) spkMetrics.getNumTokens(spkKey) / (double) spkMetrics.getNumUnits(spkKey)); iCol++; row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue( (double) spkMetrics.getTurnOffset(spkKey) / (double) spkMetrics.getNumTurnOffsets(spkKey)); row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC).setCellValue( (double) spkMetrics.getWordRate(spkKey) / (double) spkMetrics.getNumWordRates(spkKey)); } } if (wbcounts != null) { iCRow++; ArrayList<Object> keys = new ArrayList<Object>(diaAllCounts.keySet()); Collections.sort(keys, new DescendingComparator<Object>(diaAllCounts)); for (Object key : keys) { Row row = sheetcounts.createRow(iCRow++); int iCol = 0; row.createCell(iCol++, Cell.CELL_TYPE_STRING) .setCellValue(creationHelper.createRichTextString(key.toString())); Cell cell = row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC); if (diaAllCounts.get(key) != null) { cell.setCellValue(diaAllCounts.get(key)); } cell = row.createCell(iCol++, Cell.CELL_TYPE_NUMERIC); if (diaCommonCounts.get(key) != null) { cell.setCellValue(diaCommonCounts.get(key)); } } } return scores; }
From source file:reports.notReached.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException, InvalidFormatException { session = request.getSession();//from ww w . j ava 2 s .c o m dbConn conn = new dbConn(); position = 1; String reportHeader[] = ("COUNTY NAME ,PARTNER NAME,DISTRICT NAME, DIC NAME, GROUP NAME,CLIENT FULL NAME ," + " CCC NO. , MOBILE NUMBER , GENDER , DATE OF BIRTH , MARITAL STATUS , EMPLOYMENT STATUS ," + "EDUCATION LEVEL , ART STATUS , SERVICE PROVIDER NAME , HEALTH FACILITY, LESSONS ATTENDED,AGE BRACKET, Knowledge of HIV Status," + "Partner HIV Testing,Child HIV Testing,Discordance,HIV Disclosure,Risk Factor/Reduction,Condom Use," + "Alcohol and Substance Abuse,Adherence,STIs,Family Planning,PMTCT,TB").split(","); // COPY FILE TO BE WRITTEN TO Path original = Paths.get(getServletContext().getRealPath("/NOT_ACHIEVED_TEMPLATE.xlsm")); //original file Path destination = Paths.get(getServletContext().getRealPath("/NOT_ACHIEVED_TEMPLATE_1.xlsm")); //new file System.out.println("origin : " + original + " destination : " + destination); try { Files.copy(original, destination, StandardCopyOption.REPLACE_EXISTING); System.out.println("file copied----------------"); } catch (IOException x) { //catch all for IO problems System.out.println("fine not copied"); } String allpath = getServletContext().getRealPath("/NOT_ACHIEVED_TEMPLATE_1.xlsm"); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb1; OPCPackage pkg = OPCPackage.open(allpath); wb1 = new XSSFWorkbook(pkg); SXSSFWorkbook wb = new SXSSFWorkbook(wb1, 100); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ // HSSFWorkbook wb=new HSSFWorkbook(); Sheet shet1 = wb.getSheet("Sheet1"); Font font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); Font font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); CellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); for (int i = 0; i <= reportHeader.length; i++) { shet1.setColumnWidth(i, 4000); } CellStyle styleBorder = wb.createCellStyle(); styleBorder.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); CellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); Font fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); Cell cell; Row rw0 = shet1.createRow(0); rw0.setHeightInPoints(30); rw0.setRowStyle(style2); for (int i = 0; i <= (reportHeader.length - 1); i++) { cell = rw0.createCell(i); cell.setCellValue(reportHeader[i]); cell.setCellStyle(stylex); } String getClients = "SELECT county.county_name,partner.partner_name,district.district_name,dic.dic_name," + "groups.group_name,personal_information.fname,personal_information.mname,personal_information.lname," + "personal_information.ccc_no,personal_information.mobile_no, personal_information.gender," + "personal_information.dob,marital_status.name,employment_status.name,education_levels.name," + "art_status.name," + "service_provider.fname,service_provider.mname,service_provider.lname,health_facility.hf_name, " + "personal_information.lessons_attended," + "CASE" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 9 THEN '0-9'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 10 AND 14 THEN '10-14'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 25 AND 49 THEN '25-49'" + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >49 THEN '50 and above'" + " ELSE 'NO DATE OF BIRTH'" + " END AS AGEBRACKET," + "CASE " + "when personal_information.gender LIKE 'Female' THEN 'F' " + "when personal_information.gender LIKE 'Male' THEN 'M' " + "ELSE 'NO SEX' " + "END AS SEX,s1,s2,s3,s4,s5,s6,s7,s8,s9,s10,s11,s12,s13 " + " FROM personal_information " + " LEFT JOIN groups ON personal_information.group_id=groups.group_id " + " LEFT JOIN dic ON personal_information.dic_id=dic.dic_id " + " LEFT JOIN service_provider ON personal_information.provider_id=service_provider.provider_id " + " LEFT JOIN health_facility ON personal_information.hf_id=health_facility.hf_id " + " LEFT JOIN district ON personal_information.district_id=district.district_id " + " LEFT JOIN marital_status ON personal_information.marital_status=marital_status.id " + " LEFT JOIN employment_status ON personal_information.employment_status=employment_status.id " + " LEFT JOIN education_levels ON personal_information.education_level=education_levels.id " + " LEFT JOIN art_status ON personal_information.art_status=art_status.id " + " LEFT JOIN register ON personal_information.client_id=register.client_id " + " LEFT JOIN partner ON personal_information.partner_id=partner.partner_id " + " LEFT JOIN county ON district.county_id=county.county_id " + " WHERE (personal_information.completionmonth=0 || personal_information.completionyear=0) " + " ORDER BY partner.partner_name,county.county_name,district.district_name,dic.dic_name," + "groups.group_name"; System.out.println("query is : " + getClients); conn.rs = conn.st.executeQuery(getClients); while (conn.rs.next()) { // ADD THE DATA TO EXCEL HERE groupName = DICName = districtName = partnerName = countyName = agebracket = lessons_attended = year = ""; clientFname = clientMname = clientLname = ccc_no = mobile_no = gender = dob = marital_status = ""; location = employment_status = education_level = under_18 = ovc_children = hiv_year = art_status = ""; registration_date = approved_by = designation = approval_date = ""; SPFname = SPMname = SPLname = SPFullName = healthFacility = ""; s1 = s2 = s3 = s4 = s5 = s6 = s7 = s8 = s9 = s10 = s11 = s12 = s13 = ""; if (conn.rs.getString(1) != null) { countyName = conn.rs.getString(1); } if (conn.rs.getString(2) != null) { partnerName = conn.rs.getString(2); } if (conn.rs.getString(3) != null) { districtName = conn.rs.getString(3); } if (conn.rs.getString(4) != null) { DICName = conn.rs.getString(4); } else { DICName = "NO DIC"; } if (conn.rs.getString(5) != null) { groupName = conn.rs.getString(5); } else { groupName = "Individual"; } if (conn.rs.getString(6) != null) { clientFname = conn.rs.getString(6); } if (conn.rs.getString(7) != null) { clientMname = conn.rs.getString(7); } if (conn.rs.getString(8) != null) { clientLname = conn.rs.getString(8); } if (conn.rs.getString(9) != null) { ccc_no = conn.rs.getString(9); } if (conn.rs.getString(10) != null) { mobile_no = conn.rs.getString(10); } if (conn.rs.getString(11) != null) { gender = conn.rs.getString(11); } if (conn.rs.getString(12) != null) { dob = conn.rs.getString(12); } if (conn.rs.getString(13) != null) { marital_status = conn.rs.getString(13); } if (conn.rs.getString(14) != null) { employment_status = conn.rs.getString(14); } if (conn.rs.getString(15) != null) { education_level = conn.rs.getString(15); } if (conn.rs.getString(16) != null) { art_status = conn.rs.getString(16); } if (conn.rs.getString(17) != null) { SPFname = conn.rs.getString(17); } if (conn.rs.getString(18) != null) { SPMname = conn.rs.getString(18); } if (conn.rs.getString(19) != null) { SPLname = conn.rs.getString(19); } if (conn.rs.getString(20) != null) { healthFacility = conn.rs.getString(20); } if (conn.rs.getString(21) != null) { lessons_attended = conn.rs.getString(21); } if (conn.rs.getString(22) != null) { agebracket = conn.rs.getString(22); } if (conn.rs.getString(23) != null) { gender = conn.rs.getString(23); } if (conn.rs.getString(24) != null) { s1 = conn.rs.getString(24); } if (conn.rs.getString(25) != null) { s2 = conn.rs.getString(25); } if (conn.rs.getString(26) != null) { s3 = conn.rs.getString(26); } if (conn.rs.getString(27) != null) { s4 = conn.rs.getString(27); } if (conn.rs.getString(28) != null) { s5 = conn.rs.getString(28); } if (conn.rs.getString(29) != null) { s6 = conn.rs.getString(29); } if (conn.rs.getString(30) != null) { s7 = conn.rs.getString(30); } if (conn.rs.getString(31) != null) { s8 = conn.rs.getString(31); } if (conn.rs.getString(32) != null) { s9 = conn.rs.getString(32); } if (conn.rs.getString(33) != null) { s10 = conn.rs.getString(33); } if (conn.rs.getString(34) != null) { s11 = conn.rs.getString(34); } if (conn.rs.getString(35) != null) { s12 = conn.rs.getString(35); } if (conn.rs.getString(36) != null) { s13 = conn.rs.getString(36); } if (s1.equals("5")) { s1 = ""; } if (s1.equals("2")) { s1 = "0"; } if (s2.equals("5")) { s2 = ""; } if (s2.equals("2")) { s2 = "0"; } if (s3.equals("5")) { s3 = ""; } if (s3.equals("2")) { s3 = "0"; } if (s4.equals("5")) { s4 = ""; } if (s4.equals("2")) { s4 = "0"; } if (s5.equals("5")) { s5 = ""; } if (s5.equals("2")) { s5 = "0"; } if (s6.equals("5")) { s6 = ""; } if (s6.equals("2")) { s6 = "0"; } if (s7.equals("5")) { s7 = ""; } if (s7.equals("2")) { s7 = "0"; } if (s8.equals("5")) { s8 = ""; } if (s8.equals("2")) { s8 = "0"; } if (s9.equals("5")) { s9 = ""; } if (s9.equals("2")) { s9 = "0"; } if (s10.equals("5")) { s10 = ""; } if (s10.equals("2")) { s10 = "0"; } if (s11.equals("5")) { s11 = ""; } if (s11.equals("2")) { s11 = "0"; } if (s12.equals("5")) { s12 = ""; } if (s12.equals("2")) { s12 = "0"; } if (s13.equals("5")) { s13 = ""; } if (s13.equals("2")) { s13 = "0"; } if (clientMname.equals(clientLname)) { clientMname = ""; } if (SPMname.equals(SPLname)) { SPMname = ""; } SPFullName = SPFname + " " + SPMname + " " + SPLname; clientFullName = clientFname + " " + clientMname + " " + clientLname; String rawData[] = (countyName + "," + partnerName + "," + districtName + "," + DICName + "," + groupName + "," + clientFullName + "," + ccc_no + "," + mobile_no + "," + gender + "," + dob + "," + marital_status + "," + employment_status + "," + education_level + "," + art_status + "," + SPFullName + "," + healthFacility + "," + lessons_attended + "," + agebracket + "," + s1 + "," + s2 + "," + s3 + "," + s4 + "," + s5 + "," + s6 + "," + s7 + "," + s8 + "," + s9 + "," + s10 + "," + s11 + "," + s12 + "," + s13).split(","); Row rw1 = shet1.createRow(position); rw1.setHeightInPoints(25); rw1.setRowStyle(style2); for (int i = 0; i <= (reportHeader.length - 1); i++) { cell = rw1.createCell(i); cell.setCellStyle(styleBorder); } for (int i = 0; i <= (rawData.length - 1); i++) { cell = rw1.getCell(i); cell.setCellValue(rawData[i]); } position++; System.out.println("at position : " + position); } IdGenerator CRT = new IdGenerator(); ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_CLIENTS_NOT_REACHED_REPORT_CREATED_ON_" + CRT.timestamp() + ".xlsm"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); pkg.close(); }
From source file:ro.dabuno.office.integration.BusinessPlan.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;//w ww .j av a2 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(75); //75% scale // 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(); wb.close(); }
From source file:ro.fortsoft.wicket.pivot.exporter.PivotXlsExporter.java
License:Apache License
private void autoSizeColumns(Sheet sheetData, int maxColNum) { try {//from w w w.j a v a 2 s .c o m // Autosize columns int width = 0; for (int col = 0; col < maxColNum; col++) { sheetData.autoSizeColumn(col); int cwidth = sheetData.getColumnWidth(col); cwidth += 500; sheetData.setColumnWidth(col, cwidth); width += cwidth; } // calculate zoom factor int nominator = 45000 * 100 / width; if (nominator < 100) sheetData.setZoom(nominator, 100); } catch (HeadlessException he) { // No UI, no autosize :( } }
From source file:sample.poi.CalendarDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Calendar calendar = Calendar.getInstance(); SimpleDateFormat formatFile = new SimpleDateFormat("dd-MM-yyyy-hh-mm-ss"); HSSFWorkbook wb = 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);//from w w w . java 2 s. c om 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] + " " + "2013"); 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"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:test.poi.LoanCalculator.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new HSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Loan Calculator"); sheet.setPrintGridlines(false);//from www . j a v a 2s. c om 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 = "E:/loan-calculator.xls"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:test.poi.TimesheetDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new HSSFWorkbook(); // if(args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); // else wb = new Workbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Timesheet"); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true);/*from ww w. ja va2 s .c o m*/ sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //title row Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue("Weekly Timesheet"); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1")); //header row Row headerRow = sheet.createRow(1); headerRow.setHeightInPoints(40); Cell headerCell; for (int i = 0; i < titles.length; i++) { headerCell = headerRow.createCell(i); headerCell.setCellValue(titles[i]); headerCell.setCellStyle(styles.get("header")); } 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")); } } } //row with totals below Row sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(35); Cell cell; cell = sumRow.createCell(0); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellValue("Total Hrs:"); cell.setCellStyle(styles.get("formula")); for (int j = 2; j < 12; j++) { cell = sumRow.createCell(j); String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12"; cell.setCellFormula("SUM(" + ref + ")"); if (j >= 9) cell.setCellStyle(styles.get("formula_2")); else cell.setCellStyle(styles.get("formula")); } rownum++; sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(25); cell = sumRow.createCell(0); cell.setCellValue("Total Regular Hours"); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellFormula("L13"); cell.setCellStyle(styles.get("formula_2")); sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(25); cell = sumRow.createCell(0); cell.setCellValue("Total Overtime Hours"); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellFormula("K13"); cell.setCellStyle(styles.get("formula_2")); //set sample data for (int i = 0; i < sample_data.length; i++) { Row row = sheet.getRow(2 + i); for (int j = 0; j < sample_data[i].length; j++) { if (sample_data[i][j] == null) continue; if (sample_data[i][j] instanceof String) { row.getCell(j).setCellValue((String) sample_data[i][j]); } else { row.getCell(j).setCellValue((Double) sample_data[i][j]); } } } //finally set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(0, 30 * 256); //30 characters wide for (int i = 2; i < 9; i++) { sheet.setColumnWidth(i, 6 * 256); //6 characters wide } sheet.setColumnWidth(10, 10 * 256); //10 characters wide // Write the output to a file String file = "E:\\timesheet.xls"; // if(wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:uk.co.certait.htmlexporter.writer.excel.ExcelExporter.java
License:Apache License
protected void formatSheet(Sheet sheet) { int lastRowWithData = 0; for (int i = sheet.getLastRowNum(); i >= 0; --i) { if (sheet.getRow(i) != null && sheet.getRow(i).getPhysicalNumberOfCells() > 0) { lastRowWithData = i;//from w w w . j a v a2s . c om break; } } for (int i = 0; i < sheet.getRow(lastRowWithData).getPhysicalNumberOfCells(); ++i) { sheet.autoSizeColumn(i); } for (int i = 0; i < sheet.getRow(sheet.getLastRowNum()).getPhysicalNumberOfCells(); ++i) { sheet.setColumnWidth(i, (int) (sheet.getColumnWidth(i) * 1.2)); } }
From source file:util.excel.SheetOfStudentInfo.java
License:Open Source License
/** * Create an input stream which can be read by the server to send a spreadsheet to the server. *///from w w w . j a v a 2 s.c o m public static InputStream getDownloadStream(Map<String, List<Student>> map) { Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet(); sheet.setColumnWidth(1, 15 * 256); sheet.setColumnWidth(3, 20 * 256); sheet.setColumnWidth(4, 3 * 256); int count = 0; // Header row Row row = sheet.createRow(count); String[] strings = Messages.get("spreadsheet.header.info").split(","); for (int i = 0; i < strings.length; i++) { row.createCell(i).setCellValue(strings[i]); } count++; for (Map.Entry<String, List<Student>> entry : map.entrySet()) { // Blank row before each class count++; for (Student student : entry.getValue()) { row = sheet.createRow(count); row.createCell(0).setCellValue(entry.getKey()); String[] names = student.getName().split("\\s*,\\s*"); row.createCell(1).setCellValue(names[0]); if (names.length > 0) { row.createCell(2).setCellValue(names[1]); } row.createCell(3).setCellValue(student.getEmail()); row.createCell(4).setCellValue( Messages.get(student.isMale() ? "gender.abbrev.male" : "gender.abbrev.female")); row.createCell(5).setCellValue(student.getBebrasId()); row.createCell(6).setCellValue(student.getInitialPassword()); count++; } } try (ByteArrayOutputStream out = new ByteArrayOutputStream()) { workbook.write(out); return new ByteArrayInputStream(out.toByteArray()); } catch (IOException ex) { throw new RuntimeException("Could not create file to download", ex); } }
From source file:util.excel.SheetOfStudentMarks.java
License:Open Source License
/** * Create an input stream which can be read by the server to send a spreadsheet to the server. *//*from ww w . j av a 2s. c o m*/ public static InputStream getDownloadStream(Map<String, List<ParticipationDAO.StudentMarks>> map) { Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet(); sheet.setColumnWidth(1, 15 * 256); int count = 0; // Header row Row row = sheet.createRow(count); String[] strings = Messages.get("spreadsheet.header.marks").split(","); for (int i = 0; i < strings.length; i++) { row.createCell(i).setCellValue(strings[i]); } count++; for (Map.Entry<String, List<ParticipationDAO.StudentMarks>> entry : map.entrySet()) { // Blank row before each class count++; for (ParticipationDAO.StudentMarks marks : entry.getValue()) { row = sheet.createRow(count); row.createCell(0).setCellValue(entry.getKey()); // TODO: factor out code in common with SheetOfStudentInfo String[] names = marks.name.split("\\s*,\\s*"); row.createCell(1).setCellValue(names[0]); if (names.length > 0) { row.createCell(2).setCellValue(names[1]); } row.createCell(3).setCellValue(marks.totalMarks); row.createCell(4).setCellValue(marks.maximumMarks); count++; } } try (ByteArrayOutputStream out = new ByteArrayOutputStream()) { workbook.write(out); return new ByteArrayInputStream(out.toByteArray()); } catch (IOException ex) { throw new RuntimeException("Could not create file to download", ex); } }