Excel DataBar in VB.NET
The following program shows , how to do the frequenly used format in Excel page. Here we enter the data of a Mark List and format the page. First we MERGE excel cell and create the heading , then the students name and totals make as BOLD and create a border for the whole marklist part. Finally we select the range we want to add DataBar and select the color
After you run the source code you will get an Excel file its look like in the following picture.
Imports Excel = Microsoft.Office.Interop.Excel Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Dim xlApp As Excel.Application Dim xlWorkBook As Excel.Workbook Dim xlWorkSheet As Excel.Worksheet Dim misValue As Object = System.Reflection.Missing.Value Dim chartRange As Excel.Range xlApp = New Excel.ApplicationClass xlWorkBook = xlApp.Workbooks.Add(misValue) xlWorkSheet = xlWorkBook.Sheets("sheet1") 'add data xlWorkSheet.Cells(4, 2) = "" xlWorkSheet.Cells(4, 3) = "Student1" xlWorkSheet.Cells(4, 4) = "Student2" xlWorkSheet.Cells(4, 5) = "Student3" xlWorkSheet.Cells(5, 2) = "Term1" xlWorkSheet.Cells(5, 3) = "10" xlWorkSheet.Cells(5, 4) = "65" xlWorkSheet.Cells(5, 5) = "45" xlWorkSheet.Cells(6, 2) = "Term2" xlWorkSheet.Cells(6, 3) = "78" xlWorkSheet.Cells(6, 4) = "72" xlWorkSheet.Cells(6, 5) = "60" xlWorkSheet.Cells(7, 2) = "Term3" xlWorkSheet.Cells(7, 3) = "82" xlWorkSheet.Cells(7, 4) = "80" xlWorkSheet.Cells(7, 5) = "65" xlWorkSheet.Cells(8, 2) = "Term4" xlWorkSheet.Cells(8, 3) = "75" xlWorkSheet.Cells(8, 4) = "82" xlWorkSheet.Cells(8, 5) = "98" xlWorkSheet.Cells(9, 2) = "Total" xlWorkSheet.Cells(9, 3) = "315" xlWorkSheet.Cells(9, 4) = "299" xlWorkSheet.Cells(9, 5) = "238" chartRange = xlWorkSheet.Range("b2", "e3") chartRange.Merge() chartRange.FormulaR1C1 = "MARK LIST" chartRange.HorizontalAlignment = 3 chartRange.VerticalAlignment = 3 chartRange = xlWorkSheet.Range("b4", "e4") chartRange.Font.Bold = True chartRange = xlWorkSheet.Range("b9", "e9") chartRange.Font.Bold = True chartRange = xlWorkSheet.Range("b2", "e9") chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, _ Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex. _ ColorIndexAutomatic,Excel.XlColorIndex.xlColorIndexAutomatic) chartRange = xlWorkSheet.Range("c5", "e9") chartRange.FormatConditions.AddDatabar() chartRange.FormatConditions(chartRange.FormatConditions.Count) _ .ShowValue = True chartRange.FormatConditions(chartRange.FormatConditions.Count). _ SetFirstPriority() chartRange.FormatConditions(1).BarColor.color = 8061142 chartRange.FormatConditions(1).BarColor.TintAndShade = 0 xlWorkSheet.SaveAs("C:\vbexcel.xlsx") xlWorkBook.Close() xlApp.Quit() releaseObject(xlApp) releaseObject(xlWorkBook) releaseObject(xlWorkSheet) MsgBox("File created !") End Sub Private Sub releaseObject(ByVal obj As Object) Try System.Runtime.InteropServices.Marshal.ReleaseComObject(obj) obj = Nothing Catch ex As Exception obj = Nothing Finally GC.Collect() End Try End Sub End Class
When you execute this program you will get a MarkList with databar
- How to create an Excel file in VB.NET
- How to open or read an existing Excel file in VB.NET
- How to read an Excel file using VB.Net
- Add new worksheet in Excel file - VB.Net
- Delete worksheet from an excel file - VB.Net
- How to Format Excel Page in VB.NET
- How to insert a Picture in Excel through programing on VB.NET
- How to insert a background Picture in Excel through VB.NET
- How to create a Chart in Excel in VB.NET
- How to export a Chart in Excel as Picture file from VB.NET
- How to Excel Chart in VB.NET Picture Box
- How to Excel Data Validation Input Message
- Read and Import Excel File into DataSet or DataTable in vb.net
- How to insert cell data in an Excel file using OLEDB
- How to update cell data in an Excel file using OLEDB
- How to export from database to excel
- How to export from DataGridView to excel