Export SQL to Excel

7

How can i export my data from SQL server 2008 into Excel 2010 or later ?

i have tried on SQL way:

sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 0;
GO
RECONFIGURE;
GO
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\testing.xls;Extended Properties=EXCEL 12.0;HDR=YES', 
'SELECT NO_ORDRE, Date FROM [Sheet1$]') 
SELECT [NO_ORDRE], GETDATE() FROM ORDRE
GO

Unfortuntely i receive error: The OLE DB provider 'Microsoft.Jet.OLEDB.4.0' can not be used for distributed queries because the provider is configured to run in STA mode.

and then i tried on C# way:

 public class ExportToExcel
    {
        private Excel.Application app;

        private Excel.Workbook workbook;
        private 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 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 query, string sheetName)
        {
            SqlDataReader myReader = null;
            try
            {
                using (var connectionWrapper = new Connexion())
                {
                    var connectedConnection = connectionWrapper.GetConnected();
                    Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets.Add(Missing.Value, Missing.Value, 1, Excel.XlSheetType.xlWorksheet);

                    worksheet.Name = sheetName;
                    previousWorksheet = worksheet;

                    SqlCommand myCommand = new SqlCommand(query, connectionWrapper.conn);

                    myReader = myCommand.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(Excel.Worksheet worksheet, int row, int col, string htext)
        {
            worksheet.Cells[row, col] = htext;
        }

        public void addData(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;

            while (System.IO.File.Exists(folderPath + fileName + ext))
            {
                fileName = fileNameBase + counter;
                counter++;
            }

            fileName = fileName + ext;

            string filePath = folderPath + fileName;

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

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

            }
        }



    }

unfortunately i got error: Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005 Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)).

Any idea how can i export SQL to Excel ?

c#
sql-server-2008
excel
asked on Stack Overflow Dec 20, 2011 by user609511 • edited Dec 20, 2011 by TheBoyan

9 Answers

9

Your best bet might be to just write it out to a CSV. Excel registers itself as the file handler for CSV files, so it will open in excel by default.

For example:

private void SQLToCSV(string query, string Filename)
{

    SqlConnection conn = new SqlConnection(connection);
    conn.Open();
    SqlCommand cmd = new SqlCommand(query, conn);
    SqlDataReader dr = cmd.ExecuteReader();

    using (System.IO.StreamWriter fs = new System.IO.StreamWriter(Filename))
    {
        // Loop through the fields and add headers
        for (int i = 0; i < dr.FieldCount; i++)
        {
            string name = dr.GetName(i);
            if (name.Contains(","))
                name = "\"" + name + "\"";

            fs.Write(name + ",");
        }
        fs.WriteLine();

        // Loop through the rows and output the data
        while (dr.Read())
        {
            for (int i = 0; i < dr.FieldCount; i++)
            {
                string value = dr[i].ToString();
                if (value.Contains(","))
                    value = "\"" + value + "\"";

                fs.Write(value + ",");
            }
            fs.WriteLine();
        }

        fs.Close();
    }
}
answered on Stack Overflow Dec 20, 2011 by John Koerner • edited Dec 20, 2011 by John Koerner
6

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;
    }
}
answered on Stack Overflow Jun 24, 2016 by atik sarker • edited Feb 13, 2018 by imsome1
3

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());

        }
    }

}
answered on Stack Overflow Jan 24, 2014 by TomG • edited Feb 16, 2017 by Kiquenet
0

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

answered on Stack Overflow Dec 20, 2011 by Standage
0

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

answered on Stack Overflow Dec 20, 2011 by Jesse
0

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();
        }

    }
answered on Stack Overflow Jul 16, 2015 by Muhi Dean • edited Jul 16, 2015 by Muhi Dean
0
 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");
            }
        }
answered on Stack Overflow Aug 14, 2018 by Rohan • edited Aug 14, 2018 by Suraj Rao
0
                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();
            }
answered on Stack Overflow Apr 24, 2019 by AMAR PANRAY • edited Apr 24, 2019 by AMAR PANRAY
-1

Bind your data into a grid view....and use the following code .....

protected void ImageButton1_Click1(object sender, ImageClickEventArgs e)
{

    string attachment = "attachment; filename=Contacts.xls";
    Response.ClearContent();

    Response.AddHeader("content-disposition", attachment);
    Response.ContentType = "application/ms-excel";

    StringWriter sw = new StringWriter();HtmlTextWriter htw = new HtmlTextWriter(sw);
    GridView2.RenderControl(htw);

    Response.Write(sw.ToString());

    Response.End();

}
answered on Stack Overflow Dec 20, 2011 by Darshan • edited Feb 13, 2018 by imsome1

User contributions licensed under CC BY-SA 3.0