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

10.11.2013

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

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

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

xls to csv convertion

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

xls to csv convertion

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

xls to csv convertion

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

xls to csv convertion
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 - конвертаци будет происходить автоматически по расписанию.

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