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();
                }
            }
        }
    }
}