Man, spreadsheet code libraries are expensive!

February 12, 2011 By

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 more developers, or need a site-wide license, then you’re looking at thousands of dollars. If you consider license renewals, or subscription renewals, the options can boggle your mind.

So after thinking it through, I have to increase the price of my programming guide (Spreadsheet Open XML From Scratch). As ironic as it sounds, it might be the best thing I need to do. Simply put, my product is not expensive enough.

You will do well if you read some psychological books on human purchasing behaviour. It turns out, as studies show, there is a “perfect” price for every product (or service) for the intended audience (or market if you prefer) at a particular time period. Veer too far from that perfect price, either too cheap or too expensive, and you might need Herculean powers of persuasion to get the customer to buy.

And here I thought I was pricing my guide too expensive… I want to thank the people who’ve already bought my guide. You’re awesome.

So here’s the thing. I’m increasing the price of the guide from US$ 47 to US$ 97 (but please check the product page for current price since I might have changed it. Again…). And I’m doing it on 1 March. Why am I not doing it earlier? So you can buy the guide at the current price if you so choose. And because I’m busy writing my magazine and updates to the guide.

There are updates to the guide? Yes. And if you have anything you want to learn about creating spreadsheets from C# (or VB.NET) and with the Open XML SDK, now’s the time to tell me.

Then buy the guide at its current price, and get the updated version (soon). You get full working source code (not a code library, source code) that you can use with complete freedom (no license fees, no GPL’s, no license requirements, no need for attribution). Use the source code in a personal project or commercial product. No problem. And you get in-depth explanations of concepts taught for a particular task.

Buy it now before the price goes up. Leave comments here if you have specific Excel creation/editing tasks you want me to cover in the updated guide.

Filed Under: Offtopic Tagged With: , , , ,

Do you need to create Excel spreadsheets with C# or VB.NET?

January 17, 2011 By

Ahhh Microsoft Excel spreadsheets, an important and usually unavoidable tool in the day-to-day operations of an office worker. I too was inextricably bound to Excel during my professional programming career.

“Wait, weren’t you working as a programmer? Why were you using Excel like a ‘normal’ office worker?”

Ahhh, that’s because I work with said ‘normal’ office workers. One of my users then, a product manager, needed access to the database records. Due to circumstantial requirements, there’s no user interface that’s flexible enough to fix the problem. So the solution was to dump the records into an Excel file and send it to him. I would also send him a short analysis, and he would reply with the changes in the Excel file. I was to check the data integrity and then when it’s done, I would upload it into the database. As a result, I became well acquainted with basic Excel data manipulation.

Moving along, I worked with more users. Customer service officers, sales representatives, marketing staff, administrative staff, switch operators (I was in the telecommunications industry), technical staff, managers and directors. One common requirement from all of them was reports. There were 3 types: in text, PDF and Excel. By far the most popular type of report was the Excel reports. That’s because it’s easily editable and be manipulated by the users. The second reason was that the data required tended to be tabular, and Excel excelled (no pun intended) in that.

So to cut the story short (but the full story is in my programming guide. Yes, I’m going to tell you about it in a little while), I started looking into Open XML formats for Excel spreadsheets. My experiments yielded accepted results for my users. And I wrote about my experiments here. And some programmers found the articles useful. Then one fine day, it hit me; I should create a programming guide on it.

Spreadsheet Open XML From Scratch

Spreadsheet Open XML From Scratch

Before I tell you more, answer this question. “Do you need to create Excel spreadsheets with C# or VB.NET?” If so, you’re in luck because I created a guide just for you. It’s called Spreadsheet Open XML From Scratch. This guide will teach you how to create Excel spreadsheets using C# or VB.NET and the Open XML SDK (version 2.0). You can read the full details here.

The price is set at US$ 47. I decided on that price after thinking how much you will learn from it, and how much time and effort I’ve put into it. Half of the guide contains a compilation of the existing articles I’ve written, with the written material edited (some with new material added) and the source code cleaned up. The source code then was written with the Open XML SDK 2.0 CTP version. With the cleaned up source code, it’s written with the Open XML SDK 2.0 release version. There’s at least one class that will stop working if you use my old code. I’ve also written VB.NET versions of the C# code, so you can use that if you’re more comfortable with VB.NET.

The other half of the guide contains all new material and source code. As a bonus, there’s even a section teaching you how to use an Excel template to shortcut your spreadsheet generation process.

As an additional bonus, if you are one of the first 10 buyers, I will throw in a free 30 day consultation. You can ask me anything on the concepts involved, the source code, the Open XML SDK or Excel in general. This is in addition to the 1 year satisfaction and effectiveness guarantee, and 1 year unlimited free updates to the guide. This information is given to you because you’re reading my blog (it’s not on the product page). I’d offer it to more people, but there’s a physical limitation on my time.

