openpyxl - need help with a function to change cells to a text format (not General or Number or Dates) - like when you Right click on a cell to change formatting in excel

[Scott Evans]

I need help with how to change excel columns to text before the python script copies them from one file to another file

currently using openpyxl....

the problem is, when something has a date in it, I need it to copy as straight text, and not as a date

so when somebody has typed 6/17/2019... its stays as EXACTLY that

instead of getting changed into something like 2019-06-17 0:00:00

I *think* the right place to do it would be right after I load each workbook.

I know if I manually change the files so that the columns needed are formatted as "text", the script works perfect and everything copies as intended.

I'm looking for a function in openpyxl that is the same thing as right clicking the cell in excel, selecting formatting, and choosing "text"

#####
#Copy over Notes
# open both files, load them in,




orig_wb = load_workbook('old_qc_report.xlsx')
new_wb = load_workbook('new_qc_report.xlsx')


########///////////////////////////////////////////////////////////////////////////////////////////////////////////////////


#open Old QC Report and Iterate thru Columns F and H to change to text..... "number_format='@'" ????


########////////////////////////////////////////////////////////////////////////////////////////////////////////////////////




sheetlist = orig_wb.get_sheet_names()
sheet = orig_wb.get_sheet_by_name(sheetlist[0])
sheetlist2 = new_wb.get_sheet_names()
sheet2 = new_wb.get_sheet_by_name(sheetlist[0])




s1rownumber = 1
for s1row in sheet.iter_rows(min_row=2, max_row= sheet.max_row, min_col=6, max_col=6, values_only=True):
  s1rownumber +=1
  for cell in s1row:
    if cell != None:
      transfer_cust_rec = cell
      product_description_ref = sheet[str("A" + str(s1rownumber))].value
      order_No_ref = sheet[str("C" + str(s1rownumber))].value

      s2rownumber = 1
      for s2row in sheet2.iter_rows(min_row = 2, min_col=1, max_col=1, values_only=True):
        s2rownumber +=1
        for cell2 in s2row:
          if cell2 == product_description_ref:
            if sheet2[str("C" + str(s2rownumber))].value == order_No_ref:
              sheet2[str("F" + str(s2rownumber))].value = transfer_cust_rec




s1rownumber = 1
for s1row in sheet.iter_rows(min_row=2, max_row= sheet.max_row, min_col=8, max_col=8, values_only=True):
  s1rownumber +=1
  for cell in s1row:
    if cell != None:
      transfer_notes = cell
      product_description_ref = sheet[str("A" + str(s1rownumber))].value
      order_No_ref = sheet[str("C" + str(s1rownumber))].value


      s2rownumber = 1
      for s2row in sheet2.iter_rows(min_row = 2, min_col=1, max_col=1, values_only=True):
        s2rownumber +=1
        for cell2 in s2row:
          if cell2 == product_description_ref:
            if sheet2[str("C" + str(s2rownumber))].value == order_No_ref:
              sheet2[str("H" + str(s2rownumber))].value = transfer_notes


new_wb.save(outfilepath)


os.remove("old_qc_report.xlsx")
os.remove("new_qc_report.xlsx")

[Deac-33 Lancaster]

Scott,

I'm sorry that no one has responded with suggestions, have you been able to resolve the problem?

I'm struggling with a simpler perhaps related problem of trying to read the Excel data formatting for a cell that you may be able to assist with.  I know the cell is formatted as floating point and I can read the full precision value but I need to find out the decimal digits displayed so that I can output it exactly that way.   Do you have an idea of where to point me?

I hope you got yours solved, looks like a simple idea to want to do but not always easy to find out how.

thanks,

-deac33