Java 类org.apache.poi.ss.usermodel.Row 实例源码

@RequestMapping(value = "/test2.xlsx", produces = MediaType.APPLICATION_OCTET_STREAM_VALUE)
@ResponseBody
byte[] testDown() throws IOException, InvalidFormatException {
    Workbook workbook = new SXSSFWorkbook();
    Sheet sheet = workbook.createSheet();
    for (int i = 0; i < 60000; i++) {
        Row newRow = sheet.createRow(i);
        for (int j = 0; j < 100; j++) {
            newRow.createCell(j).setCellValue("test" + Math.random());
        }
    }
    ByteArrayOutputStream os = new ByteArrayOutputStream();
    workbook.write(os);
    byte[] bytes = os.toByteArray();
    return bytes;
}

/**
 * Método que se encarga de leer el libro de excel
 * cuya ruta recibimos en el constructor y devuelve una lista 
 * de ciudadanos
 * @return lista de Usuarios de la base de datos
 */
public List<CitizenDB> readExcelFile(){
    List<CitizenDB> citizens = new ArrayList<CitizenDB>(); 
    // para cada una de las hojas presentes en el documento de excel
    for(int i=0;i < workbook.getNumberOfSheets();i++){
        XSSFSheet sheet = this.workbook.getSheetAt(i);
        Iterator<Row> rowIterator = sheet.iterator();
        Row row;
        int counter = 0;
        //para cada fila de la hoja
        while(rowIterator.hasNext()){
            row = rowIterator.next();
            if (counter > 0) { //omitimos la cabecera (hay que mirar si hay un metodo de la API)
                Iterator<Cell> cellIterator = row.cellIterator();
                int j = 0;
                CitizenDB user = new CitizenDB();
                while (cellIterator.hasNext())  
                    this.insertCitizenField(user, j++, cellIterator.next());    
                user.setPassword(new GenerationPassword().passwordGenerator());
                citizens.add(user);
            }
            counter++;
        }
    }
    return citizens;
}

/** 创建不需要合并单元格的表头
 * @param fieldInfoMap
 * @param headCellStyleMap
 * @param row
 * @param fieldList
 * @param cellNum
 */
private static void doCreateSheetSingleHeadRow(Map<Field, ExportFieldInfo> fieldInfoMap,
        Map<Field, CellStyle> headCellStyleMap, Row row, List<SortableField> fieldList, int cellNum) {

    Cell cell;
    String value;
    for(int i = 0 ; i < fieldList.size() ; i++ , cellNum++){
        Field field = fieldList.get(i).getField();
        value = fieldInfoMap.get(field).getHeadName();
        cell = row.createCell(cellNum);
        cell.setCellValue(value);
        if( headCellStyleMap != null && headCellStyleMap.get(field) != null ){
            cell.setCellStyle(headCellStyleMap.get(field));
        }
    }
}

private List<String> getClasses(Row classRow) {
    //声明一个list，用来存放所有的字段类型
    List<String> classList = new ArrayList<String>();
    //判断这一行是否为空
    if (classRow != null) {
        //遍历这一行的所有单元格
        for (int i = 0; i < classRow.getLastCellNum(); i++) {
            //获取单元格
            Cell cell = classRow.getCell(i);
            //判断单元格是否为空
            if (cell != null) {
                //将单元格的内容存放到list中
                classList.add(cell.getStringCellValue());
            }
        }
    }
    //返回所有的字段类型
    return classList;
}

public static void createWorkBook() throws IOException {
    // ����excel������
    final Workbook wb = new XSSFWorkbook();
    // ����sheet��ҳ��
    final Sheet sheet1 = wb.createSheet("Sheet_1");
    final Sheet sheet2 = wb.createSheet("Sheet_2");

    for (int i = 0; i < 20; i = i + 2) {
        final Row row1 = sheet1.createRow(i);
        final Row row2 = sheet2.createRow(i);

        for (int j = 0; j < 10; j++) {

            row1.createCell(j).setCellValue(j + "new");
            row2.createCell(j).setCellValue(j + "This is a string");

        }
    }
    // ����һ���ļ� ����Ϊworkbooks.xlsx
    final FileOutputStream fileOut = new FileOutputStream("d:\\workbooks.xlsx");
    // �����洴���Ĺ�����������ļ���
    wb.write(fileOut);
    // �ر������
    fileOut.close();
}

/**
 * Exports a single sheet to a file
 *
 * @param sheet
 * @throws FactoryConfigurationError
 * @throws XMLStreamException
 * @throws UnsupportedEncodingException
 * @throws FileNotFoundException
 */
