Java 类org.apache.poi.hssf.usermodel.HSSFSheet 实例源码

private void makeHeader ( final List<Field> columns, final HSSFSheet sheet )
{
    final Font font = sheet.getWorkbook ().createFont ();
    font.setFontName ( "Arial" );
    font.setBoldweight ( Font.BOLDWEIGHT_BOLD );
    font.setColor ( HSSFColor.WHITE.index );

    final CellStyle style = sheet.getWorkbook ().createCellStyle ();
    style.setFont ( font );
    style.setFillForegroundColor ( HSSFColor.BLACK.index );
    style.setFillPattern ( PatternFormatting.SOLID_FOREGROUND );

    final HSSFRow row = sheet.createRow ( 0 );

    for ( int i = 0; i < columns.size (); i++ )
    {
        final Field field = columns.get ( i );

        final HSSFCell cell = row.createCell ( i );
        cell.setCellValue ( field.getHeader () );
        cell.setCellStyle ( style );
    }
}

private static void setColumWidth(ExcelFileType type,ExportInfo exportInfo,
        List<Field> availableFields,Sheet sheet) {
    Map<Field, ExportFieldInfo> fieldInfoMap = exportInfo.getFieldInfoMap();
    for(int i = 0 ; i < availableFields.size() ; i++){
        Field field = availableFields.get(i);
        ExportFieldInfo excelFieldInfo = fieldInfoMap.get(field);
        if (excelFieldInfo.getWidth() != null) {
            sheet.setColumnWidth(i, excelFieldInfo.getWidth()*256); 
            continue;
        }
        if(excelFieldInfo.getAutoWidth() != null){
            if(ExcelFileType.XLS == type){
                HSSFSheet hSheet = (HSSFSheet) sheet;
                hSheet.autoSizeColumn(i);
            }else if(ExcelFileType.XLSX == type){
                XSSFSheet xSheet = (XSSFSheet) sheet;
                xSheet.autoSizeColumn(i);
            }
        }
    }
}

private void insertItemToSheet(String table, HSSFSheet sheet, ArrayList<String> columns) {
    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
    Cursor cursor = database.rawQuery("select * from " + table, null);
    cursor.moveToFirst();
    int n = 1;
    while (!cursor.isAfterLast()) {
        HSSFRow rowA = sheet.createRow(n);
        for (int j = 0; j < columns.size(); j++) {
            HSSFCell cellA = rowA.createCell(j);
            if (cursor.getType(j) == Cursor.FIELD_TYPE_BLOB) {
                HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) j, n, (short) (j + 1), n + 1);
                anchor.setAnchorType(3);
                patriarch.createPicture(anchor, workbook.addPicture(cursor.getBlob(j), HSSFWorkbook.PICTURE_TYPE_JPEG));
            } else {
                cellA.setCellValue(new HSSFRichTextString(cursor.getString(j)));
            }
        }
        n++;
        cursor.moveToNext();
    }
    cursor.close();
}

/**
 * Read the Excel 2003-2007
 * 
 * @param path
 *            the path of the Excel
 * @return
 * @throws IOException
 */
public static String readXls(String path) throws IOException {
    InputStream is = new FileInputStream(path);
    HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
    StringBuffer sb = new StringBuffer("");
    // Read the Sheet
    for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
        HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
        if (hssfSheet == null) {
            continue;
        }
        // Read the Row
        for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
            HSSFRow hssfRow = hssfSheet.getRow(rowNum);
            if (hssfRow != null) {
                HSSFCell no = hssfRow.getCell(0);
                HSSFCell name = hssfRow.getCell(1);
                sb.append(no + ":" + name);
                sb.append(";");
            }
        }
    }
    return sb.toString().substring(0, sb.toString().length() - 1);
}

