List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt
@Override public XSSFSheet getSheetAt(int index)
From source file:org.cgiar.ccafs.ap.summaries.projects.xlsx.DeliverablePlanningSummaryXLS.java
License:Open Source License
/** * This method is used to generate the csv file for the ProjectLeading institutions. * //from w w w . j a va 2 s. c om * @param projectPartnerInstitutions is the list of institutions to be added * @param projectList is the list with the projects related to each institution */ public byte[] generateXLS(List<Map<String, Object>> deliverableList) { try { // Writting headers String[] headers = new String[] { "Project id", "Project title", "Flagship(s)", "Region(s)", "Deliverable title", "MOG", "Year of expected completion", "Main type", "Sub type", "Partner responsible", "Other responsibles" }; // defining header types. int[] headerTypes = new int[] { BaseXLS.COLUMN_TYPE_HYPERLINK, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_SHORT, BaseXLS.COLUMN_TYPE_TEXT_SHORT, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_NUMERIC, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG, BaseXLS.COLUMN_TYPE_TEXT_LONG }; XSSFWorkbook workbook = xls.initializeWorkbook(true); this.xls.initializeSheet(workbook.getSheetAt(0), headerTypes); // renaming sheet workbook.setSheetName(0, "Deliverable Report"); Sheet sheet = workbook.getSheetAt(0); xls.writeHeaders(sheet, headers); this.addContent(deliverableList, workbook); // Set description xls.writeDescription(sheet, xls.getText("summaries.expected.deliverable.summary.sheetone.description")); // write text box xls.writeTitleBox(sheet, xls.getText("summaries.expected.deliverable.summary.name")); // write text box xls.createLogo(workbook, sheet); xls.writeWorkbook(); byte[] byteArray = xls.getBytes(); // Closing streams. xls.closeStreams(); return byteArray; } catch (IOException e) { e.printStackTrace(); } return null; }
From source file:org.cgiar.ccafs.marlo.action.center.capdev.ParticipantsAction.java
License:Open Source License
public String dowmloadTemplate() { try {//from www . jav a 2s . com ClassLoader classLoader = this.getClass().getClassLoader(); File file = new File(this.getClass().getResource("/template/participants-template.xlsm").getFile()); String path = new File(".").getCanonicalPath(); String real_path = path + "/src/main/resources/template/participants-template.xlsm"; String path_ = config.getUploadsBaseFolder() + "/participants-template.xlsm"; FileInputStream fileInput = new FileInputStream(path_); XSSFWorkbook wb = new XSSFWorkbook(fileInput); DataValidationConstraint constraintCountries = null; DataValidationConstraint constraintHighestDegree = null; DataValidationHelper validationHelper = null; Sheet sheet1 = wb.getSheetAt(0); XSSFSheet sheet2 = wb.getSheet("countries"); String dataValidationCountryName = "countriesLis"; // se traen los datos desde la DB con los que se desean crear las listas para los data validator y se rellenan los // arreglos que permitaran escribir los datos en el template List<LocElement> countryList = new ArrayList<>(locElementService.findAll().stream() .filter(le -> le.isActive() && (le.getLocElementType() != null) && (le.getLocElementType().getId() == 2)) .collect(Collectors.toList())); Collections.sort(countryList, (c1, c2) -> c1.getName().compareTo(c2.getName())); // arreglo usado para escribir la data de countries al template String[] countries = new String[countryList.size()]; for (int i = 0; i < countryList.size(); i++) { countries[i] = countryList.get(i).getName() + " - " + countryList.get(i).getIsoAlpha2(); } List<CapdevHighestDegree> highestDegreeList = new ArrayList<>(capdevHighestDegreeService.findAll() .stream().filter(h -> h.getName() != null).collect(Collectors.toList())); Collections.sort(highestDegreeList, (c1, c2) -> c1.getName().compareTo(c2.getName())); // arreglo usado para escribir la data de highest degree al template String[] highestDegree = new String[highestDegreeList.size()]; for (int i = 0; i < highestDegreeList.size(); i++) { highestDegree[i] = highestDegreeList.get(i).getId() + "- " + highestDegreeList.get(i).getName() + " (" + highestDegreeList.get(i).getAcronym() + ")"; } validationHelper = sheet1.getDataValidationHelper(); // se configuran las coordenas donde se desea pegar el data validator en la sheet1 del template CellRangeAddressList addressListCountry = new CellRangeAddressList(10, 1000, 3, 3); CellRangeAddressList addressListHighestDegree = new CellRangeAddressList(10, 1000, 4, 4); // se crean cada uno de los data validator this.createDataValidator(wb, sheet2, countries, dataValidationCountryName); // se configuran y pegan cada uno de los data validator DataValidation dataValidationCountry = this.setDataValidator(dataValidationCountryName, validationHelper, addressListCountry, constraintCountries); // set de cada data davilidator al sheet1 del template sheet1.addValidationData(dataValidationCountry); ByteArrayOutputStream fileOut = new ByteArrayOutputStream(); wb.write(fileOut); wb.close(); inputStream = new ByteArrayInputStream(fileOut.toByteArray()); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return SUCCESS; }
From source file:org.cgiar.ccafs.marlo.action.center.capdev.test.java
License:Open Source License
public void createFile() throws FileNotFoundException { try {// w ww .j av a 2 s . c o m DataValidation dataValidationCountries = null; DataValidation dataValidationInstitutions = null; DataValidation dataValidationCountryOfInstitutions = null; DataValidationConstraint constraintCountries = null; DataValidationConstraint constraintInstitutions = null; DataValidationConstraint constraintCountryOfInstitutions = null; DataValidationHelper validationHelper = null; final String path = new File(".").getCanonicalPath(); final String filePath = "C:\\Users\\logonzalez\\Downloads\\participants-template.xlsm"; final File file = new File(filePath); final FileInputStream fileInput = new FileInputStream(file); final XSSFWorkbook wb = new XSSFWorkbook(fileInput); final Sheet sheet1 = wb.getSheetAt(0); final XSSFSheet sheet2 = wb.getSheet("countries"); final XSSFSheet sheet3 = wb.getSheet("institutions"); final String reference = null; final String dataValidationCountryName = "countriesLis"; final String dataValidationInstitutionName = "institutionsList"; final String[] countries = { "1- Colombia", "2- Brazil", "3- Espenia", "4- Argentina", "5- Aruba", "6- Egipto", "7- Panama", "8- Ecuador" }; final String[] institutions = { "CH- U.chile", "BZ- U.coritiba", "PN- U.panama", "AR- U.de.Palermo", "AF- U.delNilo", "EC- U.de.Quito", }; for (int i = 0; i < countries.length; i++) { final Row fila = sheet2.createRow(i); final Cell celda = fila.createCell(0); final Cell celdaformula = fila.createCell(1); // final String formula = "SUM(C1,D1)"; celda.setCellValue(countries[i]); // celdaformula.setCellFormula(formula); } // sheet2.protectSheet("marlo-ciat"); // // 3. create named range for an area using AreaReference // final Name namedCountry = wb.createName(); // namedCountry.setNameName(dataValidationCountryName); // reference = "countries!$A$1:$A$" + countries.length; // area reference // namedCountry.setRefersToFormula(reference); for (int i = 0; i < institutions.length; i++) { final Row fila = sheet3.createRow(i); final Cell celda = fila.createCell(0); celda.setCellValue(institutions[i]); } // final Name namedInstitution = wb.createName(); // namedInstitution.setNameName(dataValidationInstitutionName); // reference = "institutions!$A$1:$A$" + institutions.length; // area reference // namedInstitution.setRefersToFormula(reference); // // sheet3.protectSheet("marlo-ciat"); validationHelper = sheet1.getDataValidationHelper(); final CellRangeAddressList addressListCountry = new CellRangeAddressList(11, 1000, 4, 4); constraintCountries = validationHelper.createFormulaListConstraint(dataValidationCountryName); dataValidationCountries = validationHelper.createValidation(constraintCountries, addressListCountry); dataValidationCountries.setSuppressDropDownArrow(true); if (dataValidationCountries instanceof XSSFDataValidation) { dataValidationCountries.setSuppressDropDownArrow(true); dataValidationCountries.setShowErrorBox(true); } else { dataValidationCountries.setSuppressDropDownArrow(false); } final CellRangeAddressList addressListInstitution = new CellRangeAddressList(11, 1000, 6, 6); constraintInstitutions = validationHelper.createFormulaListConstraint(dataValidationInstitutionName); dataValidationInstitutions = validationHelper.createValidation(constraintInstitutions, addressListInstitution); dataValidationInstitutions.setSuppressDropDownArrow(true); if (dataValidationInstitutions instanceof XSSFDataValidation) { dataValidationInstitutions.setSuppressDropDownArrow(true); dataValidationInstitutions.setShowErrorBox(true); } else { dataValidationInstitutions.setSuppressDropDownArrow(false); } final CellRangeAddressList addressListCountryOfInstitution = new CellRangeAddressList(11, 1000, 7, 7); constraintCountryOfInstitutions = validationHelper .createFormulaListConstraint(dataValidationCountryName); dataValidationCountryOfInstitutions = validationHelper.createValidation(constraintCountryOfInstitutions, addressListCountryOfInstitution); dataValidationCountryOfInstitutions.setSuppressDropDownArrow(true); if (dataValidationCountryOfInstitutions instanceof XSSFDataValidation) { dataValidationCountryOfInstitutions.setSuppressDropDownArrow(true); dataValidationCountryOfInstitutions.setShowErrorBox(true); } else { dataValidationCountryOfInstitutions.setSuppressDropDownArrow(false); } sheet1.addValidationData(dataValidationCountries); sheet1.addValidationData(dataValidationInstitutions); sheet1.addValidationData(dataValidationCountryOfInstitutions); FileOutputStream fileOut; fileOut = new FileOutputStream("C:\\Users\\logonzalez\\Downloads\\vineet.xlsm"); wb.write(fileOut); fileOut.close(); wb.close(); } catch (EncryptedDocumentException | IOException e1) { e1.printStackTrace(); } }
From source file:org.cgiar.ccafs.marlo.action.center.capdev.test.java
License:Open Source License
public void readFile() throws FileNotFoundException { final File file = new File("C:\\Users\\logonzalez\\Downloads\\participants.xlsx"); FileInputStream fileInput;/* w w w. j a va2 s . c o m*/ try { fileInput = new FileInputStream(file); final XSSFWorkbook wb = new XSSFWorkbook(fileInput); final Sheet sheet = wb.getSheetAt(0); final List<Row> notEmptyRows = this.searchForEmptyRows(sheet); // System.out.println(sheet.getLastRowNum()); final Row firstRow = sheet.getRow(9); final int totalRows = sheet.getLastRowNum() - firstRow.getRowNum(); // System.out.println("firstRow " + firstRow.getRowNum()); // System.out.println("totalRows " + totalRows); final int totalColumns = firstRow.getLastCellNum(); System.out.println("notEmptyRows.size " + notEmptyRows.size()); for (int fila = 0; fila < notEmptyRows.size(); fila++) { final Row row = notEmptyRows.get(fila); for (int col = 0; col < row.getLastCellNum(); col++) { final Cell cell = row.getCell(col); System.out.println(this.getCellData(cell)); } System.out.println("-----------"); } } catch (final IOException e) { e.printStackTrace(); } }
From source file:org.cvrgrid.hl7.fileparse.PicuDataLoader.java
License:Apache License
public static void main(String[] args) throws Exception { PicuDataLoader picuDataLoader = new PicuDataLoader(); SimpleDateFormat fromUser = new SimpleDateFormat("yyyyMMddHHmmss"); SimpleDateFormat myFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); OpenTSDBConfiguration openTSDBConfiguration = picuDataLoader.getOpenTSDBConfiguration(); String urlString = openTSDBConfiguration.getOpenTSDBUrl(); HL7Measurements hl7Measurements = new HL7Measurements(); HashMap<String, String> measurementNames = hl7Measurements.getMeasurementNames(); XSSFWorkbook wb = readFile(openTSDBConfiguration.getAwareSupportedParams()); XSSFSheet sheet = wb.getSheetAt(0); for (int r = 1; r < 280; r++) { XSSFRow row = sheet.getRow(r);/*from w ww. j ava 2s . co m*/ if (row == null) { continue; } String key = row.getCell(2).getStringCellValue(); String value = row.getCell(1).getStringCellValue(); value = value.replaceAll(":", "/"); measurementNames.put(key, value); } HashMap<String, PatientInfo> idMatch = new HashMap<String, PatientInfo>(); File f = new File(openTSDBConfiguration.getIdMatch()); if (f.exists()) { wb = readFile(openTSDBConfiguration.getIdMatch()); sheet = wb.getSheetAt(0); for (int r = 1; r < sheet.getLastRowNum() + 1; r++) { XSSFRow row = sheet.getRow(r); PatientInfo patInfo = new PatientInfo(); patInfo.setPicuSubject(row.getCell(1).getBooleanCellValue()); patInfo.setFirstName(row.getCell(3).getStringCellValue()); patInfo.setLastName(row.getCell(4).getStringCellValue()); patInfo.setBirthDateTime(row.getCell(5).getStringCellValue()); patInfo.setGender(row.getCell(6).getStringCellValue()); patInfo.setBirthplace(row.getCell(7).getStringCellValue()); patInfo.setEarliestDataPoint(row.getCell(8).getStringCellValue()); LinkedList<String> locations = new LinkedList<String>(); String lSet = row.getCell(10).getStringCellValue(); lSet = lSet.replaceAll("\\[", ""); lSet = lSet.replaceAll("\\]", ""); String[] locationSet = lSet.split(","); for (String location : locationSet) { locations.add(location.trim()); } patInfo.setLocations(locations); LinkedList<String> variables = new LinkedList<String>(); String vSet = row.getCell(12).getStringCellValue(); vSet = vSet.replaceAll("\\[", ""); vSet = vSet.replaceAll("\\]", ""); String[] variableSet = vSet.split(","); for (String variable : variableSet) { variables.add(variable.trim()); } patInfo.setVariables(variables); idMatch.put(patInfo.getHash(), patInfo); } } System.out.println("Existing Subject Count: " + idMatch.size()); String processedFile = openTSDBConfiguration.getProcessedFile(); String rootDir = openTSDBConfiguration.getRootDir(); ArrayList<String> processedFiles = new ArrayList<String>(); File processedFileContents = new File(processedFile); getProcessedFiles(processedFileContents, processedFiles); ArrayList<String> messageFiles = new ArrayList<String>(); File rootDirContents = new File(rootDir); getDirectoryContents(rootDirContents, processedFiles, messageFiles); XSSFWorkbook workbook; XSSFSheet sheetOut, sheetOut2; if (processedFiles.size() > 1) { workbook = readFile(openTSDBConfiguration.getIdMatch()); sheetOut = workbook.getSheetAt(0); sheetOut2 = workbook.getSheetAt(1); } else { workbook = new XSSFWorkbook(); sheetOut = workbook.createSheet("idMatch"); sheetOut2 = workbook.createSheet(openTSDBConfiguration.getIdMatchSheet()); } for (String filePath : messageFiles) { System.out.println(" File: " + filePath); FileReader reader = new FileReader(filePath); Hl7InputStreamMessageIterator iter = new Hl7InputStreamMessageIterator(reader); while (iter.hasNext()) { HashMap<String, String> tags = new HashMap<String, String>(); Message next = iter.next(); ORU_R01 oru = new ORU_R01(); oru.parse(next.encode()); PatientInfo patInfo = new PatientInfo(); if (Terser.get(oru.getRESPONSE().getPATIENT().getPID(), 5, 0, 2, 1) != null) patInfo.setFirstName(Terser.get(oru.getRESPONSE().getPATIENT().getPID(), 5, 0, 2, 1).trim()); if (Terser.get(oru.getRESPONSE().getPATIENT().getPID(), 5, 0, 1, 1) != null) patInfo.setLastName(Terser.get(oru.getRESPONSE().getPATIENT().getPID(), 5, 0, 1, 1).trim()); if (Terser.get(oru.getRESPONSE().getPATIENT().getPID(), 7, 0, 1, 1) != null) patInfo.setBirthDateTime( Terser.get(oru.getRESPONSE().getPATIENT().getPID(), 7, 0, 1, 1).trim()); if (Terser.get(oru.getRESPONSE().getPATIENT().getPID(), 8, 0, 1, 1) != null) patInfo.setGender(Terser.get(oru.getRESPONSE().getPATIENT().getPID(), 8, 0, 1, 1).trim()); if (Terser.get(oru.getRESPONSE().getPATIENT().getPID(), 23, 0, 1, 1) != null) patInfo.setBirthplace(Terser.get(oru.getRESPONSE().getPATIENT().getPID(), 23, 0, 1, 1).trim()); LinkedList<String> locations = new LinkedList<String>(); LinkedList<String> variables = new LinkedList<String>(); if (idMatch.get(patInfo.getHash()) != null) { patInfo = idMatch.get(patInfo.getHash()); locations = patInfo.getLocations(); variables = patInfo.getVariables(); } if (!locations .contains(Terser.get(oru.getRESPONSE().getPATIENT().getVISIT().getPV1(), 3, 0, 1, 1))) { locations.add(Terser.get(oru.getRESPONSE().getPATIENT().getVISIT().getPV1(), 3, 0, 1, 1)); if (locations.peekLast().startsWith("ZB04")) patInfo.setPicuSubject(true); } tags.put("subjectId", patInfo.getHash()); String time = Terser.get(oru.getRESPONSE().getORDER_OBSERVATION().getOBR(), 7, 0, 1, 1); Date timepoint = fromUser.parse(time); String reformattedTime = myFormat.format(timepoint); if (patInfo.getEarliestDataPoint().equalsIgnoreCase("")) { patInfo.setEarliestDataPoint(reformattedTime); } List<ORU_R01_OBSERVATION> observations = oru.getRESPONSE().getORDER_OBSERVATION() .getOBSERVATIONAll(); for (ORU_R01_OBSERVATION observation : observations) { String seriesName = Terser.get(observation.getOBX(), 3, 0, 1, 1); if (measurementNames.get(seriesName) != null) { seriesName = measurementNames.get(seriesName); } else { seriesName = seriesName.replaceFirst("\\d", "#"); seriesName = measurementNames.get(seriesName); } StringBuffer buff = new StringBuffer(); String[] tokens = seriesName.split(" "); for (String i : tokens) { i = i.replaceAll("\\(", ""); i = i.replaceAll("\\)", ""); buff.append(StringUtils.capitalize(i)); } String measurementValue = Terser.get(observation.getOBX(), 5, 0, 1, 1); String units = Terser.get(observation.getOBX(), 6, 0, 1, 1); if (units != null) { units = units.replaceAll(":", ""); units = units.replaceAll("cm_h2o", "cmH2O"); units = units.replaceAll("\\(min/m2\\)", "MinPerMeterSquared"); units = units.replaceAll("l", "liters"); units = units.replaceAll("mliters", "milliliters"); units = units.replaceAll("g.m", "gramMeters"); units = units.replaceAll("dyn.sec.cm-5", "dyneSecondsPerQuinticCentimeter"); units = units.replaceAll("dyneSecondsPerQuinticCentimeter.m2", "dyneSecondsPerQuinticCentimeterPerMeterSquared"); units = units.replaceAll("m2", "MeterSquared"); units = units.replaceAll("min", "Min"); units = units.replaceAll("/", "Per"); units = units.replaceAll("%", "percent"); units = units.replaceAll("#", "Count"); units = units.replaceAll("celiters", "Celsius"); units = units.replaceAll("mm\\(hg\\)", "mmHg"); } else { units = "percent"; } seriesName = "vitals." + StringUtils.uncapitalize(units); seriesName += "." + StringUtils.uncapitalize(buff.toString()); seriesName = seriesName.trim(); if (!variables.contains(StringUtils.uncapitalize(buff.toString()))) variables.add(StringUtils.uncapitalize(buff.toString())); IncomingDataPoint dataPoint = new IncomingDataPoint(seriesName, timepoint.getTime(), measurementValue, tags); TimeSeriesStorer.storeTimePoint(urlString, dataPoint); } patInfo.setLocations(locations); patInfo.setVariables(variables); idMatch.put(patInfo.getHash(), patInfo); } System.out.println(" Subject Count: " + idMatch.size()); int rowNum = 0; Set<String> keys = idMatch.keySet(); TreeSet<String> sortedKeys = new TreeSet<String>(keys); for (String key : sortedKeys) { XSSFRow row = sheetOut.createRow(rowNum); XSSFRow row2 = sheetOut2.createRow(rowNum); XSSFCell cell, cell2; if (rowNum == 0) { cell = row.createCell(0); cell.setCellValue("Count"); cell = row.createCell(1); cell.setCellValue("PICU Subject?"); cell = row.createCell(2); cell.setCellValue("Hash"); cell = row.createCell(3); cell.setCellValue("First Name"); cell = row.createCell(4); cell.setCellValue("Last Name"); cell = row.createCell(5); cell.setCellValue("Birth Date/Time"); cell = row.createCell(6); cell.setCellValue("Gender"); cell = row.createCell(7); cell.setCellValue("Birthplace"); cell = row.createCell(8); cell.setCellValue("First Time Point"); cell = row.createCell(9); cell.setCellValue("Location Count"); cell = row.createCell(10); cell.setCellValue("Locations"); cell = row.createCell(11); cell.setCellValue("Variable Count"); cell = row.createCell(12); cell.setCellValue("Variables"); cell2 = row2.createCell(0); cell2.setCellValue("Count"); cell2 = row2.createCell(1); cell2.setCellValue("PICU Subject?"); cell2 = row2.createCell(2); cell2.setCellValue("Hash"); cell2 = row2.createCell(3); cell2.setCellValue("First Name"); cell2 = row2.createCell(4); cell2.setCellValue("Last Name"); cell2 = row2.createCell(5); cell2.setCellValue("Birth Date/Time"); cell2 = row2.createCell(6); cell2.setCellValue("Gender"); cell2 = row2.createCell(7); cell2.setCellValue("Birthplace"); cell2 = row2.createCell(8); cell2.setCellValue("First Time Point"); cell2 = row2.createCell(9); cell2.setCellValue("Location Count"); cell2 = row2.createCell(10); cell2.setCellValue("Locations"); cell2 = row2.createCell(11); cell2.setCellValue("Variable Count"); cell2 = row2.createCell(12); cell2.setCellValue("Variables"); } else { cell = row.createCell(0); cell.setCellValue(rowNum); cell = row.createCell(1); cell.setCellValue(idMatch.get(key).isPicuSubject()); cell = row.createCell(2); cell.setCellValue(key); cell = row.createCell(3); cell.setCellValue(idMatch.get(key).getFirstName()); cell = row.createCell(4); cell.setCellValue(idMatch.get(key).getLastName()); cell = row.createCell(5); cell.setCellValue(idMatch.get(key).getBirthDateTime()); cell = row.createCell(6); cell.setCellValue(idMatch.get(key).getGender()); cell = row.createCell(7); cell.setCellValue(idMatch.get(key).getBirthplace()); cell = row.createCell(8); cell.setCellValue(idMatch.get(key).getEarliestDataPoint()); cell = row.createCell(9); cell.setCellValue(idMatch.get(key).getLocations().size()); cell = row.createCell(10); cell.setCellValue(idMatch.get(key).getLocations().toString()); cell = row.createCell(11); cell.setCellValue(idMatch.get(key).getVariables().size()); cell = row.createCell(12); cell.setCellValue(idMatch.get(key).getVariables().toString()); if (idMatch.get(key).isPicuSubject()) { cell2 = row2.createCell(0); cell2.setCellValue(rowNum); cell2 = row2.createCell(1); cell2.setCellValue(idMatch.get(key).isPicuSubject()); cell2 = row2.createCell(2); cell2.setCellValue(key); cell2 = row2.createCell(3); cell2.setCellValue(idMatch.get(key).getFirstName()); cell2 = row2.createCell(4); cell2.setCellValue(idMatch.get(key).getLastName()); cell2 = row2.createCell(5); cell2.setCellValue(idMatch.get(key).getBirthDateTime()); cell2 = row2.createCell(6); cell2.setCellValue(idMatch.get(key).getGender()); cell2 = row2.createCell(7); cell2.setCellValue(idMatch.get(key).getBirthplace()); cell2 = row2.createCell(8); cell2.setCellValue(idMatch.get(key).getEarliestDataPoint()); cell2 = row2.createCell(9); cell2.setCellValue(idMatch.get(key).getLocations().size()); cell2 = row2.createCell(10); cell2.setCellValue(idMatch.get(key).getLocations().toString()); cell2 = row2.createCell(11); cell2.setCellValue(idMatch.get(key).getVariables().size()); cell2 = row2.createCell(12); cell2.setCellValue(idMatch.get(key).getVariables().toString()); } } rowNum++; } } if (messageFiles.size() > 0) { try { FileOutputStream out = new FileOutputStream(new File(openTSDBConfiguration.getIdMatch())); workbook.write(out); out.close(); System.out.println("Excel written successfully..."); PrintWriter writer = new PrintWriter(rootDir + "done.txt", "UTF-8"); for (String filePath : processedFiles) { writer.println(filePath); } for (String filePath : messageFiles) { writer.println(filePath); } writer.close(); System.out.println("done.txt written successfully..."); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } else { System.out.println("Nothing new to process..."); } }
From source file:org.dhatim.fastexcel.Correctness.java
License:Apache License
@Test public void multipleWorksheets() throws Exception { int numWs = 10; int numRows = 5000; int numCols = 6; byte[] data = writeWorkbook(wb -> { @SuppressWarnings("unchecked") CompletableFuture<Void>[] cfs = new CompletableFuture[numWs]; for (int i = 0; i < cfs.length; ++i) { Worksheet ws = wb.newWorksheet("Sheet " + i); CompletableFuture<Void> cf = CompletableFuture.runAsync(() -> { for (int j = 0; j < numCols; ++j) { ws.value(0, j, "Column " + j); ws.style(0, j).bold().fontSize(12).fillColor(Color.GRAY2).set(); for (int k = 1; k <= numRows; ++k) { switch (j) { case 0: ws.value(k, j, "String value " + k); break; case 1: ws.value(k, j, 2); break; case 2: ws.value(k, j, 3L); break; case 3: ws.value(k, j, 0.123); break; case 4: ws.value(k, j, new Date()); ws.style(k, j).format("yyyy-MM-dd HH:mm:ss").set(); break; case 5: ws.value(k, j, LocalDate.now()); ws.style(k, j).format("yyyy-MM-dd").set(); break; default: throw new IllegalArgumentException(); }/*from ww w . ja v a2s . c o m*/ } } ws.formula(numRows + 1, 1, "=SUM(" + ws.range(1, 1, numRows, 1).toString() + ")"); ws.formula(numRows + 1, 2, "=SUM(" + ws.range(1, 2, numRows, 2).toString() + ")"); ws.formula(numRows + 1, 3, "=SUM(" + ws.range(1, 3, numRows, 3).toString() + ")"); ws.formula(numRows + 1, 4, "=AVERAGE(" + ws.range(1, 4, numRows, 4).toString() + ")"); ws.style(numRows + 1, 4).format("yyyy-MM-dd HH:mm:ss").set(); ws.formula(numRows + 1, 5, "=AVERAGE(" + ws.range(1, 5, numRows, 5).toString() + ")"); ws.style(numRows + 1, 5).format("yyyy-MM-dd").bold().italic().fontColor(Color.RED) .fontName("Garamond").fontSize(new BigDecimal("14.5")).horizontalAlignment("center") .verticalAlignment("top").wrapText(true).set(); ws.range(1, 0, numRows, numCols - 1).style().borderColor(Color.RED).borderStyle("thick") .shadeAlternateRows(Color.RED).set(); }); cfs[i] = cf; } try { CompletableFuture.allOf(cfs).get(); } catch (InterruptedException | ExecutionException ex) { throw new RuntimeException(ex); } }); // Check generated workbook with Apache POI XSSFWorkbook xwb = new XSSFWorkbook(new ByteArrayInputStream(data)); assertThat(xwb.getActiveSheetIndex()).isEqualTo(0); assertThat(xwb.getNumberOfSheets()).isEqualTo(numWs); for (int i = 0; i < numWs; ++i) { assertThat(xwb.getSheetName(i)).isEqualTo("Sheet " + i); XSSFSheet xws = xwb.getSheetAt(i); assertThat(xws.getLastRowNum()).isEqualTo(numRows + 1); for (int j = 1; j <= numRows; ++j) { assertThat(xws.getRow(j).getCell(0).getStringCellValue()).isEqualTo("String value " + j); } } }
From source file:org.ecocean.servlet.importer.ImportExcelMetadata.java
License:Open Source License
public void processExcel(File dataFile, HttpServletResponse response, boolean committing, Hashtable<String, MediaAsset> assetIds, Shepherd myShepherd, PrintWriter out) throws IOException { FileInputStream fs = new FileInputStream(dataFile); XSSFWorkbook wb = new XSSFWorkbook(fs); XSSFSheet sheet;// w w w .ja v a 2 s. c o m XSSFRow row; sheet = wb.getSheetAt(0); if (wb.getNumberOfSheets() < 1) { out.println("!!! XSSFWorkbook did not find any sheets !!!"); } else if (sheet.getClass() == null) { out.println("!!! Sheet was not successfully extracted !!!"); } else { out.println("+++ Success creating FileInputStream and XSSF Worksheet +++"); } int numSheets = wb.getNumberOfSheets(); out.println("Num Sheets = " + numSheets); int physicalNumberOfRows = sheet.getPhysicalNumberOfRows(); out.println("Num Rows = " + physicalNumberOfRows); int rows = sheet.getPhysicalNumberOfRows(); ; // No of rows int cols = sheet.getRow(0).getPhysicalNumberOfCells(); // No of columns out.println("Num Cols = " + cols); out.println("committing = " + committing); int printPeriod = 25; out.println("+++++ LOOPING THROUGH FILE +++++"); String encId = null; boolean isValid = true; for (int i = 1; i < rows; i++) { try { if (committing) myShepherd.beginDBTransaction(); row = sheet.getRow(i); // example if (getStringOrIntString(row, 7) != null) { encId = String.valueOf(getInteger(row, 7)); } else { isValid = false; } out.println("---- CURRENT ID: " + encId + " ----"); Encounter enc = null; if (committing && isValid == true) { enc = parseEncounter(row, myShepherd, out); String indID = null; try { indID = getStringOrIntString(row, 7); } catch (Exception e) { out.println("Not a valid indy for this row!"); } MarkedIndividual ind = null; boolean needToAddEncToInd = false; if (indID != null) { ind = myShepherd.getMarkedIndividualQuiet(indID); if (ind == null) { ind = new MarkedIndividual(indID, enc); } else { needToAddEncToInd = true; } } try { out.println("Adding media asset : " + encId); enc.setState("approved"); myShepherd.beginDBTransaction(); if (committing && isValid == true) myShepherd.storeNewEncounter(enc, Util.generateUUID()); myShepherd.commitDBTransaction(); String encIdS = String.valueOf(encId); MediaAsset mal = assetIds.get(encIdS + "l"); MediaAsset mar = assetIds.get(encIdS + "r"); MediaAsset mac = assetIds.get(encIdS + "c"); MediaAsset map = assetIds.get(encIdS + "p"); try { myShepherd.beginDBTransaction(); if (mal != null) { enc.addMediaAsset(mal); } if (mac != null) { enc.addMediaAsset(mac); } if (map != null) { enc.addMediaAsset(map); } if (mar != null) { enc.addMediaAsset(mar); } myShepherd.commitDBTransaction(); } catch (Exception npe) { npe.printStackTrace(); out.println("!!! Failed to Add Media asset to Encounter !!!"); } } catch (Exception e) { e.printStackTrace(); out.println("!!! Failed to Store New Encounter !!!"); } if (committing && ind != null) { myShepherd.beginDBTransaction(); myShepherd.storeNewMarkedIndividual(ind); myShepherd.commitDBTransaction(); out.println("=== CREATED INDIVIDUAL " + ind.getName() + " ==="); } myShepherd.beginDBTransaction(); if (ind != null) ind.addEncounter(enc); myShepherd.commitDBTransaction(); // New Close it. if (i % printPeriod == 0) { out.println("Parsed row (" + i + "), containing Enc " + enc.getEncounterNumber() + " with Latitude " + enc.getDecimalLatitude() + " and Longitude " + enc.getDecimalLongitude() + ", dateInMillis " + enc.getDateInMilliseconds() + ", individualID " + enc.getIndividualID() + ", sex " + enc.getSex() + ", living status " + enc.getLivingStatus() + ", identification notes " + enc.getIdentificationRemarks()); } } } catch (Exception e) { fs.close(); out.println("!!! Encountered an error while Iterating through rows !!!"); e.printStackTrace(out); myShepherd.rollbackDBTransaction(); } isValid = true; } fs.close(); wb.close(); }
From source file:org.exoplatform.services.document.impl.MSXExcelDocumentReader.java
License:Open Source License
/** * Returns only a text from .xlsx file content. * //from ww w . j av a2 s. c o m * @param is an input stream with .xls file content. * @return The string only with text from file content. */ public String getContentAsText(final InputStream is) throws IOException, DocumentReadException { if (is == null) { throw new IllegalArgumentException("InputStream is null."); } StringBuilder builder = new StringBuilder(""); SimpleDateFormat dateFormat = new SimpleDateFormat(DATE_FORMAT); try { if (is.available() == 0) { return ""; } XSSFWorkbook wb; try { wb = SecurityHelper.doPrivilegedIOExceptionAction(new PrivilegedExceptionAction<XSSFWorkbook>() { public XSSFWorkbook run() throws Exception { return new XSSFWorkbook(is); } }); } catch (IOException e) { throw new DocumentReadException("Can't open spreadsheet.", e); } catch (OpenXML4JRuntimeException e) { return builder.toString(); } for (int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) { XSSFSheet sheet = wb.getSheetAt(sheetNum); if (sheet != null) { for (int rowNum = sheet.getFirstRowNum(); rowNum <= sheet.getLastRowNum(); rowNum++) { XSSFRow row = sheet.getRow(rowNum); if (row != null) { int lastcell = row.getLastCellNum(); for (int k = 0; k < lastcell; k++) { XSSFCell cell = row.getCell(k); if (cell != null) { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: { double d = cell.getNumericCellValue(); if (isCellDateFormatted(cell)) { Date date = HSSFDateUtil.getJavaDate(d); String cellText = dateFormat.format(date); builder.append(cellText).append(" "); } else { builder.append(d).append(" "); } break; } case XSSFCell.CELL_TYPE_FORMULA: builder.append(cell.getCellFormula().toString()).append(" "); break; case XSSFCell.CELL_TYPE_BOOLEAN: builder.append(cell.getBooleanCellValue()).append(" "); break; case XSSFCell.CELL_TYPE_ERROR: builder.append(cell.getErrorCellValue()).append(" "); break; case XSSFCell.CELL_TYPE_STRING: builder.append(cell.getStringCellValue().toString()).append(" "); break; default: break; } } } } } } } } finally { if (is != null) { try { is.close(); } catch (IOException e) { if (LOG.isTraceEnabled()) { LOG.trace("An exception occurred: " + e.getMessage()); } } } } return builder.toString(); }
From source file:org.hlc.utility.excel.ExcelInputHandler.java
License:Apache License
/** * Import excel./* w w w . j a v a2 s.c o m*/ * * @param <T> the generic type * @param type the type * @param in the in * @return the list */ @SuppressWarnings("rawtypes") public <T> List<T> importExcel2007(Class<T> type, InputStream in) { Excel excelAnn = type.getAnnotation(Excel.class); if (excelAnn == null) { throw new ExcelException("The Class <" + type + "> did not Excel"); } List<T> list = new ArrayList<T>(); Map<String, Method> mapping = new LinkedHashMap<String, Method>(); Map<String, TypeHandler> converters = new HashMap<String, TypeHandler>(); try { // Step1 ?? Field fileds[] = type.getDeclaredFields(); for (int i = 0; i < fileds.length; i++) { Field field = fileds[i]; ExcelColumn column = field.getAnnotation(ExcelColumn.class); if (column != null) { Method setMethod = ReflectionUtils.setValueMethod(field, type); mapping.put(column.value(), setMethod); if (column.converter() != TypeHandler.class) { converters.put(setMethod.getName().toString(), column.converter().newInstance()); } else { converters.put(setMethod.getName().toString(), TypeHandlerFactory.getHandler(field.getType())); } } } T temp = null; XSSFWorkbook hssfWorkbook = new XSSFWorkbook(in); for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { XSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); if (hssfSheet == null) { continue; } // ?Sheet List<Method> methods = new ArrayList<Method>(); for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { XSSFRow hssfRow = hssfSheet.getRow(rowNum); if (hssfRow == null) { continue; } // ? if (rowNum == 0) { for (int cellNum = 0; cellNum < hssfRow.getLastCellNum(); cellNum++) { String title = hssfRow.getCell(cellNum).getStringCellValue(); Method me = mapping.get(title); if (me == null) { continue; } methods.add(me); } continue; } temp = type.newInstance(); for (int cellNum = 0; cellNum < methods.size(); cellNum++) { XSSFCell xh = hssfRow.getCell(cellNum); if (xh == null) { continue; } Method m = methods.get(cellNum); TypeHandler handler = converters.get(m.getName()); if (handler == null) { continue; } xh.setCellType(Cell.CELL_TYPE_STRING); String value = xh.getStringCellValue(); if (StringUtils.isEmpty(value)) { continue; } Object val = null; try { val = handler.stringToType(value); } catch (Exception e) { throw new ExcelException("" + (numSheet + 1) + "" + (rowNum + 1) + "" + (cellNum + 1) + "" + value + "??"); } methods.get(cellNum).invoke(temp, val); } list.add(temp); } } } catch (Exception e) { throw new ExcelException("Excel processing error?", e); } return list; }
From source file:org.keyboardplaying.xtt.xlsx.XlsxNormalizer.java
License:Apache License
/** * Normalizes the time tracker workbook's selected sheet and cells and applies some styling. * * @param workbook//from www .ja v a2 s. c o m * the workbook to normalize */ public void normalizeSheets(XSSFWorkbook workbook) { normalizeSheet(workbook.getSheetAt(XlsxTracker.TAB_INDEX_TRACKER), trackerActiveRange); normalizeSheet(workbook.getSheetAt(XlsxTracker.TAB_INDEX_CONFIG), configActiveRange); workbook.setActiveSheet(XlsxTracker.TAB_INDEX_TRACKER); }