0

Update: So I just went back to excel: Convert the D column ( amount) to a number and then went to format cells and manually chose 2 decimal places and it worked fine, the text version looked perfectly aligned. However, my question is how can I automatically save the column to convert it's contents to a number with 2 decimal places. I know I am returning back to my first question but I believe it's the closest question to my problem I can think of.

I am using python to create an excel sheet that I would later convert to a text file. I have specific requirements for the width and alignment. If it's slightly changed Ellucian ( a software) will not be able to read it. For now, I am struggling to right-align the column of amount column: D . I used xlwt to right-align it. It seemed to work until I converted it to a .prn file, then it left-aligned again.

Find the code below: 

import pandas as pd
import xlwt

df = pd.read_excel("tms_report.xls", "Disbursement Detail Report")
workbook = xlwt.Workbook(encoding="ascii")
worksheet = workbook.add_sheet("Sheet", cell_overwrite_ok = False)

#Varibales: Width of the column, alignement
right_align = xlwt.easyxf("align: vert centre, horiz right")

def adjustement(data):
    x = len(data)
    del data[x - 1]
    return data

arrStudentID = adjustement(df['Student ID'])
arrStudentFirst = adjustement(df['Student First Name'])
arrStudentLast = adjustement(df['Student Last Name'])
arrAmount = adjustement(df['Payment Amount'])

worksheet.col(0).width = 256*10
worksheet.col(1).width = 256*17
worksheet.col(2).width = 256*18
worksheet.col(3).width = 256*8
i = 0
for studentID in arrStudentID:
    if len(studentID) < 7:
        studentID = "0" + studentID
    while len(studentID) >= 8:
            studentID = studentID[1:]
    worksheet.write(i, 0, studentID)
    i+=1

i = 0
for studentFirst in arrStudentFirst:
    worksheet.write(i, 1, studentFirst)
    i+=1

i = 0
for studentLast in arrStudentLast:
    worksheet.write(i, 2, studentLast)
    i+=1


#This is the part that I need to be a number or right aligned
i = 0
for amount in arrAmount:
    amount = '%.2f'%amount
    worksheet.write(i, 3, amount, right_align)
    i += 1

workbook.save("tms_import.xls")

The excel file that I receive looks perfectly like I want. But when I save it as a .prn file the right alignment of the amount column is messed up.

Also, this is an updated question, since I first thought that by converting the float into an actual number would solve my question. But thanks to the answers of users here I figured that my problem is as mentioned above. I truly appreciate your help!!

Fatima Zahra Chriha
  • 19
  • 5
  • 2
    Why do you need the extra zeros? Mathematically, 25 is equal to 25.00 so typically trailing zeros are only necessary for formatting a string – C_Z_ Jan 07 '20 at 17:42
  • 1
    I am using python to create an excel sheet, the column that contains the amount should be a number otherwise ( if string) it would be left-aligned. I already used librairies like xlwt and openxyl they do right-align it but when I convert my excel file to a text file it it left-aligned, because it's a string and not a number. This is why I insisted that the number should stay as a number. – Fatima Zahra Chriha Jan 07 '20 at 17:47
  • I already thought about this solution but as mentioned above, it doesn't work. For example, after formatting 25 to become '25.00', I then convert it to a float again it becomes 25.0 – Fatima Zahra Chriha Jan 07 '20 at 18:04
  • *"can't seem to find any solution to turn a number to a float with two decimal places"*. That's because floats don't have decimal places. What you're asking is not possible. – Stefan Pochmann Jan 07 '20 at 18:04
  • @StefanPochmann so I can never turn a number for example 4 to 4.00 without reformatting it to a string ? – Fatima Zahra Chriha Jan 07 '20 at 18:07
  • Not as float. And note that JavaScript's `toFixed` also creates strings. – Stefan Pochmann Jan 07 '20 at 18:09
  • 3
    @FatimaZahraChriha How are you doing the conversion to a text file? The issue with decimal places and floats *vs* strings is a red herring. Text files necessarily contain strings (i.e. bytes), not numbers. So the way to fix this is to use a different method for creating the text files. – ekhumoro Jan 07 '20 at 18:12
  • @StefanPochmann so there is no way I can solve this. That sucks ... Thank you for helping me out! – Fatima Zahra Chriha Jan 07 '20 at 18:13
  • @ekhumoro I create the excel file using python and then I manually save it as a text file. The problem is that I can't seem to conserve the same alignment I got in excel. I will upload an image of this problem above. – Fatima Zahra Chriha Jan 07 '20 at 18:16
  • @FatimaZahraChriha Please don't upload images - show all the code you are using for creating the text file. Or explain exactly what you mean by "manually save it as a text file". – ekhumoro Jan 07 '20 at 18:17
  • okay. Should I update the title of the question ? – Fatima Zahra Chriha Jan 07 '20 at 18:22
  • save as a .prn file from the excel software – Fatima Zahra Chriha Jan 07 '20 at 18:28
  • @Fatima Nah it just means you can't solve your actual problem the way you thought. See http://xyproblem.info/ – Stefan Pochmann Jan 07 '20 at 18:31
  • @FatimaZahraChriha What exactly is a `.pm` file? It might help if you showed a small example of a `.pm` file in your question. I suspect that it may be necessary to write a custom solution in python to solve your problem. But to do that, it would be necessary to know the exact format of the `.pm` file. – ekhumoro Jan 07 '20 at 19:06
  • It's a p. r. n file it's a Formatted Text. Do you need an image of how it looks like ( it's basically a text file with a ruler ) – Fatima Zahra Chriha Jan 07 '20 at 19:31
  • @FatimaZahraChriha After the latest edits to your question, it now looks like it's only about how to use Excel. Strictly speaking, this makes it off-topic for SO, since it is no longer a programming-related question. – ekhumoro Jan 07 '20 at 21:25
  • @ekhumoro it's not about using excel. Because I know how to reach this result using excel. Yet I want it to be automated. I don't want to go back every time and reformat the amount column. Yet, until now I couldn't find how . – Fatima Zahra Chriha Jan 07 '20 at 21:28
  • 1
    @FatimaZahraChriha Sure, but your opening paragraph reads like it is only about Excel. However, based on your latest comments, it seems your question has now changed to something more like: how do I set the decimal places format when writing cells using xlwt? If so, you might want to try the answers [here](https://stackoverflow.com/q/17887160/984421), [here](https://stackoverflow.com/q/9375637/984421) and [here](https://stackoverflow.com/q/16176211/984421). – ekhumoro Jan 07 '20 at 21:53
  • @FatimaZahraChriha - It does sound like you mainly need to set the decimal places in the formatting. You could also take a different approach and write the .prn file yourself directly using Python. This would allow you to have full and precise control over the output, and be slightly more automated (no "save as .prn" step). – John Y Jan 10 '20 at 23:05
  • Also, if you really do want to keep generating Excel files, I strongly recommend that you switch to XlsxWriter (generates .xlsx instead of .xls and has what I consider to be a nicer programming interface). – John Y Jan 10 '20 at 23:07

0 Answers0