Drawing objects & Charts(VB.Net)

 
top
Adding Pictures

 add pictures into the worksheet.

'add pictures with picture file path or picture stream
workBook.addPicture(1, 1, 4, 6, ".\\template\\neza.jpg")

 
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

Dim comBoxShape1 As 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

Dim checkBoxShape As 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

Dim TextboxShape As 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

Dim workBook As 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")
Dim col, row As Integer

col = 1
While col <= 5
row = 1
While row <= 5
workBook.setFormula(row, col, "RAND()")
row += 1
End While
col += 1
End While

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

Dim left, top, right, bottom As Integer
left = 1
top = 7
right = 13
bottom = 31

'create chart with it's location
Dim chart As 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
Dim chartFormat As ChartFormat
chartFormat = chart.PlotFormat
chartFormat.setSolid()
chartFormat.ForeColor = Color.DarkGray.ToArgb()
chart.PlotFormat = chartFormat

'set series 0's color to blue
Dim seriesformat As 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
Dim titleformat As ChartFormat
titleformat = chart.TitleFormat
titleformat.FontSize = 14 * 20
titleformat.FontUnderline = True
chart.TitleFormat = titleformat

workBook.write("./Chart.xls")

Catch ex As Exception
Console.Error.WriteLine(ex)
End Try

 
top
Create Chart Sheet

Create chart sheet

Dim workBook As 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")
Dim col, row As Integer

col = 1
While col <= 5
row = 1
While row <= 5
WorkBook.setFormula(row, col, "RAND()")
row += 1
End While
col += 1
End While

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")
Dim chart As 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")