Существуют задачи передачи информации между системами, когда нужно преобразовать один формат файлов в другой, причем желательно это сделать автоматически: например, нужно преобразовывать файлы формата XLS в формат CSV ежедневно после выгрузки XLS-файлов из какой-то системы.
Есть множетсво программ, которые позволяют это сделать вручную через оконный интерфейс, а вот аналогичных программ в режиме командной строки мало, причем большинство их них небесплатны при использовании в коммерческих целях. В данной статье я покажу, как сделать конвертор XLStoCSV на Visual Basic.
1. Необходимо создать новый проект "Консольное приложение" в Microsoft Visual Studio.
2. Задать свойства исполняемого файла.
3. Подключить библиотеки "System" и "Microsoft ActiveX Data Objects 6.1"
4. Вставить следующий код в файл "Module1.vb"
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 - конвертаци будет происходить автоматически по расписанию.