Главная > Powershell > Конвертирование txt-файла в xls с помощью Powershell

Конвертирование txt-файла в xls с помощью Powershell

В комментариях к статье Powershell и Excel. Часть 2: форматирование ячеек мне задали вопрос:

подскажите, каким образом занести данные с файла 123.txt в Excel?

Там же в комментариях развелась целая дискуссия на эту тему. А недавно сам столкнулся с похожей проблемой. В моём случае файлов было несколько, и мало того, что нужно было перенести данные из текстового файла в Excel, так их ещё нужно было проанализировать. Поэтому решил поделиться своим способом конвертирования текстового файла в файл Excel.

В моём скрипте два обязательных параметра: путь к исходному текстовому файлу, и знак разделитель (по умолчанию — пробел):

[CmdletBinding()]
param
(
    # Путь к текстовому файлу
    [Parameter(Mandatory=$true)]
    [string[]]$Path,
    
    # Разделитель
    # По умолчанию - пробел
    [Parameter(Mandatory=$true)]
    [char]$Separator = ' '
)

Считываем наш файл и запускаем Excel:

# Читаем файл
Write-Verbose "Читаем файл $Path"
$Content = Get-Content $Path

# Запускаем Excel
Write-Verbose 'Запускаем Excel...'
$Excel = New-Object -ComObject Excel.Application

Чтобы было веселее Для наглядности я всегда в своих скриптах добавляю поддержку стандартных параметров, в частности –Verbose для того, чтобы было видно, что в данный момент делает скрипт:

# Если указан параметр Verbose
if ($PSBoundParameters.Verbose)
{
    # Выводим подробные сообщения
    $VerbosePreference = "Continue"
    
    # Делаем excel видимым
    $Excel.Visible = $true
}

В частности, в данном скрипте кроме вывода собственных сообщений, при задании —Verbose я ещё делаю видимым Excel. (Всегда нравится наблюдать за реакцией коллег, когда после нескольких взмахов руками над клавиатурой запускается Excel, сам записывает данные, сам форматирует текст и закрывается :))

Стандартные подготовительные действия для последующей работы с Excel:

# Добавляем рабочую книгу
$WorkBook = $Excel.Workbooks.Add()

# Цепляемся к первому листу
$WorkSheet = $WorkBook.Worksheets.Item(1)

Далее начинается непосредственно работа. Каждая строка из файла разбивается на подстроки (блоки, разделённые указанным символом разделителем) и каждый блок записывается в свою собственную ячейку:

# Цикл по строкам
foreach ($Line in $Content)
{
    # Переменные для обозначения ячеек в Excel-файле
    # Строка
    $Row++
    # Первый столбец
    $Column = 1
    
    # Строка разбитая на блоки
    $Result = $Line -split $Separator
    
    # Каждый блок из строки записываем в xls-файл
    foreach ($Item in $Result)
    {
        $WorkSheet.Cells.Item($Row, $Column) = $Item

        # Переходим в следующий столбец
        $Column++
    }
}

По сути это всё. Далее при необходимости можно форматировать файл, как будет угодно, например выровнять ширину ячеек, чтобы текст помещался:

$UsedRange = $WorkSheet.UsedRange
$UsedRange.EntireColumn.AutoFit() | Out-Null

Осталось сохранить результат и выйти. В моём случае xls-файл сохраняется в том же каталоге, что и исходный текстовый файл, с тем же именем (за исключением расширения):

# Путь к исходному файлу
$FolderPath = (Get-ChildItem $Path).DirectoryName

# Имя исходного файла без расширения
$BaseName = (Get-ChildItem $Path).BaseName

# Путь к готовому xls-файлу
$xlsPath = Join-Path $FolderPath "$BaseName.xls"

# Сохраняем и выходим
$WorkBook.SaveAs($xlsPath)
$Excel.Quit()

Как-то читал, что COM-объекты устроены не так, как объекты .NET, и за ними нужно “вручную” освобождать память, вот так:

$null =
[Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$Excel)
 
[gc]::Collect()
[gc]::WaitForPendingFinalizers()
Remove-Variable Excel

В результате память может и освобождается, но процесс Excel остаётся запущенным и невидимым.

Ну и напоследок пару скриншотов, как это работает.

Если исходный текстовый файл имеет вид:

ConvertTXT-toXLS-1

То готовый xls-файл будет выглядеть так:

ConvertTXT-toXLS-2

Конечно, это не комплексное решение: здесь нет проверки на то является-ли указанный файл действительно текстовым, присутствуют-ли в нём разделители, и много чего ещё. Я только показал алгоритм работы.

Реклама
Рубрики:Powershell Метки: , ,
  1. Андрей
    20/04/2015 в 23:35

    Как вариант можно ещё попользовать com-object:

    $range = $Sheet.UsedRange
    $Range.texttocolumns($Range,1,-4142,$true,$false,$false,$false,$true)

    Excel сам все сделает.

  2. skat
    13/05/2015 в 15:17

    Отличная статья по содержанию, вечером попробую текст который накопился за все дни занести excel.Благодарен за развернутый ответ на возникший вопрос.

  1. No trackbacks yet.

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход / Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход / Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход / Изменить )

Google+ photo

Для комментария используется ваша учётная запись Google+. Выход / Изменить )

Connecting to %s

%d такие блоггеры, как: