Solved

Convert xlsx files to xml and csv files class

Posted on 2011-03-06
4
1,875 Views
Last Modified: 2012-05-11
Hi,
I am using vb 2008. I am creating a class library file which will be able to convert xlsx files to csv and xml utf-8 or utf 16 formatted.

Please help me on this.
0
Comment
Question by:suvmitra
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 14

Assisted Solution

by:robasta
robasta earned 100 total points
ID: 35053470
0
 
LVL 9

Author Comment

by:suvmitra
ID: 35053733
I am expecting a sample code in VB.Net which is capable of converting an XLSX file to CSV ... XLSX to XML 8, XML 16..etc..thank you.
0
 
LVL 9

Accepted Solution

by:
suvmitra earned 0 total points
ID: 35114543
Yeah, I just finished writing my class which is able to convert UTF8 and csv files from xl.

Public Sub FileConverter()
        Try
            Dim cls As New clsConvertFile.XlsxToXmlCsv.clsFileConvert
            cls.FileConvert("F:\Conv_Now\D0005-10122010-28022011.xlsx", "F:\Conv_Now\D0005-10122010-28022011.txt", "UTF8")
            MsgBox("Done!", , "Conversion Done!")
            Me.Close()
        Catch ex As System.Data.OleDb.OleDbException
        End Try
    End Sub
Imports System.Configuration
Imports System.IO
Imports System.Data
Imports System.Data.OleDb
Imports System.Collections.Generic
Imports System.Text
Imports System.IO.StreamWriter
Namespace XlsxToXmlCsv

    Public Class clsFileConvert
        'Convert xls or xlsx file types to UTF8 Text or CSV file format
        Public Function FileConvert(ByVal strXlsx As String, ByVal strOtherFile As String, Optional ByVal strOutputFile As String = "")

            Dim sourceFile, worksheetName, targetFile As String
            sourceFile = strXlsx '"source.xls"
            worksheetName = "Sheet1"
            targetFile = strOutputFile '"target.csv"

            Dim Conn As New OleDbConnection
            Dim Comm As New OleDbCommand

            Dim wrtr As StreamWriter = Nothing
            Dim cmd As OleDbCommand = Nothing
            Dim da As OleDbDataAdapter = Nothing
            Dim rowString As String


            cmd = New OleDbCommand("SELECT * FROM [" & worksheetName & "$]", Conn)
            cmd.CommandType = CommandType.Text

            Conn.ConnectionString = CreateConnString(strXlsx)
            Comm.Connection = Conn

            Try
                'Convert Excel File to UTF8 Text File
                Conn.Open()

                If strOutputFile = "UTF8" Then

                    wrtr = New StreamWriter(strOtherFile, append:=False, Encoding:=System.Text.Encoding.UTF8)
                    wrtr.AutoFlush = True
                    da = New OleDbDataAdapter(cmd)

                    Dim dt As New DataTable()

                    da.Fill(dt)

                    For x As Integer = 0 To dt.Rows.Count - 1
                        rowString = ""

                        For y As Integer = 0 To dt.Columns.Count - 1
                            rowString &= """" & dt.Rows(x)(y).ToString() & ""","
                        Next y

                        wrtr.WriteLine(rowString)

                    Next x

                End If

                'Convert Excel File to CSV File

                If strOutputFile = "CSV" Then
                    wrtr = New StreamWriter(strOtherFile)

                    da = New OleDbDataAdapter(cmd)

                    Dim dt As New DataTable()
                    da.Fill(dt)

                    For x As Integer = 0 To dt.Rows.Count
                        rowString = ""

                        For y As Integer = 0 To dt.Columns.Count
                            rowString &= """" & dt.Rows(x)(y).ToString() & ""","
                        Next y

                        wrtr.WriteLine(rowString)
                    Next x
                End If

            Catch exc As System.IO.IOException 'An I/O error has occurred
            Catch exc2 As System.ObjectDisposedException 'The current writer is closed
            Catch exc3 As System.Data.OleDb.OleDbException ' ISAM is not installed

            Finally

                If Conn.State = ConnectionState.Open Then
                    Conn.Close()
                End If

                Conn.Dispose()

            End Try

            Return True

        End Function
        Public Function CreateConnString(ByVal Str As String) As String
            'Check the Excel Version, Create connection string  
            Dim appExcel As Object, strXlVersion As String
            Try
                appExcel = CreateObject("Excel.Application")
                strXlVersion = appExcel.Version

                If strXlVersion = "8.0" Then
                    Return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Str & ";Extended Properties=""Excel 8.0;HDR=No;"""
                Else
                    Return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Str & ";Extended Properties=""Excel 12.0 Xml;HDR=No;"""
                End If

                appExcel.Quit()
                appExcel = Nothing

            Catch exc As System.Data.OleDb.OleDbException
            End Try
            Return True
        End Function
    End Class
End Namespace

Open in new window

0
 
LVL 9

Author Closing Comment

by:suvmitra
ID: 35145601
This is what I was looking for and providing us a complete solution.
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question