Is it possible to read data from an Excel sheet in Python using Xlsxwriter? If so how?

Question

I'm doing the following calculation.

worksheet.write_formula('E5', '=({} - A2)'.format(number))

I want to print the value in E5 on the console. Can you help me to do it? Is it possible to do it with Xlsxwriter or should I use a different library to the same?

Businesslike

I don't think you can read with a writer, you should be looking for a reader... check this question – Rabbi

There is Excel reading ability in Scipy. – Grotesque

Answer 1

It is not possible to read data from an Excel file using XlsxWriter.

There are some alternatives listed in the documentation.

Conjugated

Above link is no longer valid. I believe this is the new page: – Elvaelvah

Link fixed. Thanks. – Conjugated

Answer 2

If you want to use xlsxwriter for manipulating formats and formula that you can't do with pandas, you can at least import your excel file into an xlsxwriter object using pandas. Here's how.

import pandas as pd
import xlsxwriter   

def xlsx_to_workbook(xlsx_in_file_url, xlsx_out_file_url, sheetname):
    """
    Read EXCEL file into xlsxwriter workbook worksheet
    """
    workbook = xlsxwriter.Workbook(xlsx_out_file_url)
    worksheet = workbook.add_worksheet(sheetname)
    #read my_excel into a pandas DataFrame
    df = pd.read_excel(xlsx_in_file_url)
    # A list of column headers
    list_of_columns = df.columns.values

    for col in range(len(list_of_columns)):
        #write column headers.
        #if you don't have column headers remove the folling line and use "row" rather than "row+1" in the if/else statments below
        worksheet.write(0, col, list_of_columns[col] )
        for row in range (len(df)):
            #Test for Nan, otherwise worksheet.write throws it.
            if df[list_of_columns[col]][row] != df[list_of_columns[col]][row]:
                worksheet.write(row+1, col, "")
            else:
                worksheet.write(row+1, col, df[list_of_columns[col]][row])
    return workbook, worksheet

# Create a workbook
#read you Excel file into a workbook/worksheet object to be manipulated with xlsxwriter
#this assumes that the EXCEL file has column headers
workbook, worksheet = xlsx_to_workbook("my_excel.xlsx", "my_future_excel.xlsx", "My Sheet Name")

###########################################################
#Do all your fancy formatting and formula manipulation here
###########################################################

#write/close the file my_new_excel.xlsx
workbook.close()

Holiday

Answer 3

Not answering this specific question, just a suggestion - simply try pandas and read data from excel. Thereafter you can simply manipulate the data using pandas DataFrame built-in methods:

df = pd.read_excel(file_,index_col=None, header=0)

df is the pandas.DataFrame, just go through DataFrame from this it's cookbook site. If you are unaware about this package, you might get surprised by this awesome python module.

Absorptivity