如何在python / pandas中复制excel COUNTIFS

Question

如题所述

Answer 1

# -*- coding: utf-8 -*-
import xlwt
import pandas as pd
import numpy as np
from xlrd import open_workbook
from xlutils.copy import copy

data = 'all.xls'
muban = 'muban.xls'

for mon in range( 201501, 201513 ):

allData = pd.read_excel( data, sheetname = str( mon ), skiprows = [ 0, 1 ] )
allData = allData.iloc[ : -2, : 10 ] # 除去表头表尾
allData.sort_values( by = [ '单据号码', '物料编码' ], inplace = True )

for dj in allData[ '单据号码' ].drop_duplicates():
newFile = dj + '.xls'

thisData = allData[ allData[ '单据号码' ] == dj ]
date = thisData[ '发生日期' ].iloc[ 0 ]

rb = open_workbook(muban,formatting_info=True)
rs = rb.sheet_by_index(0)
wb = copy(rb)

s=wb.get_sheet(0)
s.set_name(dj)

font = xlwt.Font()
font.name = 'Arial Narrow'
font.color = 'red'
font.size = 10

style = xlwt.XFStyle()
style.font = font

s.write(2,4,date, style)
s.write(2,7,dj, style)

borders = xlwt.Borders()
borders.left = xlwt.Borders.DOUBLE

for row in range( len( thisData ) ):
for col in range( len( thisData.columns ) - 2 ):

content = thisData.iloc[ row, col ]
content = "" if type(content)==type(1.3) and np.isnan(content) else content

if col == 0:
style = xlwt.XFStyle()
style.borders = borders
s.write(5+row,1+col, content, style )
else:
s.write(5+row,1+col, content )

borders = xlwt.Borders()
borders.bottom = xlwt.Borders.DOUBLE
style = xlwt.XFStyle()
style.borders = borders

s.write(106,6,xlwt.Formula("sum(G6:G106)"), style )
s.write(106,7,xlwt.Formula("sum(H6:H106)"), style )

wb.save(str( mon ) + "_" + newFile )