Santosh Kumar Bind
Reputation: 63
Excel sheet is not working from server in c#
Hi i am downloding excel report, it is not working when download from server but it is working on local machine
Error:
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 CompanyStatics.btnexport_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\TestAdmin\CompanyStatics.aspx.cs:line 959
Code:
using ExcelAutoFormat = Microsoft.Office.Interop.Excel.XlRangeAutoFormat;
using System.Globalization;
using System.Runtime.InteropServices;
using ClosedXML.Excel;
c# Code on button click
protected void btnexport_Click(object sender, EventArgs e)
{
int companyId = Convert.ToInt32((ddlCompanyName.SelectedValue));
try
{
string path = Server.MapPath("exportedfiles\\");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
File.Delete(path + "Company&VesselDetails.xlsx");
var xlAppToExport = new Excel.Application();
xlAppToExport.Workbooks.Add("");
Excel.Worksheet xlWorkSheetToExport = default(Excel.Worksheet);
xlWorkSheetToExport = (Excel.Worksheet)xlAppToExport.Sheets["Sheet1"];
int iRowCnt = 2;
int iRowCnt1 = 5;
// Add company Name
xlWorkSheetToExport.Cells[1, 1] = "Company Name";
// Get the range from excel.
Excel.Range rangeComp = xlAppToExport.ActiveCell.Worksheet.Cells[1, 1] as Excel.Range;
rangeComp.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.SteelBlue);
// Set font color.
rangeComp.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
// Set Row Height.
rangeComp.EntireRow.RowHeight = 25.0;
// Set Column Width.
rangeComp.ColumnWidth = 40.0;
//rangeComp.HorizontalAlignment =ri;;
//end company Name
// Add Token Name
xlWorkSheetToExport.Cells[1, 2] = "Total Tokens";
// Get the range from excel.
Excel.Range rangeTokens = xlAppToExport.ActiveCell.Worksheet.Cells[1, 2] as Excel.Range;
rangeTokens.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.SteelBlue);
// Set font color.
rangeTokens.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
// Set Row Height.
rangeTokens.EntireRow.RowHeight = 30.0;
// Set Column Width.
rangeTokens.ColumnWidth = 40.0;
//end Token Name
// Add Approve Token
xlWorkSheetToExport.Cells[1, 3] = "Approve Tokens";
// Get the range from excel.
Excel.Range ApproveToken = xlAppToExport.ActiveCell.Worksheet.Cells[1, 3] as Excel.Range;
ApproveToken.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.SteelBlue);
// Set font color.
ApproveToken.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
// Set Row Height.
ApproveToken.EntireRow.RowHeight = 25.0;
// Set Column Width.
ApproveToken.ColumnWidth = 25.0;
//end Approve Token
// Add Remaining Tokens
xlWorkSheetToExport.Cells[1, 4] = "Remaining Tokens";
// Get the range from excel.
Excel.Range RemainingTokens = xlAppToExport.ActiveCell.Worksheet.Cells[1, 4] as Excel.Range;
RemainingTokens.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.SteelBlue);
// Set font color.
RemainingTokens.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
// Set Row Height.
RemainingTokens.EntireRow.RowHeight = 25.0;
// Set Column Width.
RemainingTokens.ColumnWidth = 25.0;
//end Remaining Tokens
// Add Remaining Tokens
xlWorkSheetToExport.Cells[4, 1] = "Vessel Name";
// Get the range from excel.
Excel.Range VesselName = xlAppToExport.ActiveCell.Worksheet.Cells[4, 1] as Excel.Range;
VesselName.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.SteelBlue);
// Set font color.
VesselName.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
// Set Row Height.
VesselName.EntireRow.RowHeight = 25.0;
// Set Column Width.
VesselName.ColumnWidth = 40.0;
// Add Remaining Tokens
xlWorkSheetToExport.Cells[4, 2] = " Ship Email ID";
// Get the range from excel.
Excel.Range ShipEmailID = xlAppToExport.ActiveCell.Worksheet.Cells[4, 2] as Excel.Range;
ShipEmailID.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.SteelBlue);
// Set font color.
ShipEmailID.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
// Set Row Height.
ShipEmailID.EntireRow.RowHeight = 25.0;
// Set Column Width.
ShipEmailID.ColumnWidth = 40.0;
// Add Remaining Tokens
xlWorkSheetToExport.Cells[4, 3] = "Total Tokens (300)";
// Get the range from excel.
Excel.Range TotalTokens = xlAppToExport.ActiveCell.Worksheet.Cells[4, 3] as Excel.Range;
TotalTokens.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.SteelBlue);
// Set font color.
TotalTokens.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
// Set Row Height.
TotalTokens.EntireRow.RowHeight = 25.0;
// Set Column Width.
TotalTokens.ColumnWidth = 25.0;
//end Remaining Tokens
// Add Remaining Tokens
xlWorkSheetToExport.Cells[4, 4] = "Assigned Tokens (99)";
// Get the range from excel.
Excel.Range AssignedTokens = xlAppToExport.ActiveCell.Worksheet.Cells[4, 4] as Excel.Range;
AssignedTokens.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.SteelBlue);
// Set font color.
AssignedTokens.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
// Set Row Height.
AssignedTokens.EntireRow.RowHeight = 25.0;
// Set Column Width.
AssignedTokens.ColumnWidth = 25.0;
//end Remaining Tokens
// Add Remaining Tokens
xlWorkSheetToExport.Cells[4, 5] = "Remaining Tokens (201)";
// Get the range from excel.
Excel.Range Remaining = xlAppToExport.ActiveCell.Worksheet.Cells[4, 5] as Excel.Range;
Remaining.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.SteelBlue);
// Set font color.
Remaining.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
// Set Row Height.
Remaining.EntireRow.RowHeight = 25.0;
// Set Column Width.
Remaining.ColumnWidth = 25.0;
//end Remaining Tokens
// Add Package Start
xlWorkSheetToExport.Cells[4, 6] = "Start Date";
// Get the range from excel.
Excel.Range pkgstart = xlAppToExport.ActiveCell.Worksheet.Cells[4, 6] as Excel.Range;
pkgstart.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.SteelBlue);
// Set font color.
pkgstart = (Excel.Range)xlAppToExport.Cells[4, 6];
pkgstart.EntireColumn.NumberFormat = "dd/mm/yyyy";
pkgstart = (Excel.Range)xlAppToExport.Cells[4, 6];
pkgstart.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
pkgstart.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
pkgstart.get_Range("F1", "G1").Cells.HorizontalAlignment =
Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
//string startRange = "A1";
//string endRange = "A1";
//Excel.Range currentRange = (Excel.Range)xlAppToExport.get_Range(startRange, endRange);
//currentRange.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
//pkgstart.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
// Set Row Height.
pkgstart.EntireRow.RowHeight = 50.0;
// Set Column Width.
pkgstart.ColumnWidth = 10.0;
//end Package Start
// Add Last Update
xlWorkSheetToExport.Cells[4, 7] = "Last Update Date";
// Get the range from excel.
Excel.Range lastupdate = xlAppToExport.ActiveCell.Worksheet.Cells[4, 7] as Excel.Range;
lastupdate.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.SteelBlue);
// Set font color.
lastupdate.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
// Set Row Height.
lastupdate.EntireRow.RowHeight = 25.0;
// Set Column Width.
lastupdate.ColumnWidth = 25.0;
//end Last Update Date
for (int i = 0; i < gvCompany.Rows.Count; i++)
{
string sid = gvCompany.Rows[i].Cells[3].Text;
string cmpl = gvCompany.Rows[i].Cells[4].Text;
string kpit = gvCompany.Rows[i].Cells[5].Text;
string tcs = gvCompany.Rows[i].Cells[6].Text;
xlWorkSheetToExport.Cells[iRowCnt, 1] = sid;
xlWorkSheetToExport.Cells[iRowCnt, 2] = cmpl;
xlWorkSheetToExport.Cells[iRowCnt, 3] = kpit;
xlWorkSheetToExport.Cells[iRowCnt, 4] = tcs;
iRowCnt = iRowCnt + 1;
string query = string.Empty;
DataTable dt = new DataTable();
query = "select vesselID, vesselName,ShipEmailID, CompanyMasterId , ISNULL(AllowToken, 0 ) as 'AllowToken', ISNULL(AssignTokenToEmp, 0 ) as 'Total Assign Token',( ISNULL(AllowToken, 0 ) - ISNULL(AssignTokenToEmp, 0 )) as 'RemainingToken' from Vessel where CompanyMasterID =" + companyId;
dt = SqlHelper.ReturnDataTable(query);
divcrew.Visible = true;
gvvessel.AllowPaging = false;
gvvessel.DataSource = dt;
gvvessel.DataBind();
for (int j = 0; j < gvvessel.Rows.Count; j++)
{
// gvvessel.AllowPaging = false;
Label lblvess = (Label)gvvessel.Rows[j].FindControl("lblvesselName");
string myVal = lblvess.Text;
Label lblShipEmail = (Label)gvvessel.Rows[j].FindControl("lblShipEmailID");
string mylblShipEmail = lblShipEmail.Text;
Label lblAllowTo = (Label)gvvessel.Rows[j].FindControl("lblAllowToken");
string mylblAllowTo = lblAllowTo.Text;
Label lblAssignToken = (Label)gvvessel.Rows[j].FindControl("lblAssignToken");
string mylAssignToken = lblAssignToken.Text;
Label lblRemainingT = (Label)gvvessel.Rows[j].FindControl("lblRemainingToken");
string mylRemainingT = lblRemainingT.Text;
Label lblpkg = (Label)gvvessel.Rows[j].FindControl("lblpackagedate");
string myVal1 = lblpkg.Text;
Label lbllastupd = (Label)gvvessel.Rows[j].FindControl("lbllastupdate");
string myVal2 = lbllastupd.Text;
//gvvessel.SetPageIndex(j);
xlWorkSheetToExport.Cells[iRowCnt1, 1] = myVal;
xlWorkSheetToExport.Cells[iRowCnt1, 2] = mylblShipEmail;
xlWorkSheetToExport.Cells[iRowCnt1, 3] = mylblAllowTo;
xlWorkSheetToExport.Cells[iRowCnt1, 4] = mylAssignToken;
xlWorkSheetToExport.Cells[iRowCnt1, 5] = mylRemainingT;
// string date = DateTime.Now.ToString("yyyy-MM-dd");
//if (!string.IsNullOrEmpty(myVal1) && myVal1!="----")
//{
// date = DateTime.ParseExact(myVal1,"dd-MM-yyyy", CultureInfo.InvariantCulture).ToString("yyyy-MM-dd");
//}
//else if (myVal1 =="----")
//{
// lblpkg.Text = "--------";
//}
xlWorkSheetToExport.Cells[iRowCnt1, 6] = myVal1;
xlWorkSheetToExport.Cells[iRowCnt1, 7] = myVal2;
iRowCnt1 = iRowCnt1 + 1;
}
// SAVE THE FILE IN A FOLDER.
xlWorkSheetToExport.SaveAs(path + "Company&VesselDetails.xlsx");
// CLEAR.
xlAppToExport.Workbooks.Close();
xlAppToExport.Quit();
xlAppToExport = null;
xlWorkSheetToExport = null;
//string path = Server.MapPath("exportedfiles\\");
// CHECK IF THE FOLDER EXISTS.
if (Directory.Exists(path))
{
// CHECK IF THE FILE EXISTS.
if (File.Exists(path + "Company&VesselDetails.xlsx"))
{
string sPath = Server.MapPath("exportedfiles\\");
Response.AppendHeader("Content-Disposition", "attachment; filename=Company&VesselDetails.xlsx");
Response.TransmitFile(sPath + "Company&VesselDetails.xlsx");
Response.End();
}
}
}
}
catch (Exception ex)
{
lblshowexcelerror.Text = ex.ToString();
}
finally
{
}
Upvotes: 1
Views: 943
Answers (1)
Emad
Reputation: 3939
To use COM objects you should have MS Excel installed on server machine. Otherwise you are going to need third-party libraries to manually work with .xls and .xlsx files.
One such library is EPPlus which works with new .xlsx files. It have worked for me and I know many others exist.
Upvotes: 2
Related Questions
- Reading Excel file on the server
- Not Able to Open Excel File When Hosting In IIS Server In Asp.Net C#
- Could not read the excel file in server
- Excel is not working in Windows 8
- Excel file on server not found when opened by client
- Error while reading and saving the excel file on serverside
- Can't open the excel file after generating it on server side
- Excel opening with c# gets error at server
- How to fetch excel sheet data which is stored in another server
- Excel file doesnot open when I host my Dot Net Application in IIS