Persisting XSSFRichTextString in (mongo)DB using Apache POI

Question

For a project I'm reading rich text from an excel file (.xlsx), which should be stored in a mongoDB to be copy-pasted into another excel file later. Right now I'm trying to save it like this:

public static BasicDBObject createDBGlobalIssue(GlobalIssue g) {
        BasicDBObject o = new BasicDBObject();
            XSSFRichTextString richText = g.getRichValue();
            byte[] value;
            try {
                value = transform(richText);
            } catch (IOException e) {
                throw new RuntimeException(e.getMessage(), e);
            }
            o.put("test", value);
        }
        return o;
    }

public static byte[] transform(XSSFRichTextString yourObject) throws IOException {
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        ObjectOutput out = null;
        try {
            out = new ObjectOutputStream(bos);
            out.writeObject(yourObject);
            byte[] yourBytes = bos.toByteArray();
            return yourBytes;
        } finally {
            // lots of try-catch
        }
    }

and trying to convert it back like this:

public static GlobalIssue createGlobalIssue(BasicDBObject o) {
        byte[] stream = (byte[])o.get("test");
        XSSFRichTextString record;
        try {
            record = (XSSFRichTextString)unPack(stream);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e.getMessage(), e);
        } catch (IOException e) {
            throw new RuntimeException(e.getMessage(), e);
        }
    }
    GlobalIssue g = new GlobalIssue(richStringsByAttribute);
    return g;
}

public static XSSFRichTextString unPack(byte[] input) throws ClassNotFoundException, IOException {
        ByteArrayInputStream bis = new ByteArrayInputStream(input);
        ObjectInput in = null;
        try {
            in = new ObjectInputStream(bis);
            Object o = in.readObject();
            return (XSSFRichTextString)o;
        } finally {
            try {
                bis.close();
            } catch (IOException ex) {
                // ignore close exception
            }
            try {
                if (in != null) {
                    in.close();
                }
            } catch (IOException ex) {
                // ignore close exception
            }
        }
    }

unfortunately, this results in an error because XSSFRichTextString is not serializable: Caused by: java.io.NotSerializableException: org.apache.poi.xssf.usermodel.XSSFRichTextString

I know that for HSSF there is a wrapper class that is serializable (TextObjectRecord) but all my attempts to write a similar class for XSSF have failed. I'd rather not switch my whole program back to HSSF because it's outdated and hurts in many other places.

Does anyone know how I could tackle this problem? thanks!

score 0 · Answer 1 · answered May 13 '15 at 12:56

I could not find a way around this. Instead I'm now saving my entire workbook in the database as a byte[]. I'm saving the richTextStrings as normal Strings, but I've added a reference to the cell in the workbook where it came from. so whenever I need the rich string, I can just rebuild the workbook from the byte[], and look-up the string using the references.

Persisting XSSFRichTextString in (mongo)DB using Apache POI

1 Answers1