I'm writing an application which works with excel files. I need a feature to delete a sheet. I have to use an assembly Microsoft.Office.Interop.Excel.dll.
It's running fine on developer machine but when I try to deploy it on server I'm getting an error:
Could not load file or assembly 'office, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' or one of its dependencies
I understand that problem occurs when MS Office is not installed on a machine. Customer don't want to install and buy MS Office on a server not at any price.
I install "Redistributable Primary Interop Assemblies" on developer machine as advised here: http://forums.asp.net/t/1530230.aspx/1 and compile my project again.
Code sample:
public bool DeleteSheet(string tableName)
{
Excel.Application app = null;
Excel.Workbooks wbks = null;
Excel._Workbook _wbk = null;
Excel.Sheets shs = null;
bool found = false;
try
{
app = new Excel.Application();
app.Visible = false;
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
wbks = app.Workbooks;
_wbk = wbks.Add(xlsfile);
shs = _wbk.Sheets;
int nSheets = shs.Count;
for (int i = 1; i <= nSheets; i++)
{
Excel._Worksheet _iSheet = (Excel._Worksheet)shs.get_Item(i);
if (_iSheet.Name == tableName)
{
_iSheet.Delete();
found = true;
Marshal.ReleaseComObject(_iSheet);
break;
}
Marshal.ReleaseComObject(_iSheet);
}
if (!found)
throw new Exception(string.Format("Table \"{0}\" was't found", tableName));
_wbk.SaveAs(connect, _wbk.FileFormat, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
finally
{
_wbk.Close(null, null, null);
wbks.Close();
app.Quit();
Marshal.ReleaseComObject(shs);
Marshal.ReleaseComObject(_wbk);
Marshal.ReleaseComObject(wbks);
Marshal.ReleaseComObject(app);
}
return true;
}
An exception
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)).
occurs on the line
app = new Excel.Application();
Can anyone advise on how to get this feature working successfully?
You can't use Microsoft.Office.Interop.Excel without having ms office installed.
Just search in google for some libraries, which allows to modify xls or xlsx: