c#读取CSV文件跟Excel导入成DataTble

1.读取CSV文件

  /// <summary>/// 读取CSV文件/// </summary>/// <param name="fileName">文件路径</param>public static DataTable ReadCSV(string fileName){DataTable dt = new DataTable();FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);StreamReader sr = new StreamReader(fs, Encoding.UTF8, false);//记录每次读取的一行记录string strLine = null;//记录每行记录中的各字段内容string[] arrayLine = null;//判断，若是第一次，建立表头bool isFirst = true;//列的个数int dtColumns = 0;//逐行读取CSV文件while ((strLine = sr.ReadLine()) != null){strLine = strLine.Trim();//去除头尾空格arrayLine = strLine.Split(',');//分隔字符串，返回数组if (isFirst)  //建立表头{dtColumns = arrayLine.Length;//列的个数for (int i = 0; i < dtColumns; i++){dt.Columns.Add(arrayLine[i]);//每一列名称}isFirst = false;}else   //表内容{DataRow dataRow = dt.NewRow();//新建一行for (int j = 0; j < dtColumns; j++){if (arrayLine.Length > j){dataRow[j] = arrayLine[j];}}dt.Rows.Add(dataRow);//添加一行}}sr.Close();fs.Close();return dt;}

2.Excel导入成DataTble

    /// <summary>/// Excel导入成DataTble/// </summary>/// <param name="file">导入路径(包含文件名与扩展名)</param>/// <returns></returns>public static DataTable ExcelToTable(string file){DataTable dt = new DataTable();IWorkbook workbook;string fileExt = Path.GetExtension(file).ToLower();using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read)){if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }if (workbook == null) { return null; }ISheet sheet = workbook.GetSheetAt(0);//表头  IRow header = sheet.GetRow(sheet.FirstRowNum);List<int> columns = new List<int>();for (int i = 0; i < header.LastCellNum; i++){object obj = GetValueType(header.GetCell(i));if (obj == null || obj.ToString() == string.Empty){dt.Columns.Add(new DataColumn("Columns" + i.ToString()));}elsedt.Columns.Add(new DataColumn(obj.ToString()));columns.Add(i);}//数据  for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++){DataRow dr = dt.NewRow();bool hasValue = false;foreach (int j in columns){dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));if (dr[j] != null && dr[j].ToString() != string.Empty){hasValue = true;}}if (hasValue){dt.Rows.Add(dr);}}}return dt;}/// <summary>/// 获取单元格类型/// </summary>/// <param name="cell">目标单元格</param>/// <returns></returns>private static object GetValueType(ICell cell){if (cell == null)return null;switch (cell.CellType){case CellType.Blank:return null;case CellType.Boolean:return cell.BooleanCellValue;case CellType.Numeric:return cell.NumericCellValue;case CellType.String:return cell.StringCellValue;case CellType.Error:return cell.ErrorCellValue;case CellType.Formula:default:return "=" + cell.CellFormula;}}