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.fota.statMgt.controller.StatDevExcelDown.java

License:Open Source License

@Override
protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
    String date = DateTimeUtil.getCurrentDate();
    String formNm = "?";
    String fileName = date + "_" + formNm;
    String sheetName = "sheet1";

    response.setContentType("application/vnd.ms-excel;charset=UTF-8");
    response.setHeader("Content-disposition",
            "attachent; filename=" + new String((fileName).getBytes("KSC5601"), "8859_1") + ".xls");

    @SuppressWarnings("unchecked")
    List<StatDevSearchVO> resultData = (List<StatDevSearchVO>) model.get("resultData");

    HSSFSheet sheet = workbook.createSheet(sheetName);

    //  ?/*from w  ww.ja  va 2s  . c  o  m*/
    HSSFRow header = sheet.createRow(0);
    header.createCell(0).setCellValue("FOTA TYPE");
    header.createCell(1).setCellValue("");
    header.createCell(2).setCellValue("");
    header.createCell(3).setCellValue("?");
    header.createCell(4).setCellValue("??");
    header.createCell(5).setCellValue("??");
    header.createCell(6).setCellValue("??");
    header.createCell(7).setCellValue("??");
    header.createCell(8).setCellValue("??");

    int index = 0;
    for (int i = 0; i < resultData.size(); i++) {
        StatDevSearchVO fvo = resultData.get(i);
        HSSFRow row = sheet.createRow(++index);
        row.createCell(0).setCellValue(fvo.getFotaType());
        row.createCell(1).setCellValue(fvo.getBizNm());
        row.createCell(2).setCellValue(fvo.getSvcNm());
        row.createCell(3).setCellValue(fvo.getDevMakerNm());
        row.createCell(4).setCellValue(fvo.getDevModelNm());
        row.createCell(5).setCellValue(fvo.getDevCntDtFrom());
        row.createCell(6).setCellValue(fvo.getDevCntDtTo());
        row.createCell(7).setCellValue(fvo.getIncDevCnt());
        row.createCell(8).setCellValue(fvo.getSumDevCntNow());
    }

    workbook.write(response.getOutputStream());
    response.getOutputStream().close();
}

From source file:com.fota.statMgt.controller.StatFotaExcelDown.java

License:Open Source License

@Override
protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
    String date = DateTimeUtil.getCurrentDate();
    String formNm = "FOTA";
    String fileName = date + "_" + formNm;
    String sheetName = "sheet1";

    response.setContentType("application/vnd.ms-excel;charset=UTF-8");
    response.setHeader("Content-disposition",
            "attachent; filename=" + new String((fileName).getBytes("KSC5601"), "8859_1") + ".xls");

    @SuppressWarnings("unchecked")
    List<StatFotaSearchVO> resultData = (List<StatFotaSearchVO>) model.get("resultData");

    HSSFSheet sheet = workbook.createSheet(sheetName);

    //  ?//from  www  . j a va 2  s.  com
    HSSFRow header = sheet.createRow(0);
    header.createCell(0).setCellValue("??");
    header.createCell(1).setCellValue("FOTA TYPE");
    header.createCell(2).setCellValue("");
    header.createCell(3).setCellValue("");
    header.createCell(4).setCellValue("?");
    header.createCell(5).setCellValue("??");
    header.createCell(6).setCellValue("");
    header.createCell(7).setCellValue("?");
    header.createCell(8).setCellValue("");
    header.createCell(9).setCellValue("?");
    header.createCell(10).setCellValue("");
    header.createCell(11).setCellValue("");
    header.createCell(12).setCellValue("");
    header.createCell(13).setCellValue("");
    header.createCell(14).setCellValue("(%)");

    int index = 0;
    for (int i = 0; i < resultData.size(); i++) {
        StatFotaSearchVO fvo = resultData.get(i);
        HSSFRow row = sheet.createRow(++index);
        row.createCell(0).setCellValue(fvo.getCretDt());
        row.createCell(1).setCellValue(fvo.getFotaType());
        row.createCell(2).setCellValue(fvo.getBizNm());
        row.createCell(3).setCellValue(fvo.getSvcNm());
        row.createCell(4).setCellValue(fvo.getDevMakerNm());
        row.createCell(5).setCellValue(fvo.getDevModelNm());
        row.createCell(6).setCellValue(fvo.getFirmwareMakerVer());
        row.createCell(7).setCellValue(fvo.getEarlyCnt());
        row.createCell(8).setCellValue(fvo.getWaitCnt());
        row.createCell(9).setCellValue(fvo.getVersionChkCnt());
        row.createCell(10).setCellValue(fvo.getProcessCnt());
        row.createCell(11).setCellValue(fvo.getSuccessCnt());
        row.createCell(12).setCellValue(fvo.getFailCnt());
        row.createCell(13).setCellValue(fvo.getTotal());
        row.createCell(14).setCellValue(fvo.getSuccessRate());
    }

    workbook.write(response.getOutputStream());
    response.getOutputStream().close();
}

From source file:com.frameworkset.platform.sanylog.util.POIExcelUtil.java

License:Open Source License

