Programming languages

How center text horizontally using Alignment in OpenPyXL

Based on our previous OpenPyXL minimal XLSX write example, this code will generate a XLSX with a number in cell A1 that is right-aligned automatically:

from openpyxl import Workbook
wb = Workbook()
sheet = wb["Sheet"] # This sheet is created by default
# Add content to sheet
sheet["A1"] = 4.5
sheet["A1"].alignment = Alignment(horizontal="center")
# Save
wb.save("openpyxl-test.xlsx")

How to add centered alignment

In order to align the cell horizontally, we just need to

from openpyxl.styles.alignment import Alignment

sheet["A1"].alignment = Alignment(horizontal="center")

Full example

from openpyxl import Workbook
from openpyxl.styles.alignment import Alignment
wb = Workbook()
sheet = wb["Sheet"] # This sheet is created by default
# Add content to sheet
sheet["A1"] = 4.5
sheet["A1"].alignment = Alignment(horizontal="center")
# Save
wb.save("openpyxl-test.xlsx")

This will look like this:

Posted by Uli Köhler in OpenPyXL, Python

Pandas XLSX export with background color based on cell value

This example script uses openpyxl to set a cell’s background color to red for False cells or to green for True cells. Note that we convert the boolean values to "True"or "False" strings based on the method we outlined in our previous post How to write Pandas bool column as True/False instead of 1/0 to XLSX. For more details on how to just set the background color in OpenPyXL, see our post on How to set cell background color in OpenPyXL.

import pandas as pd
from openpyxl.styles import PatternFill

df = pd.DataFrame([
    {"a": True},
    {"a": False},
    {"a": True},
    {"a": False},
    {"a": False},
])
df["a"] = df["a"].map({True: "True", False: "False"})
with pd.ExcelWriter("out.xlsx", engine="openpyxl") as writer:
    sheet_name = "Bool"
    # Export DataFrame content
    df.to_excel(writer, sheet_name=sheet_name)
    # Set backgrund colors depending on cell values
    sheet = writer.sheets[sheet_name]
    for cell, in sheet[f'B2:B{len(df) + 1}']: # Skip header row, process as many rows as there are DataFrames
        value = df["a"].iloc[cell.row - 2] # value is "True" or "False"
        cell.fill = PatternFill("solid", start_color=("5cb800" if value == "True" else 'ff2800'))

The output from this script looks like this:

Posted by Uli Köhler in OpenPyXL, Python

How to get OpenPyXL column letter by index

If you want to access OpenPyXL columns using indices instead of letters, use

import openpyxl.utils.cell

openpyxl.utils.cell.get_column_letter(idx)

Note that idx is 1-based: index 0 is not a valid argument & index 1 yields column name A 

For example:

openpyxl.utils.cell.get_column_letter(3) # returns "C"

 

Posted by Uli Köhler in OpenPyXL, Python

How to set cell background color in OpenPyXL

In order to set a cell’s background color in OpenPyXL, use PatternFill with fill_type="solid" and start_color="your_desired_color"  and no end_color. The following example will set a cell’s background to green:

sheet["A1"].fill = PatternFill("solid", start_color="5cb800")

Full example based on our OpenPyXL minimal XLSX write example

from openpyxl import Workbook
wb = Workbook()
sheet = wb["Sheet"] # This sheet is created by default
# Add content to sheet
sheet["A1"] = "This is cell A1"
sheet["A1"].fill = PatternFill("solid", start_color="5cb800")
# Save
wb.save("openpyxl-test.xlsx")

Posted by Uli Köhler in OpenPyXL, Python

OpenPyXL minimal XLSX write example

This serves a minimal example of how to write an XLSX file using OpenPyXL:

from openpyxl import Workbook
wb = Workbook()
sheet = wb["Sheet"] # This sheet is created by default
# Add content to sheet
sheet["A1"] = "This is cell A1"
# Save
wb.save("openpyxl-test.xlsx")

Posted by Uli Köhler in OpenPyXL, Python

How to write Pandas bool column as True/False instead of 1/0 to XLSX

Problem:

When writing a pandas XLSX, bool-type columns are shown are 0 for False or 1 for True.

df = pd.DataFrame([
    {"a": True},
    {"a": False},
    {"a": True},
    {"a": False},
    {"a": False},
])

