List of usage examples for org.apache.poi.hssf.usermodel HSSFRow getFirstCellNum
@Override public short getFirstCellNum()
From source file:at.spardat.xma.mdl.grid.GridPOIAdapter.java
License:Open Source License
/** * Transfers the spreadsheet data from the POI <code>HSSFWorkbook</code> into the <code>IGridWMServer</code>. * Only the sheet on the given sheetIndex is copied. //from ww w . j a v a 2s .co m * @param igrid the XMA model where to copy the data * @param book the POI represntation of the data * @param sheetIndex the index of the sheet to copy * @return a list containing all SysExceptions describing problems with individual cell formulas or ranges */ public static List poi2xma(IGridWM igrid, HSSFWorkbook book, int sheetIndex) { GridWM grid = (GridWM) igrid; try { List errorList = new ArrayList(); grid.setSheetName(book.getSheetName(sheetIndex)); grid.colors.clear(); grid.initBuildInColors(); short ic = GridWM.HSSF_FIRST_COLOR_INDEX; HSSFPalette palette = book.getCustomPalette(); for (HSSFColor color = palette.getColor(ic); ic < 64 && color != null; color = palette.getColor(++ic)) { grid.colors.add(ic, new GridColor(color.getTriplet())); } grid.fonts.clear(); int numFonts = book.getNumberOfFonts(); if (numFonts > 4) { // adjust for "There is no 4" see code of org.apache.poi.hssf.model.Workbook.getFontRecordAt() numFonts += 1; } for (short i = 0; i < numFonts; i++) { HSSFFont font = book.getFontAt(i); byte fontstyle = GridFont.FONT_NORML; if (font.getBoldweight() >= HSSFFont.BOLDWEIGHT_BOLD) fontstyle |= GridFont.FONT_BOLD; if (font.getItalic()) fontstyle |= GridFont.FONT_ITALIC; grid.fonts.add(i, new GridFont(font.getFontName(), fontstyle, font.getColor())); } grid.styles.clear(); for (short i = 0, numStyles = book.getNumCellStyles(); i < numStyles; i++) { HSSFCellStyle style = book.getCellStyleAt(i); grid.styles.add(i, new GridCellStyle(style.getFontIndex(), style.getFillForegroundColor())); } grid.namedRanges.clear(); for (int i = 0, numRanges = book.getNumberOfNames(); i < numRanges; i++) { HSSFName name = book.getNameAt(i); String rangeName = name.getNameName(); String rangeRef = null; try { // ranges not defined but referenced by formulas have a name but no reference in HSSF rangeRef = name.getReference(); } catch (Exception exc) { errorList.add(new SysException(exc, ((GridWM) grid).getMessage("inconsistentRange", rangeName)) .setCode(GridWM.CODE_inconsistentRange)); } if (rangeRef != null) { try { GridRange range = grid.getJeksDelegate().toRange(rangeRef); range.setKey(rangeName); grid.namedRanges.put(rangeName, range); } catch (Exception exc) { errorList.add(new SysException(exc, ((GridWM) grid).getMessage("unsupportedReference", rangeName, rangeRef)) .setCode(GridWM.CODE_unsupportedReference)); } } } grid.rows.clear(); grid.cols.clear(); grid.cells.clear(); grid.delegate = new GridJeksDelegate(grid); HSSFSheet sheet = book.getSheetAt(sheetIndex); int firstColNum = Integer.MAX_VALUE; int lastColNum = Integer.MIN_VALUE; for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { HSSFRow row = sheet.getRow(i); if (row == null) continue; if (row.getFirstCellNum() >= 0) firstColNum = Math.min(firstColNum, row.getFirstCellNum()); lastColNum = Math.max(lastColNum, row.getLastCellNum()); if (lastColNum > 255) lastColNum = 255; for (short j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) { HSSFCell hssfcell = row.getCell(j); if (hssfcell == null) continue; GridCell gridcell = grid.getOrCreateCell(i, j); switch (hssfcell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: break; case HSSFCell.CELL_TYPE_BOOLEAN: gridcell.setValue(hssfcell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: // TODO: recherche error text byte errorCode = hssfcell.getErrorCellValue(); // gridcell.setValue(errorCode); gridcell.setValue("#ERROR"); errorList.add(new SysException(((GridWM) grid).getMessage("errorRecord", grid.getJeksDelegate().toExcelRef(i, j), Byte.toString(errorCode))) .setCode(GridWM.CODE_errorRecord)); break; case HSSFCell.CELL_TYPE_FORMULA: String formula = null; try { formula = hssfcell.getCellFormula(); gridcell.setFormula(formula); } catch (SysException e) { if (formula != null) gridcell.setValue("=" + formula); //set it as text without interpretation errorList.add(e); } break; case HSSFCell.CELL_TYPE_NUMERIC: if (isDateCell(book, hssfcell)) { gridcell.setValue(hssfcell.getDateCellValue()); } else { gridcell.setValue(hssfcell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_STRING: gridcell.setValue(hssfcell.getStringCellValue()); break; default: throw new SysException("unknown cell type " + hssfcell.getCellType()); } gridcell.setEditable(!hssfcell.getCellStyle().getLocked()); gridcell.setStyle(hssfcell.getCellStyle().getIndex()); } } final int scalefactor = 256 / 7; // empirically testet // int width = sheet.getDefaultColumnWidth(); // returns nonsense // width = width/scalefactor; // grid.setDefaultColumnWidth(width); for (short i = (short) firstColNum; i <= lastColNum; i++) { int width = sheet.getColumnWidth(i); width = width / scalefactor; grid.getOrCreateColumn(i).setWidth(width); } if (firstColNum == Integer.MAX_VALUE) firstColNum = 0; if (lastColNum == Integer.MIN_VALUE) lastColNum = 0; grid.setMaxRange( new GridRange(grid, sheet.getFirstRowNum(), firstColNum, sheet.getLastRowNum(), lastColNum)); grid.setVisibleRange(grid.getMaxRange()); return errorList; } finally { grid.handle(grid.new GridReloadEvent()); } }
From source file:ch.elexis.core.importer.div.importers.ExcelWrapper.java
License:Open Source License
/** * Return a row of data from the sheet.//from w w w .j a v a2 s.c om * * @param rowNr * zero based index of the desired row * @return a List of Strings with the row values or null if no such row exists. */ public List<String> getRow(final int rowNr) { HSSFRow row = sheet.getRow(rowNr); if (row == null) { return null; } ArrayList<String> ret = new ArrayList<String>(); short first = 0; short last = 100; if (types != null) { last = (short) (types.length); } else { first = row.getFirstCellNum(); last = row.getLastCellNum(); } for (short i = first; i < last; i++) { HSSFCell cell = row.getCell(i); if (cell != null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: ret.add(""); //$NON-NLS-1$ break; case HSSFCell.CELL_TYPE_BOOLEAN: ret.add(Boolean.toString(cell.getBooleanCellValue())); break; case HSSFCell.CELL_TYPE_NUMERIC: if (types != null) { if (types[i].equals(Integer.class)) { ret.add(Long.toString(Math.round(cell.getNumericCellValue()))); } else if (types[i].equals(TimeTool.class)) { Date date = cell.getDateCellValue(); if (date != null) { TimeTool tt = new TimeTool(date.getTime()); ret.add(tt.toString(TimeTool.FULL_MYSQL)); } else { ret.add(""); //$NON-NLS-1$ } } else if (types[i].equals(Double.class)) { ret.add(Double.toString(cell.getNumericCellValue())); break; } else /* if(types[i].equals(String.class)) */ { double cv = cell.getNumericCellValue(); // String r=Double.toString(cv); String r = NumberFormat.getNumberInstance().format(cv); ret.add(r); } break; } // else fall thru case HSSFCell.CELL_TYPE_FORMULA: ret.add(Double.toString(cell.getNumericCellValue())); break; case HSSFCell.CELL_TYPE_STRING: ret.add(cell.toString()); break; default: ret.add(Messages.ExcelWrapper_ErrorUnknownCellType); } } else { // empty cell ret.add(""); //$NON-NLS-1$ } } return ret; }
From source file:cn.trymore.core.util.excel.PoiExcelParser.java
License:Open Source License
@SuppressWarnings("rawtypes") public String xlsToHtml(int sheetIdx) throws Exception { if ((this.book == null) && (this.sheet == null)) { open(sheetIdx);/*from w w w . ja va 2 s.c o m*/ } StringBuilder sb = new StringBuilder(); sb.append(new StringBuilder().append("<table cellspacing=\"0\" style=\"width:").append(this.htmlTbWidth) .append("px;table-layout:fixed\">").toString()); Iterator itor = this.sheet.rowIterator(); while (itor.hasNext()) { HSSFRow row = (HSSFRow) itor.next(); sb.append("<tr>"); int i = 0; for (int size = row.getLastCellNum() - row.getFirstCellNum(); i < size; ++i) { HSSFCell cell = (HSSFCell) getCell(row.getRowNum(), i); sb.append(new StringBuilder().append("<td ").append(getCellStyle(cell)).append(">").toString()); sb.append(getCellContent(cell)); sb.append("</td>"); } sb.append("</tr>"); } sb.append("</table>"); return sb.toString(); }
From source file:cn.trymore.core.util.excel.PoiExcelParser.java
License:Open Source License
public Integer getColumnCount() { HSSFRow headerRow = this.sheet.getRow(0); int count = headerRow.getLastCellNum() - headerRow.getFirstCellNum(); //return Integer.valueOf((this.sheet != null) ? headerRow.getRowNum() // : 0); //return null; return Integer.valueOf(count); }
From source file:com.allinfinance.bo.impl.risk.T40201BOTarget.java
License:Open Source License
public String importFile(List<File> fileList, List<String> fileNameList, Operator operator) throws Exception { HSSFWorkbook workbook = null;//from ww w. j a va 2s. c o m HSSFSheet sheet = null; HSSFRow row = null; // ? String returnMsg = ""; // ?? int fileNameIndex = 0; // ?? String fileName = null; // ?? String saCardNo = null; // ?? String saLimitAmt = null; // ? String saAction = null; // String saBrhId = operator.getOprBrhId(); // ? String saOprId = operator.getOprId(); // String saInitTime = CommonFunction.getCurrentDateTime(); TblCtlCardInf tblCtlCardInf = null; FileInputStream fileInputStream = null; for (File file : fileList) { fileInputStream = new FileInputStream(file); workbook = new HSSFWorkbook(fileInputStream); sheet = workbook.getSheetAt(0); fileName = fileNameList.get(fileNameIndex); for (int rowIndex = sheet.getFirstRowNum(); rowIndex <= sheet.getLastRowNum(); rowIndex++) { row = sheet.getRow(rowIndex); for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) if (row.getCell(i).getCellType() != HSSFCell.CELL_TYPE_STRING) returnMsg += "[ " + fileName + " ]" + (row.getRowNum() + 1) + "" + "" + (i + 1) + "???<br>"; if (!"".equals(returnMsg)) return returnMsg; saCardNo = row.getCell(0).getStringCellValue(); // ?? if (saCardNo.getBytes().length > 19) return "[ " + fileName + " ]" + (row.getRowNum() + 1) + "???<br>"; saLimitAmt = row.getCell(1).getStringCellValue(); // saLimitAmt = CommonFunction.transYuanToFen(saLimitAmt); // ?? if (!CommonFunction.isAllDigit(saLimitAmt)) return "[ " + fileName + " ]" + (row.getRowNum() + 1) + "????<br>"; if (saLimitAmt.getBytes().length > 12) return "[ " + fileName + " ]" + (row.getRowNum() + 1) + "???<br>"; saAction = row.getCell(2).getStringCellValue(); // ? if (!("1".equals(saAction) || "2".equals(saAction) || "3".equals(saAction) || "4".equals(saAction))) return "[ " + fileName + " ]" + (row.getRowNum() + 1) + "???<br>"; // ??????? tblCtlCardInf = new TblCtlCardInf(); tblCtlCardInf.setId(saCardNo); tblCtlCardInf.setSaLimitAmt(saLimitAmt); tblCtlCardInf.setSaAction(saAction); tblCtlCardInf.setSaInitZoneNo(saBrhId); tblCtlCardInf.setSaInitOprId(saOprId); tblCtlCardInf.setSaInitTime(saInitTime); tblCtlCardInfDAO.saveOrUpdate(tblCtlCardInf); } fileInputStream.close(); fileNameIndex++; } return Constants.SUCCESS_CODE; }
From source file:com.allinfinance.bo.impl.risk.T40202BOTarget.java
License:Open Source License
@SuppressWarnings("unchecked") public String importFile(List<File> fileList, List<String> fileNameList, Operator operator) throws Exception { HSSFWorkbook workbook = null;//w w w . j a va2 s . c o m HSSFSheet sheet = null; HSSFRow row = null; // ? String returnMsg = ""; // ?? int fileNameIndex = 0; // ?? String fileName = null; String sql = null; // List<Object[]> dataList = null; // ?? String saMerNo = null; // ?? String saMerChName = null; // ?? String saMerEnName = null; // ? String saZoneNo = null; // ?? String saLimitAmt = null; // ? String saAction = null; // String saBrhId = operator.getOprBrhId(); // ? String saOprId = operator.getOprId(); // String saInitTime = CommonFunction.getCurrentDateTime(); TblCtlMchtInf tblCtlMchtInf = null; FileInputStream fileInputStream = null; for (File file : fileList) { fileInputStream = new FileInputStream(file); workbook = new HSSFWorkbook(fileInputStream); sheet = workbook.getSheetAt(0); fileName = fileNameList.get(fileNameIndex); for (int rowIndex = sheet.getFirstRowNum(); rowIndex <= sheet.getLastRowNum(); rowIndex++) { row = sheet.getRow(rowIndex); for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) if (row.getCell(i).getCellType() != HSSFCell.CELL_TYPE_STRING) returnMsg += "[ " + fileName + " ]" + (row.getRowNum() + 1) + "" + "" + (i + 1) + "???<br>"; if (!"".equals(returnMsg)) return returnMsg; saMerNo = row.getCell(0).getStringCellValue(); // ? if (saMerNo.getBytes().length > 15) return "[ " + fileName + " ]" + (row.getRowNum() + 1) + "???<br>"; sql = "select mcht_nm,eng_name,bank_no from TBL_MCHT_BASE_INF where MCHT_NO = '" + saMerNo + "'"; dataList = CommonFunction.getCommQueryDAO().findBySQLQuery(sql); if (dataList.size() == 0) return "[ " + fileName + " ]" + (row.getRowNum() + 1) + "??<br>"; // ?? saMerChName = dataList.get(0)[0].toString(); // ?? saMerEnName = dataList.get(0)[1].toString(); saZoneNo = dataList.get(0)[2].toString(); saLimitAmt = row.getCell(1).getStringCellValue(); // saLimitAmt = CommonFunction.transYuanToFen(saLimitAmt); // ?? if (!CommonFunction.isAllDigit(saLimitAmt)) return "[ " + fileName + " ]" + (row.getRowNum() + 1) + "????<br>"; if (saLimitAmt.getBytes().length > 12) return "[ " + fileName + " ]" + (row.getRowNum() + 1) + "???<br>"; saAction = row.getCell(2).getStringCellValue(); // ? if (!("1".equals(saAction) || "2".equals(saAction) || "3".equals(saAction) || "4".equals(saAction))) return "[ " + fileName + " ]" + (row.getRowNum() + 1) + "???<br>"; // ?????? tblCtlMchtInf = new TblCtlMchtInf(); tblCtlMchtInf.setId(saMerNo); tblCtlMchtInf.setSaMerChName(saMerChName); tblCtlMchtInf.setSaMerEnName(saMerEnName); tblCtlMchtInf.setSaZoneNo(saZoneNo); tblCtlMchtInf.setSaLimitAmt(saLimitAmt); tblCtlMchtInf.setSaAction(saAction); tblCtlMchtInf.setSaInitZoneNo(saBrhId); tblCtlMchtInf.setSaInitOprId(saOprId); tblCtlMchtInf.setSaInitTime(saInitTime); tblCtlMchtInfDAO.saveOrUpdate(tblCtlMchtInf); } fileInputStream.close(); fileNameIndex++; } return Constants.SUCCESS_CODE; }
From source file:com.bayareasoftware.chartengine.ds.ExcelDataStream.java
License:Apache License
List<String[]> getRawData(int maxRows) { List<String[]> ret = new ArrayList<String[]>(); int last = sheet.getLastRowNum(); int first = sheet.getFirstRowNum(); for (int i = first; i < last && i < maxRows; i++) { HSSFRow row = sheet.getRow(i); if (row == null) { ret.add(new String[0]); continue; }/*from w ww. j a va 2 s .c om*/ Iterator<Cell> iter = (Iterator<Cell>) row.cellIterator(); int count = row.getLastCellNum() - row.getFirstCellNum(); String[] s = new String[count]; int j = 0; while (iter.hasNext() && j < count) { s[j] = getCellString(iter.next()); j++; } ret.add(s); /* positionRowIterator(i); String[] s = new String[rowData.length]; for (int j = 0; j < s.length; j++) { s[j] = this.getString(i + 1); } re t.add(s); */ } return ret; }
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 a 2 s . c om 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 w w w . j av a2 s.com 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 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 ww w . ja v a2s. c o 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; }