溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
本文示例代碼下載： 鏈接：http://pan.baidu.com/s/1jHBdgCA 密碼：hzh7
ps:Vs數(shù)據(jù)庫腳本在解壓目錄下，修改web.config數(shù)據(jù)庫鏈接,示例代碼包含：導(dǎo)入，導(dǎo)出，上傳
導(dǎo)入導(dǎo)出實(shí)在多例子，很多成熟的組建都分裝了導(dǎo)入和導(dǎo)出，這一節(jié)演示利用LinqToExcel組件對(duì)Excel的導(dǎo)入，這個(gè)是一個(gè)極其簡單的例子。
我并不是說導(dǎo)入的簡單。而是LinqToExcel讓我們對(duì)Excel操作更加簡單！
最后我們將利用ClosedXML輸出Excel。這個(gè)比現(xiàn)流行NPOI與EPPlus更加優(yōu)秀的組件，以O(shè)pen XML SDK為基礎(chǔ)，所以只支持xlsx，不支持xls格式（現(xiàn)階段誰沒有個(gè)office2007以上版本）
他導(dǎo)出的Excel根據(jù)官方描述，兼容性遠(yuǎn)超同行對(duì)手
如果你不是使用本架構(gòu)只看2，3，4點(diǎn)，使用BLL層的代碼，這同樣適用你的MVC程序
LinqToExcel組件讀取Excel文件
ClosedXML組件輸出Excel
一張演示的數(shù)據(jù)庫表
安裝LinqToExcel NuGet包
文件上傳樣例
CloseXML導(dǎo)出Excel
CREATE TABLE [dbo].[Spl_Person]( [Id] [nvarchar](50) NOT NULL, --ID [Name] [nvarchar](50) NULL, --姓名 [Sex] [nchar](10) NULL, --性別 [Age] [int] NULL, --年齡 [IDCard] [nvarchar](50) NULL, --IDCard [Phone] [nvarchar](50) NULL, --電話 [Email] [nvarchar](200) NULL, --郵件 [Address] [nvarchar](300) NULL, --地址 [CreateTime] [datetime] NOT NULL, --創(chuàng)建時(shí)間 [Region] [nvarchar](50) NULL, --區(qū)域 [Category] [nvarchar](50) NULL, --類別 CONSTRAINT [PK_Spl_Person] PRIMARY KEY CLUSTERED ( [Id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO
如何使用這個(gè)框架？
按照之前的做法，更新到EF。并利用T4生成DAL,BLL,MODEL。再用代碼生成器生成界面復(fù)制進(jìn)解決方案，一步到位
配置好訪問地址和權(quán)限，直接運(yùn)行
再手動(dòng)在工具欄添加導(dǎo)入和導(dǎo)出的按鈕（別忘記添加權(quán)限）
@Html.ToolButton("btnImport", "fa fa-level-down", Resource.Import, perm, "Import", true) @Html.ToolButton("btnExport", "fa fa-level-up", Resource.Export, perm, "Export", true)因?yàn)槲覀冏x取Excel放在BLL層，所有在BLL層安裝LinqToExcel包
（這一點(diǎn)簡單帶過，可以到網(wǎng)上下載上傳代碼植入到自己系統(tǒng)中）
或者下載第32節(jié)的源碼 或者下載本節(jié)的示例代碼都可以
我這里使用普通的form上傳功能
添加導(dǎo)入前端代碼
<div id="uploadExcel" class="easyui-window" data-options="modal:true,closed:true,minimizable:false,shadow:false">
<form name="form1" method="post" id="form1">
<table>
<tr>
<th style=" padding:20px;">Excel：</th>
<td style=" padding:20px;">
<input name="ExcelPath" type="text" maxlength="255" id="txtExcelPath" readonly="readonly" style="width:200px" class="txtInput normal left">
<a href="javascript:$('#FileUpload').trigger('click').void(0);;" class="files">@Resource.Browse</a>
<input class="displaynone" type="file" id="FileUpload" name="FileUpload" onchange="Upload('ExcelFile', 'txtExcelPath', 'FileUpload');">
<span class="uploading">@Resource.Uploading</span>
</td>
</tr>
</table>
<div class="endbtndiv">
<a id="btnSave" href="javascript:ImportData()" class="easyui-linkbutton btns">Save</a>
<a id="btnReturn" href="javascript:$('#uploadExcel').window('close')" class="easyui-linkbutton btnc">Close</a>
</div>
</form>
</div>導(dǎo)入按鈕事件只要彈出上傳框就好
$("#btnImport").click(function () {
$("#uploadExcel").window({ title: '@Resource.Import', width: 450, height: 160, iconCls: 'icon-details' }).window('open');
});
保證上傳是成功的。
直接查看源碼的C#上傳代碼
-------------------------------------------------------------------------------------------------------上面只是前期的準(zhǔn)備工作--------------------------------------------------------------
在業(yè)務(wù)層添加以下代碼
using Apps.Common;
using Apps.Models;
using Apps.Models.Spl;
using LinqToExcel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Apps.Spl.BLL
{
public partial class Spl_ProductBLL
{
/// <summary>
/// 校驗(yàn)Excel數(shù)據(jù)
/// </summary>
public bool CheckImportData( string fileName, List<Spl_PersonModel> personList,ref ValidationErrors errors )
{
var targetFile = new FileInfo(fileName);
if (!targetFile.Exists)
{
errors.Add("導(dǎo)入的數(shù)據(jù)文件不存在");
return false;
}
var excelFile = new ExcelQueryFactory(fileName);
//對(duì)應(yīng)列頭
excelFile.AddMapping<Spl_PersonModel>(x => x.Name, "Name");
excelFile.AddMapping<Spl_PersonModel>(x => x.Sex, "Sex");
excelFile.AddMapping<Spl_PersonModel>(x => x.Age, "Age");
excelFile.AddMapping<Spl_PersonModel>(x => x.IDCard, "IDCard");
excelFile.AddMapping<Spl_PersonModel>(x => x.Phone, "Phone");
excelFile.AddMapping<Spl_PersonModel>(x => x.Email, "Email");
excelFile.AddMapping<Spl_PersonModel>(x => x.Address, "Address");
excelFile.AddMapping<Spl_PersonModel>(x => x.Region, "Region");
excelFile.AddMapping<Spl_PersonModel>(x => x.Category, "Category");
//SheetName
var excelContent = excelFile.Worksheet<Spl_PersonModel>(0);
int rowIndex = 1;
//檢查數(shù)據(jù)正確性
foreach (var row in excelContent)
{
var errorMessage = new StringBuilder();
var person = new Spl_PersonModel();
person.Id =
person.Name = row.Name;
person.Sex = row.Sex;
person.Age = row.Age;
person.IDCard = row.IDCard;
person.Phone = row.Phone;
person.Email = row.Email;
person.Address = row.Address;
person.Region = row.Region;
person.Category = row.Category;
if (string.IsNullOrWhiteSpace(row.Name))
{
errorMessage.Append("Name - 不能為空. ");
}
if (string.IsNullOrWhiteSpace(row.IDCard))
{
errorMessage.Append("IDCard - 不能為空. ");
}
//=============================================================================
if (errorMessage.Length > 0)
{
errors.Add(string.Format(
"第 {0} 列發(fā)現(xiàn)錯(cuò)誤：{1}{2}",
rowIndex,
errorMessage,
"<br/>"));
}
personList.Add(person);
rowIndex += 1;
}
if (errors.Count > 0)
{
return false;
}
return true;
}
/// <summary>
/// 保存數(shù)據(jù)
/// </summary>
public void SaveImportData(IEnumerable<Spl_PersonModel> personList)
{
try
{
DBContainer db = new DBContainer();
foreach (var model in personList)
{
Spl_Person entity = new Spl_Person();
entity.Id = ResultHelper.NewId;
entity.Name = model.Name;
entity.Sex = model.Sex;
entity.Age = model.Age;
entity.IDCard = model.IDCard;
entity.Phone = model.Phone;
entity.Email = model.Email;
entity.Address = model.Address;
entity.CreateTime = ResultHelper.NowTime;
entity.Region = model.Region;
entity.Category = model.Category;
db.Spl_Person.Add(entity);
}
db.SaveChanges();
}
catch (Exception ex)
{
throw;
}
}
}
}BLL
public class ValidationErrors : List<ValidationError>
{
/// <summary>
/// 添加錯(cuò)誤
/// </summary>
/// <param name="errorMessage">信息描述</param>
public void Add(string errorMessage)
{
base.Add(new ValidationError { ErrorMessage = errorMessage });
}
/// <summary>
/// 獲取錯(cuò)誤集合
/// </summary>
public string Error
{
get {
string error = "";
this.All(a => {
error += a.ErrorMessage;
return true;
});
return error;
}
}
}ValidationError
代碼包含兩個(gè)方法
public bool CheckImportData( string fileName, List<Spl_PersonModel> personList,ValidationErrors errors )
fileName為我們上傳的文件。
personList為承接數(shù)據(jù)List
ValidationErrors 錯(cuò)誤集合
public void SaveImportData(IEnumerable<Spl_PersonModel> personList)
保存數(shù)據(jù)
別忘記添加接口
public partial interface ISpl_PersonBLL
{ bool CheckImportData(string fileName, List<Spl_PersonModel> personList, ref ValidationErrors errors); void SaveImportData(IEnumerable<Spl_PersonModel> personList);
}簡單明白，直接看代碼，不再解析。OK這樣控制器就可以直接調(diào)用了
public ActionResult Import(string filePath)
{ var personList = new List<Spl_PersonModel>(); //校驗(yàn)數(shù)據(jù)is bool checkResult = m_BLL.CheckImportData(filePath, personList, ref errors); //校驗(yàn)通過直接保存 if (checkResult)
{
m_BLL.SaveImportData(personList);
LogHandler.WriteServiceLog(GetUserId(),"導(dǎo)入成功", "成功", "導(dǎo)入", "Spl_Person"); return Json(JsonHandler.CreateMessage(1, Resource.InsertSucceed));
} else
{ string ErrorCol = errors.Error;
LogHandler.WriteServiceLog(GetUserId(), ErrorCol, "失敗", "導(dǎo)入", "Spl_Person"); return Json(JsonHandler.CreateMessage(0, Resource.InsertFail + ErrorCol));
}
}最后前端還需要把路徑給回來。
function ImportData()
{
$.post("@Url.Action("Import")?filePath=" + $("#txtExcelPath").val(), function (data) { if (data.type == 1) {
$("#List").datagrid('load');
$('#uploadExcel').window('close');
}
$.messageBox5s('@Resource.Tip', data.message);
}, "json");
}OK測試一下！建立一個(gè)新的excel格式
一般情況下我們是提供模版給用戶下載供用戶輸入數(shù)據(jù)，來確保格式的正確性
--------------------------------------------------------------------------------------導(dǎo)出功能------------------------------------------------------------------------------
在控制器添加以下代碼：
public ActionResult Export()
{ var exportSpource = this.GetExportData(); var dt = JsonConvert.DeserializeObject<DataTable>(exportSpource.ToString()); var exportFileName = string.Concat( "Person",
DateTime.Now.ToString("yyyyMMddHHmmss"), ".xlsx"); return new ExportExcelResult
{
SheetName = "人員列表",
FileName = exportFileName,
ExportData = dt
};
} private JArray GetExportData()
{
List<Spl_PersonModel> list = m_BLL.GetList(ref setNoPagerAscById, "");
JArray jObjects = new JArray(); foreach (var item in list)
{ var jo = new JObject();
jo.Add("Id", item.Id);
jo.Add("Name", item.Name);
jo.Add("Sex", item.Sex);
jo.Add("Age", item.Age);
jo.Add("IDCard", item.IDCard);
jo.Add("Phone", item.Phone);
jo.Add("Email", item.Email);
jo.Add("Address", item.Address);
jo.Add("CreateTime", item.CreateTime);
jo.Add("Region", item.Region);
jo.Add("Category", item.Category);
jObjects.Add(jo);
} return jObjects;
}注意：ExportExcelResult
此類是使用ClosedXML.Excel，已經(jīng)封裝好了。大家直接拿來用就可以。把關(guān)注點(diǎn)都放在業(yè)務(wù)中
using ClosedXML.Excel;using System;using System.Data;using System.IO;using System.Text;using System.Web;using System.Web.Mvc;namespace Apps.Web.Core
{ public class ExportExcelResult : ActionResult
{ public string SheetName { get; set; } public string FileName { get; set; } public DataTable ExportData { get; set; } public ExportExcelResult()
{
} public override void ExecuteResult(ControllerContext context)
{ if (ExportData == null)
{ throw new InvalidDataException("ExportData");
} if (string.IsNullOrWhiteSpace(this.SheetName))
{ this.SheetName = "Sheet1";
} if (string.IsNullOrWhiteSpace(this.FileName))
{ this.FileName = string.Concat( "ExportData_",
DateTime.Now.ToString("yyyyMMddHHmmss"), ".xlsx");
} this.ExportExcelEventHandler(context);
} /// <summary> /// Exports the excel event handler. /// </summary> /// <param name="context">The context.</param> private void ExportExcelEventHandler(ControllerContext context)
{ try
{ var workbook = new XLWorkbook(); if (this.ExportData != null)
{
context.HttpContext.Response.Clear(); // 編碼
context.HttpContext.Response.ContentEncoding = Encoding.UTF8; // 設(shè)置網(wǎng)頁ContentType
context.HttpContext.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; // 導(dǎo)出名字 var browser = context.HttpContext.Request.Browser.Browser; var exportFileName = browser.Equals("Firefox", StringComparison.OrdinalIgnoreCase) ? this.FileName
: HttpUtility.UrlEncode(this.FileName, Encoding.UTF8);
context.HttpContext.Response.AddHeader( "Content-Disposition", string.Format("attachment;filename={0}", exportFileName)); // Add all DataTables in the DataSet as a worksheets
workbook.Worksheets.Add(this.ExportData, this.SheetName); using (var memoryStream = new MemoryStream())
{
workbook.SaveAs(memoryStream);
memoryStream.WriteTo(context.HttpContext.Response.OutputStream);
memoryStream.Close();
}
}
workbook.Dispose();
} catch (Exception ex)
{ throw;
}
}
}
}
本節(jié)知識(shí)點(diǎn)，全部聚集在CheckImportData方法上。
對(duì)應(yīng)列頭是模版xlsx的列頭
1.如果模版需要是是中文的，如Name=名字，那么方法應(yīng)該這么寫
excelFile.AddMapping<Spl_PersonModel>(x => x.Name, "名字");
2.導(dǎo)入第幾個(gè)sheet工作薄可以這么寫
我這里寫0是指第一個(gè)sheet工作薄。可以直接指定工作薄
var excelContent = excelFile.Worksheet<Spl_PersonModel>("Sheet1");
3.檢查正確性可以確保數(shù)據(jù)的來源?？梢越o出用戶正確的修改提示。
4.借助ClosedXML，導(dǎo)出實(shí)際只需要幾行代碼。哈哈..這是如此的簡單。
return new ExportExcelResult
{
SheetName = "人員列表",
FileName = exportFileName,
ExportData = dt
};
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