求助 提取excel 某个字段相关数据 并生成其字段的新excel文件

hondao

求助 提取excel 某个字段相关数据  并生成其字段的新excel文件，以下代码提取数据打开后是乱码，
#include <Excel.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <FileConstants.au3>
#include <Array.au3>

Global $hGUI, $hInput1, $hInput2, $hInput3, $hButton

$hGUI = GUICreate("Excel Data Extraction", 600, 400)

GUICtrlCreateLabel("Source File Path:", 10, 10)
$hInput1 = GUICtrlCreateInput("", 10, 30, 480, 20)
$hButton1 = GUICtrlCreateButton("Browse", 500, 30, 80, 20)

GUICtrlCreateLabel("Save Path:", 10, 60)
$hInput2 = GUICtrlCreateInput("", 10, 80, 480, 20)
$hButton2 = GUICtrlCreateButton("Browse", 500, 80, 80, 20)

GUICtrlCreateLabel("Extracted Field:", 10, 110)
$hInput3 = GUICtrlCreateInput("", 10, 130, 580, 20)
$hButton = GUICtrlCreateButton("Extract", 260, 160, 80, 30)

GUISetState(@SW_SHOW)

While 1
    Switch GUIGetMsg()
        Case $GUI_EVENT_CLOSE
            ExitLoop
        Case $hButton1
            SelectSourceFile()
        Case $hButton2
            SelectSavePath()
        Case $hButton
            ExtractData()
    EndSwitch
WEnd

GUIDelete($hGUI)

Func SelectSourceFile()
    Local $sSourceFile = FileOpenDialog("Select Source File", "", "Excel Files (*.xls;*.xlsx)|All Files (*.*)", $FD_FILEMUSTEXIST)
    If @error Then Return
    GUICtrlSetData($hInput1, $sSourceFile)
EndFunc

Func SelectSavePath()
    Local $sSavePath = FileSelectFolder("Select Save Path", "")
    If @error Then Return
    GUICtrlSetData($hInput2, $sSavePath)
EndFunc

Func ExtractData()
    Local $sSourceFile = GUICtrlRead($hInput1)
    Local $sSavePath = GUICtrlRead($hInput2)
    Local $sFieldName = GUICtrlRead($hInput3)

    If Not FileExists($sSourceFile) Then
        MsgBox(16, "Error", "Source file does not exist.")
        Return
    EndIf


    Local $hFile = FileOpen($sSourceFile, $FO_READ)
    If $hFile = -1 Then
        MsgBox(16, "Error", "Failed to open file.")
        Return
    EndIf

    Local $aFieldNames = StringSplit(FileReadLine($hFile), ",")
    Local $sFileName = $sSavePath & "\" & $sFieldName & ".xlsx"
    $sFileName = StringReplace($sFileName, "\", "/") ; 替换路径中的反斜杠为正斜杠

    Local $hOutputFile = FileOpen($sFileName, BitOR($FO_OVERWRITE, $FO_UTF8))
    If $hOutputFile = -1 Then
        MsgBox(16, "Error", "Failed to create file.")
        FileClose($hFile)
        Return
    EndIf

    FileWriteLine($hOutputFile, _ArrayToString($aFieldNames, ","))

    Local $sLine
    While 1
        $sLine = FileReadLine($hFile)
        If @error Then ExitLoop

        Local $aLineData = StringSplit($sLine, ",")
        If StringInStr($sLine, $sFieldName) Then
            FileWriteLine($hOutputFile, _ArrayToString($aLineData, ","))
        EndIf
    WEnd

    FileClose($hFile)
    FileClose($hOutputFile)

    MsgBox(64, "Success", "Data extracted and saved to " & $sFileName)
EndFunc

chzj589

D:\AU3调试\运动训练管理系统\提取excel
读取excel数据，应该用excel函数，或用ObjCreate("ADODB.Connection")

chzj589

D:\AU3调试\运动训练管理系统\提取excel
图片无法上传？

D:\AU3调试\运动训练管理系统\提取excel

可以上传了

hondao

chzj589 发表于 2023-8-2 15:58
图片无法上传？

具体咋写，指导一下，谢谢

qq413774005

#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 = "规转机转发表生成V02"
;---------------------------------------
Local $message = $ver & "（工程技术中心-友情制作）"
Local $sFilePath = FileOpenDialog($message, @ScriptDir & "\", "规转机104文件 (*.xlsx)", 1)
If @error Then
        MsgBox(16, "错误", "没有选择IEC104文本导出文件,脚本退出!", 3)
        Exit
EndIf

Local $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)

yohoboy

qq413774005 发表于 2023-8-3 21:26
#region ;**** 参数创建于 ACNWrapper_GUI ****
#PRE_icon=C:\Windows\syswow64\SHELL32.dll|-21
#PRE_Us ...

樓上的au3版本?  因3.3.14.2 版部分函數與您的版本不同，因此需要做函數轉換.

hondao

qq413774005 发表于 2023-8-3 21:26
#region ;**** 参数创建于 ACNWrapper_GUI ****
#PRE_icon=C:\Windows\syswow64\SHELL32.dll|-21
#PRE_Us ...

错误: _ExcelBookOpen(): 未定义函数.
Local $oExcel = _ExcelBookOpen($sFilePath, $fVisible = 0)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"d:\Users\baobao\Desktop\two.au3"(51,42) : 错误: _ExcelSheetActivate(): 未定义函数.
        _ExcelSheetActivate($oExcel, $ZW)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"d:\Users\baobao\Desktop\two.au3"(65,54) : 错误: _ExcelReadCell(): 未定义函数.
                $ex1 = _ExcelReadCell($oExcel, $n, 1)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
"d:\Users\baobao\Desktop\two.au3"(80,55) : 错误: _ExcelBookClose(): 未定义函数.
_ExcelBookClose($oExcel) ; 鏈

haijie1223

楼主给的代码都没用excel的UDF，读取出来是乱码正常，自己对照帮助，先去熟悉excel的命令。

chzj589

hondao 发表于 2023-8-3 12:43
具体咋写，指导一下，谢谢

提供你要操作的excel文件
说明想要达到的效果

qq413774005

yohoboy 发表于 2023-8-3 23:55
樓上的au3版本?  因3.3.14.2 版部分函數與您的版本不同，因此需要做函數轉換.

我用的是
AUTOIT_3.3.11.X.SVN R1054M

qq413774005

qq413774005 发表于 2023-8-3 21:26
#region ;**** 参数创建于 ACNWrapper_GUI ****
#PRE_icon=C:\Windows\syswow64\SHELL32.dll|-21
#PRE_Us ...

AUTOIT_3.3.11.X.SVN R1054M