C#导入导出EXCEL文件的代码实例

using system;
using system.data;
using system.data.oledb;

namespace zfsoft.joint
{
    public class excelio
    {
        private int _returnstatus;
        private string _returnmessage;

        /// <summary>
        /// 执行返回状态
        /// </summary>
        public int returnstatus
        {
            get
            {
                return _returnstatus;
            }
        }

        /// <summary>
        /// 执行返回信息
        /// </summary>
        public string returnmessage
        {
            get
            {
                return _returnmessage;
            }
        }

        public excelio()
        {
        }

         
        /// <summary>
        /// 导入excel到dataset
        /// </summary>
        /// <param name="filename">excel全路径文件名</param>
        /// <returns>导入成功的dataset</returns>
        public datatable importexcel(string filename)
        {
            //判断是否安装excel
            microsoft.office.interop.excel.application xlapp = new microsoft.office.interop.excel.application();
            if (xlapp == null)
            {
                _returnstatus = -1;
                _returnmessage = "无法创建excel对象，可能您的计算机未安装excel";
                return null;
            }

            //判断文件是否被其他进程使用           
            microsoft.office.interop.excel.workbook workbook;
            try
            {
                workbook = xlapp.workbooks.open(filename, 0, false, 5, "", "", false, microsoft.office.interop.excel.xlplatform.xlwindows, "", true, false, 0, true, 1, 0);
            }
            catch
            {
                _returnstatus = -1;
                _returnmessage = "excel文件处于打开状态，请保存关闭";
                return null;
            }

            //获得所有sheet名称
            int n = workbook.worksheets.count;
            string[] sheetset = new string[n];
            system.collections.arraylist al = new system.collections.arraylist();
            for (int i = 1; i <= n; i++)
            {
                sheetset[i - 1] = ((microsoft.office.interop.excel.worksheet)workbook.worksheets[i]).name;
            }

            //释放excel相关对象
            workbook.close(null, null, null);
            xlapp.quit();
            if (workbook != null)
            {
                system.runtime.interopservices.marshal.releasecomobject(workbook);
                workbook = null;
            }
            if (xlapp != null)
            {
                system.runtime.interopservices.marshal.releasecomobject(xlapp);
                xlapp = null;
            }
            gc.collect();

            //把excel导入到dataset
            dataset ds = new dataset();
            datatable table = new datatable();
            string connstr = " provider = microsoft.jet.oledb.4.0 ; data source = " + filename + ";extended properties=excel 8.0";
            using (oledbconnection conn = new oledbconnection(connstr))
            {
                conn.open();
                oledbdataadapter da;
                string sql = "select * from [" + sheetset[0] + "$] ";
                da = new oledbdataadapter(sql, conn);
                da.fill(ds, sheetset[0]);
                da.dispose();
                table = ds.tables[0];
                conn.close();
                conn.dispose();
            }
            return table;
        }

