使用Aspose.Cells实现导入导出

using System;using System.Collections.Generic;using System.Data;using System.IO;using System.Linq;using System.Text;using System.Threading.Tasks;using Aspose.Cells;namespace Lzd.Mvc.EasyUi.Common.ExcelUtil{  ///   /// excel操作基类  ///   ///  public  class BaseExcelUtil  {    private Workbook m_Wb = null;        ///     /// 生成Excel    ///     /// 模板Excel的路径+文件名    /// Excel文件的字节对象    public byte[] CreateExcel(string url)    {      FileStream fs = null;      try      {        //读取模板Excel文件的中内容        fs = new FileStream(url, FileMode.Open, FileAccess.Read, FileShare.Read);         m_Wb = new Workbook();         m_Wb.Open(fs);         setValue(m_Wb);         //转换为字节对象并返回        return m_Wb.SaveToStream().ToArray();       }      catch (Exception ex)      {        throw ex;      }      finally      {        fs.Close();      }    }      ///     /// 设定Excel中的数据     /// 数据源为datable类型    ///     /// 工作簿    public virtual void setValue(Workbook wb)    {      throw new Exception("The method or operation is not implemented.");    }             ///     /// 读取Excel    ///     /// Excel的路径+文件名    /// Excel文件的字节对象    public DataTable GetExcel(string url)    {      FileStream fs = null;      try      {        //读取Excel文件的中内容        fs = new FileStream(url, FileMode.Open, FileAccess.Read, FileShare.Read);         m_Wb = new Workbook();         m_Wb.Open(fs);         //设定Excel中的数据       return  getValue(m_Wb);       }      finally      {        fs.Close();      }    }     ///     /// 取得Excel中的数据    ///     /// 工作簿    public virtual DataTable getValue(Workbook wb)    {      throw new Exception("The method or operation is not implemented.");    }    ///     /// 设置字符串值    ///     ///     ///     public void putValue(Cell c, object value)    {      try      {        if (value == null || object.Equals(value, DBNull.Value) || value.ToString().Trim().Length == 0)        {         }        else        {          c.PutValue(value.ToString());        }      }      catch (Exception)      {        c.PutValue("--");      }    }    ///     /// 设置数值值    ///     ///     ///     public void putValueDouble(Cell c, object value)    {      try      {        if (value == null || object.Equals(value, DBNull.Value) || value.ToString().Trim().Length == 0)        {         }        else        {          c.PutValue(Decimal.Parse(value.ToString()));        }      }      catch (Exception)      {        c.PutValue(value.ToString());      }    }    ///     /// 设置日期值    ///     ///     ///     public void putDateValue(Cell c, object value)    {      try      {        if (value == null || object.Equals(value, DBNull.Value) || value.ToString().Trim().Length == 0)        {         }        else        {          c.PutValue(DateTime.Parse(value.ToString()));        }      }      catch (Exception)      {        c.PutValue(value.ToString());      }    }    }  }

using System;using System.Collections.Generic;using System.Data;using System.Linq;using System.Reflection;using System.Text;using System.Threading.Tasks;using Aspose.Cells;namespace Lzd.Mvc.EasyUi.Common.ExcelUtil{  ///   /// Excel帮助类  ///   public class ExcelUtil :BaseExcelUtil  {    private DataTable dt;    private string title;       public ExcelUtil() {           }     ///     /// 从第几行开始读取    ///     public int FirstRow { get; set; }    ///     /// 从第几列开始读取    ///     public int FirstColumns { get; set; }     ///     /// excel标题    ///     public string Title    {      get { return title; }      set { title = value; }    }    private string fileName;     ///     /// 文件名    ///     public string FileName    {      get { return fileName; }      set { fileName = value; }    }     public DataTable Dt    {      get { return dt; }      set { dt = value; }    }     public bool Flag    {      set;      get;    }   ///   ///   ///导出设定值    public override void setValue(Workbook wb)    {            int index = 0;      Worksheet ws = null;      int rcount = dt.Rows.Count, columns = dt.Columns.Count;      if (dt != null && dt.Rows.Count > 0)      {        index = wb.Worksheets.AddCopy(0);        ws = wb.Worksheets[index];        ws.Name = FileName.Replace(".xls", "");         try        {          putValue(ws.Cells[0, 0], this.title);          int i = 1;           for (int j = 0; j < columns; j++)          {             putValue(ws.Cells[1, j], dt.Columns[j].ColumnName);          }           for (int j = 0; j < rcount; j++)          {            i++;            for (int h = 0; h < columns; h++)            {               putValue(ws.Cells[i, h], dt.Rows[j][h].ToString());            }           }           wb.Worksheets.RemoveAt(0);        }        catch (Exception ex)        {          throw ex;        }      }    }     ///     /// 导入excel    ///     /// 读取的文件名    /// 从第几行开始读取    /// 从第几列开始读取    ///     ///      public override DataTable getValue(Workbook wb)    {          Worksheet sheet = wb.Worksheets[0];      Cells cells = sheet.Cells;       return cells.ExportDataTableAsString(FirstRow, FirstColumns, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);    }          } }

public ActionResult ToExcel() {      List list = new List();      for (int i = 0; i < 100; i++)      {        UserInfo info = new UserInfo();        info.Age = i.ToString();        info.ID = i;        info.Name = "姓名" + i;        list.Add(info);      }      ///将list类型转换为datatable      DataTable dt= DataTableHelper.IListToDataTable(list);      //实例化帮助类      ExcelUtil exc = new ExcelUtil();      exc.Dt = dt;      exc.FileName = "导出测试.xls";      exc.Title = "导出测试";      //需要写入的模板      string url = Server.MapPath("/Content/Down/template.xls");      byte[] data = exc.CreateExcel(url);      //浏览器下载文件      Response.AppendHeader("Content-Disposition", "attachment; filename=" + exc.FileName);//HttpUtility.UrlEncode(r.FileName, Encoding.UTF8));      Response.ContentType = "application/ms-excel";      Response.AddHeader("Content-Length", data.Length.ToString());      Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");      Response.BinaryWrite(data);      System.Web.HttpContext.Current.ApplicationInstance.CompleteRequest();      return Content("ss");    }

public ActionResult ImportExcel()    {      string url = Server.MapPath("/Content/Down/Import.xls");      ExcelUtil exc = new ExcelUtil();      exc.FirstRow = 1;      exc.FirstColumns = 0;       DataTable dt= exc.GetExcel(url);                return Content("ss");    }