Re: Xlwt/Xwrd Excel formatting and print area

[Daniel França]

Some information I just forgot

Python version: 2.7

OS: MAC OSX 10.6       

2012/1/9 Daniel França <daniel...@gmail.com>

Hi all,

I'm trying to do the following using xlwt/xlrd/xlutils

Use an excel template as base to copy and create another excel file.

I need to keep formatting and print options in this new file.

After some research I find out how to keep the formatting... but some cell seems to lose these borders... I tried to manually set it, but it bizarre, I can set the border for a cell at left, for a cell at right, but the cell I need seems to not work at all.

here's some code:

        self.setCellBorder(ws, 157, 30, xlwt.Borders.THIN, xlwt.Borders.THIN )

        self.setCellBorder(ws, 157, 31, xlwt.Borders.THIN, xlwt.Borders.THIN )

        self.setCellBorder(ws, 157, 32, xlwt.Borders.THIN, xlwt.Borders.THIN )

        self.setCellBorder(ws, 157, 33, xlwt.Borders.THIN, xlwt.Borders.THIN )

        self.setCellBorder(ws, 156, 30, xlwt.Borders.THIN, xlwt.Borders.THIN )

        self.setCellBorder(ws, 156, 31, xlwt.Borders.THIN, xlwt.Borders.THIN )

        self.setCellBorder(ws, 156, 32, xlwt.Borders.THIN, xlwt.Borders.THIN )

        self.setCellBorder(ws, 156, 33, xlwt.Borders.THIN, xlwt.Borders.THIN )

        

        self.setCellBorder(ws, 158, 30, xlwt.Borders.THIN, xlwt.Borders.THIN )

        self.setCellBorder(ws, 158, 31, xlwt.Borders.THIN, xlwt.Borders.THIN )

        self.setCellBorder(ws, 158, 32, xlwt.Borders.THIN, xlwt.Borders.THIN )

        self.setCellBorder(ws, 158, 33, xlwt.Borders.THIN, xlwt.Borders.THIN )

the setCellBorder method:

    def setCellBorder(self,ws, col, row, top=None,bottom=None,left=None,right=None):

        borders = xlwt.Borders() 

        if top:

            borders.top = top

            borders.top_colour = 0x40

        if bottom:            

            borders.bottom = bottom

            borders.bottom_colour = 0x40

        if left:

            borders.left = left

            borders.left_colour = 0x40

        if right:

            borders.right = right

            borders.right_colour = 0x40                    

        

        style = xlwt.XFStyle()

        style.borders = borders

        self.setOutCell(ws, col, row, '', style)

And the setOutCell/getOutCell that I got from stackoverflow:

    def _getOutCell(self,outSheet, colIndex, rowIndex):

        """ HACK: Extract the internal xlwt cell representation. """

        row = outSheet._Worksheet__rows.get(rowIndex)

        if not row: return None

        cell = row._Row__cells.get(colIndex)

        return cell

    def setOutCell(self,outSheet, col, row, value, style=None):

        """ Change cell value without changing formatting. """

        # HACK to retain cell style.

        previousCell = self._getOutCell(outSheet, col, row)

        # END HACK, PART I

        if style == None:

            outSheet.write(row, col, value)

        else:

            outSheet.write(row, col, value, style)

        # HACK, PART II

        if previousCell:

            newCell = self._getOutCell(outSheet, col, row)

            if newCell:

                newCell.xf_idx = previousCell.xf_idx

        # END HACK

Some cells simply seems to not work to apply the border and I can figure out why =/

The another problem is about keep print formatting, I'd search at internet for these and it seems that it's not possible, right?

So I tried to set it manually:

        ws.paper_size_code = 9

        ws.portrait = 0        

        ws.fit_height_to_pages = 0

        ws.fit_width_to_pages = 0

        ws.fit_num_pages = 0

        ws.print_scaling = 91

        ws.print_centered_horz = 0

        ws.print_centered_vert = 1

First, I can't set the paper size to letter (1, according to BiffRecords.py, right?)

If I set it to 1 it simply shows blank the size code combobox in Excel.

Second, I need it to print in only 1 page, like the original excel file, but I can't figure out why it print to 2 pages(the second page it prints seems to be blank).

I tried to change the settings manually in Excel and the only way I could set it to 1 page is changing the print area...

Is there someway to change the print area using xlwt?

Or someway to find out why it's trying to print to 2 pages?

The other options seems to be fine.

Sorry the big first email =/ But I couldn't find these answer on internet and the python-excel.pdf doesn't say anything about printing =(.

Best Regards,

Daniel França

[John Machin]

If you have multiple problems, please post multiple questions.

I can't reproduce your paper size problem; after opening with Excel 2003 the file produced by the following script, the File/Page Setup/Page panel shows "Letter" in the Paper size pull-down list.

import xlwt
b = xlwt.Workbook()
s = b.add_sheet('x')
s.paper_size_code = 1
b.save('paper_size_code_1.xls')

Please show us the *minimal* script necessary to reproduce your problem.

[Payl0ad]

Sorry about my previous message... i jumped the gun... turned out it was a permissions issue... i had read write access only to myself and not for others.  Thanks again.