C# Export to Excel xls

Question

I have a method that currently exports the results of a stored procedure to a CSV file. I've been tasked with altering it to export to XLS but I'm having some trouble.

The code:

protected void ExportFundingSummaryToExcelSA(Object sender, EventArgs e)
        {
            const string fileName = "METT Dashboard - Funding Summary";
            const string rowFormat = "{0},{1},{2},{3},{4},{5}\n";

            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment;filename=" + fileName + ".csv");
            Response.Charset = "";
            Response.ContentType = "text/csv";

            GetCompanyGroupCode();

            var sb = new StringBuilder();
            sb.AppendFormat(rowFormat, "Sector", "Product Line", "Program Number", "Program Description","Participating Companies", "Gross");

            var customerId = CurrentUser.Company.Id;

            var  year = 2015;

            // Set Title Row
            Response.Write(year + " Products Lines for: " + CurrentUser.Company.Name + " (" + customerId + ")\n");

            // Set Generated Date (Report Created on: 9/29/2004 3:33:32 PM)
            Response.Write("Report Created on: " + System.DateTime.Now.ToString() + "\n\n\n");

            var fundingData = GetFundingData();

            if (fundingData != null)
            {
                if (fundingData.Summary != null && fundingData.Summary.Count > 0)
                {
                    var summaries = MoveSetAsidesDown(fundingData.Summary);

                    for (var i = 0; i < summaries.Count; i++)
                    {
                        if (fundingData.Programs != null && fundingData.Programs.Count > 0)
                        {
                            foreach (var program in fundingData.Programs)
                            {
                                if (program.PlId == summaries[i].PlId)
                                {
                                    sb.AppendFormat(rowFormat,
                                                    SharePointUtil.ToCsvFriendly(summaries[i].SectorName),
                                                    SharePointUtil.ToCsvFriendly(summaries[i].PlName),
                                                    SharePointUtil.ToCsvFriendly(program.TargetId.ToString()),
                                                    SharePointUtil.ToCsvFriendly(program.TargetName),
                                                    SharePointUtil.ToCsvFriendly(program.ParticipantsCount.ToString()),
                                                    SharePointUtil.ToCsvFriendly(program.AmountAllocated.ToString("$###,###,###,##0")));
                                }
                            }
                        }
                    }
                }
            }

            Response.Write(sb.ToString());

            Response.Flush();
            Response.End();
        }

The big catch is the data manipulation once the data comes back from GetFundingData, I have to do it like that because our DBA is out and I need to knock this out. I thought I'd be able to just change the content type but that blows it up. Any help would be appreciated.

Can you elaborate on how your code "doesn't work"? What were you expecting, and what actually happened? If you got an exception/error, post the line it occurred on and the exception/error details. Please [edit] these details in or we may not be able to help. — Blue, Aug 10 '16 at 15:42
Good point. When i change the content type, it comes back essentially the same way as the CSV format, and doesn't split it up by column. — TrevorGoodchild, Aug 10 '16 at 15:45
XLS is old hat these days you'd be better off using xlsx see https://blogs.msdn.microsoft.com/chrisrae/2011/08/18/creating-a-simple-xlsx-from-scratch-using-the-open-xml-sdk/ — MikeT, Aug 10 '16 at 15:47
If your question concerns getting query results into an Excel file, save yourself the headache and use an Excel library that supports that natively. See this question, http://stackoverflow.com/questions/13669733/export-datatable-to-excel-with-epplus You can do it in just four lines of code. — user2023861, Aug 10 '16 at 15:48
If you don't explicitly need the deprecated format `.xls` I highly recommend you switch to `.xlsx` and use the Open XML SDK provided by Microsoft like the article MikeT linked to mentioned. Part of the problem is the Excel office interop does not work very well with IIS servers (like the sharepoint you are using). The Open XML SDK works fine on IIS server. — Scott Chamberlain, Aug 10 '16 at 15:49
user2023861 is right. You can't just arbitrarily convert csv to xls (or xlsx) by changing the file extension -- they are completely different file formats which store data in completely different ways. Use a library that will help you build an Excel file from scratch. — pymaxion, Aug 10 '16 at 15:51
Thanks for the input. I'll try Open XML. Something's wrong with my nuget manager, working to sort that out now. — TrevorGoodchild, Aug 10 '16 at 19:12

score 2 · Answer 1 · edited May 23 '17 at 12:00

I think the problem is your trying to use CSV formatting to create a XLS file. CSV uses a text based formatting with commas separating the data. XLS uses a binary style of formatting. Because XLS is a Microsoft file format, you will need to use Excel's Object Library to create the files. I do not know if you have the option, but if you can include EPPlus in your application, EPPlus can create, open, and edit XLSX files. XLSX is not XLS, but any version of Excel after the 2007 version can read both types.

[Edit]

Thank you Scott Chamberlain for pointing out that TrevorGoodchild is using a Sharepoint web service. As Scott Chamberlain has pointed out in his comment above, Open XML SDK is an option when using an IIS web service. In addition, because EPPlus does not use COM Interop it may also be usable in your application.

He is using Sharepoint (a IIS webservice) you can [not reliably run the COM Interop on a webservice](https://support.microsoft.com/en-us/kb/257757). — Scott Chamberlain, Aug 10 '16 at 16:02

C# Export to Excel xls

1 Answers1