Moving data from Sql Server to Excel via a Web Service

Question

My users want data from my application in Excel. The data resides in a SQL Server database but I don't want the users to have direct access to the database, I would rather provide them a web service to get the data. What is the best way to move data from SQL Server to Excel via a web service?

Answer 1

You can do it as a straight asp.net page and return a .csv file. If you change the mimetype to text/csv, it should default to open in excel. This would be the easiest approach, and one that I've used in the past with great success.

Answer 2

you can also blow back an html page with a table on it with a .xls filename, excel knows how to open this as well

Answer 3

The following code will generate an excel file from a datatable, you can just stream this to the user

    public static void CreateExcelFromDataTable(string filename, DataTable dt) {

        DataGrid grid = new DataGrid();
        grid.HeaderStyle.Font.Bold = true;
        grid.DataSource = dt;
        grid.DataMember = dt.TableName;

        grid.DataBind();

        // render the DataGrid control to a file

        using (StreamWriter sw = new StreamWriter(filename)) {
            using (HtmlTextWriter hw = new HtmlTextWriter(sw)) {
                grid.RenderControl(hw);
            }
        }
    }

Answer 4

Have the Web service emit a string buffer where cells are delimited by the tab character and rows by a carriage return/line feed. Then pipe the results of this into Excel with an Excel Web query. It's cheap, quick, a little bit dirty, but good for simple processes.

Answer 5

I'm in favor of Excel Web Query (Data/Import External) Also, there is a way to post data from Excel back to the web site. At least it's working in MS Sharepoint