You don’t have to buy my guide if you don’t want to for any reason. No problem, you can still read my blog for free. But if you’re interested, go check out my programming guide Spreadsheet Open XML From Scratch. Or you can buy the guide directly here. Thanks for reading.

Filed Under: Programming Tagged With: , , , , ,

First programming product almost done

December 11, 2010 By

As I mentioned earlier, I’m preparing a guide to creating Excel files using just code and the Open XML SDK. I’m calling it “Excel Open XML From Scratch” (nice name, huh?). All the source code (C# and VB.NET) had been written and tested. They work! Yay!

So now, I’m writing the accompanying PDF to explain the code and concepts. As mentioned before, I’m targeting a “before Christmas” launch. Now that we’re nearer the date, I can confidently tell you that it’s going to be sometime within the next few days. It will be released on 17 December. This gives you a week before Christmas to get this as a present for yourself or a programmer friend.

The price will be set at USD 17 (see actual price on product page), which I believe is a fair price considering that it will save you hours of work. Think about how much you’re paid per hour. Probably more than the price of this product. Hmm… I might even have to increase the price…

This is my first programming-related product, so I’m really excited by this. If you don’t really care about C#, VB.NET, Open XML or Excel, then I apologise. Just ignore any related posts for the immediate couple of weeks.

Filed Under: Curiosita Tagged With: , , , ,

Basic guide to Excel Open XML

November 22, 2010 By

Ok, I don’t know why I didn’t think of it before. So it appears that there’s some need to create Excel files using Open XML SDK 2.0. I’m halfway done with my guide to doing that. It will be sold as an ebook (packaged with source code). It’s a consolidation of some of the articles I’ve already written on the subject, together with some all new articles. Included topics are:

  • How to create a stylesheet and use it
  • How to insert an image into a worksheet
  • How to insert multiple images into a worksheet
  • More advanced styling options
  • Setting column widths
  • Setting column and row settings (such as grouping columns and hiding rows)
  • Text alignment in a cell

When I teach, I believe in giving you the simplest explanation and code. This allows you to create more complex functions if you want. If I complicate it for you, you might not know what the individual parts are. It’s like giving you all 26 alphabets and let you play with it. Much better if you learn the individual alphabets and it’s up to you to form words with them.

I haven’t finalised the cost of the product, nor the release date. But I’m planning a “before Christmas” launch, so you should see it in my store soon. This is a heads up in case you’re agonising over a Christmas present for a programmer (or yourself). The price will be fair, considering that I will be saving you tons of hours of research and pain. It will also come with both C# and VB.NET supporting code.

Having worked in a mid-sized company, I understand that sometimes your IT department doesn’t allow third party products (or doesn’t have the budget to buy the license). I don’t consider the Open XML SDK 2.0 to be a third party product. Besides, it’s by Microsoft (and free!). Most companies should be able to accept that. The source code will only need the Open XML SDK (and the .NET Framework of course) to work.

My product license will be fairly flexible, so you can use my code to create commercial products. And the license is tied to the product, not to number of computers or developers or servers (or whatever complicated rule being used by other software companies). You only need to buy 1 copy. (But tell your friends to buy as well because I need to eat)

So yeah, that’s all I have to say. If there’s a particular topic you want me to cover, there’s still some time. Comment below and I might consider adding it to the guide. If this turns out to be popular (as in I get to eat a couple of meals because of it), I’ve got plans to include more Excel Open XML goodness, and even expand to Word and PowerPoint. We’ll see…

Filed Under: Programming Tagged With: , , , , , , ,

How to insert multiple images in Excel Open XML

November 10, 2010 By

A blog reader, Fabrice Sacré, sent me an email with code about how to insert multiple images into an Excel file using Open XML. (I had fun playing Yanni’s “In The Mirror” on the virtual piano on his site.) He based it on my article on inserting just one single image into an Excel file. Based on his email address and signature, he works in the Red Cross. Wow! I’m honoured to be saving lives somehow, somewhere, even if in a long, circuitous and indirect manner. Possibly, I’m thinking too highly of myself and there’s no impact at all to the world at large, but I’m trying not to consider that.

Now when I started with the original code, I was in that “create xlsx, rename to zip, scrutinise xml till I fall asleep” mode. Frankly speaking, I had no idea what I was doing most of the time. And in this respect, the order of the individual code/data components was important, but I don’t know which one should come first (in fact, I still don’t). In particular, the code for inserting an image was before the code for inserting data. That worked fine for 1 image, but didn’t turn out so well when I tried with Fabrice’s code.

Ok, here’s the thing. Fabrice used an Excel template file (.xltx). He loaded that up into a new Excel file, then inserted images. Basically, you have an Excel file, then you obtained the worksheet you want, and then insert images to that worksheet. After I understood that (I had to ask him to send his full source code, because he only sent the image insertion function code), I made some changes to my original code to use his function code, and it worked! I’m not going to post the full source code he sent, because it might be sensitive (Red Cross! Saving lives, people!).

Here are the 3 images I used:
Cloud streaks
I like clouds and blue skies.

Crystallised pavillion
I used the “Crystalize” [sic] function in Paint.NET.

Dozing cat
Everyone loves cats, right?

So here’s the main body of the code:

static void Main(string[] args)
{
    string sFile = "ExcelOpenXmlMultipleImageInsert.xlsx";
    if (File.Exists(sFile))
    {
        File.Delete(sFile);
    }
    try
    {
        BuildWorkbook(sFile);
    }
    catch (Exception e)
    {
        Console.WriteLine(e.ToString());
    }
    Console.WriteLine("Program end");
}

protected static void BuildWorkbook(string filename)
{
    using (SpreadsheetDocument xl = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
    {
        WorkbookPart wbp = xl.AddWorkbookPart();
        WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
        Workbook wb = new Workbook();
        FileVersion fv = new FileVersion();
        fv.ApplicationName = "Microsoft Office Excel";
        Worksheet ws = new Worksheet();
        SheetData sd = new SheetData();

        WorkbookStylesPart wbsp = wbp.AddNewPart<WorkbookStylesPart>();
        wbsp.Stylesheet = CreateStylesheet();
        wbsp.Stylesheet.Save();

        Row r = new Row();
        r.RowIndex = 15;
        Cell c;
        c = new Cell();
        c.DataType = CellValues.String;
        c.StyleIndex = 1;
        c.CellReference = "G15";
        c.CellValue = new CellValue("We have multiple images!");
        r.Append(c);
        sd.Append(r);

        ws.Append(sd);
        wsp.Worksheet = ws;

        // It happens the resolution for the 3 images are 72 dpi
        // Images are 240 by 180 pixels
        // Adjust as needed

        InsertImage(ws, 0, 0, "cloudstreaks.jpg");
        // 2286000 = 180 (pixels) * 914400 (magic number) / 72 (bitmap resolution)
        InsertImage(ws, 0, 2286000, "crystallisedpavilion.png");
        // 3048000 = 240 (pixels) * 914400 (magic number) / 72 (bitmap resolution)
        InsertImage(ws, 3048000, 0, "dozingcat.jpg");

        wsp.Worksheet.Save();
        Sheets sheets = new Sheets();
        Sheet sheet = new Sheet();
        sheet.Name = "Sheet1";
        sheet.SheetId = 1;
        sheet.Id = wbp.GetIdOfPart(wsp);
        sheets.Append(sheet);
        wb.Append(fv);
        wb.Append(sheets);

        xl.WorkbookPart.Workbook = wb;
        xl.WorkbookPart.Workbook.Save();
        xl.Close();
    }
}

I used the CreateStyleSheet() from this article. I want you to note that the data creation (the single Cell containing the sentence) is now before the image insertion. My approach was originally to model what I see on an Excel worksheet. I would most probably see the image(s) at the top, then the (table of) data below. Hence I modeled the coding to be as such. Apparently, that didn’t work out so well.

[UPDATE: For the CreateStyleSheet() function, it turns out that the NumberFormats class is no longer available in Open XML SDK 2.0. It's now NumberingFormats. I've changed it for the source code for this article (but not for previous code articles)]

Fabrice’s approach was to use a template, then insert images to a particular worksheet. The data sheet was already created (via the template). My approach was to insert images and data in memory, so to speak, before committing the save to the worksheet/workbook parts. Basically, I was trying to create a worksheet out of thin air, add images, add data, and then plug it onto the workbook. Oh well, whatever works then…

Here’s the image insertion function Fabrice sent:

protected static void InsertImage(Worksheet ws, long x, long y, long? width, long? height, string sImagePath)
{
    try
    {
        WorksheetPart wsp = ws.WorksheetPart;
        DrawingsPart dp;
        ImagePart imgp;
        WorksheetDrawing wsd;

        ImagePartType ipt;
        switch (sImagePath.Substring(sImagePath.LastIndexOf('.') + 1).ToLower())
        {
            case "png":
                ipt = ImagePartType.Png;
                break;
            case "jpg":
            case "jpeg":
                ipt = ImagePartType.Jpeg;
                break;
            case "gif":
                ipt = ImagePartType.Gif;
                break;
            default:
                return;
        }

        if (wsp.DrawingsPart == null)
        {
            //----- no drawing part exists, add a new one

            dp = wsp.AddNewPart<DrawingsPart>();
            imgp = dp.AddImagePart(ipt, wsp.GetIdOfPart(dp));
            wsd = new WorksheetDrawing();
        }
        else
        {
            //----- use existing drawing part

            dp = wsp.DrawingsPart;
            imgp = dp.AddImagePart(ipt);
            dp.CreateRelationshipToPart(imgp);
            wsd = dp.WorksheetDrawing;
        }

        using (FileStream fs = new FileStream(sImagePath, FileMode.Open))
        {
            imgp.FeedData(fs);
        }

        int imageNumber = dp.ImageParts.Count<ImagePart>();
        if (imageNumber == 1)
        {
            Drawing drawing = new Drawing();
            drawing.Id = dp.GetIdOfPart(imgp);
            ws.Append(drawing);
        }

        NonVisualDrawingProperties nvdp = new NonVisualDrawingProperties();
        nvdp.Id = new UInt32Value((uint)(1024 + imageNumber));
        nvdp.Name = "Picture " + imageNumber.ToString();
        nvdp.Description = "";
        DocumentFormat.OpenXml.Drawing.PictureLocks picLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks();
        picLocks.NoChangeAspect = true;
        picLocks.NoChangeArrowheads = true;
        NonVisualPictureDrawingProperties nvpdp = new NonVisualPictureDrawingProperties();
        nvpdp.PictureLocks = picLocks;
        NonVisualPictureProperties nvpp = new NonVisualPictureProperties();
        nvpp.NonVisualDrawingProperties = nvdp;
        nvpp.NonVisualPictureDrawingProperties = nvpdp;

        DocumentFormat.OpenXml.Drawing.Stretch stretch = new DocumentFormat.OpenXml.Drawing.Stretch();
        stretch.FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle();

        BlipFill blipFill = new BlipFill();
        DocumentFormat.OpenXml.Drawing.Blip blip = new DocumentFormat.OpenXml.Drawing.Blip();
        blip.Embed = dp.GetIdOfPart(imgp);
        blip.CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print;
        blipFill.Blip = blip;
        blipFill.SourceRectangle = new DocumentFormat.OpenXml.Drawing.SourceRectangle();
        blipFill.Append(stretch);

        DocumentFormat.OpenXml.Drawing.Transform2D t2d = new DocumentFormat.OpenXml.Drawing.Transform2D();
        DocumentFormat.OpenXml.Drawing.Offset offset = new DocumentFormat.OpenXml.Drawing.Offset();
        offset.X = 0;
        offset.Y = 0;
        t2d.Offset = offset;
        Bitmap bm = new Bitmap(sImagePath);

        DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents();

        if (width == null)
            extents.Cx = (long)bm.Width * (long)((float)914400 / bm.HorizontalResolution);
        else
            extents.Cx = width;

        if (height == null)
            extents.Cy = (long)bm.Height * (long)((float)914400 / bm.VerticalResolution);
        else
            extents.Cy = height;

        bm.Dispose();
        t2d.Extents = extents;
        ShapeProperties sp = new ShapeProperties();
        sp.BlackWhiteMode = DocumentFormat.OpenXml.Drawing.BlackWhiteModeValues.Auto;
        sp.Transform2D = t2d;
        DocumentFormat.OpenXml.Drawing.PresetGeometry prstGeom = new DocumentFormat.OpenXml.Drawing.PresetGeometry();
        prstGeom.Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle;
        prstGeom.AdjustValueList = new DocumentFormat.OpenXml.Drawing.AdjustValueList();
        sp.Append(prstGeom);
        sp.Append(new DocumentFormat.OpenXml.Drawing.NoFill());

        DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture picture = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture();
        picture.NonVisualPictureProperties = nvpp;
        picture.BlipFill = blipFill;
        picture.ShapeProperties = sp;

        Position pos = new Position();
        pos.X = x;
        pos.Y = y;
        Extent ext = new Extent();
        ext.Cx = extents.Cx;
        ext.Cy = extents.Cy;
        AbsoluteAnchor anchor = new AbsoluteAnchor();
        anchor.Position = pos;
        anchor.Extent = ext;
        anchor.Append(picture);
        anchor.Append(new ClientData());
        wsd.Append(anchor);
        wsd.Save(dp);
    }
    catch (Exception ex)
    {
        throw ex; // or do something more interesting if you want
    }
}

protected static void InsertImage(Worksheet ws, long x, long y, string sImagePath)
{
    InsertImage(ws, x, y, null, null, sImagePath);
}

As of now, it supports only the image formats gif, jpg (jpeg) and png. However, there’s no stopping you from adding support for bmp, tiff, emf and other image formats supported by the ImagePartType enumeration.

Download the source code and the resulting Excel file.

Have fun.

There’s updated material and source code, together with more information on how to work with Open XML. Click here to find out more.

Filed Under: Programming Tagged With: , , , ,
« Older Posts
Newer Posts »