使用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");
        }