LibXL库读写Excel表格-『谈天说地』-AUTOIT CN

繁星发表于 2023-8-21 23:10:47

邪恶海盗发表于 2023-8-21 18:58
好像数值需要转换,比如日期...

我用的是xlSheetReadStr读的是字符串，你要别的格式，比如日期，官方文档有例子，参考改改就行
顺便也翻译了下官方的例子，关于日期的读取写入
;Source: https://www.libxl.com/read-write-excel-date-time.html
#AutoIt3Wrapper_UseX64 = n
Local $hDLL = DllOpen('libxl32.dll')
Local $BookHandle = xlCreateBook()
xlBookSetKey($BookHandle) ;must License
Local $format1 = xlBookAddFormat($BookHandle)
xlFormatSetNumFormat($format1, 14) ;NUMFORMAT_DATE
Local $format2 = xlBookAddFormat($BookHandle)
xlFormatSetNumFormat($format2, 22) ;NUMFORMAT_CUSTOM_MDYYYY_HMM
Local $format3 = xlBookAddFormat($BookHandle)
xlFormatSetNumFormat($format3, xlBookAddCustomNumFormat($BookHandle, 'd mmmm yyyy'))
Local $format4 = xlBookAddFormat($BookHandle)
xlFormatSetNumFormat($format4, 18) ;NUMFORMAT_CUSTOM_HMM_AM
Local $sheet = xlBookAddSheet($BookHandle, 'Sheet1')
xlSheetSetCol($sheet, 1, 1, 15)
;// writing
xlSheetWriteNum($sheet, 2, 1, xlBookDatePack($BookHandle, 2010, 3, 11), $format1)
xlSheetWriteNum($sheet, 3, 1, xlBookDatePack($BookHandle, 2010, 3, 11, 10, 25, 55), $format2)
xlSheetWriteNum($sheet, 4, 1, xlBookDatePack($BookHandle, 2010, 3, 11), $format3)
xlSheetWriteNum($sheet, 5, 1, xlBookDatePack($BookHandle, 2010, 3, 11, 10, 25, 55), $format4)
;// reading
Local $Time = DllStructCreate('int Year;int Month; int Day; int Hour; int Min; int Sec')
xlBookDateUnpack($BookHandle, xlSheetReadNum($Sheet, 2, 1), DllStructGetPtr($Time, 'Year'), DllStructGetPtr($Time, 'Month'), DllStructGetPtr($Time, 'Day'))
ConsoleWrite(DllStructGetData($Time, 'Year') & '-' & DllStructGetData($Time, 'Month') & '-' & DllStructGetData($Time, 'Day') & @CRLF)

xlBookDateUnpack($BookHandle, xlSheetReadNum($Sheet, 3, 1), _
DllStructGetPtr($Time, 'Year'), _
DllStructGetPtr($Time, 'Month'), _
DllStructGetPtr($Time, 'Day'), _
DllStructGetPtr($Time, 'Hour'), _
DllStructGetPtr($Time, 'Min'), _
DllStructGetPtr($Time, 'Sec'))
ConsoleWrite(DllStructGetData($Time, 'Year') & '-' & DllStructGetData($Time, 'Month') & '-' & DllStructGetData($Time, 'Day') & @TAB & _
DllStructGetData($Time, 'Hour') & ':' & DllStructGetData($Time, 'Min') & ':' & DllStructGetData($Time, 'Sec') & @CRLF)

xlBookSave($BookHandle, @ScriptDir & '\datetime.xls')
xlBookRelease($BookHandle)
DllClose($hDLL)

Func xlCreateBook()
Local $aRet = DllCall($hDLL, 'handle:cdecl', 'xlCreateBookW')
If @error Or Not $aRet Then
Return SetError(@error, @extended, 0)
Else
Return $aRet
EndIf
EndFunc ;==>xlCreateBook

Func xlBookSetKey($BookHandle, $Name = 'GCCG', $Key = 'windows-282123090cc0e6036db16b60a1o3p0h9')
Local $aRet = DllCall($hDLL, 'none:cdecl', 'xlBookSetKeyW', 'handle', $BookHandle, 'wstr', $Name, 'wstr', $Key)
If @error Then Return SetError(@error, @extended, 0)
EndFunc ;==>xlBookSetKey

Func xlBookAddFormat($BookHandle, $FormatHandle = 0)
Local $aRet = DllCall($hDLL, 'handle:cdecl', 'xlBookAddFormatW', 'handle', $BookHandle, 'handle', $FormatHandle)
If @error Or Not $aRet Then
Return SetError(@error, @extended, 0)
Else
Return $aRet
EndIf
EndFunc

Func xlFormatSetNumFormat($FormatHandle, $sFormat)
Local $aRet = DllCall($hDLL, 'none:cdecl', 'xlFormatSetNumFormatW', 'handle', $FormatHandle, 'int', $sFormat)
If @error Or Not $aRet Then
Return SetError(@error, @extended, 0)
Else
Return $aRet
EndIf
EndFunc

Func xlBookAddCustomNumFormat($BookHandle, $sFormat)
Local $aRet = DllCall($hDLL, 'int:cdecl', 'xlBookAddCustomNumFormatW', 'handle', $BookHandle, 'wstr', $sFormat)
If @error Or Not $aRet Then
Return SetError(@error, @extended, 0)
Else
Return $aRet
EndIf
EndFunc

Func xlBookAddSheet($BookHandle, $sSheetName, $SheetHandle = 0)
Local $aRet = DllCall($hDLL, 'handle:cdecl', 'xlBookAddSheetW', 'handle', $BookHandle, 'wstr', $sSheetName, 'handle', $SheetHandle)
If @error Or Not $aRet Then
Return SetError(@error, @extended, 0)
Else
Return $aRet
EndIf
EndFunc ;==>xlBookAddSheet

Func xlSheetSetCol($SheetHandle, $colFirst, $colLast, $width, $FormatHandle = 0, $hidden = False)
Local $aRet = DllCall($hDLL, 'int:cdecl', 'xlSheetSetColW', _
'handle', $SheetHandle, _
'int', $colFirst, _
'int', $colLast, _
'double', $width, _
'handle', $FormatHandle, _
'bool', $hidden)
If @error Or Not $aRet Then
Return SetError(@error, @extended, 0)
Else
Return $aRet
EndIf
EndFunc

Func xlSheetWriteNum($SheetHandle, $row, $col, $value, $FormatHandle = 0)
Local $aRet = DllCall($hDLL, 'int:cdecl', 'xlSheetWriteNumW', _
'handle', $SheetHandle, _
'int', $row, _
'int', $col, _
'double', $value, _
'handle', $FormatHandle)
If @error Or Not $aRet Then
Return SetError(@error, @extended, 0)
Else
Return $aRet
EndIf
EndFunc

Func xlSheetReadNum($SheetHandle, $iRow, $iCol, $FormatHandle = 0)
Local $aRet = DllCall($hDLL, 'double:cdecl', 'xlSheetReadNumW', _
'handle', $SheetHandle, _
'int', $iRow, _
'int', $iCol, _
'handle', $FormatHandle)
If @error Or Not $aRet Then
Return SetError(@error, @extended, 0)
Else
Return $aRet
EndIf
EndFunc

Func xlBookDatePack($BookHandle, $Year, $Month, $Day, $Hour = Null, $Min = Null, $Sec = Null, $Msec = Null)
Local $aRet = DllCall($hDLL, 'double:cdecl', 'xlBookDatePackW', _
'handle', $BookHandle, _
'int', $Year, _
'int', $Month, _
'int', $Day, _
'int', $Hour, _
'int', $Min, _
'int', $Sec, _
'int', $Month)
If @error Or Not $aRet Then
Return SetError(@error, @extended, 0)
Else
Return $aRet
EndIf
EndFunc

