Export SQL to Excel

C# SQL to Excel

Call you SP from Database

public DataTable GetDrugUtilizationReport_IndividualGenerateFile(long pharmacyId, DateTime from, DateTime to, long DrugNameId, int sortBy)
{
    var parameters = new Dictionary<string, object>
        {
            { "PharmacyId", pharmacyId },
            { "DateFrom", from },
            { "DateTo", to },
            { "DrugNameId", DrugNameId },
            { "SortBy", sortBy }
        };

    return ExecuteQuery("RPT_DrugUtilizationReportByIndividualGenerateFile", CommandType.StoredProcedure, parameters);
}

Use in your C# Code

private void OnCreateFileCommand(object obj)
{

    string path, parameterLabel;
    path = ConfigurationManager.AppSettings["VSSPORTEXELExportPath"];
    parameterLabel = FromDate.ToString("yyyy-MM-dd") + "_" + ToDate.ToString("yyyy-MM-dd");

    try
    {
        path =
            ExcelUtlity.ExportDataToExcel(
                dataTable:
                    context.GetDrugUtilizationReport_IndividualGenerateFile(GlobalVar.Pharminfo.pharminfo_PK,
                        FromDate, ToDate, SelectedDrug != null ? SelectedDrug.drugnameid_PK : 0,
                        sortBy: SortBy + 1),
                directoryPath: path,
                fileName_withoutExt: "DrugUtilizationReport" + "__" + parameterLabel,
                skipComplexObjects: true,
                skipInheritedProps: true);

        DXMessageBox.Show("Data exported successfully at \"" + path + "\".", GlobalVar.MessageTitle,
            MessageBoxButton.OK, MessageBoxImage.Information);
    }
    catch (Exception ex)
    {
        string errorMessage = ExceptionHelper.ProcessException(ex);
        DXMessageBox.Show(errorMessage, GlobalVar.MessageTitle, MessageBoxButton.OK, MessageBoxImage.Error);
    }

}

Excel Utility

public static string ExportDataToExcel(DataTable dataTable, string directoryPath, string fileName_withoutExt, bool skipComplexObjects, bool skipInheritedProps, string[] skipProps = null)
{
    if (directoryPath[directoryPath.Length - 1] == '\\') // no need to check for >0 length. let it throw an exection for that
        directoryPath = directoryPath + "\\";

    using (var spreadSheet = new SpreadsheetControl())
    {
        // Create new excel document and import the datatable to the worksheet
        spreadSheet.CreateNewDocument();
        spreadSheet.BeginUpdate();
        var worksheet = spreadSheet.Document.Worksheets.ActiveWorksheet;
        worksheet.Import(source: dataTable, addHeader: true, firstRowIndex: 0, firstColumnIndex: 0);

        // applying style on header
        Range range = worksheet.Range["A1:" + worksheet.Columns[worksheet.Columns.LastUsedIndex].Heading+"1"];
        Formatting rangeFormatting = range.BeginUpdateFormatting();
        rangeFormatting.Fill.BackgroundColor = System.Drawing.Color.LightSteelBlue;
        rangeFormatting.Font.FontStyle = SpreadsheetFontStyle.Bold;
        range.AutoFitColumns();
        range.EndUpdateFormatting(rangeFormatting);

        spreadSheet.EndUpdate();
        fileName_withoutExt += ".xlsx";
        Directory.CreateDirectory(directoryPath); // if directory already exists, CreateDirectory will do nothing
        spreadSheet.SaveDocument(directoryPath + fileName_withoutExt, DocumentFormat.OpenXml);

        return directoryPath + fileName_withoutExt;
    }
}

Using Microsoft Office dll