private void export(final XSSFSheet sheet, final XMLStreamWriter out)
        throws UnsupportedEncodingException, XMLStreamException, FactoryConfigurationError, FileNotFoundException {
    boolean isFirst = true;
    final Map<String, String> columns = new HashMap<String, String>();
    final String sheetName = sheet.getSheetName();
    System.out.print(sheetName);
    out.writeStartElement("sheet");
    out.writeAttribute("name", sheetName);
    Iterator<Row> rowIterator = sheet.rowIterator();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        if (isFirst) {
            isFirst = false;
            this.writeFirstRow(row, out, columns);
        } else {
            this.writeRow(row, out, columns);
        }
    }
    out.writeEndElement();
    System.out.println("..");
}

/**
 * 解析行
 * @param row
 * @return
 */
protected List<Object> parseRow(Row row) {
    List<Object> rowData = new ArrayList<Object>();

    for (int i = 0; i < row.getLastCellNum(); i++) {
        Cell cell = row.getCell(i);
        Object cellObj=null;
        if(cell!=null){
            cellObj = parseCell(cell);
        }
        rowData.add(cellObj);
    }
    /*// 迭代 一行的各个单元格
    Iterator<Cell> cellIterator = row.iterator();
    // 遍历一行多列
    while (cellIterator.hasNext()) {
        Cell cell = cellIterator.next();
        Object cellObj = parseCell(cell);
        rowData.add(cellObj);
    }*/
    return rowData;
}

public <T> List<T> unmarshal(Class<T> type) {
    Workbook workbook = poijiWorkbook.workbook();
    Sheet sheet = workbook.getSheetAt(options.sheetIndex());

    int skip = options.skip();
    int maxPhysicalNumberOfRows = sheet.getPhysicalNumberOfRows() + 1 - skip;
    List<T> list = new ArrayList<>(maxPhysicalNumberOfRows);

    for (Row currentRow : sheet) {

        if (skip(currentRow, skip))
            continue;

        if (isRowEmpty(currentRow))
            continue;

        if (maxPhysicalNumberOfRows > list.size()) {
            T t = deserialize0(currentRow, type);
            list.add(t);
        }
    }

    return list;
}

/**
 * 按报表模板格式写分页页眉标题
 * 
 * @author      ZhengWei(HY)
 * @createDate  2017-06-25
 * @version     v1.0
 *
 * @param i_DataWorkbook   数据工作薄
 * @param i_DataSheet      数据工作表
 * @param io_RTotal        将数据写入Excel时的辅助统计信息
 * @param io_RSystemValue  系统变量信息
 * @param i_Datas          数据
 * @param i_RTemplate      报表模板对象
 */
public final static void writeTitlePageHeader(RWorkbook i_DataWorkbook ,Sheet i_DataSheet ,RTotal io_RTotal ,RSystemValue io_RSystemValue ,Object i_Datas ,RTemplate i_RTemplate) 
{
    Sheet v_TemplateSheet    = i_RTemplate.getTemplateSheet();
    int   v_TemplateRowCount = io_RTotal.getTitlePageHeaderCount();
    int   v_ExcelRowIndex    = io_RTotal.getExcelRowIndex();

    copyMergedRegionsTitlePageHeader(i_RTemplate ,i_DataSheet ,io_RTotal);  // 按模板合并单元格
    copyImagesTitlePageHeader(       i_RTemplate ,i_DataSheet ,io_RTotal);  // 按模板复制图片

    io_RSystemValue.setPageNo(io_RSystemValue.getPageNo() + 1);

    for (int v_RowNo=0; v_RowNo<v_TemplateRowCount; v_RowNo++)
    {
        int v_TemplateRowNo = i_RTemplate.getTitlePageHeaderBeginRow() + v_RowNo;
        Row v_TemplateRow   = v_TemplateSheet.getRow(v_TemplateRowNo);

        int v_DataRowNo = v_RowNo + v_ExcelRowIndex;
        Row v_DataRow   = i_DataSheet.createRow(v_DataRowNo);
        io_RTotal.addExcelRowIndex(1);

        copyRow(i_RTemplate ,v_TemplateRow ,i_DataWorkbook ,io_RTotal ,io_RSystemValue ,v_DataRow ,i_Datas);
    }
}

public static void main(String[] args) throws Throwable {
        SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
        Sheet sh = wb.createSheet();
        for (int rownum = 0; rownum < 1000; rownum++) {
            Row row = sh.createRow(rownum);
            Row row1 = sh.createRow(rownum);
            for (int cellnum = 0; cellnum < 10; cellnum++) {
                Cell cell = row.createCell(cellnum);
                String address = new CellReference(cell).formatAsString();
                cell.setCellValue(address);
            }

        }

        // Rows with rownum < 900 are flushed and not accessible
//        for (int rownum = 0; rownum < 103857; rownum++) {
//            Assert.assertNull(sh.getRow(rownum));
//        }
//
//        // ther last 100 rows are still in memory
//        for (int rownum = 103857; rownum < 104857; rownum++) {
//            Assert.assertNotNull(sh.getRow(rownum));
//        }

        File file = new File("C:\\Users\\FlyingHe\\Desktop", "datas.xlsx");
        FileOutputStream out = new FileOutputStream(file);
        wb.write(out);
        out.close();

        // dispose of temporary files backing this workbook on disk
        wb.dispose();
    }

private void writeRow(final Row row, final XMLStreamWriter out, final Map<String, String> columns) {
    try {
        int rowIndex = row.getRowNum();
        out.writeStartElement("row");
        final String rowNum = String.valueOf(rowIndex);
        out.writeAttribute("row", rowNum);
        int count = 0;
        Iterator<Cell> cellIterator = row.iterator();
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            int columnIndex = cell.getColumnIndex();
            if (this.exportEmptyCells) {
                while (count < columnIndex) {
                    this.writeAnyCell(rowIndex, count, null, out, columns);
                    count++;
                }
            }
            this.writeCell(cell, out, columns);
            count++;
        }
        out.writeEndElement();
    } catch (XMLStreamException e) {
        e.printStackTrace();
    }

}

/**
 * 按报表模板格式写入合计（暂时不支持分页功能）
 * 
 * @author      ZhengWei(HY)
 * @createDate  2017-03-18
 * @version     v1.0
 *
 * @param i_DataWorkbook   数据工作薄
 * @param i_DataSheet      数据工作表
 * @param io_RTotal        将数据写入Excel时的辅助统计信息
 * @param io_RSystemValue  系统变量信息
 * @param i_Datas          数据
 * @param i_RTemplate      报表模板对象
 */
public final static void writeTotal(RWorkbook i_DataWorkbook ,Sheet i_DataSheet ,RTotal io_RTotal ,RSystemValue io_RSystemValue, Object i_Datas ,RTemplate i_RTemplate) 
{
    Sheet v_TemplateSheet         = i_RTemplate.getTemplateSheet();
    int   v_TemplateRowCountTotal = i_RTemplate.getRowCountTotal();
    int   v_ExcelRowIndex         = io_RTotal.getExcelRowIndex();

    copyMergedRegionsTotal(i_RTemplate ,i_DataSheet ,io_RTotal);  // 按模板合并单元格
    copyImagesTotal(       i_RTemplate ,i_DataSheet ,io_RTotal);  // 按模板复制图片

    for (int v_RowNo=0; v_RowNo<v_TemplateRowCountTotal; v_RowNo++) 
    {
        int v_TemplateRowNo = i_RTemplate.getTotalBeginRow() + v_RowNo;
        Row v_TemplateRow   = v_TemplateSheet.getRow(v_TemplateRowNo);

        int v_DataRowNo = v_RowNo + v_ExcelRowIndex;
        Row v_DataRow   = i_DataSheet.createRow(v_DataRowNo);
        io_RTotal.addExcelRowIndex(1);
        io_RTotal.addRealDataCount(1);

        copyRow(i_RTemplate ,v_TemplateRow ,i_DataWorkbook ,io_RTotal ,io_RSystemValue ,v_DataRow ,i_Datas);
    }
}

public CommentWriter(OutputStream outputStream) throws IOException{
    this.outputStream = outputStream;

    this.workbook = new XSSFWorkbook();
    this.sheet = workbook.createSheet("Comments");

    Row row = sheet.createRow(0);
    Cell cell = row.createCell(0);
    cell.setCellValue("#");

    cell = row.createCell(1);
    cell.setCellValue("comment");

    cell = row.createCell(2);
    cell.setCellValue("timestamp");

    rowCount = 1;
}

/** 获取表头名
 * @param sheet
 * @param headRow
 * @return
 */
private static List<String> createHeadNameList(Sheet sheet ,int headRow){
    List<String> headNameList;
    Row row;
    int cellCount;
    Cell cell;
    try {
        row = sheet.getRow(headRow);
        cellCount = row.getPhysicalNumberOfCells();
        headNameList = new ArrayList<String>();
        cell = null;
        for(int i = 0 ; i < cellCount ; i++){
            cell = row.getCell(i);
            headNameList.add(cell.getStringCellValue());            
        }
    } catch (Exception e) {
        e.printStackTrace();
        return null;
    }
    return headNameList;
}