/**
 * Excel Workbook?./*www.j  a v  a  2 s . c  o m*/
 * 
 * @param colDesc 17"?:user_id,??:user_name,:type_name"
 * @param dataList
 * @return
 * @author gw_liaozh
 * @throws InvocationTargetException 
 * @throws IllegalAccessException 
 * @throws IllegalArgumentException 
 */
public static HSSFWorkbook createHSSFWorkbook(String colDesc, List<?> dataList)
        throws IllegalArgumentException, IllegalAccessException, InvocationTargetException {
    //???17
    //TODO: ?
    List<String> colTitleList = getColumnTitleList(colDesc);
    List<String> colFieldList = getColumnFieldList(colDesc);

    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();

    HSSFFont font = getBaseFont(wb);
    HSSFCellStyle headCellStyle = getHeadCellStyle(wb, font);

    //?
    CellStyle dateCellStyle = getDateTimeCellStyle(wb);

    //CellStyle strCellStyle = getStringCellStyle(wb);

    //??17
    HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet);
    Map<String, Class<?>> fieldTypeMap = new HashMap<String, Class<?>>();

    //
    HSSFRow titleRow = sheet.createRow(0);
    for (int i = 0; i < colTitleList.size(); i++) {
        HSSFCell cell = titleRow.createCell(i);
        cell.setCellStyle(headCellStyle);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cell.setCellValue(colTitleList.get(i));
    }

    //?17
    for (int i = 0; i < dataList.size(); i++) {
        Object obj = dataList.get(i);
        HSSFRow row = sheet.createRow(i + 1);
        for (int j = 0; j < colFieldList.size(); j++) {
            String fieldName = colFieldList.get(j);
            HSSFCell cell = row.createCell(j);
            if (obj == null) {
                continue;
            }
            Object value = BeanConvertUtil.getProperty(obj, fieldName);
            //ClassInfo classInfo = ClassUtil.getClassInfo(obj.getClass());
            //Object value = classInfo.getPropertyDescriptor(fieldName).getValue(obj);
            if (value == null) {
                continue;
            }
            //??
            if (value instanceof Number) {
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(((Number) value).doubleValue());
            } else if (value instanceof Date) {
                cell.setCellStyle(dateCellStyle);
                cell.setCellValue((Date) value);
            } else {
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                //cell.setCellStyle(strCellStyle);
                cell.setCellValue(value.toString());
            }
            fieldTypeMap.put(fieldName, value.getClass());
        }
    }

    //??
    for (int i = 0; i < colFieldList.size(); i++) {
        String fieldName = colFieldList.get(i);
        Class<?> fieldClass = fieldTypeMap.get(fieldName);
        if (fieldClass == null) {
            continue;
        }
        CellRangeAddressList range = new CellRangeAddressList(1, 65535, i, i);
        DataValidationConstraint constraint = null;
        if (Integer.class.isAssignableFrom(fieldClass)) {
            constraint = dvHelper.createIntegerConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN,
                    "0", "-1");
            sheet.addValidationData(dvHelper.createValidation(constraint, range));
        } else if (Number.class.isAssignableFrom(fieldClass)) {
            constraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.DECIMAL,
                    DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1");
            sheet.addValidationData(dvHelper.createValidation(constraint, range));
        } else if (Date.class.isAssignableFrom(fieldClass)) {
            constraint = dvHelper.createDateConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN,
                    "0000-01-02", "0000-01-01", "yyyy-MM-dd");
            sheet.addValidationData(dvHelper.createValidation(constraint, range));
        }
    }

    //
    for (int i = 0; i < colTitleList.size(); i++) {
        //??
        //sheet.autoSizeColumn(i);
    }

    return wb;
}

From source file:com.fufang.bi.controllers.ChainReportController.java

/***
 * ?excel//w  w  w  . j a  va2  s .  c o  m
 * @param orderType
 * @param list
 * @param inputStream
 * @return
 */
