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')

GrvTyagi · Accepted Answer · 2016-02-16 10:10:04Z

5

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

answered Feb 16, 2016 at 10:10

GrvTyagi

4,5071 gold badge37 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:32:36Z

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, 2017 at 12:32

CommunityBot

11 silver badge

answered Mar 9, 2014 at 4:30

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

7 Comments

user2916886 Over a year ago

when I am doing this workbook = xlwt.Workbook() sheet = workbook.add_sheet('test') it is throwing exception as Exception: invalid worksheet name u'test'

alecxe Over a year ago

@user2916886 hm, could you please include in the question the whole code you are using? Thanks.

alecxe Over a year ago

@user2916886 that's a strange error. According to the source code it should not throw an error on test worksheet name.

alecxe Over a year ago

@user2916886 also, it's a thing that's actually not relevant to the question you've initially asked.

user2916886 Over a year ago

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

|

anurag · Accepted Answer · 2016-03-31 07:17:12Z

0

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')]

answered Mar 31, 2016 at 7:17

anurag

6343 gold badges10 silver badges27 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related