I lose leading zeros when copy data from dataframe to openpyxl.workbook-CodePudding

I use openpyxl and pandas to fill row color with specified condition. Everything works fine but in some cells I lose leading zeros (like 0345 -> output 345), I don't want that. How can I get the exact data?

dt = pd.read_excel(file_luu, sheet_name="Sheet1")
dt = pd.DataFrame(dt)
dinhDanh = len(dt.columns) - 1
wb = load_workbook(file_luu)
print(type(wb))
ws = wb['Sheet1']
for i in range(0, dt.shape[1]):
    ws.cell(row=1, column=i   1).value = dt.columns[i]
for row in range(dt.shape[0]):
    for col in range(dt.shape[1]  ):

        ws.cell(row   2, col   1).value = str(dt.iat[row, col]) if (str(dt.iat[row, col]) != "nan") else " "

        if dt.iat[row, dinhDanh] == True:
            ws.cell(row   2, col   1).fill = PatternFill(start_color='FFD970', end_color='FFD970',
                                                         fill_type="solid")  # used hex code for brown color

ws.delete_cols(1)
ws.delete_cols(dinhDanh)
wb.save(file_luu)

Copy exactly all characters

CodePudding user response：

To prevent losing leading zeros when writing data to an Excel file with openpyxl and pandas, you can specify that the cell should be formatted as a string by setting the number_format property of the cell to @. This tells Excel that the cell should be treated as a string, and any leading zeros will be preserved.

# Import the openpyxl Workbook and cell classes
from openpyxl.workbook import Workbook
from openpyxl.cell import Cell

dt = pd.read_excel(file_luu, sheet_name="Sheet1")
dt = pd.DataFrame(dt)
dinhDanh = len(dt.columns) - 1
wb = load_workbook(file_luu)
print(type(wb))
ws = wb['Sheet1']
for i in range(0, dt.shape[1]):
    ws.cell(row=1, column=i   1).value = dt.columns[i]
for row in range(dt.shape[0]):
    for col in range(dt.shape[1]  ):

        # Create a cell with the value from the DataFrame, and specify that it should be formatted as a string
        cell = Cell(ws, row   2, col   1, value=dt.iat[row, col], number_format="@")

        # Set the cell's value and fill color
        ws.cell(row   2, col   1).value = str(dt.iat[row, col]) if (str(dt.iat[row, col]) != "nan") else " "
        if dt.iat[row, dinhDanh] == True:
            ws.cell(row   2, col   1).fill = PatternFill(start_color='FFD970', end_color='FFD970',
                                                         fill_type="solid")  # used hex code for brown color

ws.delete_