(7) How to manipulate Excel files with Apache POI-----How to draw a discrete graph with Apache POI

Sometimes, we need the data in Excel, through a picture, visualize the display. So what should be done at this time? Now Jiehuaxianfo, take an example from Apache poi to give you a demonstration of how the POI API is drawing. The following is a final. Then we will explain the function and meaning of each piece of code separately.

Code as follows,

Import Java.io.fileoutputstream;import Org.apache.poi.ss.usermodel.*;import Org.apache.poi.ss.util.*;import Org.apache.poi.ss.usermodel.charts.*;import org.apache.poi.xssf.usermodel.xssfworkbook;/** * Illustrates how to Create a simple scatter chart.        * * @author Roman Kashitsyn */public class Scatterchart {public static void main (string[] args) throws Exception {        Workbook wb = new Xssfworkbook ();        Sheet Sheet = Wb.createsheet ("Sheet 1");        Final int num_of_rows = 3;        Final int num_of_columns = 10; Create a row and put some cells in it.        Rows are 0 based.        Row row;        Cell cell;            for (int rowIndex = 0, rowIndex < num_of_rows; rowindex++) {row = Sheet.createrow ((short) rowIndex); for (int colindex = 0, colindex < num_of_columns; colindex++) {cell = Row.createcell ((short) ColI                NDEX);            Cell.setcellvalue (Colindex * (RowIndex + 1)); }} Drawing Drawing = sheEt.createdrawingpatriarch ();        Clientanchor anchor = drawing.createanchor (0, 0, 0, 0, 0, 5, 10, 15);        Chart chart = Drawing.createchart (anchor);        ChartLegend legend = Chart.getorcreatelegend ();        Legend.setposition (Legendposition.top_right);        Scatterchartdata data = Chart.getchartdatafactory (). Createscatterchartdata ();        Valueaxis Bottomaxis = Chart.getchartaxisfactory (). Createvalueaxis (Axisposition.bottom);        Valueaxis Leftaxis = Chart.getchartaxisfactory (). Createvalueaxis (Axisposition.left);        Leftaxis.setcrosses (Axiscrosses.auto_zero); chartdatasource<number> xs = datasources.fromnumericcellrange (sheet, new cellrangeaddress (0, 0, 0, NUM_OF_        COLUMNS-1)); chartdatasource<number> ys1 = datasources.fromnumericcellrange (sheet, new cellrangeaddress (1, 1, 0, NUM_OF_        COLUMNS-1)); chartdatasource<number> ys2 = datasources.fromnumericcellrange (sheet, new Cellrangeaddress (2, 2, 0, NUM_OF_        COLUMNS-1)); DAta.addserie (xs, ys1);        Data.addserie (xs, YS2);        Chart.plot (data, Bottomaxis, Leftaxis);        Write the output to a file FileOutputStream fileout = new FileOutputStream ("ooxml-scatter-chart.xlsx");        Wb.write (fileout);    Fileout.close (); }}

The following decomposition:

1. The following code creates a new workbook and a work form object

   Workbook wb = new Xssfworkbook ();        Sheet Sheet = Wb.createsheet ("Sheet 1");

2. The following code is generated to initialize the data, the total data has 3 rows 10 columns.

Final int num_of_rows = 3; final int num_of_columns = ten;        Create a row and put some cells in it. Rows are 0 based.        Row row;        Cell cell;        for (int rowIndex = 0, rowIndex < num_of_rows; rowindex++) {            row = Sheet.createrow ((short) rowIndex);            for (int colindex = 0, colindex < num_of_columns; colindex++) {                cell = Row.createcell ((short) colindex);                Cell.setcellvalue (Colindex * (RowIndex + 1));            }        }

3. The following code sets the area of paint: Starting at line 5th, ending with 15 lines, occupying 10 columns in total

Drawing Drawing = Sheet.createdrawingpatriarch (); Clientanchor anchor = drawing.createanchor (0, 0, 0, 0, 0, 5, 10, 15);

4. Create a coordinate system for a discrete graph

Chart chart = Drawing.createchart (anchor); ChartLegend legend = Chart.getorcreatelegend (); Legend.setposition (Legendposition.top_right); Scatterchartdata data = Chart.getchartdatafactory (). Createscatterchartdata (); Valueaxis Bottomaxis = Chart.getchartaxisfactory (). Createvalueaxis (Axisposition.bottom); Valueaxis Leftaxis = Chart.getchartaxisfactory (). Createvalueaxis (Axisposition.left); Leftaxis.setcrosses (Axiscrosses.auto_zero);

5. Populating data on discrete graphs

chartdatasource<number> xs = datasources.fromnumericcellrange (sheet, new cellrangeaddress (0, 0, 0, NUM_OF_ COLUMNS-1)); chartdatasource<number> ys1 = datasources.fromnumericcellrange (sheet, new cellrangeaddress (1, 1, 0, NUM_OF_ COLUMNS-1)); chartdatasource<number> ys2 = datasources.fromnumericcellrange (sheet, new Cellrangeaddress (2, 2, 0, NUM_OF_ COLUMNS-1)); Data.addserie (xs, ys1); Data.addserie (xs, YS2);

wherein, the following method defines

Datasources.fromnumericcellrange (sheet, new cellrangeaddress (0, 0, 0, num_of_columns-1));

As follows

As can be seen from the above, the key method of filling the data is that

<pre name= "code" class= "Java" >new cellrangeaddress (0, 0, 0, num_of_columns-1)

So how does this approach be defined?

Public cellrangeaddress (int firstrow, int lastrow, int firstcol, int lastcol)

As can be seen from the above, it allows us to set the data from that line, the end of the line, that column began, that column is over.

In the above code 5 sentences, respectively, the 1th row of 1 to 10 columns as the benchmark, and then the 2nd row of 1 to 10 to make a comparison, draw the Curve series 1

Make a comparison of rows 1 to 10 of line 1th, then compare the 1 to 10 columns of line 3rd, and draw the Curve series 2

6. Start Drawing

Chart.plot (data, Bottomaxis, Leftaxis);

7. Save as an Excel file

FileOutputStream fileout = new FileOutputStream ("ooxml-scatter-chart.xlsx"); Wb.write (fileout); Fileout.close ();

(7) How to manipulate Excel files with Apache POI-----How to draw a discrete graph with Apache POI