Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook write

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook write

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. write

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFWorkbook write.

Prototype

private void write(POIFSFileSystem fs) throws IOException

Source Link

Document

Writes the workbook out to a brand new, empty POIFS

Usage

From source file:com.viettel.bankplus.merchantgw.dao.ExportExcelAction.java

License:Open Source License

private void exportTransaction() {
    try {/*from w ww .j a  va 2s . co  m*/
        Date now = Calendar.getInstance().getTime();
        Map map = new HashMap();

        String templateFolder = "/template/";
        String reportFolder = getConfig("export_output");

        String reportFileNameTmp = getCpCode() + "_" + DateTimeUtils.convertDateToString(now, "yyMMddHHmm");
        String reportFileNameExt = ".xls";

        String templateFileName = templateFolder + "transaction.xls";
        //<editor-fold defaultstate="collapsed" desc="fill cac thong tin chung">

        Object fromDateTemp = getRequest().getSession().getAttribute("listTransaction.fromDate");
        Object toDateTemp = getRequest().getSession().getAttribute("listTransaction.toDate");
        map.put("fromDate", fromDateTemp);
        map.put("toDate", toDateTemp);
        List<Transaction> lst = (List) getRequest().getSession().getAttribute("listTransaction");
        List<Transaction> lst2 = new ArrayList();
        String spCode = "";
        String spName = "";
        if (lst != null) {
            for (Transaction transaction : lst) {
                if (transaction.getCONFIRMSTATUS() != null) {
                    if (transaction.getCONFIRMSTATUS().compareTo(new BigDecimal("2")) == 0) {
                        transaction.setTEXTCONFIRMSTATUS("Thnh cng");
                    }
                    if (transaction.getCONFIRMSTATUS().compareTo(new BigDecimal("4")) == 0) {
                        transaction.setTEXTCONFIRMSTATUS("Tht bi");
                    }
                } else {
                    transaction.setTEXTCONFIRMSTATUS("");
                }
                if (transaction.getTRANSSTATUS() != null) {
                    if (transaction.getTRANSSTATUS().equals("0")) {
                        transaction.setTEXTTRANSSTATUS("Cha x l");
                    } else if (transaction.getTRANSSTATUS().equals("1")) {
                        transaction.setTEXTTRANSSTATUS("?ang x l");
                    } else if (transaction.getTRANSSTATUS().equals("2")) {
                        transaction.setTEXTTRANSSTATUS("Thnh cng");
                    } else if (transaction.getTRANSSTATUS().equals("3")) {
                        transaction.setTEXTTRANSSTATUS("? hy");
                    } else if (transaction.getTRANSSTATUS().equals("4")) {
                        transaction.setTEXTTRANSSTATUS("Tht bi");
                    }
                } else {
                    transaction.setTEXTTRANSSTATUS("");
                }
                lst2.add(transaction);
            }
            ContentProvider cp = new DBProcessor().getProviderProcessor().getProviderById(getCpId());
            spCode = cp.getCpCode();
            spName = cp.getCpName();
            if (cp != null) {
                map.put("cpCode", spCode);
                map.put("cpName", spName);
            }
            map.put("list", lst2);
        } else {
            map.put("list", new ArrayList());
        }

        String reportFileName = reportFolder + reportFileNameTmp + reportFileNameExt;
        fileName = reportFileName;
        XLSTransformer transformer = new XLSTransformer();

        List sheetNames = new ArrayList();
        List maps = new ArrayList();
        List<Transaction> tempList = new ArrayList<Transaction>();
        int indexSheet = 0;
        for (int i = 0; i < lst2.size(); i++) {
            tempList.add(lst2.get(i));
            if ((i != 0) && ((i + 1) % 65000 == 0)) {
                map.put("list", tempList);
                map.put("name", "sheet_transaction_" + (indexSheet + 1));
                sheetNames.add("Sheet_" + (indexSheet + 1));
                maps.add(map);
                //
                tempList = new ArrayList<Transaction>();
                map = new HashMap();
                map.put("cpCode", spCode);
                map.put("cpName", spName);
                map.put("fromDate", fromDateTemp);
                map.put("toDate", toDateTemp);
                indexSheet++;
            }
        }

        if (tempList != null && !tempList.isEmpty()) {
            map = new HashMap();
            map.put("cpCode", spCode);
            map.put("cpName", spName);
            map.put("fromDate", fromDateTemp);
            map.put("toDate", toDateTemp);

            map.put("list", tempList);
            map.put("name", "sheet_transaction_" + (indexSheet + 1));
            sheetNames.add("Sheet_" + (indexSheet + 1));
            maps.add(map);
        }
        if (lst == null || lst.isEmpty()) {
            map = new HashMap();
            map.put("cpCode", spCode);
            map.put("cpName", spName);
            map.put("fromDate", fromDateTemp);
            map.put("toDate", toDateTemp);

            map.put("list", new ArrayList());
            map.put("name", "sheet_transaction_" + (indexSheet + 1));
            sheetNames.add("Sheet_" + (indexSheet + 1));
            maps.add(map);
        }

        InputStream inputStream = new BufferedInputStream(
                new FileInputStream(getRequest().getRealPath(templateFileName)));
        //            transformMultipleSheetsList(InputStream is, List objects, List newSheetNames, String beanName, Map beanParams, int startSheetNum);
        HSSFWorkbook resultWorkbook = transformer.transformMultipleSheetsList(inputStream, maps, sheetNames,
                "map", new HashMap(), 0);
        OutputStream outputStream = new FileOutputStream(getRequest().getRealPath(reportFileName));
        resultWorkbook.write(outputStream);
        outputStream.close();

        //            transformer.transformXLS(inputStream, sheetNames, sheetNames, maps);
        //            transformer.transformXLS(getRequest().getRealPath(templateFileName), map,
        //                    getRequest().getRealPath(reportFileName));
        fileInputStream = new FileInputStream(new File(getRequest().getRealPath(reportFileName)));
    } catch (Exception e) {
        e.printStackTrace();
        log.error(e, e);
    }
}

