In my application not getting proper row number or value .(Apache poi)
In this application,I am reading xlsx file by using apache-poi ,but I am not getting only row number , it is giving some other data also, please check my code and output.
This is my code:
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
// c => cell
System.out.println("The Value of Row is : "+attributes.getValue("r"));
if(name.equals("c")) {
// Print the cell reference
// Figure out if the value is an index in the SST
String cellType = attributes.getValue("t");
if(cellType != null && cellType.equals("s")) {
nextIsString = true;
} else {
nextIsString = false;
}
}
When I am printing the row number by using below line, I am getting below output.
System.out.println("The Value of Row is : "+attributes.getValue("r"));
output is :
The Value of Row is : null The Value of Row is : 1 The Value of Row is : A1 The Value of Row is : null The Value of Row is : B1 The Value of Row is : null The Value of Row is : C1 The Value of Row is : null The Value of Row is : D1
The Value of Row is : null
The Value of Row is : E1
The Value of Row is : null
The Value of Row is : F1
The Value of Row is : null
The Value of Row is : G1
The Value of Row is : null
The Value of Row is : H1
The Value of Row is : null
The Value of Row is : I1
The Value of Row is : null
The Value of Row is : J1
The Value of Row is : null
The Value of Row is : AMD1
The Value of Row is : AME1
The Value of Row is : AMF1
The Value of Row is : AMG1
The Value of Row is : AMH1
The Value of Row is : AMI1
The Value of Row is : AMJ1
The Value of Row is : 2
The Value of Row is : A2
The Value of Row is : null
The Value of Row is : B2
The Value of Row is : null
The Value of Row is : C2
The Value of Row is : null
The Value of Row is : D2
How can I get the row numbers only ? 1,2,3,4,5,6,..... like that?
and
How can I get total number of columns and number of Rows ?
As already stated in comments, this is not the default way reading a Excel sheet with apache poi. Please read https://poi.apache.org/spreadsheet/quick-guide.html. Especially https://poi.apache.org/spreadsheet/quick-guide.html#Iterator.
For your sax.ContentHandler code:
The XML of a Excel sheet looks like:
...
<sheetData>
<row r="1">
<c r="A1">
<v>1</v>
</c>
<c r="B1">
<v>2</v>
</c>
</row>
...
As you see, there are different elements, not only rows and the "r" attribute has different meanings within those elements. Within element "v" it is null.
So you must first check if you have a row element:
...
if(name.equals("row")) {
System.out.println("The Value of Row is : "+attributes.getValue("r"));
}
But, as stated above, I don't know if this is really the best way for you.
Edit:
As from you comment if you have up to a million rows, then using sax.ContentHandler code will really be a good approach. So to your concrete question, as i said:
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
// c => cell
if(name.equals("row")) {
System.out.println("The Value of Row is : "+attributes.getValue("r"));
}
...
That code you provided seems to be SAX parsing instead of Apache POI. One of the ways you can get the row number using POI (code is in Java 7):
InputStream excelFileToRead = new FileInputStream("path to path to excel/xlsx file");
try(XSSFWorkbook workbook = new XSSFWorkbook(excelFileToRead);){
XSSFSheet sheet = workbook.getSheetAt(0); // first sheet
Iterator<Row> rows = sheet.rowIterator(); // rows iterator
...
while(rows.hasNext()){
XSSFRow row = (XSSFRow) rows.next();
int rowNum = row.getRowNum(); // Get the row number
// Reading cell values
Iterator cells = row.cellIterator();
while (cells.hasNext()){
cell = (XSSF) cells.next();
if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING){
System.out.print(cell.getStringCellValue()+" ");
}
else if(cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC){
System.out.print(cell.getNumericCellValue()+" ");
}else{
//handle the rest etc.
}
}
...................
}
} catch (IOException e) {
e.printStackTrace();
}