public HSSFWorkbook createUploadSplitExcel(List<?> list, InputStream inputStream, Integer select, Object obj) {
    try {
        HSSFWorkbook workbook = null;
        workbook = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = null;//
        sheet = workbook.getSheetAt(0);//

        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setLocked(false);
        HSSFRow row = null;
        HSSFCell cell = null;
        if (list != null && list.size() > 0) {
            for (int i = 0, size = list.size(); i < size; i++) {
                HSSFRow rows = null;
                rows = sheet.createRow(i + 5);
                if (select == 3) {
                    row = sheet.createRow(i + 3);
                } else {
                    row = sheet.createRow(i + 2);
                }

                if (select == 1) {
                    StorageTotal data = (StorageTotal) list.get(i);
                    Converter.setCellText(row, cell, 0, Converter.toBlank(data.getId()), cellStyle);
                    Converter.setCellText(row, cell, 1, Converter.toBlank(data.getPharmacycode()), cellStyle);
                    Converter.setCellText(row, cell, 2, Converter.toBlank(data.getName()), cellStyle);
                    Converter.setCellText(row, cell, 3, Converter.toBlank(data.getQckc()), cellStyle);
                    Converter.setCellText(row, cell, 4, Converter.toBlank(data.getQckcje()), cellStyle);
                    Converter.setCellText(row, cell, 5, Converter.toBlank(data.getQjrk()), cellStyle);
                    Converter.setCellText(row, cell, 6, Converter.toBlank(data.getQjrkje()), cellStyle);
                    Converter.setCellText(row, cell, 7, Converter.toBlank(data.getQjck()), cellStyle);
                    Converter.setCellText(row, cell, 8, Converter.toBlank(data.getQjckje()), cellStyle);
                    Converter.setCellText(row, cell, 9, Converter.toBlank(data.getQmkc()), cellStyle);
                    Converter.setCellText(row, cell, 10, Converter.toBlank(data.getQmkcje()), cellStyle);
                }
                if (select == 2) {
                    StorageMilde data = (StorageMilde) list.get(i);
                    Converter.setCellText(row, cell, 0, Converter.toBlank(data.getId()), cellStyle);
                    Converter.setCellText(row, cell, 1, Converter.toBlank(data.getPharmacycode()), cellStyle);
                    Converter.setCellText(row, cell, 2, Converter.toBlank(data.getName()), cellStyle);
                    Converter.setCellText(row, cell, 3, Converter.toBlank(data.getMatcode()), cellStyle);
                    Converter.setCellText(row, cell, 4, Converter.toBlank(data.getBarcode()), cellStyle);
                    Converter.setCellText(row, cell, 5, Converter.toBlank(data.getMatname()), cellStyle);
                    Converter.setCellText(row, cell, 6, Converter.toBlank(data.getCommonname()), cellStyle);
                    Converter.setCellText(row, cell, 7, Converter.toBlank(data.getDosage()), cellStyle);
                    Converter.setCellText(row, cell, 8, Converter.toBlank(data.getSpec()), cellStyle);
                    Converter.setCellText(row, cell, 9, Converter.toBlank(data.getUnitname()), cellStyle);
                    Converter.setCellText(row, cell, 10, Converter.toBlank(data.getManufname()), cellStyle);
                    Converter.setCellText(row, cell, 11, Converter.toBlank(data.getProductarea()), cellStyle);
                    Converter.setCellText(row, cell, 12, Converter.toBlank(data.getLicensenum()), cellStyle);
                    Converter.setCellText(row, cell, 13, Converter.toBlank(data.getRetail()), cellStyle);

                    Converter.setCellText(row, cell, 14, Converter.toBlank(data.getQckc()), cellStyle);
                    Converter.setCellText(row, cell, 15, Converter.toBlank(data.getQckcje()), cellStyle);
                    Converter.setCellText(row, cell, 16, Converter.toBlank(data.getQjrk()), cellStyle);
                    Converter.setCellText(row, cell, 17, Converter.toBlank(data.getQjrkje()), cellStyle);
                    Converter.setCellText(row, cell, 18, Converter.toBlank(data.getQjck()), cellStyle);
                    Converter.setCellText(row, cell, 19, Converter.toBlank(data.getQjckje()), cellStyle);
                    Converter.setCellText(row, cell, 20, Converter.toBlank(data.getQmkc()), cellStyle);
                    Converter.setCellText(row, cell, 21, Converter.toBlank(data.getQmkcje()), cellStyle);

                }
                if (select == 3) {
                    StorageDetail data = (StorageDetail) list.get(i);
                    Converter.setCellText(row, cell, 0, Converter.toBlank(data.getId()), cellStyle);
                    Converter.setCellText(row, cell, 1, Converter.toBlank(data.getPharmacycode()), cellStyle);
                    Converter.setCellText(row, cell, 2, Converter.toBlank(data.getName()), cellStyle);
                    Converter.setCellText(row, cell, 3, Converter.toBlank(data.getMatcode()), cellStyle);
                    Converter.setCellText(row, cell, 4, Converter.toBlank(data.getBarcode()), cellStyle);
                    Converter.setCellText(row, cell, 5, Converter.toBlank(data.getMatname()), cellStyle);
                    Converter.setCellText(row, cell, 6, Converter.toBlank(data.getCommonname()), cellStyle);
                    Converter.setCellText(row, cell, 7, Converter.toBlank(data.getDosage()), cellStyle);
                    Converter.setCellText(row, cell, 8, Converter.toBlank(data.getSpec()), cellStyle);
                    Converter.setCellText(row, cell, 9, Converter.toBlank(data.getUnitname()), cellStyle);
                    Converter.setCellText(row, cell, 10, Converter.toBlank(data.getManufname()), cellStyle);
                    Converter.setCellText(row, cell, 11, Converter.toBlank(data.getProductarea()), cellStyle);
                    Converter.setCellText(row, cell, 12, Converter.toBlank(data.getLicensenum()), cellStyle);
                    Converter.setCellText(row, cell, 13, Converter.toBlank(data.getRetail()), cellStyle);

                    Converter.setCellText(row, cell, 14, Converter.toBlank(data.getQckc()), cellStyle);
                    Converter.setCellText(row, cell, 15, Converter.toBlank(data.getQckcje()), cellStyle);
                    Converter.setCellText(row, cell, 16, Converter.toBlank(data.getCg()), cellStyle);
                    Converter.setCellText(row, cell, 17, Converter.toBlank(data.getCgje()), cellStyle);
                    Converter.setCellText(row, cell, 18, Converter.toBlank(data.getPyrk()), cellStyle);
                    Converter.setCellText(row, cell, 19, Converter.toBlank(data.getPyrkje()), cellStyle);
                    Converter.setCellText(row, cell, 20, Converter.toBlank(data.getQtrk()), cellStyle);
                    Converter.setCellText(row, cell, 21, Converter.toBlank(data.getQtrkje()), cellStyle);
                    Converter.setCellText(row, cell, 22, Converter.toBlank(data.getClrk()), cellStyle);
                    Converter.setCellText(row, cell, 23, Converter.toBlank(data.getClrkje()), cellStyle);
                    Converter.setCellText(row, cell, 24, Converter.toBlank(data.getPsrk()), cellStyle);
                    Converter.setCellText(row, cell, 25, Converter.toBlank(data.getPsrkje()), cellStyle);
                    Converter.setCellText(row, cell, 26, Converter.toBlank(data.getQcrk()), cellStyle);
                    Converter.setCellText(row, cell, 27, Converter.toBlank(data.getQcrkje()), cellStyle);

                    Converter.setCellText(row, cell, 28, Converter.toBlank(data.getXsck()), cellStyle);
                    Converter.setCellText(row, cell, 29, Converter.toBlank(data.getXsckje()), cellStyle);
                    Converter.setCellText(row, cell, 30, Converter.toBlank(data.getPkck()), cellStyle);
                    Converter.setCellText(row, cell, 31, Converter.toBlank(data.getPkckje()), cellStyle);
                    Converter.setCellText(row, cell, 32, Converter.toBlank(data.getQtck()), cellStyle);
                    Converter.setCellText(row, cell, 33, Converter.toBlank(data.getQtckje()), cellStyle);
                    Converter.setCellText(row, cell, 34, Converter.toBlank(data.getClck()), cellStyle);
                    Converter.setCellText(row, cell, 35, Converter.toBlank(data.getClckje()), cellStyle);
                    Converter.setCellText(row, cell, 36, Converter.toBlank(data.getPsck()), cellStyle);
                    Converter.setCellText(row, cell, 37, Converter.toBlank(data.getPsckje()), cellStyle);
                    Converter.setCellText(row, cell, 38, Converter.toBlank(data.getQdck()), cellStyle);
                    Converter.setCellText(row, cell, 39, Converter.toBlank(data.getQdckje()), cellStyle);
                    Converter.setCellText(row, cell, 40, Converter.toBlank(data.getXhck()), cellStyle);
                    Converter.setCellText(row, cell, 41, Converter.toBlank(data.getXhckje()), cellStyle);

                    Converter.setCellText(row, cell, 42, Converter.toBlank(data.getQmkc()), cellStyle);
                    Converter.setCellText(row, cell, 43, Converter.toBlank(data.getQmkcje()), cellStyle);
                }

                if (i == size - 1) {
                    if (select == 1) {
                        StorageTotal sum = (StorageTotal) obj;

                        Converter.setCellText(rows, cell, 0, "? ", cellStyle);
                        Converter.setCellText(rows, cell, 3, Converter.toBlank(sum.getQckc()), cellStyle);
                        Converter.setCellText(rows, cell, 4, Converter.toBlank(sum.getQckcje()), cellStyle);
                        Converter.setCellText(rows, cell, 5, Converter.toBlank(sum.getQjrk()), cellStyle);
                        Converter.setCellText(rows, cell, 6, Converter.toBlank(sum.getQjrkje()), cellStyle);
                        Converter.setCellText(rows, cell, 7, Converter.toBlank(sum.getQjck()), cellStyle);
                        Converter.setCellText(rows, cell, 8, Converter.toBlank(sum.getQjckje()), cellStyle);
                        Converter.setCellText(rows, cell, 9, Converter.toBlank(sum.getQmkc()), cellStyle);
                        Converter.setCellText(rows, cell, 10, Converter.toBlank(sum.getQmkcje()), cellStyle);
                    }
                    if (select == 2) {
                        StorageMilde sum = (StorageMilde) obj;
                        Converter.setCellText(rows, cell, 0, Converter.toBlank("? "), cellStyle);
                        Converter.setCellText(rows, cell, 14, Converter.toBlank(sum.getQckc()), cellStyle);
                        Converter.setCellText(rows, cell, 15, Converter.toBlank(sum.getQckcje()), cellStyle);
                        Converter.setCellText(rows, cell, 16, Converter.toBlank(sum.getQjrk()), cellStyle);
                        Converter.setCellText(rows, cell, 17, Converter.toBlank(sum.getQjrkje()), cellStyle);
                        Converter.setCellText(rows, cell, 18, Converter.toBlank(sum.getQjck()), cellStyle);
                        Converter.setCellText(rows, cell, 19, Converter.toBlank(sum.getQjckje()), cellStyle);
                        Converter.setCellText(rows, cell, 20, Converter.toBlank(sum.getQmkc()), cellStyle);
                        Converter.setCellText(rows, cell, 21, Converter.toBlank(sum.getQmkcje()), cellStyle);
                    }
                    if (select == 3) {
                        StorageDetail sum = (StorageDetail) obj;
                        Converter.setCellText(rows, cell, 0, Converter.toBlank("? "), cellStyle);
                        //                     Converter.setCellText(rows, cell, 1,Converter.toBlank(sum.getPharmacycode()), cellStyle);
                        //                     Converter.setCellText(rows, cell, 2,Converter.toBlank(sum.getName()), cellStyle);
                        //                     Converter.setCellText(rows, cell, 3,Converter.toBlank(sum.getMatcode()), cellStyle);
                        //                     Converter.setCellText(rows, cell, 4,Converter.toBlank(sum.getBarcode()), cellStyle);
                        //                     Converter.setCellText(rows, cell, 5,Converter.toBlank(sum.getMatname()), cellStyle);
                        //                     Converter.setCellText(rows, cell, 6,Converter.toBlank(sum.getCommonname()), cellStyle);
                        //                     Converter.setCellText(rows, cell, 7,Converter.toBlank(sum.getDosage()), cellStyle);
                        //                     Converter.setCellText(rows, cell, 8,Converter.toBlank(sum.getSpec()), cellStyle);
                        //                     Converter.setCellText(rows, cell, 9,Converter.toBlank(sum.getUnitname()), cellStyle);
                        //                     Converter.setCellText(rows, cell, 10,Converter.toBlank(sum.getManufname()), cellStyle);
                        //                     Converter.setCellText(rows, cell, 11,Converter.toBlank(sum.getProductarea()), cellStyle);
                        //                     Converter.setCellText(rows, cell, 12,Converter.toBlank(sum.getLicensenum()), cellStyle);
                        //                     Converter.setCellText(rows, cell, 13,Converter.toBlank(sum.getRetail()), cellStyle);

                        Converter.setCellText(rows, cell, 14, Converter.toBlank(sum.getQckc()), cellStyle);
                        Converter.setCellText(rows, cell, 15, Converter.toBlank(sum.getQckcje()), cellStyle);
                        Converter.setCellText(rows, cell, 16, Converter.toBlank(sum.getCg()), cellStyle);
                        Converter.setCellText(rows, cell, 17, Converter.toBlank(sum.getCgje()), cellStyle);
                        Converter.setCellText(rows, cell, 18, Converter.toBlank(sum.getPyrk()), cellStyle);
                        Converter.setCellText(rows, cell, 19, Converter.toBlank(sum.getPyrkje()), cellStyle);
                        Converter.setCellText(rows, cell, 20, Converter.toBlank(sum.getQtrk()), cellStyle);
                        Converter.setCellText(rows, cell, 21, Converter.toBlank(sum.getQtrkje()), cellStyle);
                        Converter.setCellText(rows, cell, 22, Converter.toBlank(sum.getClrk()), cellStyle);
                        Converter.setCellText(rows, cell, 23, Converter.toBlank(sum.getClrkje()), cellStyle);
                        Converter.setCellText(rows, cell, 24, Converter.toBlank(sum.getPsrk()), cellStyle);
                        Converter.setCellText(rows, cell, 25, Converter.toBlank(sum.getPsrkje()), cellStyle);
                        Converter.setCellText(rows, cell, 26, Converter.toBlank(sum.getQcrk()), cellStyle);
                        Converter.setCellText(rows, cell, 27, Converter.toBlank(sum.getQcrkje()), cellStyle);

                        Converter.setCellText(rows, cell, 28, Converter.toBlank(sum.getXsck()), cellStyle);
                        Converter.setCellText(rows, cell, 29, Converter.toBlank(sum.getXsckje()), cellStyle);
                        Converter.setCellText(rows, cell, 30, Converter.toBlank(sum.getPkck()), cellStyle);
                        Converter.setCellText(rows, cell, 31, Converter.toBlank(sum.getPkckje()), cellStyle);
                        Converter.setCellText(rows, cell, 32, Converter.toBlank(sum.getQtck()), cellStyle);
                        Converter.setCellText(rows, cell, 33, Converter.toBlank(sum.getQtckje()), cellStyle);
                        Converter.setCellText(rows, cell, 34, Converter.toBlank(sum.getClck()), cellStyle);
                        Converter.setCellText(rows, cell, 35, Converter.toBlank(sum.getClckje()), cellStyle);
                        Converter.setCellText(rows, cell, 36, Converter.toBlank(sum.getPsck()), cellStyle);
                        Converter.setCellText(rows, cell, 37, Converter.toBlank(sum.getPsckje()), cellStyle);
                        Converter.setCellText(rows, cell, 38, Converter.toBlank(sum.getQdck()), cellStyle);
                        Converter.setCellText(rows, cell, 39, Converter.toBlank(sum.getQdckje()), cellStyle);
                        Converter.setCellText(rows, cell, 40, Converter.toBlank(sum.getXhck()), cellStyle);
                        Converter.setCellText(rows, cell, 41, Converter.toBlank(sum.getXhckje()), cellStyle);

                        Converter.setCellText(rows, cell, 42, Converter.toBlank(sum.getQmkc()), cellStyle);
                        Converter.setCellText(rows, cell, 43, Converter.toBlank(sum.getQmkcje()), cellStyle);
                    }

                }
            }
        }
        return workbook;
    } catch (Exception e) {

        e.printStackTrace();
        return null;
    }

}

