locked
Help adapting EPPlus in Web Pages RRS feed

  • Question

  • User325035487 posted

    I am trying to adapt mikes article http://mikesdotnetting.com/article/278/a-better-way-to-export-gridviews-to-excel in Web Pages

    I am getting error on this line var adapter = new SqlDataAdapter(data); //Best Overload has invalid arguments error at this line

    @using OfficeOpenXml;
    @using System.Configuration;
    @using System.Data;
    @using System.Data.SqlClient;
    @using System.IO;
    @using System.Globalization;
    @{
        Layout = null;
        var db = Database.Open("eReq");
        int unitid;
    
        if (Session["unitid"] != null)
        {
            unitid = (int)Session["unitid"];
        }
        else
        {
            unitid = 0;
        }
    
    
        var y1 = UrlData[0].IsEmpty() ? 0 : UrlData[0].AsInt();
        var m1 = UrlData[1].IsEmpty() ? 0 : UrlData[1].AsInt();
        var altunit = Request.QueryString["unitid"].AsInt();
        if (altunit != 0)
        {
            unitid = altunit;
        }
        int days = DateTime.DaysInMonth(y1, m1);
        var sqldutydata = "";
        switch (days)
        {
            case 31:
                sqldutydata = "SELECT Full_Name AS Name, Emp_ID AS ID, D1 AS [1], D2 AS [2], D3 AS [3], D4 AS [4], D5 AS [5], D6 AS [6], D7 AS [7], D8 AS [8], D9 AS [9], D10 AS [10], D11 AS [11], D12 AS [12], D13 AS [13], D14 AS [14], D15 AS [15], D16 AS [16], D17 AS [17], D18 AS [18], D19 AS [19], D20 AS [20], D21 AS [21], D22 AS [22], D23 AS [23], D24 AS [24], D25 AS [25], D26 AS [26], D27 AS [27], D28 AS [28], D29 AS [29], D30 AS [30], D31 AS [31]  FROM DutySchedQ WHERE Years=@0 AND Months=@1 AND Unit_ID=@2";
                break;
            case 30:
                sqldutydata = "SELECT Full_Name AS Name, Emp_ID AS ID, D1 AS [1], D2 AS [2], D3 AS [3], D4 AS [4], D5 AS [5], D6 AS [6], D7 AS [7], D8 AS [8], D9 AS [9], D10 AS [10], D11 AS [11], D12 AS [12], D13 AS [13], D14 AS [14], D15 AS [15], D16 AS [16], D17 AS [17], D18 AS [18], D19 AS [19], D20 AS [20], D21 AS [21], D22 AS [22], D23 AS [23], D24 AS [24], D25 AS [25], D26 AS [26], D27 AS [27], D28 AS [28], D29 AS [29], D30 AS [30]  FROM DutySchedQ WHERE Years=@0 AND Months=@1 AND Unit_ID=@2";
                break;
            case 29:
                sqldutydata = "SELECT Full_Name AS Name, Emp_ID AS ID, D1 AS [1], D2 AS [2], D3 AS [3], D4 AS [4], D5 AS [5], D6 AS [6], D7 AS [7], D8 AS [8], D9 AS [9], D10 AS [10], D11 AS [11], D12 AS [12], D13 AS [13], D14 AS [14], D15 AS [15], D16 AS [16], D17 AS [17], D18 AS [18], D19 AS [19], D20 AS [20], D21 AS [21], D22 AS [22], D23 AS [23], D24 AS [24], D25 AS [25], D26 AS [26], D27 AS [27], D28 AS [28], D29 AS [29]  FROM DutySchedQ WHERE Years=@0 AND Months=@1 AND Unit_ID=@2";
                break;
            case 28:
                sqldutydata = "SELECT Full_Name AS Name, Emp_ID AS ID, D1 AS [1], D2 AS [2], D3 AS [3], D4 AS [4], D5 AS [5], D6 AS [6], D7 AS [7], D8 AS [8], D9 AS [9], D10 AS [10], D11 AS [11], D12 AS [12], D13 AS [13], D14 AS [14], D15 AS [15], D16 AS [16], D17 AS [17], D18 AS [18], D19 AS [19], D20 AS [20], D21 AS [21], D22 AS [22], D23 AS [23], D24 AS [24], D25 AS [25], D26 AS [26], D27 AS [27], D28 AS [28]  FROM DutySchedQ WHERE Years=@0 AND Months=@1 AND Unit_ID=@2";
                break;
        }
    
        var data = db.Query(sqldutydata, y1, m1, unitid);
        
        SqlDataAdapter adapter = new SqlDataAdapter(data); //Best Overload has invalid arguments error at this line
        
        var products = new DataTable();
        adapter.Fill(products);
        ExcelPackage excel = new ExcelPackage();
        var workSheet = excel.Workbook.Worksheets.Add("Duty");
        var totalCols = products.Columns.Count;
        var totalRows = products.Rows.Count;
    
        for (var col = 1; col <= totalCols; col++)
        {
            workSheet.Cells[1, col].Value = products.Columns[col - 1].ColumnName;
        }
        for (var row = 1; row <= totalRows; row++)
        {
            for (var col = 0; col < totalCols; col++)
            {
                workSheet.Cells[row + 1, col + 1].Value = products.Rows[row - 1][col];
            }
        }
        using (var memoryStream = new MemoryStream())
        {
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment;  filename=products.xlsx");
            excel.SaveAs(memoryStream);
            memoryStream.WriteTo(Response.OutputStream);
            Response.Flush();
            Response.End();
        }
        
    }

    P.S. I don't want to put connection string as a string in this page.

    Saturday, July 25, 2015 11:01 AM

Answers

  • User325035487 posted

    Solved it. For those interested

    ConnectionStringSettings mySetting = ConfigurationManager.ConnectionStrings["eReq"];
    var conString = mySetting.ConnectionString;
    SqlDataAdapter adapter = new SqlDataAdapter(sqldutydata, conString);
    adapter.SelectCommand.Parameters.AddWithValue("@0", y1);
    adapter.SelectCommand.Parameters.AddWithValue("@1", m1);
    adapter.SelectCommand.Parameters.AddWithValue("@2", unitid);

    instead of

    var data = db.Query(sqldutydata, y1, m1, unitid);
        
    SqlDataAdapter adapter = new SqlDataAdapter(data); //Best Overload has invalid arguments error at this line

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, July 25, 2015 11:44 AM