public void Write()
{
var array = new string[] { };
var name = array.GetType().Name;
var helper = new DefaultExcelBulkEditHelper();
var model = new ExcelDataModel <CategroyExcelBulkRow, ProductExcelBulkRow>();
using (var stream = new FileStream($"{DateTime.Now.ToUnixStampDateTime()}.xlsx", FileMode.Create)) {
using (var database = factory.GenerateDatabase()) {
model.Data1 = database.SqlQuery <CategroyExcelBulkRow>("SELECT * FROM [dbo].[Category] (NOLOCK)").ToArray();
model.Data2 = database.SqlQuery <ProductExcelBulkRow>("SELECT * FROM [dbo].[Product] (NOLOCK)").ToArray();
model.Data2.ToList().ForEach(ctx => {
var settings = ctx.Settings.DeserializeToObject <ProductSettings>();
ctx.Options = settings?.Specifications.SerializeToJson();
ctx.Banners = string.Join(",",
(settings?.Banners.Select(x => x.Replace("https://www.yourc.club/images/products/", string.Empty))) ??
new string[] { ctx.ImageUrl.Replace("https://www.yourc.club/images/products/", string.Empty) });
ctx.Size = settings?.Specifications[0].SerializeToJson();
ctx.Temperature = settings?.Specifications[1].SerializeToJson();
ctx.ImageUrl = ctx.ImageUrl?.Replace("https://www.yourc.club/images/products/", string.Empty);
ctx.Banners = string.IsNullOrEmpty(ctx.Banners) ? ctx.ImageUrl : ctx.Banners;
});
}
model.DataMark = new ExcelDataMark()
{
DateTime = DateTime.Now.ToUnixStampDateTime(),
Additional = new Dictionary <string, string>(),
Version = "1.0"
};
helper.Write(stream, model);
stream.Flush();
}
}
public static void Go(int cellCount, object val, ExcelDataModel excelModel)
{
switch (cellCount) //Excel columns should be known
{
case 1:
excelModel.Id = Convert.ToInt32(val);
break;
case 2:
excelModel.Surname = val.ToString();
break;
case 3:
excelModel.FirstName = val.ToString();
break;
case 4:
excelModel.Cell = Convert.ToInt64(FormatCellNumber.ToInternational(val));
break;
case 5:
excelModel.Email = val.ToString();
break;
case 6:
if (val != null)
{
if (val.ToString() != "")
{
excelModel.StartDate = DateTime.ParseExact(val.ToString(), "dd/MM/yyyy", System.Globalization.CultureInfo.CurrentCulture);
}
}
break;
}
}
public void Write <T1, T2>(Stream stream, ExcelDataModel <T1, T2> model)
where T1 : ExcelBulkEditRow
where T2 : ExcelBulkEditRow
{
using (var package = new ExcelPackage(stream)) {
Write(package, typeof(T1), model.DataMark, model.Data1.Select(o => o as object).ToArray());
Write(package, typeof(T2), model.DataMark, model.Data2.Select(o => o as object).ToArray());
package.SaveAs(stream);
}
}
public void Write <T>(Stream stream, ExcelDataModel <T> model)
where T : ExcelBulkEditRow
{
Guard.ArgumentNotNull(model, "model");
Guard.ArgumentNotNull(model.DataMark, "model.DataMark");
using (var package = new ExcelPackage(stream)) {
Write(package, typeof(T), model.DataMark, model.Data.Select(o => o as object).ToArray());
package.SaveAs(stream);
}
}
/// <summary>
/// 处理项目数据
/// </summary>
/// <param name="sheet"></param>
/// <param name="excelId"></param>
private void ProcessDatas(Worksheet sheet, int excelId)
{
var excelDatas = new ExcelDataModel()
{
Datas = new List <XiangMuForExcelDataModel>()
};
for (int i = 3; i < sheet.Cells.MaxDataColumn + 200; i++)
{
if (string.IsNullOrEmpty(GetCellValue(sheet, 6, i)))
{
break;
}
var xiangMuForExcelDataModel = new XiangMuForExcelDataModel()
{
ShiYeBuName = GetCellValue(sheet, 2, i),
PianQuName = GetCellValue(sheet, 1, i),
XingZhiName = string.IsNullOrEmpty(GetCellValue(sheet, 3, i)) ? GetCellValue(sheet, 4, i) : GetCellValue(sheet, 3, i),
XiangMuName = GetCellValue(sheet, 6, i),
QiJianTypeName = GetCellValue(sheet, 5, i),
Datas = new List <DataDetailForExcelDataModel>()
};
for (int j = 8; j < sheet.Cells.MaxDataRow + 200; j++)
{
if (string.IsNullOrEmpty(GetCellValue(sheet, j, 0)))
{
break;
}
var dataDetailForExcelDataModel = new DataDetailForExcelDataModel()
{
AccountCode = GetCellValue(sheet, j, 0),
Data = string.IsNullOrEmpty(GetCellValue(sheet, j, i)) ? 0.0 : Convert.ToDouble(GetCellValue(sheet, j, i))
};
xiangMuForExcelDataModel.Datas.Add(dataDetailForExcelDataModel);
}
excelDatas.Datas.Add(xiangMuForExcelDataModel);
}
foreach (var data in excelDatas.Datas)
{
if (string.IsNullOrEmpty(data.ShiYeBuName))
{
data.ShiYeBuName = data.XiangMuName;
}
}
ProcessXiangMu(excelDatas.Datas, excelId);
var financialDataModels = new List <FinancialDataModel>();
foreach (var excelXiangMuData in excelDatas.Datas)
{
foreach (var detailData in excelXiangMuData.Datas)
{
var financialDataModel = new FinancialDataModel()
{
ExcelRecordId = excelId,
XiangMuIdId = excelXiangMuData.XiangMuId,
XingZhiId = excelXiangMuData.XingZhiId,
PianQuId = excelXiangMuData.PianQuId,
ShiYeBuId = excelXiangMuData.ShiYeBuId,
AccountCode = detailData.AccountCode,
QiJianTypeId = excelXiangMuData.QiJianTypeName.Contains("年") ? 1 : 2,
Data = detailData.Data
};
financialDataModels.Add(financialDataModel);
}
}
_financialDataBll.BatchInsertFinancialData(financialDataModels);
}
