C#匯入匯出（excel）資料

C#匯入匯出（excel）資料

今天弄了一個匯入匯出excel資料的例子，首先命名空間要引用：Microsoft.Office.Interop.Excel和System.IO。下面是我弄的代碼（僅限參考）：

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 Microsoft.Office.Interop.Excel;

using System.Data.SqlClient;

using System.IO;


namespace ImportExcel

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }


        System.Data.DataTable dt = new System.Data.DataTable();


        //將excel裡的資料匯入資料到windows表單

        private void btn_Import_Click(object sender, EventArgs e)

        {

            OpenFileDialog ofd = new OpenFileDialog();

            ofd.Filter = "Excel Files|*.xls";

            if (ofd.ShowDialog() == DialogResult.OK)

            {

                string filename = ofd.FileName;

                Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

                Microsoft.Office.Interop.Excel.Workbook workbook;

                Microsoft.Office.Interop.Excel.Worksheet worksheet;

                object oMissing = System.Reflection.Missing.Value;

                workbook = excel.Workbooks.Open(filename, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);

                worksheet = (Worksheet)workbook.Worksheets[1];

                int rowCount = worksheet.UsedRange.Rows.Count;

                int colCount = worksheet.UsedRange.Columns.Count;

                Microsoft.Office.Interop.Excel.Range range1;

                


                int i;

                for (i = 0; i < colCount; i++)

                {

                    range1 = worksheet.Range[worksheet.Cells[1, i + 1], worksheet.Cells[1, i + 1]];

                    dt.Columns.Add(range1.Value2.ToString());

                }


                int j;

                for (j = 1; j < rowCount; j++)

                {

                    DataRow dr = dt.NewRow();

                    for (i = 0; i < colCount; i++)

                    {

                        range1 = worksheet.Range[worksheet.Cells[j + 1, i + 1], worksheet.Cells[j + 1, i + 1]];

                        dr[i] = range1.Value2;

                    }

                    dt.Rows.Add(dr);

                }

                dgv_Import.DataSource = dt;

                excel.Quit();

            }

            else

            {

                MessageBox.Show("檔案路徑出錯!");

            }

        }


        //將剛匯入windows表單的資料儲存到對應的資料庫中

        private void btn_Save_Click(object sender, EventArgs e)

        {

            string connStr = "server=.;database=SDSYSTEM;Trusted_Connection=true";

            SqlConnection conn = new SqlConnection(connStr);

            SqlCommand cmd = new SqlCommand(connStr,conn);

            cmd.CommandText = "insert into Student (studentId,studentName,sex,academyId,majorId,dormId,memo) values (@學號,@姓名,@性別,@學院,@專業,@宿舍,@備忘)";

            cmd.Parameters.Add(new SqlParameter("@學號", SqlDbType.VarChar));

            cmd.Parameters.Add(new SqlParameter("@姓名", SqlDbType.VarChar));

            cmd.Parameters.Add(new SqlParameter("@性別", SqlDbType.VarChar));

            cmd.Parameters.Add(new SqlParameter("@學院", SqlDbType.VarChar));

            cmd.Parameters.Add(new SqlParameter("@專業", SqlDbType.VarChar));

            cmd.Parameters.Add(new SqlParameter("@宿舍", SqlDbType.VarChar));

            cmd.Parameters.Add(new SqlParameter("@備忘", SqlDbType.VarChar));

            if (conn.State == ConnectionState.Closed)

                {

                    conn.Open();

                }

            try

            {

                for (int i = 0; i < dt.Rows.Count; i++)

                {

                    cmd.Parameters["@學號"].Value = http://www.cnblogs.com/WSL-william-love/archive/2012/04/23/dt.Rows[i][0].ToString();

                    cmd.Parameters["@姓名"].Value = http://www.cnblogs.com/WSL-william-love/archive/2012/04/23/dt.Rows[i][1].ToString();

                    cmd.Parameters["@性別"].Value = http://www.cnblogs.com/WSL-william-love/archive/2012/04/23/dt.Rows[i][2].ToString();

                    cmd.Parameters["@學院"].Value = http://www.cnblogs.com/WSL-william-love/archive/2012/04/23/dt.Rows[i][3].ToString();

                    cmd.Parameters["@專業"].Value = http://www.cnblogs.com/WSL-william-love/archive/2012/04/23/dt.Rows[i][4].ToString();

                    cmd.Parameters["@宿舍"].Value = http://www.cnblogs.com/WSL-william-love/archive/2012/04/23/dt.Rows[i][5].ToString();

                    cmd.Parameters["@備忘"].Value = http://www.cnblogs.com/WSL-william-love/archive/2012/04/23/dt.Rows[i][6].ToString();

                    cmd.ExecuteNonQuery();  //每一次都要執行一次插入操作，而不能講插入操作放在for外面

                }

                conn.Close();

                MessageBox.Show("成功將資料儲存到資料庫中！");

                dgv_Import.DataSource = null;

                dgv_Import.ReadOnly = true;

            }

            catch (Exception ex)

            {

                MessageBox.Show("儲存資料有錯！" + ex.Message.ToString());

            }

        }


        //將windows裡的資料匯出到excel裡

        private void btn_OutPut_Click(object sender, EventArgs e)

        {

            SaveFileDialog dlg = new SaveFileDialog();   //執行個體化一個SaveFileDialog儲存檔案對話方塊

            dlg.Filter = "Excel files (*.xls)|*.xls";

            dlg.FilterIndex = 0;

            dlg.RestoreDirectory = true;

            dlg.CreatePrompt = true;



            if (dlg.ShowDialog() == DialogResult.OK)

            {

                Stream myStream;

                myStream = dlg.OpenFile();

                StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));

                string columnTitle = "";


                try

                {

                    //寫入欄位標題

                    for (int i = 0; i < dgv_Import.ColumnCount; i++)

                    {

                        if (i > 0)

                        {

                            columnTitle += "\t";

                        }

                        columnTitle += dgv_Import.Columns[i].HeaderText;

                    }

                    sw.WriteLine(columnTitle);


                    //寫入列內容

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

                    {

                        string columnValuehttp://www.cnblogs.com/WSL-william-love/archive/2012/04/23/= "";

                        for (int k = 0; k < dgv_Import.Columns.Count; k++)

                        {

                            if (k > 0)

                            {

                                columnValue += "\t";

                            }

                            if (dgv_Import.Rows[j].Cells[k].Value =http://www.cnblogs.com/WSL-william-love/archive/2012/04/23/= null)

                                columnValue += "";

                            else

                                columnValue += dgv_Import.Rows[j].Cells[k].Value.ToString().Trim();

                        }

                        sw.WriteLine(columnValue);

                    }

                    sw.Close();

                    myStream.Close();

                }

                catch

                {

                    MessageBox.Show("匯出不成功");

                }

                finally

                {

                    sw.Close();

                    myStream.Close();

                }

            }

        }

    }

}