使用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 

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