public bool WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType)
{
    Microsoft.Office.Interop.Excel.Application excel;
    Microsoft.Office.Interop.Excel.Workbook excelworkBook;
    Microsoft.Office.Interop.Excel.Worksheet excelSheet;
    Microsoft.Office.Interop.Excel.Range excelCellrange;

    try
    {
        // Start Excel and get Application object.
        excel = new Microsoft.Office.Interop.Excel.Application();

        // for making Excel visible
        excel.Visible = false;
        excel.DisplayAlerts = false;

        // Creation a new Workbook
        excelworkBook = excel.Workbooks.Add(Type.Missing);

        // Workk sheet
        excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
        excelSheet.Name = worksheetName;

        excelSheet.Cells[1, 1] = ReporType;
        excelSheet.Cells[1, 2] = "Date : " + DateTime.Now.ToShortDateString();

        // loop through each row and add values to our sheet
        int rowcount = 2;

        foreach (DataRow datarow in dataTable.Rows)
        {
            rowcount += 1;
            for (int i = 1; i <= dataTable.Columns.Count; i++)
            {
                // on the first iteration we add the column headers
                if (rowcount == 3)
                {
                    excelSheet.Cells[2, i] = dataTable.Columns[i - 1].ColumnName;
                    excelSheet.Cells.Font.Color = System.Drawing.Color.Black;
                }

                excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString();

                //for alternate rows
                if (rowcount > 3)
                {
                    if (i == dataTable.Columns.Count)
                    {
                        if (rowcount % 2 == 0)
                        {
                            excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
                            FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black, false);
                        }

                    }
                }
            }
        }

        // now we resize the columns
        excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]];
        excelCellrange.EntireColumn.AutoFit();
        Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
        border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
        border.Weight = 2d;


        excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[2, dataTable.Columns.Count]];
        FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);


        //now save the workbook and exit Excel

        excelworkBook.SaveAs(saveAsLocation); ;
        excelworkBook.Close();
        excel.Quit();

        return true;
    }
    catch (Exception ex)
    {
        DXMessageBox.Show(ex.Message);
        return false;
    }
    finally
    {
        excelSheet = null;
        excelCellrange = null;
        excelworkBook = null;
    }

}

/// <summary>
/// FUNCTION FOR FORMATTING EXCEL CELLS
/// </summary>
/// <param name="range"></param>
/// <param name="HTMLcolorCode"></param>
/// <param name="fontColor"></param>
/// <param name="IsFontbool"></param>
public void FormattingExcelCells(Microsoft.Office.Interop.Excel.Range range, string HTMLcolorCode, System.Drawing.Color fontColor, bool IsFontbool)
{
    range.Interior.Color = System.Drawing.ColorTranslator.FromHtml(HTMLcolorCode);
    range.Font.Color = System.Drawing.ColorTranslator.ToOle(fontColor);
    if (IsFontbool == true)
    {
        range.Font.Bold = IsFontbool;
    }
}

I have modified code which was given above as and is working. Edit according to your requirements

namespace ExcelExport
{

public class ExportToExcel
{
    string strCon = ConfigurationManager.ConnectionStrings["SafewayGVDemoDBContext"].ConnectionString;
    private Microsoft.Office.Interop.Excel.Application app;
    private Microsoft.Office.Interop.Excel.Workbook workbook;
    private Microsoft.Office.Interop.Excel.Worksheet previousWorksheet;
    // private Excel.Range workSheet_range;
    private string folder;

    public ExportToExcel(string folder)
    {

        this.folder = folder;
        this.app = null;
        this.workbook = null;
        this.previousWorksheet = null;
        // this.workSheet_range = null;

        createDoc();
    }

    private void createDoc()
    {
        try
        {
            app = new Microsoft.Office.Interop.Excel.Application();
            app.Visible = false;
            workbook = app.Workbooks.Add(1);
        }
        catch (Exception excThrown)
        {
            throw new Exception(excThrown.Message);
        }
        finally
        {
        }
    }

    public void shutDown()
    {
        try
        {
            workbook = null;
            app.Quit();
        }
        catch (Exception excThrown)
        {
            throw new Exception(excThrown.Message);
        }
        finally
        {
        }
    }

