VB.NET - Excel - How to modify cell/row height

also check http://howtodomssqlcsharpexcelaccess.blogspot.ca/2012/08/vbnet-how-to-creae-excel-file-simple.html   

    Dim fileTest As String = "C:\Temp\ExcelTest\test.xlsx"
        If File.Exists(fileTest) Then
            File.Delete(fileTest)
        End If

        Dim oExcel As Object
        oExcel = CreateObject("Excel.Application")
        Dim oBook As Excel.Workbook
        Dim oSheet As Excel.Worksheet

        oBook = oExcel.Workbooks.Add
        oSheet = oExcel.Worksheets(1)

        oSheet.Name = "no name"
        oSheet.Range("A1").Value = "FirstName"
        oSheet.Range("B1").Value = "Year"
        oSheet.Range("C1").Value = "Salary"

        oSheet.Range("A2").Value = "Frank"
        oSheet.Range("B2").Value = "2012"
        oSheet.Range("C2").Value = "20000"

        oSheet.Range("A3").Value = "John"
        oSheet.Range("B3").Value = "2012"
        oSheet.Range("C3").Value = "30000"

        oSheet.Range("A4").Value = "Bob"
        oSheet.Range("B4").Value = "2012"
        oSheet.Range("C4").Value = "40000"

        oSheet.Range("A5").Value = "Elvis"
        oSheet.Range("B5").Value = "2012"
        oSheet.Range("C5").Value = "60000"

        osheet.Range("A1").RowHeight = 100
        osheet.Range("A1").EntireRow.WrapText = True
        osheet.Range("A1").EntireRow.AutoFit()

        oBook.SaveAs(fileTest)
        oBook.Close()
        oBook = Nothing
        oExcel.Quit()
        oExcel = Nothing

YouTube - Create simple Excel file with VB.NET

VB.NET - Excel - How to freeze panes

also check http://howtodomssqlcsharpexcelaccess.blogspot.ca/2012/08/vbnet-how-to-creae-excel-file-simple.html   

    Dim fileTest As String = "C:\Temp\ExcelTest\test.xlsx"
        If File.Exists(fileTest) Then
            File.Delete(fileTest)
        End If

        Dim oExcel As Object
        oExcel = CreateObject("Excel.Application")
        Dim oBook As Excel.Workbook
        Dim oSheet As Excel.Worksheet

        oBook = oExcel.Workbooks.Add
        oSheet = oExcel.Worksheets(1)

        oSheet.Name = "no name"
        oSheet.Range("A1").Value = "FirstName"
        oSheet.Range("B1").Value = "Year"
        oSheet.Range("C1").Value = "Salary"

        oSheet.Range("A2").Value = "Frank"
        oSheet.Range("B2").Value = "2012"
        oSheet.Range("C2").Value = "20000"

        oSheet.Range("A3").Value = "John"
        oSheet.Range("B3").Value = "2012"
        oSheet.Range("C3").Value = "30000"

        oSheet.Range("A4").Value = "Bob"
        oSheet.Range("B4").Value = "2012"
        oSheet.Range("C4").Value = "40000"

        oSheet.Range("A5").Value = "Elvis"
        oSheet.Range("B5").Value = "2012"
        oSheet.Range("C5").Value = "60000"

        oSheet.Range("A2", "A2").Select()
        oExcel.ActiveWindow.FreezePanes = True

        oBook.SaveAs(fileTest)
        oBook.Close()
        oBook = Nothing
        oExcel.Quit()
        oExcel = Nothing

Same example on YouTube

How to fix error Event message: Viewstate verification failed. Reason: The viewstate supplied failed integrity check.

How to fix error
 Event message: Viewstate verification failed. Reason: The viewstate supplied failed integrity check.
Event ID 1316

 - configure machine key as per this article
http://msdn.microsoft.com/en-us/library/ff649308.aspx

the following site helps to generate machine key
http://aspnetresources.com/tools/machineKey

VB.NET - Excel - How to make part of the string inside cell bold

osheet.Range("A1").Characters(Start:=0, Length:=10).Font.FontStyle = "Bold"
will produce This is my text inside the cell

VB.NET - Create Excel file with Pivot Table using VB.Net

In this example I create Excel file with 2 sheets
- one with data in table format
- another with pivot table

This code can be copied and pasted, don't forget to add Excel reference -


Of course, Excel must be installed on the computer that will run this code.


    Dim fileTest As String = "C:\Temp\ExcelTest\testPivot.xlsx"
        If File.Exists(fileTest) Then
            File.Delete(fileTest) ' oh, file is still open
        End If

        Dim oExcel As Object
        oExcel = CreateObject("Excel.Application")
        Dim oBook As Excel.Workbook
        Dim oSheet As Excel.Worksheet

        oBook = oExcel.Workbooks.Add
        oSheet = oExcel.Worksheets(1)

        oSheet.Name = "Report"
        oSheet.Range("A1").Value = "First Name"
        oSheet.Range("B1").Value = "Year"
        oSheet.Range("C1").Value = "Salary"

        oSheet.Range("A2").Value = "Frank"
        oSheet.Range("B2").Value = "2012"
        oSheet.Range("C2").Value = "30000"

        oSheet.Range("A3").Value = "Frank"
        oSheet.Range("B3").Value = "2011"
        oSheet.Range("C3").Value = "25000"

        oSheet.Range("A4").Value = "Ann"
        oSheet.Range("B4").Value = "2011"
        oSheet.Range("C4").Value = "55000"

        oSheet.Range("A5").Value = "Ann"
        oSheet.Range("B5").Value = "2012"
        oSheet.Range("C5").Value = "35000"

        oSheet.Range("A6").Value = "Ann"
        oSheet.Range("B6").Value = "2010"
        oSheet.Range("C6").Value = "35000"

        ' OK, at this point we have Excel file with 1 sheet with data
        ' Now let's create pivot table

        ' first get range of cells from sheet 1 that will be used by pivot
        Dim xlRange As Excel.Range = CType(oSheet, Excel.Worksheet).Range("A1:C6")

        ' create second sheet
        If oExcel.Application.Sheets.Count() < 2 Then
            oSheet = CType(oBook.Worksheets.Add(), Excel.Worksheet)
        Else
            oSheet = oExcel.Worksheets(2)
        End If
        oSheet.Name = "Pivot Table"

        ' specify first cell for pivot table on the second sheet
        Dim xlRange2 As Excel.Range = CType(oSheet, Excel.Worksheet).Range("B3")

        ' Create pivot cache and table
        Dim ptCache As Excel.PivotCache = oBook.PivotCaches.Add(Excel.XlPivotTableSourceType.xlDatabase, xlRange)
        Dim ptTable As Excel.PivotTable = oSheet.PivotTables.Add(PivotCache:=ptCache, TableDestination:=xlRange2, TableName:="Summary")

        ' create Pivot Field, note that pivot field name is the same as column name in sheet 1
        Dim ptField As Excel.PivotField = ptTable.PivotFields("Salary")
        With ptField
            .Orientation = Excel.XlPivotFieldOrientation.xlDataField
            .Function = Excel.XlConsolidationFunction.xlSum
            .Name = " Salary" ' by default name will be something like SumOfSalary, change it here to Salary, note space in front of it -
            ' this field name cannot be the same as therefore that space
            ' also it cannot be empty

            '' add another field
            'ptField = ptTable.PivotFields("Year")
            'With ptField
            '    .Orientation = Excel.XlPivotFieldOrientation.xlDataField
            '    .Function = Excel.XlConsolidationFunction.xlMax
            '    .Name = " Year" ' this is how you create another field, in my example I don't need it so let's comment it out
            'End With

            ' add column
            ptField = ptTable.PivotFields("First Name")
            With ptField
                .Orientation = Excel.XlPivotFieldOrientation.xlColumnField
                .Name = " "
            End With

        End With
        ' add grouping - again I don't need this in my example, this is just to show how to do it
        'oSheet.Range("C5").Group(1, 20, 40)

        oBook.SaveAs(fileTest)
        oBook.Close()
        oBook = Nothing
        oExcel.Quit()
        oExcel = Nothing

same example on Youtube - part 1


same example on Youtube - part 2