python实现文本与Excel内容相互转换

思考：

如何用两层循环将以下列表转换为[‘1’, 1, 2, 3, ‘2’, 3, 4, 5]？

[('1',[1,2,3]),('2',[3,4,5])]
#解答在最后

正题

纯文本文件 student.txt为学生信息, 里面的内容如下所示：

{
    "1":["张三",150,120,100],
    "2":["李四",90,99,95],
    "3":["王五",60,66,68]
}

请将以上内容转换为表格形式如下：

import  json
import xlwt,xlrd
from collections import  OrderedDict

def txt_to_xls():
    with open('student.txt') as f:
        students_dict = json.load(f)
    order_dict = sorted(students_dict.items(),key = lambda item:item[0])#对读取的对象进行按键排序

    wb = xlwt.Workbook()  #创建一个工作簿
    ws = wb.add_sheet('student') #创建一个sheet

    row = 0
    for k,v in order_dict:
        ws.write(row,0,k)
        col = 1
        for item in v:
            ws.write(row,col,item)
            col += 1
        row +=1
    wb.save('student.xls')  #保存 
if __name__=='__main__':
    txt_to_xls()

将上边cls表格中的数据导出为json格式：

def xls_to_txt():
    result = {}
    workbook = xlrd.open_workbook('student.xls')
    # 获取所有sheet
    #print workbook.sheet_names()
    sheet1_name = workbook.sheet_names()[0]

    # 根据sheet索引或者名称获取sheet内容
    sheet1 = workbook.sheet_by_index(0) # sheet索引从0开始

    # sheet的名称，行数，列数
    print sheet1.name
    for i in range(sheet1.nrows):
        result[sheet1.row_values(i)[0]] = sheet1.row_values(i)[1:]

    for x in result:
        for y in result[x]:
            print y

    with open('student.json','w') as fo:
        json.dump(result,fo)

思考解答：

lst = []
for k, v in [('1',[1,2,3]),('2',[3,4,5])]:
        lst.append(k)
        for item in v:
            lst.append(item)