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

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

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

Introduction

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

Prototype

@Override
public void addValidationData(DataValidation dataValidation)

Source Link

Document

Creates a data validation object

Usage

From source file:com.asakusafw.testtools.templategen.ExcelBookBuilder.java

License:Apache License

private void setExplicitListConstraint(HSSFSheet sheet, String[] list, int firstRow, int lastRow, int firstCol,
        int lastCol) {
    //????//  www  .  ja  v a 2  s .  co m
    CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
    DVConstraint constraint = DVConstraint.createExplicitListConstraint(list);
    HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
    validation.setEmptyCellAllowed(true);
    validation.setSuppressDropDownArrow(false);
    sheet.addValidationData(validation);
}

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

License:Open Source License

/**
 * Sheet???.//  ww  w . j  a va 2s  .com
 * 
 * @param sheet
 * @param dvcMap ??fieldName"type_name"
 * @param colDesc 17"?:user_id,??:user_name,:type_name"
 * @author gw_liaozh
 */
public static void addDataValidationConstraints(HSSFSheet sheet, Map<String, DataValidationConstraint> dvcMap,
        String colDesc) {//
    List<String> colFieldList = getColumnFieldList(colDesc);
    HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet);
    //??
    for (int i = 0; i < colFieldList.size(); i++) {
        String fieldName = colFieldList.get(i);
        DataValidationConstraint constraint = dvcMap.get(fieldName);
        if (constraint == null) {
            continue;
        }
        CellRangeAddressList range = new CellRangeAddressList(1, 65535, i, i);
        sheet.addValidationData(dvHelper.createValidation(constraint, range));
    }
}

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

License:Open Source License

/**
 * Excel Workbook?.//from   w ww.  j  ava  2s  .  c om
 * 
 * @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.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  ww  .ja v a2 s  .c  o m*/
    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:kr.co.blackducksoftware.rg.displayexcel.Ex.java

License:Open Source License

/**
  *//*w  ww. jav a 2s .  co  m*/
