Aspose------导入Excel

时间:2021-03-21 03:28:04

代码:

public List<T> ImportExcelToList<T>()
{
HttpContext context = HttpContext.Current;
if (context.Request.Files.AllKeys.Length > )
{
var key = context.Request.Files.AllKeys[];
var file = context.Request.Files[key];
var folderName = Path.GetFileNameWithoutExtension(file.FileName);
var fileExtension = Path.GetExtension(file.FileName); Workbook book = new Workbook(file.InputStream);
Worksheet sheet = book.Worksheets[]; Cells cells = sheet.Cells;
var da = cells[, ].Value;
var dataTable = cells.ExportDataTable(, , cells.MaxDataRow + , cells.MaxColumn, true);
var datalist = GetList<T>(dataTable); //拆分单元格后需要给Component为空的单元格赋值
Type type = typeof(T);
PropertyInfo[] property = type.GetProperties();
for (var i = ; i < datalist.Count(); i++)
{
var li = datalist[i];
foreach (var pi in property)
{
if (pi.Name == "Component")
{
var value = pi.GetValue(li);
if (value == null)
{ var v = pi.GetValue(datalist[i - ]);
pi.SetValue(li, v);
}
}
}
}
return datalist;
}
return null;
} public List<T> GetList<T>(DataTable table)
{
List<T> list = new List<T>();
T t = default(T);
PropertyInfo[] propertypes = null;
string tempName = string.Empty;
foreach (DataRow row in table.Rows)
{
t = Activator.CreateInstance<T>();
propertypes = t.GetType().GetProperties();
foreach (PropertyInfo pro in propertypes)
{
tempName = pro.Name;
if (table.Columns.Contains(tempName))
{
object value = row[tempName];
if (!value.ToString().Equals(""))
{
if (pro.Name == "NewDuct" || pro.Name == "ExistingDuct" || pro.Name == "NewAerial" || pro.Name == "ExistingAerial")
{
var v = ChangePropertyType(pro.PropertyType,value);
pro.SetValue(t, v, null);
continue;
}
pro.SetValue(t, value, null);
}
}
}
list.Add(t);
}
return list.Count == ? null : list;
} private object ChangePropertyType(Type type, object value)
{
object data;
if (type.FullName.ToLower().Contains("decimal"))
{
data = ;
data = Convert.ToDecimal(value);
}
else
{
data = "";
data = Convert.ToString(value);
}
return data;
}

转成树结构

private List<ProjectItem> AddItemToTree(List<MergeItem> list)
{
var datalist = new List<ProjectItem>();
var component = "";
PropertyInfo[] property = typeof(MergeItem).GetProperties(); foreach (var li in list)
{
var item = new ProjectItem(); if (string.IsNullOrEmpty(li.SubComponent))
{
component = li.Component;
item.SubComponent = null;
item.ItemLevel = "Level1";
item.Unit = null;
}
else
{
item.SubComponent = li.Component;
item.ItemLevel = "Level2";
item.Unit = null; //建第三级
foreach (var pi in property)
{
var scenario = "";
if (pi.Name == "NewDuct")
{
scenario = "New Duct";
}
else if (pi.Name == "ExistingDuct")
{
scenario = "Existing Duct";
}
else if (pi.Name == "NewAerial")
{
scenario = "New Aerial";
}
else if (pi.Name == "ExistingAerial")
{
scenario = "Existing Aerial";
}
else {
continue;
} var value = pi.GetValue(li);
if (value != null)
{
var child = new ProjectItem();
child.Component = component;
child.SubComponent = item.SubComponent;
child.Description = li.SubComponent;
child.Unit = li.Unit;
child.Scenario = scenario;
child.UnitCost = Convert.ToDecimal(value);
child.Remark = li.Remark;
child.FillInstruct = li.FillInstruct;
child.ItemLevel = "Level3";
datalist.Add(child);
}
}
} item.Component = component;
item.Description = li.Component;
item.Scenario = null;
item.UnitCost = null;
item.Remark = null;
item.FillInstruct = null; var isExist = datalist.Any(p => p.Component == item.Component && p.SubComponent == item.SubComponent &&
p.Description == item.Description && p.ItemLevel == "Level2");
if (!isExist)
{
datalist.Add(item);
}
} return datalist;
}