C# EF ExcelHelper 导入导出

         //EF导出示例
         var query = from u in orderQuery
         join pro in dbContext.Project on
          u.projectNo equals pro.projectNo
         join org in dbContext.Org on
          u.orgNo equals org.orgNo into JoinedEmpDept
         from dept in JoinedEmpDept.DefaultIfEmpty()
         select new
          {
              学校名称= pro.projectName,
              所属班级=dept.orgName,
              费用类型=u.orderName,
              学生姓名=u.userName,
              身份证号=u.userNo,
              手机号=u.userPhone,
              订单编号=u.orderNo,
              费用金额=u.orderAmount/100.0,
              支付状态=u.payState==1?"已支付":"未支付",
              支付时间=u.payTime,
              银行流水号=u.ccbOrderId,
              添加时间=u.createTime,
              备注=u.remark
          };
         string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
         string file = localPath + fileName;
         using (ExcelHelper excelHelper = new ExcelHelper(file))
          {
             var list = query.OrderByDescending(u => u.添加时间).ToList();
              excelHelper.ListToExcel(list,"费用");
             return ReturnNode.ReturnSuccess(fileName);
          }
  
 

  
 
 

      //EF导出示例2，SQL直接导出
      try
      {
         var sql = "SELECT * from user ";
        //输出排名
        //sql = string.Format("SELECT @rank :=@rank + 1 AS 排名,A.* FROM ( {0}) A, (SELECT @rank := 0) B;", sql);
         string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
         string file = localPath + fileName;
          using (ExcelHelper excelHelper = new ExcelHelper(file))
          {
              System.Data.Common.DbCommand cmd = dbContext.Database.Connection.CreateCommand();
              cmd.CommandText = sql;
              cmd.CommandType = CommandType.Text;
             //转成sqlcommand正常查询语句。
              MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter();
              da.SelectCommand = cmd as MySql.Data.MySqlClient.MySqlCommand;
             DataTable dt = new DataTable();
              da.Fill(dt);
              excelHelper.DataTableToExcel(dt, "用户表",true);
             return ReturnNode.ReturnSuccess(serverPath + fileName);
          }
      }
      catch (Exception ex)
      {
         return ReturnNode.ReturnError(ex.Message);
      }
  
 

  
 
 

      //导入示例
      string file = "C:\\Data\\模板.xlsx";
      using (ExcelHelper excelHelper = new ExcelHelper(file))
      {
         DataTable dt = excelHelper.ExcelToDataTable("Sheet1", true);
         int num = 0;
         for (int i = 0; i < dt.Rows.Count; ++i)
          {
             DataRow row = dt.Rows[i];
             string index = row[0].ToString();
             string orgName = row[1].ToString();
             string orgNo = row[2].ToString();
             string orderName = row[3].ToString();
             string orderAmount = row[4].ToString();
             string userName = row[5].ToString();
             string userNo = row[6].ToString();
             string userPhone = row[7].ToString();
             string remark = row[8].ToString();
              num++;
          }
      }
  
 

  
 
 

      using NPOI.HSSF.UserModel;
      using NPOI.SS.UserModel;
      using NPOI.XSSF.UserModel;
      using System;
      using System.Collections.Generic;
      using System.ComponentModel;
      using System.Data;
      using System.Diagnostics;
      using System.IO;
      using System.Linq;
      using System.Text;
      using System.Threading.Tasks;
      namespace YFAPICommon.Lib
      {
         class ExcelHelper : IDisposable
          {
             private string fileName = null; //文件名
             private NPOI.SS.UserModel.IWorkbook workbook = null;
             private FileStream fs = null;
             private bool disposed;
             public ExcelHelper(string fileName)
              {
                 this.fileName = fileName;
                  disposed = false;
              }
             /// <summary>
             /// 将DataTable数据导入到excel中
             /// </summary>
             /// <param name="data">要导入的数据</param>
             /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
             /// <param name="sheetName">要导入的excel的sheet的名称</param>
             /// <returns>导入数据行数(包含列名那一行)</returns>
             public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten)
              {
                 int i = 0;
                 int j = 0;
                 int count = 0;
                  ISheet sheet = null;
                  fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                 if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                      workbook = new XSSFWorkbook();
                 else if (fileName.IndexOf(".xls") > 0) // 2003版本
                      workbook = new HSSFWorkbook();
                 try
                  {
                     if (workbook != null)
                      {
                          sheet = workbook.CreateSheet(sheetName);
                      }
                     else
                      {
                         return -1;
                      }
                     if (isColumnWritten == true) //写入DataTable的列名
                      {
                          IRow row = sheet.CreateRow(0);
                         for (j = 0; j < data.Columns.Count; ++j)
                          {
                              row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
                          }
                          count = 1;
                      }
                     else
                      {
                          count = 0;
                      }
                     for (i = 0; i < data.Rows.Count; ++i)
                      {
                          IRow row = sheet.CreateRow(count);
                         for (j = 0; j < data.Columns.Count; ++j)
                          {
                              row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
                          }
                          ++count;
                      }
                      workbook.Write(fs); //写入到excel
                     return count;
                  }
                 catch (Exception ex)
                  {
                      Console.WriteLine("Exception: " + ex.Message);
                     return -1;
                  }
              }
             /// <summary>
             /// 将List数据导入到excel中
             /// </summary>
             /// <param name="data">要导入的数据</param>
             /// <param name="sheetName">要导入的excel的sheet的名称</param>
             /// <returns>导入数据行数(包含列名那一行)</returns>
             public int ListToExcel<T>(List<T> data, string sheetName)
              {
                  ISheet sheet = null;
                  fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                 if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                      workbook = new XSSFWorkbook();
                 else if (fileName.IndexOf(".xls") > 0) // 2003版本
                      workbook = new HSSFWorkbook();
                 try
                  {
                     if (workbook != null)
                      {
                          sheet = workbook.CreateSheet(sheetName);
                      }
                     else
                      {
                         return -1;
                      }
                      PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
                      IRow row0 = sheet.CreateRow(0);
                     for (int i = 0; i < properties.Count; i++)
                      {
                          PropertyDescriptor property = properties[i];
                          Debug.WriteLine(property.Name);
                          row0.CreateCell(i).SetCellValue(property.Name);
                      }
                     int count = 1;
                     object[] values = new object[properties.Count];
                     foreach (T item in data)
                      {
                          IRow row = sheet.CreateRow(count);
                         for (int j = 0; j < values.Length; j++)
                          {
                             object obj = properties[j].GetValue(item);
                              Debug.WriteLine(properties[j].GetValue(item));
                              row.CreateCell(j).SetCellValue(obj==null?"":obj.ToString());
                          }
                          ++count;
                      }
                      workbook.Write(fs); //写入到excel
                     return count;
                  }
                 catch (Exception ex)
                  {
                      Console.WriteLine("Exception: " + ex.Message);
                     return -1;
                  }
              }
             /// <summary>
             /// 将excel中的数据导入到DataTable中
             /// </summary>
             /// <param name="sheetName">excel工作薄sheet的名称</param>
             /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
             /// <returns>返回的DataTable</returns>
             public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn)
              {
                  ISheet sheet = null;
                  DataTable data = new DataTable();
                 int startRow = 0;
                 try
                  {
                      fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                     if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                          workbook = new XSSFWorkbook(fs);
                     else if (fileName.IndexOf(".xls") > 0) // 2003版本
                          workbook = new HSSFWorkbook(fs);
                     if (sheetName != null)
                      {
                          sheet = workbook.GetSheet(sheetName);
                         if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
                          {
                              sheet = workbook.GetSheetAt(0);
                          }
                      }
                     else
                      {
                          sheet = workbook.GetSheetAt(0);
                      }
                     if (sheet != null)
                      {
                          IRow firstRow = sheet.GetRow(0);
                         int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
                         if (isFirstRowColumn)
                          {
                             for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                              {
                                  ICell cell = firstRow.GetCell(i);
                                 if (cell != null)
                                  {
                                     string cellValue = cell.StringCellValue;
                                     if (cellValue != null)
                                      {
                                          DataColumn column = new DataColumn(cellValue);
                                          data.Columns.Add(column);
                                      }
                                  }
                              }
                              startRow = sheet.FirstRowNum + 1;
                          }
                         else
                          {
                              startRow = sheet.FirstRowNum;
                          }
                         //最后一列的标号
                         int rowCount = sheet.LastRowNum;
                         for (int i = startRow; i <= rowCount; ++i)
                          {
                              IRow row = sheet.GetRow(i);
                             if (row == null)
                                 break;
                                 //continue; //没有数据的行默认是null　　　　　　　
                              DataRow dataRow = data.NewRow();
                             for (int j = row.FirstCellNum; j < cellCount; ++j)
                              {
                                 if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                                      dataRow[j] = row.GetCell(j).ToString();
                              }
                              data.Rows.Add(dataRow);
                          }
                      }
                     return data;
                  }
                 catch (Exception ex)
                  {
                      Console.WriteLine("Exception: " + ex.Message);
                     //System.Windows.Forms.MessageBox.Show(ex.Message);
                     return null;
                  }
              }
             internal void ListToExcel(List<object> list, string v)
              {
                 throw new NotImplementedException();
              }
             public void Dispose()
              {
                  Dispose(true);
                  GC.SuppressFinalize(this);
              }
             protected virtual void Dispose(bool disposing)
              {
                 if (!this.disposed)
                  {
                     if (disposing)
                      {
                         if (fs != null)
                              fs.Close();
                      }
                      fs = null;
                      disposed = true;
                  }
              }
          }
      }
  
 

  
 
 

文章来源: zzzili.blog.csdn.net，作者：清雨小竹，版权归原作者所有，如需转载，请联系作者。

原文链接：zzzili.blog.csdn.net/article/details/95599785