c#导入导出excel（无格式控制）

usingSystem;

usingSystem.Collections.Generic;

usingSystem.Linq;

usingSystem.Text;

usingSystem.Windows.Forms;

usingMicrosoft.Office.Interop.Excel;

 

namespaceExcelTools

{

   public classReadingAndwriting

   {

       ///<summary>

       ///导出Excel

       ///</summary>

       ///<param name="gridView">dataGridView控件名</param>

       ///<param name="filename">路径名</param>

       public staticvoid WritingExcel(DataGridView gridView, string filename)

       {

            Microsoft.Office.Interop.Excel.Application app = newMicrosoft.Office.Interop.Excel.Application();

            Workbookbook = app.Workbooks.Add(Type.Missing);

            Worksheetsheet = book.Worksheets[1] as Worksheet;

 

            //sheet.Name= "student";

 

            //遍历所有的列标题

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

            {

                sheet.Cells[1, i + 1] =gridView.Columns[i].HeaderText;

            }

 

            //循环读取所有的数据

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

            {

                for(int j = 0; j < gridView.Columns.Count; j++)

               {

                    sheet.Cells[i + 2, j + 1] =gridView.Rows[i].Cells[j].Value;

                }

            }

            //设置禁止弹出保存和覆盖的询问提示框 

            app.DisplayAlerts = false;

            app.AlertBeforeOverwriting = false;

 

            //sheet.SaveAs(Environment.CurrentDirectory+ "\\" + filename + ".xls", Type.Missing, Type.Missing,Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,Type.Missing, Type.Missing);

            //保存excel文件

            sheet.SaveAs(filename, Type.Missing, Type.Missing,Type.Missing, Type.Missing,Type.Missing, Type.Missing,Type.Missing, Type.Missing,Type.Missing);

            book.Close(Type.Missing,Type.Missing, Type.Missing);

            app.Workbooks.Close();

            app.Quit();

       }

 

       ///<summary>

       ///导入Excel

       ///</summary>

       ///<param name="gridView">dataGridView控件名</param>

       ///<param name="filename">路径名</param>

       public staticvoid ReadingExcel(DataGridView gridView, string fileName)

       {

            Microsoft.Office.Interop.Excel.Application app = newMicrosoft.Office.Interop.Excel.Application();

            Workbookbook = app.Workbooks.Open(fileName, Type.Missing,Type.Missing, Type.Missing,Type.Missing, Type.Missing,Type.Missing, Type.Missing,Type.Missing, Type.Missing,Type.Missing, Type.Missing,Type.Missing, Type.Missing,Type.Missing);

            Worksheetsheet = book.Worksheets[1] as Worksheet;

 

           

           

            gridView.Rows.Clear();

            gridView.Columns.Clear();

 

            //添加每一列的列标题

            for(int i = 1; ; i++)

            {

                stringvalue = (sheet.Cells[1, i] as Range).Text.ToString();//获得每列第一行数据。

                if(value == string.Empty)//如果为空则代表已无数据

                {

                    break;

                }

                gridView.Columns.Add("", value);

            }

 

            //导入每一行的数据

            for(int i = 2; ; i++)

            {

                stringvalue = (sheet.Cells[i, 1] as Range).Text.ToString();//获得每行第一列数据。

                if(value == string.Empty)//如果为空则代表已无数据

                {

                    break;

                }

 

                //获得EXCEL中每行上的每列数据

                stringID = (sheet.Cells[i, 1] as Range).Text.ToString();

                stringparam1 = (sheet.Cells[i, 2] as Range).Text.ToString();

                stringparam2 = (sheet.Cells[i, 3] as Range).Text.ToString();

                stringparam3 = (sheet.Cells[i, 4] as Range).Text.ToString();

                stringparam4 = (sheet.Cells[i, 5] as Range).Text.ToString();

                stringparam5 = (sheet.Cells[i, 6] as Range).Text.ToString();

                stringparam6 = (sheet.Cells[i, 7] as Range).Text.ToString();

                gridView.Rows.Add(new object[] { ID,param1, param2, param3, param4, param5, param6 });

            }

 

            book.Close(Type.Missing,Type.Missing, Type.Missing);

            app.Workbooks.Close();

            app.Quit();

       }

 

   }

}

 

 

注：以上为c# VS2008的写法，c# VS2010的写法把文中所有的（Type.Missing）去掉就行。

以下内容为实际调用方法：

usingSystem;

usingSystem.Collections.Generic;