From source file:com.github.dougkelly88.FLIMPlateReaderGUI.SequencingClasses.Classes.FilterTableModel.java

public void saveFilterTableModelAsSpreadsheet() {

    // Save FilterTable in second sheet of wb .xls
    // Initialize second sheet
    HSSFSheet sheet2 = HCAFLIMPluginFrame.wb.createSheet("SpectralSequencing");

    // Initialize first row with headers
    int RowSize = data_.size();
    HSSFRow row0 = sheet2.createRow(0);
    HSSFCell cell00 = row0.createCell(0);
    HSSFCell cell01 = row0.createCell(1);
    HSSFCell cell02 = row0.createCell(2);
    HSSFCell cell03 = row0.createCell(3);
    HSSFCell cell04 = row0.createCell(4);
    HSSFCell cell05 = row0.createCell(5);
    HSSFCell cell06 = row0.createCell(6);
    HSSFCell cell07 = row0.createCell(7);

    cell00.setCellValue("Label");
    cell01.setCellValue("Ex filter");
    cell02.setCellValue("ND filter");
    cell03.setCellValue("Dichroic");
    cell04.setCellValue("Em filter");
    cell05.setCellValue("Filter Cube");
    cell06.setCellValue("Camera integration (ms)");
    cell07.setCellValue("Delays");

    // write row for row from table to .xls       
    for (int RowNum = 0; RowNum < RowSize; RowNum++) {
        HSSFRow row = sheet2.createRow(RowNum + 1);
        HSSFCell cell0 = row.createCell(0);
        HSSFCell cell1 = row.createCell(1);
        HSSFCell cell2 = row.createCell(2);
        HSSFCell cell3 = row.createCell(3);
        HSSFCell cell4 = row.createCell(4);
        HSSFCell cell5 = row.createCell(5);
        HSSFCell cell6 = row.createCell(6);
        HSSFCell cell7 = row.createCell(7);

        cell0.setCellValue(data_.get(RowNum).getLabel());
        cell1.setCellValue(data_.get(RowNum).getExFilt());
        cell2.setCellValue(data_.get(RowNum).getNDFilt());
        cell3.setCellValue(data_.get(RowNum).getDiFilt());
        cell4.setCellValue(data_.get(RowNum).getEmFilt());
        cell5.setCellValue(data_.get(RowNum).getCube());
        cell6.setCellValue(data_.get(RowNum).getIntTime());

        // convert Array<List> to String like "[0, 1000, 2000, 3000]" and write it to .xls      
        ArrayList<Integer> a = new ArrayList<Integer>();
        a = data_.get(RowNum).getDelays();
        List<String> newList = new ArrayList<String>(a.size());
        for (Integer myInt : a) {
            newList.add(String.valueOf(myInt));
        }/*from www  .  j  a v  a 2s  .  co  m*/
        String b = "[";
        for (String s : newList) {
            b += s + ", ";
        }
        int s = b.length();
        b = b.substring(0, s - 2);
        b = b + "]";
        cell7.setCellValue(b);

    }

}

