CSV to Excel XML Spreadsheet with VB.NET

• Download source files and executable - 21.8 KB

Introduction

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=""http://www.w3.org/TR/REC-html40"">" & _ 
      "<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 _
            Microsoft.VisualBasic.FileIO.TextFieldParser(lblImportFileName.Text)
        MyReader.TextFieldType = FileIO.FieldType.Delimited
        MyReader.SetDelimiters(",")

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

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

        Windows.Forms.Cursor.Current = Cursors.Default
        MsgBox("Done")

    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.

History

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