public Map<String, BoundaryPoint> parse(InputStream is) throws IOException {
    Map<String, BoundaryPoint> boundaryPoints = new HashMap<>();
    HSSFWorkbook workbook = new HSSFWorkbook(is);
    HSSFSheet sheet = workbook.getSheetAt(0);
    Iterator<Row> rowIterator = sheet.iterator();
    rowIterator.next();
    rowIterator.next();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        Cell boundaryPointNameCell = row.getCell(13);
        Cell borderFromCell = row.getCell(14);
        Cell borderToCell = row.getCell(15);
        String boundaryPointName = boundaryPointNameCell.getStringCellValue();
        if (boundaryPointName.equals("-")) {
            continue;
        }
        Country borderFrom = toCountry(borderFromCell.getStringCellValue());
        Country borderTo = toCountry(borderToCell.getStringCellValue());
        boundaryPoints.put(boundaryPointName, new BoundaryPoint(boundaryPointName, borderFrom, borderTo));
    }
    return boundaryPoints;
}

/**
 * 判断单元格在不在合并单元格范围内，如果是，获取其合并的列数。
 */
private static int getMergerCellRegionCol(HSSFSheet sheet, int cellRow, int cellCol) throws Throwable {
    int retVal = 0;
    int sheetMergerCount = sheet.getNumMergedRegions();
    for (int i = 0; i < sheetMergerCount; i++) {
        CellRangeAddress cra = sheet.getMergedRegion(i);
        int firstRow = cra.getFirstRow(); // 合并单元格CELL起始行
        int firstCol = cra.getFirstColumn(); // 合并单元格CELL起始列
        int lastRow = cra.getLastRow(); // 合并单元格CELL结束行
        int lastCol = cra.getLastColumn(); // 合并单元格CELL结束列
        if (cellRow >= firstRow && cellRow <= lastRow) { // 判断该单元格是否是在合并单元格中
            if (cellCol >= firstCol && cellCol <= lastCol) {
                retVal = lastCol - firstCol + 1; // 得到合并的列数
                break;
            }
        }
    }
    return retVal;
}

/**
 * 判断单元格是否是合并的单格，如果是，获取其合并的行数。
 */
private static int getMergerCellRegionRow(HSSFSheet sheet, int cellRow, int cellCol) throws Throwable {
    int retVal = 0;
    int sheetMergerCount = sheet.getNumMergedRegions();
    for (int i = 0; i < sheetMergerCount; i++) {
        CellRangeAddress cra = sheet.getMergedRegion(i);
        int firstRow = cra.getFirstRow(); // 合并单元格CELL起始行
        int firstCol = cra.getFirstColumn(); // 合并单元格CELL起始列
        int lastRow = cra.getLastRow(); // 合并单元格CELL结束行
        int lastCol = cra.getLastColumn(); // 合并单元格CELL结束列
        if (cellRow >= firstRow && cellRow <= lastRow) { // 判断该单元格是否是在合并单元格中
            if (cellCol >= firstCol && cellCol <= lastCol) {
                retVal = lastRow - firstRow + 1; // 得到合并的行数
                break;
            }
        }
    }
    return retVal;
}

/**
 * 获取合并单元格式
 *
 * @param sheet
 * @param row
 * @param columnFrom
 * @param columnTo
 * @return
 */
private static void setRegionBorder(HSSFSheet sheet, int row, int columnFrom, int columnTo) {
    CellRangeAddress region = new CellRangeAddress(row, row, columnFrom, columnTo);
    sheet.addMergedRegion(region);
    final short border = CellStyle.BORDER_THIN;
    HSSFWorkbook wb = sheet.getWorkbook();
    RegionUtil.setBorderBottom(border, region, sheet, wb);
    RegionUtil.setBorderTop(border, region, sheet, wb);
    RegionUtil.setBorderLeft(border, region, sheet, wb);
    RegionUtil.setBorderRight(border, region, sheet, wb);
    RegionUtil.setBottomBorderColor(HSSFColor.BLACK.index, region, sheet, wb);
    RegionUtil.setTopBorderColor(HSSFColor.BLACK.index, region, sheet, wb);
    RegionUtil.setLeftBorderColor(HSSFColor.BLACK.index, region, sheet, wb);
    RegionUtil.setRightBorderColor(HSSFColor.BLACK.index, region, sheet, wb);
}

