Get work sheet by name in Excel Object Model automation interface

Question

Is there a way to use XlsxWriter to get a worksheet by name?

import win32com.client, types, pythoncom, sys, os, string
import xlsxwriter
xlApp = win32com.client.Dispatch("Excel.Application")
for file in os.listdir("C:\Users\\Desktop\Escel"):
    if file.endswith(".xlsx"):
        fileName = file
        books = xlApp.Workbooks.Open(r"C:\\Users\\Desktop\\Escel\\" + str(fileName))
        ws = books.sheet_by_name("2015 Data")
        #ws = books.Worksheets[0]
        ws.Visible = 1
        fileName.replace(".xlsx","")  
        ws.ExportAsFixedFormat(0, r"C:\\Users\\Desktop\\PDF\\" + str(fileName) + str(".pdf"))
        books.Close(True, r"C:\\Users\\Desktop\\Escel\\" + str(fileName))

AFAIK, you cannot read Excel worksheets at all with xlsxwriter, just create them. — RBarryYoung, Jul 27 '15 at 18:23
Using Python, does [this question](http://stackoverflow.com/questions/12250024/how-to-obtain-sheet-names-from-xls-files-without-loading-the-whole-file-in-pytho) help you get the worksheet name? — BruceWayne, Jul 27 '15 at 18:31
@RBarryYoung ws = books.Worksheets[0] seems to do that though. I guess that's as far as you can go with xlsxwriter. — user3078335, Jul 27 '15 at 18:35
AFAIK, that's not xlsxwriter at all, but rather the Excel Object Model automation interface. — RBarryYoung, Jul 27 '15 at 18:40
Oh okay. Sorry. All this is kinda new to me. Is there a way to get a worksheet by it's name using Excel Object Model automation interface then @RBarryYoung — user3078335, Jul 27 '15 at 18:43
Guessing it would be something like this: ws= books.Worksheets('2015 data') — user3078335, Jul 27 '15 at 18:46
I get this error though. 'AttributeError: 'NoneType' object has no attribute 'Worksheets' Any suggestion? You can respond as an answer so I vote. Thanks for your help. @RBarryYoung — user3078335, Jul 27 '15 at 19:02
Nope, sorry, no idea what's going on. `books.Worksheets("2015 data")` would work in VBA, so I have to assume it's some Python limitation, or whatever your using isn't the full Excel Object Model interface. — RBarryYoung, Jul 27 '15 at 19:12

score 5 · Answer 1 · answered May 20 '16 at 15:25

5

XlsxWriter 0.8.7 now comes with a way to do that. For example:

worksheet = workbook.get_worksheet_by_name("Sheet1")

See: http://xlsxwriter.readthedocs.io/workbook.html#get_worksheet_by_name

answered May 20 '16 at 15:25

Chris Nielsen

839
1
16
31

raysabr · Answer 2 · 2015-12-22T14:04:21.407

Suppose, for example, that you want to add a chart or formatting to the worksheet created by Pandas. You might then need to access the sheet even when using XlsxWriter. Admittedly this does not open an existing file but instead finds the sheet in the workbook you are creating. Here's how you might do this:

import numpy as np
import pandas as pd
from xlsxwriter.utility import xl_range

writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
workbook = writer.book

df = pd.DataFrame({'Data': np.random.rand(255)})
df.to_excel(writer, 'TEST')
test_sheet = writer.sheets['TEST']

chart = workbook.add_chart({'type': 'scatter'})
chart.add_series(
    {
        'name': 'Rand',
        'marker': {
            'type': 'circle',
            'size': 2,
            'fill': {'color': '#008000'},
            'border': {'none': True},
        },
        'line': {'none': True},
        'categories': "=TEST!{}".format(xl_range(1, 0, 255, 0)),
        'values': "=TEST!{}".format(xl_range(1, 1, 255, 1))
    }
)
test_sheet.insert_chart('C1', chart)        
writer.close()

Get work sheet by name in Excel Object Model automation interface

2 Answers2