New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Plans on Implementing Inserting Image in Footer? #133

Closed
NinjaMeTimbers opened this Issue Jun 9, 2014 · 25 comments

Comments

4 participants
@NinjaMeTimbers

In the documentation under the worksheet.insert_image() method, you mention that inserting an image into the worksheet footer is not supported. Are there any plans on implementing this? I know it would be useful, for example, for putting a company logo in the footer.

@jmcnamara jmcnamara self-assigned this Jun 9, 2014

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Jun 9, 2014

Owner

Images in the header/footer is becoming a Frequently Requested Feature. However, it isn't easily implemented.

Image positions in Excel are stored in a horrible pseudo XML format called VML. I've done this twice, for ordinary images and comment boxes and I'm not rushing to do it another time for header images.

However, I do appreciate that it would be a nice feature. I'll see if I can revisit it in the future.

I'll leave this open to see if it accumulates some +1s.

John

Owner

jmcnamara commented Jun 9, 2014

Images in the header/footer is becoming a Frequently Requested Feature. However, it isn't easily implemented.

Image positions in Excel are stored in a horrible pseudo XML format called VML. I've done this twice, for ordinary images and comment boxes and I'm not rushing to do it another time for header images.

However, I do appreciate that it would be a nice feature. I'll see if I can revisit it in the future.

I'll leave this open to see if it accumulates some +1s.

John

@georgelovas

This comment has been minimized.

Show comment
Hide comment
@georgelovas

georgelovas Sep 15, 2014

This library is pretty much useless without the ability to add an image to the header. If at least there were the ability to open a template file, then that could server as a suitable work around. Because of this limitation, have to revert back to openpyxl or some such.

How can this library be sold as a report automation tool, it one has to manually open the file and insert an image before it can be sent to the recipients?

This library is pretty much useless without the ability to add an image to the header. If at least there were the ability to open a template file, then that could server as a suitable work around. Because of this limitation, have to revert back to openpyxl or some such.

How can this library be sold as a report automation tool, it one has to manually open the file and insert an image before it can be sent to the recipients?

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Sep 15, 2014

Owner

Hi George,

This library is pretty much useless without the ability to add an image to the header

That is rather a unique response. I'll put it in the 👍 column, I suppose.

How can this library be sold as a report automation tool,

If someone sold you this module you should ask for your money back.

For the reasons stated in my comment above, this is a non-trivial task. Non-trivial mean it will take time and I don't have a lot of that at the moment. I'm also feeling the will to do it drain away for some reason.

Nevertheless, thanks for your input.

Regards,

John

Owner

jmcnamara commented Sep 15, 2014

Hi George,

This library is pretty much useless without the ability to add an image to the header

That is rather a unique response. I'll put it in the 👍 column, I suppose.

How can this library be sold as a report automation tool,

If someone sold you this module you should ask for your money back.

For the reasons stated in my comment above, this is a non-trivial task. Non-trivial mean it will take time and I don't have a lot of that at the moment. I'm also feeling the will to do it drain away for some reason.

Nevertheless, thanks for your input.

Regards,

John

@georgelovas

This comment has been minimized.

Show comment
Hide comment
@georgelovas

georgelovas Sep 16, 2014

Hi John,

Sorry, didn't mean to be critical, its just that I invested a lot of time
building this report engine only to find this limitation at the last
minute, I suppose that I can finish it off with a c# process to add the
last bit. I also noticed that when adding a footer, the font and other
control information are included in the character count. Thus a footer
that fits when entered manually exceeds the character limit when font and
other info are included. Is there a work around for this?

Overall, its a great tool and thanks for the effort.

Best,
George

George Lovas
Quantum Solutions Plus, LLC

Hi John,

Sorry, didn't mean to be critical, its just that I invested a lot of time
building this report engine only to find this limitation at the last
minute, I suppose that I can finish it off with a c# process to add the
last bit. I also noticed that when adding a footer, the font and other
control information are included in the character count. Thus a footer
that fits when entered manually exceeds the character limit when font and
other info are included. Is there a work around for this?

Overall, its a great tool and thanks for the effort.

Best,
George

George Lovas
Quantum Solutions Plus, LLC

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Sep 16, 2014

Owner

Hi George,

Sorry, didn't mean to be critical, its just that I invested a lot of time
building this report engine only to find this limitation at the last
minute,

If you had started with that I would have been a lot more sympathetic.

As it stands I do appreciate your frustration. However, this really isn't an easy feature to implement. If it was I would have added it a long time ago.

Nevertheless, I'll look at it again and see if I can get anything working quickly. I'll update the thread here if I do and you'll get a ping.

I also noticed that when adding a footer, the font and other
control information are included in the character count. Thus a footer
that fits when entered manually exceeds the character limit when font and
other info are included. Is there a work around for this

That sounds like a bug. I'll look into that too.

Regards,

John

Owner

jmcnamara commented Sep 16, 2014

Hi George,

Sorry, didn't mean to be critical, its just that I invested a lot of time
building this report engine only to find this limitation at the last
minute,

If you had started with that I would have been a lot more sympathetic.

As it stands I do appreciate your frustration. However, this really isn't an easy feature to implement. If it was I would have added it a long time ago.

Nevertheless, I'll look at it again and see if I can get anything working quickly. I'll update the thread here if I do and you'll get a ping.

I also noticed that when adding a footer, the font and other
control information are included in the character count. Thus a footer
that fits when entered manually exceeds the character limit when font and
other info are included. Is there a work around for this

That sounds like a bug. I'll look into that too.

Regards,

John

@georgelovas

This comment has been minimized.

Show comment
Hide comment
@georgelovas

georgelovas Sep 17, 2014

Hi John,

That would be great and much appreciated. Apologies again, I was really
frustrated thinking I would have to abandon weeks worth of work. I am
generating the excel files on a linux server, so don't have access to .Net
framework.

I have been able to do everything quite well with exception of the logo.

Best,
George

George Lovas
Quantum Solutions Plus, LLC

Hi John,

That would be great and much appreciated. Apologies again, I was really
frustrated thinking I would have to abandon weeks worth of work. I am
generating the excel files on a linux server, so don't have access to .Net
framework.

I have been able to do everything quite well with exception of the logo.

Best,
George

George Lovas
Quantum Solutions Plus, LLC

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Sep 30, 2014

Owner

@georgelovas

I also noticed that when adding a footer, the font and other
control information are included in the character count. Thus a footer
that fits when entered manually exceeds the character limit when font and
other info are included.

I had a look at this and it seems to be the way that Excel handles it as well, i.e, it includes the control characters in the character count.

If you think otherwise and can provide an example or could you open a new issue for it?

Thanks,

John

Owner

jmcnamara commented Sep 30, 2014

@georgelovas

I also noticed that when adding a footer, the font and other
control information are included in the character count. Thus a footer
that fits when entered manually exceeds the character limit when font and
other info are included.

I had a look at this and it seems to be the way that Excel handles it as well, i.e, it includes the control characters in the character count.

If you think otherwise and can provide an example or could you open a new issue for it?

Thanks,

John

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Oct 5, 2014

Owner

@NinjaMeTimbers @georgelovas

I've pushed initial, but working, support for images in headers and footers to the master branch.

See the updated header/footer example program in the repo.

The set_header() and set_footer() methods has been modified to add a options dict which the following keys:

  • image_left
  • image_center
  • image_right

The syntax looks like the following with &G as a placeholder for the image:

worksheet2.set_header('&L&G', None,
                      {'image_left': 'python-200x80.png'})

Which gives something like this:

screenshot

This is preliminary work and I'll be adding docs and additional validation to the API to ensure that the placeholders and images match. Also, I'll be adding in memory image support and a few additional header/footer options.

If you'd care to try it please do and let me know me know your feedback.

John

Owner

jmcnamara commented Oct 5, 2014

@NinjaMeTimbers @georgelovas

I've pushed initial, but working, support for images in headers and footers to the master branch.

See the updated header/footer example program in the repo.

The set_header() and set_footer() methods has been modified to add a options dict which the following keys:

  • image_left
  • image_center
  • image_right

The syntax looks like the following with &G as a placeholder for the image:

worksheet2.set_header('&L&G', None,
                      {'image_left': 'python-200x80.png'})

Which gives something like this:

screenshot

