using NPOI.SS.Formula.Functions;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks; namespace DCZY.BISC
{
public class ExcelTool
{ public static Dictionary<string, int> SetColumncaption(IRow header)
{
Dictionary<string, int> columns = new Dictionary<string, int>();
for (int i = 0; i < header.LastCellNum; i++)
{
string strcaption = header.GetCell(i).ToString();
if (strcaption != null && strcaption.Length > 0)
{
columns.Add(strcaption, i);
}
}
return columns;
} /// <summary>
/// 获取单元格类型(xls)
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
public static string GetValueTypeForXLS(ICell cell)
{
try
{
if (cell == null)
return string.Empty;
switch (cell.CellType)
{
case CellType.BLANK: //BLANK:
return string.Empty;
case CellType.BOOLEAN: //BOOLEAN:
return cell.BooleanCellValue.ToString();
case CellType.NUMERIC: //NUMERIC:
try
{
if (cell.ColumnIndex == 3 || cell.ColumnIndex == 4)
{
return cell.DateCellValue.ToString("HH:mm");
}
}
catch
{
}
return cell.NumericCellValue.ToString();
case CellType.STRING: //STRING:
return cell.StringCellValue == null ? string.Empty : cell.StringCellValue;
case CellType.ERROR: //ERROR:
return cell.ErrorCellValue.ToString();
case CellType.FORMULA: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}
catch (Exception e)
{
PLog.Log.WriteError(e.Message);
return string.Empty;
} } /// <summary>
/// 获取单元格类型(xls)
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
public static string OtherGetValueTypeForXLS(ICell cell)
{
try
{
if (cell == null)
return string.Empty;
switch (cell.CellType)
{
case CellType.BLANK: //BLANK:
return string.Empty;
case CellType.BOOLEAN: //BOOLEAN:
return cell.BooleanCellValue.ToString();
case CellType.NUMERIC: //NUMERIC:
return cell.NumericCellValue.ToString();
case CellType.STRING: //STRING:
return cell.StringCellValue == null ? string.Empty : cell.StringCellValue;
case CellType.ERROR: //ERROR:
return cell.ErrorCellValue.ToString();
case CellType.FORMULA: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}
catch (Exception e)
{
PLog.Log.WriteError(e.Message);
return string.Empty;
} } }
}
调用：
using DCZY.Base.Param;
using DCZY.Bean;
using DCZY.Bean.LocationDevice;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using PLog;
using PTool;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text; namespace DCZY.BISC.OperationPost
{
public class OperationPostFromExcel
{
private string _strfilename = string.Empty;
public OperationPostFromExcel(string strfilename)
{
_strfilename = strfilename;
} public List<OperationVariablePostInfo> GetInfo()
{
StringBuilder sb = new StringBuilder();
try
{
FileStream fs = new FileStream(_strfilename, FileMode.Open, FileAccess.Read);
IWorkbook hssfworkbook = new XSSFWorkbook(fs);
ISheet sheet = hssfworkbook.GetSheet("岗位配置表 (2)");
List<OperationVariablePostInfo> devicecollection = new List<OperationVariablePostInfo>();
IRow header = sheet.GetRow(sheet.FirstRowNum);
Dictionary<string, int> colcollection = ExcelTool.SetColumncaption(header);
//数据
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
OperationVariablePostInfo info = new OperationVariablePostInfo();
info.Name = ExcelTool.OtherGetValueTypeForXLS(sheet.GetRow(i).GetCell(colcollection["岗位名称"]));
if (info.Name == null)
{
info.Name = string.Empty;
}
info.Firstgwname = ExcelTool.OtherGetValueTypeForXLS(sheet.GetRow(i).GetCell(colcollection["一级岗位"]));
info.Gwname = ExcelTool.OtherGetValueTypeForXLS(sheet.GetRow(i).GetCell(colcollection["二级岗位"]));
string lookwith= ExcelTool.OtherGetValueTypeForXLS(sheet.GetRow(i).GetCell(colcollection["是否盯控"]));
if (lookwith.Length == 0)
{
info.Islookwith = false;
}
else
{
info.Islookwith = true;
}
//info.Islookwith=
info.Posttype = new ParamInfo();
info.Posttype.Name = ExcelTool.OtherGetValueTypeForXLS(sheet.GetRow(i).GetCell(colcollection["岗位类型"]));
info.Organization = new OrganizationInfo();
info.Organization.Name = ExcelTool.OtherGetValueTypeForXLS(sheet.GetRow(i).GetCell(colcollection["车站"]));
devicecollection.Add(info);
}
return devicecollection;
}
catch (Exception e)
{
Log.WriteError(e.Message);
return null;
} } }
}