asp.net 使用NPOI讀取excel文件

asp.net 使用NPOI讀取excel文件內容

NPOI下載地址：NPOI

public class ExcelHelper
{
    /// <summary>
    /// 讀取Excel文件數據到DataSet，一個Sheet對應一個DataTable
    /// </summary>
    /// <param name="strExcelFilePath">Excel文件的物理路徑</param>
    /// <returns></returns>
    public static DataSet GetDataFromExcel(string strExcelPhysicalPath, out string strError)
    {
        try
        {
            DataSet dsResult = new DataSet();
            strError = "";

            IWorkbook wbook = null;
            using (FileStream fs = new FileStream(strExcelPhysicalPath, FileMode.Open, FileAccess.Read))
            {
                if (strExcelPhysicalPath.IndexOf(".xlsx") > 0)
                {
                    wbook = new XSSFWorkbook(fs);
                }
                else
                {
                    wbook = new HSSFWorkbook(fs);
                }
            }

            for (int i = 0; i < wbook.NumberOfSheets; i++)
            {
                ISheet wsheet = wbook.GetSheetAt(i);
                if (wsheet == null) continue;

                DataTable dtSheet = GetDataFromSheet(wsheet, out strError);
                if (dtSheet != null)
                {
                    dtSheet.TableName = wsheet.SheetName.Trim();
                    dsResult.Tables.Add(dtSheet);
                }
                else
                {
                    dsResult = null;
                    break;
                }
            }
            return dsResult;
        }
        catch (Exception ex)
        {
            strError = ex.Message.ToString();
            return null;
        }
    }

    private static DataTable GetDataFromSheet(ISheet wsheet, out string strError)
    {
        try
        {
            DataTable dtResult = new DataTable();
            strError = "";

            //取sheet最大列數
            int max_column = 0;
            for (int i = wsheet.FirstRowNum; i <= wsheet.LastRowNum; i++)
            {
                IRow rsheet = wsheet.GetRow(i);
                if (rsheet != null && rsheet.LastCellNum > max_column)
                {
                    max_column = rsheet.LastCellNum;
                }
            }
            //給DataTable添加列
            for (int i = 0; i < max_column; i++)
            {
                dtResult.Columns.Add("A" + i.ToString());
            }

            for (int i = wsheet.FirstRowNum; i <= wsheet.LastRowNum; i++)
            {
                DataRow dRow = dtResult.NewRow();
                IRow rsheet = wsheet.GetRow(i);

                if (rsheet == null) continue;

                for (int j = rsheet.FirstCellNum; j < rsheet.LastCellNum; j++)
                {
                    ICell csheet = rsheet.GetCell(j);

                    if (csheet == null) continue;

                    switch (csheet.CellType)
                    {
                        case CellType.Blank:
                            dRow[j] = "";
                            break;
                        case CellType.Boolean:
                            dRow[j] = csheet.BooleanCellValue;
                            break;
                        case CellType.Error:
                            dRow[j] = csheet.ErrorCellValue;
                            break;
                        case CellType.Formula:
                            try
                            {
                                dRow[j] = csheet.NumericCellValue;

                                short format1 = csheet.CellStyle.DataFormat;
                                if (format1 == 177 || format1 == 178 || format1 == 188)
                                {
                                    dRow[j] = csheet.NumericCellValue.ToString("#0.00");
                                }
                            }
                            catch
                            {
                                dRow[j] = csheet.StringCellValue.Trim();
                            }
                            break;
                        case CellType.Numeric:
                            try
                            {
                                short format2 = csheet.CellStyle.DataFormat;
                                if (format2 == 14 || format2 == 31 || format2 == 57 || format2 == 58)
                                {
                                    dRow[j] = csheet.DateCellValue;
                                }
                                else
                                {
                                    dRow[j] = csheet.NumericCellValue;
                                }
                                if (format2 == 177 || format2 == 178 || format2 == 188)
                                {
                                    dRow[j] = csheet.NumericCellValue.ToString("#0.00");
                                }
                            }
                            catch
                            {
                                dRow[j] = csheet.StringCellValue.Trim();
                            }
                            break;
                        case CellType.String:
                            dRow[j] = csheet.StringCellValue.Trim();
                            break;
                        default:
                            dRow[j] = csheet.StringCellValue.Trim();
                            break;
                    }
                }

                dtResult.Rows.Add(dRow);
            }
            return dtResult;
        }
        catch (Exception ex)
        {
            strError = ex.Message.ToString();
            return null;
        }
    }
}