[Автоматизация] Автоматическая корректировка файла Excel

Yriy_07 · 18 Апр 2018

Версия AutoIt: 3.3.0.0

Описание: Есть выгруженныe файлы отчета в excel (5 листов по 200-300 строк в каждом). Необходимо имеющиеся в них данные перенести в один новый файл exel. Пример во вложении

Примечания: Сделал вариант с "Send" разделив на несколько операций обработки, но обрабатывается все равно долго и не всегда корректно

gunter123 · 18 Апр 2018

Можно пример такого отчета Excel?
Добавлено:
Сообщение автоматически объединено: 18 Апр 2018

Вот, как-то так. Считается, что в отчетах после строчки со статусом OK идет строчка со статусом PROBLEM, а еще что время записано в первой колонке, продолжительность - в пятой, статус - в третьей и первая строчка не содержит информации.

Код:

#include <Array.au3>
#include <File.au3>
#include <Excel.au3>

Dim $aTotal[0][4]								; Конечный массив
$iCount = 0										; Переменная для конечного массива
$sPath = @ScriptDir 							; Путь к папке с файлами Excel
$sTotalName = "Итог.xlsx"						; Название итогового файла Excel
$aList = _FileListToArray($sPath, '*.xlsx')		; Берем список xlsx файлов в папке
_ArrayDelete($aList, $sTotalName)				; Удаляем из этого списка название итогового файла, если он есть
$oExcel = _Excel_Open()							; Открываем Excel
$iTotalTime = 0

