how to use format read from xlrd and write thru xlsxwriter in python

Question

I am reading an excel file using xlrd. Doing some macro replacing and then writing thru xlsxwriter. Without reading and copying formatting info the code works but when I add formatting info I get an error (at the bottom) The code snippet is below..I read a xls file, for each data row I replace token macros with the values and write back. when I try to close the output_workbook I get an error

    filePath = os.path.realpath(os.path.join(inputPath,filename))
    input_workbook = open_workbook(filePath, formatting_info=True)
    input_DataSheet = input_workbook.sheet_by_index(0)
    data = [[input_DataSheet.cell_value(r,c) for c in range(input_DataSheet.ncols)] for r in range(input_DataSheet.nrows)]

    output_workbook = xlsxwriter.Workbook('C:\Users\Manish\Downloads\Sunny\Drexel_Funding\MacroReplacer\demo.xlsx')
    output_worksheet = output_workbook.add_worksheet()

    for rowIndex, value in enumerate(data):
        copyItem = []

        for individualItem in value:
            tempItem = individualItem

            if (isinstance(individualItem, basestring)):
                tempItem = tempItem.replace("[{0}]".format(investorNameMacro), investorName)
                tempItem = tempItem.replace("[{0}]".format(investorPhoneMacro), investorPhone)
                tempItem = tempItem.replace("[{0}]".format(investorEmailMacro), investorEmail)
                tempItem = tempItem.replace("[{0}]".format(loanNumberMacro), loanNumber)

            copyItem.append(tempItem)

        for columnIndex, val in enumerate(copyItem):
            fmt =input_workbook.xf_list[input_DataSheet.cell(rowIndex,columnIndex).xf_index]
            output_worksheet.write(rowIndex,columnIndex, val,fmt)

    output_workbook.close()

The error that I get is

Traceback (most recent call last): File "C:/Users/Manish/Downloads/Sunny/Drexel_Funding/MacroReplacer/drexelfundingmacroreplacer.py", line 87, in output_workbook.close() File "build\bdist.win-amd64\egg\xlsxwriter\workbook.py", line 297, in close File "build\bdist.win-amd64\egg\xlsxwriter\workbook.py", line 605, in _store_workbook File "build\bdist.win-amd64\egg\xlsxwriter\packager.py", line 131, in _create_package File "build\bdist.win-amd64\egg\xlsxwriter\packager.py", line 189, in _write_worksheet_files File "build\bdist.win-amd64\egg\xlsxwriter\worksheet.py", line 3426, in _assemble_xml_file File "build\bdist.win-amd64\egg\xlsxwriter\worksheet.py", line 4829, in _write_sheet_data File "build\bdist.win-amd64\egg\xlsxwriter\worksheet.py", line 5015, in _write_rows File "build\bdist.win-amd64\egg\xlsxwriter\worksheet.py", line 5183, in _write_cell AttributeError: 'XF' object has no attribute '_get_xf_index'

any help is appreciated

Thanks

score 1 · Answer 1 · answered Aug 15 '16 at 07:03

1

The Xlrd and XlsxWriter formats are different object types and are not interchangeable.

If you wish to preserve formatting you will have to write some code that translates the properties from one to the other.

answered Aug 15 '16 at 07:03

jmcnamara

38,196
6
90
108

Is this also true for openpyxl and xlrd? If so, how does one read in an xlsx and then write it back out while preserving formatting in a cross-platform way? – virtualxtc Jun 18 '18 at 08:42

how to use format read from xlrd and write thru xlsxwriter in python

1 Answers1

Linked