Tag: aspose

The Dew Review – Aspose.Cells for .NET

Introduction

It is time for another development component review. Last June, I reviewed the Aspose.Email for .NET component and demonstrated how to work with email messages in PST files or via IMAP. Well, I am back again for a look at another Aspose package. This one is Aspose.Cells for .NET.

Aspose.Cells provides full access to hundreds of Excel file features without the need to install or distribute Microsoft Excel.

Aspose.Cells Features

For a full list of features visit the Aspose.Cells site. Here is a summary of some of the highlights.

File Manipulation

There are so many file formats supported by Aspose.Cells including Excel (XLS, XLSM, etc.), HTML, CSV, Tab Delimited and PDF. Developers can open/save files, encrypt saved files, convert Excel documents to PDF or even save a worksheets as a SVG file.  You can also manipulate the file properties of existing Excel files.

Need to capture part of a workbook into an image file? There are APIs available to export Charts and entire Worksheets to an image, which can then be saved or manipulated like any other bitmap in .NET.

Worksheets, Rows and Columns

Aspose.Cells has Worksheet APIs to add and remove worksheets from workbooks. New worksheets can also be added to an existing PDF file with these APIs. Within a sheet, developers can manipulate scrollbar visibility, tabs, zoom factor as well as freezing and splitting panes on a sheet.

You can also take advantage of the APIs for working with rows and columns on a sheet. Your application can insert, delete, copy, hide/unhide, and adjust height/width.

Data and Formatting

Aspose.Cells can import data into worksheets from many different sources, including:

  • Arrays and ArrayLists
  • Custom .NET Objects and Collections
  • DataTable / DataView / DataColumn
  • Manual Data Entry

Once data is in a worksheet, it can be sorted, accessed and searched upon.

Cells includes a formula engine which can run with formulas embedded in existing spreadsheets or with new formulas created at runtime.

Think of any kind of data formatting you can perform in a cell in Excel, and you can probably do it with these APIs… fonts, colors, text formatting… you name it.

Tables and Charts

Lists can easily be formatted as tables on a worksheet with Aspose.Cells. Once part of a table, the data can be styled, formatted, grouped, and summed.

Charts are one of the most powerful features in Excel and Aspose.Cells. I’ll be showing an example of the charting API in my sample application below. Additional features not shown in my app include 3D formatting, inserting controls into charts (labels, pictures, textboxes), and Excel Sparklines.

Multi-Platform Support

Not only are there tons of features in the Aspose.Cells library, but you can access those features from all of these platforms:

  • .NET Framework
  • PHP
  • Python
  • Mono

at-glace-diagram-Updated2

Sample App – Data and Pivots and Charts, Oh My!

Just like the last time I started out working on an application with Aspose, I immediately cracked open the Examples Dashboard to find out how to implement the features I needed for my app.

examples

Other NuGet Packages

I used a couple of other NuGet packages to help make my application development a little bit simpler.

MVVM Light – This is my go-to MVVM helper framework. It makes binding and messaging a breeze in WPF and other XAML applications.

CsvHelper – This package provides handy APIs for working with CSV files. Because I wanted to import my data from a CSV file into a .NET collection. I could have imported the CSV data directly with Aspose.Cells, but I wanted to see how the library worked with collections in case my data was coming from another source like a REST service or other API.

The App

The application itself is relatively simple. I first load some data from a CSV file which contains a list of sites and authors and the number of visits each had during a particular quarter. I load this into a List<AuthorSummary> collection with CsvHelper and import the collection to a sheet in a workbook I create in memory with Aspose.Cells.

// The path to the documents directory.
_dataDir = Path.GetFullPath("../../../Data/");

// Create directory if it is not already present.
bool IsExists = Directory.Exists(_dataDir);
if (!IsExists)
    Directory.CreateDirectory(_dataDir);

//Instantiating a Workbook object
_workbook = new Workbook();

