List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet setColumnHidden
@Override public void setColumnHidden(int columnIndex, boolean hidden)
From source file:com.assentisk.controller.OrganizationController.java
private ModelAndView importEmploye(HttpServletRequest req, HttpServletResponse res) throws Exception { System.out.println("hiiii heree......."); MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) req; MultipartFile multipartFile = multipartRequest.getFile("myexcel"); String fileName = multipartFile.getOriginalFilename(); map = new ModelMap(); String name = req.getParameter("fileName"); int status = 0; java.util.Date dt = new java.util.Date(); java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String currentTime = sdf.format(dt); InetAddress ip = InetAddress.getLocalHost(); String userId = (String) req.getSession().getAttribute("UserRecordID"); byte[] b = multipartFile.getBytes(); FileOutputStream fout = new FileOutputStream(new File(fileName)); fout.write(b);/*from w w w. jav a2s . c om*/ fout.flush(); fout.close(); try { String DepartmentID = "", LocationID = "", EmpName = "", Email1 = "", Email2 = "", address = ""; String city = "", zip = "", phone1 = "", locCoun = "", locstate = "", loccity = "", locContact = "", locEmail = ""; String phone2 = "", divisions = "", email2 = "", username = "", reporting = "", photo = ""; int isLicense = 0; int isactive = 1; int count = 0; int notcount = 0; FileInputStream file = new FileInputStream(new File(fileName)); boolean flag = false; String[] data = null; List<String> dataStatus = new ArrayList<String>(); int val = 0; //Reading .xls files if (fileName.toLowerCase().endsWith(".xls")) { // Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(file); // Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); sheet.setColumnHidden((short) 14, false); // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.rowIterator(); if (rowIterator.hasNext()) { while (rowIterator.hasNext()) { short cellIndex = 0; HSSFRow hsrow = (HSSFRow) rowIterator.next(); if (hsrow.getRowNum() != 0) { if (hsrow instanceof HSSFRow) { try { EmpName = hsrow.getCell((short) 0).getStringCellValue(); } catch (Exception e) { EmpName = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": EmpName - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": EmpName - " + e.getMessage()); val++; } try { DepartmentID = hsrow.getCell((short) 1).getStringCellValue(); } catch (Exception e) { DepartmentID = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": Department - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Department - " + e.getMessage()); val++; } try { LocationID = hsrow.getCell((short) 2).getStringCellValue(); } catch (Exception e) { LocationID = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": Location -" + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location - " + e.getMessage()); val++; } try { locCoun = hsrow.getCell((short) 3).getStringCellValue(); } catch (Exception e) { locCoun = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": location Country - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Country- " + e.getMessage()); val++; } try { locstate = hsrow.getCell((short) 4).getStringCellValue(); } catch (Exception e) { locstate = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": location State - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location State- " + e.getMessage()); val++; } try { loccity = hsrow.getCell((short) 5).getStringCellValue(); } catch (Exception e) { loccity = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": location City -" + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location City- " + e.getMessage()); val++; } try { locContact = hsrow.getCell((short) 6).getStringCellValue(); } catch (Exception e) { locContact = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": location Contact Name -" + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Contact Name- " + e.getMessage()); val++; } try { locEmail = hsrow.getCell((short) 7).getStringCellValue(); boolean isValid = false; try { // // Create InternetAddress object and validated the supplied // address which is this case is an email address. InternetAddress internetAddress = new InternetAddress(locEmail); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Email- " + e.getMessage()); } } catch (Exception e) { locEmail = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": location Email -" + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Email- " + e.getMessage()); val++; } try { divisions = hsrow.getCell((short) 8).getStringCellValue(); } catch (Exception e) { divisions = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": Divisions - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Divisions - " + e.getMessage()); val++; } try { address = hsrow.getCell((short) 9).getStringCellValue(); } catch (Exception e) { address = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": address - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": address - " + e.getMessage()); val++; } try { city = hsrow.getCell((short) 10).getStringCellValue(); } catch (Exception e) { city = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": city - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": city - " + e.getMessage()); val++; } try { zip = String.valueOf(hsrow.getCell((short) 11).getNumericCellValue()); zip = String.valueOf(zip).split("\\.")[0]; } catch (Exception e) { zip = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": zip - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": zip - " + e.getMessage()); val++; } try { DataFormatter formatter = new DataFormatter(); phone1 = String.valueOf(hsrow.getCell((short) 12).getNumericCellValue()); String df2 = formatter.formatCellValue(hsrow.getCell((short) 12)); phone1 = df2; phone1 = String.valueOf(phone1).split("\\.")[0]; } catch (Exception e) { phone1 = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": phone1 - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": phone1 - " + e.getMessage()); val++; } try { DataFormatter formatter = new DataFormatter(); phone2 = String.valueOf(hsrow.getCell((short) 13).getNumericCellValue()); String df2 = formatter.formatCellValue(hsrow.getCell((short) 13)); phone2 = df2; System.out.println("check phone1" + phone2); phone2 = String.valueOf(phone2).split("\\.")[0]; } catch (Exception e) { phone2 = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": phone2 -" + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": phone2 - " + e.getMessage()); val++; } try { boolean isValid = false; Email1 = hsrow.getCell((short) 14).getStringCellValue(); try { InternetAddress internetAddress = new InternetAddress(Email1); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Email1 - " + e.getMessage()); } } catch (Exception e) { Email1 = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": Email1 - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Email1 - " + e.getMessage()); val++; } try { boolean isValid = false; Email2 = hsrow.getCell((short) 15).getStringCellValue(); try { InternetAddress internetAddress = new InternetAddress(Email2); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Email2 - " + e.getMessage()); } } catch (Exception e) { Email2 = ""; System.out.println("Wrong Data in Row " + hsrow.getRowNum() + ": Email2 - " + e.getMessage()); dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Email2 - " + e.getMessage()); val++; } int userdata = 0; String groupdata = ""; if (username.equals("")) { userdata = 0; reporting = "0"; groupdata = "0"; } if (!LocationID.equals("")) { LocationID = organizationDao.getLocIdByName(LocationID, currentTime, Integer.parseInt(userId), locCoun, locstate, loccity, locContact, locEmail); if (LocationID.equals("0")) { LocationID = ""; } } if (!DepartmentID.equals("")) { DepartmentID = organizationDao.getDeparmentIdByName(DepartmentID, currentTime, Integer.parseInt(userId)); if (DepartmentID.equals("0")) { DepartmentID = ""; } } if (!divisions.equals("")) { divisions = organizationDao.getDivisionByName(divisions, currentTime, Integer.parseInt(userId)); if (divisions.equals("0")) { divisions = ""; } } if (!EmpName.equals("") && !DepartmentID.equals("") && !divisions.equals("") && !LocationID.equals("")) { String duplicate = ""; try { duplicate = organizationDao.saveEmployeedata(0, DepartmentID, LocationID, EmpName, Email1, reporting, groupdata, currentTime, currentTime, Integer.parseInt(userId), currentTime, Integer.parseInt(userId), status, address, city, "", "", zip, photo, phone1, phone2, divisions, Email2); masterDao.userAuditTrail("assentisk_employees", "1", "add", currentTime, Integer.parseInt(userId), ip.getHostAddress()); } catch (Exception e) { //dataStatus.add("Error on the data : "+hsrow.getRowNum()); req.setAttribute("message", "Invalid Data File"); return new ModelAndView("organization/employees", map); } if (duplicate.equals("true")) { notcount = notcount + 1; dataStatus.add("Duplicate Row : " + hsrow.getRowNum()); } else { count = count + 1; } } else { System.out.println( "Error Row : " + hsrow.getRowNum() + "Not Inserted Row : " + EmpName); notcount = notcount + 1; } } } } } else { dataStatus.add("Please import valid Data file"); } if (count > 0) { dataStatus.add("Succesfully inserted Row : " + count); val++; } if (notcount > 0) { if (notcount == 1) { dataStatus.add(notcount + " Row is not inserted"); } else { dataStatus.add(notcount + " Rows are not inserted"); } } } //Reading .xlsx files else if (fileName.toLowerCase().endsWith(".xlsx")) { // Get the workbook instance for XLS file XSSFWorkbook wBook = new XSSFWorkbook(file); // Get first sheet from the workbook XSSFSheet sheet = wBook.getSheetAt(0); sheet.setColumnHidden((short) 14, false); // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cite = row.cellIterator(); if (row.getRowNum() != 0) { while (cite.hasNext()) { Cell c = cite.next(); if (c.getColumnIndex() == 0) { try { EmpName = c.toString(); } catch (Exception e) { EmpName = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": EmpName - " + e.getMessage()); } } if (c.getColumnIndex() == 1) { try { DepartmentID = c.toString(); } catch (Exception e) { DepartmentID = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Department - " + e.getMessage()); } } if (c.getColumnIndex() == 2) { try { LocationID = c.toString(); } catch (Exception e) { LocationID = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Location -" + e.getMessage()); } } if (c.getColumnIndex() == 3) { try { locCoun = c.toString(); } catch (Exception e) { locCoun = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": location Country - " + e.getMessage()); } } if (c.getColumnIndex() == 4) { try { locstate = c.toString(); } catch (Exception e) { locstate = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": location State - " + e.getMessage()); } } if (c.getColumnIndex() == 5) { try { loccity = c.toString(); } catch (Exception e) { loccity = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": location City -" + e.getMessage()); } } if (c.getColumnIndex() == 6) { try { locContact = c.toString(); } catch (Exception e) { locContact = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": location Contact Name -" + e.getMessage()); } } if (c.getColumnIndex() == 7) { try { locEmail = c.toString(); boolean isValid = false; try { InternetAddress internetAddress = new InternetAddress(locEmail); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": locEmail - " + e.getMessage()); } } catch (Exception e) { locEmail = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": location Email -" + e.getMessage()); } } if (c.getColumnIndex() == 8) { try { divisions = c.toString(); } catch (Exception e) { divisions = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Divisions - " + e.getMessage()); } } if (c.getColumnIndex() == 9) { try { address = c.toString(); } catch (Exception e) { address = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": address - " + e.getMessage()); } } if (c.getColumnIndex() == 10) { try { city = c.toString(); } catch (Exception e) { city = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": city - " + e.getMessage()); } } if (c.getColumnIndex() == 11) { try { zip = c.toString(); } catch (Exception e) { zip = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": zip - " + e.getMessage()); } } if (c.getColumnIndex() == 12) { try { DataFormatter formatter = new DataFormatter(); phone1 = c.toString(); String df2 = formatter.formatCellValue(row.getCell((short) 12)); phone1 = df2; } catch (Exception e) { phone1 = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": phone1 - " + e.getMessage()); } } if (c.getColumnIndex() == 13) { try { DataFormatter formatter = new DataFormatter(); phone2 = c.toString(); String df2 = formatter.formatCellValue(row.getCell((short) 12)); phone2 = df2; } catch (Exception e) { phone2 = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": phone2 -" + e.getMessage()); } } if (c.getColumnIndex() == 3) { try { Email1 = c.toString(); boolean isValid = false; try { InternetAddress internetAddress = new InternetAddress(Email1); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Email1 - " + e.getMessage()); } } catch (Exception e) { Email1 = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Email1 - " + e.getMessage()); } } if (c.getColumnIndex() == 10) { try { Email2 = c.toString(); boolean isValid = false; try { InternetAddress internetAddress = new InternetAddress(Email2); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Email2 - " + e.getMessage()); } } catch (Exception e) { Email2 = ""; dataStatus.add("Wrong Data in Row " + rowIterator.hasNext() + ": Email2 - " + e.getMessage()); } } } int userdata = 0; String groupdata = ""; if (username.equals("")) { userdata = 0; reporting = "0"; groupdata = "0"; } if (!LocationID.equals("")) { LocationID = organizationDao.getLocIdByName(LocationID, currentTime, Integer.parseInt(userId), locCoun, locstate, loccity, locContact, locEmail); if (LocationID.equals("0")) { LocationID = ""; } } if (!DepartmentID.equals("")) { DepartmentID = organizationDao.getDeparmentIdByName(DepartmentID, currentTime, Integer.parseInt(userId)); if (DepartmentID.equals("0")) { DepartmentID = ""; } } if (!divisions.equals("")) { divisions = organizationDao.getDivisionByName(divisions, currentTime, Integer.parseInt(userId)); if (divisions.equals("0")) { divisions = ""; } } if (!EmpName.equals("") && !DepartmentID.equals("") && !divisions.equals("") && !LocationID.equals("")) { String duplicate = ""; try { duplicate = organizationDao.saveEmployeedata(0, DepartmentID, LocationID, EmpName, Email1, reporting, groupdata, currentTime, currentTime, Integer.parseInt(userId), currentTime, Integer.parseInt(userId), status, address, city, "", "", zip, photo, phone1, phone2, divisions, Email2); masterDao.userAuditTrail("assentisk_employees", "1", "add", currentTime, Integer.parseInt(userId), ip.getHostAddress()); } catch (Exception e) { //dataStatus.add("Error on the data : "+hsrow.getRowNum()); req.setAttribute("message", "Invalid Data File"); return new ModelAndView("organization/employees", map); } if (duplicate.equals("true")) { notcount = notcount + 1; dataStatus.add("Duplicate Row : " + row.getRowNum()); } else { count = count + 1; } } else { System.out.println( "Error Row : " + row.getRowNum() + "Not Inserted Row EmpName : " + EmpName); notcount = notcount + 1; } } } if (count > 0) { System.out.println("Succesfully inserted Row :" + count); dataStatus.add("Succesfully inserted Row : " + count); val++; } if (notcount > 0) { dataStatus.add(notcount + " Rows are not inserted"); } file.close(); File f = new File(fileName); if (f.exists()) { f.delete(); } } if (dataStatus.size() > 0) { map.addObject("dataStatus", dataStatus); map.addObject("datasize", "true"); } else { map.addObject("datasize", "false"); } file.close(); File f = new File(fileName); if (f.exists()) { f.delete(); } } catch (FileNotFoundException e) { e.printStackTrace(); req.setAttribute("message", "Invalid data file"); } catch (IOException e) { e.printStackTrace(); req.setAttribute("message", "Invalid data file"); } catch (Exception e) { e.printStackTrace(); req.setAttribute("message", "Invalid data file"); } return new ModelAndView("organization/employees", map); }
From source file:com.assentisk.controller.OrganizationController.java
private ModelAndView importAssets(HttpServletRequest req, HttpServletResponse res) throws Exception { MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) req; MultipartFile multipartFile = multipartRequest.getFile("myexcel"); String fileName = multipartFile.getOriginalFilename(); map = new ModelMap(); String name = req.getParameter("fileName"); int status = 0; java.util.Date dt = new java.util.Date(); java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String currentTime = sdf.format(dt); InetAddress ip = InetAddress.getLocalHost(); String userId = (String) req.getSession().getAttribute("UserRecordID"); byte[] b = multipartFile.getBytes(); FileOutputStream fout = new FileOutputStream(new File(fileName)); fout.write(b);//w w w . j av a2 s. c o m fout.flush(); fout.close(); try { String Assets = "", category = "", location = "", application = "", business = ""; String initial = "", quantity = "", country = "", state = "", city = "", contact = "", email = ""; List<String> dataStatus = new ArrayList<String>(); int count = 0; int notcount = 0; FileInputStream file = new FileInputStream(new File(fileName)); boolean flag = false; // Reading .xls files if (fileName.toLowerCase().endsWith(".xls")) { // Get the workbook instance for XLS file HSSFWorkbook workbook = new HSSFWorkbook(file); // Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); sheet.setColumnHidden((short) 14, false); // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.rowIterator(); if (rowIterator.hasNext()) { while (rowIterator.hasNext()) { short cellIndex = 0; HSSFRow hsrow = (HSSFRow) rowIterator.next(); System.out.println("check this...hsrow." + hsrow.getRowNum()); if (hsrow.getRowNum() != 0) { if (hsrow instanceof HSSFRow) { try { Assets = hsrow.getCell((short) 0).getStringCellValue(); } catch (Exception e) { Assets = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": AssetName - " + e.getMessage()); } } try { category = hsrow.getCell((short) 1).getStringCellValue(); } catch (Exception e) { category = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Category - " + e.getMessage()); } } try { application = hsrow.getCell((short) 2).getStringCellValue(); } catch (Exception e) { application = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Application - " + e.getMessage()); } } try { business = hsrow.getCell((short) 3).getStringCellValue(); } catch (Exception e) { business = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": BusinessProcess - " + e.getMessage()); } } try { initial = String.valueOf(hsrow.getCell((short) 4).getNumericCellValue()); initial = String.valueOf(initial).split("\\.")[0]; } catch (Exception e) { initial = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": InitialValue - " + e.getMessage()); } } try { quantity = String.valueOf(hsrow.getCell((short) 5).getNumericCellValue()); quantity = String.valueOf(quantity).split("\\.")[0]; } catch (Exception e) { quantity = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Quantity - " + e.getMessage()); } } try { location = hsrow.getCell((short) 6).getStringCellValue(); } catch (Exception e) { location = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location - " + e.getMessage()); } } try { country = hsrow.getCell((short) 7).getStringCellValue(); } catch (Exception e) { country = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Country - " + e.getMessage()); } } try { state = hsrow.getCell((short) 8).getStringCellValue(); } catch (Exception e) { state = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location State - " + e.getMessage()); } } try { city = hsrow.getCell((short) 9).getStringCellValue(); } catch (Exception e) { city = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location City - " + e.getMessage()); } } try { contact = hsrow.getCell((short) 10).getStringCellValue(); } catch (Exception e) { contact = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Contact Name - " + e.getMessage()); } } try { email = hsrow.getCell((short) 11).getStringCellValue(); boolean isValid = false; try { // // Create InternetAddress object and // validated the supplied // address which is this case is an email // address. InternetAddress internetAddress = new InternetAddress(email); internetAddress.validate(); isValid = true; } catch (AddressException e) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Email- " + e.getMessage()); } } catch (Exception e) { email = ""; System.out.println("Error Row: " + hsrow.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + hsrow.getRowNum() + ": Location Email - " + e.getMessage()); } } if (!location.equals("")) { location = organizationDao.getLocIdByName(location, currentTime, Integer.parseInt(userId), country, state, city, contact, email); } else { location = "0"; } if (!application.equals("")) { application = organizationDao.getAppByName(application, currentTime, Integer.parseInt(userId)); } else { application = "0"; } if (!business.equals("")) { business = organizationDao.getBusinessByName(business, currentTime, Integer.parseInt(userId)); } else { business = "0"; } if (!category.equals("")) { category = organizationDao.getCategoryByName(category, currentTime, Integer.parseInt(userId)); } else { category = ""; } if (!category.equals("") && !Assets.equals("")) { String duplicate = organizationDao.saveAssetData(Assets, category, location, application, business, "", currentTime, Integer.parseInt(userId), currentTime, Integer.parseInt(userId), "", "", initial, quantity); masterDao.userAuditTrail("assentisk_assets", "1", "Insert", currentTime, Integer.parseInt(userId), ip.getHostAddress()); if (duplicate.equals("true")) { notcount = notcount + 1; dataStatus.add("Duplicate Row : " + hsrow.getRowNum()); } else { count = count + 1; } } else { System.out.println("Error Row : " + hsrow.getRowNum() + "Not Inserted Row Asset Name : " + Assets); notcount = notcount + 1; } } } } } else { dataStatus.add("Please import valid Data file"); } if (count > 0) { dataStatus.add("Succesfully inserted Row : " + count); } if (notcount > 0) { if (notcount == 1) { dataStatus.add(notcount + " Row is not inserted"); } else { dataStatus.add(notcount + " Rows are not inserted"); } } } // Reading .xlsx files else if (fileName.toLowerCase().endsWith(".xlsx")) { // Get the workbook instance for XLS file XSSFWorkbook wBook = new XSSFWorkbook(file); // Get first sheet from the workbook XSSFSheet sheet = wBook.getSheetAt(0); sheet.setColumnHidden((short) 14, false); // Iterate through each rows from first sheet Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); Iterator<Cell> cite = row.cellIterator(); if (row.getRowNum() != 0) { while (cite.hasNext()) { Cell c = cite.next(); if (c.getColumnIndex() == 0) { try { Assets = c.toString(); } catch (Exception e) { Assets = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": AssetName - " + e.getMessage()); } } } if (c.getColumnIndex() == 1) { try { category = c.toString(); } catch (Exception e) { category = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Category - " + e.getMessage()); } } } if (c.getColumnIndex() == 2) { try { application = c.toString(); } catch (Exception e) { application = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Application - " + e.getMessage()); } } } if (c.getColumnIndex() == 3) { try { business = c.toString(); } catch (Exception e) { business = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": BusinessProcess - " + e.getMessage()); } } } if (c.getColumnIndex() == 4) { try { initial = c.toString(); } catch (Exception e) { initial = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": InitialValue - " + e.getMessage()); } } } if (c.getColumnIndex() == 5) { try { quantity = c.toString(); } catch (Exception e) { quantity = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Quantity - " + e.getMessage()); } } } if (c.getColumnIndex() == 6) { try { location = c.toString(); } catch (Exception e) { location = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location - " + e.getMessage()); } } } if (c.getColumnIndex() == 7) { try { country = c.toString(); } catch (Exception e) { country = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location Country - " + e.getMessage()); } } } if (c.getColumnIndex() == 8) { try { state = c.toString(); } catch (Exception e) { state = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location State - " + e.getMessage()); } } } if (c.getColumnIndex() == 9) { try { city = c.toString(); } catch (Exception e) { city = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location City - " + e.getMessage()); } } } if (c.getColumnIndex() == 10) { try { contact = c.toString(); } catch (Exception e) { contact = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location Contact Name - " + e.getMessage()); } } } if (c.getColumnIndex() == 11) { try { email = c.toString(); boolean isValid = false; try { // // Create InternetAddress object and // validated the supplied // address which is this case is an // email address. InternetAddress internetAddress = new InternetAddress(email); internetAddress.validate(); isValid = true; } catch (AddressException e) { if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location Email- " + e.getMessage()); } } } catch (Exception e) { email = ""; System.out.println("Error Row: " + row.getRowNum() + " " + e.getMessage()); if (e.getMessage() != null) { dataStatus.add("Wrong Data in Row " + row.getRowNum() + ": Location Email- " + e.getMessage()); } } } } if (!location.equals("")) { location = organizationDao.getLocIdByName(location, currentTime, Integer.parseInt(userId), country, state, city, contact, email); } else { location = "0"; } if (!application.equals("")) { application = organizationDao.getAppByName(application, currentTime, Integer.parseInt(userId)); } else { application = "0"; } if (!business.equals("")) { business = organizationDao.getBusinessByName(business, currentTime, Integer.parseInt(userId)); } else { business = "0"; } if (!category.equals("")) { category = organizationDao.getCategoryByName(category, currentTime, Integer.parseInt(userId)); } else { category = ""; } if (!category.equals("") && !Assets.equals("")) { String duplicate = organizationDao.saveAssetData(Assets, category, location, application, business, "", currentTime, Integer.parseInt(userId), currentTime, Integer.parseInt(userId), "", "", initial, quantity); masterDao.userAuditTrail("assentisk_assets", "1", "Insert", currentTime, Integer.parseInt(userId), ip.getHostAddress()); if (duplicate.equals("true")) { notcount = notcount + 1; dataStatus.add("Duplicate Row : " + row.getRowNum()); } else { count = count + 1; } } else { System.out.println( "Error Row : " + row.getRowNum() + "Not Inserted Row Asset Name : " + Assets); } } } if (count > 0) { System.out.println("Succesfully inserted Row :" + count); dataStatus.add("Succesfully inserted Row : " + count); } if (notcount > 0) { if (notcount == 1) { dataStatus.add(notcount + " Row is not inserted"); } else { dataStatus.add(notcount + " Rows are not inserted"); } } file.close(); File f = new File(fileName); if (f.exists()) { f.delete(); } } if (dataStatus.size() > 0) { map.addObject("dataStatus", dataStatus); map.addObject("datasize", "true"); } else { map.addObject("datasize", "false"); } file.close(); File f = new File(fileName); if (f.exists()) { f.delete(); } } catch (FileNotFoundException e) { e.printStackTrace(); req.setAttribute("message", "Invalid data file"); } catch (IOException e) { e.printStackTrace(); req.setAttribute("message", "Invalid data file"); } catch (Exception e) { e.printStackTrace(); req.setAttribute("message", "Invalid data file"); } return new ModelAndView("assests/assets", map); }
From source file:com.assentisk.util.FilesService.java
public void generatePositionProfileXls(HttpServletRequest req, String Doc_name, List catList) throws SQLException, IOException { Integer key = 1;/*w w w . jav a 2 s . com*/ try { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(Doc_name); Map<String, Object[]> data = new LinkedHashMap<String, Object[]>(); data.put(key.toString(), new Object[] { "Control ID", "Risk", "Control Objective", "Control Description", "Test Steps", "Busines Process Name", "Control Category Name" }); if (catList != null) { for (int i = 0; i < catList.size(); i++) { Map map = (Map) catList.get(i); short colIndex = 0; data.put((++key).toString(), new Object[] { map.get("Control_ID"), map.get("Risk"), map.get("Control_Objective"), map.get("Control_Description"), map.get("Test_Steps"), map.get("businessProcess"), map.get("controlcategory") }); Set<String> keyset = data.keySet(); int rownum = 0; sheet.setColumnHidden((short) 14, true); for (String key2 : keyset) { HSSFRow row = sheet.createRow(rownum++); Object[] objArr = data.get(key2); short cellnum = 0; for (Object obj : objArr) { if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof Integer) row.createCell(cellnum++).setCellValue((Integer) obj); else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((Integer) obj); else if (obj instanceof Integer) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); } } } } FileOutputStream out = new FileOutputStream(new File("" + Doc_name + ".xls")); workbook.write(out); out.close(); System.out.println("Excel written successfully.."); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } catch (Exception ex) { ex.printStackTrace(); } }
From source file:com.assentisk.util.FilesService.java
public void generateEmployeDemoXls(HttpServletRequest req, String docName, List<LocationsBean> getLocationList, List<DepartmentBean> getDepartmnentList, List<LegalEntitiesBean> divisionList) throws SQLException, IOException { Integer key = 1;/* ww w . ja v a 2 s . co m*/ try { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(docName); String[] str = new String[getLocationList.size()]; if (getLocationList.size() > 0) { for (int i = 0; i < getLocationList.size(); i++) { Map m = (Map) getLocationList.get(i); str[i] = m.get("Name").toString(); } } String[] strDprtmnt = new String[getDepartmnentList.size()]; if (getDepartmnentList.size() > 0) { for (int i = 0; i < getDepartmnentList.size(); i++) { Map m = (Map) getDepartmnentList.get(i); strDprtmnt[i] = m.get("DeptName").toString(); } } String[] strDivision = new String[divisionList.size()]; if (divisionList.size() > 0) { for (int i = 0; i < divisionList.size(); i++) { Map m = (Map) divisionList.get(i); strDivision[i] = m.get("LegalEntityName").toString(); } } /*for(int i=0;i<locationName.size();i++){ str[i]=locationName.get(i).toString(); }*/ // for(int i=1;i<50;i++){ // CellRangeAddressList addressList = new CellRangeAddressList(i, i, 1, 1); // DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(str); // DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint); // dataValidation.setSuppressDropDownArrow(false); // sheet.addValidationData(dataValidation); // // CellRangeAddressList departmnentList = new CellRangeAddressList(i, i, 0, 0); // DVConstraint dvConstraint1 = DVConstraint.createExplicitListConstraint(strDprtmnt); // DataValidation dataValidation1 = new HSSFDataValidation(departmnentList, dvConstraint1); // dataValidation1.setSuppressDropDownArrow(false); // sheet.addValidationData(dataValidation1); // // // CellRangeAddressList devisionList = new CellRangeAddressList(i, i, 9, 9); // DVConstraint dvConstraint2 = DVConstraint.createExplicitListConstraint(strDivision); // DataValidation dataValidation2 = new HSSFDataValidation(devisionList, dvConstraint2); // dataValidation2.setSuppressDropDownArrow(false); // sheet.addValidationData(dataValidation2); // // } // Map<String, String[]> data = new LinkedHashMap<String, String[]>(); String[] stringVal = { "EmpName", "Department", "Location", "location Country", "location State", "location City", "location Contact Name", "location Email", "Divisions", "address", "city", "zip", "phone1", "phone2", "Email1", "Email2" }; // data.put(key.toString(), { "DepartmentID","LocationID","EmpName","Email", "address","city","state","country","zip","phone1","phone2","divisions","email2","isLicense","isActive","abc"}); data.put(key.toString(), stringVal); Set<String> keyset = data.keySet(); int rownum = 0; sheet.setColumnHidden((short) 17, true); for (String key2 : keyset) { HSSFRow row = sheet.createRow(rownum++); String[] objArr = data.get(key2); short cellnum = 0; for (String obj : objArr) { if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); else if (obj instanceof String) row.createCell(cellnum++).setCellValue((String) obj); } } FileOutputStream out = new FileOutputStream(new File("" + docName + ".xls")); workbook.write(out); out.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } catch (Exception ex) { ex.printStackTrace(); } }
From source file:com.assentisk.util.FilesService.java
public void generateAssestDemoXls(HttpServletRequest req, String docName, List<CategoriesBean> getCategoryList, List<CountryBean> getCountryList, List<ApplicationsBean> appList2, List<BusinessProcessesBean> getBusinessList) throws SQLException, IOException { Integer key = 1;//w w w. j a v a 2s.co m try { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(docName); // HSSFSheet hidden = workbook.createSheet("hidden"); // HSSFSheet hidden1 = workbook.createSheet("hidden1"); // HSSFSheet hidden2 = workbook.createSheet("hidden2"); // HSSFSheet hidden3 = workbook.createSheet("hidden3"); String[] strCategorymnt = new String[getCategoryList.size()]; // if(getCategoryList.size()>0) // { // for (int i = 0; i < getCategoryList.size(); i++) { // Map m = (Map)getCategoryList.get(i); // strCategorymnt[i] = m.get("cname").toString(); // String name = strCategorymnt[i]; // HSSFRow row = hidden3.createRow(i); // HSSFCell cell = row.createCell(0); // cell.setCellValue(name); // } // } // // String [] strCountry = new String[getCountryList.size()]; // if(getCountryList.size()>0) // { // for (int i = 0; i < getCountryList.size(); i++) { // // Map m = (Map)getCountryList.get(i); // strCountry[i] = m.get("CountryName").toString(); // String name = strCountry[i]; // HSSFRow row = hidden.createRow(i); // HSSFCell cell = row.createCell(0); // cell.setCellValue(name); // // } // } // String [] strApllication = new String[appList2.size()]; // if(appList2.size()>0) // { // for (int i = 0; i < appList2.size(); i++) { // // Map m = (Map)appList2.get(i); // strApllication[i] = m.get("ApplicationName").toString(); // String name = strApllication[i]; // HSSFRow row = hidden2.createRow(i); // HSSFCell cell = row.createCell(0); // cell.setCellValue(name); // // } // } // String [] strBusinessList = new String[getBusinessList.size()]; // if(getBusinessList.size()>0) // { // for (int i = 0; i < getBusinessList.size(); i++) { // // Map m = (Map)getBusinessList.get(i); // strBusinessList[i] = m.get("BusinessProcessName").toString(); // String name = strBusinessList[i]; // HSSFRow row = hidden1.createRow(i); // HSSFCell cell = row.createCell(0); // cell.setCellValue(name); // } // } // Name namedCell = workbook.createName(); // namedCell.setNameName("hidden"); // namedCell.setRefersToFormula("hidden!$A$1:$A$" + strCountry.length); // Name namedCell1 = workbook.createName(); // namedCell1.setNameName("hidden1"); // namedCell1.setRefersToFormula("hidden1!$A$1:$A$" + strBusinessList.length); // // Name namedCell2 = workbook.createName(); // namedCell2.setNameName("hidden2"); // namedCell2.setRefersToFormula("hidden2!$A$1:$A$" + strCategorymnt.length); // // Name namedCell3 = workbook.createName(); // namedCell3.setNameName("hidden3"); // namedCell3.setRefersToFormula("hidden3!$A$1:$A$" + strApllication.length); // for(int i=1;i<50;i++){ // DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden"); // CellRangeAddressList country = new CellRangeAddressList(1, 1, 7, 7); // HSSFDataValidation validation = new HSSFDataValidation(country, constraint); // workbook.setSheetHidden(1, true); // sheet.addValidationData(validation); // DVConstraint constraint1 = DVConstraint.createFormulaListConstraint("hidden1"); // CellRangeAddressList businessList = new CellRangeAddressList(1, 1, 3,3); // HSSFDataValidation validation1 = new HSSFDataValidation(businessList, constraint1); // workbook.setSheetHidden(1, true); // sheet.addValidationData(validation1); // // DVConstraint constraint3 = DVConstraint.createFormulaListConstraint("hidden2"); // CellRangeAddressList appList = new CellRangeAddressList(1, 1, 2, 2); // HSSFDataValidation validation2 = new HSSFDataValidation(appList, constraint3); // workbook.setSheetHidden(1, true); // sheet.addValidationData(validation2); // // // DVConstraint constraint2 = DVConstraint.createFormulaListConstraint("hidden3"); // CellRangeAddressList catList = new CellRangeAddressList(1, 1, 1, 1); // HSSFDataValidation validation3 = new HSSFDataValidation(catList, constraint2); // workbook.setSheetHidden(1, true); // sheet.addValidationData(validation3); // CellRangeAddressList businessList = new CellRangeAddressList(i, i, 3, 3); // DVConstraint dvConstraint1 = DVConstraint.createExplicitListConstraint(strBusinessList); // DataValidation dataValidation = new HSSFDataValidation(businessList, dvConstraint1); // dataValidation.setSuppressDropDownArrow(false); // sheet.addValidationData(dataValidation); // CellRangeAddressList appList = new CellRangeAddressList(i, i, 2, 2); // DVConstraint dvConstraint2 = DVConstraint.createExplicitListConstraint(strApllication); // DataValidation dataValidation2 = new HSSFDataValidation(appList, dvConstraint2); // dataValidation.setSuppressDropDownArrow(false); // sheet.addValidationData(dataValidation2); // // CellRangeAddressList catList = new CellRangeAddressList(i, i, 1,1); // DVConstraint dvConstraint3 = DVConstraint.createExplicitListConstraint(strCategorymnt); // DataValidation dataValidation3 = new HSSFDataValidation(catList, dvConstraint3); // dataValidation.setSuppressDropDownArrow(false); // sheet.addValidationData(dataValidation3); // } Map<String, String[]> data = new LinkedHashMap<String, String[]>(); String[] stringVal = { "AssetName", "Category", "Application", "BusinessProcess", "InitialValue", "Quantity", "Location", "Location Country", "Location State", "Location City", "Location Contact Name", "Location Email" }; data.put(key.toString(), stringVal); Set<String> keyset = data.keySet(); int rownum = 0; sheet.setColumnHidden((short) 12, true); for (String key2 : keyset) { HSSFRow row = sheet.createRow(rownum++); String[] objArr = data.get(key2); short cellnum = 0; for (String obj : objArr) { if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } else if (obj instanceof String) { row.createCell(cellnum++).setCellValue((String) obj); } } } FileOutputStream out = new FileOutputStream(new File("" + docName + ".xls")); workbook.write(out); out.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } catch (Exception ex) { ex.printStackTrace(); } }
From source file:com.claim.controller.Noni2015Controller.java
public ProgrameStatus noniDetail(OppReport report) { int col_last = 13; int row_start = 6; // index row int row_formula_start = 7; ProgrameStatus programeStatus = new ProgrameStatus(); List<ObjRptNoniDetail> listData = new ArrayList<ObjRptNoniDetail>(); String stmp = StringOpUtil.removeNull(report.getYearMonth()) + "-" + report.getNo(); String pathDirectory = report.getPathFile() + "" + File.separator + "noni" + File.separator + "" + stmp + "" + File.separator + ""; try {/* w ww. ja v a2 s.co m*/ connection = new DBManage().open(); Noni2015DAO noni2015DAO = new Noni2015DAO(); noni2015DAO.setConnection(connection); //readTemplate file = new FileInputStream(new File("." + File.separator + "xls" + File.separator + "NONI_detail.xls")); //EXCELL_HEADER1 = "?? ???? (NONI) 2557"; EXCELL_HEADER1 = report.getTitle1(); /*if (report.getBudget_year().equals("2014")) { EXCELL_HEADER2 = " 01 ()"; } else { EXCELL_HEADER2 = " " + new DateUtil().convertStmpToString(report.getStmp()); }*/ EXCELL_HEADER2 = new DateUtil().convertStmpToNoniString(report.getStmp()); EXCELL_HOSPITAL = "?: " + StringOpUtil.removeNull(report.getServiceName()) + " (" + StringOpUtil.removeNull(report.getServiceCode()) + ")"; // style Excell HSSFWorkbook wbNoni = new HSSFWorkbook(file); this.loadStyle(wbNoni); // Start sheet 1 HSSFSheet sheet = workbookBase.getSheetAt(0); sheet.createFreezePane(5, row_start); // col[F],row[index 6 = 7] sheet.setColumnWidth((col_last + 1), WIDTH_TXID); // row 0 HEADER0 row = sheet.createRow(0); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HEADER1); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_last)); cell.setCellStyle(csHead); // row 1 HEADER2 row = sheet.createRow(1); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HEADER2); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, col_last)); cell.setCellStyle(csHead); // row 1 HOSPITAL row = sheet.createRow(2); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HOSPITAL); sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, col_last)); cell.setCellStyle(csHead); int curRow = row_start; int i = 1; listData = noni2015DAO.getListNoniDetail(report); for (int j = 0; j < listData.size(); j++) { ObjRptNoniDetail data = listData.get(j); int col1 = i; row = sheet.createRow(curRow); row.setHeight((short) 340); /*PoiHssfUtil stylePoi = new PoiHssfUtil(wb, row, cell); stylePoi.setStyleText(0, String.valueOf(col1), PoiHssfUtil.CENTER);*/ cell = row.createCell(0); cell.setCellValue(col1); cell.setCellStyle(csNum4); cell = row.createCell(1); cell.setCellValue(data.getPid()); cell.setCellStyle(csStringPid); cell = row.createCell(2); cell.setCellValue(data.getPname()); cell.setCellStyle(csStringLeft); cell = row.createCell(3); cell.setCellValue(data.getHn()); cell.setCellStyle(csStringLeft); cell = row.createCell(4); cell.setCellValue(data.getHmainname()); cell.setCellStyle(csStringLeft); cell = row.createCell(5); cell.setCellValue(data.getDateopd_thai_buddha()); cell.setCellStyle(csStringLeft); cell = row.createCell(6); cell.setCellValue(data.getNoniclass()); cell.setCellStyle(csStringLeft); cell = row.createCell(7); cell.setCellValue(data.getChrg_middle_priced_items()); cell.setCellStyle(csDouble2); cell = row.createCell(8); cell.setCellValue(data.getChrg_other()); cell.setCellStyle(csDouble2); cell = row.createCell(9); cell.setCellValue(data.getChrg_total()); cell.setCellStyle(csDouble2); cell = row.createCell(10); cell.setCellValue(data.getPaid_middle_priced_items()); cell.setCellStyle(csDouble2); cell = row.createCell(11); cell.setCellValue(data.getPaid_other()); cell.setCellStyle(csDouble2); cell = row.createCell(12); cell.setCellValue(data.getPaid_total()); cell.setCellStyle(csDouble2); cell = row.createCell(13); cell.setCellValue(data.getInvoice_no()); cell.setCellStyle(csStringLeft); cell = row.createCell(14); cell.setCellValue(data.getTxid()); cell.setCellStyle(csStringtxid); curRow++; i++; } // row = sheet.createRow(curRow); cell = row.createCell(0); cell.setCellValue(""); sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 6)); cell.setCellStyle(csNum4B); cell = row.createCell(1); cell.setCellStyle(csNum4B); cell = row.createCell(2); cell.setCellStyle(csNum4B); cell = row.createCell(3); cell.setCellStyle(csNum4B); cell = row.createCell(4); cell.setCellStyle(csNum4B); cell = row.createCell(5); cell.setCellStyle(csNum4B); cell = row.createCell(6); cell.setCellStyle(csNum4B); cell = row.createCell(7); cell.setCellFormula(builderFormulaSum(7, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(8); cell.setCellFormula(builderFormulaSum(8, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(9); cell.setCellFormula(builderFormulaSum(9, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(10); cell.setCellFormula(builderFormulaSum(10, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(11); cell.setCellFormula(builderFormulaSum(11, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(12); cell.setCellFormula(builderFormulaSum(12, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(13); cell.setCellStyle(csDouble2B); workbookBase.setSheetName(0, report.getServiceCode());// + " " + report.getServiceName()); /// int sheetIndex, int startColumn, int endColumn, int startRow, int endRow) sheet.setColumnHidden(13, true); workbookBase.setPrintArea(0, "$A$1:$M$" + (curRow + 1)); // file out // ############# mkdir ############ pathDirectory = new FileUtil().mkdirDir(pathDirectory, report.getBudget_year(), "noni"); // ############# mkdir ############ out = new FileOutputStream(pathDirectory + "" + File.separator + "noni_" + StringOpUtil.removeNull(report.getServiceCode()) + "_" + report.getStmp() + ".xls"); workbookBase.write(out); out.close(); file.close(); Console.LOG("? " + report.getServiceName() + " : " + report.getYearMonth() + "-" + report.getNo() + " ?", 1); programeStatus.setMessage(ConstantMessage.MSG_REPORT_SUCCESS); programeStatus.setTitle(ConstantMessage.MSG_REPORT_COMPLETE); programeStatus.setProcessStatus(true); } catch (Exception e) { e.printStackTrace(); Console.LOG(e.getMessage(), 0); programeStatus.setMessage(ConstantMessage.MSG_PROCESS_FAILS + e.toString()); programeStatus.setTitle(ConstantMessage.MSG_CONTACT_ADMIN); programeStatus.setProcessStatus(false); } finally { try { if (connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); } } return programeStatus; }
From source file:com.eryansky.core.excelTools.ExcelUtils.java
License:Apache License
public static void copySheetStyle(HSSFWorkbook destwb, HSSFSheet dest, HSSFWorkbook srcwb, HSSFSheet src) { if (src == null || dest == null) return;// w w w. jav a 2 s. co m dest.setAlternativeExpression(src.getAlternateExpression()); dest.setAlternativeFormula(src.getAlternateFormula()); dest.setAutobreaks(src.getAutobreaks()); dest.setDialog(src.getDialog()); if (src.getColumnBreaks() != null) { for (int col : src.getColumnBreaks()) { dest.setColumnBreak(col); } } dest.setDefaultColumnWidth(src.getDefaultColumnWidth()); dest.setDefaultRowHeight(src.getDefaultRowHeight()); dest.setDefaultRowHeightInPoints(src.getDefaultRowHeightInPoints()); dest.setDisplayGuts(src.getDisplayGuts()); dest.setFitToPage(src.getFitToPage()); dest.setHorizontallyCenter(src.getHorizontallyCenter()); dest.setDisplayFormulas(src.isDisplayFormulas()); dest.setDisplayGridlines(src.isDisplayGridlines()); dest.setDisplayRowColHeadings(src.isDisplayRowColHeadings()); dest.setGridsPrinted(src.isGridsPrinted()); dest.setPrintGridlines(src.isPrintGridlines()); for (int i = 0; i < src.getNumMergedRegions(); i++) { CellRangeAddress r = src.getMergedRegion(i); dest.addMergedRegion(r); } if (src.getRowBreaks() != null) { for (int row : src.getRowBreaks()) { dest.setRowBreak(row); } } dest.setRowSumsBelow(src.getRowSumsBelow()); dest.setRowSumsRight(src.getRowSumsRight()); int maxcol = 0; for (int i = 0; i <= src.getLastRowNum(); i++) { HSSFRow row = src.getRow(i); if (row != null) { if (maxcol < row.getLastCellNum()) maxcol = row.getLastCellNum(); } } for (int col = 0; col <= maxcol; col++) { if (src.getColumnWidth(col) != src.getDefaultColumnWidth()) dest.setColumnWidth(col, src.getColumnWidth(col)); dest.setColumnHidden(col, src.isColumnHidden(col)); } }
From source file:com.topsec.tsm.sim.asset.web.AssetListController.java
/** * //from www . jav a 2 s . c om * @param request * @param response */ @RequestMapping("exportAssetExcel") public void exportAssetExcel(SID sid, HttpServletRequest request, HttpServletResponse response) { //? List<AssetObject> assetList; SID.setCurrentUser(sid); try { assetList = AssetFacade.getInstance().getAll(); Collections.sort(assetList, IpComparator.getInstance()); } finally { SID.removeCurrentUser(); } String[] column = { "??(*)", "IP(*)", "??(*)", "(*)", "?(*)", "?(*)", "??", "?", "", null, null, null, null, "?", "??" }; // Excel HSSFWorkbook workbook = new HSSFWorkbook(); // Excel??Asset?? HSSFSheet sheet = workbook.createSheet("?"); //? HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // HSSFRow row = sheet.createRow(0); for (int j = 0; j < column.length; j++) { HSSFCell cell = row.createCell(j); if (column[j] == null) { sheet.setColumnHidden(j, true); continue; } sheet.setColumnWidth(j, 22 * 256); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(column[j]); } NodeMgrFacade nodeMgrFacade = (NodeMgrFacade) SpringContextServlet.springCtx.getBean("nodeMgrFacade"); //?? for (int i = 0; i < assetList.size(); i++) { AssetObject ao = assetList.get(i); row = sheet.createRow(i + 1); for (int j = 0; j < column.length; j++) { HSSFCell cell = row.createCell(j); switch (j) { case 0: cell.setCellValue(i + 1); cell.setCellStyle(cellStyle); continue; case 1: cell.setCellValue(ao.getIp()); continue; case 2: cell.setCellValue(ao.getName()); continue; case 3: String deviceType = ao.getDeviceType().split("/")[0]; cell.setCellValue( DeviceTypeShortKeyUtil.getInstance().getShortZhCN(deviceType) + "_" + deviceType); continue; case 4: String vendor = ao.getDeviceType().split("/")[1]; cell.setCellValue(DeviceTypeShortKeyUtil.getInstance().getShortZhCN(vendor) + "_" + vendor); continue; case 5: Node node = nodeMgrFacade.getNodeByNodeId(ao.getScanNodeId()); cell.setCellValue(node != null ? node.getIp() : ""); continue; case 6: cell.setCellValue(ao.getHostName()); continue; case 7: cell.setCellValue(ao.getOs() != null ? ao.getOs().getOsName() : ""); continue; case 8: cell.setCellValue(ao.getSafeRank()); continue; case 9: cell.setCellValue(ao.getAssGroup().getGroupName()); continue; case 10: case 11: case 12: continue; case 13: cell.setCellValue(StringUtil.nvl(ao.getLinkman())); continue; case 14: cell.setCellValue(""); continue; default: cell.setCellValue(""); continue; } } } //?excel? //response.setContentType("application/vnd.ms-excel"); String userAgent = request.getHeader("User-Agent"); String fileName = "?.xls"; if (userAgent.indexOf("Firefox") > 0) { response.setHeader("Content-Disposition", "attachment; filename*=\"utf8' '" + StringUtil.encode(fileName, "UTF-8") + "\""); } else { response.addHeader("Content-Disposition", "attachment; filename=\"" + StringUtil.encode(fileName, "UTF-8") + "\""); } try { workbook.write(response.getOutputStream()); } catch (IOException e) { e.printStackTrace(); } }
From source file:com.viettel.hqmc.DAO.ReportDAO.java
public void reportStaffOnRequest() { try {/*from w w w .ja va 2s . c o m*/ 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:org.activityinfo.server.endpoint.export.SiteExporter.java
License:Open Source License
private void createHeaders(ActivityFormDTO activity, HSSFSheet sheet) { // / The HEADER rows Row headerRow1 = sheet.createRow(0); Row headerRow2 = sheet.createRow(1); headerRow2.setHeightInPoints(HEADER_CELL_HEIGHT); // Create a title cell with the complete database + activity name Cell titleCell = headerRow1.createCell(0); titleCell.setCellValue(//from ww w .ja va 2 s . c o m creationHelper.createRichTextString(activity.getDatabaseName() + " - " + activity.getName())); titleCell.setCellStyle(titleStyle); int column = 0; createHeaderCell(headerRow2, column++, "SiteId", CellStyle.ALIGN_LEFT); createHeaderCell(headerRow2, column++, "DateCreated", CellStyle.ALIGN_RIGHT); sheet.setColumnHidden(0, true); sheet.setColumnHidden(1, true); createHeaderCell(headerRow2, column++, "Date1", CellStyle.ALIGN_RIGHT); createHeaderCell(headerRow2, column++, "Date2", CellStyle.ALIGN_RIGHT); createHeaderCell(headerRow2, column, "Partner"); sheet.setColumnWidth(column, characters(PARTNER_COLUMN_WIDTH)); column++; createHeaderCell(headerRow2, column, activity.getLocationType().getName()); sheet.setColumnWidth(column, characters(LOCATION_COLUMN_WIDTH)); column++; createHeaderCell(headerRow2, column++, "Axe"); indicators = new ArrayList<Integer>(activity.getIndicators().size()); if (activity.getReportingFrequency() == ActivityFormDTO.REPORT_ONCE) { for (IndicatorGroup group : activity.groupIndicators()) { if (group.getName() != null) { // create a merged cell on the top row spanning all members // of the group createHeaderCell(headerRow1, column, group.getName()); sheet.addMergedRegion( new CellRangeAddress(0, 0, column, column + group.getIndicators().size() - 1)); } for (IndicatorDTO indicator : group.getIndicators()) { indicators.add(indicator.getId()); createHeaderCell(headerRow2, column, indicator.getName(), indicatorHeaderStyle); sheet.setColumnWidth(column, characters(INDICATOR_COLUMN_WIDTH)); column++; } } } attributes = new ArrayList<>(); for (AttributeGroupDTO group : activity.getAttributeGroups()) { if (group.getAttributes().size() != 0) { createHeaderCell(headerRow1, column, group.getName(), CellStyle.ALIGN_CENTER); sheet.addMergedRegion( new CellRangeAddress(0, 0, column, column + group.getAttributes().size() - 1)); for (AttributeDTO attrib : group.getAttributes()) { attributes.add(attrib.getId()); createHeaderCell(headerRow2, column, attrib.getName(), attribHeaderStyle); sheet.setColumnWidth(column, characters(ATTRIBUTE_COLUMN_WIDTH)); column++; } } } levels = new ArrayList<>(); for (AdminLevelDTO level : activity.getAdminLevels()) { createHeaderCell(headerRow2, column++, "Code " + level.getName()); createHeaderCell(headerRow2, column++, level.getName()); levels.add(level.getId()); } int latColumn = column++; int lngColumn = column++; createHeaderCell(headerRow2, latColumn, I18N.CONSTANTS.longitude(), CellStyle.ALIGN_RIGHT); createHeaderCell(headerRow2, lngColumn, I18N.CONSTANTS.latitude(), CellStyle.ALIGN_RIGHT); sheet.setColumnWidth(lngColumn, characters(COORD_COLUMN_WIDTH)); sheet.setColumnWidth(latColumn, characters(COORD_COLUMN_WIDTH)); createHeaderCell(headerRow2, column++, I18N.CONSTANTS.comments()); }