List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt
@Override public HSSFSheet getSheetAt(int index)
From source file:com.codecrate.shard.transfer.excel.ExcelObjectImporter.java
License:Apache License
public Collection importObjects(File file, ProgressMonitor progress) { Collection results = new ArrayList(); try {/* ww w .j a v a2s. co m*/ POIFSFileSystem poifs = new POIFSFileSystem(new FileInputStream(file)); HSSFWorkbook workbook = new HSSFWorkbook(poifs); HSSFSheet sheet = workbook.getSheetAt(0); int firstRow = sheet.getFirstRowNum() + 1; int lastRow = sheet.getLastRowNum(); progress.startTask("Import objects from " + file.getName(), lastRow - firstRow); for (int currentRow = firstRow; currentRow <= lastRow; currentRow++) { HSSFRow row = sheet.getRow(currentRow); try { Object result = rowHandler.handleRow(row); results.add(result); } catch (Exception e) { LOG.error("Error importing row: " + currentRow, e); } progress.completeUnitOfWork(); } } catch (Exception e) { LOG.error("Error importing file: " + file, e); } progress.finish(); return results; }
From source file:com.codequicker.quick.templates.source.adapters.ExcelSourceAdapter.java
License:Apache License
private void readOleBasedData(BufferedInputStream bufferedStream, Map<String, List<Map<String, String>>> data) { try {// w ww . jav a 2 s . c o m HSSFWorkbook workbook = new HSSFWorkbook(bufferedStream); int sheetCount = workbook.getNumberOfSheets(); for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) { HSSFSheet sheet = workbook.getSheetAt(sheetIndex); Cell cell = null; List<Map<String, String>> sheetData = new ArrayList<Map<String, String>>(); int lastRowNumber = sheet.getLastRowNum(); for (int rowIndex = 0; rowIndex <= lastRowNumber; rowIndex++) { HSSFRow row = sheet.getRow(rowIndex); if (row == null) { continue; } Map<String, String> columnData = new HashMap<String, String>(); for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) { cell = row.getCell(cellIndex, Row.CREATE_NULL_AS_BLANK); columnData.put("column" + (cellIndex + 1), cell.toString()); } sheetData.add(columnData); } data.put("sheet" + (sheetIndex + 1), sheetData); } } catch (Exception e) { throw new RuntimeException(e); } }
From source file:com.codesnippets4all.jthunder.extension.plugins.input.excel.ExcelReaderPlugin.java
License:Apache License
@SuppressWarnings("rawtypes") private void readOLEbasedExcel(BufferedInputStream bufferedStream, List<Sheet> sheets) throws IOException { HSSFWorkbook workbook = new HSSFWorkbook(bufferedStream); int sheetCount = workbook.getNumberOfSheets(); for (int index = 0; index < sheetCount; index++) { HSSFSheet sheet = workbook.getSheetAt(index); Sheet s = new Sheet(); sheets.add(s);// w ww. j a va2 s. c o m int lastRowNumber = sheet.getLastRowNum(); for (int rowIndex = 0; rowIndex < lastRowNumber; rowIndex++) { HSSFRow row = sheet.getRow(rowIndex); Record record = new Record(); s.addRecord(record); Iterator it = row.cellIterator(); while (it.hasNext()) { record.addCellValue(it.next()); } } } }
From source file:com.comcast.cats.config.ui.monitoring.reboot.UpTimeAndRebootStatusBean.java
License:Open Source License
public void postProcessXls(Object document) { logger.trace("postProcessXls start document " + document); if (document != null) { HSSFWorkbook workBook = (HSSFWorkbook) document; HSSFSheet sheet = workBook.getSheetAt(0); HSSFRow headerRow = sheet.getRow(0); for (int i = 0; i < headerRow.getPhysicalNumberOfCells(); i++) { sheet.setColumnWidth(i, 30 * 265); // width for 40 characters }/*w w w .ja v a2 s . c om*/ sheet.shiftRows(0, sheet.getLastRowNum(), 5); // shift rows 0 to n // by 1 to get space // for header sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F3")); HSSFFont headerFont = workBook.createFont(); headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle headerCellStyle = workBook.createCellStyle(); headerCellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headerCellStyle.setFont(headerFont); headerCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCell headerCell = headerRow.createCell(0); headerCell.setCellStyle(headerCellStyle); headerCell.setCellValue("CATS Uptime and Reboot Status : " + (new Date())); HSSFCellStyle metaDataCellStyle = workBook.createCellStyle(); metaDataCellStyle.setFont(headerFont); HSSFRow metaDataRow = sheet.getRow(3); if (metaDataRow == null) { metaDataRow = sheet.createRow(3); } HSSFCell metaDataKey = metaDataRow.createCell(0); metaDataKey.setCellStyle(metaDataCellStyle); metaDataKey.setCellValue("CATS Instance"); HSSFCell metaDataValue = metaDataRow.createCell(1); metaDataValue.setCellStyle(metaDataCellStyle); metaDataValue.setCellValue(AuthController.getHostAddress()); HSSFCellStyle datatTableHeaderCellStyle = workBook.createCellStyle(); datatTableHeaderCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); datatTableHeaderCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); datatTableHeaderCellStyle.setFont(headerFont); HSSFRow actualDataTableHeaderRow = sheet.getRow(5); for (int i = 0; i < actualDataTableHeaderRow.getPhysicalNumberOfCells(); i++) { HSSFCell cell = actualDataTableHeaderRow.getCell(i); if (cell != null) { String cellValue = cell.getStringCellValue(); cellValue = cellValue.replace("<br/> ", ""); // replace // any line // breaks cell.setCellValue(cellValue); cell.setCellStyle(datatTableHeaderCellStyle); } } } logger.trace("postProcessXls end"); }
From source file:com.concursive.connect.web.modules.plans.utils.AssignmentExcelImporter.java
License:Open Source License
/** * Description of the Method//w w w.j a v a2 s . co m * * @param requirement Description of the Parameter * @param db Description of the Parameter * @param buffer Description of the Parameter * @return Description of the Return Value * @throws java.sql.SQLException Description of the Exception */ public static boolean parse(byte[] buffer, Requirement requirement, Connection db) throws SQLException { if (System.getProperty("DEBUG") != null) { System.out.println("AssignmentExcelImporter-> parseExcel"); } try { db.setAutoCommit(false); // stream the Excel Spreadsheet from the uploaded byte array POIFSFileSystem fs = new POIFSFileSystem(new ByteArrayInputStream(buffer)); HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs); // get the first sheet HSSFSheet sheet = hssfworkbook.getSheetAt(0); // define objects for housing spreadsheet data HSSFRow currentRow = sheet.getRow(0); // parse each row, create and insert into a new requirement with a tree int rows = sheet.getPhysicalNumberOfRows(); if (System.getProperty("DEBUG") != null) { System.out.println("AssignmentExcelImporter-> Number of rows: " + rows); } // Columns int columnHeader = -1; int columnMax = -1; boolean columnItemComplete = false; short itemColumn = -1; short priorityColumn = -1; short assignedToColumn = -1; short effortColumn = -1; short startColumn = -1; short endColumn = -1; // parse for (int r = 0; r < rows; r++) { currentRow = sheet.getRow(r); if (currentRow != null) { // Search for header if (columnHeader == -1) { int cells = currentRow.getPhysicalNumberOfCells(); for (short c = 0; c < cells; c++) { HSSFCell cell = currentRow.getCell(c); if (cell != null) { if ("Item".equals(getValue(cell))) { columnHeader = r; itemColumn = c; columnMax = c; } else if (itemColumn > -1 && !columnItemComplete && c > itemColumn) { if ("".equals(getValue(cell))) { columnMax = c; } else if (!"".equals(getValue(cell))) { columnItemComplete = true; } } if ("Priority".equals(getValue(cell))) { columnHeader = r; priorityColumn = c; } else if ("Assigned To".equals(getValue(cell))) { columnHeader = r; assignedToColumn = c; } else if ("Lead".equals(getValue(cell))) { columnHeader = r; assignedToColumn = c; } else if ("Effort".equals(getValue(cell))) { columnHeader = r; effortColumn = c; } else if ("Start".equals(getValue(cell))) { columnHeader = r; startColumn = c; } else if ("End".equals(getValue(cell))) { columnHeader = r; endColumn = c; } } } } // Process each column if (columnHeader > -1 && r > columnHeader) { boolean gotOne = false; Assignment assignment = new Assignment(); assignment.setProjectId(requirement.getProjectId()); assignment.setRequirementId(requirement.getId()); // Activities and folders if (itemColumn > -1) { // Get the first indent level that has data for (short c = itemColumn; c <= columnMax; c++) { HSSFCell cell = currentRow.getCell(c); if (cell != null && !"".equals(getValue(cell))) { assignment.setRole(getValue(cell)); assignment.setIndent(c); gotOne = true; break; } } } if (gotOne) { // Priority if (priorityColumn > -1) { HSSFCell cell = currentRow.getCell(priorityColumn); if (cell != null) { assignment.setPriorityId(getValue(cell)); } } // Effort if (effortColumn > -1) { HSSFCell cell = currentRow.getCell(effortColumn); if (cell != null) { assignment.setEstimatedLoe(getValue(cell)); if (assignment.getEstimatedLoeTypeId() == -1) { assignment.setEstimatedLoeTypeId(2); } } } // Assigned To if (assignedToColumn > -1) { HSSFCell cell = currentRow.getCell(assignedToColumn); if (cell != null) { assignment.addUsers(getValue(cell)); } } // Start Date if (startColumn > -1) { HSSFCell cell = currentRow.getCell(startColumn); if (cell != null) { assignment.setEstStartDate(getDateValue(cell)); } } // Due Date if (endColumn > -1) { HSSFCell cell = currentRow.getCell(endColumn); if (cell != null) { assignment.setDueDate(getDateValue(cell)); } } assignment.setEnteredBy(requirement.getEnteredBy()); assignment.setModifiedBy(requirement.getModifiedBy()); assignment.setStatusId(1); // Make sure a valid priority is set if (assignment.getPriorityId() < 1 || assignment.getPriorityId() > 3) { assignment.setPriorityId(2); } // Make sure user is on team, before adding, else unset the field if (!assignment.hasValidTeam(db)) { assignment.getAssignedUserList().clear(); } // Insert the assignment assignment.insert(db); if (System.getProperty("DEBUG") != null) { System.out.println( "AssignmentExcelImporter-> Assignment Inserted: " + assignment.getId()); } } } } } db.commit(); } catch (Exception e) { db.rollback(); e.printStackTrace(System.out); return false; } finally { db.setAutoCommit(true); } return true; }
From source file:com.creativity.controller.PesquisaFichasBean.java
public void posProcessarXls(Object documento) { HSSFWorkbook planilha = (HSSFWorkbook) documento; HSSFSheet folha = planilha.getSheetAt(0); HSSFRow cabecalho = folha.getRow(0); HSSFCellStyle estiloCelula = planilha.createCellStyle(); HSSFFont fonteCabecalho = planilha.createFont(); fonteCabecalho.setColor(IndexedColors.BLACK.getIndex()); fonteCabecalho.setBold(true);// w w w .jav a2 s.c o m fonteCabecalho.setFontHeightInPoints((short) 10); estiloCelula.setFont(fonteCabecalho); estiloCelula.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); estiloCelula.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < cabecalho.getPhysicalNumberOfCells(); i++) { cabecalho.getCell(i).setCellStyle(estiloCelula); } }
From source file:com.cseur.utils.ExcelReader.java
public ArrayList<ArrayList<String>> readToMap() throws IOException { FileInputStream file = new FileInputStream(xlsFile); //Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(file); //Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); //get pol pod via1 via2 via3 via4 index in column and saved to array ArrayList<Integer> portIndexs = new ArrayList(Arrays.asList(null, null, null, null, null, null, null)); boolean isTableHeader = true; //Iterate through each rows one by one Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { //read file, find the columnindex for required order, and then insert the value to valueArray. Row row = rowIterator.next();//www .j av a 2 s .c o m // //escape empty lines Iterator<Cell> cellIterator = row.cellIterator(); if (isTableHeader) { while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); //set absolute columnIndex in the order of POL, POD, VIA1.2.l.3.4 switch (cell.getStringCellValue().trim().toUpperCase()) { case "POL": portIndexs.set(0, cell.getColumnIndex()); break; case "POD": portIndexs.set(1, cell.getColumnIndex()); break; case "VIA1": portIndexs.set(2, cell.getColumnIndex()); break; case "VIA2": portIndexs.set(3, cell.getColumnIndex()); break; case "VIA3": portIndexs.set(4, cell.getColumnIndex()); break; case "VIA4": portIndexs.set(5, cell.getColumnIndex()); break; // case "VIA5": // portIndexs.add(cell.getColumnIndex()); // break; // case "VIA6": // portIndexs.add(cell.getColumnIndex()); // break; // case "VIA7": // portIndexs.add(cell.getColumnIndex()); // break; // case "VIA8": // portIndexs.add(cell.getColumnIndex()); // break; // case "VIA9": // portIndexs.add(cell.getColumnIndex()); // break; // case "VIA0": // portIndexs.add(cell.getColumnIndex()); // break; } // portIndexs.trimToSize(); } } if (!isTableHeader) { ArrayList<String> valueArrayList = new ArrayList<>(); for (int i = 0; portIndexs.get(i) != null; i++) { if (row.getCell(portIndexs.get(i)) != null && !row.getCell(portIndexs.get(i)).getStringCellValue().isEmpty()) { valueArrayList .add(row.getCell(portIndexs.get(i)).getStringCellValue().trim().toUpperCase()); } } System.out.println(valueArrayList.size()); valueArrayList_X.add(valueArrayList); //reference added to arraylist. clear the valueArrayList remove the values in valueArrayList_X Too!! //valueArrayList.clear(); } isTableHeader = false;//add contents to vector. } file.close(); return valueArrayList_X; }
From source file:com.ctb.importdata.ImportSFDataProcessor.java
public static ArrayList<SalesForceLicenseData> readDataFromXLSFile(String fileName) { File sfDataFile = new File(fileName); FileInputStream fileInputStream = null; ArrayList<SalesForceLicenseData> sfLicenseDataList = null; SalesForceLicenseData sfld = null;//from www . j a va 2 s . c o m if (sfDataFile.exists()) { //System.out.println("Reading data from .xls file started."); logger.info("Reading data from .xls file : Started :: Timestamp >> " + new Date(System.currentTimeMillis())); try { //read the file in to stream fileInputStream = new FileInputStream(sfDataFile); //Create Workbook instance holding reference to .xls file HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); //Get first/desired sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); sfLicenseDataList = new ArrayList<SalesForceLicenseData>(); if (sheet != null) { int totalRows = sheet.getPhysicalNumberOfRows(); //System.out.println("Total no. of physical rows in file = "+ totalRows); logger.info("Total no. of physical rows in file = " + totalRows); Row headerRow = sheet.getRow(0); Cell headerCell; Cell dataCell; if (headerRow == null) { //System.out.println("No file header content found.") ; logger.info("No file header content found."); } else { int totalHeaderColumns = headerRow.getPhysicalNumberOfCells(); //System.out.println("Total no. of header cells = "+ totalHeaderColumns); logger.info("Total no. of header cells = " + totalHeaderColumns); for (int rowCtr = 1; rowCtr < totalRows; rowCtr++) { //System.out.println("Row No. >> "+rowCtr); Row dataRow = sheet.getRow(rowCtr); if (dataRow != null) { int totalRowColumns = dataRow.getPhysicalNumberOfCells(); //System.out.println("Total no. of current data row cells = "+ totalRowColumns); //logger.info("Total no. of current data row cells = "+ totalRowColumns); logger.info( "Row No. [" + rowCtr + "] :: Header Column Count = [" + totalHeaderColumns + "] :: Current Data Row Column Count = [" + totalRowColumns + "]"); //Discard dummy rows in spreadsheet if the count of row columns not equal to header columns if (totalHeaderColumns == totalRowColumns) { boolean isCustomerIdBlank = dataRow.getCell(0) .getCellType() == Cell.CELL_TYPE_BLANK ? true : false; boolean isOrgNodeIdBlank = dataRow.getCell(5) .getCellType() == Cell.CELL_TYPE_BLANK ? true : false; //System.out.println("isCustomerIdBlank >> "+isCustomerIdBlank+" :: isOrgNodeIdBlank >> "+isOrgNodeIdBlank); logger.info("Row No. [" + rowCtr + "] :: isCustomerIdBlank >> " + isCustomerIdBlank + " :: isOrgNodeIdBlank >> " + isOrgNodeIdBlank); //Condition to skip row for SF data object population if customer id or orgnode id is blank if (!isCustomerIdBlank && !isOrgNodeIdBlank) { sfld = new SalesForceLicenseData(); // For each row, loop through each column for (int colCtr = 0; colCtr < totalHeaderColumns; colCtr++) { //System.out.println("Column No. >> "+colCtr); headerCell = headerRow.getCell(colCtr); dataCell = dataRow.getCell(colCtr); if (dataCell != null) { //System.out.println("dataCell.getCellType() >> "+dataCell.getCellType()); switch (dataCell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: //Do nothing //System.out.println(dataCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: //System.out.println(dataCell.getNumericCellValue()); populateSFDataNumericColValue(sfld, dataCell, headerCell); break; case Cell.CELL_TYPE_STRING: //System.out.println(dataCell.getStringCellValue()); populateSFDataStrColValue(sfld, dataCell, headerCell); break; case Cell.CELL_TYPE_BLANK: populateSFDataBlankColValue(sfld, dataCell, headerCell); break; default: System.out.println(dataCell); break; } } } sfLicenseDataList.add(sfld); } } } } } } } catch (FileNotFoundException e) { logger.error("FileNotFoundException : occurred while procesing :: Filename >> [" + fileName + "]"); e.printStackTrace(); // unexpected } catch (IOException e) { logger.error("IOException : occurred while procesing :: Filename >> [" + fileName + "]"); e.printStackTrace(); } finally { try { if (fileInputStream != null) fileInputStream.close(); } catch (IOException e) { logger.error("IOException : occurred while closing file input stream."); e.printStackTrace(); } } //System.out.println("Reading data from .xls file completed."); logger.info("Reading data from .xls file : Completed :: Timestamp >> " + new Date(System.currentTimeMillis())); } else { //System.out.println("File does not exists"); logger.error("File does not exists :: Filename >> [" + fileName + "]"); } return sfLicenseDataList; }
From source file:com.ddt.driver.EXCELDriver.java
private void readOlderVersionExcelFile(String path, String sheetName) { try {/*from w ww .j a v a2 s . com*/ HSSFWorkbook workbook; try (FileInputStream file = new FileInputStream(new File(path))) { workbook = new HSSFWorkbook(file); HSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { String line = ""; Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: line += cell.getBooleanCellValue() + ";"; break; case Cell.CELL_TYPE_NUMERIC: line += String.valueOf(cell.getNumericCellValue()).split("\\.")[0] + ";"; break; case Cell.CELL_TYPE_STRING: line += cell.getStringCellValue() + ";"; break; } } linesOfDriver.add(line); } } } catch (FileNotFoundException e) { Logger.getLogger(EXCELDriver.class.getName()).log(Level.SEVERE, null, e); } catch (IOException e) { Logger.getLogger(EXCELDriver.class.getName()).log(Level.SEVERE, null, e); } }
From source file:com.demo.bean.bandejaBean.java
public void postProcessXLS(Object document) { System.out.println("ENRO A FORMAR ARCHIVO :" + nombreArch); HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0); HSSFRow header = sheet.getRow(0);/* w w w. j av a 2s .c om*/ HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.GOLD.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(cellStyle); } System.out.println("termino de formar el archivo"); }