Import data into excel using Python

Question

In this code I have imported data from an excel sheet named 'emails' in two lists:

a. list1

b. list2

worked on that data and stored that data in final two lists:

a. final_list1

b. final_list2

now I want to store the data present in these two final lists in the same excel in a new sheet which I am unable to do Please help me with this

The code is not running after print(final_list2)

import xlrd
import xlwt
file_location = "E:/emails.xlsx"
workbook = xlrd.open_workbook(file_location)
sheet = workbook.sheet_by_index(0)
list1 = [sheet.cell_value(r,0) for r in range(sheet.nrows)]
type(list1)
for r in range(sheet.nrows):
        print(list1[r])

final_list1 = [] 
for num in list1:
    if num not in final_list1:
        final_list1.append(num)
print(final_list1)

list2 = [sheet.cell_value(r,1) for r in range(sheet.nrows)]
type(list2)
for r in range(sheet.nrows):
        print(list2[r])

final_list2 = [] 
for num in list2:
    if num not in final_list2:
        final_list2.append(num)
print(final_list2)


final_list2 = [elem for elem in final_list2 if elem not in final_list1 ]
print(final_list2)




book = xlwt.Workbook(encoding="utf-8")
sh = book.add_sheet("sh")
i=1
for r in range(10):
    for col_index, item in enumerate(final_list2):
        sh.write(i, col_index, item)      
book.save("emails.xlsx")

The error is:

    ---------------------------------------------------------------------------
Exception                                 Traceback (most recent call last)
<ipython-input-38-740af0fd38c9> in <module>()
     35 for r in range(10):
     36     for col_index, item in enumerate(final_list2):
---> 37         sh.write(i, col_index, item)
     38 book.save("emails.xlsx")

~\Anaconda3\lib\site-packages\xlwt\Worksheet.py in write(self, r, c, label, style)
   1086            :class:`~xlwt.Style.XFStyle` object.
   1087         """
-> 1088         self.row(r).write(c, label, style)
   1089 
   1090     def write_rich_text(self, r, c, rich_text_list, style=Style.default_style):

~\Anaconda3\lib\site-packages\xlwt\Row.py in write(self, col, label, style)
    233             if len(label) > 0:
    234                 self.insert_cell(col,
--> 235                     StrCell(self.__idx, col, style_index, self.__parent_wb.add_str(label))
    236                     )
    237             else:

~\Anaconda3\lib\site-packages\xlwt\Row.py in insert_cell(self, col_index, cell_obj)
    152                 msg = "Attempt to overwrite cell: sheetname=%r rowx=%d colx=%d" \
    153                     % (self.__parent.name, self.__idx, col_index)
--> 154                 raise Exception(msg)
    155             prev_cell_obj = self.__cells[col_index]
    156             sst_idx = getattr(prev_cell_obj, 'sst_idx', None)

Exception: Attempt to overwrite cell: sheetname='sh' rowx=1 colx=0

Welcome to SO! Please read [how to ask](https://stackoverflow.com/help/how-to-ask) and provide an [mcve](https://stackoverflow.com/help/mcve) so your question will get answered and not downvoted. — cheersmate, Oct 24 '18 at 10:06
Relevant [xlutils-only-works-on-xls-not-xlsx](https://stackoverflow.com/questions/45915266/xlutils-only-works-on-xls-not-xlsx) — stovfl, Oct 24 '18 at 10:41

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

0

EDIT:

The xlwt library does is supposed to be use only with old excel files (.xls) files so that can be causing your problem. See docs

xlwt is a library for writing data and formatting information to older Excel files (ie: .xls)

Seems like the problem is that sheet sh already exists so you can not add it. Try giving it a sheet name that does not exist already or use add_sheet("sh", true), which will overwrite the sheet.

See https://xlwt.readthedocs.io/en/latest/api.html?highlight=add for more information.

edited Jun 20 '20 at 09:12

Community

1
1

answered Oct 24 '18 at 10:10

Carlos Gonzalez

858
13
23

The problem it is showing is in write function because the sh sheet doesn't exist already and after trying this true part it is still showing the error in write part – Priyanka Munjal Oct 24 '18 at 10:58
In your code you have `sh1.write()` instead of `sh.write()` so it is complaining that sh1 does not exist. Could it be that? – Carlos Gonzalez Oct 24 '18 at 11:02
I have removed 1 from the sh1 part but there is still the problem in the parameters which I've passed in the write function – Priyanka Munjal Oct 24 '18 at 11:05
It seems that your problem is that the library you are using does not work with .xlxs files. From the first page of the documentation >xlwt is a library for writing data and formatting information to older Excel files (ie: .xls) – Carlos Gonzalez Oct 24 '18 at 11:10
Yeah I guess you are right so what should be done now which method should be used to import the data in xlsx files – Priyanka Munjal Oct 24 '18 at 11:31
try a different library e.g https://xlsxwriter.readthedocs.io . I found this with a quick google search, I'm sure the are plenty of others. – Carlos Gonzalez Oct 24 '18 at 11:36

Import data into excel using Python

1 Answers1

EDIT: