欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-12-16 20:17:34
复制代码 代码如下://加载excel          public  ...
复制代码 代码如下:

//加载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;
        }

上一篇:

下一篇: