0

I have an excel template that I would like to open, and then write to on a monthly basis.

Is there a way to write write the dataframe to a specific row? Here is where I'm stuck, I can get it to write into that new file but it over writes the other rows with blanks? Is there a way to do this?

    df = pd.read_sql_query(sql, cnxn, params=[invoice])
    df.rename(columns={'COMPANYNAME': 'Company/Name', 'ADDRESS': 'Address', 'CITY': 'City', 'STATE': 'State', 'SERVICEDESCRIPTION': 'Service Description', 'INVOICE#': 'Invoice #', 'SUBTOTAL': 'Sub-Total', 'TAXAMT': 'Tax Amt', 'TOTAL': 'Total', 'ORDER': 'Order No.'}, inplace=True)

    file_name = 'BPS_Invoice_' + invoice + '.xlsx'
    copyfile("BPS_template.xlsx", file_name)
    # print(df.dtypes)
    #
    writer = pd.ExcelWriter(file_name)
    df.to_excel(writer, sheet_name='sheet1', index=False, startrow=14)
    writer.sheets['sheet1'].set_column('A:A', 30)
    writer.sheets['sheet1'].set_column('B:B', 35)
    writer.sheets['sheet1'].set_column('C:C', 15)
    writer.sheets['sheet1'].set_column('D:D', 15)
    writer.sheets['sheet1'].set_column('E:E', 15)
    writer.sheets['sheet1'].set_column('F:F', 15)
    writer.sheets['sheet1'].set_column('G:G', 15)
    writer.save()
AlliDeacon
  • 1,365
  • 3
  • 21
  • 35
  • The point was to put all your code into the `with` statement. The reason for the I/O error is because, once the `with` code exists, the file automatically closes. So, you'll have to make sure all your file op code is indented _inside_ the `with` block. – cs95 Jan 08 '18 at 21:34
  • @COLDSPEED I had it in the `with` block, indented properly. the `a` vs `ab` is where my existing workbook is being written but jumbled, vs erroring out with I/O Error. – AlliDeacon Jan 08 '18 at 21:38
  • Okay, take a look at this link: https://stackoverflow.com/questions/20219254/how-to-write-to-an-existing-excel-file-without-overwriting-data-using-pandas – cs95 Jan 08 '18 at 21:39

0 Answers0