Python excel2img

python 3.6
numpy 1.16.2
Pillow 5.4.1
pip 19.0.3
pypiwin32 223
pywin32 224
setuptools 40.6.2
xlrd 1.2.0

------ excel2img.py -----

# -*- coding: utf-8 -*-

import os
import sys
import win32com.client
from pythoncom import CoInitialize, CoUninitialize
from optparse import OptionParser
from pywintypes import com_error
from PIL import ImageGrab # Note: PIL >= 3.3.1 required to work well with Excel screenshots

class ExcelFile(object):
    @classmethod
    def open(cls, filename):
        obj = cls()
        obj._open(filename)
        return obj

    def __init__(self):
        self.app = None
        self.workbook = None

    def __enter__(self):
        return self

    def __exit__(self, *args):
        self.close()
        return False

    def _open(self, filename):
        excel_pathname = os.path.abspath(filename)  # excel requires abspath 获得文件当前路径
        if not os.path.exists(excel_pathname):
            raise IOError('No such excel file: %s', filename)

        CoInitialize()
        try:
            # Using DispatchEx to start new Excel instance, to not interfere with
            # one already possibly running on the desktop
            self.app = win32com.client.DispatchEx('Excel.Application')
            self.app.Visible = 0
        except:
            raise OSError('Failed to start Excel')

        try:
            self.workbook = self.app.Workbooks.Open(excel_pathname, ReadOnly=True)
        except:
            self.close()
            raise IOError('Failed to open %s'%filename)

    def close(self):
        if self.workbook is not None:
            self.workbook.Close(SaveChanges=False)
            self.workbook = None
        if self.app is not None:
            self.app.Visible = 0
            self.app.Quit()
            self.app = None
        CoUninitialize()


def export_img(file_path, fn_excel, fn_image, page=None, _range=None):
    """ Exports images from excel file """
    
    output_ext = os.path.splitext(fn_image)[1].upper() # 文件后缀
    # print("output_ext:"+output_ext)
    if output_ext not in ('.GIF', '.BMP', '.PNG'):
        raise ValueError('Unsupported image format: %s'%output_ext)

    # if both page and page-less range are specified, concatenate them into range
    if _range is not None and page is not None and '!' not in _range:
        _range = "'%s'!%s"%(page, _range)
    
    # print(file_path+fn_excel)
    with ExcelFile.open(file_path + fn_excel) as excel:
        if _range is None:
            if page is None: page = 1
            try:
                rng = excel.workbook.Sheets(page).UsedRange
            except com_error:
                raise Exception("Failed locating used cell range on page %s"%page)
            except AttributeError:
                # This might be a "chart page", try exporting it as a whole
                rng = excel.workbook.Sheets(page).Export(os.path.abspath(fn_image))
                return
            if str(rng) == "None":
                # No used cells on a page. maybe there's a single object.. try simply exporting as png
                shapes = excel.workbook.Sheets(page).Shapes
                if len(shapes) == 1:
                    rng = shapes[0]
                else:
                    raise Exception("Failed locating used cells or single object to print on page %s"%page)
        else:
            try:
                rng = excel.workbook.Application.Range(_range)
            except com_error:
                raise Exception("Failed locating range %s"%(_range))

        # excel.workbook.Activate() # Trying to solve intermittent CopyPicture failure (didn't work, only becomes worse)
        # rng.Parent.Activate()     # http://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-msoffice_custom/
        # rng.Select()              # cannot-use-the-rangecopypicture-method-to-copy-the/8bb3ef11-51c0-4fb1-9a8b-0d062bde582b?auth=1
        
        # See http://stackoverflow.com/a/42465354/1924207
        for shape in rng.parent.Shapes: pass

        xlScreen, xlPrinter = 1, 2
        xlPicture, xlBitmap = -4147, 2
        retries, success = 100, False
        while not success:
            try:
                rng.CopyPicture(xlScreen, xlBitmap)
                im = ImageGrab.grabclipboard()
                im.save(fn_image, fn_image[-3:])
                success = True
            except (com_error, AttributeError) as e:
                # http://stackoverflow.com/questions/24740062/copypicture-method-of-range-class-failed-sometimes
                # When other (big) Excel documents are open CopyPicture fails intermittently
                retries -= 1
                #print "CopyPicture failed, retries left:", retries
                if retries == 0: raise


if __name__ == '__main__':

    parser = OptionParser(usage='''%prog excel_filename image_filename [options]\nExamples:
            %prog test.xlsx test.png
            %prog test.xlsx test.png -p Sheet2
            %prog test.xlsx test.png -r MyNamedRange
            %prog test.xlsx test.png -r 'Sheet3!B5:C8'
            %prog test.xlsx test.png -r 'Sheet4!SheetScopedNamedRange' ''')
    parser.add_option('-p', '--page', help='pick a page (sheet) by page name. When not specified (and RANGE either not specified or doesn\'t imply a page), first page will be selected')
    parser.add_option('-r', '--range', metavar='RANGE', dest='_range', help='pick a range, in Excel notation. When not specified all used cells on a page will be selected')
    opts, args = parser.parse_args()

    if len(args) != 2:
        parser.print_help(sys.stderr)
        parser.exit()
    
    export_img(args[0], args[1], args[2], opts.page, opts._range)

