Drawing objects & Charts(C#)

 
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 a worksheet and link it to a cell

FormControlShape comBoxShape1 = workBook.addFormControl(3.0, 3.0, 4.1, 4.1, FormControlShape.CombBox);
comBoxShape1.CellRange = "A1:A3";
comBoxShape1.CellLink = "B4";

 
top
Adding CheckBox

add comments to the cells in a worksheet

FormControlShape checkBoxShape = workBook.addFormControl(3.0, 1.0, 4.1, 2.1, FormControlShape.CheckBox);
checkBoxShape.CellRange = "A1:A3";
checkBoxShape.CellLink = "B2";
checkBoxShape.Text = "checkbox1";

 
top
Adding TextBox

create textBox in the worksheet

 AutoShape textBoxShape = workBook.addAutoShape(3.0, 5.0, 5.0, 7.0, AutoShape.TextBox);
textBoxShape.Text = "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.ChartType = 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.Title = "My Chart";

//set plot area's color to darkgray
ChartFormat chartFormat = chart.PlotFormat;
chartFormat.setSolid();
chartFormat.ForeColor = Color.DarkGray.ToArgb();
chart.PlotFormat = chartFormat;

//set series 0's color to blue
ChartFormat seriesformat = chart.getSeriesFormat(0);
seriesformat.setSolid();
seriesformat.ForeColor = Color.Blue.ToArgb();
chart.setSeriesFormat(0, seriesformat);

//set series 1's color to red
seriesformat = chart.getSeriesFormat(1);
seriesformat.setSolid();
seriesformat.ForeColor = Color.Red.ToArgb();
chart.setSeriesFormat(1, seriesformat);

//set chart title's font property
ChartFormat titleformat = chart.TitleFormat;
titleformat.FontSize = 14 * 20;
titleformat.FontUnderline = true;
chart.TitleFormat = titleformat;

workBook.write("./Chart.xls");
}
catch (System.Exception ex)
{
Console.Error.WriteLine(ex);
}

 
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.Sheet = 0;//select the new created sheet
workBook.setSheetName(0, "ChartSheet");
ChartShape chart = workBook.addChartSheet(0);//add chart
chart.ChartType = 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.Title = "My Chart";
//set chart type to 3D
chart.set3Dimensional(true);

workbook.writeXLSX("Chartsheet.xlsx");