zszq-trs/UnitTestProject/BasicTests/ExcelHelperTests.cs

using System.Drawing;
using static YLErp.Commons.ExcelHelper;

namespace YLErp.BasicTests
{
    [TestClass]
    public class ExcelHelperTests
    {
        class ExportTestModel
        {
            public string Name { get; set; }

            public int Age { get; set; }

            public string phone { get; set; }

            public string Address { get; set; }
        }

        [TestMethod]
        public void ListToExcelTest()
        {
            var list = new List<ExportTestModel>
            {
                new ExportTestModel() { Name = "大林", Age = 26, phone = "13824412561", Address = "云南省曲靖市少林东街32号甲" },
                new ExportTestModel() { Name = "东近", Age = 20, phone = "13725526482", Address = "云南省曲靖市少林东街32号丁" },
                new ExportTestModel() { Name = "春妮", Age = 25, phone = "13062405400" }
            };
            var columnList = new List<DataColumnModel>
            {
                new DataColumnModel("姓名", "Name") { Style = new ExcelStyle() { BorderColor = Color.Red, BackgroundColor = Color.Green, ForegroundColor = Color.Red } },
                new DataColumnModel("年龄", "Age", (cv, obj) =>
                {
                    if (!(cv is int value))
                    {
                        return "未知";
                    }
                    if (value < 12)
                    {
                        return "儿童";
                    }
                    return "成人";
                })
                { Style = new ExcelStyle() { BorderColor = Color.OrangeRed, BackgroundColor = Color.Green, ForegroundColor = Color.Yellow } },
                new DataColumnModel("联系电话", "phone", (cv, obj) =>
                {
                    if (cv is string value)
                    {
                        cv = value.Remove(3, 4).Insert(3, "****");
                    }
                    return cv;
                })
                { Style = new ExcelStyle() { BorderColor = Color.Orange, BackgroundColor = Color.Orange, ForegroundColor = Color.Red } },
                new DataColumnModel("详细地址", (obj) =>
                {
                    if (!(obj is ExportTestModel value))
                    {
                        return "未知";
                    }
                    return string.IsNullOrWhiteSpace(value.Address) ? "地址不详" : "";
                })
                { Style = new ExcelStyle() { BorderColor = Color.Red, BackgroundColor = Color.DarkOrange, ForegroundColor = Color.Orange } }
            };
            var helper = new Commons.ExcelHelper();

            helper.ListToExcel<ExportTestModel>(
                columnList.ToArray(),
                list,
                "Sheet1",
                true,
                out var buffer,
                false,
                new DataGroupModel[] {
                    new DataGroupModel {
                        Caption = "组1",
                        StartColumnIndex = 0,
                        Length = 2 ,
                        Style = new ExcelStyle(){
                             BackgroundColor = Color.Orange,
                              BorderColor = Color.Red,
                               ForegroundColor = Color.Green
                        }
                    },
                    new DataGroupModel {
                        Caption = "组2",
                        StartColumnIndex = 3,
                        Length = 1 ,
                        Style = new ExcelStyle(){
                             BackgroundColor = Color.Green,
                              BorderColor = Color.Red,
                               ForegroundColor = Color.Orange
                        }}
                });
            System.IO.File.WriteAllBytes(@"C:\Users\shiyi\Work\sss.xlsx", buffer);

            //string fileName = @"C:\Users\shiyi\Work\sss.xls";
            //Commons.ExcelHelper helper1 = new Commons.ExcelHelper(Commons.ExcelHelper.GetVersion(fileName));

            //helper1.ListToExcel<ExportTestModel>(columnList.ToArray(), list, "Sheet1", true, out var buffer1);
            //System.IO.File.WriteAllBytes(fileName, buffer1);

        }
    }
}