Excel

 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Excel
{
    public partial class Form1 : Form
    {
        DataSet newDataset = new DataSet();
        public Form1()
        {
            InitializeComponent();
            string file = "D:\\***.xlsx";
            //TestExcelWrite(file);
            DataTable dt = TestExcelRead(file);
            DataSet ds = new DataSet();
            ds.Tables.Add(dt);
            newDataset = ds;
            this.dataGridView1.DataSource = ds.Tables[0];

            DataSet d = newDataset.GetChanges();

        }

        public static int updateTable(DataTable dt, string sql)
        {
            using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(SqlConnStr))
            {
                using (SqlDataAdapter da = new SqlDataAdapter(sql, conn))
                {

                    conn.Open();
                    SqlCommandBuilder sb = new SqlCommandBuilder(da);
                    try
                    {
                        int i = da.Update(dt);
                        return i;
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                }
            }
        }


        static DataTable GenerateData()
        {
            DataTable data = new DataTable();
            for (int i = 0; i < 5; ++i)
            {
                data.Columns.Add("Columns_" + i.ToString(), typeof(string));
            }

            for (int i = 0; i < 10; ++i)
            {
                DataRow row = data.NewRow();
                row["Columns_0"] = "item0_" + i.ToString();
                row["Columns_1"] = "item1_" + i.ToString();
                row["Columns_2"] = "item2_" + i.ToString();
                row["Columns_3"] = "item3_" + i.ToString();
                row["Columns_4"] = "item4_" + i.ToString();
                data.Rows.Add(row);
            }
            return data;
        }

        static void PrintData(DataTable data)
        {
            if (data == null) return;
            for (int i = 0; i < data.Rows.Count; ++i)
            {
                for (int j = 0; j < data.Columns.Count; ++j)
                    Console.Write("{0} ", data.Rows[i][j]);
                Console.Write("\n");
            }
        }

        static void TestExcelWrite(string file)
        {
            try
            {
                using (ExcelHelper excelHelper = new ExcelHelper(file))
                {
                    DataTable data = GenerateData();
                    int count = excelHelper.DataTableToExcel(data, "MySheet", true);
                    if (count > 0)
                        Console.WriteLine("Number of imported data is {0} ", count);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);
            }
        }

        static DataTable TestExcelRead(string file)
        {
            try
            {
                using (ExcelHelper excelHelper = new ExcelHelper(file))
                {
                    DataTable dt = excelHelper.ExcelToDataTable("Sheet1", true);
                    return dt;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }
    }
}