private static <T> T convertWithMethod(Row row, Class<T> clazz) throws IllegalAccessException, InstantiationException, ParseException, InvocationTargetException {
    Field[] fields = new Field[]{};
    Class temp = clazz;
    while(temp != null){
        fields = ArrayUtils.addAll(temp.getDeclaredFields(),fields);
        temp = temp.getSuperclass();
    }
    Object object = clazz.newInstance();
    for (Field field : fields) {
        ExcelCellField filedExcelAnnotation = getAnnotationCellFiled(field.getAnnotations());
        if (filedExcelAnnotation == null) {
            continue;
        }
        Class<?> fieldType = field.getType();
        Integer index = filedExcelAnnotation.index();
        String propertyName = field.getName();
        Object value = getValue(fieldType, row.getCell(index), filedExcelAnnotation);
        if (value != null) {
            BeanUtils.setProperty(object, propertyName, value);
        }
    }
    return (T) object;
}

public void print () {
    Sheet sheet =  getOrCreateSummary();
    Iterator<Row> rows = sheet.rowIterator();
    int index=0;
    while (rows.hasNext()) {
        Row row = (Row) rows.next();
        System.out.println("Row ---> " + index);
        Spliterator<Cell> cells = row.spliterator();
        cells.forEachRemaining(new Consumer<Cell> () {
            @Override
            public void accept(Cell cell) {
                System.out.print(cell.toString());
                System.out.print(";");
            }
        });
        System.out.println();
        index++;
    }
}

private static void createRows(List<String> keyList,
        Map<String, ResourceBundle> defaultProperties,
        Map<String, Properties> localizedProperties, Sheet sheet,
        List<String> localeList, String sheetName) {
    sheet.createFreezePane(1, 1);
    int rowIdx = 1;
    int colIdx = 0;
    for (String key : keyList) {
        Row row = sheet.createRow(rowIdx++);
        colIdx = 0;
        row.createCell(colIdx++).setCellValue(key);

        for (String locale : localeList) {
            String cellValue = null;
            cellValue = getCellValue(defaultProperties,
                    localizedProperties, key, locale, sheetName);
            row.createCell(colIdx++).setCellValue(cellValue);
        }
    }
}

private static List<String> readFirstRow(Sheet sheet)
        throws TranslationImportException, ValidationException {
    List<String> localeStringList = new ArrayList<String>();
    Row row = sheet.getRow(0);
    if (row != null) {
        int colIdx = 1; // skip the first col it contains the keys
        String localeString = "";
        while (true) {
            localeString = getCellValue(row, colIdx++, true);
            if (localeString == null) {
                break;
            }
            if (StandardLanguage.isStandardLanguage(localeString,
                    StandardLanguage.COLUMN_HEADING_SUFFIX)) {
                localeStringList.add(localeString);
                continue;
            }
            validateLocale(localeString);
            localeString = localeString.toLowerCase();
            localeStringList.add(localeString);
        }
    }
    return localeStringList;
}

/**
 * 向当前Sheet第一行(1-based)写入标题,若用户没有开启写入标题总开关(即{@link #isWriteTitle}为false),
 * 或者{@link #titles}为空则不会做任何操作
 */
private void writeTitle() {
    if (!this.isWriteTitle || this.titles == null || this.titles.isEmpty()) {
        return;
    }
    this.currentRowInSheet++;
    Row row = this.currentSheetPO.createRow(this.currentRowInSheet);
    row.setHeight(this.rowHeight < 0 ? -1 : this.rowHeight);
    for (int i = 0; i < this.titles.size(); i++) {
        Cell cell = row.createCell(i);
        cell.setCellStyle(this.defaultTitleCellStyle);
        cell.setCellValue(this.titles.get(i));
    }
    this.realRowInSheet++;
    this.realRowInExcel++;
}

private List<String> getAnnotations(Row annotationRow) {
    //声明一个list，用来存放所有的字段说明
    List<String> annotationList = new ArrayList<String>();
    //判断，字段说明那一行是否为空
    if (annotationRow != null) {
        //遍历字段说明这一行所有的单元格
        for (int i = 0; i < annotationRow.getLastCellNum(); i++) {
            //获取单元格
            Cell cell = annotationRow.getCell(i);
            //判断单元格是否为空
            if (cell != null) {
                //将单元格中的内容放入List中
                annotationList.add(cell.getStringCellValue());
            }
        }
    }
    //返回所有的字段说明
    return annotationList;
}

