Excel live AUTOMATION like VBA¶

Import win32com¶

import win32com
print(win32com.__gen_path__) # delete gen_py to RUN the SCRIPT if not responding

C:\Users\HP\AppData\Local\Temp\gen_py\3.7

import win32com.client as win32
ExcelApp = win32.gencache.EnsureDispatch('Excel.Application')

wb = ExcelApp.Workbooks.Open("C:\\Users\\HP\\Documents\\Python Anaconda\\formula_test2.xlsx")
ExcelApp.Visible = True

Sheet1 = wb.Worksheets(1)
Sheet2 = wb.Worksheets(2)
Sheet3 = wb.Worksheets(3)
Sheet4 = wb.Worksheets(4)

Range1 = Sheet2.Range("C10:J10")
Range1.Value = ["iPhone","Notebook","Earpods","Charger","Cabel","Case","Watch","Discount"]

Range2 = Sheet2.Range("C11:J11")
Range2.Value = [13000,24000,4000,500,500,350,12000,-1000]

Sheet2.Range("C12:J12").FormulaR1C1 = "=SUM(R[-1]C[0]:R[-1]C[0])"
Sheet2.Range("C13").FormulaR1C1 = "=SUM(R[-1]C[7]:R[-1]C[0])"
Sheet2.Range("C14").FormulaR1C1 = "=ROUND(AVERAGE(R[-2]C[6]:R[-2]C[0]),2)"

# INSERT FROM ANOTHER SHEET
X = Sheet2.Range("C1")
X.Value = Sheet3.Range("B2")

# INSERT FROM ANOTHER SHEET
X = Sheet2.Range("C3")
X.Value = Sheet3.Range("B3")

# INSERT FROM ANOTHER SHEET
X = Sheet2.Range("C5")
X.Value = Sheet3.Range("B4")

# INSERT FROM ANOTHER SHEET
X = Sheet2.Range("F5")
X.Value = Sheet3.Range("B5")

# INSERT FROM ANOTHER SHEET
X = Sheet2.Range("I5")
X.Value = Sheet3.Range("B6")

# SUM
Sheet2.Range("C7").FormulaR1C1 = "=SUM(R[-2]C[6]:R[-2]C[0])"

Color¶

# RGB transformed into HEX
def rgb_to_hex(rgb):
    bgr = (rgb[2], rgb[1], rgb[0])
    strValue = '%02x%02x%02x' % bgr
    # print(strValue)
    iValue = int(strValue, 16)
    return iValue

X = Sheet2.Range("C10:J10")
X.Interior.Color = rgb_to_hex((0,204,255))

X = Sheet2.Range("C13")
X.Interior.Color = 255 #Red

X = Sheet2.Range("J11:J12")
X.Interior.Color = 65280 #Green

Pandas¶

import numpy as np
import pandas as pd
Range = Sheet1.Range("A1:D10").Value
df = pd.DataFrame(Range)
new_header = df.iloc[0] #grab the first row for the header
df = df[1:] #take the data less the header row
df.columns = new_header #set the header row as the df header
df

# SUM of all rows
sum_row = df.sum()
sum_row.name = 'TOTAL:'
df = df.append(sum_row.transpose())
df

# SUM of all columns
sum_col = df.sum(axis=1)
df['TOTAL:'] = sum_col
df

df.shape

(10, 5)

Save to Excel¶

row_count = df.shape[0]
col_count = df.shape[1]

column_names = df.columns
column_names

data = df.to_records().tolist()
data

start_cell = Sheet4.Cells(2,1)
ending_cell = Sheet4.Cells(2 + row_count - 1, 1 + col_count)

col_start_cell = Sheet4.Cells(1,2)
col_end_cell = Sheet4.Cells(1+col_count)

ExcelApp.Range(col_start_cell, col_end_cell).Value = column_names
ExcelApp.Range(start_cell, ending_cell).Value = data

Range = Sheet4.Range(col_start_cell, col_end_cell)
for cell in Range:
    cell.Interior.Color = rgb_to_hex((0,204,255))

Range = Sheet4.Range("A2:A11")
for cell in Range:
    cell.Interior.Color = rgb_to_hex((0,204,255))

	SPRING	SUMMER	AUTUMN	WINTER
1	1000	2000	2500	2750
2	1000	2000	2500	2750
3	1000	2000	2500	2750
4	1000	2000	2500	2750
5	1000	2000	2500	2750
6	1000	2000	2500	2750
7	1000	2000	2500	2750
8	1000	2000	2500	2750
9	1000	2000	1000	1000
TOTAL:	9000	18000	21000	23000

	SPRING	SUMMER	AUTUMN	WINTER	TOTAL:
1	1000	2000	2500	2750	8250.0
2	1000	2000	2500	2750	8250.0
3	1000	2000	2500	2750	8250.0
4	1000	2000	2500	2750	8250.0
5	1000	2000	2500	2750	8250.0
6	1000	2000	2500	2750	8250.0
7	1000	2000	2500	2750	8250.0
8	1000	2000	2500	2750	8250.0
9	1000	2000	1000	1000	5000.0
TOTAL:	9000	18000	21000	23000	71000.0