rocket2020的博客

原创 Unite 1 Health

原创 Text 1 Do We Need Extra Vitamins?

Vitamins

原创 C6.19 使用描述统计和直方图制定目标

import pandas as pdimport matplotlib.pyplot as pltimport xlwings as xwdf = pd.read_excel(r'd:\python_file\描述统计.xlsx')df.columns = ['序号', '员工姓名', '月销售额'] # 重命名数据列df = df.drop(columns=['序号','员工姓名']) # 删除列df_describe = df.astype('float').describe(.

原创 C6.11 批量统计工作簿的最大值与最小值

import osimport xlwings as xwimport pandas as pdapp = xw.App(visible=True, add_book=False)file_path = r'd:\python_file\产品销售统计表'file_list = os.listdir(file_path)for i in file_list: if os.path.splitext(i)[1] == '.xlsx': workbook = app.book.

原创 Python ODBC连接到DB2

Python DB2 odbc 连接

原创 C6.04 筛选一个工作簿中的所有工作表数据

Purpose:下图所示, 工作簿按月份存放在不同工作表中的物品采购明细数据更改为按物品名称存放在不同工作表中.import xlwings as xwimport pandas as pdapp = xw.App(visible=True, add_book=False)workbook = app.books.open(r'd:\python_file\采购表.xlsx')worksheet = workbook.sheetstable = pd.DataFrame()for i

原创 038 批量转换工作簿的文件格式

# 将.xlsx转换为.xlsfrom pathlib import Pathimport xlwings as xwapp = xw.App(visible=False, add_book=False)folder_path = Path('D:\\python_file\\')file_list = folder_path.glob('*.xlsx') # 获取文件夹下所有'.xlsx'格式工作簿的文件路径for i in file_list: new_file_path = s

原创 034 批量新建并保存多个工作表

034 批量新建并保存多个工作表import xlwings as xwapp = xw.App(visible=False, add_book=False)for i in range(1, 5): wbs = app.books.add() # 新建工作表 wbs.save(f'D:\\python_file\\{i}.xlsx') wbs.close()app.quit()

原创 033 新建并保存工作表

033 新建并保存工作表import xlwings as xwapp = xw.App(visible=False,add_book=False) # 启动EXCEL程序wb = app.books.add() # 新建工作表wb.save(r'D:\python_file\new_creation.xlsx')wb.close() # 关闭工作簿app.quit() # 退出EXCEL程序...

原创 030 移动并重命名工作表

030 移动并重命名工作表from pathlib import Pathold_file_path = Path(r'D:\python\111.xlsx')new_file_path = Path(r'D:\python_file\receiving.xlsx')old_file_path.rename(new_file_path)

原创 031 解析工作簿的路径信息

031 解析工作簿的路径信息from pathlib import Pathfile_path = Path(r'D:\python_file\receiving.xlsx')path = file_path.parent # 提取工作簿所在文件夹的路径file_name = file_path.name # 提取工作簿的文件名stem_name = file_path.stem # 提取工作簿的文件主名suf_name = file_path.suffix

原创 6.9对一个工作簿中的所有工作表分别求和并将求和结果写入固定单元格

# 对一个工作簿中的所有工作表分别求和并将求和结果写入固定单元格import xlwings as xwimport pandas as pdapp = xw.App(visible=False,add_book=False)workbook = app.books.open(r'D:\python_file\采购表.xlsx')worksheet = workbook.sheetsfor i in worksheet: values = i.range("a1").expand('ta

原创 VBA禁止更改工作表名称

'VBA禁止更改工作表名称Private Sub workbook_sheetselectionchange(ByVal sh As Object, ByVal target As Range) If Sheet16.Name <> "数据" Then Sheet16.Name = "数据" ThisWorkbook.SaveEnd Sub

原创 VBA批量导入多个文件夹下的图片到PPT

1.需要导入到PPT的照片文件夹CodingSub InsertPicture()Dim oPPT As PresentationDim oSlide As SlideDim nSlide As ByteDim oCL As CustomLayoutDim Shp As ShapeDim myFileDim filearr()Dim filearr0()Dim myPath As StringDim sPath As StringDim FileName As String

原创 Mysql 提取 IBM DB2 表的字段及其描述

