[C#] 如何讀取Excel 中的某個欄位

如果想從這份xls檔案裡取出Wild這本書的價格(Price)

1. 需要

-add reference NPOI.dll

- 匯入NPOI.HSSF.UserModel , NPOI.SS.UserModel;

using NPOI.HSSF.UserModel; using NPOI.SS.UserModel;

2. 開檔(假設檔案路徑是　C:\book1.xls　)

HSSFWorkbook wookbook; string filepath=@"C:\book1.xls"; FileStream file = new FileStream (filepath, FileMode.Open, FileAccess.Read); byte[] bytes = new byte[file.Length]; file.Read(bytes, 0, ( int)file.Length); MemoryStream ms = new MemoryStream (bytes); wookbook = new HSSFWorkbook(ms);

3. 以每一行(row)的第一個欄位來做搜索匹配

public  string GetToolConfigCell(HSSFWorkbook　wookbook ,string sheetName, string rowName, string columnName)    {             ISheet sheet = wookbook.GetSheet(sheetName);             if (sheet == null)                 return null;             int rowIndex = -1;             int colIndex = -1;             for ( int r = 0; r < sheet.PhysicalNumberOfRows; r++)             {                 IRow row = sheet.GetRow(r);                 if (row == null || row.Cells.Count == 0)                     continue;                 if (row.Cells[0].CellType == CellType.STRING && string.Compare(row.Cells[0].StringCellValue, rowName, true) == 0)                 {                     rowIndex = r;                     break;                 }             }             IRow firstRow = sheet.GetRow(0);             for ( int c = 0; c < firstRow.Cells.Count; c++)             {                 if (firstRow.Cells[c].CellType == CellType.STRING && string.Compare(firstRow.Cells[c].StringCellValue, columnName, true) == 0)                 {                     colIndex = c;                     break;                 }             }             if (rowIndex != -1 && colIndex != -1)             {                 string cellData = "";                 ICell cell = sheet.GetRow(rowIndex).Cells[colIndex];                 switch (cell.CellType)                 {                     case CellType.BLANK:                         cellData = "";                         break;                     case CellType.BOOLEAN:                         cellData = Convert.ToString(cell.BooleanCellValue);                         break;                     case CellType.ERROR:                         cellData = Convert.ToString(cell.ErrorCellValue);                         break;                     case CellType.FORMULA:                         cellData = Convert.ToString(cell.StringCellValue);                         break;                     case CellType.NUMERIC:                         cellData = Convert.ToString(cell.NumericCellValue);                         break;                     case CellType.STRING:                         cellData = Convert.ToString(cell.StringCellValue);                         break;                     case CellType.Unknown:                         cellData = "";                         break;                 }                        return cellData;             }             else             {                             return null;             }     }

以下是使用範例（範例檔案 C:\book.xls ）

從C:\book.xls中讀出Wild這本書的價格

HSSFWorkbook wookbook; string filepath=@"C:\book1.xls"; FileStream file = new FileStream (filepath, FileMode.Open, FileAccess.Read); byte[] bytes = new byte[file.Length]; file.Read(bytes, 0, ( int)file.Length); MemoryStream ms = new MemoryStream (bytes); wookbook = new HSSFWorkbook(ms); string price=GetToolConfigCell( wookbook,"Sheet1","Wild" ,"Price"); System.Console.WriteLine( price );