#include <Excel.au3>
Opt('MustDeclareVars', 1)
Opt('TrayMenuMode', 1)
Global $sFile_txt = @ScriptDir & '\txtfile.txt', _
$sFilePath_xls = @ScriptDir & '\excelfile.xlsx', _
$aReplace, $oExcel, $sList_Name = 'Лист1'
If Not FileExists($sFile_txt) Or Not FileExists($sFilePath_xls) Then
MsgBox(16, 'Error', 'Файлов нет')
Exit
EndIf
$aReplace = _Get_2D_Array($sFile_txt)
If @error Then
MsgBox(16, 'Error', '_Get_2D_Array')
Exit
EndIf
$oExcel = _ExcelBookOpen($sFilePath_xls)
If @error Then
MsgBox(16, 'Error', '_ExcelBookOpen')
Exit
EndIf
_Get_Column_Numbers($sList_Name, $oExcel, $aReplace)
If @error Then
MsgBox(16, 'Error', '_Get_Column_Numbers')
Exit
EndIf
_ExcelWrite($oExcel, $aReplace)
If @error Then
MsgBox(16, 'Error', '_ExcelWrite')
Exit
Else
MsgBox(64, 'Info', 'OK')
;_ExcelBookClose($oExcel)
Exit
EndIf
Func _ExcelWrite(ByRef $o_Excel, ByRef $a_Array)
Local $a_Rows
$a_Rows = _ExcelReadSheetToArray($o_Excel, 1, $a_Array[0][1], 0, 1)
If @error Then
_ExcelBookClose($o_Excel)
Return SetError(1)
EndIf
For $i = 2 To $a_Rows[0][0]
For $j = 2 To $a_Array[0][0]
If StringInStr($a_Rows[$i][1], $a_Array[$j][1]) Then
For $q = 2 To $a_Array[1][0]
_ExcelWriteCell($o_Excel, $a_Array[$j][$q], $i, $a_Array[0][$q])
If @error Then
_ExcelBookClose($o_Excel)
Return SetError(1)
EndIf
Next
EndIf
Next
Next
EndFunc ;==>_ExcelWrite
Func _Get_Column_Numbers($s_List_Name, ByRef $o_Excel, ByRef $a_Array)
Local $a_ColumnNames, $i_Error = 1
If Not $s_List_Name Then $s_List_Name = 1
For $i = 1 To 1
_ExcelSheetActivate($o_Excel, $s_List_Name)
If @error Then ExitLoop
$a_ColumnNames = _ExcelReadSheetToArray($o_Excel, 1, 1, 1)
If @error Then ExitLoop
$i_Error = 0
Next
If $i_Error Then
_ExcelBookClose($o_Excel)
Return SetError(1)
EndIf
For $i = 1 To $a_ColumnNames[0][1]
For $j = 1 To $a_Array[1][0]
If $a_ColumnNames[1][$i] = $a_Array[1][$j] Then
$a_Array[0][$j] = $i
EndIf
Next
Next
Return SetError(0)
EndFunc ;==>_Get_Column_Numbers
Func _Get_2D_Array($s_FilePath_txt)
Local $h_File, $s_Text, $a_Text, $i_Column, $a_Result, $a_Temp_Array, $i_Count
$h_File = FileOpen($s_FilePath_txt)
If $h_File = -1 Then Return SetError(1)
$s_Text = FileRead($h_File)
FileClose($h_File)
If Not $s_Text Then Return SetError(1)
$a_Text = StringSplit(StringStripCR($s_Text), @LF)
If $a_Text[0] < 2 Then Return SetError(1)
StringRegExpReplace($a_Text[1], '\h{2,}', '')
$i_Column = @extended + 1
If $i_Column < 2 Then Return SetError(1)
Dim $a_Result[$a_Text[0] + 1][$i_Column + 1]
For $i = 1 To $a_Text[0]
$a_Temp_Array = StringRegExp($a_Text[$i], '(.+?)(?:\h{2,}|$)', 3)
If Not @error Then
If UBound($a_Temp_Array) = $i_Column Then
$i_Count += 1
For $j = 0 To $i_Column - 1
$a_Result[$i_Count][$j + 1] = StringStripWS($a_Temp_Array[$j], 7)
Next
EndIf
EndIf
Next
If $i_Count Then
ReDim $a_Result[$i_Count + 1][$i_Column + 1]
$a_Result[0][0] = $i_Count
$a_Result[1][0] = $i_Column
Return SetError(0, 0, $a_Result)
Else
Return SetError(1)
EndIf
EndFunc ;==>_Get_2D_Array