Func xlBookDateUnpack($BookHandle, $Value, $Year, $Month, $Day = 0, $Hour = 0, $Min = 0, $Sec = 0, $Msec = 0)
Local $aRet = DllCall($hDLL, 'int:cdecl', 'xlBookDateUnpackW', _
'handle', $BookHandle, _
'double', $Value, _
'ptr', $Year, _
'ptr', $Month, _
'ptr', $Day, _
'ptr', $Hour, _
'ptr', $Min, _
'ptr', $Sec, _
'ptr', $Month)
If @error Or Not $aRet Then
Return SetError(@error, @extended, 0)
Else
Return $aRet
EndIf
EndFunc

Func xlBookSave($BookHandle, $sFileName)
Local $aRet = DllCall($hDLL, 'int:cdecl', 'xlBookSaveW', 'handle', $BookHandle, 'wstr', $sFileName)
If @error Or Not $aRet Then
Return SetError(@error, @extended, 0)
Else
Return $aRet
EndIf
EndFunc ;==>xlBookSave

Func xlBookRelease($BookHandle)
DllCall($hDLL, 'none:cdecl', 'xlBookReleaseW', 'handle', $BookHandle)
EndFunc ;==>xlBookRelease

chzj589 发表于 2023-8-22 08:43:12

本帖最后由 chzj589 于 2023-8-22 09:26 编辑

繁星发表于 2023-8-21 23:10
我用的是xlSheetReadStr读的是字符串，你要别的格式，比如日期，官方文档有例子，参考改改就行
顺便也翻 ...

日期读取显示的格式是文本，怎么转换？
哦，明白了
xlBookDateUnpack($BookHandle, xlSheetReadNum($hSheet, 2, 1), DllStructGetPtr($Time, 'Year'), DllStructGetPtr($Time, 'Month'), DllStructGetPtr($Time, 'Day'))
DllStructGetData($Time, 'Year') & '年' & DllStructGetData($Time, 'Month') & '月' & DllStructGetData($Time, 'Day') & '日'

afan 发表于 2023-8-22 09:11:30

这贴应该在源码区吧~

不要光做看客，举手之劳点个赞不难吧 _ _||

chzj589 发表于 2023-8-22 10:31:16

繁星发表于 2023-8-21 23:10
我用的是xlSheetReadStr读的是字符串，你要别的格式，比如日期，官方文档有例子，参考改改就行
顺便也翻 ...

转换日期

qq413774005 发表于 2023-8-22 10:45:35

