Tuesday 17 July 2012

How to Edit and Save Excel Sheet using .NET


        public void EditExcel()
        {
            try
            {
                string file;
                int row = 1;
                
                openFileDialog1.ShowDialog();
                textBox1.Text = openFileDialog1.FileName;

               
                if (textBox1.Text == "")
                  {
                  Interaction.MsgBox("Please Select File  Path",MsgBoxStyle.Information, "Attendace");
                  textBox1.Focus();
                  return;
                  }
                file = 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;
            }
        }