如何在java中将POI Workbook添加为json元素 [英] How to add POI Workbook as a json element in java
问题描述
我已经在这个问题上挣扎了好几天.我需要将 Apache poi Workbook 添加到 json 对象.我尝试了 Json.org、objectmapper 和 gson 库.我的序列化方法对于所有经过测试的库都是类似的，例如:
I have been struggling for days in this issue. I need to add Apache poi Workbook to json object. I tried Json.org, objectmapper and gson libraries. My approach in order to serialize it is similar for all the tested libraries, something like:
objectMapper.writeValue(writer, objectToSerialize);
当我想写入 json 我的 Workbook 对象时，我收到此错误:
When I want to Write to json my Workbook object I get this error:
Caused by: com.fasterxml.jackson.databind.JsonMappingException: Not implemented yet (through reference chain: com.....[".."]->java.util.ArrayList[0]->com.["workbook"]->org.apache.poi.xssf.usermodel.XSSFWorkbook["hidden"])
at com.fasterxml.jackson.databind.JsonMappingException.wrapWithPath(JsonMappingException.java:388)
at com.fasterxml.jackson.databind.JsonMappingException.wrapWithPath(JsonMappingException.java:348)
at com.fasterxml.jackson.databind.ser.std.StdSerializer.wrapAndThrow(StdSerializer.java:343)
有没有办法解决这个问题，或者我应该尝试另一种方法吗?谢谢.
Is there a way to solve this issue or should I try another approach ? Thanks.
推荐答案
Axel Richter 的评论是对的，工作簿不可序列化.
Axel Richter's comment is right, the workbook is not serializable.
发送 Java 对象 并避免对文件本身进行编码的解决方法，首先使用 WorkBook's write(OutputStreams) 方法:
The workaround to send the Java Object and avoid encoding the file itself, starts by using WorkBook's write(OutputStream s) method:
void write(java.io.OutputStream stream)
将此工作簿写入输出流.
完成后，我们可以将源 OutputStream 的字节转换为 Base64 编码的字符串.现在发送的步骤已经完成.
Once done, we can convert the origin OutputStream's bytes to a Base64 encoded string. The steps in order to send it are complete by now.
现在，当接收到字符串时，为了将其读回Workbook Object，关键是使用Workbook(InputStream s) 构造函数.Workbook 的三个直接实现中的两个提供了这个构造函数，而最棘手的一个需要多一步.(下面的附录中有关此的信息)*.
Now, when receiving the string, in order to read it back to a Workbook Object, the key is using the Workbook(InputStream s) constructor. Two of the three direct implementations of Workbook offer this constructor, while the trickiest one would need one more step. (Info about this on the appendix below)*.
这段代码是一个简单的测试，探索了整个过程.
This code is a simple test probing the whole process.
工作簿 ->转换 ->发送 ->接收 ->恢复 ->工作簿
使用Workbook 的HSSFWorkbook 实现来实际测试它.
Using the HSSFWorkbook implementation of Workbook in order to actually test it.
HSSFWorkbook wb = new HSSFWorkbook();
wb.createSheet("test");
ByteArrayOutputStream binOut = new ByteArrayOutputStream();
wb.write(binOut);
String wbString = Base64.getEncoder().encodeToString(binOut.toByteArray());
wb.close();
现在工作簿表示为字符串 [wbString]，您可以将其附加到 JSON 中.另请注意，ByteArray 流不需要关闭，但从我读过的内容来看，工作簿应该.
Now the Workbook is represented as a String [wbString], which you can append into the JSON. Also note that ByteArray streams don't need to be closed, but from what I've read, the Workbook should.
出于好奇，本示例的 base64 编码:
0M8R4KGxGuEAAAAAAAAAAAAAAAAAAAAAOwADAP7/CQAGAAAAAAAAAAAAAAABAAAAAAAAAAAAAAAAEAAABQAAAAEAAAD+////AAAAAAYAAAD///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////9SAG8AbwB0ACAARQBuAHQAcgB5AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAFgAFAf//////////AQAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEAAACABgAAAAAAAFcAbwByAGsAYgBvAG8AawAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAASAAIB////////////////AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEgGAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAIAAAD///////////////8AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAgAAAP///////////////wAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAkIEAAABgUA0xDMB0EAAAAGAAAA4QACALAEwQACAAAA4gAAAFwAcAAFAAA2MDE1NCAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgQgACALAEYQECAAAAPQECAAAAnAACAA4AGQACAAAAEgACAAAAEwACAAAArwECAAAAvAECAAAAPQASAGgBDgFcOr4jOAAAAAAAAQBYAkAAAgAAAI0AAgAAACIAAgAAAA4AAgABALcBAgAAANoAAgAAADEAFQDIAAAA/3+QAQAAAAAAAAUAQXJpYWwxABUAyAAAAP9/kAEAAAAAAAAFAEFyaWFsMQAVAMgAAAD/f5ABAAAAAAAABQBBcmlhbDEAFQDIAAAA/3+QAQAAAAAAAAUAQXJpYWweBBoABQAVAAAiJCIjLCMjMF8pOygiJCIjLCMjMCkeBB8ABgAaAAAiJCIjLCMjMF8pO1tSZWRdKCIkIiMsIyMwKR4EIAAHABsAACIkIiMsIyMwLjAwXyk7KCIkIiMsIyMwLjAwKR4EJQAIACAAACIkIiMsIyMwLjAwXyk7W1JlZF0oIiQiIywjIzAuMDApHgQsACoAJwAAXygqICMsIyMwXyk7XygqICgjLCMjMCk7XygqICItIl8pO18oQF8pHgQ1ACkAMAAAXygiJCIqICMsIyMwXyk7XygiJCIqICgjLCMjMCk7XygiJCIqICItIl8pO18oQF8pHgQ0ACwALwAAXygqICMsIyMwLjAwXyk7XygqICgjLCMjMC4wMCk7XygqICItIj8/Xyk7XyhAXykeBD0AKwA4AABfKCIkIiogIywjIzAuMDBfKTtfKCIkIiogKCMsIyMwLjAwKTtfKCIkIiogIi0iPz9fKTtfKEBfKeAAFAAAAAAA9f8gAAAAAAAAAAAAAADAIOAAFAABAAAA9f8gAAD0AAAAAAAAAADAIOAAFAABAAAA9f8gAAD0AAAAAAAAAADAIOAAFAACAAAA9f8gAAD0AAAAAAAAAADAIOAAFAACAAAA9f8gAAD0AAAAAAAAAADAIOAAFAAAAAAA9f8gAAD0AAAAAAAAAADAIOAAFAAAAAAA9f8gAAD0AAAAAAAAAADAIOAAFAAAAAAA9f8gAAD0AAAAAAAAAADAIOAAFAAAAAAA9f8gAAD0AAAAAAAAAADAIOAAFAAAAAAA9f8gAAD0AAAAAAAAAADAIOAAFAAAAAAA9f8gAAD0AAAAAAAAAADAIOAAFAAAAAAA9f8gAAD0AAAAAAAAAADAIOAAFAAAAAAA9f8gAAD0AAAAAAAAAADAIOAAFAAAAAAA9f8gAAD0AAAAAAAAAADAIOAAFAAAAAAA9f8gAAD0AAAAAAAAAADAIOAAFAAAAAAAAQAgAAAAAAAAAAAAAADAIOAAFAABACsA9f8gAAD4AAAAAAAAAADAIOAAFAABACkA9f8gAAD4AAAAAAAAAADAIOAAFAABACwA9f8gAAD4AAAAAAAAAADAIOAAFAABACoA9f8gAAD4AAAAAAAAAADAIOAAFAABAAkA9f8gAAD4AAAAAAAAAADAIJMCBAAQgAP/kwIEABGABv+TAgQAEoAE/5MCBAATgAf/kwIEAACAAP+TAgQAFIAF/2ABAgAAAIUACwBLBQAAAAADAGFhYYwABAABAAEArgEEAAEAAQQXAAgAAQAAAAAAAAD8AAgAAAAAAAAAAAD/AAIACAAKAAAACQgQAAAGEAC7DcwHwQAAAAYAAAALAhAAAAAAAP////8AAAAAAAAAAA0AAgABAAwAAgBkAA8AAgABABEAAgAAABAACAD8qfHSTWJQP18AAgABACoAAgAAACsAAgAAAIIAAgABAIAACAAAAAAAAAAAACUCBAAAAP8AgQACAMEEFAAAABUAAACDAAIAAACEAAIAAAChACIAAQBkAAEAAQABAAIALAEsAQAAAAAAAOA/AAAAAAAA4D8BAFUAAgAIAAACDgAAAAAAAQAAAAAAAQAAAD4CEgC2BgAAAABAAAAAAAAAAAAAAAAdAA8AAwAAAAAAAAEAAAAAAAAACgAAAP//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////AQAAAAIAAAADAAAABAAAAAUAAAAGAAAABwAAAAgAAAAJAAAACgAAAAsAAAAMAAAADQAAAA4AAAAPAAAAEAAAABEAAAASAAAAEwAAABQAAAAVAAAAFgAAABcAAAAYAAAAGQAAAP7////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////+////AgAAAAMAAAAEAAAA/v////7////+/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////w==
解码 - 接收 {从 Json 读取}
为了取回您的 Workbook 对象，一旦您从 Json 字段中读取字符串 - 在此示例中为 wbJsonString.使用 Workbook(java.io.InputStream s) 构造函数，您可以取回原始工作簿:
Decode - Receive {read from Json}
In order to get back your Workbook object, once you read the string from the Json field - in this example wbJsonString. Using the Workbook(java.io.InputStream s) constructor you get your original Workbook back:
byte[] decodedB64 = Base64.getDecoder().decode(wbJsonString);
ByteArrayInputStream bis = new ByteArrayInputStream(decodedB64);
HSSFWorkbook receivedWb = new HSSFWorkbook(bis);
System.out.println(receivedWb.getSheetAt(0).getSheetName()); // --> test
还有另一种可能的方法，使用解码器的 wrap 方法.这将返回一个用于解码 Base64 编码字节流的输入流，因此您可以直接将其传递给 Workbook 构造函数:
There's another possible approach for this, using the decoder's wrap method. This will return an input stream for decoding Base64 encoded byte stream, so you can directly pass it to the Workbook constructor:
InputStream b64Ins = Base64.getDecoder()
.wrap(new ByteArrayInputStream(wbJsonString.getBytes()));
HSSFWorkbook receivedWb = new HSSFWorkbook(b64Ins);
就是这样，您正确地将工作簿作为字符串发送并将其转换回来.
And that's it, you properly sent the Workbook as String and converted it back.
请注意，这也可以让您将 Workbook Java 对象保存/发送为文本，而不管序列化格式(Json、protobuff ..)如何
Note that this would also let you save/send the Workbook Java Objects as text, regardless of the serialization format (Json, protobuff,..)
即使不需要 Base64 编码来获得字符串表示，它也能保证数据的完整性.
工作簿界面 提供 4 种实现:
HSSFWorkbook
XSSFWorkbook
SXSSFWorkbook
3.1 SXSSFWorkbookWithCustomZipEntrySource
上面的代码将直接用于 HSSFWorkbook 和 XSSFWorkbook 对象.
The code above will directly work with HSSFWorkbook and XSSFWorkbook objects.
大名称的实现是SXSSFWorkbook 的扩展，因此这同样适用于后两个.此实现中的区别在于对象创建，因为它们不提供基于 Inputstream 的构造函数，而前两个实现则提供.
The large name's implementation is an extension of SXSSFWorkbook, so the same applies for these last two. The difference within this implementations is in the object creation, as these don't offer the constructor based on an Inputstream that the first two do.
但它确实提供了这个:SXSSFWorkbook(XSSFWorkbook workbook).因此，唯一的额外步骤将包括调用此构造函数.例如:
But it does offer this one: SXSSFWorkbook(XSSFWorkbook workbook). So, the only additional step would consist of calling this constructor. For example:
InputStream b64Ins = Base64.getDecoder()
.wrap(new ByteArrayInputStream(wbJsonString.getBytes()));
SXSSFWorkbook sxss = new SXSSFWorkbook(new XSSFWorkbook(b64Ins));
/* The inputstream will by itself decode the base 64 encoded bytes,
allowing the creation of the XSSFWorkbook. But who the hell cares about
XSSF when you can have the KING OF kings, aka SXSSF? NO-BO-DY. So
we just send it as argument for the SXSSFWorkbook creation and forget
that looser. All the POI fans know SXSSF is the implementation that can
get you laid in a matter of seconds if shown to that co-worker...
Yes you know what I'm talking about. Come on baby burn my fire.
Also, Apache, "SXSSFWorkbookWithCustomZipEntrySource"? Really? Who
chooses your names? */
这篇关于如何在java中将POI Workbook添加为json元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
