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.adonis.ui.print.PrintVehiclesUI.java

public static File createXLSVehicles(String fileName, List<Vehicle> vehicles) {
    try {/*from  www  . ja v a 2 s  .co  m*/
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("Excel Sheet");
        HSSFRow rowhead = sheet.createRow((short) 0);
        rowhead.createCell((short) 0).setCellValue("vehicleNmbr");
        rowhead.createCell((short) 1).setCellValue("licenseNmbr");
        rowhead.createCell((short) 2).setCellValue("make");
        rowhead.createCell((short) 3).setCellValue("vehicleType");
        rowhead.createCell((short) 4).setCellValue("model");
        rowhead.createCell((short) 5).setCellValue("price");
        rowhead.createCell((short) 6).setCellValue("location");

        vehicles.forEach(rent -> {
            HSSFRow row = sheet.createRow((short) index);
            row.createCell((short) 0).setCellValue(rent.getVehicleNmbr());
            row.createCell((short) 1).setCellValue(rent.getLicenseNmbr());
            row.createCell((short) 2).setCellValue(rent.getMake());
            row.createCell((short) 3).setCellValue(rent.getVehicleType());
            row.createCell((short) 4).setCellValue(rent.getModel());
            row.createCell((short) 5).setCellValue(rent.getPrice());
            row.createCell((short) 6).setCellValue(rent.getLocation());
            index++;
        });
        FileOutputStream fileOut = new FileOutputStream(fileName);
        wb.write(fileOut);
        fileOut.close();
        System.out.println("Data is saved in excel file.");

    } catch (Exception ex) {
        ex.printStackTrace();
    }
    return new File(fileName);
}

From source file:com.ah.ui.actions.admin.LicenseMgrAction.java

License:Open Source License

