excel 导入 与 导出
Excel导入
public ActionResult Excel(HttpPostedFileBase file)
{
HttpPostedFileBase files = file;//接收客户端传递过来的数据.
try
{
if (files == null)
{
return Content("请选择上传的Excel文件");
}
else
{
file.SaveAs(Server.MapPath("~/UploadFile/Excel/" + file.FileName));
//对文件的格式判断，此处省略
AltasEntities db = new AltasEntities();//EF上下文对象
Stream inputStream = files.InputStream;
IWorkbook workbook;
string fileExt = Path.GetExtension(files.FileName);
if (fileExt == ".xls")
{
workbook = new HSSFWorkbook(inputStream);
}
else if (fileExt == ".xlsx")
{
workbook = new XSSFWorkbook(inputStream);
}
else
{
return null;
}
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
int rowCount = sheet.LastRowNum + 1;
for (int i = (sheet.FirstRowNum + 1); i <= rowCount - 1; i++)
{
IRow row = sheet.GetRow(i);
Equiemints model = new Equiemints();
if (row != null)
{
if (row.GetCell(0) != null)
{
model.EquiemintId = GetCellValue(row.GetCell(0)).ToUpper();
}
if (row.GetCell(1) != null)
{
model.Company = GetCellValue(row.GetCell(1));
}
if (row.GetCell(2) != null)
{
model.ModelsType = GetCellValue(row.GetCell(2));
}
if (row.GetCell(3) != null)
{
model.OpenCreatetime = DateTime.Parse(GetCellValue(row.GetCell(3)));
}
if (row.GetCell(4) != null)
{
model.ZBType = GetCellValue(row.GetCell(4));
}
if (row.GetCell(5) != null)
{
model.ZBEndCreatetime = DateTime.Parse(GetCellValue(row.GetCell(5)));
//model.SalesName = GetCellValue(row.GetCell(5));
}
if (row.GetCell(6) != null)
{
model.a1 = GetCellValue(row.GetCell(6));
}
if (row.GetCell(7) != null)
{
model.a2 = GetCellValue(row.GetCell(7));
}
if (row.GetCell(8) != null)
{
model.a3 = GetCellValue(row.GetCell(8));
}
if (row.GetCell(9) != null)
{
model.a4 = GetCellValue(row.GetCell(9));
}
if (row.GetCell(10) != null)
{
model.a5 = GetCellValue(row.GetCell(10));
}
}
db.Equiemints.Add(model);
}
db.SaveChanges();
return Content("<script>alert('导入成功！',location.href='/Equiemints/Index');</script>");
}
}
catch (Exception)
{
return Content("<script>alert('导入失败！');</script>");
}
}
/// <summary>
/// 根据Excel列类型获取列的值
/// </summary>
/// <param name="cell">Excel列</param>
/// <returns></returns>
private static string GetCellValue(ICell cell)
{
if (cell == null)
return string.Empty;
switch (cell.CellType)
{
case CellType.Blank:
return string.Empty;
case CellType.Boolean:
return cell.BooleanCellValue.ToString();
case CellType.Error:
return cell.ErrorCellValue.ToString();
case CellType.Numeric:
case CellType.Unknown:
default:
return cell.ToString();
case CellType.String:
return cell.StringCellValue;
case CellType.Formula:
try
{
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
e.EvaluateInCell(cell);
return cell.ToString();
}
catch
{
return cell.NumericCellValue.ToString();
}
}
}
Excel导出
public ActionResult DcExcel(List<int> id)
{
var db = new AltasEntities();
if (id == null)
{
id = db.Equiemints.Select(s => s.Id).ToList();
}
string filename = DateTime.Now.ToString("yyyyMMddHHmmss") + ".csv";
string path = Server.MapPath("/UploadFile/" + filename);
FileInfo fi = new FileInfo(path);
using (StreamWriter sw = fi.CreateText())
{
sw.WriteLine("设备序号,所属公司,机型,开始时间,质保类型,结束时间,服务销售,是否绑定,字段1,字段2,字段3");
foreach (int i in id)
{
//找到要导出的数据
var saa = db.Equiemints.Find(i);
if (saa.a1==null)
{
saa.a1 = "无";
}
if (saa.a2 == null)
{
saa.a2 = "无";
}
if (saa.a3 == null)
{
saa.a3 = "无";
}
sw.WriteLine(
saa.EquiemintId.ToString()
+ "," + saa.Company.ToString()
+ "," + saa.ModelsType.ToString()
+ "," + saa.OpenCreatetime.ToString()
+ "," + saa.ZBType.ToString()
+ "," + saa.ZBEndCreatetime.ToString()
+ "," + saa.uname.ToString()
+ "," + saa.bangname.ToString()
+ "," + saa.a1.ToString()
+ "," + saa.a2.ToString()
+ "," + saa.a3.ToString()
);
}
sw.Close();
}
var name = Path.GetFileName(path);
return File(path, "application/x-zip-compressed", name);
}
