How do I export data from table as Excel file RSS

• • Reply
  Liquidmetal

  Member

  52 Points

  252 Posts

  How do I export data from table as Excel file

  Jun 04, 2013 06:53 AM|Liquidmetal|LINK

  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.

   

   

• • Reply
  wavemaster

  Contributor

  2590 Points

  2661 Posts

  Re: How do I export data from table as Excel file

  Jun 04, 2013 08:46 AM|wavemaster|LINK

  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.

  Click on "Mark as Answer" if my answers help you along.
• • Reply
  GmGregori

  Contributor

  4946 Points

  873 Posts

  Re: How do I export data from table as Excel file

  Jun 04, 2013 09:19 AM|GmGregori|LINK

  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.

• • Reply
  lxy

  Member

  2 Points

  11 Posts

  Re: How do I export data from table as Excel file

  Jun 04, 2013 10:38 AM|lxy|LINK

  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

• • Reply
  dblaire

  Member

  690 Points

  185 Posts

  Re: How do I export data from table as Excel file

  Jun 04, 2013 01:36 PM|dblaire|LINK

  You can check out EPPLUS which creates Excel spreadsheets.

   

  http://epplus.codeplex.com/

   

   

• • Reply
  Liquidmetal

  Member

  52 Points

  252 Posts

  Re: How do I export data from table as Excel file

  Jun 06, 2013 10:22 AM|Liquidmetal|LINK

  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.

  
  
  

• • Reply
  Liquidmetal

  Member

  52 Points

  252 Posts

  Re: How do I export data from table as Excel file

  Jun 10, 2013 04:47 AM|Liquidmetal|LINK

  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.

• • Reply
  Liquidmetal

  Member

  52 Points

  252 Posts

  Re: How do I export data from table as Excel file

  Jun 10, 2013 07:46 AM|Liquidmetal|LINK

  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.