private void exportEntitleKeyInfo() {
    try {/*from  w  w  w . j  a  v  a  2  s. co m*/
        // create a new file
        FileOutputStream out = new FileOutputStream(ORDERKEYINFO_EXPORT_FILE_PATH);
        // create a new workbook
        HSSFWorkbook wb = new HSSFWorkbook();
        // create a new sheet
        HSSFSheet s = wb.createSheet("Sheet1");
        // declare a row object reference
        HSSFRow r = null;
        // declare a cell object reference
        HSSFCell c = null;
        // row index
        int rowNum = 0;
        // cell count
        int cellcount = 0;
        if (NmsUtil.isHMForOEM()) {
            cellcount = 7;
        } else if (getIsInHomeDomain()) {
            cellcount = 8;
        } else if (NmsUtil.isHostedHMApplication()) {
            cellcount = 9;
        }
        if (cellcount == 0) {
            return;
        } else {
            for (int i = 0; i < cellcount; i++) {
                s.setColumnWidth(i, getColumnWidthByIndex(i) * 256);
            }
        }
        // create cell style
        HSSFCellStyle cs = wb.createCellStyle();
        // create font object
        HSSFFont f = wb.createFont();

        //set font to 12 point type
        f.setFontHeightInPoints((short) 12);
        f.setFontName("Calibri");

        //set cell stlye
        cs.setFont(f);
        cs.setAlignment(CellStyle.ALIGN_CENTER);
        cs.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);

        // create cell style
        HSSFCellStyle cs1 = wb.createCellStyle();
        // create font object
        HSSFFont f1 = wb.createFont();

        //set font to 12 point type
        f1.setFontHeightInPoints((short) 12);
        f1.setFontName("Calibri");
        f1.setBoldweight(Font.BOLDWEIGHT_BOLD);

        //set cell stlye
        cs1.setFont(f1);
        cs1.setAlignment(CellStyle.ALIGN_RIGHT);
        cs1.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);

        // create cell style
        HSSFCellStyle cs2 = wb.createCellStyle();
        cs2.setFont(f1);
        cs2.setAlignment(CellStyle.ALIGN_CENTER);
        cs2.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);

        // create cell style
        HSSFCellStyle cs3 = wb.createCellStyle();
        cs3.setFont(f);
        cs3.setAlignment(CellStyle.ALIGN_RIGHT);
        cs3.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);

        // create cell style
        HSSFCellStyle cs4 = wb.createCellStyle();
        cs4.setFont(f1);
        cs4.setAlignment(CellStyle.ALIGN_LEFT);
        cs4.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);

        // create cell style
        HSSFCellStyle cs5 = wb.createCellStyle();
        // create font object
        HSSFFont f2 = wb.createFont();

        //set font to 12 point type
        f2.setFontHeightInPoints((short) 12);
        f2.setFontName("Calibri");
        f2.setBoldweight(Font.BOLDWEIGHT_BOLD);
        f2.setColor(Font.COLOR_RED);
        cs5.setFont(f2);
        cs5.setAlignment(CellStyle.ALIGN_LEFT);
        cs5.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);

        // create cell style
        HSSFCellStyle cs6 = wb.createCellStyle();
        cs6.setFont(f);
        cs6.setAlignment(CellStyle.ALIGN_LEFT);
        cs6.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);

        // create a row
        // row 1
        s.addMergedRegion(new CellRangeAddress(0, 0, 0, cellcount - 1));
        r = s.createRow(rowNum++);
        c = r.createCell(0);
        c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.title"));
        c.setCellStyle(cs);

        // row 2
        String sysInfo = "";
        if (getIsInHomeDomain()) {
            sysInfo = "System ID: " + getSystemId();
            // HiveManager Online user
        } else if (NmsUtil.isHostedHMApplication()) {
            sysInfo = "VHM ID: " + getDomain().getVhmID();
        }
        s.addMergedRegion(new CellRangeAddress(1, 1, 0, cellcount - 1));
        r = s.createRow(rowNum++);
        c = r.createCell(0);
        c.setCellValue(sysInfo);
        c.setCellStyle(cs);

        // row 3
        s.addMergedRegion(new CellRangeAddress(2, 2, 0, cellcount - 1));
        r = s.createRow(rowNum++);
        c = r.createCell(0);
        List<?> userInfo = QueryUtil.executeQuery(
                "SELECT email, company FROM " + UserRegInfoForLs.class.getSimpleName(), null,
                new FilterParams("owner.domainName", getDomain().getDomainName()));
        if (!userInfo.isEmpty()) {
            Object[] userInfos = (Object[]) userInfo.get(0);
            c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.email.company",
                    new String[] { (String) userInfos[0], (String) userInfos[1] }));
        }
        c.setCellStyle(cs);

        // row 4
        r = s.createRow(rowNum++);

        // row 5
        r = s.createRow(rowNum++);

        // row 6 cell 1
        c = r.createCell(0);
        c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.device.licensed"));
        c.setCellStyle(cs1);

        // row 6 cell 2
        c = r.createCell(1);
        c.setCellValue(licenseInfo.getHiveAps());
        c.setCellStyle(cs2);

        // device management info
        Map<String, Integer> apcount = HiveAPInfoFromeDatabase
                .getManagedDeviceTypeAndNumber(getDomain().getDomainName());
        int vpnCount = 0;
        int totalCount = 0;
        if (null != apcount) {
            Object[] typeNames = apcount.keySet().toArray();
            Arrays.sort(typeNames);
            for (Object typeName : typeNames) {

                // VPN Gateway VA does not belong to device
                if (MgrUtil.getEnumString("enum.hiveAp.model.10").equals(typeName)) {
                    vpnCount = apcount.get(typeName);
                } else {
                    r = s.createRow(rowNum++);
                    c = r.createCell(0);
                    c.setCellValue((String) typeName);
                    c.setCellStyle(cs3);

                    c = r.createCell(1);
                    c.setCellValue(apcount.get(typeName));
                    c.setCellStyle(cs);
                    totalCount += apcount.get(typeName);
                }
            }

        }

        // managed device total number cell1
        r = s.createRow(rowNum++);
        c = r.createCell(0);
        c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.device.total"));
        c.setCellStyle(cs1);

        // managed device total number cell2
        c = r.createCell(1);
        c.setCellValue(totalCount);
        c.setCellStyle(cs2);

        // blank row
        r = s.createRow(rowNum++);

        // licensed VPN Gateway VA cell1
        r = s.createRow(rowNum++);
        c = r.createCell(0);
        c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.cvg.licensed"));
        c.setCellStyle(cs1);

        // licensed VPN Gateway VA cell2
        c = r.createCell(1);
        c.setCellValue(licenseInfo.getCvgNumber());
        c.setCellStyle(cs2);

        // managed VPN Gateway VA cell1
        r = s.createRow(rowNum++);
        c = r.createCell(0);
        c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.cvg.total"));
        c.setCellStyle(cs1);

        // managed VPN Gateway VA cell 2
        c = r.createCell(1);
        c.setCellValue(vpnCount);
        c.setCellStyle(cs2);

        // entitlement key information
        preparePage();
        if (null != page && !page.isEmpty()) {
            r = s.createRow(rowNum++);
            List<OrderHistoryInfo> normalKey = new ArrayList<>();
            List<OrderHistoryInfo> invalidKey = new ArrayList<>();
            List<OrderHistoryInfo> expiredKey = new ArrayList<>();

            for (Object obj : page) {
                OrderHistoryInfo orderInfo = (OrderHistoryInfo) obj;
                if (orderInfo.getStatusFlag() == OrderHistoryInfo.ENTITLE_KEY_STATUS_NORMAL
                        && orderInfo.getCvgStatusFlag() == OrderHistoryInfo.ENTITLE_KEY_STATUS_NORMAL) {
                    normalKey.add(orderInfo);
                } else if (orderInfo.getStatusFlag() == OrderHistoryInfo.ENTITLE_KEY_STATUS_DISABLE
                        || orderInfo.getCvgStatusFlag() == OrderHistoryInfo.ENTITLE_KEY_STATUS_DISABLE) {
                    invalidKey.add(orderInfo);
                } else {
                    expiredKey.add(orderInfo);
                }
            }

            if (!normalKey.isEmpty()) {

                // normal entitle key title
                r = s.createRow(rowNum++);
                setEntitlementKeyCellValue(cellcount, cs4, null, r, null, MgrUtil.getUserMessage("order.key"));

                // normal entitle key info
                for (OrderHistoryInfo keyInfo : normalKey) {
                    r = s.createRow(rowNum++);
                    setEntitlementKeyCellValue(cellcount, cs6, cs3, r, keyInfo, null);
                }
            }

            if (!invalidKey.isEmpty()) {

                if (!normalKey.isEmpty()) {
                    r = s.createRow(rowNum++);
                    r = s.createRow(rowNum++);
                }

                // invalid entitle key title
                r = s.createRow(rowNum++);
                setEntitlementKeyCellValue(cellcount, cs5, null, r, null,
                        MgrUtil.getUserMessage("admin.license.orderkey.export.invalidkey.title"));

                // invalid entitle key info
                for (OrderHistoryInfo keyInfo : invalidKey) {
                    r = s.createRow(rowNum++);
                    setEntitlementKeyCellValue(cellcount, cs6, cs3, r, keyInfo, null);
                }
            }

            if (!expiredKey.isEmpty()) {

                if (!normalKey.isEmpty() || !invalidKey.isEmpty()) {
                    r = s.createRow(rowNum++);
                    r = s.createRow(rowNum++);
                }

                // expired entitle key title
                r = s.createRow(rowNum++);
                setEntitlementKeyCellValue(cellcount, cs5, null, r, null,
                        MgrUtil.getUserMessage("admin.license.orderkey.export.expiredkey.title"));

                // expired entitle key info
                for (OrderHistoryInfo keyInfo : expiredKey) {
                    r = s.createRow(rowNum++);
                    setEntitlementKeyCellValue(cellcount, cs6, cs3, r, keyInfo, null);
                }
            }
        }

        // write the workbook to the output stream
        // close our file (don't blow out our file handles)
        wb.write(out);
        out.close();
        generateAuditLog(HmAuditLog.STATUS_SUCCESS,
                MgrUtil.getUserMessage("hm.audit.log.export.entitlement.key"));
    } catch (Exception ex) {
        generateAuditLog(HmAuditLog.STATUS_FAILURE,
                MgrUtil.getUserMessage("hm.audit.log.export.entitlement.key"));
        log.error("exportEntitleKeyInfo()", ex.getMessage());
    }
}

