# QuickCode

**Repository Path**: duyibo/QuickCode

## Basic Information

- **Project Name**: QuickCode
- **Description**: No description available
- **Primary Language**: C#
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 5
- **Forks**: 2
- **Created**: 2015-01-13
- **Last Updated**: 2021-10-29

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# QuickCode快速使用手册
## 数据库操作
### **依赖核心DLL**
```
Mysoft.QuickCode.dll
```

### **引入依赖**

```csharp
using Mysoft.QuickCode.DAL;
```

### **文档例子中用的BaseEntity数据结构**
```csharp
using System;
using Mysoft.Map.Extensions.DAL;

namespace Mysoft.Test.Entity
{
    [Serializable]
    [DataEntity(Alias = "myUser")]
    public class MyUser : BaseEntity
    {
        [DataColumn(PrimaryKey = true)]
        public Guid UserGUID{ set; get; }
        
        [DataColumn(IsNullable = true)]
        public string UserCode{ set; get; }
        
        [DataColumn(IsNullable = true)]
        public string UserName{ set; get; }
        
        [DataColumn(IsNullable = true)]
        public int IsDisabled{ set; get; }
    }
}
```

### **数据新增**
- **单条数据新增**
```csharp
//要新增的数据
var user = new MyUser(){
    UserGUID=Guid.NewGuid(),
    UserName="皮皮瞎"
};

//新增或修改数据，如果数据库中根据主键查询不到对应的数据，则默认会新增，最终调用的是小平台的Insert方法。
QuickDAL.Context.SaveEntity(user);

/*
此方法和上面方法的区别是，如果user对应的字段值未赋值，则会调用小平台的SetDefaultValue设置每个字段对应数据类型的默认值。
一般用于更新场景下，如果不需要全部设置未默认值，第二个参数不需要传
*/
QuickDAL.Context.SaveEntity(user, true);
```
- **批量数据新增**

```csharp
//假设这里新增了3个用户信息
var list = new List<MyUser>();
list.Add(ew MyUser(){
    UserGUID=Guid.NewGuid(),
    UserName="皮皮瞎1"
});
list.Add(ew MyUser(){
    UserGUID=Guid.NewGuid(),
    UserName="皮皮瞎2"
});
list.Add(ew MyUser(){
    UserGUID=Guid.NewGuid(),
    UserName="皮皮瞎3"
});

//批量写入用户信息到数据库。内部使用的是sqlbulkcopy实现，已经对字段或属性映射顺序自动处理，因此无字段顺序要求。
QuickDAL<MyUser>.Context.BatchInsert(list);
```


### **数据修改**
- **通过实体方式修改**
```csharp
user.UserName="皮皮虾";
user.IsDisabled=0;  //如果IsDisabled数据类型为int类型，而非int?类型

/*
更新用户信息。由于IsDisabled为int类型，且int类型的默认值本身为0，所以在更新的时候，0不会被更新到数据库。
因为内部使用的小平台的Update方法。所以如果要更新，则需要使用SetDefaultValue方法设置。
*/
QuickDAL.Context.SaveEntity(user);

/*
更新用户信息。会将IsDisabled=0更新到数据库，同时其他字段如果也是默认值，则也会更新数据库。
此方式更新，务必在更新前先从数据库读取当前数据信息，再给要更新的字段赋值后再更新，同时启用事务。
如果采用new对象方式更新，数据库已存在值的其他字段也会更新为默认值。
*/
QuickDAL.Context.SaveEntity(user, true);
```
- **通过Expression语法修改**
```csharp
var userGUID = Guid.NewGuid();

//这种语法更新，会按需更新。
//第一个参数：a=>new MyUser(){UserName="皮皮瞎", IsDisabled=0}。指定了什么字段就更新什么字段。
//第二个参数则为更新的条件。
//翻译后的意思大约是:update myUser set UserName='皮皮瞎', IsDisabled=0 where UserGUID='xxxxxx' and IsDisabled<>0
QuickDAL<MyUser>.Context.UpdateEntity(a=>new MyUser(){UserName="皮皮瞎", IsDisabled=0}, 
    a=>a.UserGUID=userGUID && a.IsDisabled<>0);
```

### **数据查询**
- **单条数据查询**
```csharp
//假如已知要查询用户的GUID
var userGUID=Guid.NewGuid();    
//通过主键查询单条记录。
var user = QuickDAL<MyUser>.Context.GetEntity(userGUID);
//使用Expression表达式方式查询符合条件的第一条用户数据。
var user1 = QuickDAL<MyUser>.Context.GetEntity(a=>a.UserName=="皮皮瞎");
//在VB语言中中，由于VB无法自动区分委托和一般参数，所以使用Expression方式时，请使用方法GetEntityOne方法。如下：
//Dim user1 As MyUser = QuickDAL(Of MyUser).Context.GetEntityOne(Function(a) a.UserName = "皮皮瞎");
```
- **集合数据查询**
```csharp
//批量查询用户编码不等于空或不为null的记录
var user = QuickDAL<MyUser>.Context.GetEntityList(a=> a.UserCode <> "" || a.UserCode.Sqlable().IsNotNull());
```

### **数据删除**
待完善

### **其他语法**
- Exists
```csharp
//判断数据库中是否存在满足条件的数据
var result = QuickDAL<MyUser>.Context.Exists(a=> a.UserCode <> "" || a.UserCode.Sqlable().IsNotNull());
```

- Sqlable对象
> 常用开发中SQL中还有一些其他条件查询语法，不然：in，is null, is not null等，为了方便表达这些语义，因此将一些关键字语法
统一扩展到了Sqlable对象中。例如如下语法中，判断UserCode是否为null：

```csharp
QuickDAL<MyUser>.Context.GetEntityList(a=> a.UserCode <> "" || a.UserCode.Sqlable().IsNotNull());
```


***

## Excell导出
### **依赖DLL**
```
Mysoft.QuickCode.dll
```

### **引入依赖**

```csharp
using Mysoft.QuickCode.Office;
```

### **概览**

![导出](./docs/excel/files/概览.png)

### **常规导出**

- **常规导出（带格式）**

> 例如：数字格式化。

![导出](./docs/excel/files/常规导出（带格式）.png)

```csharp
public static void NoTemplateExport()
{
    Random rand = new Random(1000000);

    DataTable table = new DataTable();
    table.Columns.Add("姓名", typeof(string));
    table.Columns.Add("年龄", typeof(int));
    table.Columns.Add("资产", typeof(long));
    table.Columns.Add("备注", typeof(string));

    for (int i = 1; i <= 100; i++)
    {
        var row = table.NewRow();
        row["姓名"] = $"王富贵{i}";
        row["年龄"] = $"{rand.Next(20, 60)}";
        row["资产"] = $"{Math.Round(rand.Next(100000000, 999999999) * 100M, 2)}";
        row["备注"] = $"不知道为什么这么富{i}";

        table.Rows.Add(row);
    }

    var dataList = table.ToExcelCellList();

    //可以设置单元格的一些格式
    int nnColIndex = dataList.First().IndexOf(dataList.First().GetExcelCellByField("年龄"));  //获取年龄列的索引，通过GetExcelCellByField来按字段查找单元格。
    int zcColIndex = dataList.First().IndexOf(dataList.First().GetExcelCellByField("资产"));
    for (int i = 1; i < dataList.Count; i++)
    {
        var nn = dataList[i][nnColIndex];
        nn.DataType = "number";
        nn.DataFormat = "0";
        nn.Align = "right";

        var zc = dataList[i][zcColIndex];
        zc.DataType = "number";
        zc.DataFormat = "#,##0.00";
        zc.Align = "right";
        zc.Width = 200;
    }

    var workbook = ExcelCreator.ExportToExcel(null, dataList, null, new ExportExcelSetting()
    {
        Version = OfficeVersion.Office2007Plus
    });

    //保存到文件
    using (FileStream file = new FileStream(@"D:/QC导出测试/常规导出（带格式）.xlsx", FileMode.OpenOrCreate))
    {
        workbook.Write(file);
    }
}
```

- **常规导出（大标题、备注、样式）**

> 导出的表格中，可以自定义样式。

![导出](./docs/excel/files/常规导出（大标题、备注、样式）.png)

```csharp
public static void NoTemplateExport1()
{
    Random rand = new Random(1000000);

    DataTable table = new DataTable();
    table.Columns.Add("姓名", typeof(string));
    table.Columns.Add("年龄", typeof(int));
    table.Columns.Add("资产", typeof(long));
    table.Columns.Add("备注", typeof(string));

    for (int i = 1; i <= 100; i++)
    {
        var row = table.NewRow();
        row["姓名"] = $"王富贵{i}";
        row["年龄"] = $"{rand.Next(20, 60)}";
        row["资产"] = $"{Math.Round(rand.Next(100000000, 999999999) * 100M, 2)}";
        row["备注"] = $"不知道为什么这么富{i}";

        table.Rows.Add(row);
    }

    var dataList = table.ToExcelCellList();
    var maxColumn = dataList.First().Count;

    //大标题、备注、样式
    var title = new List<ExcelCell>();
    var memo = new List<ExcelCell>();
    for (int i = 0; i < maxColumn; i++)
    {
        title.Add(new ExcelCell()
        {
            IsTitle = true,   //标记为标题
            Height = 50,
            Text = "大标题"
        });

        memo.Add(new ExcelCell()
        {
            IsSummary = true,   //标记为摘要备注行
            Height = 50,
            FontColor = Mysoft.QuickCode.NPOI.HSSF.Util.HSSFColor.Red.Index,
            Text = @"
1.说明文字1
2.说明文字2
"
        });
    }

    //插入到导数列表
    dataList.Insert(0, memo);
    dataList.Insert(0, title);

    var workbook = ExcelCreator.ExportToExcel(null, dataList, null, new ExportExcelSetting()
    {
        Version = OfficeVersion.Office2007Plus,
        HeaderRowCount = 3    //因为加入了大标题、备注行，所以行头一共有3行了。
    });

    //保存到文件
    using (FileStream file = new FileStream(@"D:/QC导出测试/常规导出（大标题、备注、样式）.xlsx", FileMode.OpenOrCreate))
    {
        workbook.Write(file);
    }
}
```

- **常规导出（多级表头）**

![导出](./docs/excel/files/常规导出（多级表头）.png)

```csharp
public static void NoTemplateExport2()
{
    Random rand = new Random(1000000);

    DataTable table = new DataTable();
    table.Columns.Add("姓名", typeof(string));
    table.Columns.Add("年龄", typeof(int));
    table.Columns.Add("资产", typeof(long));
    table.Columns.Add("备注", typeof(string));

    for (int i = 1; i <= 100; i++)
    {
        var row = table.NewRow();
        row["姓名"] = $"王富贵{i}";
        row["年龄"] = $"{rand.Next(20, 60)}";
        row["资产"] = $"{Math.Round(rand.Next(100000000, 999999999) * 100M, 2)}";
        row["备注"] = $"不知道为什么这么富{i}";

        table.Rows.Add(row);
    }

    var dataList = table.ToExcelCellList();

    //多级表头
    var firstRow = new List<ExcelCell>();
    firstRow.Add(new ExcelCell()
    {
        Text = "个人信息"
    });
    firstRow.Add(new ExcelCell()
    {
        Text = "个人信息"
    });
    firstRow.Add(new ExcelCell()
    {
        Text = "资产"
    });
    firstRow.Add(new ExcelCell()
    {
        Text = "备注"
    });

    //插入合并头行到表头
    dataList.Insert(0, firstRow);

    var workbook = ExcelCreator.ExportToExcel(null, dataList, null, new ExportExcelSetting()
    {
        Version = OfficeVersion.Office2007Plus,
        HeaderRowCount = 2    //因为加入了合并行，所以标题为2行。
    });

    //保存到文件
    using (FileStream file = new FileStream(@"D:/QC导出测试/常规导出（多级表头）.xlsx", FileMode.OpenOrCreate))
    {
        workbook.Write(file);
    }
}
```

- **常规导出（追加多页签）**

> 导出的内容，需要分多个页签存放。

![导出](./docs/excel/files/常规导出（追加多页签）.png)

```csharp
public static void NoTemplateExport3()
{
    IWorkbook workbook = null;

    for (int p = 0; p < 5; p++)
    {
        Random rand = new Random(1000000);

        DataTable table = new DataTable();
        table.Columns.Add("姓名", typeof(string));
        table.Columns.Add("年龄", typeof(int));
        table.Columns.Add("资产", typeof(long));
        table.Columns.Add("备注", typeof(string));

        for (int i = 1; i <= 100; i++)
        {
            var row = table.NewRow();
            row["姓名"] = $"王富贵{i}";
            row["年龄"] = $"{rand.Next(20, 60)}";
            row["资产"] = $"{Math.Round(rand.Next(100000000, 999999999) * 100M, 2)}";
            row["备注"] = $"不知道为什么这么富{i}";

            table.Rows.Add(row);
        }

        var dataList = table.ToExcelCellList();

        workbook = ExcelCreator.ExportToExcel(workbook, dataList, null, new ExportExcelSetting()
        {
            Version = OfficeVersion.Office2007Plus
        },
        sheetName: $"页签{p}",
        createMode: CreateMode.AppendSheet);
    }

    //保存到文件
    if (File.Exists(@"D:/QC导出测试/常规导出（追加多页签）.xlsx"))
    {
        File.Delete(@"D:/QC导出测试/常规导出（追加多页签）.xlsx");
    }
    using (FileStream file = new FileStream(@"D:/QC导出测试/常规导出（追加多页签）.xlsx", FileMode.OpenOrCreate))
    {
        workbook.Write(file);
    }
}
```