    public void ExportTable(string procName, string sheetName)
    {
        SqlDataReader myReader = null;
        try
        {

            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets.Add(Missing.Value, Missing.Value, 1, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
            using (SqlConnection Sqlcon = new SqlConnection(strCon))
            {
                SqlCommand cmd = new SqlCommand();
                Sqlcon.Open();
                cmd.Connection = Sqlcon;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = procName;
                cmd.Parameters.Add(new SqlParameter("@pvchAction", SqlDbType.VarChar, 50));
                cmd.Parameters.Add("@pIntErrDescOut", SqlDbType.Int).Direction = ParameterDirection.Output;
                cmd.Parameters["@pvchAction"].Value = "select";
                worksheet.Name = sheetName;
                previousWorksheet = worksheet;

                myReader = cmd.ExecuteReader();

                int columnCount = myReader.FieldCount;

                for (int n = 0; n < columnCount; n++)
                {
                    //Console.Write(myReader.GetName(n) + "\t");
                    createHeaders(worksheet, 1, n + 1, myReader.GetName(n));
                }

                int rowCounter = 2;
                while (myReader.Read())
                {
                    for (int n = 0; n < columnCount; n++)
                    {
                        //Console.WriteLine();
                        //Console.Write(myReader[myReader.GetName(n)].ToString() + "\t");
                        addData(worksheet, rowCounter, n + 1, myReader[myReader.GetName(n)].ToString());
                    }
                    rowCounter++;
                }
            }

        }

        catch (Exception e)
        {
            Console.WriteLine(e.ToString());

        }
        finally
        {
            if (myReader != null && !myReader.IsClosed)
            {
                myReader.Close();
            }
            myReader = null;
        }

    }

    public void createHeaders(Microsoft.Office.Interop.Excel.Worksheet worksheet, int row, int col, string htext)
    {
        worksheet.Cells[row, col] = htext;
    }

    public void addData(Microsoft.Office.Interop.Excel.Worksheet worksheet, int row, int col, string data)
    {
        worksheet.Cells[row, col] = data;
    }

    public void SaveWorkbook()
    {

        String folderPath = @"C:\My Files\" + this.folder;

        if (!System.IO.Directory.Exists(folderPath))
        {
            System.IO.Directory.CreateDirectory(folderPath);
        }

        string fileNameBase = "db";
        String fileName = fileNameBase;
        string ext = ".xlsx";
        int counter = 1;
        //System.IO.File.Open(folderPath + fileName + ext, System.IO.FileMode.Open);
        while (System.IO.File.Exists(folderPath + @"\"+ fileName + ext))
        {
            fileName = fileNameBase + counter;
            counter++;
        }

        fileName = fileName + ext;

        string filePath = folderPath +@"\"+ fileName;

        try
        {
            workbook.SaveAs(filePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

        }
        catch (Exception e)
        {
            Console.WriteLine(e.ToString());

        }
    }

}

2 simple options would be:

1) to use the SQL server import and export wizard, which you can use to Export any table from your database in Excel (Just make sure the mapping is correct)

2) Would be just to run your sql statement and then in your results window below, select all and right click and do a 'Copy with Headers' and then just paste the results in Excel

Since you're using Excel 2010 the easiest solution is to download PowerPivot from Microsoft and execute the SQL query directly. This will create a refreshable data connection that pulls the data from the Query into a pivot table.

http://www.microsoft.com/bi/en-us/Solutions/Pages/PowerPivot.aspx

This is hundred percent working for VS-2013 Premium for C# for Coded UI testing. just copy and paste the code That's it. Its working for SQL Server database You can save the data into xls, xlsx, or csv and can use that same csv for parameterization. you need to install following packages to make it work.

using System.Data.SqlClient

using Excel=Microsoft.Office.Interop.Excel

using SQL = System.Data

///***Copy from here and paste it under / To generate code for this test, select "Generate Code for Coded UI Test" from the shortcut menu and select one of the menu items.

    SqlConnection cnn;
        string connectionstring = null;
        string sql = null;
        string data = null;
        int i = 0;
        int j = 0;



        ////*** Preparing excel Application

        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;

        ///*** Opening Excel application

        xlApp = new Microsoft.Office.Interop.Excel.Application();
        xlWorkBook = xlApp.Workbooks.Open(@"C:\Users\MM18100\Documents\Visual Studio 2013\Projects\SQL\SQL\Book1.csv");
        xlWorkSheet = (Excel.Worksheet)(xlWorkBook.ActiveSheet as Excel.Worksheet);


        ////*** It will always remove the prvious result from the CSV file so that we can get always the updated data
        xlWorkSheet.UsedRange.Select();
        xlWorkSheet.UsedRange.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
        xlApp.DisplayAlerts = false;
        //xlWorkBook.Save();

        /////***Opening SQL Database

        connectionstring = "Integrated Security = SSPI;Initial Catalog=Exascale; Data Source=DCNA-Q-SQL-07;";
        cnn = new SqlConnection(connectionstring);
        cnn.Open();

        ////** Write your Sql Query here
        sql = "SELECT  TOP 10 [FirstName],[MiddleName],[LastName],[Email],[AltEmail],[Phone],[AltPhoneNumber],[Mobile],[Fax],[CompanyName],[AuthorizedUserName],[AuthorizedUserPhone],[CreatedDate],[ModifiedDate],[VERSION],[LanguageID],[TaxID],[CustomerType]FROM [Exascale].[dbo].[Customer] Where [FirstName] = 'Automation'";

        ///*** Preparing to retrieve value from the database
        SQL.DataTable dtable = new SQL.DataTable();

        SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
        SQL.DataSet ds = new SQL.DataSet();
        dscmd.Fill(dtable);

        ////*** Generating the column Names here

        string[] colNames = new string[dtable.Columns.Count];

        int col = 0;

        foreach (SQL.DataColumn dc in dtable.Columns)
            colNames[col++] = dc.ColumnName;

        char lastColumn = (char)(65 + dtable.Columns.Count - 1);

        xlWorkSheet.get_Range("A1", lastColumn + "1").Value2 = colNames;
        xlWorkSheet.get_Range("A1", lastColumn + "1").Font.Bold = true;
        xlWorkSheet.get_Range("A1", lastColumn + "1").VerticalAlignment
                    = Excel.XlVAlign.xlVAlignCenter;

        /////*** Inserting the Column and Values into Excel file



        for (i = 0 ; i <= dtable.Rows.Count - 1; i++)
            {
                for (j = 0; j <= dtable.Columns.Count-1; j++)
                  {
                          data = dtable.Rows[i].ItemArray[j].ToString();
                          xlWorkSheet.Cells[i + 2, j + 1] = data;

                }
            }


        ///**Saving the csv file without notification.
            xlApp.DisplayAlerts = false;
            xlWorkBook.Save();

            //xlWorkBook.SaveAs("Book1.csv", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);



        xlWorkBook.Close(true, misValue, misValue);
        xlApp.Quit();

        releaseObject(xlWorkSheet);
        releaseObject(xlWorkBook);
        releaseObject(xlApp);


        ////MessageBox.Show("Excel file created , you can find the file C:\\Users\\MM18100\\Documents\\informations.xls");
    }

