I am using the POI 3.9 to read data from xlsx. But now I face an issue, the POI don't support the xlsb files, and I need to read data from xlsb programly. Does anybody know how to read data from xlsb programly? Appreciated.
An XLSB file is an Excel Binary Workbook file. Open one with Excel Viewer, Excel, or WPS Office Spreadsheets. Convert to XLSX, CSV, and others with some of those programs or other spreadsheet software.
Files made in the spreadsheet program Excel can be saved in XLSB format. These files are completely normal Excel tables in which data can be stored and calculations performed.
xlsb) Binary File Format, which is a collection of records and structures that specify Excel workbook content. The content can include unstructured or semi-structured tables of numbers, text, or both numbers and text, formulas, external data connections, charts and images.
XLSX files are encoded/decoded as XML files to be saved/loaded from disc. Saving plain text files takes more time and affects the loading/saving of such files. XLSB files are saved as plain binary encoded compressed data which takes less time to save and load.
Using poi you can read XLSB to DB, Structure(XML,...), List of Contents or etc.
The following code convert XLSB to List of row/comment lists and map for extra info.
Just you can customize the code according your needs.
Please find many examples from link; thanks to authors of poi.
// Main class
package excel;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.binary.XSSFBSharedStringsTable;
import org.apache.poi.xssf.binary.XSSFBSheetHandler;
import org.apache.poi.xssf.binary.XSSFBStylesTable;
import org.apache.poi.xssf.eventusermodel.XSSFBReader;
import org.xml.sax.SAXException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
public class Excel {
public static void main (String [] args){
String xlsbFileName = "C:\\Users\\full path to .xlsb file";
callXLToList(xlsbFileName);
}
static void callXLToList(String xlsbFileName){
OPCPackage pkg;
try {
pkg = OPCPackage.open(xlsbFileName);
XSSFBReader r = new XSSFBReader(pkg);
XSSFBSharedStringsTable sst = new XSSFBSharedStringsTable(pkg);
XSSFBStylesTable xssfbStylesTable = r.getXSSFBStylesTable();
XSSFBReader.SheetIterator it = (XSSFBReader.SheetIterator) r.getSheetsData();
List<XLSB2Lists> workBookAsList = new ArrayList<>();
int sheetNr = 1;
while (it.hasNext()) {
InputStream is = it.next();
String name = it.getSheetName();
System.out.println("Begin parsing sheet "+sheetNr+": "+name);
XLSB2Lists testSheetHandler = new XLSB2Lists();
testSheetHandler.startSheet(name);
XSSFBSheetHandler sheetHandler = new XSSFBSheetHandler(is,
xssfbStylesTable,
it.getXSSFBSheetComments(),
sst, testSheetHandler,
new DataFormatter(),
false);
sheetHandler.parse();
testSheetHandler.endSheet();
System.out.println("End parsing sheet "+sheetNr+": "+name);
sheetNr++;
// Add parsed sheet to workbook list
workBookAsList.add(testSheetHandler);
}
// For every sheet in Workbook
System.out.println("\nShort Report:");
for(XLSB2Lists sheet:workBookAsList){
// sheet content
System.out.println("Size of content: " +sheet.getSheetContentAsList().size());
// sheet comment
System.out.println("Size fo comment: "+sheet.getSheetCommentAsList().size());
// sheet extra info
System.out.println("Extra info.: "+sheet.getMapOfInfo().toString());
}
} catch (InvalidFormatException e) {
// TODO Please do your catch hier
e.printStackTrace();
} catch (IOException e) {
// TODO Please do your catch hier
e.printStackTrace();
} catch (OpenXML4JException e) {
// TODO Please do your catch hier
e.printStackTrace();
} catch (SAXException e) {
// TODO Please do your catch hier
e.printStackTrace();
}
}
}
// Parsing class
package excel;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.usermodel.XSSFComment;
/**
*
* @author Dominique
*/
public class XLSB2Lists implements XSSFSheetXMLHandler.SheetContentsHandler {
private final List sheetAsList = new ArrayList<>();
private List rowAsList;
private final List sheetCommentAsList = new ArrayList<>();
private List rowCommentAsList;
private final Map propertyMap = new HashMap<>();
public void startSheet(String sheetName) {
propertyMap.put("sheetName", sheetName);
}
@Override
public void startRow(int rowNum) {
rowAsList = new ArrayList<>();
rowCommentAsList = new ArrayList<>();
}
@Override
public void endRow(int rowNum) {
sheetAsList.add(rowNum, rowAsList);
sheetCommentAsList.add(rowNum, rowCommentAsList);
}
@Override
public void cell(String cellReference, String formattedValue, XSSFComment comment) {
formattedValue = (formattedValue == null) ? "" : formattedValue;
rowAsList.add(formattedValue);
if (comment == null) {
rowCommentAsList.add("");
} else {
propertyMap.put("comment author at "+comment.getRow()+":"+cellReference, comment.getAuthor());
rowCommentAsList.add(comment.getString().toString().trim());
}
}
@Override
public void headerFooter(String text, boolean isHeader, String tagName) {
if (isHeader) {
propertyMap.put("header tag", tagName);
propertyMap.put("header text", text);
} else { // footer
propertyMap.put("header tag", tagName);
propertyMap.put("header text", text);
}
}
public List getSheetContentAsList(){
return sheetAsList;
}
public List getSheetCommentAsList(){
return sheetCommentAsList;
}
public Map getMapOfInfo(){
return propertyMap;
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With