1、xlsx转换成csv

import xlrd
import csv
import  codecs

def xlsx_to_csv():
    workbook = xlrd.open_workbook('test.xlsx')
    table = workbook.sheet_by_index(0)
    with codecs.open('test.csv', 'w', encoding='utf-8') as f:
        write = csv.writer(f)
        for row_num in range(table.nrows):
            row_value = table.row_values(row_num)
            write.writerow(row_value)
 
if __name__ == '__main__':
    xlsx_to_csv()

2、使用pandas转换csv并合并

def xlsx2csv(self):
        xls_file = pd.ExcelFile(self._excelname)
        sheet_names = xls_file.sheet_names
        df_all = pd.DataFrame()
        for i in sheet_names:
            data_xls = pd.read_excel(self._excelname,sheetname=i,index_col=0,encoding='utf-8')
            df_all = df_all.append(data_xls)
        df_all.to_csv(self._csvname,sep='~',encoding='utf-8')

3、csv转换成xlsx

import pandas as pd
 
def csv_to_xlsx_pd():
    csv = pd.read_csv('test.csv', encoding='utf-8')
    csv.to_excel('test.xlsx', sheet_name='data')

if __name__ == '__main__':
    csv_to_xlsx_pd()

或者写代码转换也可以

#!/usr/bin/python
# -*- coding:utf-8 -*-
#python 2.7

from openpyxl import load_workbook
import csv
import os,sys

reload(sys)
sys.setdefaultencoding('utf8')

def xlsx2csv(filename):
    try:
        xlsx_file_reader = load_workbook(filename=filename)
        for sheet in xlsx_file_reader.get_sheet_names():
            # 每个sheet输出到一个csv文件中，文件名用xlsx文件名和sheet名用'_'连接
            csv_filename = '{xlsx}_{sheet}.csv'.format(
                xlsx=os.path.splitext(filename.replace(' ', '_'))[0],
                sheet=sheet.replace(' ', '_'))
            #生成文件将xlsx文件内容写入到csv中
            csv_file = file(csv_filename, 'wb')
            csv_file_writer = csv.writer(csv_file)
            sheet_ranges = xlsx_file_reader[sheet]
            for row in sheet_ranges.rows:
                row_container = []
                for cell in row:
                    if type(cell.value) == unicode:
                        row_container.append(cell.value.encode('utf-8'))
                    else:
                        row_container.append(str(cell.value))
                csv_file_writer.writerow(row_container)
            csv_file.close()

    except Exception as e:
        print e

if __name__ == '__main__':
    #如脚本后没有跟xlsx的文件名将不会执行
    if len(sys.argv) != 2:
        print 'usage: xlsx2csv <xlsx file name>'
    else:
        xlsx2csv(sys.argv[1])
    sys.exit(0)

转载于:https://my.oschina.net/FrankXin/blog/596041

将excel(xls/xlsx)转换成csv文件

首先pom.xml 需要引进的包如下：

<dependency>
            <groupId>net.sourceforge.jexcelapi</groupId>
            <artifactId>jxl</artifactId>
            <version>2.6.12</version>
        </dependency>

接着是代码部分：

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;

import java.io.*;
import java.util.Locale;

/**
 * 将excel(xls/xlsx)转换成csv文件
 */
public class ExcelToCsv {

    /**
     * 将excel(xls/xlsx)转换成csv文件
     *
     * @param excelFile
     * @param csvFile
     * @return String
     */
    public static String getCsv(String excelFile, String csvFile) {
        //.xlsx文件后缀转成csv
        if (excelFile.endsWith(".xlsx")) {
            XLSX2CSV.trans(excelFile, csvFile);
            return csvFile;
        }
        //.xls文件后缀转成csv
        else {
            try {
                OutputStream os = new FileOutputStream(new File(csvFile));
                OutputStreamWriter osw = new OutputStreamWriter(os, "UTF8");
                BufferedWriter bw = new BufferedWriter(osw);
                // 载入Excel文件
                WorkbookSettings ws = new WorkbookSettings();
                ws.setLocale(new Locale("en", "EN"));
                Workbook wk = Workbook.getWorkbook(new File(excelFile), ws);
                // 从工作簿(workbook)取得每页(sheets)
                for (int sheet = 0; sheet < wk.getNumberOfSheets(); sheet++) {
                    Sheet s = wk.getSheet(sheet);
                    Cell[] row = null;
                    // 从每页(sheet)取得每个区块(Cell)
                    for (int i = 0; i < s.getRows(); i++) {
                        row = s.getRow(i);
                        if (row.length > 0) {
                            bw.write(row[0].getContents());
                            for (int j = 1; j < row.length; j++) {
                                //写入分隔符
                                bw.write(',');
                                bw.write(row[j].getContents());
                            }
                        }
                        bw.newLine();
                    }
                }
                bw.flush();
                bw.close();

            } catch (Exception e) {
                System.err.println(e.toString());
                e.printStackTrace();
            }
            return csvFile;
        }
    }
}