Click here to Skip to main content
15,867,686 members
Articles / Web Development / ASP.NET

Save data to Excel file using Microsoft Excel

Rate me:
Please Sign up or sign in to vote.
4.57/5 (14 votes)
12 May 2012CPOL 33.9K   22   8
How to save data to excel file using Microsoft Excel

Sometimes we need to save data to Excel file using Microsoft Excel. To do that we might need to create the Excel file, need to create table on that with some columns and then save data to the table of Excel file. This is pretty easy enough. Have a look on that.

Save data to Excel table:

C#
private void InsertDataOnExcellTable(string submitttedExcellPath, Test submittedExcellData)
{
    if (!string.IsNullOrEmpty(submitttedExcellPath) && submittedExcellData != null)
    {
        if (!string.IsNullOrEmpty(submittedExcellData.FileName))
        {
            string fullFileName = submitttedExcellPath + Path.DirectorySeparatorChar + 
                                  submittedExcellData.FileName;
            string xlFileExtension = ".xls";
            if (!fullFileName.Contains(xlFileExtension))
            {
                fullFileName = fullFileName + xlFileExtension;
            }
            string connectionString = ConnectionString.GetConStr(fullFileName);
            if (!string.IsNullOrEmpty(connectionString))
            {
                if (this.CreateTableOnExcell(fullFileName, connectionString) == true)
                {
                    lock (new object())
                    {
                        System.Data.OleDb.OleDbConnection myConnection;
                        System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
                        string sql = null;
                        myConnection = new System.Data.OleDb.OleDbConnection(connectionString);
                        myConnection.Open();
                        myCommand.Connection = myConnection;
                        sql = "Insert into [Sheet1$] "
                            + "( "
                            + "[Title],[FirstName],[Surname],[Email],[TelePhoneNumber],[OrderNumber],[SubmissionDate]"
                            + " )"
                            + " values "
                            + "('" + submittedExcellData.Title + "','" 
                            + submittedExcellData.FirstName + "','" 
                            + submittedExcellData.SurName + "','" 
                            + submittedExcellData.Email + "','" 
                            + submittedExcellData.TelePhoneNumber + "','" 
                            + submittedExcellData.OrderNumber + "','" 
                            + submittedExcellData.SubmissionDate + "')";
                        myCommand.CommandText = sql;
                        myCommand.ExecuteNonQuery();
                        myConnection.Close();
                    }
                }
            }
        }
    }
}

Create table on Excell file,

C#
private bool CreateTableOnExcell(string submittedFullPath, string submittedConnStr)
{
    if (!string.IsNullOrEmpty(submittedConnStr) && !string.IsNullOrEmpty(submittedFullPath))
    {
        if (this.CreateExcellFile(submittedFullPath))
        {
            lock (new object())
            {
                OleDbConnection connExcel = new OleDbConnection(submittedConnStr);
                OleDbCommand cmdExcel = new OleDbCommand();
                cmdExcel.Connection = connExcel;
                System.Data.DataTable excelSchema = null;
                try
                {
                    ////Check if the Sheet Exists
                    connExcel.Open();
                    excelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                }
                catch
                {
                }
                finally
                {
                    connExcel.Close();
                }
                DataRow[] dr = excelSchema != null ? 
                  excelSchema.Select("TABLE_NAME = 'Sheet1'") : null;
                ////if not Create the Sheet
                if (dr == null || dr.Length == 0)
                {
                    string excelCommand = "CREATE TABLE";
                    string sheetName = "[Sheet1]";
                    ////All Column Name
                    StringBuilder tableColumn = new StringBuilder();
                    tableColumn.Append("(");
                    tableColumn.Append("Title varchar(20)");
                    tableColumn.Append(",");
                    tableColumn.Append("FirstName varchar(20)");
                    tableColumn.Append(",");
                    tableColumn.Append("Surname varchar(20)");
                    tableColumn.Append(",");
                    tableColumn.Append("Email varchar(20)");
                    tableColumn.Append(",");
                    tableColumn.Append("TelePhoneNumber varchar(20)");
                    tableColumn.Append(",");
                    tableColumn.Append("OrderNumber varchar(20)");
                    tableColumn.Append(",");
                    tableColumn.Append("SubmissionDate varchar(20)");
                    tableColumn.Append(")");
                    tableColumn.Append(";");
                    StringBuilder exelQuery = new StringBuilder();
                    exelQuery.Append(excelCommand);
                    exelQuery.Append(" ");
                    exelQuery.Append(sheetName);
                    exelQuery.Append(" ");
                    exelQuery.Append(tableColumn.ToString());
                    if (cmdExcel != null)
                    {
                        cmdExcel.CommandText = exelQuery.ToString();
                        connExcel.Open();
                        cmdExcel.ExecuteNonQuery();
                        connExcel.Close();
                    }
                }
            }
            return true;
        }
    }
    return false;
}

