C# .NET - exporting data into Excel - Asked By raj on 16-May-12 04:32 AM

Dear Friends

I am writing a application for exporting data into Excel by using C#, what i want is when i write the value in Excel sheet i want to know the cell address like A10,A11,C10,C11 and so on . what should i do to get the cell addres. if any one help me on that it will be a great help to me.

Thanks  in advance



Somesh Yadav replied to raj on 16-May-12 05:17 AM
The following is some code that I have used to export data to excel:
private static void Excel(string fileName, List<IDirectoryInventoryDataCollector> list)
{
    try
    {
        var xlApp = new Excel.Application();
        var xlWorkBook = xlApp.Workbooks.Add();
        var xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        ExcelTitleRow(list[0], 1, xlWorkSheet);
 
        int row = 2;
        foreach (var item in list)
        {
            ExcelFillRow(item, row++, xlWorkSheet);
        }
 
        for (int i = 1; i < list[0].MaxLevel - 1; i++)
        {
            ((Range)xlWorkSheet.Columns[i]).ColumnWidth = 2;
        }
        ((Range)xlWorkSheet.Columns[list[0].MaxLevel - 1]).ColumnWidth = 30;
        ((Range)xlWorkSheet.Rows[1]).WrapText = true;
        ((Range)xlWorkSheet.Rows[1]).HorizontalAlignment = HorizontalAlignment.Center;
        ((Range)xlWorkSheet.Cells[1, 1]).WrapText = false;
 
        xlWorkBook.SaveAs(fileName);
        xlWorkBook.Close();
        xlApp.Quit();
    }
    catch (AccessViolationException)
    {
        System.Windows.Forms.MessageBox.Show(
             "Have encountered access violation. This could be issue with Excel 2000 if that is only version installed on computer",
             "Access Violation");
    }
    catch (Exception)
    {
        System.Windows.Forms.MessageBox.Show("Unknown error",
             "Unknown error");
    }
}
 
private static void ExcelFillRow(IDirectoryInventoryDataCollector item, int row, Excel.Worksheet sheet)
{
    sheet.Cells[row, item.Level] = item.Name;
    int column = item.MaxLevel;
    foreach (var property in item.GetProperties())
    {
        sheet.Cells[row, column++] = property;
    }
}
 
private static void ExcelTitleRow(IDirectoryInventoryDataCollector item, int row, Excel.Worksheet sheet)
{
    sheet.Cells[row, 1] = "Name";
    int column = item.MaxLevel;
    foreach (var property in item.GetPropertyNames())
    {
        sheet.Cells[row, column++] = property;
    }
}
 
It will have to be appropriately modified for what you need. You should be able to put tabs, commas, or something else between the fields before inserting into an Excel cell.
Venkat K replied to raj on 16-May-12 05:23 AM

it seems you are finding difficult to copy a range at particular position.
You need to just assign the respective column and row value under the cells.

Ex:
//if you want to Input data into B2 cell.
cells[10, 0].PutValue("Hello World!");   [Column 0, row 10]

//Set A10 cell as an active cell in the worksheet.
worksheet1.ActiveCell = "A10";

 

Thanks

dipa ahuja replied to raj on 16-May-12 06:36 AM
Add the reference in your project. Right click on the project -  > add reference
 
Now from the COM tab add two references
1.    Microsoft Office 12.0 Object Library
2.    Microsoft Excel 12.0 Object Library
 
  using Excel = Microsoft.Office.Interop.Excel;
 private void btnExport_Click(object sender, EventArgs e)
 {    
  // creating Excel Application
   Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
  // creating new WorkBook within Excel application
   Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
  // creating new Excelsheet in workbook
   Excel._Worksheet worksheet = null;
  // see the excel sheet behind the program
   app.Visible = false;
  // get the reference of first sheet. By default its name is Sheet1.
   // store its reference to worksheet
   worksheet = workbook.Sheets["Sheet1"];
   worksheet = workbook.ActiveSheet;
  // changing the name of active sheet
   worksheet.Name = "Exported from gridview";
 
  // storing header part in Excel
   for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
   {
   worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
   }
  // storing Each row and column value to excel sheet
   for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
   {
   for (int j = 0; j < dataGridView1.Columns.Count; j++)
   {
     worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
   }
   }
  // save the application
   workbook.SaveAs("d:\\output.xls", Type.Missing, Type.Missing, Type.Missing,
  Type.Missing,
  Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
  Type.Missing, Type.Missing, Type.Missing, Type.Missing);
  // Exit from the application
   app.Quit();
   MessageBox.Show("Excel file created , you can find the file d:\\output.xls");
 }