with pd.ExcelWriter("out.xlsx", engine="xlsxwriter") as writer:
    df.to_excel(writer)

Solution:

Map the column to a string column with your desired value before exporting:

df["a"] = df["a"].map({True: "True", False: "False"})

Full example code:

df = pd.DataFrame([
    {"a": True},
    {"a": False},
    {"a": True},
    {"a": False},
    {"a": False},
])
df["a"] = df["a"].map({True: "True", False: "False"})
with pd.ExcelWriter("out.xlsx", engine="xlsxwriter") as writer:
    df.to_excel(writer)

 

Posted by Uli Köhler in pandas, Python

How to implement 1MHz interrupt in PlatformIO / Arduino on STM32

In our previous post Minimal STM32 HardwareTimer PlatformIO / Arduino timer interrupt blink example we showed how to use HardwareTimer to blink the onboard LED of our STM32F407 board using a timer interrupt.

In this post, we’ll provide an example of how to use HardwareTimer and have a really fast interrupt which runs at 1 MHz – in other words: one million times per second.

#include <Arduino.h>

HardwareTimer timer(TIM1);
bool ledOn = false;

void OnTimer1Interrupt() {
    ledOn = !ledOn;
    digitalWrite(PC13, ledOn ? HIGH : LOW);
}

void setup() {
    pinMode(PC13, OUTPUT);
    // Configure timer
    timer.setPrescaleFactor(21); // Set prescaler to 21 => timer frequency = 168/21 = 8 MHz (from prediv'd by 1 clocksource of 168 MHz)
    timer.setOverflow(8); // Set ARR to 8 => timer frequency = 1 MHz
    timer.attachInterrupt(OnTimer1Interrupt);
    timer.refresh(); // Make register changes take effect
    timer.resume(); // Start timre
}

void loop() {
}

Note that when running such a quick interrupt, you can’t do all too much within the interrupt before the next time the interrupt will run.

Posted by Uli Köhler in C/C++, PlatformIO, STM32

How to use STM32 _Msk and _Pos definitions to read and write registers

The STM32 HAL contains definitions like TIM_CR1_CKD_Msk or TIM_CR1_CKD_Pos which you can use to make it easier to read or write parts of a register.

Reading a part of a register

uint32_t ckd = (TIM1->CR1 & TIM_CR1_CKD_Msk) >> TIM_CR1_CKD_Pos;

Writing a part of a register

uint32_t new_ckd_value = TIM_CLOCKDIVISION_DIV4; // example
TIM1->CR1 &= TIM_CR1_CKD_Msk; // Clear bits
TIM1->CR1 |= new_ckd_value << TIM_CR1_CKD_Pos; // Set bits

 

Posted by Uli Köhler in C/C++, STM32

How to create pandas pd.DataFrame from list of dicts

You can create a pandas pd.DataFrame from a list of dicts (lst) using the pd.DataFrame(lst) constructor.

a = {"x": 1, "y": 2}
b = {"x": 3, "y": 2}
c = {"x": 6, "y": 9}
df = pd.DataFrame([a,b,c])

df will look like this:

Posted by Uli Köhler in pandas, Python

How to identify the latest npm package version

You can use

npm info [package name] version | head -n1

to print just the latest package version, for example:

npm info @angular/cli version | head -n1

The head -n1 part is neccessary to suppress unwanted npm info messages. Just running the command without head -n1 :

npm info @angular/cli version

prints the following output:

12.2.6
npm notice 
npm notice New minor version of npm available! 7.21.1 -> 7.24.0
npm notice Changelog: https://github.com/npm/cli/releases/tag/v7.24.0
npm notice Run npm install -g [email protected] to update!
npm notice

I typically use the node:latest docker container instead of a local npm to find the latest version of an npm package:

docker run -it --rm node:latest npm info meshcommander version | head -n1

At the time of writing this article, the command prints

0.9.0-d

 

Posted by Uli Köhler in NodeJS

How to get size of compressed file using JSZip

In JSZip, you can get the file size of a file compressed inside a ZIP archive by first decompressing it using zip.file(...).async(...) into an ArrayBuffer and then using .byteLength to get the size of the buffer.

zip.file("filename.txt").async("ArrayBuffer").then(function(data) {
    var fileSize = data.byteLength;
    // TODO Your code goes here
})

