EXCEL合并表

#!/usr/bin/python
# -*- coding: utf8 -*-
#FileName: TransformTool.py
#1.首先从EXCEL表中读入数据分别放入两个DICT,DICT以文本编号为索引,
#2.将新表的内容覆盖掉老表的,
#3.将表的处理结果写入新的表
#xlrd,xlwt EXCEL起始列从0开始,
#win32com起始列从1开始,坑死我啦



import os;
import xlsToServerTxt;
import excel2sqlite3;
import xlrd;
import xlwt;
import win32com.client;
from win32com.client import constants, Dispatch
from xml.dom import minidom, Node


mePath = r"config.xml"


#根据配置文件进行操作
def DoConfig():
    print "DoConfig()";
    srcPath = "";
    addPath = "";
    distPath = "";
    dom = minidom.parse( mePath );
    root = dom.documentElement;
    for node in root.childNodes:
        if node.nodeType == node.ELEMENT_NODE:
            #print node.nodeName
            if node.nodeName == "paths":
                print "paths:-----------------------------------";
                print "srcPath:" + node.getAttribute( "srcPath" );
                srcPath = node.getAttribute( "srcPath" );

                print "addPath:" + node.getAttribute( "addPath" );
                addPath = node.getAttribute( "addPath" );

                print "distPath:" + node.getAttribute( "distPath" );
                distPath = node.getAttribute( "distPath" );

            if node.nodeName == "file":
                print "file:-----------------------------------";
                print "srcFile:" + node.getAttribute( "srcFile" );
                print "sheet:" + node.getAttribute( "sheet" );
                RevertExcel( srcPath + node.getAttribute( "srcFile" ), addPath + node.getAttribute( "srcFile" ), distPath + node.getAttribute( "srcFile" ), node.getAttribute( "sheet" ) );


def RevertExcel( srcFile, addFile, dstFile, sheetName ):
    print "RevertExcel():"
    print "srcFile:" + srcFile;
    print "addFile:" + addFile;
    print "dstFile:" + dstFile;
    print "sheetName:" + sheetName;
    srcDict = {};
    addDict = {};
    GetDict( srcDict , srcFile, sheetName );
    GetDict( addDict , addFile, sheetName );
    AddDict( srcDict, addDict );
    WriteExcelByDict( srcDict, srcFile, addFile, dstFile, sheetName );


