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