public static void setOneLineHeader(HSSFSheet sheet, int iRowNum, ArrayList<String> al) {
    LogMaker.makelog("Creating Header in Excel");
    try {
        HSSFRow row = sheet.createRow(iRowNum);
        row.setHeight((short) 600);
        int i = 0;
        for (String str : al) {
            row.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue(str);
            if (i == 1) {
                row.getCell(i).setCellStyle(Style.finalHeaderCellStyle);

                CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 1, 1);
                DVConstraint dvConstraint = DVConstraint
                        .createExplicitListConstraint(new String[] { "A", "B" });
                DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
                dataValidation.setSuppressDropDownArrow(false);
                sheet.addValidationData(dataValidation);

            } else if (i == 2) {
                row.getCell(i).setCellStyle(Style.finalHeaderCellStyle);

                CellRangeAddressList addressList = new CellRangeAddressList(0, 0, i, i);
                DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(
                        new String[] { "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12" });
                DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
                dataValidation.setSuppressDropDownArrow(false);
                sheet.addValidationData(dataValidation);

            } else if (i == 3) {
                row.getCell(i).setCellStyle(Style.finalHeaderCellStyle);

                CellRangeAddressList addressList = new CellRangeAddressList(0, 0, i, i);
                DVConstraint dvConstraint = DVConstraint
                        .createExplicitListConstraint(new String[] { "A", "B", "C", "D", "E" });
                DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
                dataValidation.setSuppressDropDownArrow(false);
                sheet.addValidationData(dataValidation);

            }

            else {
                try {
                    row.getCell(i).setCellStyle(Style.finalHeaderCellStyle);

                } catch (NullPointerException e) {
                    e.printStackTrace();

                }

            }

            sheet.autoSizeColumn((short) i, true);
            i++;

        }

    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:kr.co.blackducksoftware.rg.displayexcel.Ex.java

License:Open Source License

@SuppressWarnings("unchecked")

public static void srcPr(HSSFSheet srcSheet) {
    LogMaker.makelog("prepare to Print ID File info to Excel sheet");

    //category /*  w  w w . ja v  a2  s.  c o m*/
    String[] strCategory = new String[FileVals.category.size()];
    FileVals.category.toArray(strCategory);

    String[] strFPath = new String[FileVals.fPath.size()];
    FileVals.fPath.toArray(strFPath);

    String[] strDType = new String[FileVals.dtype.size()];
    FileVals.dtype.toArray(strDType);

    String[] strFileFolder = new String[FileVals.file.size()];
    FileVals.file.toArray(strFileFolder);

    String[] strComponent = new String[FileVals.component.size()];
    FileVals.component.toArray(strComponent);

    String[] strLicense = new String[FileVals.license.size()];
    FileVals.license.toArray(strLicense);

    String[] strVersion = new String[FileVals.version.size()];
    FileVals.version.toArray(strVersion);

    //matched files
    String[] strOSSFname = new String[FileVals.OSSFname.size()];
    FileVals.OSSFname.toArray(strOSSFname);

    String[] strPercent = new String[FileVals.matchedRatio.size()];
    FileVals.matchedRatio.toArray(strPercent);

    //OSS matched file line
    String[] strOSSFLine = new String[FileVals.OSSFLine.size()];
    FileVals.OSSFLine.toArray(strOSSFLine);

    //Dev Matched First Line
    String[] strFirstLine = new String[FileVals.firstLine.size()];
    FileVals.firstLine.toArray(strFirstLine);

    //matched total line
    String[] strTotalLine = new String[FileVals.tline.size()];
    FileVals.tline.toArray(strTotalLine);

    //identified date
    String[] strIdentifiedDate = new String[FileVals.identifiedDate.size()];
    FileVals.identifiedDate.toArray(strIdentifiedDate);

    //identifier
    String[] strIdentifier = new String[FileVals.identifier.size()];
    FileVals.identifier.toArray(strIdentifier);

    String[] strComment = new String[FileVals.comment.size()];
    FileVals.comment.toArray(strComment);

    int partialCount = Count.partialCount;
    //int partialCount=0;
    LogMaker.makelog("Printing File Info to excel");
    for (int i = 0; i < strDType.length; i++) {
        //HSSFRow tempRow = srcSheet.createRow(i+2+iCount-(lineCount-1));//???
        HSSFRow tempRow = srcSheet.createRow(i + 2 + Count.partialCount);//???
        partialCount++;

        tempRow.setHeight((short) 500);

        String a = (strFileFolder[i].substring(1));

        // category print
        int c = 0;

        try {
            tempRow.createCell(c).setCellValue(a.substring(0, a.indexOf("/")));
            //if (strCate.equals(a.substring(0,a.indexOf("/")))){}

        } catch (Exception e) {
            tempRow.createCell(c).setCellValue(a);
        }

        tempRow.getCell(c).setCellStyle(Style.componentCellStyle);
        srcSheet.autoSizeColumn((short) c, true);
        c++;

        tempRow.createCell(c).setCellValue(a);
        tempRow.getCell(c).setCellStyle(Style.componentCellStyle);
        srcSheet.autoSizeColumn((short) c, true);
        c++;

        //Identification type print
        tempRow.createCell(c).setCellValue(strDType[i]);
        tempRow.getCell(c).setCellStyle(Style.componentCellStyle);
        srcSheet.autoSizeColumn((short) c, true);
        c++;

        tempRow.createCell(c).setCellValue(strComponent[i]);
        tempRow.getCell(c).setCellStyle(Style.componentCellStyle);
        srcSheet.autoSizeColumn((short) c, true);
        c++;

        //License Print
        tempRow.createCell(c).setCellValue(strLicense[i]);
        tempRow.getCell(c).setCellStyle(Style.componentCellStyle);
        srcSheet.autoSizeColumn((short) c, true);
        c++;

        //version Print
        tempRow.createCell(c).setCellValue(strVersion[i]);
        tempRow.getCell(c).setCellStyle(Style.componentCellStyle);
        srcSheet.autoSizeColumn((short) c, true);
        c++;

        //OSS matched Files;OSS matched Filename; print
        try {
            tempRow.createCell(c).setCellValue(strOSSFname[i]);
        } catch (Exception e) {
            tempRow.createCell(c).setCellValue("");
        }
        tempRow.getCell(c).setCellStyle(Style.componentCellStyle);
        srcSheet.autoSizeColumn((short) c, true);
        c++;

        //matched percent print;
        if (strPercent[i] == "" || strPercent[i] == null) {
            tempRow.createCell(c).setCellValue("100%");
        } else {
            tempRow.createCell(c).setCellValue(strPercent[i] + "%");
        }
        tempRow.getCell(c).setCellStyle(Style.componentCellStyle);
        srcSheet.autoSizeColumn((short) c, true);
        c++;

        //OSS file line print
        try {
            tempRow.createCell(c).setCellValue(strOSSFLine[i]);
        } catch (Exception e) {
            tempRow.createCell(c).setCellValue("");
        }
        tempRow.getCell(c).setCellStyle(Style.componentCellStyle);
        srcSheet.autoSizeColumn((short) c, true);
        c++;

        //Dev Matched First Line print
        try {
            tempRow.createCell(c).setCellValue(strFirstLine[i]);
        } catch (Exception e) {
            tempRow.createCell(c).setCellValue("");
        }
        tempRow.getCell(c).setCellStyle(Style.componentCellStyle);
        srcSheet.autoSizeColumn((short) c, true);
        c++;

        //Dev Matched Total Line print
        try {
            tempRow.createCell(c).setCellValue(strTotalLine[i]);
        } catch (Exception e) {
            tempRow.createCell(c).setCellValue("");
        }
        tempRow.getCell(c).setCellStyle(Style.componentCellStyle);
        srcSheet.autoSizeColumn((short) c, true);
        c++;

        //Identified Date; identifieddate;
        try {
            tempRow.createCell(c).setCellValue(strIdentifiedDate[i]);
        } catch (Exception e) {
            tempRow.createCell(c).setCellValue("");
        }
        //tempRow.createCell(c).setCellValue(strIdentifiedDate[i]);
        tempRow.getCell(c).setCellStyle(Style.componentCellStyle);
        srcSheet.autoSizeColumn((short) c, true);
        c++;

        //Identifier print
        try {
            tempRow.createCell(c).setCellValue(strIdentifier[i]);
        } catch (Exception e) {
            tempRow.createCell(c).setCellValue("");
        }

        tempRow.getCell(c).setCellStyle(Style.componentCellStyle);
        srcSheet.autoSizeColumn((short) c, true);
        c++;

        //Comment print
        tempRow.createCell(c).setCellValue(strComment[i]);
        tempRow.getCell(c).setCellStyle(Style.componentCellStyle);
        srcSheet.autoSizeColumn((short) c, true);
        c++;

        //other empty cell
        for (int temp = c; temp < 21; temp++) {
            tempRow.createCell(temp).setCellValue("");
            tempRow.getCell(temp).setCellStyle(Style.componentCellStyle);
            //srcSheet.autoSizeColumn((short) c, true);
            if (temp == 16) {
                //CellRangeAddressList(startRow, endRow, startCol, endCol)
                CellRangeAddressList addressList = new CellRangeAddressList(i + 2, i + 2, temp, temp);
                DVConstraint dvConstraint = DVConstraint
                        .createExplicitListConstraint(new String[] { "A", "B" });
                DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
                dataValidation.setSuppressDropDownArrow(false);
                srcSheet.addValidationData(dataValidation);
            }
            if (temp == 15) {
                //CellRangeAddressList(startRow, endRow, startCol, endCol)
                CellRangeAddressList addressList = new CellRangeAddressList(i + 2, i + 2, temp, temp);
                DVConstraint dvConstraint = DVConstraint
                        .createExplicitListConstraint(new String[] { "A", "B", "C", "D", "E" });
                DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
                dataValidation.setSuppressDropDownArrow(false);
                srcSheet.addValidationData(dataValidation);
            }

            //c++;   
        }

        //tempRow.createCell(3).setCellValue(strComponent[i]+" (+"+(compCount-1)+")");
        tempRow.createCell(3).setCellValue(strComponent[i] + " (+0)");
        tempRow.getCell(3).setCellStyle(Style.componentCellStyle);
        srcSheet.autoSizeColumn((short) 3, true);

        FileVals.clearAllArrayList();
    }

    Count.partialCount = partialCount;

}

From source file:kr.co.blackducksoftware.rg.displayexcel.Ex.java

License:Open Source License

/**
 * "Build Image Analysis"  /* w  w  w  .ja v a  2s  .  co m*/
 *      * 
 */

public static void BIA(HSSFWorkbook wb) {
    LogMaker.makelog("Making BIA sheet");
    HSSFSheet sheetBIA = wb.createSheet("Build Image Analysis");// ??????? 

    ArrayList<String> Header = new ArrayList<String>();

    Header.add("Analysis Description");
    Header.add("1st Reviewer (Development Team)");
    Header.add("Final Revewer");

    lineHeader(sheetBIA, 0, Header);// 0?? ???? ??????? 
    sheetBIA.addMergedRegion(new Region(0, (short) 0, 0, (short) 5));
    sheetBIA.addMergedRegion(new Region(0, (short) 6, 0, (short) 8));
    sheetBIA.addMergedRegion(new Region(0, (short) 9, 0, (short) 10));
    Header.clear();

    HSSFRow row = sheetBIA.createRow(1); //row ????
    row.setHeight((short) 500);
    Header.add("Binary File Path");
    Header.add("                ");
    Header.add("Component");
    Header.add("License");//drop down
    Header.add("Analyzer");
    Header.add("Analyzer's Comments");

    src2ndrow(sheetBIA, Header, 0, row, 6, 9);
    Header.clear();
    sheetBIA.addMergedRegion(new Region(1, (short) 0, 1, (short) 1));

    Header.add("Person In Charge");
    Header.add("A or B");//drop down
    Header.add("Reviewer's Ccomments");

    src2ndrow(sheetBIA, Header, 6, row, 6, 9);// 0?? ???? ??????? 
    sheetBIA.autoSizeColumn((short) 6, true);
    sheetBIA.autoSizeColumn((short) 8, true);
    Header.clear();

    Header.add("Review Date");
    Header.add("Reviewer's Comments");

    src2ndrow(sheetBIA, Header, 9, row, 6, 9);// 0?? ???? ??????? 
    Header.clear();

    for (int i = 2; i < 200; i++) {
        HSSFRow row2 = sheetBIA.createRow(i);
        row2.setHeight((short) 500);
        for (int j = 0; j < 11; j++) {
            row2.createCell(j, HSSFCell.CELL_TYPE_STRING).setCellValue("           ");
            row2.getCell(j).setCellStyle(Style.componentCellStyle);

            if (j == 3) {
                CellRangeAddressList addressList = new CellRangeAddressList(i, i, j, j);
                DVConstraint dvConstraint = DVConstraint
                        .createExplicitListConstraint(new String[] { "A", "B" });
                DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
                dataValidation.setSuppressDropDownArrow(false);
                sheetBIA.addValidationData(dataValidation);
            }

            if (j == 7) {
                CellRangeAddressList addressList = new CellRangeAddressList(i, i, j, j);
                DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(
                        new String[] { "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12" });
                DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
                dataValidation.setSuppressDropDownArrow(false);
                sheetBIA.addValidationData(dataValidation);
            }
        }
    }

}

From source file:kr.co.blackducksoftware.rg.displayexcel.Ex.java

License:Open Source License

/**
 * "Final List"  //w ww .j  a  v  a 2  s.  c om
 * 
 */
public static void FL(HSSFWorkbook wb) {
    LogMaker.makelog("Making Final sheet");
    HSSFSheet sheet = wb.createSheet("Final List");// ??????? 

    ArrayList<String> Header = new ArrayList<String>();
    Header.add("                     Component Name                        ");
    Header.add("                     A or B                      "); //Drop down 
    Header.add("                 License                 "); //Drop Down               
    Header.add("               Linkage Type              "); //Drop down
    Header.add("                   License Copy                     ");
    Header.add("                 Remark                  ");
    // Macro ? ???
    setOneLineHeader(sheet, 0, Header);// 0?? ???? ??????? 
    Header.clear();

    //?? ????
    for (int i = 1; i < 200; i++) {
        HSSFRow row2 = sheet.createRow(i);
        row2.setHeight((short) 500);
        for (int j = 0; j < 6; j++) {
            row2.createCell(j, HSSFCell.CELL_TYPE_STRING).setCellValue("");
            row2.getCell(j).setCellStyle(Style.componentCellStyle);
            if (j == 1) {
                //CellRangeAddressList(startRow, endRow, startCol, endCol)
                CellRangeAddressList addressList = new CellRangeAddressList(i, i, 1, 1);
                DVConstraint dvConstraint = DVConstraint
                        .createExplicitListConstraint(new String[] { "A", "B" });
                DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
                dataValidation.setSuppressDropDownArrow(false);
                sheet.addValidationData(dataValidation);
            }

            if (j == 2) {
                //CellRangeAddressList(startRow, endRow, startCol, endCol)
                CellRangeAddressList addressList = new CellRangeAddressList(i, i, j, j);
                DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(
                        new String[] { "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12" });
                DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
                dataValidation.setSuppressDropDownArrow(false);
                sheet.addValidationData(dataValidation);
            }
            if (j == 3) {
                //CellRangeAddressList(startRow, endRow, startCol, endCol)
                CellRangeAddressList addressList = new CellRangeAddressList(i, i, j, j);
                DVConstraint dvConstraint = DVConstraint
                        .createExplicitListConstraint(new String[] { "A", "B", "C", "D", "E" });
                DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
                dataValidation.setSuppressDropDownArrow(false);
                sheet.addValidationData(dataValidation);
            }

        }
    }

}

From source file:org.openelis.bean.WorksheetExcelHelperBean.java

License:Open Source License

@TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
public WorksheetManager1 exportToExcel(WorksheetManager1 manager) throws Exception {
    boolean isEditable;
    int r, i, a, o, aCount, aTotal;
    ArrayList<WorksheetAnalysisViewDO> waList;
    ArrayList<WorksheetResultViewDO> wrList;
    ArrayList<WorksheetQcResultViewDO> wqrList;
    String cellNameIndex, posNum, outFileName;
    File outFile;//from   w ww  . j  av  a  2 s  .c o  m
    FileInputStream in;
    FileOutputStream out;
    HashMap<Integer, ArrayList<WorksheetAnalysisViewDO>> waMap;
    HashMap<Integer, ArrayList<WorksheetResultViewDO>> wrMap;
    HashMap<Integer, ArrayList<WorksheetQcResultViewDO>> wqrMap;
    HashMap<Integer, WorksheetAnalysisViewDO> waLinkMap;
    HashMap<String, HashMap<Integer, ArrayList<AnalyteParameterViewDO>>> apMap;
    HashMap<String, String> tCellNames;
    Cell cell;
    CellRangeAddressList /*statusCells, */ reportableColumn;
    DVConstraint /*statusConstraint, */ reportableConstraint;
    HSSFDataValidation /*statusValidation, */ reportableValidation;
    HSSFSheet resultSheet, overrideSheet;
    HSSFWorkbook wb;
    Name cellName;
    Row row, oRow, tRow;
    DictionaryDO formatDO;
    ReportStatus status;
    SimpleDateFormat dateTimeFormat;
    WorksheetAnalysisDO waLinkDO;

    status = new ReportStatus();
    status.setMessage("Exporting to Excel: Initializing");
    status.setPercentComplete(0);
    session.setAttribute("ExportToExcelStatus", status);

    dateTimeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm");

    outFileName = getWorksheetOutputFileName(manager.getWorksheet().getId(),
            manager.getWorksheet().getSystemUserId());
    outFile = new File(outFileName);
    if (outFile.exists())
        throw new Exception(
                "An Excel file for this worksheet already exists, please delete it before trying to export");

    try {
        formatDO = dictionaryCache.getById(manager.getWorksheet().getFormatId());
    } catch (NotFoundException nfE) {
        formatDO = new DictionaryDO();
        formatDO.setEntry("DefaultTotal");
        formatDO.setSystemName("wf_total");
    } catch (Exception anyE) {
        throw new Exception("Error retrieving worksheet format: " + anyE.getMessage());
    }

    try {
        in = new FileInputStream(getWorksheetTemplateFileName(formatDO));
        wb = new HSSFWorkbook(in, true);
        createStyles(wb);
    } catch (FileNotFoundException fnfE) {
        try {
            wb = buildTemplate(formatDO);
            createStyles(wb);
        } catch (FileNotFoundException fnfE2) {
            throw new Exception("Error loading template file: " + fnfE2.getMessage());
        } catch (IOException ioE) {
            throw new Exception("Error loading workbook from template file: " + ioE.getMessage());
        }
    }

    loadStatuses();
    //        statusCells = new CellRangeAddressList();

    tCellNames = loadNamesByCellReference(wb);

    resultSheet = wb.getSheet("Worksheet");

    tRow = resultSheet.getRow(1);
    resultSheet.removeRow(tRow);

    overrideSheet = wb.getSheet("Overrides");

    status.setPercentComplete(5);
    session.setAttribute("ExportToExcelStatus", status);

    r = 1;
    o = 1;
    i = 0;
    aCount = 0;
    apMap = new HashMap<String, HashMap<Integer, ArrayList<AnalyteParameterViewDO>>>();
    waMap = new HashMap<Integer, ArrayList<WorksheetAnalysisViewDO>>();
    waLinkMap = new HashMap<Integer, WorksheetAnalysisViewDO>();
    wrMap = new HashMap<Integer, ArrayList<WorksheetResultViewDO>>();
    wqrMap = new HashMap<Integer, ArrayList<WorksheetQcResultViewDO>>();
    loadMaps(manager, waMap, waLinkMap, wrMap, wqrMap);
    aTotal = getAnalyses(manager).size();
    if (getItems(manager) != null) {
        for (WorksheetItemDO wiDO : getItems(manager)) {
            a = 0;
            waList = waMap.get(wiDO.getId());
            if (waList != null && waList.size() > 0) {
                for (WorksheetAnalysisViewDO waVDO : waList) {
                    aCount++;
                    status.setMessage("Exporting to Excel: Analysis " + aCount + " of " + aTotal);
                    status.setPercentComplete((int) (((double) (aCount - 1) / aTotal) * 90) + 5);
                    session.setAttribute("ExportToExcelStatus", status);

                    waLinkDO = waLinkMap.get(waVDO.getWorksheetAnalysisId());

                    row = resultSheet.createRow(r);

                    // position number
                    posNum = wiDO.getPosition().toString();
                    cell = row.createCell(0);
                    cell.setCellStyle(styles.get("row_no_edit"));
                    if (a == 0)
                        cell.setCellValue(posNum);

                    // accession number
                    cell = row.createCell(1);
                    cell.setCellStyle(styles.get("row_no_edit"));
                    cell.setCellValue(waVDO.getAccessionNumber());

                    cellNameIndex = i + "." + a;
                    if (waVDO.getAnalysisId() != null) {
                        isEditable = (waVDO.getFromOtherId() == null
                                && !Constants.dictionary().ANALYSIS_INPREP.equals(waVDO.getStatusId())
                                && !Constants.dictionary().ANALYSIS_RELEASED.equals(waVDO.getStatusId())
                                && !Constants.dictionary().ANALYSIS_CANCELLED.equals(waVDO.getStatusId()));

                        // description
                        cell = row.createCell(2);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue(waVDO.getDescription());

                        // qc link
                        cell = row.createCell(3);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        if (waLinkDO != null)
                            cell.setCellValue(waLinkDO.getAccessionNumber());
                        else
                            cell.setCellValue("");

                        // test name
                        cell = row.createCell(4);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue(waVDO.getTestName());

                        // method name
                        cell = row.createCell(5);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue(waVDO.getMethodName());

                        // analysis status
                        cell = row.createCell(6);
                        //                            if (isEditable)
                        //                                cell.setCellStyle(styles.get("row_edit"));
                        //                            else
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue(statusIdNameMap.get(waVDO.getStatusId()));
                        cellName = wb.createName();
                        cellName.setNameName("analysis_status." + i + "." + a);
                        cellName.setRefersToFormula("Worksheet!$" + CellReference.convertNumToColString(6) + "$"
                                + (row.getRowNum() + 1));
                        //                            statusCells.addCellRangeAddress(r, 6, r, 6);

                        wrList = wrMap.get(waVDO.getId());
                        if (wrList == null || wrList.size() == 0) {
                            // analyte
                            cell = row.createCell(7);
                            cell.setCellStyle(styles.get("row_no_edit"));
                            cell.setCellValue("NO ANALYTES DEFINED");

                            // reportable
                            cell = row.createCell(8);
                            cell.setCellStyle(styles.get("row_no_edit"));
                            cell.setCellValue("N");

                            createEmptyCellsForFormat(row, tRow);
                            r++;
                        } else {
                            r = createResultCellsForFormat(resultSheet, row, tRow, cellNameIndex, tCellNames,
                                    manager.getWorksheet(), waVDO, wrList, isEditable, apMap);
                        }

                        //
                        // Add override row to override sheet
                        //
                        oRow = overrideSheet.createRow(o);

                        // position number
                        cell = oRow.createCell(0);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        if (a == 0)
                            cell.setCellValue(posNum);

                        // accession number
                        cell = oRow.createCell(1);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue(waVDO.getAccessionNumber());

                        // description (override)
                        cell = oRow.createCell(2);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue(waVDO.getDescription());

                        // test name (overrride)
                        cell = oRow.createCell(3);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue(waVDO.getTestName());

                        // method name (override)
                        cell = oRow.createCell(4);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue(waVDO.getMethodName());

                        // users (override)
                        cell = oRow.createCell(5);
                        if (isEditable)
                            cell.setCellStyle(styles.get("row_edit"));
                        else
                            cell.setCellStyle(styles.get("row_no_edit"));
                        if (waVDO.getSystemUsers() != null)
                            cell.setCellValue(waVDO.getSystemUsers());
                        cellName = wb.createName();
                        cellName.setNameName("analysis_users." + cellNameIndex);
                        cellName.setRefersToFormula("Overrides!$" + CellReference.convertNumToColString(5) + "$"
                                + (oRow.getRowNum() + 1));

                        // started (override)
                        cell = oRow.createCell(6);
                        if (isEditable)
                            cell.setCellStyle(styles.get("datetime_edit"));
                        else
                            cell.setCellStyle(styles.get("datetime_no_edit"));
                        if (waVDO.getStartedDate() != null)
                            cell.setCellValue(dateTimeFormat.format(waVDO.getStartedDate().getDate()));
                        cellName = wb.createName();
                        cellName.setNameName("analysis_started." + cellNameIndex);
                        cellName.setRefersToFormula("Overrides!$" + CellReference.convertNumToColString(6) + "$"
                                + (oRow.getRowNum() + 1));

                        // completed (override)
                        cell = oRow.createCell(7);
                        if (isEditable)
                            cell.setCellStyle(styles.get("datetime_edit"));
                        else
                            cell.setCellStyle(styles.get("datetime_no_edit"));
                        if (waVDO.getCompletedDate() != null)
                            cell.setCellValue(dateTimeFormat.format(waVDO.getCompletedDate().getDate()));
                        cellName = wb.createName();
                        cellName.setNameName("analysis_completed." + cellNameIndex);
                        cellName.setRefersToFormula("Overrides!$" + CellReference.convertNumToColString(7) + "$"
                                + (oRow.getRowNum() + 1));
                        o++;
                    } else if (waVDO.getQcLotId() != null) {
                        // description
                        cell = row.createCell(2);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue(waVDO.getDescription());

                        // qc link
                        cell = row.createCell(3);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        if (waLinkDO != null)
                            cell.setCellValue(waLinkDO.getAccessionNumber());
                        else
                            cell.setCellValue("");

                        // test name
                        cell = row.createCell(4);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue("");

                        // method name
                        cell = row.createCell(5);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue("");

                        // analysis status
                        cell = row.createCell(6);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue("");

                        wqrList = wqrMap.get(waVDO.getId());
                        if (wqrList == null || wqrList.size() == 0) {
                            // analyte
                            cell = row.createCell(7);
                            cell.setCellStyle(styles.get("row_no_edit"));
                            cell.setCellValue("NO ANALYTES DEFINED");

                            // reportable
                            cell = row.createCell(8);
                            cell.setCellStyle(styles.get("row_no_edit"));
                            cell.setCellValue("N");

                            createEmptyCellsForFormat(row, tRow);

                            r++;
                        } else {
                            cellNameIndex = i + "." + a;
                            r = createQcResultCellsForFormat(resultSheet, row, tRow, cellNameIndex, tCellNames,
                                    manager.getWorksheet(), waVDO.getQcId(), wqrList, apMap);
                        }

                        //
                        // Add override row to override sheet
                        //
                        oRow = overrideSheet.createRow(o);

                        // position number
                        cell = oRow.createCell(0);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        if (a == 0)
                            cell.setCellValue(posNum);

                        // accession number
                        cell = oRow.createCell(1);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue(waVDO.getAccessionNumber());

                        // description (override)
                        cell = oRow.createCell(2);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue(waVDO.getDescription());

                        // test name (overrride)
                        cell = oRow.createCell(3);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue("");

                        // method name (override)
                        cell = oRow.createCell(4);
                        cell.setCellStyle(styles.get("row_no_edit"));
                        cell.setCellValue("");

                        // users (override)
                        cell = oRow.createCell(5);
                        cell.setCellStyle(styles.get("row_edit"));
                        if (waVDO.getSystemUsers() != null)
                            cell.setCellValue(waVDO.getSystemUsers());
                        cellName = wb.createName();
                        cellName.setNameName("analysis_users." + cellNameIndex);
                        cellName.setRefersToFormula("Overrides!$" + CellReference.convertNumToColString(5) + "$"
                                + (oRow.getRowNum() + 1));

                        // started (override)
                        cell = oRow.createCell(6);
                        cell.setCellStyle(styles.get("datetime_edit"));
                        if (waVDO.getStartedDate() != null)
                            cell.setCellValue(dateTimeFormat.format(waVDO.getStartedDate().getDate()));
                        cellName = wb.createName();
                        cellName.setNameName("analysis_started." + cellNameIndex);
                        cellName.setRefersToFormula("Overrides!$" + CellReference.convertNumToColString(6) + "$"
                                + (oRow.getRowNum() + 1));

                        // completed (override)
                        cell = oRow.createCell(7);
                        cell.setCellStyle(styles.get("datetime_no_edit"));
                        cellName = wb.createName();
                        cellName.setNameName("analysis_completed." + cellNameIndex);
                        cellName.setRefersToFormula("Overrides!$" + CellReference.convertNumToColString(7) + "$"
                                + (oRow.getRowNum() + 1));
                        o++;
                    }

                    a++;
                }
            }

            i++;
        }
    }

    status.setMessage("Exporting to Excel: Finalizing");
    status.setPercentComplete(95);
    session.setAttribute("ExportToExcelStatus", status);

    //
    // Create validators
    //
    //        statusConstraint = DVConstraint.createExplicitListConstraint(statuses);
    //        statusValidation = new HSSFDataValidation(statusCells, statusConstraint);
    //        statusValidation.setEmptyCellAllowed(true);
    //        statusValidation.setSuppressDropDownArrow(false);
    //        statusValidation.createPromptBox("Statuses", formatTooltip(statuses));
    //        statusValidation.setShowPromptBox(false);
    //        resultSheet.addValidationData(statusValidation);

    reportableColumn = new CellRangeAddressList(1, resultSheet.getPhysicalNumberOfRows() - 1, 8, 8);
    reportableConstraint = DVConstraint.createExplicitListConstraint(new String[] { "Y", "N" });
    reportableValidation = new HSSFDataValidation(reportableColumn, reportableConstraint);
    reportableValidation.setSuppressDropDownArrow(false);
    resultSheet.addValidationData(reportableValidation);

    //
    // Auto resize columns on result sheet and override sheet
    //
    resultSheet.autoSizeColumn(2, true); // Description
    resultSheet.autoSizeColumn(4, true); // Test
    resultSheet.autoSizeColumn(5, true); // Method
    resultSheet.autoSizeColumn(7, true); // Analyte

    overrideSheet.autoSizeColumn(2, true); // Description
    overrideSheet.autoSizeColumn(3, true); // Test
    overrideSheet.autoSizeColumn(4, true); // Method

    try {
        out = new FileOutputStream(outFileName);
        wb.write(out);
        out.close();
        Runtime.getRuntime().exec("chmod go+rw " + outFileName);
    } catch (Exception anyE) {
        throw new Exception("Error writing Excel file: " + anyE.getMessage());
    }

    status.setMessage("Exporting to Excel: Done");
    status.setPercentComplete(100);
    session.setAttribute("ExportToExcelStatus", status);

    return manager;
}