//Obtaining the reference of the default first worksheet by passing its sheet index
Worksheet worksheet = _workbook.Worksheets[0];

var authorSummaries = new List<AuthorSummary>();

using (var csv = new CsvReader(new StreamReader("SitesChartSample.csv"), new CsvHelper.Configuration.CsvConfiguration { HasHeaderRecord = true, Delimiter = "," }))
{
    while (csv.Read())
    {
        var authorSummary = new AuthorSummary
                            {
                                Site = csv.GetField<string>(0),
                                Author = csv.GetField<string>(1),
                                Visits = csv.GetField<int>(2),
                                Quarter = csv.GetField<string>(3)
                            };

        authorSummaries.Add(authorSummary);
    }
}

var options = new ImportTableOptions { InsertRows = true };

worksheet.Cells.ImportCustomObjects(authorSummaries, 1, 0, options);

The next step is to create a pivot table. I want a better visualization of the number of visits each site is getting per quarter, but I still want to see the breakdown by author. I add a new PivotTable object with Aspose.Cells and point it to the range of cells to use as the source data. I then tell it which of the fields to use as the pivot Rows, Columns and Data.

Aspose.Cells.Pivot.PivotTableCollection pivotTables = worksheet.PivotTables;
int index = pivotTables.Add("=A2:D26", "F2", "VisitsBySiteAndQuarter");

//Accessing the instance of the newly added PivotTable
Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];

//Unshowing grand totals for rows.
pivotTable.RowGrand = false;

//Dragging the first field to the row area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 0);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 1);

//Dragging the second field to the column area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, 3);

//Dragging the third field to the data area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 2);

pivotTable.CalculateData();

The final step is to create a chart based on the summary data in the pivot table. I use Aspose.Cells to add a new Chart object to the worksheet and then add each series of data elements that is to be part of the chart.

//Adding a chart to the worksheet
int chartIndex = worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.Pyramid, 2, 12, 14, 18);

//Accessing the instance of the newly added chart
Aspose.Cells.Charts.Chart chart = worksheet.Charts[chartIndex];

//Adding SeriesCollections (chart data sources) to the chart
chart.NSeries.Add("H10:K10", false);
chart.NSeries[0].Name = "C#";
chart.NSeries.Add("H16:K16", false);
chart.NSeries[1].Name = "VB";
chart.NSeries.Add("H20:K20", false);
chart.NSeries[2].Name = "F#";
chart.NSeries.Add("H31:K31", false);
chart.NSeries[3].Name = "JavaScript";

chart.Title.Text = "Quarterly Visits By Site";

One last bit I threw in was some styling/formatting. I wanted to see how easily I could change the appearance of a chart. Most of this code is taken from one of the samples in the Examples Dashboard application installed with Aspose.Cells.

private void SetChartAppearance(Aspose.Cells.Charts.Chart chart)
{
    //Setting the foreground color of the plot area
    chart.PlotArea.Area.ForegroundColor = Color.Blue;

    //Setting the foreground color of the chart area
    chart.ChartArea.Area.ForegroundColor = Color.Yellow;

    //Setting the foreground color of the 1st SeriesCollection area
    chart.NSeries[0].Area.ForegroundColor = Color.Red;

    //Setting the foreground color of the area of the 1st SeriesCollection point
    chart.NSeries[0].Points[0].Area.ForegroundColor = Color.Cyan;

    //Filling the area of the 2nd SeriesCollection with a gradient
    chart.NSeries[3].Area.FillFormat.SetOneColorGradient(Color.Lime, 1, Aspose.Cells.Drawing.GradientStyleType.Horizontal, 1);

    //Get the CellsColor of SolidFill
    CellsColor cc = chart.NSeries[0].Area.FillFormat.SolidFill.CellsColor;

    //Create a theme in Accent style
    cc.ThemeColor = new ThemeColor(ThemeColorType.Accent6, 0.6);

    //Apply the them to the series
    chart.NSeries[0].Area.FillFormat.SolidFill.CellsColor = cc;
}

