如何用java拆分Excel文件？ [英] how to split Excel file using java?
问题描述
我Excel工作表有20万rows.i要拆分Excel文件为每个50000条记录。
我使用的Apache POI API读取和写入Excel的file.Is它可能的，如果行的数量达到上定义记录size.please帮助我获取该问题的解决方案分割文件。
I have Excel sheet with 200000 rows.i want to splits the excel file for each 50000 Records. i am using Apache POI API To read and write Excel file.Is it possible to split file if number of row reaches on defined Record size.please help me to get the solution for that problem.
code：
public String[][] getSheetData(int SheetIndex)
{
int noOfColumns = 0;XSSFRow row = null;
XSSFCell cell = null;
int i=0;int noOfRows=0;
int j=0;
String[][] data=null; XSSFSheet sheet=null;
try {
loadFile(); //load give Excel
if(validateIndex(SheetIndex))
{
sheet = workbook.getSheetAt(SheetIndex);
noOfColumns = getNumberOfColumns(SheetIndex);
noOfRows =getNumberOfRows(SheetIndex)+1;
data = new String[noOfRows][noOfColumns];
Iterator rowIter = sheet.rowIterator();
while(rowIter.hasNext())
{
row = (XSSFRow) rowIter.next();
Iterator cellIter = row.cellIterator();
j=0;
while(cellIter.hasNext())
{
cell = (XSSFCell) cellIter.next();
if(cell.getCellType() == cell.CELL_TYPE_STRING)
{
data[i][j] = cell.getStringCellValue();
}
else if(cell.getCellType() == cell.CELL_TYPE_NUMERIC)
{
data[i][j] = Double.toString(cell.getNumericCellValue());
}
j++;
}
i++;
} // outer while
}
else throw new InvalidSheetIndexException("Invalid sheet index.");
} catch (Exception ex) {
logger.error(ex);}
return data;
}
发生异常：
Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
at org.apache.xmlbeans.impl.store.Locale$ScrubBuffer.<init>(Locale.java:1885)
at org.apache.xmlbeans.impl.store.Locale.getScrubBuffer(Locale.java:1904)
at org.apache.xmlbeans.impl.store.Xobj.getValueAsString(Xobj.java:1205)
at org.apache.xmlbeans.impl.store.Xobj.fetch_text(Xobj.java:1796)
at org.apache.xmlbeans.impl.values.XmlObjectBase.get_wscanon_text(XmlObjectBase.java:1332)
at org.apache.xmlbeans.impl.values.XmlObjectBase.check_dated(XmlObjectBase.java:1269)
at org.apache.xmlbeans.impl.values.JavaLongHolder.longValue(JavaLongHolder.java:53)
at org.apache.xmlbeans.impl.values.XmlObjectBase.getLongValue(XmlObjectBase.java:1502)
at org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTRowImpl.getR(Unknown Source)
at org.apache.poi.xssf.usermodel.XSSFRow.getRowNum(XSSFRow.java:321)
at org.apache.poi.xssf.usermodel.XSSFSheet.initRows(XSSFSheet.java:180)
at org.apache.poi.xssf.usermodel.XSSFSheet.read(XSSFSheet.java:147)
at org.apache.poi.xssf.usermodel.XSSFSheet.onDocumentRead(XSSFSheet.java:134)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.onDocumentRead(XSSFWorkbook.java:235)
at org.apache.poi.POIXMLDocument.load(POIXMLDocument.java:190)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:189)
at com.arosys.excelReading.ReadXLSX.loadFile(ReadXLSX.java:49)
at com.arosys.excelReading.ReadXLSX.getNumberOfSheet(ReadXLSX.java:121)
at com.arosys.excelReading.ReadXLSX.main(ReadXLSX.java:324)
Java Result: 1
感谢
推荐答案
如果您无法阅读docuemnt与POI但该表很简单，我建议你用Excel打开它，将它保存为CSV文件，然后分开这文件以片段。这可以通过shell脚本/批处理文件甚至完成。
If you cannot read this docuemnt with POI but the table is simple I'd suggest you to open it with Excel, save it as CSV file and then separate this file to fragments. This can be even done using shell script/batch file.
这建议是有效的，如果你有一个巨大的文件，并有分裂它。然而，如果你正在实施应该接收这样的Excel文件并分开它寻找其他解决方案的服务。
This recommendation is valid if you have one huge file and have to split it. If however you are implementing a service that should receive such excel files and separate it look for other solution.
这篇关于如何用java拆分Excel文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