Create Excel file:

C#
private bool CreateExcellFile(string submittedFullPath)
{
    if (!string.IsNullOrEmpty(submittedFullPath))
    {
        lock (new object())
        {
            if (!File.Exists(submittedFullPath))
            {
                ////Ex = Microsoft.Office.Interop.Excel;
                Ex.Application exelApp = null;
                Ex.Workbook exelWorkBook = null;
                Ex.Worksheet exelWorkSheet = null;
                object misValue = System.Reflection.Missing.Value;
                try
                {
                    ////Create an Excell Application
                    exelApp = new Ex.ApplicationClass();
                    if (exelApp != null)
                    {
                        if (exelApp.Workbooks != null)
                        {
                            ////Add an Workbook to the current Excell Application
                            exelWorkBook = exelApp.Workbooks.Add(misValue);
                        }
                    }
                    if (exelWorkBook != null)
                    {
                        ////Getting Sheet1
                        exelWorkSheet = (Ex.Worksheet)exelWorkBook.Worksheets.get_Item(1);
                        if (exelWorkSheet != null)
                        {
                            //// Naming the Table Data Column
                            exelWorkSheet.Cells[1, 1] = "Title";
                            exelWorkSheet.Cells[1, 2] = "FirstName";
                            exelWorkSheet.Cells[1, 3] = "Surname";
                            exelWorkSheet.Cells[1, 4] = "Email";
                            exelWorkSheet.Cells[1, 5] = "TelePhoneNumber";
                            exelWorkSheet.Cells[1, 6] = "OrderNumber";
                            exelWorkSheet.Cells[1, 7] = "SubmissionDate";
                        }
                        ////Save the Excell file to its Specified location
                        exelWorkBook.SaveAs(submittedFullPath, Ex.XlFileFormat.xlWorkbookNormal, 
                          misValue, misValue, misValue, misValue, 
                          Ex.XlSaveAsAccessMode.xlExclusive, misValue, 
                          misValue, misValue, misValue, misValue);
                    }
                }
                catch
                {
                }
                finally
                {
                    if (exelWorkBook != null)
                    {
                        exelWorkBook.Close(true, misValue, misValue);
                    }
                    if (exelApp != null)
                    {
                        exelApp.Quit();
                    }
                }
                this.ReleaseObject(exelWorkSheet);
                this.ReleaseObject(exelWorkBook);
                this.ReleaseObject(exelApp);
            }
            return true;
        }
    }
    return false;
}

Release the Object by force,

C#
private void ReleaseObject(object submittedObj)
{
    try
    {
        //// By force realeasing the Object if the Garbage Collector not doing it right now
        System.Runtime.InteropServices.Marshal.ReleaseComObject(submittedObj);
        submittedObj = null;
    }
    catch
    {
        submittedObj = null;
    }
    finally
    {
        GC.Collect();
    }
}

And then the Connection String,

C#
public static string GetConStr(string submittedPath)
{
    string connectionString = string.Empty;
    if (!string.IsNullOrEmpty(submittedPath))
    {
        connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 
           submittedPath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=0\"";
    }
    return connectionString;
}

License

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


Written By
Chief Technology Officer RightKnack Limited
Bangladesh Bangladesh
A big fan of getting down the latest cutting-edge technologies to the ground to innovate exceptionally amazing ideas.

My Blog: http://rashimuddin.wordpress.com/

My Email: rashimiiuc at yahoo dot com

Comments and Discussions

 
QuestionF1 Pin
MilutJojolag23-Mar-13 8:54
MilutJojolag23-Mar-13 8:54 
QuestionGreat for client applications, but not for server applications Pin
Bruce Goodman10-Jun-12 22:25
Bruce Goodman10-Jun-12 22:25 
Using Excel Interop in Excel C# programming requires MS Excel to be installed on a machine.

For clients that have MS Excel on their machine this is a great solution, but for server applications, like ASP.NET applications this could be problematic because most of the Internet service providers do not have MS Excel installed on the hosting server and there are additional problems regarding licensing and code security permissions.
GeneralMy vote of 5 Pin
Member 432084427-May-12 10:09
Member 432084427-May-12 10:09 
Question3 stars Pin
Jacky201121-Mar-12 17:20
Jacky201121-Mar-12 17:20 
AnswerRe: 3 stars Pin
karenpayne14-May-12 9:17
karenpayne14-May-12 9:17 
GeneralWhy don't i see the all messages posted by viewer? Pin
Md. Rashim Uddin20-Mar-12 19:23
Md. Rashim Uddin20-Mar-12 19:23 
GeneralMy vote of 4 Pin
Manoj Kumar Choubey16-Mar-12 19:33
professionalManoj Kumar Choubey16-Mar-12 19:33 
GeneralRe: My vote of 4 Pin
Md. Rashim Uddin17-Mar-12 21:22
Md. Rashim Uddin17-Mar-12 21:22 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.