Exception is inserting a list into a column using xlwt and python

Question

I am very new to python and I am working on lists and excel sheet write using xlwt.I created 6 colums using st.write(ID,name). My first doubt is that ID in write is the rowID or columnID(means write(1,'abc') will write to first row or first column)?

Now I have 6 lists I want to write each list to one of the columns in the sheet.How to write that? Is st.write(colIDX,0,list1) a valid write statement(will it write list1 to column 0)? If not then how can I do it?

UPDATE: This is the code that I am currently using but it is throwing exception as Exception: invalid worksheet name u'test'

import xlwt
import os
from merge import *

workbook = xlwt.Workbook()
sheet = workbook.add_sheet('test')

sheet.write(0,0,'Column1')
sheet.write(0,1,'Column2')
sheet.write(0,2,'Column3')
sheet.write(0,3,'Column4')

lst1 = list()
lst2 = list()
lst3 =  lt3 + lt4  #lt1 lt2 lt3 lt4 are lists are from merge file
lst4 =  lt1 + lt2


pt = 'C:/Users/Desktop/tmp'
for filename in os.listdir(pt):
    lst1.append(filename)
    lst2.append('PST')

ng = 'C:/Users/Desktop/tmp2'
for filename in os.listdir(ng):
    lst1.append(filename)
    lst2.append('NG')



column_number = 0
for row_number, item in enumerate(lst1):
    sheet.write(row_number, column_number, item)

column_number = 1
for row_number, item in enumerate(lst2):
    sheet.write(row_number, column_number, item)

column_number = 2
for row_number, item in enumerate(lst3):
    sheet.write(row_number, column_number, item)

column_number = 3
for row_number, item in enumerate(lst4):
    sheet.write(row_number, column_number, item)


workbook.save('test.xls')

score 5 · Answer 1 · answered Feb 16 '16 at 10:10

In my case just exceed the limit of char allow in sheet name

So, cut your name_string and try again can help you.

ws = wb.add_sheet(_small_course_name)

Excel has a hard limit on sheet names of 31 characters. Your name is 34 characters. This is not an xlwt limit. Try it manually in Excel.

For more: https://groups.google.com/forum/#!msg/python-excel/QK4iJrPDSB8/D4LmyJJ7tXkJ

score 1 · Answer 2 · edited May 23 '17 at 12:32

1

First of all, here's a Worksheet.write() implementation (source):

def write(self, r, c, label="", style=Style.default_style):
    self.row(r).write(c, label, style)

First argument is a row number, the second one is a column number.

Also, here's an example on how to write a list into the first column:

import xlwt

data = ['Hello', 'World']

workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('test')

column_number = 0
for row_number, item in enumerate(data):
    worksheet.write(row_number, column_number, item)

workbook.save('test.xls')

This will produce a test.xls file with "Hello" in the A1 cell and "World" in the A2 cell.

Also see relevant threads:

Hope that helps.

edited May 23 '17 at 12:32

Community

1
1

answered Mar 09 '14 at 04:30

alecxe

462,703
120
1,088
1,195

when I am doing this `workbook = xlwt.Workbook() sheet = workbook.add_sheet('test')` it is throwing exception as `Exception: invalid worksheet name u'test'` – user2916886 Mar 09 '14 at 05:15
@user2916886 hm, could you please include in the question the whole code you are using? Thanks. – alecxe Mar 09 '14 at 05:16
@user2916886 that's a strange error. According to the [source code](https://github.com/python-excel/xlwt/blob/master/xlwt/Utils.py#L154) it should not throw an error on `test` worksheet name. – alecxe Mar 09 '14 at 05:37
@user2916886 also, it's a thing that's actually not relevant to the question you've initially asked. – alecxe Mar 09 '14 at 05:37
even I am not able to understand that why is it throwing exception at that point. Also I have changed the topic name to make it relevant to current problem – user2916886 Mar 09 '14 at 06:30
One more doubt that I have is that suppose I am able to run this program successfully and sheet 'test' is created.Now if I run it again with new parameter in `add_sheet` say test2, will it overwrite the previous sheet 'test' or will it create another sheet with name test1 in same excel? – user2916886 Mar 09 '14 at 06:40
@user2916886 - `xlwt` always creates a new file, it's an excel writer. Could you try to upgrade your xlwt module? Also, try to use other sheet names instead of `test`. – alecxe Mar 09 '14 at 07:03

score 0 · Answer 3 · answered Mar 31 '16 at 07:17

There is one more method to do it (see if it helps)- Do it in a pythonic way

Transpose your list like below and then paste it on excel normally.

>>> a = [['a', 'b', 'c'], ['aaaaaaaaaa', 'b', 'c'], ['a', 'bbbbbbbbbb', 'c']]
>>> list(zip(*a))
[('a', 'aaaaaaaaaa', 'a'), ('b', 'b', 'bbbbbbbbbb'), ('c', 'c', 'c')]

Exception is inserting a list into a column using xlwt and python

3 Answers3