python常用数据文件格式转换

自己整理的txt格式和xls、xlsx、csv格式的相互转换代码，平时处理数据常用。

# -*- coding:UTF-8 -*-


"""
author:
time:
"""

import xlwt
import xlrd
import openpyxl
import codecs
from openpyxl.utils import get_column_letter
import pandas as pd
import os


class TxtToExcel(object):

    def txt_xls(self, filename, xlsname):
        """
        :文本转换成xls的函数
        :param filename txt文本文件名称、
        :param xlsname 表示转换后的excel文件名
        """
        try:
            f = open(filename, errors='ignore')
            xls = xlwt.Workbook()
            # 生成excel的方法，声明excel
            sheet = xls.add_sheet('sheet1', cell_overwrite_ok=True)
            x = 0
            while True:
                # 按行循环，读取文本文件
                line = f.readline()
                if not line:
                    break  # 如果没有内容，则退出循环
                for i in range(len(line.split('\t'))):
                    item = line.split('\t')[i]
                    sheet.write(x, i, item)  # x单元格经度，i 单元格纬度
                x += 1  # excel另起一行
            f.close()
            xls.save(xlsname)  # 保存xls文件
        except:
            raise


class ExcelToTxt(object):

    def strs(self, row):
        """
        :返回一行数据
        """
        try:
            values = ""
            for i in range(len(row)):
                if i == len(row) - 1:
                    values = values + str(row[i])
                else:
                    # 使用“，”逗号作为分隔符
                    values = values + str(row[i]) + "\t"
            return values
        except:
            raise

    def xls_txt(self, xls_name, txt_name):
        """
        :excel文件转换为txt文件
        :param xls_name excel 文件名称
        :param txt_name txt   文件名称
        """
        try:
            data = xlrd.open_workbook(xls_name)
            sqlfile = open(txt_name, "a")
            table = data.sheets()[0]  # 表头
            nrows = table.nrows  # 行数
            # 如果不需跳过表头，则将下一行中1改为0
            for ronum in range(1, nrows):
                row = table.row_values(ronum)
                values = self.strs(row)  # 条用函数，将行数据拼接成字符串
                sqlfile.writelines(values + "\n")  # 将字符串写入新文件
            sqlfile.close()  # 关闭写入的文件
        except Exception as e:
            print(e)

    def xlsx_txt(self, xlsx_name, txt_name):
        try:
            # 载入文件
            wb = openpyxl.load_workbook(xlsx_name)
            # 获取Sheet1工作表
            xlsx_sheet_name = pd.ExcelFile(xlsx_name).sheet_names
            ws = wb[xlsx_sheet_name[0]]
            sqlfile = open(txt_name, "a")
            for rows in ws.iter_rows(min_row=2):
                row = []
                for cell in rows:
                    row.append(cell.value)
                values = self.strs(row)  # 条用函数，将行数据拼接成字符串
                sqlfile.writelines(values + "\n")  # 将字符串写入新文件
            sqlfile.close()  # 关闭写入的文件
        except Exception as e:
            print(e)


class TxtToExcel2(object):

    def txt_to_xlsx(self, filename, outfile):
        fr = codecs.open(filename, 'r')
        wb = openpyxl.Workbook()
        ws = wb.active
        ws = wb.create_sheet()
        ws.title = 'Sheet1'
        row = 0
        for line in fr:
            row += 1
            line = line.strip()
            line = line.split('\t')
            col = 0
            for j in range(len(line)):
                col += 1
                # print (line[j])
                ws.cell(column=col, row=row, value=line[j].format(get_column_letter(col)))
        wb.save(outfile)

    # 读取xlsx内容
    def read_xlsx(self, filename):
        # 载入文件
        wb = openpyxl.load_workbook(filename)
        # 获取Sheet1工作表
        ws = wb.get_sheet_by_name('Sheet1')
        # 按行读取
        for row in ws.rows:
            for cell in row:
                print(cell.value)
        # 按列读
        for col in ws.columns:
            for cell in col:
                print(cell.value)


class CsvToTxt(object):
    def csv_to_txt(self, csv_file, txt_name):
        try:
            filename = pd.read_csv(csv_file)
            sqlfile = open(txt_name, 'a')
            for rows in filename.values:
                for item in rows:
                    sqlfile.writelines(str(item) + ',')
                sqlfile.writelines('\n')
            sqlfile.close()
        except Exception as e:
            print(e)

    def csv_to_txt_adcode2xy(self, csv_file, txt_name):
        try:
            filename= pd.read_csv(csv_file)
            sqlfile = open(txt_name, 'a')
            for rows in filename.values:
                sqlfile.writelines('%d' % int(rows[0])+'\t'+str(rows[1])+','+str(rows[2])+'\n')
            sqlfile.close()
        except Exception as e:
            print(e)


if __name__ == "__main__":
    main_path = os.path.abspath(os.path.dirname(os.path.dirname(os.path.dirname(__file__))))
    print(main_path)

    """txt_to_xls"""
    # filename = main_path + "/.txt"
    # xlsname = main_path + "/.xls"
    # TxtToExcel().txt_xls(filename, xlsname)
    """xls_txt"""
    # xls_name = main_path + "/.xls"
    # txt_name = main_path + "/.txt"
    # ExcelToTxt().xls_txt(xls_name, txt_name)
    """txt_to_xlsx"""
    # inputfileTxt = main_path + "/.txt"
    # outfileExcel = main_path + "/.xlsx"
    # TxtToExcel2().txt_to_xlsx(inputfileTxt, outfileExcel)
    # TxtToExcel2().read_xlsx(outfileExcel)
    """xlsx_txt"""
    xlsx_name = main_path + "/.xlsx"
    txt_name = main_path + "/.txt"
    ExcelToTxt().xlsx_txt(xlsx_name, txt_name)
    """csv_txt"""
    # csv_name = main_path + "/csv"
    # txt_name = main_path + "/.txt"
    # CsvToTxt().csv_to_txt(csv_name, txt_name)
    # csv_name = main_path + "/.csv"
    # txt_name = main_path + "/.txt"
    # CsvToTxt().csv_to_txt_adcode2xy(csv_name, txt_name)