List of usage examples for org.apache.poi.hssf.usermodel HSSFRow getPhysicalNumberOfCells
@Override public int getPhysicalNumberOfCells()
From source file:com.kahlon.guard.controller.DocumentManager.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0);// w ww.j av a 2s . c om HSSFRow header = sheet.getRow(0); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); HSSFFont font = wb.createFont(); font.setColor(HSSFColor.WHITE.index); cellStyle.setFont(font); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(cellStyle); } }
From source file:com.learn.core.utils.HSSFReadWrite.java
License:Apache License
/** * Method main//from w w w . j ava 2s . c om * * 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) { try (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; } System.out.println("\nROW " + row.getRowNum() + " has " + row.getPhysicalNumberOfCells() + " cell(s)."); for (int c = 0; c < row.getLastCellNum(); c++) { HSSFCell cell = row.getCell(c); String value; if (cell != 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; case BLANK: value = "<BLANK>"; break; case BOOLEAN: value = "BOOLEAN value-" + cell.getBooleanCellValue(); break; case ERROR: value = "ERROR value=" + cell.getErrorCellValue(); break; default: value = "UNKNOWN value of type " + cell.getCellTypeEnum(); } System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value); } } } } } } 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"); try (HSSFWorkbook wb = HSSFReadWrite.readFile(fileName)) { try (FileOutputStream stream = new FileOutputStream(args[1])) { wb.write(stream); } } } } else if (args.length == 3 && args[2].equalsIgnoreCase("modify1")) { // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to string "MODIFIED CELL!!" try (HSSFWorkbook wb = HSSFReadWrite.readFile(fileName)) { 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!!!!!"); try (FileOutputStream stream = new FileOutputStream(args[1])) { wb.write(stream); } } } } catch (Exception e) { e.printStackTrace(); } }
From source file:com.lushapp.common.excel.ExcelUtil.java
License:Apache License
/** * ? Excel// ww w. jav a 2s .c o m * * @param excel_name * @return * @throws Exception */ public static List<String[]> readExcel(String excel_name) throws Exception { // List<String[]> list = new ArrayList<String[]>(); HSSFWorkbook hssfworkbook = new HSSFWorkbook(new FileInputStream(excel_name)); // ??i? getNumberOfSheets HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0); // ??,j getPhysicalNumberOfRows for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) { HSSFRow hssfrow = hssfsheet.getRow(j); if (hssfrow != null) { int col = hssfrow.getPhysicalNumberOfCells(); // ?? String[] arrayString = new String[col]; for (int i = 0; i < col; i++) { HSSFCell cell = hssfrow.getCell(i); if (cell == null) { arrayString[i] = ""; } else if (cell.getCellType() == 0) { // arrayString[i] = new Double(cell.getNumericCellValue()).toString(); if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) { if (HSSFDateUtil.isCellDateFormatted(cell)) { Date d = cell.getDateCellValue(); // DateFormat formater = new SimpleDateFormat("yyyy-MM-dd"); DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); arrayString[i] = formater.format(d); } else { arrayString[i] = new BigDecimal(cell.getNumericCellValue()).longValue() + ""; } } } else {// EXCEL? arrayString[i] = cell.getStringCellValue().trim(); } } list.add(arrayString); } } return list; }
From source file:com.lushapp.common.excel.ExcelUtil.java
License:Apache License
/** * ? Excel/*from w w w. j a v a 2 s. co m*/ * * @param excel_name * @return * @throws Exception */ public static List<List<Object>> readExcelByList(String excel_name) throws Exception { // List<List<Object>> list = new ArrayList<List<Object>>(); HSSFWorkbook hssfworkbook = new HSSFWorkbook(new FileInputStream(excel_name)); // ??i? getNumberOfSheets HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0); // ??,j getPhysicalNumberOfRows for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) { HSSFRow hssfrow = hssfsheet.getRow(j); if (hssfrow != null) { int col = hssfrow.getPhysicalNumberOfCells(); // ?? List<Object> arrayString = new ArrayList<Object>(); for (int i = 0; i < col; i++) { HSSFCell cell = hssfrow.getCell(i); if (cell == null) { arrayString.add(""); } else if (cell.getCellType() == 0) { arrayString.add(new Double(cell.getNumericCellValue()).toString()); } else {// EXCEL? arrayString.add(cell.getStringCellValue().trim()); } } list.add(arrayString); } } return list; }
From source file:com.lushapp.common.excel.ExcelUtil.java
License:Apache License
/** * ? Excel/* w ww . j a va 2 s .co m*/ * * @param inputstream * @return * @throws Exception */ public static List<List<Object>> readExcelByInputStream(InputStream inputstream) throws Exception { // List<List<Object>> list = new ArrayList<List<Object>>(); HSSFWorkbook hssfworkbook = new HSSFWorkbook(inputstream); // ??i? getNumberOfSheets HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0); // ??,j getPhysicalNumberOfRows // //System.out.println("excel "+hssfsheet.getPhysicalNumberOfRows()); for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) { HSSFRow hssfrow = hssfsheet.getRow(j); if (hssfrow != null) { int col = hssfrow.getPhysicalNumberOfCells(); // ?? List<Object> arrayString = new ArrayList<Object>(); for (int i = 0; i < col; i++) { HSSFCell cell = hssfrow.getCell(i); if (cell == null) { arrayString.add(""); } else if (cell.getCellType() == 0) { arrayString.add(new Double(cell.getNumericCellValue()).toString()); } else {// EXCEL? arrayString.add(cell.getStringCellValue().trim()); } } list.add(arrayString); } } return list; }
From source file:com.mohammad.donkiello.CustomerManager.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0);// w w w .j av a2s . co m HSSFRow header = sheet.getRow(0); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.ORANGE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(cellStyle); } }
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 {// w w w . ja va2 s .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.ms.commons.test.datareader.impl.ExcelReadUtil.java
License:Open Source License
protected static String[] readSheetHead(HSSFRow headRow) { List<String> headList = new ArrayList<String>(); int n = headRow.getPhysicalNumberOfCells(); for (Integer i = 0; i < n; i++) { HSSFCell cell = headRow.getCell(i.shortValue()); String head = readCellValue(null, cell).getStringValue(); if (head == null || head.trim().length() == 0) { break; }//from www. j a v a 2s . c o m headList.add(readCellValue(null, cell).getStringValue()); } return headList.toArray(new String[] {}); }
From source file:com.pe.nisira.movil.view.action.RegistroPaleta.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0);/*from ww w.j ava2 s .c o m*/ HSSFRow header = sheet.getRow(0); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.GREEN.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { header.getCell(i).setCellStyle(cellStyle); } }
From source file:com.qms.operations.rates.ejb.sls.BuyRatesSessionBean.java
License:Open Source License
public String processExcel(String fileName, ESupplyGlobalParameters loginBean) throws EJBException { long startTime = System.currentTimeMillis(); String s1 = ""; PreparedStatement pstmtMainHeader = null; PreparedStatement pstmtDetails = null; PreparedStatement pstmtDetailsData = null; PreparedStatement MainHeader = null; PreparedStatement Details = null; PreparedStatement DetailsData = null; Connection connection = null; OperationsImpl operationsImpl = null; String dateFormat = null;//from www.j a v a2 s . c o m String deleteDtldata = "DELETE FROM QMS_STG_BUYRATES_DETAILS_DATA"; String deleteDtl = "DELETE FROM QMS_STG_BUYRATES_DETAILS"; String deleteHeader = "DELETE FROM QMS_STG_BUYRATES_MAIN_HEADER"; String selectMainHeader = "SELECT WEIGHT_BREAK FROM QMS_STG_BUYRATES_MAIN_HEADER"; ResultSet rs = null; PreparedStatement pstmtHeader = null; //@@Added by Kameswari on 15/04/09 String wtbreak = null; String returnstr = null; CallableStatement cstmt = null; double time = 0.0; String data = null; try { String s = ""; String mainHeaderData[] = new String[8]; int rows = 0; int cols = 0; int notesIndex = 0; int batchCount = 0; HSSFRow row = null; HSSFCell cell = null; InputStream myxls = new FileInputStream(fileName); HSSFWorkbook wb = new HSSFWorkbook(myxls); HSSFSheet sheet = wb.getSheetAt(0); rows = sheet.getPhysicalNumberOfRows(); dateFormat = loginBean.getUserPreferences().getDateFormat(); String queryMainHeader = "INSERT INTO QMS_STG_BUYRATES_MAIN_HEADER (SHIPMENT_MODE,CURRENCY,WEIGHT_BREAK,WEIGHT_CLASS,RATE_TYPE,UOM,CONSOLETYPE,DENSITY_RATIO,TERMINALID) VALUES (?,?,?,?,?,?,?,?,?)"; // String queryDetails = "INSERT INTO QMS_STG_BUYRATES_DETAILS (ROW_ID,ORIGIN,DESTINATION,CARRIER_ID,SERVICELEVEL,FREQUENCY,TRANSIT,EFFECTIVE_FROM,VALID_UPTO,NOTES) VALUES (?,?,?,?,?,?,?,?,?,?)"; String queryDetails = "INSERT INTO QMS_STG_BUYRATES_DETAILS (ROW_ID,ORIGIN,DESTINATION,CARRIER_ID,SERVICELEVEL,FREQUENCY,TRANSIT,EFFECTIVE_FROM,VALID_UPTO,NOTES) VALUES (?,?,?,?,?,?,?,TO_DATE(?),TO_DATE(?),?)"; String queryDetailsData = "INSERT INTO QMS_STG_BUYRATES_DETAILS_DATA (ROW_ID,LINE_NO,CONTAINER_NO,CONTAINERS_VALUE,LOWER_BOUND,UPPER_BOUND,RATE_DESCRIPTION) VALUES (?,?,?,?,?,?,?)"; String msgquery = "SELECT ERROR_MSG FROM QMS_STG_BUYRATES_DETAILS"; // connection=ConnectionUtil.getConnection(); operationsImpl = new OperationsImpl(); connection = operationsImpl.getConnection(); DetailsData = connection.prepareStatement(deleteDtldata); Details = connection.prepareStatement(deleteDtl); MainHeader = connection.prepareStatement(deleteHeader); DetailsData.execute(); Details.execute(); MainHeader.execute(); // long startTime1=System.currentTimeMillis(); //For MainHeader row = sheet.getRow(1); for (int k = 0; k < 8; k++) { cell = row.getCell((short) k); mainHeaderData[k] = getCellData(cell, 0); } pstmtMainHeader = connection.prepareStatement(queryMainHeader); insertMainHeaderData(mainHeaderData, pstmtMainHeader, loginBean); //End pstmtHeader = connection.prepareStatement(selectMainHeader); rs = pstmtHeader.executeQuery(); if (rs.next()) { wtbreak = rs.getString("WEIGHT_BREAK"); } //To get columns count , column index for NOTES and containers ids row = sheet.getRow(3); cols = row.getPhysicalNumberOfCells(); int l = cols - 1; for (; l >= 0; l--) { cell = row.getCell((short) l); if ("NOTES".equalsIgnoreCase(getCellData(cell, l))) break; } notesIndex = l + 1; String detailsData[] = new String[notesIndex]; String containersData[] = new String[notesIndex - 9]; for (l = 8; l < notesIndex - 1; l++) { cell = row.getCell((short) l); containersData[l - 8] = getCellData(cell, l); } //End //For Details pstmtDetails = connection.prepareStatement(queryDetails); ; pstmtDetailsData = connection.prepareStatement(queryDetailsData); for (int i = 4; i <= rows; i++) { row = sheet.getRow(i); //cols=row.getPhysicalNumberOfCells(); //s=row.getRowNum() + " : "; for (int j = 0; j < notesIndex; j++) { if (row != null) { cell = row.getCell((short) j); detailsData[j] = getCellData(cell, j); } // s = s+ " -- " + detailsData[j]; if (j == 6 && (cell.CELL_TYPE_NUMERIC == cell.getCellType()) && (detailsData[j].indexOf(".") != -1) && (Double.parseDouble(detailsData[j].substring(0, detailsData[j].indexOf("."))) < 1)) { time = 1 / Double.parseDouble(detailsData[j]); Double t = new Double(time); if (t.toString().indexOf(".") != -1) { data = t.toString().substring(0, t.toString().indexOf(".")); } if (data != null) { detailsData[j] = data + ":" + "00"; } } if (j == 6 && (cell.CELL_TYPE_NUMERIC == cell.getCellType()) && (detailsData[j].indexOf(".") != -1) && (!(Double .parseDouble(detailsData[j].substring(0, detailsData[j].indexOf("."))) < 1))) { if (detailsData[j].indexOf(".") != -1) { detailsData[j] = detailsData[j].substring(0, detailsData[j].indexOf(".")); } } if (j == 5 && detailsData[j].indexOf(".") != -1) { detailsData[j] = detailsData[j].substring(0, detailsData[j].indexOf(".")); } } //long EndTime1=System.currentTimeMillis(); //System.out.println("\n------->>>> M.SEC : "+(EndTime1-startTime1) +" SEC : " + (EndTime1-startTime1)/(1000) + " MIN : "+(EndTime1-startTime1)/(1000*60)); batchCount++; insertDetails(detailsData, containersData, pstmtDetails, pstmtDetailsData, notesIndex, i, dateFormat, wtbreak); if (batchCount == 150) { pstmtDetails.executeBatch(); pstmtDetailsData.executeBatch(); pstmtDetails.clearBatch(); pstmtDetailsData.clearBatch(); batchCount = 0; } // s1=s1+s + "\n"; } if (batchCount > 0) { pstmtDetails.executeBatch(); pstmtDetailsData.executeBatch(); pstmtDetails.clearBatch(); pstmtDetailsData.clearBatch(); batchCount = 0; } cstmt = connection.prepareCall("{ ?=call QMS_BUY_RATES_UPLOAD_PKG.BUY_RATES_PROC(?)}"); cstmt.registerOutParameter(1, Types.VARCHAR); cstmt.setString(2, loginBean.getTerminalId()); cstmt.execute(); returnstr = (String) cstmt.getString(1); //End return returnstr; } catch (Exception e) { logger.error("Exception"); e.printStackTrace(); } finally { try { if (pstmtMainHeader != null) pstmtMainHeader.close(); if (pstmtDetails != null) pstmtDetails.close(); if (pstmtDetailsData != null) pstmtDetailsData.close(); if (cstmt != null) cstmt.close(); if (connection != null) connection.close(); if (rs != null)//Added by govind on 16-02-2010 for Connection Leakages rs.close(); if (pstmtHeader != null)//Added by govind on 16-02-2010 for Connection Leakages pstmtHeader.close(); if (MainHeader != null)//Added by govind on 16-02-2010 for Connection Leakages MainHeader.close(); if (DetailsData != null) //Added by govind on 16-02-2010 for Connection Leakages DetailsData.close(); if (Details != null)//Added by govind on 16-02-2010 for Connection Leakages Details.close(); } catch (Exception e) { logger.error("Error Occured while closing Resources" + e); } } long EndTime = System.currentTimeMillis(); s1 = " M.SEC : " + (EndTime - startTime) + " SEC : " + (EndTime - startTime) / (1000) + " MIN : " + (EndTime - startTime) / (1000 * 60); return s1; }