//如果数据量不大，可采用LiteDataHelper便捷导出；可自定义Sheet名称，默认Sheet1
var excelInfo = LiteDataHelper.ExportToWebDown(users,fileName: $"用户数据-{DateTime.Now.ToString("yyyyMMddHHmm")}");
//保存物理文件，默认位置为程序运行目录；可自定义Sheet名称，默认Sheet1
var excelInfo = LiteDataHelper.ExportToDisk(users,fileName: $"用户数据-{DateTime.Now.ToString("yyyyMMddHHmm")}");
//获取安全的Sheet名称
var sheetName = GetSafeSheetName(string sheetName);
//是否是安全的Sheet名称
var isSafeSheetName = IsSafeSheetName(string sheetName)

/// <summary>
/// 非大批量数据便捷导出（Web）
/// </summary>
/// <returns></returns>
public IActionResult LiteDataExport()
{
    //此方式对象全部在内存中，故数据量大的时候会占用内存，适合数据量不大使用；大数据量不占用内存的请采用AppendData方式
    var users = Enumerable.Range(1, 1000).Select(index => new UserExportDto { Account = $"2021-{index}", Name = $"测试用户-{index}", IsMan = true, IsConfirm = true }).ToList();
    var excelInfo = LiteDataHelper.ExportToWebDown(users, fileName: "用户数据");
    return File(excelInfo.Stream, excelInfo.WebContentType, excelInfo.FileName);
}

public class UserDto
{
	[ExcelKit(Desc = "账号", Width = 20, IsIgnore = false, Sort = 20, Align = TextAlign.Right, FontColor = DefineColor.LightBlue)]
	public string Account { get; set; }

	[ExcelKit(Desc = "昵称", Width = 50, Sort = 10, FontColor = DefineColor.Rose, ForegroundColor = DefineColor.LemonChiffon)]
	public string Name { get; set; }
	
	[ExcelKit(Desc = "金额", Width = 20, Sort = 10, Converter = typeof(DecimalPointDigitConverter), ConverterParam = 2)]
	public decimal Money { get; set; } = 20;
	
	[ExcelKit(Desc = "是否确认", Width = 20, Sort = 30, Converter = typeof(BoolConverter), ConverterParam = "√|×")]
	public bool? IsConfirm { get; set; }

	[ExcelKit(Desc = "性别", Width = 20, Sort = 30, Converter = typeof(BoolConverter), ConverterParam = "男|女")]
	public bool? IsMan { get; set; }

	[ExcelKit(Desc = "创建时间", Width = 50, Sort = 10, Converter = typeof(DateTimeFmtConverter), ConverterParam = "yyyy-MM-dd")]
	public DateTime CreateDate { get; set; } = DateTime.Now;
}


using (var context = ContextFactory.GetWriteContext("测试导出文件"))
{
    Parallel.For(1, 4, index =>
    {
       //并发导出时切记一个Sheet一个处理线程
       var sheet = context.CrateSheet<UserDto>($"Sheet{index}");

       for (int i = 0; i < 1000000; i++)
       {
           sheet.AppendData<UserDto>($"Sheet{index}", new UserDto { Account = $"{index}-{i}-2010211", Name = $"{index}-{i}-用户", CreateDate = DateTime.Now, Money = Convert.ToDouble(i), IsConfirm = i % 2 == 0, IsMan = i % 2 == 0  });
       }
    });

    filePath = context.Save();
    Console.WriteLine($"文件路径：{filePath}");
}

using (var context = ContextFactory.GetWriteContext("测试导出文件"))
{
    var sheet = context.CrateSheet("Sheet1", new List<ExcelKitAttribute>()
    {
        new ExcelKitAttribute(){ Code = "Account", Desc = "账号",Width=60 },
        new ExcelKitAttribute(){ Code = "Name", Desc = "昵称" }
    }, 5);

    for (int i = 0; i < 10; i++)
    {
        sheet.AppendData("Sheet1", new Dictionary<string, object>()
        {
            {"Account", $"{i}-2010211" }, {"Name", $"{i}-用户用户" }
        });
    }

    filePath = context.Save();
    Console.WriteLine($"文件路径：{filePath}");
}

//sheetIndex为Sheet索引(从1开始)，rowLine为行号(从1开始)，更多使用请查看定义
var headers = LiteDataHelper.ReadOneRow(filePath: "用户数据.xlsx", sheetIndex: 1, rowLine: 1);
Console.WriteLine($"表头为：{string.Join("  ", headers)}");

var context = ContextFactory.GetReadContext();
context.ReadRows("测试导出文件.xlsx", new ReadRowsOptions()
{
	RowData = rowdata =>
	{
		Console.WriteLine(JsonConvert.SerializeObject(rowdata));
	}
});

var context = ContextFactory.GetReadContext();
context.ReadRows("测试导出文件.xlsx", new ReadRowsOptions()
{
	context.ReadRows("测试导出文件.xlsx", new ReadRowsOptions()
	{
		ReadWay = ReadWay.SheetName,
		RowData = rowdata =>
		{
			Console.WriteLine(JsonConvert.SerializeObject(rowdata));
		}
	});
});

//FailData中的odata为Excel中的原始数据，failinfo为错误信息
var context = ContextFactory.GetReadContext();
context.ReadSheet<UserDto>("测试导出文件.xlsx", new ReadSheetOptions<UserDto>()
{
	SucData = (rowdata, rowindex) =>
	{
		Console.WriteLine(JsonConvert.SerializeObject(rowdata));
	},
	FailData = (odata, failinfo) =>
	{
		Console.WriteLine($"读取失败，{failinfo.FirstOrDefault().errorMsg}");
	}
});

var context = ContextFactory.GetReadContext();
context.ReadSheet("测试导出文件.xlsx", new ReadSheetDicOptions()
{
	DataEndRow = 10,
	ExcelFields = new (string field, ColumnType type, bool allowNull)[]
	{
		("账号",ColumnType.String,false)),("昵称",ColumnType.String,false))
	},
	SucData = (rowdata, rowindex) =>
	{
		Console.WriteLine(JsonConvert.SerializeObject(rowdata));
	}
});

//1.指定Sheet索引(从1开始)读取
var count1 = ContextFactory.GetReadContext().ReadSheetRowsCount("用户数据.xlsx", new ReadSheetRowsCountOptions()
{
      //可以不指定SheetIndex，默认就为1
      SheetIndex = 1,
      //可以不指定，默认为释放，当需要多次读取时，可指定不释放传false
     //比如对于反馈进度的，先读取总行数，再读取内容
     IsDisposeStream = true,
});
Console.WriteLine($"指定Sheet索引为1读取后的总行数为：{count1}");

//2.指定Sheet名称读取
var count2 = ContextFactory.GetReadContext().ReadSheetRowsCount("用户数据.xlsx", new ReadSheetRowsCountOptions()
{
     //可以不指定SheetName，默认为Sheet1
     SheetName = "Sheet2",
     //可以不指定，默认为释放，当需要多次读取时，可指定不释放传false
    //比如对于反馈进度的，先读取总行数，再读取内容
    IsDisposeStream = true,
});
Console.WriteLine($"指定Sheet名称为Sheet2读取后的总行数为：{count2}");