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:
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,
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
{
connExcel.Open();
excelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
}
catch
{
}
finally
{
connExcel.Close();
}
DataRow[] dr = excelSchema != null ?
excelSchema.Select("TABLE_NAME = 'Sheet1'") : null;
if (dr == null || dr.Length == 0)
{
string excelCommand = "CREATE TABLE";
string sheetName = "[Sheet1]";
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.Application exelApp = null;
Ex.Workbook exelWorkBook = null;
Ex.Worksheet exelWorkSheet = null;
object misValue = System.Reflection.Missing.Value;
try
{
exelApp = new Ex.ApplicationClass();
if (exelApp != null)
{
if (exelApp.Workbooks != null)
{
exelWorkBook = exelApp.Workbooks.Add(misValue);
}
}
if (exelWorkBook != null)
{
exelWorkSheet = (Ex.Worksheet)exelWorkBook.Worksheets.get_Item(1);
if (exelWorkSheet != null)
{
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";
}
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
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(submittedObj);
submittedObj = null;
}
catch
{
submittedObj = null;
}
finally
{
GC.Collect();
}
}
And then the Connection String,
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;
}