    private void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
            MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
        }
        finally
        {
            GC.Collect();
        }

    }

 private void button1_Click(object sender, EventArgs e)
        {

            string StartDate = Start_Date.Value.Date.ToString("MM/dd/yyyy").Replace("-", "/");
            string EndDate = End_Date.Value.Date.ToString("MM/dd/yyyy").Replace("-", "/");

            string LogFolder = @"C:\Log\";
            try
            {
                string StoredProcedureName = comboBox1.Text;

                SqlConnection SQLConnection = new SqlConnection();
                SQLConnection.ConnectionString = ConnectionString;   

                //Load Data into DataTable from by executing Stored Procedure
                string queryString =
                  "EXEC  " + StoredProcedureName + " @Ifromdate ='" + StartDate + "',@Itodate ='" + EndDate+"'";


                SqlDataAdapter adapter = new SqlDataAdapter(queryString, SQLConnection);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                DataTable DtValue = new DataTable();
                DtValue = (ds.Tables[0]);


            }

            catch (Exception exception)
            {
                // Create Log File for Errors
                using (StreamWriter sw = File.CreateText(LogFolder
                    + "\\" + "ErrorLog_" + datetime + ".log"))
                {
                    sw.WriteLine(exception.ToString());
                }
            }

        }

///JUST add ClosedXMl.dll
        public void DataTableToExcel(DataTable dt)
        {
            string FileName = "Records";
            string SheetName = "Records";
            string folderPath = "C:\\New\\";
            if (!Directory.Exists(folderPath))
            {
                Directory.CreateDirectory(folderPath);
            }
            using (XLWorkbook wb = new XLWorkbook())
            {
                wb.Worksheets.Add(dt, SheetName);
                wb.SaveAs(folderPath + "\\" + FileName + ".xlsx");
            }
        }

                ExcelPackage EP = new ExcelPackage();

                ExcelWorksheet Sheet = 
                EP.Workbook.Worksheets.Add("subscriptions");
                Sheet.Cells["A1"].Value = "Email";
                Sheet.Cells["A1"].Style.Font.Bold = true;

                Sheet.Cells["B1"].Value = "First Name";
                Sheet.Cells["B1"].Style.Font.Bold = true;

                Sheet.Cells["C1"].Value = "Middle Name";
                Sheet.Cells["C1"].Style.Font.Bold = true;

                Sheet.Cells["D1"].Value = "Last Name";
                Sheet.Cells["D1"].Style.Font.Bold = true;

                Sheet.Cells["E1"].Value = "Date Created";
                Sheet.Cells["E1"].Style.Font.Bold = true;

                Sheet.Cells["F1"].Value = "Subscribed";
                Sheet.Cells["F1"].Style.Font.Bold = true;

                var collection = MyRepository.GetSubscriptionsAll();
                int row = 2;
                foreach (var item in collection)
                {
                    Sheet.Cells[string.Format("A{0}", row)].Value = item.Email;
                    Sheet.Cells[string.Format("B{0}", row)].Value 
  =item.FistName;
                    Sheet.Cells[string.Format("C{0}", row)].Value 
  =item.MiddleName;
                    Sheet.Cells[string.Format("D{0}", row)].Value 
  =item.LastName;
                    Sheet.Cells[string.Format("E{0}", row)].Value =   
       .DateCreated.ToString();
                    Sheet.Cells[string.Format("F{0}", row)].Value = 
   (item.Subscribed == false 
         ? "No" : "Yes"); ;

                    row++;
                }

                Sheet.Cells["A:AZ"].AutoFitColumns();
                System.Web.HttpContext.Current.Response.Clear();
                System.Web.HttpContext.Current.Response.ContentType = 
      "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                System.Web.HttpContext.Current.Response.AddHeader("content- 
  disposition", 
      "attachment: filename=" + "ListofSubscribers.xlsx");

    System.Web.HttpContext.Current.Response.BinaryWrite(EP.GetAsByteArray());
                System.Web.HttpContext.Current.Response.End();
            }

Excel 2016 and newer comes with something called Power Query (Older versions will also work but require a separate install). With this tool you can pull data directly from a database into excel.

On the "Data" tab in Excel select "Get Data" --> "From Database" --> "From SQL Server database". Put in the database information and under "Advanced options" you can paste your SQL query in and pull the data directly in Excel. This works for many different database types, flat files and other sources.