C# Excel导入导出

演示 C#如何实现对 Excel 文件的读取、写入等基本操作。

//======================================================================
//        《31天学会CRM项目开发》机械工业出版社 版权所有 （C） 2015-2016
//        文件名：Form1.cs
//        随书附赠源代码，若转发需保留版权信息。
//        社区支持：http://www.huiyaosoft.com/
//======================================================================
//======================================================================
//
//        Copyright （C） 2015 huiyaosoft.com    
//        All rights reserved
//
//        filename :ExcelImportAndExport.Form1.cs
//        description :Excel导入导出
//
//        created by huiyaosoft.com at  2015-06-216
//        http://www.huiyaosoft.com/
//
//======================================================================
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.OleDb;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExcelImportAndExport
{
    public partial class Form1 : Form
    {
        public Form1（）
        {
            InitializeComponent（）;
        }

        private void Form1_Load（object sender EventArgs e）
        {

        }

        private void btnImport_Click（object sender EventArgs e）
        {
            // 普通方式导入
            int tc = ImportDataFromExcel（）;
            MessageBox.Show（string.Format（“共导入{0}条记录“ tc））;
        }

        private void btnOLEDB_Click（object sender EventArgs e）
        {
            // OLEDB方式导入
            DataTable dt = ImportDataFromExcelByOLEDB（）;
            MessageBox.Show（string.Format（“共导入{0}条记录“ dt.Rows.Count））;
        }

        private void btnExport_Click（object sender EventArgs e）
        {
            DataTable dt = ImportDataFromExcelByOLEDB（）;
            MessageBox.Show（string.Format（“共导入{0}条记录“dt.Rows.Count））;
            ExportDataToExcel（dt “data1.xlsx“）;
        }

        private DataTable ImportDataFromExcelByOLEDB（）
        {
            try
            {
                var ofd = new OpenFileDialog（）
                {
                    Filter = “Microsoft Office Excel 工作簿（*.xlsx;*.xls）|*.xlsx;*.xls“
                    Multiselect = false
                };
                if （ofd.ShowDialog（） == DialogResult.Cancel）
                    return null;
                DataTable dt = new DataTable（“excel“）;
                //var strConn = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=“ + ofd.FileName + “;Extended Properties=‘Excel 8.0;HDR=Yes;IMEX=1;‘“;
                var strConn = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=“ + ofd.FileName + “;Extended Properties=‘Excel 12.0;HDR=Yes;IMEX=1;‘“;
                using （var oledbConn = new OleDbConnection（strConn））
                {
                    oledbConn.Open（）;
                    var sheetName = oledbConn.GetOleDbSchemaTable（OleDbSchemaGuid.Tables new[] { null null null

 属性            大小     日期    时间   名称
----------- ---------  ---------- -----  ----
     文件         902  2015-04-28 08:15  ExcelImportAndExport\ExcelImportAndExport.sln
     文件       21504  2015-04-28 09:06  ExcelImportAndExport\ExcelImportAndExport.suo
     文件       42496  2015-07-15 16:32  ExcelImportAndExport\ExcelImportAndExport.v12.suo
     目录           0  2016-03-25 21:36  ExcelImportAndExport\ExcelImportAndExport\
     目录           0  2016-03-25 21:36  ExcelImportAndExport\ExcelImportAndExport\bin\
     目录           0  2016-03-25 21:36  ExcelImportAndExport\ExcelImportAndExport\bin\Debug\
     文件        9974  2015-04-28 08:40  ExcelImportAndExport\ExcelImportAndExport\bin\Debug\data.xlsx
     文件        8883  2015-07-15 09:08  ExcelImportAndExport\ExcelImportAndExport\bin\Debug\data1.xlsx
     文件        9997  2015-07-15 09:04  ExcelImportAndExport\ExcelImportAndExport\bin\Debug\data2.xlsx
     文件       22528  2015-07-15 09:07  ExcelImportAndExport\ExcelImportAndExport\bin\Debug\ExcelImportAndExport.exe
     文件       30208  2015-07-15 09:07  ExcelImportAndExport\ExcelImportAndExport\bin\Debug\ExcelImportAndExport.pdb
     文件       23168  2015-07-15 09:08  ExcelImportAndExport\ExcelImportAndExport\bin\Debug\ExcelImportAndExport.vshost.exe
     文件         490  2010-03-17 22:39  ExcelImportAndExport\ExcelImportAndExport\bin\Debug\ExcelImportAndExport.vshost.exe.manifest
     文件        3922  2015-07-03 11:34  ExcelImportAndExport\ExcelImportAndExport\ExcelImportAndExport.csproj
     文件       10667  2016-03-10 09:53  ExcelImportAndExport\ExcelImportAndExport\Form1.cs
     文件        3925  2016-03-10 09:53  ExcelImportAndExport\ExcelImportAndExport\Form1.Designer.cs
     文件        5817  2015-04-28 08:59  ExcelImportAndExport\ExcelImportAndExport\Form1.resx
     目录           0  2016-03-25 21:36  ExcelImportAndExport\ExcelImportAndExport\obj\
     目录           0  2016-03-25 21:36  ExcelImportAndExport\ExcelImportAndExport\obj\x86\
     目录           0  2016-03-25 21:36  ExcelImportAndExport\ExcelImportAndExport\obj\x86\Debug\
     文件        3459  2015-07-15 09:07  ExcelImportAndExport\ExcelImportAndExport\obj\x86\Debug\DesignTimeResolveAssemblyReferences.cache
     文件        7229  2015-07-03 11:34  ExcelImportAndExport\ExcelImportAndExport\obj\x86\Debug\DesignTimeResolveAssemblyReferencesInput.cache
     文件        2221  2015-07-15 09:08  ExcelImportAndExport\ExcelImportAndExport\obj\x86\Debug\ExcelImportAndExport.csproj.FileListAbsolute.txt
     文件         977  2015-07-03 11:34  ExcelImportAndExport\ExcelImportAndExport\obj\x86\Debug\ExcelImportAndExport.csproj.GenerateResource.Cache
     文件       10839  2015-07-03 11:34  ExcelImportAndExport\ExcelImportAndExport\obj\x86\Debug\ExcelImportAndExport.csprojResolveAssemblyReference.cache
     文件       22528  2015-07-15 09:07  ExcelImportAndExport\ExcelImportAndExport\obj\x86\Debug\ExcelImportAndExport.exe
     文件         180  2015-07-03 11:34  ExcelImportAndExport\ExcelImportAndExport\obj\x86\Debug\ExcelImportAndExport.Form1.resources
     文件       30208  2015-07-15 09:07  ExcelImportAndExport\ExcelImportAndExport\obj\x86\Debug\ExcelImportAndExport.pdb
     文件         180  2015-07-03 11:34  ExcelImportAndExport\ExcelImportAndExport\obj\x86\Debug\ExcelImportAndExport.Properties.Resources.resources
     文件         488  2015-04-28 09:06  ExcelImportAndExport\ExcelImportAndExport\obj\x86\Debug\GenerateResource.read.1.tlog
     文件        1126  2015-04-28 09:06  ExcelImportAndExport\ExcelImportAndExport\obj\x86\Debug\GenerateResource.write.1.tlog
............此处省略11个文件信息