Сравнение Excel таблиц

Asshaker · 15 Июн 2012

Доброго времени суток! Вчера получил идиотское задание от вышестоящего руководства. У них зуб на меня :smile:

видимо хотят измором взять. Таблицы эти являются выборкой из 2х разных баз, задача выявить расхождение данных.
Вот скрипт:

Код:

#include <Excel.au3>
#include <Array.au3>
Local $sFilePath_1 = "d:\AutoIT_excel_workplace\EOD_Im.xls" ;путь к первому файлу
Local $sFilePath_2 = "d:\AutoIT_excel_workplace\FIR_im.xls" ;путь к файлу в котором ищем записи
Local $sFilePath_3 = "d:\AutoIT_excel_workplace\All_Im.xls" ;путь к файлу с итогами выборки
Local $oExcel_1 = _ExcelBookOpen($sFilePath_1,0)
Local $oExcel_2 = _ExcelBookOpen($sFilePath_2,0)
Local $oExcel_3 = _ExcelBookOpen($sFilePath_3,0)
Local $Arr_1=_ExcelReadArray($oExcel_1,1,2,4887,1)
Local $ArrDateIn_1=_ExcelReadArray($oExcel_1,1,3,4887,1)
Local $ArrDateOut_1=_ExcelReadArray($oExcel_1,1,4,4887,1)
Local $Arr_2=_ExcelReadArray($oExcel_2,1,2,2500,1)
Local $ArrDateIn_2=_ExcelReadArray($oExcel_2,1,3,2500,1)
Local $ArrDateOut_2=_ExcelReadArray($oExcel_2,1,4,2500,1)
Dim $count=0
For $i=0 to UBound($Arr_1)-1
   For $j=0 to UBound($Arr_2)-1
	  If $Arr_1[$i]=$Arr_2[$j] Then
		 _ExcelWriteCell($oExcel_3,$Arr_1[$i],$i+2,1)
		 _ExcelWriteCell($oExcel_3,$ArrDateIn_1[$i],$i+2,2)		 
		 _ExcelWriteCell($oExcel_3,$ArrDateOut_1[$i],$i+2,3)
		 _ExcelWriteCell($oExcel_3,$ArrDateIn_2[$j],$i+2,4)
		 _ExcelWriteCell($oExcel_3,$ArrDateOut_2[$j],$i+2,5)
		 _ExcelWriteCell($oExcel_3,$Arr_2[$j],$i+2,6)
		 $count+=1
	  EndIf
   Next   
Next
MsgBox(0,0,$count) 
_ExcelBookClose($oExcel_1)
_ExcelBookClose($oExcel_2)
_ExcelBookClose($oExcel_3)

Помогите пожалуйста выявить номера(второй столбец) которые есть в FIR_Im, но нет в EOD_Im
З.Ы. еще вопрос про _ExcelReadArray можно ли записать массив без указания размера?
Т.к. размеры таблиц будут разные и постоянно править эту запись не феншуй :smile:

vcomp71 · 15 Июн 2012

Результатом сравнения что будет? Таблица не найденных элементов, таблица найденных элементов?

Asshaker · 15 Июн 2012

Нужны результаты:
1 общение кпп в 2х таблицах
2 кпп которые есть в таблице EOD_im, но нет в FIR_im
3 кпп которые есть в FIR_im, но нет в EOD_im
хотя вроде уже разобрался.
Но очень хочется разобраться с размерами массивов

Redline · 17 Июн 2012

Количество занятых строк в таблице:

Код:

#include <Excel.au3>
Local $sFilePath_1 = "c:\EOD_Im.xls"
Local $oExcel_1 = _ExcelBookOpen($sFilePath_1,0)
$iCount = $oExcel_1.ActiveSheet.UsedRange.Rows.Count
ConsoleWrite($iCount & @CRLF)
_ExcelBookClose($oExcel_1)

По сравнениям могу только посоветовать сначала делать _ArrayUnique(), чтобы убрать повторяющиеся строки в одном массиве, а потом уже проводить вычитание массивов.
Можно почитать темы про ObjCreate('Scripting.Dictionary') здесь на форуме, может подойдет :smile:

Asshaker · 17 Июн 2012

Код:

_ArrayUnique

Спасибо за функцию. Как раз думал как убрать дубли из таблиц.
И поясни пожалуйста, что тут делаем?

Код:

$iCount = $oExcel_1.ActiveSheet.UsedRange.Rows.Count
ConsoleWrite($iCount & @CRLF)

Redline · 18 Июн 2012

Код:

$iCount = $oExcel_1.ActiveSheet.UsedRange.Rows.Count

Это обращение к объекту эксель, где через точки перечислены его свойства к которым мы обращаемся, т.е. активный лист, использованный диапазон (Rows строк), и count содержит их количество.
О методах Экселя можно погуглить на тему "VBA Excel", там есть множество разных свойств и прочего, чего не реализовано в UDF Excel.au3

Кстати ваше руководство, делая выгрузки из базы, могло сразу вывести все что нужно, используя SQL-запросы, но похоже ему лень :smile:

Вот пример как можно прогнать ваши данные через SQLite-базу (при условии что нам нужен только один столбец):

Код:

#include <SQLite.au3>
#include <Array.au3>
_SQLite_Startup()
$sDb_1 = 'c:\1.db'; файл для базы создастся сам
Dim $aR, $iRows, $iColumns
Dim $aList_1[10] = [0,1,2,'n',4,'m',6,7,8,9]; массив 1
Dim $aList_2[11] = [0,1,'a',3,4,5,6,7,8,'z',0]; массив 2
_ArrayDisplay($aList_1, '$aList_1')
_ArrayDisplay($aList_2, '$aList_2')

_SQLite_Open($sDb_1)
_SQLite_Exec(-1, 'CREATE TABLE IF NOT EXISTS t1 (r1 TEXT UNIQUE ON CONFLICT IGNORE);'); создаем таблицы с одним столбцом и уникальным ключом
_SQLite_Exec(-1, 'CREATE TABLE IF NOT EXISTS t2 (r1 TEXT UNIQUE ON CONFLICT IGNORE);'); этот ключ проигнорирует вставку повторяющихся значений
For $i = 0 To UBound($aList_1) - 1; заполнение первой таблицы
	_SQLite_Exec(-1, 'BEGIN;');начало транзакции (транзакция ускоряет построчную вставку данных)
	_SQLite_Exec(-1, 'INSERT INTO t1 VALUES ("' & $aList_1[$i] & '");');непосредственно вставка
	_SQLite_Exec(-1, 'COMMIT;');конец транзакции
Next
For $i = 0 To UBound($aList_2) - 1;вторая таблица...
	_SQLite_Exec(-1, 'BEGIN;')
	_SQLite_Exec(-1, 'INSERT INTO t2 VALUES ("' & $aList_2[$i] & '");')
	_SQLite_Exec(-1, 'COMMIT;')
Next

$sSql = 'SELECT t1.r1 FROM t1, t2 WHERE t1.r1=t2.r1';запрос на выборку одинаковых значений для столбцов r1
_SQLite_GetTable2d(-1, $sSql, $aR, $iRows, $iColumns)
_ArrayDisplay($aR, '$aList_1 = $aList_2')
$sSql = 'SELECT t1.r1 FROM t1 WHERE r1 NOT IN (SELECT t2.r1 FROM t2)';запрос на выборку разницы между таблицей 1 и таблицей 2
_SQLite_GetTable2d(-1, $sSql, $aR, $iRows, $iColumns)
_ArrayDisplay($aR, '$aList_1 - $aList_2')
$sSql = 'SELECT t2.r1 FROM t2 WHERE r1 NOT IN (SELECT t1.r1 FROM t1)';запрос на выборку разницы между таблицей 2 и таблицей 1
_SQLite_GetTable2d(-1, $sSql, $aR, $iRows, $iColumns)
_ArrayDisplay($aR, '$aList_2 - $aList_1')

_SQLite_Close(-1); закрытие базы
_SQLite_Shutdown()
FileDelete($sDb_1); удаление файла базы

Asshaker · 21 Июн 2012

Треснул череп изза этого _ArrayUnique
___________________________
Стоило сюда написать, как мысля полетела и все получилось :smile:

Добавлено:
Сообщение автоматически объединено: 21 Июн 2012

Хитрое руководство задумало коварство...
Хочет знать какие кпп повторяются в таблицах. Предвкушая доп вопрос, сколько раз они повторяются.
Я ума не приложу, как реализовать это?
Помогите пожалуйста!

Redline · 22 Июн 2012

Asshaker [?]

какие кпп повторяются в таблицах. Предвкушая доп вопрос, сколько раз они повторяются

Используя все тот же SQLite (работать с массивами напрямую лень):

Код:

#include <SQLite.au3>
#include <Array.au3>
_SQLite_Startup()
$sDb_1 = 'c:\1.db'; файл для базы создастся сам
Dim $aR, $iRows, $iColumns
Dim $aList_1[15] = [0,1,2,'n',4,'m',6,7,8,9,0,0,4,4,2]; массив
_ArrayDisplay($aList_1)

_SQLite_Open($sDb_1)
_SQLite_Exec(-1, 'CREATE TABLE IF NOT EXISTS t1 (r1 TEXT);'); создаем таблицу с одним столбцом
For $i = 0 To UBound($aList_1) - 1; заполнение первой таблицы
    _SQLite_Exec(-1, 'BEGIN;');начало транзакции (транзакция ускоряет построчную вставку данных)
    _SQLite_Exec(-1, 'INSERT INTO t1 VALUES ("' & $aList_1[$i] & '");');непосредственно вставка
    _SQLite_Exec(-1, 'COMMIT;');конец транзакции
Next
;запрос на выборку одинаковых значений для столбцов r1 и количества повторов + сортировка по количеству повторов
$sSql = 'SELECT r1, count(*) AS r1_count FROM t1 GROUP BY r1 HAVING count(r1) > 1 ORDER BY r1_count DESC'
_SQLite_GetTable2d(-1, $sSql, $aR, $iRows, $iColumns)
_ArrayDisplay($aR)

_SQLite_Close(-1); закрытие базы
_SQLite_Shutdown()
FileDelete($sDb_1); удаление файла базы

Asshaker · 22 Июн 2012

Спасибо за примеры с SQL, но сложно для меня :(
Вот как я решил поставленную задачу.
Сравнение таблиц:

Код:

#include <Excel.au3>
#include <Array.au3>
$sFilePath_1 = "d:\AutoIT_excel_workplace\obos_EOD.xls" ; путь к файлам
$sFilePath_2 = "d:\AutoIT_excel_workplace\obos_FIR.xls" 
$sFilePath_3 = "d:\AutoIT_excel_workplace\obos_All.xls" 
$oExcel_1 = _ExcelBookOpen($sFilePath_1,0) ; открываем книжки 
$oExcel_2 = _ExcelBookOpen($sFilePath_2,0)
$oExcel_3 = _ExcelBookOpen($sFilePath_3,0)
$row_1=$oExcel_1.ActiveSheet.UsedRange.Rows.Count ; смотрим скока строк в книжках
$row_2=$oExcel_2.ActiveSheet.UsedRange.Rows.Count
$ArrKPP_1=_ExcelReadArray($oExcel_1,1,2,$row_1,1) ; берем массивы по столбцам
$ArrDateIn_1=_ExcelReadArray($oExcel_1,1,3,$row_1,1)
$ArrDateOut_1=_ExcelReadArray($oExcel_1,1,4,$row_1,1)
$ArrKPP_2=_ExcelReadArray($oExcel_2,1,2,$row_2,1)
$ArrDateIn_2=_ExcelReadArray($oExcel_2,1,3,$row_2,1)
$ArrDateOut_2=_ExcelReadArray($oExcel_2,1,4,$row_2,1)
$uArrKPP_1=_ArrayUnique($ArrKPP_1) ; убераем дубли
$uArrKPP_2=_ArrayUnique($ArrKPP_2)
;############################################
;## сравение данных ЭОД относительно ФИРа  ##
;############################################
For $i=1 to UBound($uArrKPP_1)-1
   _ExcelWriteCell($oExcel_3,$uArrKPP_1[$i],$i+1,1)   
   For $j=0 to UBound($ArrKPP_1)-1
	  If $uArrKPP_1[$i]=$ArrKPP_1[$j] Then
		 _ExcelWriteCell($oExcel_3,$ArrDateOut_1[$j],$i+1,2)
	  EndIf
   Next
   For $k=0 to UBound($ArrKPP_2)-1
	  If $uArrKPP_1[$i]=$ArrKPP_2[$k] Then
		 _ExcelWriteCell($oExcel_3,$ArrKPP_2[$k],$i+1,4)
		 _ExcelWriteCell($oExcel_3,$ArrDateOut_2[$k],$i+1,3)
	  EndIf
   Next
Next
;############################################
;## сравнение данных ФИР относительно ЭОДа ##
;############################################
For $m=1 to UBound($uArrKPP_2)-1
   _ExcelWriteCell($oExcel_3,$uArrKPP_2[$m],$m+1,6)
   For $n=0 to UBound($ArrKPP_2)-1
	  If $uArrKPP_2[$m]=$ArrKPP_2[$n] Then
		 _ExcelWriteCell($oExcel_3,$ArrDateOut_2[$n],$m+1,7)
	  EndIf	  
   Next
   For $o=0 to UBound($ArrKPP_1)-1
	  If $uArrKPP_2[$m]=$ArrKPP_1[$o] Then
		 _ExcelWriteCell($oExcel_3,$ArrDateOut_1[$o],$m+1,8)
		 _ExcelWriteCell($oExcel_3,$ArrKPP_1[$o],$m+1,9)
	  EndIf
   Next
Next
;###############################################
;# Тут закрываем книги с сохранением изменения #
;###############################################
_ExcelBookClose($oExcel_1)
_ExcelBookClose($oExcel_2)
_ExcelBookClose($oExcel_3)

Второй скрипт ищет дубли:

Код:

#include <Excel.au3>
#include <Array.au3>
$sFilePath_1 = "d:\AutoIT_excel_workplace\obos_EOD.xls" 
$sFilePath_2 = "d:\AutoIT_excel_workplace\obos_FIR.xls" 
$sFilePath_3 = "d:\AutoIT_excel_workplace\obos_All.xls" 
$oExcel_1 = _ExcelBookOpen($sFilePath_1,0)
$oExcel_2 = _ExcelBookOpen($sFilePath_2,0)
$oExcel_3 = _ExcelBookOpen($sFilePath_3,0)
$row_1=$oExcel_1.ActiveSheet.UsedRange.Rows.Count
$row_2=$oExcel_2.ActiveSheet.UsedRange.Rows.Count
$ArrKPP_1=_ExcelReadArray($oExcel_1,1,2,$row_1,1)
$ArrKPP_2=_ExcelReadArray($oExcel_2,1,2,$row_2,1)
$uArrKPP_1=_ArrayUnique($ArrKPP_1)
$uArrKPP_2=_ArrayUnique($ArrKPP_2)
;############################################
;## Ищем дубли в ФИРе                      ##
;############################################
For $i=0 to UBound($uArrKPP_2)-1
   $count=0
   For $j=0 to UBound($ArrKPP_2)-1
	  If $uArrKPP_2[$i]=$ArrKPP_2[$j] Then
		 $count+=1
	  EndIf
   Next
   If $count>1 Then
	  _ExcelWriteCell($oExcel_3,$uArrKPP_2[$i],$i+1,11)
	  _ExcelWriteCell($oExcel_3,$count,$i+1,12)
   EndIf
Next
;############################################
;## Ищем дубли в ЭОДе                      ##
;############################################
For $m=0 to UBound($uArrKPP_1)-1
   $count=0
   For $n=0 to UBound($ArrKPP_1)-1
	  If $uArrKPP_1[$m]=$ArrKPP_1[$n] Then
		 $count+=1
	  EndIf
   Next
   If $count>1 Then
	  _ExcelWriteCell($oExcel_3,$uArrKPP_1[$m],$m+1,13)
	  _ExcelWriteCell($oExcel_3,$count,$m+1,14)
   EndIf
Next
;###############################################
;# Тут закрываем книги с сохранением изменения #
;###############################################
_ExcelBookClose($oExcel_1)					  
_ExcelBookClose($oExcel_2)					 
_ExcelBookClose($oExcel_3)

Redline · 23 Июн 2012

Самостоятельно решение это очень хорошо!
Один совет:
_ExcelWriteCell пишет значение в ячейку и в ваших скриптах оно используется в цикле, заполняя таблицу. Для повышения быстродействия лучше сначала сохранять данные в отдельный массив, а потом этот массив разом заливать. Можно воспользоваться _ExcelWriteArray, но он также вставляет ячейки по одной :smile:

и не умеет вставлять двумерные массивы.
Вот пример:

Код:

#include <Excel.au3>
#include <Array.au3>
$oExcel = _ExcelBookNew(1)
Dim $aList[3][100]
For $i = 0 To UBound($aList, 2) - 1
	For $j = 0 To UBound($aList) - 1
		$aList[$j][$i] = $i & '_' & $j
	Next
Next
_ArrayDisplay($aList)
With $oExcel
	.ActiveSheet.Range(.Cells(1, 1), .Cells(UBound($aList, 2), UBound($aList))).Value = $aList
	; на активном листе выделяем область по размеру массива и вставляем туда массив целиком
EndWith

Но есть одна тонкость - Эксель при вставке массива "поворачивает" его, т.е. то что выглядело как столбец в _ArrayDisplay вставится как строка. Поэтому приходится "крутить" массив перед вставкой чтобы он встал нормально. Поэтому вставка обычного одномерного массива будет выглядеть так:

Код:

#include <Excel.au3>
#include <Array.au3>
$oExcel = _ExcelBookNew(1)
Dim $aList[1][100];создаем двумерный массив с одним элементом и сотней индексов в нем
For $i = 0 To UBound($aList, 2) - 1
	$aList[0][$i] = $i
Next
_ArrayDisplay($aList)
With $oExcel
	.ActiveSheet.Range(.Cells(1, 1), .Cells(UBound($aList, 2), UBound($aList))).Value = $aList
EndWith

Автор темы	Похожие темы	Форум	Ответы	Дата
W	Сравнение скорости Python и AutoIt	Общие вопросы по AutoIt	1	21 Окт 2024
	Время, дата Сравнение даты-времени	Общие вопросы по AutoIt	9	20 Окт 2022
R	Данные, строки Сравнение с игнорированием 1 знака	Общие вопросы по AutoIt	1	19 Авг 2019
M	Сравнение двух Excel	Общие вопросы по AutoIt	5	21 Дек 2016
I	[Excel, VB] Вопрос по макросу (сравнение, заполнение ячеек)	Общение	2	31 Мар 2014

Поиск

Поиск

Сравнение Excel таблиц

Asshaker

Знающий

vcomp71

Осваивающий

Asshaker

Знающий

Redline

AutoIT Гуру

Asshaker

Знающий

Redline

AutoIT Гуру

Asshaker

Знающий

Redline

AutoIT Гуру

Asshaker

Знающий

Redline

AutoIT Гуру