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”)