From source file:com.aimluck.eip.modules.screens.ALXlsScreen.java

License:Open Source License

@Override
protected void doOutput(RunData rundata) throws Exception {
    VelocityContext context = new VelocityContext();

    try {// ww  w .j  av  a  2 s  .  co  m
        init(rundata, context);

        doCheckAclPermission(rundata, context, getDefineAclType());

        // xls??
        HSSFWorkbook wb = createXLSFile(rundata, context);

        HttpServletResponse response = rundata.getResponse();
        // ????
        response.setHeader("Content-disposition", "attachment; filename=\"" + getFileName() + "\"");
        response.setHeader("Cache-Control", "aipo");
        response.setHeader("Pragma", "aipo");

        if (wb != null) {
            wb.write(response.getOutputStream());
        }
    } catch (ALPermissionException e) {
        ALEipUtils.redirectPermissionError(rundata);
    } catch (Exception e) {
        logger.error("ALXlsScreen.doOutput", e);
    }
}

From source file:com.alibaba.differ.biz.TableExporter.java

License:Open Source License

public void export() throws IOException {
    fc.setFileFilter(new ExcelFileFilter());
    fc.setFileHidingEnabled(true);/*from   w w w . j  ava 2  s  .c  om*/
    fc.setAcceptAllFileFilterUsed(false);
    int returnValue = fc.showSaveDialog(null);
    if (returnValue != JFileChooser.APPROVE_OPTION) {
        return;
    }
    File file = fc.getSelectedFile();
    if (file.exists()) {
        JOptionPane.showMessageDialog(null, "");
        return;
    }
    FileOutputStream fos = new FileOutputStream(file + ".xls");
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet hs = wb.createSheet();
    TableModel tm = table.getModel();
    int row = tm.getRowCount();
    int cloumn = tm.getColumnCount();
    HSSFCellStyle style = wb.createCellStyle();
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    HSSFFont font = wb.createFont();
    font.setFontHeightInPoints((short) 11);
    style.setFont(font);
    HSSFCellStyle style1 = wb.createCellStyle();
    style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style1.setFillForegroundColor(HSSFColor.ORANGE.index);
    style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    HSSFFont font1 = wb.createFont();
    font1.setFontHeightInPoints((short) 15);
    font1.setBoldweight((short) 700);
    style1.setFont(font);

    for (int i = 0; i < row + 1; i++) {
        HSSFRow hr = hs.createRow(i);
        for (int j = 0; j < cloumn; j++) {
            if (i == 0) {
                String value = tm.getColumnName(j);
                hs.setColumnWidth(j, UIConfig.EXCEL_COLUMN_WIDTH);
                HSSFRichTextString srts = new HSSFRichTextString(value);
                HSSFCell hc = hr.createCell((short) j);
                hc.setCellStyle(style1);
                hc.setCellValue(srts);
            } else {
                if (tm.getValueAt(i - 1, j) != null) {
                    String value = tm.getValueAt(i - 1, j).toString();
                    HSSFRichTextString srts = new HSSFRichTextString(value);
                    HSSFCell hc = hr.createCell((short) j);
                    hc.setCellStyle(style);

                    if (value.equals("") || value == null) {
                        hc.setCellValue(new HSSFRichTextString(""));
                    } else {
                        hc.setCellValue(srts);
                    }
                }
            }
        }
    }
    wb.write(fos);
    fos.close();
    JOptionPane.showMessageDialog(null, "Excel");
}

From source file:com.anphat.list.controller.ListStaffController.java

public File exportFile(List<StaffDTO> lstStaffInfo) {
    try {//from   w  ww  . j  a  v  a2 s .  c  o  m
        FileOutputStream fileOut = new FileOutputStream(PATH_EXPORT + Constants.FILE_NAME.LIST_STAFF);
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet worksheet = workbook.createSheet("danhsachnhanvien");
        HSSFCellStyle cellStyle;
        HSSFCellStyle rowStyle;

        HSSFCellStyle cellStyleLeft = null;
        HSSFCellStyle cellStyleRight = null;
        //style left
        cellStyleLeft = workbook.createCellStyle();
        cellStyleLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        //phai
        cellStyleRight = workbook.createCellStyle();
        cellStyleRight.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        cellStyleRight.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyleRight.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyleRight.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyleRight.setBorderTop(HSSFCellStyle.BORDER_THIN);

        //                //header bang
        HSSFRow row5 = worksheet.createRow(0);

        HSSFCell cellA1 = row5.createCell(0);
        cellA1.setCellValue(BundleUtils.getString("STT"));
        cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellStyle.setWrapText(true);
        //rowstyle
        rowStyle = workbook.createCellStyle();
        rowStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        rowStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        rowStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        rowStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        rowStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        rowStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        rowStyle.setFillForegroundColor(HSSFColor.WHITE.index);
        rowStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        rowStyle.setWrapText(true);

        cellA1.setCellStyle(cellStyle);

        HSSFCell cellB1 = row5.createCell(1);
        cellB1.setCellValue(BundleUtils.getString("cms.StaffInfo.code"));
        cellB1.setCellStyle(cellStyle);

        HSSFCell cellC1 = row5.createCell(2);
        cellC1.setCellValue(BundleUtils.getString("cms.StaffInfo.name"));
        cellC1.setCellStyle(cellStyle);
        HSSFCell cellD1 = row5.createCell(3);
        cellD1.setCellValue(BundleUtils.getString("cms.StaffInfo.email"));
        cellD1.setCellStyle(cellStyle);
        HSSFCell cellE1 = row5.createCell(4);
        cellE1.setCellValue(BundleUtils.getString("cms.StaffInfo.birthdate"));
        cellE1.setCellStyle(cellStyle);
        HSSFCell cellF1 = row5.createCell(5);
        cellF1.setCellValue(BundleUtils.getString("cms.StaffInfo.phoneNumber"));
        cellF1.setCellStyle(cellStyle);
        HSSFCell cellG1 = row5.createCell(6);
        cellG1.setCellValue(BundleUtils.getString("cms.StaffInfo.deptName"));
        cellG1.setCellStyle(cellStyle);
        HSSFCell cellH1 = row5.createCell(7);
        cellH1.setCellValue(BundleUtils.getString("cms.StaffInfo.staffType"));
        cellH1.setCellStyle(cellStyle);
        HSSFCell cellI1 = row5.createCell(8);
        cellI1.setCellValue(BundleUtils.getString("cms.StaffInfo.status"));
        cellI1.setCellStyle(cellStyle);
        //content bang
        if (!lstStaffInfo.isEmpty()) {
            int j = 0;
            for (StaffDTO staff : lstStaffInfo) {
                HSSFRow row = worksheet.createRow(j + 1);

                HSSFCell cellA = row.createCell(0);
                cellA.setCellValue(j + 1);
                cellA.setCellStyle(rowStyle);
                HSSFCell cellB = row.createCell(1);
                cellB.setCellValue((staff.getDeptId() == null) ? Constants.NULL : staff.getCode());
                cellB.setCellStyle(cellStyleLeft);
                HSSFCell cellC = row.createCell(2);
                cellC.setCellValue((staff.getCode() == null) ? Constants.NULL : staff.getName());
                cellC.setCellStyle(cellStyleLeft);
                HSSFCell cellD = row.createCell(3);
                cellD.setCellValue((staff.getName() == null) ? Constants.NULL : staff.getEmail());
                cellD.setCellStyle(cellStyleLeft);
                HSSFCell cellE = row.createCell(4);
                //                    cellE.setCellValue((staff.getEmail() == null) ? Constants.NULL : staff.getBirthDate());
                cellE.setCellStyle(cellStyleLeft);
                HSSFCell cellF = row.createCell(5);
                cellF.setCellValue((staff.getTelNumber() == null) ? Constants.NULL : staff.getTelNumber());
                cellF.setCellStyle(cellStyleLeft);
                HSSFCell cellG = row.createCell(6);
                //                    cellG.setCellValue((staff.getDeptName() == null) ? Constants.NULL : staff.getDeptName());
                cellG.setCellStyle(cellStyleLeft);
                HSSFCell cellH = row.createCell(7);
                cellH.setCellValue((staff.getStaffType() == null) ? Constants.NULL
                        : BundleUtils.getString(
                                "staff.type." + DataUtil.getStringEscapeHTML4(staff.getStaffType())));
                cellH.setCellStyle(cellStyleLeft);
                HSSFCell cellI = row.createCell(8);
                cellI.setCellValue((staff.getStatus() == null) ? Constants.NULL
                        : BundleUtils.getString(
                                "common.status." + DataUtil.getStringEscapeHTML4(staff.getStatus())));
                cellI.setCellStyle(cellStyleLeft);
                j++;
            }
            //Set Width
            for (int i = 0; i <= 0; i++) {
                worksheet.setColumnWidth(i, 2000);
            }
            for (int i = 1; i <= 7; i++) {
                worksheet.setColumnWidth(i, 5000);
            }
            for (int i = 8; i <= 10; i++) {
                worksheet.setColumnWidth(i, 3000);
            }
        }
        try {
            workbook.write(fileOut);
            fileOut.flush();
            fileOut.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    }
    File file = new File(PATH_EXPORT + Constants.FILE_NAME.LIST_STAFF);
    return file;

}

From source file:com.assentisk.util.FilesService.java

public void generatePositionProfileXls(HttpServletRequest req, String Doc_name, List catList)
        throws SQLException, IOException {
    Integer key = 1;//from ww  w . j  av  a  2s.  co  m

    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;/*from   www . j  a v a2s .  c o  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;//ww w  . ja va 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.astrocytes.core.statistics.LayerStatistics.java

License:Open Source License

public boolean saveLayerStatisticsToXls(Map<Integer, List<Point>> layers, List<Point> astrocyteCenters,
        List<Point> neuronsCenters, File fileToSave) {
    try {/*  w w w.j ava2  s.  c om*/
        FileOutputStream outputStream = new FileOutputStream(fileToSave);

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(CoreConstants.XLS_SPREADSHEET_TITLE);

        HSSFRow headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue(CoreConstants.XLS_LAYERS_TITLE);

        for (int i = 0; i < layers.size() - 1; i++) {
            HSSFRow row = sheet.createRow(i + 1);
            HSSFCell cell = row.createCell(0);
            cell.setCellType(CellType.STRING);
            cell.setCellValue(String.valueOf(i < 1 ? i + 1 : (i == 1 ? "2 - 3" : i + 2)));
        }

        if (astrocyteCenters != null) {
            headerRow.createCell(1).setCellValue(CoreConstants.XLS_ASTROCYTES_TITLE);

            for (Map.Entry<Integer, Integer> count : count(astrocyteCenters, layers).entrySet()) {
                HSSFRow row = sheet.getRow(count.getKey() + 1);
                HSSFCell cell = row.createCell(1);
                cell.setCellType(CellType.NUMERIC);
                cell.setCellValue(count.getValue());
            }
        }

        if (neuronsCenters != null) {
            int colIdx = astrocyteCenters != null ? 2 : 1;
            headerRow.createCell(colIdx).setCellValue(CoreConstants.XLS_NEURONS_TITLE);

            for (Map.Entry<Integer, Integer> count : count(neuronsCenters, layers).entrySet()) {
                HSSFRow row = sheet.getRow(count.getKey() + 1);
                HSSFCell cell = row.createCell(astrocyteCenters != null ? 2 : 1);
                cell.setCellType(CellType.NUMERIC);
                cell.setCellValue(count.getValue());
            }
        }

        sheet.autoSizeColumn(0);
        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(2);
        workbook.write(outputStream);
        outputStream.close();
    } catch (IOException e) {
        e.printStackTrace();
        return false;
    }

    return true;
}

From source file:com.b510.excel.client.HSSFReadWrite.java

License:Apache License

/**
 * Method main/*w w  w .j a v a 2  s. c  o  m*/
 *
 * Given 1 argument takes that as the filename, inputs it and dumps the cell
 * values/types out to sys.out.<br/>
 *
 * given 2 arguments where the second argument is the word "write" and the
 * first is the filename - writes out a sample (test) spreadsheet see
 * {@link HSSFReadWrite#testCreateSampleSheet(String)}.<br/>
 *
 * given 2 arguments where the first is an input filename and the second an
 * output filename (not write), attempts to fully read in the spreadsheet
 * and fully write it out.<br/>
 *
 * given 3 arguments where the first is an input filename and the second an
 * output filename (not write) and the third is "modify1", attempts to read
 * in the spreadsheet, deletes rows 0-24, 74-99. Changes cell at row 39, col
 * 3 to "MODIFIED CELL" then writes it out. Hence this is "modify test 1".
 * If you take the output from the write test, you'll have a valid scenario.
 */
public static void main(String[] args) {
    String[] name = new String[2];
    name[0] = "HSSFReadWrite.xlsx";
    name[1] = "write";
    if (name.length < 1) {
        System.err.println("At least one argument expected");
        return;
    }
    String fileName = name[0];
    try {
        if (name.length < 2) {

            HSSFWorkbook wb = HSSFReadWrite.readFile(fileName);

            System.out.println("Data dump:\n");

            for (int k = 0; k < wb.getNumberOfSheets(); k++) {
                HSSFSheet sheet = wb.getSheetAt(k);
                int rows = sheet.getPhysicalNumberOfRows();
                System.out.println("Sheet " + k + " \"" + wb.getSheetName(k) + "\" has " + rows + " row(s).");
                for (int r = 0; r < rows; r++) {
                    HSSFRow row = sheet.getRow(r);
                    if (row == null) {
                        continue;
                    }

                    int cells = row.getPhysicalNumberOfCells();
                    System.out.println("\nROW " + row.getRowNum() + " has " + cells + " cell(s).");
                    for (int c = 0; c < cells; c++) {
                        HSSFCell cell = row.getCell(c);
                        String value = null;

                        switch (cell.getCellType()) {

                        case HSSFCell.CELL_TYPE_FORMULA:
                            value = "FORMULA value=" + cell.getCellFormula();
                            break;

                        case HSSFCell.CELL_TYPE_NUMERIC:
                            value = "NUMERIC value=" + cell.getNumericCellValue();
                            break;

                        case HSSFCell.CELL_TYPE_STRING:
                            value = "STRING value=" + cell.getStringCellValue();
                            break;

                        default:
                        }
                        System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value);
                    }
                }
            }
        } else if (name.length == 2) {
            if (name[1].toLowerCase().equals("write")) {
                System.out.println("Write mode");
                long time = System.currentTimeMillis();
                HSSFReadWrite.testCreateSampleSheet(fileName);

                System.out.println("" + (System.currentTimeMillis() - time) + " ms generation time");
            } else {
                System.out.println("readwrite test");
                HSSFWorkbook wb = HSSFReadWrite.readFile(fileName);
                FileOutputStream stream = new FileOutputStream(name[1]);

                wb.write(stream);
                stream.close();
            }
        } else if (name.length == 3 && name[2].toLowerCase().equals("modify1")) {
            // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to
            // string "MODIFIED CELL!!"

            HSSFWorkbook wb = HSSFReadWrite.readFile(fileName);
            FileOutputStream stream = new FileOutputStream(name[1]);
            HSSFSheet sheet = wb.getSheetAt(0);

            for (int k = 0; k < 25; k++) {
                HSSFRow row = sheet.getRow(k);

                sheet.removeRow(row);
            }
            for (int k = 74; k < 100; k++) {
                HSSFRow row = sheet.getRow(k);

                sheet.removeRow(row);
            }
            HSSFRow row = sheet.getRow(39);
            HSSFCell cell = row.getCell(3);
            cell.setCellValue("MODIFIED CELL!!!!!");

            wb.write(stream);
            stream.close();
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}