溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇文章為大家展示了使用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");
}上述內容就是使用Aspose.Cells項目實現導入導出，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關注億速云行業資訊頻道。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