#字典写入结果EXCEL
def WriteExcelByDict( srcDict, srcFile, addFile, dstFile, sheetName ):
    print "WriteExcelByDict()";
    #先从srcFile哪里COPY表头################################################################
    print "WriteExcelByDict:" + srcFile;
    exitDistFile = 0;
    try:
        srcXlsFile = xlrd.open_workbook( srcFile );
    except:
        print( "can't find xls :" + srcFile );

    try:
        mySheet = srcXlsFile.sheet_by_name( sheetName );
    except:
        print( "can't find sheet :" + sheetName );

    print( "%d rows, %d cols" % ( mySheet.nrows, mySheet.ncols ) );

    if os.path.isfile( dstFile ):
        xlsApp = win32com.client.Dispatch( "Excel.Application" );
        writeFile = xlsApp.Workbooks.Open( dstFile );
        table = writeFile.Worksheets.Add();
        table.Name = sheetName;
        exitDistFile = 1;
    else:
        writeFile = xlwt.Workbook();
        exitDistFile = 0;
        table = writeFile.add_sheet( sheetName );
    cur_row = 0;
    cur_col = 0;
    for row in range( 0, mySheet.nrows ):
        temp = unicode( mySheet.cell( row, 2 ).value );
        coArr = [];
        #如果如果第3列(索引列)是数字那就不COPY了,是数据行了,从第一行第一列开始读

        for col in range( 0, mySheet.ncols ):
            if exitDistFile == 0:
                table.write( row, col, mySheet.cell( row, col ).value );
            else:
                try:
                    table.Cells( row + 1, col + 1 ).Value = mySheet.cell( row, col ).value;
                except:
                    print "except -0 " + str( row ) + ", " + str( col ) + "," + str( mySheet.cell( row, col ).value );

        if temp == "int":
            cur_row = row + 1;
            cur_col = col;
            break;

    for key in srcDict.keys():
        content = srcDict[key];
        content_index = 1;
        if exitDistFile == 0:
            table.write( cur_row, 0, "*" );
            table.write( cur_row, 1, "*" );
            table.write( cur_row, 2, key );
        else:
            try:
                table.Cells( cur_row + 1, 0 + 1 ).Value = mySheet.cell( cur_row, 0 ).value;
            except:
                print "except 0 " + str( cur_row ) + ", " + str( 0 ) + "," + str( mySheet.cell( cur_row, 0 ).value );
            try:
                table.Cells( cur_row + 1, 1 + 1 ).Value = mySheet.cell( cur_row, 1 ).value;
            except:
                print "except 1 " + str( cur_row ) + ", " + str( 1 ) + "," + str( mySheet.cell( cur_row, 1 ).value );
            try:
                table.Cells( cur_row + 1, 2 + 1 ).Value = mySheet.cell( cur_row, 2 ).value;
            except:
                print "except 2 " + str( cur_row ) + ", " + str( 2 ) + "," + str( mySheet.cell( cur_row, 2 ).value );


        for col in range( 3, mySheet.ncols ):
            if unicode( mySheet.cell( 1, col ).value ) != "*":
                continue;
            if exitDistFile == 0:
                table.write( cur_row, col, content[content_index] );
            else:
                try:
                    table.Cells( cur_row + 1, col + 1 ).Value = mySheet.cell( cur_row, col ).value;
                except:
                    print "except 3 " + str( cur_row ) + ", " + str( col ) + "," + str( mySheet.cell( cur_row, col ).value );
            content_index = content_index + 1;
        cur_row = cur_row + 1;

    if exitDistFile == 0:
        writeFile.save( dstFile );
    else:
        writeFile.Save();
        writeFile.Close();
        xlsApp.Quit();


#合并源字典和增量字典
def AddDict( srcDict, addDict ):
    print "AddDict( srcDict, addDict )"

    for key in addDict.keys():
        #print str( key ) + ":"
        for content in addDict[key]:
            srcDict[key] = addDict[key];
            #print content;



#获得EXCEL对应的字典,INDEX索引[content1,content2,conten3]
def GetDict( srcDict , srcFile, sheetName ):


    try:
        srcXlsFile = xlrd.open_workbook( srcFile );
    except:
        print( "can't find xls :" + srcFile );

    try:
        mySheet = srcXlsFile.sheet_by_name( sheetName );
    except:
        print( "can't find sheet :" + sheetName );

    print( "%d rows, %d cols" % ( mySheet.nrows, mySheet.ncols ) );


    for row in range( 0, mySheet.nrows ):
        temp = unicode( mySheet.cell( row, 1 ).value );
        coArr = [];
        if temp != "*":
            continue;
        temp = unicode( mySheet.cell( row, 2 ).value );
        if temp == '':
            continue;
        temp = "";
        for col in range( 0, mySheet.ncols ):
            if unicode( mySheet.cell( 1, col ).value ) != "*":
                continue;
            if mySheet.cell( row, col ).value != None:
                Uni = str( mySheet.cell( row, col ).value ).decode( "UTF-8", 'ignore' );
                temp += Uni + "\t";
                coArr.append( Uni );


#        print( temp.decode( "UTF-8", 'ignore' ) );

        try:
            index = round( int( mySheet.cell( row, 2 ).value ) )

        except:
            print row;

        srcDict[index] = coArr;




if __name__ == "__main__":
    print "readsheet";
    DoConfig();
    print "well done all table !!!!!!!!";