List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getRow
@Override public HSSFRow getRow(int rowIndex)
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 }//from w w w. ja va 2 s .co m 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/* ww w . ja v a 2s. 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);/* www.ja v a 2s.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.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 w ww. ja v a 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.dawg6.web.dhcalc.server.ExportExcel.java
License:Open Source License
private Cell createTableHeader(HSSFSheet sheet, int col, String label) { int n = sheet.getPhysicalNumberOfRows(); Row row = null;// w w w . jav a 2 s . c om if (n < 1) row = sheet.createRow(0); else row = sheet.getRow(0); Cell cell = row.createCell(col); cell.setCellValue(label); cell.setCellStyle(boldStyle); CellUtil.setAlignment(cell, sheet.getWorkbook(), CellStyle.ALIGN_CENTER); return cell; }
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); 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);//from w ww . j av a 2s . c o m } System.out.println("termino de formar el archivo"); }
From source file:com.demo.bean.ExportarGestionesBean.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0); HSSFRow header = sheet.getRow(0); 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);//from ww w .j a va 2s . c o m } }
From source file:com.demo.bean.ReportePagosBean.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0); HSSFRow header = sheet.getRow(0); 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);//ww w .j a va 2s.co m } LogSistema .guardarlog(this.getClass().getName() + " Method: postProcessXLS, se exporto un reporte de pagos "); }
From source file:com.demo.bean.VisitaDatosBean.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0); HSSFRow header = sheet.getRow(0); 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);//ww w. java2 s .c o m } LogSistema.guardarlog(this.getClass().getName() + " Method: postProcessXLS, se exporto un reporte de visitas domiciliarias"); }
From source file:com.dtrules.compiler.excel.util.ImportRuleSets.java
License:Apache License
private String getCellValue(HSSFSheet sheet, int row, int column) { if (row > sheet.getLastRowNum()) return ""; HSSFRow theRow = sheet.getRow(row); if (theRow == null) return ""; HSSFCell cell = theRow.getCell(column); if (cell == null) return ""; switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: return ""; case HSSFCell.CELL_TYPE_BOOLEAN: return cell.getBooleanCellValue() ? "true" : "false"; case HSSFCell.CELL_TYPE_NUMERIC: { Double v = cell.getNumericCellValue(); if (v.doubleValue() == (v.longValue())) { return Long.toString(v.longValue()); }//w ww . j a va 2s . c om return Double.toString(v); } case HSSFCell.CELL_TYPE_STRING: String v = cell.getRichStringCellValue().getString().trim(); return v; default: return ""; } }