python excel復(fù)制數(shù)據(jù)保留單元格格式(.xls.xlsx)

這篇具有很好參考價(jià)值的文章主要介紹了python excel復(fù)制數(shù)據(jù)保留單元格格式(.xls.xlsx)。希望對(duì)大家有所幫助。如果存在錯(cuò)誤或未考慮完全的地方，請(qǐng)大家不吝賜教，您也可以點(diǎn)擊"舉報(bào)違法"按鈕提交疑問(wèn)。

最近幫朋友開(kāi)發(fā)一個(gè)數(shù)據(jù)excel根據(jù)條件動(dòng)態(tài)過(guò)率的功能.讀取生成用pandas很方便,但是這里有一點(diǎn)比較麻煩的是得保留原來(lái)的單元格格式.這點(diǎn)操作起來(lái)就要麻煩一點(diǎn)了.下面總結(jié)了.xlsx和.xls處理

python打開(kāi)excel文件復(fù)制數(shù)據(jù),python,python,excel,開(kāi)發(fā)語(yǔ)言

1.xlsx 文件處理

xlsx文件處理可以使用openpyxl庫(kù)進(jìn)行處理,比較簡(jiǎn)單,流程如下

1.獲取原來(lái)的數(shù)據(jù)cell

2.進(jìn)行value和style復(fù)制

import openpyxl
import copy
# 復(fù)制excel 保留格式
# file_path : 原文件地址
# out_file_path : 輸出文件地址
# ids : 條件
def copy_xlsx(file_path,out_file_path,ids):
    # 打開(kāi)原表
    workbook = openpyxl.load_workbook(file_path, data_only=True)
    # 獲取第一個(gè)sheet表
    sheet = workbook.sheet_by_index(0)

    # 創(chuàng)建一個(gè)新的 Excel 文件
    new_workbook = openpyxl.Workbook()
    new_sheet = new_workbook.active  # 選擇新工作表
    # 復(fù)制數(shù)據(jù)和樣式
    i = 1  # openpyxl 行號(hào)從1開(kāi)始
    for row in sheet.iter_rows():
        # todo 這里條件按需添加,可以去掉
        if i ==1 or row[0].value in ids:
            # 復(fù)制行
            for source_cell in row:
                target_cell = new_sheet.cell(row=i, column=source_cell.column,value = source_cell.value)
                # 復(fù)制樣式
                if source_cell.has_style:
                    # 設(shè)置樣式 得用 copy.copy() 不然會(huì)報(bào)錯(cuò)
                    target_cell._style = copy.copy(source_cell._style)
                    target_cell.font = copy.copy(source_cell.font)
                    target_cell.border = copy.copy(source_cell.border)
                    target_cell.fill = copy.copy(source_cell.fill)
                    target_cell.number_format = copy.copy(source_cell.number_format)
                    target_cell.protection = copy.copy(source_cell.protection)
                    target_cell.alignment = copy.copy(source_cell.alignment)
            i += 1

    # 保存新的 Excel 文件
    new_workbook.save(out_file_path)

2. xls 文件處理

xls文件處理起來(lái)麻煩點(diǎn),

首先得引入xlrd 只能進(jìn)行讀取,xlwt負(fù)責(zé)寫(xiě)入,xlutils相當(dāng)中間人,可以簡(jiǎn)化很多操作

import xlrd
import xlwt
from xlutils.filter import process, XLRDReader, XLWTWriter

1.xlrd 獲取workbook_rb, formatting_info=True 這個(gè)一定要加才能獲取到格式,formatting_info模式是false是不獲取格式.

2.通過(guò) xlutils 獲取到所有單元格格式

3.通過(guò) xlwt 的 worksheet.write(i, col_num, cell.value,style) 寫(xiě)入value 及 style

import xlrd
import xlwt
from xlutils.filter import process, XLRDReader, XLWTWriter

# 創(chuàng)建 xls 
def create_xls(savePath:str,ids):
    
    # 創(chuàng)建一個(gè)新的 Excel 文件（寫(xiě)入模式）
    workbook_rb = xlrd.open_workbook(path.get(),formatting_info=True)  # 打開(kāi)工作簿
    # 這里是關(guān)鍵,獲取所有樣式列表
    w = XLWTWriter()
    process(XLRDReader(workbook_rb, 'unknown.xls'), w)
    style_list = w.style_list
    
    sheet = sheet = workbook.sheet_by_index(0)

    new_workbook = xlwt.Workbook(encoding='utf-8')
    new_worksheet = new_workbook.add_sheet('sheet1')  # 添加一個(gè)新工作表，替換為你的工作表名稱
	
    # xlrd 的 index 從0開(kāi)始
    i=0
    for row_num, row in enumerate(sheet.get_rows(), start=0):
        if i ==0 or row[0].value in ids:
            for col_num, cell in enumerate(row, start=0):
                # 復(fù)制格式
                style = style_list[cell.xf_index]
                #獲取當(dāng)前單元格的style
                new_worksheet.write(i, col_num, cell.value,style)
            i+=1  
                
    new_workbook.save(savePath)
    return savePath

