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.

Answer 1

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.