如何将xls转换为xlsx

I have some *.xls(excel 2003) files， and I want to convert those files into xlsx(excel 2007).

我有一些*。xls(excel 2003)文件,我想把这些文件转换成xlsx(excel 2007)。

I use the uno python package, when I save the documents, I can set the Filter name: MS Excel 97 But there is no Filter name like 'MS Excel 2007',

我使用uno python包，当我保存文档时，我可以设置过滤器名称:MS Excel 97但是没有像'MS Excel 2007'这样的过滤器名称，

please help me, how can set the the filter name to convert xls to xlsx ?

请帮助我，如何设置过滤器名称将xls转换为xlsx ?

10 个解决方案

#1

I've had to do this before. The main idea is to use the xlrd module to open and parse a xls file and write the content to a xlsx file using the openpyxl module.

我以前也做过。主要思想是使用xlrd模块打开和解析xls文件，并使用openpyxl模块将内容写入xlsx文件。

Here's my code. Attention! It cannot handle complex xls files, you should add you own parsing logic if you are going to use it.

这是我的代码。注意!它不能处理复杂的xls文件，如果要使用它，应该添加自己的解析逻辑。

import xlrd
from openpyxl.workbook import Workbook
from openpyxl.reader.excel import load_workbook, InvalidFileException

def open_xls_as_xlsx(filename):
    # first open using xlrd
    book = xlrd.open_workbook(filename)
    index = 0
    nrows, ncols = 0, 0
    while nrows * ncols == 0:
        sheet = book.sheet_by_index(index)
        nrows = sheet.nrows
        ncols = sheet.ncols
        index += 1

    # prepare a xlsx sheet
    book1 = Workbook()
    sheet1 = book1.get_active_sheet()

    for row in xrange(0, nrows):
        for col in xrange(0, ncols):
            sheet1.cell(row=row, column=col).value = sheet.cell_value(row, col)

    return book1

#2

Here is my solution, without considering fonts, charts and images:

以下是我的解决方案，不考虑字体、图表和图像:

$ pip install pyexcel pyexcel-xls pyexcel-xlsx

Then do this::

然后这样做:

import pyexcel as p

p.save_book_as(file_name='your-file-in.xls',
               dest_file_name='your-new-file-out.xlsx')

If you do not need a program, you could install one additinal package pyexcel-cli::

如果你不需要一个程序，你可以安装一个额外的包pyexcelcli:

$ pip install pyexcel-cli
$ pyexcel transcode your-file-in.xls your-new-file-out.xlsx

The transcoding procedure above uses xlrd and openpyxl.

上面的代码转换过程使用xlrd和openpyxl。

#3

You need to have win32com installed on your machine. Here is my code:

您需要在您的机器上安装win32com。这是我的代码:

import win32com.client as win32
fname = "full+path+to+xls_file"
excel = win32.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Open(fname)

wb.SaveAs(fname+"x", FileFormat = 51)    #FileFormat = 51 is for .xlsx extension
wb.Close()                               #FileFormat = 56 is for .xls extension
excel.Application.Quit()

#4

The answer by Ray helped me a lot, but for those who search a simple way to convert all the sheets from a xls to a xlsx, I made this Gist:

雷的答案对我很有帮助，但对于那些想用简单的方法将所有的表单从xls转换为xlsx的人来说，我提出了以下要点:

import xlrd
from openpyxl.workbook import Workbook as openpyxlWorkbook

# content is a string containing the file. For example the result of an http.request(url).
# You can also use a filepath by calling "xlrd.open_workbook(filepath)".

xlsBook = xlrd.open_workbook(file_contents=content)
workbook = openpyxlWorkbook()

for i in xrange(0, xlsBook.nsheets):
    xlsSheet = xlsBook.sheet_by_index(i)
    sheet = workbook.active if i == 0 else workbook.create_sheet()
    sheet.title = xlsSheet.name

    for row in xrange(0, xlsSheet.nrows):
        for col in xrange(0, xlsSheet.ncols):
            sheet.cell(row=row, column=col).value = xlsSheet.cell_value(row, col)

# The new xlsx file is in "workbook", without iterators (iter_rows).
# For iteration, use "for row in worksheet.rows:".
# For range iteration, use "for row in worksheet.range("{}:{}".format(startCell, endCell)):".

You can find the xlrd lib here and the openpyxl here (you must download xlrd in your project for Google App Engine for example).

您可以在这里找到xlrd lib和openpyxl(例如，您必须在项目中为谷歌应用程序引擎下载xlrd)。

#5

I found none of answers here 100% right. So I post my codes here:

我没有找到百分之百正确的答案。所以我把代码放在这里:

import xlrd
from openpyxl.workbook import Workbook

