CODEWITHSUNDEEP
c#adooffice-interop
darshan
darshan

Reputation: 31

Generate / Open Excel file without saving

I have a requirement to generate output report in Excel format and open the same on the screen when the processing is complete. But in this case, it should not save the report on the drive anywhere and only open on the screen.

I tried to use ADO using OLEDB but it always generates file before writing anything to it. This is what I have tried so far.

            using (OleDbConnection con = new OleDbConnection(connString))
            {
                try
                {
                    con.Open();
                }
                catch (InvalidOperationException invalidEx)
                {
                   //Exception handling
                }
                // Create table for excel structure
                StringBuilder strSQL = new StringBuilder();
                strSQL.Append("CREATE TABLE [" + tableName + "]([TITLE] text,[SURNAME] text,[STATUS] text)");

                // Define file columns
                StringBuilder strfield = new StringBuilder();
                strfield.Append("[TITLE],[SURNAME],[STATUS]");

                OleDbCommand cmd = new OleDbCommand(strSQL.ToString(), con);
                cmd.ExecuteNonQuery(); // This creates the table

 //Actual row for creating and insering row - logic not shown completely
cmd.CommandText = strSQL.Append(" insert into [" + tableName  + "]( ")
                        .Append(strfield.ToString())
                        .Append(") values (").Append(strvalue).Append(")").ToString();
                    success = cmd.ExecuteNonQuery();

But this always creates the file first which I do not want. Please advise if anyone has worked on the similar requirement. Thanks.

Upvotes: 0

Views: 1512

Answers (2)

cjb110
cjb110

Reputation: 1471

Ok, first off use ADO (a database access technology) to try and create a spreadsheet is bizarre, possibly doable, but definitely not easy.

Secondly you're saying create a spreadsheet and open it, without creating a file, this means that you'll also have to create ALL the functionality to open, parse, format and display spreadsheets (basically recreate Excel!)...as Excel cannot do this for you.

So I would question the "generate output report in Excel format" requirement, does this really mean "display in a grid"? Or is it "display in a grid that allows formatting, totalling?"

If it the Excel format really is a requirement, then the only thing I can suggest is you will have to create a temporary Excel file, then delete it after you've displayed it.

I would look at the ClosedXML library that really simplifies the use of OpenXML to create xlsx spreadsheets.

Upvotes: 1

Michael Bray
Michael Bray

Reputation: 15265

Perhaps this Microsoft Article will help: How to: Open a spreadsheet document from a stream (Open XML SDK)

Upvotes: 0

Related Questions