List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getRow
@Override public XSSFRow getRow(int rownum)
From source file:org.apache.ofbiz.pricat.AbstractPricatParser.java
License:Apache License
public void writeCommentsToFile(XSSFWorkbook workbook, XSSFSheet sheet) { report.println();/*w w w . j a v a 2 s. com*/ report.print(UtilProperties.getMessage(resource, "WriteCommentsBackToExcel", locale), InterfaceReport.FORMAT_NOTE); FileOutputStream fos = null; XSSFCreationHelper factory = workbook.getCreationHelper(); XSSFFont boldFont = workbook.createFont(); boldFont.setFontName("Arial"); boldFont.setBold(true); boldFont.setCharSet(134); boldFont.setFontHeightInPoints((short) 9); XSSFFont plainFont = workbook.createFont(); plainFont.setFontName("Arial"); plainFont.setCharSet(134); plainFont.setFontHeightInPoints((short) 9); XSSFSheet errorSheet = null; if (errorMessages.keySet().size() > 0) { String errorSheetName = UtilDateTime.nowDateString("yyyy-MM-dd HHmm") + " Errors"; errorSheetName = WorkbookUtil.createSafeSheetName(errorSheetName); errorSheet = workbook.createSheet(errorSheetName); workbook.setSheetOrder(errorSheetName, 0); workbook.setActiveSheet(workbook.getSheetIndex(errorSheetName)); XSSFDrawing drawingPatriarch = errorSheet.getDrawingPatriarch(); if (drawingPatriarch == null) { drawingPatriarch = errorSheet.createDrawingPatriarch(); } for (int i = 0; i <= getHeaderRowNo(); i++) { XSSFRow newRow = errorSheet.createRow(i); XSSFRow row = sheet.getRow(i); newRow.setHeight(row.getHeight()); copyRow(row, newRow, factory, drawingPatriarch); } // copy merged regions for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress mergedRegion = sheet.getMergedRegion(i); if (mergedRegion.getFirstRow() < getHeaderRowNo()) { errorSheet.addMergedRegion(mergedRegion); } } // copy images List<XSSFPictureData> pics = workbook.getAllPictures(); List<XSSFShape> shapes = sheet.getDrawingPatriarch().getShapes(); for (int i = 0; i < shapes.size(); i++) { XSSFShape shape = shapes.get(i); XSSFAnchor anchor = shape.getAnchor(); if (shape instanceof XSSFPicture && anchor instanceof XSSFClientAnchor) { XSSFPicture pic = (XSSFPicture) shape; XSSFClientAnchor clientAnchor = (XSSFClientAnchor) anchor; if (clientAnchor.getRow1() < getHeaderRowNo()) { for (int j = 0; j < pics.size(); j++) { XSSFPictureData picture = pics.get(j); if (picture.getPackagePart().getPartName() .equals(pic.getPictureData().getPackagePart().getPartName())) { drawingPatriarch.createPicture(clientAnchor, j); } } } } } } try { // set comments in the original sheet XSSFDrawing patriarch = sheet.getDrawingPatriarch(); for (CellReference cell : errorMessages.keySet()) { if (cell != null && errorMessages.get(cell) != null) { XSSFComment comment = sheet.getCellComment(new CellAddress(cell.getRow(), cell.getCol())); boolean isNewComment = false; if (comment == null) { XSSFClientAnchor anchor = factory.createClientAnchor(); anchor.setDx1(100); anchor.setDx2(100); anchor.setDy1(100); anchor.setDy2(100); anchor.setCol1(cell.getCol()); anchor.setCol2(cell.getCol() + 4); anchor.setRow1(cell.getRow()); anchor.setRow2(cell.getRow() + 4); anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE); comment = patriarch.createCellComment(anchor); isNewComment = true; } XSSFRichTextString rts = factory.createRichTextString("OFBiz PriCat:\n"); rts.applyFont(boldFont); rts.append(errorMessages.get(cell), plainFont); comment.setString(rts); comment.setAuthor("Apache OFBiz PriCat"); if (isNewComment) { sheet.getRow(cell.getRow()).getCell(cell.getCol()).setCellComment(comment); OFBizPricatUtil.formatCommentShape(sheet, cell); } } } // set comments in the new error sheet XSSFDrawing errorPatriarch = errorSheet.getDrawingPatriarch(); int newRowNum = getHeaderRowNo() + 1; Map<Integer, Integer> rowMapping = new HashMap<Integer, Integer>(); for (CellReference cell : errorMessages.keySet()) { if (cell != null && errorMessages.get(cell) != null) { XSSFRow row = sheet.getRow(cell.getRow()); Integer rowNum = Integer.valueOf(row.getRowNum()); int errorRow = newRowNum; if (rowMapping.containsKey(rowNum)) { errorRow = rowMapping.get(rowNum).intValue(); } else { XSSFRow newRow = errorSheet.getRow(errorRow); if (newRow == null) { newRow = errorSheet.createRow(errorRow); } rowMapping.put(rowNum, Integer.valueOf(errorRow)); newRow.setHeight(row.getHeight()); copyRow(row, newRow, factory, errorPatriarch); newRowNum++; } } } // write to file if (sequenceNum > 0L) { File commentedExcel = FileUtil.getFile(tempFilesFolder + userLoginId + "/" + sequenceNum + ".xlsx"); fos = new FileOutputStream(commentedExcel); workbook.write(fos); } else { fos = new FileOutputStream(pricatFile); workbook.write(fos); } fos.flush(); fos.close(); workbook.close(); } catch (FileNotFoundException e) { report.println(e); Debug.logError(e, module); } catch (IOException e) { report.println(e); Debug.logError(e, module); } finally { if (fos != null) { try { fos.close(); } catch (IOException e) { Debug.logError(e, module); } } if (workbook != null) { try { workbook.close(); } catch (IOException e) { Debug.logError(e, module); } } } report.println(UtilProperties.getMessage(resource, "ok", locale), InterfaceReport.FORMAT_OK); report.println(); }
From source file:org.apache.ofbiz.pricat.sample.SamplePricatParser.java
License:Apache License
public boolean existsCurrencyId(XSSFSheet sheet) { report.print(UtilProperties.getMessage(resource, "StartCheckCurrencyId", locale), InterfaceReport.FORMAT_NOTE); XSSFCell currencyIdCell = sheet.getRow(2).getCell(1); currencyId = currencyIdCell.getStringCellValue().trim().toUpperCase(); if (UtilValidate.isEmpty(currencyId)) { String errorMessage = UtilProperties.getMessage(resource, "CurrencyIdRequired", locale); report.println(errorMessage, InterfaceReport.FORMAT_ERROR); errorMessages.put(new CellReference(currencyIdCell), errorMessage); return false; } else {/*from w w w . j a v a2s . c om*/ try { GenericValue currencyUom = delegator.findOne("Uom", UtilMisc.toMap("uomId", currencyId), false); if (!"CURRENCY_MEASURE".equals(currencyUom.getString("uomTypeId"))) { String errorMessage = UtilProperties.getMessage(resource, "CurrencyIdNotCurrency", new Object[] { currencyId }, locale); report.println(errorMessage, InterfaceReport.FORMAT_ERROR); errorMessages.put(new CellReference(currencyIdCell), errorMessage); return false; } } catch (GenericEntityException e) { String errorMessage = UtilProperties.getMessage(resource, "CurrencyIdNotFound", new Object[] { currencyId }, locale); report.println(errorMessage, InterfaceReport.FORMAT_ERROR); errorMessages.put(new CellReference(currencyIdCell), errorMessage); return false; } report.print(UtilProperties.getMessage(resource, "CurrencyIdIs", new Object[] { currencyId }, locale), InterfaceReport.FORMAT_NOTE); report.println(" ... " + UtilProperties.getMessage(resource, "ok", locale), InterfaceReport.FORMAT_OK); } return true; }
From source file:org.apache.ofbiz.pricat.sample.SamplePricatParser.java
License:Apache License
public void parseRowByRow(XSSFSheet sheet) { int rows = sheet.getPhysicalNumberOfRows(); List<Object[]> colNames = ColNamesList.get(pricatFileVersion); int colNumber = colNames.size(); int emptyRowStart = -1; int emptyRowEnd = -1; for (int i = headerRowNo + 1; i < rows; i++) { XSSFRow row = sheet.getRow(i); if (UtilValidate.isEmpty(row) || isEmptyRow(row, colNumber, false)) { if (emptyRowStart == -1) { report.print("(" + (i + 1) + ") ", InterfaceReport.FORMAT_NOTE); emptyRowStart = i;/*from ww w . ja v a 2s. c om*/ } else { emptyRowEnd = i; } continue; } else { if (emptyRowStart != -1) { if (emptyRowEnd != -1) { report.print(" - (" + (emptyRowEnd + 1) + ") ", InterfaceReport.FORMAT_NOTE); } report.print(UtilProperties.getMessage(resource, "ExcelEmptyRow", locale), InterfaceReport.FORMAT_NOTE); report.println(" ... " + UtilProperties.getMessage(resource, "skipped", locale), InterfaceReport.FORMAT_NOTE); emptyRowStart = -1; emptyRowEnd = -1; } } report.print("(" + (i + 1) + ") ", InterfaceReport.FORMAT_NOTE); List<Object> cellContents = getCellContents(row, colNames, colNumber); try { if (parseCellContentsAndStore(row, cellContents)) { report.println(" ... " + UtilProperties.getMessage(resource, "ok", locale), InterfaceReport.FORMAT_OK); } else { report.println(" ... " + UtilProperties.getMessage(resource, "skipped", locale), InterfaceReport.FORMAT_NOTE); } } catch (GenericTransactionException e) { report.println(e); } } if (emptyRowEnd != -1) { report.print(" - (" + (emptyRowEnd + 1) + ") ", InterfaceReport.FORMAT_NOTE); report.print(UtilProperties.getMessage(resource, "ExcelEmptyRow", locale), InterfaceReport.FORMAT_NOTE); report.println(" ... " + UtilProperties.getMessage(resource, "skipped", locale), InterfaceReport.FORMAT_NOTE); } }
From source file:org.apache.ofbiz.pricat.sample.SamplePricatParser.java
License:Apache License
public boolean isTableHeaderMatched(XSSFSheet sheet) { List<Object[]> columnNames = ColNamesList.get(pricatFileVersion); short cols = sheet.getRow(headerRowNo).getLastCellNum(); report.print(UtilProperties.getMessage(resource, "StartCheckHeaderColNum", new Object[] { pricatFileVersion }, locale), InterfaceReport.FORMAT_NOTE); if (cols != columnNames.size()) { report.print(//from w w w . j a v a 2 s . co m UtilProperties.getMessage(resource, "HeaderColNumNotMatch", new Object[] { String.valueOf(cols), String.valueOf(columnNames.size()) }, locale), InterfaceReport.FORMAT_WARNING); if (cols < columnNames.size()) { report.println( UtilProperties.getMessage(resource, "HeaderColNumShortThanRequired", new Object[] { String.valueOf(columnNames.size()) }, locale), InterfaceReport.FORMAT_ERROR); return false; } else { report.println( UtilProperties.getMessage(resource, "UseHeaderColNum", new Object[] { String.valueOf(columnNames.size()) }, locale), InterfaceReport.FORMAT_WARNING); cols = (short) columnNames.size(); } } else { report.println(UtilProperties.getMessage(resource, "ok", locale), InterfaceReport.FORMAT_OK); } report.print(UtilProperties.getMessage(resource, "StartCheckHeaderColLabel", new Object[] { pricatFileVersion }, locale), InterfaceReport.FORMAT_NOTE); boolean foundLabelNotMatch = false; for (int i = 0; i < cols; i++) { String coltext = sheet.getRow(headerRowNo).getCell(i).getStringCellValue().trim(); headerColNames.add(coltext); Object[] versionColumn = columnNames.get(i); if (!coltext.equals(versionColumn[0])) { report.println(UtilProperties.getMessage(resource, "HeaderColLabelNotMatch", new Object[] { String.valueOf(headerRowNo + 1), String.valueOf(i + 1), coltext, versionColumn[0] }, locale), InterfaceReport.FORMAT_ERROR); foundLabelNotMatch = true; } else { report.print(" " + coltext, InterfaceReport.FORMAT_NOTE); if (i < cols - 1) { report.print(",", InterfaceReport.FORMAT_NOTE); } } } if (foundLabelNotMatch) { report.println(); return false; } report.println(" ... " + UtilProperties.getMessage(resource, "ok", locale), InterfaceReport.FORMAT_OK); return true; }
From source file:org.apache.ofbiz.pricat.sample.SamplePricatParser.java
License:Apache License
public boolean isVersionSupported(XSSFSheet sheet) { report.print(UtilProperties.getMessage(resource, "StartCheckPricatVersion", locale), InterfaceReport.FORMAT_NOTE); pricatFileVersion = sheet.getRow(2).getCell(0).getStringCellValue().trim(); if (ColNamesList.containsKey(pricatFileVersion)) { report.print(" " + pricatFileVersion + " ... ", InterfaceReport.FORMAT_NOTE); report.println(UtilProperties.getMessage(resource, "ok", locale), InterfaceReport.FORMAT_OK); } else {/*from w w w . j a v a2s. c o m*/ report.println(UtilProperties.getMessage(resource, "error", locale), InterfaceReport.FORMAT_ERROR); report.println(UtilProperties.getMessage(resource, "PricatVersionNotSupport", new Object[] { pricatFileVersion }, locale), InterfaceReport.FORMAT_ERROR); return false; } return true; }
From source file:org.azkfw.datasource.excel.ExcelDatasourceBuilder.java
License:Apache License
/** * ?// w w w.j a v a 2 s . c om * * @return * @throws FileNotFoundException * @throws ParseException * @throws IOException */ @SuppressWarnings({ "unchecked", "rawtypes" }) public Datasource build() throws FileNotFoundException, ParseException, IOException { ExcelDatasource datasource = new ExcelDatasource(); datasource.name = datasourceName; InputStream stream = null; try { List<Table> tables = new ArrayList<>(); for (File file : excelFiles) { stream = new FileInputStream(file); XSSFWorkbook workbook = new XSSFWorkbook(stream); int cntSheet = workbook.getNumberOfSheets(); for (int i = 0; i < cntSheet; i++) { String sheetName = workbook.getSheetName(i); // sheet name -> table name ExcelTable table = new ExcelTable(); Matcher matcher = PTN_TABLE_NAME.matcher(sheetName); if (matcher.find()) { table.label = matcher.group(3); table.name = matcher.group(1); } else { table.label = sheetName; table.name = sheetName; } XSSFSheet sheet = workbook.getSheetAt(i); // Check row size int cntRow = sheet.getLastRowNum() + 1; if (3 > cntRow) { System.out.println("Skip sheet[" + sheetName + "]. row size < 3"); continue; } // Read Field List<ExcelField> fields = new ArrayList<ExcelField>(); XSSFRow rowLabel = sheet.getRow(0); XSSFRow rowName = sheet.getRow(1); XSSFRow rowType = sheet.getRow(2); for (int col = 0; col < rowLabel.getLastCellNum(); col++) { ExcelField field = readField(col, rowLabel.getCell(col), rowName.getCell(col), rowType.getCell(col)); fields.add(field); } // Read Data List<ExcelRecord> records = new ArrayList<ExcelRecord>(); for (int row = 3; row < cntRow; row++) { XSSFRow xssfrow = sheet.getRow(row); if (!isEmptyRow(xssfrow)) { ExcelRecord record = readData(row, xssfrow, fields); records.add(record); } else { System.out .println("Skip empty row.[table: " + table.getName() + "; row: " + row + ";]"); } } table.fields = (List) fields; table.records = (List) records; tables.add(table); } } datasource.tables = tables; } catch (FileNotFoundException ex) { throw ex; } catch (ParseException ex) { throw ex; } catch (IOException ex) { throw ex; } finally { if (null != stream) { try { stream.close(); } catch (IOException ex) { } finally { stream = null; } } } return datasource; }
From source file:org.azkfw.datasource.excel.ExcelDatasourceFactory.java
License:Apache License
/** * Excel???// ww w. j av a 2 s . c o m * * @param aName ?? * @param aStream Excel * @return */ @SuppressWarnings({ "unchecked", "rawtypes" }) public static Datasource generate(final String aName, final InputStream aStream) throws FileNotFoundException, ParseException, IOException { ExcelDatasource datasource = new ExcelDatasource(); datasource.name = aName; try { List<Table> tables = new ArrayList<>(); XSSFWorkbook workbook = new XSSFWorkbook(aStream); int cntSheet = workbook.getNumberOfSheets(); for (int i = 0; i < cntSheet; i++) { String sheetName = workbook.getSheetName(i); // sheet name -> table name ExcelTable table = new ExcelTable(); Matcher matcher = PTN_TABLE_NAME.matcher(sheetName); if (matcher.find()) { table.label = matcher.group(3); table.name = matcher.group(1); } else { table.label = sheetName; table.name = sheetName; } XSSFSheet sheet = workbook.getSheetAt(i); // Check row size int cntRow = sheet.getLastRowNum() + 1; if (3 > cntRow) { System.out.println("Skip sheet[" + sheetName + "]. row size < 3"); continue; } // Read Field List<ExcelField> fields = new ArrayList<ExcelField>(); XSSFRow rowLabel = sheet.getRow(0); XSSFRow rowName = sheet.getRow(1); XSSFRow rowType = sheet.getRow(2); for (int col = 0; col < rowLabel.getLastCellNum(); col++) { ExcelField field = readField(col, rowLabel.getCell(col), rowName.getCell(col), rowType.getCell(col)); fields.add(field); } // Read Data List<ExcelRecord> records = new ArrayList<ExcelRecord>(); for (int row = 3; row < cntRow; row++) { XSSFRow xssfrow = sheet.getRow(row); if (!isEmptyRow(xssfrow)) { ExcelRecord record = readData(row, xssfrow, fields); records.add(record); } else { System.out.println("Skip empty row.[table: " + table.getName() + "; row: " + row + ";]"); } } table.fields = (List) fields; table.records = (List) records; tables.add(table); } datasource.tables = tables; } catch (FileNotFoundException ex) { throw ex; } catch (ParseException ex) { throw ex; } catch (IOException ex) { throw ex; } finally { if (null != aStream) { try { aStream.close(); } catch (IOException ex) { } } } return datasource; }
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); if (row == null) { continue; }//from ww w .ja v a 2 s . c om 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 singleWorksheet() throws Exception { String sheetName = "Worksheet 1"; String stringValue = "Sample text with chars to escape : < > & \\ \" ' ~ "; Date dateValue = new Date(); LocalDateTime localDateTimeValue = LocalDateTime.now(); ZoneId timezone = ZoneId.of("Australia/Sydney"); ZonedDateTime zonedDateValue = ZonedDateTime.ofInstant(dateValue.toInstant(), timezone); double doubleValue = 1.234; int intValue = 2_016; long longValue = 2_016_000_000_000L; BigDecimal bigDecimalValue = BigDecimal.TEN; byte[] data = writeWorkbook(wb -> { Worksheet ws = wb.newWorksheet(sheetName); int i = 1; ws.value(i, i++, stringValue);//from w ww .j av a 2 s. c o m ws.value(i, i++, dateValue); ws.value(i, i++, localDateTimeValue); ws.value(i, i++, zonedDateValue); ws.value(i, i++, doubleValue); ws.value(i, i++, intValue); ws.value(i, i++, longValue); ws.value(i, i++, bigDecimalValue); try { ws.finish(); } catch (IOException 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(1); XSSFSheet xws = xwb.getSheet(sheetName); @SuppressWarnings("unchecked") Comparable<XSSFRow> row = (Comparable) xws.getRow(0); assertThat(row).isNull(); int i = 1; assertThat(xws.getRow(i).getCell(i++).getStringCellValue()).isEqualTo(stringValue); assertThat(xws.getRow(i).getCell(i++).getDateCellValue()).isEqualTo(dateValue); // Check zoned timestamps have the same textual representation as the Dates extracted from the workbook // (Excel date serial numbers do not carry timezone information) assertThat(DateTimeFormatter.ISO_LOCAL_DATE_TIME.format(ZonedDateTime .ofInstant(xws.getRow(i).getCell(i++).getDateCellValue().toInstant(), ZoneId.systemDefault()))) .isEqualTo(DateTimeFormatter.ISO_LOCAL_DATE_TIME.format(localDateTimeValue)); assertThat(DateTimeFormatter.ISO_LOCAL_DATE_TIME.format(ZonedDateTime .ofInstant(xws.getRow(i).getCell(i++).getDateCellValue().toInstant(), ZoneId.systemDefault()))) .isEqualTo(DateTimeFormatter.ISO_LOCAL_DATE_TIME.format(zonedDateValue)); assertThat(xws.getRow(i).getCell(i++).getNumericCellValue()).isEqualTo(doubleValue); assertThat(xws.getRow(i).getCell(i++).getNumericCellValue()).isEqualTo(intValue); assertThat(xws.getRow(i).getCell(i++).getNumericCellValue()).isEqualTo(longValue); assertThat(new BigDecimal(xws.getRow(i).getCell(i++).getRawValue())).isEqualTo(bigDecimalValue); }
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 w w w . j a v a2 s. 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); } } }