List of usage examples for org.apache.poi.xssf.usermodel XSSFRow getLastCellNum
@Override public short getLastCellNum()
From source file:FormatConvert.exceloperation.Excel2csv.java
public static void copySheets2CSV(XSSFSheet sheet, String csvfile) { int maxColumnNum = 0; Map<Integer, XSSFCellStyle> styleMap = null; try {/* ww w . j ava 2 s . c om*/ FileWriter fw = new FileWriter(csvfile); String str = ""; for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { XSSFRow srcRow = sheet.getRow(i); if (srcRow != null) { System.out.println(srcRow.getLastCellNum()); System.out.println(srcRow.getFirstCellNum()); // System.out.println(srcRow.getCell(srcRow.getLastCellNum()).toString()); for (int j = srcRow.getFirstCellNum(); j < srcRow.getLastCellNum(); j++) { if (srcRow.getCell(j) != null && j != srcRow.getLastCellNum() - 1) { srcRow.getCell(j).setCellType(1); str = str + srcRow.getCell(j).getReference() + ","; } else if (srcRow.getCell(j) != null) { srcRow.getCell(j).setCellType(1); str = str + srcRow.getCell(j).getStringCellValue() + "\r\n"; } // } fw.append(str); } str = ""; } fw.flush(); fw.close(); } catch (IOException ex) { } //Util.copyPictures(newSheet,sheet) ; }
From source file:Import.ProgrammerMode.java
private void getHeader(XSSFSheet sheet) { XSSFRow row = sheet.getRow(1); int lastCellNum = row.getLastCellNum(); for (int index = 0; index < lastCellNum; index++) { validationError = new StringBuffer(); XSSFCell cell = row.getCell(index); log.append(//from w ww.j av a 2s . com "mapStringValidation.put(" + index + ", \"" + getStringCellValueNoSetError(cell) + "\"); \n"); // Date value = getDateCellValueSetError(cell, index); // String value = getStringCellValueNoSetError(cell); // Integer value = getIntegerCellValueSetError(cell, index); // BigDecimal value = getBigDecimalCellValueSetError(cell, index); // logln(index + " : " + value); // log.append("XSSFCell cell" + index + " = row.getCell(" + index + ");\n"); // log.append("String regisNo = getStringCellValueSetError(cell" + index + ", " + index + "); // " + getStringCellValueNoSetError(cell) + "\n\n"); // ShPerson person = createShPerson(); // dao.createShPerson(person); // ShAddress address = createShAddress(person); // dao.addEntity(address); // AcDonateMember donateMember = createAcDonateMember(person); // dao.addEntity(donateMember); // logln(person.getPersonId() + " : " + address.getAddressId() + " : " + donateMember.getDonateMemberId()); TestGUI.refresh(); } }
From source file:net.openchrom.xxd.processor.supplier.rscripting.ui.jobs.LoadXlsxExcelJob.java
License:Open Source License
public static int endOfColumn(XSSFSheet sheet) { int rowCount = endOfRow(sheet); int maxCellNum = 0; for (int rowIndex = 0; rowIndex < rowCount; rowIndex++) { XSSFRow row = sheet.getRow(rowIndex); if (row != null) { maxCellNum = Math.max(maxCellNum, row.getLastCellNum()); }//from w ww. j av a 2 s . co m } return maxCellNum; }
From source file:nl.architolk.ldt.processors.ExcelConverter.java
License:Open Source License
public void generateData(PipelineContext context, ContentHandler contentHandler) throws SAXException { try {/*from ww w . java 2s . c o m*/ // Read binary content of Excel file ByteArrayOutputStream os = new ByteArrayOutputStream(); Base64XMLReceiver base64ContentHandler = new Base64XMLReceiver(os); readInputAsSAX(context, INPUT_DATA, base64ContentHandler); final byte[] fileContent = os.toByteArray(); final java.io.ByteArrayInputStream bais = new ByteArrayInputStream(fileContent); // Create workbook XSSFWorkbook workbook = new XSSFWorkbook(bais); DataFormatter formatter = new DataFormatter(); XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook); contentHandler.startDocument(); contentHandler.startElement("", "workbook", "workbook", new AttributesImpl()); for (int s = 0; s < workbook.getNumberOfSheets(); s++) { XSSFSheet sheet = workbook.getSheetAt(s); AttributesImpl sheetAttr = new AttributesImpl(); sheetAttr.addAttribute("", "name", "name", "CDATA", sheet.getSheetName()); contentHandler.startElement("", "sheet", "sheet", sheetAttr); for (int r = 0; r <= sheet.getLastRowNum(); r++) { XSSFRow row = sheet.getRow(r); if (row != null) { AttributesImpl rowAttr = new AttributesImpl(); rowAttr.addAttribute("", "id", "id", "CDATA", Integer.toString(r)); contentHandler.startElement("", "row", "row", rowAttr); for (int c = 0; c < row.getLastCellNum(); c++) { XSSFCell cell = row.getCell(c); if (cell != null) { try { String cellvalue = formatter.formatCellValue(cell, evaluator); if (cellvalue != "") { AttributesImpl columnAttr = new AttributesImpl(); columnAttr.addAttribute("", "id", "id", "CDATA", Integer.toString(cell.getColumnIndex())); contentHandler.startElement("", "column", "column", columnAttr); contentHandler.characters(cellvalue.toCharArray(), 0, cellvalue.length()); contentHandler.endElement("", "column", "column"); } } catch (Exception e) { } } } contentHandler.endElement("", "row", "row"); } } contentHandler.endElement("", "sheet", "sheet"); } contentHandler.endElement("", "workbook", "workbook"); contentHandler.endDocument(); } catch (IOException e) { throw new OXFException(e); } }
From source file:offishell.excel.Excel.java
License:MIT License
private XSSFRow findFirstBlankRow() { XSSFRow head = sheet.getRow(0); // compute head size int headerSize = 0; for (; headerSize < head.getLastCellNum(); headerSize++) { Cell cell = head.getCell(headerSize); if (cell == null || cell.getCellTypeEnum() == CellType.BLANK) { headerSize--;/*from w w w . j av a 2s . co m*/ break; } } row: for (int i = 1; i < sheet.getLastRowNum(); i++) { XSSFRow row = sheet.getRow(i); if (row == null) { row = sheet.createRow(i); row.setHeightInPoints(30f); } for (int j = 0; j < headerSize; j++) { XSSFCell cell = row.getCell(j); if (cell == null) { XSSFCell created = row.createCell(j); created.setCellStyle(baseStyle); } else if (cell.getCellTypeEnum() != CellType.BLANK) { continue row; } } return row; } XSSFRow row = sheet.getRow(sheet.getLastRowNum()); if (row == null) { row = sheet.createRow(sheet.getLastRowNum()); row.setHeightInPoints(30f); } for (int j = 0; j < headerSize; j++) { XSSFCell cell = row.getCell(j); if (cell == null) { XSSFCell created = row.createCell(j); created.setCellStyle(baseStyle); } } return row; }
From source file:org.azkfw.datasource.excel.ExcelDatasourceBuilder.java
License:Apache License
/** * ?//from ww w . j a va 2s.c o m * * @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.ExcelDatasourceBuilder.java
License:Apache License
private boolean isEmptyRow(final XSSFRow aRow) { for (int col = 0; col < aRow.getLastCellNum(); col++) { XSSFCell cell = aRow.getCell(col); String value = toStringFromCell(cell); if (0 < value.length()) { return false; }// w w w . j a v a2 s . c o m } return true; }
From source file:org.azkfw.datasource.excel.ExcelDatasourceFactory.java
License:Apache License
/** * Excel???// w w w. ja v 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.azkfw.datasource.excel.ExcelDatasourceFactory.java
License:Apache License
private static boolean isEmptyRow(final XSSFRow aRow) { for (int col = 0; col < aRow.getLastCellNum(); col++) { XSSFCell cell = aRow.getCell(col); String value = toStringFromCell(cell); if (0 < value.length()) { return false; }// ww w. j av a 2 s .c om } return true; }
From source file:org.exoplatform.services.document.impl.MSXExcelDocumentReader.java
License:Open Source License
/** * Returns only a text from .xlsx file content. * /*from w ww. j a va 2s. co 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(); }