摘要：
string。空：单元格。StringCellValue；caseCellType。Error://ERROR:returncell.ErrorCellValue.ToString (); caseCellType。Formula://FORMULA:default:return“=”+单元格。CellFormula；｝catch｛returnstring.Empty；｝｝//////获取单元格类型////////publicstaticstringOtherGetValueTypeForXLS｛try｛ifreturnstring.Empty；switch｛caseCellType.Blank://BLANK:returnstring.Empty ; caseCellType。Boolean://BOOLEAN:returncell.BooleanCellValue.ToString (); caseCellType。Numeric://NUMERIC:if｛DateTimeD=cell.DateCellValue；返回？
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 ImportClient
{
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:
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)
{
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:
if (DateUtil.IsValidExcelDate(cell.NumericCellValue) && DateUtil.IsCellDateFormatted(cell))
{
DateTime D = cell.DateCellValue;
return (D.ToString().Length == 0 || D.ToString().Contains("#")) ? " " : D.ToString();
}
else
{
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)
{
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;
}
}
}
}