And here is the output of the chart when exported to an image and displayed on my WPF form:

chart output

Ok, so I’m no graphic designer. I am a developer after all.

Exporting Charts and Saving Files

When the WPF form loads and the ViewModel prepares the data for the chart, it then exports the chart to a Bitmap object which gets converted to a BitmapSource which can be bound to a WPF Image control on the form. I found this handy image conversion code on StackOverflow.

ChartImage = LoadBitmap(chart.ToImage());

[DllImport("gdi32")]
private static extern int DeleteObject(IntPtr o);

private static BitmapSource LoadBitmap(Bitmap image)
{
    IntPtr pointer = image.GetHbitmap();
    BitmapSource bitmapSource;

    try
    {
        bitmapSource = System.Windows.Interop.Imaging.CreateBitmapSourceFromHBitmap(pointer,
           IntPtr.Zero, Int32Rect.Empty,
           BitmapSizeOptions.FromEmptyOptions());
    }
    finally
    {
        DeleteObject(pointer);
    }

    return bitmapSource;
}

Two buttons on the form are bound to commands to save the workbook as either an Excel file (.xls) or PDF document. The code to perform each of these actions is similar and very intuitive. I also added some code to launch each file after saving so I could view the results.

private void SavePDF()
{
    //Save in Pdf format
    _workbook.Save(_dataDir + "book1.pdf", SaveFormat.Pdf);

    Process.Start(_dataDir + "book1.pdf");
}

private void SaveXLS()
{
    //Saving the Excel file
    _workbook.Save(_dataDir + "book1.xls");

    Process.Start(_dataDir + "book1.xls");
}

The full source code for this project can be found here. The trial Aspose.Cells product is fully functional but adds watermarks to all of the files created.

Summary

If you are in the market for a library to give your spreadsheet application a jump-start, Aspose.Cells should be at the top of your list. I found the APIs really intuitive and the documentation and examples are thorough and comprehensive. I have really enjoyed my second experience with an Aspose product.

 

Happy Coding!

 

 

Disclosure of Material Connection: I received one or more of the products or services mentioned above for free in the hope that I would mention it on my blog. Regardless, I only recommend products or services I use personally and believe my readers will enjoy. I am disclosing this in accordance with the Federal Trade Commission’s 16 CFR, Part 255: “Guides Concerning the Use of Endorsements and Testimonials in Advertising.”

The Dew Review – Taking a Look at the Latest Release of Aspose.Email

Introduction

I have been spending some time working with the latest Apose.Email for .NET. It has been twelve or thirteen years since I have written any email related code. Back in the early 2000s, I did a little bit of work with Visual Basic and the Outlook Collaboration Data Objects (CDO). We have come a long way since that time. Email in the cloud is now becoming the norm with Gmail and Office 365.

There’s still plenty of need for local and Exchange based email processing in business applications as well. I will get to one possible scenario in my own application below.

Latest Aspose.Email Release

The Aspose.Email library supports just about any email related activity imaginable. Here is just a handful of scenarios developers can code into their own applications with Aspose.Email:

