C# excel与DataTable之间的转换

注意，Excel读入DataTable需要使用NPOI包

/// 
  /// Excel导入成Datable  ///   /// 导入路径(包含文件名与扩展名)  ///   public static DataTable ExcelToTable(string file, string sheetName = "")  {    DataTable dt = new DataTable();    IWorkbook workbook = null;    try    {      string fileExt = Path.GetExtension(file).ToLower();      using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))      {        //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式        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 = string.IsNullOrWhiteSpace(sheetName) ? workbook.GetSheetAt(0) : workbook.GetSheet(sheetName);        //表头          IRow header = sheet.GetRow(sheet.FirstRowNum);        if (header == null)        {          throw new Exception("Excel表没有数据，请添加数据");        }        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().Trim() == string.Empty)          {            continue;          }          else          {            dt.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)          {            if (sheet.GetRow(i) != null) {              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);          //}          dt.Rows.Add(dr);        }        dt.AcceptChanges();      }      return dt;    }    catch (Exception e)    {      return null;    }    finally    {      if (dt != null) { dt.Dispose(); }      if (workbook != null) { workbook = null; }    }  }  /// 
  /// 获取单元格类型  ///   ///   ///   private static object GetValueType(ICell cell)  {    if (cell == null)      return null;    switch (cell.CellType)    {      case CellType.Blank: //BLANK:          return "";      case CellType.Boolean: //BOOLEAN:          return cell.BooleanCellValue;      case CellType.Numeric: //NUMERIC:          return cell.NumericCellValue;      case CellType.String: //STRING:          return cell.StringCellValue;      case CellType.Error: //ERROR:          return cell.ErrorCellValue;      case CellType.Formula: //FORMULA:        default:        return "=" + cell.CellFormula;    }  }

DataTable读入Excel

/// 
/// Datatable生成Excel表格并返回路径/// /// Datatable/// 文件名/// public static string DataToExcel(System.Data.DataTable m_DataTable, string s_FileName){//string FileName = @"C:\Users\JK\Desktop\DataTableToExcel\" + s_FileName + ".xlsx";  //文件存放路径string FileName = s_FileName;if (System.IO.File.Exists(FileName))    //存在则删除{System.IO.File.Delete(FileName);}System.IO.FileStream objFileStream;System.IO.StreamWriter objStreamWriter;string strLine = "";objFileStream = new System.IO.FileStream(FileName, System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.Write);objStreamWriter = new System.IO.StreamWriter(objFileStream, Encoding.Unicode);for (int i = 0; i < m_DataTable.Columns.Count; i++){strLine = strLine + m_DataTable.Columns[i].Caption.ToString() + Convert.ToChar(9);      //写列标题}objStreamWriter.WriteLine(strLine);strLine = "";for (int i = 0; i < m_DataTable.Rows.Count; i++){for (int j = 0; j < m_DataTable.Columns.Count; j++){if (m_DataTable.Rows[i].ItemArray[j] == null)strLine = strLine + " " + Convert.ToChar(9);        //写内容else{string rowstr = "";rowstr = m_DataTable.Rows[i].ItemArray[j].ToString();if (rowstr.IndexOf("\r\n") > 0)rowstr = rowstr.Replace("\r\n", " ");if (rowstr.IndexOf("\t") > 0)rowstr = rowstr.Replace("\t", " ");strLine = strLine + rowstr + Convert.ToChar(9);}}objStreamWriter.WriteLine(strLine);strLine = "";}objStreamWriter.Close();objFileStream.Close();return FileName;        //返回生成文件的绝对路径}

来源地址：https://blog.csdn.net/mingjing941018/article/details/132512786