經(jīng)過(guò)測(cè)試,上述代碼是可以的,但是有個(gè)小問(wèn)題

w = XLWTWriter()
process(XLRDReader(workbook_rb, 'unknown.xls'), w)
style_list = w.style_list

這段代碼我就想獲取到style_list 但的東西有點(diǎn)多,這里其實(shí)是復(fù)制了一個(gè)新的workbook對(duì)象.新對(duì)象里面有原始workbook的所有信息.

python打開(kāi)excel文件復(fù)制數(shù)據(jù),python,python,excel,開(kāi)發(fā)語(yǔ)言
可以看到它包含的原始 xlrd.book信息 ,xlwt.worksheet 信息(他已經(jīng)將xlrd.book原始信息進(jìn)行復(fù)制),style_list信息.

這個(gè)如果只是對(duì)于我們想獲取style_list,那么這里信息有點(diǎn)太多.

因此我們能不能只獲取style_list呢,我們通過(guò)XLWTWriter源碼查詢下style_list是如何獲取的.看下能不能一探究竟.

通過(guò)過(guò)w.style_list進(jìn)入源碼查看,我們發(fā)現(xiàn)在xlutils.filter.BaseWriter.workbook 進(jìn)行了定義.
python打開(kāi)excel文件復(fù)制數(shù)據(jù),python,python,excel,開(kāi)發(fā)語(yǔ)言
接著我們找下style_list是如何進(jìn)行賦值的,查詢下發(fā)現(xiàn)如下代碼self.style_list.append(wtxf),這個(gè)就是賦值代碼.

讓我們看下它是如何實(shí)現(xiàn)的,核心代碼就是下面這個(gè),大致流程就是

1.創(chuàng)建一個(gè) xlwt.Style.XFStyle() 對(duì)象 wtxf

2.從rdbook中獲取到格式信息 rdbook.xf_list

3.對(duì) wtxf 進(jìn)行各種賦值

		if not rdbook.formatting_info:
            return
        for rdxf in rdbook.xf_list:
            wtxf = xlwt.Style.XFStyle()
            #
            # number format
            #
            wtxf.num_format_str = rdbook.format_map[rdxf.format_key].format_str
            #
            # font
            #
            wtf = wtxf.font
            rdf = rdbook.font_list[rdxf.font_index]
            wtf.height = rdf.height
            wtf.italic = rdf.italic
            wtf.struck_out = rdf.struck_out
            wtf.outline = rdf.outline
            wtf.shadow = rdf.outline
            wtf.colour_index = rdf.colour_index
            wtf.bold = rdf.bold #### This attribute is redundant, should be driven by weight
            wtf._weight = rdf.weight #### Why "private"?
            wtf.escapement = rdf.escapement
            wtf.underline = rdf.underline_type #### 
            # wtf.???? = rdf.underline #### redundant attribute, set on the fly when writing
            wtf.family = rdf.family
            wtf.charset = rdf.character_set
            wtf.name = rdf.name
            # 
            # protection
            #
            wtp = wtxf.protection
            rdp = rdxf.protection
            wtp.cell_locked = rdp.cell_locked
            wtp.formula_hidden = rdp.formula_hidden
            #
            # border(s) (rename ????)
            #
            wtb = wtxf.borders
            rdb = rdxf.border
            wtb.left   = rdb.left_line_style
            wtb.right  = rdb.right_line_style
            wtb.top    = rdb.top_line_style
            wtb.bottom = rdb.bottom_line_style 
            wtb.diag   = rdb.diag_line_style
            wtb.left_colour   = rdb.left_colour_index 
            wtb.right_colour  = rdb.right_colour_index 
            wtb.top_colour    = rdb.top_colour_index
            wtb.bottom_colour = rdb.bottom_colour_index 
            wtb.diag_colour   = rdb.diag_colour_index 
            wtb.need_diag1 = rdb.diag_down
            wtb.need_diag2 = rdb.diag_up
            #
            # background / pattern (rename???)
            #
            wtpat = wtxf.pattern
            rdbg = rdxf.background
            wtpat.pattern = rdbg.fill_pattern
            wtpat.pattern_fore_colour = rdbg.pattern_colour_index
            wtpat.pattern_back_colour = rdbg.background_colour_index
            #
            # alignment
            #
            wta = wtxf.alignment
            rda = rdxf.alignment
            wta.horz = rda.hor_align
            wta.vert = rda.vert_align
            wta.dire = rda.text_direction
            # wta.orie # orientation doesn't occur in BIFF8! Superceded by rotation ("rota").
            wta.rota = rda.rotation
            wta.wrap = rda.text_wrapped
            wta.shri = rda.shrink_to_fit
            wta.inde = rda.indent_level
            # wta.merg = ????
            #
            self.style_list.append(wtxf)

