List of usage examples for org.apache.poi.ss.usermodel Row getCell
Cell getCell(int cellnum);
From source file:com.benasmussen.maven.plugin.i18n.io.ResourceReader.java
License:Apache License
/** * Get cell value as string/*from w w w . java2 s. c o m*/ * * @param sheetName * @param row * @param col * @return */ public String getCellValue(String sheetName, int row, int col) { Sheet sheet = getSheetByName(sheetName); Row r = sheet.getRow(row); if (r != null) { Cell c = r.getCell(col); if (c != null) { return c.getStringCellValue(); } } return null; }
From source file:com.beyondb.io.ExcelControl.java
@Override public Object[][] readTableContent() throws IOException, InvalidFormatException, Exception { try {//from w ww .j av a 2 s . c om //OPCPackage pkg = OPCPackage.open(file); // InputStream m_InputStream = new FileInputStream(m_File); Sheet sheet = null; // if (!m_InputStream.markSupported()) { // m_InputStream = new PushbackInputStream(m_InputStream, 8); // } // if (POIFSFileSystem.hasPOIFSHeader(m_InputStream)) { // HSSFWorkbook hSSFWorkbook = new HSSFWorkbook(m_InputStream); // sheet = (Sheet)hSSFWorkbook.getSheetAt(0); // // } else if (POIXMLDocument.hasOOXMLHeader(m_InputStream)) { // XSSFWorkbook xSSFWorkbook = new XSSFWorkbook(OPCPackage.open(m_File)); // sheet = (Sheet)xSSFWorkbook.getSheetAt(0); // } // else { // throw new IllegalArgumentException("excel?poi??"); // } sheet = getSheet(); if (sheet != null) { if (sheet.getLastRowNum() == 0) { throw new Exception("Excel"); } //? m_RowNum = sheet.getLastRowNum() + 1; // m_ColumnNum = sheet.getRow(0).getPhysicalNumberOfCells(); m_ColumnNum = sheet.getRow(0).getLastCellNum(); m_TableStr = new Object[m_RowNum][m_ColumnNum]; for (int rindex = 0; rindex < m_RowNum; rindex++) { Row row = sheet.getRow(rindex); for (int cindex = 0; cindex < m_ColumnNum; cindex++) { Cell cell = row.getCell(cindex); if (cell == null) { m_TableStr[rindex][cindex] = ""; } else { String value = ""; switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING: // System.out.println(cell.getRichStringCellValue().getString()); value = cell.getRichStringCellValue().getString().replace("\n", ""); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { // System.out.println(cell.getDateCellValue()); value = cell.getDateCellValue().toString(); } else { DecimalFormat df = new DecimalFormat("#"); value = String.valueOf(cell.getNumericCellValue()); double d = cell.getNumericCellValue(); int dInt = (int) d; BigDecimal b1 = new BigDecimal(value); BigDecimal b2 = new BigDecimal(Integer.toString(dInt)); double dPoint = b1.subtract(b2).doubleValue(); if (dPoint == 0) { //? value = df.format(cell.getNumericCellValue()); } } break; case Cell.CELL_TYPE_BOOLEAN: // System.out.println(cell.getBooleanCellValue()); value = cell.getBooleanCellValue() + ""; break; case Cell.CELL_TYPE_FORMULA: // System.out.println(cell.getCellFormula()); value = cell.getCellFormula(); break; case Cell.CELL_TYPE_BLANK: value = ""; default: // System.out.println(); value = ""; } m_TableStr[row.getRowNum()][cell.getColumnIndex()] = value; } } } } } catch (IOException | InvalidFormatException e) { Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "excel??", e); throw e; } catch (Exception ex) { Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "excel??", ex); throw ex; } finally { m_InputStream.close(); } return m_TableStr; }
From source file:com.beyondb.io.ExcelControl.java
@Override public boolean deleteColumn(int[] columnIndex) throws FileNotFoundException, IOException, InvalidFormatException { boolean flag = true; Sheet sheet = null;/*from w w w. jav a 2s. c o m*/ try { sheet = getSheet(); if (sheet == null) { return false; } for (int i = 0; i <= sheet.getLastRowNum(); i++) { //? Row tmpRow = sheet.getRow(i); for (int j = columnIndex.length - 1; j > -1; j--) { //???? for (int k = columnIndex[j]; k < tmpRow.getLastCellNum(); k++) { Cell tmpCell = tmpRow.getCell(k); if (null != tmpCell) { tmpRow.removeCell(tmpCell); } Cell rightCell = tmpRow.getCell(k + 1); if (null != rightCell) { HSSFRow hr = (HSSFRow) tmpRow; hr.moveCell((HSSFCell) rightCell, (short) k); } } } } m_InputStream.close(); try ( // Write the output to a file final FileOutputStream fileOut = new FileOutputStream(m_File)) { m_Workerbook.write(fileOut); } } catch (FileNotFoundException ex) { flag = false; Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "", ex); throw ex; } catch (IOException | InvalidFormatException ex) { flag = false; Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "", ex); throw ex; } return flag; }
From source file:com.beyondb.io.ExcelControl.java
@Override public boolean addColumn(Object[] columnName, Object[][] columnData) throws FileNotFoundException, IOException, InvalidFormatException { boolean flag = true; Row rowCaption;// ww w .ja v a2 s.co m Sheet sheet = null; try { sheet = getSheet(); if (sheet == null) { return false; } // rowCaption = sheet.getRow(0); if (rowCaption != null) { int columnsCount = rowCaption.getLastCellNum(); for (int i = 0; i < columnName.length; i++) { Cell cell = rowCaption.createCell(columnsCount + i); cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(String.valueOf(columnName[i])); } for (int i = 0; i < sheet.getLastRowNum(); i++) { //? Row tmpRow = sheet.getRow(i + 1); for (int cIndex = 0; cIndex < columnName.length; cIndex++) { Cell cell = tmpRow.getCell(columnsCount + cIndex); if (cell == null) { cell = tmpRow.createCell(columnsCount + cIndex); } //? Object obj = columnData[i][cIndex]; if (obj.getClass().getName().equals(Double.class.getName())) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); } else if (obj.getClass().getName().equals(String.class.getName())) { cell.setCellType(Cell.CELL_TYPE_STRING); } else { //? cell.setCellType(Cell.CELL_TYPE_STRING); } setCellValue(cell, obj); } } } m_InputStream.close(); try ( // Write the output to a file FileOutputStream fileOut = new FileOutputStream(m_File)) { m_Workerbook.write(fileOut); } } catch (FileNotFoundException ex) { flag = false; Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "", ex); throw ex; } catch (IOException | InvalidFormatException ex) { flag = false; Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "", ex); throw ex; } return flag; }
From source file:com.beyondb.io.ExcelControl.java
@Override public boolean updateColumn(int[] columnIndexs, Object[][] columnData) throws FileNotFoundException, IOException, InvalidFormatException { boolean flag = true; Sheet sheet = null;/*from w w w .j av a 2s . co m*/ try { sheet = getSheet(); if (sheet == null) { return false; } for (int i = 0; i < sheet.getLastRowNum(); i++) { //? Row tmpRow = sheet.getRow(i + 1); for (int j = 0; j < columnIndexs.length; j++) { Cell cell = tmpRow.getCell(columnIndexs[j]); if (cell != null) { setCellValue(cell, columnData[i][j]); } } } m_InputStream.close(); try ( // Write the output to a file final FileOutputStream fileOut = new FileOutputStream(m_File)) { m_Workerbook.write(fileOut); } } catch (FileNotFoundException ex) { flag = false; Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "?", ex); throw ex; } catch (IOException | InvalidFormatException ex) { flag = false; Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "?", ex); throw ex; } return flag; }
From source file:com.binlist.binlistreader.BinlistReader.java
public static void main(String[] args) { String result[] = null;/*from w w w .j a v a 2s . co m*/ String folder = "/opt/"; String sourceFileName = "binlist.xls"; FileInputStream fis = null; try { fis = new FileInputStream(folder + sourceFileName); HSSFWorkbook workbook = new HSSFWorkbook(fis); HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); Iterator<Cell> cellIterator = null; Row row = null; Cell cell = null; int cellNo = 0; String binno = ""; String longUrl = ""; String shortUrl = ""; int rownum = 0; while (rowIterator.hasNext()) { rownum++; if (rownum == 1) continue; row = rowIterator.next(); cellIterator = row.cellIterator(); cellNo = 0; binno = ""; longUrl = ""; shortUrl = ""; cell = row.getCell(4); if (cell != null && cell.getStringCellValue() != null) { binno = cell.getStringCellValue(); } if (binno != null && binno.length() > 5) { result = postRequestV3(binno); System.out.println("rownum..:" + rownum + " binno..:" + binno + " result..:" + result.length); if (result != null) { row.getCell(5).setCellValue(checkNull(result[0], "")); row.getCell(6).setCellValue(checkNull(result[1], "")); row.getCell(7).setCellValue(checkNull(result[2], "")); } } } System.out.println("rownum..:" + rownum); fis.close(); FileOutputStream out = new FileOutputStream(folder + "newfile/" + sourceFileName); workbook.write(out); out.close(); workbook.close(); workbook = null; } catch (Exception e) { e.printStackTrace(); } finally { if (fis != null) try { fis.close(); } catch (IOException ex) { Logger.getLogger(BinlistReader.class.getName()).log(Level.SEVERE, null, ex); } fis = null; } }
From source file:com.blackducksoftware.tools.commonframework.standard.datatable.reader.DataTableReaderExcel.java
License:Apache License
@Override public void read(DataTable dataTable) throws Exception { workbook = new XSSFWorkbook(inputStream); Sheet sheet = workbook.getSheetAt(0); int rowIndex = 0; for (Row row : sheet) { log.debug("\nRow: " + rowIndex++); Record rec = new Record(dataTable.getRecordDef()); int colIndex = 0; for (FieldDef fieldDef : dataTable.getRecordDef()) { log.debug("Col: " + colIndex + ": " + fieldDef.getName() + ": " + fieldDef.getDescription()); Cell cell = row.getCell(colIndex++); readCell(rec, fieldDef, cell); }/* ww w .j av a 2 s . c o m*/ dataTable.add(rec); } }
From source file:com.blackducksoftware.tools.commonframework.standard.datatable.writer.DataTableWriterExcelManual.java
License:Apache License
@Test public void testMultiSheet() throws Exception { RecordDef recordDef = createSimpleRecordDef(); DataTable dataSet = new DataTable(recordDef); for (int i = 0; i < DataSetWriterExcel.EXCEL_MAX_ROWS; i++) { Record record = new Record(recordDef); for (FieldDef fieldDef : recordDef) { record.setFieldValue(fieldDef.getName(), fieldDef.getName() + " test value " + i); }/*from w w w .j av a 2 s . co m*/ dataSet.add(record); } DataSetWriterExcel writer = new DataSetWriterExcel(); // Pass a filename // if you want an // output file writer.write(dataSet); Workbook wb = writer.getWorkbook(); assertEquals(2, wb.getNumberOfSheets()); // Second sheet Sheet sheet = wb.getSheetAt(1); assertEquals(2, sheet.getLastRowNum()); // Last row Row row = sheet.getRow(2); assertEquals("applicationVersion test value 1048575", row.getCell(1).getStringCellValue()); }
From source file:com.blackducksoftware.tools.commonframework.standard.protex.report.template.TemplateReader.java
License:Apache License
/** * Called by the TemplateReader.//w w w.j ava2 s . com * * @param sheet * the sheet * @param templateSheet * the template sheet * @throws Exception * the exception */ private void populateColumns(Sheet sheet, TemplateSheet templateSheet) throws Exception { Map<String, TemplateColumn> columnMap = templateSheet.getColumnMap(); Row headerRow = sheet.getRow(0); if (headerRow == null) { throw new Exception("No header row found! Please create one."); } Row styleRow = sheet.getRow(1); if (styleRow == null) { throw new Exception( "Sheet name " + templateSheet.getSheetName() + ": No style row found! Please create one."); } for (int i = 0; i < headerRow.getPhysicalNumberOfCells(); i++) { TemplateColumn column = new TemplateColumn(); Cell headerCell = headerRow.getCell(i); Cell styleCell = styleRow.getCell(i); if (headerCell == null) { throw new Exception("The following column appears to be empty: " + i); } if (styleCell == null) { throw new Exception("The following style position is not defined: " + i); } String columnName = headerCell.getStringCellValue(); // We want to use the style cell (row below) as the header will // always be text. Integer cellType = styleCell.getCellType(); column.setColumnPos(i); column.setCellStyle(styleCell.getCellStyle()); column.setColumnName(columnName); column.setCellType(cellType); if (cellType == Cell.CELL_TYPE_FORMULA) { column.setCellFormula(styleCell.getCellFormula()); } columnMap.put(columnName, column); } populateColumnMappings(columnMap); }
From source file:com.blackducksoftware.tools.commonframework.standard.protex.report.template.TemplateReader.java
License:Apache License
/** * Populate columns.// w w w . ja v a 2s. c o m * * @param sheet * the sheet * @param templateSheet * the template sheet * @param columnNames * the column names * @throws Exception * the exception */ private void populateColumns(Sheet sheet, TemplateSheet templateSheet, List<String> columnNames) throws Exception { Map<String, TemplateColumn> columnMap = templateSheet.getColumnMap(); Row headerRow = sheet.getRow(0); if (headerRow == null) { throw new Exception("No header row found!"); } for (int i = 0; i < headerRow.getPhysicalNumberOfCells(); i++) { Cell headerCell = headerRow.getCell(i); if (headerCell == null) { throw new Exception("The following header column is empty: " + i); } String headerName = headerCell.getStringCellValue().trim(); if (columnNames.contains(headerName)) { log.info("Processing user specified column: " + headerName); TemplateColumn column = new TemplateColumn(); column.setColumnPos(i); column.setColumnName(headerName); columnMap.put(headerName, column); } } }