如何读取使用POI的EXCELL文件的空白单元格 [英] How to read empty cells of an excell file using POI
问题描述
我曾尝试用POI来读取EXCELL文件，然后我想将数据放到一个的JTable 。
下面是EXCELL文件，
正如你可以看到，有两个空细胞是在上表中，有一次我读到上述数据代入一个的JTable 我得到了下面的结果，
在我的的JTable ，空单元格已经转移到一个错误的地方，我用下面的codeS得到这样的结果，请帮助我实现了正确的结果，
私人XLSContainer xLSContainer;
矢量cellVectorHolder;
私人INT noOfCells = 0; 公共XLSContainer readXLS（XLSFile xLSFile）{
cellVectorHolder =新的向量（）; 尝试{ 的FileInputStream的InputStream =新的FileInputStream（xLSFile.getFileName（））; POIFSFileSystem pOIFSFileSystem =新POIFSFileSystem（InputStream的）; HSSFWorkbook hSSFWorkbook =新HSSFWorkbook（pOIFSFileSystem）; HSSFSheet hSSFSheet = hSSFWorkbook.getSheetAt（0）; 迭代器rowIter = hSSFSheet.rowIterator（）;
而（rowIter.hasNext（））{
HSSFRow行=（HSSFRow）rowIter.next（）;
如果（row.getRowNum（）== 0）{
noOfCells = row.getLastCellNum（）;
}
迭代器cellIter = row.cellIterator（）; 矢量cellStoreVector =新的向量（）; 而（cellIter.hasNext（））{
HSSFCell hSSFCell =（HSSFCell）cellIter.next（）;
//System.out.println（hSSFCell.getCellNum（））;
cellStoreVector.addElement（hSSFCell）;
}
cellVectorHolder.addElement（cellStoreVector）; }
}赶上（例外五）{
e.printStackTrace（）;
}
feedXLSContainer（）;
返回xLSContainer;
} // readXLS 私人无效feedXLSContainer（）{ xLSContainer =新XLSContainer（）;
的for（int i = 0; I＆LT; cellVectorHolder.size（）;我++）{
矢量cellStoreVector =（矢量）cellVectorHolder.elementAt（ⅰ）;
矢量项目=新的向量（）;
对于（INT J = 0; J＆LT; cellStoreVector.size（）; J ++）{
HSSFCell细胞=（HSSFCell）cellStoreVector.elementAt（J）;
item.add（cell.toString（））;
}
如果（我== 0）{
xLSContainer.addHeader（项目）;
}其他{
xLSContainer.addRow（项目）;
} } }
我在上面所做的是将标题和数据行成单独的载体在一个名为 xLSContainer 类，然后把这些矢量成的JTable 。
下面是我如何经过谷歌搜索解决它： - ）
私人XLSContainer xLSContainer; 公共XLSContainer readXLS（XLSFile xLSFile）{
尝试{ WorkbookSettings WS =新WorkbookSettings（）;
ws.setLocale（新区域设置（恩，EN））;
工作簿簿= Workbook.getWorkbook（新文件（xLSFile.getFileName（）），WS）;
纸张S = workbook.getSheet（0）;
的System.out.println（表内容::+ s.getName（））;
readDataSheet（多个）;
workbook.close（）; }赶上（例外五）{
e.printStackTrace（）;
}
返回xLSContainer; } 私人无效readDataSheet（纸S）{
xLSContainer =新XLSContainer（）; INT noOfRows = s.getRows（）;
INT noOfCols = s.getColumns（）; 的for（int i = 0; I＆LT; noOfRows;我++）{
矢量项目=新的向量（）;
对于（INT J = 0; J＆LT; noOfCols; J ++）{ 如果（s.getCell（J，I）.getContents（）==）{
item.add（）; }其他{
item.add（s.getCell（J，I）.getContents（））; }
} 如果（我== 0）{
xLSContainer.addHeader（项目）;
}其他{
xLSContainer.addRow（项目）;
}
}
}
的迭代器返回你实际存在的文件中的单元格。如果你想复制自己的立场，几乎肯定不是你想要什么，而不是你要检查每个单元依次
您可能会希望code是这样的：
workbook.setMissingCellPolicy（MissingCellPolicy.RETURN_BLANK_AS_NULL）;
DataFormatter FMT =新DataFormatter（）;对（INT SN = 0; SN＆下; workbook.getNumberOfSheets（）; SN ++）{
苫布苫布= workbook.getSheetAt（SN）;
对（INT RN = sheet.getFirstRowNum（）; RN＆下; = sheet.getLastRowNum（）; RN ++）{
鳞次栉比= sheet.getRow（RN）;
如果（行== NULL）{
//有此行中没有数据，拉手根据需要
}其他{
//行RN有数据
对（INT CN = 0; CN＆下; row.getLastCellNum（）; CN ++）{
电池单元= row.getCell（CN）;
如果（细胞== NULL）{
//这个单元格是空白/空/未使用，根据需要处理
}其他{
字符串cellStr = fmt.formatCell（单元）;
//做一些与价值
}
}
}
}
}
这code将让你在回合每个单元，并且还将正确地格式化你的细胞（使数字的格式看起来像他们在Excel中做的）
I have tried to read an excell file using POI and then I wanted to put that data into a JTable.
Here is the excell file,
As you can see, there are two empty cells in the above table, once I read above data into a JTable I got following result,
In my JTable, empty cells has moved to a wrong place, I used following codes to get this result please assist me to achieve the correct result,
private XLSContainer xLSContainer;
Vector cellVectorHolder;
private int noOfCells=0;
public XLSContainer readXLS(XLSFile xLSFile) {
cellVectorHolder = new Vector();
try {
FileInputStream inputStream = new FileInputStream(xLSFile.getFileName());
POIFSFileSystem pOIFSFileSystem = new POIFSFileSystem(inputStream);
HSSFWorkbook hSSFWorkbook = new HSSFWorkbook(pOIFSFileSystem);
HSSFSheet hSSFSheet = hSSFWorkbook.getSheetAt(0);
Iterator rowIter = hSSFSheet.rowIterator();
while (rowIter.hasNext()) {
HSSFRow row = (HSSFRow) rowIter.next();
if(row.getRowNum()==0){
noOfCells = row.getLastCellNum();
}
Iterator cellIter = row.cellIterator();
Vector cellStoreVector = new Vector();
while (cellIter.hasNext()) {
HSSFCell hSSFCell = (HSSFCell) cellIter.next();
//System.out.println(hSSFCell.getCellNum());
cellStoreVector.addElement(hSSFCell);
}
cellVectorHolder.addElement(cellStoreVector);
}
} catch (Exception e) {
e.printStackTrace();
}
feedXLSContainer();
return xLSContainer;
}//readXLS
private void feedXLSContainer() {
xLSContainer = new XLSContainer();
for (int i = 0; i < cellVectorHolder.size(); i++) {
Vector cellStoreVector = (Vector) cellVectorHolder.elementAt(i);
Vector item = new Vector();
for (int j = 0; j < cellStoreVector.size(); j++) {
HSSFCell cell = (HSSFCell) cellStoreVector.elementAt(j);
item.add(cell.toString());
}
if (i == 0) {
xLSContainer.addHeader(item);
} else {
xLSContainer.addRow(item);
}
}
}
What I have done above is put headings and data rows into separate vectors in a class called xLSContainer and then put those vectors into a JTable.
Here is how I've solved it after more google searches :-)
private XLSContainer xLSContainer;
public XLSContainer readXLS(XLSFile xLSFile) {
try {
WorkbookSettings ws = new WorkbookSettings();
ws.setLocale(new Locale("en", "EN"));
Workbook workbook = Workbook.getWorkbook(new File(xLSFile.getFileName()), ws);
Sheet s = workbook.getSheet(0);
System.out.println("Sheet Content::" + s.getName());
readDataSheet(s);
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
return xLSContainer;
}
private void readDataSheet(Sheet s) {
xLSContainer = new XLSContainer();
int noOfRows = s.getRows();
int noOfCols = s.getColumns();
for (int i = 0; i < noOfRows; i++) {
Vector item = new Vector();
for (int j = 0; j < noOfCols; j++) {
if (s.getCell(j, i).getContents() == "") {
item.add("");
} else {
item.add(s.getCell(j, i).getContents());
}
}
if (i == 0) {
xLSContainer.addHeader(item);
}else{
xLSContainer.addRow(item);
}
}
}
The Iterators return you the cells the actually exist in the file. If you're trying to replicate their positions, that almost certainly isn't what you want, instead you'll want to check each cell in turn
You'll likely want code something like:
workbook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);
DataFormatter fmt = new DataFormatter();
for(int sn=0; sn<workbook.getNumberOfSheets(); sn++) {
Sheet sheet = workbook.getSheetAt(sn);
for (int rn=sheet.getFirstRowNum(); rn<=sheet.getLastRowNum(); rn++) {
Row row = sheet.getRow(rn);
if (row == null) {
// There is no data in this row, handle as needed
} else {
// Row "rn" has data
for (int cn=0; cn<row.getLastCellNum(); cn++) {
Cell cell = row.getCell(cn);
if (cell == null) {
// This cell is empty/blank/un-used, handle as needed
} else {
String cellStr = fmt.formatCell(cell);
// Do something with the value
}
}
}
}
}
This code will let you get at each cell in turn, and will also correctly format your cells (so that the numbers are formatted to look like they do in Excel)
这篇关于如何读取使用POI的EXCELL文件的空白单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