public static void writeXLSFile() throws IOException {

            HSSFWorkbook wbObj = new HSSFWorkbook();
            HSSFSheet sheet = wbObj.createSheet(sheetName);

            for (int row = 0; row < tableData.size(); row++) {
                HSSFRow rowObj = sheet.createRow(row);
                rowData = tableData.get(row);
                for (int col = 0; col < rowData.size(); col++) {
                    HSSFCell cellObj = rowObj.createCell(col);
                    cellObj.setCellValue(rowData.get(col));
                }
            }

            FileOutputStream fileOut = new FileOutputStream(excelFileName);
            wbObj.write(fileOut);
            wbObj.close();
            fileOut.flush();
            fileOut.close();
        }

/**
 * Creates XLS document from given list of {@link PollOption} objects.
 * 
 * @param results List of {@link PollOption} objects.
 * @return XLS document.
 */
public static HSSFWorkbook getXLS(List<PollOption> results) {
    HSSFWorkbook document = new HSSFWorkbook();
    HSSFSheet sheet = document.createSheet("Results");
    HSSFRow rowhead = sheet.createRow(0);
    rowhead.createCell(0).setCellValue("Ime opcije:");
    rowhead.createCell(1).setCellValue("Broj glasova:");

    for (int i = 0; i < results.size(); i++) {
        HSSFRow row = sheet.createRow(i + 1);
        row.createCell(0).setCellValue(results.get(i).getName());
        row.createCell(1).setCellValue(
                Double.valueOf(results.get(i).getVotes()));
    }
    return document;
}

/**
 * Creates XLS document based on given input parameters.
 * 
 * @param a Start number.
 * @param b End number.
 * @param n Last power.
 * @return Created XLS document.
 */
private HSSFWorkbook getXLS(Integer a, Integer b, Integer n) {
    HSSFWorkbook workbook = new HSSFWorkbook();

    for (int i = 1; i <= n; i++) {
        HSSFSheet sheet = workbook.createSheet(i + "-th power.");
        HSSFRow rowHead = sheet.createRow(0);
        rowHead.createCell(0).setCellValue("x");
        rowHead.createCell(1).setCellValue("x^" + i);

        int rowCounter = 1;
        for (int j = a; j <= b; j++) {
            HSSFRow row = sheet.createRow(rowCounter++);
            row.createCell(0).setCellValue(Double.valueOf(j));
            row.createCell(1).setCellValue(Math.pow(Double.valueOf(j), i));
        }
    }
    return workbook;
}

/**
 * Creates XLS document based from given list of bands.
 * 
 * @param results List of bands.
 * @return XLS document.
 */
public static HSSFWorkbook getXLS(List<Band> results) {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("Voting results");
    HSSFRow rowHead = sheet.createRow(0);
    rowHead.createCell(0).setCellValue("Band name:");
    rowHead.createCell(1).setCellValue("Number of votes:");

    for (int i = 0, size = results.size(); i < size; i++) {
        HSSFRow row = sheet.createRow(i + 1);
        row.createCell(0).setCellValue(results.get(i).getName());
        row.createCell(1).setCellValue(
                Double.valueOf(results.get(i).getVotes()));
    }
    return workbook;
}

private Span getSpan(HSSFSheet sheet,int row ,int column){
    int sheetMergeCount = sheet.getNumMergedRegions(); 
    for (int i = 0; i < sheetMergeCount; i++) {
        CellRangeAddress range = sheet.getMergedRegion(i);
        int firstColumn = range.getFirstColumn();
        int lastColumn = range.getLastColumn();
        int firstRow = range.getFirstRow();
        if(row == firstRow && column==firstColumn){  
            int lastRow = range.getLastRow();
            int rowSpan=lastRow-firstRow;
            if(rowSpan>0){
                rowSpan++;
            }
            int colSpan=lastColumn-firstColumn;
            if(colSpan>0){
                colSpan++;
            }
            return new Span(rowSpan,colSpan);
        }
    }
    return new Span(0,0);
}

