Generating Plain Vanilla XLSX Files From Data Table Results Using EPPlus

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:

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:

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:

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:

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:

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:

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:

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.