Drawing objects & Charts(C#)
- Adding Pictures.
- Adding Comments.
- Adding DropDown.
- Adding CheckBox.
- Adding TextBox.
- Create Chart.
- Create Chart Sheet.
- 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 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";
- 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";
- Adding TextBox
-
create textBox in the worksheet
AutoShape textBoxShape = workBook.addAutoShape(3.0, 5.0, 5.0, 7.0, AutoShape.TextBox);
textBoxShape.Text = "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.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);
}
- 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");