C# Export data from data source to excel

Comments

• serifelsen Unknown

  April 2011 edited 7:19PM

  serifelsen wrote: »

  Hi
  
  I need to manually export report data to excel because fast report does not provide "Export only data". I need to export only data (without report title, page header only once)
  
  So, first i need to get "Data source" of the "Data" band. and then i have to export data in "Data source" to excel
  
  please provide the sample code. (C#)
  
  Thanks in advance

  
  I did it by myself (works perfect)
  
  /// <summary>
  /// Export data to excel
  /// </summary>
  public void Export()
  {
  // Show mode
  String DataSourceName = ((DataBand)fReport.FindObject("Data1")).DataSource.Name;
  String selectCommand = ((TableDataSource)fReport.Dictionary.FindByName(DataSourceName)).SelectCommand;
  
  // connect to database
  OracleConnection conn = database_Connect();
  
  // Call report from database
  OracleCommand command = new OracleCommand(selectCommand, conn);
  
  // Execute command
  OracleDataReader reader = command.ExecuteReader();
  
  string tempPath = System.IO.Path.GetTempPath();
  string myUniqueFileName = string.Format(@{0}.xls, Guid.NewGuid());
  
  // export to excel file
  ExportToExcel(reader, tempPath + myUniqueFileName);
  
  // call excel file
  Process.Start(tempPath + myUniqueFileName);
  }
  
  /// <summary>
  /// Export to excel file
  /// </summary>
  /// <param name="reader">Oracle reader</param>
  /// <param name="fileName">Excel file name</param>
  public void ExportToExcel(OracleDataReader reader, string fileName)
  {
  Excel.Application xlApp;
  Excel.Workbook xlWorkBook;
  Excel.Worksheet xlWorkSheet;
  
  object misValue = System.Reflection.Missing.Value;
  
  xlApp = new Excel.ApplicationClass();
  xlWorkBook = xlApp.Workbooks.Add(misValue);
  
  xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
  
  try
  {
  //Add Headers
  for (int count = 0; count < reader.FieldCount; count++)
  {
  if (reader.GetName(count) != null)
  {
  xlWorkSheet.Cells[1, count + 1] = reader.GetName(count);
  }
  }
  
  //Append Data
  int row = 2;
  while (reader.Read())
  {
  for (int col = 0; col < reader.FieldCount; col++)
  {
  if (!reader.IsDBNull(col))
  {
  xlWorkSheet.Cells[row, col + 1] = reader.GetValue(col).ToString();
  }
  }
  
  row++;
  }
  reader.Dispose();
  }
  catch (Exception)
  {
  
  }
  finally
  {
  xlWorkBook.SaveAs(fileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
  xlWorkBook.Close(true, misValue, misValue);
  xlApp.Quit();
  }
  }
• Landon Evans usa

  February 2019 edited 7:19PM

  I suggest you try the EXCEL SDK. that will help you to create fast excel sheet. try https://zetexcel.com/. it offers the best excel spreadsheet programming services for .NET to convert spreadsheets.