首先介绍了,博主也是新手,本文章只针对新手哦,大佬们请见谅了!
思路是: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();
}
}
}