Click here to Skip to main content
12,825,743 members (36,912 online)
Click here to Skip to main content
Add your own
alternative version


28 bookmarked
Posted 22 Aug 2006

CSV to Excel XML Spreadsheet with VB.NET

, 22 Aug 2006 CPOL
Rate this:
Please Sign up or sign in to vote.
A simple program to create Excel XML spreadsheets from CSV files. Utility with source.

Sample Image - csvtoexcelxml.jpg


Have you ever been frustrated by the fact that you (or your users) can't simply double-click a .csv file that contains fields that have leading zeros, without having them truncated by Excel during "auto-import"? An acquaintance of mine was in just such a situation, and was crying out for help.

I created this simple little application that helps alleviate the pain. I thought I would post it here just in case someone else might find it useful.

Using the Code

The code is very straightforward. It uses an XML header and footer taken straight out of an Excel XML spreadsheet. It opens the file the user has specified, and then reads in each line from the file using a TextFieldParser. The application then creates rows for each line, and writes them to the file system using a StreamWriter. Error checking has been removed from the source for clarity. However, "Try/Catch" statements should be added to handle possible errors that might be raised.

Public Class Form1
    Private sHeader As String = "<?xml version=""1.0""?><?mso-application " & _ 
      "progid=""Excel.Sheet""?> <Workbook xmlns=""urn:schemas-microsoft-com:office:" & _ 
      "spreadsheet"" xmlns:o=""urn:schemas-microsoft-com:office:office"" " + _
      "xmlns:x=""urn:schemas-microsoft-com:office:excel"" xmlns:ss=""urn:schemas" & _ 
      "-microsoft-com:office:spreadsheet"" " & _ 
      "xmlns:html="""">" & _ 
      "<DocumentProperties xmlns=""urn:schemas-microsoft-com:office:office"">" + _
      "</DocumentProperties> <ExcelWorkbook xmlns=""urn:schemas-microsoft-com:" & _ 
      "office:excel""> <ProtectStructure>False</ProtectStructure>" + _
      "<ProtectWindows>False</ProtectWindows> </ExcelWorkbook> " & _ 
      "<styles> <style ss:ID=""Default"" ss:Name=""Normal"">" + _
      "<Alignment ss:Vertical=""Bottom""/> <Borders/> <Font/> " & _ 
      "<Interior/> <NumberFormat/> <Protection/> </Style>" + _
      "<style ss:ID=""s21""> <NumberFormat ss:Format=""@""/> " & _ 
      "</Style> </Styles> <Worksheet ss:Name=""Sheet1"">" + _
      "<Table> <Column ss:StyleID=""s21""/>"

    Private sFooter As String = "</Table></Worksheet></Workbook>"

    Private Sub Form1_Load(ByVal sender As System.Object, _
                ByVal e As System.EventArgs) Handles MyBase.Load
        lblImportFileName.Text = ""
        lblExportFileName.Text = ""
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, _
                ByVal e As System.EventArgs) Handles Button1.Click
        If OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
            lblImportFileName.Text = OpenFileDialog1.FileName
        End If
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, _
                ByVal e As System.EventArgs) Handles Button2.Click
        If SaveFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
            lblExportFileName.Text = SaveFileDialog1.FileName
        End If
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, _
                ByVal e As System.EventArgs) Handles Button3.Click
        Windows.Forms.Cursor.Current = Cursors.WaitCursor
        If lblImportFileName.Text = "" Or lblExportFileName.Text = "" Then
                MsgBox("Please select a file to import in CSV format " & _ 
                       "and also a destination file location and name.")
                Windows.Forms.Cursor.Current = Cursors.Default
                Exit Sub
        End If

        Dim MyReader As New _
        MyReader.TextFieldType = FileIO.FieldType.Delimited

        Dim file As System.IO.StreamWriter = _
            My.Computer.FileSystem.OpenTextFileWriter(lblExportFileName.Text, False)

        Dim currentRow As String()
        While Not MyReader.EndOfData
           currentRow = MyReader.ReadFields()
           Dim currentField As String
           For Each currentField In currentRow
              file.Write("<Cell><Data ss:Type=""String"">" + _
                         currentField + "</Data></Cell>")
        End While

        Windows.Forms.Cursor.Current = Cursors.Default

    End Sub

End Class

Points of Interest

There is much more that can be done with an application like this. This barely scratches the surface of what is possible with XML spreadsheets for Excel. I am looking forward to learning more.


  • 8/23/2006 - Updated with source on the page.
  • 8/22/2006 - Initial application created.


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


About the Author

United States United States
No Biography provided

You may also be interested in...

Comments and Discussions

QuestionThanks for sharing Pin
Tre Grisby17-Sep-12 13:13
memberTre Grisby17-Sep-12 13:13 
Generalcomponent Pin
FilipKrnjic29-Jul-09 1:40
memberFilipKrnjic29-Jul-09 1:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170308.1 | Last Updated 23 Aug 2006
Article Copyright 2006 by Brasstax
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid