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

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

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

Introduction

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

Prototype

@Override
public HSSFName createName()

Source Link

Document

creates a new named range and add it to the model

Usage

From source file:com.wetts.base.utils.poi.excel.ExcelDao.java

License:Open Source License

/**
 * ??//from  www  . ja v a 2s .c om
 *
 * @param wb
 * @param name
 * @param expression
 * @return
 */
public static HSSFName createName(HSSFWorkbook wb, String name, String expression) {
    HSSFName refer = wb.getName(name);
    //?????
    if (refer == null) {
        refer = wb.createName();
        refer.setNameName(name);
    }
    //???
    refer.setRefersToFormula(expression);
    return refer;
}

From source file:mat.server.service.impl.XLSGenerator.java

/** Generate name.
 * /*  w ww.  j  a va  2s . com*/
 * @param wkbk - HSSFWorkbook.
 * @param nameStr - String.
 * @param referenceStr - String. * */
protected final void generateName(final HSSFWorkbook wkbk, final String nameStr, final String referenceStr) {
    // names are required for 508 testing
    Name name = wkbk.createName();
    name.setNameName(nameStr);
    name.setRefersToFormula(referenceStr);
}

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

License:Open Source License

private void finishSheet(HSSFSheet sheet, HSSFWorkbook wb, String qcName, String qcType, String sheetName) {
    int i, columnIndex;
    ArrayList<DictionaryDO> tempQcColumns;
    DictionaryDO dict;/*from   w  ww. j  a  va2 s  . c o m*/
    HashSet<Integer> emptyColumns;
    Name rangeName;
    Row row;
    String rangeFormula;

    if (qcColumns != null && !qcColumns.isEmpty())
        row = sheet.getRow(32);
    else
        row = sheet.getRow(3);
    emptyColumns = new HashSet<Integer>();
    for (i = 0; i < row.getLastCellNum(); i++) {
        if (i >= maxChars.size() || maxChars.get(i) == 0)
            emptyColumns.add(i);
    }

    setHeaderCells(sheet, qcName, qcType, sheetName);

    if (qcColumns != null && !qcColumns.isEmpty()) {
        tempQcColumns = new ArrayList<DictionaryDO>();
        tempQcColumns.addAll(qcColumns);
        for (i = tempQcColumns.size() - 1; i > -1; i--) {
            if (emptyColumns.contains(i + 5)) {
                tempQcColumns.remove(i);
                removeColumn(sheet, i + 5);
                maxChars.remove(i + 5);
            }
        }

        rangeName = getName(wb, sheet, "RowNumber");
        if (rangeName == null) {
            rangeName = wb.createName();
            rangeName.setSheetIndex(wb.getSheetIndex(sheet));
            rangeName.setNameName("RowNumber");
        }
        rangeFormula = sheet.getSheetName() + "!$" + CellReference.convertNumToColString(0) + "$33:" + "$"
                + CellReference.convertNumToColString(0) + "$" + (sheet.getLastRowNum() + 1);
        rangeName.setRefersToFormula(rangeFormula);

        /*
         * Create named ranges for the graph to be able to locate the appropriate
         * data
         */
        columnIndex = 5;
        for (i = 0; i < tempQcColumns.size(); i++) {
            dict = tempQcColumns.get(i);
            if (!DataBaseUtil.isEmpty(dict.getCode())) {
                rangeName = getName(wb, sheet, dict.getCode());
                if (rangeName == null) {
                    rangeName = wb.createName();
                    rangeName.setSheetIndex(wb.getSheetIndex(sheet));
                    rangeName.setNameName(dict.getCode());
                }
                rangeFormula = rangeName.getRefersToFormula();
                if (rangeFormula != null && rangeFormula.length() > 0
                        && !"$A$2".equals(rangeFormula.substring(rangeFormula.indexOf("!") + 1)))
                    rangeFormula += ",";
                else
                    rangeFormula = "";
                rangeFormula += sheet.getSheetName() + "!$" + CellReference.convertNumToColString(columnIndex)
                        + "$33:" + "$" + CellReference.convertNumToColString(columnIndex) + "$"
                        + (sheet.getLastRowNum() + 1);
                rangeName.setRefersToFormula(rangeFormula);
            }
            columnIndex++;
        }
        /*
         * make each column wide enough to show the longest string in it; the
         * width for each column is set as the maximum number of characters in
         * that column multiplied by 256; this is because the default width of
         * one character is 1/256 units in Excel
         */
        for (i = 5; i < maxChars.size(); i++)
            sheet.setColumnWidth(i, maxChars.get(i) * 256);
    } else if (worksheetHeaders != null && worksheetHeaders.size() > 0) {
        /*
         * make each column wide enough to show the longest string in it; the
         * width for each column is set as the maximum number of characters in
         * that column multiplied by 256; this is because the default width of
         * one character is 1/256 units in Excel
         */
        for (i = 0; i < maxChars.size(); i++)
            sheet.setColumnWidth(i, maxChars.get(i) * 256);
    }

    wb.setSheetName(wb.getSheetIndex(sheet), sheetName);
    sheet.setForceFormulaRecalculation(true);
    maxChars.clear();
}

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;/*ww w.  ja  v  a2 s.  co 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;
}

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

License:Open Source License

private HSSFWorkbook buildTemplate(DictionaryDO formatDO) throws Exception {
    int c;//from   www  . j  a  va2s .c o  m
    ArrayList<DictionaryDO> formatColumns;
    Cell hCell, tCell;
    FileInputStream in;
    HSSFSheet resultSheet;
    HSSFWorkbook wb;
    Name cellName;
    Row hRow, tRow;

    in = new FileInputStream(getWorksheetTemplateFileName("Base"));
    wb = new HSSFWorkbook(in, true);
    createStyles(wb);

    try {
        formatColumns = categoryCache.getBySystemName(formatDO.getSystemName()).getDictionaryList();
    } catch (Exception anyE) {
        throw new Exception("Error retrieving worksheet format: " + anyE.getMessage());
    }

    c = 9;
    resultSheet = wb.getSheet("Worksheet");
    hRow = resultSheet.getRow(0);
    tRow = resultSheet.getRow(1);
    for (DictionaryDO columnDO : formatColumns) {
        hCell = hRow.createCell(c);
        hCell.setCellStyle(styles.get("header"));
        hCell.setCellValue(columnDO.getEntry());

        tCell = tRow.createCell(c);
        tCell.setCellStyle(styles.get("row_edit"));

        cellName = wb.createName();
        cellName.setNameName(columnDO.getSystemName().substring(formatDO.getSystemName().length()));
        cellName.setRefersToFormula(
                "Worksheet!$" + CellReference.convertNumToColString(c) + "$" + (tRow.getRowNum() + 1));

        resultSheet.autoSizeColumn(c, true);
        c++;
    }

    return wb;
}

From source file:org.opentestsystem.delivery.testreg.rest.view.ExcelView.java

License:Open Source License

@SuppressWarnings("unchecked")
@Override//from   w  w  w  . j  a va2s . c  o  m
protected void buildExcelDocument(final Map<String, Object> model, final HSSFWorkbook workbook,
        final HttpServletRequest request, final HttpServletResponse response) {
    // get data model which is passed by the Spring container
    final List<TestRegistrationBase> entityList = (List<TestRegistrationBase>) model.get(DATA_LIST);
    final String formatType = ((String) model.get(FORMAT_TYPE)).toUpperCase();
    String exportType = null;
    if (formatType.equalsIgnoreCase(FormatType.STUDENT.name())) {
        exportType = ((String) model.get(EXPORT_TYPE)).toUpperCase();
        HSSFName name = workbook.createName();
        // display filename to return to the client
        name.setNameName(exportType);
    }

    final Sheet sheet = workbook.createSheet(formatType);
    sheet.setDefaultColumnWidth(30);

    String headerColumns[] = new String[100];
    if (formatType.equalsIgnoreCase(FormatType.STUDENT.name())) {
        // Based on export type for FormatType( STUDENT ) assigning headers for export file 
        if (exportType.equalsIgnoreCase(ExportStudentFormatEnums.studentsPlusAccommodations.name())) {
            List<String> headerStudentList = templateDownloadMap.get(FormatType.STUDENT);
            String[] headerCodesTemp = headersMap.get(FormatType.DESIGNATEDSUPPORTSANDACCOMMODATIONS.name());
            List<String> headerCodes = Arrays.asList(headerCodesTemp);
            List<String> headerAccommodationList = headerCodes.subList(2, headerCodes.size());
            String lastElement = headerStudentList.get(headerStudentList.size() - 1);
            List<String> combinedHeaders = ListUtils
                    .union(headerStudentList.subList(0, headerStudentList.size() - 1), headerAccommodationList);
            combinedHeaders.add(lastElement);
            headerColumns = combinedHeaders.toArray(new String[combinedHeaders.size()]);
        } else if (exportType.equalsIgnoreCase(ExportStudentFormatEnums.studentsOnly.name())) {
            List<String> headerStudentList = templateDownloadMap.get(FormatType.STUDENT);
            headerColumns = headerStudentList.toArray(new String[headerStudentList.size()]);
        } else if (exportType.equalsIgnoreCase(ExportStudentFormatEnums.accommodationsOnly.name())) {
            headerColumns = headersMap.get(FormatType.DESIGNATEDSUPPORTSANDACCOMMODATIONS.name());
        } else if (exportType.equalsIgnoreCase(ExportStudentFormatEnums.explicitEligibility.name())) {
            List<String> headerExplicitEligibilityList = templateDownloadMap
                    .get(FormatType.EXPLICITELIGIBILITY);
            headerColumns = headerExplicitEligibilityList
                    .toArray(new String[headerExplicitEligibilityList.size()]);
        }
    } else {
        headerColumns = this.headersMap.get(formatType);
    }
    final Row header = sheet.createRow(0);
    for (int i = 0; i < headerColumns.length; i++) {
        final Cell cell = header.createCell(i);
        cell.setCellValue(headerColumns[i]);
    }
    // DataRow Begin
    if (!CollectionUtils.isEmpty(entityList)) {
        int rowCount = 1;
        for (final TestRegistrationBase entity : entityList) {
            // if format is user then we need to customize export to include role associations as separate rows
            switch (FormatType.valueOf(formatType)) {
            case USER:
                rowCount = exportUser(sheet, rowCount, entity);
                break;
            case STUDENTGROUP:
                rowCount = exportStudentGroup(sheet, rowCount, entity);
                break;
            case STUDENT:
                rowCount = exportStudent(sheet, rowCount, (Student) entity, exportType);
                break;
            default:
                final Row rowData = sheet.createRow(rowCount);
                final String[] columnData = this.testRegistrationViewTransformer.transform(entity);
                addData(rowData, 0, columnData.length, columnData);
            }
            rowCount++;
        }
    }
}