Full example

This is based on our post on How to uncompress file inside ZIP archive using HTML5 & JSZip which reads a local file using a HTML5 file input. This example prints the file size of every file inside the ZIP archive:

<html>
<body>
    <input type="file" id="myfile" onchange="onMyfileChange(this)" />

    <script src="https://unpkg.com/[email protected]/dist/jszip.js" type="text/javascript"></script>
    <script type="text/javascript">
        function onMyfileChange(fileInput) {
            if(fileInput.files[0] == undefined) {
                return ;
            }

            var filename = fileInput.files[0].name;
            var reader = new FileReader();
            reader.onload = function(ev) {
                JSZip.loadAsync(ev.target.result).then(function(zip) {
                    zip.file("word/document.xml").async("ArrayBuffer").then(function(data) {
                        console.log("word/document.xml is", data.byteLength, "bytes long");
                    })
                }).catch(function(err) {
                    console.error("Failed to open", filename, " as ZIP file:", err);
                })
            };
            reader.onerror = function(err) {
                console.error("Failed to read file", err);
            }
            reader.readAsArrayBuffer(fileInput.files[0]);
        }
    </script>
</body>
</html>

Example output

word/document.xml is 88369 bytes long

 

Posted by Uli Köhler in Javascript

How to list files inside a ZIP archive using JSZip

In JSZip, you can list files inside a ZIP archive using zip.fileswhich is an Object with key = filename and value = file metadata object.

JSZip.loadAsync(fileContent).then(function(zip) {
    for(let [filename, file] of Object.entries(zip.files)) {
        // TODO Your code goes here
        console.log(filename);
    }
}).catch(function(err) {
    console.error("Failed to open", filename, " as ZIP file:", err);
})

Full example

This is based on our post on How to read local ZIP in HTML5/Javascript using JSZip

<html>
<body>
    <input type="file" id="myfile" onchange="onMyfileChange(this)" />

    <script src="https://unpkg.com/[email protected]/dist/jszip.js" type="text/javascript"></script>
    <script type="text/javascript">
        function onMyfileChange(fileInput) {
            if(fileInput.files[0] == undefined) {
                return ;
            }

            var filename = fileInput.files[0].name;
            // var filesize = fileInput.files[0].size;
            var reader = new FileReader();
            reader.onload = function(ev) {
                JSZip.loadAsync(ev.target.result).then(function(zip) {
                    console.log(zip)
                }).catch(function(err) {
                    console.error("Failed to open", filename, " as ZIP file");
                })
            };
            reader.onerror = function(err) {
                console.error("Failed to read file", err);
            }
            reader.readAsArrayBuffer(fileInput.files[0]);
        }
    </script>
</body>
</html>

 

 

Posted by Uli Köhler in Javascript

How to read a file inside a ZIP archive as string using JSZip

Want to read as an ArrayBuffer instead? Read How to read a file inside a ZIP archive as ArrayBuffer using JSZip

In JSZip, you can read a compressed file as JavaScript string using

zip.file("filename.txt").async("string").then(function(data) {
    // data is a string
    // TODO your code goes here, this is just an example
    console.log(data);
})

Full example

This is based on our post on How to uncompress file inside ZIP archive using HTML5 & JSZip which reads a local file using a HTML5 file input. You can upload any .docx file for testing:

<html>
<body>
    <input type="file" id="myfile" onchange="onMyfileChange(this)" />

    <script src="https://unpkg.com/[email protected]/dist/jszip.js" type="text/javascript"></script>
    <script type="text/javascript">
        function onMyfileChange(fileInput) {
            if(fileInput.files[0] == undefined) {
                return ;
            }

            var filename = fileInput.files[0].name;
            var reader = new FileReader();
            reader.onload = function(ev) {
                JSZip.loadAsync(ev.target.result).then(function(zip) {
                    zip.file("word/document.xml").async("string").then(function(data) {
                        // data is a string
                        // TODO Your code goes here!
                        console.log(data)
                    })
                }).catch(function(err) {
                    console.error("Failed to open", filename, " as ZIP file:", err);
                })
            };
            reader.onerror = function(err) {
                console.error("Failed to read file", err);
            }
            reader.readAsArrayBuffer(fileInput.files[0]);
        }
    </script>
