#coding=utf-8
import xlwings as xw
import pandas as pd
import time

start_row = 2 # 處理Excel文件開始行
end_row = 10002 # 處理Excel結(jié)束行

#記錄打開表單開始時(shí)間
start_open_time = time.time()

#指定不顯示地打開Excel，讀取Excel文件
app = xw.App(visible=False, add_book=False)
wb = app.books.open('D:/PYTHON/TEST_CODE/Book300s.xlsx') # 打開Excel文件
sheet = wb.sheets[0] # 選擇第0個(gè)表單

#記錄打開Excel表單結(jié)束時(shí)間
end_open_time = time.time()

#記錄開始循環(huán)計(jì)算時(shí)間
start_run = time.time()

row_content = []
#讀取Excel表單前10000行的數(shù)據(jù)，Python的in range是左閉右開的,到10002結(jié)束,但區(qū)間只包含2到10001這一萬(wàn)條
for row in range(start_row, end_row):
  row_str = str(row)
  #循環(huán)中引用Excel的sheet和range的對(duì)象，讀取B列和C列的每一行的值，對(duì)比計(jì)算
  start_value = sheet.range('B' + row_str).value
  end_value = sheet.range('C' + row_str).value
  if start_value <= end_value:
    values = end_value - start_value
    #同時(shí)測(cè)試List數(shù)組添加記錄
    row_content.append(values)

#計(jì)算和
total_values = sum(row_content)

#記錄結(jié)束循環(huán)計(jì)算時(shí)間
end_run = time.time()
sheet.range('E2').value = str(total_values)
sheet.range('E3').value = '使用Sheet計(jì)算時(shí)間(秒):' + str(end_run - start_run)

#保存并關(guān)閉Excel文件
wb.save()
wb.close()

print ('結(jié)果總和:', total_values)
print ('打開并讀取Excel表單時(shí)間(秒):',  end_open_time - start_open_time)
print ('計(jì)算時(shí)間(秒):',  end_run - start_run)
print ('處理數(shù)據(jù)條數(shù):' , len(row_content))

Option Explicit

Sub VBA_CAL_Click()
  Dim i_count As Long
  Dim offset_value, total_offset_value As Double
  Dim st, et As Date
  st = Time()

  i_count = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
  i_count = 10001
  For i_count = 2 To i_count
     If Range("C" & i_count).Value > Range("B" & i_count).Value Then
       offset_value = Range("C" & i_count).Value - Range("B" & i_count).Value
       total_offset_value = total_offset_value + offset_value
     End If
  Next i_count

  et = Time()
  Range("E2").Value = total_offset_value
  Range("E3").Value = et - st

  MsgBox "Result: " & total_offset_value & Chr(10) & "Running time: " & et - st
End Sub

#coding=utf-8
import xlwings as xw
import pandas as pd
import time

#記錄打開表單開始時(shí)間
start_open_time = time.time()

#指定不顯示地打開Excel，讀取Excel文件
app = xw.App(visible=False, add_book=False)
wb = app.books.open('D:/PYTHON/TEST_CODE/Book300s.xlsx') # 打開Excel文件
sheet = wb.sheets[0] # 選擇第0個(gè)表單

#記錄打開Excel表單結(jié)束時(shí)間
end_open_time = time.time()

#記錄開始循環(huán)計(jì)算時(shí)間
start_run = time.time()

row_content = []
#讀取Excel表單前10000行的數(shù)據(jù)，并計(jì)算B列和C列的差值之和
list_value = sheet.range('A2:D10001').value
for i in range(len(list_value)):
   #使用Python的類庫(kù)直接訪問Excel的表單是很緩慢的，不要在Python的循環(huán)中引用sheet等Excel表單的單元格，
   #而是要用List一次性讀取Excel里的數(shù)據(jù)，在List內(nèi)存中計(jì)算好了，然后返回結(jié)果
   start_value = list_value[i][1]
   end_value = list_value[i][2]
   if start_value <= end_value:
     values = end_value- start_value
     #同時(shí)測(cè)試List數(shù)組添加記錄
     row_content.append(values)

#計(jì)算和
total_values = sum(row_content)
#記錄結(jié)束循環(huán)計(jì)算時(shí)間
end_run = time.time()
sheet.range('E2').value = str(total_values)
sheet.range('E3').value = '使用List 計(jì)算時(shí)間(秒):' + str(end_run - start_run)

#保存并關(guān)閉Excel文件
wb.save()
wb.close()

print ('結(jié)果總和:', total_values)
print ('打開并讀取Excel表單時(shí)間(秒):',  end_open_time - start_open_time)
print ('計(jì)算時(shí)間(秒):',  end_run - start_run)
print ('處理數(shù)據(jù)條數(shù):' , len(row_content))