From source file:com.github.dougkelly88.FLIMPlateReaderGUI.SequencingClasses.Classes.FOVTableModel.java

public void saveFOVTableModelAsSpreadsheet() {

    // Save FilterTable in first sheet of wb .xls
    // Initialize first sheet
    HSSFSheet sheet1 = HCAFLIMPluginFrame.wb.createSheet("XYSequencing");

    // Initialize first row with headers    
    int RowSize = data_.size();
    HSSFRow row0 = sheet1.createRow(0);
    HSSFCell cell00 = row0.createCell(0);
    HSSFCell cell01 = row0.createCell(1);
    HSSFCell cell02 = row0.createCell(2);
    HSSFCell cell03 = row0.createCell(3);
    HSSFCell cell04 = row0.createCell(4);
    cell00.setCellValue("Well");
    cell01.setCellValue("X " + um);
    cell02.setCellValue("Y" + um);
    cell03.setCellValue("Z" + um);
    cell04.setCellValue("Group");

    // write row for row from table to sheet        
    for (int RowNum = 0; RowNum < RowSize; RowNum++) {
        HSSFRow row = sheet1.createRow(RowNum + 1);
        HSSFCell cell0 = row.createCell(0);
        HSSFCell cell1 = row.createCell(1);
        HSSFCell cell2 = row.createCell(2);
        HSSFCell cell3 = row.createCell(3);
        HSSFCell cell4 = row.createCell(4);
        cell0.setCellValue(data_.get(RowNum).getWell());
        cell1.setCellValue(data_.get(RowNum).getX());
        cell2.setCellValue(data_.get(RowNum).getY());
        cell3.setCellValue(data_.get(RowNum).getZ());
        cell4.setCellValue(data_.get(RowNum).getGroup());
    }/* www .ja  v a2  s  . c  om*/

    //To change body of generated methods, choose Tools | Templates.
}

