Drawing objects & Charts(VB.Net)
- 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, ".\\template\\neza.jpg")
- 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
Dim comBoxShape1 As 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
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"
- 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 !!!"
- 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
- 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")