win32com操作xlsm文件

python操作xlsm文件

• 因作业需求，需要对一使用VBA编程的excel工具进行操作，简单的说就是设置路面结构的参数，再设置荷载的参数，输入点的坐标以提取该点的各项应力值、应变值，由于我想提取一个矩形窗格内的成百上千的点数据，手动输入太麻烦，于是想到了win32com，将点的坐标数据批量快速生成出来填入xlsm表中，并提取计算出的应力应变值生成csv文件。
• 本人第一次尝试面向对象编程
• 本人第一次尝试pycharm写py
• 两次第一次都很棒
• 写出这个openpave.xlsm的人才是大牛
• 真正有趣的发现是，可以直接把一个numpy矩阵赋值给表格的一整个区域（刚开始还一个单元格一个单元格地往里面输入，特别慢）
• 废话不多说，上代码。注释较少，估计也没有太多需求，只是为了自己记录，但以防以后再看不知道写的啥玩意儿，有空(x)再来添注释。

"""
@date:20/04/2020

"""
import win32com.client as win32
import numpy as np
import pandas as pd
import os

if not os.path.exists('./data'):
    os.mkdir('./data')


class Pave:
    def __init__(self, l1t_range, l2t_range, l1e_range, l4e_range, zrr):
        self.l1tr = l1t_range
        self.l2tr = l2t_range
        self.l1er = l1e_range
        self.l4er = l4e_range
        self.zr = zrr

        self.layers = np.array([[100, 7000, 0.35, 1],
                                [300, 10000, 0.15, 1],
                                [150, 250, 0.3, 1],
                                [1000, 50, 0.45, 1]])
        self.z = self.layers[0, 0] / 2  # 初始值，不必在此处改
        # self.z = self.layers[0, 0]*1/3
        # the position of two loads are (206.5,206.5), (526,206.5)
        # the calculation range of (x,y) is (0,0) to (732.5, 41.3),
        # the calculation depth of z is the middle of first layer
        self.loads = np.array([[100 + 106.5, 100 + 106.5, 0, 700, 106.5],
                               [100 + 426, 100 + 106.5, 0, 700, 106.5]])
        # set a point every 20 mm , add up to 74*42 /4 = 3108/4
        self.points = np.array([[20 * i for i in range(74 // 2) for j in range(42 // 2)],
                                [20 * j for i in range(74 // 2) for j in range(42 // 2)],
                                [self.z for i in range(74 * 42 // 4)],
                                [1 for i in range(74 * 42 // 4)]])

        self.excel = win32.DispatchEx('Excel.Application')
        self.excel.Visible = False
        self.workbook = self.excel.Workbooks.Open(os.getcwd() + '\\openpave.xlsm')
        print('Excel opened')
        self.sheet = self.workbook.Worksheets(1)

    def setV(self, cell, value):
        self.sheet.Cells(cell[0], cell[1]).Value = value

    def setLy(self, l1t=100, l2t=300, l1e=7000, l4e=50, z=50):
        self.layers[0, 0] = l1t
        self.layers[1, 0] = l2t
        self.layers[0, 1] = l1e
        self.layers[3, 1] = l4e
        self.z = z
        self.points[2, :] = [self.z for i in range(74 * 42 // 4)]
        sc = np.array([9, 3])  # start cell
        ec = sc + np.array([self.layers.shape[0] - 1, self.layers.shape[1] - 1])  # end cell
        for row in range(self.layers.shape[0]):
            self.sheet.Range(self.sheet.Cells(sc[0] + row, sc[1]),
                             self.sheet.Cells(sc[0] + row, ec[1])).Value = self.layers[row, :]

        while self.sheet.Cells(sc[0] + self.layers.shape[0], 3).Value is not None:
            self.sheet.Rows(sc[0] + self.layers.shape[0]).Delete()

    def setLd(self):
        sc = np.array([self.layers.shape[0] + 12, 3])
        # the load that set first will change after radius been changed
        for row in range(self.loads.shape[0]):
            for col in range(self.loads.shape[1]):
                self.setV(sc + np.array([row, col]), self.loads[row, col])

        while self.sheet.Cells(sc[0] + self.loads.shape[0], 3).Value is not None:
            self.sheet.Rows(sc[0] + self.loads.shape[0]).Delete()

    def setPt(self):
        sc = np.array([self.layers.shape[0] + self.loads.shape[0] + 15, 3])
        ec = sc + np.array([self.points.shape[0] - 1, self.points.shape[1] - 1])
        self.sheet.Range(self.sheet.Cells(sc[0], sc[1]),
                         self.sheet.Cells(ec[0], ec[1])).Value = self.points

    def data2csv(self):
        sc = np.array([self.layers.shape[0] + self.loads.shape[0] + 15, 3])
        ec = sc + np.array([30, self.points.shape[1] - 1])  # end cell
        data = self.sheet.Range(self.sheet.Cells(sc[0], sc[1]), self.sheet.Cells(ec[0], ec[1])).GetValue()
        columns = ['X (mm)', 'Y (mm)', 'Z(mm)', 'Layer', 'dx (mm)', 'dy (mm)', 'dz (mm)', 'sxx (kPa)',
                   'syy (kPa)', 'szz (kPa)', 'txy (kPa)', 'txz (kPa)', 'tyz (kPa)', 'exx (me)', 'eyy (me)',
                   'ezz (me)', 'gxy (me)', 'gxz (me)', 'gyz (me)', 's1 (kPa)', 's2 (kPa)', 's3 (kPa)', 't1 (kPa)',
                   't2 (kPa)', 't3 (kPa)', 'e1 (me)', 'e2 (me)', 'e3 (me)', 'g1 (me)', 'g2 (me)', 'g3 (me)']
        df = pd.DataFrame(np.array(data).T, columns=columns)
        # L1T: Thickness of layer1, L4E: Elastic Modulus of layer 4, z: depth of point
        name = 'L1T-{}_L2T-{}_L1E-{}_L4E-{}_z-{}.csv'.format(self.layers[0, 0],
                                                             self.layers[1, 0],
                                                             self.layers[0, 1],
                                                             self.layers[3, 1],
                                                             self.z)
        print('Saving {} now'.format(name))
        df.to_csv('./data/{}'.format(name))

    def close(self):
        self.workbook.Close(SaveChanges=0)
        self.excel.Application.Quit()

    def render(self):
        self.setLy()
        self.setLd()
        for l1t in self.l1tr:
            for l2t in self.l2tr:
                for l1e in self.l1er:
                    for l4e in self.l4er:
                        for z in self.zr:
                            self.setLy(l1t, l2t, l1e, l4e, z)
                            self.setPt()
                            self.data2csv()
        print("all done")


if __name__ == '__main__':
    l1tRange = [150]  # the range of the thickness of layer1
    l2tRange = [300]  # the range of the thickness of layer2
    l1eRange = [7000]  # the range of the elastic modulus of layer1
    l4eRange = [45]  # the range of the elastic modulus of layer4
    z_range = [10*i for i in range(16)]
    pave = Pave(l1tRange, l2tRange, l1eRange, l4eRange, z_range)
    try:
        pave.render()
    finally:
        pave.close()