Aspose cells for java unable to extract data properly from xlsx file

Aspose.Cells Product Family
1

Hello Aspose team,
I am having an issue with parsing a “.xlsx” file using Aspose cells Java library. I am using jdk 1.8, aspose-cells-19.2.jar.
Its a simple xlsx file that has 4 columns. The logic is not able to extract all data from the excel file, since it can recognize only 3 columns.

I have pasted my source code below. Also I have attached the sample excel file used.

public class ParseExcel {
public static void main(String[] args) throws Exception {
ParseExcel xlsObj = new ParseExcel () ;
try {
com.aspose.cells.License license = new com.aspose.cells.License();
String dirLicense = “C:\shash_other\trg2\aspose\sample-code\sample-files\” ;
license.setLicense(new java.io.FileInputStream(dirLicense +“Aspose.Total.Java.lic”));

		System.out.println("   +++++++++++++++++++++++++++++++++++++++++++++++++++++++    ");
		Worksheet worksheet1= null ;
		worksheet1=xlsObj.getworksheets( args[0] ).get( 0) ;
		xlsObj.extractExcelData (worksheet1 );
	} catch (IOException ioec) {
		System.out.printf("%s /n", ioec.getMessage());
	}
}

public WorksheetCollection getworksheets (String fileName) throws Exception {

	String dataDir = "C:\\shash_other\\trg2\\aspose\\sample-code\\" ;
	FileInputStream fInstream = new FileInputStream( dataDir  + fileName) ;
	Workbook workbook = null;

	workbook = new Workbook( fInstream) ;

	return workbook.getWorksheets() ;
}	

public void extractExcelData (Worksheet worksheet ) {
	List <List<String >> lstRows = new ArrayList () ;
	int rowCount  , columnCount ;
	com.aspose.cells.Cells cells = ((com.aspose.cells.Worksheet) worksheet).getCells();
	RowCollection rows= cells.getRows() ; 
	ColumnCollection columns= cells.getColumns() ;
	columnCount = (cells.getMaxDataColumn() > columns.getCount() )? cells.getMaxDataColumn() :columns.getCount() ;
	System.out.println("columncount: " + columnCount + ";"+ columns.getCount()+ ";" + cells.getMaxDataColumn() +
			"; "+ cells.getMaxColumn()) ;
	
	rows.forEach( rowData -> {
		for (int k = 0; k < columnCount ; ++k) { 
			Cell cellDat = ((Row)rowData).getCellOrNull(k) ;
			String 	strbTemp ="";

			if (cellDat != null) {
				if (cellDat.getType()== CellValueType.IS_DATE_TIME)
					strbTemp = cellDat.getDateTimeValue().toString() ;
				else if (cellDat.getType()== CellValueType.IS_NUMERIC)
					strbTemp =  ""+ cellDat.getFloatValue() ; 
				else //string 
					strbTemp=  cellDat.getValue().toString() ;
				System.out.println("data: " + strbTemp + "||");
			}
		}
	});
}

}

I would appreciate your help on this.

Regards,
Shashi.
basic doc extract.zip (7.1 KB)

2

@shashimn,

Thanks for the sample code segment and template file.

I checked your sample code a bit and found your code is not right. Please use Cells.getMaxDataColumn attribute which returns the farthest column index (0 based). In your case, it will return 3 (since your last data column is D column), so you got to to add “1” to get it actual column’s count (4). See the sample code segment for your reference:
e.g
Sample code:

..........
List <List<String >> lstRows = new ArrayList () ;
		int rowCount  , columnCount ;
		com.aspose.cells.Cells cells = ((com.aspose.cells.Worksheet) worksheet).getCells();
		RowCollection rows= cells.getRows() ; 
		ColumnCollection columns= cells.getColumns() ;
		int mCol = cells.getMaxDataColumn() +1;
		columnCount = (mCol > columns.getCount() )? mCol :columns.getCount();
		System.out.println("columncount: " + columnCount ) ;
............

Hope, this helps a bit.