Hello Friends
I am trying to read and write excel file with c#. For this I included the
Interop.Excel.dll in my project.
I have installed MS Excel in my local machine. This app is working fine there.
But when I put this app at my server, where MS Excel is not installed, this is not generating the file or may be even not reading.
I am confused although I am using dll in my project, does it require MS Excel to be installed there?
I am getting the following exception
===================START=====================
10/27/2012 7:48:52 PM
System.Runtime.InteropServices.COMException (0x80040154): Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).
at System.RuntimeTypeHandle.CreateInstance(RuntimeType type, Boolean publicOnly, Boolean noCheck, Boolean& canBeCached, RuntimeMethodHandleInternal& ctor, Boolean& bNeedSecurityCheck)
at System.RuntimeType.CreateInstanceSlow(Boolean publicOnly, Boolean skipCheckThis, Boolean fillCache)
at System.RuntimeType.CreateInstanceDefaultCtor(Boolean publicOnly, Boolean skipVisibilityChecks, Boolean skipCheckThis, Boolean fillCache)
at System.Activator.CreateInstance(Type type, Boolean nonPublic)
at ImpExpExcelFile.Form1.generateExcel(String loginId, String fileName, DataSet newValues)
====================END======================
My Code
private DataSet readExcelFile(string path)
{
string excelConn = @"Provider=Microsoft.Jet.Oledb.4.0; Data Source=" + path + "; Extended Properties=Excel 8.0;";
OleDbConnection oleConn = new OleDbConnection(excelConn);
DataSet xlsDs = new DataSet();
try
{
if (!string.IsNullOrEmpty(path))
{
string strSQL = "SELECT * FROM [Sheet1$]";
OleDbDataAdapter oleDa = new OleDbDataAdapter(strSQL, oleConn);
oleDa.Fill(xlsDs);
}
}
catch (Exception ex)
{
handleException(ex);
}
finally
{
oleConn.Close();
oleConn.Dispose();
}
return xlsDs;
}
private bool generateExcel(String loginId, String fileName, DataSet newValues)
{
bool retVal = false;
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
string filePath = @"E:\";
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int j = 1;
for (int i = 0; i < newValues.Tables[0].Rows.Count; i++)
{
xlWorkSheet.Cells[j, 1] = newValues.Tables[0].Rows[i][0].ToString();
xlWorkSheet.Cells[j, 2] = newValues.Tables[0].Rows[i][1].ToString();
j++;
}
if (File.Exists(filePath + fileName))
{
File.Delete(filePath + fileName);
}
xlWorkBook.SaveAs(filePath + fileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
retVal = true;
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
}
finally
{
GC.Collect();
}
}