Excel和Word操作在开发过程中经常需要使用，这类工作不涉及到核心业务，但又往往不可缺少。以往的开发方式在业务代码中直接引入NPOI、Aspose或者其他第三方库，工作繁琐，耗时多，扩展性差——比如基础库由NPOI修改为EPPlus，意味着业务代码需要全部修改。由于工作需要，我在之前版本的基础上，封装了OfficeService，目的是最大化节省导入导出这种非核心功能开发时间，专注于业务实现，并且业务端与底层基础组件完全解耦，即业务端完全不需要知道底层使用的是什么基础库，使得重构代价大大降低。
EasyOffice提供了
Excel导入：通过对模板类标记特性自动校验数据（后期计划支持FluentApi，即传参决定校验行为），并将有效数据转换为指定类型，业务端只在拿到正确和错误数据后决定如何处理；
Excel导出：通过对模板类标记特性自动渲染样式（后期计划支持FluentApi，即传参决定导出行为）；
Word根据模板生成：支持使用文本和图片替换，占位符只需定义模板类，制作Word模板，一行代码导出docx文档（后期计划支持转换为pdf）；
Word根据Table母版生成：只需定义模板类，制作表格模板，传入数据，服务会根据数据条数自动复制表格母版，并填充数据；
Word从空白创建等功能：特别复杂的Word导出任务，支持从空白创建；
EasyOffice底层库目前使用NPOI,因此是完全免费的。通过IExcelImportProvider等Provider接口实现了底层库与实现的解耦，后期如果需要切换比如Excel导入的基础库为EPPlus，只需要提供IExcelImportProvider接口的EPPlus实现，并且修改依赖注入代码即可。
提供了.net core自带ServiceCollection注入和Autofac注入
builder.AddOffice(
new OfficeOptions());services.AddOffice(
new OfficeOptions());
定义Excel模板类
public class Car { [ColName(
"车牌号")] [Required] [Regex(RegexConstant.CAR_CODE_REGEX)] [Duplication]
public string CarCode { get;
set; } [ColName(
"手机号")] [Regex(RegexConstant.MOBILE_CHINA_REGEX)]
public string Mobile { get;
set; } [ColName(
"身份证号")] [Regex(RegexConstant.IDENTITY_NUMBER_REGEX)]
public string IdentityNumber { get;
set; } [ColName(
"姓名")] [MaxLength(
10)]
public string Name { get;
set; } [ColName(
"性别")] [Regex(RegexConstant.GENDER_REGEX)]
public GenderEnum Gender { get;
set; } [ColName(
"注册日期")] [DateTime]
public DateTime RegisterDate { get;
set; } [ColName(
"年龄")] [Range(
0,
150)]
public int Age { get;
set; } }
校验数据
var _rows = _excelImportService.ValidateAsync<ExcelCarTemplateDTO>(
new ImportOption() { FileUrl = fileUrl, DataRowStartIndex =
1, HeaderRowIndex =
0, MappingDictionary = null, SheetIndex =
0, ValidateMode = ValidateModeEnum.Continue }).Result; var errorDatas = _rows.Where(x => !x.IsValid); var validDatas = _rows.Where(x=>x.IsValid).FastConvert<ExcelCarTemplateDTO>();
转换为DataTable
var dt = _excelImportService.ToTableAsync<ExcelCarTemplateDTO> ( fileUrl,
0,
0,
1,
-1);
定义导出模板类
[
Header(Color = ColorEnum.BRIGHT_GREEN, FontSize = 22, IsBold = true)] [
WrapText]
public class ExcelCarTemplateDTO { [
ColName("车牌号")] [
MergeCols]
public string CarCode {
get;
set; } [
ColName("手机号")]
public string Mobile {
get;
set; } [
ColName("身份证号")]
public string IdentityNumber {
get;
set; } [
ColName("姓名")]
public string Name {
get;
set; } [
ColName("性别")]
public GenderEnum Gender {
get;
set; } [
ColName("注册日期")]
public DateTime RegisterDate {
get;
set; } [
ColName("年龄")]
public int Age {
get;
set; }
导出Excel
var bytes =
await _excelExportService.ExportAsync(
new ExportOption<ExcelCarTemplateDTO>() { Data = list, DataRowStartIndex =
1, ExcelType = Bayantu.Extensions.Office.Enums.ExcelTypeEnum.XLS, HeaderRowIndex =
0, SheetName =
"sheet1" }); File.WriteAllBytes(@
"c:\test.xls", bytes);
首先定义模板类，参考通用Excel导入
var templateBytes =
await _excelImportSolutionService.GetImportTemplateAsync<DemoTemplateDTO>();
var importConfig =
await _excelImportSolutionService.GetImportConfigAsync<DemoTemplateDTO>(
"uploadUrl",
"templateUrl");
var previewData =
await _excelImportSolutionService.GetFileHeadersAndRowsAsync<DemoTemplateDTO>(
"fileUrl");
var importOption =
new ImportOption() { FileUrl =
"fileUrl", ValidateMode = ValidateModeEnum.Continue };
object importSetData =
new object();
var importResult =
await _excelImportSolutionService.ImportAsync<DemoTemplateDTO> (importOption , importSetData , BusinessAction , CustomValidate );
var errorMsg =
await _excelImportSolutionService.ExportErrorMsgAsync(importResult.Tag);
CreateFromTemplateAsync - 根据模板生成Word
public class WordCarTemplateDTO {
public string OwnerName {
get;
set; } [
Placeholder("{Car_Type Car Type}")]
public string CarType {
get;
set; }
public IEnumerable<Picture> CarPictures {
get;
set; }
public Picture CarLicense {
get;
set; } }
string templateUrl =
@"c:\template.docx";WordCarTemplateDTO car =
new WordCarTemplateDTO(){ OwnerName =
"刘德华", CarType =
"豪华型宾利", CarPictures =
new List<Picture>() {
new Picture() { PictureUrl = pic1, FileName =
"图片1", Height =
10, Width =
3, PictureData =
null, PictureType = PictureTypeEnum.JPEG },
new Picture(){ PictureUrl = pic2 } }, CarLicense =
new Picture { PictureUrl = pic3 }};
var word =
await _wordExportService.CreateFromTemplateAsync(templateUrl, car);File.WriteAllBytes(
@"c:\file.docx", word.WordBytes);
CreateWordFromMasterTable-根据模板表格循环生成word
string templateurl = @
"c:\template.docx";
var user1 =
new UserInfoDTO() { Name =
"张三", Age =
15, Gender =
"男", Remarks =
"简介简介" };
var user2 =
new UserInfoDTO() { Name =
"李四", Age =
20, Gender =
"女", Remarks =
"简介简介简介" };
var datas =
new List<UserInfoDTO>() { user1, user2 };
for (
int i =
0; i <
10; i++) { datas.Add(user1); datas.Add(user2); }
var word =
await _wordExportService.CreateFromMasterTableAsync(templateurl, datas); File.WriteAllBytes(@
"c:\file.docx", word.WordBytes);
CreateWordAsync - 从空白生成word
[Fact] public
async Task 导出所有日程() {
var date1 =
new ScheduleDate() { DateTimeStr =
"2019年5月5日 星期八", Addresses =
new List<Address>() };
var address1 =
new Address() { Name =
"会场一", Categories =
new List<Category>() };
var cate1 =
new Category() { Name =
"分类1", Schedules =
new List<Schedule>() };
var schedule1 =
new Schedule() { Name =
"日程1", TimeString =
"上午9：00 - 上午12：00", Speakers =
new List<Speaker>() };
var schedule2 =
new Schedule() { Name =
"日程2", TimeString =
"下午13：00 - 下午14：00", Speakers =
new List<Speaker>() };
var speaker1 =
new Speaker() { Name =
"张三", Position =
"总经理" };
var speaker2 =
new Speaker() { Name =
"李四", Position =
"副总经理" }; schedule1.Speakers.Add(speaker1); schedule1.Speakers.Add(speaker2); cate1.Schedules.Add(schedule1); cate1.Schedules.Add(schedule2); address1.Categories.Add(cate1); date1.Addresses.Add(address1);
var dates =
new List<ScheduleDate>() { date1,date1,date1 };
var tables =
new List<Table>();
var table =
new Table() { Rows =
new List<TableRow>() }; foreach (
var date
in dates) {
var rowDate =
new TableRow() { Cells =
new List<TableCell>() }; rowDate.Cells.Add(
new TableCell() { Color =
"lightblue", Paragraphs =
new List<Paragraph>() {
new Paragraph() { Run =
new Run() { Text = date.DateTimeStr, Color =
"red", FontFamily =
"微软雅黑", FontSize =
12, IsBold =
true, Pictures =
new List<Picture>() }, Alignment = Alignment.CENTER } } }); table.Rows.Add(rowDate); foreach (
var addr
in date.Addresses) { foreach (
var cate
in addr.Categories) {
var rowCate =
new TableRow() { Cells =
new List<TableCell>() }; rowCate.Cells.Add(
new TableCell() { Paragraphs =
new List<Paragraph>{
new Paragraph() { Run =
new Run() { Text = addr.Name, } } } }); rowCate.Cells.Add(
new TableCell() { Paragraphs =
new List<Paragraph>(){
new Paragraph() { Run =
new Run() { Text = cate.Name, } } } }); table.Rows.Add(rowCate); foreach (
var sche
in cate.Schedules) {
var rowSche =
new TableRow() { Cells =
new List<TableCell>() };
var scheCell =
new TableCell() { Paragraphs =
new List<Paragraph>() {
new Paragraph() { Run =
new Run() { Text = sche.Name } }, {
new Paragraph() { Run =
new Run() { Text = sche.TimeString } } } } }; foreach (
var speaker
in sche.Speakers) { scheCell.Paragraphs.Add(
new Paragraph() { Run =
new Run() { Text = $
"{speaker.Position}:{speaker.Name}" } }); } rowSche.Cells.Add(scheCell); table.Rows.Add(rowSche); } } } } tables.Add(table);
var word =
await _wordExportService.CreateWordAsync(tables); File.WriteAllBytes(fileUrl, word.WordBytes); }
github地址：https://github.com/holdengong/EasyOffice
水平有限，如果有bug欢迎提issue；如果本项目对您略有帮助，请帮忙Start和推荐，谢谢。
原文地址：https://www.cnblogs.com/holdengong/p/10889838.html
.NET社区新闻，深度好文，欢迎访问公众号文章汇总 http://www.csharpkit.com
