List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getRow
@Override public HSSFRow getRow(int rowIndex)
From source file:com.alkacon.opencms.excelimport.CmsExcelContent.java
License:Open Source License
/** * Creates mapping between excel column names set by user and excel column names from excel internal.<p> * /*from w w w. jav a 2 s . c o m*/ * @param in the document input stream * * @throws IOException if something goes wring */ private void readExcelColumnMappings(InputStream in) throws IOException { HSSFWorkbook excelWb = new HSSFWorkbook(in); HSSFSheet sheet = excelWb.getSheetAt(0); int numberOfRows = sheet.getPhysicalNumberOfRows(); if (numberOfRows > 0) { HSSFRow firstRow = sheet.getRow(0); // loop over all columns in first excel row Iterator rowIter = firstRow.cellIterator(); while (rowIter.hasNext()) { // get cell HSSFCell cell = (HSSFCell) rowIter.next(); if (cell != null) { // get user column name String userColName = cell.getStringCellValue(); // get excel column name int excelColName = cell.getCellNum(); CmsExcelColumn excelCol = new CmsExcelColumn(userColName, excelColName); m_colNames.put(userColName, new Integer(excelColName)); m_colContents.put(new Integer(excelColName), excelCol); } } } }
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 w w w . j av a 2 s. 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;/* ww w.j ava 2s.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.asakusafw.testtools.excel.ExcelUtils.java
License:Apache License
/** * ????????/* w ww . j av a 2s .com*/ * @param sheet ? * @param rownum ?? * @param col ?? * @return ?? */ private HSSFCell getCell(HSSFSheet sheet, int rownum, int col) { HSSFRow row = sheet.getRow(rownum); if (isEmpty(row)) { String fmt = "Excel???(), file = %s, sheet = %s, row = %d"; String msg = String.format(fmt, filename, sheet.getSheetName(), rownum); throw new InvalidExcelBookException(msg); } HSSFCell cell = getCell(sheet, row, col); return cell; }
From source file:com.asakusafw.testtools.excel.ExcelUtils.java
License:Apache License
/** * ??????// ww w .j a v a 2 s . c o m * @param sheet ????? * @return ? */ private List<Writable> createDatalList(HSSFSheet sheet) { List<Writable> list = new ArrayList<Writable>(); Class<?> modelClass = getModelClass(); int rownum = 0; // 0?????? // 1??? for (;;) { rownum++; HSSFRow row = sheet.getRow(rownum); if (isEmpty(row)) { break; } Writable model; try { model = (Writable) modelClass.newInstance(); } catch (InstantiationException e) { throw new RuntimeException(e); } catch (IllegalAccessException e) { throw new RuntimeException(e); } for (int col = 0; col < columnInfos.size(); col++) { HSSFCell cell = row.getCell(col, Row.CREATE_NULL_AS_BLANK); MySqlDataType type = columnInfos.get(col).getDataType(); ValueOption<?> vo; switch (type) { case CHAR: case VARCHAR: vo = getStringOption(cell); break; case DATE: vo = getDateOption(cell); break; case DATETIME: case TIMESTAMP: vo = getDateTimeOption(cell); break; case DECIMAL: vo = getDecimalOption(cell); break; case TINY_INT: vo = getByteOption(cell); break; case SMALL_INT: vo = getShortOption(cell); break; case INT: vo = getIntOption(cell); break; case LONG: vo = getLongOption(cell); break; default: throw new RuntimeException("Unsupported data type: " + type); } try { String setterName = columnInfos.get(col).getSetterName(); Method setter = model.getClass().getMethod(setterName, vo.getClass()); setter.invoke(model, vo); } catch (IllegalAccessException e) { throw new RuntimeException(e); } catch (InvocationTargetException e) { throw new RuntimeException(e); } catch (NoSuchMethodException e) { throw new RuntimeException(e); } } list.add(model); } return list; }
From source file:com.asakusafw.testtools.excel.ExcelUtilsTest.java
License:Apache License
/** * ????????/*from w w w . ja v a2 s. com*/ * @param sheet * @param rownum * @param col * @return */ private HSSFCell getCell(HSSFSheet sheet, int rownum, int col) { HSSFRow row = sheet.getRow(rownum); HSSFCell cell = row.getCell(col); return cell; }
From source file:com.asakusafw.testtools.templategen.ExcelBookBuilder.java
License:Apache License
private HSSFCell getCell(HSSFSheet sheet, int rownum, int col) { HSSFRow row = sheet.getRow(rownum); if (row == null) { row = sheet.createRow(rownum);//www . ja v a 2s .c o m } HSSFCell cell = row.getCell(col); if (cell == null) { cell = row.createCell(col); } cell.setCellStyle(commonStyle); return cell; }
From source file:com.astrocytes.core.statistics.LayerStatistics.java
License:Open Source License
public boolean saveLayerStatisticsToXls(Map<Integer, List<Point>> layers, List<Point> astrocyteCenters, List<Point> neuronsCenters, File fileToSave) { try {/* w ww. j a v a2s.co m*/ FileOutputStream outputStream = new FileOutputStream(fileToSave); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(CoreConstants.XLS_SPREADSHEET_TITLE); HSSFRow headerRow = sheet.createRow(0); headerRow.createCell(0).setCellValue(CoreConstants.XLS_LAYERS_TITLE); for (int i = 0; i < layers.size() - 1; i++) { HSSFRow row = sheet.createRow(i + 1); HSSFCell cell = row.createCell(0); cell.setCellType(CellType.STRING); cell.setCellValue(String.valueOf(i < 1 ? i + 1 : (i == 1 ? "2 - 3" : i + 2))); } if (astrocyteCenters != null) { headerRow.createCell(1).setCellValue(CoreConstants.XLS_ASTROCYTES_TITLE); for (Map.Entry<Integer, Integer> count : count(astrocyteCenters, layers).entrySet()) { HSSFRow row = sheet.getRow(count.getKey() + 1); HSSFCell cell = row.createCell(1); cell.setCellType(CellType.NUMERIC); cell.setCellValue(count.getValue()); } } if (neuronsCenters != null) { int colIdx = astrocyteCenters != null ? 2 : 1; headerRow.createCell(colIdx).setCellValue(CoreConstants.XLS_NEURONS_TITLE); for (Map.Entry<Integer, Integer> count : count(neuronsCenters, layers).entrySet()) { HSSFRow row = sheet.getRow(count.getKey() + 1); HSSFCell cell = row.createCell(astrocyteCenters != null ? 2 : 1); cell.setCellType(CellType.NUMERIC); cell.setCellValue(count.getValue()); } } sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); workbook.write(outputStream); outputStream.close(); } catch (IOException e) { e.printStackTrace(); return false; } return true; }
From source file:com.b510.excel.client.HSSFReadWrite.java
License:Apache License
/** * Method main//from w w w . j a va2s . com * * 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) { String[] name = new String[2]; name[0] = "HSSFReadWrite.xlsx"; name[1] = "write"; if (name.length < 1) { System.err.println("At least one argument expected"); return; } String fileName = name[0]; try { if (name.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); } } } } else if (name.length == 2) { if (name[1].toLowerCase().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(name[1]); wb.write(stream); stream.close(); } } else if (name.length == 3 && name[2].toLowerCase().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(name[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(); } } catch (Exception e) { e.printStackTrace(); } }
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); 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;//from w w w . j a va 2s . co m 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()); }