- **常规导出（同页签多个表格XLS格式）**

> 多个数据表格，需要导出到同一个页签中全部展示出来。此功能当前只实现了xls格式版本的导出，还未实现xlsx高版本的兼容。

![导出](./docs/excel/files/常规导出（同页签多个表格XLS格式）.png)

```csharp
public static void NoTemplateExport4()
{
    var tabKvList = new List<KeyValuePair<string, DataTable>>();

    for (int p = 0; p < 5; p++)
    {
        Random rand = new Random(1000000);

        DataTable table = new DataTable();
        table.Columns.Add("姓名", typeof(string));
        table.Columns.Add("年龄", typeof(int));
        table.Columns.Add("资产", typeof(long));
        table.Columns.Add("备注", typeof(string));

        for (int i = 1; i <= 3; i++)
        {
            var row = table.NewRow();
            row["姓名"] = $"王富贵{i}";
            row["年龄"] = $"{rand.Next(20, 60)}";
            row["资产"] = $"{Math.Round(rand.Next(100000000, 999999999) * 100M, 2)}";
            row["备注"] = $"不知道为什么这么富{i}";

            table.Rows.Add(row);
        }

        tabKvList.Add(new KeyValuePair<string, DataTable>($"表格{p}", table));
    }

    var dst = new DataSetTool(tabKvList);
    IWorkbook workbook = dst.GetWorkbook();

    //保存到文件
    using (FileStream file = new FileStream(@"D:/QC导出测试/常规导出（同页签多个表格XLS格式）.xls", FileMode.OpenOrCreate))
    {
        workbook.Write(file);
    }
}
```


### **高级导出**

- **高级导出（下拉框及联动）**

> 下拉框输入控制，下拉框联动场景。内置名称管理器实现。建议必须使用xlsx格式的导出，低版本Excel中会有255长度字符的限制，会导致功能失效。

![导出](./docs/excel/files/高级导出（下拉框及联动）.png)

```csharp
public void Export()
{
    string fileName = @"D:/QC导出测试/高级导出（下拉框及联动）.xlsx";
    if (File.Exists(fileName))
    {
        File.Delete(fileName);
    }


    IWorkbook workbook = new XSSFWorkbook();


    #region 创建下拉框备选项和名称管理器

    //普通下拉框数据表格
    List<List<ExcelCell>> dpd1List = new List<List<ExcelCell>>();
    var dpl1Name = "备选表格1";
    for (int i = 0; i < 200; i++)
    {
        //构建模拟下拉框备选项
        var row = new List<ExcelCell>();
        row.Add(new ExcelCell()
        {
            Field = $"Field1",
            Text = $"下拉选项{i}"
        });
        dpd1List.Add(row);
    }
    ExcelCreator.NumberToAZRender(dpd1List);    //渲染单元格在EXCEL中的坐标，只有渲染后才能获取XY值

    //根据定义的数据表格创建名称管理器表单页
    workbook = ExcelCreator.ExportToExcel(workbook, dpd1List, fileName, new ExportExcelSetting()
    {
        HeaderRowCount = 0, //0-表示 List<List<ExcelCell>> 这个数据集中全部是数据，不包含列头单元格数据
        Version = OfficeVersion.Office2007Plus,
        //创建名称管理器，可用于普通下拉框和联动下拉框
        NameManager = new Dictionary<string, string>() {
            {
                "Name1",    //固定的名称可以随意命名得特殊一点，因为名称管理器中名称全局唯一不能重复，所以固定的下拉框尽量避免与动态生成的名称产生重复。
                $"{dpl1Name}!{dpd1List.First().First().AbsoluteXY}:{dpd1List.Last().First().AbsoluteXY}"    //通过XY值获取名称管理器取值范围
            }
        },
        SheetHidden = 1 //隐藏页签
    }, null, dpl1Name, CreateMode.AppendSheet);

    #endregion


    #region 创建模拟名称管理器数据页签和对应的名称管理器

    List<KeyValuePair<string, List<string>>> nameList = new List<KeyValuePair<string, List<string>>>();

    //注意名称管理器的名称不支持数字字符，应该只使用中英文
    var kv1 = new KeyValuePair<string, List<string>>("大分类", new List<string>() { "AA", "AB", "AC", "AD", "AE" });
    var kv2 = new KeyValuePair<string, List<string>>("AA", new List<string>() { "B1", "B2", "B3", "B4" });
    var kv3 = new KeyValuePair<string, List<string>>("AB", new List<string>() { "C1", "C2", "C3", "C4", "C5" });
    var kv4 = new KeyValuePair<string, List<string>>("AC", new List<string>() { "D1", "D2", "D3" });

    nameList.Add(kv1);
    nameList.Add(kv2);
    nameList.Add(kv3);
    nameList.Add(kv4);

    //先把以上模拟数据转存到EXCEL表格中
    List<List<ExcelCell>> nameRows = new List<List<ExcelCell>>();
    int maxColumns = nameList.Max(m => m.Value.Count());    //取最多的记录项作为EXCEL中的最大列数
    for (int i = 0; i < nameList.Count; i++)
    {
        var names = nameList[i];
        var cells = new List<ExcelCell>();

        //键，也就是第一列加入到表格
        cells.Add(new ExcelCell()
        {
            Field = "field",    //字段名称随意，列字段名称不重复就行了，类似DataTable列名称
            Text = names.Key
        });

        //先初始化后面的列数据
        for (int j = 0; j < maxColumns; j++)
        {
            cells.Add(new ExcelCell()
            {
                Field = $"field{j}" //字段名称随意，列字段名称不重复就行了，类似DataTable列名称
            });
        }

        //将有值的名称值写入单元格
        for (int j = 0; j < names.Value.Count; j++)
        {
            cells[j + 1].Text = names.Value[j];
        }

        //添加到数据行
        nameRows.Add(cells);
    }
    ExcelCreator.NumberToAZRender(nameRows);

    //构建名称管理器变量
    var nameManager = new Dictionary<string, string>();
    var nameSheetName1 = "名称页签1";   //Excel各页签名称不能重复
    foreach (var row in nameRows)
    {
        var firstEmptyIndex = row.FindLastIndex(m => string.IsNullOrEmpty(m.Text) == false); //找到最后一个不为空字符串的单元格为可用名称取值范围
        var name = row.First(); //第一个记录为名称
        var subList = row.Skip(1).Take(firstEmptyIndex);    //取非空单元格的数据为子类，因为本身已经忽略的第一列，所以 firstEmptyIndex 就是需要截取的记录个数
        if (subList.Count() > 0)
        {
            nameManager.Add(
                name.Text,    //名称管理器中的名称：全局唯一，不能重复
                $"{nameSheetName1}!{subList.First().AbsoluteXY}:{subList.Last().AbsoluteXY}"    //名称取值引用单元格范围
            );
        }
    }

    //输出名称管理器页签
    workbook = ExcelCreator.ExportToExcel(workbook, nameRows, fileName,
        new ExportExcelSetting()
        {
            HeaderRowCount = 0,
            Version = OfficeVersion.Office2007Plus,
            NameManager = nameManager,
            SheetHidden = 1 //隐藏页签
        },
        null,
        nameSheetName1,
        CreateMode.AppendSheet);

    #endregion


    #region 导出业务数据，导出过程中使用下拉框页签的数据或名称管理器

    //业务数据表格
    List<List<ExcelCell>> lstTable = new List<List<ExcelCell>>();
    var dataSheetName = "主数据";
    //测试3W行数据性能
    for (int i = 0; i < 50; i++)
    {
        List<ExcelCell> row = new List<ExcelCell>();
        int colNums = 100;  //生成100列数据
        if (i == 0)
        {
            //第一行假设为列表头
            for (int j = 0; j < colNums; j++)
            {
                row.Add(new ExcelCell()
                {
                    Field = $"字段{j}",
                    Text = $"字段{j}"
                });
            }
        }
        else
        {
            //数据行
            for (int j = 0; j < colNums; j++)
            {
                row.Add(new ExcelCell()
                {
                    Field = $"字段{j}",
                    Text = (10 * i + j).ToString()
});
            }
        }
        lstTable.Add(row);
    }
    ExcelCreator.NumberToAZRender(dpd1List);    //渲染单元格在EXCEL中的坐标，只有渲染后才能获取XY值，如果不需要从代码中自己获取坐标值，则不需要提前调用，导出逻辑会自动调用
    var settings = GetCellSetting();    //导出字段级配置项
    workbook = ExcelCreator.ExportToExcel(workbook, lstTable, fileName, new ExportExcelSetting()
    {
        HeaderRowCount = 1,
        ECellable = true,   //高级导出才支持公式设置渲染
        ProtectSheet = true, //不启用表格密码保护，启用保护会导致部分内容无法编辑
        SheetIndex = 0,   //创建的表格放到第一页
        Version = OfficeVersion.Office2007Plus
    }, settings, dataSheetName, CreateMode.AppendSheet);

    #endregion


    //保存到文件
    using (FileStream file = new FileStream(fileName, FileMode.OpenOrCreate))
    {
        workbook.Write(file);
    }

}

/// <summary>
/// 导出公式配置项
/// </summary>
/// <returns></returns>
public Dictionary<string, Func<object, List<ExcelCell>, ExcelCell, int, ExcelCellSetting>> GetCellSetting()
{
    //列公式配置字典
    Dictionary<string, Func<object, List<ExcelCell>, ExcelCell, int, ExcelCellSetting>> dict = new Dictionary<string, Func<object, List<ExcelCell>, ExcelCell, int, ExcelCellSetting>>();
    var dpdSetting = new DropDownSetting()
    {
        NameOrReference = "Name1"   //普通下拉框，对应的名称管理器里的名称
    };

    //配置渲染过程处理逻辑
    //1.普通下拉框例子
    dict.Add("字段3", //添加处理逻辑的列，就是ExcelCell的Field字段值对应的列。这里假如给“字段3”添加导出时的处理逻辑
        (value, row, column, rowIndex) =>
        {
            column.BackgroundColor = HSSFColor.LightYellow.Index;   //可编辑字段颜色可以设置为特殊颜色
                                                                    //设置为普通下拉框配置项
            return new ExcelCellSetting
            {
                DPDSetting = dpdSetting
            };
        });

    //2.联动下拉框例子
    dict.Add("字段5", //例如“字段5”为名称管理器中的大分类
        (value, row, column, rowIndex) =>
        {
            column.BackgroundColor = HSSFColor.LightYellow.Index;   //可编辑字段颜色可以设置为特殊颜色
            //设置为普通下拉框配置项
            return new ExcelCellSetting
            {
                DPDSetting = new DropDownSetting()
                {
                    NameOrReference = "大分类" //名称管理器中的大分类的名称
                }
            };
        });
    dict.Add("字段6", //例如“字段6”需要根据“字段5”的选项值来联动
        (value, row, column, rowIndex) =>
        {
            column.BackgroundColor = HSSFColor.LightYellow.Index;   //可编辑字段颜色可以设置为特殊颜色

            //找到联动依赖单元格
            var depend = row.First(m => m.Field == "字段5"); //字段依赖当前行的“字段5”
            return new ExcelCellSetting
            {
                DPDSetting = new DropDownSetting()
                {
                    IsIndirect = true,    //true标记为名称管理器联动模式
                    NameOrReference = $"INDIRECT({depend.AbsoluteXY})"  //联动依赖公式
                }
            };
        });

    return dict;
}
```

