Programmatically export query table from MS Access

Question

Today i have a software which downloads xml data from the web and exports it to a MS Access DB in appropriate tables.

In MS Access DB i have created a query using the tables to make columns and rows as i want it to look like in Excel.

When i right-click on my new query table, and chose Export to Excel, i'm able to create an Excel file from that query.

Basically what i want to do is extend my software so that i can export the query to Excel programmatically with C#.

How can i do this?

---------------------------

other side things related i also would like to solve.

I'm getting green triangles above the numbers on left side, check the image postimg.org/image/t6tvfw2cz how can i remove from c#.

Is it possible to format the table look and design with c# code?

Is it poosible to add filters to the headers with c# code? – Mana 15 hours ago

Answer 1

Something like this should do it:

private static void ExportQuery(string databaseLocation, string queryNameToExport, string locationToExportTo)
{
    var application = new Application();
    application.OpenCurrentDatabase(databaseLocation);
    application.DoCmd.TransferSpreadsheet(AcDataTransferType.acExport, AcSpreadSheetType.acSpreadsheetTypeExcel12,
                                          queryNameToExport, locationToExportTo, true);
    application.CloseCurrentDatabase();
    application.Quit();
    Marshal.ReleaseComObject(application);
}

You would call it like this:

ExportQuery(@"C:\blah\blah.accdb", "myQuery", @"C:\blah\blah.xlsx");

Be sure to add these using statements:

using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Access;

Answer 2

Another solution is to create a CSV file which can be opened in Excel or other applications. See this function which returns a StringBuilder. This can then be saved as a file and opened via code as such:-

  string _filename = "a path" + "name.csv";
  File.WriteAllText(_filename, TheReturnedStringBuilder.ToString());
  System.Diagnostics.Process.Start(_filename);

The CSV creation

    /// <summary>
    /// Nvest Development Solutions
    /// Export the SQL data into a comma separated file
    /// </summary>
    /// <param name="ConnectionString">Valid SQL Server connection string</param>
    /// <param name="Sql">A valid SQL statement</param>
    /// <param name="TimeOut">A timeout specified in seconds</param>
    /// <returns>A stringbuilder with comma separated data</returns>
    public static StringBuilder ExportToCSVFormat(string ConnectionString, string Sql, int TimeOut = 30)
    {
        StringBuilder csv = new StringBuilder();
        string s = "";
        DataTable dt = SQL.BuildTableFromSQL(ConnectionString, Sql, TimeOut);

        //Add the titles
        foreach (DataColumn col in dt.Columns)
        {
            s += "\"" + col.ColumnName + "\", ";
        }
        if (s.Length > 1)
        {
            s = s.Remove(s.Length - 2);
        }
        csv.AppendLine(s);

        //Add the data
        foreach (DataRow row in dt.Rows)
        {
            object[] param = new object[dt.Columns.Count];
            int j = 0;
            s = "";

            foreach (DataColumn col in dt.Columns)
            {
                s += "{" + j + "";

                if (col.DataType == typeof(int) || col.DataType == typeof(long) || col.DataType == typeof(double))
                {
                    s += ":0},";
                }
                else
                {
                    s += ":},";
                }

                param[j] = row[col.ToString()];

                j++;
            }

            csv.AppendLine(string.Format(s, param));
        }

        return csv;
    }
}