c#-nopi读取excel内容

1. 读取sheet

public Dictionary<string, DataTable> ReadWorkbook(Stream Stream)
{
    Dictionary<string, DataTable> KeyValue = new Dictionary<string, DataTable>();
    using (Stream fs = Stream)
    {
        IWorkbook workbook = new XSSFWorkbook(fs);
        int COUNT = workbook.NumberOfSheets;
        for (int i = 0; i < COUNT; i++)
        {
            ISheet sheet = workbook.GetSheetAt(i);
            string SheetName = sheet.SheetName;
            DataTable dt = GetDataTable(sheet);
            KeyValue.Add(SheetName, dt);
        }
    }
    return KeyValue;
}

2. 读取单元格内容

 private DataTable GetDataTable(ISheet sheet)
 {
     DataTable dt = new DataTable();
     for (int rowIndex = 0; rowIndex <= sheet.GetRow(2).LastCellNum; rowIndex++)
     {
         XSSFCell cell = (XSSFCell)sheet.GetRow(2).GetCell(rowIndex);
         dt.Columns.Add(GetCellValue(cell));
     }
     System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
     while (rows.MoveNext())
     {
         IRow row = (XSSFRow)rows.Current;
         if (row == null) continue;
         DataRow dr = dt.NewRow();
         for (int cellIndex = 0; cellIndex < row.LastCellNum - 1; cellIndex++)
         {
             if (cellIndex > dt.Columns.Count - 1)
             {
                 break;
             }
             NPOI.SS.UserModel.ICell cell = row.GetCell(cellIndex);
             string cellValue = GetCellValue(cell) + GetCellBackgroundColor(cell);
             dr[cellIndex] = cellValue;
         }
         dt.Rows.Add(dr);
     }
     return dt;
 }

3. 调用方法

Dictionary<string, DataTable> valuePairs = new ReadFileUtils().ReadWorkbook(fs);
DataTable sheet = valuePairs["sheet1"];

拓展方法

 ///读取单元格属性
 private static string GetCellValue(NPOI.SS.UserModel.ICell cell)
 {
     if (cell == null)
         return null;
     switch (cell.CellType)
     {
         case CellType.String:
             return cell.StringCellValue;
         case CellType.Numeric:
             return DateUtil.IsCellDateFormatted(cell)
                 ? cell.DateCellValue.ToString("yyyy-MM-dd")
                 : cell.NumericCellValue.ToString();
         case CellType.Boolean:
             return cell.BooleanCellValue.ToString();
         case CellType.Formula:
             return cell.NumericCellValue.ToString();
         default:
             return string.Empty;
     }
 }
 ///读取单元格背景色
 private string GetCellBackgroundColor(NPOI.SS.UserModel.ICell cell)
 {
     if (cell == null) return null;
     ICellStyle style = cell.CellStyle;
     IColor backgroundColor = style.FillBackgroundColorColor;
     IColor foregroundColor = style.FillForegroundColorColor;
     IColor fillColor = foregroundColor ?? backgroundColor;
     if (fillColor == null)
     {
         return null;
     }
     if (fillColor is HSSFColor hssfColor)
     {
         return $"#{hssfColor.GetHexString()}";
     }
     else if (fillColor is XSSFColor xssfColor)
     {
         byte[] rgb = xssfColor.RGB;
         if (rgb != null && rgb.Length >= 3)
         {
             return $"#{rgb[0]:X2}{rgb[1]:X2}{rgb[2]:X2}";
         }
     }
     return null;
 }