0

I am using C# and Visual studio 2010. I am using a ASP.NET web application. I am trying to pass the data from Oracle ( data available in a datatable ) to multiple sheets within the same excel. The user should get a prompt to save the file. I have tried the below way, but it is affecting the performance. Is there any different way which can be achieved. I cannot use a third party tool. There are no macros in the excel and is a simple data transfer to excel from datatable. No formatting required in excel. The datatable is huge. 

Excel.Application oXL = new Excel.Application();

            Excel.Workbook oWB = oXL.Workbooks.Add(missing);

            Excel.Worksheet oSheet = oWB.ActiveSheet as Excel.Worksheet;

            using (OracleConnection con = new OracleConnection(oradb))
            {
                con.Open();
                test2(oSheet, "select * from table1", "Names", con);
                Excel.Worksheet oSheet2 = oWB.Sheets.Add(missing, missing, 1, missing) as Excel.Worksheet;
                test2(oSheet2, "select * from table2", "Address", con);
                Excel.Worksheet oSheet3 = oWB.Sheets.Add(missing, missing, 2, missing) as Excel.Worksheet;
                test2(oSheet3, "select * from table3", "Phones", con);
            }
        }

        public static void test2(Excel.Worksheet oSheet, string sql, string name, OracleConnection con)
        {
            OracleDataAdapter da = new OracleDataAdapter(sql, con);
            DataTable dt = new DataTable();
            da.Fill(dt);
            if (dt == null || dt.Columns.Count == 0)
            {
            }
            else
            {
                oSheet.Name = name;
                for (var i = 0; i < dt.Columns.Count; i++)
                {
                    oSheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
                }
                for (var i = 0; i < dt.Rows.Count; i++)
                {
                    for (var j = 0; j < dt.Columns.Count; j++)
                    {
                        oSheet.Cells[i + 2, j + 1] = dt.Rows[i][j];
                    }
                }
            }
        }
    }

How to pass data to multiple sheets within same excel and prompt user to save the file ?

nisarg parekh
  • 413
  • 4
  • 23
venkat14
  • 583
  • 3
  • 12
  • 34
  • Very likely the COM interop is what is giving you problems. Effectively you're running an instance of the Excel application itself on your server, which is not efficient, and also quite error-prone. It's also unnecessary. Use some code which is dedicated to reading and writing the excel file format directly, without opening Excel itself. You say "I cannot use a third party tool"...but why not exactly? There are free ones available. – ADyson Jun 07 '19 at 08:39
  • The fact that your datatable is "huge" of course won't be helping...what's your definition of huge exactly? How many rows and columns? You could perhaps try seeing if it's possible to do bulk inserting instead of doing each cell individually. – ADyson Jun 07 '19 at 08:40
  • Yes . the columns could be max of 60, and rows in 80000 per sheet – venkat14 Jun 07 '19 at 11:04
  • Well it should be fairly obvious that inserting that amount of stuff one cell at a time is never going to be fast, especially when you add in the overhead of doing it via the `Excel.Application` interface, which is effectively doing it via the GUI, as if you were impersonating a user. I would guess that also the SQL query itself is maybe relatively slow? Have you benchmarked to see what proportion of the execution time is taken up by the SQL and what by the C#/Excel code? – ADyson Jun 07 '19 at 11:06
  • The Oracle query is faster. only passing the data from datatable to excel is taking time. Is there any code blocks which can be used to pass multiple data tables to excel sheets and prompting user to save the file. – venkat14 Jun 07 '19 at 11:14
  • It's not my area of expertise directly, but I did a bit of googling (which you could also do too....!) and found [this](https://stackoverflow.com/a/51829921/5947043) which might help. The idea it to create a Range in Excel and assign the data to that. It's written in VB.NET but you can easily [convert it](http://converter.telerik.com/) – ADyson Jun 07 '19 at 13:21

0 Answers0