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

/**
 * Writes the example set into a excel file with XLSX format. If you want to write it in XLS
 * format use {@link #write(ExampleSet, Charset, OutputStream)}.
 *
 * @param exampleSet
 *            the exampleSet to write
 * @param sheetName
 *            name of the excel sheet which will be created.
 * @param dateFormat
 *            a string which describes the format used for dates.
 * @param numberFormat
 *            a string which describes the format used for numbers.
 * @param outputStream
 *            the stream to write the file to
 * @param opProg
 *            increases the progress by the number of examples to provide a more detailed
 *            progress.
 */
public static void writeXLSX(ExampleSet exampleSet, String sheetName, String dateFormat, String numberFormat,
        OutputStream outputStream, OperatorProgress opProg) throws WriteException, IOException,
        ProcessStoppedException {
    // .xlsx files can only store up to 16384 columns, so throw error in case of more
    if (exampleSet.getAttributes().allSize() > 16384) {
        throw new IllegalArgumentException(I18N.getMessage(I18N.getErrorBundle(),
                "export.excel.excel_xlsx_file_exceeds_column_limit"));
    }

    try {
        XSSFWorkbook workbook = new XSSFWorkbook();

        Sheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName(sheetName));
        dateFormat = dateFormat == null ? DEFAULT_DATE_FORMAT : dateFormat;

        numberFormat = numberFormat == null ? "#.0" : numberFormat;

        writeXLSXDataSheet(workbook, sheet, dateFormat, numberFormat, exampleSet, opProg);
        workbook.write(outputStream);
    } finally {
        outputStream.flush();
        outputStream.close();
    }
}

/**
 * 复制工作表相关参数
 * 
 * @author      ZhengWei(HY)
 * @createDate  2017-03-20
 * @version     v1.0
 *
 * @param i_FromSheet
 * @param i_ToSheet
 */
public final static void copySheet(Sheet i_FromSheet ,Sheet i_ToSheet)
{
    // 打印时显示网格线
    i_ToSheet.setPrintGridlines(           i_FromSheet.isPrintGridlines());
    i_ToSheet.setPrintRowAndColumnHeadings(i_FromSheet.isPrintRowAndColumnHeadings());
    i_ToSheet.setFitToPage(                i_FromSheet.getFitToPage());

    // Sheet页自适应页面大小
    i_ToSheet.setAutobreaks(               i_FromSheet.getAutobreaks());
    i_ToSheet.setDisplayZeros(             i_FromSheet.isDisplayZeros());
    i_ToSheet.setDisplayGuts(              i_FromSheet.getDisplayGuts());
    // 网格线
    i_ToSheet.setDisplayGridlines(         i_FromSheet.isDisplayGridlines());

    // 冻结线
    if ( i_FromSheet.getPaneInformation() != null )
    {
        i_ToSheet.createFreezePane(i_FromSheet.getPaneInformation().getVerticalSplitPosition()
                                  ,i_FromSheet.getPaneInformation().getHorizontalSplitPosition()
                                  ,i_FromSheet.getPaneInformation().getVerticalSplitLeftColumn()
                                  ,i_FromSheet.getPaneInformation().getHorizontalSplitTopRow());
    }
}

public void set(int sheetIndex, int x_index, int y_index, String value) {
    try {
        if (wb == null) throw new Exception("未打开文件");
        Sheet sheet = wb.getSheetAt(sheetIndex);
        Row row = null;
        Cell cell = null;
        row = sheet.getRow(x_index);
        if (row == null) {
            row = sheet.createRow(x_index);
        }
        cell = row.getCell(y_index);
        if (cell == null) {
            cell = row.createCell(y_index);
        }
        cell.setCellValue(value);
        save();
    } catch (Exception e) {
        logger.error(e.getMessage());
    }
}

/**
 * 合并行
 */
//TODO 暂时支持两行表头
private void mergeRows(Sheet sheet, CellStyle cellStyle, ExcelMeta excelMeta) {

    Row row = null;
    Cell cell = null;
    String[] lastRowVals = new String[excelMeta.getTitleColumnNum()];
    for (int r = 0; r < excelMeta.getTitleRowNum(); r++) {
        for (int c = 0; c < excelMeta.getTitleColumnNum(); c++) {
            row = sheet.getRow(r);
            cell = row.getCell(c);
            if (r == 0) {
                lastRowVals[c] = cell.getStringCellValue();
            } else {
                if (StringUtils.equals(lastRowVals[c], cell.getStringCellValue())) {
                    cell.setCellValue("");
                    sheet.addMergedRegion(new CellRangeAddress(0, r, c, c));
                    Cell nowCell = sheet.getRow(0).getCell(c);
                    nowCell.setCellStyle(cellStyle);
                }
            }

        }
    }

}

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();
}

private void formatCellDate(Sheet sheet, Cell cell, String format) {
    CellStyle style = wb.createCellStyle();
    CreationHelper createHelper = wb.getCreationHelper();
    style.setDataFormat(createHelper.createDataFormat().getFormat(format));
    cell.setCellStyle(style);

}

protected Sheet createSheet(SXSSFWorkbook wb,Paper paper,String name){
    Sheet sheet = null;
    if(name==null){
        sheet=wb.createSheet();
    }else{          
        sheet=wb.createSheet(name);
    }
    PaperType paperType=paper.getPaperType();
    XSSFPrintSetup printSetup=(XSSFPrintSetup)sheet.getPrintSetup();
    Orientation orientation=paper.getOrientation();
    if(orientation.equals(Orientation.landscape)){
        printSetup.setOrientation(PrintOrientation.LANDSCAPE);          
    }
    setupPaper(paperType, printSetup);
    int leftMargin=paper.getLeftMargin();
    int rightMargin=paper.getRightMargin();
    int topMargin=paper.getTopMargin();
    int bottomMargin=paper.getBottomMargin();
    sheet.setMargin(Sheet.LeftMargin, UnitUtils.pointToInche(leftMargin));
    sheet.setMargin(Sheet.RightMargin, UnitUtils.pointToInche(rightMargin));
    sheet.setMargin(Sheet.TopMargin, UnitUtils.pointToInche(topMargin));
    sheet.setMargin(Sheet.BottomMargin, UnitUtils.pointToInche(bottomMargin));
    return sheet;
}

private int getStartRow(Sheet sheet) throws SheetParsingException {

        Iterator<Row> rit = sheet.iterator();
        while (rit.hasNext()) {
            Row row = rit.next();
            Cell cell = row.getCell(0);

            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                /*
                 * In my case first column is Sr.no. and Data starts where sr. no is 1 ,so this
                 * is start row index
                 */
                if (cell.getNumericCellValue() == 1.0)
                    return row.getRowNum();
            }
        }
        throw new SheetParsingException("no start index found for data");
    }

@Test
public void test1() throws Exception {
    File file = folder.newFile("temp.xlsx");
    try (Workbook wb = new XSSFWorkbook()) {
        Sheet sheet1 = wb.createSheet("Sheet1");
        for (int row = 10; row <= 110; row++) {
            ExcelUtils.writeCell(sheet1, row, 20, "ISSUE-" + row);
        }
        try (OutputStream out = new FileOutputStream(file)) {
            wb.write(out);
        }
    }

    Context context = new MockContext();

    Read re = new Read(file.getAbsolutePath(), "Sheet1", "U");
    TextList list = re.execute(context, None.getInstance());
    assertNotNull(list);

    List<Text> texts = list.remaining(Hint.none());
    assertNotNull(texts);
    assertEquals(101, texts.size());
    assertEquals("ISSUE-10", texts.get(0).getText());
    assertEquals("ISSUE-110", texts.get(100).getText());
}

/**
 * 按报表模板格式写入小计（暂时不支持分页功能）
 * 
 * @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);
    }
}

/**
 * 按报表模板格式写分页页脚标题
 * 
 * @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 writeTitlePageFooter(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.getTitlePageFooterCount();
    int   v_ExcelRowIndex    = io_RTotal.getExcelRowIndex();

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

    for (int v_RowNo=0; v_RowNo<v_TemplateRowCount; v_RowNo++)
    {
        int v_TemplateRowNo = i_RTemplate.getTitlePageFooterBeginRow() + 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);
    }
}

/**
 * Read the excel to get the Map of properties for supported locals
 * 
 * @param wb
 *            workbook which is the source
 * @param supportedLocales
 *            supported Locale Iterator
 * @param sheetName
 * @param defaultKeySet
 *            if this parameter is not null: if there is invalid key not
 *            in this set, TranslationImportException.KEY_NOT_FOUND will
 *            throw.
 * @return
 * @throws ValidationException
 * @throws TranslationImportException
 */
public static Map<String, Properties> readExcel(Workbook wb,
        Iterator<Locale> supportedLocales, String sheetName,
        Set<Object> defaultKeySet) throws ValidationException,
        TranslationImportException {
    Sheet sheet = null;
    try {
        sheet = wb
                .getSheet(getDefaultResourceBundle().getString(sheetName));
        if (sheet == null) {
            throw new TranslationImportException();
        }
    } catch (Exception e) {
        throw new TranslationImportException(
                TranslationImportException.Reason.SHEET_NAME_NOT_FOUND);
    }
    return readSheet(sheet, supportedLocales, sheetName, defaultKeySet);
}