Mysql 提取 IBM DB2 表的字段及其描述 Column descriptions:IBM DB2 Iseries Field Names and Field Descriptions# Column descriptions:Select SYSTEM_TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, varchar(COLUMN_TEXT, 50) As COLUMN_DESC From QSYS2.SYSCOLUMNSWhere SYSTEM_TABLE_

原创 6.8 对一个工作簿中的所有工作表分别求和

需要汇总的表格式如下图：Code 如下表：import xlwings as xwimport pandas as pdapp = xw.App(visible=False,add_book=False)workbook = app.books.open(r'D:/python_file/采购表.xlsx')worksheets = workbook.sheets # 列出工作簿中所有工作表for i in worksheets: values = i.range('a1'.

原创 6.7 将多个工作簿数据分类汇总到一个工作簿

import osimport xlwings as xwimport pandas as pdapp = xw.App(visible=False,add_book=False)file_path = r'D:\python_file\销售表'file_list = os.listdir(file_path) # 列出文件夹下所有文件和子文件夹的名称collection = [] for i in file_list: # 遍历文件夹下的文件 if os.path.s

原创 6.6 批量分类汇总多个工作簿中的指定工作表

文件路径每张表格式相同，如下图所示：Python 处理如下import osimport xlwings as xwimport pandas as pdapp = xw.App(visible=False,add_book=False)file_path = r'D:\python_file\销售表'file_list = os.listdir(file_path)for i in file_list: if os.path.splitext(i)[1] ..

原创 6.5 对多个工作簿中的工作表分别进行分类汇总

import osimport xlwings as xwimport pandas as pdapp = xw.App(visible=False,add_book=False)file_path = r'D:\python_file\销售表'file_list = os.listdir(file_path) # 列出文件夹下所有文件和子文件夹的名称for i in file_list: if os.path.splitext(i)[1] == '.xlsx': # 判断文件是否

原创 13-3 合并内容相同的连续单元格