Generate emails and send via SMTP
  • Embed objects in message body – Send emails with embedded images or documents.
  • Attach files – Attach files to an email as a user would from their email client.
  • Mail merge – Powerful support for mail merge and mass emailing.
  • iCalendar support – Read and manipulate calendar events via the iCalendar standard.
    • Receive POP3 mail
    • Work with IMAP mail sources
    • Authentication
    • Work with messages and folders
    • SSL support (for POP3 and SMTP)
    Message Files
    • EML/MSG/MHT formats – All common mail message formats are supported.
    • Work with files or streams – Open messages from disk or network streams.
    • Manipulate PST files – Create, read and manipulate Outlook PST files and their contents.
    MS Exchange Server
    • WebDav and Exchange Web Services support
    • Unified Messaging operations
    • Send emails and Meeting Invites
    Advanced support for recurrence
    • Easily and reliably calculate event recurrence
    • Suppoprt for iCalendar (RFC 2445)

    Detailed developer documentation for all of the Aspose.Email features is available online here.

    So, whether your application needs to work with Exchange, Outlook files, POP3/SMTP, or talk to Gmail via IMAP, Aspose.Email has APIs to help with each situation. There are also sample apps for each feature exposed in Aspose.Email to get developers started off on the right track.

    An IMAP Console Application

    There are dozens of sample applications installed along with Apose.Email. To get started, launch the Aspose Examples Dashboard:

    asposeexamples

    The example apps are grouped by feature set. Developers can view the code in the Sample Browser, launch the solution in C# or VB, or run the example app right from the Browser application.

    I decided to take a closer look at one of the IMAP samples. The one I chose was “Fetch Messages from IMAP Server and Save to Disk”, which does exacly what the name implies. It is a console application that connects to a Gmail account via IMAP, selects the Inbox folder and loops through all of the messages, saving each one to a local folder in the “.eml” format. Here is the complete code implementation for the app after a couple of ReSharper refactorings.

    public static void Main(string[] args)
{
    // The path to the documents directory.
    string dataDir = Path.GetFullPath("../../../Data/");
    Directory.CreateDirectory(dataDir);

    //Create an instance of the ImapClient class
    var client = new ImapClient
                 {
                     Host = "imap.gmail.com",
                     Username = "asposetest123@gmail.com",
                     Password = "F123456f",
                     Port = 993,
                     SecurityMode = ImapSslSecurityMode.Implicit,
                     EnableSsl = true
                 };

    try
    {
        client.Connect();
       
        //Log in to the remote server.
        client.Login();

        // Select the inbox folder
        client.SelectFolder(ImapFolderInfo.InBox);

        // Get the message info collection
        ImapMessageInfoCollection list = client.ListMessages();

        // Download each message
        for (int i = 0; i < list.Count; i++)
        {
            //Save the EML file locally
            client.SaveMessage(list[i].UniqueId, dataDir + list[i].UniqueId + ".eml");
        }

        //Disconnect to the remote IMAP server
        client.Disconnect();

        System.Console.WriteLine("Disconnected from the IMAP server");
    }
    catch (System.Exception ex)
    {
        System.Console.Write(ex.ToString());
    }
}

    It is simple and intuitive to use.

    The PST Archive Utility

    I didn’t have the time to write my own applications that use every aspect of the library, so I decided to take one set of features and focus there. For years, I have been meaning to organize my work-related PST files into yearly archives. In fact, I have one PST that covers nearly seven years of email. It is nearly 6gb in size and contains who knows how many thousands of items.

    I built a small utility that will read a selected PST file, iterate through all of its folders and move all items for the specified year into a new PST with the year prepended to the PST’s file name, mirroring the folder structure of the original PST. I decided to build the utility as a WPF application, but this could function nicely as a command line utility also.

    pst1

    Using the utility is rather straightforward, simply:

    1. Enter the full path to the PST to be read.
    2. Click ‘Open PST’.
    3. The available years will display. Select a year.
    4. Click ‘Process PST’.
    5. When complete, the status message will update to “New PST Created for Year xxxx”.

    The code to display the available years iterates through the folders and items, collecting the unique years into a List<int>. It then sorts them before setting the property in the ViewModel to which the Available Years ListBox is bound.

    /// <summary>
/// Gets all the years of items in a PST file.
/// </summary>
private void GetPstYears()
{
    if (String.IsNullOrWhiteSpace(PstPath) || !File.Exists(PstPath)) return;

    using (PersonalStorage mainPst = PersonalStorage.FromFile(PstPath, true))
    {
        CurrentStatus = "Processing Years...";

        List<int> years = GetAvailableYears(mainPst.RootFolder);
        years.Sort();
        Years.Clear();

        foreach (int year in years)
        {
            Years.Add(year);
        }

        CurrentStatus = "PST Ready";
    }
}

