首先介绍了，博主也是新手，本文章只针对新手哦，大佬们请见谅了！
思路是：1.读取excel，2.将excel的数据导入DataTable，3.将DataTable数据循环出来，插入数据库。
支持：excel 2003 2007 2010 版本，本地电脑无需安装office
需要dll：NPOI.DLL NPOI.OOXML.DLL (这个可以自己在网上下载~)
接下来是代码（附上图片）：
（新手可以直接拿去使用）
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Linq; using System.Windows.Forms; using DevExpress.XtraEditors; using System.Data.OleDb; using TenderClear.Common; using TenderClear.BLL.TC01; using System.IO; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.SS.Formula.Eval; using NPOI.XSSF.UserModel; namespace TenderClear.UI.Forms.TC01 { public partial class TC010103Form : DevExpress.XtraEditors.XtraForm { public TC010103Form() { InitializeComponent(); } private string xmlx; //项目类型 private string xmbm;//项目编码 private string xmmc;//项目名称 private string xmdw;//项目单位 private string dydj;//电压等级 private string zyxf;//专业细分/项目专页 private DateTime kssj;//开始时间 private DateTime jssj;//结束时间 private string xmnr;//项目内容 private string cjr;//创建人 private DateTime cjsj;//创建时间 private void txtExcel_Click(object sender, EventArgs e) { //打开excel选择框 OpenFileDialog frm = new OpenFileDialog(); frm.Filter = "Excel文件(*.xls,xlsx)|*.xls;*.xlsx"; if (frm.ShowDialog() == DialogResult.OK) { txtExcel.Text = frm.FileName; } } /// <summary>读取excel /// 默认第一行为表头 /// </summary> /// <param name="strFileName">excel文档绝对路径</param> /// <param name="rowIndex">内容行偏移量，第一行为表头，内容行从第二行开始则为1</param> /// <returns></returns> public static DataTable Import(string strFileName, int rowIndex) { DataTable dt = new DataTable(); IWorkbook hssfworkbook; using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { hssfworkbook = WorkbookFactory.Create(file); } ISheet sheet = hssfworkbook.GetSheetAt(0); IRow headRow = sheet.GetRow(0); if (headRow != null) { int colCount = headRow.LastCellNum; for (int i = 0; i < colCount; i++) { dt.Columns.Add("COL_" + i); } } for (int i = (sheet.FirstRowNum + rowIndex); i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); bool emptyRow = true; object[] itemArray = null; if (row != null) { itemArray = new object[row.LastCellNum]; for (int j = row.FirstCellNum; j < row.LastCellNum; j++) { if (row.GetCell(j) != null) { switch (row.GetCell(j).CellType) { case CellType.Numeric: if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))//日期
类型
{ itemArray[j] = row.GetCell(j).DateCellValue.ToString("yyyy-MM-dd"); } else//其他数字类型 { itemArray[j] = row.GetCell(j).NumericCellValue; } break; case CellType.Blank: itemArray[j] = string.Empty; break; case CellType.Formula: if (Path.GetExtension(strFileName).ToLower().Trim() == ".xlsx") { XSSFFormulaEvaluator eva = new XSSFFormulaEvaluator(hssfworkbook); if (eva.Evaluate(row.GetCell(j)).CellType == CellType.Numeric) { itemArray[j] = eva.Evaluate(row.GetCell(j)).NumberValue; } else { itemArray[j] = eva.Evaluate(row.GetCell(j)).StringValue; } } else { HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(hssfworkbook); if (eva.Evaluate(row.GetCell(j)).CellType == CellType.Numeric) { itemArray[j] = eva.Evaluate(row.GetCell(j)).NumberValue; } else { itemArray[j] = eva.Evaluate(row.GetCell(j)).StringValue; } } break; default: itemArray[j] = row.GetCell(j).StringCellValue; break; } if (itemArray[j] != null && !string.IsNullOrEmpty(itemArray[j].ToString().Trim())) { emptyRow = false; } } } } //非空数据行数据添加到DataTable if (!emptyRow) { dt.Rows.Add(itemArray); } } return dt; } private void InsertToDatabase() { DataTable dt = Import(txtExcel.Text, 1); // 1表示第一行为表头，内容行从第二行开始 int count = 0; foreach (DataRow dr in dt.Rows) { xmlx = dr[0].ToString(); xmbm = dr[1].ToString(); xmmc = dr[2].ToString(); xmdw = dr[3].ToString(); dydj = dr[4].ToString(); zyxf = dr[5].ToString(); kssj = Convert.ToDateTime(dr[6].ToString()); jssj = Convert.ToDateTime(dr[7].ToString()); xmnr = dr[8].ToString(); count = BLL10101.InsertOrUpdate(xmlx, xmbm, xmmc, xmdw, dydj, zyxf, xmnr, kssj, jssj); } if (count > 0) { MsgBox.Sucess("导入成功！"); } } private void simpleButton2_Click(object sender, EventArgs e) { InsertToDatabase(); } } }