- **高级导出（规则计算公式）**

> 某一列或某一行统一设置公式或只读等。

![导出](./docs/excel/files/高级导出（规则计算公式）.png)

```csharp
public void Export()
{
    string fileName = @"D:/QC导出测试/高级导出（规则计算公式）.xlsx";
    if (File.Exists(fileName))
    {
        File.Delete(fileName);
    }


    Random rand = new Random(1000000);

    DataTable table = new DataTable();
    table.Columns.Add("房号", typeof(string));
    table.Columns.Add("面积", typeof(decimal));
    table.Columns.Add("单价", typeof(decimal));
    table.Columns.Add("总价", typeof(decimal));
    table.Columns.Add("备注", typeof(string));

    for (int i = 1; i <= 100; i++)
    {
        var row = table.NewRow();
        row["房号"] = $"F2021-{i}";
        row["面积"] = $"{rand.Next(90, 140)}";
        row["单价"] = $"{rand.Next(20000, 50000)}";
        row["总价"] = 0M;
        row["备注"] = $"买买买{i}";

        table.Rows.Add(row);
    }

    var dataList = table.ToExcelCellList();

    //可以设置单元格的一些格式
    int fhColIndex = dataList.First().IndexOf(dataList.First().GetExcelCellByField("房号"));
    int mjColIndex = dataList.First().IndexOf(dataList.First().GetExcelCellByField("面积"));  //通过GetExcelCellByField来按字段查找单元格。
    int djColIndex = dataList.First().IndexOf(dataList.First().GetExcelCellByField("单价"));
    for (int i = 1; i < dataList.Count; i++)
    {
        //锁定房号
        var fh = dataList[i][fhColIndex];
        fh.Settings.Locked = true;

        //数字格式
        var nn = dataList[i][mjColIndex];
        nn.DataType = "number";
        nn.DataFormat = "0";
        nn.Align = "right";

        var zc = dataList[i][djColIndex];
        zc.DataType = "number";
        zc.DataFormat = "#,##0.00";
        zc.Align = "right";
    }

    var workbook = ExcelCreator.ExportToExcel(null, dataList, null, new ExportExcelSetting()
    {
        Version = OfficeVersion.Office2007Plus,
        ECellable = true  //启用高级导出支持，启用后支持公式和表格保护锁定等功能
    }, delegateCellSettings: GetExportFormula());

    //保存到文件
    using (FileStream file = new FileStream(fileName, FileMode.OpenOrCreate))
    {
        workbook.Write(file);
    }
}

/// <summary>
/// 导出公式配置项
/// </summary>
/// <returns></returns>
public Dictionary<string, Func<object, List<ExcelCell>, ExcelCell, int, ExcelCellSetting>> GetExportFormula()
{
    //列公式配置字典
    Dictionary<string, Func<object, List<ExcelCell>, ExcelCell, int, ExcelCellSetting>> dict = new Dictionary<string, Func<object, List<ExcelCell>, ExcelCell, int, ExcelCellSetting>>();

    //配置渲染过程处理逻辑
    dict.Add("面积", //面积锁定
        (value, row, column, rowIndex) =>
        {
            return new ExcelCellSetting
            {
                Locked = false
            };
        });

    dict.Add("总价", //为总价字段配置公式
        (value, row, column, rowIndex) =>
        {
            //配置格式
            column.DataFormat = "#,##0.00";
            column.DataType = "number";
            column.Align = "right";

            //配置公式
            //总价=单价*面积
            var dj = row.GetExcelCellByField("单价").XY;
            var mj = row.GetExcelCellByField("面积").XY;

            return new ExcelCellSetting
            {
                Formula = $"{dj}*{mj}",    //Excel的公式字符串：单价单元格*面积单元格
                Locked = true //计算单元格锁定
            };
        });

    return dict;
}
```

- **高级导出（不规则计算公式）**

> 只有某些特定位置的单元格需要设置公式、设置只读。需要快速查找任意位置的单元格来动态设置公式等。

![导出](./docs/excel/files/高级导出（不规则计算公式）.png)

```csharp
public void Export2()
{
    string fileName = @"D:/QC导出测试/高级导出（不规则计算公式）.xlsx";
    if (File.Exists(fileName))
    {
        File.Delete(fileName);
    }


    Random rand = new Random(1000000);

    DataTable table = new DataTable();
    table.Columns.Add("房号", typeof(string));
    table.Columns.Add("面积", typeof(decimal));
    table.Columns.Add("单价", typeof(decimal));
    table.Columns.Add("总价", typeof(decimal));
    table.Columns.Add("备注", typeof(string));

    for (int i = 1; i <= 100; i++)
    {
        var row = table.NewRow();
        row["房号"] = $"F2021-{i}";
        row["面积"] = $"{rand.Next(90, 140)}";
        row["单价"] = $"{rand.Next(20000, 50000)}";
        row["总价"] = 0M;
        row["备注"] = $"买买买{i}";

        table.Rows.Add(row);
    }

    var dataList = table.ToExcelCellList();
    var maxColumn = dataList.First().Count;

    //可以设置单元格的一些格式
    int fhColIndex = dataList.First().IndexOf(dataList.First().GetExcelCellByField("房号"));
    int mjColIndex = dataList.First().IndexOf(dataList.First().GetExcelCellByField("面积"));  //通过GetExcelCellByField来按字段查找单元格。
    int djColIndex = dataList.First().IndexOf(dataList.First().GetExcelCellByField("单价"));
    for (int i = 1; i < dataList.Count; i++)
    {
        //锁定房号
        var fh = dataList[i][fhColIndex];
        fh.Settings.Locked = true;

        var nn = dataList[i][mjColIndex];
        nn.DataType = "number";
        nn.DataFormat = "0";
        nn.Align = "right";

        var zc = dataList[i][djColIndex];
        zc.DataType = "number";
        zc.DataFormat = "#,##0.00";
        zc.Align = "right";
    }

    //追加单价均价、总价均价行，并配置计算公式
    var avgRow = new List<ExcelCell>();
    var totalRow = new List<ExcelCell>();
    for (int i = 0; i < maxColumn; i++)
    {
        if (i == 0)
        {
            avgRow.Add(new ExcelCell()
            {
                Field = table.Columns[i].ColumnName,
                Text = "单价均价",
                Settings = new ExcelCellSetting()
                {
                    Locked = true
                }
            });

            totalRow.Add(new ExcelCell()
            {
                Field = table.Columns[i].ColumnName,
                Text = "总价均价",
                Settings = new ExcelCellSetting()
                {
                    Locked = true
                }
            });
        }
        else
        {
            //补全单元格
            avgRow.Add(new ExcelCell()
            {
                Field = table.Columns[i].ColumnName
            });
            totalRow.Add(new ExcelCell()
            {
                Field = table.Columns[i].ColumnName
            });
        }
    }
    dataList.Add(avgRow);
    dataList.Add(totalRow);

    var workbook = ExcelCreator.ExportToExcel(null, dataList, null, new ExportExcelSetting()
    {
        Version = OfficeVersion.Office2007Plus,
        ECellable = true  //启用高级导出支持，启用后支持公式和表格保护锁定等功能
    }, delegateCellSettings: GetExport2Formula(dataList));

    //保存到文件
    using (FileStream file = new FileStream(fileName, FileMode.OpenOrCreate))
    {
        workbook.Write(file);
    }
}

/// <summary>
/// 导出公式配置项
/// </summary>
/// <returns></returns>
public Dictionary<string, Func<object, List<ExcelCell>, ExcelCell, int, ExcelCellSetting>> GetExport2Formula(List<List<ExcelCell>> dataList)
{
    //列公式配置字典
    Dictionary<string, Func<object, List<ExcelCell>, ExcelCell, int, ExcelCellSetting>> dict = new Dictionary<string, Func<object, List<ExcelCell>, ExcelCell, int, ExcelCellSetting>>();

    //配置渲染过程处理逻辑
    dict.Add("面积", //面积锁定
        (value, row, column, rowIndex) =>
        {
            //配置不规则定点公式
            if (row.GetExcelCellByField("房号").TrimText == "单价均价")
            {
                column.DataType = "number";
                column.Align = "right";
                column.DataFormat = "#,##0.00";
                return new ExcelCellSetting
                {
                    Locked = true,
                    Formula = $"AVERAGE({dataList[1].GetExcelCellByField("单价").XY}:{dataList[dataList.Count - 2].GetExcelCellByField("单价").XY})"
                };
            }
            else if (row.GetExcelCellByField("房号").TrimText == "总价均价")
            {
                column.DataType = "number";
                column.Align = "right";
                column.DataFormat = "#,##0.00";
                return new ExcelCellSetting
                {
                    Locked = true,
                    Formula = $"AVERAGE({dataList[1].GetExcelCellByField("总价").XY}:{dataList[dataList.Count - 2].GetExcelCellByField("总价").XY})"
                };
            }
            else
            {
                return new ExcelCellSetting
                {
                    Locked = false
                };
            }
        });

    dict.Add("总价", //为总价字段配置公式
        (value, row, column, rowIndex) =>
        {
            //配置格式
            column.DataFormat = "#,##0.00";
            column.DataType = "number";
            column.Align = "right";

            //配置公式
            //总价=单价*面积
            var dj = row.GetExcelCellByField("单价").XY;
            var mj = row.GetExcelCellByField("面积").XY;

            return new ExcelCellSetting
            {
                Formula = $"{dj}*{mj}",    //Excel的公式字符串：单价单元格*面积单元格
                Locked = true //计算单元格锁定
            };
        });

    dict.Add("单价", //单号房间禁止调单价
        (value, row, column, rowIndex) =>
        {
            if (column.TrimText.EndsWith("1") || column.TrimText.EndsWith("3") || column.TrimText.EndsWith("5") || column.TrimText.EndsWith("7") || column.TrimText.EndsWith("9"))
            {
                return new ExcelCellSetting
                {
                    Locked = true
                };
            }
            else
            {
                return new ExcelCellSetting
                {
                    Locked = false
                };
            }
        });

    return dict;
}
```

## EXCEL导出API查询