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

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

private void createHeader(HSSFSheet sheet, List<ExpedientConsultaDissenyDto> expedientsConsultaDissenyDto) {
    int rowNum = 0;
    int colNum = 0;

    // Capçalera
    HSSFRow xlsRow = sheet.createRow(rowNum++);

    HSSFCell cell;

    cell = xlsRow.createCell(colNum++);
    cell.setCellValue(new HSSFRichTextString(StringUtils.capitalize("Expedient")));
    cell.setCellStyle(headerStyle);

    Iterator<Entry<String, DadaIndexadaDto>> it = expedientsConsultaDissenyDto.get(0).getDadesExpedient().entrySet().iterator();
    while (it.hasNext()) {
        Map.Entry<String, DadaIndexadaDto> e = (Map.Entry<String, DadaIndexadaDto>)it.next();
        sheet.autoSizeColumn(colNum);
        cell = xlsRow.createCell(colNum++);
        cell.setCellValue(new HSSFRichTextString(StringUtils.capitalize(e.getValue().getEtiqueta())));
        cell.setCellStyle(headerStyle);
    }
}

/**
 * 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 static int getIntCellValue(HSSFSheet sheet, int r, int c) {
    HSSFRow row = sheet.getRow(r);
    if (row == null) {
        return 0;
    }
    HSSFCell cell = row.getCell(c);

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

    return (int) cell.getNumericCellValue();
}

/**
 * 获取EXCEL文件单元列值
 * 
 * @param row
 * @param point
 * @return
 */
private static String getCellValue(HSSFRow row, int point) {
    String reString = "";
    try {
        HSSFCell cell = row.getCell((short) point);
        if (cell.getCellType() == 1)
            reString = cell.getStringCellValue();
        else if (cell.getCellType() == 0) {
            reString = convert(cell.getNumericCellValue());
            BigDecimal bd = new BigDecimal(reString);
            reString = bd.toPlainString();
        } else {
            reString = "";
        }
        System.out.println(cell.getCellType() + ":" + cell.getCellFormula());
    } catch (Exception localException) {
    }
    return checkNull(reString);
}

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

/**
 */
protected void createColumnHeaders() {
    final HSSFRow headersRow = this.sheet.createRow(0);
    final HSSFFont font = this.workbook.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    final HSSFCellStyle style = this.workbook.createCellStyle();
    style.setFont(font);
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    int counter = 1;
    for (int i = 0; i < this.model.getColumnCount(); i++) {
        final HSSFCell cell = headersRow.createCell(counter++);
        // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
        cell.setCellValue(this.model.getColumnName(i));
        cell.setCellStyle(style);
    }
}

/**
 *
 * @param rowIndex
 *            int
 */
protected void createRow(final int rowIndex) {
    final HSSFRow row = this.sheet.createRow(rowIndex + 1); // since the
                                                            // rows in
    // excel starts from 1
    // not 0
    final HSSFCellStyle style = this.workbook.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    int counter = 1;
    for (int i = 0; i < this.model.getColumnCount(); i++) {
        final HSSFCell cell = row.createCell(counter++);
        // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
        final Object value = this.model.getValueAt(rowIndex, i);
        setValue(cell, value);
        cell.setCellStyle(style);
    }
}

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

@Override
protected void writeDirectTestData(ERTable table,
        Map<NormalColumn, String> data, String database) {
    HSSFRow row = this.sheet.createRow(this.rowNum++);

    int col = 0;

    for (NormalColumn column : table.getExpandedColumns()) {
        HSSFCell cell = row.createCell(col++);

        String value = Format.null2blank(data.get(column));

        if (value == null || "null".equals(value.toLowerCase())) {

        } else {
            cell.setCellValue(new HSSFRichTextString(value));
        }
    }
}

/**
 * @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) ;
}

private boolean createHeader(WorkbookGeneratorContext context,
                            HSSFSheet sheet,
                            HSSFCellStyle style) {
    if (context.headerNames == null || context.headerNames.isEmpty()) {
        return false;
    }

    int headerRowIndex = 0;
    HSSFRow rowHeader = sheet.createRow(headerRowIndex);
    for (int i = 0; i < context.headerNames.size(); i++) {
        HSSFCell cell = rowHeader.createCell(i);
        sheet.autoSizeColumn((short) i);

        cell.setCellStyle(style);
        cell.setCellValue(new HSSFRichTextString(context.headerNames.get(i)));
    }
    return true;
}

public static boolean createHeader(HSSFWorkbook workbook, HSSFSheet sheet,String[] header) throws Exception{
    boolean flag = false;
    try {
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        HSSFRow head_row = sheet.createRow(0); // 创建行
        // 操作head
        for (short h = 0; h < header.length; h++) {
            createcsv(head_row, h, header[h], cellStyle);
        }
        flag = true;
    } catch (Exception e) {
        logger.error(sheet.getSheetName() + " : 抬头标题创建失败");
        throw e;
    }
    return flag;
}

public static boolean createTop(HSSFWorkbook workbook, HSSFSheet sheet,String[] header, HSSFCellStyle cellStyle) throws Exception{
    boolean flag = false;
    try {
        HSSFRow head_row = sheet.createRow(1); // 创建行

        // 操作head
        for (short h = 0; h < header.length; h++) {
            createcsv(head_row, h, header[h], cellStyle);
        }
        flag = true;
    } catch (Exception e) {
        logger.error(sheet.getSheetName() + " : 抬头标题创建失败");
        throw e;
    }
    return flag;
}

public static List<HSSFCellStyle> copyCellStyle(HSSFWorkbook workbook,
        HSSFRow row) {
    List<HSSFCellStyle> cellStyleList = new ArrayList<HSSFCellStyle>();

    for (int colNum = row.getFirstCellNum(); colNum <= row.getLastCellNum(); colNum++) {

        HSSFCell cell = row.getCell(colNum);
        if (cell != null) {
            HSSFCellStyle style = cell.getCellStyle();
            HSSFCellStyle newCellStyle = copyCellStyle(workbook, style);
            cellStyleList.add(newCellStyle);
        } else {
            cellStyleList.add(null);
        }
    }

    return cellStyleList;
}

public void write(String src) throws IOException{
  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 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 static void readExcel(String filePth) throws Exception {
    InputStream is = new FileInputStream(filePth);
    //创建工作薄
    //XSSFWorkbook hwb = new XSSFWorkbook(is);
    HSSFWorkbook hwb = new HSSFWorkbook(new POIFSFileSystem(is));
    //得到sheet
    for (int i = 0; i < hwb.getNumberOfSheets(); i++) {
        HSSFSheet sheet = hwb.getSheetAt(i);
        int rows = sheet.getPhysicalNumberOfRows();
        //遍历每一行
        for (int j = 0; j < rows; j++) {
            HSSFRow hr = sheet.getRow(j);
            Iterator<?> it = hr.iterator();
            while(it.hasNext()){
                String context = it.next().toString();
                System.out.println(context);
            }
        }
    }
    hwb.close();
}

public static void readExcel(String filePth) throws Exception {
    InputStream is = new FileInputStream(filePth);
    //创建工作薄
    //XSSFWorkbook hwb = new XSSFWorkbook(is);
    HSSFWorkbook hwb = new HSSFWorkbook(new POIFSFileSystem(is));
    //得到sheet
    for (int i = 0; i < hwb.getNumberOfSheets(); i++) {
        HSSFSheet sheet = hwb.getSheetAt(i);
        int rows = sheet.getPhysicalNumberOfRows();
        //遍历每一行
        for (int j = 0; j < rows; j++) {
            HSSFRow hr = sheet.getRow(j);
            Iterator<?> it = hr.iterator();
            while(it.hasNext()){
                String context = it.next().toString();
                System.out.println(context);
            }
        }
    }
    hwb.close();
}

/**
 * 表头条件
 * @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);
                }
            }
        }
    }

/**
   * 初始化表头
   * @param sheet
   * @param columnJson
   * @param rowNumber
   */
  private static void writeSheetHead(HSSFSheet sheet,JSONObject columnJson,int rowNumber){
if (logger.isDebugEnabled()) {
    logger.debug("writeSheetHead(HSSFSheet, JSONObject, int) - start"); //$NON-NLS-1$
}

Set<String> keySet = columnJson.keySet();
int cellNumber = 0;
HSSFRow row = sheet.createRow(rowNumber);
for (String k : keySet) {//k:GOODS_NO
    String name = columnJson.getString(k);//品项编码
    sheet.autoSizeColumn(cellNumber);
    HSSFCell cell = row.createCell(cellNumber++);
    cell.setCellValue(name);
}

if (logger.isDebugEnabled()) {
    logger.debug("writeSheetHead(HSSFSheet, JSONObject, int) - end"); //$NON-NLS-1$
}
  }

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 Integer findColumn(final HSSFRow row, final String str) {
    for (int colNum = row.getFirstCellNum(); colNum <= row.getLastCellNum(); colNum++) {
        final HSSFCell cell = row.getCell(colNum);

        if (cell == null) {
            continue;
        }

        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
            final HSSFRichTextString cellValue = cell.getRichStringCellValue();

            if (str.equals(cellValue.getString())) {
                return Integer.valueOf(colNum);
            }
        }
    }

    return null;
}

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 String getCellValue(final HSSFSheet sheet, final int r, final int c) {
    final HSSFRow row = sheet.getRow(r);

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

    final HSSFCell cell = row.getCell(c);

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

    final HSSFRichTextString cellValue = cell.getRichStringCellValue();

    return cellValue.toString();
}

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 boolean getBooleanCellValue(final HSSFSheet sheet, final int r, final int c) {
    final HSSFRow row = sheet.getRow(r);

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

    final HSSFCell cell = row.getCell(c);

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

    try {
        return cell.getBooleanCellValue();
    } catch (final RuntimeException e) {
        System.err.println("Exception at sheet name:" + sheet.getSheetName() + ", row:" + (r + 1) + ", col:" + (c + 1));
        throw 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 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());
            }
        }
    }
}

protected void setColumnData(final Map<String, String> keywordsValueMap, final ColumnTemplate columnTemplate, final HSSFRow row, final NormalColumn normalColumn, final TableView tableView, final int order) {

        for (final int columnNum : columnTemplate.columnTemplateMap.keySet()) {
            final HSSFCell cell = row.createCell(columnNum);
            final String template = columnTemplate.columnTemplateMap.get(columnNum);

            String value = null;
            if (KEYWORD_ORDER.equals(template)) {
                value = String.valueOf(order);

            } else {
                value = getColumnValue(keywordsValueMap, normalColumn, tableView, template);
            }

            try {
                final double num = Double.parseDouble(value);
                cell.setCellValue(num);

            } catch (final NumberFormatException e) {
                final HSSFRichTextString text = new HSSFRichTextString(value);
                cell.setCellValue(text);
            }
        }
    }

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

@Override
protected void writeDirectTestData(final ERTable table, final Map<NormalColumn, String> data, final String database) {
    final HSSFRow row = sheet.createRow(rowNum++);

    int col = 0;

    for (final NormalColumn column : table.getExpandedColumns()) {
        final HSSFCell cell = row.createCell(col++);

        final String value = Format.null2blank(data.get(column));

        if (value == null || "null".equals(value.toLowerCase())) {

        } else {
            cell.setCellValue(new HSSFRichTextString(value));
        }
    }
}

public static boolean createHeader(HSSFWorkbook workbook, HSSFSheet sheet,String[] header) throws Exception{
    boolean flag = false;
    try {
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        HSSFRow head_row = sheet.createRow(0); // 创建行
        // 操作head
        for (short h = 0; h < header.length; h++) {
            createcsv(head_row, h, header[h], cellStyle);
        }
        flag = true;
    } catch (Exception e) {
        logger.error(sheet.getSheetName() + " : 抬头标题创建失败");
        throw e;
    }
    return flag;
}