List of usage examples for org.apache.poi.ss.usermodel Workbook write
void write(OutputStream stream) throws IOException;
From source file:cn.bzvs.excel.imports.base.ImportBaseService.java
License:Apache License
public void saveThisExcel(ImportParams params, Class<?> pojoClass, boolean isXSSFWorkbook, Workbook book) throws Exception { String path = PoiPublicUtil.getWebRootPath(getSaveExcelUrl(params, pojoClass)); File savefile = new File(path); if (!savefile.exists()) { savefile.mkdirs();//from w w w . j a v a 2 s. c om } SimpleDateFormat format = new SimpleDateFormat("yyyMMddHHmmss"); FileOutputStream fos = new FileOutputStream(path + "/" + format.format(new Date()) + "_" + Math.round(Math.random() * 100000) + (isXSSFWorkbook == true ? ".xlsx" : ".xls")); book.write(fos); IOUtils.closeQuietly(fos); }
From source file:cn.com.zhbook.component.poi.PoiPerformanceTest.java
License:Apache License
private static void saveFile(Workbook workBook, String fileName) { try {//w w w . j a v a2s . c o m FileOutputStream out = new FileOutputStream(fileName); workBook.write(out); out.close(); } catch (IOException ioe) { System.err.println("Error: failed to write to file \"" + fileName + "\", reason=" + ioe.getMessage()); } }
From source file:cn.edu.pku.lib.dataverse.ManageUserGroupPage.java
private File generateExcelRequestJoinGroupLogFile() { //excel workbook Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet(WorkbookUtil.createSafeSheetName("Groups' user member")); Locale locale = FacesContext.getCurrentInstance().getViewRoot().getLocale(); //generate header String heads = ResourceBundle.getBundle("Bundle", locale) .getString("dataverse.permissions.groups.member.header"); String[] array = heads.split(","); Row row = sheet.createRow(0);//from ww w . j ava 2s . c o m for (int k = 0; k < array.length; k++) { Cell cell = row.createCell(k); cell.setCellValue(array[k]); } //generate logs Set<AuthenticatedUser> authUsersSet = explicitGroup.getContainedAuthenticatedUsers(); int j = 1; Cell cell; for (AuthenticatedUser user : authUsersSet) { row = sheet.createRow(j); if (user.isBuiltInUser()) { BuiltinUser b = builtinUserService.findByUserName(user.getUserIdentifier()); cell = row.createCell(0); cell.setCellValue(b.getUserName()); cell = row.createCell(1); cell.setCellValue(b.getLastName()); cell = row.createCell(2); cell.setCellValue(b.getFirstName()); cell = row.createCell(3); if (b.getUserType() == UserType.ORDINARY) cell.setCellValue("ORDINARY"); else if (b.getUserType() == UserType.ADVANCE) cell.setCellValue("ADVANCE"); else cell.setCellValue(""); cell = row.createCell(4); cell.setCellValue(b.getAffiliation()); cell = row.createCell(5); cell.setCellValue(b.getPosition()); cell = row.createCell(6); cell.setCellValue(b.getDepartment()); cell = row.createCell(7); cell.setCellValue(b.getEmail()); cell = row.createCell(8); cell.setCellValue(b.getSpeciality()); cell = row.createCell(9); cell.setCellValue(b.getResearchInterest()); cell = row.createCell(10); cell.setCellValue(b.getGender()); cell = row.createCell(11); cell.setCellValue(b.getEducation()); cell = row.createCell(12); cell.setCellValue(b.getProfessionalTitle()); cell = row.createCell(13); cell.setCellValue(b.getSupervisor()); cell = row.createCell(14); cell.setCellValue(b.getCertificateType()); cell = row.createCell(15); cell.setCellValue(b.getCertificateNumber()); cell = row.createCell(16); cell.setCellValue(b.getOfficePhone()); cell = row.createCell(17); cell.setCellValue(b.getCellphone()); cell = row.createCell(18); cell.setCellValue(b.getOtherEmail()); cell = row.createCell(19); cell.setCellValue(b.getCountry()); cell = row.createCell(20); cell.setCellValue(b.getProvince()); cell = row.createCell(21); cell.setCellValue(b.getCity()); cell = row.createCell(22); cell.setCellValue(b.getAddress()); cell = row.createCell(23); cell.setCellValue(b.getZipCode()); cell = row.createCell(24); cell.setCellValue("Built In"); } else if (user.isPKUIAAAUser()) { PKUIAAAUser p = pkuIAAAUserService.findByUserName(user.getUserIdentifier()); cell = row.createCell(0); cell.setCellValue(p.getUserName()); cell = row.createCell(1); cell.setCellValue(p.getLastName()); cell = row.createCell(2); cell.setCellValue(p.getFirstName()); cell = row.createCell(3); if (p.getUserType() == UserType.ORDINARY) cell.setCellValue("ORDINARY"); else if (p.getUserType() == UserType.ADVANCE) cell.setCellValue("ADVANCE"); else cell.setCellValue(""); cell = row.createCell(4); cell.setCellValue(p.getAffiliation()); cell = row.createCell(5); cell.setCellValue(p.getPosition()); cell = row.createCell(6); cell.setCellValue(p.getDepartment()); cell = row.createCell(7); cell.setCellValue(p.getEmail()); cell = row.createCell(8); cell.setCellValue(p.getSpeciality()); cell = row.createCell(9); cell.setCellValue(p.getResearchInterest()); cell = row.createCell(10); cell.setCellValue(p.getGender()); cell = row.createCell(11); cell.setCellValue(p.getEducation()); cell = row.createCell(12); cell.setCellValue(p.getProfessionalTitle()); cell = row.createCell(13); cell.setCellValue(p.getSupervisor()); cell = row.createCell(14); cell.setCellValue(p.getCertificateType()); cell = row.createCell(15); cell.setCellValue(p.getCertificateNumber()); cell = row.createCell(16); cell.setCellValue(p.getOfficePhone()); cell = row.createCell(17); cell.setCellValue(p.getCellphone()); cell = row.createCell(18); cell.setCellValue(p.getOtherEmail()); cell = row.createCell(19); cell.setCellValue(p.getCountry()); cell = row.createCell(20); cell.setCellValue(p.getProvince()); cell = row.createCell(21); cell.setCellValue(p.getCity()); cell = row.createCell(22); cell.setCellValue(p.getAddress()); cell = row.createCell(23); cell.setCellValue(p.getZipCode()); cell = row.createCell(24); cell.setCellValue("PKU IAAA"); } j++; } 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 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);//w w w.ja v a 2s .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);/* www. j a v a 2 s. 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:cn.mypandora.util.MyExcelUtil.java
License:Apache License
/** * ?Excel//ww w. j a v a 2 s . c om * * @param workbook Excel * @param outputPath ? */ private static void saveExcelFile(Workbook workbook, String outputPath) { try { FileOutputStream fos = new FileOutputStream(outputPath); workbook.write(fos); fos.flush(); fos.close(); } catch (IOException e) { throw new RuntimeException(e); } }
From source file:cn.org.vbn.util.LinkedDropDownLists.java
License:Apache License
LinkedDropDownLists(String workbookName) {
File file = null;//from w w w.j a va2s .c o m
FileOutputStream fos = null;
Workbook workbook = null;
Sheet sheet = null;
DataValidationHelper dvHelper = null;
DataValidationConstraint dvConstraint = null;
DataValidation validation = null;
CellRangeAddressList addressList = null;
try {
// Using the ss.usermodel allows this class to support both binary
// and xml based workbooks. The choice of which one to create is
// made by checking the file extension.
if (workbookName.endsWith(".xlsx")) {
workbook = new XSSFWorkbook();
} else {
workbook = new HSSFWorkbook();
}
// Build the sheet that will hold the data for the validations. This
// must be done first as it will create names that are referenced
// later.
sheet = workbook.createSheet("Linked Validations");
LinkedDropDownLists.buildDataSheet(sheet);
// Build the first data validation to occupy cell A1. Note
// that it retrieves it's data from the named area or region called
// CHOICES. Further information about this can be found in the
// static buildDataSheet() method below.
addressList = new CellRangeAddressList(0, 0, 0, 0);
dvHelper = sheet.getDataValidationHelper();
dvConstraint = dvHelper.createFormulaListConstraint("CHOICES");
validation = dvHelper.createValidation(dvConstraint, addressList);
sheet.addValidationData(validation);
// Now, build the linked or dependent drop down list that will
// occupy cell B1. The key to the whole process is the use of the
// INDIRECT() function. In the buildDataSheet(0 method, a series of
// named regions are created and the names of three of them mirror
// the options available to the user in the first drop down list
// (in cell A1). Using the INDIRECT() function makes it possible
// to convert the selection the user makes in that first drop down
// into the addresses of a named region of cells and then to use
// those cells to populate the second drop down list.
addressList = new CellRangeAddressList(0, 0, 1, 1);
dvConstraint = dvHelper.createFormulaListConstraint("INDIRECT(UPPER($A$1))");
validation = dvHelper.createValidation(dvConstraint, addressList);
sheet.addValidationData(validation);
file = new File(workbookName);
fos = new FileOutputStream(file);
workbook.write(fos);
} catch (IOException ioEx) {
System.out.println("Caught a: " + ioEx.getClass().getName());
System.out.println("Message: " + ioEx.getMessage());
System.out.println("Stacktrace follws:.....");
ioEx.printStackTrace(System.out);
} finally {
try {
if (fos != null) {
fos.close();
fos = null;
}
} catch (IOException ioEx) {
System.out.println("Caught a: " + ioEx.getClass().getName());
System.out.println("Message: " + ioEx.getMessage());
System.out.println("Stacktrace follws:.....");
ioEx.printStackTrace(System.out);
}
}
}
From source file:co.com.jafc.components.excelbuilder.Test.java
public void construirReporteMapaPruebas() { List<ReporteMapaPruebaDTO> listaCasosPrueba = new ArrayList<ReporteMapaPruebaDTO>(); listaCasosPrueba.add(new ReporteMapaPruebaDTO(33, "Artefacto 1 bbbbbbbbbbb", "DSDSD", "Esperada", "DSDSA", new Date(), "admin", null, null, null, null, "Mapa Prueba 1", 1, "prueba1", "otra prueba1", "No Conformidad", "Funcional", "Baja", "Valeria.Vasquez", "Actualizacin del Ambiente", "El cliente lo asumir administrativamente", "Cerrado", new Date())); listaCasosPrueba.add(new ReporteMapaPruebaDTO(33, "Artefacto 1 bbbbbbbbbbb", "DSDSD", "Esperada", "DSDSA", new Date(), "admin", null, null, null, null, "Mapa Prueba 1", 2, "prueba1", "otra prueba1", "No Conformidad", "Funcional", "Baja", "Valeria.Vasquez", "Actualizacin del Ambiente", "El cliente lo asumir administrativamente", "Cerrado", new Date())); listaCasosPrueba.add(new ReporteMapaPruebaDTO(34, "Artefacto 1 bbbbbbbbbbb", "Logger is a subclass of Category, i.e. it extends Category. In other words, a logger is a category. " + "Thus, all operations that can be performed on a category can be performed on a logger. Internally, " + "whenever log4j is asked to produce a Category object, it will instead produce a Logger object. ", "Esperada", "DSDSA", new Date(), "admin", null, null, null, null, "Mapa Prueba 2", 5, "sfdsf", "sdfsdf", "No Conformidad", "Bloqueante", "Media", "Valeria.Vasquez", "Actualizacin del Ambiente", "Lo reportado no se encuentra en el alcance", "Inicial", new Date())); Workbook libroExcel = null; try {/*from w ww . j a v a 2 s . c om*/ String nombreMapaPrueba = "Mapa Prueba"; libroExcel = reporteExcel.construirReporteMapaPruebas(listaCasosPrueba, nombreMapaPrueba); } catch (Exception e) { //LogUtil.log(ReporteExcelTest.class.getName(), e.getMessage(), Level.ERROR, e); } finally { if (libroExcel != null) { try { FileOutputStream fileOut = new FileOutputStream(LOCATION); libroExcel.write(fileOut); Desktop d = Desktop.getDesktop(); d.open(new File(LOCATION)); } catch (FileNotFoundException ex) { ex.printStackTrace(); } catch (IOException ex) { ex.printStackTrace(); } } } }
From source file:CODIGOS.EditaPlanilha.java
public static void edita(String diretorio, String arquivo, int linha, int coluna, String texto) { try {/*w w w .ja v a 2s . c om*/ File dir = new File(diretorio); File file = new File(dir, arquivo + ".xlsx"); InputStream inp = new FileInputStream(file); try { Workbook wb = WorkbookFactory.create(inp); Sheet sheet = wb.getSheetAt(0); Row row = sheet.getRow(linha);//LINHA Cell cell = row.getCell(0); cell = row.createCell(coluna);//COLUNA cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(texto); try (FileOutputStream fileOut = new FileOutputStream(file)) { wb.write(fileOut); } } catch (IOException | InvalidFormatException ex) { Logger.getLogger(EditaPlanilha.class.getName()).log(Level.SEVERE, null, ex); } } catch (FileNotFoundException ex) { Logger.getLogger(EditaPlanilha.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.actelion.research.spiritapp.ui.util.PDFUtils.java
License:Open Source License
public static void convertHSSF2Pdf(Workbook wb, String header, File reportFile) throws Exception { assert wb != null; assert reportFile != null; //Precompute formula FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i);/* ww w. ja v a 2 s .co m*/ for (Row r : sheet) { for (Cell c : r) { if (c.getCellType() == HSSFCell.CELL_TYPE_FORMULA) { try { evaluator.evaluateFormulaCell(c); } catch (Exception e) { System.err.println(e); } } } } } File tmp = File.createTempFile("tmp_", ".xlsx"); try (OutputStream out = new BufferedOutputStream(new FileOutputStream(tmp))) { wb.write(out); } //Find page orientation int maxColumnsGlobal = 0; for (int sheetNo = 0; sheetNo < wb.getNumberOfSheets(); sheetNo++) { Sheet sheet = wb.getSheetAt(sheetNo); for (Iterator<Row> rowIterator = sheet.iterator(); rowIterator.hasNext();) { Row row = rowIterator.next(); maxColumnsGlobal = Math.max(maxColumnsGlobal, row.getLastCellNum()); } } Rectangle pageSize = maxColumnsGlobal < 10 ? PageSize.A4 : PageSize.A4.rotate(); Document pdfDocument = new Document(pageSize, 10f, 10f, 20f, 20f); PdfWriter writer = PdfWriter.getInstance(pdfDocument, new FileOutputStream(reportFile)); addHeader(writer, header); pdfDocument.open(); //we have two columns in the Excel sheet, so we create a PDF table with two columns //Note: There are ways to make this dynamic in nature, if you want to. //Loop through sheets for (int sheetNo = 0; sheetNo < wb.getNumberOfSheets(); sheetNo++) { Sheet sheet = wb.getSheetAt(sheetNo); //Loop through rows, to find number of columns int minColumns = 1000; int maxColumns = 0; for (Iterator<Row> rowIterator = sheet.iterator(); rowIterator.hasNext();) { Row row = rowIterator.next(); if (row.getFirstCellNum() >= 0) minColumns = Math.min(minColumns, row.getFirstCellNum()); if (row.getLastCellNum() >= 0) maxColumns = Math.max(maxColumns, row.getLastCellNum()); } if (maxColumns == 0) continue; //Loop through first rows, to find relative width float[] widths = new float[maxColumns]; int totalWidth = 0; for (int c = 0; c < maxColumns; c++) { int w = sheet.getColumnWidth(c); widths[c] = w; totalWidth += w; } for (int c = 0; c < maxColumns; c++) { widths[c] /= totalWidth; } //Create new page and a new chapter with the sheet's name if (sheetNo > 0) pdfDocument.newPage(); Chapter pdfSheet = new Chapter(sheet.getSheetName(), sheetNo + 1); PdfPTable pdfTable = null; PdfPCell pdfCell = null; boolean inTable = false; //Loop through cells, to create the content // boolean leftBorder = true; // boolean[] topBorder = new boolean[maxColumns+1]; for (int r = 0; r <= sheet.getLastRowNum(); r++) { Row row = sheet.getRow(r); //Check if we exited a table (empty line) if (row == null) { if (pdfTable != null) { addTable(pdfDocument, pdfSheet, totalWidth, widths, pdfTable); pdfTable = null; } inTable = false; continue; } //Check if we start a table (>MIN_COL_IN_TABLE columns) if (row.getLastCellNum() >= MIN_COL_IN_TABLE) { inTable = true; } if (!inTable) { //Process the data outside table, just add the text boolean hasData = false; Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getCellType() == Cell.CELL_TYPE_BLANK) continue; Chunk chunk = getChunk(wb, cell); pdfSheet.add(chunk); pdfSheet.add(new Chunk(" ")); hasData = true; } if (hasData) pdfSheet.add(Chunk.NEWLINE); } else { //Process the data in table if (pdfTable == null) { //Create table pdfTable = new PdfPTable(maxColumns); pdfTable.setWidths(widths); // topBorder = new boolean[maxColumns+1]; } int cellNumber = minColumns; // leftBorder = false; Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); for (; cellNumber < cell.getColumnIndex(); cellNumber++) { pdfCell = new PdfPCell(); pdfCell.setBorder(0); pdfTable.addCell(pdfCell); } Chunk phrase = getChunk(wb, cell); pdfCell = new PdfPCell(new Phrase(phrase)); pdfCell.setFixedHeight(row.getHeightInPoints() - 3); pdfCell.setNoWrap(!cell.getCellStyle().getWrapText()); pdfCell.setPaddingLeft(1); pdfCell.setHorizontalAlignment( cell.getCellStyle().getAlignment() == CellStyle.ALIGN_CENTER ? PdfPCell.ALIGN_CENTER : cell.getCellStyle().getAlignment() == CellStyle.ALIGN_RIGHT ? PdfPCell.ALIGN_RIGHT : PdfPCell.ALIGN_LEFT); pdfCell.setUseBorderPadding(false); pdfCell.setUseVariableBorders(false); pdfCell.setBorderWidthRight(cell.getCellStyle().getBorderRight() == 0 ? 0 : .5f); pdfCell.setBorderWidthBottom(cell.getCellStyle().getBorderBottom() == 0 ? 0 : cell.getCellStyle().getBorderBottom() > 1 ? 1 : .5f); pdfCell.setBorderWidthLeft(cell.getCellStyle().getBorderLeft() == 0 ? 0 : cell.getCellStyle().getBorderLeft() > 1 ? 1 : .5f); pdfCell.setBorderWidthTop(cell.getCellStyle().getBorderTop() == 0 ? 0 : cell.getCellStyle().getBorderTop() > 1 ? 1 : .5f); String color = cell.getCellStyle().getFillForegroundColorColor() == null ? null : ((XSSFColor) cell.getCellStyle().getFillForegroundColorColor()).getARGBHex(); if (color != null) pdfCell.setBackgroundColor(new Color(Integer.decode("0x" + color.substring(2)))); pdfTable.addCell(pdfCell); cellNumber++; } for (; cellNumber < maxColumns; cellNumber++) { pdfCell = new PdfPCell(); pdfCell.setBorder(0); pdfTable.addCell(pdfCell); } } //Custom code to add all images on the first sheet (works for reporting) if (sheetNo == 0 && row.getRowNum() == 0) { for (PictureData pd : wb.getAllPictures()) { try { Image pdfImg = Image.getInstance(pd.getData()); pdfImg.scaleToFit( pageSize.getWidth() * .8f - pageSize.getBorderWidthLeft() - pageSize.getBorderWidthRight(), pageSize.getHeight() * .8f - pageSize.getBorderWidthTop() - pageSize.getBorderWidthBottom()); pdfSheet.add(pdfImg); } catch (Exception e) { e.printStackTrace(); } } } } if (pdfTable != null) { addTable(pdfDocument, pdfSheet, totalWidth, widths, pdfTable); } pdfDocument.add(pdfSheet); } pdfDocument.close(); }