Форматирование excel файла

[AlexVong]

Друзья прошу помощи.
Имеется таблица с известным количеством столбцов = 17, но неизвестным количеством строк.
Как для такой таблицы установить границы? А также необходимо установить цвет шрифта в строке в зависимости от значения в ячейке "R*" (если значение 000100 - красный, 000300 - синий, пусто - черный ), с последующим удалением колонки "R"? Заранее спасибо!

 

[madmasles]

AlexVong,
Дайте примеры excel-файлов: исходник и то, что нужно получить.

 

[AlexVong]

небольшой пример :-\ , реальные файлы бывают по 10-20 тыс строк ;)

 

[madmasles]

AlexVong,
Попробуйте так. Проверял только на Excel 2003.

Спойлер

#include <Excel.au3>

Global Const $xlLastCell = 11

Global $sFileExcel = @ScriptDir & '\test.xls', $oObjEr, $oExcel, $sList = 'Тест', $oList, $oRange, $iLastColumn, $iLastRow, $aColor

$oObjEr = ObjEvent('AutoIt.Error', '_Obj_Error')

$oExcel = _ExcelBookOpen($sFileExcel)
$oList = $oExcel.Sheets($sList)
$iLastColumn = $oList.Cells.SpecialCells($xlLastCell).Column
If Not $iLastColumn Then Exit 13
$iLastRow = $oList.Cells.SpecialCells($xlLastCell).Row
If Not $iLastRow Then Exit 13
$oRange = $oList.Range($oList.Cells(1, 1), $oList.Cells($iLastRow, $iLastColumn - 1))
For $i = $xlEdgeLeft To $xlInsideHorizontal
	With $oRange.Borders($i)
		.LineStyle = $xlContinuous
	EndWith
Next
$oRange = $oList.Range($oList.Cells(1, $iLastColumn), $oList.Cells($iLastRow, $iLastColumn))
$aColor = $oExcel.Application.WorksheetFunction.Transpose($oRange.Value)
$oRange = 0
For $i = 0 To $iLastRow - 1
	Switch $aColor[$i]
		Case '000100'
			$oList.Range($oList.Cells($i + 1, 1), $oList.Cells($i + 1, $iLastColumn - 1)).Font.ColorIndex = 3;red
		Case '000300'
			$oList.Range($oList.Cells($i + 1, 1), $oList.Cells($i + 1, $iLastColumn - 1)).Font.ColorIndex = 5;blue
	EndSwitch
Next
$aColor = 0
$oList.Range($oList.Cells(1, $iLastColumn), $oList.Cells($iLastRow, $iLastColumn)).ClearContents
;~ _ExcelBookSave($oExcel)
;~ _ExcelBookClose($oExcel)

Func _Obj_Error()
	ConsoleWrite('Object error number: ' & $oObjEr.number & @LF)
EndFunc   ;==>_Obj_Error

На большом файле очень долго.
Можно попробовать сделать с условным форматированием. Быстрее во много раз.

Спойлер

#include <Excel.au3>

Opt('MustDeclareVars', 1)

Global Const $xlLastCell = 11, $xlExpression = 2

Global $sFileExcel = @ScriptDir & '\1_test.xls', $oObjEr, $oExcel, $sList = 'Тест', $oRange, $iLastColumn, $iLastRow, _
		$sRed = '"000100"', $sBlue = '"000300"', $iTimer

If Not FileExists($sFileExcel) Then Exit @ScriptLineNumber
$oObjEr = ObjEvent('AutoIt.Error', '_Obj_Error')
$iTimer = TimerInit()
$oExcel = _ExcelBookOpen($sFileExcel)
If @error Then Exit @ScriptLineNumber - 1
_ExcelSheetActivate($oExcel, $sList)
If @error Then Exit @ScriptLineNumber - 1
$iLastColumn = $oExcel.Cells.SpecialCells($xlLastCell).Column
If Not $iLastColumn Then Exit @ScriptLineNumber - 1
$iLastRow = $oExcel.Cells.SpecialCells($xlLastCell).Row
If Not $iLastRow Then Exit @ScriptLineNumber - 1
$oRange = $oExcel.Range($oExcel.Cells(1, 1), $oExcel.Cells($iLastRow, $iLastColumn - 1))
For $i = $xlEdgeLeft To $xlInsideHorizontal
	With $oRange.Borders($i)
		.LineStyle = $xlContinuous
	EndWith
Next
$oExcel.Columns($iLastColumn).EntireColumn.Hidden = True
$oExcel.Range('A1').Select
With $oRange
	.FormatConditions.Delete
	.FormatConditions.Add($xlExpression, 0, '=$' & Chr(64 + $iLastColumn) & '1=' & $sRed)
	.FormatConditions(1).Font.ColorIndex = 3
	.FormatConditions.Add($xlExpression, 0, '=$' & Chr(64 + $iLastColumn) & '1=' & $sBlue)
	.FormatConditions(2).Font.ColorIndex = 5
EndWith
;~ _ExcelBookSave($oExcel)
;~ _ExcelBookClose($oExcel)
ConsoleWrite(TimerDiff($iTimer) & @LF)

Func _Obj_Error()
	ConsoleWrite('Scriptline :' & $oObjEr.scriptline & @LF)
EndFunc   ;==>_Obj_Error

Добавлено:

Сообщение автоматически объединено: 24 Окт 2013

Так, вроде, еще чуть быстрее.

Спойлер

#include <Excel.au3>

Opt('MustDeclareVars', 1)

Global Const $xlLastCell = 11, $xlExpression = 2

Global $sFileExcel = @ScriptDir & '\1_test.xls', $oObjEr, $oExcel, $sList = 'Тест', $oRange, $iLastColumn, $iLastRow, _
		$aBorders[5] = [4, $xlLeft, $xlRight, $xlTop, $xlBottom], $iTimer = TimerInit()

If Not FileExists($sFileExcel) Then Exit @ScriptLineNumber
$oObjEr = ObjEvent('AutoIt.Error', '_Obj_Error')
$oExcel = _ExcelBookOpen($sFileExcel)
If @error Then Exit @ScriptLineNumber - 1
_ExcelSheetActivate($oExcel, $sList)
If @error Then Exit @ScriptLineNumber - 1
$iLastColumn = $oExcel.Cells.SpecialCells($xlLastCell).Column
If Not $iLastColumn Then Exit @ScriptLineNumber - 1
$iLastRow = $oExcel.Cells.SpecialCells($xlLastCell).Row
If Not $iLastRow Then Exit @ScriptLineNumber - 1
$oRange = $oExcel.Range($oExcel.Cells(1, 1), $oExcel.Cells($iLastRow, $iLastColumn - 1))
$oExcel.Columns($iLastColumn).EntireColumn.Hidden = True
$oExcel.Range('A1').Select
Local $aColor[4][2] = [[3],['=$' & Chr(64 + $iLastColumn) & '1="000100"', 3],['=$' & Chr(64 + $iLastColumn) & '1="000300"', 5],['=A1<>""']]
With $oRange
	.FormatConditions.Delete
	For $j = 1 To $aColor[0][0]
		.FormatConditions.Add($xlExpression, 0, $aColor[$j][0])
		If $j <> $aColor[0][0] Then .FormatConditions($j).Font.ColorIndex = $aColor[$j][1]
		For $i = 1 To $aBorders[0]
			.FormatConditions($j).Borders($aBorders[$i]).LineStyle = $xlContinuous
		Next
	Next
EndWith
;~ _ExcelBookSave($oExcel)
;~ _ExcelBookClose($oExcel)
ConsoleWrite(TimerDiff($iTimer) & @LF)

Func _Obj_Error()
	ConsoleWrite('Scriptline :' & $oObjEr.scriptline & @LF)
EndFunc   ;==>_Obj_Error

 

[AlexVong]

Спасибо... работает отлично... :beer:

 

[tech-gs]

Вопрос по этой же теме. Так же прошу помощи.

Приведенные в теме примеры работают отлично. Работают так же правила типа "значение ячейки ...", например:

Local Const $xlCellValue=1, $xlExpression=2, $xlTextString=9
Local Const $xlEqual=3, $xlBeginsWith=2, $xlBetween=1

$oExcel.Selection.FormatConditions.Add($xlCellValue, $xlBetween, "=1", "=2")
; Код, генерируемый в VBA:
; Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="=1", Formula2:="=2"

Но вот заставить AutoIt работать с правилами типа "текст содержит ..." не получается ни как (в системе установлен MS Office 2013 Std).

В VBA генерируется код:

Selection.FormatConditions.Add Type:=xlTextString, String:="IP", TextOperator:=xlBeginsWith

В теме "FormatConditions.Add Method (Excel)" на сайте MSDN (http://msdn.microsoft.com/en-us/library/office/ff822801%28v=office.15%29.aspx) про xlBeginsWith вообще ни слова.

Кому-нибудь удавалось заставить работать подобные правила или это глюк Excel?