1.excel的导入按钮
/// <summary>
/// 导入
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnImport_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "(*.xls,*.xlsx)|*.xls;*.xlsx";
if (ofd.ShowDialog(this) == DialogResult.OK)
{
string str = ofd.FileName;
Dictionary<string, string> headDict = new Dictionary<string, string>();
headDict.Add("坐标点", "PointCode");
headDict.Add("寄存器地址", "Register");
List<AGVPoint> AGVPointList = ExcelUtils.ImportExcel<AGVPoint>(str, headDict);
Landmark landmark = landmarkLogic.GetLandmark();
//寄存器地址不存在
if (landmark == null)
{
this.Info("请先添加寄存器的结束地址!");
return;
}
//判断数据的合法性
if (AGVPointList.Count > 0)
{
for (int i = 0; i < AGVPointList.Count; i++)
{
//判断编号是否存在
if (null != pointLogic.Get(AGVPointList[i].PointCode))
{
this.Info("第" + (i + 1) + "行坐标点编号已存在,新增失败!");
return;
}
//判断寄存器地址是否已经被占用
if (null != pointLogic.GetRegister(AGVPointList[i].Register))
{
this.Info("第" + (i + 1) + "行寄存器地址已经被占用,新增失败!");
return;
}
if (landmark.MarkNum < Convert.ToInt32(AGVPointList[i].Register))
{
this.Info("第" + (i + 1) + "行填写的寄存器地址大于寄存器结束地址,请重新输入!");
return;
}
}
//插入到数据库中
int row = pointLogic.InsertList(AGVPointList);
if (row > 0)
{
this.Info("坐标点导入成功!");
LoadPointData();
}
else
{
this.Info("坐标点导入失败!");
}
}
}
}
2.ExcelUtils
/// <summary>
/// 导入Excel
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static DataTable ImportExcel(string filePath)
{
DataTable dt = new DataTable();
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite))
{
using (ExcelPackage pck = new ExcelPackage(fs))
{
ExcelWorksheet ws = pck.Workbook.Worksheets[1];
int minRowNum = ws.Dimension.Start.Row; //工作区开始行号
int minColNum = ws.Dimension.Start.Column;
int maxRowNum = ws.Dimension.End.Row; //工作区结束行号
int maxColNum = ws.Dimension.End.Column;
//创建DataTable 列
for (int i = minColNum; i <= maxColNum; i++)
{
//表头
object obj = ws.Cells[minRowNum, i].Value;
string colName = obj.ToString();
DataColumn datacolum = new DataColumn(colName);
dt.Columns.Add(datacolum);
}
//数据
for (int i = minRowNum + 1; i <= maxRowNum; i++)
{
//创建行
DataRow dr = dt.NewRow();
//循环填充数据
for (int j = minColNum; j <= maxColNum; j++)
{
object obj = ws.Cells[i, j].Value;
dr[j - 1] = obj;
}
dt.Rows.Add(dr); //把每行追加到DataTable
}
}
}
return dt;
}
/// <summary>
/// 导入Excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="filePath"></param>
/// <param name="headDict"></param>
/// <returns></returns>
public static List<T> ImportExcel<T>(string filePath, Dictionary<string, string> headDict)
{
DataTable dt = ImportExcel(filePath);
//根据Dict,修改dt字段名
foreach (KeyValuePair<string, string> pair in headDict)
{
dt.Columns[pair.Key].ColumnName = pair.Value;
}
string json = JSONUtils.obj2Json(dt);
return JSONUtils.JsonToList<T>(json);
}