private boolean isMergedRegion(HSSFSheet sheet, int row, int column) {
    int sheetMergeCount = sheet.getNumMergedRegions();
    for (int i = 0; i < sheetMergeCount; i++) {
        CellRangeAddress range = sheet.getMergedRegion(i);
        int firstColumn = range.getFirstColumn();
        int lastColumn = range.getLastColumn();
        int firstRow = range.getFirstRow();
        int lastRow = range.getLastRow();
        if (row > firstRow && row < lastRow) {
            if (column > firstColumn && column < lastColumn) {
                return true;
            }
        }
    }
    return false;
}

private void exportTables(List<String> tables, final String fileName) throws Exception {
    workbook = new HSSFWorkbook();
    for (int i = 0; i < tables.size(); i++) {
        if (!tables.get(i).equals("android_metadata")) {
            HSSFSheet sheet = workbook.createSheet(tables.get(i));
            createSheet(tables.get(i), sheet);
        }
    }
    File file = new File(mExportPath, fileName);
    FileOutputStream fos = new FileOutputStream(file);
    workbook.write(fos);
    fos.flush();
    fos.close();
    workbook.close();
    database.close();
}

public void setImage(HSSFWorkbook workbook, HSSFSheet sheet) {
    CellLocation cellLocation = POIUtils.findMatchCell(sheet, "\\"
            + KEYWORD_ER + ".*");

    if (cellLocation != null) {
        int width = -1;
        int height = -1;

        String value = POIUtils.getCellValue(sheet, cellLocation);

        int startIndex = value.indexOf("(");
        if (startIndex != -1) {
            int middleIndex = value.indexOf(",", startIndex + 1);
            if (middleIndex != -1) {
                width = Integer.parseInt(value.substring(startIndex + 1,
                        middleIndex).trim());
                height = Integer.parseInt(value.substring(middleIndex + 1,
                        value.length() - 1).trim());
            }
        }

        this.setImage(workbook, sheet, cellLocation, width, height);
    }
}

/**
 * @param newSheet the sheet to create from the copy.
 * @param sheet the sheet to copy.
 * @param copyStyle true copy the style.
 */
public static void copySheets(HSSFSheet newSheet, HSSFSheet sheet, boolean copyStyle) {
    int maxColumnNum = 0;
    Map<Integer, HSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, HSSFCellStyle>() : null;
    for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
        HSSFRow srcRow = sheet.getRow(i);
        HSSFRow destRow = newSheet.createRow(i);
        if (srcRow != null) {
            Util.copyRow(sheet, newSheet, srcRow, destRow, styleMap);
            if (srcRow.getLastCellNum() > maxColumnNum) {
                maxColumnNum = srcRow.getLastCellNum();
            }
        }
    }
    for (int i = 0; i <= maxColumnNum; i++) {
        newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
    }
   //Util.copyPictures(newSheet,sheet) ;
}

/**
 * 获取Excel2003图片
 * 
 * @param sheet
 *            当前sheet对象
 * @param workbook
 *            工作簿对象
 * @return Map key:图片单元格索引（1_1）String，value:图片流PictureData
 */
public static Map<String, PictureData> getSheetPictrues03(HSSFSheet sheet,
                                                          HSSFWorkbook workbook) {
    Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
    List<HSSFPictureData> pictures = workbook.getAllPictures();
    if (!pictures.isEmpty()) {
        for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {
            HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
            if (shape instanceof HSSFPicture) {
                HSSFPicture pic = (HSSFPicture) shape;
                int pictureIndex = pic.getPictureIndex() - 1;
                HSSFPictureData picData = pictures.get(pictureIndex);
                String picIndex = String.valueOf(anchor.getRow1()) + "_"
                                  + String.valueOf(anchor.getCol1());
                sheetIndexPicMap.put(picIndex, picData);
            }
        }
        return sheetIndexPicMap;
    } else {
        return sheetIndexPicMap;
    }
}

