c# 导入、导出excel

一、 导入excel文件的坑

今天在写C#导入excel文件的时候，遇到以下错误信息，真的是很坑。百度了一大堆的东西，完全没有什么用。

详细错误大概是这样的：
System.InvalidOperationException”类型的未经处理的异常在 System.Data.dll 中发生 未在本地计算机上注册“Microsoft.ACE.OLEDB.12.0

没事的哇！遇到困难也要微笑面对（我能怎么办，我也很绝望呀≡(▔﹏▔)≡）

经过一系列的试错,终于黄天不负 有心人（我太难了〒▽〒）。现在一般都是用的是excel 2007版本以上，而且系统的windows10一般是64bit的。

• 打开你的解决方案
• 右键点击，找到属性
  
• 找到生成
• 将目标平台改成64bit的即可

二、完整代码

引入命名空间

using System.IO;
using System.Data.SqlClient;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Xml;
using System.Data.OleDb;

注意： Excel = Microsoft.Office.Interop.Excel; 需导入扩展包，见下图：

哈哈哈！！！不知道在哪里安装咋办，不要着急，见下图：

1. 点击项目（p）,然后点击管理Nuget程序包
   
2. 然后点击浏览，在搜索框里搜索即可：
   

下面附上完整代码：

注意窗体设计，看我运行图就好了 。主要是两个button、两个 datagridview。窗体内预加载部分数据。
后面增加了可以导出为csv文件格式

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

/*引入命名空间*/
using System.IO;
using System.Data.SqlClient;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Xml;
using System.Data.OleDb;