</body>
</html>

 

Posted by Uli Köhler in Javascript

How to read a file inside a ZIP archive as ArrayBuffer using JSZip

Want to read as a string instead? Read How to read a file inside a ZIP archive as string using JSZip

In JSZip, you can read a compressed file as ArrayBuffer using

zip.file("filename.txt").async("ArrayBuffer").then(function(data) {
    // data is an ArrayBuffer
    // TODO your code goes here
})

Full example

This is based on our post on How to uncompress file inside ZIP archive using HTML5 & JSZip which reads a local file using a HTML5 file input:

<html>
<body>
    <input type="file" id="myfile" onchange="onMyfileChange(this)" />

    <script src="https://unpkg.com/[email protected]/dist/jszip.js" type="text/javascript"></script>
    <script type="text/javascript">
        function onMyfileChange(fileInput) {
            if(fileInput.files[0] == undefined) {
                return ;
            }

            var filename = fileInput.files[0].name;
            var reader = new FileReader();
            reader.onload = function(ev) {
                JSZip.loadAsync(ev.target.result).then(function(zip) {
                    zip.file("word/document.xml").async("ArrayBuffer").then(function(data) {
                        // data is an ArrayBuffer
                        // TODO Your code goes here!
                    })
                }).catch(function(err) {
                    console.error("Failed to open", filename, " as ZIP file:", err);
                })
            };
            reader.onerror = function(err) {
                console.error("Failed to read file", err);
            }
            reader.readAsArrayBuffer(fileInput.files[0]);
        }
    </script>
</body>
</html>

 

Posted by Uli Köhler in Javascript

How to get size of ArrayBuffer in Javascript

You can get the size of an ArrayBuffer in Javascript using .byteLength:

var buf = new ArrayBuffer(8);
buf.byteLength // == 8

 

Posted by Uli Köhler in Javascript

How to uncompress file inside ZIP archive using HTML5 & JSZip

In our previous post How to read local ZIP in HTML5/Javascript using JSZip we provided an example of how to use JSZip to list files inside a local ZIP archive using HTML5.

This post will expand upon this in order to read the content of a file compressed inside a local ZIP archive file.

In this example, we’ll read a local ZIP file using HTML5 <input type="file"> and uncompress its content. We’ll read the word/document.xml file, so you can upload any .docx file to test that.

JSZip.loadAsync(ev.target.result).then(function(zip) {
    zip.file("word/document.xml").async("ArrayBuffer").then(function(data) {
        // data is an ArrayBuffer
        // TODO Your code goes here!
    })
}).catch(function(err) {
    console.error("Failed to open", filename, " as ZIP file:", err);
})

Full example

<html>
<body>
    <input type="file" id="myfile" onchange="onMyfileChange(this)" />

    <script src="https://unpkg.com/[email protected]/dist/jszip.js" type="text/javascript"></script>
    <script type="text/javascript">
        function onMyfileChange(fileInput) {
            if(fileInput.files[0] == undefined) {
                return ;
            }

            var filename = fileInput.files[0].name;
            var reader = new FileReader();
            reader.onload = function(ev) {
                JSZip.loadAsync(ev.target.result).then(function(zip) {
                    zip.file("word/document.xml").async("ArrayBuffer").then(function(data) {
                        // data is an ArrayBuffer
                        // TODO Your code goes here!
                    })
                }).catch(function(err) {
                    console.error("Failed to open", filename, " as ZIP file:", err);
                })
            };
            reader.onerror = function(err) {
                console.error("Failed to read file", err);
            }
            reader.readAsArrayBuffer(fileInput.files[0]);
        }
    </script>
</body>
</html>

 

 

Posted by Uli Köhler in HTML, Javascript

How to include JSZip in the browser

The JSZip documentation doesn’t make it entirely clear how to include it in the browser, i.e. which <script> tag you need to use in order to load JSZip.

Here’s the appropriate <script> tag:

<script src="https://unpkg.com/[email protected]/dist/jszip.min.js" type="text/javascript"></script>

You can also use the non-minified version which does not have minified class and object names:

<script src="https://unpkg.com/[email protected]/dist/jszip.js" type="text/javascript"></script>

 

Posted by Uli Köhler in HTML, Javascript

How to read local ZIP in HTML5/Javascript using JSZip