From source file:com.github.dougkelly88.FLIMPlateReaderGUI.SequencingClasses.Classes.TimeCourseTableModel.java

public void saveTimeCourseTableModelAsSpreadsheet() {

    // Save FilterTable in third sheet of wb .xls
    // Initialize third sheet
    HSSFSheet sheet3 = HCAFLIMPluginFrame.wb.createSheet("TimeCourseSequencing");

    // Initialize first row with headers    
    int RowSize = data_.size();
    HSSFRow row0 = sheet3.createRow(0);
    HSSFCell cell00 = row0.createCell(0);
    HSSFCell cell01 = row0.createCell(1);
    HSSFCell cell02 = row0.createCell(2);
    cell00.setCellValue("Time (s)");
    cell01.setCellValue("Liquid dispense?");
    cell02.setCellValue("Liquid dispension well(s)");

    // write row for row from table to sheet        
    for (int RowNum = 0; RowNum < RowSize; RowNum++) {
        HSSFRow row = sheet3.createRow(RowNum + 1);
        HSSFCell cell0 = row.createCell(0);
        HSSFCell cell1 = row.createCell(1);
        HSSFCell cell2 = row.createCell(2);
        cell0.setCellValue(data_.get(RowNum).getTimeCell());
        cell1.setCellValue(data_.get(RowNum).getLDState());
        cell2.setCellValue((RichTextString) data_.get(RowNum).getLdWells());

    }/*w ww .  ja  v a 2  s  .  c om*/

    //To change body of generated methods, choose Tools | Templates.
}

From source file:com.github.gaborfeher.grantmaster.framework.base.ExcelExporter.java

License:Open Source License

private int addExcelRow(HSSFWorkbook workbook, HSSFSheet sheet, int rowId, List<Object> data) {
    Row excelRow = sheet.createRow(rowId);
    int cellId = 0;
    for (Object cellValue : data) {
        Cell excelCell = excelRow.createCell(cellId);
        cellId += 1;//from w  w  w  . j a v  a 2  s.  c o m
        setExcelCell(workbook, cellValue, excelCell);
    }
    return rowId + 1;
}

From source file:com.github.gujou.deerbelling.sonarqube.service.XlsTasksGenerator.java

License:Open Source License

public static File generateFile(Project sonarProject, FileSystem sonarFileSystem, String sonarUrl,
        String sonarLogin, String sonarPassword) {

    short formatIndex;
    HSSFDataFormat dataFormat = null;/*  w w w  .jav  a 2s .  com*/
    FileOutputStream out = null;
    HSSFWorkbook workbook = null;

    String filePath = sonarFileSystem.workDir().getAbsolutePath() + File.separator + "tasks_report_"
            + sonarProject.getEffectiveKey().replace(':', '-') + "."
            + ReportsKeys.TASKS_REPORT_TYPE_XLS_EXTENSION;

    File resultFile = new File(filePath);

    try {
        out = new FileOutputStream(resultFile);

        workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Tasks list");

        // Date format.
        dataFormat = workbook.createDataFormat();
        formatIndex = dataFormat.getFormat("yyyy-MM-ddTHH:mm:ss");
        HSSFCellStyle dateStyle = workbook.createCellStyle();
        dateStyle.setDataFormat(formatIndex);

        Issues rootIssue = IssueGateway.getOpenIssues(sonarProject.getEffectiveKey(), sonarUrl, sonarLogin,
                sonarPassword);

        if (rootIssue == null) {
            return null;
        }

        DataValidationHelper validationHelper = new HSSFDataValidationHelper(sheet);
        DataValidationConstraint constraint = validationHelper.createExplicitListConstraint(
                new String[] { "OPENED", "CONFIRMED", "REOPENED", "RESOLVED", "CLOSE" });
        CellRangeAddressList addressList = new CellRangeAddressList(1, rootIssue.getIssues().size() + 1,
                STATUS_COLUMN_INDEX, STATUS_COLUMN_INDEX);
        DataValidation dataValidation = validationHelper.createValidation(constraint, addressList);
        dataValidation.setSuppressDropDownArrow(false);
        sheet.addValidationData(dataValidation);

        int rownum = 0;

        Row row = sheet.createRow(rownum++);
        row.createCell(STATUS_COLUMN_INDEX).setCellValue("Status");
        row.createCell(SEVERITY_COLUMN_INDEX).setCellValue("Severity");
        row.createCell(COMPONENT_COLUMN_INDEX).setCellValue("Component");
        row.createCell(LINE_COLUMN_INDEX).setCellValue("Line");
        row.createCell(MESSAGE_COLUMN_INDEX).setCellValue("Message");
        row.createCell(AUTHOR_COLUMN_INDEX).setCellValue("Author");
        row.createCell(ASSIGNED_COLUMN_INDEX).setCellValue("Assigned");
        row.createCell(CREATION_DATE_COLUMN_INDEX).setCellValue("CreationDate");
        row.createCell(UPDATE_DATE_COLUMN_INDEX).setCellValue("UpdateDate");
        row.createCell(COMPONENT_PATH_COLUMN_INDEX).setCellValue("Path");

        for (Issue issue : rootIssue.getIssues()) {
            if (issue != null) {
                row = sheet.createRow(rownum++);
                int componentIndex = 0;
                if (issue.getComponent() != null) {
                    componentIndex = issue.getComponent().lastIndexOf('/');
                }
                String component;
                String path;
                if (componentIndex > 0) {
                    component = issue.getComponent().substring(componentIndex + 1);
                    path = issue.getComponent().substring(0, componentIndex);
                } else {
                    component = issue.getComponent();
                    path = "";
                }

                // Set values.
                row.createCell(STATUS_COLUMN_INDEX).setCellValue(issue.getStatus());
                row.createCell(SEVERITY_COLUMN_INDEX).setCellValue(issue.getSeverity());
                row.createCell(COMPONENT_COLUMN_INDEX).setCellValue(component);
                row.createCell(LINE_COLUMN_INDEX).setCellValue(issue.getLine());
                row.createCell(MESSAGE_COLUMN_INDEX).setCellValue(issue.getMessage());
                row.createCell(AUTHOR_COLUMN_INDEX).setCellValue(issue.getAuthor());
                row.createCell(ASSIGNED_COLUMN_INDEX).setCellValue(issue.getAssignee());
                row.createCell(CREATION_DATE_COLUMN_INDEX).setCellValue(issue.getCreationDate());
                row.createCell(UPDATE_DATE_COLUMN_INDEX).setCellValue(issue.getUpdateDate());
                row.createCell(COMPONENT_PATH_COLUMN_INDEX).setCellValue(path);

                // Set date style to date column.
                row.getCell(CREATION_DATE_COLUMN_INDEX).setCellStyle(dateStyle);
                row.getCell(UPDATE_DATE_COLUMN_INDEX).setCellStyle(dateStyle);
            }
        }

        // Auto-size sheet columns.
        sheet.autoSizeColumn(STATUS_COLUMN_INDEX);
        sheet.autoSizeColumn(STATUS_COLUMN_INDEX);
        sheet.autoSizeColumn(COMPONENT_COLUMN_INDEX);
        sheet.autoSizeColumn(LINE_COLUMN_INDEX);
        sheet.autoSizeColumn(MESSAGE_COLUMN_INDEX);
        sheet.autoSizeColumn(AUTHOR_COLUMN_INDEX);
        sheet.autoSizeColumn(ASSIGNED_COLUMN_INDEX);
        sheet.autoSizeColumn(CREATION_DATE_COLUMN_INDEX);
        sheet.autoSizeColumn(UPDATE_DATE_COLUMN_INDEX);
        sheet.autoSizeColumn(COMPONENT_PATH_COLUMN_INDEX);

        workbook.write(out);

    } catch (FileNotFoundException e) {

        // TODO manage error.
        e.printStackTrace();
    } catch (IOException e) {

        // TODO manage error.
        e.printStackTrace();
    } finally {
        IOUtils.closeQuietly(workbook);
        IOUtils.closeQuietly(out);
    }

    return resultFile;
}

