Click here to Skip to main content
Click here to Skip to main content
Add your own
alternative version

Creating basic Excel workbook with Open XML

, 5 May 2012
This article describes how to create a basic Excel workbook using Open XML.
ExcelOpenXMLBasics_CSharp.zip
CSharp
ExcelOpenXMLBasics
ExcelOpenXMLBasics.suo
Properties
Settings.settings
ExcelOpenXMLBasics_VB.zip
VB
ExcelOpenXMLBasics
ExcelOpenXMLBasics.suo
ExcelOpenXMLBasics.suo
ExcelOpenXMLBasics.vbproj.user
My Project
MyExtensions
Settings.settings
Option Explicit On
Public Class MainWindow

    Public Sub New()

        ' This call is required by the designer.
        InitializeComponent()

#If EN_US_CULTURE Then
        System.Threading.Thread.CurrentThread.CurrentUICulture = System.Threading.Thread.CurrentThread.CurrentCulture
        System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")
#End If

    End Sub

    Public Sub btnCreateBasicWorkbook_Click(sender As System.Object, e As System.Windows.RoutedEventArgs)
        Me.CreateBasicWorkbook("BasicWorkbook.xlsx", True)
    End Sub

    Private Sub btnCreate10000SharedStrings_Click(sender As System.Object, e As System.Windows.RoutedEventArgs)
        Me.CreateStringWorkbook("SharedStrings10000.xlsx", 10000, True)
    End Sub

    Private Sub btnCreate10000Strings_Click(sender As System.Object, e As System.Windows.RoutedEventArgs)
        Me.CreateStringWorkbook("Strings10000.xlsx", 10000, False)
    End Sub

    Private Sub btnCreateBasicWorkbookPredefinedStyles_Click(sender As System.Object, e As System.Windows.RoutedEventArgs)
        Me.CreateBasicWorkbook("BasicWorkbookPredefinedStyles.xlsx", False)
    End Sub

    ''' <summary>
    ''' Creates a workbook with specified amount of strings
    ''' </summary>
    ''' <param name="workbookName">Name of the workbook</param>
    ''' <param name="stringCount">Number of strings to add</param>
    ''' <param name="useShared">Use shared strings?</param>
    ''' <returns>True if succesful</returns>
    Private Function CreateStringWorkbook(workbookName As String, stringCount As Int32, useShared As Boolean) As Boolean
        Dim spreadsheet As DocumentFormat.OpenXml.Packaging.SpreadsheetDocument
        Dim worksheet As DocumentFormat.OpenXml.Spreadsheet.Worksheet

        spreadsheet = Excel.CreateWorkbook(workbookName)
        If (spreadsheet Is Nothing) Then
            Return False
        End If

        Excel.AddBasicStyles(spreadsheet)
        Excel.AddWorksheet(spreadsheet, "Strings")
        worksheet = spreadsheet.WorkbookPart.WorksheetParts.First().Worksheet

        ' Add shared strings
        For counter As UInt32 = 0 To stringCount - 1 Step 1
            Excel.SetStringCellValue(spreadsheet, worksheet, 1, counter + 1, "Some string", useShared, False)
        Next
        ' Set column widths
        Excel.SetColumnWidth(worksheet, 1, 15)

        worksheet.Save()
        spreadsheet.Close()

        System.Diagnostics.Process.Start(workbookName)
        Return True
    End Function

    ''' <summary>
    ''' Creates a basic workbook
    ''' </summary>
    ''' <param name="workbookName">Name of the workbook</param>
    ''' <param name="createStylesInCode">Create the styles in code?</param>
    Private Sub CreateBasicWorkbook(workbookName As String, createStylesInCode As Boolean)
        Dim spreadsheet As DocumentFormat.OpenXml.Packaging.SpreadsheetDocument
        Dim worksheet As DocumentFormat.OpenXml.Spreadsheet.Worksheet
        Dim styleXml As String

        spreadsheet = Excel.CreateWorkbook(workbookName)
        If (spreadsheet Is Nothing) Then
            Return
        End If

        If (createStylesInCode) Then
            Excel.AddBasicStyles(spreadsheet)
        Else
            Using styleXmlReader As System.IO.StreamReader = New System.IO.StreamReader("PredefinedStyles.xml")
                styleXml = styleXmlReader.ReadToEnd()
                Excel.AddPredefinedStyles(spreadsheet, styleXml)
            End Using
        End If

        Excel.AddSharedString(spreadsheet, "Shared string")
        Excel.AddWorksheet(spreadsheet, "Test 1")
        Excel.AddWorksheet(spreadsheet, "Test 2")
        worksheet = spreadsheet.WorkbookPart.WorksheetParts.First().Worksheet

        ' Add shared strings
        Excel.SetStringCellValue(spreadsheet, worksheet, 1, 1, "Shared string", True)
        Excel.SetStringCellValue(spreadsheet, worksheet, 1, 2, "Shared string", True)
        Excel.SetStringCellValue(spreadsheet, worksheet, 1, 3, "Shared string", True)

        ' Add a string
        Excel.SetStringCellValue(spreadsheet, worksheet, 1, 5, "Number", False, False)
        ' Add a decimal number
        Excel.SetDoubleCellValue(spreadsheet, worksheet, 2, 5, 1.23, Nothing, True)

        ' Add a string
        Excel.SetStringCellValue(spreadsheet, worksheet, 1, 6, "Integer", False, False)
        ' Add an integer number
        Excel.SetDoubleCellValue(spreadsheet, worksheet, 2, 6, 1, Nothing, True)

        ' Add a string
        Excel.SetStringCellValue(spreadsheet, worksheet, 1, 7, "Currency", False, False)
        ' Add currency
        Excel.SetDoubleCellValue(spreadsheet, worksheet, 2, 7, 1.23, 2, True)

        ' Add a string
        Excel.SetStringCellValue(spreadsheet, worksheet, 1, 8, "Date", False, False)
        ' Add date
        Excel.SetDateCellValue(spreadsheet, worksheet, 2, 8, System.DateTime.Now, 1, True)

        ' Add a string
        Excel.SetStringCellValue(spreadsheet, worksheet, 1, 9, "Percentage", False, False)
        ' Add percentage
        Excel.SetDoubleCellValue(spreadsheet, worksheet, 2, 9, 0.123, 3, True)

        ' Add a string
        Excel.SetStringCellValue(spreadsheet, worksheet, 1, 10, "Boolean", False, False)
        ' Add boolean
        Excel.SetBooleanCellValue(spreadsheet, worksheet, 2, 10, True, Nothing, True)

        ' Set column widths
        Excel.SetColumnWidth(worksheet, 1, 15)
        Excel.SetColumnWidth(worksheet, 2, 20)

        worksheet.Save()
        spreadsheet.Close()

        System.Diagnostics.Process.Start(workbookName)
    End Sub

End Class

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

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

About the Author

Mika Wendelius
Architect
Finland Finland
I've been a programmer since mid 80's using languages like assembler, C/C++, PL/I (mainframe environment), pascal, VB (I know, I know, no comments please) and C# and utilizing different techniques and tools.
 
However I'm specialized in databases and database modeling. Mostly I have used products like Oracle (from version 6), SQL Server (from version 4.2), DB2 and Solid Server (nowadays an IBM product).
 
For the past 10+ years my main concerns have been dealing with different business processes and how to create software to implement and improve them. At my spare time (what ever that actually means) I'm also teaching and consulting on different areas of database management, development and database oriented software design.

| Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 5 May 2012
Article Copyright 2012 by Mika Wendelius
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid