English 中文(简体)
POI
原标题:Parsing Excel file without Apache POI

我知道,我们可以使用阿帕奇帕伊分公司复制Excel文档并获取数据。 但是,我听到了一种奇怪的事情,即可以以类似的方式通过Excel档案(Like刚刚读到档案中,将每个栏目价值与“comma”分离器分开)。 当我们把Excel混为一谈时,我们不得不使用表格作为限定者。 是否可能? 如果是的话,那么帕帕奇就有了这样一个复杂的框架。 我感到迷惑。 谁能帮助我?

最佳回答

CSV是一种案文形式,因此可以使用划界人加以分类。 旧Excel是一种双轨和专有形式,因此需要切除。 新的Excel格式是运用XML,但也应理解该文件的结构,然后将其变成一个简单的阅读单元。 因此,对你的问题的回答是没有的,你需要使用阿帕奇帕伊,而且没有错。

As a side note, on the path to become a good developer you will need to learn to do a bit of your own research before looking for help. Get your hands dirty is the best way to learn things.

问题回答

你可能混淆了你们听到的话,或者说你的人被混淆了。

Excel文档的某些部分(can)作为CSV文档储存(有些称),因为表格数据结构在CSV档案格式内很合适。 然而,如果你用特别安全套格式节省费用,那么你就在每间囚室里拿到简单明了的文字,就失去了所有格式信息、任何图表、多个工作表格等等。

本土的XLS外表形式是阿帕奇帕西民族解放组织与某些囚室合作,因此可以处理任何东西,而不仅仅是限制性的平原。 CSV文档有其用途,但肯定不是直接替代正常Excel文档。

i tried to read/write excel file without using any external JAR like POI or any other. I am able to write file as xls format. Here is my Code

FileWriter fwriter = new FileWriter(file,true);
writer = new BufferedWriter(fwriter);
writer.newLine();
writer.write("a"    + "	");
writer.write("b"    + "	");
writer.write("c"    + "	");
writer.write("d"    + "	");
writer.write("e"    + "	");
writer.write("f"    + "	");

Reading File here is my code for reading

if(file != null) {
            BufferedReader reader = null;
            try {
                reader = new BufferedReader(new FileReader(file));
                String line;
                while((line = reader.readLine()) != null) {
                    String[] component = line.split("\t");
                }
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                if(reader != null) {
                    try {
                        reader.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }
InputStream is = new FileInputStream(new File(filepath));
        StreamingReader reader=null;
        try {
            reader = StreamingReader.builder()
                    .rowCacheSize(100)     
                    .bufferSize(4096)     
                    .sheetIndex(0)        
                    .read(is);
        } catch (Exception e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }finally{
            is.close();
        }
        //pass here to reader and itrate it 
          for (Row row : reader) {
            if (row.getRowNum()!=0){
                for (Cell cell : row) {
              // write ur logic to store ur value 
                }

            }
        }

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 + ")";
    }
}




相关问题
Spring Properties File

Hi have this j2ee web application developed using spring framework. I have a problem with rendering mnessages in nihongo characters from the properties file. I tried converting the file to ascii using ...

Logging a global ID in multiple components

I have a system which contains multiple applications connected together using JMS and Spring Integration. Messages get sent along a chain of applications. [App A] -> [App B] -> [App C] We set a ...

Java Library Size

If I m given two Java Libraries in Jar format, 1 having no bells and whistles, and the other having lots of them that will mostly go unused.... my question is: How will the larger, mostly unused ...

How to get the Array Class for a given Class in Java?

I have a Class variable that holds a certain type and I need to get a variable that holds the corresponding array class. The best I could come up with is this: Class arrayOfFooClass = java.lang....

SQLite , Derby vs file system

I m working on a Java desktop application that reads and writes from/to different files. I think a better solution would be to replace the file system by a SQLite database. How hard is it to migrate ...

热门标签