拿到這個(gè)代碼后就簡(jiǎn)單了,我們就是照著封裝一下,就是使用了.這樣我們只要能夠獲取到rdbook (也就是xlrd獲取到的book),就是獲取到style_list了.無(wú)需獲取到其他我們不關(guān)注的信息.

這樣代碼就是可以簡(jiǎn)單搞成這樣,效率會(huì)提高不少,只需引入xlrd,xlwt

import xlrd
import xlwt
# 獲取到表的所有單元格格式
def get_style_list(rdbook:xlrd.Book):
    style_list=[]
    if not rdbook.formatting_info:
        return
    for rdxf in rdbook.xf_list:
        wtxf = xlwt.Style.XFStyle()
        #
        # number format
        #
        wtxf.num_format_str = rdbook.format_map[rdxf.format_key].format_str
        #
        # font
        #
        wtf = wtxf.font
        rdf = rdbook.font_list[rdxf.font_index]
        wtf.height = rdf.height
        wtf.italic = rdf.italic
        wtf.struck_out = rdf.struck_out
        wtf.outline = rdf.outline
        wtf.shadow = rdf.outline
        wtf.colour_index = rdf.colour_index
        wtf.bold = rdf.bold  # This attribute is redundant, should be driven by weight
        wtf._weight = rdf.weight  # Why "private"?
        wtf.escapement = rdf.escapement
        wtf.underline = rdf.underline_type
        # wtf.???? = rdf.underline #### redundant attribute, set on the fly when writing
        wtf.family = rdf.family
        wtf.charset = rdf.character_set
        wtf.name = rdf.name
        #
        # protection
        #
        wtp = wtxf.protection
        rdp = rdxf.protection
        wtp.cell_locked = rdp.cell_locked
        wtp.formula_hidden = rdp.formula_hidden
        #
        # border(s) (rename ????)
        #
        wtb = wtxf.borders
        rdb = rdxf.border
        wtb.left = rdb.left_line_style
        wtb.right = rdb.right_line_style
        wtb.top = rdb.top_line_style
        wtb.bottom = rdb.bottom_line_style
        wtb.diag = rdb.diag_line_style
        wtb.left_colour = rdb.left_colour_index
        wtb.right_colour = rdb.right_colour_index
        wtb.top_colour = rdb.top_colour_index
        wtb.bottom_colour = rdb.bottom_colour_index
        wtb.diag_colour = rdb.diag_colour_index
        wtb.need_diag1 = rdb.diag_down
        wtb.need_diag2 = rdb.diag_up
        #
        # background / pattern (rename???)
        #
        wtpat = wtxf.pattern
        rdbg = rdxf.background
        wtpat.pattern = rdbg.fill_pattern
        wtpat.pattern_fore_colour = rdbg.pattern_colour_index
        wtpat.pattern_back_colour = rdbg.background_colour_index
        #
        # alignment
        #
        wta = wtxf.alignment
        rda = rdxf.alignment
        wta.horz = rda.hor_align
        wta.vert = rda.vert_align
        wta.dire = rda.text_direction
        # wta.orie # orientation doesn't occur in BIFF8! Superceded by rotation ("rota").
        wta.rota = rda.rotation
        wta.wrap = rda.text_wrapped
        wta.shri = rda.shrink_to_fit
        wta.inde = rda.indent_level
        # wta.merg = ????
        #
        style_list.append(wtxf)
        return style_list


# 創(chuàng)建 xls
def copy_xls(file_path:str, savePath: str, ids):

    # 創(chuàng)建一個(gè)新的 Excel 文件（寫(xiě)入模式）
    workbook_rb = xlrd.open_workbook(file_path, formatting_info=True)  # 打開(kāi)工作簿
    # 這里是關(guān)鍵,獲取所有樣式列表
    style_list = get_style_list(workbook_rb)
	# 獲取第一個(gè)sheet
    sheet = sheet = workbook_rb.sheet_by_index(0)

    new_workbook = xlwt.Workbook(encoding='utf-8')
    new_worksheet = new_workbook.add_sheet('sheet1')  # 添加一個(gè)新工作表，替換為你的工作表名稱

    # xlrd 的 index 從0開(kāi)始
    i = 0
    for row in sheet.get_rows():
        # todo 這里條件按需添加,可以去掉
        if i == 0 or row[0].value in ids:
            for col_num, cell in enumerate(row, start=0):
                # 復(fù)制格式
                style = style_list[cell.xf_index]
                # 獲取當(dāng)前單元格的style
                new_worksheet.write(i, col_num, cell.value, style)
            i += 1

    new_workbook.save(savePath)
    return savePath

參考:

https://www.cnblogs.com/KeenLeung/p/14101049.html

https://blog.csdn.net/weixin_39804265/article/details/105127786文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-772445.html

到了這里，關(guān)于python excel復(fù)制數(shù)據(jù)保留單元格格式(.xls.xlsx)的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！