Apache的POI - 读取和存储DB富文本内容 [英] Apache POI - Read and store Rich Text content in DB

问题描述

我们已经在我们的Java应用程序的新要求，其中用户将上传的Excel文件。
在一个Excel文件中的列将粗体，斜体，要点，彩色文本等格式。

We have a new requirement in our java application where user’s would upload an excel file. One of the column in the excel file will be formatted with bold, italics, bullet points, colored text etc.

我们需要读这个excel文件，并存储在Oracle数据库表中的这些值。
也随之我们需要提取这些数据和下载到Excel工作表的格式preserved。

We need to read this excel file and store these values in Oracle DB table. Also subsequently we need to extract these data and download into excel sheet with the formatting preserved.

我们计划使用 Apache的POI 作为相同的，但现在卡在我们有需要的 HSSFRichTextString 对象点被转换成一个的格式存储到Oracle表

We planned to use the Apache-poi for the same, but now stuck at the point where we have the HSSFRichTextString object that needs to be converted into a format to store into Oracle table.

的的ToString（） HSSFRichTextString 的方法提供了字符串，但格式会丢失。
是否有人可以建议我如何将此 HSSFRichTextString 对象转换为Oracle数据类型（preferably CLOB）。

The tostring() method of HSSFRichTextString gives the string but the formatting is lost. Can someone please suggest me how to convert this HSSFRichTextString object into Oracle data type (preferably clob).

推荐答案

您是正确的，该的toString（）方法将只返回未格式化的字符串内容的 HSSFRichTextString 。

You are right in that the toString() method will just return the unformatted String contents of the HSSFRichTextString.

下面是提取了所有的其他重要数据的方法的 HSSFRichTextString 来存储与字符串值。

Here is a method of extracting out all the other important data from the HSSFRichTextString to be stored with the string value.

非常相似，<一个href=\"http://stackoverflow.com/questions/9699151/is-it-possible-to-append-2-rich-text-strings/18369404#18369404\">my这个问题的答案，提取富文本格式信息将在 HSSFRichTextString ，而数据存储在您将创建一个类， FormattingRun 。

Very similar to my answer to this question, extract the rich text formatting information from the HSSFRichTextString, and store that data in a class you'll create, FormattingRun.

public class FormattingRun {
    private int beginIdx;
    private int length;
    private short fontIdx;
    public FormattingRun(int beginIdx, int length, short fontIdx) {
        this.beginIdx = beginIdx;
        this.length = length;
        this.fontIdx = fontIdx;
    }
    public int getBegin() { return beginIdx; }
    public int getLength() { return length; }
    public short getFontIndex { return fontIdx; }
}

然后，调用的Apache POI的方法来提取数据。

Then, call Apache POI methods to extract that data.

• numFormattingRuns（） - 返回格式化奔跑在 HSFFRichTextString 。
  
• getFontOfFormattingRun（INT） - 返回短字体索引$ P $在字符串中的指定位置psent

• numFormattingRuns() - Returns the number of formatting runs in the HSFFRichTextString.
• getFontOfFormattingRun(int) - Returns the short font index present at the specified position in the string

现在，在数据的实际提取

Now, the actual extraction of the data:

List<FormattingRun> formattingRuns = new ArrayList<FormattingRun>();
int numFormattingRuns = richTextString.numFormattingRuns();
for (int fmtIdx = 0; fmtIdx < numFormattingRuns; fmtIdx)
{
    int begin = richTextString.getIndexOfFormattingRun(fmtIdx);
    short fontIndex = richTextString.getFontOfFormattingRun(fmtIdx);

    // Walk the string to determine the length of the formatting run.
    int length = 0;
    for (int j = begin; j < richTextString.length(); j++)
    {
        short currFontIndex = richTextString.getFontAtIndex(j);
        if (currFontIndex == fontIndex)
            length++;
        else
            break;
    }
    formattingRuns.add(new FormattingRun(begin, length, fontIndex));
}

要存储这些数据在数据库中，第一个认识到，有一个 HSSFRichTextString 和 FormattingRun 。因此，在任何Oracle表中你打算存储富文本字符串数据，您需要创建到存储格式运行数据的另一个新表的外键关系。事情是这样的：

To store this data in the database, first recognize that there is a one-to-many relationship between a HSSFRichTextString and FormattingRun. So in whatever Oracle table you're planning on storing the rich text string data, you will need to create a foreign key relationship to another new table that stores the formatting run data. Something like this:

Table: rich_text_string
rts_id     NUMBER
contents   VARCHAR2(4000)

与 rts_id 作为主键，

Table: rts_formatting_runs
rts_id     NUMBER
run_id     NUMBER
run_pos    NUMBER
run_len    NUMBER
font_index NUMBER

与（rts_id，run_id）作为主键， rts_id 指回 rich_text_string 表。

使用您喜欢的Java到数据库的框架（JDBC，Hibernate的等），存储字符串价值为内容在 rich_text_string ，以及相关的 FormattingRun 对象数据为 rt_formatting_runs 。

Using your favorite Java-to-database framework (JDBC, Hibernate, etc.), store the String value into contents in rich_text_string, and the associated FormattingRun object data into rt_formatting_runs.

只是要小心 - 字体指数只有在工作簿内有效。你需要存储从字体信息 HSSFWorkbook 也给 font_index 的含义。

Just be careful - the font index is only valid within the workbook. You'll need to store the font information from the HSSFWorkbook also, to give the font_index meaning.

这不是存储为 CLOB ，但数据可以说是更有意义的这种方式存储。

It's not stored as a CLOB, but the data are arguably more meaningful stored this way.

这篇关于Apache的POI - 读取和存储DB富文本内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！