1 using System; 2 using System.Collections.Generic; 3 using System.ComponentModel; 4 using System.Data; 5 using System.Drawing; 6 using System.Linq; 7 using System.Text; 8 using System.Windows.Forms; 9 using System.IO; 10 using NPOI.SS.UserModel; 11 using NPOI.HSSF.UserModel; 12 using System.Data.SqlClient; 13 namespace NPOI
14 { 15 public partial class CustomersForm : Form 16 { 17 public CustomersForm() 18 { 19 InitializeComponent(); 20 } 21 private void btnData_Click(object sender, EventArgs e) 22 { 23 //创建文件流 24 using (FileStream fs = File.OpenRead(@"F:\ASP\NPOIData\Customers.xls")) 25 { 26 //创建一个工作薄 27 using (Workbook book = new HSSFWorkbook(fs)) 28 { 29 //读取工作表 30 using (Sheet sheet = book.GetSheetAt(0)) 31 { 32 //构建Sql语句 33 string sql = "insert into T_Customers
(CC_CustomerName,CC_CellPhone,CC_Landline,CC_CarNum,CC_BracketNum,CC_BuyDate)
values(@name,@phone,@landLine,@carNum,@bracketNum,@buyDate)"; 34 SqlParameter[] pms = { 35 new SqlParameter("@name",SqlDbType.NVarChar), 36 new SqlParameter("@phone",SqlDbType.VarChar), 37 new SqlParameter("@landLine",SqlDbType.VarChar), 38 new SqlParameter("@carNum",SqlDbType.VarChar), 39 new SqlParameter("@bracketNum",SqlDbType.VarChar), 40 new SqlParameter("@buyDate",SqlDbType.DateTime), 41 }; 42 //从索引为1的行开始读取数据 43 for (int i = 1; i <= sheet.LastRowNum; i++) 44 { 45 Row row = sheet.GetRow(i);//获取当前行 46 //获取当前行中的所有列 47 for (int j = 0; j < row.LastCellNum; j++) 48 { 49 Cell cell = row.GetCell(j);//获取每个单元格 50 //数据库中的null值在.net中是用DBNull.Value来表示的,不能直接写null 51 //三元表达式: 52 pms[j].Value = cell == null ? (object)DBNull.Value : cell.ToString(); 53 } 54 //每循环一次向表中插入一行 55 SqlHelper.ExecuteNonQuery(sql, pms); 56 } 57 MessageBox.Show("数据插入成功!"); 58 } 59 } 60 } 61 } 62 } 63 }