java + excel data +graph

java + excel data +graph

i am doin' a project in which i need to take up data from excel sheets and work upon them mathematically and finally draw a graph with the facility of drawing a trendline(in the graph)...also i need to be able to select points in the graph and draw the graph again between those points.
View Answers

June 3, 2010 at 11:26 AM

Hi Friend,

Try the following code:

import java.io.*;
import java.util.*;
import java.awt.*;
import org.jfree.ui.*;
import org.jfree.chart.*;
import org.jfree.chart.plot.*;
import org.jfree.data.xy.*;
import java.text.*;
import org.jfree.chart.axis.NumberAxis;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;

public class TrendLine extends ApplicationFrame {
public TrendLine(final String title) {
super(title);
final XYDataset dataset = createDataset();
final JFreeChart chart = createChart(dataset);
final ChartPanel chartPanel = new ChartPanel(chart);
chartPanel.setPreferredSize(new java.awt.Dimension(500, 270));
setContentPane(chartPanel);
}
private XYDataset createDataset() {
short a=0;
short b=1;
String st1="",st2="";
String filename ="C:/data.xls";
if (filename != null && !filename.equals("")) {
try{
FileInputStream fs =new FileInputStream(filename);
HSSFWorkbook wb = new HSSFWorkbook(fs);
for (int k = 0; k < wb.getNumberOfSheets(); k++){
HSSFSheet sheet = wb.getSheetAt(k);
int rows = sheet.getPhysicalNumberOfRows();
for (int r = 1; r < rows; r++){
HSSFRow row = sheet.getRow(r);
if (row != null) {
int cells = row.getPhysicalNumberOfCells();
HSSFCell cell1 = row.getCell(a);
if (cell1 != null){
String value = null;
switch (cell1.getCellType()){
case HSSFCell.CELL_TYPE_FORMULA :
value = "FORMULA ";
break;
case HSSFCell.CELL_TYPE_NUMERIC :
value = ""+cell1.getNumericCellValue();
break;
case HSSFCell.CELL_TYPE_STRING :
value = cell1.getStringCellValue();
break;
}
st1+=value+" ";

}
HSSFCell cell2 = row.getCell(b);
if (cell2 != null){
String value = null;
switch (cell2.getCellType()){
case HSSFCell.CELL_TYPE_FORMULA :
value = "FORMULA ";
break;
case HSSFCell.CELL_TYPE_NUMERIC :
value = ""+cell2.getNumericCellValue();
break;
case HSSFCell.CELL_TYPE_STRING :
value = cell2.getStringCellValue();
break;
}
st2+=value+" ";
}
}
}
}
}
catch (Exception ex){ }
}
String arr1[]=st1.split(" ");
String arr2[]=st2.split(" ");
final XYSeries series1 = new XYSeries("Graph");
for(int i=0;i<arr1.length;i++){
String s1=arr1[i];
double n1=Double.parseDouble(s1);
String s2=arr2[i];
double n2=Double.parseDouble(s2);
series1.add(n1, n2);
}

DecimalFormat df = new DecimalFormat("####0.00");
double weight[]=new double[7];
double trend[]=new double[7];
trend[0]=40;
for(int i=0;i<arr1.length;i++){
weight[i]=Double.parseDouble(arr2[i]);
}
double value1=weight[0]-trend[0];
double newvalue1=value1/10;
double roundedValue1=Double.parseDouble(df.format(newvalue1));
trend[0]=trend[0]+roundedValue1;
System.out.println(trend[0]);

June 3, 2010 at 11:39 AM

continue........

double value2=weight[1]-trend[0];
double newvalue2=value2/10;
double roundedValue2=Double.parseDouble(df.format(newvalue2));
trend[1]=trend[0]+roundedValue2;
System.out.println(trend[1]);

double value3=weight[2]-trend[1];
double newvalue3=value3/10;
double roundedValue3=Double.parseDouble(df.format(newvalue3));
trend[2]=trend[1]+roundedValue3;
System.out.println(trend[2]);

double value4=weight[3]-trend[2];
double newvalue4=value4/10;
double roundedValue4=Double.parseDouble(df.format(newvalue4));
trend[3]=trend[2]+roundedValue4;
System.out.println(trend[3]);

double value5=weight[4]-trend[3];
double newvalue5=value5/10;
double roundedValue5=Double.parseDouble(df.format(newvalue5));
trend[4]=trend[3]+roundedValue5;
System.out.println(trend[4]);

double value6=weight[5]-trend[4];
double newvalue6=value6/10;
double roundedValue6=Double.parseDouble(df.format(newvalue6));
trend[5]=trend[4]+roundedValue6;
System.out.println(trend[5]);

double value7=weight[6]-trend[5];
double newvalue7=value7/10;
double roundedValue7=Double.parseDouble(df.format(newvalue7));
trend[6]=trend[5]+roundedValue7;
System.out.println(trend[6]);

final XYSeries series2 = new XYSeries("TrendLine");
for(int i=0;i<arr1.length;i++){
String s1=arr1[i];
double n1=Double.parseDouble(s1);
series2.add(n1, trend[i]);
}
final XYSeriesCollection dataset = new XYSeriesCollection();
dataset.addSeries(series1);
dataset.addSeries(series2);


return dataset;
}
private JFreeChart createChart(final XYDataset dataset) {
final JFreeChart chart = ChartFactory.createXYLineChart(
"",
"Number of Days",
"Weight",
dataset,
PlotOrientation.VERTICAL,
true,
true,
false
);
chart.setBackgroundPaint(Color.white);

return chart;
}
public static void main(String[]args){

final TrendLine demo = new TrendLine("Multi Line Chart");
demo.setSize(300,300);
RefineryUtilities.centerFrameOnScreen(demo);
demo.setVisible(true);
}
}

For the above code, we have created an excel file with two fields 'number of days and weight'.

Hope that it will be helpful for you.
Thanks

June 24, 2010 at 2:28 PM

thanx a lot for ur input ..i hav been able to complete my project ...









Related Tutorials/Questions & Answers:
java + excel data +graph - JDBC
java + excel data +graph  i am doin' a project in which i need to take up data from excel sheets and work upon them mathematically and finally draw a graph with the facility of drawing a trendline(in the graph)...also i need
How to draw to graph in Ms Excel from the data which is sent from an java application....?
How to draw to graph in Ms Excel from the data which is sent from an java application....?  How to draw to graph in Ms Excel from the data which is sent from an java application
Advertisements
reading data from excel file and plotting graph
reading data from excel file and plotting graph  I am doing a project... the data in excel file, i have to plot graphs based on CELL ID selected. please help... that reads an excel file using POI api and using the data of excel file
Create Bar Graph from reading excel sheet in Java
Create Bar Graph from reading excel sheet in Java  I'm New to Java and I have to create a java program where I have to read data from multiple excel... in the different excel sheets (data can change and based on that graph should also get
Fetching data from excel file on other website and display in interactive graph form
Fetching data from excel file on other website and display in interactive graph... tell me how will it be possible? Do I need java script? I have a website which have zip files and excel files and they get updated everyday, so how can i
How to export chart(graph) generated by jsp into a excel?
How to export chart(graph) generated by jsp into a excel?  How to export chart(graph) generated by jsp into a excel? I have a jsp page which generates charts . Now I need those charts to be exported into an excel.please help
Read Excel file and generate bar graph
Read Excel file and generate bar graph In this tutorial, you will learn how to read an excel file and generate bar graph. Here is an example that reads an excel file using POI api and store the data into array list. We have created
JFreeChart Tutorial
to visualize your business data in Java based applications. This library is very popular...; Read Excel file and generate bar graph - In this tutorial, you will learn how to read an excel file and generate bar graph..  
Open Source Charting and Reporting Tools in Java
; JgraphT: JGraphT is a free Java graph library that provides mathematical... Open Source Charting and Reporting Tools in Java... of them are given below : JfreeChart: This is a free java library for creating
importing excel file and drawing multiple graphs from one excel file
importing excel file and drawing multiple graphs from one excel file  thanks a lot sir for replying with code for importing excel file... time from one excel file using different columns..and instead of passing column
R Tutorial
, excel file, csv files, database data etc. Community support Who should... Reading CSV files Importing data from Excel files Importing data..., generation of visual graphs and scientific research. In the field of data science
R Programming Training Course
files Importing data from Excel files Importing data from SAS... Create and run simple program R Data Structure All the programming language offers their data structures for easy programming, in this session we

Ads