Click here to Skip to main content
15,889,876 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Please help me in formatting headertext for properties I want to apply are background color and font bold where I am passing datatable for creating an excel sheet using vb.net with open xml sdk

[EDIT - OPs code from comment]

#Region "Excel Download"
    '''  
    '''  Generate an excel file with data in GridView control 
    '''  
    ''' <param name="datatable">DataTable object</param> 
    ''' <param name="filepath">The Path of exported excel file</param> 
    Public Sub ExportToExcel(ByVal datatable As DataTable, ByVal filepath As String, ByVal sheetname1 As String)
        ' Initialize an instance of  SpreadSheet Document  
        Try
            sheetname = sheetname1
            Using _spreadsheetDocument As SpreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook)
                CreateExcelFile(_spreadsheetDocument, datatable)
            End Using
        Catch ex As Exception
            MsgBox("SuffixCode export to excel error, " & ex.Message)
        End Try
    End Sub


    '''  
    '''  Create SpreadSheet Document and Fill datas 
    '''  
    ''' <param name="spreadsheetdoc">SpreadSheet Document</param> 
    ''' <param name="table">DataTable Object</param> 
    Private Sub CreateExcelFile(ByRef spreadsheetdoc As SpreadsheetDocument, ByVal table As DataTable)
        ' Initialize an instance of WorkbookPart 
        Try


            Dim workBookPart As WorkbookPart = spreadsheetdoc.AddWorkbookPart()


            ' Create WorkBook  
            CreateWorkBookPart(workBookPart)


            ' Add WorkSheetPart into WorkBook 
            Dim worksheetPart1 As WorksheetPart = workBookPart.AddNewPart(Of WorksheetPart)("rId1")
            CreateWorkSheetPart(worksheetPart1, table)


            ' Add SharedStringTable Part into WorkBook 
            Dim sharedStringTablePart As SharedStringTablePart = workBookPart.AddNewPart(Of SharedStringTablePart)("rId2")
            CreateSharedStringTablePart(sharedStringTablePart, table)


            ' Add WorkbookStyles Part into Workbook 
            Dim workbookStylesPart As WorkbookStylesPart = workBookPart.AddNewPart(Of WorkbookStylesPart)("rId3")
            CreateWorkBookStylesPart(workbookStylesPart)


            ' Save workbook 
            workBookPart.Workbook.Save()
        Catch ex As Exception
            'MsgBox("Error in calling CreateExcelFile foe 
        End Try
    End Sub


    '''  
    ''' Create an Workbook instance and add its children 
    '''  
    ''' <param name="workbookPart">WorkbookPart Object</param> 
    Private Sub CreateWorkBookPart(ByRef workbookPart As WorkbookPart)
        Dim workbook As New Workbook()
        Dim sheets As New Sheets()


        ' Initilize an instance of Sheet Object 
        Dim sheet1 As New Sheet() With { _
         .Name = sheetname, _
         .SheetId = Convert.ToUInt32(1), _
         .Id = "rId1" _
        }


        ' Add the sheet into sheets collection 
        sheets.Append(sheet1)


        Dim calculationProperties1 As New CalculationProperties() With { _
          .CalculationId = Convert.ToUInt32(111222) _
        }


        ' Add elements into workbook 
        workbook.Append(sheets)
        workbook.Append(calculationProperties1)
        workbookPart.Workbook = workbook
    End Sub


    '''  
    '''  Generates content of worksheetPart 
    '''  
    ''' <param name="worksheetPart">WorksheetPart Object</param> 
    ''' <param name="table">DataTable Object</param> 
    Private Sub CreateWorkSheetPart(ByRef worksheetPart As WorksheetPart, ByVal table As DataTable)
        ' Initialize worksheet and set the properties 
        Try

            Dim worksheet1 As New Worksheet() With { _
              .MCAttributes = New MarkupCompatibilityAttributes() With { _
              .Ignorable = "x14ac" _
             } _
             }

            worksheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships")
            worksheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006")
Posted
Updated 11-Sep-13 7:52am
v2
Comments
CHill60 22-Aug-13 20:23pm    
Post what you have done so far
Pavankumar s k 10-Sep-13 23:35pm    
#Region "Excel Download"
'''
''' Generate an excel file with data in GridView control
'''

''' <param name="datatable">DataTable object</param>
''' <param name="filepath">The Path of exported excel file</param>
Public Sub ExportToExcel(ByVal datatable As DataTable, ByVal filepath As String, ByVal sheetname1 As String)
' Initialize an instance of SpreadSheet Document
Try
sheetname = sheetname1
Using _spreadsheetDocument As SpreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook)
CreateExcelFile(_spreadsheetDocument, datatable)
End Using
Catch ex As Exception
MsgBox("SuffixCode export to excel error, " & ex.Message)
End Try
End Sub


'''
''' Create SpreadSheet Document and Fill datas
'''

''' <param name="spreadsheetdoc">SpreadSheet Document</param>
''' <param name="table">DataTable Object</param>
Private Sub CreateExcelFile(ByRef spreadsheetdoc As SpreadsheetDocument, ByVal table As DataTable)
' Initialize an instance of WorkbookPart
Try


Dim workBookPart As WorkbookPart = spreadsheetdoc.AddWorkbookPart()


' Create WorkBook
CreateWorkBookPart(workBookPart)


' Add WorkSheetPart into WorkBook
Dim worksheetPart1 As WorksheetPart = workBookPart.AddNewPart(Of WorksheetPart)("rId1")
CreateWorkSheetPart(worksheetPart1, table)


' Add SharedStringTable Part into WorkBook
Dim sharedStringTablePart As SharedStringTablePart = workBookPart.AddNewPart(Of SharedStringTablePart)("rId2")
CreateSharedStringTablePart(sharedStringTablePart, table)


' Add WorkbookStyles Part into Workbook
Dim workbookStylesPart As WorkbookStylesPart = workBookPart.AddNewPart(Of WorkbookStylesPart)("rId3")
CreateWorkBookStylesPart(workbookStylesPart)


' Save workbook
workBookPart.Workbook.Save()
Catch ex As Exception
'MsgBox("Error in calling CreateExcelFile foe
End Try
End Sub


'''
''' Create an Workbook instance and add its children
'''

''' <param name="workbookPart">WorkbookPart Object</param>
Private Sub CreateWorkBookPart(ByRef workbookPart As WorkbookPart)
Dim workbook As New Workbook()
Dim sheets As New Sheets()


' Initilize an instance of Sheet Object
Dim sheet1 As New Sheet() With { _
.Name = sheetname, _
.SheetId = Convert.ToUInt32(1), _
.Id = "rId1" _
}


' Add the sheet into sheets collection
sheets.Append(sheet1)


Dim calculationProperties1 As New CalculationProperties() With { _
.CalculationId = Convert.ToUInt32(111222) _
}


' Add elements into workbook
workbook.Append(sheets)
workbook.Append(calculationProperties1)
workbookPart.Workbook = workbook
End Sub


'''
''' Generates content of worksheetPart
'''

''' <param name="worksheetPart">WorksheetPart Object</param>
''' <param name="table">DataTable Object</param>
Private Sub CreateWorkSheetPart(ByRef worksheetPart As WorksheetPart, ByVal table As DataTable)
' Initialize worksheet and set the properties
Try

Dim worksheet1 As New Worksheet() With { _
.MCAttributes = New MarkupCompatibilityAttributes() With { _
.Ignorable = "x14ac" _
} _
}

worksheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships")
worksheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006")

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900