using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb; //导入命名空间
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Data;
using EnterExcel.BLL;
using EnterExcel.Models;
public partial class JoinExcel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack) { }
}
private static List<string> listFiles = new List<string>();
List<Pencil> pencilList = new List<Pencil>();
//“将Excel添加到集合中”事件
protected void btnAdd_Click(object sender, EventArgs e)
{
listFiles.Add(this.fuSearchExcel.PostedFile.FileName);
}
//“导入Excel到界面”事件
protected void btnJoinExcel_Click(object sender, EventArgs e)
{
//遍历集合
foreach (string listFile in listFiles)
{
//将Excel路径循环赋给方法
DataTable dt = CreateDataSource(listFile);
foreach (DataRow row in dt.Rows)
{
Pencil pencil = new Pencil();
pencil.Name = row[0].ToString();
pencil.Type = row[1].ToString();
pencil.Number = row[2].ToString();
pencil.Unit = row[3].ToString();
pencilList.Add(pencil);
}
}
this.gvInfo.DataSource = LoadExcelFiles(pencilList);
this.gvInfo.DataBind();
listFiles.Clear();
}
List<Pencil> list = new List<Pencil>();
public List<Pencil> LoadExcelFiles(List<Pencil> pencilList)
{
Pencil p = null;
for (int i = 0; i < pencilList.Count; i++)
{
for (int j = i+1; j < pencilList.Count; j++)
{
if (pencilList[i].Name == pencilList[j].Name && pencilList[i].Type == pencilList[j].Type)
{
p = new Pencil();
p.Name = pencilList[i].Name;
p.Type = pencilList[i].Type;
p.Number = (int.Parse(pencilList[i].Number) + int.Parse(pencilList[j].Number)).ToString();
p.Unit = pencilList[i].Unit;
pencilList[i]=p;
pencilList.RemoveAt(j);
j--;
}
}
}
return pencilList;
}
/// <summary>
/// 创建数据源
/// </summary>
private DataTable CreateDataSource(string path)
{
//客户端路径
string strCon = "provider = Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 8.0;";
//建立连接
OleDbConnection olecon = new OleDbConnection(strCon);
//实例化数据适配器
OleDbDataAdapter myda = new OleDbDataAdapter("select * from [MyPencil$]", olecon);
//新建数据表
DataTable myds = new DataTable();
myda.Fill(myds);
return myds;
}
//“分页”
protected void gvInfo_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
this.gvInfo.PageIndex = e.NewPageIndex;
this.gvInfo.DataSource = LoadExcelFiles(pencilList);
this.gvInfo.DataBind();
}
}