Are you looking for a reference manual on creating Microsoft Excel spreadsheets using C#/VB.NET and Open XML SDK? Just to be sure, see if the following applies to you:
- You need to automate the creation of Excel spreadsheets with the .xlsx file extension (Excel 2007 and later)
- You can’t (or prefer not to) install Microsoft Office (on your web servers or target machines) and thus have no Excel software
- You can’t (or prefer not to) use third party spreadsheet code libraries (Open XML SDK doesn’t count, since it’s from Microsoft)
- You find commercial spreadsheet code libraries too expensive
- You find the software licensing for commercial spreadsheet code libraries too confusing and complicated
- You can’t (or prefer not to) use Visual Studio Tools for Office
- You’re confused about the Open XML format
We had to create Excel spreadsheets on a server where we did not have and did not want to install MS Office. With very little information on the Open XML SDK available anywhere, Vincent’s programming guide was a real life saver for us. We could not have done it without it.
Gerard van de Ven
Vincent’s book is an excellent resource for the trickier aspects of Excel Open Xml. If you need to know how to add images to your spreadsheet, style columns and all the other things that the usual examples just gloss over this is money well spent. My time savings more than covered the cost plus Vincent throws in free technical support! Great Value.
Director, Information Age Ltd
When I first started working with Openxml it turned out to be a pretty overwhelming task. There is not much documentation and support available online. I was able to easily create an XLSX file but was not really clear on what to do after that. After purchasing and reading thru the Spreadsheet OpenXML from Scratch book I was able to move along with the coding at a faster pace. But more importantly than the book itself was the support that I received from Vincent himself. Every time I had a question or problem he replied immediately and was able to help me with what I needed to do.
We now have converted a handful of Excel applications to openxml and our goal is to migrate all of our server side excel applications to openxml by the end of the first quarter of next year 2012.
Director of Information Systems
Hapag-Lloyd America Inc
Piscataway , NJ
Does this sound like you?
You want to create spreadsheets for your users, your clients, your customers. You want to do it securely and without relying too much on an external vendor. You want to do it inexpensively. You want to do it without any hidden or complicated licensing fees and support fees haunting you after you launch your product or release your software.
Then you will benefit from this programming guide because:
- You can create spreadsheets without installing Microsoft Office
- You will learn to create spreadsheets with common features in step-by-step detail
- You don’t have to worry about license costs (because there aren’t any)
- You don’t have to worry about how you can use the source code (because there are no limits. Use it in a commercial product.)
- You get to learn programming tips and tricks (that’s got nothing to do with spreadsheets or Open XML)
- You don’t have to worry about a third-party vendor dropping their support (Open XML SDK is from Microsoft)
I know exactly what you’re going through because I was a programmer who worried about those problems.
Some time in the past, I was working in a telecommunications company in Singapore. I was in an internal IT department supporting the billing department. Reports generated from the software I maintained get a lot of attention, since they contain a lot of financial figures and data transfer numbers.
As you can imagine, budget was tight. And third party libraries had to be vetted to make sure that they’re absolutely needed before buying them. And they had to be safe because of the financial figures. And the department didn’t want to keep track of unnecessary licensing and support contracts.
So it came to pass that I had to generate Excel spreadsheets for the reports. Faced with the problem of a practically non-existent budget, no reliance on third party libraries or vendors, no complicated licensing schemes or support fees to bite the department some time in the future, I did the only thing reasonable.
I wrote code to generate spreadsheets with C# (on Visual Studio) and used the freely available Open XML SDK (provided by Microsoft).
I am offering you a programming guide that’s an accumulation of that experience and research. All you need is this guide, a compiler for C#/VB.NET (the free Visual Studio Express versions work too) and the free downloadable Open XML SDK. There’s no third party library here. Why? Because I’m giving you full source code that works with the Open XML SDK.
Your reference manual is a star, it helps me from digging inside out SDK document and fast-track me on to OpenXML without tearing my hair out. Your writing is wicked funny, I enjoy reading it on the way home.
Code safety and security
I know sometimes you can’t use any third-party libraries, and all code must be written by in-house programmers. Maybe it’s because you’re working in the government. Maybe it’s because you’re working in a financially-related department. In any case, the code must be safe and secure. “Black box” code where you don’t know the internals of library functions is unacceptable.
Well, I’m offering you full working source code. The only library you might have to worry about is the Open XML SDK, which is a software development kit provided by Microsoft, and not so much a library. If you can’t even trust Microsoft, then you have a bigger problem, because you’re working with the .NET Framework, right? The .NET Framework is provided by Microsoft.
So with full working source code and an SDK backed by a large software company, this is code as open to you as it can get.
I know from personal experience how easy it is to become overwhelmed by the complexity of OpenXML, but your guide takes away the pain by presenting simple, modular solutions to many of the common challenges that developers face when creating Excel spreadsheets from code. I highly recommend this to any C# or VB.NET developer who’s getting started with OpenXML, and especially to those who have already hit the frustration barrier.
I remember wasting several days searching for a solution to the “column width” problem, but it would have been a snap if I had known about the simple technique referred to as the Double Underscore hack.
Developer/Creator of ExtremeML
Learn at your own pace
You will learn how to create an Excel spreadsheet with the major functions available in Microsoft Excel. Create spreadsheets with different fonts, colours and styles. Add images to your worksheets. Change column widths and row heights to change the look.
Imagine creating a spreadsheet that your user typically create, automating that process and in a fraction of the time!
All of the chapters have accompanying C# and VB.NET working source code, so you can mix and match any code section to create your desired Excel spreadsheet.
Thanks for a good solid start in the labyrinth called OpenXML. The book has been very helpful.
Save hours of your time
Do you want to spend half a day searching for a solution? Do you want to spend 2 hours poring over the Open XML specifications?
Well, I’ve done all that for you. I’ve gone through the Open XML SDK help file. I’ve looked at the ECMA Open XML specifications. I’ve made sure the resulting source code works. You will learn the Open XML concepts and get the corresponding source code.
Save more than $700 from buying this guide!
Leading spreadsheet commercial code libraries cost over $700, some going into the 1,000′s of dollars. These libraries offer a lot of power. I want you to think about what you want to achieve. You might just want to use a fraction of that power, but you still have to pay the full price.
Then there are the different licensing payment options, the typical ones are charging by the number of developers and charging by the number of sites (websites, computer servers, clients). There are yearly renewal schemes and even licensing for the code library source code.
What I’m offering is a programming guide working with the Open XML SDK, a freely available software development kit from Microsoft. You get full working source code, and in-depth explanations of concepts used in that code. This means you will understand how the code works and how you can adapt it to your needs.
And there are no licensing fees here. You buy the guide, and you get to use the source code however you like, whether it’s a personal or commercial product. There are no renewal fees so you don’t have to worry about licensing payments biting you some time down the road.
Overall I’d say the guide was well structured, introduced new techniques at a solid pace, and covered most scenarios I can imagine a software engineer needing to cover when generating Excel docs on the fly. It’s very obvious you put a lot of time and anguish into digging through specs and existing spreadsheets… all the hard work shows! I’d definitely consider this (especially the example source code included) a valuable resource to have with me when trying to work with OOXML library, and the concepts introduced would almost certainly be useful if one wanted to figure out how the other document formats work, as well.
Just referring to the PDF has helped me to overcome tasks that I couldn’t figure out while searching through Microsoft’s documentation. I also love the comical humor you’ve included, which helps to make this an enjoyable read.
Here’s what you’re going to learn
- How to create a blank workbook (code template for future use)
- How to set a cell value
- How to add a worksheet
- How to set a defined name
- How to use shared strings
- How to use inline strings
- How to create a stylesheet (forced decimal display; 12.30 instead of 12.3)
- How to insert an image (it’s not as simple as you might think)
- How to do advanced styling (fonts, font sizes, font colour, background colour, underlined/bold/italicised text)
- How to insert multiple images (it’s just slightly more complicated than one image)
- How to set custom column widths
- How to calculate column widths (and presenting the Double Underscore hack)
- Advanced column and row settings (group columns and rows, or you can hide them)
- How to align text in a cell (such as how to rotate text in code) and merge cells
- How to insert comments
- How to set cell formulas
- How to set header and footer of a worksheet
- How to set page setup options (to prepare your file for printing)
- How to create multiple worksheets
- How to freeze and split panes
- How to use and style tables (and add indicators to sorting/filtering by colour and cell icons)
- How to easily write multiple sets of tabular data in one worksheet
- How to write large Excel files quickly
- How to work with an existing template file (and learn the one thing you need to know when updating cells)
- How to export your Open XML spreadsheet in ASP.NET
Not only do you learn exactly how to achieve the above, you’re going to see exactly how your resulting Excel spreadsheet looks like.
Learn to style cell contents
Include multiple images
Add multiple images on one Excel worksheet.
Use column and row settings
Group and collapse columns and rows. Learn to hide them too.
Align the contents of your cells
Set your text to be left-, centre- or right-aligned. Learn to vertically align your text too. Need to merge cells? Check.
And it’s not as easy as setting text in a cell…
Set formulas in the cells
You can even nest formulas.
Specify headers and footers
You can specify a different look for the first page, odd-numbered pages and even-numbered pages.
Translate page setup options to code equivalents
For printing options, there are a lot of class properties you can play with.
Create multiple worksheets in a workbook
Make sure you assign a unique ID for each sheet.
Harness the power of ice and freeze panes!
Stop the top row and left-most column from moving!
Make like a banana and split!
Split your worksheet view into 4. Learn the secret to splitting the worksheet nicely along the lines dividing columns and rows.
Learn to sort your tables by colour…
Or sort your tables by cell icon…
Or filter your table data…
The value “19″ of column E is filtered out.
Or do customised styles on your tables…
Make use of an existing template file for easy styling
You have an existing template file with fonts and styles, bar charts and graphs, cell formulas and images. You just want to fill it up with dynamic data. No problem. You’ll learn how to do that too.
I also include supporting information to help you in your quest to create Open XML spreadsheets.
- What English Metric Units are, and why you need to know them
- The leap year 1900 “bug” in Excel
- Excel built-in format styles (and why you shouldn’t use them)
- Page size enumerations (in case you want to print it)
- Order of code execution. Because if you write your code out of order, Excel will spit out so many errors you need Task Manager to shut it down.
- How to read Open XML SDK schema
- Learn about the Open XML SDK class structure (do you know your Relationship Part Class from a Root Class?)
“I can search for the information myself”
Of course you can. You’re a smart person. You’ll find out how to do exactly what you need to do. Eventually.
I’m offering you this neat little guide right here, right now. I explain all the relevant concepts so you understand what’s going on. I tell you the little things to take note, the problems you might face and the solutions to solve them. You also get full working source code in C# and VB.NET. You also get the Excel spreadsheet that’s the direct result of that source code so you can see what comes out.
You can save hours of work. How much is 1 day of productivity worth to you? How much is learning details of Open XML and coding with the SDK worth to you as a programmer? How much will missing the deadline for a customer cost you? How much will finding alternate solutions and not delivering your product to your client on time cost you?
Open XML SDK is a very advantageous tool if, for example, you want to programmatically create and provide Excel files from a web server to clients. But until January 2011, if you wanted to navigate in the use of Open XML SDK, you had the choice between two solutions: Either try to read the illegible and incomplete official documentation, either snoop around the blogs and web pages about it, and to waste a considerable time twisting some simple XML Excel files in the uncertain hope of understanding how this could be working.
Now, at last, here’s a structured and practical ebook accompanied with code examples, and giving clear explanations (when it is possible to give some).
Vincent’s blog, which is used as a base for this ebook, has ever helped me several times. Reading this ebook helped me to step back and consider possible improvements to my library of objects using Open XML SDK. Fiddling the XML files produced by Excel 2007 and Open XML SDK, I sometimes arrived at the same conclusions as Vincent’s, but you cannot imagine how reassuring it is to read it when it is written by someone else!
“I didn’t know that changing the SpreadsheetDocumentType is enough to transform a template into a simple workbook. This would have avoided me much unnecessary work!!!”
“So what do I really get? What do you mean by source code?”
You get a programming guide on Open XML concepts in the form of a PDF file. You also get accompanying source code in C# and VB.NET, in the form of .cs and .vb files.
There are no additional solution files or project files or XML files or anything like that. In terms of source code, for a particular chapter, there will only be one corresponding C# file and one corresponding VB.NET file, together with any resources such as images. This reduces the amount of information that’s not relevant to you. The Visual Studio solution and project files aren’t relevant to you, because you will have your own projects already. You just want to copy and paste the useful parts of code, which is what I give you.
“What does the source code look like?”
The source code is written to be compiled into a console program. The reason is that the console program has the least amount of boilerplate code, and thus allows you to have the maximum amount of flexibility in using that code. You can always copy relevant code sections into a Windows application or web application.
There’s also a minimum of custom written functions. This is because you will already have your own custom functions, with your own set of function parameters. Any custom written function in the guide is there to make it easier to write the code or for understanding. You are free to use those functions or pick it apart for your own functions.
The goal is for the source code to be as “flat” as possible, so you can follow each step of the way. It’s like unrolling a for loop, so you can follow the logic of what’s being done, and why it’s being done that way. Once you understand the concept and code, you can write your own code.
It’s designed this way because different businesses have different needs, with different development teams, and with different coding styles. With this design, with the parts laid out individually, you can combine them in your own style, for your own use and purposes.
“What can I do with the source code?”
Whatever you want. Use it in a personal project. Plug it into a commercial product. Give me credit for it. Don’t give me credit for it. It’s up to you.
The source code is written for simplicity and understanding. It’s also arranged in modules so you can easily pick and choose code parts and adapt it to your specific needs. Code comments are there to explain any possible confusion, and supplementary information is available in the accompanying PDF.
Write a function to encapsulate certain tasks you do on a regular basis. Streamline parts of the code into a function based on your business requirements. The source code is opened up for you to mix and match, to take apart and put together again.
My goal is that you solve your problem. If you succeed, then we all win.
“Do you have the source code in VB.NET?”
Yes! All sample code comes in both C# and VB.NET flavours, the 2 most common programming languages used in .NET applications. So you can work with whichever language you’re more familiar with.
The resulting Excel spreadsheets from the C# and VB.NET source code are identical. Well, other than the spreadsheet’s file name, so you can differentiate between the one from C# and the one from VB.NET.
The explanations of Open XML concepts are language agnostic, so you can go through the guide regardless of which language you’re familiar with.
“What’s the version of Open XML SDK you’re using?”
Open XML SDK 2.0
“Does this include charts?”
I have another reference manual for this.
“Do you have a free trial version?”
No. But I offer a 1 year money back guarantee. Buy the guide and test the source code in your projects. If they don’t meet your requirements or expectations, just let me know and I’ll give you a full refund. No hassle and no harm done.
Maybe you’re just a tired programmer looking for a solution to solve your problem. Your department is tight on the budget, and you’re this close to smashing your computer and throwing it at the next person who asks you “When is your program ready?”. You just want to solve that one pesky problem.
I offer a 1 year guarantee and support with the purchase. License fees? None, because it’s already included when you buy the guide. And there’s no need to renew any license.
You only need to pay once. Then you get the right to use the source code in a personal or commercial product. You don’t need to pay any renewal license fees. You can deploy the source code and your software product in an unlimited number of client machines. You don’t have to worry about how many developers can use it, because there’s no limit.
Just give it to your developers and start working on your product already. Have I mentioned you only need to pay once?
The price and what you’re going to get for it
This price is a fraction of what software companies charge for their commercial spreadsheet code libraries, anywhere between $500 and $5000. I’ve already listed what the guide will teach you. Think about what you really need to get done. A full commercial code library can be an overkill for what you’re trying to achieve.
You should also know that you’ll be getting full source code. You do know that software companies charge even more to give you their source code, right? With the source code, you can quickly fix any immediate problems. And you know exactly what goes into your software, which makes it more secure.
100% Money Back Guarantee
I’m offering you a 1 year guarantee. Within 1 year of your purchase, if for any reason, the source code doesn’t do what it’s supposed to do, tell me and I’ll give you your money back. If you don’t learn anything useful from this guide, tell me and I’ll give you your money back.
This guide is one of those rare products where it’s guaranteed to work. You know those dieting pills, slimming creams, exercise machines, business guides, self-improvement guides? I’m not saying they don’t work. Some of them probably work fantastically. What I’m saying is they deal with human potential. At its best, human potential is astoundingly effective and amazing. There are also cases of failure, because we’re human.
This is a programming guide. We’re dealing with computer potential. Much more predictable. If the executable produces a particular Excel spreadsheet on a computer, then every time you run it, the exact Excel spreadsheet is produced.
And I’m giving you full working source code. You can see exactly what goes on in your program. You can figure out exactly what’s going to be produced. It’s safe and secure. And it’s working source code. It’s guaranteed to work.
I want you to be satisfied with your purchase. I want you to get results. It’s risk-free to you.
Click the “Buy Now” button to get the guide
Spreadsheet Open XML From Scratch at USD 79
After you click on the button, you can pay using PayPal or your credit card (your payment information is not stored here). After your payment is processed, you will be sent to a web page where you can immediately download the guide and source code (they’re together in a zip file).
That’s it! Enjoy the guide.
P.S. In case you scrolled all the way down here to find out the price, you can get Spreadsheet Open XML From Scratch for USD 79. You get full working source code, and in-depth explanation of concepts used in the accompanying PDF (with more than 160 pages of information). You also get freedom to use the source code however you like, in a personal project or a commercial product. (Except for evil plans. Please, don’t do that.)