From source file:com.grant.data.ItemDAO.java

public List getAllItemOutReport() throws FileNotFoundException, IOException {

    ResultSet rs = null;/*from w  w  w .j a  v a  2 s.c  o m*/
    Connection dbConn = null;
    List ss = null;

    try {
        dbConn = dbConnManager.connect();

        Statement stmt = dbConn.createStatement();

        String query = "SELECT * FROM grant_item_out";
        //String query = "INSERT INTO grant_item_out(v_item_name,v_item_no,v_ref_code,v_descrip,v_invo_no,i_outwards,i_balance,d_in_date)  " + "VALUES( '" + itemName + "','" + itemNo + "','" + refCode +  "','"+ description + "','" + invoiceNo + "','" + outwards + "','" + balance +  "','"+ itemInDate + "'" + ")";

        System.out.println(query);

        rs = stmt.executeQuery(query);

        /////////////
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("lawix10");
        HSSFRow rowhead = sheet.createRow((short) 0);
        rowhead.createCell((short) 0).setCellValue("CellHeadName1");
        rowhead.createCell((short) 1).setCellValue("CellHeadName2");
        rowhead.createCell((short) 2).setCellValue("CellHeadName3");
        int i = 1;
        while (rs.next()) {
            HSSFRow row = sheet.createRow((short) i);
            // row.createCell((short) 0).setCellValue(Integer.toString(rs.getInt("column1")));
            row.createCell((short) 1).setCellValue(rs.getString(1));
            row.createCell((short) 2).setCellValue(rs.getString(2));
            i++;
        }
        String yemi = "C:/Users/Isura Amarasinghe/Desktop/test.xls";
        //C:/Users/Isura Amarasinghe/Desktop
        FileOutputStream fileOut = new FileOutputStream(yemi);
        workbook.write(fileOut);
        fileOut.close();
        ///////////

    } catch (SQLException sQLException) {

        System.out.println(sQLException + "-----------Insert query failed-------");

        rs = null;
    } finally {
        dbConnManager.con_close(dbConn);
    }
    return ss;
}