excel导入数据到数据库，只能根据特定条件来到，需要设置模板excel

这个功能也是我以前项目中经常用到的，感觉很实用，拿来分享下：

上传文件代码：

string cmsPath = ConfigurationManager.AppSettings["UserDataUrl"];//获取配置路径

// string savePath g= Server.MapPath(cmsPath) + "\\";//虚拟路径映射物理路径
string savePath =Path.Combine(Server.MapPath(cmsPath), "CMS_ImportExcel");
if (FileUpload1.HasFile)
{
string fileExt = System.IO.Path.GetExtension(FileUpload1.FileName);//文件后缀名
if (fileExt == ".xls")
{
try
{ 
string filename = FileUpload1.FileName;
//string PathsPuff = Path.Combine(savePath, filename);
savePath = Path.Combine(savePath, filename);

//savePath += filename;
if (filename== "importexceltemp.xls")
{
this.lblError.Text = "不允许上传文件名称为：importexceltemp.xls的文件";
return;
}
FileUpload1.SaveAs(savePath);//文件拿到了 
//FileUpload1.SaveAs(PathsPuff) ;

Session["filename"] = filename;
Workbook book = Workbook.Load(savePath);
//Workbook book = Workbook.Load(PathsPuff);
Worksheet sheet1 = book.Worksheets[0];
this.lblError.Text = "文件名为:"+filename+"的文件成功上传了:"+ sheet1.Cells.LastRowIndex + "条数据";
//Response.Write("<script languge='javascript'>alert('共有数据" + sheet1.Cells.LastRowIndex + "条数据')</script>");
this.btnExcelToImport.Enabled = true;//确定导入可用
this.btnUpload.Enabled = true;//上传不可用
}
catch (Exception ex)
{
this.lblError .Text=ex.Message;
}
}
else
{
this.lblError.Text = "文件只能.xls类型的文件";

//Response.Write("<script languge='javascript'>alert('文件只能.xls或者.xlsx类型的文件')</script>");
}
}
else
{
this.lblError.Text = "没有选择要上传的文件";
//Response.Write("<script languge='javascript'>alert('没有选择要上传的文件')</script>");
}

 

 

excel数据导入代码：

string cmsPath = ConfigurationManager.AppSettings["UserDataUrl"];//获取配置路径
List<string> colName = new List<string>();//第一行数据值

string savePaths = Server.MapPath(cmsPath);//虚拟路径映射物理路径
if (Session["filename"]!= null)
{


string readPath = Path.Combine("CMS_ImportExcel", Session["filename"].ToString());

//string readPath = savePaths + Session["filename"].ToString();//获取上传后的文件savePaths + Session["filename"].ToString() != null
try
{
string rootPath = Path.Combine(savePaths, readPath);//拼接

Workbook book = Workbook.Load(rootPath);
//Workbook book = Workbook.Load(readPath);
Worksheet sheet = book.Worksheets[0];
if (sheet.Cells.LastRowIndex > 0)//判断是否有数据
{
Row rowOne = sheet.Cells.GetRow(sheet.Cells.FirstRowIndex);//第一行数据 
for (int colIndex = rowOne.FirstColIndex; colIndex <= rowOne.LastColIndex; colIndex++)
{
Cell cell = rowOne.GetCell(colIndex);
colName.Add(cell.Value.ToString());
}
List<SortedList> cmslist = new List<SortedList>();
for (int RowIndex = sheet.Cells.FirstRowIndex + 1; RowIndex <= sheet.Cells.LastRowIndex; RowIndex++)
{
Row row = sheet.Cells.GetRow(RowIndex);
SortedList cms = new SortedList();
cms.Add("ChannelID", Convert.ToInt32(Request.QueryString["ChannelID"]));
for (int i = 0; i < colName.Count; i++)
{
Cell cell = row.GetCell(i);
string currColname = colName[i];
if (currColname == "opDate1" || currColname == "opDate2" || currColname == "opDate3" || currColname == "opDate4" || currColname == "opDate5" || currColname == "opDate6" || currColname == "opDate7" || currColname == "StatusCode" || currColname == "opUserName1" || currColname == "ClickCount" || currColname == "Identifier" || currColname == "DocDate")
continue;
cms.Add(currColname, cell.Value);
}
WebSessionState sessionState = new WebSessionState();
cms.Add("opUserName1", sessionState.UserModuleList);
cms.Add("StatusCode", "NEW");
cms.Add("opDate1", DateTime.Now);
cmslist.Add(cms);
}
MetaDataDao mdd = new MetaDataDao(XFWebApplication.Instance.ConnectionString);
int num = mdd.AddMetaDataExcel(cmslist);
if (num > 0)//导入成功
{
this.lblError.Text = "成功导入了:" + num + "条数据";
//Response.Write("<script languge='javascript'>alert('成功导入了" + num + "条数据')</script>");
}
else 
{
this.lblError.Text = "导入失败，请检查excel内容是否正确";
//Response.Write("<script languge='javascript'>alert('导入失败，请检查excel内容是否正确')</script>");
}
}
else
{
this.lblError.Text = "文件不存在数据";
//Response.Write("<script languge='javascript'>alert('文件不存在数据')</script>");
}
}
catch (Exception ex)
{
this.lblError.Text = ex.Message;
//throw new Exception(ex.Message);
}
}
else 
{
this.lblError.Text = "选择文件后没有点击upload,请点击upload再确定导入";
//Response.Write("<script languge='javascript'>alert('选择文件后没有点击upload,请点击upload再确定导入')</script>"); 
}