1.NPOI读取Excel内容

private void button2_Click(object sender, EventArgs e){//需要读取的文件:学生表.xls// 创建文件OpenFileDialog ofd = new OpenFileDialog();ofd.Filter = "Excel文件|*.xls";ofd.ShowDialog();string filePath = ofd.FileName;FileStream fsRead=null;IWorkbook wkBook = null;if (filePath != ""){//1、创建一个工作簿workBook对象fsRead = new FileStream(filePath, FileMode.Open);//将学生表.xls中的内容读取到fsRead中wkBook = new HSSFWorkbook(fsRead);//2、遍历wkBook中的每个工作表Sheetfor (int i = 0; i < wkBook.NumberOfSheets; i++){//获取每个工作表对象ISheet sheet = wkBook.GetSheetAt(i);//获取每个工作表的行//foreach遍历 sheet.GetEnumeratorfor (int r = 0; r < sheet.LastRowNum; r++){//获取工作表中的每一行IRow currentRow = sheet.GetRow(r);//遍历当前行中的每个单元格for (int c = 0; c < currentRow.LastCellNum; c++){try{//获取每个单元格ICell cell = currentRow.GetCell(c);if (cell == null) //如果单元格为空时，程序会报错，这里判断提示用户，用try catch防止程序蹦{MessageBox.Show(string.Format("第{0}行,第{1}列单元格为空！",r,c));}CellType cType = cell.CellType; // 获取单元格中的类型MessageBox.Show(cType.ToString());//判断当前单元格的数据类型，可以拓展switch (cType){case CellType.Numeric: //数字MessageBox.Show("我是数字");break;case CellType.String: //字符串MessageBox.Show("我是字符串");break;case CellType.Boolean:MessageBox.Show("我是布尔值");break; }//获取单元格的值//日期DateTime date = cell.DateCellValue;//数字double num = cell.NumericCellValue;//字符串string str = cell.StringCellValue;//布尔值bool bl = cell.BooleanCellValue;}catch (Exception EX){}}}} }else{MessageBox.Show("选择文件失败!","提示");}fsRead.Close();wkBook.Close();fsRead.Dispose();}

2、数据库中数据，导出Excel

private void button4_Click(object sender, EventArgs e){// 需引用命名空间// using System.Data.SqlClient;// using NPOI.HSSF.UserModel;// using NPOI.SS.UserModel;// using System.IO;//1、通过读取数据string strSql = "SELECT * FROM Students";SqlDataReader reader = sqlHelper.ExecuteReader(strSql,CommandType.Text);if (reader.HasRows) //若有数据{//2、将读取到的数据写入到Excel中//2.1创建工作簿WorkBook对象IWorkbook wkBook = new HSSFWorkbook();//2.2创建工作表ISheet sheet = wkBook.CreateSheet("学生信息表"); //工作表名称 int rIndex = 0;while (reader.Read()){//每读取一条数据，就创建一行rowIRow currentRow = sheet.CreateRow(rIndex);rIndex++;int ID = reader.GetInt32(0);string name = reader.GetString(1);int age = reader.GetInt32(2);//向行中创建单元格 currentRow.CreateCell(0).SetCellValue(ID); //第一个参数：单元格索引；第二个参数：给单元格赋值currentRow.CreateCell(1).SetCellValue(name);currentRow.CreateCell(2).SetCellValue(age); }//创建文件string fileName = "学生信息表";string saveFilePath = ""; //导出时文件的路径SaveFileDialog saveDialog = new SaveFileDialog();saveDialog.DefaultExt = "xls"; //默认文件扩展名saveDialog.Filter = "Excel文件|*.xls"; //文件名筛选字符串saveDialog.FileName = fileName; //导出文件名称saveDialog.ShowDialog(); //显示窗口saveFilePath = saveDialog.FileName; //文件路径//将workBook对象写入到磁盘上FileStream fsWrite = new FileStream(saveFilePath, FileMode.Create);wkBook.Write(fsWrite);MessageBox.Show("数据导出成功！", "提示");fsWrite.Close(); //关闭文件流wkBook.Close(); //关闭工作簿fsWrite.Dispose(); //释放文件流}else{MessageBox.Show("没有数据");}//reader.Close();}

函数

public static SqlDataReader ExecuteReader(string strSql, CommandType cmdType, params SqlParameter[] pms){SqlDataReader sr = null;SqlConnection conn = new SqlConnection(conStr);SqlCommand cmd = new SqlCommand(strSql, conn);mandType = cmdType;if (pms != null){cmd.Parameters.AddRange(pms);}try{if (conn.State == ConnectionState.Closed){conn.Open();}sr = cmd.ExecuteReader();return sr;}catch (Exception EX){MessageBox.Show(EX.Message.ToString());}finally{cmd.Dispose();}return sr;}

3、Excel数据导入数据库

数据库字段字符串最好用varchar（50），不要用nchar（50）；还要设置默认值（过来人走过的坑）。

Excel数据(必须和数据库字段对上)

Thread th; //声明公共变量private void button5_Click(object sender, EventArgs e){//因为要遍历Excel中的数据，我们这里用线程执行// 需引入命名空间//using System.Threading;//using System.Data.SqlClient;//using NPOI.HSSF.UserModel;//using NPOI.SS.UserModel;//using System.IO;//创建文件object filePath = ""; // 文件路径OpenFileDialog ofd = new OpenFileDialog(); //创建文件ofd.Filter = "Excel文件|*.xls";ofd.ShowDialog();filePath = ofd.FileName;th = new Thread(inportData);th.IsBackground = true; //将线程设置为后台进程th.Start(filePath);ofd.Dispose();}private void inportData(object filePath){// 创建表副本 SELECT TOP 0 * INSERT INTO newTable FROM oldTable//1、从Excel中读取数据if (filePath.ToString() != ""){FileStream fsRead = new FileStream(filePath.ToString(), FileMode.Open);//一、创建工作簿IWorkbook workBook = new HSSFWorkbook(fsRead);string insert_sql = "";string insert_module = "INSERT INTO Student2 (id,name,age) VALUES ({0})";StringBuilder sb = new StringBuilder();for (int i = 0; i < workBook.NumberOfSheets; i++){//获取工作表ISheet sheet = workBook.GetSheetAt(i);for (int r = 0; r <= sheet.LastRowNum; r++) //遍历当前工作表中的所有行{IRow currentRow = sheet.GetRow(r); //获取每一行for (int c = 0; c < currentRow.LastCellNum; c++) //遍历当前行中的所有列{//获取每个单元格ICell cell = currentRow.GetCell(c);//listCells.Add(cell);sb.Append("'").Append(cell.ToString()).Append("',");}//拼接SQL语句insert_sql += string.Format(insert_module, sb.ToString().Substring(0, sb.ToString().Length - 1)) + ";";sb.Clear();//listCells.Clear();}}//2、把读取到的数据插入到数据库//执行SQL语句int ret = sqlHelper.ExecuteNonQuery(insert_sql, CommandType.Text);if (ret == 1){MessageBox.Show("导入成功！");}else{MessageBox.Show("导入失败！");}fsRead.Close();fsRead.Dispose();}else{MessageBox.Show("文件打开失败！");}}

函数

/// <summary>/// 执行SQL语句/// </summary>/// <param name="strSql">sql语句</param>/// <param name="cmdType">CommandType.Text代表执行的SQL语句、CommandType.StoreProcedure代表执行的是存储过程</param>/// <param name="pms">可变参数数组</param>/// <returns></returns>public static int ExecuteNonQuery(string strSql, CommandType cmdType, params SqlParameter[] pms){SqlConnection conn = new SqlConnection(conStr);SqlCommand cmd = new SqlCommand(strSql, conn);mandType = cmdType;if (pms != null){cmd.Parameters.AddRange(pms);}conn.Open();SqlTransaction trans = conn.BeginTransaction();try{cmd.Transaction = trans;int count = cmd.ExecuteNonQuery();if (count > 0){mit(); //提交事务return 1;}else{trans.Rollback(); //回滚事务return -1;}}catch (Exception EX){trans.Rollback(); //回滚事务MessageBox.Show(EX.Message.ToString());return -1;}finally{conn.Close();conn.Dispose();cmd.Dispose();}}

导出成功！！！