List of usage examples for org.apache.poi.hssf.util CellReference CellReference
public CellReference(int pRow, int pCol)
From source file:ch.bfh.lca._15h.library.export.ExportToExcel.java
/*** * Prototype function. Not yet functional. * Allow to generate an age pyramid graphic in Excel by using an existing Excel Template. * @param language Language of the label in the Excel file * @param rows Arrays of rows to include in the listing * @param excelFilePath Path of the outputed file * @throws FileNotFoundException/* w w w . j a v a2 s . c o m*/ * @throws IOException * @throws InvalidFormatException */ public static void exportToAgePyramid(Translation.TRANSLATION_LANGUAGE language, GenericResultRow[] rows, String excelFilePath) throws FileNotFoundException, IOException, InvalidFormatException { //open template URL url = Translation.class.getClassLoader() .getResource("ch/bfh/lca/_15h/library/export/template/alter-pyramide-v2.xlsx"); //Workbook wb = WorkbookFactory.create(new File(url.getPath())); XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(new File(url.getPath()))); Sheet sheet = wb.getSheetAt(0); //http://www.programming-free.com/2012/12/create-charts-in-excel-using-java.html //https://poi.apache.org/spreadsheet/quick-guide.html#NewWorkbook Row row; Cell cell; for (int i = 0; i < 20; i++) { row = sheet.getRow(i + 1); if (row == null) { row = sheet.createRow(i + 1); } for (int j = 0; j < 3; j++) { cell = row.getCell(j); if (cell == null) { cell = row.createCell(j); } switch (j) { case 0: cell.setCellValue(i); break; case 1: cell.setCellValue(i * j * -1); break; case 2: cell.setCellValue(i * j); break; } } } //redefine data range //http://thinktibits.blogspot.ch/2014/09/Excel-Insert-Format-Table-Apache-POI-Example.html XSSFSheet sheet1 = wb.getSheetAt(0); XSSFTable table = sheet1.getTables().get(0); CTTable cttable = table.getCTTable(); AreaReference my_data_range = new AreaReference(new CellReference(0, 0), new CellReference(20, 2)); /* Set Range to the Table */ cttable.setRef(my_data_range.formatAsString()); // cttable.setDisplayName("DATEN"); /* this is the display name of the table */ //cttable.setName("test"); /* This maps to "displayName" attribute in <table>, OOXML */ //cttable.setId(1L); //id attribute against table as long value /* //redefine data range Name rangeCell = wb.getName("DATEN"); //Set new range for named range //String reference = sheetName + "!$C$" + (deface + 1) + ":$C$" + (rowNum + deface); String reference = sheet.getSheetName() + "!$A$2:$C$20"; //Assigns range value to named range rangeCell.setRefersToFormula(reference); */ //write to the file FileOutputStream fileOut = new FileOutputStream(excelFilePath); wb.write(fileOut); fileOut.close(); wb.close(); }
From source file:com.krawler.esp.servlets.XLSDataExtractor.java
License:Open Source License
public JSONObject parseXLS(String filename, int sheetNo) throws FileNotFoundException, IOException, JSONException { JSONObject jobj = new JSONObject(); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb); HSSFSheet sheet = wb.getSheetAt(sheetNo); int startRow = 0; int maxRow = sheet.getLastRowNum(); int maxCol = 0; int noOfRowsDisplayforSample = 20; if (noOfRowsDisplayforSample > sheet.getLastRowNum()) { noOfRowsDisplayforSample = sheet.getLastRowNum(); }//from w w w . j a v a2 s. co m JSONArray jArr = new JSONArray(); for (int i = 0; i <= noOfRowsDisplayforSample; i++) { Row row = sheet.getRow(i); JSONObject obj = new JSONObject(); JSONObject jtemp1 = new JSONObject(); if (row == null) { jArr.put(obj); continue; } if (maxCol < row.getLastCellNum()) maxCol = row.getLastCellNum(); for (int j = 0; j < row.getLastCellNum(); j++) { Cell cell = row.getCell(j); if (cell == null) continue; String colHeader = new CellReference(i, j).getCellRefParts()[2]; String val = null; switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: val = Double.toString(cell.getNumericCellValue()); if (HSSFDateUtil.isCellDateFormatted(cell)) { java.util.Date df = HSSFDateUtil.getJavaDate(Double.parseDouble(val)); String df_full = "yyyy-MM-dd"; DateFormat sdf = new SimpleDateFormat(df_full); val = sdf.format(df); } break; case Cell.CELL_TYPE_STRING: val = cell.getRichStringCellValue().getString(); break; } if (i == 0) { // List of Headers (Consider first row as Headers) jtemp1 = new JSONObject(); jtemp1.put("header", val); jtemp1.put("index", j); jobj.append("Header", jtemp1); } obj.put(colHeader, val); } jArr.put(obj); } jobj.put("startrow", startRow); jobj.put("maxrow", maxRow); jobj.put("maxcol", maxCol); jobj.put("index", sheetNo); jobj.put("data", jArr); jobj.put("filename", filename); jobj.put("msg", "Image has been successfully uploaded"); jobj.put("lsuccess", true); jobj.put("valid", true); return jobj; }
From source file:com.krawler.esp.servlets.XLSDataExtractor.java
License:Open Source License
public JSONObject parseXLS1(String filename, int sheetNo) throws FileNotFoundException, IOException, JSONException { JSONObject jobj = new JSONObject(); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb); HSSFSheet sheet = wb.getSheetAt(sheetNo); ArrayList<String> arr = new ArrayList<String>(); int startRow = 0; int maxRow = sheet.getLastRowNum(); int maxCol = 0; JSONArray jArr = new JSONArray(); try {/* w w w . j av a 2s . co m*/ for (int i = 0; i <= sheet.getLastRowNum(); i++) { Row row = sheet.getRow(i); JSONObject obj = new JSONObject(); JSONObject jtemp1 = new JSONObject(); if (row == null) { jArr.put(obj); continue; } if (maxCol < row.getLastCellNum()) maxCol = row.getLastCellNum(); for (int j = 0; j < row.getLastCellNum(); j++) { Cell cell = row.getCell(j); String val = null; if (cell == null) { arr.add(val); continue; } ; String colHeader = new CellReference(i, j).getCellRefParts()[2]; switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: val = Double.toString(cell.getNumericCellValue()); if (HSSFDateUtil.isCellDateFormatted(cell)) { java.util.Date df = HSSFDateUtil.getJavaDate(Double.parseDouble(val)); String df_full = "yyyy-MM-dd"; DateFormat sdf = new SimpleDateFormat(df_full); val = sdf.format(df); } break; case Cell.CELL_TYPE_STRING: val = cell.getRichStringCellValue().getString(); break; } if (i == 0) { // List of Headers (Consider first row as Headers) jtemp1 = new JSONObject(); jtemp1.put("header", val); jtemp1.put("index", j); jobj.append("Header", jtemp1); obj.put(colHeader, val); arr.add(val); } else { if (arr.get(j) != null) obj.put(arr.get(j), val); } } jArr.put(obj); } } catch (Exception ex) { Logger.getLogger(XLSDataExtractor.class.getName()).log(Level.SEVERE, null, ex); } jobj.put("startrow", startRow); jobj.put("maxrow", maxRow); jobj.put("maxcol", maxCol); jobj.put("index", sheetNo); jobj.put("data", jArr); jobj.put("filename", filename); jobj.put("msg", "Image has been successfully uploaded"); jobj.put("lsuccess", true); jobj.put("valid", true); return jobj; }
From source file:com.krawler.spring.importFunctionality.ImportHandler.java
License:Open Source License
public JSONObject parseXLS(String filename, int sheetNo) throws FileNotFoundException, IOException, JSONException { JSONObject jobj = new JSONObject(); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb); HSSFSheet sheet = wb.getSheetAt(sheetNo); int startRow = 0; int maxRow = sheet.getLastRowNum(); int maxCol = 0; int noOfRowsDisplayforSample = 20; if (noOfRowsDisplayforSample > sheet.getLastRowNum()) { noOfRowsDisplayforSample = sheet.getLastRowNum(); }/*w ww . j a va2s .c o m*/ JSONArray jArr = new JSONArray(); try { for (int i = 0; i <= noOfRowsDisplayforSample; i++) { HSSFRow row = sheet.getRow(i); JSONObject obj = new JSONObject(); JSONObject jtemp1 = new JSONObject(); if (row == null) { continue; } if (i == 0) { maxCol = row.getLastCellNum(); } for (int cellcount = 0; cellcount < maxCol; cellcount++) { HSSFCell cell = row.getCell(cellcount); CellReference cref = new CellReference(i, cellcount); String colHeader = cref.getCellRefParts()[2]; String val = null; if (cell != null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { val = Double.toString(cell.getNumericCellValue()); java.util.Date date1 = HSSFDateUtil.getJavaDate(Double.parseDouble(val)); DateFormat sdf = new SimpleDateFormat(df); val = sdf.format(date1); } else { val = dfmt.format(cell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_STRING: val = cleanHTML(cell.getRichStringCellValue().getString()); break; } } if (i == 0) { // List of Headers (Consider first row as Headers) if (val != null) { jtemp1 = new JSONObject(); jtemp1.put("header", val == null ? "" : val); jtemp1.put("index", cellcount); jobj.append("Header", jtemp1); } } obj.put(colHeader, val); } // if(obj.length()>0){ //Don't show blank row in preview grid[SK] jArr.put(obj); // } } } catch (Exception ex) { Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex); } jobj.put("startrow", startRow); jobj.put("maxrow", maxRow); jobj.put("maxcol", maxCol); jobj.put("index", sheetNo); jobj.put("data", jArr); jobj.put("filename", filename); jobj.put("msg", "XLS has been successfully uploaded"); jobj.put("lsuccess", true); jobj.put("valid", true); return jobj; }
From source file:com.krawler.spring.importFunctionality.ImportHandler.java
License:Open Source License
public JSONObject parseXLS1(String filename, int sheetNo, int startindex) throws FileNotFoundException, IOException, JSONException { JSONObject jobj = new JSONObject(); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb); HSSFSheet sheet = wb.getSheetAt(sheetNo); ArrayList<String> arr = new ArrayList<String>(); int startRow = 0; int maxRow = sheet.getLastRowNum(); int maxCol = 0; JSONArray jArr = new JSONArray(); try {//from w w w.j ava 2s . c om for (int i = startindex; i <= sheet.getLastRowNum(); i++) { HSSFRow row = sheet.getRow(i); JSONObject obj = new JSONObject(); JSONObject jtemp1 = new JSONObject(); if (row == null) { continue; } if (i == startindex) { maxCol = row.getLastCellNum(); } for (int j = 0; j < maxCol; j++) { HSSFCell cell = row.getCell(j); String val = null; if (cell == null) { arr.add(val); continue; } ; String colHeader = new CellReference(i, j).getCellRefParts()[2]; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { val = Double.toString(cell.getNumericCellValue()); java.util.Date date1 = HSSFDateUtil.getJavaDate(Double.parseDouble(val)); DateFormat sdf = new SimpleDateFormat(df); val = sdf.format(date1); } else { val = dfmt.format(cell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_STRING: val = cleanHTML(cell.getRichStringCellValue().getString()); break; } if (i == startindex) { // List of Headers (consider startindex row as a headers) if (val != null) { jtemp1 = new JSONObject(); jtemp1.put("header", val); jtemp1.put("index", j); jobj.append("Header", jtemp1); obj.put(colHeader, val); } arr.add(val); } else { if (arr.get(j) != null) obj.put(arr.get(j), val); } } if (obj.length() > 0) { jArr.put(obj); } } } catch (Exception ex) { Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex); } jobj.put("startrow", startRow); jobj.put("maxrow", maxRow); jobj.put("maxcol", maxCol); jobj.put("index", sheetNo); jobj.put("data", jArr); jobj.put("filename", filename); jobj.put("msg", "XLS has been successfully uploaded"); jobj.put("lsuccess", true); jobj.put("valid", true); return jobj; }
From source file:com.krawler.spring.importFunctionality.ImportHandler.java
License:Open Source License
public int dumpXLSFileData(String filename, int sheetNo, int startindex) throws ServiceException { int dumpedRows = 0; try {//from w ww .j a v a 2 s.c o m String destinationDirectory = storageHandlerImpl.GetDocStorePath() + "xlsfiles"; POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(destinationDirectory + "/" + filename)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(sheetNo); int maxRow = sheet.getLastRowNum(); int maxCol = 0; String tableName = importDao.getTableName(filename); for (int i = startindex; i <= maxRow; i++) { HSSFRow row = sheet.getRow(i); if (row == null) { continue; } if (i == startindex) { maxCol = row.getLastCellNum(); //Column Count } ArrayList<String> dataArray = new ArrayList<String>(); JSONObject dataObj = new JSONObject(); for (int j = 0; j < maxCol; j++) { HSSFCell cell = row.getCell(j); String val = null; if (cell == null) { dataArray.add(val); continue; } String colHeader = new CellReference(i, j).getCellRefParts()[2]; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { val = Double.toString(cell.getNumericCellValue()); java.util.Date date1 = HSSFDateUtil.getJavaDate(Double.parseDouble(val)); DateFormat sdf = new SimpleDateFormat(df);//(df_full); //BUG:16085 val = sdf.format(date1); } else { val = dfmt.format(cell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_STRING: val = cleanHTML(cell.getRichStringCellValue().getString()); break; } dataObj.put(colHeader, val); dataArray.add(val); //Collect row data } //Insert Query if (dataObj.length() > 0) { // Empty row check (if lenght==0 then all columns are empty) dumpedRows += importDao.dumpFileRow(tableName, dataArray.toArray()); } } } catch (IOException ex) { throw ServiceException.FAILURE("dumpXLSFileData: " + ex.getMessage(), ex); } catch (Exception ex) { throw ServiceException.FAILURE("dumpXLSFileData: " + ex.getMessage(), ex); } return dumpedRows; }
From source file:com.krawler.spring.importFunctionality.ImportUtil.java
License:Open Source License
/** * Generate the preview of the xls grid/*from www . j a v a 2 s .co m*/ * @param filename * @param sheetNo * @return * @throws FileNotFoundException * @throws IOException * @throws JSONException */ public static JSONObject parseXLS(String filename, int sheetNo) throws FileNotFoundException, IOException, JSONException { JSONObject jobj = new JSONObject(); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb); HSSFSheet sheet = wb.getSheetAt(sheetNo); //DateFormat sdf = new SimpleDateFormat(df); int startRow = 0; int maxRow = sheet.getLastRowNum(); int maxCol = 0; int maxSheetColCnt = 0; int noOfRowsDisplayforSample = 20; if (noOfRowsDisplayforSample > sheet.getLastRowNum()) { noOfRowsDisplayforSample = sheet.getLastRowNum(); } int firstValidRec = 0; JSONArray jArr = new JSONArray(); try { for (int i = 0; i <= noOfRowsDisplayforSample; i++) { HSSFRow row = sheet.getRow(i); JSONObject obj = new JSONObject(); JSONObject jtemp1 = new JSONObject(); if (row == null) { continue; } if (i != 0 && firstValidRec == 0 && !jobj.has("Header")) // get first valid row which having some columns with data as a header firstValidRec = i; // if(i==0) { maxCol = row.getLastCellNum(); if (maxSheetColCnt < maxCol) // get max column count maxSheetColCnt = maxCol; // } for (int cellcount = 0; cellcount < maxCol; cellcount++) { HSSFCell cell = row.getCell(cellcount); CellReference cref = new CellReference(i, cellcount); String colHeader = cref.getCellRefParts()[2]; String val = null; if (cell != null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { val = cell.toString();//Long.toString(cell.getDateCellValue().getTime()); } else { val = dfmt.format(cell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_STRING: val = ImportUtil.cleanHTML(cell.getRichStringCellValue().getString()); break; } } if (i == firstValidRec) { // List of Headers (Consider first row as Headers) if (val != null) { jtemp1 = new JSONObject(); jtemp1.put("header", val == null ? "" : val); jtemp1.put("index", cellcount); jobj.append("Header", jtemp1); } } obj.put(colHeader, val); } // if(obj.length()>0){ //Don't show blank row in preview grid[SK] jArr.put(obj); // } } } catch (Exception ex) { Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex); } jobj.put("startrow", startRow); jobj.put("maxrow", maxRow); jobj.put("maxcol", maxSheetColCnt); jobj.put("index", sheetNo); jobj.put("data", jArr); jobj.put("filename", filename); jobj.put("msg", "XLS has been successfully uploaded"); jobj.put("lsuccess", true); jobj.put("valid", true); return jobj; }
From source file:com.krawler.spring.importFunctionality.ImportUtil.java
License:Open Source License
/** * @param filename/* www. j a v a 2 s.c om*/ * @param sheetNo * @param startindex * @param importDao * @return * @throws ServiceException */ public static void dumpXLSFileData(String filename, int sheetNo, int startindex, ImportDAO importDao, HibernateTransactionManager txnManager) throws ServiceException { boolean commitedEx = false; DefaultTransactionDefinition def = new DefaultTransactionDefinition(); def.setName("import_Tx"); def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED); TransactionStatus status = txnManager.getTransaction(def); Session session = txnManager.getSessionFactory().getCurrentSession(); try { String destinationDirectory = storageHandlerImpl.GetDocStorePath() + "xlsfiles"; POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(destinationDirectory + "/" + filename)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(sheetNo); //DateFormat sdf = new SimpleDateFormat(df_full); int maxRow = sheet.getLastRowNum(); int maxCol = 0; String tableName = importDao.getTableName(filename); int flushCounter = 0; for (int i = startindex; i <= maxRow; i++) { HSSFRow row = sheet.getRow(i); if (row == null) { continue; } if (i == startindex) { maxCol = row.getLastCellNum(); //Column Count } ArrayList<String> dataArray = new ArrayList<String>(); JSONObject dataObj = new JSONObject(); for (int j = 0; j < maxCol; j++) { HSSFCell cell = row.getCell(j); String val = null; if (cell == null) { dataArray.add(val); continue; } String colHeader = new CellReference(i, j).getCellRefParts()[2]; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { val = Long.toString(cell.getDateCellValue().getTime()); } else { val = dfmt.format(cell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_STRING: val = ImportUtil.cleanHTML(cell.getRichStringCellValue().getString()); break; } dataObj.put(colHeader, val); dataArray.add(val); //Collect row data } //Insert Query if (dataObj.length() > 0) { // Empty row check (if lenght==0 then all columns are empty) importDao.dumpFileRow(tableName, dataArray.toArray()); if (flushCounter % 30 == 0) { session.flush(); session.clear(); } flushCounter++; } } try { txnManager.commit(status); } catch (Exception ex) { commitedEx = true; throw ex; } } catch (IOException ex) { throw ServiceException.FAILURE("dumpXLSFileData: " + ex.getMessage(), ex); } catch (Exception ex) { if (!commitedEx) { //if exception occurs during commit then dont call rollback txnManager.rollback(status); } throw ServiceException.FAILURE("dumpXLSFileData: " + ex.getMessage(), ex); } }
From source file:com.krawler.spring.importFunctionality.ImportUtil.java
License:Open Source License
/** * Generate the preview of the xls grid// w w w .j a v a2 s . com * @param filename * @param sheetNo * @return * @throws FileNotFoundException * @throws IOException * @throws JSONException */ public static JSONObject parseXLSX(String filename, int sheetNo) throws FileNotFoundException, IOException, JSONException { JSONObject jobj = new JSONObject(); FileInputStream fs = new FileInputStream(filename); XSSFWorkbook wb = new XSSFWorkbook(fs); XSSFSheet sheet = wb.getSheetAt(sheetNo); //DateFormat sdf = new SimpleDateFormat(df); int startRow = 0; int maxRow = sheet.getLastRowNum(); int maxCol = 0; int noOfRowsDisplayforSample = 20; if (noOfRowsDisplayforSample > sheet.getLastRowNum()) { noOfRowsDisplayforSample = sheet.getLastRowNum(); } JSONArray jArr = new JSONArray(); try { for (int i = 0; i <= noOfRowsDisplayforSample; i++) { XSSFRow row = sheet.getRow(i); JSONObject obj = new JSONObject(); JSONObject jtemp1 = new JSONObject(); if (row == null) { continue; } if (i == 0) { maxCol = row.getLastCellNum(); } for (int cellcount = 0; cellcount < maxCol; cellcount++) { XSSFCell cell = row.getCell(cellcount); CellReference cref = new CellReference(i, cellcount); String colHeader = cref.getCellRefParts()[2]; String val = null; if (cell != null) { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { val = Long.toString(cell.getDateCellValue().getTime()); } else { val = dfmt.format(cell.getNumericCellValue()); } break; case XSSFCell.CELL_TYPE_STRING: val = ImportUtil.cleanHTML(cell.getRichStringCellValue().getString()); break; } } if (i == 0) { // List of Headers (Consider first row as Headers) if (val != null) { jtemp1 = new JSONObject(); jtemp1.put("header", val == null ? "" : val); jtemp1.put("index", cellcount); jobj.append("Header", jtemp1); } } obj.put(colHeader, val); } // if(obj.length()>0){ //Don't show blank row in preview grid[SK] jArr.put(obj); // } } } catch (Exception ex) { Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex); } jobj.put("startrow", startRow); jobj.put("maxrow", maxRow); jobj.put("maxcol", maxCol); jobj.put("index", sheetNo); jobj.put("data", jArr); jobj.put("filename", filename); jobj.put("msg", "XLSX has been successfully uploaded"); jobj.put("lsuccess", true); jobj.put("valid", true); return jobj; }
From source file:com.krawler.spring.importFunctionality.ImportUtil.java
License:Open Source License
/** * @param filename//from w w w. java 2s. c om * @param sheetNo * @param startindex * @param importDao * @return * @throws ServiceException */ public static void dumpXLSXFileData(String filename, int sheetNo, int startindex, ImportDAO importDao, HibernateTransactionManager txnManager) throws ServiceException { boolean commitedEx = false; DefaultTransactionDefinition def = new DefaultTransactionDefinition(); def.setName("import_Tx"); def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED); TransactionStatus status = txnManager.getTransaction(def); Session session = txnManager.getSessionFactory().getCurrentSession(); try { String destinationDirectory = storageHandlerImpl.GetDocStorePath() + "xlsfiles"; FileInputStream fs = new FileInputStream(destinationDirectory + "/" + filename); XSSFWorkbook wb = new XSSFWorkbook(fs); XSSFSheet sheet = wb.getSheetAt(sheetNo); //DateFormat sdf = new SimpleDateFormat(df_full); int maxRow = sheet.getLastRowNum(); int maxCol = 0; String tableName = importDao.getTableName(filename); int flushCounter = 0; for (int i = startindex; i <= maxRow; i++) { XSSFRow row = sheet.getRow(i); if (row == null) { continue; } if (i == startindex) { maxCol = row.getLastCellNum(); //Column Count } ArrayList<String> dataArray = new ArrayList<String>(); JSONObject dataObj = new JSONObject(); for (int j = 0; j < maxCol; j++) { XSSFCell cell = row.getCell(j); String val = null; if (cell == null) { dataArray.add(val); continue; } String colHeader = new CellReference(i, j).getCellRefParts()[2]; switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { val = Long.toString(cell.getDateCellValue().getTime()); } else { val = dfmt.format(cell.getNumericCellValue()); } break; case XSSFCell.CELL_TYPE_STRING: val = ImportUtil.cleanHTML(cell.getRichStringCellValue().getString()); break; } dataObj.put(colHeader, val); dataArray.add(val); //Collect row data } //Insert Query if (dataObj.length() > 0) { // Empty row check (if lenght==0 then all columns are empty) importDao.dumpFileRow(tableName, dataArray.toArray()); if (flushCounter % 30 == 0) { session.flush(); session.clear(); } flushCounter++; } } try { txnManager.commit(status); } catch (Exception ex) { commitedEx = true; throw ex; } } catch (IOException ex) { throw ServiceException.FAILURE("dumpXLSXFileData: " + ex.getMessage(), ex); } catch (Exception ex) { if (!commitedEx) { //if exception occurs during commit then dont call rollback txnManager.rollback(status); } throw ServiceException.FAILURE("dumpXLSXFileData: " + ex.getMessage(), ex); } }