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:
Sometimes you can get a "too many rows" error when inserting a couple or rows in an empty file. We learn how this might happen.
Why is FlexCel complaining that I don't have the permissions to embed a font?
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"
Excel is moving more and more to cloud fonts, and why that is bad news for the rest of us.
The same, yet different.
Paint the columns, not the cells.
A Docker container needs fonts!
Do we rotate the text first and then align it, or do we align it first?
Modern Excel versions allow SVG as a file format for images inside. In this tip we explore the FlexCel support for the feature.
It all depends on what we define by "used column"
When exporting to PDF, you need to find out which fonts are actually used.
Did you know that you can use FlexCel's "Export as SVG" feature to get scalable images of your spreadsheets?
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.
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?
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.
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.
Sometimes you want to know the number of pages needed for a report, before actually exporting or printing it.
When autofitting rows, you might want to use TExcelFile.CellMarginFactor besides adjustment.
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.
Don't use APIMate for this.
Excel has some numeric formats that vary from locale to locale. Let's get a more in depth look under the covers.
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.
Here we discuss the different ways to embed a barcode in your file using FlexCel.
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.
You open an existing file with FlexCel and save it. Now the file is some kilobytes smaller. What is happening?
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.
FlexCel supports both normal and strict xlsx files. Which one should you use?
A simple code snippet to find out the version of FlexCel that you are using from inside your application.
You are creating documents. How do you ensure that nobody alters the data on them?
Whenever you need change the format of a cell depending on its value, conditional format is here to help.
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)
Excel 2007 made changing the font name in a cell a little more complex than what it should be.
You can enter multiple lines of text inside a single cell, but there are a couple of things to be aware of.
What does a column width of 8.44 actually mean?
I have my data in this dataset. How do I export it to Excel?
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.