public static boolean getBooleanCellValue(HSSFSheet sheet, int r, int c) {
    HSSFRow row = sheet.getRow(r);

    if (row == null) {
        return false;
    }

    HSSFCell cell = row.getCell(c);

    if (cell == null) {
        return false;
    }

    try {
        return cell.getBooleanCellValue();
    } catch (RuntimeException e) {
        System.err.println("Exception at sheet name:"
                + sheet.getSheetName() + ", row:" + (r + 1) + ", col:"
                + (c + 1));
        throw e;
    }
}

protected Map<String, String> buildKeywordsValueMap(final HSSFSheet wordsSheet, final int columnNo, final String[] keywords) {
    final Map<String, String> keywordsValueMap = new HashMap<String, String>();

    for (final String keyword : keywords) {
        final CellLocation location = POIUtils.findCell(wordsSheet, keyword, columnNo);
        if (location != null) {
            final HSSFRow row = wordsSheet.getRow(location.r);

            final HSSFCell cell = row.getCell(location.c + 2);
            final String value = cell.getRichStringCellValue().getString();

            if (value != null) {
                keywordsValueMap.put(keyword, value);
            }
        }
    }

    return keywordsValueMap;
}

public static CellRangeAddress getMergedRegion(HSSFSheet sheet,
        CellLocation location) {
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress region = sheet.getMergedRegion(i);

        int rowFrom = region.getFirstRow();
        int rowTo = region.getLastRow();

        if (rowFrom == location.r && rowTo == location.r) {
            int colFrom = region.getFirstColumn();

            if (colFrom == location.c) {
                return region;
            }
        }
    }

    return null;
}

public void write(String src) throws IOException{

  System.out.println(src);
  HSSFWorkbook wb = new HSSFWorkbook();
  HSSFSheet sheet = wb.createSheet("Resultats");

  for (int i=0; i<size(); i++) {
    HSSFRow row = sheet.createRow(i);
    HSSFCell cell = row.createCell(0);
    cell.setCellValue(get(i).area());
  }


  FileOutputStream fileOut;
    fileOut = new FileOutputStream(src);
    wb.write(fileOut);
    fileOut.close();
}

public void createHeaderRow(JSONArray jsonData, JSONObject conversionOptions, HSSFSheet sheet) {
    long length = jsonData.size();
    Row row = sheet.createRow(0);
    if (length > 0) {
        Object firstObject = jsonData.get(0);
        if (firstObject instanceof JSONObject) {
            JSONObject firstData = (JSONObject) firstObject;
            Iterator<?> keys = firstData.keys();

            int cellNum = 0;

            while (keys.hasNext()) {
                Cell cell = row.createCell(cellNum++);
                cell.setCellValue("" + keys.next());
            }
        }
    }
}

private void createDataRows(JSONArray jsonData, JSONObject conversionOptions, HSSFSheet sheet) {
    for (int i = 0; i < jsonData.size(); i++) {
        Row row = sheet.createRow(i + 1);
        JSONObject retrievedObject = (JSONObject) jsonData.get(i);
        Iterator<?> keys = retrievedObject.keys();
        int cellNum = 0;

        while (keys.hasNext()) {
            String key = (String) keys.next();
            Cell cell = row.createCell(cellNum++);
            Object object = retrievedObject.get(key);
            if (object instanceof Date) cell.setCellValue((Date) object);
            else if (object instanceof Boolean) cell.setCellValue((Boolean) object);
            else if (object instanceof String) cell.setCellValue((String) object);
            else if (object instanceof Double) cell.setCellValue((Double) object);
            else if (object instanceof Integer) cell.setCellValue((Integer) object);
            else cell.setCellValue(object.toString());
        }
    }
}

