private ExcelField GetField(String fielNameExcel)
{
ExcelField retVal = null;
foreach (ExcelField f in fields)
{
if (f.FielNameExcel.Equals(fielNameExcel))
{
retVal = f;
break;
}
}
return(retVal);
}
public void ObjToRow(object obj, IRow row)
{
for (int i = 0; i < fields.Count; i++)
{
ExcelField ef = fields[i];
try{
PropertyInfo pi = obj.GetType().GetProperty(ef.FieldNameCls);
object val = pi.GetValue(obj, null);
string valStr = "";
if (val != null)
{
valStr = val.ToString();
}
row.CreateCell(i).SetCellValue(valStr);
}catch (Exception e) {
throw e;
}
}
}
//public IList<object> List()
//{
// return List(type);
//}
public IList <object> List(Type type)
{
IList <object> retVal = new List <object>();
try {
Stream input = new FileStream(filePath, FileMode.Open); //建立输入流
IWorkbook wb = null;
//根据文件格式(2003或者2007)来初始化
if (isE2007)
{
wb = new XSSFWorkbook(input);
}
else
{
wb = new HSSFWorkbook(input);
}
ISheet sheet = wb.GetSheet(table);//查找表名
if (sheet != null)
{
for (int i = 0; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i); //获得行数据
//如果当前行小于开始行则跳过
if (i < startLine)
{
continue;
}
else
{
//字段行
if (i == startLine)
{
fieldsName = new ExcelField[row.PhysicalNumberOfCells];
for (int j = 0; j < row.LastCellNum; j++)
{
ICell cell = row.GetCell(j);
ExcelField f = GetField(cell.StringCellValue);
fieldsName[j] = f;
}
}
//数据行
else if (i > startLine)
{
object newT = type.Assembly.CreateInstance(type.FullName);
for (int j = 0; j < row.LastCellNum; j++)
{
ICell cell = row.GetCell(j);
SetValue(newT, fieldsName[j].FieldNameCls, cell);
//给属性赋值
//newT.Add(fieldsName[j].FieldNameCls, val);
}
retVal.Add(newT);
}
}
}
}
else
{
throw new Exception("Table is not exists");
}
} catch (Exception ex) {
throw ex;
}
return(retVal);
}
public ActionResult ImportTest()
{
IList<ExcelField> fields=new List<ExcelField>();
ExcelField ef=null;
ef=new ExcelField("Code","代码");
fields.Add(ef);
ef = new ExcelField("Name", "名字");
fields.Add(ef);
ef = new ExcelField("TestExcel", "测试Excel");
fields.Add(ef);
string url=Server.MapPath("/") + @"Files\Temp\"+"1.xlsx";
//ImportExcel ie = new ImportExcel(url,"abc",fields);
ImportExcel ie = new ImportExcel(url,typeof(Department));
//IList<Department> vals = ie.List<Department>();
//foreach (Department dic in vals)
//{
// string code = dic.Code;
// string name = dic.Name;
// string testExcel = dic.TestExcel;
//}
return null;
}
public ActionResult DownLoadDept()
{
string fileName = Server.MapPath("/") + @"Files\Temp\"+"1.xlsx";
IList<ExcelField> fields=new List<ExcelField>();
ExcelField ef=null;
ef=new ExcelField("Code","代码");
fields.Add(ef);
ef = new ExcelField("Name", "名字");
fields.Add(ef);
ef = new ExcelField("TestExcel", "测试Excel");
fields.Add(ef);
TbBaseOper<Department> departmentOper = new TbBaseOper<Department>(HibernateOper, typeof(Department));
IList<Department> list=departmentOper.Get();
//ExportExcel ee = new ExportExcel(fileName, "abc", fields);
ExportExcel ee = new ExportExcel(fileName,typeof(Department));
ee.Save(list);
//返回文件
return File(fileName, "xlsx", "1.xlsx");
}
