[Автоматизация] Обработка файла excel

Yriy_07 · 1 Июн 2018

Версия AutoIt: 3.3.3

Описание:
Здравствуйте, уважаемые Гуру и продвинутые пользователи AUTOIT. Есть файл excel требующий обработки. Процедура такая взять данные из файлов 1,2,3,4 и тд (дату и время и поместить все это в другой файл №2 В соответствующие ячейки. Попутно просчитывая проценты и суммарное время. Написал код но он не в цикле и не работает с массивом. Не все мне понятно по массивам и есть вопросы по циклу. Прилагаю исходный код и описание, а также исходные готовый файл и описание. Буду благодарен за вашу помощь и разьяснение!

Примечания:

gunter123 · 1 Июн 2018

Что такое время недоступности / перегрузки? Как понять, в какую из колонок писать?

mr.Gbabak · 2 Июн 2018

Код:

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

$FileList = _FileListToArray(@ScriptDir)
If @error = 4 Then
	MsgBox(0, "", "Файлы/папки не найдены.")
	Exit
EndIf

For $i = UBound($FileList) - 1 To 0 Step -1
	If StringRight($FileList[$i], 4) <> '.csv' Then
		_ArrayDelete($FileList, $i)
	EndIf
Next

Dim $mOtchet[UBound($FileList)][7]

For $y = 0 To UBound($FileList) - 1
	$otchet1 = readexcsel($FileList[$y])
	Local $temp = 0, $temp2 = 0, $koltemp
	For $i = 1 To UBound($otchet1) - 1
		If $otchet1[$i][2] = 'problem' Then
			$temp = StringRegExpReplace($otchet1[$i][4], 'm(.*?)?$', '')
			If @extended = 0 Then
				$temp = 0
			Else
				$temp = Number($temp * 60)
			EndIf
			$temp2 = StringReplace($otchet1[$i][4], 's', '')
			$temp2 = StringRegExpReplace($temp2, '(.*?) ', '')
			$temp2 = StringReplace($temp2, 'm', '')
			If @extended > 0 Then
				$temp2 = 0
			EndIf
			$otchet1[$i][4] = $temp + $temp2
			$temp = 0
			$temp2 = 0
			$koltemp = $koltemp + $otchet1[$i][4]
		EndIf
	Next

	If $otchet1[1][1] = 'перегрузка' Then
		$mOtchet[$y][2] = $koltemp / 60
	Else
		$mOtchet[$y][1] = $koltemp / 60
	EndIf
	; $Otchet1 = это готовый массив по каждому файлу, дальше работате как с массивами.
	; $mOtchet в этот массив сводите значения, которые будут записываться в EXCEL
	$mOtchet[$y][0] = $FileList[$y]
	$koltemp = 0

Next
_ArrayDisplay($mOtchet)

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookNew($oExcel)
If @error Then
	MsgBox(0, "error", "error")
	_Excel_Close($oExcel)
	Exit
EndIf

_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $mOtchet, 'e6')

Func readexcsel($fName)
	Local $sWorkbook = @ScriptDir & '/' & $fName
	Local $sResult = FileRead($sWorkbook)
	ConsoleWrite($sResult)
	$sResult = StringRegExp($sResult, '(.*?)\r', 3)
	Dim $mResult[UBound($sResult)][7]
	Local $tempSplit
	For $i = 0 To UBound($sResult) - 1
		$tempSplit = StringSplit($sResult[$i], ',', 2)
		For $j = 0 To 6
			$mResult[$i][$j] = $tempSplit[$j]
		Next
	Next
	Return ($mResult)
EndFunc   ;==>readexcsel

По сути осталось только доработать. Выполнить математические действия в массиве.

Yriy_07 · 4 Июн 2018

Спасибо да! считывает в массив. Но время 30 сек считает как 0,5 а не как 0,3 и не подсчитывает Итого и не вписывает время и точки в детализацию как в файле Книга 1 с примером ну и не высчитывает проценты я написал обработку одной строки для процентов с проверкой на пустое поле если поле не пустое он продолжает высчитывать следующую строчку. Но лучше наверное чтобы это в цикле отрабатывало и почему то 0 в ячейке он видит как пустую..в моем скрипте. Филиалов этих более сотни а в месяц по некоторым из них некоторых по детализации более 400 строк.
Добавлено:
Сообщение автоматически объединено: 4 Июн 2018

gunter123 сказал(а):
Что такое время недоступности / перегрузки? Как понять, в какую из колонок писать?

