C# excel檔案導入導出

在C#交流群裡，看到很多小夥伴在excel資料導入導出到C#界面上存在疑惑，是以今天專門做了這個主題，希望大家有所收獲！

環境：win10+vs2017

界面：主要以示範為主，是以沒有做優化，然後主界面上添加兩個按鈕，分别命名為ExportExcel和ImportExcel，添加兩個dataGridView，分别是dataGridView1和dataGridView2

然後在窗體加載程式中給dataGridView1寫入三行資料，代碼如下：

DataTable dt = new DataTable();

dt.Columns.Add("Name");

dt.Columns.Add("Age");

dt.Rows.Add("小王","15");

dt.Rows.Add("老李","42");

dt.Rows.Add("老張","25");

dataGridView1.DataSource = dt;

軟體運作後，點選ExportExcel，則将datagridview1的資料儲存到excel中，點選ImportExcel，選擇excel後讀取資料到datagridview2.

注意：如果報System.InvalidOperationException:“未在本地計算機上注冊“Microsoft.ACE.OLEDB.12.0”提供程式。”請檢查office是否正确安裝

具體步驟：

step1：引用dll，在nuget上安裝Microsoft.Office.Interop.Excel

step2:code

主要由三個方法：

public void ExportExcel() 實作資料導出到excel
public DataSet ImportExcel(int t = 1）實作讀取excel資料

public void ExportCSV() 資料導出到csv

其次

儲存選項對話框

  string fileName = "";

                string saveFileName = "";

                SaveFileDialog saveDialog = new SaveFileDialog();

                saveDialog.DefaultExt = "xlsx";

                saveDialog.InitialDirectory = @"C:\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; //被點了取消

完整代碼如下：

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 excelReadWriter
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }


        #region /* 資料導出到excel */
        public void ExportExcel()
        {
            try
            {
                this.Cursor = Cursors.WaitCursor;


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




                string fileName = "";
                string saveFileName = "";
                SaveFileDialog saveDialog = new SaveFileDialog();
                saveDialog.DefaultExt = "xlsx";
                saveDialog.InitialDirectory = @"C:\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());


            }








        }


        #endregion
        #region /* ImportExcel(int t) */


        public DataSet ImportExcel(int t = 1)
        {
            //打開檔案
            string sql_select = string.Empty;




            OpenFileDialog file = new OpenFileDialog();
            file.Filter = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls|(*.csv)|*.csv";
            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\"";
                //讀取檔案


                if (t == 1)
                    sql_select = " SELECT ID,CREATETIME,REASONCODE1,PROCESSID FROM [Sheet1$] ";
                else if (t == 2)
                    sql_select = " SELECT PANELID,EVENTTIME,DESCRIPTION,PROCESSID FROM [Grid$] ";
                else if (t == 3)
                    sql_select = " SELECT Operation,Lot_ID,EQP_ID,Event_Time FROM [報表1$] ";




                using (OleDbConnection conn = new OleDbConnection(connString))
                using (OleDbDataAdapter cmd = new OleDbDataAdapter(sql_select, conn))
                {
                    conn.Open();
                    cmd.Fill(ds);
                }
                if (ds == null || ds.Tables.Count <= 0) return null;
                return ds;
            }
        }
        #endregion




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


        private void Form1_Load(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Name");
            dt.Columns.Add("Age");
            dt.Rows.Add("小王","15");
            dt.Rows.Add("老李","42");
            dt.Rows.Add("老張","25");
            dataGridView1.DataSource = dt;
        }


        private void button2_Click(object sender, EventArgs e)
        {
            DataSet ds = new DataSet();
            ds = ImportExcel();
            dataGridView2.DataSource = ds.Tables[0];


            // ExportCSV();
        }


        #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
    }
}

運作效果：

如果你想把資料導入csv檔案，則可以用以下方法：

#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

C# excel檔案導入導出

繼續閱讀

20條程式設計經驗

【觀點】風雨20年：我所積累的20條程式設計經驗【觀點】風雨20年：我所積累的20條程式設計經驗

【轉載】風雨20年：我所積累的20條程式設計經驗

部署.net平台的應用程式

拓端tecdat|R語言彈性網絡Elastic Net正則化懲罰回歸模型交叉驗證可視化

在一個非套接字上嘗試了一個操作

二叉樹及其應用--二叉樹建立

Oracle的基本操作

設定某一行背景顔色的CListCtrl

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

關于SQL語言

SQL語言基礎：常用的資料查詢語句

neo4j之cypher使用文檔

詳解STM32單片機的堆棧

sqlServer根據經緯查距離