List of usage examples for org.apache.poi.hssf.usermodel HSSFCell getColumnIndex
@Override
public int getColumnIndex()
From source file:poi.HSSFReadWrite.java
License:Apache License
/** * Method main// w w w .jav a 2 s. c o m * * Given 1 argument takes that as the filename, inputs it and dumps the * cell values/types out to sys.out.<br/> * * given 2 arguments where the second argument is the word "write" and the * first is the filename - writes out a sample (test) spreadsheet * see {@link HSSFReadWrite#testCreateSampleSheet(String)}.<br/> * * given 2 arguments where the first is an input filename and the second * an output filename (not write), attempts to fully read in the * spreadsheet and fully write it out.<br/> * * given 3 arguments where the first is an input filename and the second an * output filename (not write) and the third is "modify1", attempts to read in the * spreadsheet, deletes rows 0-24, 74-99. Changes cell at row 39, col 3 to * "MODIFIED CELL" then writes it out. Hence this is "modify test 1". If you * take the output from the write test, you'll have a valid scenario. */ public static void main(String[] args) { if (args.length < 1) { System.err.println("At least one argument expected"); return; } String fileName = args[0]; try { if (args.length < 2) { HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s)."); for (int c = 0; c < cells; c++) { HSSFCell cell = row.getCell(c); String value = null; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_FORMULA: value = "FORMULA value=" + cell.getCellFormula(); break; case HSSFCell.CELL_TYPE_NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case HSSFCell.CELL_TYPE_STRING: value = "STRING value=" + cell.getStringCellValue(); break; default: } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } // wb.close(); } else if (args.length == 2) { if (args[1].toLowerCase(Locale.ROOT).equals("write")) { System.out.println("Write mode"); long time = System.currentTimeMillis(); HSSFReadWrite.testCreateSampleSheet(fileName); System.out.println("" + (System.currentTimeMillis() - time) + " ms generation time"); } else { System.out.println("readwrite test"); HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(args[1]); wb.write(stream); stream.close(); // wb.close(); } } else if (args.length == 3 && args[2].toLowerCase(Locale.ROOT).equals("modify1")) { // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to string "MODIFIED CELL!!" HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); FileOutputStream stream = new FileOutputStream(args[1]); HSSFSheet sheet = wb.getSheetAt(0); for (int k = 0; k < 25; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } for (int k = 74; k < 100; k++) { HSSFRow row = sheet.getRow(k); sheet.removeRow(row); } HSSFRow row = sheet.getRow(39); HSSFCell cell = row.getCell(3); cell.setCellValue("MODIFIED CELL!!!!!"); wb.write(stream); stream.close(); // wb.close(); } } catch (Exception e) { e.printStackTrace(); } }
From source file:ReadExcel.HSSFReadWrite.java
License:Apache License
private static void startReadXlsFile(String fileName) { try {/*from w w w . ja v a2 s . c o m*/ HSSFWorkbook wb = HSSFReadWrite.readxlsFile(fileName); System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s)."); for (int c = 0; c < cells; c++) { HSSFCell cell = row.getCell(c); String value = null; switch (cell.getCellTypeEnum()) { case FORMULA: value = "FORMULA value=" + cell.getCellFormula(); break; case NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case STRING: value = "STRING value=" + cell.getStringCellValue(); break; default: } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } wb.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:ro.nextreports.engine.exporter.util.XlsUtil.java
License:Apache License
/** * Copy a row from a sheet to another sheet * //from w w w . ja v a 2 s . c o m * * @param srcSheet the sheet to copy * @param destSheet the sheet to copy into * @param parentSheetRow the row inside destSheet where we start to copy * @param parentSheetColumn the column inside destSheet where we start to copy * @param srcRow the row to copy * @param destRow the row to create * @param styleMap style map * */ public static void copyRow(HSSFSheet srcSheet, HSSFSheet destSheet, int parentSheetRow, int parentSheetColumn, HSSFRow srcRow, HSSFRow destRow, Map<Integer, HSSFCellStyle> styleMap) { // manage a list of merged zone in order to not insert two times a // merged zone Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>(); destRow.setHeight(srcRow.getHeight()); // pour chaque row for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) { HSSFCell oldCell = srcRow.getCell(j); // ancienne cell if (oldCell != null) { HSSFCell newCell = destRow.createCell(parentSheetColumn + j); copyCell(oldCell, newCell, styleMap); CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(), (short) oldCell.getColumnIndex()); if (mergedRegion != null) { CellRangeAddress newMergedRegion = new CellRangeAddress( parentSheetRow + mergedRegion.getFirstRow(), parentSheetRow + mergedRegion.getLastRow(), parentSheetColumn + mergedRegion.getFirstColumn(), parentSheetColumn + mergedRegion.getLastColumn()); CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion); if (isNewMergedRegion(wrapper, mergedRegions)) { mergedRegions.add(wrapper); destSheet.addMergedRegion(wrapper.range); } } } } }
From source file:vone.HSSFReadWrite.java
License:Apache License
/** * Method main/* w ww . java 2 s . c o m*/ * * Given 1 argument takes that as the filename, inputs it and dumps the cell * values/types out to sys.out.<br/> * * given 2 arguments where the second argument is the word "write" and the * first is the filename - writes out a sample (test) spreadsheet see * {@link HSSFReadWrite#testCreateSampleSheet(String)}.<br/> * * given 2 arguments where the first is an input filename and the second an * output filename (not write), attempts to fully read in the spreadsheet * and fully write it out.<br/> * * given 3 arguments where the first is an input filename and the second an * output filename (not write) and the third is "modify1", attempts to read * in the spreadsheet, deletes rows 0-24, 74-99. Changes cell at row 39, col * 3 to "MODIFIED CELL" then writes it out. Hence this is "modify test 1". * If you take the output from the write test, you'll have a valid scenario. */ @SuppressWarnings("deprecation") public static void main(String[] args) { if (args.length < 1) { System.err.println("At least one argument expected"); return; } String fileName = args[0]; try { if (args.length < 2) { HSSFWorkbook wb = HSSFReadWrite.readFile(fileName); System.out.println("Data dump:\n"); for (int k = 0; k < wb.getNumberOfSheets(); k++) { HSSFSheet sheet = wb.getSheetAt(k); int rows = sheet.getPhysicalNumberOfRows(); System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s)."); for (int r = 0; r < rows; r++) { HSSFRow row = sheet.getRow(r); if (row == null) { continue; } // int cells = row.getPhysicalNumberOfCells(); int cells = row.getLastCellNum(); System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s)."); for (int c = 0; c < cells; c++) { HSSFCell cell = row.getCell(c); String value = null; if (cell == null) { System.out.println("CELL col=" + c + " VALUE= Empty"); continue; } switch (cell.getCellTypeEnum()) { case FORMULA: value = "FORMULA value=" + cell.getCellFormula(); break; case NUMERIC: value = "NUMERIC value=" + cell.getNumericCellValue(); break; case STRING: value = "STRING value=" + cell.getStringCellValue(); break; default: } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } wb.close(); } } catch (Exception e) { e.printStackTrace(); } }
From source file:ypcnv.views.impl.FileXLS.java
License:Open Source License
/** * With respect to cell type get cell's content. * //from ww w .j a v a2 s . c om * @param cell * - cell to be processed. * @return Cell's content. */ private String getCellContent(HSSFCell cell) { int foundCellType = cell.getCellType(); String cellContent = null; switch (foundCellType) { case Cell.CELL_TYPE_STRING: cellContent = cell.getStringCellValue(); break; case Cell.CELL_TYPE_FORMULA: cellContent = cell.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: cellContent = cell.getBooleanCellValue() ? "TRUE" : "FALSE"; break; case Cell.CELL_TYPE_ERROR: byte xlsErrorCode = cell.getErrorCellValue(); if (xlsErrorCode == 0) { cellContent = ""; } else { cellContent = "XLS error code '" + xlsErrorCode + "'."; String message = "XLS cell type is 'ERROR', the XLS error code is '" + xlsErrorCode + "'." + " The cell row=" + cell.getRowIndex() + ", col=" + cell.getColumnIndex() + "."; LOG.info(message); } break; case Cell.CELL_TYPE_NUMERIC: HSSFDataFormatter numericFormat = new HSSFDataFormatter(); cellContent = numericFormat.formatCellValue(cell); // cellContent = cell.getNumericCellValue(); break; default: cellContent = ""; break; } return cellContent; }