Drawing objects & Charts(Java)

 
top
Adding Pictures

 add pictures into the worksheet.

//add pictures with picture file path or picture stream
workBook.addPicture(1, 1, 4, 6, "demo.png");

 
top
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!");

 
top
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");

 
top
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");

 
top
Adding TextBox

create textBox in the worksheet

AutoShape textBoxShape = workBook.addAutoShape(3.0, 5.0, 5.0, 7.0, AutoShape.TextBox);
textBoxShape.setText("textBox !!!");

 
top
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();
}

 
top
Stacked Column Chart

create stacked column chart

//stacked column chart
chart.setChartType(ChartShape.Column);
chart.setPlotStacked(true);//set plot serires stacked
chart.setBarGapRatio(-100);

 
top
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");

 
top
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();