namespace excelshow
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        #region /* 数据导出到excel */
        public void ExportExcel()
        {
            try
            {
                this.Cursor = Cursors.WaitCursor;

                if (!Directory.Exists(@"D:Desktop\BMDT"))
                    Directory.CreateDirectory(@"D:Desktop\BMDT");


                string fileName = "";
                string saveFileName = "";
                SaveFileDialog saveDialog = new SaveFileDialog();
                saveDialog.DefaultExt = "xlsx";
                saveDialog.InitialDirectory = @"D:Desktop\BMDT";
                saveDialog.Filter = "Excel文件|*.xlsx";
                // saveDialog.FileName = fileName;
                saveDialog.FileName = "BMDT_Data_" + DateTime.Now.ToLongDateString().ToString();
                saveDialog.ShowDialog();
                saveFileName = saveDialog.FileName;



                if (saveFileName.IndexOf(":") < 0)
                {
                    this.Cursor = Cursors.Default;
                    return; //被点了取消
                }
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                if (xlApp == null)
                {
                    MessageBox.Show("无法创建Excel对象，您的电脑可能未安装Excel");
                    return;
                }
                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 
                Microsoft.Office.Interop.Excel.Range range = worksheet.Range[worksheet.Cells[4, 1], worksheet.Cells[8, 1]];

                //写入标题             
                for (int i = 0; i < dataGridView1.ColumnCount; i++)
                { worksheet.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText; }
                //写入数值
                for (int r = 0; r < dataGridView1.Rows.Count; r++)
                {
                    for (int i = 0; i < dataGridView1.ColumnCount; i++)
                    {
                        worksheet.Cells[r + 2, i + 1] = dataGridView1.Rows[r].Cells[i].Value;

                        if (this.dataGridView1.Rows[r].Cells[i].Style.BackColor == Color.Red)
                        {

                            range = worksheet.Range[worksheet.Cells[r + 2, i + 1], worksheet.Cells[r + 2, i + 1]];
                            range.Interior.ColorIndex = 10;

                        }



                    }
                    System.Windows.Forms.Application.DoEvents();
                }
                worksheet.Columns.EntireColumn.AutoFit();//列宽自适应

                MessageBox.Show(fileName + "资料保存成功", "提示", MessageBoxButtons.OK);
                if (saveFileName != "")
                {
                    try
                    {
                        workbook.Saved = true;
                        workbook.SaveCopyAs(saveFileName);  //fileSaved = true;  

                    }
                    catch (Exception ex)
                    {//fileSaved = false;                      
                        MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
                    }
                }
                xlApp.Quit();
                GC.Collect();//强行销毁           

                this.Cursor = Cursors.Default;
            }
            catch (Exception e)
            {
                this.Cursor = Cursors.Default;
                MessageBox.Show(e.ToString());

            }




        }


        private void button1_Click(object sender, EventArgs e)
        {
            ExportExcel();

        }

        public DataSet getData()

        {

            //打开文件

            OpenFileDialog file = new OpenFileDialog();

            file.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";

            file.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);

            file.Multiselect = false;

            if (file.ShowDialog() == DialogResult.Cancel)

                return null;

            //判断文件后缀

            var path = file.FileName;

            string fileSuffix = System.IO.Path.GetExtension(path);

            if (string.IsNullOrEmpty(fileSuffix))

                return null;

            using (DataSet ds = new DataSet())

            {

                //判断Excel文件是2003版本还是2007版本

                string connString = "";

                if (fileSuffix == ".xls")

                    connString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";

                else

                    connString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + path + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";

                //读取文件

                string sql_select = " SELECT * FROM [Sheet1$]";

                using (OleDbConnection conn = new OleDbConnection(connString))

                using (OleDbDataAdapter cmd = new OleDbDataAdapter(sql_select, conn))

                {
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                  

                    cmd.Fill(ds);

                }

                if (ds == null || ds.Tables.Count <= 0) return null;

                return ds;

            }

        }
        private void Form1_Load(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("姓名");
            dt.Columns.Add("年龄");
            dt.Rows.Add("王昭君", "18");
            dt.Rows.Add("李白", "22");
            dt.Rows.Add("张飞", "25");
            dataGridView1.DataSource = dt;
        }

        private void button2_Click(object sender, EventArgs e)
        {
            dataGridView2.DataSource = null; //每次打开清空内容

            DataTable dt = getData().Tables[0];

            dataGridView2.DataSource = dt;
        }

        #region /* 数据导出到CSV */
        public void ExportCSV()
        {
            if (dataGridView1.Rows.Count == 0)
            {
                MessageBox.Show("没有数据可导出!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "CSV files (*.csv)|*.csv";
            saveFileDialog.FilterIndex = 0;
            saveFileDialog.RestoreDirectory = true;
            saveFileDialog.CreatePrompt = true;
            saveFileDialog.FileName = null;
            saveFileDialog.Title = "保存";
            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                Stream stream = saveFileDialog.OpenFile();
                StreamWriter sw = new StreamWriter(stream, System.Text.Encoding.GetEncoding(-0));
                string strLine = "";
                try
                {
                    for (int i = 0; i < dataGridView1.ColumnCount; i++)
                    {
                        if (i > 0)
                            strLine += ",";

                        strLine += dataGridView1.Columns[i].HeaderText;

                    }

                    strLine.Remove(strLine.Length - 1);

                    sw.WriteLine(strLine);

                    strLine = "";

                    //表的内容

                    for (int j = 0; j < dataGridView1.Rows.Count; j++)
                    {

                        strLine = "";

                        int colCount = dataGridView1.Columns.Count;

                        for (int k = 0; k < colCount; k++)
                        {

                            if (k > 0 && k < colCount)

                                strLine += ",";

                            if (dataGridView1.Rows[j].Cells[k].Value == null)

                                strLine += "";

                            else
                            {

                                string cell = dataGridView1.Rows[j].Cells[k].Value.ToString().Trim();

                                //防止里面含有特殊符号

                                cell = cell.Replace("\"", "\"\"");

                                cell = "\"" + cell + "\"";

                                strLine += cell;

                            }

                        }

                        sw.WriteLine(strLine);

                    }

                    sw.Close();

                    stream.Close();

                    MessageBox.Show("数据被导出到：" + saveFileDialog.FileName.ToString(), "导出完毕", MessageBoxButtons.OK, MessageBoxIcon.Information);

                }

                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "导出错误", MessageBoxButtons.OK, MessageBoxIcon.Information);

                }
            }
        }
        #endregion

    }
}
#endregion

运行结果如下：