Click here to Skip to main content
14,926,139 members
Articles / Programming Languages / C#
Tip/Trick
Posted 29 Aug 2016

Tagged as

Stats

8.7K views
5 bookmarked

Generating Plain Vanilla XLSX Files From Data Table Results Using EPPlus

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
30 Aug 2016CPOL1 min read
Using the EPPlus open source library, this article shows how to generate Open XML Files from any data table results by means of a Winforms utility

Spreadsheet Assembly Line

Using the EPPlus open source library (which is to Excel Interop what a Maytag washing machine is to boulders along the edge of a crocodile-infested river), this article will show how to create a utility that will turn any DataTable results into a spreadsheet file. This is a quick-and-dirty approach for queries that don't need or warrant custom tweaking and complex logic for the report generation.

First, you need to add EPPlus to your project; this is easy as gooseberry pie:

  1. Right-click the Project and select “Manage NuGetPackages…”
  2. Search for “EPPlus” and Install it

Add some boilerplate code to your Winforms app:

C#
private readonly int FIRST_ROW_HEIGHT = 28;
private readonly int FIRST_ROW_FONT_SIZE = 16;
private int COLUMN_HEADER_ROW_QUERY = 1;
private int COLUMN_HEADER_ROW_SP = 2;
private int FIRST_DATA_ROW_QUERY = 2;
private int FIRST_DATA_ROW_SP = 3;
private readonly int COLUMN_HEADER_FONT_SIZE = 13;
private readonly int COLUMN_HEADER_ROW_HEIGHT = 18;

private int colCount;
private SqlConnection conn;
private SqlCommand sqlcom;
private DataTable dtResults;
private ExcelWorksheet dataTable2Spreadsheet;

Note: This code is SQL Server-specific; if you want to use a different database, you will have to change the database-specific code accordingly.

Of course, you can change any of these values that you want to, if you prefer different sizes, etc.

Drop a TextBox and a button on your form, naming them "txtbxQuery" and "btnRunQuery".

Add a click event handler to the button like so:

C#
private void btnRunQuery_Click(object sender, EventArgs e)
{
    try
    {
        Cursor.Current = Cursors.WaitCursor;
        RunQuery();
    }
    finally
    {
        Cursor.Current = Cursors.Default;
    }
}

The RunQuery() method assumes this splendid appearance:

C#
private void RunQuery()
{
    string qry = txtbxQuery.Text;
    dtResults = Platypus.ExecuteQuery(qry, CommandType.Text);
    if (null == dtResults)
    {
        MessageBox.Show("no results");
        return;
    }
    if (dtResults.Rows.Count > 0)
    {
        GenerateAndSaveQueryResultsToSpreadsheet();
    }
}

If you are using SQL Server, you can use the following method called above to retrieve the data:

C#
internal static DataTable ExecuteQuery(string qry, CommandType cmdType)
{
    using (var ds = new DataSet())
    using (var connStr = new SqlConnection(YourConnStr))
    using (var cmd = new SqlCommand(qry, connStr))
    {
        cmd.CommandType = cmdType;
        cmd.CommandTimeout = EXTENDED_TIMEOUT;

        try
        {
            cmd.Connection.Open();
            new SqlDataAdapter(cmd).Fill(ds);
            return ds.Tables[0];
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        return null;
        }
}

Finally, the root of the matter, the crux of the biscuit, as far as generating the spreadsheet file goes, is the method that is called if any data was returned from the provided query statement:

C#
private void GenerateAndSaveQueryResultsToSpreadsheet()
{
    if (null == dtResults)
    {
        MessageBox.Show("no results");
        return;
    }
    int rowsReturned = dtResults.Rows.Count;
    if (rowsReturned > 0)
    {
        var fileName = "Query_" + DateTime.Now.ToString("yyyy-MM-dd_hh-mm-ss") + ".xlsx";
        var outputDir = @"C:\misc\";
        Directory.CreateDirectory(outputDir); 
        var file = new FileInfo(outputDir + fileName);
        using (var package = new ExcelPackage(file))
        {
            dataTable2Spreadsheet = package.Workbook.Worksheets.Add("Query");
            AddColumnHeadingRow(1);
            AddDataRows(2);
            dataTable2Spreadsheet.Cells.AutoFitColumns();
            Directory.CreateDirectory(PlatypusConstsAndUtils.GetUniqueFolder
                                     (PlatypusConstsAndUtils.SpreadsheetLandingSpot));
            String _uniqueFolder = PlatypusConstsAndUtils.uniqueFolder;
            string currentYYYYMMDDHHMM = PlatypusConstsAndUtils.GetYYYYMMDDHHMM();
            string filename = String.Format("{0}\\{1} - {2} generated at {3}.xlsx",
                        _uniqueFolder,
                        PlatypusConstsAndUtils.SpreadsheetLandingSpot,
                        SPName,
                        currentYYYYMMDDHHMM);
            if (File.Exists(filename))
            {
                File.Delete(filename);
            }
            Stream stream = File.Create(filename);
            package.SaveAs(stream);
            stream.Close();
            package.Save();

            MessageBox.Show(
                string.Format(@"Excel sheet has been generated. 
                  You can find it in {0}\{1}\YYYYMMDD_HHMM",
                  PlatypusConstsAndUtils.OUTPUT_DIRECTORY, 
                  PlatypusConstsAndUtils.SpreadsheetLandingSpot));
            }
        }
        else
        {
            MessageBox.Show("No rows returned");
        }
    }
}

