Error:=Exception Details: System.Data.OleDb.OleDbException: Unexpected error from external database driver ().
My following Code:-
public ActionResult ExportToExcel(int MID)
{
MModule objModule = new MModule();
objModule.LoadModule(MID);
DatabaseObject objData = new DatabaseObject();
string file = DateTime.Now.Ticks + "_" + objModule.Title + ".xlsx";
System.IO.File.Copy(Server.MapPath("/Excel/Export/Atemplate.xlsx"), Server.MapPath("/Excel/Export/" + file));
string filename = Server.MapPath("/Excel/Export/" + file);
objData.Query = "SELECT * FROM " + objModule.TableName + "";
DataTable dtReport = objData.GetTable();
System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Mode=ReadWrite;Extended Properties=\"Excel 12.0 Xml;Readonly=False;IMEX=0;\"");
con.Open();
System.Data.OleDb.OleDbCommand cmd = con.CreateCommand();
string tableString = "";
string ColString = "";
for (int i = 0; i < dtReport.Columns.Count; i++)
{
tableString += "[" + dtReport.Columns[i].Caption + "] varchar(255),";
ColString += "[" + dtReport.Columns[i].Caption + "],";
}
tableString = tableString.Substring(0, tableString.Length - 1);
try
{
ColString = ColString.Substring(0, ColString.Length - 1);
cmd.CommandText = "CREATE TABLE [" + objModule.Title + "] ( " + tableString + ");";
cmd.ExecuteNonQuery();
string ValString = "";
for (int j = 0; j < dtReport.Rows.Count; j++)
{
ValString = "";
for (int i = 0; i < dtReport.Columns.Count; i++)
{
ValString += "'" + dtReport.Rows[j][i] + "',";
}
ValString = ValString.Substring(0, ValString.Length - 1);
cmd.CommandText = "Insert Into [" + objModule.Title + "] (" + ColString + ") Values (" + ValString + ");";
cmd.ExecuteNonQuery();
}
cmd.Dispose();
con.Dispose();
con.Close();
Microsoft.Office.Interop.Excel.Application excelApplication = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook = excelApplication.Workbooks.Open(filename,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
foreach (Excel.Worksheet wrkst1 in workbook.Worksheets)
{
Excel.Range usedrange = wrkst1.UsedRange;
usedrange.Columns.AutoFit();
wrkst1.Tab.Color = 255;
for (int i = 0; i < dtReport.Rows.Count; i++)
{
for (int j = 0; j < dtReport.Columns.Count; j++)
{
Microsoft.Office.Interop.Excel.Range range = wrkst1.Cells[i + 2, j + 1];
range.Interior.Color = System.Drawing.
ColorTranslator.ToOle(System.Drawing.Color.YellowGreen);
}
}
Excel.Range rng1 = (Excel.Range)wrkst1.Rows[1];
rng1.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
Excel.Range rng = (Excel.Range)wrkst1.Cells[1, 1];
Excel.Range row = rng.EntireRow;
row.Insert(Excel.XlInsertShiftDirection.xlShiftDown, false);
Excel.Range chartRange;
chartRange = wrkst1.get_Range("E1");
chartRange.FormulaR1C1 = objModule.Title;
chartRange.HorizontalAlignment = 3;
chartRange.VerticalAlignment = 3;
chartRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green);
chartRange.Font.Size = 20;
}
excelApplication.DisplayAlerts = false;
((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]).Delete();
workbook.Close(true, filename, null);
excelApplication.DisplayAlerts = true;
}
catch (Exception ex)
{
}
return File(filename, "application/x-excel", DateTime.Now.Ticks + "_Report.xlsx");
}