一张excel表格中包含4张表的数据，分别是Area地区表、PlantUnit机组表、Device设备表、Spares备品表
他们的关系分别为：地区表包含机组、机组包含设备、设备包含备品，依次层叠的关系。
首先使用NPOI解析excel表格数据
表格数据结构为：
解析代码块：
/// <summary>
/// excel文件导入
/// </summary>
public class ExcelImportService
{
public DataTable ExcelImport(HttpPostedFileBase fileBase)
{
//HttpPostedFileBase fileBase = Request.Files[0];
if (fileBase == null || fileBase.ContentLength <= 0)
{
throw new Exception("只能上传Excel文件！");
}
try
{
//获取文件后缀名
string FinName = Path.GetExtension(fileBase.FileName);
//获取文件内容
Stream streamFile = fileBase.InputStream;
DataTable dt = new DataTable();
if (FinName != ".xls" && FinName != ".xlsx")
{
throw new Exception("只能上传Excel文件！");
}
else
{
if (FinName == ".xls")
{
//创建一个webbook，对应一个Excel文件(用于xls文件导入类)
HSSFWorkbook book = new HSSFWorkbook(streamFile);
dt = HSSFExcel(dt, book);
}
else
{
XSSFWorkbook book = new XSSFWorkbook(streamFile);
dt = XSSFExcel(dt, book);
}
return dt;
}
}
catch (Exception ex)
{
throw new Exception("导入失败！ " + ex.Message);
}
}
/// <summary>
/// .xls文件导入
/// </summary>
/// <param name="dt"></param>
/// <param name="book"></param>
/// <returns></returns>
public DataTable HSSFExcel(DataTable dt, HSSFWorkbook book)
{
// 在webbook中添加一个sheet,对应Excel文件中的sheet,读取当前表数据，索引是0
ISheet sheet = book.GetSheetAt(0);
//读取行数据
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
for (int i = 0; i < (sheet.GetRow(0).LastCellNum); i++)
{
dt.Columns.Add(sheet.GetRow(0).Cells[i].ToString());
}
//读取每行,从第二行起
for (int r = 1; r <= sheet.LastRowNum; r++)
{
bool result = false;
DataRow dr = dt.NewRow();
//获取当前行
IRow row = sheet.GetRow(r);
//读取每列
for (int j = 0; j < row.Cells.Count; j++)
{
ICell cell = row.GetCell(j); //一个单元格
if (cell.IsMergedCell && r > 1) //检测列的单元格是否合并
{
//dr[j] = dt.Rows[r - 2][j];
var cellValue = GetCellValue(cell);
if (string.IsNullOrEmpty(cellValue))
{
if (j > 0)
{
if (dr[j - 1].ToString() != dt.Rows[r - 2][j - 1].ToString())
{
dr[j] = null;
}
else
{
dr[j] = dt.Rows[r - 2][j];
}
}
else
{
dr[j] = dt.Rows[r - 2][j];
}
}
else
{
dr[j] = cellValue; //获取单元格的值
if (string.IsNullOrWhiteSpace(dr[j].ToString()) && j > 0)
{
dr[j] = dr[j - 1];
}
}
}
else
{
//控制空列的值是否等于上一列的值
dr[j] = GetCellValue(cell); //获取单元格的值
if (string.IsNullOrWhiteSpace(dr[j].ToString()) && j > 0)
{
//dr[j] = dr[j - 1];
}
}
if (dr[j].ToString() != "") //全为空则不取
{
result = true;
}
}
if (result == true)
{
dt.Rows.Add(dr); //把每行追加到DataTable
}
}
return dt;
}
/// <summary>
/// .xlsx文件导入
/// </summary>
/// <param name="dt"></param>
/// <param name="book"></param>
/// <returns></returns>
public DataTable XSSFExcel(DataTable dt, XSSFWorkbook book)
{
ISheet sheet = book.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
for (int i = 0; i < (sheet.GetRow(0).LastCellNum); i++)
{
dt.Columns.Add(sheet.GetRow(0).Cells[i].ToString());
}
//读取每行,从第二行起
for (int r = 1; r <= sheet.LastRowNum; r++)
{
bool result = false;
DataRow dr = dt.NewRow();
//获取当前行
IRow row = sheet.GetRow(r);
//读取每列
for (int j = 0; j < row.Cells.Count; j++)
{
ICell cell = row.GetCell(j); //一个单元格
if (cell.IsMergedCell && r > 1) //检测列的单元格是否合并
{
//dr[j] = dt.Rows[r - 2][j];
var cellValue = GetCellValue(cell);
if (string.IsNullOrEmpty(cellValue))
{
if (j > 0)
{
if (dr[j - 1].ToString() != dt.Rows[r - 2][j - 1].ToString())
{
dr[j] = null;
}
else
{
dr[j] = dt.Rows[r - 2][j];
}
}
else
{
dr[j] = dt.Rows[r - 2][j];
}
}
else
{
dr[j] = cellValue; //获取单元格的值
if (string.IsNullOrWhiteSpace(dr[j].ToString()) && j > 0)
{
dr[j] = dr[j - 1];
}
}
}
else
{
//控制空列的值是否等于上一列的值
dr[j] = GetCellValue(cell); //获取单元格的值
if (string.IsNullOrWhiteSpace(dr[j].ToString()) && j > 0)
{
//dr[j] = dr[j - 1];
}
}
if (dr[j].ToString() != "") //全为空则不取
{
result = true;
}
}
if (result == true)
{
dt.Rows.Add(dr); //把每行追加到DataTable
}
}
//dt.Rows.RemoveAt(0);
return dt;
}
/// <summary>
/// 对单元格进行判断取值
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static string GetCellValue(ICell cell)
{
if (cell == null)
return string.Empty;
switch (cell.CellType)
{
case CellType.Blank: //空数据类型 这里类型注意一下，不同版本NPOI大小写可能不一样,有的版本是Blank（首字母大写)
return string.Empty;
case CellType.Boolean: //bool类型
return cell.BooleanCellValue.ToString();
case CellType.Error:
return cell.ErrorCellValue.ToString();
case CellType.Numeric: //数字类型
if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
{
return cell.DateCellValue.ToString();
}
else //其它数字
{
return cell.NumericCellValue.ToString();
}
case CellType.Unknown: //无法识别类型
default: //默认类型
return cell.ToString();//
case CellType.String: //string 类型
{
if (cell.IsMergedCell) { }
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();
}
}
}
}解析出的datatable结果为：
后续操作datatable存入数据库
public void Import(HttpPostedFileBase fileBase)
{
var dt = new ExcelImportService().ExcelImport(fileBase);
var num = 0;
var areaRow = new Area();
var plantUnitRow = new PlantUnit();
var deviceRow = new Device();
Area area = new Area();
PlantUnit plantUnit = new PlantUnit();
Device device = new Device();
Spares spares = new Spares();
//行
for (int i = 1; i < dt.Rows.Count; i++)
{
if (!dt.Rows[i-1][0].ToString().IsNullOrEmpty()&& dt.Rows[i - 1][0].ToString()!= dt.Rows[i][0].ToString())
{
area = new Area()
{
OnCreated = DateTime.Now,
OnLastUpdated = DateTime.Now
};
area.AreaName = dt.Rows[i][0].ToString();
_dao.Add(area).Commit();
areaRow = _dao.QueryFirst<Area>(t => t.Id == area.Id);
}
if (!dt.Rows[i - 1][1].ToString().IsNullOrEmpty() && dt.Rows[i - 1][1].ToString() != dt.Rows[i][1].ToString())
{
plantUnit = new PlantUnit()
{
OnCreated = DateTime.Now,
OnLastUpdated = DateTime.Now,
PlantUnitCode = dt.Rows[i][1].ToString(),
};
_dao.Add(plantUnit).Commit();
if (areaRow.PlantUnit==null)
{
areaRow.PlantUnit = new List<PlantUnit>();
}
areaRow.PlantUnit.Add(plantUnit);
_dao.Update(areaRow).Commit();
plantUnitRow = _dao.QueryFirst<PlantUnit>(t => t.Id == plantUnit.Id);
}
if (!dt.Rows[i - 1][2].ToString().IsNullOrEmpty() && dt.Rows[i - 1][2].ToString()!= dt.Rows[i][2].ToString())
{
//加入判断如果数据库已有相同的数据则不添加
var deviceName = dt.Rows[i][2].ToString();
var brand = dt.Rows[i][3].ToString();
var modelNumber = dt.Rows[i][4].ToString();
var demo = _dao.QueryFirst<Device>(t =>
t.DeviceName == deviceName && t.Brand == brand &&
t.ModelNumber == modelNumber);
if (demo == null)
{
device = new Device()
{
DeviceName = dt.Rows[i][2].ToString(),
Brand = dt.Rows[i][3].ToString(),
ModelNumber = dt.Rows[i][4].ToString(),
OnCreated = DateTime.Now,
OnLastUpdated = DateTime.Now,
};
_dao.Add(device).Commit();
}
else
{
device = demo;
}
if (plantUnitRow.Device == null)
{
plantUnitRow.Device = new List<Device>();
}
plantUnitRow.Device.Add(device);
_dao.Update(plantUnitRow).Commit();
deviceRow = _dao.QueryFirst<Device>(t => t.Id == device.Id);
}
if (!dt.Rows[i - 1][5].ToString().IsNullOrEmpty())
{
//加入判断如果数据库已有相同的数据则不添加
var sparesName = dt.Rows[i][5].ToString();
var brand = dt.Rows[i][8].ToString();
var modelNumber = dt.Rows[i][6].ToString();
var demo = _dao.QueryFirst<Spares>(t =>
t.SparesName == sparesName && t.Brand == brand &&
t.ModelNumber == modelNumber);
if (demo == null)
{
spares = new Spares()
{
OnCreated = DateTime.Now,
OnLastUpdated = DateTime.Now,
SparesName = dt.Rows[i][5].ToString(),
ModelNumber = dt.Rows[i][6].ToString(),
SparesCode = dt.Rows[i][7].ToString(),
Brand = dt.Rows[i][8].ToString()
};
_dao.Add(spares).Commit();
}
else
{
spares = demo;
}
if (deviceRow.Spares == null)
{
deviceRow.Spares = new List<Spares>();
}
deviceRow.Spares.Add(spares);
_dao.Update(deviceRow).Commit();
}
}
}以上为我自己写代码时遇到的业务场景处理记录，本人新手一枚，只为做个简单记录！！
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
