菜鳥奇奇寫程式

先加入參考npoi  下載網址:https://npoi.codeplex.com/

使用這方法->fileName=檔案路徑

EX->D:\\示範\\Apple.xls

此方法會回傳DataTable,請直接放進datagridview.DataSource內就好

public static DataTable ReadExcelAsTableNPOI(string fileName)
{
using (FileStream fs = new FileStream(fileName, FileMode.Open))
{
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.GetSheetAt(0);
DataTable table = new DataTable();
//由第一列取標題做為欄位名稱
HSSFRow headerRow = new HSSFRow();
headerRow = sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{ //以欄位文字為名新增欄位，此處全視為字串型別以求簡化
if (headerRow.GetCell(i) != null)

table.Columns.Add(

new DataColumn(headerRow.GetCell(i).StringCellValue));

else//null 則放空白

table.Columns.Add(

new DataColumn(""));
}

//此處是給有轉日期需求用的
HSSFCellStyle cellStyle = wb.CreateCellStyle();
HSSFDataFormat format = wb.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("yyyyMMdd");

//略過第零列(標題列)，一直處理至最後一列
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = sheet.GetRow(i);
if (row == null) continue;
DataRow dataRow = table.NewRow();
//依先前取得的欄位數逐一設定欄位內容
for (int j = row.FirstCellNum; j < cellCount; j++)
if (row.GetCell(j) != null)
{//如要針對不同型別做個別處理，可善用.CellType判斷型別
//再用.StringCellValue, .DateCellValue, .NumericCellValue...取值
//此處只簡單轉成字串

//第一欄為日期格式
if (j == 0)
{
row.GetCell(j).CellStyle = cellStyle;
}

dataRow[j] = row.GetCell(j).ToString();
//dataRow[j + 1] = row.GetCell(j + 1).ToString();
}
table.Rows.Add(dataRow);

}
return table;
}
}