[Xamarin Blog] Generate Excel Spreadsheets in Native Mobile Apps using FlexCel

MichaelBluesteinMichaelBluestein Michael BluesteinUSForum Administrator, Moderator, Xamarin Team Xamurai
edited January 16 in Component Store

Just posted: "Generate Excel Spreadsheets in Native Mobile Apps" on the Xamarin Blog.

Read the full article to learn how to use the FlexCel component to generate Excel spreadsheets from C# code in Xamarin applications.

Posts

  • JoergBattermannJoergBattermann Joerg Battermann DEBeta, University

    I've been using FlexCel for a large enterprise customer of mine for the past years in a couple projects and can only say it works incredibly well and even better, their support is top-notch, too - highly recommended!

  • ernstkuglerernstkugler Ernst Kugler DEMember

    is it possible to integrate pictures in the excel files???

  • AdrianGalleroAdrianGallero Adrian Gallero UYMember

    Hi,
    I am FlexCel's author. First of all thanks Michael and Joerg for the comments, I appreciate them a lot! :)

    About adding images, of course you can integrate pictures into Excel files, both xls or xlsx. FlexCel has a tool, APIMate, which can help you with this kind of questions, and which you can get from:
    APIMate for OSX: http://www.tmssoftware.com/flexcel/tools/net/ApiMate.dmg
    APIMate for Windows: http://www.tmssoftware.com/flexcel/tools/net/ApiMate.zip

    Once you get APIMate, create the file in Excel, add the image, save, open the file in APIMate, and it will tell you the code you need to create the file.
    should be similar to this
    The code I get is the following:
    XlsFile xls = new XlsFile(1, true);
    //Images
    using (FileStream fs = new FileStream("imagename.png", FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
    {
    TImageProperties ImgProps = new TImageProperties();
    ImgProps.Anchor = new TClientAnchor(TFlxAnchorType.MoveAndDontResize, 2, 13, 2, 16, 13, 13, 6, 208);
    ImgProps.ShapeName = "Picture 1";
    xls.AddImage(fs, ImgProps);
    }

    This is using the API. Using Reports, you would just drop an image in the template and name it something like "<#myimage>", then use FlexCelReport.AddValue("myImage", ByteArrayWithImage);

    You can find more information about images in reports in the example: Demo\Modules\20.Reports\65.Images
    (Note that you need to download the Windows version of FlexCel from our site to get this demo: http://www.tmssoftware.com/site/flexcelnet.asp If you are trying, instal the trial, if you buy in the component store, you get also a license to Windows FlexCel too, and you can get it from the registered users component in our page)

    A final note: You specify an image in Excel by specifying the starting cell, the offset to the starting cell, an the ending cell and offset to the ending cell. But you can also specify image width and height. I'll paste an answer I gave in FlexCel forums last week about image aspect ratios that might be useful here:

    Basically, you need to use the constructor of TClientAnchor that takes a width and a height, and FlexCel will calculate the ending rows and columns. (Excel always needs rows and columns, so TClientAnchor needs to calculate them.

    The line should be:
    TClientAnchor imgAnchor = new TClientAnchor(TFlxAnchorType.MoveAndResize, iImageRowStart, 0, 1, 0, imgHeight, imgWidth, xls);

    Where imgHeight and imgWidth are the height an width of the image in "pixels". I wrote "pixels" in quotes because Excel doesn't deal with real pixels, but with resolution independent pixels instead. An image might have 640 pixels width, but the width in the screen will depend on the screen resolution.
    So, now having this part, how do we calculate imgHeight and imgWidth.
    You want imgWidth to be 5 columns. So you'll need to loop over all columns, sum their width, and divide by ExcelMetrics.ColMult to get the row in pixels. Then to keep the aspect ratio, imgHeight should be originalimageheight * newWidth/OriginalImageWidth
    A full working example is below:
    private double GetWidth(ExcelFile xls, int col1, int col2)
    {
    double Result = 0;
    for (int c = col1; c < col2; c++)
    {
    Result += xls.GetColWidth(c, true);
    }

            return Result / ExcelMetrics.ColMult(xls);
    }

    private void TmpButton_Click(object sender, System.EventArgs e)
    {
    string file = "r:\\test.png";
    XlsFile xls = new XlsFile("r:\\test.xlsx", true);
    int imgColStart = 3;
    int imgColWidth = 5;
    int imgWidth = (int)(GetWidth(xls, imgColStart, imgColStart + imgColWidth));
    int imgHeight = 0;
    using (Image img = Image.FromFile(file))
    {
    if (img.Widht > 0) imgHeight = img.Height * imgWidth / img.Width;
    }
    int iImageRowStart = 2;
    TClientAnchor imgAnchor = new TClientAnchor(TFlxAnchorType.MoveAndResize, iImageRowStart, 0, imgColStart, 0, imgHeight, imgWidth, xls);

    xls.AddImage(file, new TImageProperties(imgAnchor, file));
    xls.Save("r:\\test2.xlsx");

    }
    regards,
    Adrian

  • JorgeIsaiasJorgeIsaias Jorge Isaias USMember

    Hello Adrian,

    Nice component, is it possible to create an excel file using your component from an existing populated Data base (on my app I´m using SQLite.Net)?. My App is on Xamarin.Android. I share with you a post a made asking for a component like your to complement my question.

    http://forums.xamarin.com/discussion/comment/46192#Comment_46192

    Thanks in advance.

  • AdrianGalleroAdrianGallero Adrian Gallero UYMember

    Hi,

    Yes, not only it is possible, this is what this component is mostly about. Getting data from database or business objects, and export them to Excel, csv, pdf or html.

    You have 2 ways to do it:
    1)Via only code. In this case, you would loop over the records in your dataset, and call SetCellValue() to fill an Excel file. For example, in pseudocode:

    XlsFile xls = new XlsFile(1, true);
    xls.SetCellValue(1,1,"REPORT");
    for (int row = 0; row < dataset.RecordCount; row++)
    {
    for (int col = ...)
    {
    xls.SetCellValue(row, col, databasevalue[row, col]);
    }
    }

    You can do most things you want with the code, for example protect the sheet, or freeze the header row so when you scroll down it is fixed. To know how to do all this stuff, you create a file in Excel, do the things you want (for example freeze a row), and then open it in APIMate (the links for downloading APIMate for windows or OSX are in the Getting started page). APIMate will tell you the C# code you need to recreate the file you created in Excel in FlexCel.

    2)Using Reports: This works a little different, and it is nice specially when you know the fields in advance that you want to export. For example, you could create an xls/x file in Excel and write in A2: <#Customer.Name>, in B2: <#Customer.Country>, etc, and then run a report using your SQLite db to fill in the tags. The advantage of the report approach is that your user can modify the template and add columns, or freeze the header row as in the case 1), but without touching the code. They only have to modify the template. I personally know some "power users" on some of my personal apps who learn how to create templates even if they can't code, and they can get some reports that I would not know how to do them. It is great because I send them basic reports, and then they can customize them as they wish. For example if the logo of the company changed, they just edit the template and change the logo.

    For an example on how to do reports, please take a look at the "langwars" iOS demo: It reads a dataset from StackOverflow (of from a local file, depending on your settings), and then dumps it into an xls file which is later converted to html so it can be displayed in a web browser component.

    But to get more information on how to use either reports or just code, you'll need to get the trial windows version of FlexCel from here:
    http://www.tmssoftware.com/site/flexcelnet.asp

    There are about 50 examples which we sadly can't convert all to iOS and android, so you'll have to look at them in Windows. But they translate virtually unchanged to iOS.

    Note also that if you decide to buy either on our site or in the component store, you'll get a license that is good for iOS, Android, Windows, OSX and very soon now Windows Store 8.1 (WinRT) and Windows Phone 8.

    Well, I could keep speaking for years about this stuff, so I'll stop now :) If you have any more specific doubts, please don't hesitate to ask.

Sign In or Register to comment.