Example usage for org.apache.poi.hssf.usermodel HSSFSheet createRow

List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet createRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFSheet
  6. createRow

Introduction

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

Prototype

@Override
public HSSFRow createRow(int rownum)

Source Link

Document

Create a new row within the sheet and return the high level representation

Usage

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 w  w  w.j  a  v a  2 s.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 w ww  .ja  va  2  s  . co  m*/

    try {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(docName);

        String[] str = new String[getLocationList.size()];
        if (getLocationList.size() > 0) {
            for (int i = 0; i < getLocationList.size(); i++) {

                Map m = (Map) getLocationList.get(i);
                str[i] = m.get("Name").toString();

            }
        }

        String[] strDprtmnt = new String[getDepartmnentList.size()];
        if (getDepartmnentList.size() > 0) {
            for (int i = 0; i < getDepartmnentList.size(); i++) {
                Map m = (Map) getDepartmnentList.get(i);
                strDprtmnt[i] = m.get("DeptName").toString();
            }
        }

        String[] strDivision = new String[divisionList.size()];
        if (divisionList.size() > 0) {
            for (int i = 0; i < divisionList.size(); i++) {

                Map m = (Map) divisionList.get(i);
                strDivision[i] = m.get("LegalEntityName").toString();

            }
        }

        /*for(int i=0;i<locationName.size();i++){
           str[i]=locationName.get(i).toString();
        }*/
        //      for(int i=1;i<50;i++){
        //          CellRangeAddressList addressList = new CellRangeAddressList(i, i, 1, 1);
        //           DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(str);
        //           DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
        //           dataValidation.setSuppressDropDownArrow(false);
        //           sheet.addValidationData(dataValidation);
        //           
        //           CellRangeAddressList departmnentList = new CellRangeAddressList(i, i, 0, 0);
        //           DVConstraint dvConstraint1 = DVConstraint.createExplicitListConstraint(strDprtmnt);
        //           DataValidation dataValidation1 = new HSSFDataValidation(departmnentList, dvConstraint1);
        //           dataValidation1.setSuppressDropDownArrow(false);
        //           sheet.addValidationData(dataValidation1);
        //           
        //           
        //           CellRangeAddressList devisionList = new CellRangeAddressList(i, i, 9, 9);
        //           DVConstraint dvConstraint2 = DVConstraint.createExplicitListConstraint(strDivision);
        //           DataValidation dataValidation2 = new HSSFDataValidation(devisionList, dvConstraint2);
        //           dataValidation2.setSuppressDropDownArrow(false);
        //           sheet.addValidationData(dataValidation2);
        //           
        //      }
        //       

        Map<String, String[]> data = new LinkedHashMap<String, String[]>();

        String[] stringVal = { "EmpName", "Department", "Location", "location Country", "location State",
                "location City", "location Contact Name", "location Email", "Divisions", "address", "city",
                "zip", "phone1", "phone2", "Email1", "Email2" };
        //      data.put(key.toString(), { "DepartmentID","LocationID","EmpName","Email", "address","city","state","country","zip","phone1","phone2","divisions","email2","isLicense","isActive","abc"});
        data.put(key.toString(), stringVal);
        Set<String> keyset = data.keySet();
        int rownum = 0;

        sheet.setColumnHidden((short) 17, true);

        for (String key2 : keyset) {
            HSSFRow row = sheet.createRow(rownum++);
            String[] objArr = data.get(key2);
            short cellnum = 0;
            for (String obj : objArr) {

                if (obj instanceof String) {
                    row.createCell(cellnum++).setCellValue((String) obj);

                } else if (obj instanceof String) {
                    row.createCell(cellnum++).setCellValue((String) obj);

                } else if (obj instanceof String)
                    row.createCell(cellnum++).setCellValue((String) obj);
                else if (obj instanceof String)
                    row.createCell(cellnum++).setCellValue((String) obj);
                else if (obj instanceof String)
                    row.createCell(cellnum++).setCellValue((String) obj);
                else if (obj instanceof String)
                    row.createCell(cellnum++).setCellValue((String) obj);
                else if (obj instanceof String)
                    row.createCell(cellnum++).setCellValue((String) obj);
                else if (obj instanceof String)
                    row.createCell(cellnum++).setCellValue((String) obj);
                else if (obj instanceof String)
                    row.createCell(cellnum++).setCellValue((String) obj);
                else if (obj instanceof String)
                    row.createCell(cellnum++).setCellValue((String) obj);
                else if (obj instanceof String)
                    row.createCell(cellnum++).setCellValue((String) obj);
                else if (obj instanceof String)
                    row.createCell(cellnum++).setCellValue((String) obj);
                else if (obj instanceof String)
                    row.createCell(cellnum++).setCellValue((String) obj);
                else if (obj instanceof String)
                    row.createCell(cellnum++).setCellValue((String) obj);
                else if (obj instanceof String)
                    row.createCell(cellnum++).setCellValue((String) obj);
                else if (obj instanceof String)
                    row.createCell(cellnum++).setCellValue((String) obj);
                else if (obj instanceof String)
                    row.createCell(cellnum++).setCellValue((String) obj);
            }

        }

        FileOutputStream out = new FileOutputStream(new File("" + docName + ".xls"));
        workbook.write(out);
        out.close();

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (Exception ex) {
        ex.printStackTrace();
    }

}

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

