Tag: excel

Jun

Considerations for storing Excel cell value in code

You may hate Excel, but you may find a discussion of how Excel stores cell values interesting. So I have a spreadsheet library. The biggest concern at the initial stage was how to store all the spreadsheet data efficiently. I hear people talking about millions of cells, so I’m scared. If my program stores a […]

Nov

Modulo 26 and column names

I was sitting in the lecture theatre valiantly trying to keep awake. The professor was speaking on the rigorous application and proving of the modulus function. It’s basically the remainder, but I’ve never been introduced to it in such, uh, rigor. He brought up an example using modulo 26. And demonstrated the wrapping around of […]

Nov

Calculate Excel column width pixel interval

Brace yourself. You’re about to learn the secret behind how Excel mysteriously calculates the column width intervals. In this article, I’m not going into the details of the column widths, but the column width intervals. There’s a difference. From the Open XML SDK specs: width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 […]

Aug

How to properly use OpenXmlWriter to write large Excel files

Brian Jones already wrote something on using the OpenXmlWriter with the Open XML SDK to write large Excel files. There are a couple of things that weren’t mentioned though. OpenXmlWriter works best for creating new things For the purposes of explanation, we’ll assume the “new thing” is a new worksheet, although OpenXmlWriter can be used […]

May

Collision detection in merging Excel cells

You can merge cells in Excel. “Duh!” you say. So how does Excel ensure that the cells you want to merge don’t overlap any existing merged cells? How do you check programmatically that the cells you want to merge won’t overlap any existing merged cells? Well, that was the problem I faced while writing my […]

May

Spreadsheet Open XML V2

I was rushing to get this out. The updated version of my programming guide is out! I first launched it on 17 Jan this year, so I was hoping to meet the personal-and-unseen deadline of 17 May, so it’s a nice 4 months interval. Ah well, I’m the only one who cares anyway… I’ve added […]

Feb

Built-in styles for Excel Open XML

So a blog reader, Sebastien, once asked about built-in style numbers used in Open XML for Excel. I’m sorry to break the news to you. It’s not straightforward to use those built-in styles. What are these built-in styles? Check these out: For instance, Sebastien was asking how to use the “Good” and “Bad” built-in styles. […]

Feb

Man, spreadsheet code libraries are expensive!

I did more market research on commercial code libraries that create, edit or otherwise manipulate Excel spreadsheets (specifically using .NET languages (C# *ahem*)). Mother of columns and rows, they’re expensive! The mid-range products are about US$ 500 and the high-end products start at about US$ 1000. And that’s just the per-developer license. If you have […]