List of usage examples for org.apache.poi.hssf.usermodel HSSFRow getLastCellNum
@Override public short getLastCellNum()
From source file:com.bayareasoftware.chartengine.ds.util.ExcelDump.java
License:Apache License
private static void runOld(String fileName) throws Exception { InputStream is = new FileInputStream(fileName); POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0);// w ww . j a v a2 s .co m int firstRow = sheet.getFirstRowNum(); int lastRow = sheet.getLastRowNum(); p("first/last row: " + firstRow + "/" + lastRow); HSSFRow[] rows = new HSSFRow[lastRow + 1]; int maxFirstCell = 0, maxLastCell = 0; for (int i = firstRow; i <= lastRow; i++) { HSSFRow r = sheet.getRow(i); if (r != null) { rows[i] = r; maxFirstCell = Math.max(maxFirstCell, r.getFirstCellNum()); maxLastCell = Math.max(maxLastCell, r.getLastCellNum()); } } p("maxFirstCell=" + maxFirstCell + ", maxLastCell=" + maxLastCell); StringBuilder table = new StringBuilder(); table.append("<html><head><style>\n"); table.append("body,td { font-family: monospaced; font-size: 12 }\n"); table.append("</style></head>"); table.append("<p>maxFirstCell=" + maxFirstCell + " maxLastCell=" + maxLastCell + "</p>"); table.append("<table border=\"1\">"); for (int i = firstRow; i <= lastRow; i++) { HSSFRow r = sheet.getRow(i); if (r == null) { System.err.println("NULL row at " + i); } table.append(row2string(r, maxLastCell)); } table.append("</table></body></html>"); File f = new File("sheet.html"); Writer w = new FileWriter(f); w.write(table.toString()); w.close(); p("saved to " + f.getAbsolutePath()); }
From source file:com.bayareasoftware.chartengine.ds.util.ExcelDump.java
License:Apache License
private static String row2string(HSSFRow r, int maxCell) { if (r == null) { return ""; }/*from ww w. j av a 2s . c o m*/ StringBuilder sb = new StringBuilder(); sb.append("<tr>"); sb.append("<td>#<b>" + r.getRowNum() + "</b> phys=" + r.getPhysicalNumberOfCells() + "<br/>1st=" + r.getFirstCellNum() + " last=" + r.getLastCellNum() + "</td>"); for (short i = 0; i < maxCell; i++) { HSSFCell c = r.getCell(i); sb.append("<td>" + cell2string(c) + "</td>"); } sb.append("</tr>\n"); return sb.toString(); }
From source file:com.bayareasoftware.chartengine.ds.util.ExcelInference.java
License:Apache License
private HSSFRow inferHeaderRow(int dataRowNum) { if (dataRowNum < 1) return null; HSSFRow titleRow = sheet.getRow(dataRowNum - 1); if (titleRow == null) return null; HSSFRow dataRow = sheet.getRow(dataRowNum); short titleCells = titleRow.getLastCellNum(); short dataCells = dataRow.getLastCellNum(); if (titleCells != dataCells) { /* expect columns in title row same # columns in first row of data */ //p("titleLastCell=" + titleCells + "/dataLastCell=" + dataCells); return null; }//w ww .j a v a 2 s . co m if (true) { return titleRow; } //p("getTitleRow() iterating over " + titleCells + " cells"); Iterator tcs = titleRow.cellIterator(); Iterator dcs = dataRow.cellIterator(); while (tcs.hasNext()) { HSSFCell tc = (HSSFCell) tcs.next(); if (tc.getCellType() != HSSFCell.CELL_TYPE_STRING) return null; HSSFCell dc = (HSSFCell) dcs.next(); if (tc.getCellNum() != dc.getCellNum()) return null; } return titleRow; }
From source file:com.bayareasoftware.chartengine.ds.util.ExcelInference.java
License:Apache License
private int getMaxColumn() { int ret = 0;//from ww w . j av a 2 s .c o m for (int i = 0; i < maxrows && i < sheet.getLastRowNum(); i++) { HSSFRow row = sheet.getRow(i); if (row != null) { int cols = row.getLastCellNum(); ret = Math.max(ret, cols); } } return ret; }
From source file:com.bayareasoftware.chartengine.ds.util.ExcelInference.java
License:Apache License
private Metadata getMetadata(HSSFRow row) { int ncols = row.getLastCellNum() - row.getFirstCellNum(); Metadata md = new Metadata(ncols); eval.setCurrentRow(row); // Workaround for formula evaluator bug int i = 1;//from w ww. j a v a2s . co m for (short s = row.getFirstCellNum(); s < row.getLastCellNum(); s++) { HSSFCell cell = row.getCell(s); int type = getType(cell); if (s == 1) { //p("getMeta(): for cell " + row.getRowNum() + "/" + cell.getCellNum() + // " got type=" + DataType.toString(type)+ " from '" + getCellString(cell)+ "'"); } //if (type == UNKNOWN) return null; md.setColumnType(i++, type); } return md; }
From source file:com.bayareasoftware.chartengine.ds.util.ExcelInference.java
License:Apache License
private void loadStrings() { int last = sheet.getLastRowNum(); int first = sheet.getFirstRowNum(); DateFormat fmt = new SimpleDateFormat("yyyy-MM-dd"); for (int i = 0/*first*/; i < last && i < maxrows; i++) { HSSFRow row = sheet.getRow(i); if (row == null) { rawStrings.add(new String[0]); continue; }//www . j a va2s . co m int count;// = row.getLastCellNum() - row.getFirstCellNum(); count = row.getLastCellNum(); eval.setCurrentRow(row); if (count < 0) { rawStrings.add(new String[0]); continue; } Iterator<Cell> iter = row.cellIterator(); String[] s = new String[count]; while (iter.hasNext()) { Cell cell = iter.next(); //int col = cell.getCellNum(); int col = cell.getRowIndex(); if (col >= 0 && col < count) { s[col] = getCellString(cell, eval, fmt); } else { String msg = "cell at row=" + rawStrings.size() + " column=" + col + " is out of bounds."; throw new RuntimeException(msg); } } rawStrings.add(s); } }
From source file:com.beginner.core.utils.ObjectExcelRead.java
License:Apache License
/** * @param filepath ////w w w. j av a 2 s.com * @param filename //?? * @param startrow //? * @param startcol //? * @param sheetnum //sheet * @return list */ @SuppressWarnings("deprecation") public static List<Object> readExcel(String filepath, String filename, int startrow, int startcol, int sheetnum) { List<Object> varList = new ArrayList<Object>(); try { File target = new File(filepath, filename); FileInputStream fi = new FileInputStream(target); HSSFWorkbook wb = new HSSFWorkbook(fi); HSSFSheet sheet = wb.getSheetAt(sheetnum); //sheet 0 int rowNum = sheet.getLastRowNum() + 1; //??? for (int i = startrow; i < rowNum; i++) { // PageData varpd = new PageData(); HSSFRow row = sheet.getRow(i); // int cellNum = row.getLastCellNum(); //???? for (int j = startcol; j < cellNum; j++) { // HSSFCell cell = row.getCell(Short.parseShort(j + "")); String cellValue = null; if (null != cell) { switch (cell.getCellType()) { // excel????? case 0: cellValue = String.valueOf((int) cell.getNumericCellValue()); break; case 1: cellValue = cell.getStringCellValue(); break; case 2: cellValue = cell.getNumericCellValue() + ""; // cellValue = String.valueOf(cell.getDateCellValue()); break; case 3: cellValue = ""; break; case 4: cellValue = String.valueOf(cell.getBooleanCellValue()); break; case 5: cellValue = String.valueOf(cell.getErrorCellValue()); break; } } else { cellValue = ""; } varpd.put("var" + j, cellValue); } varList.add(varpd); } } catch (Exception e) { System.out.println(e); } return varList; }
From source file:com.cladonia.xngreditor.ImportUtilities.java
License:Open Source License
public static int getFirstColumn(HSSFSheet sheet) throws Exception { int minimum = 0; boolean isFirstTime = true; for (int cnt = sheet.getFirstRowNum(); cnt < sheet.getPhysicalNumberOfRows(); ++cnt) { //get the first row HSSFRow row = sheet.getRow(cnt); boolean found = false; //now find the first column that isn't null or empty short icnt = 0; while ((icnt < row.getLastCellNum()) && (found != true)) { try { HSSFCell cell = row.getCell(icnt); //System.out.println(icnt+":"+cell.getCellType()); if (cell != null) { //System.out.println(cell.getCellType()); if (icnt < minimum) { minimum = icnt;//w ww.ja v a 2 s. com } if (isFirstTime) { minimum = icnt; isFirstTime = false; } found = true; } } catch (NullPointerException e) { // TODO Auto-generated catch block //System.out.println(icnt+" is null"); e.printStackTrace(); } //System.out.println("minimum for row: "+cnt+ " is "+minimum); ++icnt; } } return (minimum); }
From source file:com.cms.utils.ExcelReader.java
public static void copySheets(HSSFSheet newSheet, HSSFSheet sheet, boolean copyStyle) { int maxColumnNum = 0; Map<Integer, HSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, HSSFCellStyle>() : null; for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { HSSFRow srcRow = sheet.getRow(i); HSSFRow destRow = newSheet.createRow(i); if (srcRow != null) { copyRow(sheet, newSheet, srcRow, destRow, styleMap); if (srcRow.getLastCellNum() > maxColumnNum) { maxColumnNum = srcRow.getLastCellNum(); }// www .j a v a 2 s. co m } } for (int i = 0; i <= maxColumnNum; i++) { newSheet.setColumnWidth(i, sheet.getColumnWidth(i)); } }
From source file:com.cms.utils.ExcelReader.java
public static void copyRow(HSSFSheet srcSheet, HSSFSheet destSheet, HSSFRow srcRow, HSSFRow destRow, Map<Integer, HSSFCellStyle> styleMap) { Set<CellRangeAddress> mergedRegions = new TreeSet<CellRangeAddress>(); destRow.setHeight(srcRow.getHeight()); for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) { HSSFCell oldCell = srcRow.getCell(j); HSSFCell newCell = destRow.getCell(j); if (oldCell != null) { if (newCell == null) { newCell = destRow.createCell(j); }/*from w w w . ja va 2s . c o m*/ copyCell(oldCell, newCell, styleMap); CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(), (short) oldCell.getColumnIndex()); if (mergedRegion != null) { CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(), mergedRegion.getFirstColumn(), mergedRegion.getLastRow(), mergedRegion.getLastColumn()); if (isNewMergedRegion(newMergedRegion, mergedRegions)) { mergedRegions.add(newMergedRegion); destSheet.addMergedRegion(newMergedRegion); } } } } }