private void setIndexMatrix(HSSFWorkbook workbook, HSSFSheet sheet,
        ERTable table) {
    CellLocation logicalIndexCellLocation = POIUtils.findCell(sheet,
            KEYWORD_LOGICAL_INDEX_MATRIX);

    if (logicalIndexCellLocation != null) {
        if (this.logicalIndexMatrixCellStyle == null) {
            this.logicalIndexMatrixCellStyle = this.createMatrixCellStyle(
                    workbook, sheet, logicalIndexCellLocation);
        }
        setIndexMatrix(workbook, sheet, table, logicalIndexCellLocation,
                this.logicalIndexMatrixCellStyle, true);
    }

    CellLocation physicalIndexCellLocation = POIUtils.findCell(sheet,
            KEYWORD_PHYSICAL_INDEX_MATRIX);

    if (physicalIndexCellLocation != null) {
        if (this.physicalIndexMatrixCellStyle == null) {
            this.physicalIndexMatrixCellStyle = this.createMatrixCellStyle(
                    workbook, sheet, physicalIndexCellLocation);
        }
        setIndexMatrix(workbook, sheet, table, physicalIndexCellLocation,
                this.physicalIndexMatrixCellStyle, false);
    }
}

/**
 * 表头条件
 * @param sheet
 * @param t
 * @param cellCount
 * @return
 */
void writeCondtions(HSSFSheet sheet){
    T t = getConditions();
    if (t!=null) {
        HSSFRow row = sheet.createRow(getRowNumber());
        row.setHeight((short) 500);
        CellRangeAddress cra = new CellRangeAddress(getRowNumber(), getRowNumber(), 0, getColumnJson().size());
        sheet.addMergedRegion(cra);
        HSSFCell cell = row.createCell(0);
        HSSFCellStyle style = cell.getCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setWrapText(true);
        cell.setCellStyle(style);
        setCellValue(cell, formatCondition(t));
        addRowNumber();
    }
}

/**
 * 写入表头
 * @param sheet
 */
void writeHead(HSSFSheet sheet){
    LinkedHashMap<String, Object> columnJson = getColumnJson();
    Set<String> keySet = columnJson.keySet();
    int cellNumber = 0;
    HSSFRow row = sheet.createRow(addRowNumber());
    for (String k : keySet) {
        Object name = columnJson.get(k);//品项编码
        sheet.autoSizeColumn(cellNumber);
        HSSFCell cell = row.createCell(cellNumber++);
        setCellValue(cell, name);
        pubMaxValue(k,name);
    }
}

void writeBody(HSSFSheet sheet){
        Set<String> keySet = getColumnJson().keySet();
        List<T> ts = getData();
        for (T t:ts) {
//          Class cls = t.getClass();
            int cellNumber = 0;//将cellNumber从0开始
            HSSFRow row = sheet.createRow(addRowNumber());//创建新的一行
            for(String key:keySet){
                try {
                    HSSFCell cell = row.createCell(cellNumber++);
                    Object value = getValueByKey(t, key);
                    setCellValue(cell, value);
                    pubMaxValue(key, value);
                } catch (Exception e) {
                    throw new RuntimeException("writeBody", e);
                }
            }
        }
    }

protected Map<String, String> buildKeywordsValueMap(HSSFSheet wordsSheet,
        int columnNo, String[] keywords) {
    Map<String, String> keywordsValueMap = new HashMap<String, String>();

    for (String keyword : keywords) {
        CellLocation location = POIUtils.findCell(wordsSheet, keyword,
                columnNo);
        if (location != null) {
            HSSFRow row = wordsSheet.getRow(location.r);

            HSSFCell cell = row.getCell(location.c + 2);
            String value = cell.getRichStringCellValue().getString();

            if (value != null) {
                keywordsValueMap.put(keyword, value);
            }
        }
    }

    return keywordsValueMap;
}

