Rashim's Blog

Save data to Excel file using Microsoft Excel

Posted on: January 9, 2012

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 Excell Table,


        private void InsertDataOnExcellTable(string submitttedExcellPath, Test submittedExcellData)
        {
            if (!string.IsNullOrEmpty(submitttedExcellPath) && submittedExcellData != null)
            {
                if (!string.IsNullOrEmpty(submittedExcellData.FileName))
                {
                    var fullFileName = submitttedExcellPath + Path.DirectorySeparatorChar + submittedExcellData.FileName;

                    var 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;
                                var 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,


        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,


        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,

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);
            }
            catch
            {
            }
            finally
            {
                GC.Collect();
            }
        }

And then the Connection String,


        public static string GetConStr(string submittedPath)
        {
            var 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;
        }

Advertisements

1 Response to "Save data to Excel file using Microsoft Excel"

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: