C# .Net Excel上传&后台解析成DataTable

Http请求 讀取，解析

[AcceptVerbs(HttpVerbs.Post)]
public ActionResult importExcel()
{
    try
    {
        HttpFileCollectionBase files = HttpContext.Request.Files;
        if (files != null)
        {
            foreach (string key in files.Keys)
            {
                #region MyRegion
                HttpPostedFileBase fileData = files[key];
                if (fileData != null)
                {
                    HttpContext.Request.ContentEncoding = Encoding.GetEncoding("UTF-8");
                    HttpContext.Response.ContentEncoding = Encoding.GetEncoding("UTF-8");
                    HttpContext.Response.Charset = "UTF-8";

                    //1. Excel 轉 DataTable
                    Stream sileStream = fileData.InputStream;
                    //string sheetName = "";
                    string fileName = fileData.FileName;
                    ExcelHelper excelHelper = new ExcelHelper();
                    DataTable importedExcelDt = excelHelper.ExcelToDataTable("默認第一頁", 1, fileName, sileStream);
                }
            }
        }
    }
    catch (Exception ex)
    {
        string ls_ErrMsg = "--> *Exception Error:" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + "
     Message:" + ex.Message;
        Log4net.ErrorLog(ls_ErrMsg);//log 
        return Json(new { IsSuccess = false, Message = "NG", Data = ex.Message });
    }
}

Excel 转DataTable

     /// <summary>
     /// Excel To DataTable
     /// </summary>
     /// <param name="sheetName">默認第一頁</param>
     /// <param name="headerRow">表頭行 index</param>
     /// <param name="fileName"></param>
     /// <param name="sileStream"></param>
     /// <returns></returns>
     public DataTable ExcelToDataTable(string sheetName, int headerRow, string fileName, Stream sileStream)
     {
         ISheet sheet = null;
         DataTable data = new DataTable();
         int startRow = 0;
         try
         {
             //var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
             if (fileName.IndexOf(".xlsx") > 0)
                 workbook = new XSSFWorkbook(sileStream);
             else if (fileName.IndexOf(".xls") > 0)
                 workbook = new HSSFWorkbook(sileStream);
 
             if (sheetName != null)
             {
                 sheet = workbook.GetSheet(sheetName);
                 if (sheet == null)
                 {
                     sheet = workbook.GetSheetAt(0);
                 }
             }
             else
             {
                 sheet = workbook.GetSheetAt(0);
             }
             if (sheet != null)
             {
                 IRow firstRow = sheet.GetRow(1);
                 int cellCount = firstRow.LastCellNum;
 
                 for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                 {
                     ICell cell = firstRow.GetCell(i);
                     if (cell != null)
                     {
                         string cellValue = "";
                         if (cell.CellType == CellType.Numeric)
                         {
                             cellValue = cell.NumericCellValue.ToString();
                         }
                         if (cell.CellType == CellType.Formula)
                         {
                             cellValue = cell.CellFormula.ToString();
                         }
                         else
                         {
                             cellValue = cell.StringCellValue;
                         }
                         if (cellValue != null)
                         {
                             DataColumn column = new DataColumn(cellValue);
                             data.Columns.Add(column);
                         }
                     }
                 }
                 
                 startRow = headerRow + 1; //從第headerRow + 1 行開始，根據表數據進行調整
 
 
                 int rowCount = sheet.LastRowNum;
                 for (int i = startRow; i <= rowCount; ++i)
                 {
                     IRow row = sheet.GetRow(i);
                     if (row == null) continue;
 
                     DataRow dataRow = data.NewRow();
                     for (int j = row.FirstCellNum; j < cellCount; ++j)
                     {
                         if (row.GetCell(j) == null)
                         {
                             dataRow[j] = "";
                             continue;
                         }
                         if (row.GetCell(j).CellType == CellType.Formula)
                         {
                             row.GetCell(j).SetCellType(CellType.String);
                             //dataRow[j] = row.GetCell(j).StringCellValue;
 
                             string r_num = row.GetCell(j).StringCellValue;
                             //double num= Convert.ToDouble(r_num);
                             //double r_num1 =Math.Round(Convert.ToDouble(r_num), 4, MidpointRounding.AwayFromZero);
                             string percentage = (Math.Round(Convert.ToDouble(row.GetCell(j).StringCellValue), 4, MidpointRounding.AwayFromZero) * 100) + "%";
                             dataRow[j] = percentage;
                             continue;
                         }
                         if (row.GetCell(j) != null)
                             dataRow[j] = row.GetCell(j).ToString();
                     }
                     data.Rows.Add(dataRow);
                 }
             }
             //去除空行
             data = RemoveEmpty(data);
             return data;
         }
         catch (Exception ex)
         {
             Console.WriteLine("Exception: " + ex.Message);
             return null;
         }
     }

去除空行数据

    /// <summary>
    /// 去除空行及之後的數據
    /// </summary>
    /// <param name="data"></param>
    /// <returns></returns>
    protected DataTable RemoveEmpty(DataTable data)
    {
        try
        {
            int cutline = 0;
            List<DataRow> removelist = new List<DataRow>();
            for (int i = 0; i < data.Rows.Count; i++)
            {
                bool IsNull = true;
                for (int j = 0; j < data.Columns.Count; j++)
                {
                    if (!string.IsNullOrEmpty(data.Rows[i][j].ToString().Trim()))
                    {
                        IsNull = false;
                    }
                }
                if (IsNull)
                {
                    cutline = i;
                    break;
                    //removelist.Add(data.Rows[i]);
                }
            }
            DataTable cutDt = data.Clone();
            if (cutline > 0)
            {
                var query = data.AsEnumerable().Skip(0).Take(cutline);
                foreach (DataRow item in query)
                {
                    cutDt.Rows.Add(item.ItemArray);
                }
                return cutDt;
            }
            return data;

            //for (int i = 0; i < removelist.Count; i++)
            //{
            //    data.Rows.Remove(removelist[i]);
            //}
            //return data;
        }
        catch (Exception ex)
        {
            return null;
        }
    }