python-trying to save an Excel file as a picture and add a watermark

python-trying to save an Excel file as a picture and add a watermark

Scenario: Instead of generating a picture of an Excel chart, you generate a picture of the entire table content.

1. Preparatory work

Currently there is no way to search, you can only try to write one, there are two ideas:

1. Through the Python image processing Library PIL , the information in Excel is written to the picture;

2. By saving it as an HTML file, use Phantomjs the tool to intercept the HTML. A more tortuous approach, but it may be easier to get started.

I chose the latter, although each step is not very skilled, but first break it!

2. Excel to HTML2.1 attempt to save directly as HTML is unsuccessful

This step refers to [how does I save Excel Sheet as HTML in Python?
] Https://stackoverflow.com/questions/19631511/how-do-i-save-excel-sheet-as-html-in-python, the
needs to be edited in Excel first good one Code style= "FONT-SIZE:0.9EM; Font-family:consolas, Inconsolata, Courier, monospace; margin:0px 0.15em; padding:0px 0.3em; White-space:pre-wrap; Color: #c7254e; Display:inline "> macro , and then run the following code:

fromimport Dispatchxl = Dispatch(‘Excel.Application‘)xl.Workbooks.Open(‘C:\Foo\Bar.xlsx‘)#xl.Visible = True -- optionalxl.Application.Run("SaveHTML")xl.Workbooks.Close

Use the above code to error, the information is as follows:

com_error: (-2147352567, ‘发生意外。‘, (0, ‘Microsoft Excel‘, ‘无法运行“SaveHTML”宏。可能是因为该宏在此工作簿中不可用，或者所有的宏都被禁用。‘, ‘xlmain11.chm‘, 0, -2146827284), None)

I skipped it because I needed to generate a lot of Excel and I didn't think it would work for me.

2.2 Use Python to edit the ready-made HTML template, and then modify the information you need.

Need to use selenium3 , chromedriver and the corresponding version of Chrome. Https://www.cnblogs.com/technologylife/p/5829944.html
A short program:

 fromSeleniumImportWebdriver fromSelenium.webdriver.chrome.optionsImportoptionschrome_options = Options () chrome_options.add_argument ('--headless ')#无界面Chrome_options.add_argument ('--disable-gpu ') br = Webdriver. Chrome (chrome_options=chrome_options)#实例化浏览器Br.set_window_size (1024x768, the)#设置窗口大小Br.get (' f:/file.htm ') Br.get_screenshot_as_file ("F:\\file.png") Br.quit ()#退出很重要

3. Modify the HTML file

You need to open a html file locally, modify it, and then open it with your browser.

3.1 Encountering code transcoding issues

My HTML file encoding is ' gb2312 ', which is the default encoding for Excel2010 export. I open this file with Python always error!

UnicodeDecodeError: ‘ascii‘ codec can‘t decode byte 0xe5 in position 4: ordinal not in range

10 hours before and after the toss! has been decode encode! This is not the problem at all! The problem is:
do not have Chinese file names and paths! The
was startled by its own stupidity. Just do this, and then cooperate with this sentence:

 #打开  data = open ( Span class= "hljs-string" style= "color: #a31515" > "f://ktd.htm" , ). Read (). Decode ( "gb2312" , " Ignore ")  #保存  f = open ( ' f:/data.htm ' ,  ' W ' ) F.write (Data.encode ( "gb2312" , " ignore ")" F.close ()  

Basically, it's done.

3.2 Modifying Web pages with BeautifulSoup

Discard, even if there is a way to modify the page string, I also find it too difficult to find the element I need to modify, because it does not support XPath positioning. So I use the string directly in the replace way.

4. Control Google Chrome

Code

#-*-Coding:utf-8-*-"" " Created on Tue Jan 21:00:45 2018@author:xglc" "" fromSeleniumImportWebdriver fromSelenium.webdriver.chrome.optionsImportOptions fromPILImportimagechrome_options = Options ()#chrome_options. Add_argument ('--headless ') #无头#chrome_options. Add_argument ('--disable-gpu ')br = Webdriver. Chrome (chrome_options=chrome_options) br.set_window_size (950,830) def _pic(name):Br.get (' f:/zqht/%s.htm '%name)# Truncate the current window and specify where to save the picturePath =' F:/ktd/new/%s.png '% (name) br.get_screenshot_as_file (path) _sy (path)# br.quit () def _sy(path):LP = Image.open (PATH) TP = Image.open (' F:/work/zq/zqz.gif ')#水印# rgba_image = Lp.convert (' Rgba ')# Rgba_watermark = Tp.convert (' Rgba ')img = Tp.convert (' RGBA ') R, G, B, alpha = Img.split () Alpha = Alpha.point (LambdaI:i>0  and  -) Img.putalpha (Alpha) image_x, image_y = Lp.size watermark_x, watermark_y = tp.size# watermark Position mask is the channel value, 240+ is the transparency effectLp.paste (IMG, (image_x-watermark_x, image_y-watermark_y), mask = Alpha)Print(' Save picture ') Lp.save (' F:/lala.png ',"PNG")if__name__ = =' __main__ ': name =' BGS '_pic (name)

1. By adjusting the browser window to control the size, the effect is good. The current known flaw is that the memory usage problem is not well coordinated and may generate a picture with a alpha channel value of 240 and save resources.

2. Such a thing, sometimes save a Chinese path or picture can not be done. may be related to the previous codec.

?

python-trying to save an Excel file as a picture and add a watermark