In our previous post How to load local file in Javascript using HTML5 FileReader we showed how to read a local file using the HTML5 FileReader.

Based on this, it’s pretty easy to use JSZip to read a local ZIP file:

var reader = new FileReader();
reader.onload = function(ev) {
    JSZip.loadAsync(ev.target.result).then(function(zip) {
        // TODO Your code goes here. This is just an example.
        console.log(zip)
    }).catch(function(err) {
        console.error("Failed to open", filename, " as ZIP file:", err);
    })
};
reader.onerror = function(err) {
    console.error("Failed to read file", err);
}
reader.readAsArrayBuffer(fileInput.files[0]);

One example of what you can do using the JSZip object is to list the filenames inside the ZIP file:

var filename = fileInput.files[0].name;
var reader = new FileReader();
reader.onload = function(ev) {
    JSZip.loadAsync(ev.target.result).then(function(zip) {
        for(let [filename, file] of Object.entries(zip.files)) {
            console.log(filename);
        }
    }).catch(function(err) {
        console.error("Failed to open", filename, " as ZIP file:", err);
    })
};
reader.onerror = function(err) {
    console.error("Failed to read file", err);
}
reader.readAsArrayBuffer(fileInput.files[0]);

Full example

<html>
<body>
    <input type="file" id="myfile" onchange="onMyfileChange(this)" />

    <script src="https://unpkg.com/[email protected]/dist/jszip.js" type="text/javascript"></script>
    <script type="text/javascript">
        function onMyfileChange(fileInput) {
            if(fileInput.files[0] == undefined) {
                return ;
            }

            var filename = fileInput.files[0].name;
            // var filesize = fileInput.files[0].size;
            var reader = new FileReader();
            reader.onload = function(ev) {
                JSZip.loadAsync(ev.target.result).then(function(zip) {
                    console.log(zip)
                }).catch(function(err) {
                    console.error("Failed to open", filename, " as ZIP file");
                })
            };
            reader.onerror = function(err) {
                console.error("Failed to read file", err);
            }
            reader.readAsArrayBuffer(fileInput.files[0]);
        }
    </script>
</body>
</html>

 

Posted by Uli Köhler in HTML, Javascript

How to load local file in Javascript using HTML5 FileReader

In HTML5, you can not only upload a file to a server but also process it directly in the browser using Javascript.

This post provides minimal examples of how to use FileReader to do that.

How to read a text file

<html>
<body>
    <input type="file" id="myfile" onchange="onMyfileChange(this)" />
    <script type="text/javascript">
        function onMyfileChange(fileInput) {
            if(fileInput.files[0] == undefined) {
                return ;
            }

            // Example of what information you can read
            // var filename = fileInput.files[0].name;
            // var filesize = fileInput.files[0].size;
            var reader = new FileReader();
            reader.onload = function(ev) {
                var content = ev.target.result; // content is a string
                console.log("Successfully read file");
            };
            reader.onerror = function(err) {
                console.error("Failed to read file", err);
            }
            reader.readAsText(fileInput.files[0]);
        }
    </script>
</body>
</html>

How to read a binary file as ArrayBuffer

<html>
<body>
    <input type="file" id="myfile" onchange="onMyfileChange(this)" />
    <script type="text/javascript">
        function onMyfileChange(fileInput) {
            if(fileInput.files[0] == undefined) {
                return ;
            }

            // Example of what information you can read
            // var filename = fileInput.files[0].name;
            // var filesize = fileInput.files[0].size;
            var reader = new FileReader();
            reader.onload = function(ev) {
                var content = ev.target.result; // content is an ArrayBuffer object
                console.log("Successfully read file");
            };
            reader.onerror = function(err) {
                console.error("Failed to read file", err);
            }
            reader.readAsArrayBuffer(fileInput.files[0]);
        }
    </script>
</body>
</html>

 

Posted by Uli Köhler in HTML, Javascript

How to fix error: cannot find the python “fuse” module; please install it

Problem:

When running bup fuse, you see this error message

error: cannot find the python "fuse" module; please install it

Solution:

On Ubuntu or other debian-based systems, just install the package using apt:

sudo apt -y install python3-fuse

Otherwise, install it using pip:

sudo pip3 install fuse-python
Posted by Uli Köhler in Python