Solved

Convert xlsx files to xml and csv files class

Posted on 2011-03-06
4
1,700 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
  • 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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

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…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now