读EXCEL转TXT技巧。
#region ;**** 参数创建于 ACNWrapper_GUI ****
#PRE_icon=C:\Windows\syswow64\SHELL32.dll|-21
#PRE_UseUpx=n
#PRE_UseX64=n
#PRE_Res_requestedExecutionLevel=None
#endregion ;**** 参数创建于 ACNWrapper_GUI ****
;---------------------------------------
#include <Excel.au3>
#include <Array.au3>
;防止多次打开文件
$g_szVersion = "JYSP821"
If WinExists($g_szVersion) Then Exit
AutoItWinSetTitle($g_szVersion)
#include <Misc.au3>
_Singleton("JYSP821")
;------------------------
$ver = "规转机转发表生成V03"
;---------------------------------------
Local $message = $ver & "（工程技术中心-友情制作）"
Local $sFilePath = FileOpenDialog($message, @ScriptDir & "\", "文件格式 (*.xls)|文件格式 (*.xlsx)", 1)
If @error Then
MsgBox(64, "提示", "没有选择IEC104文本导出文件,脚本退出!", 3)
Exit
EndIfLocal $fVisible = 1
Local $oExcel = _ExcelBookOpen($sFilePath, $fVisible = 0)
If @error = 1 Then
MsgBox(4096, "错误!", "无法创建对象!")
Exit
ElseIf @error = 2 Then
MsgBox(4096, "错误!", "文件不存在!")
Exit
EndIf
;---------------------------------------
$file1 = FileOpen(@ScriptDir & "\（规转机）104.txt", 8 + 2)
Sleep(2000)
ProgressOn($ver, "规转机104表格转换", "0 %")
;---------------------------------------
$WZ = "遥信";"遥信转发表"
$ZW = "遥信"
_XH()
;---------------------------------------
$WZ = "遥测";"遥测转发表"
$ZW = "遥测"
_XH()
;---------------------------------------
$WZ = "遥控"; "遥控转发表"
$ZW = "遥控"
_XH()
;---------------------------------------
$WZ = "遥调";"参数转发表";
$ZW = "遥调"
_XH()
;---------------------------------------
Func _XH()
;--------------------------
_ExcelSheetActivate($oExcel, $ZW)
Sleep(300)
FileWrite($file1, $ZW & "定义:" & @CRLF)
Local $n = 1, $m = 0
;--------------------------
While 1
;--------------------------
$m = $m + 1
If $m > 100 Then
$m = 0
EndIf
ProgressSet($m, $WZ & ":" & $n & "条");分百比
;--------------------------
$n = $n + 1
$ex1 = _ExcelReadCell($oExcel, $n, 1)
$ex2 = _ExcelReadCell($oExcel, $n, 2)
$ex3 = _ExcelReadCell($oExcel, $n, 3)
$ex4 = _ExcelReadCell($oExcel, $n, 4)If StringLen($ex1) > 0 Then
FileWrite($file1, $ex1 & " " & $ex2 & " " & $ex3 & " " & $ex4 & @CRLF)
Else
ExitLoop
EndIf
WEnd
EndFunc ;==>_XH
;---------------------------------------
ProgressOff()
FileClose($file1)
_ExcelBookClose($oExcel) ; 最后我们关闭并退出
;---------------------------------------
MsgBox(64, "提示", "网关机104表格转换,表格转换结束!", 3)

邪恶海盗 发表于 2023-8-24 14:01:11

撸主有空写个帮助文件吧,像AU3chs.CHM一样的福利我等小白...:face (1):

chzj589 发表于 2023-8-25 13:37:17

繁星发表于 2023-8-21 23:10
我用的是xlSheetReadStr读的是字符串，你要别的格式，比如日期，官方文档有例子，参考改改就行
顺便也翻 ...

单元格是文本格式，如何转换为数字格式？

zghwelcome 发表于 2023-8-25 18:37:38

chzj589 发表于 2023-8-25 13:37
单元格是文本格式，如何转换为数字格式？

#include "libxl.au3"
Global Enum $NUMFORMAT_GENERAL, $NUMFORMAT_NUMBER, $NUMFORMAT_NUMBER_D2, $NUMFORMAT_NUMBER_SEP, $NUMFORMAT_NUMBER_SEP_D2
Local $sFile_xlsx = @ScriptDir & '\Test.xlsx'
If Not FileExists($sFile_xlsx) Then MsgBox(16, '提示', '文件不存在.')
Local $hBook = xlCreateXMLBook()
If $hBook Then
xlBookSetKey($hBook)
xlBookLoad($hBook, $sFile_xlsx);// 加载文件
Local $hFormatHandle = xlBookAddFormat($hBook) ;//添加格式
xlFormatSetNumFormat($hFormatHandle, $NUMFORMAT_NUMBER) ;//设置数字格式
Local $hSheet = xlBookGetSheet($hBook, 0) ;// 获取第一个 sheet 的句柄
Local $iRow = 1, $iCol = 1 ;// 行、列 (0基索引)
xlSheetSetCellFormat($hSheet, $iRow, $iCol, $hFormatHandle) ;// 设置单元格数字格式
xlBookSave($hBook, $sFile_xlsx) ;//保存
xlBookRelease($hBook) ;// 释放
EndIf
DllClose($hDLL)
Func xlSheetSetCellFormat($hSheet, $iRow, $iCol, $hFormatHandle) ;设置单元格格式
DllCall($hDLL, 'none:cdecl', 'xlSheetSetCellFormatW', 'handle', $hSheet, 'int', $iRow, 'int', $iCol, 'handle', $hFormatHandle)
If @error Then
Return SetError(1, 0, False)
Else
Return True
EndIf
EndFunc ;==>xlSheetSetCellFormat

chzj589 发表于 2023-8-26 09:50:37

zghwelcome 发表于 2023-8-25 18:37

格里有个箭头，无法改变格式

zghwelcome 发表于 2023-8-26 13:39:42

chzj589 发表于 2023-8-26 09:50
格里有个箭头，无法改变格式

#include "libxl.au3"
Global Enum $NUMFORMAT_GENERAL, $NUMFORMAT_NUMBER, $NUMFORMAT_NUMBER_D2, $NUMFORMAT_NUMBER_SEP, $NUMFORMAT_NUMBER_SEP_D2
Local $sFile_xlsx = @ScriptDir & '\Test.xlsx'
If Not FileExists($sFile_xlsx) Then MsgBox(16, '提示', '文件不存在.')
Local $hBook = xlCreateXMLBook()
If $hBook Then
xlBookSetKey($hBook)
xlBookLoad($hBook, $sFile_xlsx);// 加载文件
Local $hFormatHandle = xlBookAddFormat($hBook) ;//添加格式
xlFormatSetNumFormat($hFormatHandle, $NUMFORMAT_NUMBER) ;//设置数字格式
Local $hSheet = xlBookGetSheet($hBook, 0) ;// 获取第一个 sheet 的句柄
Local $iRow = 1, $iCol = 1 ;// 行、列 (0基索引)
Local $sCellValue = ''
Local $vCellType = xlSheetCellType($hSheet, $iRow, $iCol)
Switch $vCellType
Case 1
$sCellValue = xlSheetReadNum($hSheet, $iRow, $iCol)
Case 2
$sCellValue = xlSheetReadStr($hSheet, $iRow, $iCol)
Case 3
$sCellValue = xlSheetReadBool($hSheet, $iRow, $iCol)
EndSwitch
xlSheetWriteNum($hSheet, $iRow, $iCol,$sCellValue, $hFormatHandle)
xlBookSave($hBook, $sFile_xlsx) ;//保存
xlBookRelease($hBook) ;// 释放
EndIf
DllClose($hDLL)
Func xlSheetSetCellFormat($hSheet, $iRow, $iCol, $hFormatHandle) ;设置单元格格式
DllCall($hDLL, 'none:cdecl', 'xlSheetSetCellFormatW', 'handle', $hSheet, 'int', $iRow, 'int', $iCol, 'handle', $hFormatHandle)
If @error Then
Return SetError(1, 0, False)
Else
Return True
EndIf
EndFunc ;==>xlSheetSetCellFormat

繁星发表于 2023-8-26 20:10:42

邪恶海盗发表于 2023-8-24 14:01
撸主有空写个帮助文件吧,像AU3chs.CHM一样的福利我等小白...

是个大工程，要不你来翻译下:face (24):

邪恶海盗 发表于 2023-8-26 21:30:57

繁星发表于 2023-8-26 20:10
是个大工程，要不你来翻译下

我要有那水平地话还在这大放厥词么???

chzj589 发表于 2023-8-27 07:05:10

zghwelcome 发表于 2023-8-26 13:39

谢谢。搞定了:face (10):

chzj589 发表于 2023-8-27 10:09:12

zghwelcome 发表于 2023-8-26 13:39

如要设置日期格式是改下面：
xlFormatSetNumFormat($hFormatHandle, $NUMFORMAT_GENERAL) ;//设置日期格式

chzj589 发表于 2023-8-27 18:13:10

本帖最后由 chzj589 于 2023-8-30 13:26 编辑

zghwelcome 发表于 2023-8-26 13:39

都解决了

页: 1 [2] 3 4 5

AUTOIT CN's Archiver