Sadly it is not comma seperated like a .csv file.
But if you just want to grap the table content out of an .xlsx file, without all the fancy ApachePOI stuff. You can use my simple parser:
如何:
- copy all code into some class.
- call
fromXLSX
, where the first argument is the .xlsx file, and the second argument is the name of the excel sheet (leaving it null will select the first sheet by default)
- it returns an
String[#ROWS][#COLUMN]
with the contents of the table.
Disapper: 只测试了扼杀和编号值。 确保其与公式制定者或日期合作
public static String[][] fromXLSX(File xlsx, String sheet) {
FileInputStream fis;
try {
fis = new FileInputStream(xlsx);
ZipInputStream zis = new ZipInputStream(fis);
ZipEntry ze = zis.getNextEntry();
LinkedList<CellData> cellDatas = null;
String[] sharedStrings = null;
while(ze != null) {
String fileName = ze.getName();
if ((sheet == null && fileName.contains("worksheets")) || (sheet != null && fileName.endsWith(sheet + ".xml"))) {
cellDatas = cellDataFromXLSXextractedXML(readZipEntryToString(zis));
} else if (fileName.endsWith("sharedStrings.xml")) {
sharedStrings = sharedStringsFromXML(readZipEntryToString(zis));
}
zis.closeEntry();
ze = zis.getNextEntry();
}
zis.closeEntry();
zis.close();
fis.close();
int minX = Integer.MAX_VALUE, minY = Integer.MAX_VALUE, maxX = 0, maxY = 0;
for(CellData c : cellDatas) {
int x = c.getCol();
int y = c.getRow();
if (x < minX)
minX = x;
if (x > maxX)
maxX = x;
if (y < minY)
minY = y;
if (y > maxY)
maxY = y;
//replace String values
if(c.string) {
c.value = sharedStrings[c.getValueInt()];
}
}
int w = maxX - minX + 1;
int h = maxY - minY + 1;
String[][] values = new String[h][w];
for(CellData c : cellDatas) {
values[c.getRow() - minY][c.getCol() - minX] = c.value;
}
return values;
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
private static String readZipEntryToString(ZipInputStream zis) throws IOException {
byte[] buffer = new byte[1024];
int len;
StringBuilder xml = new StringBuilder();
while ((len = zis.read(buffer)) > 0) {
String str = new String(buffer, 0, len);
xml.append(str);
}
return xml.toString();
}
private static String[] sharedStringsFromXML(String xml) {
LinkedList<String> res = new LinkedList<>();
int index = 0;
while((index = xml.indexOf("<t>", index)) != -1) {
int end = xml.indexOf("</t>", index);
res.add(xml.substring(index + 3, end));
index = end;
}
return (String[]) res.toArray(new String[res.size()]);
}
private static LinkedList<CellData> cellDataFromXLSXextractedXML(String sheetXml) {
String sheetData = sheetXml.substring(sheetXml.indexOf("<sheetData>") + "<sheetData>".length(), sheetXml.indexOf("</sheetData>"));
LinkedList<CellData> cellData = new LinkedList<>();
int index = 0;
while((index = sheetData.indexOf("<c r="", index)) != -1) {
String cellID = sheetData.substring(index + 6, sheetData.indexOf( " , index + 6));
int endOfTag = sheetData.indexOf( > , index);
int typeString = sheetData.indexOf("t="s"", index);
boolean isStr = typeString != -1 && typeString < endOfTag;
int valOpen = sheetData.indexOf("<v>", endOfTag);
int valClose = sheetData.indexOf("</v>", valOpen);
String value = sheetData.substring(valOpen + 3, valClose);
cellData.add(new CellData(cellID, value, isStr));
index = endOfTag;
}
return cellData;
}
private static class CellData {
public String cellID;
public String value;
public boolean string;
public CellData(String cellID, String value, boolean string) {
this.cellID = cellID;
this.value = value;
this.string = string;
}
private int getCol() {
String rev_id = new StringBuilder(cellID).reverse().toString();
int pos = 0;
int sum = 0;
while(Character.isDigit(rev_id.charAt(pos))) {
pos++;
}
int offs = pos;
while(pos < rev_id.length()) {
sum += (((int)rev_id.charAt(pos)) - ((int) A )) * (powInt(26, pos - offs));
pos++;
}
return sum;
}
private int getRow() {
int pos = 0;
while(!Character.isDigit(cellID.charAt(pos))) {
pos++;
}
return Integer.parseInt(cellID.substring(pos));
}
public int getValueInt() {
return Integer.parseInt(value);
}
private int powInt(int base, int to) {
int res = 1;
for(int i = 0; i < to; i++) {
res *= base;
}
return res;
}
@Override
public String toString() {
return "Cell: [" + getCol() + ", " + getRow() + "]: " + value + " (str: " +string + ")";
}
}