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

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

程序员文章站 2024-02-11 17:42:58
今天工作中遇到一个需求,就是获取 excel 里面的内容,并且把 excel 另存为 csv,因为本人以前未接触过,所以下面整理出来的代码均来自网络,具体参考链接已丢失,原...

今天工作中遇到一个需求,就是获取 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,添加引用。