------- test_excel2img.py --------

# coding: utf-8
import sys
import os

# Run in the tests directory
mypath = os.path.dirname(__file__)
os.chdir(mypath)

# file_path文件路径，file_name文件名，sheet_name指定sheet
# 图片路径为file_path + sheet_name + '.png'
def run_sheet(file_path, file_name, sheet_name, templateId):
    import excel2img
    fnout = file_path + sheet_name + "_" + templateId + ".png"
    if os.path.exists(fnout): os.unlink(fnout)
    try:
        excel2img.export_img(file_path, file_name, fnout, sheet_name)
        # print('java call python success')
    except OSError as e:
        if "Failed to start Excel" in str(e) and os.environ.get("PYTEST_SKIP_EXCEL"):
            # Waive Excel functionality on Travis
            return
        raise
    assert os.path.exists(fnout), fnout + " didn't get generated"
    print('java call python success') # 调用成功，生成图片

def test(strs):
    print ("hello," + strs);

def test_cells():
    run_sheet('Sheet1')

def test_single_chart():
    run_sheet('Sheet2')

def test_chart_sheet():
    run_sheet('Chart1')

def test_bad_extension():
    import excel2img
    try:
        excel2img.export_img("test.xlsx", "abc.xyz", "Sheet1", None)
    except ValueError as e:
        if 'Unsupported image format' in str(e): return # success
    assert 0, "ValueError('Unsupported image format .XYZ') should have been thrown"

if __name__ == '__main__':
  """
  print(sys.argv[0])
  for i in range(1, len(sys.argv)):
      strs = sys.argv[i]
      test(strs)
  """
  run_sheet(sys.argv[1], sys.argv[2], sys.argv[3], sys.argv[4]);
# run_sheet('D:\\','test.xlsx','Sheet1','templateId'); 

------- openExcel.py 打开excel并保存，解决启用编辑公式问题-----

# -*- coding: utf-8 -*-

import os
import sys
import win32com.client
from pythoncom import CoInitialize, CoUninitialize
from optparse import OptionParser
from pywintypes import com_error
from PIL import ImageGrab # Note: PIL >= 3.3.1 required to work well with Excel screenshots

class ExcelFile(object):
    @classmethod
    def open(cls, filename):
        obj = cls()
        obj._open(filename)
        return obj

    def __init__(self):
        self.app = None
        self.workbook = None

    def __enter__(self):
        return self

    def __exit__(self, *args):
        self.close()
        return False

    def _open(self, filename):
        excel_pathname = os.path.abspath(filename)  # excel requires abspath 获得文件当前路径
        if not os.path.exists(excel_pathname):
            raise IOError('No such excel file: %s', filename)

        CoInitialize()
        try:
            # Using DispatchEx to start new Excel instance, to not interfere with
            # one already possibly running on the desktop
            self.app = win32com.client.DispatchEx('Excel.Application')
            self.app.Visible = 0
        except:
            raise OSError('Failed to start Excel')

        try:
            self.workbook = self.app.Workbooks.Open(excel_pathname, ReadOnly=True)
        except:
            self.close()
            raise IOError('Failed to open %s'%filename)

    def close(self):
        if self.workbook is not None:
            self.workbook.Save()
            self.workbook.Close(SaveChanges=0)
            # self.workbook.Close(SaveChanges=False)
            self.workbook = None
        if self.app is not None:
            self.app.Visible = 0
            self.app.Quit()
            self.app = None
        CoUninitialize()


def export_img(file_path, fn_excel, page=None, _range=None):
    print(file_path+fn_excel)    
    # if both page and page-less range are specified, concatenate them into range
    if _range is not None and page is not None and '!' not in _range:
        _range = "'%s'!%s"%(page, _range)
    
    print(file_path+fn_excel)
    with ExcelFile.open(file_path + fn_excel) as excel:
        if _range is None:
            if page is None: page = 1
            try:
                rng = excel.workbook.Sheets(page).UsedRange
                print('java call python success open excel')
                # print(rng)
            except com_error:
                raise Exception("Failed locating used cell range on page %s"%page)
            except AttributeError:                
                return
            if str(rng) == "None":
                # No used cells on a page. maybe there's a single object.. try simply exporting as png
                shapes = excel.workbook.Sheets(page).Shapes
                if len(shapes) == 1:
                    rng = shapes[0]
                else:
                    raise Exception("Failed locating used cells or single object to print on page %s"%page)
        else:
            try:
                rng = excel.workbook.Application.Range(_range)
            except com_error:
                raise Exception("Failed locating range %s"%(_range))       


if __name__ == '__main__':
    # export_img('D:\\','xxx.xlsx',None,None);
    export_img(sys.argv[1], sys.argv[2], None, None)