public static List<CellRangeAddress> getMergedRegionList(HSSFSheet sheet,
        int rowNum) {
    List<CellRangeAddress> regionList = new ArrayList<CellRangeAddress>();

    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress region = sheet.getMergedRegion(i);

        int rowFrom = region.getFirstRow();
        int rowTo = region.getLastRow();

        if (rowFrom == rowNum && rowTo == rowNum) {
            regionList.add(region);
        }
    }

    return regionList;
}

public static CellLocation findCell(HSSFSheet sheet, String str, int colNum) {
    for (int rowNum = sheet.getFirstRowNum(); rowNum < sheet
            .getLastRowNum() + 1; rowNum++) {
        HSSFRow row = sheet.getRow(rowNum);
        if (row == null) {
            continue;
        }

        HSSFCell cell = row.getCell(colNum);

        if (cell == null) {
            continue;
        }
        HSSFRichTextString cellValue = cell.getRichStringCellValue();

        if (!Check.isEmpty(cellValue.getString())) {
            if (cellValue.getString().equals(str)) {
                return new CellLocation(rowNum, (short) colNum);
            }
        }
    }

    return null;
}

public void createContent(WebInput wi, DocInfo di)throws ControllerException {
    HSSFWorkbook wb = di.getExcelDocument();
    // 创建HSSFSheet对象
    HSSFSheet sheet = wb.createSheet("sheet0");
    // 创建HSSFRow对象
    HSSFRow row = sheet.createRow((short) 0);
    // 创建HSSFCell对象
    HSSFCell cell = row.createCell((short) 0);
    // 用来处理中文问题
    // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
    // 设置单元格的值
    // cell.setCellValue("Hello World! 你好，中文世界");
    String info = wi.getParameter("info");
    HSSFRichTextString rts = new HSSFRichTextString(info);
    cell.setCellValue(rts);
    HSSFCell cell2 = row.createCell((short) 1);
    cell2.setCellValue(new HSSFRichTextString(
            "Beetle Web Framework 页面生成Excel文件演示!"));
}

public static CellLocation findMatchCell(final HSSFSheet sheet, final String regexp) {
    for (int rowNum = sheet.getFirstRowNum(); rowNum < sheet.getLastRowNum() + 1; rowNum++) {
        final HSSFRow row = sheet.getRow(rowNum);
        if (row == null) {
            continue;
        }

        final Integer colNum = findMatchColumn(row, regexp);

        if (colNum != null) {
            return new CellLocation(rowNum, colNum.shortValue());
        }
    }

    return null;
}

public static CellLocation findCell(final HSSFSheet sheet, final String str, final int colNum) {
    for (int rowNum = sheet.getFirstRowNum(); rowNum < sheet.getLastRowNum() + 1; rowNum++) {
        final HSSFRow row = sheet.getRow(rowNum);
        if (row == null) {
            continue;
        }

        final HSSFCell cell = row.getCell(colNum);

        if (cell == null) {
            continue;
        }
        final HSSFRichTextString cellValue = cell.getRichStringCellValue();

        if (!Check.isEmpty(cellValue.getString())) {
            if (cellValue.getString().equals(str)) {
                return new CellLocation(rowNum, (short) colNum);
            }
        }
    }

    return null;
}

public static int getIntCellValue(final HSSFSheet sheet, final int r, final int c) {
    final HSSFRow row = sheet.getRow(r);
    if (row == null) {
        return 0;
    }
    final HSSFCell cell = row.getCell(c);

    try {
        if (cell.getCellType() != Cell.CELL_TYPE_NUMERIC) {
            return 0;
        }
    } catch (final RuntimeException e) {
        System.err.println("Exception at sheet name:" + sheet.getSheetName() + ", row:" + (r + 1) + ", col:" + (c + 1));
        throw e;
    }

    return (int) cell.getNumericCellValue();
}

public static String getCellValue(HSSFSheet sheet, int r, int c) {
    HSSFRow row = sheet.getRow(r);

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

    HSSFCell cell = row.getCell(c);

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

    HSSFRichTextString cellValue = cell.getRichStringCellValue();

    return cellValue.toString();
}

public static void copyRow(final HSSFSheet oldSheet, final HSSFSheet newSheet, final int oldStartRowNum, final int oldEndRowNum, final int newStartRowNum) {
    final HSSFRow oldAboveRow = oldSheet.getRow(oldStartRowNum - 1);

    int newRowNum = newStartRowNum;

    for (int oldRowNum = oldStartRowNum; oldRowNum <= oldEndRowNum; oldRowNum++) {
        POIUtils.copyRow(oldSheet, newSheet, oldRowNum, newRowNum++);
    }

    final HSSFRow newTopRow = newSheet.getRow(newStartRowNum);

    if (oldAboveRow != null) {
        for (int colNum = newTopRow.getFirstCellNum(); colNum <= newTopRow.getLastCellNum(); colNum++) {
            final HSSFCell oldAboveCell = oldAboveRow.getCell(colNum);
            if (oldAboveCell != null) {
                final HSSFCell newTopCell = newTopRow.getCell(colNum);
                newTopCell.getCellStyle().setBorderTop(oldAboveCell.getCellStyle().getBorderBottom());
            }
        }
    }
}

@Override
public void generate(final ProgressMonitor monitor, final HSSFWorkbook workbook, final int sheetNo, final boolean useLogicalNameAsSheetName, final Map<String, Integer> sheetNameMap, final Map<String, ObjectModel> sheetObjectMap, final ERDiagram diagram, final Map<String, LoopDefinition> loopDefinitionMap) throws InterruptedException {

    for (final Sequence sequence : diagram.getDiagramContents().getSequenceSet()) {
        final String name = sequence.getName();

        final HSSFSheet newSheet = createNewSheet(workbook, sheetNo, name, sheetNameMap);

        final String sheetName = workbook.getSheetName(workbook.getSheetIndex(newSheet));
        monitor.subTaskWithCounter("[Sequence] " + sheetName);

        sheetObjectMap.put(sheetName, sequence);

        setSequenceData(workbook, newSheet, sequence, diagram);
        monitor.worked(1);
    }
}

private void setIndexMatrix(final HSSFWorkbook workbook, final HSSFSheet sheet, final ERTable table) {
    final CellLocation logicalIndexCellLocation = POIUtils.findCell(sheet, KEYWORD_LOGICAL_INDEX_MATRIX);

    if (logicalIndexCellLocation != null) {
        if (logicalIndexMatrixCellStyle == null) {
            logicalIndexMatrixCellStyle = this.createMatrixCellStyle(workbook, sheet, logicalIndexCellLocation);
        }
        setIndexMatrix(workbook, sheet, table, logicalIndexCellLocation, logicalIndexMatrixCellStyle, true);
    }

    final CellLocation physicalIndexCellLocation = POIUtils.findCell(sheet, KEYWORD_PHYSICAL_INDEX_MATRIX);

    if (physicalIndexCellLocation != null) {
        if (physicalIndexMatrixCellStyle == null) {
            physicalIndexMatrixCellStyle = this.createMatrixCellStyle(workbook, sheet, physicalIndexCellLocation);
        }
        setIndexMatrix(workbook, sheet, table, physicalIndexCellLocation, physicalIndexMatrixCellStyle, false);
    }
}

@Test
public void test01() throws Exception {
    File file = new File("C:\\Users\\he\\Downloads\\2016-05-10.xls");

    try (FileInputStream fis = new FileInputStream(file)) {
        HSSFWorkbook workbook = new HSSFWorkbook(fis);
        System.out.println(workbook.sheetIterator().hasNext());
        HSSFSheet spreadsheet = workbook.getSheetAt(0);
        print(spreadsheet);
    }
}