usingSystem.ComponentModel;

usingSystem.Data;

usingSystem.Drawing;

usingSystem.Linq;

usingSystem.Text;

usingSystem.Windows.Forms;

usingSystem.Data.SqlClient;

usingSystem.IO;

usingSystem.Data.Sql;

usingMicrosoft.Office.Interop.Excel;

 

 

//创建人牟鹏，2014-12，excel导入功能的实现

 

namespaceWindowsFormsApplication1

{

   public partialclass Form1: Form

   {

       public Form1()

       {

            InitializeComponent();

       }

 

       private voidbtn_close_Click(object sender, EventArgs e)

       {

            this.Close();

       }

       ///<summary>

       ///数据库插入数据操作

       ///</summary>

       ///<param name="username">用户名</param>

       ///<param name="pwd">密码</param>

       ///<param name="remark">备注</param>

       ///<returns>true表示插入成功，false表示插入失败</returns>

       public staticbool DataCon(stringusername,string pwd,stringremark)

       {

           //数据库连接字符串

            stringsqlCon = "Server=; database=InDataOfExcel;Uid=sa;pwd=123;";

            using(SqlConnection con = new SqlConnection(sqlCon))

            {

                //判断数据库连接状态

                if(con.State != ConnectionState.Open)

                {

                    con.Close();

                    con.Open();

                }

                SqlCommandcmd = con.CreateCommand();

                //数据操作字符串

                stringsqlText = "insert into userCon(username,pwd,remark)values('"+username+"','"+pwd+"','"+remark+"')";

                cmd.CommandText = sqlText;

                //返回受影响的行数大于0表示插入成功

                if(cmd.ExecuteNonQuery() > 0)

                {

                    returntrue;

 

                }

                else

                {

                    returnfalse;

                }

                }

           

       }     

 

      ///<summary>

       ///导入Excel

       ///</summary>

       ///<param name="gridView">dataGridView控件名</param>

       ///<param name="filename">路径名</param>

       public staticvoid ReadingExcel(DataGridViewgridView, string fileName)

       {

                           

            Microsoft.Office.Interop.Excel.Application app = newMicrosoft.Office.Interop.Excel.Application();

             Workbookbook = app.Workbooks.Open(fileName, Type.Missing,Type.Missing, Type.Missing,Type.Missing, Type.Missing,Type.Missing, Type.Missing,Type.Missing, Type.Missing,Type.Missing, Type.Missing,Type.Missing, Type.Missing,Type.Missing);

            Worksheetsheet = book.Worksheets[1] as Worksheet;        

           

            gridView.Rows.Clear();

            gridView.Columns.Clear();

 

            //添加每一列的列标题

            for(int i = 1; ; i++)

            {

                stringvalue = (sheet.Cells[1, i] as Range).Text.ToString();//获得每列第一行数据。

                if(value == string.Empty)//如果为空则代表已无数据

                {

                    break;

                }

                gridView.Columns.Add("", value);

            }

 

            //导入每一行的数据

            for(int i = 2; ; i++)

            {

               

                stringvalue = (sheet.Cells[i, 1] as Range).Text.ToString();//获得每行第一列数据。

                if(value == string.Empty)//如果为空则代表已无数据

                {

                    break;

                }

                //获得EXCEL中每行上的每列数据

                stringID = (sheet.Cells[i, 1] as Range).Text.ToString();

                stringusername = (sheet.Cells[i, 2] as Range).Text.ToString();

                stringpwd= (sheet.Cells[i, 3] as Range).Text.ToString();

                stringremark = (sheet.Cells[i, 4] as Range).Text.ToString();

                DataCon(username, pwd, remark);

                gridView.Rows.Add(new object[] { ID,username, pwd, remark });

            }

 

            book.Close(Type.Missing,Type.Missing, Type.Missing);

            app.Workbooks.Close();

            app.Quit();

            DevComponents.DotNetBar.MessageBoxEx.Show("数据导入成功");

       }

       ///<summary>

       ///导入数据事件

       ///</summary>

       ///<param name="sender"></param>

       ///<param name="e"></param>

       private voidbtn_inport_Click(object sender, EventArgs e)

       {

            //打开要导入的文件

            OpenFileDialogopen=new OpenFileDialog();

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

            {

                open.ShowDialog();

                stringfileName = open.FileName;

                ReadingExcel(dataGridViewX1,fileName);

            }

            else

            {

                return;

            }

          

       }

 

   }

}