/// <summary>
/// Gets the available years in a specified Outlook folder.
/// </summary>
/// <param name="folder">The folder.</param>
/// <returns>A list of years.</returns>
private List<int> GetAvailableYears(FolderInfo folder)
{
    var years = new List<int>();

    foreach (MapiMessage message in
                folder.EnumerateMapiMessages().Where(message => !years.Contains(message.DeliveryTime.Year)))
    {
        years.Add(message.DeliveryTime.Year);
    }

    foreach (int subYear in from folderInfo in folder.EnumerateFolders()
        where folderInfo.HasSubFolders
        select GetAvailableYears(folderInfo)
        into subYears
        from subYear in subYears
        where !years.Contains(subYear)
        select subYear)
    {
        years.Add(subYear);
    }

    return years;
}

    Similarly, the code to move the messages for the selected year to the new PST, iterates the folder structure to find any matching items. 

    /// <summary>
/// Process a PST by moving items from a selected year to a new PST
/// while creating the same folder structure.
/// </summary>
private void ProcessPst()
{
    if (SelectedYearIndex < 0 || String.IsNullOrWhiteSpace(PstPath) || !File.Exists(PstPath)) return;

    int year = Years[SelectedYearIndex];

    using (PersonalStorage mainPst = PersonalStorage.FromFile(PstPath, true))
    {
        string newFileName = PstPath.Insert(PstPath.LastIndexOf("\\", StringComparison.Ordinal) + 1, year.ToString(CultureInfo.InvariantCulture));

        using (PersonalStorage pstWithYear = PersonalStorage.Create(newFileName, FileFormatVersion.Unicode))
        {
            ProcessSubfolders(mainPst.RootFolder, pstWithYear.RootFolder, year);
        }
    }

    CurrentStatus = String.Format("New PST Created for Year {0}", Years[SelectedYearIndex]);
}

/// <summary>
/// Processes the subfolders of a provided PST folder and adds items
/// from the specified year to the new folder provided.
/// </summary>
/// <param name="folder">The source folder.</param>
/// <param name="newParentFolder">The new folder.</param>
/// <param name="year">The year of items to move.</param>
private void ProcessSubfolders(FolderInfo folder, FolderInfo newParentFolder, int year)
{
    foreach (FolderInfo folderInfo in folder.EnumerateFolders())
    {
        FolderInfo newFolder = newParentFolder.GetSubFolder(folderInfo.DisplayName) ??
                               newParentFolder.AddSubFolder(folderInfo.DisplayName);

        if (folderInfo.HasSubFolders)
        {
            ProcessSubfolders(folderInfo, newFolder, year);
        }

        newFolder.AddMessages(folderInfo.EnumerateMapiMessages().Where(m => m.DeliveryTime.Year == year));

        if (newFolder.ContentCount == 0 && !newFolder.HasSubFolders && newFolder.DisplayName != "Deleted Items")
            newParentFolder.DeleteChildItem(newFolder.EntryId);
    }
}

    You can see that all of the PST manipulation is very intuitive. Everything I needed to know, I was able to quickly learn from the documentation and the sample applications. It feels as if the classes are a part of the .NET Framework. It is a very well written API.

    You can download the complete source code for the project here.

    Summary

    If you are working on any projects involving email processing or access, Aspose.Email can definitely simplify the code required to get the job done. I will definitely keep these libraries in mind for future projects and you should too.

    Happy coding!

    del.icio.us Tags: ,,

     

    Disclosure of Material Connection: I received one or more of the products or services mentioned above for free in the hope that I would mention it on my blog. Regardless, I only recommend products or services I use personally and believe my readers will enjoy. I am disclosing this in accordance with the Federal Trade Commission’s 16 CFR, Part 255: “Guides Concerning the Use of Endorsements and Testimonials in Advertising.”

    Mastodon
    github.com/alvinashcraft