通过NPOI读取Excel数据到DataTable

【实例简介】

通过NPOI库，使用C#来读取excel数据

【实例截图】

【核心代码】

		public static DataTable ExcelToTable(string file, ref List<string> typeList)
		{
			DataTable dt = new DataTable();
			List<IComparable> rowList = new List<IComparable>();
			IWorkbook workbook = null;
			string fileExt = Path.GetExtension(file).ToLower();
			using(FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
			{
				if (fileExt == ".xlsx")
				{
					workbook = new XSSFWorkbook(fs);
				}
				else if (fileExt == ".xls")
				{
					workbook = new HSSFWorkbook(fs);
				}

				if (workbook == null)
				{
					return null;
				}
				ISheet sheet = workbook.GetSheetAt(0);

				//表头
				IRow header = sheet.GetRow(0);
				for (int i = 0; i < header.LastCellNum; i  )
				{
					ICell cell = header.GetCell(i);
					if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) continue;
					{
						dt.Columns.Add(cell.ToString());
						//Console.WriteLine(cell.ToString());
					}
				}
				
				// 数据内容
				for (int i = (sheet.FirstRowNum   1); i <= sheet.LastRowNum; i  )
				{
					IRow row = sheet.GetRow(i);
					if (row == null) continue;
					if (row.Cells.All(d => d.CellType == CellType.Blank)) continue;
					for (int j = row.FirstCellNum; j < header.LastCellNum; j  )
					{
						if (row.GetCell(j) != null)
						{
							if (!string.IsNullOrEmpty(row.GetCell(j).ToString()) && !string.IsNullOrWhiteSpace(row.GetCell(j).ToString()))
							{
								try
								{
									rowList.Add(row.GetCell(j).NumericCellValue);
									//Console.WriteLine("获取数值");
									if (i == 1)
									{
										typeList.Add("Number");
									}
								}
								catch
								{
									rowList.Add(row.GetCell(j).ToString());
									//Console.WriteLine("获取字符串");
									if (i == 1)
									{
										typeList.Add("String");
									}
								}
							}
						}
					}
					if (rowList.Count > 0)
					{

						dt.Rows.Add(rowList.ToArray());
						rowList.Clear();
					}
				}

			}
			return dt;
		}