Drawing objects & Charts(Java)
- Adding Pictures.
- Adding Comments.
- Adding DropDown.
- Adding CheckBox.
- Adding TextBox.
- Create Chart.
- Stacked Column Chart.
- Chart with two Y-Axis.
- Create Chart Sheet.
- Export Chart As An Image.
- Adding Pictures
-
add pictures into the worksheet.
//add pictures with picture file path or picture stream
workBook.addPicture(1, 1, 4, 6, "demo.png");
- Adding Comments
-
add comments to the cells in a worksheet
//add a comment to B2
workBook.addComment(1, 1, "comment text here!", "author name here!");
- Adding DropDown
-
add dropdown in the worksheet and link it to a cell
FormControlShape comBoxShape1 = workBook.addFormControl(3.0, 3.0, 4.1, 4.1, FormControlShape.CombBox);
comBoxShape1.setCellRange("A1:A3");
comBoxShape1.setCellLink("B4");
- Adding CheckBox
-
add checkbox in the worksheet and link it to a cell
FormControlShape checkBoxShape = workBook.addFormControl(3.0, 1.0, 4.1, 2.1, FormControlShape.CheckBox);
checkBoxShape.setCellLink("B2");
checkBoxShape.setText("checkbox1");
- Adding TextBox
-
create textBox in the worksheet
AutoShape textBoxShape = workBook.addAutoShape(3.0, 5.0, 5.0, 7.0, AutoShape.TextBox);
textBoxShape.setText("textBox !!!");
- Create Chart
-
create chart and link it to sheet range
WorkBook workBook = new WorkBook();
try
{
//set data
workBook.setText(0, 1, "Jan");
workBook.setText(0, 2, "Feb");
workBook.setText(0, 3, "Mar");
workBook.setText(0, 4, "Apr");
workBook.setText(0, 5, "Jun");
workBook.setText(1, 0, "Comfrey");
workBook.setText(2, 0, "Bananas");
workBook.setText(3, 0, "Papaya");
workBook.setText(4, 0, "Mango");
workBook.setText(5, 0, "Lilikoi");
for (int col = 1; col <= 5; col++)
for (int row = 1; row <= 5; row++)
workBook.setFormula(row, col, "RAND()");
workBook.setText(6, 0, "Total");
workBook.setFormula(6, 1, "SUM(B2:B6)");
workBook.setSelection("B7:F7");
//auto fill the range with the first cell's formula or data
workBook.editCopyRight();
int left = 1;
int top = 7;
int right = 13;
int bottom = 31;
//create chart with it's location
ChartShape chart = workBook.addChart(left, top, right, bottom);
chart.setChartType(ChartShape.Column);
//link data source, link each series to columns(true to rows).
chart.setLinkRange("Sheet1!$a$1:$F$6", false);
//set axis title
chart.setAxisTitle(ChartShape.XAxis, 0, "X-axis data");
chart.setAxisTitle(ChartShape.YAxis, 0, "Y-axis data");
//set series name
chart.setSeriesName(0, "My Series number 1");
chart.setSeriesName(1, "My Series number 2");
chart.setSeriesName(2, "My Series number 3");
chart.setSeriesName(3, "My Series number 4");
chart.setSeriesName(4, "My Series number 5");
chart.setTitle("My Chart");
//set plot area's color to darkgray
ChartFormat chartFormat = chart.getPlotFormat();
chartFormat.setSolid();
chartFormat.setForeColor(Color.red.getRGB());
chart.setPlotFormat(chartFormat);
//set series 0's color to blue
ChartFormat seriesformat = chart.getSeriesFormat(0);
seriesformat.setSolid();
seriesformat.setForeColor(Color.BLUE.getRGB());
chart.setSeriesFormat(0, seriesformat);
//set series 1's color to red
seriesformat = chart.getSeriesFormat(1);
seriesformat.setSolid();
seriesformat.setForeColor(Color.RED.getRGB());
chart.setSeriesFormat(1, seriesformat);
//set chart title's font property
ChartFormat titleformat = chart.getTitleFormat();
titleformat.setFontSize(14*20);
titleformat.setFontUnderline(true);
titleformat.setTextRotation(90);
chart.setTitleFormat(titleformat);
workBook.writeXLSX("Chart.xlsx");
}
catch (Exception ex)
{
ex.printStackTrace();
}
- Stacked Column Chart
-
create stacked column chart
//stacked column chart
chart.setChartType(ChartShape.Column);
chart.setPlotStacked(true);//set plot serires stacked
chart.setBarGapRatio(-100);
- Create Chart Sheet
-
Create chart sheet
WorkBook workbook = new WorkBook();
//set data
workbook.setText(0, 1, "Jan");
workbook.setText(0, 2, "Feb");
workbook.setText(0, 3, "Mar");
workbook.setText(0, 4, "Apr");
workbook.setText(0, 5, "Jun");
workbook.setText(1, 0, "Comfrey");
workbook.setText(2, 0, "Bananas");
workbook.setText(3, 0, "Papaya");
workbook.setText(4, 0, "Mango");
workbook.setText(5, 0, "Lilikoi");
for (int col = 1; col <= 5; col++)
for (int row = 1; row <= 5; row++)
workbook.setFormula(row, col, "RAND()");
workbook.setText(6, 0, "Total");
workbook.setFormula(6, 1, "SUM(B2:B6)");
workbook.setSelection("B7:F7");
//auto fill the range with the first cell's formula or data
workbook.editCopyRight();
workbook.insertSheets(0, 1);//insert sheet from left
workbook.setSheet(0);//select the new created sheet
workbook.setSheetName(0, "ChartSheet");
ChartShape chart = workbook.addChartSheet(0);//add chart
chart.setChartType(ChartShape.Column);
chart.addSeries();
chart.setSeriesName(0, "My Series number 1");
chart.setSeriesYValueFormula(0, "Sheet1!$B$2:$B$6");
//set axis title
chart.setAxisTitle(ChartShape.XAxis, 0, "X-axis data");
chart.setAxisTitle(ChartShape.YAxis, 0, "Y-axis data");
//set series name
chart.setSeriesName(0, "My Series number 1");
chart.setTitle("My Chart");
//set chart type to 3D
chart.set3Dimensional(true);
workbook.writeXLSX("Chartsheet.xlsx");
- Export Chart As An Image
-
save an Excel chart to PNG Image
//create or get chart object
ChartShape chart = workBook.addChart(left,top,right,bottom);
//...
//export the chart to image
java.io.FileOutputStream out = new java.io.FileOutputStream("chart.png");
chart.writeChartAsPNG(workbook, out);
out.close();