public void feedDetailsSheet(Sheet sheet, boolean exportAsTemplate, List<XLTestStep> xLTestSteps) {
    int index = 0;
    for (XLTestStep xLTestStep : xLTestSteps) {
        index++;
        Row row = sheet.createRow(index);
        Cell cell = row.createCell(STEPNAME_INDEX);
        cell.setCellValue(xLTestStep.getName());
        cell = row.createCell(EXPECTED_OR_ACTION_INDEX);
        cell.setCellValue(xLTestStep.getExpected());
        cell = row.createCell(RESULT_INDEX);
        if (exportAsTemplate)
            cell.setCellValue("");
        else
            cell.setCellValue(xLTestStep.getActual());
        cell = row.createCell(STATUS_INDEX);
        formatCellStatus(sheet, cell);
        if (exportAsTemplate)
            cell.setCellValue("");
        else
            cell.setCellValue(Integer.parseInt(xLTestStep.getStatus()));
    }
    this.autoSize(sheet, new int[] { 0, 1, 2, 3 });
}

private void setSheetData(SheetData data, String group) {
    data.setCurrentGroup(group);
    // start from 1
    data.setCurrentIndex(1);
    // get sheet
    Sheet vSheet = getWorkBook().getSheet(group);
    Assert.notNull(vSheet, "Can't get sheet with name: " + group);
    data.setSheet(vSheet);
    // get row number
    int vRowCount = vSheet.getLastRowNum() + 1;
    data.setRowCount(vRowCount);
    // get first row
    Row vRow = vSheet.getRow(0);
    Assert.notNull(vRow, "Invalid format: first row must be title");
    // get column number
    int vColumnCount = vRow.getLastCellNum();
    String[] vTitles = new String[vColumnCount];
    // read titles
    for (int i = 0; i < vColumnCount; ++i) {
        Cell vCell = vRow.getCell(i);
        vTitles[i] = vCell.getStringCellValue();
    }
    data.setTitles(vTitles);
}

/**
 * 按报表模板格式写入小计（暂时不支持分页功能）
 * 
 * @author      ZhengWei(HY)
 * @createDate  2017-03-27
 * @version     v1.0
 *
 * @param i_DataWorkbook   数据工作薄
 * @param i_DataSheet      数据工作表
 * @param io_RTotal        将数据写入Excel时的辅助统计信息
 * @param io_RSystemValue  系统变量信息
 * @param i_Datas          数据
 * @param i_RTemplate      报表模板对象
 */
public final static void writeSubtotal(RWorkbook i_DataWorkbook ,Sheet i_DataSheet ,RTotal io_RTotal ,RSystemValue io_RSystemValue, Object i_Datas ,RTemplate i_RTemplate) 
{
    Sheet v_TemplateSheet            = i_RTemplate.getTemplateSheet();
    int   v_TemplateRowCountSubtotal = i_RTemplate.getRowCountSubtotal();
    int   v_ExcelRowIndex            = io_RTotal.getExcelRowIndex();

    copyMergedRegionsSubtotal(i_RTemplate ,i_DataSheet ,io_RTotal);  // 按模板合并单元格
    copyImagesSubtotal(       i_RTemplate ,i_DataSheet ,io_RTotal);  // 按模板复制图片

    for (int v_RowNo=0; v_RowNo<v_TemplateRowCountSubtotal; v_RowNo++) 
    {
        int v_TemplateRowNo = i_RTemplate.getSubtotalBeginRow() + v_RowNo;
        Row v_TemplateRow   = v_TemplateSheet.getRow(v_TemplateRowNo);

        int v_DataRowNo = v_RowNo + v_ExcelRowIndex;
        Row v_DataRow   = i_DataSheet.createRow(v_DataRowNo);
        io_RTotal.addExcelRowIndex(1);
        io_RTotal.addRealDataCount(1);

        copyRow(i_RTemplate ,v_TemplateRow ,i_DataWorkbook ,io_RTotal ,io_RSystemValue ,v_DataRow ,i_Datas);
    }
}

private <T> T deserialize0(Row currentRow, Class<T> type) {
    T instance;
    try {
        instance = type.getDeclaredConstructor().newInstance();
    } catch (NoSuchMethodException | InvocationTargetException | IllegalAccessException | InstantiationException e) {
        throw new PoijiInstantiationException("Cannot create a new instance of " + type.getName());
    }

    return setFieldValue(currentRow, type, instance);
}

/**
 * 获取指定单元格的值
 *
 * @param rowNumber  行数，从1开始
 * @param cellNumber 列数，从1开始
 * @return 该单元格的值
 */
public String getCellValue(int rowNumber, int cellNumber) {
    String result;
    checkRowAndCell(rowNumber, cellNumber);
    Sheet sheet = this.workbook.getSheet(this.sheetName);
    Row row = sheet.getRow(--rowNumber);
    Cell cell = row.getCell(--cellNumber);
    switch (cell.getCellTypeEnum()) {
        case BLANK:
            result = cell.getStringCellValue();
            break;
        case BOOLEAN:
            result = String.valueOf(cell.getBooleanCellValue());
            break;
        case ERROR:
            result = String.valueOf(cell.getErrorCellValue());
            break;
        case FORMULA:
            result = cell.getCellFormula();
            break;
        case NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
                result = format.format(cell.getDateCellValue());
            } else {
                result = String.valueOf(cell.getNumericCellValue());
            }
            break;
        case STRING:
            result = cell.getRichStringCellValue().getString();
            break;
        default:
            result = cell.getStringCellValue();
            break;
    }
    return result;
}