Есть два типа файлов csv С буквой N и с буквой P
P- это по мощности перегрузка
N- это не доступность

Берется файл с N или P без разницы какой первый, время начало проблемы и завершения вписывается в детализацию Книга1, Название узла соответствует названию файла только без N или P и без расширения. суммируется время с проблемой и вписывается общее время в книга 1 в отчете. Потом все переводится в проценты

gunter123 · 4 Июн 2018

Вот это должно работать ;) Можете еще поиграться с форматированием, объединением ячеек

Код:

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

$sPath = @ScriptDir                             ; Путь к папке с файлами Excel
$sTotalName = "Итог.xlsx"                       ; Название итогового файла Excel

Local $iN = 0, $iP = 0							; Переменные для подсчета общего времени недоступности / перегрузки
Dim $aFirstReport[2][8] = [['SLA' & @CRLF & '(30*24*60)', 'Точка', 'Время  недоступности (мин)', _
						    'Время перегрузки', '% недоступность ', '% перегрузка', '% суммарная недоступность', '% суммарная доступность'], [43200]]
Dim $aSecondReport[2][6] = [['№', 'Начало', 'Конец', 'Продолжительность (мин)', 'Узел', 'Тип']]   ; Массивы для отчетов
$iCount = 1                                     ; Переменная для конечного массива
$iTotalTime = 0									; Общее время
$aList = _FileListToArray($sPath, '*.csv', 1)   ; Берем список xlsx файлов в папке
For $i = UBound($aList) -1 To 0 Step -1			; Оставляем только те файлы, в конце названия которых есть P или N
   If Not StringRegExp($aList[$i], '[P|N]\.csv') Then
	  _ArrayDelete($aList, $i)
   EndIf
Next
$oExcel = _Excel_Open()                         ; Открываем Excel

For $iFile = 0 To UBound($aList) -1             ; Для каждого файла...
   $oBook  = _Excel_BookOpen($oExcel, $sPath & "\" & $aList[$iFile])
   $aRange = _Excel_RangeRead($oBook)           ; Читаем в массив содержимое файла Excel
   $iTime = 0
   $sType = StringRegExp($aList[$iFile], '(.)\.', 3)[0]	; Определяем тип файла ('P'/'N')
   For $iRow = 1 To UBound($aRange) -1 Step 2   ; Проходим по каждой строчке массива, первую строчку пропускаем
	  ReDim $aSecondReport[$iCount + 1][6]		; Добавляем строку во второй отчет
      If $aRange[$iRow] = "" Then ContinueLoop  ; Пропускаем пустые строки
      $aCurrent = StringSplit($aRange[$iRow], ',', 3)	; Разбиваем текущую строку по разделителю в массив
      $aNext = StringSplit($aRange[$iRow+1], ',', 3)	; И следующую
	  $iTime += _ToSecond($aNext[4])
	  $aSecondReport[$iCount][0] = $iCount				; Записываем номер
	  $aSecondReport[$iCount][1] = $aNext[0]            ; Записываем в первую колонку дату в следующей строчке
      $aSecondReport[$iCount][2] = $aCurrent[0]          ; Во вторую колонку - дату в текущей строчке
      $aSecondReport[$iCount][3] = _SecondToMinutes(_ToSecond($aNext[4]))  ; В четвертую колонку записываем время в минутах
      $aSecondReport[$iCount][4] = StringLeft($aList[$iFile], StringInStr($aList[$iFile], '-', 0, -1) -1) ; Название узла
      If $sType = 'N' Then								; Записываем Тип
		 $aSecondReport[$iCount][5] = 'Недоступность'
	  Else
		 $aSecondReport[$iCount][5] = 'Перегрузка'
	  EndIf
	  $iCount += 1
   Next
   $aFirstReport[$iFile + 1][1] = StringLeft($aList[$iFile], StringInStr($aList[$iFile], '-', 0, -1) -1)	; Записываем название файла в первый отчет
   If $sType = 'N' Then																						; А также время перегрузки/недоступности
	  $aFirstReport[$iFile + 1][2] = _SecondToMinutes($iTime)
	  $aFirstReport[$iFile + 1][3] = "0"
   Else
	  $aFirstReport[$iFile + 1][2] = "0"
	  $aFirstReport[$iFile + 1][3] = _SecondToMinutes($iTime)
   EndIf
   $iN += StringReplace($aFirstReport[$iFile + 1][2], ',', '.')				; Подсчитываем суммарную недоступность
   $iP += StringReplace($aFirstReport[$iFile + 1][2], ',', '.')				; И перегрузку
   ReDim $aFirstReport[UBound($aFirstReport) + 1][8]
   _Excel_BookClose($oBook)                         ; Закрываем книгу
Next

For $i = 1 To UBound($aFirstReport) -1
   $aFirstReport[$i][6] = Round(StringReplace($aFirstReport[$i][2], ',', '.') / $iN * 100, 2) & '%'		; Записываем % от общего времени перегрузки и недоступности
   $aFirstReport[$i][7] = Round(StringReplace($aFirstReport[$i][3], ',', '.') / $iP * 100, 2) & '%'
Next
;_ArrayDisplay($aFirstReport)

$oBook = _Excel_BookOpen($oExcel, $sPath & "\" & $sTotalName)
If @error Then $oBook = _Excel_BookNew($oExcel)
$oExcel.Columns('A:L').EntireColumn.Delete                ; Удаляем все из файла Excel
_Excel_RangeWrite($oBook, Default, $aFirstReport, "D6")
_Excel_RangeWrite($oBook, Default, $aSecondReport, "D" & (6 + UBound($aSecondReport)))
$oExcel.Columns('A:L').EntireColumn.AutoFit            ; Расширяем колонки по длине текста
_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 · 4 Июн 2018

Отрабатывает отлично, спасибо большое! Но почему то проценты не правильно просчитывает и не во всех полях

gunter123 · 4 Июн 2018

Проценты считает только для суммарной доступности/недоступности. Насколько я понял, в этой ячейке должна быть часть в % от общего времени доступности/недоступности. Для строчек с заголовками "% недоступность " / "% перегрузка" пока нет никаких расчетов, поэтому они остаются пустыми

Yriy_07 · 4 Июн 2018

Вот то так отрабатывает скрипт сейчас
C :rofl:

5 Время недоступности (мин) 40,3, Время перегрузки 0, % недоступность (нет данных), % перегрузка (нет данных), % суммарная недоступность 47.64%, % суммарная доступность 0%
а так должно быть:
C :rofl:

5 Время недоступности (мин) 40,3, Время перегрузки 0, % недоступность 0,0933, % перегрузка 0,0000, % суммарная недоступность 0,0933, % суммарная доступность 99,9067

Сначала sla делится на время недоступности затем 100 нужно разделить на полученную сумму это будет % перегрузка. % суммарная недоступность складывается - % недоступность 0,0933, % + перегрузка 0,0000, получаем суммарная недоступность 0,0933
чтобы получить суммарную доступность нужно от 100 отнять суммарую недоступность. Вот .. Только как в коде прописать?

gunter123 · 5 Июн 2018

Код:

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

$sPath = @ScriptDir                             ; Путь к папке с файлами Excel
$sTotalName = "Итог.xlsx"                       ; Название итогового файла Excel

Dim $aFirstReport[2][8] = [['SLA' & @CRLF & '(30*24*60)', 'Точка', 'Время  недоступности (мин)', _
						    'Время перегрузки', '% недоступность ', '% перегрузка', '% суммарная недоступность', '% суммарная доступность'], [43200]]
Dim $aSecondReport[2][6] = [['№', 'Начало', 'Конец', 'Продолжительность (мин)', 'Узел', 'Тип']]   ; Массивы для отчетов
$iCount = 1                                     ; Счетчик записанных строк
$iTotalTime = 0									; Общее время
$aList = _FileListToArray($sPath, '*.csv', 1)   ; Берем список csv файлов в папке
For $i = UBound($aList) -1 To 0 Step -1			; Оставляем только те файлы, в конце названия которых есть P или N
   If Not StringRegExp($aList[$i], '[P|N]\.csv') Then
	  _ArrayDelete($aList, $i)
   EndIf
Next
$oExcel = _Excel_Open()                         ; Открываем Excel

For $iFile = 0 To UBound($aList) -1             ; Для каждого файла...
   $oBook  = _Excel_BookOpen($oExcel, $sPath & "\" & $aList[$iFile])
   $aRange = _Excel_RangeRead($oBook)           ; Читаем в массив содержимое файла Excel
   $iTime = 0
   $sType = StringRegExp($aList[$iFile], '(.)\.', 3)[0]	; Определяем тип файла ('P'/'N')
   For $iRow = 1 To UBound($aRange) -1 Step 2   ; Проходим по каждой строчке массива, первую строчку пропускаем
	  ReDim $aSecondReport[$iCount + 1][6]		; Добавляем строку во второй отчет
      If $aRange[$iRow] = "" Then ContinueLoop  ; Пропускаем пустые строки
      $aCurrent = StringSplit($aRange[$iRow], ',', 3)	; Разбиваем текущую строку по разделителю в массив
      $aNext = StringSplit($aRange[$iRow+1], ',', 3)	; И следующую
	  $iTime += _ToSecond($aNext[4])
	  $aSecondReport[$iCount][0] = $iCount				; Записываем номер
	  $aSecondReport[$iCount][1] = $aNext[0]            ; Записываем в первую колонку дату в следующей строчке
      $aSecondReport[$iCount][2] = $aCurrent[0]          ; Во вторую колонку - дату в текущей строчке
      $aSecondReport[$iCount][3] = _SecondToMinutes(_ToSecond($aNext[4]))  ; В четвертую колонку записываем время в минутах
      $aSecondReport[$iCount][4] = StringLeft($aList[$iFile], StringInStr($aList[$iFile], '-', 0, -1) -1) ; Название узла
      If $sType = 'N' Then								; Записываем Тип
		 $aSecondReport[$iCount][5] = 'Недоступность'
	  Else
		 $aSecondReport[$iCount][5] = 'Перегрузка'
	  EndIf
	  $iCount += 1
   Next
   $aFirstReport[$iFile + 1][1] = StringLeft($aList[$iFile], StringInStr($aList[$iFile], '-', 0, -1) -1)	; Записываем название файла в первый отчет
   If $sType = 'N' Then																						; А также время перегрузки/недоступности
	  $aFirstReport[$iFile + 1][2] = _SecondToMinutes($iTime)
	  $aFirstReport[$iFile + 1][3] = "0"
   Else
	  $aFirstReport[$iFile + 1][2] = "0"
	  $aFirstReport[$iFile + 1][3] = _SecondToMinutes($iTime)
   EndIf
   ReDim $aFirstReport[UBound($aFirstReport) + 1][8]
   _Excel_BookClose($oBook)                         ; Закрываем книгу
Next

For $i = 1 To UBound($aFirstReport) -1
   $aFirstReport[$i][4] = Round(StringReplace($aFirstReport[$i][2], ',', '.') / 43200 * 100, 4)
   $aFirstReport[$i][5] = Round(StringReplace($aFirstReport[$i][3], ',', '.') / 43200 * 100, 4)
   $aFirstReport[$i][6] = $aFirstReport[$i][4] + $aFirstReport[$i][5]
   $aFirstReport[$i][7] = 100.0 - $aFirstReport[$i][6]
   $aFirstReport[$i][4] &= '%'
   $aFirstReport[$i][5] &= '%'
   $aFirstReport[$i][6] &= '%'
   $aFirstReport[$i][7] &= '%'
Next

$oBook = _Excel_BookOpen($oExcel, $sPath & "\" & $sTotalName)
If @error Then $oBook = _Excel_BookNew($oExcel)
$oExcel.Columns('A:L').EntireColumn.Delete
_Excel_RangeWrite($oBook, Default, $aFirstReport, "D6")
_Excel_RangeWrite($oBook, Default, $aSecondReport, "D" & (6 + UBound($aSecondReport)))
$oExcel.Columns('A:L').EntireColumn.AutoFit
_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 · 5 Июн 2018

Огромное спасибо! Это то что нужно!!! :smile:

:IL_AutoIt_1:

Автор темы	Похожие темы	Форум	Ответы	Дата
T	[Автоматизация] Захват и обработка изображения, удаление временных файлов	Общие вопросы по AutoIt	1	15 Янв 2018
H	[Автоматизация] Обработка текстовой информации	Покупка/продажа, поиск услуг	1	21 Сен 2016
	[Автоматизация] pop.ya.ru Чтение новой почты и ее обработка	Общие вопросы по AutoIt	5	4 Май 2014
	[Автоматизация] [Сеть, Интернет, IE] Автоматическая обработка документа до его загрузки	Автоматизация IE и Web-интерфейса	1	27 Апр 2014
K	[Автоматизация] Обработка PDF через командную строку	Стол заказов	1	18 Окт 2012

Поиск

Поиск

[Автоматизация] Обработка файла excel

Yriy_07

Новичок

Вложения

gunter123

Продвинутый

mr.Gbabak

Осваивающий

Yriy_07

Новичок

gunter123

Продвинутый

Yriy_07

Новичок

gunter123

Продвинутый

Yriy_07

Новичок

gunter123

Продвинутый

Yriy_07

Новичок