def cvt_xls_to_xlsx(src_file_path, dst_file_path):
    book_xls = xlrd.open_workbook(src_file_path)
    book_xlsx = Workbook()

    sheet_names = book_xls.sheet_names()
    for sheet_index in range(0,len(sheet_names)):
        sheet_xls = book_xls.sheet_by_name(sheet_names[sheet_index])
        if sheet_index == 0:
            sheet_xlsx = book_xlsx.active()
            sheet_xlsx.title = sheet_names[sheet_index]
        else:
            sheet_xlsx = book_xlsx.create_sheet(title=sheet_names[sheet_index])

        for row in range(0, sheet_xls.nrows):
            for col in range(0, sheet_xls.ncols):
                sheet_xlsx.cell(row = row+1 , column = col+1).value = sheet_xls.cell_value(row, col)

    book_xlsx.save(dst_file_path)

#6

I'm improve performance for @Jackypengyu method.

我正在改进@Jackypengyu方法的性能。

• XLSX: working per row, not per cell (http://openpyxl.readthedocs.io/en/default/api/openpyxl.worksheet.worksheet.html#openpyxl.worksheet.worksheet.Worksheet.append)
• XLSX:按行工作，而不是按单元工作。
• XLS: read whole row excluding empty tail, see ragged_rows=True (http://xlrd.readthedocs.io/en/latest/api.html#xlrd.sheet.Sheet.row_slice)
• XLS:读取整个行，不包括空尾，请参阅ragged_rows=True (http://xlrd.readthedocs.io/en/latest/api.html# xlrd.sheet.row_slice)。

Merged cells will be converted too.

合并的单元也将被转换。

Results

Convert same 12 files in same order:

按相同顺序转换相同的12个文件:

Original:

原:

0:00:01.958159
0:00:02.115891
0:00:02.018643
0:00:02.057803
0:00:01.267079
0:00:01.308073
0:00:01.245989
0:00:01.289295
0:00:01.273805
0:00:01.276003
0:00:01.293834
0:00:01.261401

Improved:

改善:

0:00:00.774101
0:00:00.734749
0:00:00.741434
0:00:00.744491
0:00:00.320796
0:00:00.279045
0:00:00.315829
0:00:00.280769
0:00:00.316380
0:00:00.289196
0:00:00.347819
0:00:00.284242

Solution

def cvt_xls_to_xlsx(*args, **kw):
    """Open and convert XLS file to openpyxl.workbook.Workbook object

    @param args: args for xlrd.open_workbook
    @param kw: kwargs for xlrd.open_workbook
    @return: openpyxl.workbook.Workbook


    You need -> from openpyxl.utils.cell import get_column_letter
    """

    book_xls = xlrd.open_workbook(*args, formatting_info=True, ragged_rows=True, **kw)
    book_xlsx = Workbook()

    sheet_names = book_xls.sheet_names()
    for sheet_index in range(len(sheet_names)):
        sheet_xls = book_xls.sheet_by_name(sheet_names[sheet_index])

        if sheet_index == 0:
            sheet_xlsx = book_xlsx.active
            sheet_xlsx.title = sheet_names[sheet_index]
        else:
            sheet_xlsx = book_xlsx.create_sheet(title=sheet_names[sheet_index])

        for crange in sheet_xls.merged_cells:
            rlo, rhi, clo, chi = crange

            sheet_xlsx.merge_cells(
                start_row=rlo + 1, end_row=rhi,
                start_column=clo + 1, end_column=chi,
            )

        def _get_xlrd_cell_value(cell):
            value = cell.value
            if cell.ctype == xlrd.XL_CELL_DATE:
                value = datetime.datetime(*xlrd.xldate_as_tuple(value, 0))

            return value

        for row in range(sheet_xls.nrows):
            sheet_xlsx.append((
                _get_xlrd_cell_value(cell)
                for cell in sheet_xls.row_slice(row, end_colx=sheet_xls.row_len(row))
            ))

        for rowx in range(sheet_xls.nrows):
            if sheet_xls.rowinfo_map[rowx].hidden != 0:
                print sheet_names[sheet_index], rowx
                sheet_xlsx.row_dimensions[rowx+1].hidden = True
        for coly in range(sheet_xls.ncols):
            if sheet_xls.colinfo_map[coly].hidden != 0:
                print sheet_names[sheet_index], coly
                coly_letter = get_column_letter(coly+1)
                sheet_xlsx.column_dimensions[coly_letter].hidden = True

    return book_xlsx

#7

Simple solution

I required a simple solution to convert couple of xlx to xlsx format. There are plenty of answers here, but they are doing some "magic" that I do not completely understand.

我需要一个简单的解决方案来将xlx转换为xlsx格式。这里有很多答案，但是他们正在做一些我不完全理解的“魔法”。

A simple solution was given by chfw, but not quite complete.

chfw给出了一个简单的解决方案，但并不十分完整。

Install dependencies

Use pip to install

使用pip安装

pip install pyexcel-cli pyexcel-xls pyexcel-xlsx

Execute

All the styling and macros will be gone, but the information is intact.

所有的样式和宏都将消失，但是信息是完整的。

For single file

pyexcel transcode your-file-in.xls your-new-file-out.xlsx

For all files in the folder, one liner

for file in *.xls; do; echo "Transcoding $file"; pyexcel transcode "$file" "${file}x"; done;

#8

The Answer from Ray was clipping the first row and last column of the data. Here is my modified solution (for python3):

Ray的答案是剪切数据的第一行和最后一列。这是我修改后的解(对于python3):

def open_xls_as_xlsx(filename):
# first open using xlrd
book = xlrd.open_workbook(filename)
index = 0
nrows, ncols = 0, 0
while nrows * ncols == 0:
    sheet = book.sheet_by_index(index)
    nrows = sheet.nrows+1   #bm added +1
    ncols = sheet.ncols+1   #bm added +1
    index += 1

# prepare a xlsx sheet
book1 = Workbook()
sheet1 = book1.get_active_sheet()

for row in range(1, nrows):
    for col in range(1, ncols):
        sheet1.cell(row=row, column=col).value = sheet.cell_value(row-1, col-1) #bm added -1's

return book1

#9

I tried @Jhon Anderson's solution, works well but got an "year is out of range" error when there are cells of time format like HH:mm:ss without date. There for I improved the algorithm again:

我尝试了@Jhon Anderson的解决方案，效果很好，但是当有像HH:mm:ss等时间格式的单元格时，出现了“年超出范围”的错误。这里我再次改进了算法:

def xls_to_xlsx(*args, **kw):
"""
    open and convert an XLS file to openpyxl.workbook.Workbook
    ----------
    @param args: args for xlrd.open_workbook
    @param kw: kwargs for xlrd.open_workbook
    @return: openpyxl.workbook.Workbook对象
    """
    book_xls = xlrd.open_workbook(*args, formatting_info=True, ragged_rows=True, **kw)
    book_xlsx = openpyxl.workbook.Workbook()

    sheet_names = book_xls.sheet_names()
    for sheet_index in range(len(sheet_names)):
        sheet_xls = book_xls.sheet_by_name(sheet_names[sheet_index])
        if sheet_index == 0:
            sheet_xlsx = book_xlsx.active
            sheet_xlsx.title = sheet_names[sheet_index]
        else:
            sheet_xlsx = book_xlsx.create_sheet(title=sheet_names[sheet_index])
        for crange in sheet_xls.merged_cells:
            rlo, rhi, clo, chi = crange
            sheet_xlsx.merge_cells(start_row=rlo + 1, end_row=rhi,
            start_column=clo + 1, end_column=chi,)

        def _get_xlrd_cell_value(cell):
            value = cell.value
            if cell.ctype == xlrd.XL_CELL_DATE:
                datetime_tup = xlrd.xldate_as_tuple(value,0)    
                if datetime_tup[0:3] == (0, 0, 0):   # time format without date
                    value = datetime.time(*datetime_tup[3:])
                else:
                    value = datetime.datetime(*datetime_tup)
            return value

        for row in range(sheet_xls.nrows):
            sheet_xlsx.append((
                _get_xlrd_cell_value(cell)
                for cell in sheet_xls.row_slice(row, end_colx=sheet_xls.row_len(row))
            ))
    return book_xlsx

Then work perfect!

然后完美!

#10

CONVERT XLS FILE TO XLSX

Using python3.6 I have just come accross the same issue and after hours of struggle I solved it by doing the ff, you probably wont need all of the packages: (I will be as clear as posslbe)

使用python3.6我刚刚遇到了同样的问题，经过数小时的斗争，我用ff解决了这个问题，你可能不需要所有的软件包:(我将会像posslbe一样清楚)

make sure to install the following packages before proceeding

在继续之前，请确保安装以下包

pip install pyexcel, pip install pyexcel-xls, pip install pyexcel-xlsx,

pip安装pyexcel, pip安装pyexcel-xls, pip安装pyexcel-xlsx，

pip install pyexcel-cli

step 1:

步骤1:

import pyexcel

step 2: "example.xls","example.xlsx","example.xlsm"

步骤2:“example.xls”、“example.xlsx”、“example.xlsm”

sheet0 = pyexcel.get_sheet(file_name="your_file_path.xls", name_columns_by_row=0)

step3: create array from contents

step3:从内容创建数组。

xlsarray = sheet.to_array() 

step4: check variable contents to verify

步骤4:检查变量内容以进行验证

xlsarray

step5: pass the array held in variable called (xlsarray) to a new workbook variable called(sheet1)

步骤5:将变量(xlsarray)中的数组传递给一个名为(sheet1)的新工作簿变量

sheet1 = pyexcel.Sheet(xlsarray)

step6: save the new sheet ending with .xlsx (in my case i want xlsx)

步骤6:保存以.xlsx结尾的新表(在我的例子中，我想要xlsx)

sheet1.save_as("test.xlsx")