Apache POI - 在数据库中读取和存储富文本内容 [英] 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 DB 表中.随后我们还需要提取这些数据并下载到保留格式的Excel表格中.

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.

HSSFRichTextString 的 tostring() 方法给出了字符串，但格式丢失了.有人可以建议我如何将此 HSSFRichTextString 对象转换为 Oracle 数据类型(最好是 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() 方法只会返回未格式化的 String 内容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.

非常类似于 我对此的回答问题，从 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) - 返回出现在字符串中指定位置的 short 字体索引

• 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

现在，实际提取数据:

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 表.

with (rts_id, run_id) being the primary key, and rts_id referring back to the rich_text_string table.

使用您喜欢的 Java 到数据库框架(JDBC、Hibernate 等)，将 String 值存储到 rich_text_string 中的 contents，以及关联的 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 - 在数据库中读取和存储富文本内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！