List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheetAt
@Override public HSSFSheet getSheetAt(int index)
From source file:com.testmax.util.ExcelSheet.java
License:CDDL license
/** * This method is used to modify data from an excel file. * @param sheetIndex - Index of sheet 0,1,2 etc. * * @param rowColFilter - [filtercol=value]@[modifyCol1=value,modifyCol2=value,...] * filterCol= filter column to identify the row[s] to be modify with a value * modifyCol[n]= column to be modified found by filter col with a value *///from w w w . j a v a 2s . c o m public void modifyMultiRowExcel(int sheetIndex, String rowColFilterText) { try { FileInputStream fileInputStream = new FileInputStream(this.fileName); POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream); HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem); HSSFSheet sheet = workBook.getSheetAt(sheetIndex); sheet.setForceFormulaRecalculation(true); String[] rowColFilters = rowColFilterText.split(";"); for (String rowColFilter : rowColFilters) { String filter = ""; String modcol = ""; try { filter = rowColFilter.split("@")[0].replace("[", "").replace("]", "").trim(); modcol = rowColFilter.split("@")[1].replace("[", "").replace("]", "").trim(); if (filter != null && !filter.isEmpty()) { String[] filters = filter.split("="); int colIndex = this.getColumnIndex(filters[0]); int rowIndex = this.getRowIndexByColumnValue(colIndex, filters[1]); if (rowIndex >= 0) { Row row = sheet.getRow(rowIndex); String[] colModList = modcol.split(","); for (String eachCol : colModList) { String[] eachList = eachCol.split("="); if (eachList.length > 0) { int modColIndex = this.getColumnIndex(eachList[0]); Cell cell = row.getCell(modColIndex); cell.setCellValue(eachList[1]); } } } } } catch (Exception e) { WmLog.printMessage("ERROR in modifying XLs file =" + this.fileName + " Excel Sheet Index=" + sheetIndex + " Col Filter=" + filter + " Mod Col=" + modcol); // TODO Auto-generated catch block e.printStackTrace(); } } FileOutputStream fileOut = new FileOutputStream(this.fileName); workBook.write(fileOut); fileOut.close(); } catch (IOException e) { WmLog.printMessage("ERROR in modifying XLs file =" + this.fileName + "Excel Sheet Index=" + sheetIndex + " with Excel Row Col Filter=" + rowColFilterText); // TODO Auto-generated catch block e.printStackTrace(); } catch (Exception e) { WmLog.printMessage("ERROR in modifying XLs file =" + this.fileName + "Excel Sheet Index=" + sheetIndex + " with Excel Row Col Filter=" + rowColFilterText); // TODO Auto-generated catch block e.printStackTrace(); } }
From source file:com.testmax.util.ExcelSheet.java
License:CDDL license
/** * This method is used to read the data's from an excel file. * @param sheetIndex - Index of sheet 0,1,2 etc. * @param rowIndex - Index of row 0,1,2 etc. * @param colIndex - Index of col 0,1,2 etc. * *///from w ww. j a v a 2 s .c om private String readExcelCol(int sheetIndex, int rowIndex, int colIndex) { String cellContents = ""; try { FileInputStream fileInputStream = new FileInputStream(this.fileName); POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream); HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem); HSSFSheet sheet = workBook.getSheetAt(sheetIndex); Row row = sheet.getRow(rowIndex); Cell cell = row.getCell(colIndex); cellContents = cell.getStringCellValue(); } catch (IOException e) { // TODO Auto-generated catch block WmLog.printMessage("ERROR in reading =" + this.fileName + "Excel Sheet Index=" + sheetIndex + " Excel Row Index=" + rowIndex + "Excel Col Index=" + colIndex); e.printStackTrace(); return null; } return (cellContents); }
From source file:com.testmax.util.ExcelSheet.java
License:CDDL license
/** * This method is used to read the data's from an excel file. * @param sheetIndex - Index of sheet 0,1,2 etc. * @param rowIndex - Index of row 0,1,2 etc. * */// w w w .j a v a 2 s .co m private List<String> readExcelRow(int sheetIndex, int rowIndex) { String cellContents = ""; ArrayList<String> rowVal = new ArrayList<String>(); try { FileInputStream fileInputStream = new FileInputStream(this.fileName); POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream); HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem); HSSFSheet sheet = workBook.getSheetAt(sheetIndex); Row row = sheet.getRow(rowIndex); Iterator<Cell> colIt = row.cellIterator(); while (colIt.hasNext()) { Cell cell = colIt.next(); cellContents = cell.getStringCellValue(); rowVal.add(cellContents); } } catch (IOException e) { WmLog.printMessage("ERROR in reading Excel File=" + this.fileName + " Sheet Index=" + sheetIndex + " Excel Row Index=" + rowIndex + " " + e.getMessage()); // TODO Auto-generated catch block e.printStackTrace(); return null; } return (rowVal); }
From source file:com.testmax.util.ExcelSheet.java
License:CDDL license
/** * This method is used to read the data's from an excel file. * @param sheetIndex - Index of sheet 0,1,2 etc. * *//* w w w.j a v a 2 s . c om*/ private List<ArrayList> readExcel(int sheetIndex) { String cellContents = ""; ArrayList<ArrayList> excel = new ArrayList<ArrayList>(); ArrayList<String> rowVal = new ArrayList<String>(); try { FileInputStream fileInputStream = new FileInputStream(this.fileName); POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream); HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem); HSSFSheet sheet = workBook.getSheetAt(sheetIndex); Iterator<Row> rowIt = sheet.rowIterator(); while (rowIt.hasNext()) { Row row = rowIt.next(); Iterator<Cell> colIt = row.cellIterator(); while (colIt.hasNext()) { Cell cell = colIt.next(); cellContents = cell.getStringCellValue(); rowVal.add(cellContents); } excel.add(rowVal); } } catch (IOException e) { WmLog.printMessage("ERROR in reading Excel Sheet Index=" + sheetIndex + " Excel File=" + this.fileName + " " + e.getMessage()); // TODO Auto-generated catch block e.printStackTrace(); return null; } return (excel); }
From source file:com.tm.hiber.service.TMDataOperationServiceImpl.java
@Override public List<TaskMasterExcelTemplate> prepareTaskMasterFromExcel(File excelFile) { mLogger.log(Level.INFO, "prepareTaskMasterFromExcel--Starts"); if (excelFile != null && excelFile.exists()) { InputStream fis = null;/*from www.j a v a 2s .co m*/ try { mTaskMasterList.clear(); fis = new FileInputStream(excelFile); HSSFWorkbook objWorkBook = new HSSFWorkbook(fis); HSSFSheet objSheet = objWorkBook.getSheetAt(0); Iterator<Row> rowItr = objSheet.iterator(); TaskMasterExcelTemplate objTaskMaster; while (rowItr.hasNext()) { Row row = rowItr.next(); objTaskMaster = new TaskMasterExcelTemplate(); Iterator<Cell> cellItr = row.cellIterator(); while (cellItr.hasNext()) { Cell objCell = cellItr.next(); String cellValue = getCellData(objCell); switch (objCell.getColumnIndex()) { case 0: objTaskMaster.setTaskReference(cellValue); break; case 2: Date createDate = parseDate(cellValue); if (createDate != null) { objTaskMaster.setCreateDate(createDate); } break; case 6: objTaskMaster.setTitle(cellValue); break; case 7: objTaskMaster.setDescription(cellValue); break; case 8: objTaskMaster.setPriority(cellValue); break; case 18: objTaskMaster.setProjectName(cellValue); break; } } objTaskMaster.setAuditLastupdateon(new Date()); objTaskMaster.setAuditLastupdateby(UtilService.self().getSystemUser()); mTaskMasterList.add(objTaskMaster); } } catch (Exception ex) { mLogger.log(Level.FATAL, ex.getMessage()); } finally { try { if (fis != null) { fis.close(); } } catch (IOException ex) { mLogger.log(Level.FATAL, ex.getMessage()); } } } mLogger.log(Level.INFO, "prepareTaskMasterFromExcel--Ends"); return mTaskMasterList; }
From source file:com.util.tools.FeraExporter.java
public void postProcessXLS(Object document) { HSSFWorkbook wb = (HSSFWorkbook) document; HSSFSheet sheet = wb.getSheetAt(0); HSSFRow header = sheet.getRow(0);/* w ww . j a v a 2 s . c o m*/ HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.BLUE_GREY.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) { HSSFCell cell = header.getCell(i); cell.setCellStyle(cellStyle); } }
From source file:com.validation.manager.core.tool.table.extractor.TableExtractor.java
License:Apache License
public List<DefaultTableModel> extractTables() throws IOException, FileNotFoundException, ClassNotFoundException, VMException { List<DefaultTableModel> tables = new ArrayList<>(); if (source.getName().endsWith(".doc") || source.getName().endsWith(".docx") || source.getName().endsWith(".docm")) { //Word documents tables = loadSerializedTables(); } else if (source.getName().endsWith(".xls")) { //Pre Office 2007+ XML //Excel documents FileInputStream file = new FileInputStream(source); //Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(file); //Get first sheet from the workbook Sheet sheet = workbook.getSheetAt(0); //Get iterator to all the rows in current sheet Iterator<Row> rowIterator = sheet.iterator(); int rowNum = 0; int columns = 0; Map<Integer, ArrayList<Object>> data = new HashMap<>(); while (rowIterator.hasNext()) { ArrayList<Object> cells = new ArrayList<>(); Row row = rowIterator.next(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); cells.add(cell.getStringCellValue().trim()); if (rowNum == 0) { columns++;/* w w w .j a v a2 s .c o m*/ } } data.put(rowNum, cells); rowNum++; } //Process Object[][] data2 = new Object[rowNum][columns]; String[] title = new String[columns]; for (int i = 0; i < columns; i++) { title[i] = format("Column {0}", i + 1); } int row = 0; int col = 0; for (int i = 0; i < rowNum; i++) { for (Object obj : data.get(row)) { LOG.log(Level.FINE, "r: {0} c: {1} v: {2}", new Object[] { row, col, obj }); data2[row][col] = obj; col++; } row++; col = 0; } tables.add(new DefaultTableModel(data2, title)); } else if (source.getName().endsWith(".xlsx") || source.getName().endsWith(".xlsm")) { //Office 2007+ XML FileInputStream file = new FileInputStream(source); //Get the workbook instance for XLS file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); //Get iterator to all the rows in current sheet Iterator<Row> rowIterator = sheet.iterator(); int rowNum = 0; int columns = 0; Map<Integer, ArrayList<Object>> data = new HashMap<>(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); ArrayList<Object> cells = new ArrayList<>(); Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); cells.add(cell.getStringCellValue().trim()); if (rowNum == 0) { columns++; } } data.put(rowNum, cells); rowNum++; } //Process Object[][] data2 = new Object[rowNum][columns]; String[] title = new String[columns]; for (int i = 0; i < columns; i++) { title[i] = format("Column {0}", i + 1); } int row = 0, col = 0; for (int i = 0; i < rowNum; i++) { for (Object obj : data.get(row)) { LOG.log(Level.FINE, "r: {0} c: {1} v: {2}", new Object[] { row, col, obj }); data2[row][col] = obj; col++; } row++; col = 0; } tables.add(new DefaultTableModel(data2, title)); } else { throw new VMException(format("Invalid import file: {0}", source)); } return tables; }
From source file:com.verticon.treatment.poi.handlers.PoiUtils.java
License:Open Source License
static HSSFSheet getWorkSheet(File f) { FileInputStream fin = null;//from ww w . j av a 2s .c o m HSSFSheet s = null; try { // create a new file input stream with the input file specified // at the command line fin = new FileInputStream(f); HSSFWorkbook w = new HSSFWorkbook(fin); s = w.getSheetAt(0); } catch (Exception e) { e.printStackTrace(); } finally { // once all the events are processed close our file input stream if (fin != null) { try { fin.close(); } catch (IOException e) { } } } return s; }
From source file:com.viettel.hqmc.DAO.ReportDAO.java
public void reportStaffOnRequest() { try {// w w w . j a v a2s . c om String templateFile = "/WEB-INF/reportTemplate/reportStaffOnRequest.xls"; List<FilesNoClob> data; ConcurrentHashMap bean = new ConcurrentHashMap(); String sql; SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy"); String header = ""; List lstParam = new ArrayList(); int check = 0; // co so cong bo, ngay ky, nguoi ky if (searchForm.getSignerNameCheck() != null || searchForm.getAnnouncementNoCheck() != null || searchForm.getApproveDateFrom() != null || searchForm.getApproveDateTo() != null) { sql = "select distinct f.file_code,f.send_date,f.business_name,f.business_licence, f.product_type_name,f.nation_name,arp.SIGN_DATE,b.business_province\n" + ",f.name_staff_process,f.file_type_name,f.display_status,b.business_address,arp.RECEIPT_NO,f.product_name,f.MANUFACTURE_NAME,arp.SIGNER_NAME " + "from Files f, Process p, Detail_Product d, Business b , Announcement_Receipt_Paper arp \n" + "where f.detail_Product_Id = d.detail_Product_Id and f.file_Id = p.object_Id and f.dept_Id = b.business_Id \n" + "and f.is_Active = 1 and p.receive_Group_Id = 3103 and (f.is_Temp is null or f.is_Temp = 0 ) and f.announcement_Receipt_Paper_Id = arp.announcement_Receipt_Paper_Id \n"; if (searchForm.getApproveDateFrom() != null) { sql += " and arp.sign_date >= to_date( ? ,'dd/MM/yyyy hh24:mi:ss') "; String param = "" + DateTimeUtils.convertDateToString(searchForm.getApproveDateFrom(), "dd/MM/yyyy") + " 00:00:00"; lstParam.add(param); } if (searchForm.getApproveDateTo() != null) { sql += " and arp.sign_date <= to_date( ?,'dd/MM/yyyy hh24:mi:ss') "; String param = "" + DateTimeUtils.convertDateToString(searchForm.getApproveDateTo(), "dd/MM/yyyy") + " 23:59:59"; lstParam.add(param); } if (searchForm.getSendDateFrom() != null) { sql += " and f.send_date >= to_date( ? ,'dd/MM/yyyy hh24:mi:ss') "; String param = "" + DateTimeUtils.convertDateToString(searchForm.getSendDateFrom(), "dd/MM/yyyy") + " 00:00:00"; lstParam.add(param); } if (searchForm.getSendDateTo() != null) { sql += " and f.send_date <= to_date( ?,'dd/MM/yyyy hh24:mi:ss') "; String param = "" + DateTimeUtils.convertDateToString(searchForm.getSendDateTo(), "dd/MM/yyyy") + " 23:59:59"; lstParam.add(param); } if (searchForm.getIs30() != null && searchForm.getIs30() == 1) { sql += " and f.is_30 = 1 "; } // hieptq update 190515 //fileCode if (searchForm.getFileCode() != null && searchForm.getFileCode().length() > 0) { sql += " and lower(f.file_code) like ? ESCAPE '/'"; lstParam.add(StringUtils.toLikeString(searchForm.getFileCode().toLowerCase().trim())); } //fileType if (searchForm.getFileType() != -1l) { sql += " and f.file_type = ? "; lstParam.add(searchForm.getFileType()); } //tinh thanh pho if (searchForm.getBusinessProvinceId() != -1l) { sql += " and b.business_province_id = ? "; lstParam.add(searchForm.getBusinessProvinceId()); } //trang thai if (searchForm.getStatus() != -1l) { sql += " and f.status = ? "; lstParam.add(searchForm.getStatus()); } // businessName if (searchForm.getBusinessName() != null && searchForm.getBusinessName().length() > 0) { sql += " and lower(f.dept_name) like ? ESCAPE '/' "; lstParam.add(StringUtils.toLikeString(searchForm.getBusinessName().toLowerCase().trim())); } //bus address if (searchForm.getBusinessAddress() != null && searchForm.getBusinessAddress().length() > 0) { sql += " and lower(b.business_address) like ? ESCAPE '/' "; lstParam.add(StringUtils.toLikeString(searchForm.getBusinessAddress().toLowerCase().trim())); } // bus licence if (searchForm.getBusinessLicence() != null && searchForm.getBusinessLicence().length() > 0) { sql += " and lower(b.business_license) like ? ESCAPE '/' "; lstParam.add(StringUtils.toLikeString(searchForm.getBusinessLicence().toLowerCase().trim())); } //annoucementNo if (searchForm.getAnnoucementNo() != null && searchForm.getAnnoucementNo().length() > 0) { sql += " and lower(arp.receipt_no) like ? ESCAPE '/' "; lstParam.add(StringUtils.toLikeString(searchForm.getAnnoucementNo().toLowerCase().trim())); } // productTypeId if (searchForm.getProductTypeId() != -1l) { sql += " and f.product_type_id = ? "; lstParam.add(searchForm.getProductTypeId()); } // productName if (searchForm.getProductName() != null && searchForm.getProductName().length() > 0) { sql += " and lower(f.product_name) like ? ESCAPE '/' "; lstParam.add(StringUtils.toLikeString(searchForm.getProductName().toLowerCase().trim())); } // nationName if (searchForm.getNationName() != null && searchForm.getNationName().length() > 0) { sql += " and lower(f.nation_name) like ? ESCAPE '/' "; lstParam.add(StringUtils.toLikeString(searchForm.getNationName().toLowerCase().trim())); } //manufactureName if (searchForm.getManufactureName() != null && searchForm.getManufactureName().length() > 0) { sql += " and lower(f.manufacture_name) like ? ESCAPE '/' "; lstParam.add(StringUtils.toLikeString(searchForm.getManufactureName().toLowerCase().trim())); } //signerName if (searchForm.getSignerName() != null && searchForm.getSignerName().length() > 0) { sql += " and lower(arp.signer_name) like ? ESCAPE '/' "; lstParam.add(StringUtils.toLikeString(searchForm.getSignerName().toLowerCase().trim())); } //nameStaffProcess if (searchForm.getNameStaffProcess() != null && searchForm.getNameStaffProcess().length() > 0) { sql += " and lower(f.name_staff_process) like ? ESCAPE '/' "; lstParam.add(StringUtils.toLikeString(searchForm.getNameStaffProcess().toLowerCase().trim())); } sql += " order by f.send_Date ASC"; } // khong co else { sql = "select distinct f.file_code,f.send_date,f.business_name,f.business_licence, f.product_type_name,f.nation_name,b.business_province\n" + ",f.name_staff_process,f.file_type_name,f.display_status,b.business_address,f.product_name,f.MANUFACTURE_NAME from Files f, Process p, Detail_Product d, Business b where 1=1 \n" + "and f.file_Id = p.object_Id and f.detail_Product_Id = d.detail_Product_Id and f.dept_Id = b.business_Id \n" + "and f.is_Active = 1 and f.file_Id = p.object_Id and (f.is_Temp is null or f.is_Temp = 0 )\n" + "and p.receive_Group_Id = 3103"; if (searchForm.getSendDateFrom() != null) { sql += " and f.send_date >= to_date( ? ,'dd/MM/yyyy hh24:mi:ss') "; String param = "" + DateTimeUtils.convertDateToString(searchForm.getSendDateFrom(), "dd/MM/yyyy") + " 00:00:00"; lstParam.add(param); } if (searchForm.getSendDateTo() != null) { sql += " and f.send_date <= to_date( ?,'dd/MM/yyyy hh24:mi:ss') "; String param = "" + DateTimeUtils.convertDateToString(searchForm.getSendDateTo(), "dd/MM/yyyy") + " 23:59:59"; lstParam.add(param); } if (searchForm.getIs30() != null && searchForm.getIs30() == 1) { sql += " and f.is_30 = 1 "; } //fileCode if (searchForm.getFileCode() != null && searchForm.getFileCode().length() > 0) { sql += " and lower(f.file_code) like ? ESCAPE '/'"; lstParam.add(StringUtils.toLikeString(searchForm.getFileCode().toLowerCase().trim())); } //fileType if (searchForm.getFileType() != -1l) { sql += " and f.file_type = ? "; lstParam.add(searchForm.getFileType()); } //tinh thanh pho if (searchForm.getBusinessProvinceId() != -1l) { sql += " and b.business_province_id = ? "; lstParam.add(searchForm.getBusinessProvinceId()); } //trang thai if (searchForm.getStatus() != -1l) { sql += " and f.status = ? "; lstParam.add(searchForm.getStatus()); } // businessName if (searchForm.getBusinessName() != null && searchForm.getBusinessName().length() > 0) { sql += " and lower(f.dept_name) like ? ESCAPE '/' "; lstParam.add(StringUtils.toLikeString(searchForm.getBusinessName().toLowerCase().trim())); } //bus address if (searchForm.getBusinessAddress() != null && searchForm.getBusinessAddress().length() > 0) { sql += " and lower(b.business_address) like ? ESCAPE '/' "; lstParam.add(StringUtils.toLikeString(searchForm.getBusinessAddress().toLowerCase().trim())); } // bus licence if (searchForm.getBusinessLicence() != null && searchForm.getBusinessLicence().length() > 0) { sql += " and lower(b.business_license) like ? ESCAPE '/' "; lstParam.add(StringUtils.toLikeString(searchForm.getBusinessLicence().toLowerCase().trim())); } //annoucementNo if (searchForm.getAnnoucementNo() != null && searchForm.getAnnoucementNo().length() > 0) { sql += " and lower(arp.receipt_no) like ? ESCAPE '/' "; lstParam.add(StringUtils.toLikeString(searchForm.getAnnoucementNo().toLowerCase().trim())); } // productTypeId if (searchForm.getProductTypeId() != -1l) { sql += " and f.product_type_id = ? "; lstParam.add(searchForm.getProductTypeId()); } // productName if (searchForm.getProductName() != null && searchForm.getProductName().length() > 0) { sql += " and lower(f.product_name) like ? ESCAPE '/' "; lstParam.add(StringUtils.toLikeString(searchForm.getProductName().toLowerCase().trim())); } // nationName if (searchForm.getNationName() != null && searchForm.getNationName().length() > 0) { sql += " and lower(f.nation_name) like ? ESCAPE '/' "; lstParam.add(StringUtils.toLikeString(searchForm.getNationName().toLowerCase().trim())); } //manufactureName if (searchForm.getManufactureName() != null && searchForm.getManufactureName().length() > 0) { sql += " and lower(f.manufacture_name) like ? ESCAPE '/' "; lstParam.add(StringUtils.toLikeString(searchForm.getManufactureName().toLowerCase().trim())); } //signerName if (searchForm.getSignerName() != null && searchForm.getSignerName().length() > 0) { sql += " and lower(arp.signer_name) like ? ESCAPE '/' "; lstParam.add(StringUtils.toLikeString(searchForm.getSignerName().toLowerCase().trim())); } //nameStaffProcess if (searchForm.getNameStaffProcess() != null && searchForm.getNameStaffProcess().length() > 0) { sql += " and lower(f.name_staff_process) like ? ESCAPE '/' "; lstParam.add(StringUtils.toLikeString(searchForm.getNameStaffProcess().toLowerCase().trim())); } sql += " order by f.send_Date ASC"; check = 1; } SQLQuery query = (SQLQuery) getSession().createSQLQuery(sql); for (int i = 0; i < lstParam.size(); i++) { query.setParameter(i, lstParam.get(i)); } List lstResult = query.list(); FilesForm item = new FilesForm(); List result = new ArrayList<FilesForm>(); if (lstResult != null && lstResult.size() > 0) { for (int i = 0; i < lstResult.size(); i++) { Object[] row = (Object[]) lstResult.get(i); if (row.length > 0) { if (check == 1) { //fileCode if (row[0] != null && !"".equals(row[0])) { String fileCode = row[0].toString(); item.setFileCode(fileCode); } //sendDate if (row[1] != null && !"".equals(row[1])) { String sendDate = row[1].toString(); item.setSendDateNew(sendDate); } //businessName if (row[2] != null && !"".equals(row[2])) { String businessName = row[2].toString(); item.setBusinessName(businessName); } //buslicense if (row[3] != null && !"".equals(row[3])) { String businessLicense = row[3].toString(); item.setBusinessLicence(businessLicense); } //productTypeName if (row[4] != null && !"".equals(row[4])) { String productTypeName = row[4].toString(); item.setProductTypeName(productTypeName); } //nationName if (row[5] != null && !"".equals(row[5])) { String nationName = row[5].toString(); item.setNationName(nationName); } //bus province if (row[6] != null && !"".equals(row[6])) { String businessProvince = row[6].toString(); item.setBusinessProvince(businessProvince); } // name staff process if (row[7] != null && !"".equals(row[7])) { String nameStaftProcess = row[7].toString(); item.setNameStaffProcess(nameStaftProcess); } // fileTypeName if (row[8] != null && !"".equals(row[8])) { String fileTypeName = row[8].toString(); item.setFileTypeName(fileTypeName); } //display status if (row[9] != null && !"".equals(row[9])) { String displayStatus = row[9].toString(); item.setDisplayStatus(displayStatus); } //businessAddress if (row[10] != null && !"".equals(row[10])) { String businessAddress = row[10].toString(); item.setBusinessAddress(businessAddress); } // productName if (row[11] != null && !"".equals(row[11])) { String productName = row[11].toString(); item.setProductName(productName); } // manufactureName if (row[12] != null && !"".equals(row[12])) { String manufactureName = row[12].toString(); item.setManufactureName(manufactureName); } } else { //fileCode if (row[0] != null && !"".equals(row[0])) { String fileCode = row[0].toString(); item.setFileCode(fileCode); } //sendDate if (row[1] != null && !"".equals(row[1])) { String sendDate = row[1].toString(); item.setSendDateNew(sendDate); } //businessName if (row[2] != null && !"".equals(row[2])) { String businessName = row[2].toString(); item.setBusinessName(businessName); } //buslicense if (row[3] != null && !"".equals(row[3])) { String businessLicense = row[3].toString(); item.setBusinessLicence(businessLicense); } //productTypeName if (row[4] != null && !"".equals(row[4])) { String productTypeName = row[4].toString(); item.setProductTypeName(productTypeName); } //nationName if (row[5] != null && !"".equals(row[5])) { String nationName = row[5].toString(); item.setNationName(nationName); } //signDateNew if (row[6] != null && !"".equals(row[6])) { String signDateNew = row[6].toString(); item.setSignDateNew(signDateNew); } //bus province if (row[7] != null && !"".equals(row[7])) { String businessProvince = row[7].toString(); item.setBusinessProvince(businessProvince); } // name staff process if (row[8] != null && !"".equals(row[8])) { String nameStaftProcess = row[8].toString(); item.setNameStaffProcess(nameStaftProcess); } // fileTypeName if (row[9] != null && !"".equals(row[9])) { String fileTypeName = row[9].toString(); item.setFileTypeName(fileTypeName); } //display status if (row[10] != null && !"".equals(row[10])) { String displayStatus = row[10].toString(); item.setDisplayStatus(displayStatus); } //businessAddress if (row[11] != null && !"".equals(row[11])) { String businessAddress = row[11].toString(); item.setBusinessAddress(businessAddress); } // receiptNo if (row[12] != null && !"".equals(row[12])) { String receiptNo = row[12].toString(); item.setReceiptNo(receiptNo); } // productName if (row[13] != null && !"".equals(row[13])) { String productName = row[13].toString(); item.setProductName(productName); } // manufactureName if (row[14] != null && !"".equals(row[14])) { String manufactureName = row[14].toString(); item.setManufactureName(manufactureName); } //signer if (row[15] != null && !"".equals(row[15])) { String signerName = row[15].toString(); item.setSignerName(signerName); } } // index // if (row[16] != null && !"".equals(row[16])) { // Long index = Long.parseLong(row[16].toString()); // item.setIndex(index); // } } result.add(item); item = new FilesForm(); } } data = result; if (data == null) { data = new ArrayList<FilesNoClob>(); } bean.put("header", header); bean.put("data", data); bean.put("dateFormat", dateFormat); DateTimeUtils dateUtil = new DateTimeUtils(); bean.put("ConvertTime", dateUtil); String fileTemp = ReportUtil.exportReportSaveFileTemp(getRequest(), bean, templateFile); InputStream myxls = new FileInputStream(fileTemp);//get file excel Date newDate = new Date(); //fix sonar // ResourceBundle rb = ResourceBundle.getBundle("config"); // String filePath = rb.getString("report_excel_temp"); // String fullFilePath = filePath + "report_" + newDate.getTime() + ".xls"; // File file = new File(fullFilePath); // FileOutputStream fop = new FileOutputStream(file);; HSSFWorkbook wb = new HSSFWorkbook(myxls); HSSFSheet sheet = wb.getSheetAt(0); // check hien thi cot if (searchForm.getSignerNameCheck() != null || searchForm.getAnnouncementNoCheck() != null || searchForm.getApproveDateFrom() != null || searchForm.getApproveDateTo() != null) { sheet.setColumnHidden((short) 0, true); if (searchForm.getFileCodeCheck() == null) { sheet.setColumnHidden((short) 1, true); } if (searchForm.getSendDateFrom() == null && searchForm.getSendDateTo() == null) { sheet.setColumnHidden((short) 2, true); } if (searchForm.getBusinessNameCheck() == null) { sheet.setColumnHidden((short) 3, true); } if (searchForm.getBusinessLicenceCheck() == null) { sheet.setColumnHidden((short) 4, true); } if (searchForm.getProductTypeNameCheck() == null) { sheet.setColumnHidden((short) 5, true); } if (searchForm.getNationNameCheck() == null) { sheet.setColumnHidden((short) 6, true); } if (searchForm.getApproveDateFrom() == null && searchForm.getApproveDateTo() == null) { sheet.setColumnHidden((short) 7, true); } if (searchForm.getBusinessProvinceCheck() == null) { sheet.setColumnHidden((short) 8, true); } if (searchForm.getNameStaffProcessCheck() == null) { sheet.setColumnHidden((short) 9, true); } if (searchForm.getFileTypeNameCheck() == null) { sheet.setColumnHidden((short) 10, true); } if (searchForm.getDisplayStatusCheck() == null) { sheet.setColumnHidden((short) 11, true); } if (searchForm.getBusinessAddressCheck() == null) { sheet.setColumnHidden((short) 12, true); } if (searchForm.getAnnouncementNoCheck() == null) { sheet.setColumnHidden((short) 13, true); } if (searchForm.getProductNameCheck() == null) { sheet.setColumnHidden((short) 14, true); } if (searchForm.getManufactureNameCheck() == null) { sheet.setColumnHidden((short) 15, true); } if (searchForm.getSignerNameCheck() == null) { sheet.setColumnHidden((short) 16, true); } } else { sheet.setColumnHidden((short) 0, true); sheet.setColumnHidden((short) 7, true); sheet.setColumnHidden((short) 13, true); sheet.setColumnHidden((short) 16, true); if (searchForm.getFileCodeCheck() == null) { sheet.setColumnHidden((short) 1, true); } if (searchForm.getSendDateFrom() == null && searchForm.getSendDateTo() == null) { sheet.setColumnHidden((short) 2, true); } if (searchForm.getBusinessNameCheck() == null) { sheet.setColumnHidden((short) 3, true); } if (searchForm.getBusinessLicenceCheck() == null) { sheet.setColumnHidden((short) 4, true); } if (searchForm.getProductTypeNameCheck() == null) { sheet.setColumnHidden((short) 5, true); } if (searchForm.getNationNameCheck() == null) { sheet.setColumnHidden((short) 6, true); } if (searchForm.getBusinessProvinceCheck() == null) { sheet.setColumnHidden((short) 8, true); } if (searchForm.getNameStaffProcessCheck() == null) { sheet.setColumnHidden((short) 9, true); } if (searchForm.getFileTypeNameCheck() == null) { sheet.setColumnHidden((short) 10, true); } if (searchForm.getDisplayStatusCheck() == null) { sheet.setColumnHidden((short) 11, true); } if (searchForm.getBusinessAddressCheck() == null) { sheet.setColumnHidden((short) 12, true); } if (searchForm.getProductNameCheck() == null) { sheet.setColumnHidden((short) 14, true); } if (searchForm.getManufactureNameCheck() == null) { sheet.setColumnHidden((short) 15, true); } } HttpServletResponse res = getResponse(); res.setContentType("application/vnd.ms-excel"); res.setHeader("Content-Disposition", "attachment; filename=report_" + newDate.getTime() + ".xls"); res.setHeader("Content-Type", "application/vnd.ms-excel"); wb.write(res.getOutputStream()); res.getOutputStream().flush(); //fop.close(); } catch (Exception ex) { LogUtil.addLog(ex);//binhnt sonar a160901 // log.error(e); } }
From source file:com.viettel.vsaadmin.database.DAO.UsersDAO.java
License:Open Source License
public String importStaffFromExcel() throws Exception { List customInfo = new ArrayList();//lst users Long attachId = Long.parseLong(getRequest().getParameter("attachId"));//get attactId VoAttachs att = (VoAttachs) getSession().get("com.viettel.voffice.database.BO.VoAttachs", attachId);//Attachs BO if (att == null) {// if att null return error users customInfo.add("error"); } else {/* ww w .j a va2s . c o m*/ ResourceBundle rb = ResourceBundle.getBundle("config");//get link tuong doi String dir = rb.getString("directory"); String linkFile = att.getAttachPath(); linkFile = dir + linkFile; InputStream myxls = new FileInputStream(linkFile);//get file excel //Get the workbook instance for XLS file HSSFWorkbook wb = new HSSFWorkbook(myxls); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row; // HSSFRow firstRow = sheet.getRow(1); int rowNums = sheet.getLastRowNum(); // UsersDAOHE sdhe = new UsersDAOHE(); SimpleDateFormat formatter = new SimpleDateFormat("dd/mm/yyyy"); String userError = ""; for (int i = 1; i <= rowNums; i++) { try { row = sheet.getRow(i); if (row != null) { Users entity = new Users(); HSSFCell cellUserName = row.getCell((short) 0); HSSFCell cellFullName = row.getCell((short) 1); HSSFCell cellEmail = row.getCell((short) 2); HSSFCell cellCellPhone = row.getCell((short) 3); HSSFCell cellDeptName = row.getCell((short) 4); HSSFCell cellBusiness = row.getCell((short) 5); HSSFCell cellPosition = row.getCell((short) 6); HSSFCell cellGender = row.getCell((short) 7); HSSFCell cellTelephone = row.getCell((short) 8); HSSFCell cellFax = row.getCell((short) 9); HSSFCell cellDateOfBirth = row.getCell((short) 10); HSSFCell cellBirthPlace = row.getCell((short) 11); HSSFCell cellStaffCode = row.getCell((short) 12); HSSFCell cellIdentityCard = row.getCell((short) 13); HSSFCell cellIssueDateIdent = row.getCell((short) 14); HSSFCell cellIssuePlaceIdent = row.getCell((short) 15); HSSFCell cellDescription = row.getCell((short) 16); //validate input if (cellUserName != null) { entity.setUserName(cellUserName.toString()); } else { userError += i + " li Ti khon,"; customInfo.add(userError); } if (cellFullName != null) { entity.setFullName(cellFullName.toString()); } else { userError += i + " li Tn y ,"; customInfo.add(userError); } if (cellEmail.toString() != null && cellEmail.toString().length() > 0) { entity.setEmail(cellEmail.toString()); } if (cellCellPhone.toString() != null && cellCellPhone.toString().length() > 0) { entity.setCellphone(cellCellPhone.toString()); } //get dept DepartmentDAOHE deptdhe = new DepartmentDAOHE(); Department deptBo = deptdhe.findByName(cellDeptName.toString()); if (deptBo != null) { entity.setDeptName(deptBo.getDeptName()); entity.setDeptId(deptBo.getDeptId()); } //get business BusinessDAOHE busdhe = new BusinessDAOHE(); Business busbo = busdhe.findByName(cellBusiness.toString()); if (busbo != null) { entity.setBusinessId(busbo.getBusinessId()); entity.setBusinessName(busbo.getBusinessName()); } //get posId PositionDAOHE posdhe = new PositionDAOHE(); Position pos = posdhe.findByName(cellPosition.toString()); if (pos != null) { entity.setPosId(pos.getPosId()); } else { userError += i + " li Chc v,"; customInfo.add(userError); } if (cellTelephone != null) { entity.setTelephone(cellTelephone.toString()); } if (cellFax != null) { entity.setFax(cellFax.toString()); } if (cellBirthPlace != null) { entity.setBirthPlace(cellBirthPlace.toString()); } if (cellStaffCode != null) { entity.setStaffCode(cellStaffCode.toString()); } if (cellIdentityCard != null) { entity.setIdentityCard(cellIdentityCard.toString()); } if (cellIssuePlaceIdent != null) { entity.setIssuePlaceIdent(cellIssuePlaceIdent.toString()); } if (cellIssueDateIdent != null && cellIssueDateIdent.toString().length() > 0) { entity.setIssueDateIdent(formatter.parse(cellIssueDateIdent.toString())); } if (cellDateOfBirth != null) { entity.setDateOfBirth(formatter.parse(cellDateOfBirth.toString())); } if (cellDescription != null) { entity.setDescription(cellDescription.toString()); } // end validate input String passwordEncrypt = PasswordService.getInstance().encrypt("Attp@123"); entity.setPassword(passwordEncrypt); entity.setPasswordchanged(0L); entity.setStatus(1L); // entity.setDeptId(Long.parseLong(cellDeptId.toString())); // entity.setPosId(Long.parseLong(cellPosId.toString())); // entity.setStatus(Long.parseLong(cellStatus.toString())); // entity.setGender(Long.parseLong(cellGender.toString())); String gender; if (cellGender == null) { userError += i + " li Gii tnh,"; customInfo.add(userError); } else { gender = cellGender.toString().trim().toLowerCase(); if (gender.contains("Nam") || gender.contains("man") || gender.contains("male")) { entity.setGender(0L); } else { entity.setGender(1L); } } if (entity != null) { getSession().saveOrUpdate(entity); RoleUser roleUser = new RoleUser(); roleUser.setIsActive(1L); roleUser.setIsAdmin(0L); roleUser.setUserId(entity.getUserId()); roleUser.setRoleId(323L); roleUser.setRoleUserPK(new RoleUserPK(322, entity.getUserId())); getSession().saveOrUpdate(roleUser); customInfo.add("success"); } else { userError += i + ","; customInfo.add(userError); } } // end if row != null } // end if att != null catch (Exception ex) { LogUtil.addLog(ex);//binhnt sonar a160901 userError += i + ","; customInfo.add(userError); // jsonDataGrid.setCustomInfo(customInfo); // return "gridData"; } } // end loop } this.jsonDataGrid.setCustomInfo(customInfo); return "gridData"; }