Sub MergeLinkedCells() Dim r as Integer Dim i as integer Application.DisplayAlerts = False With Sheet1 r = .cells(row.count,1).end(xlup).row for i = r to 2 step-1 if .cells(i,2).value = .cells(i-1,2).value then .range(.cells(i-1,2),.cells(i,

原创 13-2 合并单元格时连接每个单元格的文本

Sub Mergecells6() Dim MergeStr As String Dim MergeRng As Range Dim rng As Range Set MergeRng = Range("a1:b10") For Each rng In MergeRng MergeStr = MergeStr & rng & " " Next Application.DisplayAlerts = False M

原创 1-5 使用Resize属性返回调整后的单元格区域

sub RngResize() sheets("sheet1").range("a1").resize(4,4).SelectEnd sub

原创 1-4 使用Offset属性返回单元格区域

sub RngOffset() sheets("sheet1").range("a1:b2").offset(2,2).selectend sub

原创 1-3 使用快捷记号实现快速输入

sub FastMark() [a1] = "Excel 2007"end sub

原创 1-2 使用Cells属性引用单元格区域

sub Mycell() dim i as byte for i = 1 to 10 sheets("sheet1").cells(i,1).value = i nextEnd sub

原创 1-1 使用Range属性引用单元格区域

sub MyRng() range("a1:b4,d5:e8").select range("a1").formula = "=Rnd()" range("a1:b4 b2:c6").value = 10 range("a1","b4").font.italic = Trueend sub

原创 11-3 判断单元格公式是否存在错误

sub CellFormulaIsWrong() if IsError(range("a1").value) = true then msgbox "a1 单元格错误类型为:" & range("a1").text Else MsgBox "a1 单元格公式结果为:" & range("a1").value end ifEnd Sub

原创 筛选一个工作簿中的所有工作表数据

import xlwings as xwimport pandas as pdapp = xw.App(visible=False,add_book=False) # 启动excelworkbook = app.books.open(r'd:\python_file\采购表.xlsx') # 打开要处理的工作簿worksheet = workbook.sheets # 列出工作簿中的所有工作表table = pd.DataFrame()

原创 6.2 批量排序多个工作簿中的数据

6.2 批量排序多个工作簿中的数据import time as tmt1 = tm.time()import osimport xlwings as xwimport pandas as pdapp = xw.App(visible=False,add_book=False)file_path = r'd:\python_file\产品销售统计表'file_list = os.listdir(file_path)for i in file_list: if os.path.spli

原创 6.1 批量升序排序一个工作簿中的所有工作表

import xlwings as xwimport pandas as pdapp = xw.App(visible=False,add_book=False)workbook = app.books.open('d:\\python_file\\产品销售统计表.xlsx')worksheet = workbook.sheets # 列出工作簿中的所有工作表for i in worksheet: # 遍历工作簿中的工作表 values = i.range('A1').expand

原创 批量提取一个工作簿中所有工作表的唯一值并汇总

import osimport xlwings as xwapp = xw.App(visible=True,add_book=False)wb = app.books.open('d:\\python_file\\上半年销售统计表.xlsx')data = list() # 创建一个空列表用于存放书名和销量的明细数据for i, sht in enumerate(wb.sheets): values = sht['A2'].expand('table').value data

原创 批量提取一个工作簿中所有工作表的唯一值

# 5.14 批量提取一个工作簿中所有工作表的唯一值import xlwings as xwapp = xw.App(visible=False,add_book=False) # 启动excel程序workbook = app.books.open('d:\\python_file\\上半年产品销售统计表.xlsx') # 打开指定工作簿data = [] # 创建一个空列表用于存放书名数据for i, worksheet in enumerate(workbook.sheet

原创 在多个工作簿的指定工作表中批量追加行数据

# 在多个工作簿的指定工作表中批量追加行数据import osimport xlwings as xwnewContent = [['双肩包','64','110'],['腰包','23','58']] # 给出要追加的行数据app = xw.apps.add() # 打开excel 并新增一个工作簿file_path = 'd:\\python_file\\分部信息'file_list = os.listdir(file_path)for i in file_list:

原创 批量提取一个工作簿中所有工作表的列数据

import xlwings as xwimport pandas as pdapp = xw.App(visible=False,add_book=False)workbook = app.books.open('d:\\python_file\\采购表.xlsx')worksheet = workbook.sheetscolumn=['采购日期','采购金额'] # 指定要提取的列的列标题data = []for i in worksheet: values = i.range

原创 如何使用VBA进行多表汇总

Sub 如何使用VBA进行多表汇总() Dim AdoConn As New ADODB.Connection Dim AdoRst As ADODB.Recordset Dim strConn As String Dim strSQL As String Application.ScreenUpdating = False '设置连接字符串 strConn = " Provider=Microsoft.ACE.OLEDB.12.0;" &

原创 python 批量打印工作簿

import osimport xlwings as xwfile_path = 'd:\\python_file' # 给出需要打印的工作簿所在文件夹的路径file_list = os.listdir(file_path) # 列出文件夹下所有文件和子文件夹app = xw.App(visible=False,add_book=False) # 打开工作表，不新建表for i in file_list: if i.startswith('~$'): #

原创 Python 按条件将一个工作表拆分为多个工作簿

import xlwings as xwfile_path = 'd:\\python_file\\产品统计表.xlsx' # 给出来源工作簿的文件路径sheet_name = '统计表' # 给出要拆分的工作表的名称app = xw.App(visible=False,add_book=False) # 启动Excel程序workbook = app.books.open(file_path) # 打开来源工作簿

原创 Python 将一个工作簿的所有工作表批量复制到其他工作簿

import osimport xlwings as xwapp = xw.App(visible=False,add_book=False)file_path = 'd:\\python_file\\销售表' # 给出目标工作簿所在的文件夹路径file_list = os.listdir(file_path) # 列出文件与子文件夹workbook = app.books.open('d:\\python_file\\信息表.xlsx') # 打开来源工作表workshee

原创 Python 柱形图

import matplotlib.pyplot as pltx = [1,2,3,4,5,6,7,8,9,10,11,12]y = [2,4,3,5,1,6,8,4,6,5,4,9]plt.bar(x,y)plt.show()

原创 Python折线图

import matplotlib.pyplot as pltx = [1,2,3,4,5,6,7,8,9,10,11,12]y = [2,4,3,5,1,6,8,4,6,5,4,9]plt.plot(x,y)plt.show()