List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetName
@Override public String getSheetName(int sheetIx)
From source file:accounts.ExcelUtils.java
License:Apache License
public Map<String, Map<TRId, TR>> processAllSheets(String filename, Map<String, BankAccount> baMap, String accountName) throws IOException, DBException { Map<String, Map<TRId, TR>> excelTrMap = new TreeMap<>(); FileInputStream file = new FileInputStream(new File(filename)); // Get the workbook instance for XLS file XSSFWorkbook workbook = new XSSFWorkbook(file); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { XSSFSheet sheet = workbook.getSheetAt(i); String sheetName = workbook.getSheetName(i); if ("RentalSummary".equalsIgnoreCase(sheetName) || "CompanySummary".equalsIgnoreCase(sheetName) || "PersonalSummary".equalsIgnoreCase(sheetName)) { continue; }/*from w w w.ja v a 2s. c o m*/ if (accountName != null && !accountName.equalsIgnoreCase(sheetName)) { continue; } Map<TRId, TR> mapTr = new HashMap<>(); excelTrMap.put(sheetName, mapTr); System.out.println("Processing sheet: " + sheetName); BankAccount ba = baMap.get(sheetName); if (ba == null) { throw new IOException("Unknown bank account name in excel=" + sheetName); } for (int rownum = 1; rownum <= sheet.getLastRowNum(); rownum++) { Row row = sheet.getRow(rownum); // Get iterator to all cells of current row TR tr = DBFactory.inst().createCorrespondingTRObj(ba); tr.setDate(row.getCell(0).getDateCellValue()); tr.setDescription(row.getCell(1).getStringCellValue()); tr.setDebit((float) row.getCell(2).getNumericCellValue()); tr.setComment(row.getCell(3).getStringCellValue()); tr.setTrType(row.getCell(4).getStringCellValue()); tr.setTaxCategory(row.getCell(5).getStringCellValue()); tr.setProperty(row.getCell(6).getStringCellValue()); tr.setOtherEntity(row.getCell(7).getStringCellValue()); String locked = row.getCell(8).getStringCellValue(); tr.setLocked("YES".equalsIgnoreCase(locked)); tr.setTrId(); mapTr.put(tr.getTrId(), tr); } } return excelTrMap; }
From source file:accounts.ExcelUtils.java
License:Apache License
public Map<String, Map<TRId, TR>> processAllSheets(String filename) throws IOException { Map<String, Map<TRId, TR>> excelTrMap = new TreeMap<>(); FileInputStream file = new FileInputStream(new File(filename)); // Get the workbook instance for XLS file XSSFWorkbook workbook = new XSSFWorkbook(file); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { XSSFSheet sheet = workbook.getSheetAt(i); String sheetName = workbook.getSheetName(i); Map<TRId, TR> mapTr = new HashMap<>(); excelTrMap.put(sheetName, mapTr); System.out.println("Processing sheet: " + sheetName); // Get iterator to all the rows in current sheet Iterator<Row> rowIterator = sheet.iterator(); for (int rownum = 1; rownum <= sheet.getLastRowNum(); rownum++) { Row row = sheet.getRow(rownum); // Get iterator to all cells of current row TR tr = new TRNonDB(); tr.setDate(row.getCell(0).getDateCellValue()); tr.setDescription(row.getCell(1).getStringCellValue()); tr.setDebit((float) row.getCell(2).getNumericCellValue()); tr.setComment(row.getCell(3).getStringCellValue()); tr.setTrType(row.getCell(4).getStringCellValue()); tr.setTaxCategory(row.getCell(5).getStringCellValue()); tr.setProperty(row.getCell(6).getStringCellValue()); tr.setOtherEntity(row.getCell(7).getStringCellValue()); String lockedStr = row.getCell(7).getStringCellValue(); if ("YES".equalsIgnoreCase(lockedStr) || "TRUE".equalsIgnoreCase(lockedStr)) { tr.setLocked(true);//w w w . j av a2s. c o m } tr.setTrId(); mapTr.put(tr.getTrId(), tr); } } return excelTrMap; }
From source file:com.consensus.qa.framework.ExcelOperations.java
private XSSFSheet GetSheetFromWorkBook(XSSFWorkbook workbook, String sheetName) { int numOfWorkBooks = 0; String sheet = null;// w ww .j a v a2s .co m numOfWorkBooks = workBook.getNumberOfSheets(); for (int count = 0; count < numOfWorkBooks; count++) { if (sheetName.toString().toLowerCase().contains(workBook.getSheetName(count).toLowerCase())) { sheet = workBook.getSheetName(count); break; } } if (sheet != null) { worksheet = workBook.getSheet(sheet); } return worksheet; }
From source file:com.excel.javafx.frames.MainFrame.java
private void getSourceFileHeaders(File file) { try {/*from w ww .j a v a 2s . co m*/ FileInputStream sourceFile1 = new FileInputStream(file); XSSFWorkbook workbook1 = new XSSFWorkbook(sourceFile1); sourceSheetSelector.removeAllItems(); //to clear existing headers for (int sheetno = 0; sheetno < workbook1.getNumberOfSheets(); sheetno++) { sourceSheetSelector.addItem(workbook1.getSheetName(sheetno)); } //sourceColumnSelector(); // to fill columnSelection } catch (FileNotFoundException ex) { Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.excel.javafx.frames.MainFrame.java
private void getDestinationFileHeaders(File file) { try {/*from www. j ava2 s .c om*/ FileInputStream destFile = new FileInputStream(file); XSSFWorkbook workbook1 = new XSSFWorkbook(destFile); destSheetSelector.removeAllItems(); //to clear existing headers for (int sheetno = 0; sheetno < workbook1.getNumberOfSheets(); sheetno++) { destSheetSelector.addItem(workbook1.getSheetName(sheetno)); } //destinationColumnSelector(); // to fill columnSelection } catch (FileNotFoundException ex) { Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.krawler.spring.importFunctionality.ImportController.java
License:Open Source License
public ModelAndView fileUploadXLSX(HttpServletRequest request, HttpServletResponse response) { String View = "jsonView-ex"; JSONObject jobj = new JSONObject(); try {// w w w. j av a 2 s .c o m System.out.println("A(( Upload XLSX start : " + new Date()); jobj.put("success", true); FileItemFactory factory = new DiskFileItemFactory(4096, new File(ConfigReader.getinstance().get("UploadTempDir", "/tmp"))); ServletFileUpload upload = new ServletFileUpload(factory); upload.setSizeMax(10485760); // 10Mb List fileItems = upload.parseRequest(request); Iterator i = fileItems.iterator(); String destinationDirectory = storageHandlerImpl.GetDocStorePath() + "xlsfiles"; String fileName = null; String fileid = UUID.randomUUID().toString(); fileid = fileid.replaceAll("-", ""); // To append UUID without "-" [SK] String Ext = ""; while (i.hasNext()) { java.io.File destDir = new java.io.File(destinationDirectory); if (!destDir.exists()) { //Create xls file's folder if not present destDir.mkdirs(); } FileItem fi = (FileItem) i.next(); if (fi.isFormField()) continue; fileName = fi.getName(); if (fileName.contains(".")) { Ext = fileName.substring(fileName.lastIndexOf(".")); int startIndex = fileName.contains("\\") ? (fileName.lastIndexOf("\\") + 1) : 0; fileName = fileName.substring(startIndex, fileName.lastIndexOf(".")); } if (fileName.length() > 28) { // To fixed Mysql ERROR 1103 (42000): Incorrect table name throw new DataInvalidateException("Filename is too long, use upto 28 characters."); } fi.write(new File(destinationDirectory, fileName + "_" + fileid + Ext)); } FileInputStream fs = new FileInputStream(destinationDirectory + "/" + fileName + "_" + fileid + Ext); XSSFWorkbook wb = new XSSFWorkbook(fs); int count = wb.getNumberOfSheets(); JSONArray jArr = new JSONArray(); for (int x = 0; x < count; x++) { JSONObject obj = new JSONObject(); obj.put("name", wb.getSheetName(x)); obj.put("index", x); jArr.put(obj); } jobj.put("file", destinationDirectory + "/" + fileName + "_" + fileid + Ext); jobj.put("filename", fileName + "_" + fileid + Ext); jobj.put("data", jArr); jobj.put("msg", "Image has been successfully uploaded"); jobj.put("lsuccess", true); jobj.put("valid", true); } catch (FileUploadBase.SizeLimitExceededException ex) { Logger.getLogger(ImportController.class.getName()).log(Level.SEVERE, null, ex); jobj.put("msg", "File exceeds max size limit i.e 10MB."); jobj.put("lsuccess", false); jobj.put("valid", true); } catch (Exception e) { Logger.getLogger(ImportController.class.getName()).log(Level.SEVERE, null, e); try { jobj.put("msg", e.getMessage()); jobj.put("lsuccess", false); jobj.put("valid", true); } catch (Exception ex) { } } finally { System.out.println("A(( Upload XLS end : " + new Date()); return new ModelAndView(View, "model", jobj.toString()); } }
From source file:de.fionera.javamailer.dataProcessors.parseFilesForImport.java
/** * Gets a XLSX file and parse it/*from ww w. j av a2 s.c om*/ * @param file The XLSX File that you want to get parsed * @return A ArrayList where the first object is a Array containing the Data and the Second the Header */ public ArrayList<Object> parseXLSXFile(File file) { int index = -1; XSSFWorkbook workbook = null; try { try { FileInputStream inputStream = new FileInputStream(file); workbook = new XSSFWorkbook(inputStream); } catch (IOException ex) { ex.printStackTrace(); } assert workbook != null; String[] strings = new String[workbook.getNumberOfSheets()]; //get all sheet names from selected workbook for (int i = 0; i < strings.length; i++) { strings[i] = workbook.getSheetName(i); } JFrame frame = new JFrame("Input Dialog"); String selectedsheet = (String) JOptionPane.showInputDialog(frame, "Which worksheet you want to import ?", "Select Worksheet", JOptionPane.QUESTION_MESSAGE, null, strings, strings[0]); if (selectedsheet != null) { for (int i = 0; i < strings.length; i++) { if (workbook.getSheetName(i).equalsIgnoreCase(selectedsheet)) index = i; } XSSFSheet sheet = workbook.getSheetAt(index); XSSFRow row = sheet.getRow(0); if (row != null) { headers = new String[row.getLastCellNum()]; for (int i = 0; i < row.getLastCellNum(); i++) { headers[i] = row.getCell(i).toString(); } } data = new String[sheet.getLastRowNum()][]; for (int j = 1; j < sheet.getLastRowNum() + 1; j++) { row = sheet.getRow(j); int rowCount = row.getLastCellNum(); String[] dataRow = new String[rowCount]; for (int i = 0; i < rowCount; i++) { XSSFCell cell = row.getCell(i, org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK); dataRow[i] = cell.toString(); } data[j - 1] = dataRow; } } else { return null; } } catch (Exception e) { e.printStackTrace(); } ArrayList<Object> returnData = new ArrayList<>(); returnData.add(data); returnData.add(headers); return returnData; }
From source file:iscas.tca.ake.demoapp.mvc.module.tools.fileoperator.ExcelBasic.java
License:Open Source License
/** * TODO:<>// w w w.j a v a 2s . co m * @param xwb * @param sheetName * @return */ public static boolean isSheetExists(XSSFWorkbook xwb, String sheetName) { int num = xwb.getNumberOfSheets(); for (int i = 0; i < num; i++) { if (xwb.getSheetName(i).equals(sheetName)) return true; } return false; }
From source file:javaapp.ReadExcelFile.java
public static void main(String[] args) { FileInputStream fileInputStream = null; try {/*from ww w .j a v a 2 s .com*/ fileInputStream = new FileInputStream("GBRCNCOR.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream); // for each sheet in the workbook for (int i = 0; i < workbook.getNumberOfSheets(); i++) { System.out.println("Sheet name: " + workbook.getSheetName(i)); } } catch (IOException e) { e.printStackTrace(); } finally { if (fileInputStream != null) { try { fileInputStream.close(); } catch (IOException e) { e.printStackTrace(); } } } }
From source file:Opm_Package.OpenFileName.java
public List<String> readReposNames(String file) throws Exception { int x = 0;/*from ww w .j a v a2s. c o m*/ OpenFileName fname = new OpenFileName(); // array list to store the Repos names ArrayList<String> list = new ArrayList<String>(); //calling the file name..... XSSFWorkbook workbook = readFileName(file); // setting the sheet number... XSSFSheet spreadsheet = workbook.getSheetAt(x); String sname = workbook.getSheetName(x); Row row; Cell cell = null; for (int j = 0; j < spreadsheet.getLastRowNum() + 1; ++j) {//To loop thru the rows in a sheet row = spreadsheet.getRow(j); cell = row.getCell(0); //forks are in the eighth column... switch (cell.getCellType()) { //Checking for strings values inthe cells.. case Cell.CELL_TYPE_STRING: if (!cell.getStringCellValue().equals("")) { // adding the call value to the arraylist called forksList list.add(cell.getStringCellValue()); } //end of if statement... break; //Checking for numeric values inthe cells.. case Cell.CELL_TYPE_NUMERIC: list.add(String.valueOf(cell.getNumericCellValue())); break; //Checking for bank in the cells.. case Cell.CELL_TYPE_BLANK: break; }//end of switch statement } // end of for loop for the rows.. //returns the arraylist to the main class.... return list; }