/**
 * Read the sheet to get the Map of properties for supported locals
 * 
 * @param sheet
 * @param supportedLocales
 * @param sheetName
 * @param defaultKeySet
 *            if this parameter is not null: if there is invalid key not
 *            in this set, TranslationImportException.KEY_NOT_FOUND will
 *            throw.
 * @return 
 * @throws ValidationException
 * @throws TranslationImportException
 */
public static Map<String, Properties> readSheet(Sheet sheet,
        Iterator<Locale> supportedLocales, String sheetName,
        Set<Object> defaultKeySet) throws ValidationException,
        TranslationImportException {

    List<String> localeStringList = readFirstRow(sheet);

    // create a properties object for each supported locale and uploaded
    // locale
    Map<String, Properties> propertiesMap = initializePropertyMap(
            supportedLocales, localeStringList, sheetName);

    readRows(sheet, propertiesMap, localeStringList, defaultKeySet);

    return propertiesMap;
}

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;
}

@Test
public void readSheet_ManageLanguage() throws Exception {
    // given

    Sheet sheet = prepareSheet(null, true, KEY1, "key2");
    FacesContext fc = prepareContext();
    HashSet<Object> defaultKeySet = new HashSet<Object>();
    defaultKeySet.add(KEY1);
    defaultKeySet.add("key2");

    // when
    Map<String, Properties> map = ExcelHandler.readSheet(sheet, fc
            .getApplication().getSupportedLocales(), SHEET_NAME,
            defaultKeySet);

    // then
    Properties props = map.get(DE);
    assertTrue(props.containsKey(KEY1));
    assertEquals(props.get(KEY1), "key1de ");
    props = map.get("de system");
    assertTrue(props.containsKey(KEY1));
    assertEquals(props.get(KEY1), "\nkey1de");

}

/**
 * 按报表模板格式写入数据
 * 
 * @author      ZhengWei(HY)
 * @createDate  2017-03-17
 * @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 writeData(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();

    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);

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

@Override
protected void fillTrie(Logger logger, Trie<List<String>> trie, Corpus corpus) throws IOException, ModuleException {
    Iterator<InputStream> inputStreams = xlsFile.getInputStreams();
    while (inputStreams.hasNext()) {
        try (InputStream is = inputStreams.next()) {
            Workbook wb = WorkbookFactory.create(is);
            for (int sheetNumber : sheets) {
                Sheet sheet = wb.getSheetAt(sheetNumber);
                fillSheetEntries(trie, sheet);
            }
        }
        catch (EncryptedDocumentException|InvalidFormatException e) {
            rethrow(e);
        }
    }
}

/**
 * this method adds row items for non-Hierarchical Containers. Override this
 * method to make any changes. To change the CellStyle used for all Grid
 * data use setDataStyle(). For different data cells to have different
 * CellStyles, override getDataStyle().
 *
 * @param sheetToAddTo the sheet to add to
 * @param row the row
 * @return the int
 */
protected int addDataRows(final Sheet sheetToAddTo, final int row) {
    final Collection<T> itemIds = getGridHolder().getItemIds();
    int localRow = row;
    int count = 0;
    for (final T itemId : itemIds) {
        addDataRow(sheetToAddTo, itemId, localRow);
        count = 1;
        if (count > 1) {
            sheet.groupRow(localRow + 1, (localRow + count) - 1);
            sheet.setRowGroupCollapsed(localRow + 1, true);
        }
        localRow = localRow + count;
    }
    return localRow;
}

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);
}

/**
 * @param pageList
 */
private void sheetsParse(List<SuitePage> pageList)
{
    int total = workbook.getNumberOfSheets();

    for(int index = 0; index < total; index++)
    {
        Sheet sheet = workbook.getSheetAt(index);

        SuitePage suitePage = new SuitePage();
        if(sheetParse(sheet, suitePage))
        {
            suitePage.setRepeat(1);
            pageList.add(suitePage);
        }
    }
}

public static void readWorkBook() throws Exception {
    // poi��ȡexcel
    // ����Ҫ������ļ���������
    final InputStream inp = new FileInputStream("d:\\workbooks.xlsx");

    // �������������������� ��������������
    final Workbook wb = WorkbookFactory.create(inp);

    for (final Sheet sheet : wb) {

        System.out.println(sheet.getSheetName());
        for (final Row row : sheet) {

            for (final Cell cell : row) {

                System.out.print(cell.toString() + "  ");
            }

            System.out.println();
        }
    }
    // �ر�������
    inp.close();
}

