List of usage examples for org.apache.poi.hssf.usermodel HSSFRow getFirstCellNum
@Override public short getFirstCellNum()
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 www.j ava 2 s. com*/ 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); } } } } }
From source file:com.cn.util.ExcelImport.java
/** * ?2003excel//from w ww .j a va2 s. co m * @param file * @return */ private static List<List<Object>> read2003Excel(InputStream inputStream) throws IOException { List<List<Object>> dataList = new ArrayList<>(); HSSFWorkbook wb = new HSSFWorkbook(inputStream); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = null; HSSFCell cell = null; Object val = null; DecimalFormat df = new DecimalFormat("0");// ? SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// ? for (int i = sheet.getFirstRowNum(); i < sheet.getPhysicalNumberOfRows(); i++) { row = sheet.getRow(i); if (row == null) { continue; } List<Object> objList = new ArrayList<>(); for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) { cell = row.getCell(j); if (cell == null) { val = null; objList.add(val); continue; } switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: val = cell.getStringCellValue(); break; case HSSFCell.CELL_TYPE_NUMERIC: if ("@".equals(cell.getCellStyle().getDataFormatString())) { val = df.format(cell.getNumericCellValue()); } else if ("General".equals(cell.getCellStyle().getDataFormatString())) { val = df.format(cell.getNumericCellValue()); } else { val = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())); } break; case HSSFCell.CELL_TYPE_BOOLEAN: val = cell.getBooleanCellValue(); break; case HSSFCell.CELL_TYPE_BLANK: val = ""; break; default: val = cell.toString(); break; } objList.add(val); } dataList.add(objList); } return dataList; }
From source file:com.cn.util.ExcelImport.java
/** * ?excel/*from w ww . j a va 2s.co m*/ * @param file * @return * @throws IOException */ public static String[] readExcelHead(File file) throws IOException { HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file)); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = null; HSSFCell cell = null; row = sheet.getRow(0); String[] buff = new String[row.getLastCellNum()]; for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) { cell = row.getCell(i); buff[i] = cell.getStringCellValue(); } return buff; }
From source file:com.etest.view.tq.itemanalysis.FileUploadWindow.java
void readContentFromExcelFile(File excelFile) { try {/*from w w w.j av a2 s. com*/ POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(excelFile)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(wb.getActiveSheetIndex()); HSSFRow row; HSSFCell cell; boolean stop = false; boolean nonBlankRowFound; int s; HSSFRow lastRow = null; while (stop == false) { nonBlankRowFound = false; lastRow = sheet.getRow(sheet.getLastRowNum()); for (s = lastRow.getFirstCellNum(); s <= lastRow.getLastCellNum(); s++) { cell = lastRow.getCell(s); if (cell != null && lastRow.getCell(s).getCellType() != HSSFCell.CELL_TYPE_BLANK) { nonBlankRowFound = true; } } if (nonBlankRowFound == true) { stop = true; } else { sheet.removeRow(lastRow); } } int rows; // No of rows rows = sheet.getPhysicalNumberOfRows(); int cols = 0; // No of columns int tmp = 0; // This trick ensures that we get the data properly even if it doesn't start from first few rows for (int i = 0; i < 10 || i < rows; i++) { row = sheet.getRow(i); if (row != null) { tmp = sheet.getRow(i).getPhysicalNumberOfCells(); if (tmp > cols) cols = tmp; } } List<ItemAnalysis> itemAnalysisList = new ArrayList<>(); List<Character> answer; ItemAnalysis itemAnalysis = null; for (int c = 0; c < cols; c++) { itemAnalysis = new ItemAnalysis(); answer = new ArrayList<>(); for (int r = 0; r < rows; r++) { row = sheet.getRow(r); if (row == null || row.toString().isEmpty()) { ShowErrorNotification.error("Remove all blank/empty rows after the last Item!"); return; } else { // if(row != null){ cell = row.getCell(c); if (cell == null || cell.toString().isEmpty()) { ShowErrorNotification.error("Remove all blank/empty columns after the last student!"); return; } else { // if(cell != null){ if (c != 0) { if (r == 0) { itemAnalysis.setStudentNumber(cell.toString().trim()); } else { answer.add(cell.toString().trim().charAt(0)); } } else { if (r != 0) { totalItems++; } } } } } if (c != 0) { itemAnalysis.setAnswer(answer); itemAnalysisList.add(itemAnalysis); } } if (tq.getCellItemIdByTQCoverageId(getTqCoverageId()).size() != totalItems) { ShowErrorNotification.error("Total Items do not MATCH!"); totalItems = 0; return; } studentNoAndTotalScore = new HashMap<>(); studentNoAndAnswer = new HashMap<>(); totalItems = 1; new Thread() { @Override public void run() { totalData = itemAnalysisList.size(); for (ItemAnalysis i : itemAnalysisList) { try { Thread.sleep(50); studentNoAndTotalScore.put(i.getStudentNumber(), ItemAnalysisInterpretation .getTotalScoresOfAllStudent(tqCoverageId, i.getAnswer())); studentNoAndAnswer.put(i.getStudentNumber(), i.getAnswer()); getUI().access(new Runnable() { @Override public void run() { if (totalItems < itemAnalysisList.size()) { analyze.setValue("Analyzing data.. " + CommonUtilities.roundOffToTwoDecimal((current / totalData) * 100) + "%"); current++; } else { analyze.setValue("Data analyzed... 100%"); getLowerAndUpperGroupStudent(studentNoAndTotalScore); HorizontalLayout h = new HorizontalLayout(); h.setWidth("100%"); h.addComponent(viewTableProportion()); h.addComponent(viewStudentsTotalScore()); h.addComponent(approveItemAnalysis()); v.addComponent(h); v.addComponent(itemAnalysisGridPanel()); } } }); totalItems++; } catch (InterruptedException ex) { Logger.getLogger(FileUploadWindow.class.getName()).log(Level.SEVERE, null, ex); } } } }.start(); UI.getCurrent().setPollInterval(500); } catch (IOException ex) { Logger.getLogger(TQItemAnalysisUI.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.huateng.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 a v a2 s . co 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.huateng.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 av a2s. c om 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.mebigfatguy.yank.YankTask.java
License:Apache License
private Map<ColumnType, Integer> getColumnInfo(HSSFSheet sheet) { int firstRow = sheet.getFirstRowNum(); HSSFRow row = sheet.getRow(firstRow); Map<ColumnType, Integer> columnHeaders = new EnumMap<ColumnType, Integer>(ColumnType.class); for (int i = row.getFirstCellNum(); i <= row.getLastCellNum(); ++i) { HSSFCell cell = row.getCell(i);// w w w . jav a 2s . co m if (cell != null) { String value = cell.getStringCellValue(); if (value != null) { value = value.trim().toLowerCase(); if (value.startsWith("group")) { columnHeaders.put(ColumnType.GROUP_COLUMN, i); } else if (value.startsWith("artifact")) { columnHeaders.put(ColumnType.ARTIFACT_COLUMN, i); } else if (value.startsWith("type")) { columnHeaders.put(ColumnType.TYPE_COLUMN, i); } else if (value.startsWith("version")) { columnHeaders.put(ColumnType.VERSION_COLUMN, i); } else if (value.startsWith("classifier") || value.startsWith("alternate")) { columnHeaders.put(ColumnType.CLASSIFIER_COLUMN, i); } if (columnHeaders.size() == 4) { return columnHeaders; } } } } if (columnHeaders.size() >= 3) return columnHeaders; throw new BuildException( "Input yank xls file (" + xlsFile + ") does not contains GroupId, ArtifactId, or Version columns"); }
From source file:com.ms.commons.file.excel.ExcelParser.java
License:Open Source License
@SuppressWarnings({ "deprecation", "unused" })
public String[] splitLine() throws Exception {
if (m_iCurrentRow == m_iNbRows)
return null;
HSSFRow row = m_sheet.getRow(m_iCurrentRow);
if (row == null) {
return null;
} else {// www .j a v a 2s. c om
int cellIndex = 0;
int noOfCells = row.getPhysicalNumberOfCells();
short firstCellNum = row.getFirstCellNum();
short lastCellNum = row.getLastCellNum();
String[] values = new String[lastCellNum];
if (firstCellNum >= 0 && lastCellNum >= 0) {
for (short iCurrent = firstCellNum; iCurrent < lastCellNum; iCurrent++) {
HSSFCell cell = (HSSFCell) row.getCell(iCurrent);
if (cell == null) {
values[iCurrent] = StringUtils.EMPTY;
cellIndex++;
continue;
} else {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
double value = cell.getNumericCellValue();
if (HSSFDateUtil.isCellDateFormatted(cell)) {
if (HSSFDateUtil.isValidExcelDate(value)) {
Date date = HSSFDateUtil.getJavaDate(value);
SimpleDateFormat dateFormat = new SimpleDateFormat(JAVA_TOSTRING);
values[iCurrent] = dateFormat.format(date);
} else {
throw new Exception("Invalid Date value found at row number " + row.getRowNum()
+ " and column number " + cell.getCellNum());
}
} else {
values[iCurrent] = value + StringUtils.EMPTY;
}
break;
case HSSFCell.CELL_TYPE_STRING:
values[iCurrent] = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BLANK:
values[iCurrent] = null;
break;
default:
values[iCurrent] = null;
}
}
}
}
m_iCurrentRow++;
return values;
}
}
From source file:com.siva.javamultithreading.ExcelUtil.java
public static void copyRow(HSSFWorkbook newWorkbook, HSSFSheet srcSheet, HSSFSheet destSheet, HSSFRow srcRow, HSSFRow destRow, Map<Integer, HSSFCellStyle> styleMap) { 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); }// w w w. j a v a 2 s . c o m copyCell(newWorkbook, oldCell, newCell, styleMap); } } }
From source file:com.xpn.xwiki.plugin.lucene.textextraction.MSExcelTextExtractor.java
License:Open Source License
/** * It will parse row and return the text *//*from ww w. j av a 2 s. c o m*/ private void parseRow(HSSFRow row, StringBuffer cleanBuffer) { short firstCell = row.getFirstCellNum(); short lastCell = row.getLastCellNum(); for (short cellIdx = firstCell; cellIdx <= lastCell; cellIdx++) { HSSFCell cell = row.getCell(cellIdx); if (cell != null) { parseCell(cell, cleanBuffer); } } }