//导入提量数据
private void toggleButton2_Click(object sender, RibbonControlEventArgs e)
{
//MessageBox.Show("导入提量数据，这里默认是三级");
Microsoft.Office.Interop.Excel.Application excelApp = Globals.ThisAddIn.Application;
Workbook wb = ExcelHelper.GetActiveWorkbook(true, excelApp);
Worksheet ws = null;
if (wb == null)
{
return;
}
//获取第三个工作簿
ws = wb.Worksheets[1];
if (ws == null)
{
return;
}
//定义集合(工作簿1数据 用于存数据的坐标--数据信息)
Dictionary <String, bamBean> dic = new Dictionary <String, bamBean>();
int rowcount = ws.UsedRange.CurrentRegion.Rows.Count;
//MessageBox.Show("行：" + rowcount);
int colcount = ws.UsedRange.CurrentRegion.Columns.Count;
//MessageBox.Show("列：" + colcount);
//先取出表格第二行数据
string projectname = ws.Cells[2, 2].Text;
string projectcode = ws.Cells[2, 9].Text;
string address = ws.Cells[2, 5].Text;
string area = ws.Cells[2, 7].Text;
project project = new project();
string projectId = System.Guid.NewGuid().ToString("N");
project.Id = projectId;
project.Name = projectname;
project.Code = projectcode;
project.Address = address;
project.Area = area;
insertinto(project);
//批量插入提量数据
for (int i = 4; i <= rowcount; i++)
{
for (int j = 2; j <= colcount; j++)
{
//判断是否为构件
if (j == 2)
{
if (!string.IsNullOrEmpty(ws.Cells[i, j].Text))
{
component component = new component();
String componentId = System.Guid.NewGuid().ToString("N");
component.Id = componentId;
component.Projectid = projectId;
component.Name = ws.Cells[i, j].Text;
saveComponent(component);
//记录数据
bamBean bean = new bamBean();
bean.Id = componentId;
dic.Add(i + "-" + j, bean);
}
else
{
//记录数据
bamBean bean = new bamBean();
bean.Id = dic[(i - 1) + "-" + j].Id;
dic.Add(i + "-" + j, bean);
}
}
//第三列
else if (j == 3)
{
if (!string.IsNullOrEmpty(ws.Cells[i, j].Text))
{
meterage meterage = new meterage();
String meterageId = System.Guid.NewGuid().ToString("N");
meterage.Id = meterageId;
// 从dic中取j-1中的数据
meterage.Componentid = dic[i + "-" + (j - 1)].Id;
meterage.Parentid = "-1";
meterage.Materielcode = "code";
meterage.Materielname = ws.Cells[i, j].Text;
meterage.Specifications = ws.Cells[i, (j + 2)].Text;
meterage.Count = ws.Cells[i, (j + 3)].Text;
meterage.Unit = ws.Cells[i, (j + 4)].Text;
meterage.Rule = ws.Cells[i, (j + 5)].Text;
meterage.Lossrate = "";
meterage.Memo = ws.Cells[i, (j + 6)].Text;
saveMeterage(meterage);
//记录数据
bamBean bean = new bamBean();
bean.Id = meterageId;
dic.Add(i + "-" + j, bean);
}
else
{
if (string.IsNullOrEmpty(ws.Cells[i, (j - 1)].Text))
{
//记录数据
bamBean bean = new bamBean();
bean.Id = dic[(i - 1) + "-" + j].Id;
dic.Add(i + "-" + j, bean);
}
}
}
else if (j == 4)
{
if (!string.IsNullOrEmpty(ws.Cells[i, j].Text))
{
meterage meterage = new meterage();
meterage.Id = System.Guid.NewGuid().ToString("N");
meterage.Componentid = dic[i + "-" + (j - 2)].Id;
meterage.Parentid = dic[i + "-" + (j - 1)].Id;
meterage.Materielcode = "code";
meterage.Materielname = ws.Cells[i, j].Text;
meterage.Specifications = ws.Cells[i, (j + 1)].Text;
meterage.Count = ws.Cells[i, (j + 2)].Text;
meterage.Unit = ws.Cells[i, (j + 3)].Text;
meterage.Rule = ws.Cells[i, (j + 4)].Text;
meterage.Lossrate = "";
meterage.Memo = ws.Cells[i, (j + 5)].Text;
saveMeterage(meterage);
}
}
}
}
MessageBox.Show("导入成功！");
}
//导入设计数据
private void btok_Click(object sender, EventArgs e)
{
try
{
//处理对话框中的数据
String textProject = this.textProject.Text;
String txt1 = this.txt1.Text;
String text2 = this.text2.Text;
String text3 = this.text3.Text;
String textPlan = this.textPlan.Text;
//始末行号
String textStart = this.textStart.Text;
String textEnd = this.textEnd.Text;
String houseId = findHouseId(textProject);
if (string.IsNullOrEmpty(houseId))
{
//保存house数据
house house = new house();
houseId = System.Guid.NewGuid().ToString("N");
house.Id = houseId;
house.Housename = textProject;
saveHouse(house);
}
//保存houseplan数据
houseplan houseplan = new houseplan();
String houseplanid = System.Guid.NewGuid().ToString("N");
houseplan.Id = houseplanid;
houseplan.Houseid = houseId;
houseplan.Planname = textPlan;
houseplan.Room = txt1;
houseplan.Part = text2;
houseplan.Materialpackge = text3;
saveHousePlan(houseplan);
Microsoft.Office.Interop.Excel.Application excelApp = Globals.ThisAddIn.Application;
Workbook wb = ExcelHelper.GetActiveWorkbook(true, excelApp);
Worksheet ws = null;
if (wb == null)
{
return;
}
//获取第三个工作簿
ws = wb.Worksheets[3];
if (ws == null)
{
return;
}
//定义集合(工作簿1数据 用于存数据的坐标--数据信息)
Dictionary <String, bamBean> dic = new Dictionary <String, bamBean>();
int rowcount = ws.UsedRange.CurrentRegion.Rows.Count;
//MessageBox.Show("行：" + rowcount);
int colcount = ws.UsedRange.CurrentRegion.Columns.Count;
//MessageBox.Show("列：" + colcount);
//处理行数
int start = String.IsNullOrEmpty(textStart) ? 5 : int.Parse(textStart);
int end = String.IsNullOrEmpty(textEnd) ? rowcount : int.Parse(textEnd);
for (int i = start; i <= end; i++)
{
//获取当前行的表达式，坐标为：（i，G）
String express = "0";
if (!string.IsNullOrEmpty(ws.Cells[i, 7].Text))
{
express = "G" + i + "=" + ws.Cells[i, 7].Text;
}
String unit = ws.Cells[i, 14].Text;
//获取公式
String gongshi = ws.Cells[i, 13].Formula;
for (int j = 2; j <= colcount; j++)
{
// 列7 ：表达式 列8：单位
if (j == 2 || j == 3 || j == 4 || j == 5 || j == 9 || j == 10)
{
String cName = ws.Cells[i, j].Text;
String id = System.Guid.NewGuid().ToString("N");
if (i == start)
{
//第一行一定是有值的，所以不用判断是否为空
designpackge designpackge = new designpackge();
//designpackge2Id = System.Guid.NewGuid().ToString("N");
designpackge.Id = id;
designpackge.Houseplanid = houseplanid;
designpackge.Nametype = "room";
designpackge.Cname = cName;
designpackge.Unit = unit;
//designpackge.Parent_id = "-1";
//填入公式express
if (j == 3 || j == 4 || j == 5 || j == 9)
{
designpackge.Expression = express;
}
else if (j == 10)
{
if (string.IsNullOrEmpty(gongshi))
{
designpackge.Expression = "0";
}
else
{
designpackge.Expression = "H" + i + gongshi;
}
//填入物料编码
designpackge.Materialid = ws.Cells[i, 11].Text;
}
else
{
designpackge.Expression = "0";
}
String Parent_id = "";
if (j == 3 || j == 4 || j == 5 || j == 10)
{
Parent_id = dic[i + "-" + (j - 1)].Id;
}
else if (j == 9)
{
Parent_id = dic[i + "-" + 5].Id;
}
else if (j == 2)
{
Parent_id = "-1";
}
designpackge.Parent_id = Parent_id;
insertinto(designpackge);
//记录数据
bamBean bean = new bamBean();
bean.Id = id;
bean.Name = cName;
bean.Parentid = Parent_id;
dic.Add(i + "-" + j, bean);
}
else if (i > start)
{
//大于第一层
if (!string.IsNullOrEmpty(cName))
{
designpackge designpackge = new designpackge();
//designpackge2Id = System.Guid.NewGuid().ToString("N");
designpackge.Id = id;
designpackge.Houseplanid = houseplanid;
designpackge.Nametype = "room";
designpackge.Cname = cName;
//designpackge.Expression = express;
designpackge.Unit = unit;
//designpackge.Parent_id = "-1";
//填入公式express
if (j == 5)
{
designpackge.Expression = express;
}
else if (j == 10)
{
if (string.IsNullOrEmpty(gongshi))
{
designpackge.Expression = "0";
}
else
{
designpackge.Expression = "H" + i + gongshi;
}
//填入物料编码
designpackge.Materialid = ws.Cells[i, 11].Text;
}
else
{
designpackge.Expression = "0";
}
String Parent_id = "";
if (j == 3 || j == 4 || j == 5 || j == 10)
{
if (String.IsNullOrEmpty(ws.Cells[i, j - 1].Text))
{
Parent_id = dic[(i - 1) + "-" + j].Parentid;
}
else
{
Parent_id = dic[i + "-" + (j - 1)].Id;
}
}
else if (j == 9)
{
if (String.IsNullOrEmpty(ws.Cells[i, j - 1].Text))
{
Parent_id = dic[(i - 1) + "-" + j].Parentid;
}
else
{
Parent_id = dic[i + "-" + 5].Id;
}
}
else if (j == 2)
{
Parent_id = "-1";
}
designpackge.Parent_id = Parent_id;
insertinto(designpackge);
//记录数据
bamBean bean = new bamBean();
bean.Id = id;
bean.Name = cName;
bean.Parentid = Parent_id;
dic.Add(i + "-" + j, bean);
}
else
{
// 说明这里只需要存dictionary就行了
//读取自己的name以及父级节点
bamBean bean = new bamBean();
bean.Id = id;
//bean.Name = ws.Cells[i - 1, j].Text;
bean.Name = dic[(i - 1) + "-" + j].Name;
if (j == 3 || j == 4 || j == 5 || j == 9 || j == 10)
{
bean.Parentid = dic[(i - 1) + "-" + j].Parentid;
}
else if (j == 2)
{
bean.Parentid = "-1";
}
dic.Add(i + "-" + j, bean);
}
}
}
}
}
//关闭窗口
this.Close();
MessageBox.Show("导入成功！");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