/**
 * 按报表模板格式写入“空白”数据
 * 
 * @author      ZhengWei(HY)
 * @createDate  2017-07-03
 * @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 writeDataByBlankSpace(RWorkbook i_DataWorkbook ,Sheet i_DataSheet ,RTotal io_RTotal ,RSystemValue io_RSystemValue, RTemplate i_RTemplate) 
{
    Sheet v_TemplateSheet    = i_RTemplate.getTemplateSheet();
    int   v_TemplateRowCount = i_RTemplate.getRowCountData();
    int   v_ExcelRowIndex    = io_RTotal.getExcelRowIndex();

    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);

        copyRowByBlankSpace(i_RTemplate ,v_TemplateRow ,i_DataWorkbook ,io_RTotal ,io_RSystemValue ,v_DataRow);
    }
}

public Sheet getOrCreateDetailsSheet(String testcaseid, boolean update) {
    Sheet sheet = wb.getSheet(testcaseid);
    if (sheet == null) {
        sheet = createSheet(testcaseid);
    } else {
        if (update) {
            int index = wb.getSheetIndex(sheet);
            wb.removeSheetAt(index);
            sheet = createSheet(testcaseid);
        } else {
            String name = getNextDetailsSheetName(testcaseid);
            sheet = createSheet(name);
        }
    }
    configurePrintSetup(sheet);
    createHeader(sheet, 0, titlesDetails);

    return sheet;
}

protected void formatCellStatus(Sheet sheet, Cell cell) {
    cell.setCellStyle(styles.get("status"));
    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
    ConditionalFormattingRule ruleGreen = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL, "1");
    PatternFormatting fill1 = ruleGreen.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.GREEN.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
    //
    ConditionalFormattingRule ruleRed = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL, "0");
    PatternFormatting fill2 = ruleRed.createPatternFormatting();
    fill2.setFillBackgroundColor(IndexedColors.RED.index);
    fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
    //
    ConditionalFormattingRule ruleOrange = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL, "2");
    PatternFormatting fill3 = ruleOrange.createPatternFormatting();
    fill3.setFillBackgroundColor(IndexedColors.ORANGE.index);
    fill3.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
    //
    String name = CellReference.convertNumToColString(cell.getColumnIndex());
    String location = "$" + name + "$" + cell.getRowIndex() + ":$" + name + "$" + (cell.getRowIndex() + 1);

    CellRangeAddress[] regions = { CellRangeAddress.valueOf(location) };
    ConditionalFormattingRule[] cfRules = new ConditionalFormattingRule[] { ruleGreen, ruleRed, ruleOrange };
    sheetCF.addConditionalFormatting(regions, cfRules);
}

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 });
}

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();
    }

public static void CreateCellM(Cell c, Row r, Sheet s, CellStyle cs, int colinaI, int colinaF, String valorS, int linhaI, int linhaF) {
    c = r.createCell(linhaI);
    c.setCellStyle(cs);
    c.setCellValue(valorS);
    s.addMergedRegion(new CellRangeAddress(colinaI, colinaF, linhaI, linhaF));
    for (int e = (linhaI + 1); e <= linhaF; e++) {
        c = r.createCell(e);
        c.setCellStyle(cs);
    }
}

@Override
public List<String> generate(Sheet sheet) {
    return Utils.stream(sheet)
            .filter(this::hasValidUserId)
            .flatMap(this::getInsertStatementsForRow)
            .collect(Collectors.toList());
}

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

    return lines;
}

@Override
public List<String> generate(Sheet sheet) {
    return Utils.stream(sheet)
            .filter(this::hasValidUserId)
            .flatMap(this::getInsertStatementsForRow)
            .collect(Collectors.toList());
}

@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 void testGenerator(ScriptGenerator generator) throws IOException {
    try (InputStream is = getClass().getResourceAsStream("/Users.xlsx");
            Workbook workbook = new XSSFWorkbook(is)) {
        Sheet sheet = workbook.getSheetAt(0);
        List<String> lines = generator.generate(sheet);
        assertThat(lines.size()).isEqualTo(44);
        assertThat(lines.get(0)).isEqualTo("insert into ApplicationPermission(user_id, application_id) values('t.wilson', 2237);");
        assertThat(lines.get(15)).isEqualTo("insert into ApplicationPermission(user_id, application_id) values('p.romero', 3657);");
        assertThat(lines.get(22)).isEqualTo("insert into ApplicationPermission(user_id, application_id) values('b.walton', 4352);");
        assertThat(lines.get(43)).isEqualTo("insert into ApplicationPermission(user_id, application_id) values('e.nash', 5565);");
    }
}

public Sheet createSheet(Workbook workbook, String sheetName) {
    if (StringUtils.isNotEmpty(sheetName)) {
        return workbook.createSheet(sheetName);
    } else {
        return workbook.createSheet(DefaultSheetName);
    }
}

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;
}

public static void createCell(Cell c, Row r, Sheet s, CellStyle cs, int colinaI, int colinaF, String valorS, int linhaI, int linhaF) {

        c = r.createCell(linhaI);
        c.setCellStyle(cs);
        c.setCellValue(valorS);
        s.setColumnWidth(linhaI, linhaF*1000);
    }

private void setCellRangeAddressBorder(CellRangeAddress rangle, Sheet sheet) {
    int border = 1;
    Workbook wb = sheet.getWorkbook();
    RegionUtil.setBorderBottom(border, rangle, sheet, wb);
    RegionUtil.setBorderLeft(border, rangle, sheet, wb);
    RegionUtil.setBorderRight(border, rangle, sheet, wb);
    RegionUtil.setBorderTop(border, rangle, sheet, wb);
}

/**
 * excel 下载
 */
