Breaking News

Editors Picks

Tuesday, September 27, 2011

Read excel file and add new colunm in excel using asp.net ,C#


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Windows;
using System.Xml;
using System.Collections;
using System.IO;
using System.Globalization;
using System.Data;
using System.Data.OleDb;
using System.Reflection;
using System.Resources;
using System.Globalization;
using Microsoft.Office.Interop;
using Excel = Microsoft.Office.Interop.Excel;



private void button1_Click(object sender, EventArgs e)
        {
            saveexl();
        }

public void saveexl()
        {

            string strExcelFilePath = @"D:\\fdlog.xlsx";
            DataTable dt = new DataTable();
            string conStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strExcelFilePath + ";Extended Properties=Excel 8.0";
            OleDbConnection connExcel = new OleDbConnection(conStr);
            OleDbCommand cmdExcel = new OleDbCommand();
            OleDbDataAdapter oda = new OleDbDataAdapter();
            cmdExcel.Connection = connExcel;
            //Get the name of First Sheet
            connExcel.Open();
            DataTable dtExcelSchema;
            dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
            connExcel.Close();
            //Read Data from First Sheet
            connExcel.Open();
            cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
            oda.SelectCommand = cmdExcel;
            oda.Fill(dt);
            connExcel.Close();

            string temp = "";


            string filePath = @"D:\\sss.csv";

            string delimiter = "~";
            StringBuilder sb = new StringBuilder();
            DateTime dat = DateTime.Now;
            Microsoft.Office.Interop.Excel.Worksheet sheet = new Microsoft.Office.Interop.Excel.Worksheet();
            object misValue = System.Reflection.Missing.Value;
            string workbookPath = "D:/fdlog.xlsx";
            //Excel.Workbook excelWorkbook = excelApp.Workbooks.Add(misValue);
            Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);

            Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets;

            string currentSheet = "fdlog";
                    Microsoft.Office.Interop.Excel.Worksheet excelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item(currentSheet);
            for (int intRowIndex = 1; intRowIndex < dt.Rows.Count; intRowIndex++)
            {
                try
                {

                    string text = dt.Rows[intRowIndex][1].ToString();
                    string code = dt.Rows[intRowIndex][0].ToString();
                                        
                    excelWorksheet.Cells[intRowIndex, 4] = text;
               
                }
                catch (Exception ex)
                {
                  
                }

            }
            string filename = "fdlog-sunil";
            excelWorkbook.SaveAs(filename + ".xlsx", misValue, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlNoChange, misValue, misValue, misValue, misValue, misValue);
            excelWorkbook.Close(true, misValue, misValue);
            excelApp.Quit();

            releaseObject(excelWorksheet);
            releaseObject(excelWorkbook);
            releaseObject(excelApp);
            System.IO.File.WriteAllText(filePath, sb.ToString());
          
        }
  private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }

No comments :

Post a Comment

Contact Us

Name

Email *

Message *