Excel sheets read and write using c#

When we have to work with excel sheets frequently using c# language, rather than implementing methods inside the main project we can create API with implementing those methods.then we can reuse it at later stages.here i have explained how to create API and how to access it.

Here we can add references by right clicking on references which is on solution explorer.then add needed references which displays below in top of the .cs class.
and then i have clearly implemented functions separately how to load excel sheets, read excel sheets and write to excel sheets.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Drawing;
using System.Reflection;
using System.Windows.Forms;
using QiHe.CodeLib;
using ExcelLibrary.CompoundDocumentFormat;
using ExcelLibrary.BinaryFileFormat;
using ExcelLibrary.SpreadSheet;
using Excel = Microsoft.Office.Interop.Excel;

namespace CECB_API
{

    public class xml_refer
    {
    

        public void LoadExcelSheets(CompoundDocument file, TabControl tab)
        {


            byte[] bookdata = file.GetStreamData("Workbook");
            if (bookdata == null) return;
            Workbook book = WorkbookDecoder.Decode(new                      MemoryStream(bookdata));

            tab.TabPages.Clear();

            foreach (Worksheet sheet in book.Worksheets)
            {
                TabPage sheetPage = new TabPage(sheet.Name);
                DataGridView dgvCells = new DataGridView();
                dgvCells.Dock = DockStyle.Fill;
                dgvCells.RowCount = sheet.Cells.LastRowIndex + 1;
                dgvCells.ColumnCount = sheet.Cells.LastColIndex +                  1;

                // tranverse cells
                foreach (Pair<Pair<int, int>, Cell> cell in sheet.Cells)
                {
                    dgvCells[cell.Left.Right, cell.Left.Left].Value = cell.Right.Value;
                    if (cell.Right.Style.BackColor != Color.White)
                    {
                        dgvCells[cell.Left.Right, cell.Left.Left].Style.BackColor = cell.Right.Style.BackColor;
                    }
                }

                // tranvers rows by Index
                for (int rowIndex = sheet.Cells.FirstRowIndex; rowIndex <= sheet.Cells.LastRowIndex; rowIndex++)
                {
                    Row row = sheet.Cells.GetRow(rowIndex);
                    for (int colIndex = row.FirstColIndex; colIndex <= row.LastColIndex; colIndex++)
                    {
                        Cell cell = row.GetCell(colIndex);
                    }
                }
                // tranvers rows directly
                foreach (KeyValuePair<int, Row> row in sheet.Cells.Rows)
                {
                    foreach (KeyValuePair<int, Cell> cell in row.Value)
                    {
                    }
                }


                foreach (KeyValuePair<Pair<int, int>, Picture> cell in sheet.Pictures)
                {
                    int rowIndex = cell.Key.Left;
                    int colIndex = cell.Key.Right;
                    if (dgvCells.RowCount < rowIndex + 1)
                    {
                        dgvCells.RowCount = rowIndex + 1;
                    }
                    if (dgvCells.ColumnCount < colIndex + 1)
                    {
                        dgvCells.ColumnCount = colIndex + 1;
                    }
                    dgvCells[colIndex, rowIndex].Value = String.Format("<Image,{0}>", cell.Value.Image.FileExtension);
                }

                sheetPage.Controls.Add(dgvCells);
                tab.TabPages.Add(sheetPage);
            }
        }


      /// <summary>
      /// mypath parameter should give full path from the location of a file
      /// var datatype has used for future need.it is implicit type
      /// 1.open excel sheet using given path
      /// </summary>
      /// <param name="myPath"></param>
      /// <param name="stage"></param>
      /// <returns></returns>

        
        public string[] readarray(string myPath, string stage)
        {
            FileInfo fi = new FileInfo(myPath);

            var excelApp = new Microsoft.Office.Interop.Excel.Application();
            var workbook = excelApp.Workbooks.Open(myPath);
            Excel.Worksheet worksheet = workbook.Sheets.get_Item(1);

            int count = worksheet.UsedRange.Rows.Count;

          
            string col = "";
            if (stage == "1")
            {
                col = "A";
            }
            else if (stage == "2")
            {
                col = "B";
            }

            else if (stage == "3")
            {
                col = "C";
            }
            else if (stage == "4")
            {
                col = "D";
            }
            else if (stage == "5")
            {
                col = "E";
            }
            else if (stage == "6")
            {
                col = "F";
            }

           
            else
            {
                MessageBox.Show("Give valid stage..");
            }

            string[] statge1 = new string[count];
            string toDisplay = "";


            for (int x = 1; x < count; x++)
            {

                statge1[x] = Convert.ToString((worksheet.Cells[x.ToString(), col]).Value2);
                toDisplay = string.Join(",", statge1);

            }

          
            return statge1;
        }
        

        public void openandwrite(string row, string col, string value)
        {
            Excel.Application excelApp = new Excel.Application();
            Excel.Workbook workbook = (Excel.Workbook)excelApp.Workbooks.Add(Missing.Value);
            Excel.Worksheet worksheet;

            worksheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);

            ((Excel.Range)worksheet.Cells[row, col]).Value2 = value;

            excelApp.Visible = true;
        }


        public void openexistingexcel(string myPath)
        {
           // this must be full path.
            FileInfo fi = new FileInfo(myPath);
            if (!fi.Exists)
            {
                MessageBox.Show("file doesn't exists!");
            }
            else
            {
                var excelApp = new Microsoft.Office.Interop.Excel.Application();
                var workbook = excelApp.Workbooks.Open(myPath);


                Excel.Worksheet worksheet;

                worksheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);
                excelApp.Visible = true;
            }
        }

        public void WriteToExcel(string row, string col, string val,string myPath)
        {
            // this must be full path.

            FileInfo fi = new FileInfo(myPath);
            if (!fi.Exists)
            {
                MessageBox.Show("file doesn't exists!");
            }
            else
            {
                var excelApp = new Microsoft.Office.Interop.Excel.Application();
                var workbook = excelApp.Workbooks.Open(myPath);


                Excel.Worksheet worksheet;

                worksheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);
               

                    ((Excel.Range)worksheet.Cells[row, col]).Value2 = val;

                    excelApp.Visible = true;
           

                workbook.Save();
               workbook.Close();



            }
        }


    }
    
}

After developing API we can run it and add it as dll to our main project.then we have to do is create object from API and calling the methods.following is another example for method calling.

 CECB_API.xml_refer a = new CECB_API.xml_refer();


Since it is time consuming function i created thread here.

 public void Function()
        {
            val = cmbCertificateNo.SelectedValue.ToString();

            BackgroundWorker worker = new BackgroundWorker();
            worker.DoWork += TimeConsumingFunction;
            //// var frame = new DispatcherFrame();
            worker.RunWorkerCompleted += (sender, args) =>
            {
                //    // frame.Continue = false;
            };
            worker.RunWorkerAsync();
            //// Dispatcher.PushFrame(frame);
        }

This is the actual implementaion where i read my excel data using object created from API

        private void TimeConsumingFunction(object sender, DoWorkEventArgs doWorkEventArgs)
        {

            try
            {
                  MessageBox.Show(cmbCertificateNo.SelectedItem.ToString());
                string aw = val;
                string[] stage1 = a.readarray(Chosen_File, "2");


                string[] stage2 = a.readarray(Chosen_File, "3");
                string[] stage3 = a.readarray(Chosen_File, "4");
                string[] stage4 = a.readarray(Chosen_File, "5");
                string[] stage5 = a.readarray(Chosen_File, "6");
                string[] date = a.readarray(Chosen_File, "1");

                bool status = false;

                int length = 136;
                for (int x = 1; x < length; x++)
                {

                    string qry = "INSERT INTO `realdata` (`projectID`,`datentime`,  `sgage1`,  `sgage2`,  `sgage3`,  `sgage4`,  `force_`) VALUES ('"+aw+"','" + date[x].ToString() + "','" + stage1[x].ToString() + "','" + stage2[x].ToString() + "','" + stage3[x].ToString() + "','" + stage4[x].ToString() + "','" + stage5[x].ToString() + "')";
                    status=MVCAController.db_access.InsertData(qry);
                }
                if (status)
                {
                    killexcelprocess();
                    do
                    {
                        Thread.Sleep(10);
                    }
                    while (progressBar2.Value == 100);
                }

                else
                {
                    timer1.Stop();

                    killexcelprocess();
                    MessageBox.Show("Insert Fail !", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
               
                }
            }
            catch (Exception ex)
            {
                timer1.Stop();
                killexcelprocess();
                MessageBox.Show("First select a File path to store "+ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
}


When we work with excel it starts background running.so we should kill excel processes all times.this code will end all excel processes at necessary locations

  public void killexcelprocess()
        {
             foreach (Process clsProcess in Process.GetProcesses())
                if (clsProcess.ProcessName.Equals("EXCEL"))  //Process Excel?
                    clsProcess.Kill();

           
        }

This is how it displays when excel data have been opened.