nongcunqq的博客

【代码】openpyxl 删除对应数据 保持格式写入。

sht[‘2:2’].delete()# 删除行sht[1:2, :].delete() # 区域切片的方法sht[‘B:B’].delete()# 删除列sht[‘B2’].delete()# 删除单元格 (缺省默认右侧左移’left’，下侧上移’up’)sht[‘C2:D4’].delete(‘up’) # 删除区域sht[‘2:2’].insert()# 插入行sht[‘B:B’].insert()# 插入列。

【代码】xlwings 关闭所有开启的 excel。

【代码】xlwings 链接到 指定sheet。

【代码】python 设置 win32 excel 设置 非连续单元格 背景色。

修改为文件头有点过于繁琐，利用 win32 com 调用 原生 excel 太笨重，又试了一个 python 生成 excel 的模块 xlsxwriter，完美解决。openpyxl 生成的文件头为 docProps/app.xml。excel 的文件头为 [Content_Types].xml。经过分析，可能是文件头的问题，因为看起来没有区别。一个任务需要上传 excel 到 sap。使用 openpyxl 生成的不识别。excel 保存后可以识别。

【代码】csv 直接读取 第四行 字典 dict update 获取索引 根据索引获取 csv 指定栏位信息。

参考。

vba 录屏 转成 python 代码。2023年4月10日 星期一 更新。

一般 51 xlsx、56 xls 用的多。

【代码】转换excel xlsx 为xls 或者转换 xls 为 xlsx aspose cells aspose-cells 22.8.0。

读取excel 单元格 value 的两种方式。非常好的参考网站 openpyxl 图文并茂。

1、打开excel表格，在第一行输入一些文字，并需要将第一行进行冻结操作。

workbook = xlsxwriter.Workbook(download_directory + f'LUK_CMH_{today}.xlsx') worksheet = workbook.add_worksheet('data-export') for index, value in enumerate(col): worksheet.write(0, index, value.get('T...

import time,ctypesimport pandas as pdimport pyexcelerate,xlsxwriterdef test_pyexcelerate(fn_prefix,data): wb = pyexcelerate.Workbook() wb.new_sheet("sheet name", data=data) wb.save(fn_prefix+"_pyexcelerate.xlsx")def test_libxl(fn_prefix,

from PIL import ImageGrabimport xlwings as xw# get_screenshotdef excel_catch_screen2(shot_excel, sheetname, bu=None): day = datetime.now().strftime('%Y%m%d') print(day) img_dir = r"E:\code\dock_project\excel_image\ppt_images" image_n.

#!/usr/bin/env python# -*- coding:utf-8 -*-# Datetime:2018/9/16 11:45# Author:Xzs from win32com.client import Dispatch, DispatchEximport pythoncomfrom PIL import ImageGrab, Imageimport uuid # screen_area——类似格式"A1:J10"def excel_catch_screen(fil

=LEN(C1)<>LEN(SUBSTITUTE(C1," ",""))参考https://www.renrendoc.com/paper/88991132.html

try: xl = client.gencache.EnsureDispatch('Excel.Application')except AttributeError: # Corner case dependencies. import os import re import sys import shutil # Remove cache and try again. MODULE_LIST = [m.__name__ for m in s

font = xlwt.Font() # 创建一个workbook 设置编码 workbook = xlwt.Workbook(encoding='utf-8') al = xlwt.Alignment() al.horz = 0x02 # 设置水平居中 al.vert = 0x01 # 设置垂直居中 #设置字体，分两种，加粗不加粗 font = xlwt.Font() # 字体类型 font.name = 'Arial U

#!/usr/bin/env python# -*- coding: utf-8 -*-# Copyright (C) 2005 Kiseliov Romanfrom xlwt import *w = Workbook()ws = w.add_sheet('Hey, Dude')fmts = [ 'general', '0', '0.00', '#,##0', '#,##0.00', '"$"#,##0_);("$"#,##', '"

import xlwtb = xlwt.Workbook()s = b.add_sheet('x')style = xlwt.easyxf("", "#,###")s.write(0, 0, -1234567, style)b.save("locale_fmt_demo.xls")效果参考http://cn.voidcc.com/question/p-saymmzbz-rk.html

Sub Maco1()Application.DisplayAlerts = FalseDim a, b As Integer Sheets("6T").Select Range("A1:M1").Select Selection.Copy Sheets("Sheet1").Select Range("A1").Select ActiveSheet.Pasteb = 2For i = 1 To (Sheets.Count - 1) If Sheets(i).Name = "其他"

def t_barcode(): file = r'e:\code\dock_project\DN--SHIPSHIP.xls' pythoncom.CoInitialize() xl = win32com.client.Dispatch("Excel.Application") xl.DisplayAlerts = False xl.Visible = True wb = xl.Workbooks.Open(file) sheets = wb.Wo

pythoncom.CoInitialize() xl = win32com.client.Dispatch("Excel.Application") xl.DisplayAlerts = False xl.Visible = True wb = xl.Workbooks.Open(file)如果提示安全问题，用电脑上的excel打开文件重新保存一遍，再操作参考https://blog.csdn.net/qq_15557299/article/details/112645073...

设置日期显示格式 style_white_num = xlwt.easyxf('pattern: pattern solid, fore_colour white_colour') style_white_num.borders = set_boarders() style_white_num.num_format_str = 'YYYY/MM/DD' style_white_num.alignment = al style_white_num2 = xlwt.easyx

xlrd 1.2.0 版本通吃xls xlsx速度奇快win32 excel application奇慢无比，openpyxl也不快遇到for循环遍历cell数据更慢读取5000行数据，顺带获取20个栏位的信息，xlrd一秒搞定需要对固定格式的excel进行写入时，用win32 excel application，可以保持原有格式。......

import pyrfc, jsonimport tablibfrom datetime import datetimeimport win32com.client, os, shutil, timexl = win32com.client.Dispatch("Excel.Application")file = r'D:\code\maria_po_transfer\t2\dn_template.xls'xl.DisplayAlerts = Falsexl.Visible = Truew

为什么用win32，既能打开excel又能操作xlrd只能读取xlwt保存的话需要重新设置格式，win32 excel.application不受影响from win32com.client import Dispatchxl = Dispatch('Excel.Application')wb = xl.Workbooks.Open(filename)time.sleep(60)wb.Close(True)wb.Close(True)代表保存后关闭参考https://stackov

实际日期是这样xlrd读取结果为4389.8037007755转换代码，mode选择0import xlrd, osfrom xlrd import xldate_as_datetimeprint('actual shipdate--->', actual_ship_date) print(xldate_as_datetime(actual_ship_date,0).strftime('%Y%m%d')) time.sleep(60)转换后效果参考

def convert_to_number(letter, columnA=0): """ 字母列号转数字 columnA: 你希望A列是第几列(0 or 1)? 默认0 return: int """ ab = '_ABCDEFGHIJKLMNOPQRSTUVWXYZ' letter0 = letter.upper() w = 0 for _ in letter0: w *= 26 w += ab.fi

import xlwtworkbook = xlwt.Workbook()worksheet = workbook.add_sheet('Sheet')worksheet.write(4, 0, "Abracadabra!")worksheet.row(4).hidden = 1workbook.save('filename.xlsx')xlwt隐藏第一列worksheet.col(0).hidden = 1参考https://stackoverflow.com/question

import win32com.client, timexlApp = win32com.client.DispatchEx("Excel.Application")xlApp.Visible = TruexlApp.DisplayAlerts = 0# 宏所在的xlsxlApp.Workbooks.Open(r"D:\code\maria_po_transfer\1.xls", False)worksheet = xlApp.ActiveSheet#获取总行数i = workshee

Sub get() '获取总列数 MsgBox ActiveSheet.Range("IV1").End(xlToLeft).Column '获取单元格列号 MsgBox Range("AE1").Column '求和 Cells(2189, 33).Formula = Application.Sum(Range(Cells(901, 33), Cells(902, 33))) '另一种求和方式 Range("A1").Formula ="

Sub test() Dim path As String '先获取该工作簿的路径 this_workbook_path = ThisWorkbook.path '再获取目标路径的路径 target_workbook_path = this_workbook_path & "\全年核销.xlsx" Workbooks.Open (target_workbook_path)End Sub参考https://www.jianshu.com/p/e8

Sub excelToJsonFileExample()Dim excelRange As RangeDim jsonItems As New CollectionDim jsonDictionary As New DictionaryDim jsonFileObject As New FileSystemObjectDim jsonFileExport As TextStreamDim i As LongDim cell As VariantSet excelRange = Cells(

PRAGMA table_info(so);

Before saving the file set DisplayAlerts to False to suppress the warning dialog:xl.DisplayAlerts = FalseAfter the file is saved it is usually a good idea to set DisplayAlerts back to True: xl.DisplayAlerts = True参考https://stackoverflow.com/question

参考https://jingyan.baidu.com/article/4d58d5416798f19dd5e9c066.htmlhttps://support.microsoft.com/zh-cn/office/%E4%BD%BF%E7%94%A8%E9%AB%98%E7%BA%A7%E6%9D%A1%E4%BB%B6%E7%AD%9B%E9%80%89-4c9222fe-8529-4cd7-a898-3f16abdff32b#bkmk_3