@RequestMapping(value = "downLoad")
public void downLoad(HttpServletRequest request, HttpServletResponse response) throws Exception {

    Workbook wb = null;
    try {
        logger.info(">>>>>>>>ReportViewController.downLoad start>>");

        //=======================================数据
        List<Map<String,Object>> datas = Lists.newArrayList();
        Map<String,Object> data0 = Maps.newHashMap();
        data0.put("date", "2017-01-01");
        data0.put("date1", "2017-01-01");
        Map<String,Object> data1 = Maps.newHashMap();
        data1.put("shoujidai","100");
        data1.put("daxuedai","100");
        data1.put("zirandai","100");
        data0.put("zidonghebishu",data1);
        datas.add(data0);
        //==========================================


        //设置excel模板
        Map<String, Object> templateParams = Maps.newHashMap();
        XLSTransformer transformer = new XLSTransformer();
        wb = transformer.transformXLS(App.class.getResourceAsStream("/xls/excel.xlsx"), templateParams);
        Sheet billInfoSheet = wb.getSheet("sheet1");

        //设置excel展示配置
        ExcelExportSetting excelExportSetting = new ExcelExportSetting();
        List<PoiCell> cellList = Lists.newArrayList();
        //一行数据的第一列
        cellList.add(new ExcelMergeCell("日期","date"));
        cellList.add(new ExcelMergeCell("日期1","date1"));

        //一行数据的第二个列合并单元格的
        ExcelMergeCell excelMergeCell = new ExcelMergeCell("自动电核笔数","zidonghebishu",
                Arrays.asList(new ExcelCell("大学贷","daxuedai"),
                        new ExcelCell("手机贷","shoujidai"),
                        new ExcelCell("自然贷","zirandai")));
        cellList.add(excelMergeCell);

        excelExportSetting.setHeaderRow(cellList);//设置表头
        excelExportSetting.setDataList(datas);//设置数据

        //写入excel
        ExcelPoiHelp.poiWrite(wb, billInfoSheet, excelExportSetting);

        //写入response
        String outFile = "outputFile.xls";
        response.reset();
        response.addHeader("Content-Disposition", "attachment;filename="+ new String(outFile.getBytes()));
        OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        wb.write(toClient);

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        wb.close();
    }

}

ExportExcelConfig(Sheet sheet) {
    this.sheet = sheet;
    this.cellStyler = ExcelCellStyler.bordersStyle();
    this.cellStyler.initialize(sheet.getWorkbook());
    this.columnOrigin = 0;
    this.rowOrigin = 0;
}

SheetParser(Sheet sheet, SheetFormat sheetFormat, Class<? extends Inventory> clazz) {
    this.sheet = sheet;
    this.sheetFormat = sheetFormat;
    this.clazz = clazz;
    rowParser = new RowParser();
    list = new ArrayList<>();
}

public ExportExcelConfig build(String sheetName) {
    Sheet existingSheet = workbook.getSheet(sheetName);
    if (existingSheet != null) {
        return new ExportExcelConfig(existingSheet);
    }
    return new ExportExcelConfig(workbook.createSheet(sheetName));
}