private void dataTableTitile(Sheet s, String titile, CellStyle csTitulo, CellStyle csTituloP,CellStyle csTituloTabelaNBorder) {

    Row r = s.createRow(linha);
    Cell c = r.createCell(2);
    createCellM(c, r, s, csTitulo, linha, linha + 3, ConfigDoc.Empresa.NOME, 1, 22);
    linha += 4;

    r = s.createRow(linha);
    createCellM(c, r, s, csTituloP, linha, linha, ConfigDoc.Empresa.ENDERECO, 1, 22);
    linha++;

    r = s.createRow(linha);
    createCellM(c, r, s, csTituloP, linha, linha, ConfigDoc.Empresa.CAIXAPOSTAL, 1, 22);
    linha++;

    r = s.createRow(linha);
    createCellM(c, r, s, csTituloP, linha, linha, ConfigDoc.Empresa.TELEFAX + " " + ConfigDoc.Empresa.EMAIL, 1, 22);
    linha++;

    r = s.createRow(linha);
    createCellM(c, r, s, csTituloP, linha, linha, ConfigDoc.Empresa.SOCIEDADE, 1, 22);
    linha += 3;

    r = s.createRow(linha);
    createCellM(c, r, s, csTituloTabelaNBorder, linha, linha, "TOTAL PREMIUM COLLECTED ON TRAVEL INSURANCE AND TAXES FOR "+titile, 1, 10);
    linha += 2;
}

private Optional<DataSourceFieldDefinition<?>> cellToFieldDefinitionTransformer(final Cell headerCell,
                                                                                final Row dataRow) {

    notNull(headerCell, "Mandatory argument 'headerCell' is missing.");
    notNull(dataRow, "Mandatory argument 'dataRow' is missing.");
    final Cell cell = dataRow.getCell(headerCell.getColumnIndex());
    switch (cell.getCellTypeEnum()) {
        case BOOLEAN:
            return Optional.of(
                    new DefaultDataSourceFieldDefinition<>(headerCell.getStringCellValue(), Boolean.class));
        case NUMERIC:
            if (cellAppearsToBeADateType(headerCell, cell))
                return Optional.of(
                        new DefaultDataSourceFieldDefinition<>(headerCell.getStringCellValue(), Date.class));
            return Optional.of(
                    new DefaultDataSourceFieldDefinition<>(headerCell.getStringCellValue(), BigDecimal.class));
        case FORMULA:
        case ERROR:
            throw new IllegalStateException("Cell type not supported:" + cell.getCellTypeEnum());
        case STRING:
        case BLANK:
        case _NONE:
        default:
            return Optional.of(
                    new DefaultDataSourceFieldDefinition<>(headerCell.getStringCellValue(), String.class));
    }
}

public static <T> List<T> convertWithConstructor(Iterator<Row> rows, Class<T> clazz, boolean hasHeader) {
    List<T> dataList = Lists.newArrayList();
    while (rows.hasNext()) {
        Row row = rows.next();
        if (hasHeader && row.getRowNum() < 1) {
            continue;
        }
        try {
            dataList.add(convert(row, clazz));
        } catch (Exception e) {
            throw new RuntimeException(String.format(" convertWithConstructor has error: message=%s", e.getMessage()));
        }
    }
    return dataList;
}

public static ByteArrayOutputStream build(Long examId, Collection<Long> childIds) throws IOException {

        List<ExamRecord> examRecords = Ebean.find(ExamRecord.class)
                .fetch("examScore")
                .where()
                .eq("exam.parent.id", examId)
                .in("exam.id", childIds)
                .findList();
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("Exam records");
        String[] headers = ExamScore.getHeaders();
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            headerRow.createCell(i).setCellValue(headers[i]);
        }
        for (ExamRecord record : examRecords) {
            String[] data = record.getExamScore().asArray(record.getStudent(), record.getTeacher(), record.getExam());
            Row dataRow = sheet.createRow(examRecords.indexOf(record) + 1);
            for (int i = 0; i < data.length; ++i) {
                dataRow.createCell(i).setCellValue(data[i]);
            }
        }
        IntStream.range(0, headers.length).forEach(i -> sheet.autoSizeColumn(i, true));
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        wb.write(bos);
        bos.close();
        return bos;
    }

/**
 * 按报表模板格式写入数据（支持分页页眉、分页页脚）
 * 
 * @author      ZhengWei(HY)
 * @createDate  2017-06-25
 * @version     v1.0
 *
 * @param i_DataWorkbook   数据工作薄
 * @param i_DataSheet      数据工作表
 * @param io_RTotal        将数据写入Excel时的辅助统计信息
 * @param io_RSystemValue  系统变量信息
 * @param i_Datas          数据
 * @param i_RTemplate      报表模板对象
 */
public final static void writeDataPage(RWorkbook i_DataWorkbook ,Sheet i_DataSheet ,RTotal io_RTotal ,RSystemValue io_RSystemValue, Object i_Datas ,RTemplate i_RTemplate) 
{
    Sheet v_TemplateSheet    = i_RTemplate.getTemplateSheet();
    int   v_TemplateRowCount = i_RTemplate.getRowCountData();
    int   v_ExcelRowIndex    = io_RTotal.getExcelRowIndex();
    int   v_PageIndex        = (io_RTotal.getRealDataCount() + io_RTotal.getTitleCount() * i_RTemplate.getTitleRatio() - i_RTemplate.getTitlePageHeaderRate()) % i_RTemplate.getPerPageRowSize();

    // 创建分页页眉。模板的"行（可对应Excel中的多行）"按一个不可再被分割的整体对待，固没有写在下面的For语句中。
    if ( v_PageIndex == 0 || io_RTotal.getRealDataCount() == i_RTemplate.getTitlePageHeaderFirstWriteByRealDataCount() )
    {
        writeTitlePageHeader(i_DataWorkbook ,i_DataSheet ,io_RTotal ,io_RSystemValue ,i_Datas ,i_RTemplate);
        v_ExcelRowIndex += io_RTotal.getTitlePageHeaderCount();
    }

    copyMergedRegionsData(i_RTemplate ,i_DataSheet ,io_RTotal);  // 按模板合并单元格
    copyImagesData(       i_RTemplate ,i_DataSheet ,io_RTotal);  // 按模板复制图片

    for (int v_RowNo=0; v_RowNo<v_TemplateRowCount; v_RowNo++) 
    {
        int v_TemplateRowNo = i_RTemplate.getDataBeginRow() + v_RowNo;
        Row v_TemplateRow   = v_TemplateSheet.getRow(v_TemplateRowNo);

        int v_DataRowNo = v_RowNo + v_ExcelRowIndex;
        Row v_DataRow   = i_DataSheet.createRow(v_DataRowNo);
        io_RTotal.addExcelRowIndex(1);
        io_RTotal.addRealDataCount(1);

        copyRowPageFooter(i_RTemplate ,v_TemplateRow ,i_DataWorkbook ,io_RTotal ,io_RSystemValue ,v_DataRow ,i_Datas);
    }

    // 创建分页页脚。模板的"行（可对应Excel中的多行）"按一个不可再被分割的整体对待，固没有写在下面的For语句中。
    v_PageIndex = (io_RTotal.getRealDataCount() + io_RTotal.getTitleCount() * i_RTemplate.getTitleRatio() - i_RTemplate.getTitlePageHeaderRate()) % i_RTemplate.getPerPageRowSize();
    if ( (v_PageIndex == 0 && io_RTotal.getRealDataCount() >= 1) || io_RTotal.getRealDataCount() == i_RTemplate.getTitlePageHeaderFirstWriteByRealDataCount() )
    {
        writeTitlePageFooter(i_DataWorkbook ,i_DataSheet ,io_RTotal ,io_RSystemValue ,i_Datas ,i_RTemplate);
        v_ExcelRowIndex += io_RTotal.getTitlePageFooterCount();
    }
}

public void parseRow(Row row, SheetFormat sf, Object obj) throws RowParsingException {

        Iterator<Cell> cellItr = row.iterator();
        cellItr.next(); // for sr. no. column skip
        for (ColumnFormat field : sf.getColumns())
            if (cellItr.hasNext()) {
                Cell cell = cellItr.next();
                try {
                    set(obj, field.getName(), getValue(cell, field.getType()));
                } catch (CellParsingException e) {
                    throw new RowParsingException(e.getMessage() + " , at row " + row.getRowNum());
                }
            }
    }

@Override
public List<String> generate(Sheet sheet) {
    List<String> lines = new ArrayList<>();
    for (Row row : sheet) {
        addInsertStatementsForRow(lines, row);
    }

    return lines;
}

private void addInsertStatementsForRow(List<String> lines, Row row) {
    Cell firstCell = row.getCell(0);
    if (firstCell != null) {
        String userId = firstCell.getStringCellValue();
        if (isValidUserId(userId)) {
            addInsertStatementsForRow(lines, row, userId);
        }
    }
}

