private static void InsertCableLayingDetails(CableLayingDetails detailse, int rownum = 1)
{
var row = workbook.GetSheetAt(0).CreateRow(rownum);
row.CreateCell(0).SetCellValue(rownum);
row.CreateCell(1).SetCellValue("");
row.CreateCell(2).SetCellValue(detailse.CableCode);
row.CreateCell(8).SetCellValue(detailse.Start.RoomCode);
row.CreateCell(9).SetCellValue(detailse.Start.SystemName);
row.CreateCell(10).SetCellValue(detailse.Start.EquitName);
row.CreateCell(11).SetCellValue(detailse.Start.EquitCode);
row.CreateCell(12).SetCellValue(detailse.End.RoomCode);
row.CreateCell(13).SetCellValue(detailse.End.SystemName);
row.CreateCell(14).SetCellValue(detailse.End.EquitName);
row.CreateCell(15).SetCellValue(detailse.End.EquitCode);
row.CreateCell(16).SetCellValue(detailse.SafePassage);
row.CreateCell(17).SetCellValue(detailse.PressureVesselCode);
row.CreateCell(18).SetCellValue(detailse.CabinCode);
row.CreateCell(19).SetCellValue(detailse.PipeCode);
row.CreateCell(20).SetCellValue(detailse.Version);
row.CreateCell(21).SetCellValue(detailse.Specification);
row.CreateCell(22).SetCellValue(detailse.Length);
row.CreateCell(23).SetCellValue(detailse.PipeSpecification);
row.CreateCell(24).SetCellValue(detailse.PipeLength);
row.CreateCell(25).SetCellValue(detailse.CablePath);
row.CreateCell(26).SetCellValue(detailse.Other);
}
private static void InsertCableLayingeCollect(CableLayingDetails detailse, int rownum = 1)
{
var row = workbook.GetSheetAt(0).CreateRow(rownum - 1);
row.CreateCell(0).SetCellValue((rownum - 2) / 2);
row.CreateCell(1).SetCellValue(detailse.CableCode);
row.CreateCell(2).SetCellValue(detailse.Start.RoomCode);
row.CreateCell(3).SetCellValue(detailse.Start.SystemName);
row.CreateCell(4).SetCellValue(detailse.Start.EquitName);
row.CreateCell(5).SetCellValue(detailse.Start.EquitCode);
row.CreateCell(6).SetCellValue(detailse.End.RoomCode);
row.CreateCell(7).SetCellValue(detailse.End.SystemName);
row.CreateCell(8).SetCellValue(detailse.End.EquitName);
row.CreateCell(9).SetCellValue(detailse.End.EquitCode);
row.CreateCell(10).SetCellValue(detailse.SafePassage);
row.CreateCell(11).SetCellValue(detailse.PressureVesselCode);
row.CreateCell(12).SetCellValue(detailse.CabinCode);
row.CreateCell(13).SetCellValue(detailse.PipeCode);
row.CreateCell(14).SetCellValue(detailse.Version);
row.CreateCell(15).SetCellValue(detailse.Specification);
row.CreateCell(16).SetCellValue(detailse.Length);
row.CreateCell(17).SetCellValue(detailse.PipeSpecification);
row.CreateCell(18).SetCellValue(detailse.PipeLength);
row.CreateCell(19).SetCellValue(detailse.Other);
var row1 = workbook.GetSheetAt(0).CreateRow(rownum);
for (int i = 0; i < 20; i++)
{
row1.CreateCell(i);
}
row1.CreateCell(2).SetCellValue(detailse.CablePath);
workbook.GetSheetAt(0).AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rownum - 1, rownum, 0, 0));
workbook.GetSheetAt(0).AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rownum - 1, rownum, 1, 1));
workbook.GetSheetAt(0).AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rownum, rownum, 2, 19));
}
/// <summary>
/// 导入电缆设计清单
/// </summary>
/// <param name="numberNo">流水号</param>
/// <param name="path">文件路径</param>
public void InsertCableLayingDetails(string numberNo, string path)
{
int error = 0;
string col = "";
List <string> messagel = new List <string>();
//根据指定路径读取文件
using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read))
{
IWorkbook wb;
if (path.IndexOf(".xlsx") > 0)
{//2007版本
wb = new XSSFWorkbook(fs);
}
else if (path.IndexOf(".xls") > 0)
{//2003版本
wb = new HSSFWorkbook(fs);
}
else
{
//根据文件流创建excel数据结构
wb = WorkbookFactory.Create(fs);
}
// Workbook wb = new Workbook(path);
var sheet = wb.GetSheetAt(0);
int firstRow = 3;
for (int i = 3; i < sheet.LastRowNum; i++)
{
var v = sheet.GetRow(i);
if (sheet.GetRow(i).GetCell(0).ToString() == "1")
{
firstRow = i;
break;
}
}
if (wb.NumberOfSheets != 1)
{
messagel.Add(" Sheet 表不为一个 ");
}
for (int i = firstRow; i < sheet.LastRowNum;)
{
try
{
error = i + 1;
IRow row = sheet.GetRow(i);
CableLayingDetails entity = new CableLayingDetails();
col = "B";
entity.CableCode = (row.GetCell(1)).ToString().Replace(" ", "").Trim();
col = "C";
entity.Start.RoomCode = (row.GetCell(2)).ToString().Trim();
col = "D";
entity.Start.SystemName = (row.GetCell(3)).ToString().Trim();
col = "E";
entity.Start.EquitName = (row.GetCell(4)).ToString().Trim();
col = "F";
entity.Start.EquitCode = (row.GetCell(5)).ToString().Trim();
col = "G";
entity.End.RoomCode = (row.GetCell(6)).ToString().Trim();
col = "H";
entity.End.SystemName = (row.GetCell(7)).ToString().Trim();
col = "I";
entity.End.EquitName = (row.GetCell(8)).ToString().Trim();
col = "J";
entity.End.EquitCode = (row.GetCell(9)).ToString().Trim();
col = "K";
entity.SafePassage = (row.GetCell(10)).ToString().Trim();
col = "L";
entity.PressureVesselCode = (row.GetCell(11)).ToString().Trim();
col = "M";
entity.CabinCode = (row.GetCell(12)).ToString().Trim();
col = "N";
entity.PipeCode = (row.GetCell(13)).ToString().Trim();
col = "O";
entity.Version = (row.GetCell(14)).ToString().Trim();
col = "P";
entity.Specification = (row.GetCell(15)).ToString().Trim();
col = "Q";
entity.Length = (row.GetCell(16)).ToString().Trim();
col = "R";
entity.PipeSpecification = (row.GetCell(17)).ToString().Trim();
col = "S";
entity.PipeLength = (row.GetCell(18)).ToString().Trim();
col = "T";
entity.Other = (row.GetCell(19)).ToString().Trim();
col = "C";
error++;
entity.CablePath = (sheet.GetRow(i + 1).GetCell(2)).ToString().Trim().Replace("电缆路径：", "");
entity.Description = numberNo;
entity.Id = Guid.NewGuid().ToString("N");
_cableRepository.Insert(entity);
i += 2;
}
catch (Exception e)
{
messagel.Add(" 第 " + error + " 行 第 " + col + " 列 ");
i += 2;
}
}
if (messagel.Count() > 0)
{
throw new Exception(string.Join(",", messagel));
}
}
}