How can I copy a column in Excel with format using xlwings?

Question

I want to copy a column with format but there is only an attribute 'value' in range ('A1'). If I only copy the value, it turns to be like this:

eprofitfloor

    3830000000
    1750000000
    1248720000
    1200000000
     884547100
     720000000

eprofitfloor

    383000000000%
    175000000000%
    124872000000%
    120000000000%
     88454710000%
     72000000000%

  I am saying not only this column,I want to copy all the column completely,only copying value will lead me some problems like the orginal datas are align center, but the results are align left. And a lot problems like that, so I want to copy a column of an Excel file with its orignal format.

Answer 1

First you can convert it into pandas DataFrame by this way

sht = wb.sheets['Sheet1']
# in A1 column the number of row you want
val = sht.range('A1:A6').value
df = pd.DataFrame({val[0]:val[1:]})

Cast the dtype to str using astype and append% to that

 df2 = df['eprofitfloor'].astype(str)+"%"

you can also do it by using lambda

df2 = df['eprofitfloor'].apply( lambda x : str(x) + '%')

suggestion: simply read your xlsx file using pandas which gives you more functionality

 pd.ExcelFile("FileName.xlsx")