For $iFile = 1 To UBound($aList) -1				; Для каждого файла...
   $oBook  = _Excel_BookOpen($oExcel, $sPath & "\" & $aList[$iFile])
   $aRange = _Excel_RangeRead($oBook)			; Читаем в массив содержимое файла Excel
   For $iRow = 1 To UBound($aRange) -1			; Проходим по каждой строчке массива, первую строчку пропускаем
	  If $aRange[$iRow][2] = "" Then ContinueLoop	; Пропускаем пустые строки
	  If $aRange[$iRow][2] = "OK" Then				; Записываем в конечный массив, если статус (третья строчка) = OK
		 ReDim $aTotal[$iCount +1][4]				; Расширяем массив
		 $aTotal[$iCount][0] = _ToDate($aRange[$iRow+1][0])		; Записываем в первую колонку дату в следующей строчке
		 $aTotal[$iCount][1] = _ToDate($aRange[$iRow][0])		; Во вторую колонку - дату в текущей строчке
		 $aTotal[$iCount][2] = "PROBLEM"						; На место статуса (третья колонка) записываем Problem
		 $aTotal[$iCount][3] = $aRange[$iRow+1][4]				; В четвертую колонку записываем Duration
		 ;ConsoleWrite($aRange[$iRow+1][4] & ' ' & _ToSecond($aRange[$iRow+1][4]) & @CRLF)
		 $iTotalTime = $iTotalTime + _ToSecond($aRange[$iRow+1][4])
		 $iCount = $iCount + 1
	  EndIf
   Next
   _Excel_BookClose($oBook)							; Закрываем книгу
Next

ReDim $aTotal[$iCount +1][4]						; Запишем общее время
$aTotal[$iCount][3] = _SecondToTime($iTotalTime)
$aTotal[$iCount][2] = "Всего: "

$oBook = _Excel_BookOpen($oExcel, $sPath & "\" & $sTotalName)	; Открываем итоговый файл
If @error Then $oBook = _Excel_BookNew($oExcel)					; Или создаем новый

_Excel_RangeWrite($oBook, $oBook.Activesheet, $aTotal, "A1")	; Записать массив в книгу
_Excel_BookSaveAs($oBook, $sPath & "\" & $sTotalName)			; Сохранить книгу под именем итогового файла

Func _ToSecond($sTime)								; Переводит строку с временем в секунды. Например, из строки 3m 49s вернет 229
   $aTime = StringSplit($sTime, ' ', 2)
   $iValue = 0
   For $iTime = 0 To UBound($aTime) -1
	  $sTmp = StringRight($aTime[$iTime], 1)
	  $aTime[$iTime] = StringTrimRight($aTime[$iTime], 1)
	  Switch $sTmp
		 Case 'd'
			$iValue = $iValue + $aTime[$iTime] * 86400
		 Case 'h'
			$iValue = $iValue + $aTime[$iTime] * 3600
		 Case 'm'
			$iValue = $iValue + $aTime[$iTime] * 60
		 Case 's'
			$iValue = $iValue + $aTime[$iTime]
	  EndSwitch
   Next
   Return $iValue
EndFunc

Func _ToDate($sStr)									; У меня Excel читает даты примерно как 20180418235301 , эта функция меняет формат даты обратно
   Return StringLeft($sStr, 4) & '-' & StringMid($sStr, 5, 2) & '-' & StringMid($sStr, 7, 2) _
		  & ' ' & StringMid($sStr, 9, 2) & ':' & StringMid($sStr, 11, 2) & ':' & StringRight($sStr, 2)
EndFunc
	   
Func _SecondToTime($iSecond)						; Формирует время из секунд, из 123 вернет 0.00:02:03 . Костыльно, конечно, но все же
   If $iSecond >= 86400 Then
	  $iTmp = Floor($iSecond / 86400)
	  $iValue = $iTmp & '.'
	  $iSecond = $iSecond - $iTmp * 86400
   Else
	  $iValue = '0.'
   EndIf
   If $iSecond >= 3600 Then
	  $iTmp = Floor($iSecond / 3600)
	  If $iTmp < 10 Then
		 $iValue = $iValue & '0' & $iTmp & ':'
	  EndIf
	  $iValue = $iValue & $iTmp & ':'
	  $iSecond = $iSecond - $iTmp * 3600
   Else
	  $iValue = $iValue & '00:'
   EndIf
   If $iSecond >= 60 Then
	  $iTmp = Floor($iSecond / 60)
	  If $iTmp < 10 Then
		 $iValue = $iValue & '0'
	  EndIf
	  $iValue = $iValue & $iTmp & ':'
	  $iSecond = $iSecond - $iTmp * 60
   Else
	  $iValue = $iValue & '00:'
   EndIf
   If $iSecond > 10 Then
	  Return $iValue & $iSecond
   Else
	  Return $iValue & '0' & $iSecond
   EndIf
EndFunc

Yriy_07 · 19 Апр 2018

>"C:\Program Files (x86)\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.exe" /run /prod /ErrorStdOut /in "C:\Users\ILANYURI\Desktop\autoit\as\as.au3" /UserParams
+>08:39:23 Starting AutoIt3Wrapper v.2.2.0.3 SciTE v.3.4.1.0 Keyboard:00000409 OS:WIN_81/ CPU:X64 OS:X64 Environment(Language:0419)
+> SciTEDir => C:\Program Files (x86)\AutoIt3\SciTE UserDir => C:\Users\ILANYURI\AppData\Local\AutoIt v3\SciTE\AutoIt3Wrapper SCITE_USERHOME => C:\Users\ILANYURI\AppData\Local\AutoIt v3\SciTE
>Running AU3Check (1.54.14.0) from:C:\Program Files (x86)\AutoIt3 input:C:\Users\ILANYURI\Desktop\autoit\as\as.au3
C:\Users\ILANYURI\Desktop\autoit\as\as.au3(11,23) : ERROR: _Excel_Open(): undefined function.
$oExcel = _Excel_Open()

Ошибка выходит при запуске

gunter123 · 19 Апр 2018

Какая у вас версия Autoit?

Yriy_07 · 19 Апр 2018

3.3.0.0

mr.Gbabak · 19 Апр 2018

Yriy_07 сказал(а):
Описание: Пример во вложении

не затруднит вас прикрепить несколько исходных файлов, и файл который должен получиться?!

Yriy_07 сказал(а):
Описание:C:\Users\ILANYURI\Desktop\autoit\as\as.au3(11,23) : ERROR: _Excel_Open(): undefined function

у вас не установленна библиотека Excel.au3, обновите Autoit или скачайте свежую библиотеку

Yriy_07 · 20 Апр 2018

Могу выслать на почту так как excel формат загрузить нельзя
Добавлено:
Сообщение автоматически объединено: 20 Апр 2018

Обьясню всю сложность данной процедуры. Изначально файлs скачивается с браузера c расширением CSV. Далее я открываю его Либеро он предлагает разбить его на поля, соглашаюсь затем сохраняю этот файл в формате excel, после этого удаляю файлы Либеро, в среднем выгружается около 10 файлов. Затем захожу в каждый файл добовляю столбец и переношу время в соответсттвии с примером затем изменяю формат значений 30s на 0,30 | 1m 30s на 1,30 | 1h 25m 30s на 85,30 | 1d 17h 15m 30s на 2475,30 . После этого суммирую время, затем удаляю ненужные столбцы, и так каждый файл количество строк в файле различное от 2 до 500 после того как обработал каждый файл беру шаблон отчета и забиваю туда данные с каждого файла отчет имеет два раздела. вверху указывается сумма времени по узлам по мощности и физикии она переводится в проценты 4 столбца. А внизу идет детализированный отчет в котором указано время начало время завершения продолжителность имя узла и тип (мощность либо физика). Один небольшой отчет занимает около 2 с половиной часов времени по одному филиалу а их более 25. отчет ежемесячный. :(
Добавлено:
Сообщение автоматически объединено: 20 Апр 2018

Версию autoit обновил до 3.3 скрипт начал отрабатывать но не захватывает информацию со всех файлов результат примерно такой ! строка:
2018--0-4- 16: 1:45 2018--0-4- 16: 1:15 PROBLEM 11m 30s

Всего: 0.04:4:18:59

Со временем как бы непонятно

Добавлено:
Сообщение автоматически объединено: 20 Апр 2018

Положил в рабочюю директорию один файл, исхдник и результат обработки во вложении. Исходник выше результат ниже
Добавлено:
Сообщение автоматически объединено: 20 Апр 2018

Вот два исходных файла и отчет. Отчет по другим файлам создан поэтому цифры не соответствуют

gunter123 · 20 Апр 2018

С этого надо было начинать. Теперь скрипт ищет файлы CSV, в которых данные находятся в одной ячейке в каждой строке и записывает их в файл Итог.xlsx. С файлами CSV, что были в архиве, работает нормально.

Код:

#include <Array.au3>
#include <File.au3>
#include <Excel.au3>

Dim $aTotal[0][4]								; Конечный массив
$iCount = 0										; Переменная для конечного массива
$sPath = @ScriptDir 							; Путь к папке с файлами Excel
$sTotalName = "Итог.xlsx"						; Название итогового файла Excel
$aList = _FileListToArray($sPath, '*.csv')		; Берем список xlsx файлов в папке
_ArrayDelete($aList, $sTotalName)				; Удаляем из этого списка название итогового файла, если он есть
$oExcel = _Excel_Open()							; Открываем Excel
$iTotalTime = 0

For $iFile = 1 To UBound($aList) -1				; Для каждого файла...
   $oBook  = _Excel_BookOpen($oExcel, $sPath & "\" & $aList[$iFile])
   $aRange = _Excel_RangeRead($oBook)			; Читаем в массив содержимое файла Excel
   For $iRow = 1 To UBound($aRange) -1 Step 2	; Проходим по каждой строчке массива, первую строчку пропускаем
	  If $aRange[$iRow] = "" Then ContinueLoop	; Пропускаем пустые строки
	  $aSplit = StringSplit($aRange[$iRow], ',', 3)
	  $aNext = StringSplit($aRange[$iRow+1], ',', 3)
	  ;_ArrayDisplay($aNext)
	  ReDim $aTotal[$iCount +1][4]				; Расширяем массив
	  $aTotal[$iCount][0] = $aNext[0]			; Записываем в первую колонку дату в следующей строчке
	  $aTotal[$iCount][1] = $aSplit[0]			; Во вторую колонку - дату в текущей строчке
	  $aTotal[$iCount][2] = "PROBLEM"			; На место статуса (третья колонка) записываем Problem
	  $aTotal[$iCount][3] = _SecondToMinutes(_ToSecond($aNext[4]))	; В четвертую колонку записываем Duration
	  $iTotalTime = $iTotalTime + _ToSecond($aNext[4])
	  $iCount = $iCount + 1
   Next
   _Excel_BookClose($oBook)							; Закрываем книгу
Next

ReDim $aTotal[$iCount +1][4]						; Запишем общее время
$aTotal[$iCount][3] = _SecondToMinutes($iTotalTime)
$aTotal[$iCount][2] = "Всего: "

$oBook = _Excel_BookOpen($oExcel, $sPath & "\" & $sTotalName)
If @error Then $oBook = _Excel_BookNew($oExcel)

_Excel_RangeWrite($oBook, $oBook.Activesheet, $aTotal, "A1")
_Excel_BookSaveAs($oBook, $sPath & "\" & $sTotalName)

Func _SecondToMinutes($iSecond)						; Переводит секунды в минуты в формате MM,SS
   If $iSecond >= 60 Then
	  $iTmp = Floor($iSecond / 60) & ','
	  $iSecond = $iSecond - $iTmp * 60
   Else
	  $iTmp = 0 & ','
   EndIf

   If $iSecond > 10 Then
	  Return $iTmp & $iSecond
   Else
	  Return $iTmp & '0' & $iSecond
   EndIf
EndFunc

Func _ToSecond($sTime)								; Переводит строку с временем в секунды. Например, из строки 3m 49s вернет 229
   $sTime = StringReplace($sTime, '"', '')
   $aTime = StringSplit($sTime, ' ', 2)
   $iValue = 0
   For $iTime = 0 To UBound($aTime) -1
	  $sTmp = StringRight($aTime[$iTime], 1)
	  $aTime[$iTime] = StringTrimRight($aTime[$iTime], 1)
	  Switch $sTmp
		 Case 'd'
			$iValue = $iValue + $aTime[$iTime] * 86400
		 Case 'h'
			$iValue = $iValue + $aTime[$iTime] * 3600
		 Case 'm'
			$iValue = $iValue + $aTime[$iTime] * 60
		 Case 's'
			$iValue = $iValue + $aTime[$iTime]
	  EndSwitch
   Next
   Return $iValue
EndFunc

Yriy_07 · 20 Апр 2018

Спасибо огромное! Все работает! :smile:

:IL_AutoIt_1:

Автор темы	Похожие темы	Форум	Ответы	Дата
M	[Автоматизация] Автоматическая печать чертежей	Покупка/продажа, поиск услуг	0	14 Фев 2017
	[Автоматизация] Автоматическая активация программы	Общие вопросы по AutoIt	1	2 Апр 2015
V	[Автоматизация] Автоматическая установка набора программ	Общие вопросы по AutoIt	13	6 Мар 2015
G	[Автоматизация] Автоматическая раскладка окон в виндовсе 7	Стол заказов	0	18 Ноя 2014
Z	[Автоматизация] Автоматическая регистрация на сайте	Стол заказов	1	2 Сен 2014

Поиск

Поиск

[Автоматизация] Автоматическая корректировка файла Excel

Yriy_07

Новичок

Вложения

gunter123

Продвинутый

Yriy_07

Новичок

gunter123

Продвинутый

Yriy_07

Новичок

mr.Gbabak

Осваивающий

Yriy_07

Новичок

Вложения

gunter123

Продвинутый

Yriy_07

Новичок