Create excel sheet on fly RSS

• • Reply
  

  dathatriA

  Member

  2 Points

  3 Posts

  Create excel sheet on fly

  May 29, 2012 12:11 PM|LINK

  I have a button named "Export to excel". after click on this button i need to export a datatable as excel and need to do formatting.

  Can u please tell me the best way to do this.

• • Reply
  

  urenjoy

  Star

  13383 Points

  2006 Posts

  Re: Create excel sheet on fly

  May 29, 2012 12:13 PM|LINK

  Check following:

  http://www.codeproject.com/Articles/164582/8-Solutions-to-Export-Data-to-Excel-for-ASP-NET

  http://forums.asp.net/t/1744797.aspx/1

  http://techbrij.com
  • Edited by urenjoy on May 29, 2012 12:14 PM
• • Reply
  

  FarhanK

  Contributor

  2603 Points

  350 Posts

  Re: Create excel sheet on fly

  Jun 06, 2012 12:06 AM|LINK

  Hi,
  The code below would work if you are willing to use office web components.

  
  
  using Microsoft.Office.Interop.Owc11;
  
  #region Excel creation with ocw11.
      /// <summary>
      /// Crate excel file using office web components from a dataset.
      /// </summary>
      /// <param name="ds">data set</param>
      /// <param name="filePath">Full file path of file. </param>
      /// <param name="recordsPerSheet">number of records you want to display per sheet.</param>
      /// <param name="context">HTTP context. Send if you want to write the file to the response's output stream otherwise send null</param>
      public static void CreateExcelFile(DataSet ds, string filePath, int recordsPerSheet, System.Web.HttpContext context)
      {
          int index = 1;
          int r = 1;
          int c = 1;
          bool Flag = true;
          Spreadsheet ExcelSheet = new SpreadsheetClass();
          Worksheet Sheet = null;
  
  
          try
          {
              for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
              {
                  c = 1;
                  Flag = false;
                  if (i % recordsPerSheet == 0)
                  {
                      Sheet = (Worksheet)ExcelSheet.Worksheets[index];
                      Flag = true;
                      r = 2;
                      index++;
                      if ((index > 3) && (index <= (ds.Tables[0].Rows.Count / recordsPerSheet)))
                      {
                          ExcelSheet.Worksheets.Add(Type.Missing, Sheet, 1, Type.Missing);
                      }
                  }
                  for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                  {
                      if (Flag)
                      {
                          Range R = (Range)Sheet.Cells[r - 1, c];
                          R.Font.set_Bold(true);
                          R.Value2 = ds.Tables[0].Columns[j].ColumnName;
                          
  
                          Object Obj = "Yellow";
                          R.Interior.set_Color(ref Obj);
                          
  
                      }
                      Sheet.Cells[r, c] = ds.Tables[0].Rows[i][j].ToString();
                      ((Range)Sheet.Cells[r, c]).set_NumberFormat("Text");
                      c++;
                  }
                  r++;
              }
              if (context == null)
              {
                  ExcelSheet.Export(filePath, SheetExportActionEnum.ssExportActionNone, SheetExportFormat.ssExportXMLSpreadsheet);
              }
              else
              {
                  context.Response.Clear();
                  context.Response.Buffer = true;
                  context.Response.ContentType = "application/vnd.ms-excel";
                  context.Response.AddHeader("Content-Disposition", "inline;filename=" + Path.GetFileName(filePath));
                  context.Response.Charset = "";
                  context.Response.Write(ExcelSheet.XMLData);
                  context.Response.End();
              }
          }
          finally
          {
              try
              {
                  System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelSheet);
              }
              catch
              {
                  ExcelSheet = null;
              }
          }
      }
      #endregion

  [Please mark the post as answer that helps you.]
  
  Regards,
  Farhan Uddin Khan
  Breeze Technologies
  • Edited by FarhanK on Jun 06, 2012 12:07 AM
  • Marked as answer by Young Yang - MSFT on Jun 14, 2012 02:45 PM