合并导入财政

import xlrd
import xlwt
from xlutils.copy import copy
import sys
import inspect
import os


CopyStartCols = []
CopySkipCols = []
EqualCols = []

def PRINT_WITH_DETAIL(s):
    frame = None
    try:
        raise ZeroDivisionError
    except ZeroDivisionError:
        frame = sys. exc_info ( ) [ 2 ]. tb_frame. f_back
    print("Line " + str(frame.f_lineno),end=": ")
    print(s)


def FindItemFromSheet(item, sheet):
    #PRINT_WITH_DETAIL("Search value: " + item)
    for ReadCol in range(sheet.ncols - 1):
        for ReadRow in range(sheet.nrows - 1):
            if item == sheet.cell(ReadRow, ReadCol).value:
                #PRINT_WITH_DETAIL("found: " + str(ReadRow) + ":" + str(ReadCol))
                return 0


def PrintSheet(sheet):
    for ReadCol in range(sheet.ncols - 1):
        for ReadRow in range(sheet.nrows - 1):
            PRINT_WITH_DETAIL(sheet.cell(ReadRow, ReadCol).value)


def GetTitleIndex(title):
    for row0 in range(ReadSheet.ncols):
        if title == ReadSheet.cell(0, row0).value:
            #PRINT_WITH_DETAIL(row0)
            return row0


def CopyFromStart(CopyStartCols, CopySkipCols, EqualCols):
    PRINT_WITH_DETAIL("CopyFromStart start")

    #找到以项目所在的列
    for index in range(len(CopyStartCols)):
        PRINT_WITH_DETAIL(CopyStartCols[index])

        #从待写入的表格取出列
        for rowWrite in range(WriteSheet.nrows):
            targetItemName = WriteSheet.cell(rowWrite, CopyStartCols[index]).value

            #除去字符串前后的空格
            targetString = targetItemName.strip()
            #PRINT_WITH_DETAIL(targetString)
            #print("target: " + targetString)
            if targetString == "":
                continue

            #从待读入的表格读取表格值
            for rowRead in range(ReadSheet.nrows):
                currentString = ReadSheet.cell(rowRead, CopyStartCols[index]).value

                # 除去字符串前后的空格
                currentString = currentString.strip()
                if currentString == "":
                    continue

                if targetString == currentString:
                    #print(str(colRead) + ReadSheet.cell(colRead, 0).value)
                    #因为第一列用于比较，所以不需要处理第一列
                    for handleIndex in EqualCols:
                        nextIndex = 0
                        if index + 1 >= len(CopyStartCols):
                            nextIndex = WriteSheet.ncols
                        else:
                            nextIndex = CopyStartCols[index + 1]

                        if handleIndex < nextIndex and handleIndex > CopyStartCols[index]:
                            if handleIndex != CopySkipCols[0]:
                                fromValue = ReadSheet.cell(rowRead, handleIndex).value
                                #PRINT_WITH_DETAIL(str(handleIndex) + ": " + str(fromValue))

                                # write(row, col)
                                PRINT_WITH_DETAIL("row: " + str(rowWrite) + " col: "+ str(handleIndex) + " value: " + str(fromValue))
                                ws.write(rowWrite, handleIndex, fromValue)


ReadExcelNames = ["合并导出.xls", "TJ20_Z01-资产负债表"]
WriteExcelNames = ["合并导入财政.xls", "QYJS20Z01-资产负债表"]

ReadExcel = xlrd.open_workbook(ReadExcelNames[0], formatting_info=True)
ReadSheet = ReadExcel.sheet_by_name(ReadExcelNames[1])
#print(str(ReadSheet.nrows) + ": " + str(ReadSheet.ncols))

WriteExcel = xlrd.open_workbook(WriteExcelNames[0], formatting_info=True)
WriteSheet = WriteExcel.sheet_by_name(WriteExcelNames[1])
#print(str(WriteSheet.nrows) + ": " + str(WriteSheet.ncols))

for row0 in range(ReadSheet.ncols):
    #print(ReadSheet.cell(0, row0).value, end=" ")
    ReadValue = ReadSheet.cell(0, row0).value
    for row1 in range(row0, WriteSheet.ncols):
        WriteValue = WriteSheet.cell(0, row1).value
        if ReadValue == WriteValue and WriteValue == '项            目':
            #PRINT_WITH_DETAIL(str(row0) + " " + str(row1) + ": " + WriteValue)
            CopyStartCols.append(row1)
            #print(WriteSheet.cell(0, row1).value, end=" ")
            #print("")
            break

        elif ReadValue == WriteValue and WriteValue == '行次':
            CopySkipCols.append(row1)
            #PRINT_WITH_DETAIL(str(row0) + " " + str(row1) + ": " + WriteValue)
            #print(WriteSheet.cell(0, row1).value, end=" ")
            #print("")
            break
        elif ReadValue == WriteValue:
            EqualCols.append(row1)
            break

PRINT_WITH_DETAIL("Copy Start Column: " + str(CopyStartCols))
PRINT_WITH_DETAIL("Copy Skip Column: " + str(CopySkipCols))
PRINT_WITH_DETAIL("Equal Column: " + str(EqualCols))

print("")
FindItemFromSheet(ReadSheet.cell(2, 0).value, WriteSheet)

index = GetTitleIndex("项            目")

new_excel = copy(WriteExcel)

# 获得第一个sheet的对象
ws = new_excel.get_sheet(1)

CopyFromStart(CopyStartCols, CopySkipCols, EqualCols)

# 另存为excel文件，并将文件命名
new_excel.save('new_fileName.xls')