List of usage examples for org.apache.poi.ss.usermodel CreationHelper createDataFormat
DataFormat createDataFormat();
From source file:PlacasClientes.java
private void EnviarExcel(ResultSet rs) throws IOException { String rutaArchivo = System.getProperty("user.home") + "/ejemploExcelJava.xls"; /*Se crea el objeto de tipo File con la ruta del archivo*/ File archivoXLS = new File(rutaArchivo); /*Si el archivo existe se elimina*/ if (archivoXLS.exists()) archivoXLS.delete();/* www . j a v a 2s .c o m*/ /*Se crea el archivo*/ archivoXLS.createNewFile(); /*Se crea el libro de excel usando el objeto de tipo Workbook*/ Workbook libro = new HSSFWorkbook(); CreationHelper createhelper = libro.getCreationHelper(); CellStyle cellStyle = libro.createCellStyle(); cellStyle.setDataFormat(createhelper.createDataFormat().getFormat("dd/mm/yyyy")); CellStyle cellStyle2 = libro.createCellStyle(); /*Se inicializa el flujo de datos con el archivo xls*/ FileOutputStream archivo = new FileOutputStream(archivoXLS); /*Utilizamos la clase Sheet para crear una nueva hoja de trabajo dentro del libro que creamos anteriormente*/ Sheet hoja = libro.createSheet("ClientesPlacas"); Font fuente = libro.createFont(); fuente.setFontHeightInPoints((short) 13); fuente.setFontName("Arial"); fuente.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); Font fuente2 = libro.createFont(); fuente.setFontHeightInPoints((short) 13); fuente.setFontName("Arial"); //fuente.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); Row Enc = hoja.createRow(2); Cell celda = Enc.createCell(0); celda.setCellValue("PLACA DE VEHICULOS POR CLIENTES"); }
From source file:biz.webgate.dominoext.poi.component.kernel.simpleviewexport.WorkbooklExportProcessor.java
License:Apache License
public void process2HTTP(ExportModel expModel, UISimpleViewExport uis, HttpServletResponse hsr, DateTimeHelper dth) {/*from w ww . j a va 2 s . c o m*/ try { String strFileName = uis.getDownloadFileName(); Workbook wbCurrent = null; if (strFileName.toLowerCase().endsWith(".xlsx")) { wbCurrent = new XSSFWorkbook(); } else { wbCurrent = new HSSFWorkbook(); } HashMap<String, CellStyle> hsCS = new HashMap<String, CellStyle>(); CreationHelper cr = wbCurrent.getCreationHelper(); CellStyle csDate = wbCurrent.createCellStyle(); csDate.setDataFormat(cr.createDataFormat().getFormat(dth.getDFDate().toPattern())); CellStyle csDateTime = wbCurrent.createCellStyle(); csDateTime.setDataFormat(cr.createDataFormat().getFormat(dth.getDFDateTime().toPattern())); CellStyle csTime = wbCurrent.createCellStyle(); csTime.setDataFormat(cr.createDataFormat().getFormat(dth.getDFTime().toPattern())); hsCS.put("DATE", csDate); hsCS.put("TIME", csTime); hsCS.put("DATETIME", csDateTime); Sheet sh = wbCurrent.createSheet("SVE Export"); int nRowCount = 0; // BUILDING HEADER if (uis.isIncludeHeader()) { Row rw = sh.createRow(nRowCount); int nCol = 0; for (ExportColumn expColumn : expModel.getColumns()) { rw.createCell(nCol).setCellValue(expColumn.getColumnName()); nCol++; } nRowCount++; } // Processing Values for (ExportDataRow expRow : expModel.getRows()) { Row rw = sh.createRow(nRowCount); int nCol = 0; for (ExportColumn expColumn : expModel.getColumns()) { Cell clCurrent = rw.createCell(nCol); setCellValue(expRow.getValue(expColumn.getPosition()), clCurrent, expColumn, hsCS); nCol++; } nRowCount++; } for (int nCol = 0; nCol < expModel.getColumns().size(); nCol++) { sh.autoSizeColumn(nCol); } if (strFileName.toLowerCase().endsWith(".xlsx")) { hsr.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); } else if (strFileName.toLowerCase().endsWith("xls")) { hsr.setContentType("application/vnd.ms-excel"); } else { hsr.setContentType("application/octet-stream"); } hsr.addHeader("Content-disposition", "inline; filename=\"" + strFileName + "\""); OutputStream os = hsr.getOutputStream(); ByteArrayOutputStream bos = new ByteArrayOutputStream(); wbCurrent.write(bos); bos.writeTo(os); os.close(); } catch (Exception e) { ErrorPageBuilder.getInstance().processError(hsr, "Error during SVE-Generation (Workbook Export)", e); } }
From source file:bo.com.offercruzmail.utils.HojaExcelHelper.java
public void setValorCelda(int rowIndex, int colIndex, Date valor) { Cell celda = getCelda(rowIndex, colIndex); if (valor != null) { celda.setCellValue(valor);//w ww . j a v a 2s .c om CreationHelper createHelper = hoja.getWorkbook().getCreationHelper(); CellStyle estiloFecha = hoja.getWorkbook().createCellStyle(); estiloFecha.setDataFormat(createHelper.createDataFormat().getFormat("d/m/yy")); celda.setCellStyle(estiloFecha); } }
From source file:cn.edu.pku.lib.dataverse.UsageLogStatisPage.java
private File generateExcelDownloadLogFile() { //excel workbook Workbook wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet(WorkbookUtil.createSafeSheetName("File Download Statistic")); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss")); Locale local = FacesContext.getCurrentInstance().getViewRoot().getLocale(); //generate header String heads = ResourceBundle .getBundle("Bundle", FacesContext.getCurrentInstance().getViewRoot().getLocale()) .getString("log.filedownload.header"); String[] array = heads.split(","); Row row = sheet.createRow(0);//from www.j a v a2 s. co m for (int k = 0; k < array.length; k++) { Cell cell = row.createCell(k); cell.setCellValue(array[k]); } //generate logs SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); final long size = 100L; UsageLogSearchQuery query = queryForFile.clone(); query.setSize(size); query.setDateHistogramInterval(null); UsageLogSearchResult searchResult = null; int i = 0; int j = 1; Cell cell; do { query.setFrom(i * size); searchResult = usageLogSearchService.search(query); List<EventLog> logs = searchResult.getEventLogs(); for (EventLog log : logs) { row = sheet.createRow(j); AuthenticatedUser user; if (log.getUserId().equals(":guest") || (user = authenticationServiceBean.getAuthenticatedUser(log.getUserId())) == null) { cell = row.createCell(0); cell.setCellValue(log.getDate()); cell.setCellStyle(cellStyle); cell = row.createCell(1); cell.setCellValue(log.getIp()); cell = row.createCell(2); cell.setCellValue(log.getContinent()); cell = row.createCell(3); cell.setCellValue(log.getCountry()); cell = row.createCell(4); cell.setCellValue(log.getSubdivision()); cell = row.createCell(5); cell.setCellValue(log.getCity()); cell = row.createCell(6); cell.setCellValue(log.getUserId()); cell = row.createCell(7); cell.setCellValue(log.getUserName()); cell = row.createCell(8); cell.setCellValue(log.getAffiliation()); cell = row.createCell(9); cell.setCellValue(log.getPosition()); cell = row.createCell(10); cell.setCellValue(fileId2Dataset.get(log.getDatafileId()).getDisplayName(local)); cell = row.createCell(11); cell.setCellValue(fileId2DataFile.get(log.getDatafileId()).getLabel()); } else { if (user.isBuiltInUser()) { BuiltinUser b = builtinUserService.findByUserName(user.getUserIdentifier()); cell = row.createCell(0); cell.setCellValue(log.getDate()); cell.setCellStyle(cellStyle); cell = row.createCell(1); cell.setCellValue(log.getIp()); cell = row.createCell(2); cell.setCellValue(log.getContinent()); cell = row.createCell(3); cell.setCellValue(log.getCountry()); cell = row.createCell(4); cell.setCellValue(log.getSubdivision()); cell = row.createCell(5); cell.setCellValue(log.getCity()); cell = row.createCell(6); cell.setCellValue(log.getUserId()); cell = row.createCell(7); cell.setCellValue(log.getUserName()); cell = row.createCell(8); cell.setCellValue(b.getAffiliation()); cell = row.createCell(9); cell.setCellValue(b.getPosition()); cell = row.createCell(10); cell.setCellValue(fileId2Dataset.get(log.getDatafileId()).getDisplayName(local)); cell = row.createCell(11); cell.setCellValue(fileId2DataFile.get(log.getDatafileId()).getLabel()); cell = row.createCell(12); cell.setCellValue(b.getDepartment()); cell = row.createCell(13); cell.setCellValue(b.getEmail()); cell = row.createCell(14); cell.setCellValue(b.getSpeciality()); cell = row.createCell(15); cell.setCellValue(b.getResearchInterest()); cell = row.createCell(16); cell.setCellValue(b.getGender()); cell = row.createCell(17); cell.setCellValue(b.getEducation()); cell = row.createCell(18); cell.setCellValue(b.getProfessionalTitle()); cell = row.createCell(19); cell.setCellValue(b.getSupervisor()); cell = row.createCell(20); cell.setCellValue(b.getCertificateType()); cell = row.createCell(21); cell.setCellValue(b.getCertificateNumber()); cell = row.createCell(22); cell.setCellValue(b.getOfficePhone()); cell = row.createCell(23); cell.setCellValue(b.getCellphone()); cell = row.createCell(24); cell.setCellValue(b.getOtherEmail()); cell = row.createCell(25); cell.setCellValue(b.getCountry()); cell = row.createCell(26); cell.setCellValue(b.getProvince()); cell = row.createCell(27); cell.setCellValue(b.getCity()); cell = row.createCell(28); cell.setCellValue(b.getAddress()); cell = row.createCell(29); cell.setCellValue(b.getZipCode()); cell = row.createCell(30); cell.setCellValue("Built In"); } else if (user.isPKUIAAAUser()) { PKUIAAAUser p = pkuIAAAUserService.findByUserName(user.getUserIdentifier()); cell = row.createCell(0); cell.setCellValue(log.getDate()); cell.setCellStyle(cellStyle); cell = row.createCell(1); cell.setCellValue(log.getIp()); cell = row.createCell(2); cell.setCellValue(log.getContinent()); cell = row.createCell(3); cell.setCellValue(log.getCountry()); cell = row.createCell(4); cell.setCellValue(log.getSubdivision()); cell = row.createCell(5); cell.setCellValue(log.getCity()); cell = row.createCell(6); cell.setCellValue(log.getUserId()); cell = row.createCell(7); cell.setCellValue(log.getUserName()); cell = row.createCell(8); cell.setCellValue(p.getAffiliation()); cell = row.createCell(9); cell.setCellValue(p.getPosition()); cell = row.createCell(10); cell.setCellValue(fileId2Dataset.get(log.getDatafileId()).getDisplayName(local)); cell = row.createCell(11); cell.setCellValue(fileId2DataFile.get(log.getDatafileId()).getLabel()); cell = row.createCell(12); cell.setCellValue(p.getDepartment()); cell = row.createCell(13); cell.setCellValue(p.getEmail()); cell = row.createCell(14); cell.setCellValue(p.getSpeciality()); cell = row.createCell(15); cell.setCellValue(p.getResearchInterest()); cell = row.createCell(16); cell.setCellValue(p.getGender()); cell = row.createCell(17); cell.setCellValue(p.getEducation()); cell = row.createCell(18); cell.setCellValue(p.getProfessionalTitle()); cell = row.createCell(19); cell.setCellValue(p.getSupervisor()); cell = row.createCell(20); cell.setCellValue(p.getCertificateType()); cell = row.createCell(21); cell.setCellValue(p.getCertificateNumber()); cell = row.createCell(22); cell.setCellValue(p.getOfficePhone()); cell = row.createCell(23); cell.setCellValue(p.getCellphone()); cell = row.createCell(24); cell.setCellValue(p.getOtherEmail()); cell = row.createCell(25); cell.setCellValue(p.getCountry()); cell = row.createCell(26); cell.setCellValue(p.getProvince()); cell = row.createCell(27); cell.setCellValue(p.getCity()); cell = row.createCell(28); cell.setCellValue(p.getAddress()); cell = row.createCell(29); cell.setCellValue(p.getZipCode()); cell = row.createCell(30); cell.setCellValue("PKU IAAA"); } } j++; } i++; } while (i < searchResult.getPages()); String filesRootDirectory = System.getProperty("dataverse.files.directory"); if (filesRootDirectory == null || filesRootDirectory.equals("")) { filesRootDirectory = "/tmp/files"; } File file = new File(filesRootDirectory + "/temp/" + UUID.randomUUID()); try (FileOutputStream out = new FileOutputStream(file)) { wb.write(out); return file; } catch (IOException ioe) { logger.log(Level.SEVERE, null, ioe); } if (file.exists()) { file.delete(); } return null; }
From source file:cn.edu.pku.lib.dataverse.UsageLogStatisPage.java
private File generateExcelRequestJoinGroupLogFile() { //excel workbook Workbook wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet(WorkbookUtil.createSafeSheetName("User Join Group Statistic")); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss")); Locale locale = FacesContext.getCurrentInstance().getViewRoot().getLocale(); //generate header String heads = ResourceBundle.getBundle("Bundle", locale).getString("log.requestjoingroup.header"); String[] array = heads.split(","); Row row = sheet.createRow(0);/*from ww w. java 2s .com*/ for (int k = 0; k < array.length; k++) { Cell cell = row.createCell(k); cell.setCellValue(array[k]); } //generate logs SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); final long size = 100L; UsageLogSearchQuery query = queryForGroup.clone(); query.setSize(size); query.setDateHistogramInterval(null); UsageLogSearchResult searchResult = null; int i = 0; int j = 1; Cell cell; do { query.setFrom(i * size); searchResult = usageLogSearchService.search(query); List<EventLog> logs = searchResult.getEventLogs(); for (EventLog log : logs) { row = sheet.createRow(j); AuthenticatedUser user; if (log.getUserId().equals(":guest") || (user = authenticationServiceBean.getAuthenticatedUser(log.getUserId())) == null) { cell = row.createCell(0); cell.setCellValue(log.getDate()); cell.setCellStyle(cellStyle); cell = row.createCell(1); cell.setCellValue(log.getIp()); cell = row.createCell(2); cell.setCellValue(log.getContinent()); cell = row.createCell(3); cell.setCellValue(log.getCountry()); cell = row.createCell(4); cell.setCellValue(log.getSubdivision()); cell = row.createCell(5); cell.setCellValue(log.getCity()); cell = row.createCell(6); cell.setCellValue(log.getUserId()); cell = row.createCell(7); cell.setCellValue(log.getUserName()); cell = row.createCell(8); cell.setCellValue(log.getAffiliation()); cell = row.createCell(9); cell.setCellValue(log.getPosition()); cell = row.createCell(10); cell.setCellValue(getDisplayString(log.getEventType())); cell = row.createCell(11); cell.setCellValue(groupId2Group.get(log.getGroupId()).getDisplayName()); } else { if (user.isBuiltInUser()) { BuiltinUser b = builtinUserService.findByUserName(user.getUserIdentifier()); cell = row.createCell(0); cell.setCellValue(log.getDate()); cell.setCellStyle(cellStyle); cell = row.createCell(1); cell.setCellValue(log.getIp()); cell = row.createCell(2); cell.setCellValue(log.getContinent()); cell = row.createCell(3); cell.setCellValue(log.getCountry()); cell = row.createCell(4); cell.setCellValue(log.getSubdivision()); cell = row.createCell(5); cell.setCellValue(log.getCity()); cell = row.createCell(6); cell.setCellValue(log.getUserId()); cell = row.createCell(7); cell.setCellValue(log.getUserName()); cell = row.createCell(8); cell.setCellValue(b.getAffiliation()); cell = row.createCell(9); cell.setCellValue(b.getPosition()); cell = row.createCell(10); cell.setCellValue(getDisplayString(log.getEventType())); cell = row.createCell(11); cell.setCellValue(groupId2Group.get(log.getGroupId()).getDisplayName()); cell = row.createCell(12); cell.setCellValue(b.getDepartment()); cell = row.createCell(13); cell.setCellValue(b.getEmail()); cell = row.createCell(14); cell.setCellValue(b.getSpeciality()); cell = row.createCell(15); cell.setCellValue(b.getResearchInterest()); cell = row.createCell(16); cell.setCellValue(b.getGender()); cell = row.createCell(17); cell.setCellValue(b.getEducation()); cell = row.createCell(18); cell.setCellValue(b.getProfessionalTitle()); cell = row.createCell(19); cell.setCellValue(b.getSupervisor()); cell = row.createCell(20); cell.setCellValue(b.getCertificateType()); cell = row.createCell(21); cell.setCellValue(b.getCertificateNumber()); cell = row.createCell(22); cell.setCellValue(b.getOfficePhone()); cell = row.createCell(23); cell.setCellValue(b.getCellphone()); cell = row.createCell(24); cell.setCellValue(b.getOtherEmail()); cell = row.createCell(25); cell.setCellValue(b.getCountry()); cell = row.createCell(26); cell.setCellValue(b.getProvince()); cell = row.createCell(27); cell.setCellValue(b.getCity()); cell = row.createCell(28); cell.setCellValue(b.getAddress()); cell = row.createCell(29); cell.setCellValue(b.getZipCode()); cell = row.createCell(30); cell.setCellValue("Built In"); } else if (user.isPKUIAAAUser()) { PKUIAAAUser p = pkuIAAAUserService.findByUserName(user.getUserIdentifier()); cell = row.createCell(0); cell.setCellValue(log.getDate()); cell.setCellStyle(cellStyle); cell = row.createCell(1); cell.setCellValue(log.getIp()); cell = row.createCell(2); cell.setCellValue(log.getContinent()); cell = row.createCell(3); cell.setCellValue(log.getCountry()); cell = row.createCell(4); cell.setCellValue(log.getSubdivision()); cell = row.createCell(5); cell.setCellValue(log.getCity()); cell = row.createCell(6); cell.setCellValue(log.getUserId()); cell = row.createCell(7); cell.setCellValue(log.getUserName()); cell = row.createCell(8); cell.setCellValue(p.getAffiliation()); cell = row.createCell(9); cell.setCellValue(p.getPosition()); cell = row.createCell(10); cell.setCellValue(getDisplayString(log.getEventType())); cell = row.createCell(11); cell.setCellValue(groupId2Group.get(log.getGroupId()).getDisplayName()); cell = row.createCell(12); cell.setCellValue(p.getDepartment()); cell = row.createCell(13); cell.setCellValue(p.getEmail()); cell = row.createCell(14); cell.setCellValue(p.getSpeciality()); cell = row.createCell(15); cell.setCellValue(p.getResearchInterest()); cell = row.createCell(16); cell.setCellValue(p.getGender()); cell = row.createCell(17); cell.setCellValue(p.getEducation()); cell = row.createCell(18); cell.setCellValue(p.getProfessionalTitle()); cell = row.createCell(19); cell.setCellValue(p.getSupervisor()); cell = row.createCell(20); cell.setCellValue(p.getCertificateType()); cell = row.createCell(21); cell.setCellValue(p.getCertificateNumber()); cell = row.createCell(22); cell.setCellValue(p.getOfficePhone()); cell = row.createCell(23); cell.setCellValue(p.getCellphone()); cell = row.createCell(24); cell.setCellValue(p.getOtherEmail()); cell = row.createCell(25); cell.setCellValue(p.getCountry()); cell = row.createCell(26); cell.setCellValue(p.getProvince()); cell = row.createCell(27); cell.setCellValue(p.getCity()); cell = row.createCell(28); cell.setCellValue(p.getAddress()); cell = row.createCell(29); cell.setCellValue(p.getZipCode()); cell = row.createCell(30); cell.setCellValue("PKU IAAA"); } } j++; } i++; } while (i < searchResult.getPages()); String filesRootDirectory = System.getProperty("dataverse.files.directory"); if (filesRootDirectory == null || filesRootDirectory.equals("")) { filesRootDirectory = "/tmp/files"; } File file = new File(filesRootDirectory + "/temp/" + UUID.randomUUID()); try (FileOutputStream out = new FileOutputStream(file)) { wb.write(out); return file; } catch (IOException ioe) { logger.log(Level.SEVERE, null, ioe); } if (file.exists()) { file.delete(); } return null; }
From source file:com.adobe.acs.commons.mcp.impl.ProcessErrorReportExcelServlet.java
License:Apache License
@SuppressWarnings("squid:S3776") protected Workbook createSpreadsheet(ManagedProcess report) { Workbook wb = new XSSFWorkbook(); String name = report.getName(); for (char ch : new char[] { '\\', '/', '*', '[', ']', ':', '?' }) { name = StringUtils.remove(name, ch); }/* w w w . j a v a 2 s.com*/ Sheet sheet = wb.createSheet(name); sheet.createFreezePane(0, 1, 0, 1); Row headerRow = sheet.createRow(0); CellStyle headerStyle = createHeaderStyle(wb); CellStyle dateStyle = wb.createCellStyle(); CreationHelper createHelper = wb.getCreationHelper(); dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyy/mm/dd h:mm:ss")); for (String columnName : Arrays.asList("Time", "Path", "Error", "Stack trace")) { Cell headerCell = headerRow.createCell(headerRow.getPhysicalNumberOfCells()); headerCell.setCellValue(columnName); headerCell.setCellStyle(headerStyle); } Collection<ArchivedProcessFailure> rows = report.getReportedErrorsList(); //make rows, don't forget the header row for (ArchivedProcessFailure error : rows) { Row row = sheet.createRow(sheet.getPhysicalNumberOfRows()); Cell c; c = row.createCell(0); c.setCellValue(error.time); c.setCellStyle(dateStyle); c = row.createCell(1); c.setCellValue(error.nodePath); c = row.createCell(2); c.setCellValue(error.error); c = row.createCell(3); c.setCellValue(error.stackTrace); } autosize(sheet, 4); sheet.setAutoFilter(new CellRangeAddress(0, 1 + rows.size(), 0, 3)); return wb; }
From source file:com.AllenBarr.CallSheetGenerator.Generator.java
License:Open Source License
public int generateSheet(File file, Contributor contrib) { //create workbook file final String fileName = file.toString(); final Workbook wb; if (fileName.endsWith(".xlsx")) { wb = new XSSFWorkbook(); } else if (fileName.endsWith(".xls")) { wb = new HSSFWorkbook(); } else {//w w w .j ava 2s . co m return 1; } //create sheet final Sheet sheet = wb.createSheet("Call Sheet"); final Header header = sheet.getHeader(); header.setCenter("Anderson for Iowa Call Sheet"); //add empty cells final Row[] row = new Row[22 + contrib.getDonationsLength()]; final Cell[][] cell = new Cell[6][22 + contrib.getDonationsLength()]; for (int i = 0; i < (22 + contrib.getDonationsLength()); i++) { row[i] = sheet.createRow((short) i); for (int j = 0; j < 6; j++) { cell[j][i] = row[i].createCell(j); } } //populate cells with data //column 1 cell[0][0].setCellValue(contrib.getName()); cell[0][3].setCellValue("Sex:"); cell[0][4].setCellValue("Party:"); cell[0][5].setCellValue("Phone #:"); cell[0][6].setCellValue("Home #:"); cell[0][7].setCellValue("Cell #:"); cell[0][8].setCellValue("Work #:"); cell[0][10].setCellValue("Email:"); cell[0][12].setCellValue("Employer:"); cell[0][13].setCellValue("Occupation:"); cell[0][15].setCellValue("Past Contact:"); cell[0][17].setCellValue("Notes:"); cell[0][21].setCellValue("Contribution History:"); //column 2 cell[1][3].setCellValue(contrib.getSex()); cell[1][4].setCellValue(contrib.getParty()); cell[1][5].setCellValue(contrib.getPhone()); cell[1][6].setCellValue(contrib.getHomePhone()); cell[1][7].setCellValue(contrib.getCellPhone()); cell[1][8].setCellValue(contrib.getWorkPhone()); cell[1][9].setCellValue("x" + contrib.getWorkExtension()); cell[1][10].setCellValue(contrib.getEmail()); cell[1][12].setCellValue(contrib.getEmployer()); cell[1][13].setCellValue(contrib.getOccupation()); cell[1][17].setCellValue(contrib.getNotes()); //column 4 cell[3][3].setCellValue("Salutation:"); cell[3][4].setCellValue("Age:"); cell[3][5].setCellValue("Spouse:"); cell[3][7].setCellValue("Address:"); cell[3][10].setCellValue("TARGET:"); //column 5 cell[4][0].setCellValue("VANID:"); cell[4][3].setCellValue(contrib.getSalutation()); cell[4][4].setCellValue(contrib.getAge()); cell[4][5].setCellValue(contrib.getSpouse()); cell[4][7].setCellValue(contrib.getStreetAddress()); cell[4][8].setCellValue(contrib.getCity() + ", " + contrib.getState() + " " + contrib.getZip()); //column 6 cell[5][0].setCellValue(contrib.getVANID()); //contribution cells for (int i = 0; i < contrib.getDonationsLength(); i++) { cell[0][i + 22].setCellValue(contrib.getDonation(i).getDonationDate()); cell[1][i + 22].setCellValue(contrib.getDonation(i).getRecipient()); cell[5][i + 22].setCellValue(contrib.getDonation(i).getAmount()); } //format cells //Name cell sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3)); final CellStyle leftBoldUnderline14Style = wb.createCellStyle(); final Font boldUnderline14Font = wb.createFont(); boldUnderline14Font.setBoldweight(Font.BOLDWEIGHT_BOLD); boldUnderline14Font.setUnderline(Font.U_SINGLE); boldUnderline14Font.setFontHeightInPoints((short) 14); boldUnderline14Font.setFontName("Garamond"); leftBoldUnderline14Style.setFont(boldUnderline14Font); leftBoldUnderline14Style.setAlignment(CellStyle.ALIGN_LEFT); cell[0][0].setCellStyle(leftBoldUnderline14Style); //field name cells final CellStyle rightBold10Style = wb.createCellStyle(); final Font bold10Font = wb.createFont(); bold10Font.setBoldweight(Font.BOLDWEIGHT_BOLD); bold10Font.setFontHeightInPoints((short) 10); bold10Font.setFontName("Garamond"); rightBold10Style.setFont(bold10Font); rightBold10Style.setAlignment(CellStyle.ALIGN_RIGHT); for (int i = 3; i < 22; i++) { cell[0][i].setCellStyle(rightBold10Style); } sheet.addMergedRegion(new CellRangeAddress(21, 21, 0, 1)); for (int i = 3; i < 11; i++) { cell[3][i].setCellStyle(rightBold10Style); } cell[4][0].setCellStyle(rightBold10Style); //field content cells final CellStyle left10Style = wb.createCellStyle(); final Font garamond10Font = wb.createFont(); garamond10Font.setFontHeightInPoints((short) 10); garamond10Font.setFontName("Garamond"); left10Style.setFont(garamond10Font); left10Style.setAlignment(CellStyle.ALIGN_LEFT); for (int i = 3; i < 5; i++) { cell[1][i].setCellStyle(left10Style); } //phone number cells final CellStyle phoneStyle = wb.createCellStyle(); phoneStyle.setFont(garamond10Font); phoneStyle.setAlignment(CellStyle.ALIGN_LEFT); final CreationHelper createHelper = wb.getCreationHelper(); phoneStyle.setDataFormat(createHelper.createDataFormat().getFormat("[<=9999999]###-####;(###) ###-####")); for (int i = 5; i < 9; i++) { cell[1][i].setCellStyle(phoneStyle); sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 2)); } cell[1][9].setCellStyle(left10Style); //email through past contact for (int i = 10; i < 16; i++) { cell[1][i].setCellStyle(left10Style); } //notes CellStyle noteStyle = wb.createCellStyle(); noteStyle.cloneStyleFrom(left10Style); noteStyle.setWrapText(true); cell[1][17].setCellStyle(noteStyle); //column E for (int i = 3; i < 11; i++) { cell[4][i].setCellStyle(left10Style); } //VanID Cell final CellStyle right10Style = wb.createCellStyle(); right10Style.setFont(garamond10Font); right10Style.setAlignment(CellStyle.ALIGN_RIGHT); cell[5][0].setCellStyle(right10Style); //Notes cell sheet.addMergedRegion(new CellRangeAddress(17, 19, 1, 5)); //contribution cells final CellStyle date10Style = wb.createCellStyle(); date10Style.setFont(garamond10Font); date10Style.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy")); date10Style.setBorderBottom(CellStyle.BORDER_THIN); date10Style.setBorderTop(CellStyle.BORDER_THIN); date10Style.setBorderLeft(CellStyle.BORDER_THIN); date10Style.setBorderRight(CellStyle.BORDER_THIN); final CellStyle contributionStyle = wb.createCellStyle(); contributionStyle.cloneStyleFrom(left10Style); contributionStyle.setBorderBottom(CellStyle.BORDER_THIN); contributionStyle.setBorderTop(CellStyle.BORDER_THIN); contributionStyle.setBorderLeft(CellStyle.BORDER_THIN); contributionStyle.setBorderRight(CellStyle.BORDER_THIN); final CellStyle money10Style = wb.createCellStyle(); money10Style.setFont(garamond10Font); money10Style.setDataFormat( createHelper.createDataFormat().getFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)")); money10Style.setBorderBottom(CellStyle.BORDER_THIN); money10Style.setBorderTop(CellStyle.BORDER_THIN); money10Style.setBorderLeft(CellStyle.BORDER_THIN); money10Style.setBorderRight(CellStyle.BORDER_THIN); for (int i = 22; i < 22 + contrib.getDonationsLength(); i++) { cell[0][i].setCellStyle(date10Style); cell[1][i].setCellStyle(contributionStyle); cell[2][i].setCellStyle(contributionStyle); cell[3][i].setCellStyle(contributionStyle); cell[4][i].setCellStyle(contributionStyle); sheet.addMergedRegion(new CellRangeAddress(i, i, 1, 4)); cell[5][i].setCellStyle(money10Style); } //resize columns sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); try { FileOutputStream fileOut = new FileOutputStream(file); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { return 1; } catch (IOException ex) { return 1; } return 0; }
From source file:com.anritsu.mcrepositorymanager.utils.GenerateRSS.java
public String getRSS() { FileInputStream file = null;/* w w w . j av a2s . co m*/ String rssFileName = rssTemplateFileName.replaceAll("template", mcVersion); try { file = new FileInputStream( new File(Configuration.getInstance().getRssTemplatePath() + rssTemplateFileName)); XSSFWorkbook workbook = new XSSFWorkbook(file); workbook.setSheetName(workbook.getSheetIndex("MC X.X.X"), "MC " + mcVersion); XSSFSheet sheet = workbook.getSheet("MC " + mcVersion); CreationHelper createHelper = workbook.getCreationHelper(); Cell cell = null; // Update the sheet title cell = sheet.getRow(0).getCell(0); cell.setCellValue(cell.getStringCellValue().replaceAll("template", mcVersion)); XSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); XSSFCellStyle hlinkstyle = workbook.createCellStyle(); XSSFFont hlinkfont = workbook.createFont(); hlinkfont.setUnderline(XSSFFont.U_SINGLE); hlinkfont.setColor(HSSFColor.BLUE.index); hlinkstyle.setFont(hlinkfont); hlinkstyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); hlinkstyle.setBorderTop(XSSFCellStyle.BORDER_THIN); hlinkstyle.setBorderRight(XSSFCellStyle.BORDER_THIN); hlinkstyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); XSSFCellStyle dateCellStyle = workbook.createCellStyle(); dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd-MMMM-yyyy")); dateCellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); dateCellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN); dateCellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN); dateCellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN); // Populate the table int rowCount = 1; for (RecommendedMcPackage rmcp : sortedMcPackages) { if (rmcp.getRecommendedVersion() != null && rmcp.isShowInTable()) { Row row = sheet.createRow(rowCount + 1); rowCount++; cell = row.createCell(0); cell.setCellValue(rmcp.getTier().replaceAll("Anritsu/MasterClaw/", "")); cell.setCellStyle(cellStyle); cell = row.createCell(1); cell.setCellValue(rmcp.getGroup()); cell.setCellStyle(cellStyle); cell = row.createCell(2); cell.setCellValue(rmcp.getPackageName()); UrlValidator defaultValidator = new UrlValidator(UrlValidator.ALLOW_LOCAL_URLS); if (rmcp.getRecommendedVersion().getReleaseNote() != null && defaultValidator.isValid(rmcp.getRecommendedVersion().getReleaseNote())) { XSSFHyperlink releaseNotelink = (XSSFHyperlink) createHelper .createHyperlink(Hyperlink.LINK_URL); releaseNotelink.setAddress(rmcp.getRecommendedVersion().getReleaseNote()); //System.out.println("Inside(if) RN: " + rmcp.getRecommendedVersion().getReleaseNote() + " Valid: " + defaultValidator.isValid(rmcp.getRecommendedVersion().getReleaseNote())); cell.setHyperlink(releaseNotelink); } cell.setCellStyle(hlinkstyle); cell = row.createCell(3); cell.setCellValue(rmcp.getRecommendedVersion().getPackageVersion()); cell.setCellStyle(cellStyle); cell = row.createCell(4); cell.setCellValue(rmcp.getAvailability()); cell.setCellStyle(cellStyle); cell = row.createCell(5); String customers = Arrays.asList(rmcp.getRecommendedVersion().getCustomerList().toArray()) .toString(); if (customers.equalsIgnoreCase("[All]")) { customers = ""; } cell.setCellValue(customers); cell.setCellStyle(cellStyle); cell = row.createCell(6); cell.setCellValue(rmcp.getRecommendedVersion().getRisk()); cell.setCellStyle(cellStyle); cell = row.createCell(7); cell.setCellValue(rmcp.getPackageName()); XSSFHyperlink link = (XSSFHyperlink) createHelper.createHyperlink(Hyperlink.LINK_URL); link.setAddress(rmcp.getRecommendedVersion().getDownloadLinks().iterator().next()); cell.setHyperlink((XSSFHyperlink) link); cell.setCellStyle(hlinkstyle); cell = row.createCell(8); cell.setCellValue((rmcp.getRecommendedVersion() != null && rmcp.getRecommendedVersion().isLessRecommended()) ? "#" : ""); cell.setCellStyle(cellStyle); cell = row.createCell(9); cell.setCellValue(rmcp.getRecommendedVersion().getNotes()); cell.setCellStyle(cellStyle); StringBuilder newFeatures = new StringBuilder(); for (MCPackageActivities mcpa : rmcp.getRecommendedVersion().getActivities()) { if (!mcpa.getActivityType().equalsIgnoreCase("epr")) { newFeatures.append(mcpa.getActivityType() + " " + mcpa.getActivityId() + "; "); } } cell = row.createCell(10); cell.setCellValue(newFeatures.toString()); cell.setCellStyle(cellStyle); cell = row.createCell(11); cell.setCellValue(rmcp.getRecommendedVersion().getReleaseDate()); cell.setCellStyle(dateCellStyle); } sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); sheet.autoSizeColumn(3); sheet.autoSizeColumn(4); sheet.autoSizeColumn(6); sheet.autoSizeColumn(7); sheet.autoSizeColumn(8); sheet.autoSizeColumn(11); } FileOutputStream outFile = new FileOutputStream( new File(Configuration.getInstance().getRssTemplatePath() + rssFileName)); workbook.write(outFile); outFile.close(); return Configuration.getInstance().getRssTemplatePath() + rssFileName; } catch (FileNotFoundException ex) { Logger.getLogger(GenerateRSS.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(GenerateRSS.class.getName()).log(Level.SEVERE, null, ex); } finally { try { file.close(); } catch (IOException ex) { Logger.getLogger(GenerateRSS.class.getName()).log(Level.SEVERE, null, ex); } } return ""; }
From source file:com.asakusafw.testdata.generator.excel.WorkbookInfo.java
License:Apache License
/** * Creates a new instance./*from w ww . j av a 2 s .c om*/ * @param workbook target workbook * @throws IllegalArgumentException if some parameters were {@code null} */ public WorkbookInfo(Workbook workbook) { if (workbook == null) { throw new IllegalArgumentException("workbook must not be null"); //$NON-NLS-1$ } this.workbook = workbook; this.version = WorkbookGenerator.getSpreadsheetVersion(workbook); Font font = workbook.createFont(); // font.setFontName("..."); commonStyle = workbook.createCellStyle(); commonStyle.setFont(font); commonStyle.setBorderTop(CellStyle.BORDER_THIN); commonStyle.setBorderBottom(CellStyle.BORDER_THIN); commonStyle.setBorderLeft(CellStyle.BORDER_THIN); commonStyle.setBorderRight(CellStyle.BORDER_THIN); titleStyle = workbook.createCellStyle(); titleStyle.cloneStyleFrom(commonStyle); titleStyle.setLocked(true); titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); titleStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex()); titleStyle.setAlignment(CellStyle.ALIGN_CENTER); lockedStyle = workbook.createCellStyle(); lockedStyle.cloneStyleFrom(commonStyle); lockedStyle.setLocked(true); lockedStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); lockedStyle.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex()); lockedStyle.setAlignment(CellStyle.ALIGN_CENTER); optionsStyle = workbook.createCellStyle(); optionsStyle.cloneStyleFrom(commonStyle); optionsStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); optionsStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); CreationHelper helper = workbook.getCreationHelper(); DataFormat df = helper.createDataFormat(); dataStyle = workbook.createCellStyle(); dataStyle.cloneStyleFrom(commonStyle); dateDataStyle = workbook.createCellStyle(); dateDataStyle.cloneStyleFrom(commonStyle); dateDataStyle.setDataFormat(df.getFormat("yyyy-mm-dd")); //$NON-NLS-1$ timeDataStyle = workbook.createCellStyle(); timeDataStyle.cloneStyleFrom(commonStyle); timeDataStyle.setDataFormat(df.getFormat("hh:mm:ss")); //$NON-NLS-1$ datetimeDataStyle = workbook.createCellStyle(); datetimeDataStyle.cloneStyleFrom(commonStyle); datetimeDataStyle.setDataFormat(df.getFormat("yyyy-mm-dd hh:mm:ss")); //$NON-NLS-1$ }
From source file:com.asakusafw.testdriver.excel.WorkbookInfo.java
License:Apache License
/** * Creates a new instance./*w w w . j ava2 s . c o m*/ * @param workbook target workbook * @throws IllegalArgumentException if some parameters were {@code null} */ public WorkbookInfo(Workbook workbook) { if (workbook == null) { throw new IllegalArgumentException("workbook must not be null"); //$NON-NLS-1$ } this.workbook = workbook; Font font = workbook.createFont(); commonStyle = workbook.createCellStyle(); commonStyle.setFont(font); commonStyle.setBorderTop(CellStyle.BORDER_THIN); commonStyle.setBorderBottom(CellStyle.BORDER_THIN); commonStyle.setBorderLeft(CellStyle.BORDER_THIN); commonStyle.setBorderRight(CellStyle.BORDER_THIN); titleStyle = workbook.createCellStyle(); titleStyle.cloneStyleFrom(commonStyle); titleStyle.setLocked(true); titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); titleStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex()); titleStyle.setAlignment(CellStyle.ALIGN_CENTER); CreationHelper helper = workbook.getCreationHelper(); DataFormat df = helper.createDataFormat(); dataStyle = workbook.createCellStyle(); dataStyle.cloneStyleFrom(commonStyle); dateDataStyle = workbook.createCellStyle(); dateDataStyle.cloneStyleFrom(commonStyle); dateDataStyle.setDataFormat(df.getFormat("yyyy-mm-dd")); //$NON-NLS-1$ timeDataStyle = workbook.createCellStyle(); timeDataStyle.cloneStyleFrom(commonStyle); timeDataStyle.setDataFormat(df.getFormat("hh:mm:ss")); //$NON-NLS-1$ datetimeDataStyle = workbook.createCellStyle(); datetimeDataStyle.cloneStyleFrom(commonStyle); datetimeDataStyle.setDataFormat(df.getFormat("yyyy-mm-dd hh:mm:ss")); //$NON-NLS-1$ }