I tried to write data to a excel template file using asp.net c#. But I got following exception.
ExceptionType: "System.Runtime.InteropServices.COMException" Message: "The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)"
Sometimes it works for small count of rows. I cannot find what is the issue.
try
{
int rowNumber = 5;
DataSet ds = ClsSystem.getReviewResponse();
Excel.Application myExcelApplication = null;
Excel.Workbook myExcelWorkbook = null ;
Excel.Worksheet myExcelWorkSheet = null;
//copy template
System.IO.File.Copy(excelFilePath, NewExcelFilePath);
myExcelApplication = new Excel.Application();
myExcelApplication.DisplayAlerts = false;
myExcelWorkbook = (Excel.Workbook(myExcelApplication.Workbooks._Open(NewExcelFilePath, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value)); // open the existing excel file
myExcelWorkSheet = (Excel.Worksheet)myExcelWorkbook.Worksheets[1];
myExcelWorkSheet.Name = "Test sheet";
for (int n = 0; n < ds.Tables.Count; n++)
{
for (int i = 0; i < ds.Tables[n].Rows.Count; i++)
{
myExcelWorkSheet.Cells[rowNumber, "A"] = ds.Tables[n].Rows[i]["HeaderID"].ToString();
myExcelWorkSheet.Cells[rowNumber, "B"] = ds.Tables[n].Rows[i]["ResponseID"].ToString();
myExcelWorkSheet.Cells[rowNumber, "C"] = ds.Tables[n].Rows[i]["Region"].ToString();
myExcelWorkSheet.Cells[rowNumber, "D"] = ds.Tables[n].Rows[i]["Sector"].ToString();
myExcelWorkSheet.Cells[rowNumber, "E"] = ds.Tables[n].Rows[i]["Facility"].ToString();
myExcelWorkSheet.Cells[rowNumber, "F"] = ds.Tables[n].Rows[i]["Program"].ToString();
myExcelWorkSheet.Cells[rowNumber, "G"] = ds.Tables[n].Rows[i]["AuditDate"].ToString();
myExcelWorkSheet.Cells[rowNumber, "H"] = ds.Tables[n].Rows[i]["Auditor1"].ToString();
rowNumber++;
}
rowNumber++;
}
try
{
myExcelWorkbook.SaveAs(NewExcelFilePath, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, Excel.XlSaveAsAccessMode.xlExclusive,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, System.Reflection.Missing.Value); // Save data in excel
myExcelWorkbook.Close(true, NewExcelFilePath, System.Reflection.Missing.Value); // close the worksheet
myExcelApplication.Quit();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (myExcelWorkSheet != null) Marshal.ReleaseComObject(myExcelWorkSheet);
if (myExcelWorkbook != null) Marshal.ReleaseComObject(myExcelWorkbook);
if (myExcelApplication != null) Marshal.ReleaseComObject(myExcelApplication);
}
}
catch (Exception ex)
{
if (myExcelApplication != null)
{
myExcelApplication.Quit(); // close the excel application
}
throw ex;
}
I want to write more than 500 rows at once.
Not to rock the boat on your development path entirely, but I find the interop libraries horribly unreliable. I have had much greater success with ClosedXML. I write tens of thousands of rows a day and have never had an issue related to ClosedXML. https://github.com/ClosedXML/ClosedXML
User contributions licensed under CC BY-SA 3.0