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,

[Java]View PlainCopy 

2. Import Java.io.FileOutputStream;
4. Import org.apache.poi.ss.usermodel.*;
6. Import org.apache.poi.ss.util.*;
8. Import org.apache.poi.ss.usermodel.charts.*;
10. Import Org.apache.poi.xssf.usermodel.XSSFWorkbook;
14. /**
16. * Illustrates how to create a simple scatter chart.
18. *
20. * @author Roman Kashitsyn
22. */
24. Public class Scatterchart {
28. public static void Main (string[] args) throws Exception {
30. Workbook WB = new Xssfworkbook ();
32. Sheet Sheet = Wb.createsheet ("Sheet 1");
34. final int num_of_rows = 3;
36. final int num_of_columns = 10;
40. //Create a row and put some cells in it.  Rows are 0 based.
42. Row row;
44. Cell cell;
46. For (int rowIndex = 0; rowIndex < num_of_rows; rowindex++) {
48. row = Sheet.createrow ((short) rowIndex);
50. For (int colindex = 0; colindex < num_of_columns; colindex++) {
52. Cell = Row.createcell ((short) colindex);
54. Cell.setcellvalue (Colindex * (RowIndex + 1));
56. }
58. }
62. Drawing Drawing = Sheet.createdrawingpatriarch ();
64. Clientanchor anchor = Drawing.createanchor (0, 0, 0, 0, 0, 5, Ten, 15);
68. Chart chart = Drawing.createchart (anchor);
70. ChartLegend legend = Chart.getorcreatelegend ();
72. Legend.setposition (Legendposition.top_right);
76. Scatterchartdata data = Chart.getchartdatafactory (). Createscatterchartdata ();
80. Valueaxis Bottomaxis = Chart.getchartaxisfactory (). Createvalueaxis (Axisposition.bottom);
82. Valueaxis Leftaxis = Chart.getchartaxisfactory (). Createvalueaxis (Axisposition.left);
84. Leftaxis.setcrosses (Axiscrosses.auto_zero);
88. chartdatasource<number> xs = datasources.fromnumericcellrange (sheet, new cellrangeaddress (0, 0,  0, Num_of_columns- 1));
90. chartdatasource<number> ys1 = datasources.fromnumericcellrange (sheet, new cellrangeaddress (1, 1,  0, Num_of_columns- 1));
92. chartdatasource<number> ys2 = datasources.fromnumericcellrange (sheet, new Cellrangeaddress (2, 2,  0, Num_of_columns- 1));
98. Data.addserie (xs, ys1);
100. Data.addserie (xs, YS2);
104. Chart.plot (data, Bottomaxis, Leftaxis);
108. //Write the output to a file
110. FileOutputStream fileout = new FileOutputStream ("ooxml-scatter-chart.xlsx");
112. Wb.write (fileout);
114. Fileout.close ();
116. }
118. }

The following decomposition:

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

[Java]View PlainCopy 

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

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

[Java]View PlainCopy  

2. Final int num_of_rows = 3;
4. Final int num_of_columns = 10;
8. //Create a row and put some cells in it.  Rows are 0 based.
10. Row row;
12. Cell cell;
14. For (int rowIndex = 0; rowIndex < num_of_rows; rowindex++) {
16. row = Sheet.createrow ((short) rowIndex);
18. For (int colindex = 0; colindex < num_of_columns; colindex++) {
20. Cell = Row.createcell ((short) colindex);
22. Cell.setcellvalue (Colindex * (RowIndex + 1));
24. }
26. }

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

[Java]View PlainCopy 

1. Drawing Drawing = Sheet.createdrawingpatriarch ();
2. Clientanchor anchor = Drawing.createanchor (0, 0, 0, 0, 0, 5, Ten, 15);

4. Create a coordinate system for a discrete graph

[Java]View PlainCopy 

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

5. Populating data on discrete graphs

[Java]View PlainCopy 

2. chartdatasource<number> xs = datasources.fromnumericcellrange (sheet, new cellrangeaddress (0, 0,  0, Num_of_columns- 1));
4. chartdatasource<number> ys1 = datasources.fromnumericcellrange (sheet, new cellrangeaddress (1, 1,  0, Num_of_columns- 1));
6. chartdatasource<number> ys2 = datasources.fromnumericcellrange (sheet, new Cellrangeaddress (2, 2,  0, Num_of_columns- 1));
10. Data.addserie (xs, ys1);
12. Data.addserie (xs, YS2);

wherein, the following method defines

[Java]View PlainCopy 

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

As follows

[Java]View PlainCopy  

1. Public static chartdatasource<number> Fromnumericcellrange (Sheet Sheet, cellrangeaddress cellrangeaddress)

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

[Java]View PlainCopy 

1. <pre name="code" class="java" >new Cellrangeaddress (0, 0, 0, Num_of_columns- 1) /c8>

So how does this approach be defined?

[Java]View PlainCopy 

1. 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

[Java]View PlainCopy  

1. Chart.plot (data, Bottomaxis, Leftaxis);

7. Save as an Excel file

[Java]View PlainCopy 

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

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