EzDevInfo.com

Xlsxwriter can't set border width

I'm trying to set the cell's border width in an Excel file using XlsxWriter, but I could not find a call for it in the API. I'm looking for a solution similar to using this menu:

Making custom classes xlsxwriter friendly

I'm writing some data analysis pipelines in pandas. One of the columns in the dataframes that I've been working with is made up of objects of custom-written classes that are each initialized with a string, from which I read off various information with regular expressions and store in the object's attributes. The subclass structure is similar to how one might implement a tree of life (e.g. a Tiger is a sublass of Cat which is a subclass of Animal and frequently -- but not always -- animals with the same superclass will share methods). It also has some useful methods that I can use for calculations. For str and repr methods return the string that was used to initialize it, like so:

class Animal(object):

    def __init__(self, name):
        self.name = name
        self.group = self.__class__.__name__

    def __repr__(self):
        return self.name.__repr__()

    def __str__(self):
        return self.name.__str__()

I'm using this bit of code to write the a dictionary of data frames to an excel spreadsheet:

        with pd.ExcelWriter(saveas) as writer:
            for key, val in dictionary.items():
                print key
                write_index = not val.data_frame.index.is_integer()
                val.to_excel(writer, sheet_name=key, index=write_index)
            writer.save()

This means that when I want to view my data frame, I see a string. I have had no problems using the to_csv() method on data frames that have these objects in them, but when I use the to_excel() method of the pandas data frame, I get the following error:

  File "C:\Users\Mike\Anaconda\lib\site-packages\xlsxwriter\worksheet.py", line 406, in write
    f = float(token)

TypeError: float() argument must be a string or a number

So I tracked this down to the code in worksheet.py, and the offending lines look like this:

    try:
        f = float(token) ##THIS IS WHERE THE CODE FAILS
        if not self._isnan(f) and not self._isinf(f):
            return self.write_number(row, col, f, *args[1:])
    except ValueError:
        pass

    # Finally try string.
    try:
        str(token)
        return self.write_string(row, col, *args)
    except ValueError:
        raise TypeError("Unsupported type %s in write()" % type(token))

I've added a comment to the code above to show where the failure happens. My objects don't have float methods, so instead of a ValueError they're throwing TypeErrors. From the code above, it's clear that if we can pass through to the second try statement that the writing will commence (because my class has a str method). So I gave my custom class a float method, which returned a ValueError so that the except clause could be triggered.

However, there were more problems down the line with xlsx writer, a few similar ones pertaining to the lack of certain methods in my class (which I added in each case). However, this only delays the problems:

  File "C:\Users\Mike\Anaconda\lib\site-packages\xlsxwriter\sharedstrings.py", line 95, in _write_si
    string = re.sub('(_x[0-9a-fA-F]{4}_)', r'_x005F\1', string)

Now, the issue here (I looked at the code) is that, once the strings have been written to the sheets, xlsxwriter runs some function that gets all of the strings from the written file. The issue seems to be that once the sheets have been written (everything up to writer.save() passed without error), xlsxwriter assumes that the strings that got written were strings all along, and treats them like it, rather than encasing them in str() functions like they did before.

Now, I could go modify the offending code, but I don't want to have to deal with making that play nicely with updates to xlsxwriter. I could simply make my class inherit from str, but that seems like unpythonic given that I don't really want to use almost any of the string methods. Lastly, I could sanitize my dataframe by taking everything in it that's of this subclass and turning it back into a string, but that would mean I have to rewrite a lot of things that I use that depend on being able to use the DataFrame.to_excel method. Is there anything I can do within the class that saves me from having to inherit everything from str?

xlsxwriter module won't open/close Excel file correctly

I'm writing a program that writes data to an Excel file using the xlsxwriter module.

The code that opens the workbook is:

excel = xlsxwriter.Workbook('stock.xlsx')

This used to work. Then I changed some stuff around in the bottom of the program (waaaaay after that line) and now it doesn't work, saying this:

Exception ignored in: <bound method Workbook.__del__ of <xlsxwriter.workbook.Workbook object at 0x02C702B0>>
Traceback (most recent call last):
  File "c:\python34\lib\site-packages\xlsxwriter\workbook.py", line 147, in __del__
    raise Exception("Exception caught in workbook destructor. "
Exception: Exception caught in workbook destructor. Explicit close() may be required for workbook.

This has happened when I forget to close the file before running it again (as it is trying to write to a file that's open in Excel which won't work), but I don't even have Excel open and it does this.

How can I fix this? Do I need to restart or something?

Also, I tried to have a try...except loop to stop the program if the initialization doesn't work. Even with except: only, without a specific exception, it still completes the program unless I kill it manually. The script basically opens the Excel file, spends a long time downloading data from the Internet, and then writing that to the Excel file. I want it to stop if the initialization doesn't work so I don't have to wait for the script to complete (it can take up to 15 minutes). I'm pretty sure that it has something to do with the fact that it says "Exception ignored", but I'm not familiar with all the error-fu in Python.

EDIT:

I added an excel.close() command right at the end and now it doesn't give me the first error, but a second (and much larger and scarier) one:

Traceback (most recent call last):
  File "C:\Users\carte_000\Python\stock_get_rev8.py", line 161, in <module>
    excel.close()
  File "c:\python34\lib\site-packages\xlsxwriter\workbook.py", line 287, in close
    self._store_workbook()
  File "c:\python34\lib\site-packages\xlsxwriter\workbook.py", line 510, in _store_workbook
    xml_files = packager._create_package()
  File "c:\python34\lib\site-packages\xlsxwriter\packager.py", line 132, in _create_package
    self._write_worksheet_files()
  File "c:\python34\lib\site-packages\xlsxwriter\packager.py", line 189, in _write_worksheet_files
    worksheet._assemble_xml_file()
  File "c:\python34\lib\site-packages\xlsxwriter\worksheet.py", line 3395, in _assemble_xml_file
    self._write_sheet_data()
  File "c:\python34\lib\site-packages\xlsxwriter\worksheet.py", line 4802, in _write_sheet_data
    self._write_rows()
  File "c:\python34\lib\site-packages\xlsxwriter\worksheet.py", line 4988, in _write_rows
    self._write_cell(row_num, col_num, col_ref)
  File "c:\python34\lib\site-packages\xlsxwriter\worksheet.py", line 5148, in _write_cell
    xf_index = cell.format._get_xf_index()
AttributeError: type object 'str' has no attribute '_get_xf_index'

EDIT 2:

The part of the code that actually writes to the file is this:

for r, row in enumerate(data):
    for c, cell in enumerate(row):
        if 'percent' in formats[c]:
            sheet.write(r + r_offset, c + c_offset, cell, eval(formats[c].replace('_f', '')))
        elif '_f' in formats[c]:
            sheet.write(r + r_offset, c + c_offset, cell.format(n=str(r + r_offset)), eval(formats[c].replace('_f', '')))
        else:
            sheet.write(r + r_offset, c + c_offset, cell, eval(formats[c][0] + formats[c].replace('_f', '')[-1]))

If I replace all the fancy if...else stuff with a single

sheet.write(r + r_offset, c + c_offset, cell)

it doesn't give me the error, and seems to work fine.

This doesn't provide the functionality I need, as some columns need to be formulas whose numbers change based on the row. What in the above code is causing the excel.close() line to bug out?

Possible to alter worksheet order in xlsxwriter?

I have a script which creates a number of the following pairs of worksheets in order:

WorkSheet (holds data) -> ChartSheet using WorkSheet

After the script is finished, I am left with worksheets ordered as such:

Data1, Chart1, Data2, Chart2, Data3, Chart3, ...

Is it possible to re-order the worksheets at the end of the script (i.e. before workbook.close()) to obtain the following worksheet order in the final .xlsx file?

Chart1, Chart2, Chart3,...,ChartN, Data1, Data2, Data3,...

DateTime issues in django xlsxwriter

I am trying to create an export to excel functionality in my django view as follows:

def export_myreport(request, sd, ed):
    from xlsxwriter.workbook import Workbook
    import cStringIO as StringIO
    from django.utils.encoding import smart_str

    # create a workbook in memory
    output = StringIO.StringIO()

    wb = Workbook(output)

    bg = wb.add_format({'bg_color': '#9CB640', 'font_color': 'black'})
    bg2 = wb.add_format({'bg_color': '#FFFFFF', 'font_color': 'black'})

    ws = wb.add_worksheet('My Report')

    row_num = 0

    summary = MyModel.objects.filter(time__range = (sd, ed)).select_related()

    row_num += 2
    row = [
        smart_str(u"Time"),
        smart_str(u"Item"),
        smart_str(u"User")
    ]
    for col_num in xrange(len(row)):
        ws.write(row_num, col_num, row[col_num], bg)

    for s in summary:
        row_num += 1
        row2 = [
            s.time,
            s.model_name,
            s.user.first_name
        ]
        for col_num in xrange(len(row2)):
            ws.write(row_num, col_num, row2[col_num], bg2)

    wb.close()

    output.seek(0)
    response = HttpResponse(output.read(), content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
    response['Content-Disposition'] = "attachment; filename=myreport.xlsx"

    return response

But i am getting some issues with the DateTime formatting! Perhaps something i am missing here?

Here is the error i get:

TypeError at /myapp/export_myreport/2015-05-01/2015-05-19
can't subtract offset-naive and offset-aware datetimes

EDIT:

This is how i am calling the url in my html:

<a rel='nofollow' href="export_myreport/{{begindate}}/{{enddate}}" class="btn btn-default pull-right" role="button">Export to XLSX</a>

Here the {{begindate}} and {{enddate}} are angular variables.

xlsxwriter: add formula with other sheet in it

I try to create a XLSX file with xlsxwriter python plugin. In this XLSX, I have 2 sheets:

• Analyse: Contain a table with informations
• Stat: Contain some informations and 2 formulas

This 2 formulas are:

=NBVAL(Analyse!C:C)-1
=NB.SI(Analyse!D:D;"To change")

My problem is when I open the generated file, I have a error. And the formulas don't work. If I edit the formula and just press Enter, it work.

My code:

shInfo    = self.__workbook.add_worksheet("Stat")
shInfo.activate()

information = self.__workbook.add_format({'bg_color': '#BFBFBF',
                                          'font_name': 'Courier New'})

shInfo.write('G3','=NBVAL(Analyse!C:C)-1',information)
shInfo.write('G5','=NB.SI(Analyse!D:D;"To change")',information)

When I open the XML error report. I have this:

<?xml version="1.0" encoding="UTF-8" standalone="true"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <logFileName>error056160_04.xml</logFileName>
  <summary>Des erreurs ont été détectées dans le fichier « L:\UNMS\InputBEB\Output\UNMSViewer\public_html\Data\XLSX\todo\A6S54300.xlsx »</summary>
  <removedRecords summary="Liste des enregistrements supprimés ci-dessous :">  
    <removedRecord>Enregistrements supprimés: Formule dans la partie /xl/worksheets/sheet2.xml</removedRecord>
    </removedRecords>
</recoveryLog>

Why can't I freeze_panes on the xlsxwriter object pandas is creating for me?

I have a class whose objects contain pandas dataframes (self.before, and self.after below) and a save() method which uses xlsxwriter to export the data (which has two worksheets, "before" and "after"). I'm trying to freeze panes (and later want to use conditional formatting too).

Even though I know you can't change the style of cells you've already written, these two operations are applied at the worksheet level and therefore should be settable later.

Here's the code:

def save():
    writer = pd.ExcelWriter(self.path, engine='xlsxwriter')
    self.before.to_excel(writer, "before")
    self.after.to_excel(writer, "after")

    for sht_name in writer.sheets:
        ws = writer.sheets[sht_name]
        ws.freeze_panes=(2,0)

    writer.save()

The panes of the worksheets that get saved does NOT have frozen panes. I've used the method successfully when using xlsxwriter (without pandas), and I'm following the lead set by this pandas-charts documentation which appears to just retrieve the underlying xlsxwriter objects from the writer object and operate on those.

Do you have ideas about why I'm failing to get that result here.

If for whatever reason this can't be done, I can always retrieve the individual values of the table from the dataframe, of course, but that seemed excessive upon first glance.

Python: list of strings, change color of character if found (using xlsxwriter)

I have several lists I am writing to different columns/rows of an excel spreadsheet using xlsxwriter in python 2.7. For one list of strings (DNA sequences), I want to find certain characters in the string ('a','t','c','g'), change their individual colors, and then write the complete list of strings (multicolored strings, per character) to one column in the spreadsheet.

So far the code I have writen is:

row = 1
col = 1
for i in (seqs):
    worksheet.write(row,1,i,green)
    for char in i:
        if i.__contains__("A") or i.__contains__("T") :
            worksheet.write(row,1,i[char],red)
row += 1

Where seqs is my list of sequences. I want A/T to be red, and G/C to be green and the full sequence written to the spreadsheet. I'm not getting any errors, but I either write the entire sequence per row in excel in green, or one character per row in red. Is there any way to do this/get this code to work?

Write to StringIO object using Pandas Excelwriter?

I can pass a StringIO object to pd.to_csv() just fine:

io = StringIO.StringIO()
pd.DataFrame().to_csv(io)

But when using the excel writer, I am having a lot more trouble.

io = StringIO.StringIO()
writer = pd.ExcelWriter(io)
pd.DataFrame().to_excel(writer,"sheet name")
writer.save()   

Returns an

AttributeError: StringIO instance has no attribute 'rfind'

I'm trying to create an ExcelWriter object without calling pd.ExcelWriter() but am having some trouble. This is what I've tried so far:

from xlsxwriter.workbook import Workbook
writer = Workbook(io)
pd.DataFrame().to_excel(writer,"sheet name")
writer.save()

But now I am getting an AttributeError: 'Workbook' object has no attribute 'write_cells'

How can I save a pandas dataframe in excel format to a StringIO object?

xlsxwriter: is there a way to open an existing worksheet in my workbook?

I'm able to open my pre-existing workbook, but I don't see any way to open pre-existing worksheets within that workbook. Is there any way to do this?

UnicodeDecodeError error writing .xlsx file using xlsxwriter

I am trying to write about 1000 rows to a .xlsx file from my python application. The data is basically a combination of integers and strings. I am getting intermittent error while running wbook.close() command. The error is the following:

UnicodeDecodeError: 'ascii' codec can't decode byte 0xc3 in position 15: 
                     ordinal not in range(128)

My data does not have anything in unicode. I am wondering why the decoder is being at all. Has anyone noticed this problem?

How to concatenate string cell contents

I have this which works:

chart.set_title({'name': '=Sheet1!$A$2'})

What I want is functionally something like this;

chart.set_title({'name': '=Sheet1!$A$2' + ' to ' + '=Sheet1!$B$2'})

This gives an error of Unknown worksheet reference. Both cells contain dates, so it would look something like 1/1/2011 to 4/4/2014. I can't find any examples in the docs of how to do this.

Also tried this based on a now deleted proposed answer:

chart.set_title({'name': '=CONCATENATE(Sheet1!$A$2, " to ", Sheet1!$B$2)'})

It appears, however, that set_title does not accept formulas. It is possible to write this formula into a cell and then refer that cell in the set_title. A bit of a kluge:

worksheet.write(9, 20, '=CONCATENATE(Sheet1!$A$2, " to ", Sheet1!$B$2)')
chart.set_title({'name': '=Sheet1!$U$10'}) #(9,20)

Simulate autofit column in xslxwriter

I would like to simulate the Excel autofit function in Python's xlsxwriter. According to this url, it is not directly supported: http://xlsxwriter.readthedocs.org/en/latest/worksheet.html

However, it should be quite straightforward to loop through each cell on the sheet and determine the maximum size for the column and just use worksheet.set_column(row, col, width) to set the width.

The complications that is keeping me from just writing this are:

1. That URL does not specify what the units are for the third argument to set_column.
2. I can not find a way to measure the width of the item that I want to insert into the cell.
3. xlsxwriter does not appear to have a method to read back a particular cell. This means I need to keep track of each cell width as I write the cell. It would be better if I could just loop through all the cells, that way a generic routine could be written.

XlsxWriter Column-Line Chart

Is it possible to create a Column-Line Chart in Python using XlsxWriter.

Excel has an option to this but this module doesn't seem to.

Insert pandas chart into an Excel file using XlsxWriter

I use python 3.4, pandas 0.14.1 and XlsxWriter 0.5.6. I create a graph called 'graph' using pandas with the following code

 graph=data_iter['_DiffPrice'].hist() 

, which produces a beautiful histogram.

Now, how do I insert that graph into an Excel file using XlsxWriter?

I tried the XlsxWriter method

workbook.add_chart()

but this creates a graph in Excel, not what I want.

Thanks