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

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

/**
 * See the comment for the given cell
 * 
 * @param cell
 *            the cell
 * @param message
 *            the comment message
 */
public static void setComment(HSSFCell cell, String message) {
    Drawing drawing = cell.getSheet().createDrawingPatriarch();
    CreationHelper factory = cell.getSheet().getWorkbook().getCreationHelper();

    // When the comment box is visible, have it show in a 1x3 space
    ClientAnchor anchor = factory.createClientAnchor();
    anchor.setCol1(cell.getColumnIndex());
    anchor.setCol2(cell.getColumnIndex() + 1);
    anchor.setRow1(cell.getRowIndex());
    anchor.setRow2(cell.getRowIndex() + 1);
    anchor.setDx1(100);
    anchor.setDx2(1000);
    anchor.setDy1(100);
    anchor.setDy2(1000);

    // Create the comment and set the text+author
    Comment comment = drawing.createCellComment(anchor);
    RichTextString str = factory.createRichTextString(message);
    comment.setString(str);
    comment.setAuthor("TURNUS");
    // Assign the comment to the cell
    cell.setCellComment(comment);
}

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

private String getCellValue(HSSFCell cell){
    if(cell == null) return "";

    switch (cell.getCellType()) {
    case HSSFCell.CELL_TYPE_STRING: return cell.getStringCellValue();
    case HSSFCell.CELL_TYPE_BOOLEAN : return Boolean.toString(cell.getBooleanCellValue());
    case HSSFCell.CELL_TYPE_NUMERIC : 
        if(HSSFDateUtil.isCellDateFormatted(cell))
            return DateUtils.formatDateTime("yyyyMMdd", HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
        else
            return new BigDecimal(cell.getNumericCellValue()).toPlainString();
    case HSSFCell.CELL_TYPE_FORMULA : return "";
    case HSSFCell.CELL_TYPE_BLANK : return "";
    default:return "";
    }
}

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

private boolean parseBooleanCell(HSSFCell cell) {
if (cell != null) {
    String value;
    try {
    cell.setCellType(Cell.CELL_TYPE_STRING);
    if (cell.getStringCellValue() != null) {
        if (cell.getStringCellValue().trim().length() != 0) {
        emptyRow = false;
        }
    } else {
        return false;
    }
    value = cell.getStringCellValue().trim();
    } catch (Exception e) {
    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
    double d = cell.getNumericCellValue();
    emptyRow = false;
    value = new Long(new Double(d).longValue()).toString();
    }
    if (StringUtils.equals(value, "1") || StringUtils.equalsIgnoreCase(value, "true")) {
    return true;
    }
}
return false;
   }

private String parseStringCell(HSSFCell cell) {
if (cell != null) {
    try {
    cell.setCellType(Cell.CELL_TYPE_STRING);
    if (cell.getStringCellValue() != null) {
        if (cell.getStringCellValue().trim().length() != 0) {
        emptyRow = false;
        }
    } else {
        return null;
    }
    // log.debug("string cell value: '"+cell.getStringCellValue().trim()+"'");
    return cell.getStringCellValue().trim();
    } catch (Exception e) {
    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
    double d = cell.getNumericCellValue();
    emptyRow = false;
    // log.debug("numeric cell value: '"+d+"'");
    return (new Long(new Double(d).longValue()).toString());
    }
}
return null;
   }

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

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

/**
 * @param cell
 * @param value
 */
private void setValue(final HSSFCell cell, final Object value) {
    if (value == null) {
        cell.setCellValue("-");
    } else if (value instanceof Float || value instanceof Double || value instanceof Integer || value instanceof Long
            || value instanceof BigDecimal) {
        cell.setCellValue(Double.parseDouble(value.toString()));
    } else if (value instanceof String) {
        cell.setCellValue(value.toString());
    } else if (value instanceof Date) {
        cell.setCellValue((Date) value);
    } else {
        logger.info("No Special excel r endering for class : " + value.getClass().getName());
        cell.setCellValue(value.toString());
    }
}

/**
    * Write the value to the cell. Override this method if you have complex data types that may need to be exported.
    * @param value the value of the cell
    * @param cell the cell to write it to
    */
protected void writeCell(Object value, HSSFCell cell, HSSFWorkbook wb)
   {
       if (value instanceof Number)
       {
           Number num = (Number) value;
           cell.setCellValue(num.doubleValue());
       }
       else if (value instanceof Date)
       {
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setDataFormat(
                wb.getCreationHelper().createDataFormat().getFormat("dd/MM/yyyy HH:mm"));
        cell.setCellStyle(cellStyle);
        cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
           cell.setCellValue((Date) value);
       }
       else if (value instanceof Calendar)
       {
           cell.setCellValue((Calendar) value);
       }
       else
       {
           cell.setCellValue(new HSSFRichTextString(escapeColumnValue(value)));
       }
   }

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

/**
 * 表头条件
 * @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 void setCellStyle(HSSFCell cell, String metricName, String scope, Double value) {
    // getting the configuration of the metric
    Map<String, MetricConfiguration> mm = MetricConfigurations.getMc().getMetricConfigurationsMap();

    MetricConfiguration mc = mm.get(metricName + " " + scope);
    if (mc != null) {
        if (value < mc.getMinimum() || mc.getMaximum() < value) {
            cell.setCellStyle(ReportGeneratorImpl.getRedBlueStyle());
        } else {
            cell.setCellStyle(ReportGeneratorImpl.getBlueStyle());
        }
    } else {
        cell.setCellStyle(ReportGeneratorImpl.getBlueStyle());
    }
}

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 Integer findMatchColumn(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) {
            continue;
        }

        final HSSFRichTextString cellValue = cell.getRichStringCellValue();

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

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

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

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

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

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

    return cellStyleList;
}

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

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

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

/**
 * Set a bold font for the given cell with a given font size (in pt).
 * 
 * @param wb
 *            the workbook that contains the cell
 * @param cell
 *            the cell where the text is contained
 * @param size
 *            the size in pt of the text
 */
public static void setBold(Workbook wb, HSSFCell cell, short size) {
    Font font = wb.createFont();
    font.setFontHeightInPoints((short) size);
    font.setFontName("Arial");
    font.setColor(IndexedColors.BLACK.getIndex());
    font.setBold(true);
    font.setItalic(false);

    CellStyle style = wb.createCellStyle();
    style.setFont(font);
    cell.setCellStyle(style);
}

/**
 * Set a link to a cell. The link type should one of {@link Hyperlink}
 * 
 * @param wb
 *            the workbook which contains the cell
 * @param cell
 *            the cell where the link is stored
 * @param address
 *            the cell destination address
 * @param linkType
 *            the type selected among {@link Hyperlink}
 */
public static void setLink(Workbook wb, HSSFCell cell, String address, int linkType) {
    CreationHelper helper = wb.getCreationHelper();
    CellStyle style = wb.createCellStyle();
    Font font = wb.createFont();
    font.setUnderline(Font.U_SINGLE);
    font.setColor(IndexedColors.BLUE.getIndex());
    style.setFont(font);

    Hyperlink link = helper.createHyperlink(linkType);
    link.setAddress(address);
    cell.setHyperlink(link);
    cell.setCellStyle(style);
}

public static Integer findColumn(HSSFRow row, String str) {
    for (int colNum = row.getFirstCellNum(); colNum <= row.getLastCellNum(); colNum++) {
        HSSFCell cell = row.getCell(colNum);

        if (cell == null) {
            continue;
        }

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

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

    return null;
}

public static Integer findMatchColumn(HSSFRow row, String str) {
    for (int colNum = row.getFirstCellNum(); colNum <= row.getLastCellNum(); colNum++) {
        HSSFCell cell = row.getCell(colNum);

        if (cell == null) {
            continue;
        }

        if (cell.getCellType() != HSSFCell.CELL_TYPE_STRING) {
            continue;
        }

        HSSFRichTextString cellValue = cell.getRichStringCellValue();

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

    return null;
}

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