Here, too, of course, you can change things to coincide with your preferences, such as the "outputdir" value, filename format, etc.

AddColumnHeadingRow(), probably not surprisingly, adds a row with column headings for the returned data:

C#
private void AddColumnHeadingRow(int row2Pop)
{
    colCount = dtResults.Columns.Count;
    List<string> colNames = new List<string>();
    for (int i = 0; i < colCount; i++)
    {
        colNames.Add(dtResults.Columns[i].ToString());
    }

    using (var columnHeaderRowRange = dataTable2Spreadsheet.Cells[row2Pop, 1, row2Pop, colCount])
    {
        columnHeaderRowRange.Style.Fill.PatternType = ExcelFillStyle.Solid;
        columnHeaderRowRange.Style.Fill.BackgroundColor.SetColor(Color.LightBlue);
        dataTable2Spreadsheet.Row(row2Pop).Height = COLUMN_HEADER_ROW_HEIGHT;
        columnHeaderRowRange.Style.Font.Bold = true;
        columnHeaderRowRange.Style.Font.Size = COLUMN_HEADER_FONT_SIZE;
    }

    int currentColumn = 1;
    foreach (string s in colNames)
    {
        using (var colHeaderCell = dataTable2Spreadsheet.Cells[row2Pop, currentColumn])
        {
            colHeaderCell.Value = s;
        }
        currentColumn++;
    }

    dataTable2Spreadsheet.View.FreezePanes(2, 2);
}

Finally, the deceptively simple AddDataRow() does the yeoman's work of populating the data to the sheet:

C#
private void AddDataRows(int firstDataRow)
{
    int rowToPop = firstDataRow;
    foreach (DataRow row in dtResults.Rows)
    {
        for (int i = 1; i <= colCount; i++)
        {
            var curVal = row[i-1].ToString();
            using (var genericCell = dataTable2Spreadsheet.Cells[rowToPop, i])
            {
                genericCell.Value = curVal;
            }
        }
        rowToPop++;
    }
}

Und damit basta, as our Teutonic teammates tend to tempestuously declaim.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

B. Clay Shannon
Founder Across Time & Space
United States United States
I am in the process of morphing from a software developer into a portrayer of Mark Twain. My monologue (or one-man play, entitled "The Adventures of Mark Twain: As Told By Himself" and set in 1896) features Twain giving an overview of his life up till then. The performance includes the relating of interesting experiences and humorous anecdotes from Twain's boyhood and youth, his time as a riverboat pilot, his wild and woolly adventures in the Territory of Nevada and California, and experiences as a writer and world traveler, including recollections of meetings with many of the famous and powerful of the 19th century - royalty, business magnates, fellow authors, as well as intimate glimpses into his home life (his parents, siblings, wife, and children).

Peripatetic and picaresque, I have lived in eight states; specifically, besides my native California (where I was born and where I now again reside) in chronological order: New York, Montana, Alaska, Oklahoma, Wisconsin, Idaho, and Missouri.

I am also a writer of both fiction (for which I use a nom de plume, "Blackbird Crow Raven", as a nod to my Native American heritage - I am "½ Cowboy, ½ Indian") and nonfiction, including a two-volume social and cultural history of the U.S. which covers important events from 1620-2006: http://www.lulu.com/spotlight/blackbirdcraven

Comments and Discussions

 
-- There are no messages in this forum --