0

I want to insert a list to existing excel sheet and column. I insert the list to the column but after saving the changes it deletes all other sheets because I use to_excel:

mysel = [2000, 1, 1, 2018, 1, 1]
xl = pd.ExcelFile('C:\HT_Projeler\mm\HidrolojiModeli\proje.xlsx')
df = xl.parse("Sheet1")

df
   Model Giridisi  Değer
0  Başlangıç Yılı   nan
1  Başlangıç  Ayı   nan
2  Başlangıç Günü   nan
3      Bitiş Yılı   nan
4       Bitiş Ayı   nan
5      Bitiş Günü   nan

df["Değer"] = mysel
df.to_excel(xl)

How can I edit or insert list only specific column in specific sheet?

Mustafa Uçar
  • 442
  • 1
  • 6
  • 18
  • if you want to add a sheet and not remove the existing sheets while writing in excel , use Excelwriter , have a look at this : https://stackoverflow.com/questions/42370977/how-to-save-a-new-sheet-in-an-existing-excel-file-using-pandas – anky Aug 27 '18 at 13:14
  • @anky_91 I jut want to ediitng also existing sheet not want to add new sheet – Mustafa Uçar Aug 27 '18 at 13:15
  • Then use: https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_excel.html >>> writer = pd.ExcelWriter('output.xlsx') >>> df1.to_excel(writer,'Sheet1') >>> df2.to_excel(writer,'Sheet2') >>> writer.save() – anky Aug 27 '18 at 13:17
  • @anky_91 but I have already xlsx file, ExcelWriter create new xlsx file – Mustafa Uçar Aug 27 '18 at 13:25
  • you can use as below. – anky Aug 27 '18 at 13:43

1 Answers1

1

Use:

mysel = [2000, 1, 1, 2018, 1, 1]
xl = pd.ExcelFile('C:\HT_Projeler\mm\HidrolojiModeli\proje.xlsx')
df = xl.parse("Sheet1")
df["Değer"] = mysel
import pandas
from openpyxl import load_workbook

book = load_workbook('C:\HT_Projeler\mm\HidrolojiModeli\proje.xlsx')
writer = pandas.ExcelWriter('C:\HT_Projeler\mm\HidrolojiModeli\proje.xlsx', engine='openpyxl') 
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

df.to_excel(writer, "Sheetname",index=False)

writer.save()
anky
  • 74,114
  • 11
  • 41
  • 70
  • Where is my changes? – Mustafa Uçar Aug 27 '18 at 13:37
  • i didnt get you, it didnt work? what is the result? replace the sheetname with your sheetname – anky Aug 27 '18 at 13:38
  • here I use df["Değer"] = mysel to insert list my df. But I didnt see here – Mustafa Uçar Aug 27 '18 at 13:45
  • So after you insert the values into the df using df["Değer"] = mysel , use this code to write into the excel file. :) this is just to write the data which you already have – anky Aug 27 '18 at 13:49
  • but ExcelWriter dont has parse and dataFrame as I can see – Mustafa Uçar Aug 27 '18 at 13:51
  • Excelwriter is just used for writing. you can read it back using whatever excel modules you want. There are some limitations in all the excel modules, however this should work, if not let me know where are you getting stuck – anky Aug 27 '18 at 14:01
  • 1
    It works but there is something more. It gives additional column which shows like auto-increment index. It comes from dataFrame probably. Here is the image: https://ibb.co/mZvfXp. I dont have first column normally – Mustafa Uçar Aug 27 '18 at 14:08
  • Check the code now, i added index=False while writing the data :) – anky Aug 27 '18 at 14:12