C# .Net :Excel NPOI導入導出操作教程之將Excel文件讀取並寫到資料庫表,示例分享 ...
using (FileStream fileReader = File.OpenRead(@"C:\Users\Administrator\Desktop\112.xls"))
{
//創建工作簿對象接收文件流(Excel信息)
IWorkbook workbook = new HSSFWorkbook(fileReader);
//工作簿共有幾個表
//int count = workbook.NumberOfSheets;
//創建工作表讀取工作簿表信息
//ISheet sheet= workbook.GetSheet("表名稱");
ISheet sheet = workbook.GetSheetAt(0);
string sql = @" insert into T_ExcelIn(Name,Remarks) values (@Name,@Remarks)";
int ii = 0;
//r = 1,剔除表頭一行
for (int r = 1; r <= sheet.LastRowNum; r++)
{
//定義參數數組para
SqlParameter[] para = new SqlParameter[] {
new SqlParameter("Name",SqlDbType.NVarChar,50),
new SqlParameter("Remarks",SqlDbType.NVarChar,50)
};
//創建一行獲取sheet行數據
IRow row = sheet.GetRow(r);
List<ICell> listcell = new List<ICell>();
//int c = 1 即不讀取自動編號的Id列
for (int c = 1; c < row.LastCellNum; c++)
{
//將每行每個單元格的值添加帶listcell集合中
listcell.Add(row.GetCell(c));
}
//迴圈賦值給para
for (int i = 0; i < listcell.Count; i++)
{
para[i].Value = listcell[i].ToString();
}
//執行添加Sql語句
ii += SqlHelper.ExecuteNonQuery(sql, para);
}
if (ii > 0)
{
Response.Write("<script>alert('已將Excel數據插入到資料庫表')</script>");
}
else
{
Response.Write("<script>alert('Excel數據插入到資料庫表失敗!')</script>");
}
}
————————————參數傳遞不經過List<>集合————————————
using (FileStream fileReader = File.OpenRead(@"C:\Users\Administrator\Desktop\112.xls"))
{
//創建工作簿對象接收文件流(Excel信息)
IWorkbook workbook = new HSSFWorkbook(fileReader);
//工作簿共有幾個表
//int count = workbook.NumberOfSheets;
//創建工作表讀取工作簿表信息
//ISheet sheet= workbook.GetSheet("表名稱");
ISheet sheet = workbook.GetSheetAt(0);
string sql = @" insert into T_ExcelIn(Name,Remarks) values (@Name,@Remarks)";
int ii = 0;
//r = 1,剔除表頭一行
for (int r = 1; r <= sheet.LastRowNum; r++)
{
//定義參數數組para
SqlParameter[] para = new SqlParameter[] {
new SqlParameter("Name",SqlDbType.NVarChar,50),
new SqlParameter("Remarks",SqlDbType.NVarChar,50)
};
//創建一行獲取sheet行數據
IRow row = sheet.GetRow(r);
//List<ICell> listcell = new List<ICell>();
//int c = 1 即不讀取自動編號的Id列
for (int c = 1; c < row.LastCellNum; c++)
{
//將每行每個單元格的值添加帶listcell集合中
//listcell.Add(row.GetCell(c));
//將每行的每個單元格的數據添加到para中 c-1即從0 開始記錄參數
para[c-1].Value = row.GetCell(c).ToString();
}
//迴圈賦值給para
//for (int i = 0; i < listcell.Count; i++)
//{
// para[i].Value = listcell[i].ToString();
//}
//執行添加Sql語句
ii = SqlHelper.ExecuteNonQuery(sql, para);
}
if (ii > 0)
{
Response.Write("<script>alert('已將Excel數據插入到資料庫表')</script>");
}
else
{
Response.Write("<script>alert('Excel數據插入到資料庫表失敗!')</script>");
}
}