Exporting DataGrid to Excel

Exporting DataGrid to Excel

• rod_r

  Hi:

  Everything works ok, but...

  What if i want to overwrite the excel archive

  How can i avoid the question of ( Do you want to replace it )

  Thanks

  
  
  

• chakravarthi

  You could use FileInfo class to check if file with the same name already exists. If yes, then you could "silently" delete it from the code.
  
  

• Bill Thoreson

  There is no direct way to do this, but there are several ways to accomplish it using your code

  1. Use Jet OLEDB Provider. You need to create INSERT SQL statements and loop through the rows of the grid to insert records one\ by one

  2. Use Microsoft Office Tools for .NET. This way is slower, but gives you more functionality and allows formatting of the data.

  3. Write code that stores data into CSV or HTML. Those formats recognized by Excel.

  4. Use third party components that could provide desired functionality

  Following are some links wit the samples

  http://support.microsoft.com/kb/306022/en-us

  http://support.microsoft.com/kb/326548/en-us

  http://support.microsoft.com/kb/307029/en-us

  
  
  

• Tad McClellan

  If you don't mind using Interop, you can communicate with Excel via Primary interop Assemblies.

  PIA Download:
  http://www.microsoft.com/downloads/details.aspx FamilyID=3c9a983a-ac1...
  

  Primer
  http://msdn2.microsoft.com/en-us/library/aa201322(office.11).aspx

  Working with the Office XP Primary Interop Assemblies
  http://msdn2.microsoft.com/en-us/library/aa163987(office.10).aspx

  Known Issues:
  http://msdn2.microsoft.com/en-us/library/aa163989(office.10).aspx

  Sample:

  using System;

  using System.Reflection; // Required for Missing.Value

  using ExcelApplication = Microsoft.Office.Interop.Excel.Application; // conflicts with Forms App object otherwise

  using Microsoft.Office.Interop.Excel;

  namespace JHalesExcelWriter

  {

  /// </summary>

  public class ExcelExport

  {

  private readonly ExcelApplication _excel;

  private Worksheet _worksheet; // Gets set when createnew is called

  /// <summary>

  /// Ctro - creates an isntance of Excel

  /// </summary>

  public ExcelExport()

  {

  _excel = new ExcelApplication();

  }

  /// <summary>

  /// Creates a new worksheet and makes that the active sheet. Note soemtimes you get a nasty HRESULT exception

  /// which is meaningless to the user

  /// </summary>

  public void CreateNewWorkBook()

  {

  try

  {

  _excel.Workbooks.Add(Missing.Value);

  _worksheet = (Worksheet)_excel.ActiveSheet;

  }

  catch(COMException ex)

  {

  throw new ApplicationException("Error communicating with Excel. Please close this application and try again", ex);

  }

  }

  /// <summary>

  /// Create a header cell - at row 1 (remember Excel is 1 based)

  /// Uses the fieldName as the caption

  /// Does nice formatting with _ characters

  /// </summary>

  private void WriteHeaderCell(int col, string caption)

  {

  const int HEADER_ROW = 1;

  Range range = GetCell(col, HEADER_ROW);

  range.Value2 = caption;

  range.Interior.ColorIndex = 1;

  // Nice single border around the cells

  Borders borders = range.Borders;

  borders.LineStyle = 1;

  borders.Value = 1;

  borders.Weight = 2;

  range.Font.Bold = true;

  range.Font.Size = 12;

  }

  public void WriteRow(System.Data.DataRow row, int rowIndex)

  {

  for(int i = 0; i < row.Table.Columns.Count; i++)

  {

  string cellValue = row as string;

  if (cellValue != null && cellValue.Length > 0)

  {

  Range range = GetCell(i + 1, rowIndex);

  range.Value2 = cellValue;

  if (row.Table.Columns.DataType == typeof(DateTime))

  {

  range.NumberFormat = "dd/MM/yyyy";

  range.Interior.ColorIndex = 2;// date colour

  }

  }

  }

  }

  /// <summary>

  /// Show Excel - can slow thigns down when painting cells

  /// </summary>

  public void Show()

  {

  _excel.Visible = true;

  _excel.ScreenUpdating = true;

  _excel.DisplayAlerts = true;

  }

  /// <summary>

  /// Hide Excel screen - makes it bit faster

  /// </summary>

  public void Hide()

  {

  _excel.ScreenUpdating = false;

  _excel.DisplayAlerts = false;

  }

  /// <summary>

  /// Returns a range for the specificed row/col

  /// </summary>

  private Range GetCell(int col, int row)

  {

  return _worksheet.Cells[row, col] as Range;

  }

  /// <summary>

  /// Returns a rectangular cell from topLeft to bottomRight

  /// </summary>

  private Range GetRange(int col1, int row1, int col2, int row2)

  {

  string address1 = GetAbsoluteAddress(col1, row1);

  string address2 = GetAbsoluteAddress(col2, row2);

  return _worksheet.get_Range(address1, address2) as Range;

  }

  /// <summary>

  /// Converts a row/col ref into an absolute string address

  /// </summary>

  /// <returns></returns>

  private string GetAbsoluteAddress(int col, int row)

  {

  const int COL_Z = 26;

  const int ASCII_A = 65;

  int colIndex = (col - 1) / COL_Z; // Work out which chunk were in

  int remainder = (col - 1) % COL_Z; // Get the remainder

  string colAddress;

  if (colIndex == 0)

  {

  colAddress = ((char)(ASCII_A + col - 1)).ToString();

  }

  else

  {

  colAddress = ((char)(ASCII_A + colIndex - 1)).ToString() +

  ((char)(ASCII_A + remainder)).ToString();

  }

  return string.Concat(colAddress, row.ToString());

  }

  }

  }

  
  

• _brg_

  ok

  i do this

  if oBook.exist = true then

  end sub

  Ok, it exist but theres no code or instruccion that helps me to write again

  
  
  

• 22Pom

  Thanks to both of you.
  
• Exporting DataGrid to Excel