Click here to Skip to main content
Click here to Skip to main content
Go to top

CSV to Excel XML Spreadsheet with VB.NET

, 22 Aug 2006
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

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.

License

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

Share

About the Author

Brasstax

United States United States
No Biography provided

Comments and Discussions

 
QuestionThanks for sharing PinmemberTre Grisby17-Sep-12 12:13 
Generalcomponent PinmemberFilipKrnjic29-Jul-09 0:40 

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

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

| Advertise | Privacy | Mobile
Web01 | 2.8.140916.1 | Last Updated 23 Aug 2006
Article Copyright 2006 by Brasstax
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid