Opening an excel file using asp.net C#

Question

My requirement is to open an excel file which is under Attachment folder. So I took the reference from here and used abatishchev answer for my requirement. So I tried that in my below code.

public void ExportExcel()
{
   string str_lwpc_query = string.Empty;
   str_lwpc_query = "select company_name 'COMPANY NAME',Deputed_Company_Name 'DEPUTED COMPANY NAME',emp_card_no 'EMP CODE',emp_name 'EMPLOYEE NAME',LWP,'' Remarks,  " +
                    "Adj_Days Gain_Loss_LOP_Days, VAL_DAY LOP_Days_Desc, month, year from XXACL_EMP_INFO_LWP_OTDAYS_HRS_V " +
                     "where emp_type='C' and month = '3' and year = '2015' ";
    DataTable Dt_lwpc = new DataTable();
    Dt_lwpc = CF.ExecuteDT(str_lwpc_query);
    DataSet DS_lwpc = new DataSet();
    DS_lwpc.Tables.Add(Dt_lwpc);
    DS_lwpc.Tables[0].TableName = "Employee loss of pay for consultant Details";
    var directory = Server.MapPath("~/Attachment/");
    ExcelLibrary.DataSetHelper.CreateWorkbook(directory + "Employee_lwpc_Details.xls", DS_lwpc);

    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
    ExcelLibrary.Office.Excel.Workbook wb = excel.Workbooks.Open(ExcelLibrary.DataSetHelper.CreateWorkbook(directory + "Employee_lwpc_Details.xls", DS_lwpc));
}

but I get error in the last line as

The best overloaded method match for Microsoft.Office.Interop.Excel.Workbooks.Open(string, object, object, object, ...........) has some invalid arguments

kindly suggest what is wrong

@DmitryRotay: tried got error as `cannot implicitly convert type Microsoft.Office.Interop.Excel to ExcelLibrary.Office.Excel.Workbook an explicit conversion exists ( are you missing a cast)` — Nad, Apr 13 '16 at 06:14
Sorry, deleted my comment accidentally. You are trying to open file with Microsoft's Excel interop library - the result returned is of type `Microsoft.Office.Interop.Excel.Workbook`. but are expecting result type to be of some custom library - ExcelLibrary.Office.Excel.Workbook. — Dmitry Rotay, Apr 13 '16 at 06:18
If you really want to use excel interop to open workbook and work with it, then you need to replace the last line with this: `Microsoft.Office.Interop.Excel.Workbook wb = excel.Workbooks.Open(ExcelLibrary.DataSetHelper.CreateWorkbook(directory + "Employee_lwpc_Details.xls", DS_lwpc));` — Dmitry Rotay, Apr 13 '16 at 06:25
Ah, sorry. Lost code from my deleted comment. Try this. `Microsoft.Office.Interop.Excel.Workbook wb = excel.Workbooks.Open(directory + "Employee_lwpc_Details.xls")` — Dmitry Rotay, Apr 13 '16 at 06:29
It's kinda hard to make suggestions, since we don't know versions of interops and ExcelLibrary that you are using. It's also possible, that this construct will work: `ExcelLibrary.Office.Excel.Workbook wb = ExcelLibrary.Office.Excel.Workbook.Load(directory + "Employee_lwpc_Details.xls", DS_lwpc);` In this case you don't need previous line of code creating Excel.Application. — Dmitry Rotay, Apr 13 '16 at 06:32
@DmitryRotay: so can u give me the full updated code that I will try ? currently i added ur code and getting error as `Could not load file or assembly 'Office, Version=7.0.3300.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)` — Nad, Apr 13 '16 at 06:33
Version 7.0 sounds a little outdated. Please try the latter example that I've posted with `Workbook.Load`. It's more robust if your project generally uses ExcelLibrary to work with excel documents. — Dmitry Rotay, Apr 13 '16 at 06:37
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/109003/discussion-between-coder-and-dmitry-rotay). — Nad, Apr 13 '16 at 06:39

score 2 · Accepted Answer · answered Apr 13 '16 at 10:15

If you want users of your website to open an excel file that you've created on server, than you don't need to open it there - just send it to the user. Replace the last two lines of code with this:

string filePath = directory + "Employee_lwpc_Details.xls"; 
Response.ContentType = "Application/vnd.ms-excel"; 
Response.AppendHeader("content-disposition", 
"attachment; filename=" + "Employee_lwpc_Details.xls"); 
Response.TransmitFile(filePath); 
Response.End();

thanks a lot Dmitry for the patience and resolving the issue step by step. Happy coding :) — Nad, Apr 13 '16 at 10:31

score 0 · Answer 2 · answered Apr 13 '16 at 06:16

0

Microsoft.Office.Interop.Excel.Workbooks.Open takes a filename and a number of additional parameters, see the MSDN documentation here. The CreateWorkbook method that you're calling is not returning the required arguments for Open.

answered Apr 13 '16 at 06:16

Luke Pillar

164
1
4

so waht would be my code according to this ? kindly update – Nad Apr 13 '16 at 06:20
Have you tried just using `ExcelLibrary.Office.Excel.Workbook wb = excel.Workbooks.Open(directory + "Employee_lwpc_Details.xls"));`? If I'm understanding your code correctly, that would open the Workbook you created a few lines up. – Luke Pillar Apr 13 '16 at 07:22
the above code just places the file in the `attachment` folder but it doesn't opens the file – Nad Apr 13 '16 at 07:23
`ExcelLibrary.DataSetHelper.CreateWorkbook(directory + "Employee_lwpc_Details.xls", DS_lwpc);` places the file in the attachment folder, but `ExcelLibrary.Office.Excel.Workbook wb = excel.Workbooks.Open(directory + "Employee_lwpc_Details.xls"));` opens that file and assigns a local variable to it. – Luke Pillar Apr 13 '16 at 07:26
that's what I also know,but why it is not opening the file then ? – Nad Apr 13 '16 at 07:27
What do you mean by "opening the file"? The file is open, and you can access it via the local variable `wb`. – Luke Pillar Apr 13 '16 at 07:28
how to check whether it is open or not ? and how to access the same file ?? – Nad Apr 13 '16 at 07:29

Opening an excel file using asp.net C#

2 Answers2