This is preliminary work and I'll be adding docs and additional validation to the API to ensure that the placeholders and images match. Also, I'll be adding in memory image support and a few additional header/footer options.

If you'd care to try it please do and let me know me know your feedback.

John

@NinjaMeTimbers

This comment has been minimized.

Show comment
Hide comment
@NinjaMeTimbers

NinjaMeTimbers Oct 6, 2014

I managed to get it to work, but not without running into a couple issues. First, trying to use a .tif file gave me an error saying that the file type wasn't supported. I converted the file to a .png, and it worked. However, on the print preview screen once the file is opened in excel, the image is a fair bit larger than it is supposed to be (I would estimate it to be ~3 times larger). Replacing the image with the same file through excel brings it back down to the correct size. I shrunk the image to be three times smaller, and it looks fine, though when I replace the image with the same file through excel, it is way too small.

I managed to get it to work, but not without running into a couple issues. First, trying to use a .tif file gave me an error saying that the file type wasn't supported. I converted the file to a .png, and it worked. However, on the print preview screen once the file is opened in excel, the image is a fair bit larger than it is supposed to be (I would estimate it to be ~3 times larger). Replacing the image with the same file through excel brings it back down to the correct size. I shrunk the image to be three times smaller, and it looks fine, though when I replace the image with the same file through excel, it is way too small.

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Oct 6, 2014

Owner

Hi @NinjaMeTimbers

Thanks for the feedback.

  1. TIFF isn't supported. The same rules apply as other images in Module
  2. Do you see the same 3x if you convert to the Tiff to JPEG.
  3. Could you post the PNG or email it to me.

John

Owner

jmcnamara commented Oct 6, 2014

Hi @NinjaMeTimbers

Thanks for the feedback.

  1. TIFF isn't supported. The same rules apply as other images in Module
  2. Do you see the same 3x if you convert to the Tiff to JPEG.
  3. Could you post the PNG or email it to me.

John

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Oct 6, 2014

Owner

Also, which version of excel are you using?

Owner

jmcnamara commented Oct 6, 2014

Also, which version of excel are you using?

@NinjaMeTimbers

This comment has been minimized.

Show comment
Hide comment
@NinjaMeTimbers

NinjaMeTimbers Oct 6, 2014

The JPEG shows up the same ~x3 size as the PNG. I will email you the PNG in a second.

The JPEG shows up the same ~x3 size as the PNG. I will email you the PNG in a second.

@NinjaMeTimbers

This comment has been minimized.

Show comment
Hide comment
@NinjaMeTimbers

NinjaMeTimbers Oct 6, 2014

I am using the Office 365 version of Excel.

I am using the Office 365 version of Excel.

@NinjaMeTimbers

This comment has been minimized.

Show comment
Hide comment
@NinjaMeTimbers

NinjaMeTimbers Oct 6, 2014

Oops, I pressed close and comment rather than comment...

Oops, I pressed close and comment rather than comment...

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Oct 6, 2014

Owner

Hi @NinjaMeTimbers,

It looks like the difference is due to the DPI of the image. The images that I used to write the test cases were all 96 dpi while your image is 300 dpi (300/96 = ~x3).

Which means that I'm going to have to parse out the horizontal and vertical DPI form the image before setting the dimensions in the Excel files. Arrrrrgh!!

You could reduce the image DPI to 96 as a workaround while I work on a proper fix.

John

Owner

jmcnamara commented Oct 6, 2014

Hi @NinjaMeTimbers,

It looks like the difference is due to the DPI of the image. The images that I used to write the test cases were all 96 dpi while your image is 300 dpi (300/96 = ~x3).

Which means that I'm going to have to parse out the horizontal and vertical DPI form the image before setting the dimensions in the Excel files. Arrrrrgh!!

You could reduce the image DPI to 96 as a workaround while I work on a proper fix.

John

@NinjaMeTimbers

This comment has been minimized.

Show comment
Hide comment
@NinjaMeTimbers

NinjaMeTimbers Oct 6, 2014

That worked for now. I will update my code once you release a proper fix.

Thanks for everything you've done with XlsxWriter, it's come in extremely useful, especially with the stuff that xlrd couldn't do like some of the page formatting. I understand how much work must have gone into this project.

That worked for now. I will update my code once you release a proper fix.

Thanks for everything you've done with XlsxWriter, it's come in extremely useful, especially with the stuff that xlrd couldn't do like some of the page formatting. I understand how much work must have gone into this project.

@georgelovas

This comment has been minimized.

Show comment
Hide comment
@georgelovas

georgelovas Oct 7, 2014

Hi John,

Thanks for looking into this. You're right, I was basing the issue on not
being able to use the same text as then entered into Excel, but not setting
a font. The same error occurs when I try to add a font to the footer in
Excel.

Thanks,
George

George Lovas
Quantum Solutions Plus, LLC

Hi John,

Thanks for looking into this. You're right, I was basing the issue on not
being able to use the same text as then entered into Excel, but not setting
a font. The same error occurs when I try to add a font to the footer in
Excel.

Thanks,
George

George Lovas
Quantum Solutions Plus, LLC

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Oct 12, 2014

Owner

@NinjaMeTimbers

I've added support for different image resolutions in headers/footers. I've added several testcases as well but if you'd like to test it with your logo, please do.

This was interesting to say the least. I first had to figure out Excel's resolution handling and then add it to the module. This had the beneficial side effect of fixing resolution support in worksheet images. However, just to make things even more interesting header and worksheet images handle resolutions differently.

There is still some work to do before this is suitable for PyPI but try it out if you get a chance.

Owner

jmcnamara commented Oct 12, 2014

@NinjaMeTimbers

I've added support for different image resolutions in headers/footers. I've added several testcases as well but if you'd like to test it with your logo, please do.

This was interesting to say the least. I first had to figure out Excel's resolution handling and then add it to the module. This had the beneficial side effect of fixing resolution support in worksheet images. However, just to make things even more interesting header and worksheet images handle resolutions differently.

There is still some work to do before this is suitable for PyPI but try it out if you get a chance.

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Oct 13, 2014

Owner

I've changed the interface for set_header() and set_footer() so that the margin parameter is now part of the options.

worksheet.set_header('&L&G', {'image_left': 'logo.jpg'})

The old API for margin is still supported but won't be documented at release.

This feature should be integrated into the 0.6.0 release in the next day so this is your last chance to test it.

Owner

jmcnamara commented Oct 13, 2014

I've changed the interface for set_header() and set_footer() so that the margin parameter is now part of the options.

worksheet.set_header('&L&G', {'image_left': 'logo.jpg'})

The old API for margin is still supported but won't be documented at release.

This feature should be integrated into the 0.6.0 release in the next day so this is your last chance to test it.

@georgelovas

This comment has been minimized.

Show comment
Hide comment
@georgelovas

georgelovas Oct 13, 2014

Hi John,

I've been tied up on other stuff, but am testing now. Will let you know if
any issues.

Thanks,
George

George Lovas
Quantum Solutions Plus, LLC

Hi John,

I've been tied up on other stuff, but am testing now. Will let you know if
any issues.

Thanks,
George

George Lovas
Quantum Solutions Plus, LLC

@georgelovas

This comment has been minimized.

Show comment
Hide comment
@georgelovas

georgelovas Oct 14, 2014

Hi John,

Just tested it and it works great. Thanks for doing this so quickly. I've
written a metadata driven report generator around your tool and this was
the missing piece. Basically, the end user can define the data they want,
e.g., titles, footnotes, headers and footers, charts, etc and the program
generates the excel file. There is of course the numerical analysis
component that resides in python, but lets the end user control the
appearance and placement of the data.

You've done a great job with this and I have been telling my colleagues
what a great tool it is.

Keep in touch.

Best regards,
George

George Lovas
Quantum Solutions Plus, LLC
917-573-3079 office
917-589-5659 mobile
203-594-1999 fax

On Mon, Oct 13, 2014 at 5:59 PM, George Lovas <
georgelovas@quantumsolutionsplus.com> wrote:

Hi John,

I've been tied up on other stuff, but am testing now. Will let you know
if any issues.

Thanks,
George

George Lovas
Quantum Solutions Plus, LLC
917-573-3079 office
917-589-5659 mobile
203-594-1999 fax

On Mon, Oct 13, 2014 at 5:51 PM, John McNamara notifications@github.com
wrote:

I've changes the interface for set_header() and set_footer() so that the
margin parameter is now part of the options.

worksheet.set_header('&L&G', {'image_left': 'logo.jpg'})

The old API for margin is still supported but won't be documented at
release.

This feature should be integrated into the 0.6.0 release in the next day
so this is your last chance to test it.


Reply to this email directly or view it on GitHub
#133 (comment)
.

Hi John,

Just tested it and it works great. Thanks for doing this so quickly. I've
written a metadata driven report generator around your tool and this was
the missing piece. Basically, the end user can define the data they want,
e.g., titles, footnotes, headers and footers, charts, etc and the program
generates the excel file. There is of course the numerical analysis
component that resides in python, but lets the end user control the
appearance and placement of the data.

You've done a great job with this and I have been telling my colleagues
what a great tool it is.

Keep in touch.

Best regards,
George

George Lovas
Quantum Solutions Plus, LLC
917-573-3079 office
917-589-5659 mobile
203-594-1999 fax

On Mon, Oct 13, 2014 at 5:59 PM, George Lovas <
georgelovas@quantumsolutionsplus.com> wrote:

Hi John,

I've been tied up on other stuff, but am testing now. Will let you know
if any issues.

Thanks,
George

George Lovas
Quantum Solutions Plus, LLC
917-573-3079 office
917-589-5659 mobile
203-594-1999 fax

On Mon, Oct 13, 2014 at 5:51 PM, John McNamara notifications@github.com
wrote:

I've changes the interface for set_header() and set_footer() so that the
margin parameter is now part of the options.

worksheet.set_header('&L&G', {'image_left': 'logo.jpg'})

The old API for margin is still supported but won't be documented at
release.

This feature should be integrated into the 0.6.0 release in the next day
so this is your last chance to test it.


Reply to this email directly or view it on GitHub
#133 (comment)
.

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara Oct 14, 2014

Owner

Fixed in version 0.6.0 on PyPI.

Make sure to upgrade to that version since there were a few small bugs in the working versions prior to the release.

If there are any issues with this feature then open a new issue tracker. This one will be closed.

Thanks for the feedback and testing.

John

Owner

jmcnamara commented Oct 14, 2014

Fixed in version 0.6.0 on PyPI.

Make sure to upgrade to that version since there were a few small bugs in the working versions prior to the release.

If there are any issues with this feature then open a new issue tracker. This one will be closed.

Thanks for the feedback and testing.

John

@jmcnamara jmcnamara closed this Oct 14, 2014

@NinjaMeTimbers

This comment has been minimized.

Show comment
Hide comment
@NinjaMeTimbers

NinjaMeTimbers Nov 25, 2014

Sorry it's taken me so long to respond, I've been swamped with other things at work lately. I just tested out the updated version and it works great! Thanks for all you've done on this project.

Sorry it's taken me so long to respond, I've been swamped with other things at work lately. I just tested out the updated version and it works great! Thanks for all you've done on this project.

@mumtazs

This comment has been minimized.

Show comment
Hide comment
@mumtazs

mumtazs May 28, 2015

Hi John,

I have tried to run your example to put logo in the header of the excel sheet but this is not working and showing an error on opening .xlsx file to recover the file ignoring some mismatch of header and file format.

Regards,
Shaz

mumtazs commented May 28, 2015

Hi John,

I have tried to run your example to put logo in the header of the excel sheet but this is not working and showing an error on opening .xlsx file to recover the file ignoring some mismatch of header and file format.

Regards,
Shaz

@jmcnamara

This comment has been minimized.

Show comment
Hide comment
@jmcnamara

jmcnamara May 28, 2015

Owner

Hi @mumtazs,

This Feature Request issue is closed.

If you have a bug to report you should open a new issue and add a small working example that demonstrates the issue as shown in the Reporting Bugs section of the docs. In this case you will need to attach the image as well.

John

Owner

jmcnamara commented May 28, 2015

Hi @mumtazs,

This Feature Request issue is closed.

If you have a bug to report you should open a new issue and add a small working example that demonstrates the issue as shown in the Reporting Bugs section of the docs. In this case you will need to attach the image as well.

John

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment