将Execl表格的数据导入数据库

/// 导入台区
/// </summary>
/// <param name="savePath"></param>
/// <param name="msg"></param>
/// <returns></returns>
public bool Upload(string savePath, out string msg)
{
msg = "上传成功";

DataService.Model.yq_biaoji_excel model = new DataService.Model.yq_biaoji_excel();
//List<List<string>> data = new List<List<string>>();

using (FileStream stream = new FileStream(savePath, FileMode.Open, FileAccess.Read))
{
IWorkbook workbook = new XSSFWorkbook(stream);

var sheetNames = "";
if (savePath.Contains("台区1"))
{
model.tq_code = "01";
}
else if (savePath.Contains("台区2"))
{
model.tq_code = "02";
}
else if (savePath.Contains("台区3"))
{
model.tq_code = "03";
}
else if (savePath.Contains("台区4"))
{
model.tq_code = "04";
}
else if (savePath.Contains("台区5"))
{
model.tq_code = "05";
}
var query = new StringBuilder(" 1 = 1 ");

query.AppendFormat(" AND tq_code LIKE '%{0}%' ", model.tq_code.Trim());

var list = bll_biaoji_excel.GetModelList(query.ToString());

if (list.Count != 0)
{
foreach (var item in list)
{
bll_biaoji_excel.Delete(item.id);
}

for (int i = 0; i < workbook.NumberOfSheets; i++)
{

ISheet sheet = workbook.GetSheetAt(i);

int startRow = -1; // 起始行
int endRow = -1; // 结束行
startRow = -1;
endRow = -1;

for (int rowIndex = 0; rowIndex <= sheet.LastRowNum; rowIndex++)
{
IRow row = sheet.GetRow(rowIndex);

if (row != null)
{
ICell cell = row.GetCell(0);
string cellValue = "";

if (cell != null)
{
switch (cell.CellType)
{
case CellType.Numeric:
cellValue = cell.NumericCellValue.ToString();
break;
case CellType.String:
cellValue = cell.StringCellValue;
break;
case CellType.Boolean:
cellValue = cell.BooleanCellValue.ToString();
break;
case CellType.Formula:
cellValue = cell.CellFormula;
break;
default:
cellValue = "";
break;
}
}
Console.WriteLine("单元格值：" + cellValue);
//if (cellValue == a1 || cellValue == a2 || cellValue == a3 || cellValue == a4 || cellValue == a5 || cellValue == a6 || cellValue == a7 || cellValue == a8 || cellValue == a9 || cellValue == a10 || cellValue == a11 || cellValue == a12
// //|| cellValue == a12 || cellValue == a13 || cellValue == a14 || cellValue == a15 || cellValue == a16 || cellValue == a17 || cellValue == a18
// )
if (cellValue.Contains("&"))
{
startRow = rowIndex + 1;
model.bj_module = cellValue.Replace("&", "");
endRow = rowIndex + 1; // 初始化为相同的值
if (cellValue == "&日冻结-日冻结示值")
{
startRow = startRow + 1;
}
if (cellValue == "&日冻结-曲线数据")
{
startRow = startRow + 1;
}
// 寻找结束行
for (int nextRowIndex = rowIndex + 1; nextRowIndex <= sheet.LastRowNum; nextRowIndex++)
{
IRow nextRow = sheet.GetRow(nextRowIndex);
if (nextRow != null)
{

ICell nextCell = nextRow.GetCell(0);

if (nextCell != null)
{
if (nextCell.IsMergedCell)
{
endRow = nextRowIndex + 2;
}
else
{
string nextCellValue = nextCell.CellType == CellType.Numeric ? nextCell.NumericCellValue.ToString() : nextCell.StringCellValue;
if (nextCellValue != "*")
{
endRow = nextRowIndex + 1;
}
else
{
break;
}
}

}
}
}

sheetNames = sheet.SheetName;
model.biaoji = sheetNames;
model.isfirst = "1";
model.id = BasePage.GetNewId();
model.start_index = startRow;
model.end_index = endRow - 1;
Console.WriteLine("起始行：" + startRow);
Console.WriteLine("结束行：" + endRow);
bll_biaoji_excel.Add(model);
}

}

}
}
}
else
{
for (int i = 0; i < workbook.NumberOfSheets; i++)
{

ISheet sheet = workbook.GetSheetAt(i);

int startRow = -1; // 起始行
int endRow = -1; // 结束行
startRow = -1;
endRow = -1;

for (int rowIndex = 0; rowIndex <= sheet.LastRowNum; rowIndex++)
{
IRow row = sheet.GetRow(rowIndex);

if (row != null)
{
ICell cell = row.GetCell(0);
string cellValue = "";

if (cell != null)
{
switch (cell.CellType)
{
case CellType.Numeric:
cellValue = cell.NumericCellValue.ToString();
break;
case CellType.String:
cellValue = cell.StringCellValue;
break;
case CellType.Boolean:
cellValue = cell.BooleanCellValue.ToString();
break;
case CellType.Formula:
cellValue = cell.CellFormula;
break;
default:
cellValue = "";
break;
}
}
Console.WriteLine("单元格值：" + cellValue);
//if (cellValue == a1 || cellValue == a2 || cellValue == a3 || cellValue == a4 || cellValue == a5 || cellValue == a6 || cellValue == a7 || cellValue == a8 || cellValue == a9 || cellValue == a10 || cellValue == a11 || cellValue == a12
// //|| cellValue == a12 || cellValue == a13 || cellValue == a14 || cellValue == a15 || cellValue == a16 || cellValue == a17 || cellValue == a18
// )
if (cellValue.Contains("&"))
{
startRow = rowIndex + 1;
model.bj_module = cellValue.Replace("&", "");
endRow = rowIndex + 1; // 初始化为相同的值
if (cellValue == "&日冻结-日冻结示值")
{
startRow = startRow + 1;
}
if (cellValue == "&日冻结-曲线数据")
{
startRow = startRow + 1;
}
// 寻找结束行
for (int nextRowIndex = rowIndex + 1; nextRowIndex <= sheet.LastRowNum; nextRowIndex++)
{
IRow nextRow = sheet.GetRow(nextRowIndex);
if (nextRow != null)
{

ICell nextCell = nextRow.GetCell(0);

if (nextCell != null)
{
if (nextCell.IsMergedCell)
{
endRow = nextRowIndex + 2;
}
else
{
string nextCellValue = nextCell.CellType == CellType.Numeric ? nextCell.NumericCellValue.ToString() : nextCell.StringCellValue;
if (nextCellValue != "*")
{
endRow = nextRowIndex + 1;
}
else
{
break;
}
}

}
}
}

sheetNames = sheet.SheetName;
model.biaoji = sheetNames;
model.isfirst = "1";
model.id = BasePage.GetNewId();
model.start_index = startRow;
model.end_index = endRow - 1;
Console.WriteLine("起始行：" + startRow);
Console.WriteLine("结束行：" + endRow);
bll_biaoji_excel.Add(model);
}

}
}

}
}
}

return true;
}