        /// <summary>
        /// 把datatable导出到excel
        /// </summary>
        /// <param name="reportname">报表名称</param>
        /// <param name="dt">数据源表</param>
        /// <param name="savefilename">excel全路径文件名</param>
        /// <returns>导出是否成功</returns>
        public bool exportexcel(string reportname, system.data.datatable dt, string savefilename)
        {
            if (dt == null)
            {
                _returnstatus = -1;
                _returnmessage = "数据集为空！";
                return false;
            }

            bool filesaved = false;
            microsoft.office.interop.excel.application xlapp = new microsoft.office.interop.excel.application();
            if (xlapp == null)
            {
                _returnstatus = -1;
                _returnmessage = "无法创建excel对象，可能您的计算机未安装excel";
                return false;
            }

            microsoft.office.interop.excel.workbooks workbooks = xlapp.workbooks;
            microsoft.office.interop.excel.workbook workbook = workbooks.add(microsoft.office.interop.excel.xlwbatemplate.xlwbatworksheet);
            microsoft.office.interop.excel.worksheet worksheet = (microsoft.office.interop.excel.worksheet)workbook.worksheets[1];//取得sheet1
            worksheet.cells.font.size = 10;
            microsoft.office.interop.excel.range range;

            long totalcount = dt.rows.count;
            long rowread = 0;
            float percent = 0;

            worksheet.cells[1, 1] = reportname;
            ((microsoft.office.interop.excel.range)worksheet.cells[1, 1]).font.size = 12;
            ((microsoft.office.interop.excel.range)worksheet.cells[1, 1]).font.bold = true;

            //写入字段
            for (int i = 0; i < dt.columns.count; i++)
            {
                worksheet.cells[2, i + 1] = dt.columns[i].columnname;
                range = (microsoft.office.interop.excel.range)worksheet.cells[2, i + 1];
                range.interior.colorindex = 15;
                range.font.bold = true;

            }
            //写入数值
            for (int r = 0; r < dt.rows.count; r++)
            {
                for (int i = 0; i < dt.columns.count; i++)
                {
                    worksheet.cells[r + 3, i + 1] = dt.rows[r][i].tostring();
                }
                rowread++;
                percent = ((float)(100 * rowread)) / totalcount;
            }

            range = worksheet.get_range(worksheet.cells[2, 1], worksheet.cells[dt.rows.count + 2, dt.columns.count]);
            range.borderaround(microsoft.office.interop.excel.xllinestyle.xlcontinuous, microsoft.office.interop.excel.xlborderweight.xlthin, microsoft.office.interop.excel.xlcolorindex.xlcolorindexautomatic, null);
            if (dt.rows.count > 0)
            {
                range.borders[microsoft.office.interop.excel.xlbordersindex.xlinsidehorizontal].colorindex = microsoft.office.interop.excel.xlcolorindex.xlcolorindexautomatic;
                range.borders[microsoft.office.interop.excel.xlbordersindex.xlinsidehorizontal].linestyle = microsoft.office.interop.excel.xllinestyle.xlcontinuous;
                range.borders[microsoft.office.interop.excel.xlbordersindex.xlinsidehorizontal].weight = microsoft.office.interop.excel.xlborderweight.xlthin;
            }
            if (dt.columns.count > 1)
            {
                range.borders[microsoft.office.interop.excel.xlbordersindex.xlinsidevertical].colorindex = microsoft.office.interop.excel.xlcolorindex.xlcolorindexautomatic;
                range.borders[microsoft.office.interop.excel.xlbordersindex.xlinsidevertical].linestyle = microsoft.office.interop.excel.xllinestyle.xlcontinuous;
                range.borders[microsoft.office.interop.excel.xlbordersindex.xlinsidevertical].weight = microsoft.office.interop.excel.xlborderweight.xlthin;
            }

            //保存文件
            if (savefilename != "")
            {
                try
                {
                    workbook.saved = true;
                    workbook.savecopyas(savefilename);
                    filesaved = true;
                }
                catch (exception ex)
                {
                    filesaved = false;
                    _returnstatus = -1;
                    _returnmessage = "导出文件时出错,文件可能正被打开！\n" + ex.message;
                }
            }
            else
            {
                filesaved = false;
            }

            //释放excel对应的对象
            if (range != null)
            {
                system.runtime.interopservices.marshal.releasecomobject(range);
                range = null;
            }
            if (worksheet != null)
            {
                system.runtime.interopservices.marshal.releasecomobject(worksheet);
                worksheet = null;
            }
            if (workbook != null)
            {
                system.runtime.interopservices.marshal.releasecomobject(workbook);
                workbook = null;
            }
            if (workbooks != null)
            {
                system.runtime.interopservices.marshal.releasecomobject(workbooks);
                workbooks = null;
            }
            xlapp.application.workbooks.close();
            xlapp.quit();
            if (xlapp != null)
            {
                system.runtime.interopservices.marshal.releasecomobject(xlapp);
                xlapp = null;
            }
            gc.collect();
            return filesaved;
        }
    }
}