This is the step by step approach of how to create a Datasource using Excel file (CSV Format).
1) For example sake, I have taken a dataset from a question asked in stackoverflow. Question
2) Save the excel file as CSV, as shown in the image.
3) Now Open the Jaspersoft Studio and On the Repository panel Right Click on “Data Adapter” and click on create a new Datasource.
4) This will open a Wizard, where select CSV File as shown below.
5) Name the Datasouce and Browse the CSV File that you saved. Then click on Get Columns Button. All the Column Names present in teh excel sheet will appear in the box. Once that happens Click Test.
6) Here Note that the “Use Query Executer Mode” checkbox is unchecked.
7) Now Create a new report and select the Datasource as the new CSV Datasource you created.
8) Click Next and Select all the columns into the right side of the window.
9) Drag and drop all the fields in to the Detail section
9) Preview the report and it should display all the data in your excel.
10) Now we will see what small changes you need to make if you have clicked the “Use Query Executer Mode” checkbox.
11) Edit Datasouce and click the checkbox
12) Now when we refresh the report, it displays as Document Empty as follows.
13) To overcome this, we need to go to the Query editor and select the Language as CSV
14) This will now run the report as expected.
15) Now go to the query editor and change the class of two columns Hour and second from String to Integer( Any data from CSV File will be by default String).
16) The Preview will now display the right result. as follows
17) But if I change the Class to Number as requested by the questioner, it will throw an error “java.lang.String cannot be cast to java.lang.Number”, Hence the best solution is to use java.lang.Integer instead of Number.
18) My further dive into the issue made me realize just by clicking the check box ” Use custom number Pattern” and selecting a pattern will make the report execute by changing the string class to Number class.
Enjoy Jaspersoft Studio!!!!!!