C#操作EXCEL DataTable转换的实例代码

//加载excel 
        public   dataset loaddatafromexcel(string filepath)
        {
            try
            {
                string strconn;
                //strconn = "provider=microsoft.jet.oledb.4.0;data source=" + filepath + ";extended properties='excel 8.0;hdr=false;imex=1'";
                strconn = string.format("provider=microsoft.ace.oledb.12.0;data source={0};extended properties='excel 8.0;hdr=yes;imex=1;'", filepath);
                oledbconnection oleconn = new oledbconnection(strconn);
                oleconn.open();
                string sql = "select * from  [sheet1$]";//可是更改sheet名称，比如sheet2，等等 

                oledbdataadapter oledaexcel = new oledbdataadapter(sql, oleconn);
                dataset oledsexcle = new dataset();
                oledaexcel.fill(oledsexcle, "sheet1");
                oleconn.close();
                return oledsexcle;
            }
            catch (exception err)
            {

                return null;
            }
        }

        /// <summary>
        /// datatable直接导出excel,此方法会把datatable的数据用excel打开,再自己手动去保存到确切的位置
        /// </summary>
        /// <param name="dt">要导出excel的datatable</param>
        /// <returns></returns>
        public bool doexport(system.data.datatable dt)
        {
            microsoft.office.interop.excel.application app = new applicationclass();
            if (app == null)
            {
                throw new exception("excel无法启动");
            }
            app.visible = true;
            workbooks wbs = app.workbooks;
            workbook wb = wbs.add(missing.value);
            worksheet ws = (worksheet)wb.worksheets[1];

            int cnt = dt.rows.count;
            int columncnt = dt.columns.count;

            // *****************获取数据********************
            object[,] objdata = new object[cnt + 1, columncnt];  // 创建缓存数据
            // 获取列标题
            for (int i = 0; i < columncnt; i++)
            {
                objdata[0, i] = dt.columns[i].columnname;
            }
            // 获取具体数据
            for (int i = 0; i < cnt; i++)
            {
                system.data.datarow dr = dt.rows[i];
                for (int j = 0; j < columncnt; j++)
                {
                    objdata[i + 1, j] = dr[j];
                }
            }

            //********************* 写入excel******************
            range r = ws.get_range(app.cells[1, 1], app.cells[cnt + 1, columncnt]);
            r.numberformat = "@";
            //r = r.get_resize(cnt+1, columncnt);
            r.value2 = objdata;
            r.entirecolumn.autofit();

            app = null;
            return true;
        }