public void generateAssestDemoXls(HttpServletRequest req, String docName, List<CategoriesBean> getCategoryList,
        List<CountryBean> getCountryList, List<ApplicationsBean> appList2,
        List<BusinessProcessesBean> getBusinessList) throws SQLException, IOException {
    Integer key = 1;/*from w  w  w .jav a 2 s  . c o  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 {/*from  ww w .j a  v  a 2s  .c o  m*/
        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.bdx.rainbow.spsy.service.impl.EnterpriseLicenseServiceImpl.java

/**
 *
 * @param title ??//from   www  .  j a  va  2  s . c  o m
 * @param headers ??
 * @param resultLists ?
 * @param pattern ?,"yyyy-MM-dd"
 */
private HSSFWorkbook exportExcel(String title, String[] headers, List<DubboEnterpriseLicense> resultLists,
        String pattern) {
    if (resultLists == null) {
        return null;
    }
    if (pattern == null && StringUtils.isEmpty(pattern)) {
        pattern = "yyyy-MM-dd";
    }
    SimpleDateFormat sdf = new SimpleDateFormat(pattern);
    // 
    HSSFWorkbook workbook = new HSSFWorkbook();
    // ?
    HSSFSheet sheet = workbook.createSheet(title);
    // 15
    sheet.setDefaultColumnWidth((short) 15);
    // ??
    HSSFCellStyle style = workbook.createCellStyle();
    // ?
    style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    // ?
    HSSFFont font = workbook.createFont();
    font.setColor(HSSFColor.VIOLET.index);
    font.setFontHeightInPoints((short) 12);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    // ??
    style.setFont(font);
    // ???
    HSSFCellStyle style2 = workbook.createCellStyle();
    style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
    style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    // ??
    HSSFFont font2 = workbook.createFont();
    font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
    // ??
    style2.setFont(font2);
    // 
    HSSFRow row = sheet.createRow(0);
    for (short i = 0; i < headers.length; i++) {
        HSSFCell cell = row.createCell(i);
        cell.setCellStyle(style);
        HSSFRichTextString text = new HSSFRichTextString(headers[i]);
        cell.setCellValue(text);
    }
    // ?????
    for (int i = 0; i < resultLists.size(); i++) {
        row = sheet.createRow(i + 1);
        int index = 0;
        //                HSSFCell cell = row.createCell(j);
        //                row.createCell(index++).setCellStyle(style2);
        DubboEnterpriseLicense info = resultLists.get(i);
        row.createCell(index++).setCellValue(info.getEnterpriseName());
        row.createCell(index++).setCellValue(info.getOrganizationCode());
        row.createCell(index++).setCellValue(info.getLegalPerson());
        row.createCell(index++).setCellValue(info.getLegalPersonPhone());
        String type = info.getLicenseType();
        if ("001".equals(type)) {
            row.createCell(index++).setCellValue("");
        } else if ("002".equals(type)) {
            row.createCell(index++).setCellValue("??");
        } else if ("003".equals(type)) {
            row.createCell(index++).setCellValue("");
        } else if ("004".equals(type)) {
            row.createCell(index++).setCellValue("?");
        } else {
            row.createCell(index++).setCellValue(type);
        }

        row.createCell(index++).setCellValue(info.getLicenseCode());
        if (info.getLicenseTime() != null) {
            row.createCell(index++).setCellValue(sdf.format(info.getLicenseTime()));
        } else {
            row.createCell(index++).setCellValue("");
        }
        if (info.getInvalidDate() != null) {
            Long time = (System.currentTimeMillis() - info.getInvalidDate().getTime()) / (1000 * 3600 * 24);
            if (time > 0) {
                row.createCell(index++).setCellValue("");
            } else if (time <= 0) {
                row.createCell(index++).setCellValue("");
            } else {
                row.createCell(index++).setCellValue("1");
            }
        } else {
            row.createCell(index++).setCellValue("");
        }

    }
    return workbook;
}

From source file:com.bean.UserBean.java

public void gerarExcel() {

    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet firstSheet = workbook.createSheet("Aba1");

    HSSFRow row = firstSheet.createRow(0);
    HSSFCell cell = row.createCell(0);// ww w  . j av a2  s.c  o m
    cell.setCellValue("Primeiro valor");
    cell = row.createCell(1);
    cell.setCellValue("Segundo valor");

    row = firstSheet.createRow(1);
    cell = row.createCell(0);
    cell.setCellValue("Segunda linha");

    try {
        FacesContext facesContext = FacesContext.getCurrentInstance();
        ExternalContext externalContext = facesContext.getExternalContext();
        externalContext.setResponseContentType("application/vnd.ms-excel");
        externalContext.setResponseHeader("Content-Disposition", "attachment; filename=\"my.xls\"");

        workbook.write(externalContext.getResponseOutputStream());
        facesContext.responseComplete();

    } catch (Exception e) {
        System.out.println(e);
    }

}

From source file:com.bharath.dao.SelectCourseDao.java

public String getStudentslisttoExcel(String curl) {
    ArrayList<String> arr = new ArrayList<String>();
    ArrayList<String> arr1 = new ArrayList<String>();
    ArrayList<String> arr2 = new ArrayList<String>();
    ArrayList<String> arr3 = new ArrayList<String>();

    try {/* www  . ja  v  a2 s .  c o m*/
        UserAgent userAgent = new UserAgent();
        userAgent.openContent(curl);
        //userAgent.visit(curl);

        Table table = userAgent.doc.getTable(
                "<table class=\"datadisplaytable\" summary=\"This table displays a list of students registered for the course; summary information about each student is provided.\" width=\"100%\">");
        System.out.println(table.toString());
        Elements elements = table.getCol(0);
        for (Element element : elements) {
            arr.add(element.innerText());
            //System.out.println(element.innerText());
        }

        Elements elements1 = table.getCol(2);
        for (Element element : elements1) {
            arr1.add(element.innerText());
            //System.out.println(element.innerText());
        }
        Elements elements2 = table.getCol(3);
        for (Element element : elements2) {
            arr2.add(element.innerText());
            //System.out.println(element.innerText());
        }
        Elements elements3 = table.getCol(4);
        for (Element element : elements3) {
            arr3.add(element.innerText());
            // System.out.println(element.innerText());
        }

        HSSFWorkbook workBook = new HSSFWorkbook();
        HSSFSheet spreadSheet = workBook.createSheet("StudentDetails");
        HSSFRow row;
        HSSFCell cell;
        //int colnum = 0;

        int rowcount = 0;
        //for (int i = 0; i < arr.size(); i++) {
        for (String str : arr) {

            row = spreadSheet.createRow((short) rowcount);
            // System.out.println(arr.get(i));
            cell = row.createCell(0);
            cell.setCellValue(str.trim());
            rowcount++;
            //  colcount++;
        }
        rowcount = 0;
        for (String str : arr1) {

            row = spreadSheet.getRow((short) rowcount);
            // System.out.println(arr.get(i));
            cell = row.createCell(1);
            cell.setCellValue(str.trim());
            rowcount++;
            //  colcount++;
        }
        rowcount = 0;
        for (String str : arr2) {

            row = spreadSheet.getRow((short) rowcount);
            // System.out.println(arr.get(i));
            cell = row.createCell(2);
            cell.setCellValue(str.trim());
            rowcount++;
            //  colcount++;
        }
        rowcount = 0;
        for (String str : arr3) {

            row = spreadSheet.getRow((short) rowcount);
            // System.out.println(arr.get(i));
            cell = row.createCell(3);
            cell.setCellValue(str.trim());
            rowcount++;
            //  colcount++;
        }

        ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
        workBook.write(outByteStream);
        outArray = outByteStream.toByteArray();
        System.out.println(outArray.length);
        //fos = new FileOutputStream("student.xls");
        System.out.println("Done");
        //workBook.write(fos);

    } catch (Exception e) {
        System.out.println(e);
    }
    return "Done";

}

From source file:com.br.uepb.bsc7.www.UI.ManipulaXLS.java

public /*static*/ void criaXLS1(String arqSaida, String nomePlan) throws IOException, NullPointerException {

    int numLinhas = 5;
    int numCelulas = 2;

    //Cria pasta de trabalho
    wb = new HSSFWorkbook();
    //Cria planilha
    HSSFSheet s = wb.createSheet(nomePlan);
    //wb.setSheetName(0, nomePlan);

    //Alterar para a quantidade de linhas retornada por cada relatrio
    for (int i = 0; i < numLinhas; i++) {

        //Cria a linha
        HSSFRow linha = s.createRow(i);

        //Alterar para a quantidade de clulas retornada por cada relatrio
        for (int j = 0; j < numCelulas; j++) {
            HSSFCell celula = linha.createCell(j);

            /*O valor passado deve ser de acordo com aquele recebido por cada relatrio, 
                        provavelmente String;*/
            //celula.setCellValue((String) tabela.getValueAt(i, j));
            celula.setCellValue(i + j);/*from   w  ww .ja  v  a2 s  . c o  m*/
        }
    }

    FileOutputStream fileOutputStream = new FileOutputStream(arqSaida + ".xls");
    try {
        wb.write(fileOutputStream);
        //Fecha o fileOutputStream
        //Melhorar este catch    
    } catch (IOException ex) {
        System.out.println("Teste");
    } finally {
        fileOutputStream.close();
        wb.close(); //Fecha a pasta de trabalho
    }
}

From source file:com.br.uepb.bsc7.www.UI.ManipulaXLS.java

public /*static*/ void criaXLS(JTable tabela, String arqSaida, String nomePlan)
        throws IOException, NullPointerException {
    int numLinhas = tabela.getRowCount();
    int numCelulas = tabela.getColumnCount();

    //Cria pasta de trabalho
    wb = new HSSFWorkbook();
    //Cria planilha
    HSSFSheet s = wb.createSheet(nomePlan);
    //wb.setSheetName(0, nomePlan);

    //Alterar para a quantidade de linhas retornada por cada relatrio
    for (int i = 0; i < numLinhas; i++) {

        //Cria a linha
        HSSFRow linha = s.createRow(i);

        //Alterar para a quantidade de clulas retornada por cada relatrio
        for (int j = 0; j < numCelulas; j++) {
            HSSFCell celula = linha.createCell(j);

            /*O valor passado deve ser de acordo com aquele recebido por cada relatrio, 
                        provavelmente String;*/
            celula.setCellValue((String) tabela.getValueAt(i, j));
        }// www  . j  a va2  s .c  o m
    }

    FileOutputStream fileOutputStream = new FileOutputStream(arqSaida + ".xls");
    try {
        wb.write(fileOutputStream);
        //Fecha o fileOutputStream
        //Melhorar este catch    
    } catch (IOException ex) {
        System.out.println("Teste");
    } finally {
        fileOutputStream.close();
        wb.close(); //Fecha a pasta de trabalho
    }
}

From source file:com.brick.customer.util.CustomerInfoExcel.java

@SuppressWarnings("unchecked")
public HSSFWorkbook createReport(Map<String, Object> params, Context context) throws Exception {

    ExcelFileWriter efw = new ExcelFileWriter();
    HSSFSheet sheet = efw.createSheet(context.contextMap.get("sheetName") == null ? "summary"
            : (String) context.contextMap.get("sheetName"));
    List<HashMap<String, Object>> list = (List<HashMap<String, Object>>) params.get("cusInfo");

    sheet.setColumnWidth(0, 5000);//  www  . j  av  a2s  .  c o m
    sheet.setColumnWidth(1, 3000);
    sheet.setColumnWidth(2, 5300);
    sheet.setColumnWidth(3, 3600);
    sheet.setColumnWidth(4, 4600);
    sheet.setColumnWidth(5, 10000);
    sheet.setColumnWidth(6, 6000);
    sheet.setColumnWidth(7, 4000);
    sheet.setColumnWidth(8, 3500);
    sheet.setColumnWidth(9, 3500);
    sheet.setColumnWidth(10, 3500);
    sheet.setColumnWidth(11, 4200);
    sheet.setColumnWidth(12, 4200);
    sheet.setColumnWidth(13, 4200);
    sheet.setColumnWidth(14, 4800);
    sheet.setColumnWidth(15, 5000);
    sheet.setColumnWidth(16, 5000);
    sheet.setColumnWidth(17, 3000);
    sheet.setColumnWidth(18, 4500);
    sheet.setColumnWidth(19, 4500);
    sheet.setColumnWidth(20, 3000);
    sheet.setColumnWidth(21, 4300);
    sheet.setColumnWidth(22, 4000);
    sheet.setColumnWidth(23, 7000);
    sheet.setColumnWidth(24, 10000);

    HSSFFont headFont0 = null;
    HSSFCellStyle headStyle0 = null;

    headFont0 = efw.getWorkbook().createFont();
    headFont0.setFontHeightInPoints((short) 13); //?
    headFont0.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //    

    headStyle0 = efw.getWorkbook().createCellStyle(); //?
    headStyle0.setAlignment(HSSFCellStyle.ALIGN_CENTER); // ?   
    headStyle0.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //     
    headStyle0.setWrapText(true); // ?  
    headStyle0.setFillBackgroundColor((short) 59);
    headStyle0.setFont(headFont0);

    //??
    HSSFCellStyle cellMoney = efw.getWorkbook().createCellStyle();
    HSSFDataFormat format = efw.getWorkbook().createDataFormat();
    cellMoney.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    cellMoney.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    cellMoney.setDataFormat(format.getFormat("#,###,##0"));

    HSSFRow row0 = sheet.createRow(0);
    HSSFCell cell0 = row0.createCell(0);
    cell0.setCellValue("?");
    cell0.setCellStyle(headStyle0);

    HSSFCell cell1 = row0.createCell(1);
    cell1.setCellValue("??");
    cell1.setCellStyle(headStyle0);

    HSSFCell cell2 = row0.createCell(2);
    cell2.setCellValue("???");
    cell2.setCellStyle(headStyle0);

    HSSFCell cell3 = row0.createCell(3);
    cell3.setCellValue("??");
    cell3.setCellStyle(headStyle0);

    HSSFCell cell4 = row0.createCell(4);
    cell4.setCellValue("?");
    cell4.setCellStyle(headStyle0);

    HSSFCell cell5 = row0.createCell(5);
    cell5.setCellValue("??");
    cell5.setCellStyle(headStyle0);

    HSSFCell cell6 = row0.createCell(6);
    cell6.setCellValue("??");
    cell6.setCellStyle(headStyle0);

    HSSFCell cell7 = row0.createCell(7);
    cell7.setCellValue("?");
    cell7.setCellStyle(headStyle0);

    HSSFCell cell8 = row0.createCell(8);
    cell8.setCellValue("?");
    cell8.setCellStyle(headStyle0);

    HSSFCell cell9 = row0.createCell(9);
    cell9.setCellValue("");
    cell9.setCellStyle(headStyle0);

    HSSFCell cell10 = row0.createCell(10);
    cell10.setCellValue("");
    cell10.setCellStyle(headStyle0);

    HSSFCell cell11 = row0.createCell(11);
    cell11.setCellValue("??");
    cell11.setCellStyle(headStyle0);

    HSSFCell cell12 = row0.createCell(12);
    cell12.setCellValue("?");
    cell12.setCellStyle(headStyle0);

    HSSFCell cell13 = row0.createCell(13);
    cell13.setCellValue("?");
    cell13.setCellStyle(headStyle0);

    HSSFCell cell14 = row0.createCell(14);
    cell14.setCellValue("");
    cell14.setCellStyle(headStyle0);

    HSSFCell cell15 = row0.createCell(15);
    cell15.setCellValue("?");
    cell15.setCellStyle(headStyle0);

    HSSFCell cell16 = row0.createCell(16);
    cell16.setCellValue("??");
    cell16.setCellStyle(headStyle0);

    HSSFCell cell17 = row0.createCell(17);
    cell17.setCellValue("?");
    cell17.setCellStyle(headStyle0);

    HSSFCell cell18 = row0.createCell(18);
    cell18.setCellValue("??");
    cell18.setCellStyle(headStyle0);

    HSSFCell cell19 = row0.createCell(19);
    cell19.setCellValue("?");
    cell19.setCellStyle(headStyle0);

    HSSFCell cell20 = row0.createCell(20);
    cell20.setCellValue("");
    cell20.setCellStyle(headStyle0);

    HSSFCell cell21 = row0.createCell(21);
    cell21.setCellValue("???");
    cell21.setCellStyle(headStyle0);

    HSSFCell cell22 = row0.createCell(22);
    cell22.setCellValue("??");
    cell22.setCellStyle(headStyle0);

    HSSFCell cell23 = row0.createCell(23);
    cell23.setCellValue("??");
    cell23.setCellStyle(headStyle0);

    HSSFCell cell24 = row0.createCell(24);
    cell24.setCellValue("");
    cell24.setCellStyle(headStyle0);

    for (int i = 0; i < list.size(); i++) {
        HSSFRow row1 = sheet.createRow(i + 1);

        HSSFCell cellr0 = row1.createCell(0);
        cellr0.setCellValue((String) list.get(i).get("CUST_CODE"));

        HSSFCell cellr1 = row1.createCell(1);
        cellr1.setCellValue((String) list.get(i).get("NAME"));

        HSSFCell cellr2 = row1.createCell(2);
        cellr2.setCellValue((String) list.get(i).get("CUST_NAME"));

        HSSFCell cellr3 = row1.createCell(3);
        cellr3.setCellValue((String) list.get(i).get("CORP_ORAGNIZATION_CODE"));

        HSSFCell cellr4 = row1.createCell(4);
        cellr4.setCellValue((String) list.get(i).get("CUST_AREA"));

        HSSFCell cellr5 = row1.createCell(5);
        cellr5.setCellValue((String) list.get(i).get("CORP_WORK_ADDRESS"));

        HSSFCell cellr6 = row1.createCell(6);
        cellr6.setCellValue((String) list.get(i).get("VIRTUAL_CODE"));

        HSSFCell cellr7 = row1.createCell(7);
        String s = null;
        int type = Integer.parseInt(list.get(i).get("STATETYPE").toString());
        switch (type) {
        case 0:
            s = "";
            break;
        case 1:
            s = "";
            break;
        case 2:
            s = "??";
            break;
        case 3:
            s = "";
            break;
        case 4:
            s = "";
            break;
        }
        cellr7.setCellValue(s);

        HSSFCell cellr8 = row1.createCell(8);
        cellr8.setCellValue((String) list.get(i).get("CORP_SETUP_DATE"));

        HSSFCell cellr9 = row1.createCell(9);
        double n = list.get(i).get("CORP_REGISTE_CAPITAL") == null ? 0
                : (Double) list.get(i).get("CORP_REGISTE_CAPITAL");
        cellr9.setCellValue(n);
        cellr9.setCellStyle(cellMoney);

        HSSFCell cellr10 = row1.createCell(10);
        double m = list.get(i).get("CORP_PAICLUP_CAPITAL") == null ? 0
                : (Double) list.get(i).get("CORP_PAICLUP_CAPITAL");
        cellr10.setCellValue(m);
        cellr10.setCellStyle(cellMoney);

        HSSFCell cellr11 = row1.createCell(11);
        cellr11.setCellValue((String) list.get(i).get("CORP_BUSINESS_LICENSE"));

        HSSFCell cellr12 = row1.createCell(12);
        cellr12.setCellValue((String) list.get(i).get("TAX_CODE"));

        HSSFCell cellr13 = row1.createCell(13);
        cellr13.setCellValue((String) list.get(i).get("CORP_TAX_CODE"));

        HSSFCell cellr14 = row1.createCell(14);
        cellr14.setCellValue((String) list.get(i).get("CORP_PERIOD_VALIDITY"));

        HSSFCell cellr15 = row1.createCell(15);
        cellr15.setCellValue((String) list.get(i).get("CORP_WORK_ADDRESS"));

        HSSFCell cellr16 = row1.createCell(16);
        cellr16.setCellValue((String) list.get(i).get("CORP_BUSINESS_RANGE"));

        HSSFCell cellr17 = row1.createCell(17);
        cellr17.setCellValue((String) list.get(i).get("CORP_COMPANY_ZIP"));

        HSSFCell cellr18 = row1.createCell(18);
        cellr18.setCellValue((String) list.get(i).get("CORP_COMPANY_WEBSITE"));

        HSSFCell cellr19 = row1.createCell(19);
        cellr19.setCellValue((String) list.get(i).get("CORP_COMPANY_EMAIL"));

        HSSFCell cellr20 = row1.createCell(20);
        cellr20.setCellValue((String) list.get(i).get("CORP_HEAD_SIGNATURE"));

        HSSFCell cellr21 = row1.createCell(21);
        cellr21.setCellValue((String) list.get(i).get("CORP_HS_IDCARD"));

        HSSFCell cellr22 = row1.createCell(22);
        cellr22.setCellValue((String) list.get(i).get("CORP_HS_LINK_MODE"));

        HSSFCell cellr23 = row1.createCell(23);
        cellr23.setCellValue((String) list.get(i).get("CORP_HS_HOME_ADDRESS"));

        HSSFCell cellr24 = row1.createCell(24);
        cellr24.setCellValue((String) list.get(i).get("REMARK"));
    }
    return efw.getWorkbook();
}