Конвертация XLS в CSV

Существуют задачи передачи информации между системами, когда нужно преобразовать один формат файлов в другой, причем желательно это сделать автоматически: например, нужно преобразовывать файлы формата XLS в формат CSV ежедневно после выгрузки XLS-файлов из какой-то системы.

Есть множетсво программ, которые позволяют это сделать вручную через оконный интерфейс, а вот аналогичных программ в режиме командной строки мало, причем большинство их них небесплатны при использовании в коммерческих целях. В данной статье я покажу, как сделать конвертор XLStoCSV на Visual Basic.

1. Необходимо создать новый проект "Консольное приложение" в Microsoft Visual Studio.

xlstocsvconvertion_-_1.png

2. Задать свойства исполняемого файла.

xlstocsvconvertion_-_2.png

3. Подключить библиотеки "System" и "Microsoft ActiveX Data Objects 6.1"

xlstocsvconvertion_-_3.png

4. Вставить следующий код в файл "Module1.vb"

xlstocsvconvertion_-_4.png
Imports System

Module Module1
    Private mAppName As String
    Private mWorkDirectory As String
    Private mArguments() As String

    ' --- Set constants ---
    Private Const adOpenStatic As Integer = 3
    Private Const adLockOptimistic As Integer = 3
    Private Const FileReading As Integer = 1
    Private Const FileWriting As Integer = 2
    Private Const FileAppending As Integer = 8
    Private Const FileSystemDefault As Integer = -2
    Private Const FileUnicode As Integer = -1
    Private Const FileASCII As Integer = 0
    Private Const adSchemaTables = 20
    Private Const adSchemaColumns = 4

    Sub Main()
        Dim InputFolderPath As String
        Dim OutputFolderPath As String
        ' Получение имени программы
        mAppName = My.Application.Info.AssemblyName
        ' Получение рабочего каталога программы
        mWorkDirectory = Left(Reflection.Assembly.GetExecutingAssembly.Location, _
          InStrRev(Reflection.Assembly.GetExecutingAssembly.Location, "\") - 1)
        ' -------------------- Вывод в консоль информации о программе --------------------
        Console.WriteLine()
        Console.WriteLine(My.Application.Info.Title & " version " & My.Application.Info.Version.Major _
          & "." & My.Application.Info.Version.Minor)
        Console.WriteLine(My.Application.Info.Copyright)
        Console.WriteLine()
        ' -------------------- Считывание аргументов командной строки --------------------
        mArguments = Environment.GetCommandLineArgs()
        If mArguments.Length = 3 Then
            InputFolderPath = mArguments(1)
            OutputFolderPath = mArguments(2)
            If IO.Directory.Exists(InputFolderPath) = False Then
                Console.WriteLine(InputFolderPath & " does not exist")
            ElseIf IO.Directory.Exists(OutputFolderPath) = False Then
                Console.WriteLine(OutputFolderPath & " does not exist")
            Else
                For Each cuFilePath As String In IO.Directory.GetFiles(InputFolderPath)
                    If LCase(Right(cuFilePath, 4)) = ".xls" Then
                        Try
                            Call ConvertXLStoCSV(cuFilePath, OutputFolderPath)
                        Catch ex As Exception
                            Console.WriteLine(ex.Message)
                        End Try
                    End If
                Next
            End If
        End If
        Console.WriteLine()
    End Sub

    Sub ConvertXLStoCSV(ByVal sXLSPath As String, ByVal sOutputFolder As String)
        Console.WriteLine(sXLSPath & " is being processed")
        Dim ADODBConnection As New ADODB.Connection()
        Dim ADODBRecordSet As New ADODB.Recordset()
        Dim ADODBSchema As ADODB.Recordset
        Dim ADODBConnectionString As String
        Dim ADODBCommand As New ADODB.Command()
        Dim OutputFileWriter As IO.StreamWriter
        Dim OutputFilePath As String
        'ADODBConnectionString = _
        '  "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        '  "Data Source=" & sXLSPath & ";" & _
        '  "Extended Properties=""Excel 8.0;HDR=No;"""
        ADODBConnectionString = _
          "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _
          "DBQ=" & sXLSPath & ";" & _
          "ReadOnly=True;"
        ADODBConnection.ConnectionString = ADODBConnectionString
        ADODBConnection.Open()
        ADODBSchema = ADODBConnection.OpenSchema(ADODB.SchemaEnum.adSchemaTables)
        ' Проверка наличия структуры
        If ADODBSchema.EOF = False Then
            ' Перемещение курсора в начало структуры
            ADODBSchema.MoveFirst()
            ' Обработка каждого листа
            Do Until ADODBSchema.EOF
                ' Составление имени выходного файла
                OutputFilePath = ""
                OutputFilePath = OutputFilePath & sOutputFolder
                OutputFilePath = OutputFilePath & "\" & Mid(sXLSPath, InStrRev(sXLSPath, "\") + 1, _
                  Len(sXLSPath) - InStrRev(sXLSPath, "\") - 4) & ".csv"
                Try
                    ' Открытие листа
                    ADODBRecordSet.Open( _
                      "SELECT * FROM [" & ADODBSchema.Fields("TABLE_NAME").Value & "]", _
                      ADODBConnection, adOpenStatic, adLockOptimistic)
                    ' Перемещение курсора в начало листа
                    ADODBRecordSet.MoveFirst()
                    ' Создание выходного файла
                    OutputFileWriter = New IO.StreamWriter(OutputFilePath, False)
                    OutputFileWriter.AutoFlush = True
                    ' Обработка каждой строки
                    Do Until ADODBRecordSet.EOF
                        For iColumnCounter = 0 To ADODBRecordSet.Fields.Count - 1
                            OutputFileWriter.Write(ADODBRecordSet.Fields(iColumnCounter).Value & ";")
                        Next
                        OutputFileWriter.Write(vbCrLf)
                        ADODBRecordSet.MoveNext()
                    Loop
                    ' Закрытие выходного файла
                    OutputFileWriter.Close()
                    ADODBRecordSet.Close()
                    ' Выход из обработки - то есть обработка только одного листа
                    Exit Do
                Catch ex As Exception
                    Console.WriteLine(ex.Message)
                End Try
                ' Перемещение структуры к следующему листу
                ADODBSchema.MoveNext()
            Loop
        End If
        ADODBSchema.Close()
        ADODBConnection.Close()
    End Sub
End Module

Здесь используются следующий сценарий для обработки файлов:
- подключение XLS-файла в качестве базы данных с помощью драйвера Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb);
- чтение первого листа и запись данных в выходной текстовый файл с разделителями точка с запятой;
- игнорирование других листов файла;
- закрытие выходного файла и отключение от XLS-файла

5. Построить проект.

6. Готовый файл XLStoCSV.exe нужно скопировать на сервер.

7. На сервере нужно установить Microsoft Access database engine. (На компьютере, где производилась сборка проекта драйверы MS Office обычно уже установлены, поэтому этот пункт был пропущен).

8. Запускать конвертацию лучше всего через BAT-файл XLStoCSV.bat:

"%~DP0XLStoCSV.exe" "%~DP0Others\XLS" "%~DP0Others\CSV" > "%~DP0XLStoCSV.log"

Этот сценарий запускает файл XLStoCSV.exe из той же папки, в которой находится сам. В качестве входных параметров указана папка с XLS документами и папка, куда складывать CSV файлы. Лог работы программы пишется в файл XLStoCSV.log.

Запуск файл XLStoCSV.bat можно поставить в планировщик Windows - конвертаци будет происходить автоматически по расписанию.

Виталий Бочкарев
Вверх