List of usage examples for org.apache.poi.hssf.usermodel HSSFRow getPhysicalNumberOfCells
@Override public int getPhysicalNumberOfCells()
From source file:com.cn.controller.BaseInfoController.java
/** * ?//from ww w .j a v a2 s .c o m * * @param fileName * @return */ public int importData(String fileName) { InputStream inputStream = null; try { File file = new File(fileName); inputStream = new FileInputStream(file); HSSFWorkbook workbook = new HSSFWorkbook(inputStream); HSSFSheet sheet = workbook.getSheetAt(0); ArrayList<BaseInfo> imports = new ArrayList<>(); for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) { // logger.info("count row num:" + sheet.getPhysicalNumberOfRows() + ",the row num is:" + i); HSSFRow row = sheet.getRow(i); if (null == row) { continue; } int cellNum = row.getPhysicalNumberOfCells(); // logger.info("count cell num is:" + cellNum); if (cellNum >= 4) { BaseInfo info = new BaseInfo(); row.getCell(0).setCellType(Cell.CELL_TYPE_STRING); info.setPinMing(row.getCell(0).getStringCellValue()); row.getCell(1).setCellType(Cell.CELL_TYPE_STRING); info.setJianHao(row.getCell(1).getStringCellValue()); if (Units.strIsEmpty(info.getJianHao())) continue; row.getCell(3).setCellType(Cell.CELL_TYPE_NUMERIC); info.setCarNum((int) row.getCell(3).getNumericCellValue()); row.getCell(2).setCellType(Cell.CELL_TYPE_STRING); info.setCarModel(row.getCell(2).getStringCellValue()); imports.add(info); } } DatabaseOpt opt; Connection conn = null; CallableStatement statement = null; opt = new DatabaseOpt(); try { conn = opt.getConnect(); conn.setAutoCommit(false); statement = conn.prepareCall("insert into tbBaseInfo(baseId, pinMing, jianHao, carModel, carNum)" + "values(BASEID.NEXTVAL, ?, ?, ?, ?)"); for (BaseInfo infoImport : imports) { statement.setString(1, infoImport.getPinMing()); statement.setString(2, infoImport.getJianHao()); statement.setString(3, infoImport.getCarModel()); statement.setInt(4, infoImport.getCarNum()); statement.addBatch(); } statement.executeBatch(); conn.commit(); return 0; } catch (SQLException ex) { try { if (conn != null) conn.rollback(); } catch (SQLException ex1) { logger.error("?", ex1); } logger.error("?", ex); } finally { try { if (statement != null) { statement.close(); } if (conn != null) { conn.close(); } } catch (SQLException ex) { logger.error("?", ex); } } } catch (FileNotFoundException ex) { logger.error("", ex); } catch (IOException ex) { logger.error("IO", ex); } finally { try { if (null != inputStream) { inputStream.close(); } } catch (IOException ex) { logger.error("?", ex); } } return -1; }
From source file:com.cn.controller.MaterialBaseInfoController.java
/** * ?/*from w w w . j av a2 s . c om*/ * * @param fileName * @param carrierName * @return */ public int importData(String fileName, String carrierName) { InputStream inputStream = null; try { File file = new File(fileName); inputStream = new FileInputStream(file); HSSFWorkbook workbook = new HSSFWorkbook(inputStream); HSSFSheet sheet = workbook.getSheetAt(0); ArrayList<MaterialBaseInfo> imports = new ArrayList<>(); for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) { // logger.info("count row num:" + sheet.getPhysicalNumberOfRows() + ",the row num is:" + i); HSSFRow row = sheet.getRow(i); if (null == row) { continue; } int cellNum = row.getPhysicalNumberOfCells(); // logger.info("count cell num is:" + cellNum); if (cellNum >= 4) { MaterialBaseInfo info = new MaterialBaseInfo(); row.getCell(0).setCellType(Cell.CELL_TYPE_STRING); info.setPinMing(row.getCell(0).getStringCellValue()); row.getCell(1).setCellType(Cell.CELL_TYPE_STRING); info.setJianHao(row.getCell(1).getStringCellValue()); if (Units.strIsEmpty(info.getJianHao())) continue; row.getCell(2).setCellType(Cell.CELL_TYPE_STRING); info.setCarModel(row.getCell(2).getStringCellValue()); row.getCell(3).setCellType(Cell.CELL_TYPE_STRING); info.setProductJianHao(row.getCell(3).getStringCellValue()); row.getCell(4).setCellType(Cell.CELL_TYPE_NUMERIC); info.setCarNum((int) row.getCell(4).getNumericCellValue()); imports.add(info); } } DatabaseOpt opt; Connection conn = null; CallableStatement statement = null; opt = new DatabaseOpt(); try { conn = opt.getConnect(); conn.setAutoCommit(false); statement = conn.prepareCall( "insert into tbMaterialBaseInfo(materialBaseId, pinMing, jianHao, carModel, carNum, carrierName, productJianHao)" + "values(MATERIALBASEID.NEXTVAL, ?, ?, ?, ?, ?, ?)"); for (MaterialBaseInfo infoImport : imports) { statement.setString(1, infoImport.getPinMing()); statement.setString(2, infoImport.getJianHao()); statement.setString(3, infoImport.getCarModel()); statement.setInt(4, infoImport.getCarNum()); statement.setString(5, carrierName); statement.setString(6, infoImport.getProductJianHao()); statement.addBatch(); } statement.executeBatch(); conn.commit(); return 0; } catch (SQLException ex) { try { if (conn != null) conn.rollback(); } catch (SQLException ex1) { logger.error("?", ex1); } logger.error("?", ex); } finally { try { if (statement != null) { statement.close(); } if (conn != null) { conn.close(); } } catch (SQLException ex) { logger.error("?", ex); } } } catch (FileNotFoundException ex) { logger.error("", ex); } catch (IOException ex) { logger.error("IO", ex); } finally { try { if (null != inputStream) { inputStream.close(); } } catch (IOException ex) { logger.error("?", ex); } } return -1; }
From source file:com.cn.controller.MaterialInController.java
public int importData(String fileName, String carrierName) { InputStream inputStream = null; try {/*from w w w . j a v a 2s . com*/ File file = new File(fileName); inputStream = new FileInputStream(file); HSSFWorkbook workbook = new HSSFWorkbook(inputStream); HSSFSheet sheet = workbook.getSheetAt(0); ArrayList<MaterialIn> imports = new ArrayList<>(); for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) { // logger.info("count row num:" + sheet.getPhysicalNumberOfRows() + ",the row num is:" + i); HSSFRow row = sheet.getRow(i); if (null == row) { continue; } int cellNum = row.getPhysicalNumberOfCells(); // logger.info("count cell num is:" + cellNum); if (cellNum >= 5) { MaterialIn info = new MaterialIn(); row.getCell(0).setCellType(Cell.CELL_TYPE_STRING); info.setPinMing(row.getCell(0).getStringCellValue()); row.getCell(1).setCellType(Cell.CELL_TYPE_STRING); info.setJianHao(row.getCell(1).getStringCellValue()); row.getCell(2).setCellType(Cell.CELL_TYPE_STRING); info.setCarModel(row.getCell(2).getStringCellValue()); row.getCell(3).setCellType(Cell.CELL_TYPE_NUMERIC); info.setCarCount((int) row.getCell(3).getNumericCellValue()); row.getCell(4).setCellType(Cell.CELL_TYPE_STRING); info.setRemark(row.getCell(4).getStringCellValue()); imports.add(info); } } return batchAddMaterial(carrierName, imports); } catch (FileNotFoundException ex) { logger.error("", ex); } catch (IOException ex) { logger.error("IO", ex); } finally { try { if (null != inputStream) { inputStream.close(); } } catch (IOException ex) { logger.error("?", ex); } } return -1; }
From source file:com.cn.controller.MaterialInitController.java
/** * ????// w ww.j a v a 2 s .c o m * * @param fileName * @param carrierName * @return */ public int importData(String fileName, String carrierName) { InputStream inputStream = null; try { File file = new File(fileName); inputStream = new FileInputStream(file); HSSFWorkbook workbook = new HSSFWorkbook(inputStream); HSSFSheet sheet = workbook.getSheetAt(0); ArrayList<MaterialInit> imports = new ArrayList<>(); for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) { // logger.info("count row num:" + sheet.getPhysicalNumberOfRows() + ",the row num is:" + i); HSSFRow row = sheet.getRow(i); if (null == row) { continue; } int cellNum = row.getPhysicalNumberOfCells(); // logger.info("count cell num is:" + cellNum); if (cellNum >= 4) { MaterialInit info = new MaterialInit(); row.getCell(0).setCellType(Cell.CELL_TYPE_STRING); info.setJianHao(row.getCell(0).getStringCellValue()); if (Units.strIsEmpty(info.getJianHao())) { continue; } row.getCell(1).setCellType(Cell.CELL_TYPE_STRING); info.setPinMing(row.getCell(1).getStringCellValue()); row.getCell(2).setCellType(Cell.CELL_TYPE_STRING); info.setCarModel(row.getCell(2).getStringCellValue()); row.getCell(3).setCellType(Cell.CELL_TYPE_NUMERIC); info.setInitNum((int) row.getCell(3).getNumericCellValue()); if (row.getCell(4).getCellType() == Cell.CELL_TYPE_STRING) { info.setInitDate(row.getCell(4).getStringCellValue()); } else { if (HSSFDateUtil.isCellDateFormatted(row.getCell(4))) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); Date date = HSSFDateUtil.getJavaDate(row.getCell(4).getNumericCellValue()); info.setInitDate(dateFormat.format(date)); } else { DecimalFormat df = new DecimalFormat("0"); info.setInitDate(df.format(row.getCell(4).getNumericCellValue())); } } info.setCarrierName(carrierName); imports.add(info); } } return batchAddData(carrierName, imports); } catch (FileNotFoundException ex) { logger.error("", ex); } catch (IOException ex) { logger.error("IO", ex); } finally { try { if (null != inputStream) { inputStream.close(); } } catch (IOException ex) { logger.error("?", ex); } } return -1; }
From source file:com.cn.controller.MaterialOutController.java
public int importData(String fileName, String carrierName) { InputStream inputStream = null; try {// w ww.ja v a 2 s . co m File file = new File(fileName); inputStream = new FileInputStream(file); HSSFWorkbook workbook = new HSSFWorkbook(inputStream); HSSFSheet sheet = workbook.getSheetAt(0); ArrayList<MaterialOut> imports = new ArrayList<>(); for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) { // logger.info("count row num:" + sheet.getPhysicalNumberOfRows() + ",the row num is:" + i); HSSFRow row = sheet.getRow(i); if (null == row) { continue; } int cellNum = row.getPhysicalNumberOfCells(); // logger.info("count cell num is:" + cellNum); if (cellNum >= 5) { MaterialOut info = new MaterialOut(); row.getCell(0).setCellType(Cell.CELL_TYPE_STRING); info.setPinMing(row.getCell(0).getStringCellValue()); row.getCell(1).setCellType(Cell.CELL_TYPE_STRING); info.setJianHao(row.getCell(1).getStringCellValue()); row.getCell(2).setCellType(Cell.CELL_TYPE_STRING); info.setCarModel(row.getCell(2).getStringCellValue()); row.getCell(3).setCellType(Cell.CELL_TYPE_NUMERIC); info.setCarCount((int) row.getCell(3).getNumericCellValue()); row.getCell(4).setCellType(Cell.CELL_TYPE_STRING); info.setRemark(row.getCell(4).getStringCellValue()); imports.add(info); } } return batchAddMaterial(carrierName, imports); } catch (FileNotFoundException ex) { logger.error("", ex); } catch (IOException ex) { logger.error("IO", ex); } finally { try { if (null != inputStream) { inputStream.close(); } } catch (IOException ex) { logger.error("?", ex); } } return -1; }
From source file:com.cn.controller.OrderController.java
/** * ?(???, ?)//w ww . j a v a 2 s . c o m * * @param fileName * @param productLineId * @param isEmergency * @param isGuanShu * @return * @throws IllegalStateException */ public int importOrder(String fileName, int productLineId, int isEmergency, int isGuanShu) throws IllegalStateException { InputStream inputStream = null; try { File file = new File(fileName); inputStream = new FileInputStream(file); HSSFWorkbook workbook = new HSSFWorkbook(inputStream); HSSFSheet sheet = workbook.getSheetAt(0); ArrayList<OrderInfo> imports = new ArrayList<>(); for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) { // logger.info("count row num:" + sheet.getPhysicalNumberOfRows() + ",the row num is:" + i); HSSFRow row = sheet.getRow(i); if (null == row) { continue; } int cellNum = row.getPhysicalNumberOfCells(); // logger.info("count cell num is:" + cellNum); if (cellNum >= 8) { OrderInfo info = new OrderInfo(); info.setProductLineId(productLineId); row.getCell(0).setCellType(Cell.CELL_TYPE_STRING); info.setProductCommand(row.getCell(0).getStringCellValue()); row.getCell(1).setCellType(Cell.CELL_TYPE_STRING); info.setProductCode(row.getCell(1).getStringCellValue()); row.getCell(2).setCellType(Cell.CELL_TYPE_STRING); info.setProductName(row.getCell(2).getStringCellValue()); row.getCell(3).setCellType(Cell.CELL_TYPE_STRING); info.setProductUnit(row.getCell(3).getStringCellValue()); row.getCell(4).setCellType(Cell.CELL_TYPE_NUMERIC); info.setPlanNum((int) row.getCell(4).getNumericCellValue()); row.getCell(5).setCellType(Cell.CELL_TYPE_STRING); info.setProductStandard(row.getCell(5).getStringCellValue()); if (row.getCell(6).getCellType() == Cell.CELL_TYPE_STRING) { info.setPlanFinishTime(row.getCell(6).getStringCellValue()); } else { if (HSSFDateUtil.isCellDateFormatted(row.getCell(6))) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); Date date = HSSFDateUtil.getJavaDate(row.getCell(6).getNumericCellValue()); info.setPlanFinishTime(dateFormat.format(date)); } else { DecimalFormat df = new DecimalFormat("0"); info.setPlanFinishTime(df.format(row.getCell(6).getNumericCellValue())); } } row.getCell(7).setCellType(Cell.CELL_TYPE_STRING); info.setCustomerName(row.getCell(7).getStringCellValue()); imports.add(info); } } DatabaseOpt opt; Connection conn = null; CallableStatement statement = null; opt = new DatabaseOpt(); try { conn = opt.getConnect(); conn.setAutoCommit(false); statement = conn.prepareCall("{call [tbAddOrderInfo](?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}"); for (OrderInfo infoImport : imports) { statement.setInt("productLineId", infoImport.getProductLineId()); statement.setInt("planNum", infoImport.getPlanNum()); statement.setString("productCommand", infoImport.getProductCommand()); statement.setString("productCode", infoImport.getProductCode()); statement.setString("productName", infoImport.getProductName()); statement.setString("productUnit", infoImport.getProductUnit()); statement.setString("productStandard", infoImport.getProductStandard()); statement.setString("planFinishedTime", infoImport.getPlanFinishTime()); statement.setInt("isEmergency", isEmergency); statement.setInt("isGuanShu", isGuanShu); statement.setString("customerName", infoImport.getCustomerName()); statement.addBatch(); } statement.executeBatch(); conn.commit(); return 0; } catch (SQLException ex) { try { if (conn != null) conn.rollback(); } catch (SQLException ex1) { logger.error("?", ex1); } logger.error("?", ex); } finally { try { if (statement != null) { statement.close(); } if (conn != null) { conn.close(); } } catch (SQLException ex) { logger.error("?", ex); } } } catch (FileNotFoundException ex) { logger.error("", ex); } catch (IOException ex) { logger.error("IO", ex); } finally { try { if (null != inputStream) { inputStream.close(); } } catch (IOException ex) { logger.error("?", ex); } } return -1; }
From source file:com.cn.controller.ProductInitController.java
/** * ????//from w w w.ja v a2s. c om * * @param fileName * @param carrierName * @return */ public int importData(String fileName, String carrierName) { InputStream inputStream = null; try { File file = new File(fileName); inputStream = new FileInputStream(file); HSSFWorkbook workbook = new HSSFWorkbook(inputStream); HSSFSheet sheet = workbook.getSheetAt(0); ArrayList<ProductInit> imports = new ArrayList<>(); for (int i = 1; i <= sheet.getPhysicalNumberOfRows(); i++) { // logger.info("count row num:" + sheet.getPhysicalNumberOfRows() + ",the row num is:" + i); HSSFRow row = sheet.getRow(i); if (null == row) { continue; } int cellNum = row.getPhysicalNumberOfCells(); // logger.info("count cell num is:" + cellNum); if (cellNum >= 4) { ProductInit info = new ProductInit(); row.getCell(0).setCellType(Cell.CELL_TYPE_STRING); info.setJianHao(row.getCell(0).getStringCellValue()); if (Units.strIsEmpty(info.getJianHao())) continue; row.getCell(1).setCellType(Cell.CELL_TYPE_STRING); info.setPinMing(row.getCell(1).getStringCellValue()); row.getCell(2).setCellType(Cell.CELL_TYPE_STRING); info.setCarModel(row.getCell(2).getStringCellValue()); row.getCell(3).setCellType(Cell.CELL_TYPE_NUMERIC); info.setInitNum((int) row.getCell(3).getNumericCellValue()); if (row.getCell(4).getCellType() == Cell.CELL_TYPE_STRING) { info.setInitDate(row.getCell(4).getStringCellValue()); } else { if (HSSFDateUtil.isCellDateFormatted(row.getCell(4))) { SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); Date date = HSSFDateUtil.getJavaDate(row.getCell(4).getNumericCellValue()); info.setInitDate(dateFormat.format(date)); } else { DecimalFormat df = new DecimalFormat("0"); info.setInitDate(df.format(row.getCell(4).getNumericCellValue())); } } info.setCarrierName(carrierName); imports.add(info); } } DatabaseOpt opt; Connection conn = null; CallableStatement statement = null; opt = new DatabaseOpt(); try { conn = opt.getConnect(); conn.setAutoCommit(false); statement = conn.prepareCall( "insert into tbProductInit(productInitId, pinMing, jianHao, carModel, initNum, initDate, carrierName)" + "values(productInitId.NEXTVAL, ?, ?, ?, ?, ?, ?)"); for (ProductInit infoImport : imports) { statement.setString(1, infoImport.getPinMing()); statement.setString(2, infoImport.getJianHao()); statement.setString(3, infoImport.getCarModel()); statement.setInt(4, infoImport.getInitNum()); statement.setString(5, infoImport.getInitDate()); statement.setString(6, infoImport.getCarrierName()); statement.addBatch(); } statement.executeBatch(); conn.commit(); return 0; } catch (SQLException ex) { try { if (conn != null) { conn.rollback(); } } catch (SQLException ex1) { logger.error("?", ex1); } logger.error("?", ex); } finally { try { if (statement != null) { statement.close(); } if (conn != null) { conn.close(); } } catch (SQLException ex) { logger.error("?", ex); } } } catch (FileNotFoundException ex) { logger.error("", ex); } catch (IOException ex) { logger.error("IO", ex); } finally { try { if (null != inputStream) { inputStream.close(); } } catch (IOException ex) { logger.error("?", ex); } } return -1; }
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 ww . j a v a 2s . c o 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 . j ava 2 s. c om * * @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);/*from ww w . jav a2 s . c om*/ 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); } }