python Excel转xml,json

 注意脚本名称不能用xml.py，包括后面的json也不能直接json.py

否则容易出错：ModuleNotFoundError: No module named 'xml.etree'; 'xml' is not a package

#xml
import os
import openpyxl

imgpath = r'D:\picture'
excelpath = r'D:\2.xlsx'
xmlpath = r'D:'

if __name__ == '__main__':
    imgs = os.listdir(imgpath)
    workBook = openpyxl.load_workbook(excelpath)
    # print(workBook.sheet_names())
    # sheet1 = workBook[workBook.sheetnames[0]]
    # print(sheet1.dimensions)
    sheet = workBook.active
    # cell = sheet['J']
    # print(cell)
    stringName=['采集编号','采集日期','采集批次','采集地点','采集线别','采集方式',]
    for img in imgs:
        # print(img)
        imgname01 = img.split('_')[0]
        for i in sheet.iter_rows(min_row=2):
            # print(len(i))
            # for j in i:
            #     print(j)
                if i[9].value == imgname01:
                    path_t = xmlpath + '\\' + imgname01 + '.txt'
                    w =  open(path_t, 'w', encoding='utf8')
                    w.write('<?xml version="1.0" encoding="UTF-8"?>')
                    w.write('\n')
                    w.write('<root>')
                    w.write('\n')
                    for j in range(len(i)):
                        # print(j.value)
                        if j == 0:
                            first = str(i[7].value)+'_'+str(i[1].value)+'_'+str(i[2].value)

                            w.write('<' + stringName[j] + '>')
                            w.write(first)
                            w.write('</' + stringName[j] + '>')
                            w.write('\n')
                        else:
                            w.write('<'+stringName[j]+'>')
                            w.write(str(i[j].value))
                            w.write('</' + stringName[j] + '>')
                            w.write('\n')

                    w.write('</root>')
                    w.close()
                    path_x = xmlpath + '\\' + imgname01 + '.xml'
                    if os.path.exists(path_x):
                        os.remove(path_x)
                    os.rename(path_t,path_x)

#json
import os
import openpyxl

imgpath = r'D:\picture'
excelpath = r'D:\2.xlsx'
jsonpath = r'D:'

if __name__ == '__main__':
    imgs = os.listdir(imgpath)
    workBook = openpyxl.load_workbook(excelpath)
    # print(workBook.sheet_names())
    # sheet1 = workBook[workBook.sheetnames[0]]
    # print(sheet1.dimensions)
    sheet = workBook.active
    # cell = sheet['J']
    # print(cell)
    stringName=['采集编号','采集日期','采集批次','采集地点']
    for img in imgs:
        # print(img)
        imgname01 = img.split('_')[0]
        for i in sheet.iter_rows(min_row=2):

                if i[9].value == imgname01:
                    path_t = jsonpath + '\\' + imgname01 + '.txt'
                    w =  open(path_t, 'w', encoding='utf8')
                    w.write('{')
                    length = len(i)
                    for j in range(length):
                        if j == 0:
                            first = str(i[7].value)+'_'+str(i[1].value)+'_'+str(i[2].value)
                            w.write('"'+stringName[j]+'"'+':'+'"'+first+'"')
                            w.write(',')
                        elif j == length-1:
                            w.write('"'+stringName[j]+'"'+':'+'"'+str(i[j].value)+'"')
                        else:
                            w.write('"' + stringName[j] + '"' + ':' + '"' + str(i[j].value) + '"')
                            w.write(',')

                    w.write('}')

                    w.close()
                    path_x = jsonpath + '\\' + imgname01 + '.json'
                    if os.path.exists(path_x):
                        os.remove(path_x)
                    os.rename(path_t,path_x)