Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2052
  • Last Modified:

Convert xlsx files to xml and csv files class

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
suvmitra
Asked:
suvmitra
  • 3
2 Solutions
 
robastaCommented:
0
 
suvmitraAuthor Commented:
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
 
suvmitraAuthor Commented:
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
 
suvmitraAuthor Commented:
This is what I was looking for and providing us a complete solution.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now