i'm trying to read data from excel and plot graph using java.
I keep getting the below error:
org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
Exception in thread "main" java.lang.NoClassDefFoundError: org/jfree/util/PublicCloneable
My code:
import java.io.*;
import java.util.*;
import org.jfree.data.*;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.ChartFactory;
import org.jfree.chart.ChartUtilities;
import org.jfree.chart.plot.PlotOrientation;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.jfree.data.category.DefaultCategoryDataset;
public class ReadExcel{
public static void main(String[]args){
short a=0;
short b=1;
int i=0;
ArrayList<Integer> list1=new ArrayList<Integer>();
ArrayList<Integer> list2=new ArrayList<Integer>();
int x=0, y=0;
String filename ="C:\\Chart.xlsx";
if(filename != null && !filename.equals("")){
try{
FileInputStream fs =new FileInputStream(filename);
HSSFWorkbook wb = new HSSFWorkbook(fs);
for(int k = 0; k < wb.getNumberOfSheets(); k++){
int j=i+1;
HSSFSheet sheet = wb.getSheetAt(k);
int rows = sheet.getPhysicalNumberOfRows();
for(int r = 1; r < rows; r++){
HSSFRow row = sheet.getRow(r);
int cells = row.getPhysicalNumberOfCells();
HSSFCell cell1 = row.getCell(a);
x =(int) cell1.getNumericCellValue();
HSSFCell cell2 = row.getCell(b);
y =(int) cell2.getNumericCellValue();
list1.add(new Integer(x));
list2.add(new Integer(y));
}
i++;
}
}catch(Exception e){
System.out.println(e);
}
}
DefaultCategoryDataset dataset = new DefaultCategoryDataset();
for(int j=0;j<list1.size();j++){
dataset.setValue((double)list2.get(j), "Marks", list1.get(j).toString());
}
JFreeChart chart = ChartFactory.createBarChart("BarChart using JFreeChart","ID", "Marks", dataset,
PlotOrientation.VERTICAL, false,true, false);
try {
ChartUtilities.saveChartAsJPEG(new File("C:\\chart.jpg"), chart,400, 300);
} catch (IOException e) {
System.out.println("Problem in creating chart.");
}
}
}
Sample excel:
Response Time Transaction Name Runid
25 Home 1
56 Login 2
23 Order 3
i tried to use the XSSF Apache POI but no luck.
I also wanted to save the image of the chart somewhere locally.
NoClassDefFoundError
means that the JVM is unable to find a class that you're using in your code. In a general way, it means you're missing a dependency in your classpath. Try adding this dependency to your project: https://mvnrepository.com/artifact/org.jfree/jcommon