Can xlwt insert columns?
2,501 views
Skip to first unread message
YuzhaoHuang
Aug 1, 2010, 10:24:23 PM8/1/10
to python-excel
Hi all,
Does have any solutions to inserting columns to a existing xls
fils? I found that xlwt don't have any functions like insert_column.
You kindly help will be appreciate.....
Does have any solutions to inserting columns to a existing xls
fils? I found that xlwt don't have any functions like insert_column.
You kindly help will be appreciate.....
John Machin
Aug 2, 2010, 7:26:27 PM8/2/10
to python...@googlegroups.com
On 2/08/2010 12:24 PM, YuzhaoHuang wrote:
> Hi all,
> Does have any solutions to inserting columns to a existing xls
> fils? I found that xlwt don't have any functions like insert_column.
> Hi all,
> Does have any solutions to inserting columns to a existing xls
> fils? I found that xlwt don't have any functions like insert_column.
Hi Yuzhao,
xlwt doesn't have a sheet.insert_columns method because (1) it was
intended to be used for creating xls files, not for changing existing
files (2) nobody has asked for it.
What one needs to do is read the original file with xlrd and write out a
modified file using xlwt. Have a look at xlutils.filter in the tutorial
that you'll find at http://www.python-excel.org
Cheers
John
bijendra
Aug 3, 2010, 2:48:07 AM8/3/10
to python...@googlegroups.com
try to this complete. it may help u
def report(billing_sn):
import os
import shutil
import sys
import string
import urllib
import xlwt
from xlwt import Workbook,Worksheet,Utils,Row,Style,UnicodeUtils,Formatting,ExcelMagic,CompoundDoc,Column,Cell,BIFFRecords,Bitmap,antlr
from datetime import datetime
import MySQLdb
import xml.dom.minidom
from xml.dom.minidom import parse,parseString
import datetime
from StringIO import StringIO
import cStringIO
dbhost="localhost"
dbuser=" "
dbpasswd=" "
dbname=" "
#print sno
#def print_report(billing_sn):
conn1 = MySQLdb.connect(host = dbhost, user = dbuser, passwd = dbpasswd, db = dbname)
cursor = conn1.cursor()
#billing_sn=int(billing_sn)
#sql = """select * from measurement_sheet ;"""
#sql = """select * from measurement_sheet where sno =""" billing_sn """ ;"""
billing_sno = {'billing_sn': billing_sn}
cursor.execute("select * from measurement_sheet where sno = %(billing_sn)s",billing_sno)
results = cursor.fetchall()
for row in results:
lsno=row[0]
tktnow=row[7]
dnno=row[4]
cfno=row[3]
spoint=row[1]
address=row[0]
officrName=row[44]
#cursor1=cursor.fetchone()
#cursor1=cursor.fetch_row()
#result=cursor1
#return result
#print sno
Fillno='' #value variable
#tktnow=72620
#lsno=row
franchname='Telematics Inc.'
#dnno=7314048300
#cfno=0
ak='AK'
#spoint='Spare Point'
#address='302 shriji towar,383-84 Khatiwala IND'
othval='SHIFTING'
hwtchnician='H W Technician'
#officrName=''
dt=datetime.datetime.now()
style0 = xlwt.easyxf('font:name times New Roman, color-index blue, bold on',num_format_str='#,##0.00')
style2 = xlwt.easyxf('font:name times New Roman, color-index black, bold on',num_format_str='#,##0.00')
style1 = xlwt.easyxf(num_format_str='D-MM-YY')
wb = xlwt.Workbook()
ws = wb.add_sheet('A test sheet')
for h in range(0, 1): # Starting First(1) Row ws.write_merge(h+0, h+0, 0, 16, 'Measurement Sheet for SA Work %d' % h, style0)
ws.write(1,0, 'Laying Sheet no.', style0)
for i in range(0, 1):
ws.write_merge(i+1, i+1, 1, 3, lsno)
for j in range(0, 1): ws.write_merge(j+1, j+1, 4, 7, 'Date of Filling up Laying sheet %d' % j, style0)
for k in range(0, 1): ws.write_merge(j+1, j+1, 8, 10,dt,style1)
ws.write(1,11, 'Filled By', style0)
for n in range(0, 1): ws.write_merge(n+1, n+1, 12, 13,Fillno)
ws.write(1,14, 'Tecket', style0)
for n in range(0, 1): ws.write_merge(n+1, n+1, 15, 16,tktnow) # End First Row
ws.write(2, 0, 'Franchisee', style0) # Starting Second(2) Row
for i in range(0, 1):
ws.write_merge(i+2, i+2, 1, 3, franchname)
for i in range(0, 1):
ws.write_merge(i+2, i+2, 4, 5, 'Node',style1)
for i in range(0, 1):
ws.write_merge(i+2, i+2, 6, 10, 'Zone3',style1)
ws.write(2, 11, 'Dn No', style0)
for i in range(0, 1):
ws.write_merge(i+2, i+2, 12, 13, dnno)
ws.write(2, 14, 'Caf.', style0)
for n in range(0, 1): ws.write_merge(n+2, n+2, 15, 16,cfno) # End Second Row
ws.write(3, 0, 'RSU', style0) # Starting Third(3) Row
for i in range(0, 1):
ws.write_merge(i+3, i+3, 1, 3, ak)
for i in range(0, 1):
ws.write_merge(i+3, i+5, 4, 5, 'Type of Connection(Plstick)', style0)
ws.write(3, 6, 'New', style0)
ws.write(3, 7, 'Shifting', style0)
ws.write(3, 8, 'Reconnection', style0)
for i in range(0, 1):
ws.write_merge(i+3, i+3, 9, 10, 'Others', style0)
ws.write(3, 11, 'New DP', style0)
ws.write(3, 12, 'Existing Dp', style0)
for i in range(0, 1):
ws.write_merge(i+3, i+3, 13, 15, 'Additional Case from New DP', style0)
ws.write(3, 16, 'Remarks', style0) # End Third Row
ws.write(4, 0, 'Customer Name', style0) # Starting Fourth(4) Row
for i in range(0, 1):
ws.write_merge(i+4, i+4, 1, 3, spoint)
for i in range(0, 1):
ws.write_merge(i+4, i+5, 9, 10, othval) # End Fourth Row
ws.write(5, 0, 'Installation Address', style0) # Starting Fifth(5) Row
for i in range(0, 1):
ws.write_merge(i+5, i+5, 1, 3, address) # End Fifth Row
ws.write(6, 0, 'Nearest N/W element(tick)', style0) # Starting Sixth(6) Row
ws.write(6, 1, 'MDF', style2)
ws.write(6, 2, 'Pillar', style2)
ws.write(6, 3, 'SP', style2)
ws.write(6, 4, 'DP', style2)
ws.write(6, 5, 'Pole', style2)
for i in range(0, 1):
ws.write_merge(i+6, i+7, 6, 6, hwtchnician)
for i in range(0, 1):
ws.write_merge(i+6, i+7, 7, 9, ' ', style2)
for i in range(0, 1):
ws.write_merge(i+6, i+7, 10, 12, 'Insl. Officer Name ', style2)
for i in range(0, 1):
ws.write_merge(i+6, i+7, 13, 16, officrName) # End Sixth Row
ws.write(7, 0, 'N/W Element NO.', style0) # Starting Sexenth(7) Row
for i in range(0, 1):
ws.write_merge(i+7, i+7, 1, 5, '') # End Sexenth(7) Row
ws.write(8, 0, 'Subscriber Refusal/Row Issue', style0) # Starting Eighth(8) Row
for i in range(0, 1):
ws.write_merge(i+8, i+8, 1, 6, '')
for i in range(0, 1):
ws.write_merge(i+8, i+8, 7, 8, 'Category',style0)
ws.write(8, 9, 'Key', style0)
for i in range(0, 1):
ws.write_merge(i+8, i+8, 10, 11, 'PCO/CCB',style0)
for i in range(0, 1):
ws.write_merge(i+8, i+8, 12, 13, 'SME',style0)
for i in range(0, 1):
ws.write_merge(i+8, i+8, 14, 16, 'Residential',style0) # End Eighth(8) Row
ws.write(9, 0, 'Deviations if any', style0) # Starting Nine(9) Row
ws.write(9, 1, '1', style2)
for i in range(0, 1):
ws.write_merge(i+9, i+9, 2, 5, '')
ws.write(9,6,'3',style2)
for i in range(0, 1):
ws.write_merge(i+9, i+9, 7, 10, '')
ws.write(9,11,'5',style2)
for i in range(0, 1):
ws.write_merge(i+9, i+9, 12, 16, '') # End Nine(9) Row
for i in range(0, 1): # Starting Tenth(10) Row
ws.write_merge(i+10, i+19, 0, 0, 'Digging Details',style0)
ws.write_merge(i+10,i+10,1,3,'Distance(Mtrs)',style0)
ws.write_merge(i+10,i+10,4,5,'Soil Strate',style0)
ws.write_merge(i+10,i+10,6,7,'Actual Lenght',style0)
ws.write_merge(i+10,i+10,8,9,'GI Pipe(Mtrs)',style0)
ws.write_merge(i+10,i+10,10,11,'Hope',style0)
ws.write(10,12,'PCC',style0)
for i in range(0,1):
ws.write_merge(i+10,i+10,13,16,'Digging details(soil/pcc/duct)',style0) # End Tenth(10) Row
ws.write(11,1,'From',style0) # Starting Eleventh(11) Row
ws.write(11,2,'To',style0)
ws.write(11,3,'Sub-Total',style0)
ws.write(11,4,'Normal',style0)
ws.write(11,5,'Soft',style0)
ws.write(11,6,'Normal',style0)
ws.write(11,7,'Soft',style0)
ws.write(11,8,'25mm',style2)
ws.write(11,9,'40mm',style2)
ws.write(11,10,'25mm',style2)
ws.write(11,11,'40mm',style2)
ws.write(11,12,' ',style2)
for i in range(0,1):
ws.write_merge(i+11,i+19,13,16,' ',style2) # Starting Eleventh(11) Row
ws.write(12,1,' ',style0) # Starting (12) Row
ws.write(12,2,' ',style0)
ws.write(12,3,' ',style0)
ws.write(12,4,' ',style0)
ws.write(12,5,' ',style0)
ws.write(12,6,'0',style2)
ws.write(12,7,'0',style2)
ws.write(12,8,' ',style2)
ws.write(12,9,'',style2)
ws.write(12,10,'',style2)
ws.write(12,11,'',style2)
ws.write(12,12,' ',style2) # End (12) Row
ws.write(13,1,' ',style0) # Starting (13) Row
ws.write(13,2,' ',style0)
ws.write(13,3,' ',style0)
ws.write(13,4,' ',style0)
ws.write(13,5,' ',style0)
ws.write(13,6,' ',style2)
ws.write(13,7,' ',style2)
ws.write(13,8,' ',style2)
ws.write(13,9,'',style2)
ws.write(13,10,'',style2)
ws.write(13,11,'',style2)
ws.write(13,12,' ',style2) # End (13) Row
ws.write(14,1,' ',style0) # Starting (14) Row
ws.write(14,2,' ',style0)
ws.write(14,3,' ',style0)
ws.write(14,4,' ',style0)
ws.write(14,5,' ',style0)
ws.write(14,6,' ',style2)
ws.write(14,7,' ',style2)
ws.write(14,8,' ',style2)
ws.write(14,9,'',style2)
ws.write(14,10,'',style2)
ws.write(14,11,'',style2)
ws.write(14,12,' ',style2) # End (14) Row
for i in range(0, 1): # Starting (20) Row
ws.write_merge(i+20, i+20, 0, 2, 'Cable Details',style0)
ws.write(20,3,'Length(Mtrs)',style0)
for i in range(0, 1):
ws.write_merge(i+20, i+20, 4, 6, ' ',style2)
for i in range(0, 1):
ws.write_merge(i+20, i+20, 7, 8, ' ',style2)
for i in range(0, 1):
ws.write_merge(i+20, i+24, 9, 9, ' ',style2)
for i in range(0, 1):
ws.write_merge(i+20, i+24, 10, 13, ' ',style2)
for i in range(0, 1):
ws.write_merge(i+20, i+24, 14, 16, '0',style2) # End (20) Row
for i in range(0, 1): # Starting (21) Row
ws.write_merge(i+21, i+21, 0, 2, 'Laying U/G',style0)
ws.write(21,3,' ',style2)
for i in range(0, 1):
ws.write_merge(i+21, i+21, 4, 6, ' ',style2)
ws.write_merge(i+21, i+21, 7, 8, ' ',style2) # Starting (21) Row
for i in range(0, 1): # Starting (22) Row
ws.write_merge(i+22, i+22, 0, 2, 'Pulling inside pipe',style0)
ws.write(22,3,' ',style2)
for i in range(0, 1):
ws.write_merge(i+22, i+22, 4, 6, ' ',style2)
ws.write_merge(i+22, i+22, 7, 8, ' ',style2) # Starting (22) Row
for i in range(0, 1): # Starting (23) Row
ws.write_merge(i+23, i+23, 0, 2, 'Pairage',style0)
ws.write(23,3,' ',style2)
for i in range(0, 1):
ws.write_merge(i+23, i+23, 4, 6, ' ',style2)
ws.write_merge(i+23, i+23, 7, 8, ' ',style2) # Starting (23) Row
ws.write(24,0,' ',style0) # Starting (24) Row
ws.write(24,1,' ',style0)
ws.write(24,2,' ',style0)
ws.write(24,3,' ',style0)
for i in range(0, 1):
ws.write_merge(i+24, i+24, 4, 8, ' ',style2) # Starting (24) Row
ws.write(25,0,'Jointing done if any',style0) # Starting (25) Row
for i in range(0, 1):
ws.write_merge(i+25, i+25, 1, 2, 'TSE 1(Nos) ',style0)
ws.write_merge(i+25, i+25, 3,4,'TSE(Nos)',style0)
ws.write_merge(i+25, i+25, 5,6,'Sleeves',style0)
ws.write_merge(i+25, i+26, 7,8,'Pairs termination(Nose)',style0)
ws.write(25,9,'DP',style0)
ws.write(25,10,'SP',style0)
ws.write(25,11,'Pillar',style0)
ws.write(25,12,'MDF',style0)
for i in range(0, 1):
ws.write_merge(i+25, i+26, 13, 16, 'Remarks',style0) # END (25) Row
ws.write(26,0,'Qty.',style0) # Starting (26) Row
for i in range(0, 1):
ws.write_merge(i+26, i+26, 1, 2, ' ',style2)
ws.write_merge(i+26, i+26, 3,4,' ',style2)
ws.write_merge(i+26, i+26, 5,6,' ',style2)
#ws.write_merge(i+25, i+26, 7,8,'Pairs termination(Nose)',style0)
ws.write(26,9,' ',style2)
ws.write(26,10,' ',style2)
ws.write(26,11,' ',style2)
ws.write(26,12,' ',style2) # END (26) Row
for i in range(0, 1): # Starting (27) Row
ws.write_merge(i+27, i+30, 0, 0, 'DP Installation',style0)
ws.write(27,1,'Capacity',style0)
ws.write(27,2,'Quantity',style0)
for i in range(0, 1):
ws.write_merge(i+27, i+28, 3, 5, 'GL Pipe A Class',style0)
ws.write_merge(i+27, i+28, 6, 8, 'HDPE',style0)
ws.write_merge(i+27, i+28, 9, 10, 'DP',style0)
ws.write_merge(i+27, i+27, 11, 12, '1. With Module',style0) # End (27) Row
for i in range(0, 1): # Begin (28) Row
ws.write_merge(i+28, i+30, 1, 1, ' ',style0)
ws.write_merge(i+28, i+30, 2, 2, ' ',style0)
ws.write_merge(i+28, i+28, 11, 12, '2. Without Module',style0) # End (28) Row
ws.write(29,3,'Size',style0) # Begin (29) Row
ws.write(29,4,'Length',style0)
ws.write(29,5,'Clamps',style0)
ws.write(29,6,'Size',style0)
ws.write(29,7,'Length',style0)
ws.write(29,8,'Clamps',style0)
ws.write(29,9,'Number',style0)
for i in range(0, 1):
ws.write_merge(i+29, i+29, 10, 12, ' ',style0) # End (29) Row
ws.write(30,3,' 25mm',style2) # Beging (30 Row
ws.write(30,4,' ',style0)
ws.write(30,5,' ',style0)
ws.write(30,6,' ',style2)
ws.write(30,7,' ',style0)
ws.write(30,8,' ',style0)
ws.write(30,9,'Number painting',style2)
for i in range(0, 1):
ws.write_merge(i+30, i+30, 10, 12, ' ',style0) # End (30) Row
ws.write(31,3,'40mm',style2) # Beging (31) Row
ws.write(31,4,' ',style2)
ws.write(31,5,' ',style2)
ws.write(31,6,' ',style2)
ws.write(31,7,' ',style2)
ws.write(31,8,' ',style2)
#ws.write(31,9,'',style2)
for i in range(0, 1):
ws.write_merge(i+31, i+31, 9, 11, 'DP Existing New ',style0) # End (31) Row
ws.write(32,0,' House Fitting',style0)
for i in range(0, 1): # Starting (32) Row
ws.write_merge(i+32, i+32, 1, 2, 'Instrument',style0)
ws.write_merge(i+32, i+33, 3, 4, 'PVT Pipe',style0)
ws.write_merge(i+32, i+33, 5, 6, 'Drop Wire',style0)
ws.write_merge(i+32, i+32, 7, 11, 'PVC Wire',style0)
ws.write_merge(i+32, i+32, 12, 16, 'Modem Details(Type/Ser. No.)',style0) # Starting (32) Row
ws.write(33,0,' ',style0) # Starting (33) Row
ws.write(33,1,' ',style0)
ws.write(33,2,' ',style0)
for i in range(0, 1):
ws.write_merge(i+33,i+34,7,7,'Pair Capacity',style0)
ws.write_merge(i+33, i+33, 8, 11, 'Length (Mtrs)',style0)
ws.write_merge(i+33, i+40, 12, 16, ' ',style0) # Starting (33) Row
for i in range(0, 1):
ws.write_merge(i+34,i+42,0,0,'0',style0) # Starting (34) Row
ws.write(34,1,'Emerald T/P',style2)
ws.write(34,2,' ',style2)
ws.write(34,3,'Size',style0)
ws.write(34,4,'Length',style2)
for i in range(0, 1):
ws.write_merge(i+34,i+40,5,6,'0',style2)
ws.write(34,8,'New pipe/CC',style2)
ws.write(34,9,'',style2)
ws.write(34,10,'',style2)
ws.write(34,11,' ',style2) # Starting (34) Row
# Starting (35) Row
ws.write(35,1,'Gamet',style2)
ws.write(35,2,' ',style2)
ws.write(35,3,'20 mm',style2)
ws.write(35,4,' ',style2)
ws.write(35,7,'1 Pair',style2)
ws.write(35,8,' ',style2)
ws.write(35,9,'',style2)
ws.write(35,10,' ',style2)
ws.write(35,11,' ',style2) # Starting (35) Row
# Starting (36) Row
ws.write(36,1,'others',style2)
ws.write(36,2,' ',style2)
ws.write(36,3,'25 mm',style2)
ws.write(36,4,' ',style2)
ws.write(36,7,'2 Pair',style2)
ws.write(36,8,' ',style2)
ws.write(36,9,'',style2)
ws.write(36,10,' ',style2)
ws.write(36,11,' ',style2) # Starting (36) Row
# Starting (37) Row
ws.write(37,1,'M71',style2)
ws.write(37,2,'0',style2)
ws.write(37,3,'32 mm',style2)
ws.write(37,4,' ',style2)
ws.write(37,7,'4 Pair',style2)
ws.write(37,8,' ',style2)
ws.write(37,9,'',style2)
ws.write(37,10,' ',style2)
ws.write(37,11,' ',style2) # Starting (37) Row
# Starting (38) Row
ws.write(38,1,'Sprit410',style2)
ws.write(38,2,'0',style2)
ws.write(38,3,' ',style2)
ws.write(38,4,' ',style2)
ws.write(38,7,'5 Pair',style2)
ws.write(38,8,' ',style2)
ws.write(38,9,'',style2)
ws.write(38,10,' ',style2)
ws.write(38,11,' ',style2) # Starting (38) Row
ws.write(39,1,' ',style2) # Starting (39) Row
ws.write(39,2,' ',style2)
ws.write(39,3,' ',style2)
ws.write(39,4,' ',style2)
ws.write(39,7,'10 Pair',style2)
ws.write(39,8,' ',style2)
ws.write(39,9,'',style2)
ws.write(39,10,' ',style2)
ws.write(39,11,' ',style2) # Starting (38) Row
for i in range(0, 1): # Starting (40) Row
ws.write_merge(i+40,i+40,1,2,'Own Instrument',style0)
ws.write(40,3,' ',style2)
ws.write(40,4,' ',style2)
ws.write(40,7,'20 Pair',style2)
ws.write(40,8,' ',style2)
ws.write(40,9,'',style2)
ws.write(40,10,' ',style2)
ws.write(40,11,' ',style2) # Starting (40) Row
ws.write(41,1,'Rosett Single ',style2) # Starting (41) Row
ws.write(41,2,'Rosett Two Way ',style2)
ws.write(41,3,'3/4 Way JB(Nos) ',style2)
ws.write(41,4,'Saddies(Nos)',style2)
ws.write(41,5,'3way router',style2)
ws.write(41,6,'J-nail',style2)
ws.write(41,7,'Bends(Nos.)',style2)
for i in range(0, 1):
ws.write_merge(i+41,i+41,8,9,'Corrugated Pipe(Mtrs)',style0)
ws.write(41,10,'RJ-11(nos) ',style2)
ws.write(41,11,'Ravel Plug ',style2)
for i in range(0, 1):
ws.write_merge(i+41,i+41,12,13,'Single DW Router',style0)
ws.write_merge(i+41,i+41,14,15,'DW Router',style0)
ws.write(41,16,'Splitter',style2) # Starting (41) Row
ws.write(42,1,'0',style2) # Starting (42) Row
ws.write(42,2,'',style2)
ws.write(42,3,'',style2)
ws.write(42,4,' ',style2)
ws.write(42,5,'0',style2)
ws.write(42,6,'0',style2)
ws.write(42,7,'',style2)
ws.write(42,8,'',style2)
ws.write(42,9,'',style2)
ws.write(42,10,'0',style2)
ws.write(42,11,'',style2)
for i in range(0, 1):
ws.write_merge(i+42,i+42,12,13,' ',style0)
ws.write_merge(i+42,i+42,14,15,' ',style0)
ws.write(42,16,'0',style2) # Starting (42) Row
for i in range(0, 1):
ws.write_merge(i+46,i+48,0,5,'The Work carried out by the contracter is as per specification and to my satisfation ',style0)
ws.write_merge(i+49,i+51,0,3,'Hasan Ali : PU0000777',style0)
ws.write_merge(i+49,i+51,8,10,'Contractors Signature',style0)
ws.write_merge(i+49,i+51,14,16,'Installation officer signature',style0)
#f1=open('/home/atharva/Desktop/test_report.xls','w')
f1=open('/home/atharva/Desktop/test_report.xls','w')
wb.save(f1)
pth='/home/atharva/Desktop/test_report.xls'
#shutil.copyfile('/home/atharva/Desktop/test_report.xls','/home/atharva/test_report2.xls')
#print_report(billing_sn)
#os.path.dirname(os.path.abspath(billing_sn))
print'Repor Genereted'
cursor.close()
conn1.close()
sys.exit()
report(8)
def report(billing_sn):
import os
import shutil
import sys
import string
import urllib
import xlwt
from xlwt import Workbook,Worksheet,Utils,Row,Style,UnicodeUtils,Formatting,ExcelMagic,CompoundDoc,Column,Cell,BIFFRecords,Bitmap,antlr
from datetime import datetime
import MySQLdb
import xml.dom.minidom
from xml.dom.minidom import parse,parseString
import datetime
from StringIO import StringIO
import cStringIO
dbhost="localhost"
dbuser=" "
dbpasswd=" "
dbname=" "
#print sno
#def print_report(billing_sn):
conn1 = MySQLdb.connect(host = dbhost, user = dbuser, passwd = dbpasswd, db = dbname)
cursor = conn1.cursor()
#billing_sn=int(billing_sn)
#sql = """select * from measurement_sheet ;"""
#sql = """select * from measurement_sheet where sno =""" billing_sn """ ;"""
billing_sno = {'billing_sn': billing_sn}
cursor.execute("select * from measurement_sheet where sno = %(billing_sn)s",billing_sno)
results = cursor.fetchall()
for row in results:
lsno=row[0]
tktnow=row[7]
dnno=row[4]
cfno=row[3]
spoint=row[1]
address=row[0]
officrName=row[44]
#cursor1=cursor.fetchone()
#cursor1=cursor.fetch_row()
#result=cursor1
#return result
#print sno
Fillno='' #value variable
#tktnow=72620
#lsno=row
franchname='Telematics Inc.'
#dnno=7314048300
#cfno=0
ak='AK'
#spoint='Spare Point'
#address='302 shriji towar,383-84 Khatiwala IND'
othval='SHIFTING'
hwtchnician='H W Technician'
#officrName=''
dt=datetime.datetime.now()
style0 = xlwt.easyxf('font:name times New Roman, color-index blue, bold on',num_format_str='#,##0.00')
style2 = xlwt.easyxf('font:name times New Roman, color-index black, bold on',num_format_str='#,##0.00')
style1 = xlwt.easyxf(num_format_str='D-MM-YY')
wb = xlwt.Workbook()
ws = wb.add_sheet('A test sheet')
for h in range(0, 1): # Starting First(1) Row ws.write_merge(h+0, h+0, 0, 16, 'Measurement Sheet for SA Work %d' % h, style0)
ws.write(1,0, 'Laying Sheet no.', style0)
for i in range(0, 1):
ws.write_merge(i+1, i+1, 1, 3, lsno)
for j in range(0, 1): ws.write_merge(j+1, j+1, 4, 7, 'Date of Filling up Laying sheet %d' % j, style0)
for k in range(0, 1): ws.write_merge(j+1, j+1, 8, 10,dt,style1)
ws.write(1,11, 'Filled By', style0)
for n in range(0, 1): ws.write_merge(n+1, n+1, 12, 13,Fillno)
ws.write(1,14, 'Tecket', style0)
for n in range(0, 1): ws.write_merge(n+1, n+1, 15, 16,tktnow) # End First Row
ws.write(2, 0, 'Franchisee', style0) # Starting Second(2) Row
for i in range(0, 1):
ws.write_merge(i+2, i+2, 1, 3, franchname)
for i in range(0, 1):
ws.write_merge(i+2, i+2, 4, 5, 'Node',style1)
for i in range(0, 1):
ws.write_merge(i+2, i+2, 6, 10, 'Zone3',style1)
ws.write(2, 11, 'Dn No', style0)
for i in range(0, 1):
ws.write_merge(i+2, i+2, 12, 13, dnno)
ws.write(2, 14, 'Caf.', style0)
for n in range(0, 1): ws.write_merge(n+2, n+2, 15, 16,cfno) # End Second Row
ws.write(3, 0, 'RSU', style0) # Starting Third(3) Row
for i in range(0, 1):
ws.write_merge(i+3, i+3, 1, 3, ak)
for i in range(0, 1):
ws.write_merge(i+3, i+5, 4, 5, 'Type of Connection(Plstick)', style0)
ws.write(3, 6, 'New', style0)
ws.write(3, 7, 'Shifting', style0)
ws.write(3, 8, 'Reconnection', style0)
for i in range(0, 1):
ws.write_merge(i+3, i+3, 9, 10, 'Others', style0)
ws.write(3, 11, 'New DP', style0)
ws.write(3, 12, 'Existing Dp', style0)
for i in range(0, 1):
ws.write_merge(i+3, i+3, 13, 15, 'Additional Case from New DP', style0)
ws.write(3, 16, 'Remarks', style0) # End Third Row
ws.write(4, 0, 'Customer Name', style0) # Starting Fourth(4) Row
for i in range(0, 1):
ws.write_merge(i+4, i+4, 1, 3, spoint)
for i in range(0, 1):
ws.write_merge(i+4, i+5, 9, 10, othval) # End Fourth Row
ws.write(5, 0, 'Installation Address', style0) # Starting Fifth(5) Row
for i in range(0, 1):
ws.write_merge(i+5, i+5, 1, 3, address) # End Fifth Row
ws.write(6, 0, 'Nearest N/W element(tick)', style0) # Starting Sixth(6) Row
ws.write(6, 1, 'MDF', style2)
ws.write(6, 2, 'Pillar', style2)
ws.write(6, 3, 'SP', style2)
ws.write(6, 4, 'DP', style2)
ws.write(6, 5, 'Pole', style2)
for i in range(0, 1):
ws.write_merge(i+6, i+7, 6, 6, hwtchnician)
for i in range(0, 1):
ws.write_merge(i+6, i+7, 7, 9, ' ', style2)
for i in range(0, 1):
ws.write_merge(i+6, i+7, 10, 12, 'Insl. Officer Name ', style2)
for i in range(0, 1):
ws.write_merge(i+6, i+7, 13, 16, officrName) # End Sixth Row
ws.write(7, 0, 'N/W Element NO.', style0) # Starting Sexenth(7) Row
for i in range(0, 1):
ws.write_merge(i+7, i+7, 1, 5, '') # End Sexenth(7) Row
ws.write(8, 0, 'Subscriber Refusal/Row Issue', style0) # Starting Eighth(8) Row
for i in range(0, 1):
ws.write_merge(i+8, i+8, 1, 6, '')
for i in range(0, 1):
ws.write_merge(i+8, i+8, 7, 8, 'Category',style0)
ws.write(8, 9, 'Key', style0)
for i in range(0, 1):
ws.write_merge(i+8, i+8, 10, 11, 'PCO/CCB',style0)
for i in range(0, 1):
ws.write_merge(i+8, i+8, 12, 13, 'SME',style0)
for i in range(0, 1):
ws.write_merge(i+8, i+8, 14, 16, 'Residential',style0) # End Eighth(8) Row
ws.write(9, 0, 'Deviations if any', style0) # Starting Nine(9) Row
ws.write(9, 1, '1', style2)
for i in range(0, 1):
ws.write_merge(i+9, i+9, 2, 5, '')
ws.write(9,6,'3',style2)
for i in range(0, 1):
ws.write_merge(i+9, i+9, 7, 10, '')
ws.write(9,11,'5',style2)
for i in range(0, 1):
ws.write_merge(i+9, i+9, 12, 16, '') # End Nine(9) Row
for i in range(0, 1): # Starting Tenth(10) Row
ws.write_merge(i+10, i+19, 0, 0, 'Digging Details',style0)
ws.write_merge(i+10,i+10,1,3,'Distance(Mtrs)',style0)
ws.write_merge(i+10,i+10,4,5,'Soil Strate',style0)
ws.write_merge(i+10,i+10,6,7,'Actual Lenght',style0)
ws.write_merge(i+10,i+10,8,9,'GI Pipe(Mtrs)',style0)
ws.write_merge(i+10,i+10,10,11,'Hope',style0)
ws.write(10,12,'PCC',style0)
for i in range(0,1):
ws.write_merge(i+10,i+10,13,16,'Digging details(soil/pcc/duct)',style0) # End Tenth(10) Row
ws.write(11,1,'From',style0) # Starting Eleventh(11) Row
ws.write(11,2,'To',style0)
ws.write(11,3,'Sub-Total',style0)
ws.write(11,4,'Normal',style0)
ws.write(11,5,'Soft',style0)
ws.write(11,6,'Normal',style0)
ws.write(11,7,'Soft',style0)
ws.write(11,8,'25mm',style2)
ws.write(11,9,'40mm',style2)
ws.write(11,10,'25mm',style2)
ws.write(11,11,'40mm',style2)
ws.write(11,12,' ',style2)
for i in range(0,1):
ws.write_merge(i+11,i+19,13,16,' ',style2) # Starting Eleventh(11) Row
ws.write(12,1,' ',style0) # Starting (12) Row
ws.write(12,2,' ',style0)
ws.write(12,3,' ',style0)
ws.write(12,4,' ',style0)
ws.write(12,5,' ',style0)
ws.write(12,6,'0',style2)
ws.write(12,7,'0',style2)
ws.write(12,8,' ',style2)
ws.write(12,9,'',style2)
ws.write(12,10,'',style2)
ws.write(12,11,'',style2)
ws.write(12,12,' ',style2) # End (12) Row
ws.write(13,1,' ',style0) # Starting (13) Row
ws.write(13,2,' ',style0)
ws.write(13,3,' ',style0)
ws.write(13,4,' ',style0)
ws.write(13,5,' ',style0)
ws.write(13,6,' ',style2)
ws.write(13,7,' ',style2)
ws.write(13,8,' ',style2)
ws.write(13,9,'',style2)
ws.write(13,10,'',style2)
ws.write(13,11,'',style2)
ws.write(13,12,' ',style2) # End (13) Row
ws.write(14,1,' ',style0) # Starting (14) Row
ws.write(14,2,' ',style0)
ws.write(14,3,' ',style0)
ws.write(14,4,' ',style0)
ws.write(14,5,' ',style0)
ws.write(14,6,' ',style2)
ws.write(14,7,' ',style2)
ws.write(14,8,' ',style2)
ws.write(14,9,'',style2)
ws.write(14,10,'',style2)
ws.write(14,11,'',style2)
ws.write(14,12,' ',style2) # End (14) Row
for i in range(0, 1): # Starting (20) Row
ws.write_merge(i+20, i+20, 0, 2, 'Cable Details',style0)
ws.write(20,3,'Length(Mtrs)',style0)
for i in range(0, 1):
ws.write_merge(i+20, i+20, 4, 6, ' ',style2)
for i in range(0, 1):
ws.write_merge(i+20, i+20, 7, 8, ' ',style2)
for i in range(0, 1):
ws.write_merge(i+20, i+24, 9, 9, ' ',style2)
for i in range(0, 1):
ws.write_merge(i+20, i+24, 10, 13, ' ',style2)
for i in range(0, 1):
ws.write_merge(i+20, i+24, 14, 16, '0',style2) # End (20) Row
for i in range(0, 1): # Starting (21) Row
ws.write_merge(i+21, i+21, 0, 2, 'Laying U/G',style0)
ws.write(21,3,' ',style2)
for i in range(0, 1):
ws.write_merge(i+21, i+21, 4, 6, ' ',style2)
ws.write_merge(i+21, i+21, 7, 8, ' ',style2) # Starting (21) Row
for i in range(0, 1): # Starting (22) Row
ws.write_merge(i+22, i+22, 0, 2, 'Pulling inside pipe',style0)
ws.write(22,3,' ',style2)
for i in range(0, 1):
ws.write_merge(i+22, i+22, 4, 6, ' ',style2)
ws.write_merge(i+22, i+22, 7, 8, ' ',style2) # Starting (22) Row
for i in range(0, 1): # Starting (23) Row
ws.write_merge(i+23, i+23, 0, 2, 'Pairage',style0)
ws.write(23,3,' ',style2)
for i in range(0, 1):
ws.write_merge(i+23, i+23, 4, 6, ' ',style2)
ws.write_merge(i+23, i+23, 7, 8, ' ',style2) # Starting (23) Row
ws.write(24,0,' ',style0) # Starting (24) Row
ws.write(24,1,' ',style0)
ws.write(24,2,' ',style0)
ws.write(24,3,' ',style0)
for i in range(0, 1):
ws.write_merge(i+24, i+24, 4, 8, ' ',style2) # Starting (24) Row
ws.write(25,0,'Jointing done if any',style0) # Starting (25) Row
for i in range(0, 1):
ws.write_merge(i+25, i+25, 1, 2, 'TSE 1(Nos) ',style0)
ws.write_merge(i+25, i+25, 3,4,'TSE(Nos)',style0)
ws.write_merge(i+25, i+25, 5,6,'Sleeves',style0)
ws.write_merge(i+25, i+26, 7,8,'Pairs termination(Nose)',style0)
ws.write(25,9,'DP',style0)
ws.write(25,10,'SP',style0)
ws.write(25,11,'Pillar',style0)
ws.write(25,12,'MDF',style0)
for i in range(0, 1):
ws.write_merge(i+25, i+26, 13, 16, 'Remarks',style0) # END (25) Row
ws.write(26,0,'Qty.',style0) # Starting (26) Row
for i in range(0, 1):
ws.write_merge(i+26, i+26, 1, 2, ' ',style2)
ws.write_merge(i+26, i+26, 3,4,' ',style2)
ws.write_merge(i+26, i+26, 5,6,' ',style2)
#ws.write_merge(i+25, i+26, 7,8,'Pairs termination(Nose)',style0)
ws.write(26,9,' ',style2)
ws.write(26,10,' ',style2)
ws.write(26,11,' ',style2)
ws.write(26,12,' ',style2) # END (26) Row
for i in range(0, 1): # Starting (27) Row
ws.write_merge(i+27, i+30, 0, 0, 'DP Installation',style0)
ws.write(27,1,'Capacity',style0)
ws.write(27,2,'Quantity',style0)
for i in range(0, 1):
ws.write_merge(i+27, i+28, 3, 5, 'GL Pipe A Class',style0)
ws.write_merge(i+27, i+28, 6, 8, 'HDPE',style0)
ws.write_merge(i+27, i+28, 9, 10, 'DP',style0)
ws.write_merge(i+27, i+27, 11, 12, '1. With Module',style0) # End (27) Row
for i in range(0, 1): # Begin (28) Row
ws.write_merge(i+28, i+30, 1, 1, ' ',style0)
ws.write_merge(i+28, i+30, 2, 2, ' ',style0)
ws.write_merge(i+28, i+28, 11, 12, '2. Without Module',style0) # End (28) Row
ws.write(29,3,'Size',style0) # Begin (29) Row
ws.write(29,4,'Length',style0)
ws.write(29,5,'Clamps',style0)
ws.write(29,6,'Size',style0)
ws.write(29,7,'Length',style0)
ws.write(29,8,'Clamps',style0)
ws.write(29,9,'Number',style0)
for i in range(0, 1):
ws.write_merge(i+29, i+29, 10, 12, ' ',style0) # End (29) Row
ws.write(30,3,' 25mm',style2) # Beging (30 Row
ws.write(30,4,' ',style0)
ws.write(30,5,' ',style0)
ws.write(30,6,' ',style2)
ws.write(30,7,' ',style0)
ws.write(30,8,' ',style0)
ws.write(30,9,'Number painting',style2)
for i in range(0, 1):
ws.write_merge(i+30, i+30, 10, 12, ' ',style0) # End (30) Row
ws.write(31,3,'40mm',style2) # Beging (31) Row
ws.write(31,4,' ',style2)
ws.write(31,5,' ',style2)
ws.write(31,6,' ',style2)
ws.write(31,7,' ',style2)
ws.write(31,8,' ',style2)
#ws.write(31,9,'',style2)
for i in range(0, 1):
ws.write_merge(i+31, i+31, 9, 11, 'DP Existing New ',style0) # End (31) Row
ws.write(32,0,' House Fitting',style0)
for i in range(0, 1): # Starting (32) Row
ws.write_merge(i+32, i+32, 1, 2, 'Instrument',style0)
ws.write_merge(i+32, i+33, 3, 4, 'PVT Pipe',style0)
ws.write_merge(i+32, i+33, 5, 6, 'Drop Wire',style0)
ws.write_merge(i+32, i+32, 7, 11, 'PVC Wire',style0)
ws.write_merge(i+32, i+32, 12, 16, 'Modem Details(Type/Ser. No.)',style0) # Starting (32) Row
ws.write(33,0,' ',style0) # Starting (33) Row
ws.write(33,1,' ',style0)
ws.write(33,2,' ',style0)
for i in range(0, 1):
ws.write_merge(i+33,i+34,7,7,'Pair Capacity',style0)
ws.write_merge(i+33, i+33, 8, 11, 'Length (Mtrs)',style0)
ws.write_merge(i+33, i+40, 12, 16, ' ',style0) # Starting (33) Row
for i in range(0, 1):
ws.write_merge(i+34,i+42,0,0,'0',style0) # Starting (34) Row
ws.write(34,1,'Emerald T/P',style2)
ws.write(34,2,' ',style2)
ws.write(34,3,'Size',style0)
ws.write(34,4,'Length',style2)
for i in range(0, 1):
ws.write_merge(i+34,i+40,5,6,'0',style2)
ws.write(34,8,'New pipe/CC',style2)
ws.write(34,9,'',style2)
ws.write(34,10,'',style2)
ws.write(34,11,' ',style2) # Starting (34) Row
# Starting (35) Row
ws.write(35,1,'Gamet',style2)
ws.write(35,2,' ',style2)
ws.write(35,3,'20 mm',style2)
ws.write(35,4,' ',style2)
ws.write(35,7,'1 Pair',style2)
ws.write(35,8,' ',style2)
ws.write(35,9,'',style2)
ws.write(35,10,' ',style2)
ws.write(35,11,' ',style2) # Starting (35) Row
# Starting (36) Row
ws.write(36,1,'others',style2)
ws.write(36,2,' ',style2)
ws.write(36,3,'25 mm',style2)
ws.write(36,4,' ',style2)
ws.write(36,7,'2 Pair',style2)
ws.write(36,8,' ',style2)
ws.write(36,9,'',style2)
ws.write(36,10,' ',style2)
ws.write(36,11,' ',style2) # Starting (36) Row
# Starting (37) Row
ws.write(37,1,'M71',style2)
ws.write(37,2,'0',style2)
ws.write(37,3,'32 mm',style2)
ws.write(37,4,' ',style2)
ws.write(37,7,'4 Pair',style2)
ws.write(37,8,' ',style2)
ws.write(37,9,'',style2)
ws.write(37,10,' ',style2)
ws.write(37,11,' ',style2) # Starting (37) Row
# Starting (38) Row
ws.write(38,1,'Sprit410',style2)
ws.write(38,2,'0',style2)
ws.write(38,3,' ',style2)
ws.write(38,4,' ',style2)
ws.write(38,7,'5 Pair',style2)
ws.write(38,8,' ',style2)
ws.write(38,9,'',style2)
ws.write(38,10,' ',style2)
ws.write(38,11,' ',style2) # Starting (38) Row
ws.write(39,1,' ',style2) # Starting (39) Row
ws.write(39,2,' ',style2)
ws.write(39,3,' ',style2)
ws.write(39,4,' ',style2)
ws.write(39,7,'10 Pair',style2)
ws.write(39,8,' ',style2)
ws.write(39,9,'',style2)
ws.write(39,10,' ',style2)
ws.write(39,11,' ',style2) # Starting (38) Row
for i in range(0, 1): # Starting (40) Row
ws.write_merge(i+40,i+40,1,2,'Own Instrument',style0)
ws.write(40,3,' ',style2)
ws.write(40,4,' ',style2)
ws.write(40,7,'20 Pair',style2)
ws.write(40,8,' ',style2)
ws.write(40,9,'',style2)
ws.write(40,10,' ',style2)
ws.write(40,11,' ',style2) # Starting (40) Row
ws.write(41,1,'Rosett Single ',style2) # Starting (41) Row
ws.write(41,2,'Rosett Two Way ',style2)
ws.write(41,3,'3/4 Way JB(Nos) ',style2)
ws.write(41,4,'Saddies(Nos)',style2)
ws.write(41,5,'3way router',style2)
ws.write(41,6,'J-nail',style2)
ws.write(41,7,'Bends(Nos.)',style2)
for i in range(0, 1):
ws.write_merge(i+41,i+41,8,9,'Corrugated Pipe(Mtrs)',style0)
ws.write(41,10,'RJ-11(nos) ',style2)
ws.write(41,11,'Ravel Plug ',style2)
for i in range(0, 1):
ws.write_merge(i+41,i+41,12,13,'Single DW Router',style0)
ws.write_merge(i+41,i+41,14,15,'DW Router',style0)
ws.write(41,16,'Splitter',style2) # Starting (41) Row
ws.write(42,1,'0',style2) # Starting (42) Row
ws.write(42,2,'',style2)
ws.write(42,3,'',style2)
ws.write(42,4,' ',style2)
ws.write(42,5,'0',style2)
ws.write(42,6,'0',style2)
ws.write(42,7,'',style2)
ws.write(42,8,'',style2)
ws.write(42,9,'',style2)
ws.write(42,10,'0',style2)
ws.write(42,11,'',style2)
for i in range(0, 1):
ws.write_merge(i+42,i+42,12,13,' ',style0)
ws.write_merge(i+42,i+42,14,15,' ',style0)
ws.write(42,16,'0',style2) # Starting (42) Row
for i in range(0, 1):
ws.write_merge(i+46,i+48,0,5,'The Work carried out by the contracter is as per specification and to my satisfation ',style0)
ws.write_merge(i+49,i+51,0,3,'Hasan Ali : PU0000777',style0)
ws.write_merge(i+49,i+51,8,10,'Contractors Signature',style0)
ws.write_merge(i+49,i+51,14,16,'Installation officer signature',style0)
#f1=open('/home/atharva/Desktop/test_report.xls','w')
f1=open('/home/atharva/Desktop/test_report.xls','w')
wb.save(f1)
pth='/home/atharva/Desktop/test_report.xls'
#shutil.copyfile('/home/atharva/Desktop/test_report.xls','/home/atharva/test_report2.xls')
#print_report(billing_sn)
#os.path.dirname(os.path.abspath(billing_sn))
print'Repor Genereted'
cursor.close()
conn1.close()
sys.exit()
report(8)
John Machin
Aug 3, 2010, 3:22:46 AM8/3/10
to python...@googlegroups.com
On 3/08/2010 4:48 PM, bijendra wrote:
> try to this complete. it may help
> try to this complete. it may help
[BIG SNIP]
Please consider uploading such a long script to the files area on the
google-group website, or including in your posting only the minimal
snippets necessary to communicate your point.
Reply all
Reply to author
Forward
0 new messages