import xlwt
import json
import xlrd
from lxml import etree
class RedeExcel:
def init(self,filename,sheet_name=None):
print(“请输入文件名字和sheetname sheetname默认为第一个表单”)
print(“若不知道sheetname 可直接使用方法sheet_names获得”)
print(“可以使用get_nrows_ncols 获得所有的行列总数 这样可以得到所有的数据”)
filename=filename+".xlsx"
data = xlrd.open_workbook(filename)
self.sheet_names=data.sheet_names()
if sheet_name==None:
print(“你可以通过一下sheetname找的你要的数据”)
self.table = data.sheets()[0]
else :
self.table=data.sheet_by_name(sheet_name)

# 读取任意一个格子第几行第几列 获者是第几行 第几列到第几列
def get_row_col(self,row_id,row_id_end=None,col_id_start=None,col_id_end=None):

    if col_id_end==None and col_id_start!=None:
        table_data=[]
        for i in range(row_id-1,row_id_end):
            data = self.table.row_values(i, col_id_start - 1, col_id_end)
            table_data.append(data)
        return table_data
    elif col_id_end==None and col_id_start==None and row_id_end!=None:
        table_data=[]
        for i in range(row_id-1,row_id_end):
            data = self.table.row_values(i)
            table_data.append(data)
        return table_data
    elif col_id_end==None and col_id_start==None and row_id_end==None and row_id!=None:
        data = self.table.row_values(row_id-1)
        return data
    elif row_id_end!=None and col_id_start!=None and col_id_end!=None  :
        table_data = []

        for i in range(row_id - 1, row_id_end):
            data = self.table.row_values(i, col_id_start - 1, col_id_end-1)
            table_data.append(data)
        return table_data
    # 读取任意一个格子第几列第几行 获者是第几列 第几行到第几行

def get_col_row(self, col_id, col_id_end=None,row_id_start=None,row_id_end=None):

    if row_id_end == None and row_id_start != None:
        table_data = []
        for i in range(col_id - 1, col_id_end):
            data = self.table.col_values(i, row_id_start - 1, row_id_end)
            table_data.append(data)
        return table_data
    elif row_id_end == None and row_id_start == None and col_id_end != None:
        table_data = []
        for i in range(col_id - 1, col_id_end):
            data = self.table.col_values(i)
            table_data.append(data)
        return table_data
    elif row_id_end == None and row_id_start == None and col_id_end == None and col_id != None:
        data = self.table.col_values(col_id-1)
        return data
    elif col_id_end != None and row_id_start != None and row_id_end != None:
        table_data = []

        for i in range(col_id - 1, col_id_end):
            data = self.table.col_values(i, row_id_start - 1, row_id_end - 1)
            data = self.table.col_values(i, row_id_start - 1, row_id_end - 1)
            table_data.append(data)
        return table_data
# 得到行数和列数
def get_nrows_ncols(self):
    return self.table.nrows,self.table.ncols
    #  查找任意一个元素的行列数

def find_row_col_by_content(self, content):
    row, clo = self.get_nrows_ncols()
    self.row_col = []
    for i in range(row):
        con = s.get_row_col(i)
        for content_one in con:
            if content_one == content:
                self.col_index = con.index(content_one) + 1
                self.row_index = i
                self.row_col.append((self.row_index, self.col_index))
    return self.row_col

class WriteExcel():
def init(self,sheetname=None):
self.sheetname=sheetname
if sheetname==None:
self.wb = xlwt.Workbook()
self.ws = self.wb.add_sheet(“sheet1”)
else:
self.wb = xlwt.Workbook()
self.ws = self.wb.add_sheet(self.sheetname)

# 写通过行列索引
def wirte_by_row_col(self,row=0,col=0,filepath="exmple.xlsx",text=''):
    if col==0:
        self.ws.write(row, col, label=text)
        self.wb.save(filepath)
    else:

        self.ws.write(row,col-1,label=text)
        self.wb.save(filepath)

if name==“main”:s=RedeExcel(“apptest”)print(s.sheet_names)print(s.get_nrows_ncols())print(s.get_row_col(3,4))print(s.get_col_row(1,2,1,2))x=s.find_row_col_by_content(2)print(s.get_row_col(1,1,1,2))print(“ssssssssss”,x)w=WriteExcel(“Sheet1”)w.wirte_by_row_col(3,4,text=2)

class Excel_Xml:
def init(self):
print(“dddddd”)
# 读取excel写入xml
def excel_to_xml(self,ex_filepath,xml_filepath):
s = RedeExcel(ex_filepath)
zjs_dic = dict()
root= etree.Element(“excel”)

    for sheet_one in s.sheet_names:
        s = RedeExcel(ex_filepath, sheet_one)
        x, y = s.get_nrows_ncols()
        sheet_xml=etree.SubElement(root,"sheet")

        for i in range(1, x + 1):
            row_xml=etree.SubElement(root,"row")
            for col_v in s.get_row_col(i):
                col_xml=etree.SubElement(root,"col")
                col_xml.text=str(col_v)
    tree = etree.ElementTree(root)
    tree.write(xml_filepath, pretty_print=True, xml_declaration=True, encoding='utf-8')




    # child1 = etree.SubElement(root, "child1")
    # child1.set("interesting", "totally")
    # child1.text = "TEXT"
    #
    # child2 = etree.SubElement(root, "child2")
    # child2.set("name", "myattr1")
    # child2.set("auto", "myattr2")
    #
    # child3 = etree.SubElement(child2, "child3")
    # child3.text = "TEXT"
    # child4 = etree.SubElement(child2, "child4")
    # child4.text = "TEXT"
    # child5 = etree.SubElement(child2, "child5")
    #
    # child6 = etree.SubElement(child5, "child6")
    # child6.text = "TEXT"
    # child7 = etree.SubElement(child5, "child7")
    # child7.text = "TEXT"
    # root.append( etree.Element("child1") )
    # root.append( etree.Element("child1", interesting="totally"))
    # child2 = etree.SubElement(root, "child2")

    # child3 = etree.SubElement(root, "child3")
    # root.insert(0, etree.Element("child0"))

    # print etree.tostring(root, pretty_print=True)
    #
    # # write to file:
    # tree = etree.ElementTree(root)
    # tree.write('text.xml', pretty_print=True, xml_declaration=True, encoding='utf-8')



def xml_to_excel(self):
    pass

if name==“main”:
# E=Excel_json()
# # E.excel_to_json(“apptest”,“test.json”)
# E.json_to_excel(“test.json”,“xxx.xlsx”)
E=Excel_Xml()
E.excel_to_xml(“apptest”,“test.xml”)