Location>code7788 >text

Save a region of an Excel table as an image using python

Popularity：658 ℃/2024-08-04 17:10:58

In practice, we often save a certain area of the form (eg: A1: F5) or a graphic as a picture, how to use python to do this automatically? I do not know whether the partners in front of the screen have encountered similar needs, at the moment there is a drop of ideas in the mind.

python operation excel third-party libraries are many, each have their own tricks and specialize in application scenarios, a brief list:

  • pyexcel: pyexcel is a library for reading and writing Excel, CSV and other file formats, providing an easy-to-use interface.
  • pandas-ExcelWriter: This is a module of the Pandas library for writing data to Excel files.
  • DataNitro: DataNitro is an add-in that enables data processing and analysis using Python scripts in Excel.
  • pyxlsb: pyxlsb is a library for reading Excel binary files (xlsb format).
  • Pandas: Pandas is a powerful data processing library that can read, write and manipulate Excel files.
  • OpenPyXL: OpenPyXL is a library specialized for reading and writing Excel 2010 xlsx/xlsm/xltx/xltm files.
  • XlsxWriter: XlsxWriter is a library for writing Excel files that supports Excel 2007 xlsx file format.
  • xlrd / xlwt: These two libraries are used to read and write Excel files respectively, and support Excel 2003 before the xls file format.
  • pywin32 (): this library can explicitly (or implicitly) open excel for a column operation, the syntax is basically completely along the syntax of VBA, only a little pythonic transformation.

These libraries provide a number of functions and features, almost every aspect of table processing, you can choose the most suitable library to deal with Excel tables according to the project requirements. Specifically to our scenario today, the use of is the optimal solution, because it can be used in vba has been provided in the picture class API, such as - copy - selective paste - paste as a picture.

It is important to note that the following must be done in order for the region to transfer images successfully:

1. The current worksheet is in the selected state;

2. The current area must be at least partially visible to the naked eye;

3. The display ratio of the current form needs to default to 100%.

In order to avoid the picture after the screenshot being a pitch-black (colorless) background, it is recommended that the background color of the corresponding cell area be set to white or another color in advance.

With these prerequisites, you can use the cell area of the copyPicture () method to write the cell area data to the clipboard, and then with the help of the PIL library () method from the clipboard to get the data and save it as a picture.

Let's first look at the syntax of CopyPicture and how to set the parameters.

 

The full python sample code is below:

 1 from PIL import ImageGrab
 2 import  as win32
 3 from win32api import RGB
 4 
 5 def capture_sheet_range_to_picture(sheetFilePath,sheetName,sheetRange,imgFilePath):
 6     '''Take a screenshot of an area from a table in a particular excel and save it as an image
 7        sheetRange:str, e.g. "A1:E100"
 8     '''
 9     # Launching the Excel application
10     xlApp =  ('')
11     =True
12     # Open the target Excel file
13     workbook = (sheetFilePath)
14     # Select the specified worksheet
15     detailSheet = (sheetName)
16      = 2 #Pull the horizontal scrollbar to the top
17      = 2 
18      = 100 # Adjust the zoom of the table to display at 100%
19 
20     (sheetRange). = RGB(255,255,255) # true white
21     (0.3)
22     # Copy area as picture
23 
24     try:
25         (sheetRange).CopyPicture(1,2)
26         img = ()  # Get Image Data
28         (imgFilePath)
29     except Exception as e:
30         # If the screenshot fails, retry once
31         (0.3)
32         (sheetRange).CopyPicture(1,2)
33         img = ()  # Get Image Data
34         (imgFilePath)
35     # Close the workbook and exit Excel
36     (SaveChanges=False)
37     ()
38     return

This code [information] is very large, you do not believe, are a small climb I stepped on the pit over and over again to get the valuable experience of yo, I believe you have been inspired by a lot of inspiration, then try it, it will be full of harvest.

Come to follow this public number Get more knowledge of crawler, data analysis!

<Previous Next>