.NET C# 读写Excel及转换DataTable

目录

• .NET C# 读写Excel及转换DataTable
• • 1. 依赖库
  • 2. Nuget包与版本
  • 3. ExcelUtil
  • • 3.1 Excel sheet 转 DataTable
    • 3.2 Excel sheet 转 DataSet
    • 3.3 DataTable 转 Excel sheet
    • 3.4 DataSet 转 Excel
    • 3.5 私有方法

      .NET C# 读写Excel及转换DataTable

      1. 依赖库

      using NPOI.HSSF.UserModel;
      using NPOI.SS.UserModel;
      using NPOI.XSSF.UserModel;
      using System.Data;
      using System.IO;
      using System.Text;
      using System.Text.RegularExpressions;
      

      2. Nuget包与版本

      

      3. ExcelUtil

      3.1 Excel sheet 转 DataTable

      /// 
      /// Excel sheet 转 DataTable
      /// 
      /// Excel文件路径
      /// Sheet名称
      /// 结果DataTable
      public static DataTable? FromExcel(string excelFilePath, string sheetName)
      {
          DataTable dataTable = new DataTable(sheetName);
          IWorkbook wb = GetWorkbook(excelFilePath);
          if (wb == null)
          {
              return null;
          }
          ISheet ws = wb.GetSheet(sheetName);
          if (ws == null)
          {
              return null;
          }
          if (ws.LastRowNum  maxColumnNum)
              {
                  maxColumnNum = row.LastCellNum;
              }
          }
          IRow headerRow = ws.GetRow(0);
          for (int columnIdx = 0; columnIdx  
      3.4 DataSet 转 Excel
       
      /// 
      /// DataSet 转 Excel
      /// 
      /// Excel文件路径
      /// DataSet实例
      /// 转换结果
      public static bool ToExcel(string excelFilePath, DataSet dataSet)
      {
          bool allSuccess = true;
          foreach (DataTable dataTable in dataSet.Tables)
          {
              bool success = ToExcel(excelFilePath, dataTable);
              if (!success)
              {
                  allSuccess = false;
              }
          }
          return allSuccess;
      }
       
      3.5 私有方法
       
      private static IWorkbook GetWorkbook(string excelFilePath)
      {
          string extension = Path.GetExtension(excelFilePath);
          IWorkbook wb = null;
          FileStream fs = null;
          try
          {
              if (!File.Exists(excelFilePath))
              {
                  if (extension == ".xlsx" || extension == "xlsx")
                      wb = new XSSFWorkbook();
                  else if (extension == ".xls" || extension == "xls")
                      wb = new HSSFWorkbook();
                  else
                  {
                      AppLogger.Instance.Error($"错误文件类型{extension}！");
                      return null;
                  }
              }
              else
              {
                  fs = File.Open(excelFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
                  if (extension == ".xlsx" || extension == "xlsx")
                      wb = new XSSFWorkbook(fs);
                  else if (extension == ".xls" || extension == "xls")
                      wb = new HSSFWorkbook(fs);
                  else
                  {
                      AppLogger.Instance.Error($"错误文件类型{extension}！");
                      return null;
                  }
              }
              return wb;
          }
          catch (Exception ex)
          {
              AppLogger.Instance.Error("读取Excel文件失败！", ex);
              return null;
          }
          finally { if (fs != null) try { fs.Close(); } catch { } }
      }
      static object? GetCellValue(ICell cell)
      {
          if (cell == null)
              return null;
          switch (cell.CellType)
          {
              case CellType.Blank: //BLANK:  
                  return null;
              case CellType.Boolean: //BOOLEAN:  
                  return cell.BooleanCellValue;
              case CellType.Numeric: //NUMERIC:  
                  return cell.NumericCellValue;
              case CellType.String: //STRING:  
                  return cell.StringCellValue;
              case CellType.Error: //ERROR:  
                  return cell.ErrorCellValue;
              case CellType.Formula: //FORMULA:  
              default:
                  return "=" + cell.CellFormula;
          }
      }
       
      cell.NumericCellValue;
      case CellType.String: //STRING:
      return cell.StringCellValue;
      case CellType.Error: //ERROR:
      return cell.ErrorCellValue;
      case CellType.Formula: //FORMULA:
      default:
      return “=” + cell.CellFormula;
      }
      }