Excel-『中文资料』-AUTOIT CN - Powered by Discuz! Archiver

c9992 发表于 2008-8-12 06:54:50

Excel

大家收收看囉
excel 相關操作
;====================================================================
;檔案相關
;====================================================================
; 開一個新的 excel
_ExcelBookNew($fVisible = 1)

; 開啟一個存在的檔案
_ExcelBookOpen($sFilePath, $fVisible = 1, $fReadOnly = False)

; 開啟一個文字型的象csv 那種的          $sDelimiter分隔符號預設是逗點
_ExcelBookOpenTxt($sFilePath, $sDelimiter = ",", $iStartRow = 1, $iDataType = 1, $iTextQualifier = 1, $fConsecDelim = False, $fVisible = 1)

;存檔
_ExcelBookSave($oExcel, $fAlerts = 0)

; 另存新檔
_ExcelBookSaveAs($oExcel, $sFilePath, $sType = "xls", $fAlerts = 0, $fOverWrite = 0)
#CS
            If $sType = "xls" Then $sType = $xlNormal
            If $sType = "csv" Then $sType = $xlCSVMSDOS
            If $sType = "txt" Then $sType = $xlTextWindows
            If $sType = "template" Then $sType = $xlTemplate
            If $sType = "html" Then $sType = $xlHtml
#CE

;關閉
_ExcelBookClose($oExcel, $fSave = 1, $fAlerts = 0)

; 沒用過看的見看不見
_ExcelShow($oExcel)
_ExcelHide($oExcel)

;====================================================================
;重頭戲
;====================================================================

; 讀寫資料
_ExcelWriteCell($oExcel, $sValue, $sRangeOrRow, $iColumn = 1)
_ExcelReadCell($oExcel, $sRangeOrRow, $iColumn = 1)

;寫入公式沒用過
_ExcelWriteFormula($oExcel, $sFormula, $sRangeOrRow, $iColumn = 1)

; 讀寫資料ARRAY
_ExcelWriteArray($oExcel, $iStartRow, $iStartColumn, $aArray, $iDirection = 0, $iIndexBase = 0)
_ExcelReadArray($oExcel, $iStartRow, $iStartColumn, $iNumCells, $iDirection = 0, $iIndexBase = 0)

;覆制貼上
_ExcelCopy($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1)
_ExcelPaste($oExcel, $sRangeOrRow, $iColumn = 1)

;插入沒用過
_ExcelInsert($oExcel, $sRangeOrRow, $iColumn = 1, $iShiftDirection = -4121)

; 整列操作刪列新增列 ==
_ExcelRowDelete($oExcel, $iRow, $iNumRows = 1)
_ExcelRowInsert($oExcel, $iRow, $iNumRows = 1)

; 整欄操作刪欄新增欄 ||
_ExcelColumnDelete($oExcel, $iColumn, $iNumCols = 1)
_ExcelColumnInsert($oExcel, $iColumn, $iNumCols = 1)

;====================================================================
;;其他功能
;====================================================================

; 搜尋很好用找字串 $sFindWhat
_ExcelFindInRange($oExcel, $sFindWhat, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iDataType = 0, $iWholeOrPart = 2, $fMatchCase = False, $fMatchFormat = False)

;範圍內取代
_ExcelReplaceInRange($oExcel, $sFindWhat, $sReplaceWith, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iWholeOrPart = 2, $fMatchCase = False, $fMatchFormat = False, $fReplaceFormat = False)

;====================================================================
; 格式相關
;====================================================================
;set 為設定get 為取得

; 字型相關
; 設定字型粗體斜體底線
_ExcelFontSetProperties($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $fBold = False, $fItalic = False, $fUnderline = False)

;設定字型字體
_ExcelFontSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $sFontName = "Arial")
_ExcelFontGet($oExcel, $sRangeOrRow, $iColumn = 1)

;字體大小
_ExcelFontSetSize($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iFontSize = 10)
_ExcelFontGetSize($oExcel, $sRangeOrRow, $iColumn = 1)

;字體顏色
_ExcelFontSetColor($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iColorIndex = 1, $hColor = 0x000000)
_ExcelFontGetColor($oExcel, $sRangeOrRow, $iColumn = 1)

;格子的顏色
_ExcelCellColorSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iColorIndex = 1, $hColor = 0x000000)
_ExcelCellColorGet($oExcel, $sRangeOrRow, $iColumn = 1)

;寬度
_ExcelColWidthSet($oExcel, $vColumn, $vWidth)
_ExcelColWidthGet($oExcel, $vColumn)

;高度
_ExcelRowHeightSet($oExcel, $iRow, $vHeight)
_ExcelRowHeightGet($oExcel, $iRow)

;??
_ExcelCellFormat($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $fWrapText = False, $iOrientation = 0, $fAddIndent = False, $iIndentLevel = 0, $fShrinkToFit = False)

;====================================================================
; 試算表的動作
;====================================================================
;目標指向該頁
_ExcelSheetActivate($oExcel, $vSheet)
;移動
_ExcelSheetMove($oExcel, $vMoveSheet, $vRelativeSheet = 1, $fBefore = True)
;新增
_ExcelSheetAddNew($oExcel, $sName = "")
;取得目前的名稱
_ExcelSheetNameGet($oExcel)
; 改名稱or 設定名稱
_ExcelSheetNameSet($oExcel, $sSheetName)
;刪除頁
_ExcelSheetDelete($oExcel, $vSheet, $fAlerts = False)
; 取回頁列表array
_ExcelSheetList($oExcel)

;====================================================================
;比較少用到的函數
;====================================================================

;排序沒用過
_ExcelSort($oExcel, $sKey, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iDirection = 2)
_ExcelSortExtended($oExcel, $sKey, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iDirection = 2, $iHeader = 0, $fMatchCase = False, $iOrientation = 2, $iDataOption = 0)

; 註解
_ExcelCommentAdd($oExcel, $sComment, $sRangeOrRow, $iColumn = 1, $fVisible = 0)
_ExcelCommentDelete($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1)
_ExcelCommentShow($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $fVisible = False)
_ExcelCommentRead($oExcel, $sRangeOrRow, $iColumn = 1)

;切開
_ExcelSplitWindow($oExcel, $iSplitRow, $iSplitColumn)

;不曉得怎麼用的
_ExcelNumberFormat($oExcel, $sFormat, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1)
_ExcelPictureInsert($oExcel, $sFilePath, $iLeft, $iTop, $iWidth, $iHeight, $fLinkToFile = False, $fSaveWithDoc = False)
_ExcelPictureAdjust($oPicture, $iHorizontal, $iVertical, $iRotation = 0)
_ExcelPictureScale($oPicture, $nScaleWidth = 1, $nScaleHeight = 1, $fScaleOrigWidth = True, $fScaleOrigHeight = True, $iScaleFrom = 0)
_ExcelHyperlinkInsert($oExcel, $sLinkText, $sAddress, $sScreenTip, $sRangeOrRow, $iColumn = 1)

_ExcelCellMerge($oExcel, $fDoMerge, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1)
_ExcelSheetUsedRangeGet($oExcel, $vSheet)

_ExcelHorizontalAlignSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $sHorizAlign = "left")
_ExcelVerticalAlignSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $sVertAlign = "bottom")

autoit3CN 发表于 2009-6-29 13:44:05

弄个简体版

excel 相关操作
;====================================================================
;档案相关
;====================================================================
; 开一个新的 excel
_ExcelBookNew($fVisible = 1)

; 开启一个存在的档案
_ExcelBookOpen($sFilePath, $fVisible = 1, $fReadOnly = False)

; 开启一个文字型的象csv 那种的          $sDelimiter分隔符号预设是逗点
_ExcelBookOpenTxt($sFilePath, $sDelimiter = ",", $iStartRow = 1, $iDataType = 1, $iTextQualifier = 1, $fConsecDelim = False, $fVisible = 1)

;存档
_ExcelBookSave($oExcel, $fAlerts = 0)

; 另存新档
_ExcelBookSaveAs($oExcel, $sFilePath, $sType = "xls", $fAlerts = 0, $fOverWrite = 0)
#CS
            If $sType = "xls" Then $sType = $xlNormal
            If $sType = "csv" Then $sType = $xlCSVMSDOS
            If $sType = "txt" Then $sType = $xlTextWindows
            If $sType = "template" Then $sType = $xlTemplate
            If $sType = "html" Then $sType = $xlHtml
#CE

;关闭
_ExcelBookClose($oExcel, $fSave = 1, $fAlerts = 0)

; 没用过看的见看不见
_ExcelShow($oExcel)
_ExcelHide($oExcel)

;====================================================================
;重头戏
;====================================================================

; 读写资料
_ExcelWriteCell($oExcel, $sValue, $sRangeOrRow, $iColumn = 1)
_ExcelReadCell($oExcel, $sRangeOrRow, $iColumn = 1)

;写入公式没用过
_ExcelWriteFormula($oExcel, $sFormula, $sRangeOrRow, $iColumn = 1)

; 读写资料ARRAY
_ExcelWriteArray($oExcel, $iStartRow, $iStartColumn, $aArray, $iDirection = 0, $iIndexBase = 0)
_ExcelReadArray($oExcel, $iStartRow, $iStartColumn, $iNumCells, $iDirection = 0, $iIndexBase = 0)

;覆制贴上
_ExcelCopy($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1)
_ExcelPaste($oExcel, $sRangeOrRow, $iColumn = 1)

;插入没用过
_ExcelInsert($oExcel, $sRangeOrRow, $iColumn = 1, $iShiftDirection = -4121)

; 整列操作删列新增列 ==
_ExcelRowDelete($oExcel, $iRow, $iNumRows = 1)
_ExcelRowInsert($oExcel, $iRow, $iNumRows = 1)

; 整栏操作删栏新增栏 ||
_ExcelColumnDelete($oExcel, $iColumn, $iNumCols = 1)
_ExcelColumnInsert($oExcel, $iColumn, $iNumCols = 1)

;====================================================================
;;其他功能
;====================================================================

; 搜寻很好用找字串 $sFindWhat
_ExcelFindInRange($oExcel, $sFindWhat, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iDataType = 0, $iWholeOrPart = 2, $fMatchCase = False, $fMatchFormat = False)

;范围内取代
_ExcelReplaceInRange($oExcel, $sFindWhat, $sReplaceWith, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iWholeOrPart = 2, $fMatchCase = False, $fMatchFormat = False, $fReplaceFormat = False)

;====================================================================
; 格式相关
;====================================================================
;set 为设定get 为取得

; 字型相关
; 设定字型粗体斜体底线
_ExcelFontSetProperties($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $fBold = False, $fItalic = False, $fUnderline = False)

;设定字型字体
_ExcelFontSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $sFontName = "Arial")
_ExcelFontGet($oExcel, $sRangeOrRow, $iColumn = 1)

;字体大小
_ExcelFontSetSize($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iFontSize = 10)
_ExcelFontGetSize($oExcel, $sRangeOrRow, $iColumn = 1)

;字体颜色
_ExcelFontSetColor($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iColorIndex = 1, $hColor = 0x000000)
_ExcelFontGetColor($oExcel, $sRangeOrRow, $iColumn = 1)

;格子的颜色
_ExcelCellColorSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iColorIndex = 1, $hColor = 0x000000)
_ExcelCellColorGet($oExcel, $sRangeOrRow, $iColumn = 1)

;宽度
_ExcelColWidthSet($oExcel, $vColumn, $vWidth)
_ExcelColWidthGet($oExcel, $vColumn)

;高度
_ExcelRowHeightSet($oExcel, $iRow, $vHeight)
_ExcelRowHeightGet($oExcel, $iRow)

;??
_ExcelCellFormat($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $fWrapText = False, $iOrientation = 0, $fAddIndent = False, $iIndentLevel = 0, $fShrinkToFit = False)

;====================================================================
; 试算表的动作
;====================================================================
;目标指向该页
_ExcelSheetActivate($oExcel, $vSheet)
;移动
_ExcelSheetMove($oExcel, $vMoveSheet, $vRelativeSheet = 1, $fBefore = True)
;新增
_ExcelSheetAddNew($oExcel, $sName = "")
;取得目前的名称
_ExcelSheetNameGet($oExcel)
; 改名称or 设定名称
_ExcelSheetNameSet($oExcel, $sSheetName)
;删除页
_ExcelSheetDelete($oExcel, $vSheet, $fAlerts = False)
; 取回页列表array
_ExcelSheetList($oExcel)

;====================================================================
;比较少用到的函数
;====================================================================

;排序没用过
_ExcelSort($oExcel, $sKey, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iDirection = 2)
_ExcelSortExtended($oExcel, $sKey, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iDirection = 2, $iHeader = 0, $fMatchCase = False, $iOrientation = 2, $iDataOption = 0)

; 注解
_ExcelCommentAdd($oExcel, $sComment, $sRangeOrRow, $iColumn = 1, $fVisible = 0)
_ExcelCommentDelete($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1)
_ExcelCommentShow($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $fVisible = False)
_ExcelCommentRead($oExcel, $sRangeOrRow, $iColumn = 1)

;切开
_ExcelSplitWindow($oExcel, $iSplitRow, $iSplitColumn)

;不晓得怎么用的
_ExcelNumberFormat($oExcel, $sFormat, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1)
_ExcelPictureInsert($oExcel, $sFilePath, $iLeft, $iTop, $iWidth, $iHeight, $fLinkToFile = False, $fSaveWithDoc = False)
_ExcelPictureAdjust($oPicture, $iHorizontal, $iVertical, $iRotation = 0)
_ExcelPictureScale($oPicture, $nScaleWidth = 1, $nScaleHeight = 1, $fScaleOrigWidth = True, $fScaleOrigHeight = True, $iScaleFrom = 0)
_ExcelHyperlinkInsert($oExcel, $sLinkText, $sAddress, $sScreenTip, $sRangeOrRow, $iColumn = 1)

_ExcelCellMerge($oExcel, $fDoMerge, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1)
_ExcelSheetUsedRangeGet($oExcel, $vSheet)

_ExcelHorizontalAlignSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $sHorizAlign = "left")
_ExcelVerticalAlignSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $sVertAlign = "bottom")

ken0137 发表于 2008-8-14 14:24:16

3.2.13.6.1:里已经集成了excel的udf了，一批很详细的说明了

hehui 发表于 2008-8-14 17:25:24

越来越牛X！！！！！！！！

axiang 发表于 2008-8-17 11:51:29

学习了，谢谢分享。

nijinchuan 发表于 2009-4-20 08:55:56

越来越牛X！！！！！！！！:face (33): :face (33): :face (33):

hhasee 发表于 2009-4-25 08:58:45

不错，学习了！

magus 发表于 2009-4-27 10:08:01

的确是越来越强大了...

zy_ccis 发表于 2009-6-15 23:10:22

看看自己还有没不！！！

zy_ccis 发表于 2009-6-15 23:11:35

_Example01 也要钱太霸道了太穷了

风行者 发表于 2009-6-29 02:45:00

很好，谢谢分享

luke 发表于 2009-6-29 13:39:48

谢谢分享！
学习中。

yourcells 发表于 2009-6-30 16:24:23

这个UDF好像有了吧？

freezee 发表于 2009-7-3 17:12:18

非常感谢，正在到处找呢，呵呵

rain 发表于 2009-7-3 22:47:20

不是一般的厉害啊！谢谢了！

页: [1] 2 3 4 5 6 7 8 9 10

AUTOIT CN's Archiver

Excel