locked
How do I export data from table as Excel file RRS feed

  • Question

  • User743508062 posted

    Hi

    As usual I am using Mike's sample codes and as usual getting half of it wrong.

    I have amended the sample for my own use but need help on a couple of issues: The code is in a page called ExportCoatingSRPxl.cshtml

    @{
         Response.AddHeader("content-disposition", "attachment; filename=TestWebMatrix.xls");
        Response.ContentType = "application/ms-excel";
        using (var writer = new StreamWriter(Response.OutputStream)){
            writer.Write("dbo.qryAppFactsTotsXtab2");
            for(var i = 0; i < 10; i++){
                writer.Write("<tr><td>Some text</td></tr>");         }
            writer.Write("dbo.qryAppFactsTotsXtab2");
        }
        Response.End();
        
    }
    
    
    


    I have understood where to make the changes so it fits my needs but do not know what to put in the section:

    writer.Write("<tr><td>Some text</td></tr>");
    

    I am trying to export a cross tab query and this is the data I need coming out:

    LensCode", "OS", "HCu", "HCh", "HMCu", "HMCh", "HMCpu", "HMCph", "LT", "LT15", "ET", "ETPL", "GE", "GEPL", "SE", "SEPL", "DUR", "DUR15", "SF", "GF", "UMBRA", "GT", "SC", "BICOL", "POL"

    How can I limit the by Customercode?

    Then I need someone to explain how I can run this code from a page? For example my thinking is that I should install a button where the webgrid is and on click to open the page containing this code which when it opens it runs the code and exports the data. is this correct?

    Your help and advice is much needed. Thank you.

     

     

    Tuesday, June 4, 2013 6:53 AM

All replies

  • User379720387 posted

    I have also modified the export to Excel tutorial to suit my purposes. It was relatively painless.

    When I compare your code to mine I see significant differences to the structure of the code in yours.

    Here is the code section that I have which is pretty much original. Yours is quite different.

    Response.AddHeader("Content-disposition", "attachment; filename=report.xls");
    Response.ContentType = "application/octet-stream";
    Response.TransmitFile(newFile);
    Response.Flush();
    File.Delete(newFile);
    Response.End();

    The tutorial uses a webgrid and an excel icon to trigger the export.

    Tuesday, June 4, 2013 8:46 AM
  • User1713851783 posted

    Last year, I have published an article about exporting data to excel in Webmatrix: Export data to Excel in Webmatrix.

    Maybe it could help you.

    Tuesday, June 4, 2013 9:19 AM
  • User-2046151848 posted

    This blog may help you:

    Exporting The Razor WebGrid To Excel Using OleDb

    http://www.mikesdotnetting.com/Article/207/Exporting-The-Razor-WebGrid-To-Excel-Using-OleDb

    Tuesday, June 4, 2013 10:38 AM
  • User-1980594115 posted

    You can check out EPPLUS which creates Excel spreadsheets.

     

    http://epplus.codeplex.com/

     

     

    Tuesday, June 4, 2013 1:36 PM
  • User743508062 posted

    sorry its taken me so long to reply but had meetings etc.

    OK back to exporting to excel, thanks you for the link to Mike's tutorial, just to explain my original code was taken from his ans to a similar Q in this forum. The link to the tutorial has helped me galvanise the code to meet my needs. Unfortunately I have tried to follow the tutorial but cannot get the icon to click and generate well anything not even an error, so I need youe help.

    My generate page is called GenSRPlens.cshtml and it contains the following code

    @{    
          Layout = null;    
          
          var appData = Server.MapPath("~/App_Data");    
          var originalFileName = "SRPLens.xls";    
          var newFileName = string.Format("{0}.xls", Guid.NewGuid().ToString());    
          var originalFile = Path.Combine(appData, originalFileName);    
          var newFile = Path.Combine(appData, newFileName);    
          File.Copy(originalFile, newFile);
    
          var lenscat =Database.Open("A-LensCatFE-01SQL");
          var CustomerCode = UrlData[0];
    
          var sql = ("Select * from dbo.qryDiscountLensPrice WHERE CustomerCode=@0 Order by LensCode");
          var srplensdata = lenscat.Query(sql, CustomerCode);
    
          var connString = string.Format(@"Provider=Microsoft.Jet.OleDb.4.0; Data Source={0}/{1};Extended Properties='Excel 8.0;HDR=Yes;'", appData, newFileName);    
          var provider = "System.Data.OleDb";    
          using (var excel = Database.OpenConnectionString(connString, provider)){
              sql = @"INSERT INTO [Sheet1$] (LensCode, LensForm, ShortName, Description, Index, Cost, DiscountPrice, SRP)           
                    VALUES (@0,@1,@2,@3,@4,@5,@6,@7)";
    
        foreach(var srplens in srplensdata){            
            excel.Execute(sql,                  
            srplens.LensCode,                
            srplens.LensForm,                 
            srplens.ShortName,                 
            srplens.Description,                 
            srplens.Index,                 
            srplens.Cost,                 
            srplens.DiscountPrice,
            srplens.SRP);        
            }    
            }    
            Response.AddHeader("Content-disposition", "attachment; filename=SRPLens.xls");    
            Response.ContentType = "application/octet-stream";    
            Response.TransmitFile(newFile);    
            Response.Flush();    
            File.Delete(newFile);    
            Response.End();
        
    }

    In my situation I have placed the image button and jquery script in a page called DiscountCustomers:

    <img src="/images/exllens.png" id="SRPLens" alt="Export Lens SRP" title="Export Lens SRP Data to Excel" />  
    
    @section scripts{
    <script type="text/javascript">
            $(function () {        
        $('#SRPLens').on('click', function () {            
                    $('<iframe src="/GenSRPLens"></iframe>').appendTo('body').hide();        
            });    
        });
    </script>
    
    }

    I don't want it to sit in the grid but in the main body, however though I have the image in my page, I do not have the click or anything else.

    Please help.



    Thursday, June 6, 2013 10:22 AM
  • User743508062 posted

    Hi, sorry I've been awaym took a short break, but now I am back.

    I would be grateful if someone could have a look at my attempts and advice me on why it is not working. Please.

    Thank you.

    Monday, June 10, 2013 4:47 AM
  • User743508062 posted

    Thanks for the link, I have now modded my code to this:

    <img src="/images/exllens.png" id="SRPLens" alt="Export Lens SRP" title="Export Lens SRP Data to Excel" />
    
    
    @section scripts{
    <script type="text/javascript">
            $(function () {  
            $('#SRPLens').click(function () {            
                    $('<iframe src="/GenSRPLens.cshtml"></iframe>').appendTo('body').hide();        
            });    
        });
    </script>
    }

    On clicking I get the browser bar at the footer asking if I want to open or save the file, which is great - that is what we want.

    However the generate the export codes is not pulling the data and I believe it has something to do with the code relating to the UrlData.

    I do not believe the UrlData[0] is being passed to GenSRPLens page and hence no data is found for it to export.

    How can I ensure that the UrlData is passed across so that the CustomerCode value is found and can be used to obtain the data.

    This is the url:

    http://localhost:xxxx/DiscountCustomers/465520

    The value 465520 is the CustomerCode that I want pass from DiscountCustomers to GenSRPLens:

    @{    
          Layout = null;    
          
          var appData = Server.MapPath("~/App_Data");    
          var originalFileName = "SRPLens.xls";    
          var newFileName = string.Format("{0}.xls", Guid.NewGuid().ToString());    
          var originalFile = Path.Combine(appData, originalFileName);    
          var newFile = Path.Combine(appData, newFileName);    
          File.Copy(originalFile, newFile);
    
          var lenscat =Database.Open("A-LensCatFE-01SQL");
          var CustomerCode = UrlData[0];
    
          var sql = ("Select * from dbo.qryDiscountLensPrice WHERE CustomerCode=@0 Order by LensCode");
          var srplensdata = lenscat.Query(sql, CustomerCode);
    
          var connString = string.Format(@"Provider=Microsoft.Jet.OleDb.4.0; Data Source={0}/{1};Extended Properties='Excel 8.0;HDR=Yes;'", appData, newFileName);    
          var provider = "System.Data.OleDb";    
          using (var excel = Database.OpenConnectionString(connString, provider)){
              sql = @"INSERT INTO [Sheet1$] (LensCode, LensForm, ShortName, Description, Index, Cost, DiscountPrice, SRP)           
                    VALUES (@0,@1,@2,@3,@4,@5,@6,@7)";
    
        foreach(var srplens in srplensdata){            
            excel.Execute(sql,                  
            srplens.LensCode,                
            srplens.LensForm,                 
            srplens.ShortName,                 
            srplens.Description,                 
            srplens.Index,                 
            srplens.Cost,                 
            srplens.DiscountPrice,
            srplens.SRP);        
            }    
            }    
            Response.AddHeader("Content-disposition", "attachment; filename=SRPLens.xls");    
            Response.ContentType = "application/octet-stream";    
            Response.TransmitFile(newFile);    
            Response.Flush();    
            File.Delete(newFile);    
            Response.End();
        
    }
    
    

    Thanks for helping.


    Monday, June 10, 2013 7:46 AM