Goals
                                                                            Introduction to Excel
                               Week02                                       To create excel files with C#
                         Excel with C#                                      To be able to read and write Excel files 
                                                                            To add/delete worksheet to/from an excel file
                             EEE474 / EEES404                               To read/import Excel file into DataSet aor DataTable
                      Database Programming for Internet                     To export DataGridView to Excel file
                                                                            C# datavalidation input box in excel file
                             Excel Basics - I                                             Excel Basics - II
                Excel spreadsheets organize information (text and numbers) 
                by rows and columns:                                         A cell is the intersection 
               This is a row.                                                between a column and a 
               Rows are represented by                                       row.  
               numbersalong the side                                         Each cell is named for the 
               of the sheet.                                                 column letter and row 
               This is a column.                                             number that intersect to 
               Columns are represented                                       make it.
               by letters across the top 
               of the sheet.
                    To create an Excel File in C#                                         Add Reference
               Before going to create new Excel file 
                programmatically in C#, 
               you must have Excel installed on your system for 
                this code to run properly.
               Excel Library
               To access the object model from Visual C# .NET, 
                you have to add the Microsoft Excel 15.0 Object 
                Library to the project.
                                                                                                                                       1
                                          Steps of Creating an Excel File
                                    First we have to initialize the Excel application Object.                                                                                               Example of Creating an Excel File
                                              Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                                    Before creating new Excel Workbook, you should check whether Excel is installed in your system.
                                              if  (xlApp == null) {     MessageBox.Show("Excel is not properly installed!!");    return; }
                                    Then create new Workbook
                                              xlWorkBook = xlApp.Workbooks.Add(misValue);
                                    After creating the new Workbook, next step is to write content to worksheet
                                              xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                                              xlWorkSheet.Cells[1, 1] = "ID";
                                              xlWorkSheet.Cells[1, 2] = "Name";
                                              xlWorkSheet.Cells[2, 1] = "1";
                                              xlWorkSheet.Cells[2, 2] = "One";
                                              xlWorkSheet.Cells[3, 1] = "2";
                                              xlWorkSheet.Cells[3, 2] = "Two";
                                    In the above code we write the data in the Sheet1.
                                              xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(2);
                                              xlWorkSheet.Cells[1, 1] = "Sheet 2 content"; // Sheet 2
                                    After write the content to the cell, next step is to save the excel file in the system.
                                              xlWorkBook.SaveAs("your-file-name.xls");
                               using System;                                                            Excel.Workbook xlWorkBook;
                               using System.Windows.Forms;                                              Excel.Worksheet xlWorkSheet;
                               using System.Runtime.InteropServices;                                    object misValue = System.Reflection.Missing.Value;                                         How to read an Excel file using C#
                               using Excel = Microsoft.Office.Interop.Excel;                            xlWorkBook = xlApp.Workbooks.Add(misValue);
                                                                                                        xlWorkSheet = 
                               namespace WindowsFormsApplication3                                 (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);                                            To access the object model from Visual C# .NET, you have to 
                               {                                                                                                                                                                     add the Microsoft Excel 15.0 Object Library to you project.
                                 public partial class Form1 : Form                                      xlWorkSheet.Cells[1, 1] = "ID";                                                          After import the reference library, we have to initialize the Excel 
                                 {                                                                      xlWorkSheet.Cells[1, 2] = "Name";
                                    public Form1()                                                      xlWorkSheet.Cells[2, 1] = "1";                                                               application Object.
                                                                                                        xlWorkSheet.Cells[2, 2] = "One";                                                                Excel.Application xlApp = new
                                    {                                                                   xlWorkSheet.Cells[3, 1] = "2";                                                                  Excel.Workbook xlWorkBook ;
                                       InitializeComponent();                                           xlWorkSheet.Cells[3, 2] = "Two";
                                    }                                                                                                                                                                   Excel.Worksheet xlWorkSheet ;
                                                                                                        xlWorkBook.SaveAs("I:\\csharp-Excel.xls",                                                       Excel.Range range ;
                                    private void button1_Click(object sender, EventArgs e) Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, 
                                                                                                  misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, 
                                    {                                                             misValue, misValue, misValue, misValue, misValue);
                                     Excel.Application xlApp = new                                      xlWorkBook.Close(true, misValue, misValue);                                              Next step is to open the Excel file and get the specified 
                               Microsoft.Office.Interop.Excel.Application();                            xlApp.Quit();                                                                                worksheet.
                                                                                                        Marshal.ReleaseComObject(xlWorkSheet);                                                          xlApp = new Excel.Application();
                                     if  (xlApp == null)                                                Marshal.ReleaseComObject(xlWorkBook);                                                           xlWorkBook = xlApp.Workbooks.Open(@"I:\csharp-Excel.xls", 0, true, 
                                    {                                                                   Marshal.ReleaseComObject(xlApp);                                                                   5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, 
                                     MessageBox.Show("Excel is not properly installed!");                                                                                                                  "\t", false, false, 0, true, 1, 0);
                                         return;                                                        MessageBox.Show("Excel file created , you can find the 
                                       }                                                          file I:\\csharp-Excel.xls");                                                                          xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                                                                                                      }
                                                                                                    }
                                                                                                  }
                              using System;                                                      xlApp = new Excel.Application();
                              using System.Windows.Forms;                                        xlWorkBook = xlApp.Workbooks.Open(@"I:\csharp-Excel.xls", 
                              using System.Runtime.InteropServices;                      0, true, 5, "", "", true, 
                              using Excel =                                              Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false,                                                  Update and Add Value
                              Microsoft.Office.Interop.Excel;                            0, true, 1, 0);
                                                                                                 xlWorkSheet = 
                              namespace WindowsFormsApplication4                         (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
                              {                                                                  range = xlWorkSheet.UsedRange;
                                  public partial class Form1 : Form                              rw = range.Rows.Count;
                                  {                                                              cl = range.Columns.Count;
                                     public Form1()                                              for (rCnt = 1; rCnt  < = rw; rCnt++)
                                     {                                                           {
                                        InitializeComponent();                                     for (cCnt = 1; cCnt  < = cl; cCnt++)
                                     }                                                             {
                                                                                                     str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
                                                                                                      MessageBox.Show(str);
                                  private void button1_Click(object                                }
                              sender, EventArgs e)                                               }
                                  {                                                              xlWorkBook.Close(true, null, null);
                                   Excel.Application xlApp ;                                     xlApp.Quit();
                                   Excel.Workbook xlWorkBook ;                                   Marshal.ReleaseComObject(xlWorkSheet);
                                   Excel.Worksheet xlWorkSheet ;                                 Marshal.ReleaseComObject(xlWorkBook);
                                   Excel.Range range ;                                           Marshal.ReleaseComObject(xlApp);
                                            string str;                                       }
                                            int rCnt, cCnt ;                                }
                                            int rw = 0, cl = 0;                          }
                                                                                                                                                                                                                                                                                                                                                2
                                                         using System;                                                                                                                                                                                                                           private void button2_Click(object sender, EventArgs e)
                                                         using System.Collections.Generic;                                                                                                                                                                                                             {
                                                         using System.ComponentModel;                                                                                                                                                                                                                     textBox1.Clear();
                                                         using System.Data;
                                                         using System.Drawing;                                                                                                                                                                                                                            textBox2.Clear();
                                                         using System.Linq;                                                                                                                                                                                                                               textBox3.Clear();
                                                         using System.Text;                                                                                                                                                                                                                            }
                                                         using System.Windows.Forms;
                                                         using Excel = Microsoft.Office.Interop.Excel;                                                                                                                                                                                           private void button1_Click(object sender, EventArgs e)
                                                         namespace WindowsFormsExcel02                                                                                                                                                                                                                 {
                                                         {                                                                                                                                                                                                                                                Excel.Range veri = (Excel.Range)ExcelSayfa.Cells[Convert.ToInt32(textBox2.Text), Convert.ToInt32(textBox3.Text)];
                                                            public partial class Form1 : Form                                                                                                                                                                                                             veri.Value2 = textBox1.Text;
                                                            {                                                                                                                                                                                                                                             textBox1.Clear();
                                                               public Form1()
                                                               {                                                                                                                                                                                                                                          textBox2.Clear();
                                                                   InitializeComponent();                                                                                                                                                                                                                 textBox3.Clear();
                                                               }
                                                               Excel.Application ExcelUygulama;                                                                                                                                                                                                           MessageBox.Show("Veri, belirttiğiniz hücreye yazılmıştır.");
                                                               Excel.Workbook ExcelProje;                                                                                                                                                                                                              }
                                                               Excel.Worksheet ExcelSayfa;
                                                               Excel.Range ExcelRange;                                                                                                                                                                                                                 private void Form1_Load(object sender, EventArgs e)
                                                               object Missing = System.Reflection.Missing.Value;                                                                                                                                                                                       {
                                                               private void button3_Click(object sender, EventArgs e)                                                                                                                                                                                     ExcelUygulama = new Excel.Application();
                                                               {
                                                                  SaveFileDialog save = new SaveFileDialog();                                                                                                                                                                                             ExcelProje = ExcelUygulama.Workbooks.Add(Missing);     //New Project
                                                                //   ExcelProje.SaveAs(save.FileName + ".xlsx", Excel.XlFileFormat.xlWorkbookDefault, Missing, Missing, false, Missing,                                                                                                                   ExcelSayfa = (Excel.Worksheet)ExcelProje.Worksheets.get_Item(1);//Yeni bir Sayfa oluşturalım. (Worksheet1, Worksheet2 dediğimiz olay...)
                                                         Excel.XlSaveAsAccessMode.xlNoChange);                                                                                                                                                                                                            ExcelRange = ExcelSayfa.UsedRange;            //Excelde kullanacağımız aralığı seçelim. (Hemen üstte ExcelSayfada Worksheet1'i seçtiğimizi görmelisiniz.)
                                                                   ExcelProje.SaveAs("I:\\csharp-Excel" + ".xls", Excel.XlFileFormat.xlWorkbookDefault, Missing, Missing, false, Missing,                                                                                                                 ExcelSayfa = (Excel.Worksheet)ExcelUygulama.ActiveSheet;    //Kullanacağımız Sayfayı (Worksheet1'i) ExcelSayfa değişkenine atayalım .
                                                         Excel.XlSaveAsAccessMode.xlNoChange);                                                                                                                                                                                                            ExcelUygulama.Visible = false;                                           //Excel uygulamamızı gizleyelim.
                                                                   ExcelProje.Close(true, Missing, Missing);                                                                                                                                                                                              ExcelUygulama.AlertBeforeOverwriting = false;                   //Uygulamamıza veri girmeden önce verilen uyarıyı gizleyelim
                                                                   ExcelUygulama.Quit();
                                                                   MessageBox.Show("Excel Uygulamanız başarılı bir şekilde kaydedilmiştir.");                                                                                                                                                          }
                                                               }                                                                                                                                                                                                                                    }
                                                                                                                                                                                                                                                                                                 }
                                                                     Read and Import Excel File into 
                                                                                                                               DataSet
                                                                                                                                                                                                                                                                                                        We used Microsoft Excel 15.0 Object Library 
                                                                                                                                                                                                                                                                                                               for read or write to and Excel file . 
                                                                                                                                                                                                                                                                                                        In C# without using Excel Object we can insert, 
                                                                                                                                                                                                                                                                                                               edit, delete, select etc. 
                                                                                                                                                                                                                                                                                                        In cell content of an Excel file using OLEDB .
                                                 using System;
                                                 using System.Drawing;
                                                 using System.Windows.Forms;                                                                                                                                                                                                                                                                                                    Summary
                                                 Using System.Data.OleDb;
                                                 using Excel = Microsoft.Office.Interop.Excel; 
                                                 namespace WindowsApplication1
                                                 {
                                                    public partial class Form1 : Form                                                                                                                                                                                                                                 We have seen how to use Excel with C#
                                                    {
                                                       public Form1()
                                                       {                                                                                                                                                                                                                                                              How to create an Excel file 
                                                          InitializeComponent();
                                                       }
                                                       private void button1_Click(object sender, EventArgs e)
                                                       {
                                                           try
                                                          {
                                                             System.Data.OleDb.OleDbConnection MyConnection;
                                                             System.Data.DataTable DtSet;
                                                             System.Data.OleDb.OleDbDataAdapter MyCommand;
                                                             MyConnection = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\test.xlsx; Extended 
                                                 Properties='Excel 12.0 xml;HDR=YES;'");
                                                             MyCommand = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [Sayfa1$]", MyConnection);
                                                             MyCommand.TableMappings.Add("Table", "TestTable");
                                                             DtSet = new System.Data.DataTable();
                                                             MyCommand.Fill(DtSet);
                                                             dataGridView1.DataSource = DtSet.DefaultView;
                                                             MyConnection.Close();
                                                          }
                                                          catch (Exception ex)
                                                          {
                                                             MessageBox.Show(ex.Message);
                                                          }    }}
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     3