https://download.csdn.net/download/ttbat/15435334 NPOI.HSSF.Dll 下载地址。
在项目引用 NPOI.HSSF.Dll 后 using NPOI.HSSF.UserModel;
PS:excel文件只能读取1997-2003版本的,2007以上的excel版本无法读取。
/// <summary>
///
/// </summary>
/// <param name="path">文件地址</param>
/// <param name="_syptktService"></param>
/// <param name="success">成功数量</param>
/// <param name="failure">错误数量</param>
/// <returns></returns>
public static string ImprotSXSC(string path, ISYPTKTService _syptktService, out int success, out int failure)
{
string errorMsg = string.Empty;
success = failure = 0;
try
{
HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
{
#region 检查excel数据
hssfworkbook = new HSSFWorkbook(file);
HSSFSheet sheet = hssfworkbook.GetSheetAt(0);
if (sheet == null || sheet.LastRowNum < 0)
{
errorMsg = "excel格式异常,请检查";
return errorMsg;
}
Dictionary<string, int> headCellDic = GetCellDictNum(sheet, 0); //表头键与索引集合
errorMsg = CheckExcel(sheet);
if (!string.IsNullOrEmpty(errorMsg))
return errorMsg;
#endregion
HSSFRow row = null;
HSSFCell cell;
string rowMsg;
int startRowIndex = 1;
for (int i = startRowIndex; i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i);
cell = row.GetCell(headCellDic["A"]);
string name = getCellStringValue(row, cell, "A", out rowMsg);
var model = _syptktService.GetSxScxts(s => s.NAME.Contains(name)).FirstOrDefault();
bool isnew = false;int failure1 = 0;
if (model == null)
{
model = new SXSCXTEntiry();
isnew = true;
}
model.NAME = name;
cell = row.GetCell(headCellDic["B"]);
string X = getCellStringValue(row, cell, "B", out rowMsg);
cell = row.GetCell(headCellDic["C"]);
string Y = getCellStringValue(row, cell, "C", out rowMsg);
float x = CommonMethodNew.FromDFM(X);
float y = CommonMethodNew.FromDFM(Y);
model.X = X;
model.Y = Y;
if (X != "" && Y != "" && x >= 3 && y >= 2)
{
model.SHAPE = "POINT ( " + x + " " + y + ")";
}
if (string.IsNullOrEmpty(name))
{
failure1++;
errorMsg += string.Format("第{0}行水下生产系统名称未录入$", i);
}
if (string.IsNullOrEmpty(model.SHAPE))
{
failure1++;
errorMsg += string.Format("{0}水下生产系统经纬度数据不正确$", name);
}
if (failure1 > 0)
{ failure++; continue; }
}
}
}
catch (Exception ex)
{
failure++;
errorMsg = ex.ToString();
}
return errorMsg;
}
/// <summary>
/// 获取excel指定行中,列键值(键/索引)集合
/// </summary>
/// <param name="sheet"></param>
/// <param name="rowIndex"></param>
/// <returns></returns>
public static Dictionary<string, int> GetCellDictNum(HSSFSheet sheet,int rowIndex)
{
Dictionary<string, int> celldict = new Dictionary<string, int>();
HSSFRow frow = sheet.GetRow(rowIndex);
int colNum = frow.PhysicalNumberOfCells;
char A = 'A';
int Acode = (int)A;
int firA = 0;
String sfir = "";
String stow = "";
for (int i = 0; i < colNum; i++)
{
if (i < 26)
{
celldict.Add(((char)(Acode + i)).ToString(), i);
}
else if (i >= 26 && i <= (26 * 26))
{
firA = i / 26;
sfir = ((char)(Acode + firA - 1)).ToString();
firA = i % 26;
stow = ((char)(Acode + firA)).ToString();
celldict.Add(sfir + stow, i);
}
}
return celldict;
}
/// <summary>
/// 获取文本格式
/// </summary>
/// <param name="row"></param>
/// <param name="cell"></param>
/// <param name="cellKey"></param>
/// <param name="errorMsg"></param>
/// <returns></returns>
public static string getCellStringValue(HSSFRow row, HSSFCell cell, string cellKey, out string errorMsg)
{
errorMsg = string.Empty;
string value = null;
if (cell == null)
{
return null;
}
if (cell.CellType == HSSFCell.CELL_TYPE_NUMERIC)
value= cell.NumericCellValue.ToString();
else if (cell.CellType == HSSFCell.CELL_TYPE_STRING)
value= cell.StringCellValue;
else
{
try
{
value = cell.StringCellValue;
}
catch (Exception ex)
{
errorMsg = string.Format("文档第" + (row.RowNum + 1) + "行," + cellKey + "列格式不正确");
}
}
if(!string.IsNullOrEmpty(value))value = value.Trim().Replace("\n", "").Replace(" ", "").Replace("\t", "").Replace("\r", ""); ;
return value;
}