List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getRow
@Override public XSSFRow getRow(int rownum)
From source file:org.easybatch.extensions.msexcel.MsExcelSupportIntegrationTest.java
License:Open Source License
@Test public void integrationTest() throws Exception { File inputTweets = new File(this.getClass().getResource("/tweets-in.xlsx").toURI()); File outputTweets = new File(this.getClass().getResource("/tweets-out.xlsx").toURI()); String[] fields = { "id", "user", "message" }; Job job = JobBuilder.aNewJob().reader(new MsExcelRecordReader(inputTweets)) .mapper(new MsExcelRecordMapper<>(Tweet.class, fields)) .marshaller(new MsExcelRecordMarshaller<>(Tweet.class, fields)) .writer(new MsExcelRecordWriter(outputTweets, SHEET_NAME)).build(); JobReport report = new JobExecutor().execute(job); assertThat(report).isNotNull();//w w w . ja v a 2 s . co m assertThat(report.getMetrics().getReadCount()).isEqualTo(2); assertThat(report.getMetrics().getWriteCount()).isEqualTo(2); assertThat(report.getStatus()).isEqualTo(JobStatus.COMPLETED); XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(outputTweets)); XSSFSheet sheet = workbook.getSheet(SHEET_NAME); XSSFRow row = sheet.getRow(1); assertThat(row.getCell(0).getNumericCellValue()).isEqualTo(1.0); assertThat(row.getCell(1).getStringCellValue()).isEqualTo("foo"); assertThat(row.getCell(2).getStringCellValue()).isEqualTo("hi"); row = sheet.getRow(2); assertThat(row.getCell(0).getNumericCellValue()).isEqualTo(2.0); assertThat(row.getCell(1).getStringCellValue()).isEqualTo("bar"); assertThat(row.getCell(2).getStringCellValue()).isEqualTo("hello"); }
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; XSSFRow row;/* w w w. j a v a 2 s. co m*/ 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 w w w . j a v a 2 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 ww. j a v a 2 s . co 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.isatools.isacreatorconfigurator.configui.io.Utils.java
License:Open Source License
public static String createTableConfigurationEXL(String outputDir, Map<MappingObject, List<Display>> tableFields) throws DataNotCompleteException, InvalidFieldOrderException, IOException { String excelFileName = "ISA-config-template.xlsx"; FileOutputStream fos = new FileOutputStream(outputDir + File.separator + excelFileName); String tableName = ""; XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet hiddenSheet = workbook.createSheet("hiddenCV"); Map<String, List<String>> nodups = new HashMap<String, List<String>>(); XSSFSheet ontologyRestriction = workbook.createSheet("Restrictions"); XSSFRow ontorow0 = ontologyRestriction.createRow((short) 0); ontorow0.createCell(0).setCellValue("Column Name"); ontorow0.createCell(1).setCellValue("Ontology"); ontorow0.createCell(2).setCellValue("Branch"); ontorow0.createCell(3).setCellValue("Version"); CreationHelper factory = workbook.getCreationHelper(); // int counting=0; // int ontocounter=0; int lastposition = 0; for (MappingObject mo : tableFields.keySet()) { tableName = mo.getAssayName().replace("\\s", ""); List<Display> elements = tableFields.get(mo); System.out.println("creating worksheet: " + tableName); //we create a table with 50 records by default for anything that is not an investigation file if (!tableName.contains("investigation")) { XSSFSheet tableSheet = workbook.createSheet(tableName); Drawing drawing = tableSheet.createDrawingPatriarch(); CellStyle style = workbook.createCellStyle(); XSSFRow rowAtIndex;//from ww w . ja v a 2s.c om //we create 51 rows by default for each table for (int index = 0; index <= 50; index++) { rowAtIndex = tableSheet.createRow((short) index); } //the first row is the header we need to build from the configuration declaration XSSFRow header = tableSheet.getRow(0); //we now iterated through the element found in the xml table configuration for (int fieldIndex = 0; fieldIndex < elements.size(); fieldIndex++) { if (elements.get(fieldIndex).getFieldDetails() != null) { if (elements.get(fieldIndex).getFieldDetails().isRequired() == true) { XSSFCell cell = header.createCell(fieldIndex); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); font.setColor(IndexedColors.RED.index); cell.setCellStyle(style); //create the header field by setting to FieldName as Cell name cell.setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); System.out.println("REQUIRED field number " + fieldIndex + " is: " + elements.get(fieldIndex).getFieldDetails().getFieldName()); //using the ISA field description to create a Comment attached to the set ClientAnchor anchor = factory.createClientAnchor(); Comment comment = drawing.createCellComment(anchor); RichTextString rts = factory.createRichTextString( elements.get(fieldIndex).getFieldDetails().getDescription()); comment.setString(rts); cell.setCellComment(comment); tableSheet.autoSizeColumn(fieldIndex); } else { XSSFCell cell = header.createCell(fieldIndex); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index); style.setFillPattern(CellStyle.SOLID_FOREGROUND); font.setColor(IndexedColors.BLACK.index); cell.setCellStyle(style); //create the header field by setting to FieldName as Cell name cell.setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); //using the ISA field description to create a Comment attached to the set ClientAnchor anchor = factory.createClientAnchor(); Comment comment = drawing.createCellComment(anchor); RichTextString rts = factory.createRichTextString( elements.get(fieldIndex).getFieldDetails().getDescription()); comment.setString(rts); cell.setCellComment(comment); tableSheet.autoSizeColumn(fieldIndex); } //checking if the field requires controled values, i.e ISA datatype is List if (elements.get(fieldIndex).getFieldDetails().getDatatype() == DataTypes.LIST) { //create a hidden spreadsheet and named range with the list of val //counting++; //incrementing the counter defining the position where to start the new namedrange in the hidden spreadsheet //obtain the name of the ISA fields and extracting key information needed to create a unique name for the Named Range to be String rangeName = elements.get(fieldIndex).getFieldDetails().getFieldName() .replace("'", "").replace(" ", "").replace("Comment[", "") .replace("ParameterValue[", "").replace("Characteristics[", "").replace("]", "") .replace("(", "").replace(")", ""); //getting all the values allowed by the List Field String[] fieldValues = elements.get(fieldIndex).getFieldDetails().getFieldList(); //System.out.println("CV : "+elements.get(fieldIndex).getFieldDetails().getFieldName()+ " values: " + Arrays.asList(fieldValues).toString()+ "size :" +fieldValues.length); //iterating through the values and creating a cell for each for (int j = 0; j < fieldValues.length; j++) { hiddenSheet.createRow(lastposition + j).createCell(0).setCellValue(fieldValues[j]); } Name namedCell = workbook.createName(); workbook.getNumberOfNames(); int k = 0; int position = 0; //this is to handle ISA Fields sharing the same name (in different assays) //namedRanges in Excel must be unique while (k < workbook.getNumberOfNames()) { //we can the total number of field to type list we have found so far. //something already exists... if (workbook.getNameAt(k).equals(rangeName)) { // namedCell.setNameName(workbook.getNameAt(k).toString()); //no need to go further, we exit here and set the parameter position to use the value position = k; k = -1; } else { k++; } } if (k > 0) { //this means this field already existed list of that type //we name the new cell after it namedCell.setNameName(rangeName + k); System.out.println("Name Name: " + namedCell.getNameName()); } else { //there is already one, so we just point back to it using the position parameter namedCell.setNameName(workbook.getNameAt(k).toString()); //workbook.getNameAt(position).toString() System.out.println("Name Name: " + namedCell.getNameName()); } int start = 0; int end = 0; start = lastposition + 1; System.out.println("start: + " + start); end = lastposition + fieldValues.length; System.out.println("end: + " + end); // String reference ="hiddenCV"+"!"+convertNumToColString(0)+start+":"+ convertNumToColString(0)+end; String reference = "hiddenCV" + "!$" + convertNumToColString(0) + "$" + start + ":$" + convertNumToColString(0) + "$" + end; namedCell.setRefersToFormula(reference); start = 0; end = 0; DataValidationHelper validationHelper = new XSSFDataValidationHelper(tableSheet); DataValidationConstraint constraint = validationHelper .createFormulaListConstraint(reference); CellRangeAddressList addressList = new CellRangeAddressList(1, 50, fieldIndex, fieldIndex); System.out.println("field index: " + fieldIndex); DataValidation dataValidation = validationHelper.createValidation(constraint, addressList); tableSheet.addValidationData(dataValidation); lastposition = lastposition + fieldValues.length; System.out.println("lastposition: + " + lastposition); System.out.println("reference: " + reference); } // //TODO: reformat date but this is pain in Excel // if (elements.get(fieldIndex).getFieldDetails().getDatatype()== DataTypes.DATE) { // //do something // } // If a default value has been specified in the ISAconfiguration, we set it in the Excel spreadsheet if (elements.get(fieldIndex).getFieldDetails().getDefaultVal() != null) { for (int i = 1; i < 51; i++) { rowAtIndex = tableSheet.getRow(i); XSSFCell cellThere = rowAtIndex.createCell(fieldIndex); cellThere.setCellValue(elements.get(fieldIndex).getFieldDetails().getDefaultVal()); } } if (elements.get(fieldIndex).getFieldDetails().getDatatype() == DataTypes.ONTOLOGY_TERM) { int count = elements.get(fieldIndex).getFieldDetails().getRecommmendedOntologySource() .values().size(); Collection<RecommendedOntology> myList = elements.get(fieldIndex).getFieldDetails() .getRecommmendedOntologySource().values(); for (RecommendedOntology recommendedOntology : myList) { System.out.println("ONTOLOGY :" + recommendedOntology.getOntology()); try { if (recommendedOntology.getOntology() != null) { ArrayList<String> ontoAttributes = new ArrayList<String>(); ontoAttributes.add(recommendedOntology.getOntology().getOntologyID()); ontoAttributes.add(recommendedOntology.getOntology().getOntologyVersion()); // ontocounter++; // XSSFRow ontoRowj = ontologyRestriction.createRow(ontocounter); // ontoRowj.createCell(0).setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); // ontoRowj.createCell(1).setCellValue(recommendedOntology.getOntology().getOntologyID()); // ontoRowj.createCell(3).setCellValue(recommendedOntology.getOntology().getOntologyVersion()); if (recommendedOntology.getBranchToSearchUnder() != null) { System.out.println("ONTOLOGY BRANCH :" + recommendedOntology.getBranchToSearchUnder()); // ontoRowj.createCell(2).setCellValue(recommendedOntology.getBranchToSearchUnder().toString()); ontoAttributes .add(recommendedOntology.getBranchToSearchUnder().toString()); } else { ontoAttributes.add(""); } nodups.put(elements.get(fieldIndex).getFieldDetails().getFieldName(), ontoAttributes); } } catch (NullPointerException npe) { System.out.println(npe); } } } } } } else { //we now create with the Investigation Sheet XSSFSheet tableSheet = workbook.createSheet(tableName); Drawing drawing = tableSheet.createDrawingPatriarch(); CellStyle style = workbook.createCellStyle(); Font font = workbook.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); for (int fieldIndex = 0; fieldIndex < elements.size(); fieldIndex++) { XSSFRow row = tableSheet.createRow((short) fieldIndex); if (elements.get(fieldIndex).getFieldDetails() != null) { XSSFCell cell = row.createCell(0); //create the header field by setting to FieldName as Cell name cell.setCellValue(elements.get(fieldIndex).getFieldDetails().getFieldName()); //using the ISA field description to create a Comment attached to the set ClientAnchor anchor = factory.createClientAnchor(); Comment comment = drawing.createCellComment(anchor); RichTextString rts = factory .createRichTextString(elements.get(fieldIndex).getFieldDetails().getDescription()); comment.setString(rts); cell.setCellComment(comment); cell.setCellStyle(style); tableSheet.autoSizeColumn(fieldIndex); SheetConditionalFormatting sheetCF = tableSheet.getSheetConditionalFormatting(); //condition: if the output of the FIND function is equal to 1, then, set cell to a blue font ConditionalFormattingRule rule = sheetCF .createConditionalFormattingRule("FIND(Investigation,$A$1:$A$21)>1"); //ConditionalFormattingRule rule = sheetCF.createConditionalFormattingRule(ComparisonOperator.) ; FontFormatting font1 = rule.createFontFormatting(); font1.setFontStyle(false, true); font1.setFontColorIndex(IndexedColors.BLUE.index); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:A21") }; sheetCF.addConditionalFormatting(regions, rule); } } tableSheet.setSelected(true); workbook.setSheetOrder(tableName, 0); } } //writes the values of ontology resources used to restrict selection in ISA fields int compteur = 1; for (Map.Entry<String, List<String>> entry : nodups.entrySet()) { String key = entry.getKey(); // Object value = entry.getValue(); System.out.println("UNIQUE RESOURCE: " + key); XSSFRow ontoRowj = ontologyRestriction.createRow(compteur); ontoRowj.createCell(0).setCellValue(key); ontoRowj.createCell(1).setCellValue(entry.getValue().get(0)); ontoRowj.createCell(2).setCellValue(entry.getValue().get(2)); ontoRowj.createCell(3).setCellValue(entry.getValue().get(1)); compteur++; } //moving support worksheet to be the rightmost sheets in the workbook. //if the table corresponds to the study sample table, we move it to first position if (tableName.toLowerCase().contains("studysample")) { workbook.setSheetOrder(tableName, 1); } workbook.setSheetOrder("hiddenCV", tableFields.keySet().size() + 1); workbook.setSheetOrder("Restrictions", tableFields.keySet().size() + 1); workbook.write(fos); fos.close(); String message = "Files have been saved in "; if (outputDir.equals("")) { message += "this programs directory"; } else { message += outputDir; } return message; }
From source file:org.kitodo.production.plugin.importer.massimport.PicaMassImport.java
License:Open Source License
private List<Record> getRecordsForXLSX(InputStream xls) throws IOException { List<Record> records = new ArrayList<>(); XSSFWorkbook wb = new XSSFWorkbook(xls); XSSFSheet sheet = wb.getSheetAt(0); // first sheet // loop over all rows for (int j = 0; j <= sheet.getLastRowNum(); j++) { // loop over all cells XSSFRow row = sheet.getRow(j); if (Objects.nonNull(row)) { for (int i = 0; i < row.getLastCellNum(); i++) { XSSFCell cell = row.getCell(i); // changing all cell types to String cell.setCellType(HSSFCell.CELL_TYPE_STRING); Record record = changeCellTypeToString(cell, i, j); if (Objects.nonNull(record)) { records.add(record); }/*from ww w.j av a2 s .c om*/ } } } return records; }
From source file:org.kuali.test.runner.output.PoiHelper.java
License:Educational Community License
private void copySheets(XSSFSheet newSheet, XSSFSheet sheet) { int maxColumnNum = 0; Map<Integer, XSSFCellStyle> styleMap = new HashMap<Integer, XSSFCellStyle>(); int mergedReqionsCount = sheet.getNumMergedRegions(); for (int i = 0; i < mergedReqionsCount; ++i) { newSheet.addMergedRegion(sheet.getMergedRegion(i)); }//from w ww . j ava 2s . c o m for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { XSSFRow srcRow = sheet.getRow(i); XSSFRow destRow = newSheet.createRow(i); if (srcRow != null) { copyRow(srcRow, destRow, styleMap); if (srcRow.getLastCellNum() > maxColumnNum) { maxColumnNum = srcRow.getLastCellNum(); } } } for (int i = 0; i <= maxColumnNum; i++) { newSheet.setColumnWidth(i, sheet.getColumnWidth(i)); } }
From source file:org.natica.expense.ExpenseUtility.java
public List<Expense> parseExcel(File file) throws IOException, ExpenseExcelFormatException { List<Expense> expenses = new ArrayList<Expense>(); FileInputStream fis;/* w w w .j a v a 2 s .c o m*/ fis = new FileInputStream(file); XSSFWorkbook wb; wb = new XSSFWorkbook(fis); XSSFSheet sh = wb.getSheetAt(0); for (Row row : sh) { if (row.getRowNum() == 0) { if (!checkHeaderRow(sh.getRow(0))) throw new ExpenseExcelFormatException("Excel Balk simleri Hataldr."); else continue; } Expense e = new Expense(); for (Cell cell : row) { if (cell.getColumnIndex() == 0) { if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { if (!HSSFDateUtil.isCellDateFormatted(cell)) { throw new ExpenseExcelFormatException("Hatal veri! Satr:" + row.getRowNum() + " Stun:" + cell.getColumnIndex() + " Hata Nedeni: Veri tipi tarih deil"); } } else { throw new ExpenseExcelFormatException("Hatal veri! Satr:" + row.getRowNum() + " Stun:" + cell.getColumnIndex() + " Hata Nedeni: Veri tipi tarih deil"); } e.setExpenseEntryDate(cell.getDateCellValue()); } else if (cell.getColumnIndex() == 1) { if (cell.getCellType() != Cell.CELL_TYPE_STRING) { throw new ExpenseExcelFormatException("Hatal veri! Satr:" + row.getRowNum() + " Stun:" + cell.getColumnIndex() + " Hata Nedeni: Veri tipi yaz deil"); } e.setProjectName(cell.getStringCellValue()); } else if (cell.getColumnIndex() == 2) { if (cell.getCellType() != Cell.CELL_TYPE_STRING) { throw new ExpenseExcelFormatException("Hatal veri! Satr:" + row.getRowNum() + " Stun:" + cell.getColumnIndex() + " Hata Nedeni: Veri tipi yaz deil"); } e.setExpenseName(cell.getStringCellValue()); } else if (cell.getColumnIndex() == 3) { if (cell.getCellType() != Cell.CELL_TYPE_STRING) { throw new ExpenseExcelFormatException("Hatal veri! Satr:" + row.getRowNum() + " Stun:" + cell.getColumnIndex() + " Hata Nedeni: Veri tipi yaz deil"); } e.setPaymentMethod(cell.getStringCellValue()); } else if (cell.getColumnIndex() == 4) { if (cell.getCellType() != Cell.CELL_TYPE_STRING) { throw new ExpenseExcelFormatException("Hatal veri! Satr:" + row.getRowNum() + " Stun:" + cell.getColumnIndex() + " Hata Nedeni: Veri tipi yaz deil"); } e.setCurrency(cell.getStringCellValue()); } else if (cell.getColumnIndex() == 5) { if (cell.getCellType() != Cell.CELL_TYPE_NUMERIC) { throw new ExpenseExcelFormatException("Hatal veri! Satr:" + row.getRowNum() + " Stun:" + cell.getColumnIndex() + " Hata Nedeni: Veri tipi nmerik deil"); } e.setNetAmount(BigDecimal.valueOf(cell.getNumericCellValue())); } else if (cell.getColumnIndex() == 6) { if (cell.getCellType() != Cell.CELL_TYPE_STRING) { throw new ExpenseExcelFormatException("Hatal veri! Satr:" + row.getRowNum() + " Stun:" + cell.getColumnIndex() + " Hata Nedeni: Veri tipi yaz deil"); } e.setRestaurant(cell.getStringCellValue()); } else if (cell.getColumnIndex() == 7) { if (cell.getCellType() != Cell.CELL_TYPE_NUMERIC) { throw new ExpenseExcelFormatException("Hatal veri! Satr:" + row.getRowNum() + " Stun:" + cell.getColumnIndex() + " Hata Nedeni: Veri tipi nmerik deil"); } e.setDocumentNumber(Integer.valueOf((int) cell.getNumericCellValue())); } } expenses.add(e); } if (wb != null) wb.close(); if (fis != null) fis.close(); return expenses; }
From source file:org.obiba.mica.dataset.search.rest.harmonization.ExcelContingencyWriter.java
License:Open Source License
private void writeTableHeaders(XSSFSheet sheet, String title, List<String> headers) { int colNum = headers.size() + 1; IntStream.rangeClosed(0, 3).forEach(i -> { Row rowTemp = sheet.createRow(i); IntStream.rangeClosed(0, colNum).forEach(j -> rowTemp.createCell(j)); });/*w ww . j av a 2 s.c om*/ Row row = sheet.getRow(0); Cell titleLabel = row.getCell(0); titleLabel.setCellValue(title); titleLabel.setCellStyle(titleStyle); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, colNum)); row = sheet.getRow(2); Cell crossVarLabel = row.getCell(0); crossVarLabel.setCellValue(this.crossVariable.getName()); sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0)); Cell varLabel = row.getCell(1); varLabel.setCellValue(this.variable.getName()); sheet.addMergedRegion(new CellRangeAddress(2, 2, 1, colNum - 1)); Cell totalLabel = row.getCell(colNum); totalLabel.setCellValue("Total"); sheet.addMergedRegion(new CellRangeAddress(2, 3, colNum, colNum)); int cellnum = 1; row = sheet.getRow(3); for (String h : headers) { Cell cell = row.getCell(cellnum++); cell.setCellValue(h); } addMergedStyles(sheet, new CellRangeAddress(2, 3, 0, colNum)); }
From source file:org.obiba.mica.dataset.search.rest.harmonization.ExcelContingencyWriter.java
License:Open Source License
private void addMergedStyles(final XSSFSheet sheet, final CellRangeAddress r) { IntStream.rangeClosed(r.getFirstRow(), r.getLastRow()).forEach(i -> { final Row temp = sheet.getRow(i); IntStream.rangeClosed(r.getFirstColumn(), r.getLastColumn()) .forEach(j -> temp.getCell(j).setCellStyle(headerStyle)); });//from www . j a va 2s .co m }