?
Solved

Convert xlsx files to xml and csv files class

Posted on 2011-03-06
4
Medium Priority
?
2,012 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 400 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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses
Course of the Month3 days, 11 hours left to enroll

599 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