Tips and Tricks
An assorted list of small and interesting stuff to help you get the most out of FlexCel.
We will be adding new tips regularly here, so make sure to check this page from time to time.
In this section:
Localized month names
Why you are not getting the same date strings as Excel. And while we are at it, we tell you what's a "genitive month name"
Cloud fonts
Excel is moving more and more to cloud fonts, and why that is bad news for the rest of us.
Painting a full sheet black
Paint the columns, not the cells.
Running FlexCel inside Docker containers
A Docker container needs fonts!
Text rotation in shapes inside xls and xlsx files
Do we rotate the text first and then align it, or do we align it first?
SVG files inside xlsx files
Modern Excel versions allow SVG as a file format for images inside. In this tip we explore the FlexCel support for the feature.
The maximum used column on a sheet
It all depends on what we define by "used column"
Finding the actual fonts used when exporting to PDF
When exporting to PDF, you need to find out which fonts are actually used.
Scalable images in your documentation
Did you know that you can use FlexCel's "Export as SVG" feature to get scalable images of your spreadsheets?
Using Tokens to get information from formulas
Let's imagine that you want to manually modify all formulas that refer to A1 to refer to A2. You could try some manual regex to search and replace all occurrences of "A1" by "A2" into the formula string. But of course this would replace also A11 by A21 and also do replacements in things that aren't references. Luckily FlexCel's Tokens provide a much more elegant and reliable solution to do it.
Semi-absolute references
You know that a cell reference like "A4" is relative, while "$A$4" is absolute. But is there a way to have a reference be absolute when it is outside the range copied, and relative if it is inside?
References in conditional formats and data validations
Formulas inside conditional formats or data validations apply to a range of cells instead of a single cell. This makes them behave differently from regular cell formulas in ways that might not be obvious.
Replacing a font by another in an Excel file
If you want to replace for example all Calibri fonts with a different font in a file, you can use the code in this tip.
Finding out how many pages will be exported
Sometimes you want to know the number of pages needed for a report, before actually exporting or printing it.
Fine-tuning row autofitting
When autofitting rows, you might want to use ExcelFile.CellMarginFactor besides adjustment.
Understanding CSV files
There is no such thing as a "Universal CSV" which everyone can understand. To create a CSV file, you need to know who will be reading it.
Embedding Excel files in your application
Don't use APIMate for this.
Internal numeric formats
Excel has some numeric formats that vary from locale to locale. Let's get a more in depth look under the covers.
How to change the FlexCel locale
Some numeric formats in Excel change depending on your machine locale. FlexCel will by default pick your locale from the machine configuration too, but you can change those without modifying the machine settings.
Using barcodes
Here we discuss the different ways to embed a barcode in your file using FlexCel.
How to get the hyperlink in a given cell?
Hyperlinks in a cell aren't as straightforward as other cell properties. Here we discuss how they work and how to get the link in some specific cell.
Why are xlsx files generated by FlexCel smaller than the same files generated by Excel?
You open an existing file with FlexCel and save it. Now the file is some kilobytes smaller. What is happening?
Automatically opening generated Excel files
Sometimes you might want to open the generated files directly in Excel without asking the user where to save them first. While this is not technically possible, in this tip we show a way to simulate it.
Using strict xlsx files
FlexCel supports both normal and strict xlsx files. Which one should you use?
Finding out which FlexCel version you are using
A simple code snippet to find out the version of FlexCel that you are using from inside your application.
Sign your PDF files
You are creating documents. How do you ensure that nobody alters the data on them?
Conditionally format all things!
Whenever you need change the format of a cell depending on its value, conditional format is here to help.
Expanding formulas in consecutive cells
If there only was a simple way to enter "=B1" in A1 and "=B2" in A2...
We also tell you about a simple way to check if all the formulas in a range have consistent values (so the formula in A1 refers to B1, and the formula in A2 refers to B2 and so on)
Changing the font name
Excel 2007 made changing the font name in a cell a little more complex than what it should be.
Entering multiple lines of text inside a cell
You can enter multiple lines of text inside a single cell, but there are a couple of things to be aware of.
Understanding Excel measurement units
What does a column width of 8.44 actually mean?
Dumping a Dataset into Excel
I have my data in this dataset. How do I export it to Excel?
Reading only the first row of a file
If you have lots of big files to analyze, you might not want to load the full files into memory just to check if you need to process them.