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); }