0

I just start working on asp.net. I would like to generate excel from database but it does not work. below is the function. What I want is after creating my object using a form , export the filled data in an excel sheet.
my function

    private string GeneratemyobjectExcelForm(Myobject myobject)
    {
        Process[] processesBefore = Process.GetProcessesByName("excel");

        string ret = "";
        object oMissing = System.Reflection.Missing.Value;
        object missing = System.Reflection.Missing.Value;
        string tempFilePath = Server.MapPath("~/Views") + "/myobject_Views.xlsx";
        string filename = Server.MapPath("~/myobjectfolder") + "/" + myobject.myobjectNumber.Replace('/', '-') + ".xlsx";
        File.Copy(tempFilePath, filename,true);
        Microsoft.Office.Interop.Excel.Application objExcel = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook objworkbook;
        Microsoft.Office.Interop.Excel.Worksheet objworksheet;

        try
        {

            objExcel.DisplayAlerts = false;
            objExcel.Visible = false;

            objworkbook = objExcel.Workbooks.Open(filename, true, false,
                                                  Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                                  Type.Missing, Type.Missing, true, Type.Missing,
                                                  Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            // Get Excel processes after opening the file.
            Process[] processesAfter = Process.GetProcessesByName("excel");

            // Now find the process id that was created, and store it.
            int processID = 0;
            foreach (Process process in processesAfter)
            {
                if (!processesBefore.Select(p => p.Id).Conmyobjectins(process.Id))
                {
                    processID = process.Id;
                }
            }

            objworksheet = (Microsoft.Office.Interop.Excel.Worksheet)objworkbook.Sheets.get_Item("Appendix A");


            Microsoft.Office.Interop.Excel.Range selRange = objworksheet.get_Range("K3", "K3");
            selRange.set_Value(XlRangeValueDamyobjectType.xlRangeValueDefault, myobject.myobjectNumber);


            selRange = objworksheet.get_Range("D6", "D6");
            selRange.set_Value(XlRangeValueDamyobjectType.xlRangeValueDefault, myobject.name1);


            selRange = objworksheet.get_Range("D7", "D7");
            selRange.set_Value(XlRangeValueDamyobjectType.xlRangeValueDefault, myobject.name2);


            selRange = objworksheet.get_Range("D9", "D9");
            selRange.set_Value(XlRangeValueDamyobjectType.xlRangeValueDefault, myobject.name3);

            selRange = objworksheet.get_Range("D10", "D10");
            selRange.set_Value(XlRangeValueDamyobjectType.xlRangeValueDefault, myobject.name4);

            selRange = objworksheet.get_Range("D11", "D11");
            selRange.set_Value(XlRangeValueDamyobjectType.xlRangeValueDefault, string.Format("{0} - {1}",myobject.DateFromString,myobject.DateToString));

            selRange = objworksheet.get_Range("E14", "E14");
            selRange.set_Value(XlRangeValueDamyobjectType.xlRangeValueDefault, myobject.name5);
            selRange = objworksheet.get_Range("G14", "G14");
            selRange.set_Value(XlRangeValueDamyobjectType.xlRangeValueDefault, myobject.name6);

            selRange = objworksheet.get_Range("E15", "E15");
            selRange.set_Value(XlRangeValueDamyobjectType.xlRangeValueDefault, myobject.name7);
            selRange = objworksheet.get_Range("G15", "G15");
            selRange.set_Value(XlRangeValueDamyobjectType.xlRangeValueDefault, myobject.name8);

            selRange = objworksheet.get_Range("E16", "E16");
            selRange.set_Value(XlRangeValueDamyobjectType.xlRangeValueDefault, myobject.name9);
            selRange = objworksheet.get_Range("G16", "G16");
            selRange.set_Value(XlRangeValueDamyobjectType.xlRangeValueDefault, myobject.name10);

            objworkbook.Save();
            objworkbook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Type.Missing, Type.Missing);
            objExcel.Quit();
            ret = filename.ToString();
            // And now kill the process.
            if (processID != 0)
            {
                Process process = Process.GetProcessById(processID);
                process.Kill();
            }

        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            objExcel = null;
            objworkbook = null;
            objworksheet = null;
            ReleaseComObject(objExcel);
            ReleaseComObject(objworkbook);
            ReleaseComObject(objworksheet);
        }

        return ret;
    }
Matt Hogan-Jones
  • 2,981
  • 1
  • 29
  • 35
medman
  • 25
  • 7
  • You haven't added your function. – Matt Hogan-Jones Nov 16 '17 at 11:38
  • 1
    added now @MattJones – medman Nov 16 '17 at 11:39
  • thanks @Cronan but ithis function also should work – medman Nov 16 '17 at 11:46
  • 1
    See my anwer here: https://stackoverflow.com/a/47293207/5836671 – VDWWD Nov 16 '17 at 12:02
  • ok @VDWWD letme check – medman Nov 16 '17 at 12:05
  • 1
    You are trying to use Excel interop in an ASP.NET application. This is a terrible idea! Microsoft says [don't do it](https://support.microsoft.com/en-us/help/257757/considerations-for-server-side-automation-of-office). It's going to be slow, it's going to be hard to debug, and your copy of Office likely isn't licensed to run on a server. Instead, you should natively generate Excel files using managed libraries such as [EPPlus](https://github.com/JanKallman/EPPlus), [NPOI](https://github.com/tonyqus/npoi), [ClosedXML](https://github.com/closedxml/closedxml), Aspose, or Open XML SDK. – mason Nov 16 '17 at 14:03
  • 1
    Never put `throw ex;` in your code where `ex` is a caught exception. That will lose your stacktrace. Simply `throw;` it. Of course, you shouldn't catch anything at all if you're not actually going to do anything about it. – mason Nov 16 '17 at 14:06

0 Answers0