Wednesday 16 May 2012

How to Open,Edit, Save Excel Sheet in .NET Windows Application




Note: Don't Forget to add References. For Add reference follow given below process:

1. Right Click on Reference folder in Solution Explorer.
2. Choose Add reference Option, then a Reference Dialog box will be open.
3. In the dialog box Choose .NET Tab, then choose three assemblies.
          (a.)Interop.Microsoft.Office.Interop.Excel
          (b.)Interop.Microsoft.Office.Interop
          (c.)Microsoft.VisualBasic

Now Add these Assemblies in Your project.


using Excel=Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop;
using Microsoft.VisualBasic;


public void EditExcel()
        {
            try
            {
             
                int row = 1;

        openFileDialog1.ShowDialog();// Open Dialog Box //control should be defined in Form Conrol

        textBox1.Text = openFileDialog1.FileName;
            if (textBox1.Text =="")
            {
                Interaction.MsgBox("Please Select File Path", MsgBoxStyle.Information, "Employee");
                return;
            }

string file =System.IO.Path.GetFullPath(textBox1.Text);


                Excel.Application app = new Excel.Application();

                Excel.Workbook workbook;
                app = new Excel.Application();

                app.Visible = false;

                workbook = app.Workbooks.Open(file, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

                Excel.Worksheet excel = (Excel.Worksheet)workbook.Sheets.get_Item(1);//Missing.Value, Missing.Value, 1, Excel.XlSheetType.xlWorksheet);

                excel.Name = "In_&_Out_data";

                // for delete the "A" Index Column
                while (row < excel.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Missing.Value).Row + 1)
                {
                    if (excel.Cells[row, 5].ToString() == "Total : ")
                    {
                        excel.Cells[row, 5] = "";
                    }
                    row++;
                }

                Excel.Range range = (Excel.Range)excel.get_Range("A1", Missing.Value);
                range.EntireColumn.Delete(Missing.Value);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(range);


                // Remove the "Total:" string from file

                Filename = "C:\\" + excel.Name + ".xlsx";
                if (System.IO.File.Exists(Filename))
                {
                    System.IO.File.Delete(Filename);
                }

                excel.SaveAs(Filename, Excel.XlFileFormat.xlWorkbookDefault, Missing.Value, Missing.Value, 1, 1, 1, 1, 1, 1);
                //excel.SaveAs(Filename, Excel.XlFileFormat.xlWorkbookDefault, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            }
            catch (Exception ex)
            {
                MessageBox.Show("There is Problem to Read and Edit Primary Excel Sheet");
                return;
            }
        }

No comments:

Post a Comment