CODEWITHSUNDEEP
pythonexcelxlwt
user2916886
user2916886

Reputation: 845

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

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

Upvotes: 3

Views: 4099

Answers (3)

anurag
anurag

Reputation: 636

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

Upvotes: 0

GrvTyagi
GrvTyagi

Reputation: 4487

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

Upvotes: 5

alecxe
alecxe

Reputation: 473863

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.

Upvotes: 1

Related Questions