List of usage examples for org.apache.poi.hssf.usermodel HSSFCellStyle setDataFormat
@Override public void setDataFormat(short fmt)
From source file:org.hil.children.service.impl.ChildrenManagerImpl.java
License:Open Source License
public String printListChildren(ChildrenSearchVO params) { List<ChildrenPrintVO> children = childrenDaoExt.searchChildrenForPrint(params); SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy"); String strDOBFrom = format.format(params.getDateOfBirthFrom()); String strDOBTo = format.format(params.getDateOfBirthTo()); String path = ""; String prefixFileName = ""; Commune commune = communeDao.get(params.getCommuneId()); prefixFileName = commune.getDistrict().getProvince().getProvinceId() + commune.getDistrict().getDistrictId() + commune.getCommuneId();//w ww.j av a 2 s .c om GraniteContext gc = GraniteContext.getCurrentInstance(); ServletContext sc = ((HttpGraniteContext) gc).getServletContext(); String reportDir = sc.getRealPath(config.getBaseReportDir()); long currentTime = System.currentTimeMillis(); String filePath = reportDir + "/" + prefixFileName + "_List_Children_Excel_" + currentTime; POIFSFileSystem fs; try { filePath += ".xls"; fs = new POIFSFileSystem(new FileInputStream(reportDir + "/excel/ListOfChildrenInCommune.xls")); HSSFWorkbook wb = new HSSFWorkbook(fs, true); HSSFSheet s = wb.getSheetAt(0); HSSFRow r = null; HSSFCell c = null; r = s.getRow(0); c = r.getCell(1); c.setCellValue(commune.getCommuneName()); c = r.getCell(2); c.setCellValue("(" + strDOBFrom + " - " + strDOBTo + ")"); HSSFCellStyle cs = wb.createCellStyle(); cs.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs.setBorderTop(HSSFCellStyle.BORDER_THIN); cs.setBorderLeft(HSSFCellStyle.BORDER_THIN); cs.setBorderRight(HSSFCellStyle.BORDER_THIN); HSSFCellStyle cs1 = wb.createCellStyle(); cs1.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs1.setBorderTop(HSSFCellStyle.BORDER_THIN); cs1.setBorderLeft(HSSFCellStyle.BORDER_THIN); cs1.setBorderRight(HSSFCellStyle.BORDER_THIN); cs1.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle cs2 = wb.createCellStyle(); cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs2.setBorderTop(HSSFCellStyle.BORDER_THIN); cs2.setBorderLeft(HSSFCellStyle.BORDER_THIN); cs2.setBorderRight(HSSFCellStyle.BORDER_THIN); CreationHelper createHelper = wb.getCreationHelper(); cs2.setDataFormat(createHelper.createDataFormat().getFormat("dd/MM/yyyy")); int rownum = 3; for (rownum = 3; rownum < children.size() + 3; rownum++) { r = s.createRow(rownum); c = r.createCell(0); c.setCellStyle(cs1); c.setCellValue(rownum - 2); c = r.createCell(1); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getFullName()); c = r.createCell(2); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getDateOfBirth()); c = r.createCell(3); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).isGender() == true ? "N" : "Nam"); c = r.createCell(4); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getVillageName()); c = r.createCell(5); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getMotherName()); c = r.createCell(6); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getMotherBirthYear() != null ? children.get(rownum - 3).getMotherBirthYear() : 0); c = r.createCell(7); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getMotherMobile()); c = r.createCell(8); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getFatherName()); c = r.createCell(9); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getFatherBirthYear() != null ? children.get(rownum - 3).getFatherBirthYear() : 0); c = r.createCell(10); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getFatherMobile()); c = r.createCell(11); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getCaretakerName()); c = r.createCell(12); c.setCellStyle(cs); c.setCellValue(children.get(rownum - 3).getCaretakerMobile()); c = r.createCell(13); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getVGB()); c = r.createCell(14); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getBCG()); c = r.createCell(15); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getDPT_VGB_Hib1()); c = r.createCell(16); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getDPT_VGB_Hib2()); c = r.createCell(17); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getDPT_VGB_Hib3()); c = r.createCell(18); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getOPV1()); c = r.createCell(19); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getOPV2()); c = r.createCell(20); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getOPV3()); c = r.createCell(21); c.setCellStyle(cs2); c.setCellValue(children.get(rownum - 3).getMeasles1()); } FileOutputStream fileOut = new FileOutputStream(filePath); wb.write(fileOut); fileOut.close(); path = "/reports/" + prefixFileName + "_List_Children_Excel_" + currentTime + ".xls"; } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return path; }
From source file:org.hil.children.service.impl.ChildrenManagerImpl.java
License:Open Source License
public String printListVaccinatedInLocationReport(String type, String timeFrom, String timeTo, Commune commune, District district, Vaccination vaccine, List<ChildrenVaccinatedInLocationVO> statistics) { SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy"); String path = ""; String prefixFileName = ""; if (commune != null) prefixFileName = district.getDistrictId() + "_" + commune.getCommuneId(); else if (district != null) prefixFileName = district.getDistrictId(); GraniteContext gc = GraniteContext.getCurrentInstance(); ServletContext sc = ((HttpGraniteContext) gc).getServletContext(); String reportDir = sc.getRealPath(config.getBaseReportDir()); long currentTime = System.currentTimeMillis(); String filePath = reportDir + "/" + prefixFileName + "_DanhSachTreDenTiem_" + vaccine.getName() + "_" + currentTime;/* w ww .jav a 2 s. co m*/ POIFSFileSystem fs; try { filePath += ".xls"; fs = new POIFSFileSystem( new FileInputStream(reportDir + "/excel/ListOfChildrenVaccinatedInLocation.xls")); HSSFWorkbook wb = new HSSFWorkbook(fs, true); HSSFSheet s = wb.getSheetAt(0); HSSFRow r = null; HSSFCell c = null; r = s.getRow(0); c = r.getCell(0); c.setCellValue("Danh sch tr n tim chng " + vaccine.getName() + " (bao gm c tr tim bnh vin/phng khm)"); r = s.getRow(1); c = r.getCell(1); c.setCellValue(district.getDistrictName()); if (commune != null) { c = r.getCell(2); c.setCellValue("X"); c = r.getCell(3); c.setCellValue(commune.getCommuneName()); c = r.getCell(4); c.setCellValue("(" + timeFrom + " - " + timeTo + ")"); } else { c = r.getCell(2); c.setCellValue("(" + timeFrom + " - " + timeTo + ")"); } HSSFCellStyle cs = wb.createCellStyle(); cs.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs.setBorderTop(HSSFCellStyle.BORDER_THIN); cs.setBorderLeft(HSSFCellStyle.BORDER_THIN); cs.setBorderRight(HSSFCellStyle.BORDER_THIN); HSSFCellStyle cs1 = wb.createCellStyle(); cs1.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs1.setBorderTop(HSSFCellStyle.BORDER_THIN); cs1.setBorderLeft(HSSFCellStyle.BORDER_THIN); cs1.setBorderRight(HSSFCellStyle.BORDER_THIN); cs1.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFCellStyle cs2 = wb.createCellStyle(); cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs2.setBorderTop(HSSFCellStyle.BORDER_THIN); cs2.setBorderLeft(HSSFCellStyle.BORDER_THIN); cs2.setBorderRight(HSSFCellStyle.BORDER_THIN); CreationHelper createHelper = wb.getCreationHelper(); cs2.setDataFormat(createHelper.createDataFormat().getFormat("dd/MM/yyyy")); int rownum = 3; for (rownum = 3; rownum < statistics.size() + 3; rownum++) { r = s.createRow(rownum); c = r.createCell(0); c.setCellStyle(cs1); c.setCellValue(rownum - 2); c = r.createCell(1); c.setCellStyle(cs); c.setCellValue(statistics.get(rownum - 3).getCommuneName()); c = r.createCell(2); c.setCellStyle(cs); c.setCellValue(statistics.get(rownum - 3).getVillageName()); c = r.createCell(3); c.setCellStyle(cs); c.setCellValue(statistics.get(rownum - 3).getChildCode()); c = r.createCell(4); c.setCellStyle(cs); c.setCellValue(statistics.get(rownum - 3).getFullName()); c = r.createCell(5); c.setCellStyle(cs1); c.setCellValue(statistics.get(rownum - 3).getGender() == true ? "N" : "Nam"); c = r.createCell(6); c.setCellStyle(cs2); c.setCellValue(statistics.get(rownum - 3).getDateOfBirth()); c = r.createCell(7); c.setCellStyle(cs); c.setCellValue(statistics.get(rownum - 3).getMotherName()); c = r.createCell(8); c.setCellStyle(cs2); c.setCellValue(statistics.get(rownum - 3).getDateOfImmunization()); c = r.createCell(9); c.setCellStyle(cs); String vaccinatedLocation = ""; if (statistics.get(rownum - 3).getOtherLocation() != null && statistics.get(rownum - 3).getOtherLocation() >= 1 && statistics.get(rownum - 3).getOtherLocation() <= 4) { if (statistics.get(rownum - 3).getOtherLocation() == 1) vaccinatedLocation = "Bnh vin TW"; else if (statistics.get(rownum - 3).getOtherLocation() == 2) vaccinatedLocation = "Bnh vin tnh"; else if (statistics.get(rownum - 3).getOtherLocation() == 3) vaccinatedLocation = "Bnh vin huyn"; else if (statistics.get(rownum - 3).getOtherLocation() == 4) vaccinatedLocation = "Phng khm/Bnh vin t nhn"; } else vaccinatedLocation = statistics.get(rownum - 3).getVaccinatedCommune(); c.setCellValue(vaccinatedLocation); } FileOutputStream fileOut = new FileOutputStream(filePath); wb.write(fileOut); fileOut.close(); path = "/reports/" + prefixFileName + "_DanhSachTreDenTiem_" + vaccine.getName() + "_" + currentTime + ".xls"; } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return path; }
From source file:org.mili.core.text.transformation.ExcelTransformator.java
License:Apache License
/** * Transforms./* www . j a v a2 s. c om*/ * * @param from from table * @param params the params * @return the HSSF workbook */ public HSSFWorkbook transform(Table from, Object... params) { if (from.getRowSize() == 0) { HSSFWorkbook wb = new HSSFWorkbook(); HSSFDataFormat format = wb.createDataFormat(); HSSFSheet sheet = wb.createSheet(getSheetName(null)); HSSFRow r = sheet.createRow(0); HSSFCell c = r.createCell((short) (0)); c.setCellValue("Keine Daten vorhanden !"); return wb; } String fsInt = "#,###,##0"; String fsFloat = "#,###,##0.000"; HSSFWorkbook wb = new HSSFWorkbook(); HSSFDataFormat format = wb.createDataFormat(); HSSFSheet sheet = wb.createSheet(getSheetName(null)); HSSFFont headFont = wb.createFont(); headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle headStyle = wb.createCellStyle(); headStyle.setFont(headFont); HSSFCellStyle numberStyle = wb.createCellStyle(); HSSFRow headerRow = sheet.createRow(0); short z = 0; for (int i = 0, n = from.getColSize(); i < n; i++) { Col col = from.getCol(i); HSSFCell headerCell = headerRow.createCell((short) (z)); headerCell.setCellStyle(headStyle); headerCell.setCellValue(col.getName()); z++; } for (int i = 0, n = from.getRowSize(); i < n; i++) { Row row = from.getRow(i); HSSFRow contentRow = sheet.createRow(i + 1); short j = 0; for (int ii = 0, nn = from.getColSize(); ii < nn; ii++) { Col col = from.getCol(ii); Object o = row.getValue(ii); HSSFCell contentCell = contentRow.createCell((short) (j)); String value = o == null ? "" : String.valueOf(o); if (o instanceof Number) { if (o instanceof Integer) { numberStyle.setDataFormat(format.getFormat(fsInt)); contentCell.setCellValue(Integer.parseInt(value)); contentCell.setCellStyle(numberStyle); } else if (o instanceof Float) { numberStyle.setDataFormat(format.getFormat(fsFloat)); contentCell.setCellValue(Float.parseFloat(value)); contentCell.setCellStyle(numberStyle); } } else { contentCell.setCellValue(value); } j++; } } return wb; }
From source file:org.openmicroscopy.shoola.util.file.ExcelWriter.java
License:Open Source License
/** Creates the default styles. */ private void createStyles() { HSSFCellStyle style; Iterator<String> fontIterator = fontMap.keySet().iterator(); String fontName;/*from w w w. j ava 2 s .c o m*/ while (fontIterator.hasNext()) { fontName = fontIterator.next(); style = workbook.createCellStyle(); style.setFont(fontMap.get(fontName)); styleMap.put(fontName, style); } HSSFDataFormat df; style = workbook.createCellStyle(); style.setFont(fontMap.get(DEFAULT)); df = workbook.createDataFormat(); style.setDataFormat(df.getFormat("#.##")); styleMap.put(TWODECIMALPOINTS, style); style = workbook.createCellStyle(); style.setFont(fontMap.get(DEFAULT)); df = workbook.createDataFormat(); style.setDataFormat(df.getFormat("0")); styleMap.put(INTEGER, style); style = workbook.createCellStyle(); style.setFont(fontMap.get(DEFAULT)); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBottomBorderColor(HSSFColor.BLACK.index); styleMap.put(CELLBORDER_UNDERLINE, style); style = workbook.createCellStyle(); style.setFont(fontMap.get(DEFAULT)); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setTopBorderColor(HSSFColor.BLACK.index); styleMap.put(CELLBORDER_TOPLINE, style); style = workbook.createCellStyle(); style.setFont(fontMap.get(DEFAULT)); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setTopBorderColor(HSSFColor.BLACK.index); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBottomBorderColor(HSSFColor.BLACK.index); styleMap.put(CELLBORDER_UNDERLINE_TOPLINE, style); }
From source file:org.openscada.ae.ui.views.export.excel.impl.ExportEventsImpl.java
License:Open Source License
private IStatus storeExcel(final File file, final List<Event> events, final List<Field> columns, final IProgressMonitor monitor) throws IOException { final HSSFWorkbook workbook = new HSSFWorkbook(); final HSSFDataFormat dateFormat = workbook.createDataFormat(); final HSSFCellStyle dateCellStyle = workbook.createCellStyle(); dateCellStyle.setDataFormat(dateFormat.getFormat("YYYY-MM-DD hh:mm:ss.000")); try {/*from w ww . j a v a 2 s.c o m*/ monitor.beginTask(Messages.ExportImpl_Progress_ExportingEvents, events.size() + 3 + columns.size()); try { monitor.subTask(Messages.ExportImpl_Progress_CreateWorkbook); monitor.worked(1); final HSSFSheet sheet = createSheet(events, workbook, columns); monitor.worked(1); monitor.setTaskName(Messages.ExportImpl_Progress_ExportEvents); for (int i = 0; i < events.size(); i++) { final HSSFRow row = sheet.createRow(i + 1); final Event e = events.get(i); for (int j = 0; j < columns.size(); j++) { final Field field = columns.get(j); final ExcelCell cell = new ExcelCell(row, j, dateCellStyle); field.render(e, cell); } monitor.worked(1); if (monitor.isCanceled()) { return Status.CANCEL_STATUS; } } workbook.setRepeatingRowsAndColumns(0, -1, -1, 0, 1); monitor.setTaskName("Auto sizing"); for (int i = 0; i < columns.size(); i++) { monitor.subTask(String.format("Auto sizing column: %s", columns.get(i).getHeader())); sheet.autoSizeColumn(i); monitor.worked(1); if (monitor.isCanceled()) { return Status.CANCEL_STATUS; } } } finally { monitor.subTask(Messages.ExportImpl_Progress_CloseFile); if (workbook != null) { makeDocInfo(workbook); final FileOutputStream stream = new FileOutputStream(file); workbook.write(stream); stream.close(); } monitor.worked(1); } } finally { monitor.done(); } return Status.OK_STATUS; }
From source file:org.openswing.swing.export.java.ExportToExcel.java
License:Open Source License
private int prepareGenericComponent(int rownum, HSSFWorkbook wb, HSSFSheet s, ExportOptions exportOptions, ComponentExportOptions opt) throws Throwable { Object[] row = null;//from w w w . j a va 2 s . c om Object obj = null; HSSFRow r = null; HSSFCell c = null; HSSFCellStyle csText = wb.createCellStyle(); csText.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csText.setBorderBottom(HSSFCellStyle.BORDER_THIN); csText.setBorderLeft(HSSFCellStyle.BORDER_THIN); csText.setBorderRight(HSSFCellStyle.BORDER_THIN); csText.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csTitle = wb.createCellStyle(); csTitle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN); csTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN); csTitle.setBorderRight(HSSFCellStyle.BORDER_THIN); csTitle.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFFont f = wb.createFont(); f.setBoldweight(f.BOLDWEIGHT_NORMAL); csTitle.setFont(f); HSSFCellStyle csBool = wb.createCellStyle(); csBool.setBorderBottom(HSSFCellStyle.BORDER_THIN); csBool.setBorderLeft(HSSFCellStyle.BORDER_THIN); csBool.setBorderRight(HSSFCellStyle.BORDER_THIN); csBool.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csDecNum = wb.createCellStyle(); csDecNum.setDataFormat(wb.createDataFormat().getFormat("#,##0.#####")); csDecNum.setBorderBottom(HSSFCellStyle.BORDER_THIN); csDecNum.setBorderLeft(HSSFCellStyle.BORDER_THIN); csDecNum.setBorderRight(HSSFCellStyle.BORDER_THIN); csDecNum.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csIntNum = wb.createCellStyle(); csIntNum.setDataFormat(wb.createDataFormat().getFormat("#,##0")); csIntNum.setBorderBottom(HSSFCellStyle.BORDER_THIN); csIntNum.setBorderLeft(HSSFCellStyle.BORDER_THIN); csIntNum.setBorderRight(HSSFCellStyle.BORDER_THIN); csIntNum.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csDateTime = wb.createCellStyle(); csDateTime.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"/*opt.getDateTimeFormat()*/)); csDateTime.setBorderBottom(HSSFCellStyle.BORDER_THIN); csDateTime.setBorderLeft(HSSFCellStyle.BORDER_THIN); csDateTime.setBorderRight(HSSFCellStyle.BORDER_THIN); csDateTime.setBorderTop(HSSFCellStyle.BORDER_THIN); if (opt.getCellsContent() != null) for (int i = 0; i < opt.getCellsContent().length; i++) { row = opt.getCellsContent()[i]; r = s.createRow(rownum); for (short j = 0; j < row.length; j++) { c = r.createCell(j); obj = row[j]; if (obj != null) { if (obj instanceof String) { try { c.setEncoding(HSSFWorkbook.ENCODING_UTF_16); } catch (NoSuchMethodError ex) { } c.setCellValue(obj.toString()); c.setCellStyle(csText); } else if (obj instanceof BigDecimal || obj instanceof Double || obj instanceof Float || obj.getClass() == Double.TYPE || obj.getClass() == Float.TYPE) { c.setCellValue(Double.parseDouble(obj.toString())); c.setCellStyle(csDecNum); } else if (obj instanceof Integer || obj instanceof Short || obj instanceof Long || obj.getClass() == Integer.TYPE || obj.getClass() == Short.TYPE || obj.getClass() == Long.TYPE) { c.setCellValue(Double.parseDouble(obj.toString())); c.setCellStyle(csIntNum); } else if (obj instanceof Boolean) { c.setCellValue(((Boolean) obj).booleanValue()); c.setCellStyle(csBool); } else if (obj.getClass().equals(boolean.class)) { c.setCellValue(((Boolean) obj).booleanValue()); c.setCellStyle(csBool); } else if (obj instanceof Date || obj instanceof java.util.Date || obj instanceof java.sql.Timestamp) { c.setCellValue((java.util.Date) obj); c.setCellStyle(csDateTime); } } else { c.setCellValue(""); c.setCellStyle(csText); } } rownum++; } return rownum; }
From source file:org.openswing.swing.export.java.ExportToExcel.java
License:Open Source License
private int prepareGrid(int rownum, HSSFWorkbook wb, HSSFSheet s, ExportOptions exportOptions, GridExportOptions opt) throws Throwable { // declare a row object reference HSSFRow r = null;//from ww w . ja va2s . c o m // declare a cell object reference HSSFCell c = null; // create 3 cell styles HSSFCellStyle csText = wb.createCellStyle(); csText.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csText.setBorderBottom(HSSFCellStyle.BORDER_THIN); csText.setBorderLeft(HSSFCellStyle.BORDER_THIN); csText.setBorderRight(HSSFCellStyle.BORDER_THIN); csText.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csTitle = wb.createCellStyle(); csTitle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN); csTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN); csTitle.setBorderRight(HSSFCellStyle.BORDER_THIN); csTitle.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFFont f = wb.createFont(); f.setBoldweight(f.BOLDWEIGHT_NORMAL); csTitle.setFont(f); HSSFCellStyle csBool = wb.createCellStyle(); csBool.setBorderBottom(HSSFCellStyle.BORDER_THIN); csBool.setBorderLeft(HSSFCellStyle.BORDER_THIN); csBool.setBorderRight(HSSFCellStyle.BORDER_THIN); csBool.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csDecNum = wb.createCellStyle(); csDecNum.setDataFormat(wb.createDataFormat().getFormat("#,##0.#####")); csDecNum.setBorderBottom(HSSFCellStyle.BORDER_THIN); csDecNum.setBorderLeft(HSSFCellStyle.BORDER_THIN); csDecNum.setBorderRight(HSSFCellStyle.BORDER_THIN); csDecNum.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csIntNum = wb.createCellStyle(); csIntNum.setDataFormat(wb.createDataFormat().getFormat("#,##0")); csIntNum.setBorderBottom(HSSFCellStyle.BORDER_THIN); csIntNum.setBorderLeft(HSSFCellStyle.BORDER_THIN); csIntNum.setBorderRight(HSSFCellStyle.BORDER_THIN); csIntNum.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csDate = wb.createCellStyle(); csDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"/*opt.getDateFormat()*/)); csDate.setBorderBottom(HSSFCellStyle.BORDER_THIN); csDate.setBorderLeft(HSSFCellStyle.BORDER_THIN); csDate.setBorderRight(HSSFCellStyle.BORDER_THIN); csDate.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csTime = wb.createCellStyle(); csTime.setDataFormat(HSSFDataFormat .getBuiltinFormat(exportOptions.getTimeFormat().equals("HH:mm") ? "h:mm" : "h:mm AM/PM")); csTime.setBorderBottom(HSSFCellStyle.BORDER_THIN); csTime.setBorderLeft(HSSFCellStyle.BORDER_THIN); csTime.setBorderRight(HSSFCellStyle.BORDER_THIN); csTime.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFCellStyle csDateTime = wb.createCellStyle(); csDateTime.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"/*opt.getDateTimeFormat()*/)); csDateTime.setBorderBottom(HSSFCellStyle.BORDER_THIN); csDateTime.setBorderLeft(HSSFCellStyle.BORDER_THIN); csDateTime.setBorderRight(HSSFCellStyle.BORDER_THIN); csDateTime.setBorderTop(HSSFCellStyle.BORDER_THIN); // prepare vo getters methods... String methodName = null; String attributeName = null; Hashtable gettersMethods = new Hashtable(); Method[] voMethods = opt.getValueObjectType().getMethods(); for (int i = 0; i < voMethods.length; i++) { methodName = voMethods[i].getName(); if (methodName.startsWith("get")) { attributeName = methodName.substring(3, 4).toLowerCase() + methodName.substring(4); if (opt.getExportAttrColumns().contains(attributeName)) gettersMethods.put(attributeName, voMethods[i]); } } Response response = null; int start = 0; Object value = null; Object vo = null; int type; boolean firstRow = true; if (opt.getTitle() != null && !opt.getTitle().equals("")) { r = s.createRow(rownum); c = r.createCell((short) 0); try { c.setEncoding(HSSFWorkbook.ENCODING_UTF_16); } catch (NoSuchMethodError ex) { } c.setCellValue(opt.getTitle()); c.setCellStyle(csTitle); rownum++; rownum++; } String[] filters = opt.getFilteringConditions(); if (filters != null) { for (int i = 0; i < filters.length; i++) { r = s.createRow(rownum); c = r.createCell((short) 0); try { c.setEncoding(HSSFWorkbook.ENCODING_UTF_16); } catch (NoSuchMethodError ex) { } c.setCellValue(filters[i]); rownum++; } rownum++; } do { response = opt.getGridDataLocator().loadData(GridParams.NEXT_BLOCK_ACTION, start, opt.getFilteredColumns(), opt.getCurrentSortedColumns(), opt.getCurrentSortedVersusColumns(), opt.getValueObjectType(), opt.getOtherGridParams()); if (response.isError()) throw new Exception(response.getErrorMessage()); for (int j = 0; j < ((VOListResponse) response).getRows().size(); j++) { if (firstRow) { firstRow = false; // create the first row... r = s.createRow(rownum++); for (short i = 0; i < opt.getExportColumns().size(); i++) { c = r.createCell(i); try { c.setEncoding(HSSFWorkbook.ENCODING_UTF_16); } catch (NoSuchMethodError ex) { } c.setCellValue(opt.getExportColumns().get(i).toString()); c.setCellStyle(csTitle); } for (int k = 0; k < opt.getTopRows().size(); k++) { // create a row for each top rows... vo = opt.getTopRows().get(k); rownum = appendRow(wb, s, vo, exportOptions, opt, gettersMethods, csText, csBool, csDecNum, csIntNum, csDate, csTime, csDateTime, rownum, 0); } } // create a row vo = ((VOListResponse) response).getRows().get(j); rownum = appendRow(wb, s, vo, exportOptions, opt, gettersMethods, csText, csBool, csDecNum, csIntNum, csDate, csTime, csDateTime, rownum, 1); } start = start + ((VOListResponse) response).getRows().size(); if (!((VOListResponse) response).isMoreRows()) break; } while (rownum < opt.getMaxRows()); for (int j = 0; j < opt.getBottomRows().size(); j++) { // create a row for each bottom rows... vo = opt.getBottomRows().get(j); rownum = appendRow(wb, s, vo, exportOptions, opt, gettersMethods, csText, csBool, csDecNum, csIntNum, csDate, csTime, csDateTime, rownum, 2); } return rownum; }
From source file:org.opentaps.common.util.UtilCommon.java
License:Open Source License
/** * Creates an Excel document with a given column name list, and column data list. * The String objects in the column name list are used as Map keys to look up the corresponding * column header and data. The column data to be exported is a List of Map objects where * the first Map element contains column headers, and the rest has all the column data. * @param workBookName a String object as Excel file name * @param workSheetName a String object as the name of the Excel sheet * @param columnNameList a List of String objects as column names, they usually correspond to entity field names * @param data a List of Map objects to be exported where the first Map element contains column headers, * and the rest has all the column data. * @throws IOException if an error occurs *//*from w w w . jav a 2 s .c o m*/ public static void saveToExcel(final String workBookName, final String workSheetName, final List<String> columnNameList, final List<Map<String, Object>> data) throws IOException { if (StringUtils.isEmpty(workBookName)) { throw new IllegalArgumentException("Argument workBookName can't be empty"); } if (StringUtils.isEmpty(workSheetName)) { throw new IllegalArgumentException("Argument workSheetName can't be empty"); } if (columnNameList == null || columnNameList.isEmpty()) { throw new IllegalArgumentException("Argument columnNameList can't be empty"); } // the data list should have at least one element for the column headers if (data == null || data.isEmpty()) { throw new IllegalArgumentException("Argument data can't be empty"); } FileOutputStream fileOut = new FileOutputStream(new File(workBookName)); assert fileOut != null; HSSFWorkbook workBook = new HSSFWorkbook(); assert workBook != null; HSSFSheet workSheet = workBook.createSheet(workSheetName); assert workSheet != null; // create the header row HSSFRow headerRow = workSheet.createRow(0); assert workSheet != null; HSSFFont headerFont = workBook.createFont(); assert headerFont != null; headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setColor(HSSFColor.BLACK.index); HSSFCellStyle headerCellStyle = workBook.createCellStyle(); assert headerCellStyle != null; headerCellStyle.setFont(headerFont); // the first data list element should always be the column header map Map<String, Object> columnHeaderMap = data.get(0); if (columnHeaderMap != null) { for (short i = 0; i < columnNameList.size(); i++) { HSSFCell cell = headerRow.createCell(i); assert cell != null; cell.setCellStyle(headerCellStyle); Object columnHeaderTitle = columnHeaderMap.get(columnNameList.get(i)); if (columnHeaderTitle != null) { cell.setCellValue(new HSSFRichTextString(columnHeaderTitle.toString())); } } } // create data rows // column data starts from the second element if (data.size() > 1) { // Create the style used for dates. HSSFCellStyle dateCellStyle = workBook.createCellStyle(); String dateFormat = "mm/dd/yyyy hh:mm:ss"; HSSFDataFormat hsfDateFormat = workBook.createDataFormat(); short dateFormatIdx = hsfDateFormat.getFormat(dateFormat); if (dateFormatIdx == -1) { Debug.logWarning("Date format [" + dateFormat + "] could be found or created, try one of the pre-built instead:" + HSSFDataFormat.getBuiltinFormats(), MODULE); } dateCellStyle.setDataFormat(dateFormatIdx); for (int dataRowIndex = 1; dataRowIndex < data.size(); dataRowIndex++) { Map<String, Object> rowDataMap = data.get(dataRowIndex); if (rowDataMap == null) { continue; } HSSFRow dataRow = workSheet.createRow(dataRowIndex); assert dataRow != null; for (short i = 0; i < columnNameList.size(); i++) { HSSFCell cell = dataRow.createCell(i); assert cell != null; Object cellData = rowDataMap.get(columnNameList.get(i)); if (cellData != null) { // Note: dates are actually numeric values in Excel and so the cell need to have // a special style set so it actually displays as a date if (cellData instanceof Calendar) { cell.setCellStyle(dateCellStyle); cell.setCellValue((Calendar) cellData); } else if (cellData instanceof Date) { cell.setCellStyle(dateCellStyle); cell.setCellValue((Date) cellData); } else if (cellData instanceof BigDecimal) { cell.setCellValue(((BigDecimal) cellData).doubleValue()); } else if (cellData instanceof Double) { cell.setCellValue(((Double) cellData).doubleValue()); } else if (cellData instanceof Integer) { cell.setCellValue(((Integer) cellData).doubleValue()); } else if (cellData instanceof BigInteger) { cell.setCellValue(((BigInteger) cellData).doubleValue()); } else { cell.setCellValue(new HSSFRichTextString(cellData.toString())); } } } } } // auto size the column width if (columnHeaderMap != null) { for (short i = 0; i < columnNameList.size(); i++) { workSheet.autoSizeColumn(i); } } // create the Excel file workBook.write(fileOut); fileOut.close(); }
From source file:org.orbeon.oxf.util.XLSUtils.java
License:Open Source License
public static void copyCell(HSSFWorkbook workbook, HSSFCell destination, HSSFCell source) { // Copy cell content destination.setCellType(source.getCellType()); switch (source.getCellType()) { case HSSFCell.CELL_TYPE_BOOLEAN: destination.setCellValue(source.getBooleanCellValue()); break;/*from w w w. j a va 2s . com*/ case HSSFCell.CELL_TYPE_FORMULA: case HSSFCell.CELL_TYPE_STRING: destination.setCellValue(source.getStringCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: destination.setCellValue(source.getNumericCellValue()); break; } // Copy cell style HSSFCellStyle sourceCellStyle = source.getCellStyle(); HSSFCellStyle destinationCellStyle = workbook.createCellStyle(); destinationCellStyle.setAlignment(sourceCellStyle.getAlignment()); destinationCellStyle.setBorderBottom(sourceCellStyle.getBorderBottom()); destinationCellStyle.setBorderLeft(sourceCellStyle.getBorderLeft()); destinationCellStyle.setBorderRight(sourceCellStyle.getBorderRight()); destinationCellStyle.setBorderTop(sourceCellStyle.getBorderTop()); destinationCellStyle.setBottomBorderColor(sourceCellStyle.getBottomBorderColor()); destinationCellStyle.setDataFormat(sourceCellStyle.getDataFormat()); destinationCellStyle.setFillBackgroundColor(sourceCellStyle.getFillForegroundColor()); destinationCellStyle.setFillForegroundColor(sourceCellStyle.getFillForegroundColor()); destinationCellStyle.setFillPattern(sourceCellStyle.getFillPattern()); destinationCellStyle.setFont(workbook.getFontAt(sourceCellStyle.getFontIndex())); destinationCellStyle.setHidden(sourceCellStyle.getHidden()); destinationCellStyle.setIndention(sourceCellStyle.getIndention()); destinationCellStyle.setLeftBorderColor(sourceCellStyle.getLeftBorderColor()); destinationCellStyle.setLocked(sourceCellStyle.getLocked()); destinationCellStyle.setRightBorderColor(sourceCellStyle.getRightBorderColor()); destinationCellStyle.setRotation(sourceCellStyle.getRotation()); destinationCellStyle.setTopBorderColor(sourceCellStyle.getTopBorderColor()); destinationCellStyle.setVerticalAlignment(sourceCellStyle.getVerticalAlignment()); destinationCellStyle.setWrapText(sourceCellStyle.getWrapText()); destination.setCellStyle(destinationCellStyle); }
From source file:org.sakaiproject.assignment.impl.BaseAssignmentService.java
License:Educational Community License
/** * Access and output the grades spreadsheet for the reference, either for an assignment or all assignments in a context. * * @param out// w w w .jav a2 s. com * The outputStream to stream the grades spreadsheet into. * @param ref * The reference, either to a specific assignment, or just to an assignment context. * @return Whether the grades spreadsheet is successfully output. * @throws IdUnusedException * if there is no object with this id. * @throws PermissionException * if the current user is not allowed to access this. */ public boolean getGradesSpreadsheet(final OutputStream out, final String ref) throws IdUnusedException, PermissionException { boolean retVal = false; String typeGradesString = REF_TYPE_GRADES + Entity.SEPARATOR; String context = ref.substring(ref.indexOf(typeGradesString) + typeGradesString.length()); // get site title for display purpose String siteTitle = ""; try { Site s = SiteService.getSite(context); siteTitle = s.getTitle(); } catch (Exception e) { // ignore exception M_log.debug(this + ":getGradesSpreadsheet cannot get site context=" + context + e.getMessage()); } // does current user allowed to grade any assignment? boolean allowGradeAny = false; List assignmentsList = getListAssignmentsForContext(context); for (int iAssignment = 0; !allowGradeAny && iAssignment < assignmentsList.size(); iAssignment++) { if (allowGradeSubmission(((Assignment) assignmentsList.get(iAssignment)).getReference())) { allowGradeAny = true; } } if (!allowGradeAny) { // not permitted to download the spreadsheet return false; } else { int rowNum = 0; HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(WorkbookUtil.createSafeSheetName(siteTitle)); // Create a row and put some cells in it. Rows are 0 based. HSSFRow row = sheet.createRow(rowNum++); row.createCell(0).setCellValue(rb.getString("download.spreadsheet.title")); // empty line row = sheet.createRow(rowNum++); row.createCell(0).setCellValue(""); // site title row = sheet.createRow(rowNum++); row.createCell(0).setCellValue(rb.getString("download.spreadsheet.site") + siteTitle); // download time row = sheet.createRow(rowNum++); row.createCell(0).setCellValue( rb.getString("download.spreadsheet.date") + TimeService.newTime().toStringLocalFull()); // empty line row = sheet.createRow(rowNum++); row.createCell(0).setCellValue(""); HSSFCellStyle style = wb.createCellStyle(); // this is the header row number int headerRowNumber = rowNum; // set up the header cells row = sheet.createRow(rowNum++); int cellNum = 0; // user enterprise id column HSSFCell cell = row.createCell(cellNum++); cell.setCellStyle(style); cell.setCellValue(rb.getString("download.spreadsheet.column.name")); // user name column cell = row.createCell(cellNum++); cell.setCellStyle(style); cell.setCellValue(rb.getString("download.spreadsheet.column.userid")); // starting from this row, going to input user data Iterator assignments = new SortedIterator(assignmentsList.iterator(), new AssignmentComparator("duedate", "true")); // site members excluding those who can add assignments List members = new ArrayList(); // hashmap which stores the Excel row number for particular user HashMap user_row = new HashMap(); List allowAddAnySubmissionUsers = allowAddAnySubmissionUsers(context); for (Iterator iUserIds = new SortedIterator(allowAddAnySubmissionUsers.iterator(), new AssignmentComparator("sortname", "true")); iUserIds.hasNext();) { String userId = (String) iUserIds.next(); try { User u = UserDirectoryService.getUser(userId); members.add(u); // create the column for user first row = sheet.createRow(rowNum); // update user_row Hashtable user_row.put(u.getId(), Integer.valueOf(rowNum)); // increase row rowNum++; // put user displayid and sortname in the first two cells cellNum = 0; row.createCell(cellNum++).setCellValue(u.getSortName()); row.createCell(cellNum).setCellValue(u.getDisplayId()); } catch (Exception e) { M_log.warn(" getGradesSpreadSheet " + e.getMessage() + " userId = " + userId); } } int index = 0; // the grade data portion starts from the third column, since the first two are used for user's display id and sort name while (assignments.hasNext()) { Assignment a = (Assignment) assignments.next(); int assignmentType = a.getContent().getTypeOfGrade(); // for column header, check allow grade permission based on each assignment if (!a.getDraft() && allowGradeSubmission(a.getReference())) { // put in assignment title as the column header rowNum = headerRowNumber; row = sheet.getRow(rowNum++); cellNum = (index + 2); cell = row.createCell(cellNum); // since the first two column is taken by student id and name cell.setCellStyle(style); cell.setCellValue(a.getTitle()); for (int loopNum = 0; loopNum < members.size(); loopNum++) { // prepopulate the column with the "no submission" string row = sheet.getRow(rowNum++); cell = row.createCell(cellNum); cell.setCellType(1); cell.setCellValue(rb.getString("listsub.nosub")); } // begin to populate the column for this assignment, iterating through student list for (Iterator sIterator = getSubmissions(a).iterator(); sIterator.hasNext();) { AssignmentSubmission submission = (AssignmentSubmission) sIterator.next(); String userId = submission.getSubmitterId(); if (a.isGroup()) { User[] _users = submission.getSubmitters(); for (int i = 0; _users != null && i < _users.length; i++) { userId = _users[i].getId(); if (user_row.containsKey(userId)) { // find right row row = sheet.getRow(((Integer) user_row.get(userId)).intValue()); if (submission.getGraded() && submission.getGrade() != null) { // graded and released if (assignmentType == 3) { try { // numeric cell type? String grade = submission.getGradeForUser(userId) == null ? submission.getGradeDisplay() : submission.getGradeForUser(userId); //We get float number no matter the locale it was managed with. NumberFormat nbFormat = FormattedText.getNumberFormat(1, 1, null); float f = nbFormat.parse(grade).floatValue(); // remove the String-based cell first cell = row.getCell(cellNum); row.removeCell(cell); // add number based cell cell = row.createCell(cellNum); cell.setCellType(0); cell.setCellValue(f); style = wb.createCellStyle(); style.setDataFormat(wb.createDataFormat().getFormat("#,##0.0")); cell.setCellStyle(style); } catch (Exception e) { // if the grade is not numeric, let's make it as String type // No need to remove the cell and create a new one, as the existing one is String type. cell = row.getCell(cellNum); cell.setCellType(1); cell.setCellValue(submission.getGradeForUser(userId) == null ? submission.getGradeDisplay() : submission.getGradeForUser(userId)); } } else { // String cell type cell = row.getCell(cellNum); cell.setCellValue(submission.getGradeForUser(userId) == null ? submission.getGradeDisplay() : submission.getGradeForUser(userId)); } } else if (submission.getSubmitted() && submission.getTimeSubmitted() != null) { // submitted, but no grade available yet cell = row.getCell(cellNum); cell.setCellValue(rb.getString("gen.nograd")); } } // if } } else { if (user_row.containsKey(userId)) { // find right row row = sheet.getRow(((Integer) user_row.get(userId)).intValue()); if (submission.getGraded() && submission.getGrade() != null) { // graded and released if (assignmentType == 3) { try { // numeric cell type? String grade = submission.getGradeDisplay(); //We get float number no matter the locale it was managed with. NumberFormat nbFormat = FormattedText.getNumberFormat(1, 1, null); float f = nbFormat.parse(grade).floatValue(); // remove the String-based cell first cell = row.getCell(cellNum); row.removeCell(cell); // add number based cell cell = row.createCell(cellNum); cell.setCellType(0); cell.setCellValue(f); style = wb.createCellStyle(); style.setDataFormat(wb.createDataFormat().getFormat("#,##0.0")); cell.setCellStyle(style); } catch (Exception e) { // if the grade is not numeric, let's make it as String type // No need to remove the cell and create a new one, as the existing one is String type. cell = row.getCell(cellNum); cell.setCellType(1); // Setting grade display instead grade. cell.setCellValue(submission.getGradeDisplay()); } } else { // String cell type cell = row.getCell(cellNum); cell.setCellValue(submission.getGradeDisplay()); } } else if (submission.getSubmitted() && submission.getTimeSubmitted() != null) { // submitted, but no grade available yet cell = row.getCell(cellNum); cell.setCellValue(rb.getString("gen.nograd")); } } // if } } } index++; } // output try { wb.write(out); retVal = true; } catch (IOException e) { M_log.warn(" getGradesSpreadsheet Can not output the grade spread sheet for reference= " + ref); } return retVal; } }