@Restrict({@Group("ADMIN")})
public Result getExamEnrollments(Long id) throws IOException {
    Exam proto = Ebean.find(Exam.class).fetch("examEnrolments").fetch("examEnrolments.user")
            .fetch("examEnrolments.reservation").fetch("course")
            .where().eq("id", id).isNull("parent").findUnique();
    if (proto == null) {
        return notFound("sitnet_error_exam_not_found");
    }
    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet("enrolments");
    String[] headers = {"student name", "student ID", "student EPPN", "reservation time", "enrolment time"};
    addHeader(sheet, headers);
    for (ExamEnrolment e : proto.getExamEnrolments()) {
        String[] data = new String[5];
        data[0] = String.format("%s %s", e.getUser().getFirstName(), e.getUser().getLastName());
        data[1] = forceNotNull(e.getUser().getIdentifier());
        data[2] = e.getUser().getEppn();
        data[3] = e.getReservation() == null ? "" : ISODateTimeFormat.dateTimeNoMillis().print(
                new DateTime(e.getReservation().getStartAt()));
        data[4] = ISODateTimeFormat.dateTimeNoMillis().print(new DateTime(e.getEnrolledOn()));
        Row dataRow = sheet.createRow(proto.getExamEnrolments().indexOf(e) + 1);
        for (int i = 0; i < data.length; ++i) {
            dataRow.createCell(i).setCellValue(data[i]);
        }
    }
    IntStream.range(0, 5).forEach(i -> sheet.autoSizeColumn(i, true));

    response().setHeader("Content-Disposition", "attachment; filename=\"enrolments.xlsx\"");
    return ok(encode(wb));
}

private List<String> getInsertStatementsForRow(Row row) {
    List<String> lines = new ArrayList<>();
    Cell firstCell = row.getCell(0);
    if (firstCell != null) {
        String userId = firstCell.getStringCellValue();
        if (isValidUserId(userId)) {
            lines.addAll(getInsertStatementsForRow(row, userId));
        }
    }
    return lines;
}

private int createHeader(Sheet sheet, int rowNum, String[] titles) {
    Row headerRow = sheet.createRow(rowNum);
    headerRow.setHeightInPoints(40);
    Cell headerCell;
    int[] cols = new int[titles.length];
    for (int i = 0; i < titles.length; i++) {
        cols[i] = i;
        headerCell = headerRow.createCell(i);
        headerCell.setCellValue(titles[i]);
        headerCell.setCellStyle(styles.get("header"));
    }
    autoSize(sheet, cols);
    return rowNum;
}

private Map<Characteristic, CharacteristicValue> getCharacteristicsValues(Row row, List<Characteristic> characteristics) {
  Map<Characteristic, CharacteristicValue> characteristicsValues = new HashMap<>();

  for (int i = 1; i < row.getLastCellNum(); i++) {
    characteristicsValues.put(characteristics.get(i - 1), new CharacteristicValue(row.getCell(i).getStringCellValue()));
  }

  return characteristicsValues;
}

/**
 * @param row
 * @param suiteAction
 */
private void rowParse(Row row, SuiteAction suiteAction)
{
    Cell nameCell = row.getCell(0);
    Cell actionCell = row.getCell(1);

    if(nameCell == null || actionCell == null)
    {
        return;
    }

    suiteAction.setField(nameCell.getStringCellValue());
    suiteAction.setName(actionCell.getStringCellValue());
}

@Override
public List<String> generate(Sheet sheet) {
    List<String> lines = new ArrayList<>();
    for (Row row : sheet) {
        addInsertStatementsForRow(lines, row);
    }

    return lines;
}

/**
 * @param inputStream      - an xlsx file as source input stream.
 * @param fileOutputStream - the OutputStream to write the translated file to.
 * @throws IOException if an I/O error occurs.
 * @throws NoSheetFoundException - if no sheets were found in the file at all.
 * @throws NoDataFoundException - if a non hidden sheet was found but it contained no data at all.
 */
public void convert(InputStream inputStream, FileOutputStream fileOutputStream) throws Exception {
    final Optional<Iterator<Row>> optionalRowIterator = getRowIterator(inputStream);
    Iterator<Row> rowIterator = optionalRowIterator.orElseThrow(NoSheetFoundException::new);
    try {
        List<String> headers = getRowAsList(rowIterator.next());
        final int rowSize = headers.size();
        try (CSVPrinter csvPrinter = createCSVPrinter(headers.toArray(new String[headers.size()]), delimiter, fileOutputStream)) {
            writeRowsToFile(rowIterator, rowSize, csvPrinter);
        }
    } catch (NoSuchElementException e) {
        logger.error("File is empty, exiting converter");
        throw new NoDataFoundException();
    }
}