[已解决]如何每次寫入資料到 excel 時 , 可以一直往下寫 , 不要蓋掉原本的檔案?-已解决问题区-AUTOIT CN

ipmitool 发表于 2017-2-8 13:28:10

[已解决]如何每次寫入資料到 excel 時, 可以一直往下寫, 不要蓋掉原本的檔案?

本帖最后由 ipmitool 于 2017-2-14 15:46 编辑

如何每次寫入資料到 excel 時, 可以一直往下寫, 不要蓋掉原本的檔案?Func exporttoexcel();EXPORT TO EXCEL
$col = 0
$count = _GUICtrlListView_GetItemCount($ListView2)
GUICtrlSetState($export,$gui_enable)

$excel = _ExcelBookNew()
$oWorkbook = _Excel_BookNew($excel)

_ExcelWriteCell($excel,"名稱",2,2)
_ExcelWriteCell($excel,"數量",2,3)
_ExcelWriteCell($excel,"金額",2,4)
_ExcelWriteCell($excel,"總金額",2,5)
_ExcelWriteCell($excel," ",2,6)
_ExcelWriteCell($excel," ",2,7)
_ExcelWriteCell($excel," ",2,8)
_ExcelWriteCell($excel," ",2,9)
_ExcelWriteCell($excel," ",2,10)
_ExcelWriteCell($excel," ",2,11)
_ExcelWriteCell($excel," ",2,12)
_ExcelWriteCell($excel," ",2,13)
_ExcelWriteCell($excel," ",2,14)
_ExcelWriteCell($excel," ",2,15)
_ExcelWriteCell($excel," ",2,16)
_ExcelWriteCell($excel," ",2,17)

For $colexcel = 0 To $col

$i = 0
do
   _ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,0),3 + $i,2)
   _ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,1),3 + $i,3)
   _ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,2),3 + $i,4)
   _ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,3),3 + $i,5)
   _ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,4),3 + $i,6)
   _ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,5),3 + $i,7)
   _ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,6),3 + $i,8)
   _ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,7),3 + $i,9)
   _ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,8),3 + $i,10)
_ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,9),3 + $i,11)
_ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,10),3 + $i,12)
_ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,11),3 + $i,13)
_ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,12),3 + $i,14)
_ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,13),3 + $i,15)
_ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,14),3 + $i,16)
_ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,15),3 + $i,17)
_ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,16),3 + $i,18)

$i = $i + 1
Until $i = $count
Next

_ExcelWriteCell($excel, $totalPrice,2 + $i,5) ;總金額

;MsgBox(64,"Message","Export completed!" & @CRLF _
;                      & " "& @CRLF _
;                      & "Output excel file on your Desktop!",2)

DirCreate(@ScriptDir & "\Excel")

_Excel_BookSaveAs($oWorkbook, @ScriptDir & "\Excel\"& @YEAR & @MON & @MDAY & ".xlsx", Default,True)
_Excel_Close($excel,True,True)

EndFunc

Func _ExcelBookNew($fVisible = 0)
Local $oExcel = ObjCreate("Excel.Application")
If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
If NOT IsNumber($fVisible) Then Return SetError(2, 0, 0)
If $fVisible > 1 Then $fVisible = 1
If $fVisible < 0 Then $fVisible = 0
With $oExcel
.Visible = $fVisible
.WorkBooks.Add
.ActiveWorkbook.Sheets(1).Select()
EndWith
Return $oExcel

EndFunc ;==>_ExcelBookNew

Func _ExcelWriteCell($oExcel, $sValue, $sRangeOrRow, $iColumn = 1)
If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
If NOT StringRegExp($sRangeOrRow, "", 0) Then
   If $sRangeOrRow < 1 Then Return SetError(2, 0, 0)
If $iColumn < 1 Then Return SetError(2, 1, 0)
$oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Value = $sValue
Return 1
Else
$oExcel.Activesheet.Range($sRangeOrRow).Value = $sValue
Return 1
EndIf
EndFunc ;==>_ExcelWriteCell

kk_lee69 发表于 2017-2-8 21:33:50

回复 1# ipmitool

記住上次寫入到哪一行不就好了

ipmitool 发表于 2017-2-9 07:45:43

回复 2# kk_lee69

哥, 人家不會弄嘛...

ipmitool 发表于 2017-2-13 09:45:07

不好意思, 請問有人有招嗎? 謝謝喔

Alam 发表于 2017-2-13 20:01:39

这.....就算没有办法记住上次的位置,难道不可以先读出原有数据, 拼接到现在的数据前,然后再一起写进去吗?

Alam 发表于 2017-2-13 20:01:41

这.....就算没有办法记住上次的位置,难道不可以先读出原有数据, 拼接到现在的数据前,然后再一起写进去吗?

ipmitool 发表于 2017-2-14 08:58:57

回复 6# Alam

源碼是這樣的, 現在每匯出一筆都是單獨的 excel file, 想要一天的單據記錄成一筆就好
然後, 列印的部分想要只列印 listview2 就好了, 這樣可以辦到嗎...#NoTrayIcon
#RequireAdmin
#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Icon=..\ICO1.ico
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <ListViewConstants.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#include <GUIConstants.au3>
#include <GUIListView.au3>
#include <Array.au3>
#include <GuiEdit.au3>
#include <GuiMenu.au3>
#include <MsgBoxConstants.au3>
#include <File.au3>
#include <FontConstants.au3>
#include <Excel.au3>
#include <ScreenCapture.au3>

HotKeySet('{esc}', '_Exit')

#Region ### START Koda GUI section ### Form=

$Form1 = GUICreate("艾利珮珮義大利麵", 835, 950, -1, -1)

; 1 ###########################################################################################
GUICtrlCreateGroup("番茄紅醬系列",15,15,258,190)
$ListView1 = GUICtrlCreateListView("ID|名稱|價錢", 28, 35, 230, 157)
GUICtrlCreateListViewItem("1|番茄肉醬麵|65", $ListView1)
GUICtrlCreateListViewItem("2|番茄鮮蔬(可素)麵|65", $ListView1)
GUICtrlCreateListViewItem("3|番茄培根麵|75", $ListView1)
GUICtrlCreateListViewItem("4|番茄雞肉麵|75", $ListView1)
GUICtrlCreateListViewItem("5|番茄蛤蜊麵|85", $ListView1)
GUICtrlCreateListViewItem("6|番茄海鮮麵|105", $ListView1)

GUICtrlCreateListViewItem("7|番茄肉醬飯|65", $ListView1)
GUICtrlCreateListViewItem("8|番茄鮮蔬(可素)飯|65", $ListView1)
GUICtrlCreateListViewItem("9|番茄培根飯|75", $ListView1)
GUICtrlCreateListViewItem("10|番茄雞肉飯|75", $ListView1)
GUICtrlCreateListViewItem("11|番茄蛤蜊飯|85", $ListView1)
GUICtrlCreateListViewItem("12|番茄海鮮飯|105", $ListView1)
;###############################################################################################

; 1K ###########################################################################################
GUICtrlCreateGroup("羅勒青醬系列",15,230,258,190)
$ListView1K = GUICtrlCreateListView("ID|名稱|價錢", 28, 250, 230, 157)
GUICtrlCreateListViewItem("1|青醬鮮蔬麵|65", $ListView1K)
GUICtrlCreateListViewItem("2|青醬培根麵|75", $ListView1K)
GUICtrlCreateListViewItem("3|青醬雞肉麵|75", $ListView1K)
GUICtrlCreateListViewItem("4|青醬蛤蜊麵|85", $ListView1K)
GUICtrlCreateListViewItem("5|青醬海鮮麵|110", $ListView1K)

GUICtrlCreateListViewItem("6|青醬鮮蔬飯|65", $ListView1K)
GUICtrlCreateListViewItem("7|青醬培根飯|75", $ListView1K)
GUICtrlCreateListViewItem("8|青醬雞肉飯|75", $ListView1K)
GUICtrlCreateListViewItem("9|青醬蛤蜊飯|85", $ListView1K)
GUICtrlCreateListViewItem("10|青醬海鮮飯|110", $ListView1K)
;###############################################################################################

; 2K ###########################################################################################
GUICtrlCreateGroup("粉紅醬系列",285,15,258,190)
$ListView2K = GUICtrlCreateListView("ID|名稱|價錢", 300, 35, 230, 157)
GUICtrlCreateListViewItem("1|粉紅肉醬麵|70", $ListView2K)
GUICtrlCreateListViewItem("2|粉紅鮮蔬麵|70", $ListView2K)
GUICtrlCreateListViewItem("3|粉紅培根麵|85", $ListView2K)
GUICtrlCreateListViewItem("4|粉紅雞肉麵|85", $ListView2K)
GUICtrlCreateListViewItem("5|粉紅蛤蜊麵|90", $ListView2K)
GUICtrlCreateListViewItem("6|粉紅海鮮麵|115", $ListView2K)

GUICtrlCreateListViewItem("7|粉紅肉醬飯|70", $ListView2K)
GUICtrlCreateListViewItem("8|粉紅鮮蔬飯|70", $ListView2K)
GUICtrlCreateListViewItem("9|粉紅培根飯|85", $ListView2K)
GUICtrlCreateListViewItem("10|粉紅雞肉飯|85", $ListView2K)
GUICtrlCreateListViewItem("11|粉紅蛤蜊飯|90", $ListView2K)
GUICtrlCreateListViewItem("12|粉紅海鮮飯|115", $ListView2K)
;###############################################################################################

; 3K ###########################################################################################
GUICtrlCreateGroup("奶油白醬系列",285,230,258,190)
$ListView3K = GUICtrlCreateListView("ID|名稱|價錢", 300, 250, 230, 157)
GUICtrlCreateListViewItem("1|白醬鮮蔬(可奶素) 麵|65", $ListView3K)
GUICtrlCreateListViewItem("2|白醬培根麵|75", $ListView3K)
GUICtrlCreateListViewItem("3|白醬雞肉麵|75", $ListView3K)
GUICtrlCreateListViewItem("4|白醬蛤蜊麵|85", $ListView3K)
GUICtrlCreateListViewItem("5|白醬海鮮麵|110", $ListView3K)

GUICtrlCreateListViewItem("6|白醬鮮蔬(可奶素) 飯|65", $ListView3K)
GUICtrlCreateListViewItem("7|白醬培根飯|75", $ListView3K)
GUICtrlCreateListViewItem("8|白醬雞肉飯|75", $ListView3K)
GUICtrlCreateListViewItem("9|白醬蛤蜊飯|85", $ListView3K)
GUICtrlCreateListViewItem("10|白醬海鮮飯|110", $ListView3K)
;###############################################################################################

; 4K ###########################################################################################
GUICtrlCreateGroup("清炒蒜香系列",15,440,258,190)
$ListView4K = GUICtrlCreateListView("ID|名稱|價錢", 28, 460, 230, 157)
GUICtrlCreateListViewItem("1|蒜香鮮蔬麵|55", $ListView4K)
GUICtrlCreateListViewItem("2|蒜香培根麵|65", $ListView4K)
GUICtrlCreateListViewItem("3|蒜香雞肉麵|65", $ListView4K)
GUICtrlCreateListViewItem("4|蒜香蛤蜊麵|75", $ListView4K)
GUICtrlCreateListViewItem("5|蒜香海鮮麵|90", $ListView4K)
;###############################################################################################

; 5K ###########################################################################################
GUICtrlCreateGroup("中西混搭系列",285,440,258,90)
$ListView5K = GUICtrlCreateListView("ID|名稱|價錢", 300, 460, 230, 57)
GUICtrlCreateListViewItem("1|泰式酸辣海鮮湯麵|95", $ListView5K)
;###############################################################################################

; 6K ###########################################################################################
GUICtrlCreateGroup("湯品",285,540,258,90)
$ListView6K = GUICtrlCreateListView("ID|名稱|價錢", 300, 560, 230, 57)
GUICtrlCreateListViewItem("1|玉米濃湯|25", $ListView6K)
;###############################################################################################

; 7K ###########################################################################################
GUICtrlCreateGroup("加購區",555,15,258,190)
$ListView7K = GUICtrlCreateListView("ID|名稱|價錢", 568, 38, 230, 154)
GUICtrlCreateListViewItem("1|加飯/麵(半碗100g)|10", $ListView7K)
GUICtrlCreateListViewItem("2|加飯/麵(一碗200g)|20", $ListView7K)
GUICtrlCreateListViewItem("3|加青花菜|10", $ListView7K)
GUICtrlCreateListViewItem("4|加起司片|10", $ListView7K)
GUICtrlCreateListViewItem("5|玉米濃湯|20", $ListView7K)
;###############################################################################################

$LineA2=GUICtrlCreateLabel("=====================================================================================================================================", 12,635,900,50)
GUICtrlSetColor($LineA2, 0xDDCCDD)

;###########################################################################################
$Label6 = GUICtrlCreateLabel("名稱", 45, 660, 40, 17)
$Input5 = GUICtrlCreateInput("", 80, 655, 120, 21)

$Label9 = GUICtrlCreateLabel("價錢", 45, 690, 40, 17)
$Input8 = GUICtrlCreateInput("", 80, 685, 120, 21)

$Label11 = GUICtrlCreateLabel("數量", 45, 720, 28, 17)
$Input10 = _GUICtrlEdit_Create($Form1, "", 80, 715, 120, 21, $ES_NUMBER)

$Button2 = GUICtrlCreateButton("確定", 45, 750, 155, 25, $BS_DEFPUSHBUTTON)

$clear = GUICtrlCreateButton("下一筆", 45, 780, 155, 60)

;###########################################################################################
$export = GUICtrlCreateButton("匯出", 230, 785, 60, 25)
$print = GUICtrlCreateButton("列印", 230, 813, 60, 25)
$export_all = GUICtrlCreateButton("匯出+列印", 295, 785, 90, 53)
GUICtrlCreateLabel("總金額: ", 600,790,100,50)
GUICtrlCreateLabel("序號: ", 450,790,30,50)

$ListView2 = GUICtrlCreateListView("名稱                      |數量    |價錢    ", 230, 657, 570, 120)

GUISetState(@SW_SHOW)

#EndRegion ### END Koda GUI section ###

Global Enum $idOpen = 1000, $idSave, $idInfo

Global $hCMenu = GUICtrlCreateContextMenu($ListView2)
Global $hCMenuText1 = GUICtrlCreateMenuItem("Delete", $hCMenu)

;GUIRegisterMsg($WM_NOTIFY, "WM_NOTIFY")
GUIRegisterMsg($WM_NOTIFY, "WM_Notify_Events")

$OrderID="1"
$cowbe0=GUICtrlCreateLabel($OrderID, 485,766,100,80)
GUICtrlSetFont($cowbe0,40,$FW_NORMAL,$GUI_FONTUNDER,"Comic Sans MS")

GUICtrlCreatePic(@ScriptDir&"\ldF01.jpg", 15, 840, 800, 105)

While 1

   $nMsg = GUIGetMsg()
   Switch $nMsg
            Case $GUI_EVENT_CLOSE
                     Exit

Case $clear

; 1 ###########################################################################################

_GUICtrlListView_DeleteAllItems($ListView1)
GUICtrlCreateListViewItem("1|番茄肉醬麵|65", $ListView1)
GUICtrlCreateListViewItem("2|番茄鮮蔬(可素)麵|65", $ListView1)
GUICtrlCreateListViewItem("3|番茄培根麵|75", $ListView1)
GUICtrlCreateListViewItem("4|番茄雞肉麵|75", $ListView1)
GUICtrlCreateListViewItem("5|番茄蛤蜊麵|85", $ListView1)
GUICtrlCreateListViewItem("6|番茄海鮮麵|105", $ListView1)

GUICtrlCreateListViewItem("7|番茄肉醬飯|65", $ListView1)
GUICtrlCreateListViewItem("8|番茄鮮蔬(可素)飯|65", $ListView1)
GUICtrlCreateListViewItem("9|番茄培根飯|75", $ListView1)
GUICtrlCreateListViewItem("10|番茄雞肉飯|75", $ListView1)
GUICtrlCreateListViewItem("11|番茄蛤蜊飯|85", $ListView1)
GUICtrlCreateListViewItem("12|番茄海鮮飯|105", $ListView1)
;###############################################################################################

; 1K ###########################################################################################
_GUICtrlListView_DeleteAllItems($ListView1K)
GUICtrlCreateListViewItem("1|青醬鮮蔬麵|65", $ListView1K)
GUICtrlCreateListViewItem("2|青醬培根麵|75", $ListView1K)
GUICtrlCreateListViewItem("3|青醬雞肉麵|75", $ListView1K)
GUICtrlCreateListViewItem("4|青醬蛤蜊麵|85", $ListView1K)
GUICtrlCreateListViewItem("5|青醬海鮮麵|110", $ListView1K)

GUICtrlCreateListViewItem("6|青醬鮮蔬飯|65", $ListView1K)
GUICtrlCreateListViewItem("7|青醬培根飯|75", $ListView1K)
GUICtrlCreateListViewItem("8|青醬雞肉飯|75", $ListView1K)
GUICtrlCreateListViewItem("9|青醬蛤蜊飯|85", $ListView1K)
GUICtrlCreateListViewItem("10|青醬海鮮飯|110", $ListView1K)
;###############################################################################################

; 2K ###########################################################################################
_GUICtrlListView_DeleteAllItems($ListView2K)
GUICtrlCreateListViewItem("1|粉紅肉醬麵|70", $ListView2K)
GUICtrlCreateListViewItem("2|粉紅鮮蔬麵|70", $ListView2K)
GUICtrlCreateListViewItem("3|粉紅培根麵|85", $ListView2K)
GUICtrlCreateListViewItem("4|粉紅雞肉麵|85", $ListView2K)
GUICtrlCreateListViewItem("5|粉紅蛤蜊麵|90", $ListView2K)
GUICtrlCreateListViewItem("6|粉紅海鮮麵|115", $ListView2K)

GUICtrlCreateListViewItem("7|粉紅肉醬飯|70", $ListView2K)
GUICtrlCreateListViewItem("8|粉紅鮮蔬飯|70", $ListView2K)
GUICtrlCreateListViewItem("9|粉紅培根飯|85", $ListView2K)
GUICtrlCreateListViewItem("10|粉紅雞肉飯|85", $ListView2K)
GUICtrlCreateListViewItem("11|粉紅蛤蜊飯|90", $ListView2K)
GUICtrlCreateListViewItem("12|粉紅海鮮飯|115", $ListView2K)
;###############################################################################################

; 3K ###########################################################################################
_GUICtrlListView_DeleteAllItems($ListView3K)
GUICtrlCreateListViewItem("1|白醬鮮蔬(可奶素) 麵|65", $ListView3K)
GUICtrlCreateListViewItem("2|白醬培根麵|75", $ListView3K)
GUICtrlCreateListViewItem("3|白醬雞肉麵|75", $ListView3K)
GUICtrlCreateListViewItem("4|白醬蛤蜊麵|85", $ListView3K)
GUICtrlCreateListViewItem("5|白醬海鮮麵|110", $ListView3K)

GUICtrlCreateListViewItem("6|白醬鮮蔬(可奶素) 飯|65", $ListView3K)
GUICtrlCreateListViewItem("7|白醬培根飯|75", $ListView3K)
GUICtrlCreateListViewItem("8|白醬雞肉飯|75", $ListView3K)
GUICtrlCreateListViewItem("9|白醬蛤蜊飯|85", $ListView3K)
GUICtrlCreateListViewItem("10|白醬海鮮飯|110", $ListView3K)
;###############################################################################################

; 4K ###########################################################################################
_GUICtrlListView_DeleteAllItems($ListView4K)
GUICtrlCreateListViewItem("1|蒜香鮮蔬麵|55", $ListView4K)
GUICtrlCreateListViewItem("2|蒜香培根麵|65", $ListView4K)
GUICtrlCreateListViewItem("3|蒜香雞肉麵|65", $ListView4K)
GUICtrlCreateListViewItem("4|蒜香蛤蜊麵|75", $ListView4K)
GUICtrlCreateListViewItem("5|蒜香海鮮麵|90", $ListView4K)
;###############################################################################################

; 5K ###########################################################################################
_GUICtrlListView_DeleteAllItems($ListView5K)
GUICtrlCreateListViewItem("1|泰式酸辣海鮮湯麵|95", $ListView5K)
;###############################################################################################

; 6K ###########################################################################################
_GUICtrlListView_DeleteAllItems($ListView6K)
GUICtrlCreateListViewItem("1|玉米濃湯|25", $ListView6K)
;###############################################################################################

; 7K ###########################################################################################
_GUICtrlListView_DeleteAllItems($ListView7K)
GUICtrlCreateListViewItem("1|加飯/麵(半碗100g)|10", $ListView7K)
GUICtrlCreateListViewItem("2|加飯/麵(一碗200g)|20", $ListView7K)
GUICtrlCreateListViewItem("3|加青花菜|10", $ListView7K)
GUICtrlCreateListViewItem("4|加起司片|10", $ListView7K)
GUICtrlCreateListViewItem("5|玉米濃湯|20", $ListView7K)
;###############################################################################################

$OrderID=$OrderID+1
$cowbe0=GUICtrlCreateLabel($OrderID, 485,766,100,80)
GUICtrlSetFont($cowbe0,40,$FW_NORMAL,$GUI_FONTUNDER,"Comic Sans MS")

;_GUICtrlListClear($ListView2)
_GUICtrlListView_DeleteAllItems($ListView2)
_GUICtrlEdit_SetText($Input5, "")
_GUICtrlEdit_SetText($Input8, "")
_GUICtrlEdit_SetText($Input10, "")

Local $itemCounts = _GUICtrlListView_GetItemCount($ListView2)
                                             Local $totalPrice = 0
                                             Local $tArr
                                             For $i=0 To $itemCounts-1
                                                   $tArr = _GUICtrlListView_GetItemTextArray($ListView2, $i) ;get each item to the array

                                                   $totalPrice = $totalPrice + $tArr[ $tArr ] ;the last element is the price

Next

$cowbe=GUICtrlCreateLabel($totalPrice, 642,766,150,75)
GUICtrlSetFont($cowbe,40,$FW_NORMAL,$GUI_FONTUNDER,"Comic Sans MS")

            Case $Button2
                     GUICtrlSetState($ListView1, $GUI_FOCUS)

                     $mc = GUICtrlRead($Input5)
                     $jg = GUICtrlRead($Input8)
                     $num = _GUICtrlEdit_GetText($Input10)

                     GUICtrlCreateListViewItem($mc & "|" & $num & "|" & $num * $jg, $ListView2)

                     GUICtrlSetData($Input10, "")

                                             Local $itemCounts = _GUICtrlListView_GetItemCount($ListView2)
                                             Local $totalPrice = 0
                                             Local $tArr
                                             For $i=0 To $itemCounts-1
                                                   $tArr = _GUICtrlListView_GetItemTextArray($ListView2, $i) ;get each item to the array

                                                   $totalPrice = $totalPrice + $tArr[ $tArr ] ;the last element is the price

Next

;MsgBox(0,"111",$tArr[ $tArr ])

                                             ;show the total price

;GUISetFont(40,$FW_NORMAL, $GUI_FONTUNDER, "Comic Sans MS")

                                             $cowbe=GUICtrlCreateLabel($totalPrice, 642,766,150,75)

GUICtrlSetFont($cowbe,40,$FW_NORMAL,$GUI_FONTUNDER,"Comic Sans MS")

case $hCMenuText1 ;delete
_GUICtrlListView_DeleteItemsSelected($ListView2)

                                             Local $itemCounts = _GUICtrlListView_GetItemCount($ListView2)
                                             Local $totalPrice = 0
                                             Local $tArr
                                             For $i=0 To $itemCounts-1
                                                   $tArr = _GUICtrlListView_GetItemTextArray($ListView2, $i) ;get each item to the array

                                                   $totalPrice = $totalPrice + $tArr[ $tArr ] ;the last element is the price

Next

                                             ;show the total price
                                             $cowbe=GUICtrlCreateLabel($totalPrice, 642,766,150,75)
GUICtrlSetFont($cowbe,40,$FW_NORMAL,$GUI_FONTUNDER,"Comic Sans MS")

Case $export
If $jg="" Then
MsgBox(16, "Failure", "Failure")
Else
exporttoexcel()
EndIf

Case $print
If $jg="" Then
MsgBox(16, "Failure", "Failure")
Else
print()

EndIf

Case $export_all
If $jg="" Then
MsgBox(16, "Failure", "Failure")
Else
print()
exporttoexcel()
EndIf

   EndSwitch

WEnd

Func OnStateChange()
   $cur_sel = _GUICtrlListView_GetNextItem($ListView1)

_GUICtrlEdit_SetText($Input10, "1")
   GUICtrlSetData($Input5, _GUICtrlListView_GetItemText($ListView1, $cur_sel, 1))
   GUICtrlSetData($Input8, _GUICtrlListView_GetItemText($ListView1, $cur_sel, 2))

EndFunc ;==>OnStateChange

Func OnStateChange1K()

$cur_sel1K = _GUICtrlListView_GetNextItem($ListView1K) ; current selected

_GUICtrlEdit_SetText($Input10, "1")

GUICtrlSetData($Input5, _GUICtrlListView_GetItemText($ListView1K, $cur_sel1K, 1))
   GUICtrlSetData($Input8, _GUICtrlListView_GetItemText($ListView1K, $cur_sel1K, 2))

EndFunc ;==>OnStateChange

Func OnStateChange2K()

$cur_sel2K = _GUICtrlListView_GetNextItem($ListView2K) ; current selected

_GUICtrlEdit_SetText($Input10, "1")

GUICtrlSetData($Input5, _GUICtrlListView_GetItemText($ListView2K, $cur_sel2K, 1))
   GUICtrlSetData($Input8, _GUICtrlListView_GetItemText($ListView2K, $cur_sel2K, 2))

EndFunc ;==>OnStateChange

Func OnStateChange3K()

$cur_sel3K = _GUICtrlListView_GetNextItem($ListView3K) ; current selected

_GUICtrlEdit_SetText($Input10, "1")

GUICtrlSetData($Input5, _GUICtrlListView_GetItemText($ListView3K, $cur_sel3K, 1))
   GUICtrlSetData($Input8, _GUICtrlListView_GetItemText($ListView3K, $cur_sel3K, 2))

EndFunc ;==>OnStateChange

Func OnStateChange4K()

$cur_sel4K = _GUICtrlListView_GetNextItem($ListView4K) ; current selected

_GUICtrlEdit_SetText($Input10, "1")

GUICtrlSetData($Input5, _GUICtrlListView_GetItemText($ListView4K, $cur_sel4K, 1))
   GUICtrlSetData($Input8, _GUICtrlListView_GetItemText($ListView4K, $cur_sel4K, 2))

EndFunc ;==>OnStateChange

Func OnStateChange5K()

$cur_sel5K = _GUICtrlListView_GetNextItem($ListView5K) ; current selected

_GUICtrlEdit_SetText($Input10, "1")

GUICtrlSetData($Input5, _GUICtrlListView_GetItemText($ListView5K, $cur_sel5K, 1))
   GUICtrlSetData($Input8, _GUICtrlListView_GetItemText($ListView5K, $cur_sel5K, 2))

EndFunc ;==>OnStateChange

Func OnStateChange6K()

$cur_sel6K = _GUICtrlListView_GetNextItem($ListView6K) ; current selected

_GUICtrlEdit_SetText($Input10, "1")

GUICtrlSetData($Input5, _GUICtrlListView_GetItemText($ListView6K, $cur_sel6K, 1))
   GUICtrlSetData($Input8, _GUICtrlListView_GetItemText($ListView6K, $cur_sel6K, 2))

EndFunc ;==>OnStateChange

Func OnStateChange7K()

$cur_sel7K = _GUICtrlListView_GetNextItem($ListView7K) ; current selected

_GUICtrlEdit_SetText($Input10, "1")

GUICtrlSetData($Input5, _GUICtrlListView_GetItemText($ListView7K, $cur_sel7K, 1))
   GUICtrlSetData($Input8, _GUICtrlListView_GetItemText($ListView7K, $cur_sel7K, 2))

EndFunc ;==>OnStateChange

Func WM_Notify_Events($hWndGUI, $MsgID, $wParam, $lParam)
   #forceref $hWndGUI, $MsgID, $wParam
   Local $hWndFrom, $hWndListView1, $NMHDR, $NMLVDISPINFO, $NMLISTVIEW, $event, $iCode

   $hWndListView1 = $ListView1
   If Not IsHWnd($ListView1) Then $hWndListView1 = GUICtrlGetHandle($ListView1)

$hWndListView1K = $ListView1K
   If Not IsHWnd($ListView1K) Then $hWndListView1K = GUICtrlGetHandle($ListView1K)

$hWndListView2K = $ListView2K
   If Not IsHWnd($ListView2K) Then $hWndListView2K = GUICtrlGetHandle($ListView2K)

$hWndListView3K = $ListView3K
   If Not IsHWnd($ListView3K) Then $hWndListView3K = GUICtrlGetHandle($ListView3K)

$hWndListView4K = $ListView4K
   If Not IsHWnd($ListView4K) Then $hWndListView4K = GUICtrlGetHandle($ListView4K)

$hWndListView5K = $ListView5K
   If Not IsHWnd($ListView5K) Then $hWndListView5K = GUICtrlGetHandle($ListView5K)

$hWndListView6K = $ListView6K
   If Not IsHWnd($ListView6K) Then $hWndListView6K = GUICtrlGetHandle($ListView6K)

$hWndListView7K = $ListView7K
   If Not IsHWnd($ListView7K) Then $hWndListView7K = GUICtrlGetHandle($ListView7K)

   $NMHDR = DllStructCreate($tagNMHDR, $lParam)
   $event = DllStructGetData($NMHDR, 'Code')
   $hWndFrom = HWnd(DllStructGetData($NMHDR, "hWndFrom"))

$iCode = DllStructGetData($NMHDR, "Code")

Switch $iCode
Case $NM_DBLCLK
$tInfo = DllStructCreate($tagNMLISTVIEW, $lParam)
$Index = DllStructGetData($tInfo, "Index")
send("{enter}")

EndSwitch

   Switch $hWndFrom
            Case $hWndListView1
                     Switch $event
                           Case $LVN_ITEMCHANGED
                                    $NMLISTVIEW = DllStructCreate($tagNMLISTVIEW, $lParam)
                                    If BitAND(DllStructGetData($NMLISTVIEW, "Changed"), $LVIF_STATE) = $LVIF_STATE And _
DllStructGetData($NMLISTVIEW, "NewState") <> DllStructGetData($NMLISTVIEW, "OldState") Then OnStateChange()

                           Case $LVN_KEYDOWN
                                    $tInfo = DllStructCreate($tagNMLVKEYDOWN, $lParam)
                                    $Key=DllStructGetData($tInfo, "VKey")
                                    Switch $Key
                                             Case 0x30 To 0x39
                                                   _GUICtrlEdit_AppendText($Input10, DllStructGetData($tInfo, "VKey") - 0x30)
                                             Case 0x60 to 0x69
                                                   _GUICtrlEdit_AppendText($Input10, DllStructGetData($tInfo, "VKey") - 0x60)
                                    EndSwitch
EndSwitch

Case $hWndListView1K
                     Switch $event
                           Case $LVN_ITEMCHANGED
                                    $NMLISTVIEW = DllStructCreate($tagNMLISTVIEW, $lParam)
                                    If BitAND(DllStructGetData($NMLISTVIEW, "Changed"), $LVIF_STATE) = $LVIF_STATE And _
                                    DllStructGetData($NMLISTVIEW, "NewState") <> DllStructGetData($NMLISTVIEW, "OldState") Then OnStateChange1K()

                           Case $LVN_KEYDOWN
                                    $tInfo = DllStructCreate($tagNMLVKEYDOWN, $lParam)
                                    $Key=DllStructGetData($tInfo, "VKey")
                                    Switch $Key
                                             Case 0x30 To 0x39
                                                   _GUICtrlEdit_AppendText($Input10, DllStructGetData($tInfo, "VKey") - 0x30)
                                             Case 0x60 to 0x69
                                                   _GUICtrlEdit_AppendText($Input10, DllStructGetData($tInfo, "VKey") - 0x60)
                                    EndSwitch
EndSwitch

Case $hWndListView2K
Switch $event
                           Case $LVN_ITEMCHANGED
                                    $NMLISTVIEW = DllStructCreate($tagNMLISTVIEW, $lParam)
                                    If BitAND(DllStructGetData($NMLISTVIEW, "Changed"), $LVIF_STATE) = $LVIF_STATE And _
                                    DllStructGetData($NMLISTVIEW, "NewState") <> DllStructGetData($NMLISTVIEW, "OldState") Then OnStateChange2K()

                           Case $LVN_KEYDOWN
                                    $tInfo = DllStructCreate($tagNMLVKEYDOWN, $lParam)
                                    $Key=DllStructGetData($tInfo, "VKey")
                                    Switch $Key
                                             Case 0x30 To 0x39
                                                   _GUICtrlEdit_AppendText($Input10, DllStructGetData($tInfo, "VKey") - 0x30)
                                             Case 0x60 to 0x69
                                                   _GUICtrlEdit_AppendText($Input10, DllStructGetData($tInfo, "VKey") - 0x60)
                                    EndSwitch
EndSwitch

Case $hWndListView3K
Switch $event
                           Case $LVN_ITEMCHANGED
                                    $NMLISTVIEW = DllStructCreate($tagNMLISTVIEW, $lParam)
                                    If BitAND(DllStructGetData($NMLISTVIEW, "Changed"), $LVIF_STATE) = $LVIF_STATE And _
                                    DllStructGetData($NMLISTVIEW, "NewState") <> DllStructGetData($NMLISTVIEW, "OldState") Then OnStateChange3K()

                           Case $LVN_KEYDOWN
                                    $tInfo = DllStructCreate($tagNMLVKEYDOWN, $lParam)
                                    $Key=DllStructGetData($tInfo, "VKey")
                                    Switch $Key
                                             Case 0x30 To 0x39
                                                   _GUICtrlEdit_AppendText($Input10, DllStructGetData($tInfo, "VKey") - 0x30)
                                             Case 0x60 to 0x69
                                                   _GUICtrlEdit_AppendText($Input10, DllStructGetData($tInfo, "VKey") - 0x60)
                                    EndSwitch
                     EndSwitch

Case $hWndListView4K
Switch $event
                           Case $LVN_ITEMCHANGED
                                    $NMLISTVIEW = DllStructCreate($tagNMLISTVIEW, $lParam)
                                    If BitAND(DllStructGetData($NMLISTVIEW, "Changed"), $LVIF_STATE) = $LVIF_STATE And _
                                    DllStructGetData($NMLISTVIEW, "NewState") <> DllStructGetData($NMLISTVIEW, "OldState") Then OnStateChange4K()

                           Case $LVN_KEYDOWN
                                    $tInfo = DllStructCreate($tagNMLVKEYDOWN, $lParam)
                                    $Key=DllStructGetData($tInfo, "VKey")
                                    Switch $Key
                                             Case 0x30 To 0x39
                                                   _GUICtrlEdit_AppendText($Input10, DllStructGetData($tInfo, "VKey") - 0x30)
                                             Case 0x60 to 0x69
                                                   _GUICtrlEdit_AppendText($Input10, DllStructGetData($tInfo, "VKey") - 0x60)
EndSwitch
EndSwitch

Case $hWndListView5K
Switch $event
                           Case $LVN_ITEMCHANGED
                                    $NMLISTVIEW = DllStructCreate($tagNMLISTVIEW, $lParam)
                                    If BitAND(DllStructGetData($NMLISTVIEW, "Changed"), $LVIF_STATE) = $LVIF_STATE And _
                                    DllStructGetData($NMLISTVIEW, "NewState") <> DllStructGetData($NMLISTVIEW, "OldState") Then OnStateChange5K()

                           Case $LVN_KEYDOWN
                                    $tInfo = DllStructCreate($tagNMLVKEYDOWN, $lParam)
                                    $Key=DllStructGetData($tInfo, "VKey")
                                    Switch $Key
                                             Case 0x30 To 0x39
                                                   _GUICtrlEdit_AppendText($Input10, DllStructGetData($tInfo, "VKey") - 0x30)
                                             Case 0x60 to 0x69
                                                   _GUICtrlEdit_AppendText($Input10, DllStructGetData($tInfo, "VKey") - 0x60)
                                    EndSwitch

                     EndSwitch

Case $hWndListView6K
Switch $event
                           Case $LVN_ITEMCHANGED
                                    $NMLISTVIEW = DllStructCreate($tagNMLISTVIEW, $lParam)
                                    If BitAND(DllStructGetData($NMLISTVIEW, "Changed"), $LVIF_STATE) = $LVIF_STATE And _
                                    DllStructGetData($NMLISTVIEW, "NewState") <> DllStructGetData($NMLISTVIEW, "OldState") Then OnStateChange6K()

                           Case $LVN_KEYDOWN
                                    $tInfo = DllStructCreate($tagNMLVKEYDOWN, $lParam)
                                    $Key=DllStructGetData($tInfo, "VKey")
                                    Switch $Key
                                             Case 0x30 To 0x39
                                                   _GUICtrlEdit_AppendText($Input10, DllStructGetData($tInfo, "VKey") - 0x30)
                                             Case 0x60 to 0x69
                                                   _GUICtrlEdit_AppendText($Input10, DllStructGetData($tInfo, "VKey") - 0x60)
                                    EndSwitch

                     EndSwitch

Case $hWndListView7K
Switch $event
                           Case $LVN_ITEMCHANGED
                                    $NMLISTVIEW = DllStructCreate($tagNMLISTVIEW, $lParam)
                                    If BitAND(DllStructGetData($NMLISTVIEW, "Changed"), $LVIF_STATE) = $LVIF_STATE And _
                                    DllStructGetData($NMLISTVIEW, "NewState") <> DllStructGetData($NMLISTVIEW, "OldState") Then OnStateChange7K()

                           Case $LVN_KEYDOWN
                                    $tInfo = DllStructCreate($tagNMLVKEYDOWN, $lParam)
                                    $Key=DllStructGetData($tInfo, "VKey")
                                    Switch $Key
                                             Case 0x30 To 0x39
                                                   _GUICtrlEdit_AppendText($Input10, DllStructGetData($tInfo, "VKey") - 0x30)
                                             Case 0x60 to 0x69
                                                   _GUICtrlEdit_AppendText($Input10, DllStructGetData($tInfo, "VKey") - 0x60)
                                    EndSwitch

                     EndSwitch

   EndSwitch
;EndSwitch
   Return $GUI_RUNDEFMSG
EndFunc ;==>WM_Notify_Events

Func exporttoexcel();EXPORT TO EXCEL
$col = 0
$count = _GUICtrlListView_GetItemCount($ListView2)
GUICtrlSetState($export,$gui_enable)

$excel = _ExcelBookNew()
$oWorkbook = _Excel_BookNew($excel)

_ExcelWriteCell($excel,"名稱",2,2)
_ExcelWriteCell($excel,"數量",2,3)
_ExcelWriteCell($excel,"金額",2,4)
_ExcelWriteCell($excel,"總金額",2,5)
_ExcelWriteCell($excel," ",2,6)
_ExcelWriteCell($excel," ",2,7)
_ExcelWriteCell($excel," ",2,8)
_ExcelWriteCell($excel," ",2,9)
_ExcelWriteCell($excel," ",2,10)
_ExcelWriteCell($excel," ",2,11)
_ExcelWriteCell($excel," ",2,12)
_ExcelWriteCell($excel," ",2,13)
_ExcelWriteCell($excel," ",2,14)
_ExcelWriteCell($excel," ",2,15)
_ExcelWriteCell($excel," ",2,16)
_ExcelWriteCell($excel," ",2,17)

For $colexcel = 0 To $col

$i = 0
do
   _ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,0),3 + $i,2)
   _ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,1),3 + $i,3)
   _ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,2),3 + $i,4)
   _ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,3),3 + $i,5)
   _ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,4),3 + $i,6)
   _ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,5),3 + $i,7)
   _ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,6),3 + $i,8)
   _ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,7),3 + $i,9)
   _ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,8),3 + $i,10)
_ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,9),3 + $i,11)
_ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,10),3 + $i,12)
_ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,11),3 + $i,13)
_ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,12),3 + $i,14)
_ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,13),3 + $i,15)
_ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,14),3 + $i,16)
_ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,15),3 + $i,17)
_ExcelWriteCell($excel, _GUICtrlListView_GetItemText($ListView2,$i,16),3 + $i,18)

$i = $i + 1
Until $i = $count
Next

_ExcelWriteCell($excel, "ID: "&$OrderID,2 ,1)
_ExcelWriteCell($excel, $totalPrice,2 + $i,5) ;總金額

;MsgBox(64,"Message","Export completed!" & @CRLF _
;                      & " "& @CRLF _
;                      & "Output excel file on your Desktop!",2)

DirCreate(@ScriptDir & "\Excel")

_Excel_BookSaveAs($oWorkbook, @ScriptDir & "\Excel\"& @YEAR & @MON & @MDAY & @HOUR & @MIN & @SEC & ".xlsx", Default,True)
_Excel_Close($excel,True,True)

EndFunc

Func _ExcelBookNew($fVisible = 0)
Local $oExcel = ObjCreate("Excel.Application")
If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
If NOT IsNumber($fVisible) Then Return SetError(2, 0, 0)
If $fVisible > 1 Then $fVisible = 1
If $fVisible < 0 Then $fVisible = 0
With $oExcel
.Visible = $fVisible
.WorkBooks.Add
.ActiveWorkbook.Sheets(1).Select()
EndWith
Return $oExcel

EndFunc ;==>_ExcelBookNew

Func _ExcelWriteCell($oExcel, $sValue, $sRangeOrRow, $iColumn = 1)
If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
If NOT StringRegExp($sRangeOrRow, "", 0) Then
   If $sRangeOrRow < 1 Then Return SetError(2, 0, 0)
If $iColumn < 1 Then Return SetError(2, 1, 0)
$oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).Value = $sValue
Return 1
Else
$oExcel.Activesheet.Range($sRangeOrRow).Value = $sValue
Return 1
EndIf
EndFunc ;==>_ExcelWriteCell

