当前位置： 移动技术网 > IT编程>开发语言>c# > C# 解析 Excel 并且生成 Csv 文件代码分析

C# 解析 Excel 并且生成 Csv 文件代码分析

2019年07月18日  | 移动技术网IT编程  | 我要评论

今天工作中遇到一个需求，就是获取 excel 里面的内容，并且把 excel 另存为 csv，因为本人以前未接触过，所以下面整理出来的代码均来自网络，具体参考链接已丢失，原作者保留所有权利！

例子：

复制代码 代码如下:

using system;
using system.data;

namespace exportexceltocode
{
    class exceloperater
    {
        public void operater()
        {
            // excel 路径
            string excelpath = "";
            // csv 存放路径
            string csvpath = "";

            // 获取 excel sheet 名称列表
            string[] sheetnamelist = excelutils.getsheetnamelist(excelpath);

            if (sheetnamelist != null && sheetnamelist.length > 0)
            {
                foreach (string sheetname in sheetnamelist)
                {
                    string itemname = sheetname.trimend(new char[] { '$' });

                    // 解析 excel 为 datatable 对象
                    datatable datatable = excelutils.exceltodatatable(excelpath, itemname);
                    if (datatable != null && datatable.rows.count > 0)
                    {
                        // 生成 csv 文件
                        excelutils.exceltocsv(excelpath, csvpath, itemname, "|#|", 0);
                    }
                }
            }
        }
    }
}

excelutils.cs 文件

复制代码 代码如下:

using system;  
using system.data;
using microsoft.office.interop.excel;
using excel = microsoft.office.interop.excel;

namespace exportexceltocode
{
    public partial class excelutils
    {
        /// <summary>
        /// 获取 sheet 名称
        /// </summary>
        /// <param name="filepath"></param>
        /// <returns></returns>
        public static string[] getsheetnamelist(string filepath)
        {
            try
            {
                string connectiontext = "provider=microsoft.ace.oledb.12.0;" + "data source=" + filepath + ";" + "extended properties='excel 12.0;hdr=yes;imex=1';";

                system.data.oledb.oledbconnection oledbconnection = new system.data.oledb.oledbconnection(connectiontext);

                oledbconnection.open();

                system.data.datatable datatable = oledbconnection.getoledbschematable(system.data.oledb.oledbschemaguid.tables, new object[] { null, null, null, "table" }); ;

                string[] sheetnamelist = new string[datatable.rows.count];

                for (int index = 0; index < datatable.rows.count; index++)
                {
                    sheetnamelist[index] = datatable.rows[index][2].tostring();
                }

                oledbconnection.close();

                return sheetnamelist;
            }
            catch (exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// excel 转 datatable
        /// </summary>
        /// <param name="filepath"></param>
        /// <param name="sheetname"></param>
        /// <returns></returns>
        public static system.data.datatable exceltodatatable(string filepath, string sheetname)
        {
            try
            {
                string connectiontext = "provider=microsoft.ace.oledb.12.0;" + "data source=" + filepath + ";" + "extended properties='excel 12.0;hdr=yes;imex=1';";
                string selecttext = string.format("select * from [{0}$]", sheetname);

                dataset dataset = new dataset();

                system.data.oledb.oledbconnection oledbconnection = new system.data.oledb.oledbconnection(connectiontext);

                oledbconnection.open();

                system.data.oledb.oledbdataadapter oledbdataadapter = new system.data.oledb.oledbdataadapter(selecttext, connectiontext);
                oledbdataadapter.fill(dataset, sheetname);

                oledbconnection.close();

                return dataset.tables[sheetname];
            }
            catch (exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// excel 转 csv
        /// </summary>
        /// <param name="sourceexcelpathandname"></param>
        /// <param name="targetcsvpathandname"></param>
        /// <param name="excelsheetname"></param>
        /// <param name="columndelimeter"></param>
        /// <param name="headerrowstoskip"></param>
        /// <returns></returns>
        public static bool exceltocsv(string sourceexcelpathandname, string targetcsvpathandname, string excelsheetname, string columndelimeter, int headerrowstoskip)
        {
            excel.application oxl = null;
            excel.workbooks workbooks = null;
            workbook mworkbook = null;
            sheets mworksheets = null;
            worksheet mwsheet = null;

            try
            {
                oxl = new excel.application();
                oxl.visible = false;
                oxl.displayalerts = false;
                workbooks = oxl.workbooks;
                mworkbook = workbooks.open(sourceexcelpathandname, 0, false, 5, "", "", false, xlplatform.xlwindows, "", true, false, 0, true, false, false);
                mworksheets = mworkbook.worksheets;
                mwsheet = (worksheet)mworksheets.get_item(excelsheetname);
                excel.range range = mwsheet.usedrange;
                excel.range rngcurrentrow;
                for (int i = 0; i < headerrowstoskip; i++)
                {
                    rngcurrentrow = range.get_range("a1", type.missing).entirerow;
                    rngcurrentrow.delete(xldeleteshiftdirection.xlshiftup);
                }
                range.replace("\n", " ", type.missing, type.missing, type.missing, type.missing, type.missing, type.missing);
                range.replace(",", columndelimeter, type.missing, type.missing, type.missing, type.missing, type.missing, type.missing);

                mworkbook.saveas(targetcsvpathandname, excel.xlfileformat.xlcsv,
                type.missing, type.missing, type.missing, type.missing, microsoft.office.interop.excel.xlsaveasaccessmode.xlexclusive,
                type.missing, type.missing, type.missing,
                type.missing, false);
                return true;
            }
            catch (exception ex)
            {
                return false;
            }
            finally
            {
                if (mwsheet != null) mwsheet = null;
                if (mworkbook != null) mworkbook.close(type.missing, type.missing, type.missing);
                if (mworkbook != null) mworkbook = null;
                if (oxl != null) oxl.quit();
                system.runtime.interopservices.marshal.releasecomobject(oxl);
                if (oxl != null) oxl = null;
                gc.waitforpendingfinalizers();
                gc.collect();
                gc.waitforpendingfinalizers();
                gc.collect();
            }
        }
    }
}


需要特别指出的是：需要在项目中添加 microsoft.office.interop.excel.dll 文件，具体操作：选中引用->右键添加引用->浏览找到 microsoft.office.interop.excel，添加引用。

您可能感兴趣的文章:

如对本文有疑问， 点击进行留言回复！！

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网