From source file:com.viettel.hqmc.DAO.ReportDAO.java

public void reportStaffOnRequest() {
    try {//from  ww w .ja  v  a 2 s . 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:com.vportal.portlet.vcms.action.ReportAction.java

License:Open Source License

public void setReportByType(int y, int x, HSSFSheet sheet, HSSFWorkbook wb, long groupId, String language,
        long statusId, Date fromDate, Date toDate, String byUser) throws Exception {

    HSSFRow row;//from w ww.j  a  v a  2 s . c  o m

    List listType = VcmsTypeServiceUtil.getTypesByS_L(groupId, language);
    sheet.shiftRows(y, sheet.getLastRowNum(), 1);

    //date
    row = sheet.createRow(y - 2);
    ReportUtil.createCellNoBorder(row, (short) (x + 1), " T\u1eeb ng\u00e0y " + ActionUtil.dateParser(fromDate)
            + " \u0111\u1ebfn ng\u00e0y " + ActionUtil.dateParser(toDate), wb);

    row = sheet.createRow(y);

    ReportUtil.createCellBold(row, (short) (x), "S\u1ed1 TT", wb);
    ReportUtil.createCellBold(row, (short) (x + 1), "Lo\u1ea1i tin", wb);
    ReportUtil.createCellBold(row, (short) (x + 2), "T\u00e1c ph\u1ea9m", wb);

    int totalArticle = 0;
    int stt = 0;
    for (int j = 0; j < listType.size(); j++) {
        VcmsType vcmsType = (VcmsType) listType.get(j);
        sheet.shiftRows(y + 1, sheet.getLastRowNum(), 1);
        row = sheet.createRow(1 + y++);

        int total = VcmsArticleServiceUtil.countByType(groupId, language, statusId,
                Long.valueOf(vcmsType.getTypeId()), fromDate, toDate, byUser);
        totalArticle += total;

        if (vcmsType.getTypeId() != null) {
            ReportUtil.createCell(row, (short) (x), ++stt, wb);
            ReportUtil.createCellAlignLeft(row, (short) (x + 1), "" + vcmsType.getName() + "", wb);
            ReportUtil.createCell(row, (short) (x + 2), total, wb);
        }

    }
    //total count
    row = sheet.createRow(y + 2);
    ReportUtil.createCellNoBorder(row, (short) (x + 1), "T\u1ed5ng s\u1ed1 b\u00e0i vi\u1ebft", wb);
    ReportUtil.createCellNoBorder(row, (short) (x + 2), String.valueOf(totalArticle), wb);

    FileOutputStream fileOut = null;
    try {
        fileOut = new FileOutputStream(
                PortalUtil.getPortalWebDir() + "/VCMS-portlet/html/portlet/vcms/report/file/reportByType.xls");
        wb.write(fileOut);

    } catch (Exception ex) {
        System.out.println(ex.toString());
    } finally {
        fileOut.flush();
        fileOut.close();
    }
}

From source file:com.vportal.portlet.vcms.action.ReportAction.java

License:Open Source License

public void setCategoryReport(List portionResults, int y, int x, HSSFSheet sheet, HSSFWorkbook wb, long groupId,
        String language, String parentId, Date dateFrom, Date dateTo, String byUser) throws Exception {
    int totalStatus = VcmsStatusLocalServiceUtil.countByGroupId(groupId);
    List statusResults = VcmsStatusLocalServiceUtil.getByGroupId(groupId, 0, totalStatus);

    HSSFRow row;//from   ww w .  j a va 2 s .c o m
    HSSFCell cell;
    User userBean;

    sheet.shiftRows(y, sheet.getLastRowNum(), 1);

    row = sheet.createRow(y - 2);
    ReportUtil.createCellNoBorder(row, (short) (x + 1), " T\u1eeb ng\u00e0y " + ActionUtil.dateParser(dateFrom)
            + " \u0111\u1ebfn ng\u00e0y " + ActionUtil.dateParser(dateTo), wb);

    row = sheet.createRow(y);
    ReportUtil.createCellBold(row, (short) (x), "S\u1ed1 TT", wb);
    ReportUtil.createCellBold(row, (short) (x + 1), "Chuy\u00ean m\u1ee5c", wb);

    for (int j = 0; j < totalStatus; j++) {
        VcmsStatus vcmsStatus = (VcmsStatus) statusResults.get(j);
        ReportUtil.createCellBold(row, (short) (x + 2 + j), "" + vcmsStatus.getName() + "", wb);
    }

    int stt = 0;
    int totalArticle = 0;
    for (int i = portionResults.size() - 1; i >= 0; --i) {
        VcmsPortion vcmsPortion = (VcmsPortion) portionResults.get(i);
        List categoryByPortion = ActionUtil.getListCategoryReport(groupId, vcmsPortion.getPortionId(), parentId,
                language);

        for (int j = 0; j < categoryByPortion.size(); j++) {
            VcmsCategory vcmsCategory = (VcmsCategory) categoryByPortion.get(j);
            sheet.shiftRows(y + 1, sheet.getLastRowNum(), 1);
            row = sheet.createRow(1 + y++);

            if (vcmsCategory.getName() != null) {
                ReportUtil.createCell(row, (short) (x), ++stt, wb);
            }
            if (vcmsCategory.getName() != null) {
                ReportUtil.createCellAlignLeft(row, (short) (x + 1), "" + vcmsCategory.getName() + "", wb);
            }
            for (int k = 0; k < totalStatus; k++) {
                int countArticles = 0;
                VcmsStatus status = (VcmsStatus) statusResults.get(k);

                countArticles = VcmsArticleServiceUtil.countByC_P_L_S_D(vcmsCategory.getCategoryId(), groupId,
                        language, status.getStatusId(), dateFrom, dateTo, byUser);
                totalArticle += countArticles;

                ReportUtil.createCell(row, (short) (x + 2 + k), countArticles, wb);
            }
        }

    }

    row = sheet.createRow(y + 2);
    ReportUtil.createCellNoBorder(row, (short) (x + 1), "T\u1ed5ng s\u1ed1 b\u00e0i vi\u1ebft", wb);
    ReportUtil.createCellNoBorder(row, (short) (x + 2), String.valueOf(totalArticle), wb);

    FileOutputStream fileOut = null;
    try {
        fileOut = new FileOutputStream(PortalUtil.getPortalWebDir()
                + "/VCMS-portlet/html/portlet/vcms/report/file/reportCategory.xls");
        wb.write(fileOut);
    } catch (Exception ex) {
        System.out.println(ex.toString());
    } finally {
        fileOut.flush();
        fileOut.close();
    }
}

From source file:com.vportal.portlet.vcms.action.ReportAction.java

License:Open Source License

public void setReportByDate(int y, int x, HSSFSheet sheet, HSSFWorkbook wb, long groupId, String language,
        long statusId, Date dateFrom, Date dateTo, String byUser, String[] strType, int begin, int end)
        throws Exception {

    List listNews = new ArrayList();

    listNews = VcmsArticleServiceUtil.listArticleByDateTypes(groupId, language, statusId, dateFrom, dateTo,
            byUser, strType, begin, end);

    HSSFRow row;//from www  .  j ava  2  s .c om
    HSSFCell cell;
    User userBean;

    VcmsStatus vcmsStatus = VcmsStatusLocalServiceUtil.getVcmsStatus(statusId);

    sheet.shiftRows(y, sheet.getLastRowNum(), 1);
    row = sheet.createRow(y - 2);
    ReportUtil.createCellNoBorder(row, (short) (x + 1), " T\u1eeb ng\u00e0y " + ActionUtil.dateParser(dateFrom)
            + " \u0111\u1ebfn ng\u00e0y " + ActionUtil.dateParser(dateTo), wb);

    row = sheet.createRow(y);

    ReportUtil.createCellBold(row, (short) (x), "S\u1ed1 TT", wb);
    ReportUtil.createCellBold(row, (short) (x + 1), "Ti\u00eau \u0111\u1ec1", wb);
    ReportUtil.createCellBold(row, (short) (x + 2), "Ng\u00e0y \u0111\u0103ng", wb);
    ReportUtil.createCellBold(row, (short) (x + 3), "Ng\u01b0\u1eddi t\u1ea1o", wb);
    ReportUtil.createCellBold(row, (short) (x + 4), "Ng\u01b0\u1eddi duy\u1ec7t", wb);
    ReportUtil.createCellBold(row, (short) (x + 5), "Ng\u01b0\u1eddi xu\u1ea5t b\u1ea3n", wb);
    ReportUtil.createCellBold(row, (short) (x + 6), "Lo\u1ea1i tin", wb);

    int stt = 0;
    for (int j = 0; j < listNews.size(); j++) {
        VcmsArticle vcmsAr = (VcmsArticle) listNews.get(j);

        String typeNames = "";
        try {
            typeNames += ReportUtil.getTypeNames(vcmsAr.getArticleId());
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        sheet.shiftRows(y + 1, sheet.getLastRowNum(), 1);
        row = sheet.createRow(1 + y++);

        if (vcmsAr != null) {
            ReportUtil.createCell(row, (short) (x), ++stt, wb);
            ReportUtil.createCellAlignLeft(row, (short) (x + 1), "" + vcmsAr.getTitle() + "", wb);
            ReportUtil.createCell(row, (short) (x + 2), ActionUtil.dateParser(vcmsAr.getPublishedDate()), wb);

            try {
                User userCreate = UserServiceUtil.getUserById(Long.parseLong(vcmsAr.getCreatedByUser()));
                ReportUtil.createCellAlignLeft(row, (short) (x + 3), userCreate.getFullName(), wb);
            } catch (Exception ex) {
                ReportUtil.createCellAlignLeft(row, (short) (x + 3), "User \u0111\u00e3 b\u1ecb x\u00f3a", wb);
            }

            try {
                User modifieddUser = UserServiceUtil.getUserById(Long.parseLong(vcmsAr.getModifiedByUser()));
                ReportUtil.createCellAlignLeft(row, (short) (x + 4), modifieddUser.getFullName(), wb);
            } catch (Exception ex) {
                ReportUtil.createCellAlignLeft(row, (short) (x + 4), "User \u0111\u00e3 b\u1ecb x\u00f3a", wb);
            }

            try {
                User publishedUser = UserServiceUtil.getUserById(Long.parseLong(vcmsAr.getPublishedByUser()));
                ReportUtil.createCellAlignLeft(row, (short) (x + 5), publishedUser.getFullName(), wb);
            } catch (Exception ex) {
                ReportUtil.createCellAlignLeft(row, (short) (x + 5), "User \u0111\u00e3 b\u1ecb x\u00f3a", wb);
            }

            ReportUtil.createCellAlignLeft(row, (short) (x + 6), typeNames, wb);
        }

    }

    row = sheet.createRow(y + 2);
    ReportUtil.createCellNoBorder(row, (short) (x + 1), "T\u1ed5ng s\u1ed1 b\u00e0i vi\u1ebft", wb);
    ReportUtil.createCellNoBorder(row, (short) (x + 2), String.valueOf(listNews.size()), wb);

    FileOutputStream fileOut = null;
    try {
        fileOut = new FileOutputStream(
                PortalUtil.getPortalWebDir() + "/VCMS-portlet/html/portlet/vcms/report/file/reportByDate.xls");
        wb.write(fileOut);
    } catch (Exception ex) {
        System.out.println(ex.toString());
    } finally {
        fileOut.flush();
        fileOut.close();
    }
}

From source file:com.vportal.portlet.vcms.action.ReportAction.java

License:Open Source License

public void setUserReport(List userList, int y, int x, HSSFSheet sheet, HSSFWorkbook wb, long groupId,
        String language, String parentId, long statusId, Date dateFrom, Date dateTo) throws Exception {

    List listType = VcmsTypeServiceUtil.getTypesByS_L(groupId, language);

    HSSFRow row;//from w  ww. j  av a  2  s  . c  o m
    User userBean;

    sheet.shiftRows(y, sheet.getLastRowNum(), 1);

    row = sheet.createRow(y - 2);
    ReportUtil.createCellNoBorder(row, (short) (x + 1), " T\u1eeb ng\u00e0y " + ActionUtil.dateParser(dateFrom)
            + " \u0111\u1ebfn ng\u00e0y " + ActionUtil.dateParser(dateTo), wb);

    row = sheet.createRow(y);
    ReportUtil.createCellBold(row, (short) (x), "S\u1ed1 TT", wb);
    ReportUtil.createCellBold(row, (short) (x + 1), "H\u1ecd T\u00ean", wb);

    for (int j = 0; j < listType.size(); j++) {
        VcmsType vcmsType = (VcmsType) listType.get(j);
        ReportUtil.createCellBold(row, (short) (x + 2 + j), "" + vcmsType.getName() + "", wb);
    }
    ReportUtil.createCellBold(row, (short) (x + 2 + listType.size()), "C\u00e1c lo\u1ea1i tin kh\u00e1c", wb);

    int stt = 0;
    int totalArticle = 0;

    for (int i = userList.size() - 1; i >= 0; --i) {
        userBean = (User) userList.get(i);
        sheet.shiftRows(y + 1, sheet.getLastRowNum(), 1);
        row = sheet.createRow(1 + y++);

        if (userBean != null) {
            ReportUtil.createCell(row, (short) (x), ++stt, wb);
        }
        if (userBean != null) {
            ReportUtil.createCellAlignLeft(row, (short) (x + 1), "" + userBean.getFullName() + "", wb);
        }

        for (int j = 0; j < listType.size(); j++) {
            VcmsType vcmsType = (VcmsType) listType.get(j);
            int countArticle = 0;
            if (!parentId.equals("0")) {
                countArticle = VcmsArticleServiceUtil.countByUser(groupId, language, parentId, statusId,
                        Long.valueOf(vcmsType.getTypeId()), dateFrom, dateTo,
                        String.valueOf(userBean.getUserId()));
            } else {

                countArticle = VcmsArticleServiceUtil.countByType(groupId, language, statusId,
                        Long.valueOf(vcmsType.getTypeId()), dateFrom, dateTo,
                        String.valueOf(userBean.getUserId()));
            }

            ReportUtil.createCell(row, (short) (x + 2 + j), countArticle, wb);
            totalArticle += countArticle;
        }

        //other type
        int countArticleAllCatNotInType = 0;
        if (!parentId.equals("0")) {
            countArticleAllCatNotInType = VcmsArticleServiceUtil.countArticleNotInType(groupId, language,
                    parentId, statusId, dateFrom, dateTo, String.valueOf(userBean.getUserId()));
        } else {
            countArticleAllCatNotInType = VcmsArticleServiceUtil.countArticleAllCatNotInType(groupId, language,
                    statusId, dateFrom, dateTo, String.valueOf(userBean.getUserId()));
        }

        ReportUtil.createCell(row, (short) (x + 2 + listType.size()), countArticleAllCatNotInType, wb);
        totalArticle += countArticleAllCatNotInType;
    }

    row = sheet.createRow(y + 2);
    ReportUtil.createCellNoBorder(row, (short) (x + 1), "T\u1ed5ng s\u1ed1 b\u00e0i vi\u1ebft", wb);
    ReportUtil.createCellNoBorder(row, (short) (x + 2), String.valueOf(totalArticle), wb);

    FileOutputStream fileOut = null;
    try {
        fileOut = new FileOutputStream(
                PortalUtil.getPortalWebDir() + "/VCMS-portlet/html/portlet/vcms/report/file/reportUser.xls");
        wb.write(fileOut);
    } catch (Exception ex) {
        System.out.println(ex.toString());
    } finally {
        fileOut.flush();
        fileOut.close();
    }

}

From source file:com.vportal.portlet.vdoc.action.VDocManage.java

License:Open Source License

public void setReportByDate(int y, int x, HSSFSheet sheet, HSSFWorkbook wb, long groupId, String language,
        int statusId, Date dateFrom, Date dateTo) throws Exception {
    List listnew = new ArrayList();
    listnew = vdocDocumentServiceUtil.getDocbyModifyDate(groupId, language, dateFrom, dateTo, statusId);
    HSSFRow row;/*from  w w w . ja va2  s .  co  m*/
    HSSFCell cell;
    sheet.shiftRows(sheet.getLastRowNum(), y, 1);
    row = sheet.createRow(y - 2);
    ReportUtil.createCellNoBorder(row, (short) (x + 1), " T\u1eeb ng\u00e0y " + ActionUtil.dateParser(dateFrom)
            + " \u0111\u1ebfn ng\u00e0y " + ActionUtil.dateParser(dateTo), wb);

    row = sheet.createRow(y);

    ReportUtil.createCellBold(row, (short) (x), "S\u1ed1 TT", wb);
    ReportUtil.createCellBold(row, (short) (x + 1), "Ti\u00eau \u0111\u1ec1", wb);
    ReportUtil.createCellBold(row, (short) (x + 2), "Nga\u0300y xu\u00E2\u0301t ba\u0309n", wb);
    ReportUtil.createCellBold(row, (short) (x + 3), "Nga\u0300y chi\u0309nh s\u01B0\u0309a", wb);
    ReportUtil.createCellBold(row, (short) (x + 4), "Ng\u01B0\u01A1\u0300i duy\u00EA\u0323t", wb);
    ReportUtil.createCellBold(row, (short) (x + 5), "Ng\u01B0\u01A1\u0300i chi\u0309nh s\u01B0\u0309a", wb);
    ReportUtil.createCellBold(row, (short) (x + 6), "\u0110\u01A1n vi\u0323", wb);
    int stt = 0;
    for (int j = 0; j < listnew.size(); j++) {
        // VcmsArticle vcmsAr = (VcmsArticle) listNews.get(j);
        vdocDocument vdoc = (vdocDocument) listnew.get(j);

        sheet.shiftRows(y + 1, sheet.getLastRowNum(), 1);
        row = sheet.createRow(1 + y++);

        if (vdoc != null) {
            ReportUtil.createCell(row, (short) (x), ++stt, wb);
            ReportUtil.createCellAlignLeft(row, (short) (x + 1), "" + vdoc.getTitle() + "", wb);
            ReportUtil.createCell(row, (short) (x + 2), ActionUtil.dateParser(vdoc.getPublishedDate()), wb);

            try {
                // User userCreate =
                // UserServiceUtil.getUserById(vdoc.getCreatedByUser());
                ReportUtil.createCellAlignLeft(row, (short) (x + 3),
                        ActionUtil.dateParser(vdoc.getModifiedDate()), wb);
            } catch (Exception ex) {
                ReportUtil.createCellAlignLeft(row, (short) (x + 3), "Ng&#224;y ch&#7881;nh s&#7917;a", wb);
            }

            try {
                User modifieddUser = UserServiceUtil.getUserById(vdoc.getPublishedByUser());
                ReportUtil.createCellAlignLeft(row, (short) (x + 4), modifieddUser.getFullName(), wb);
            } catch (Exception ex) {
                ReportUtil.createCellAlignLeft(row, (short) (x + 4), "Ng\u01B0\u01A1\u0300i duy\u00EA\u0323t",
                        wb);
            }

            try {
                User publishedUser = UserServiceUtil.getUserById(vdoc.getModifiedByUser());
                ReportUtil.createCellAlignLeft(row, (short) (x + 5), publishedUser.getFullName(), wb);
            } catch (Exception ex) {
                ReportUtil.createCellAlignLeft(row, (short) (x + 5),
                        "Ng\u01B0\u01A1\u0300i chi\u0309nh s\u01B0\u0309a", wb);
            }
            try {

                ReportUtil.createCellAlignLeft(row, (short) (x + 6), vdoc.getOrgRels(), wb);
            } catch (Exception ex) {
                ReportUtil.createCellAlignLeft(row, (short) (x + 6), "\u0110\u01A1n vi\u0323", wb);
            }

        }

    }

    row = sheet.createRow(y + 2);
    ReportUtil.createCellNoBorder(row, (short) (x + 1), "T\u1ed5ng s\u1ed1 b\u00e0i vi\u1ebft", wb);
    ReportUtil.createCellNoBorder(row, (short) (x + 2), String.valueOf(listnew.size()), wb);

    FileOutputStream fileOut = null;
    String porttalDir = PortalUtil.getPortalWebDir();
    try {
        fileOut = new FileOutputStream(porttalDir.substring(0, porttalDir.indexOf("ROOT"))
                + "//VDoc-portlet//html//vdoc_manage//statistics//file//reportByDate.xls");
        wb.write(fileOut);
    } catch (Exception ex) {
        System.out.println(ex.toString());
    } finally {
        fileOut.flush();
        fileOut.close();
    }
}

From source file:com.vportal.portlet.vdoc.action.VDocManage.java

License:Open Source License

public void setReportByOrg(int y, int x, HSSFSheet sheet, HSSFWorkbook wb, long groupId, String language,
        int statusId) throws Exception {
    List listnew = new ArrayList();
    listnew = vdocOrgServiceUtil.getOrgByG_L(groupId, language);
    HSSFRow row;/*from  w w w .  ja  v  a2  s.  c  o m*/
    HSSFCell cell;
    sheet.shiftRows(sheet.getLastRowNum(), y, 1);
    row = sheet.createRow(y - 2);
    row = sheet.createRow(y);

    ReportUtil.createCellBold(row, (short) (x), "S\u1ed1 TT", wb);
    ReportUtil.createCellBold(row, (short) (x + 1), "C\u01A1 quan - \u0110\u01A1n vi\u0323", wb);
    ReportUtil.createCellBold(row, (short) (x + 2), "Ch\u01A1\u0300 xu\u00E2\u0301t ba\u0309n", wb);
    ReportUtil.createCellBold(row, (short) (x + 3), "\u0110a\u0303 xu\u00E2\u0301t ba\u0309n", wb);
    ReportUtil.createCellBold(row, (short) (x + 4), "Chi\u0309nh s\u01B0\u0309a", wb);
    int stt = 0;
    for (int j = 0; j < listnew.size(); j++) {
        // VcmsArticle vcmsAr = (VcmsArticle) listNews.get(j);
        vdocOrg org = (vdocOrg) listnew.get(j);

        sheet.shiftRows(y + 1, sheet.getLastRowNum(), 1);
        row = sheet.createRow(1 + y++);

        if (org != null) {
            ReportUtil.createCell(row, (short) (x), ++stt, wb);
            ReportUtil.createCellAlignLeft(row, (short) (x + 1), "" + org.getName() + "", wb);
            ReportUtil.createCell(row, (short) (x + 2),
                    vdocDORelServiceUtil.countCategory_approving(org.getOrgId()), wb);

            try {
                // User userCreate =
                // UserServiceUtil.getUserById(vdoc.getCreatedByUser());
                ReportUtil.createCellAlignLeft(row, (short) (x + 3),
                        String.valueOf(vdocDORelServiceUtil.countCategory_approving(org.getOrgId())), wb);
            } catch (Exception ex) {
                ReportUtil.createCellAlignLeft(row, (short) (x + 3), "...", wb);
            }

            try {

                ReportUtil.createCellAlignLeft(row, (short) (x + 4),
                        String.valueOf(vdocDORelServiceUtil.countCategory_approving(org.getOrgId())), wb);
            } catch (Exception ex) {
                ReportUtil.createCellAlignLeft(row, (short) (x + 4), "...", wb);
            }

        }

    }

    FileOutputStream fileOut = null;
    String porttalDir = PortalUtil.getPortalWebDir();
    try {
        fileOut = new FileOutputStream(porttalDir.substring(0, porttalDir.indexOf("ROOT"))
                + "//VDoc-portlet//html//vdoc_manage//statistics//file//reportByOrg.xls");
        wb.write(fileOut);
    } catch (Exception ex) {
        System.out.println(ex.toString());
    } finally {
        fileOut.flush();
        fileOut.close();
    }
}

From source file:com.webbfontaine.valuewebb.report.AirCargoReporter.java

License:Open Source License

private byte[] getXLS() throws SQLException, IOException {

    try (Connection connection = getConnection();
            PreparedStatement ps = getPreparedStatement(connection);
            ResultSet rs = ps.executeQuery();
            InputStream is = getTemplateFileStream(getReportFilePath())) {

        rs.setFetchSize(1000);//ww  w.  j  a  v a 2  s.c om

        HSSFWorkbook workbook = new HSSFWorkbook(is);
        Sheet sheet = workbook.getSheetAt(0);

        Map<String, CellStyle> cellStyleMap = getAllCellStyles(workbook);
        int rowsCreated = createCells(rs, cellStyleMap, sheet);

        if (rowsCreated == DEFAULT_STARTING_ROW_NUMBER) {
            LOGGER.debug("Report is empty");
            return null;
        }

        try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
            workbook.write(outputStream);
            return outputStream.toByteArray();
        }
    }
}

From source file:com.webbfontaine.valuewebb.report.ScanSelectivityReporter.java

License:Open Source License

private byte[] processXls(InputStream is, ResultSet rs) throws IOException, SQLException {
    HSSFWorkbook workbook = new HSSFWorkbook(is);
    Sheet sheet = workbook.getSheetAt(0);

    Map<String, CellStyle> cellStyleMap = getAllCellStyles(workbook);
    int rowsCreated = createCells(rs, cellStyleMap, sheet);

    if (rowsCreated == DEFAULT_STARTING_ROW_NUMBER) {
        LOGGER.debug("Report is empty");
        return null;
    }//from   w  w  w.  j a va 2  s.c  o  m

    try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
        workbook.write(outputStream);
        return outputStream.toByteArray();
    }
}