CODEWITHSUNDEEP
pythonpython-2.7csvxlsxwriterxlwt
Jeremy_L
Jeremy_L

Reputation: 23

Changing the format of cells in python 2.7 using xlsxwriter or xlwt

I am still relatively new to python and having difficulty with a little project. I am converting a csv file to xls file which I have accomplished, and now I am wanting to perform some calculations on some parts of the new xls file. All values are stored as Text, I am trying to convert the cells with numbers into numbers.

From my reading you cannot change cell type without erasing the data stored in the cell using xlsxwriter, is there a way to format the cells prior to writing the data? I have not found any way to convert cell type using xlwt.

I do not think conditional formatting will work in this case as I have not seen a way for it to detect a number and change the format of the cell.

My simple program just copies the csv file and creates a xls file, if there is a way to format the cells as they are being written that is great, if not I know the exact cells the data will be going into so I can format them prior to being written. 

import csv, xlsxwriter
def makeXls(path, fileName):
    wb = xlsxwriter.Workbook(fileName+'.xls')
    ws = wb.add_worksheet('Report')
    with open(path, "rb") as f:
        reader = csv.reader((line.replace('\0','') for line in f), delimiter=';')
        for r, row in enumerate(reader):
            for c, val in enumerate(row):
                ws.write(r,c,val)

Upvotes: 2

Views: 1935

Answers (2)

jmcnamara
jmcnamara

Reputation: 41574

All values are stored as Text, I am trying to convert the cells with numbers into numbers.

You can use the XlsxWriter strings_to_numbers constructor option:

wb = xlsxwriter.Workbook(fileName + '.xlsx', {'strings_to_numbers': True})

From the XlsxWriter docs:

strings_to_numbers: Enable the worksheet.write() method to convert strings to numbers, where possible, using float() in order to avoid an Excel warning about "Numbers Stored as Text".

Upvotes: 0

patrickjlong1
patrickjlong1

Reputation: 3823

The example I've posted below works with your existing code to show how you might use conditional formatting in excel via the xlsxwriter module (when a value is greater than or equal to zero in this example the font color is set to a dark red).

I made up a small test.csv that is written/read to/from the current directory and used to create test.xls. Also, please note that I removed the "rb" for reading in the .csv file that I made.

More information on working with conditional formats in the Xlsxwriter module can be found here

import pandas as pd
import numpy as np
import csv
import xlsxwriter

# This creates a sample .csv file used in my example
df = pd.DataFrame(np.random.randn(10, 3), columns=list('ABC'))
df.to_csv('test.csv' , sep = ';')

def makeXls(path, fileName):
    wb = xlsxwriter.Workbook(fileName+'.xls', {'strings_to_numbers':  True})
    format_1 = wb.add_format({'num_format': '#.########'})
    format_2 = wb.add_format({'num_format': '#.########', 'font_color' : '#9C0006'})
    ws = wb.add_worksheet('Report')
    with open(path) as f:
        reader = csv.reader((line.replace('\0','') for line in f), delimiter=';')
        for r, row in enumerate(reader):
            for c, val in enumerate(row):
                ws.write(r,c, val, format_1)
    ws.conditional_format('B2:K50', {'type':'cell', 'criteria': '>=', 'value': 0, 'format':   format_2})
    ws.set_column(0, 3, 15)


makeXls(path = 'test.csv', fileName = 'test')

Expected Output:

Expected test.xls

For best results with the xlsxwriter module, I would recommend using the .xlsx file format rather than the .xls.

Upvotes: 1

Related Questions