Func print()

DirCreate(@ScriptDir & "\PrintIMG")
         _ScreenCapture_CaptureWnd(@ScriptDir & "\PrintIMG\"&@YEAR&"-"&@mon&"-"&@MDAY&" "&@HOUR&@MIN&".jpg", $Form1)
         ShellExecute(@ScriptDir & "\PrintIMG\"&@YEAR&"-"&@mon&"-"&@MDAY&" "&@HOUR&@MIN&".jpg", "", "","print")

EndFunc

Func _Exit()
   Exit
EndFunc ;==>_Exit

kk_lee69 发表于 2017-2-14 09:49:19

回复 3# ipmitool

設個 TXT檔案

使用 _FileWriteToLine每 EXCEL 目前寫入的行數都寫入到 TXT 檔案的第一行
下次要使用時取讀這個檔案就好

lxwlxwayy 发表于 2017-2-14 10:38:51

申请行数变量{:face (394):}

kk_lee69 发表于 2017-2-14 14:28:35

回复 9# lxwlxwayy

請教一下【申请行数变量】這句話是啥意思??

小弟愚笨看不太懂.....有甚麼行數變量可以用在這邊的??

ipmitool 发表于 2017-2-14 15:45:33

回复 10# kk_lee69

謝謝哥, 用你的方法已解決嘍~

页: [1]

AUTOIT CN's Archiver

[已解决]如何每次寫入資料到 excel 時, 可以一直往下寫, 不要蓋掉原本的檔案?