Solved

Convert xlsx files to xml and csv files class

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

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

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

Summary Displaying images in RichTextBox is a common requirement with limited solutions available. Pasting through clipboard or embedding into RTF content only support static images.  This article describes how to insert Windows control objects int…
A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

789 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