/// <summary> /// GetExcelConfigurationMapping /// </summary> /// <typeparam name="TEntity">TEntity</typeparam> /// <returns>IExcelConfiguration</returns> public static ExcelConfiguration <TEntity> GetExcelConfigurationMapping <TEntity>() { var type = typeof(TEntity); var excelConfiguration = new ExcelConfiguration <TEntity> { SheetSettings = new[] { type.GetCustomAttribute <SheetAttribute>()?.SheetSetting ?? new SheetSetting() }, FilterSetting = type.GetCustomAttribute <FilterAttribute>()?.FilterSeting, FreezeSettings = type.GetCustomAttributes <FreezeAttribute>().Select(_ => _.FreezeSetting).ToList() }; // propertyInfos var dic = new Dictionary <PropertyInfo, PropertyConfiguration>(); var propertyInfos = Common.CacheUtil.TypePropertyCache.GetOrAdd(type, t => t.GetProperties()); foreach (var propertyInfo in propertyInfos) { var column = propertyInfo.GetCustomAttribute <ColumnAttribute>() ?? new ColumnAttribute(); if (string.IsNullOrWhiteSpace(column.Title)) { column.Title = propertyInfo.Name; } dic.Add(propertyInfo, new PropertyConfiguration(column.PropertySetting)); } excelConfiguration.PropertyConfigurationDictionary = dic; return(excelConfiguration); }
/// <summary>Initializes the <see cref="ExcelAddIn"/> class. /// </summary> static ExcelAddIn() { Configuration = ExcelConfiguration.Create(); ExcelApplication = ExcelDnaUtil.Application; Application.EnableVisualStyles(); // required for Marquee progress bar style }
/// <summary> /// Creates a new Excel writer using the given <see cref="Stream"/> and <see cref="ExcelConfiguration"/>. /// </summary> /// <param name="stream">The <see cref="Stream"/> used to write the Excel file.</param> /// <param name="configuration">The configuration.</param> public ExcelWriterC1( Stream stream, ExcelConfiguration configuration) { if (stream == null) { throw new ArgumentNullException(nameof(stream)); } if (configuration == null) { throw new ArgumentNullException(nameof(configuration)); } _configuration = configuration; _stream = stream; _book = new C1XLBook(); _book.CompatibilityMode = CompatibilityMode.Excel2007; ChangeSheet(0); if (_configuration.AutoSizeColumns) { _graphics = Graphics.FromHwnd(IntPtr.Zero); } // Set the default font to Calibri 11, which is the default in newer versions of office DefaultFont = new Font("Calibri", 11, FontStyle.Regular); }
public static async Task <IActionResult> LoadExcelBlob([HttpTrigger(AuthorizationLevel.Anonymous, "post", Route = "load/excel/blob")] HttpRequestMessage req, ILogger log) { log.LogInformation($""); ExcelConfiguration conf = await req.Content.ReadAsAsync <ExcelConfiguration>(); CloudStorageAccount storageAccount = CloudStorageAccount.Parse(conf.blobConnectionString); CloudBlobClient serviceClient = storageAccount.CreateCloudBlobClient(); CloudBlobContainer container = serviceClient.GetContainerReference(conf.blobContainerName); CloudBlockBlob blob = container.GetBlockBlobReference(conf.path); var ms = new MemoryStream(); await blob.DownloadToStreamAsync(ms); var dataSet = BuildDataset(ms.ToArray()); if (!dataSet.Tables.Contains(conf.sheet)) { return(new BadRequestObjectResult($"Unable to find {conf.sheet} sheet")); } return(new OkObjectResult(JsonConvert.SerializeObject(dataSet.Tables[conf.sheet]))); }
/// <summary> /// Adjust Column Index /// </summary> /// <typeparam name="TEntity">TEntity</typeparam> /// <param name="excelConfiguration">excelConfiguration</param> private static void AdjustColumnIndex <TEntity>(ExcelConfiguration <TEntity> excelConfiguration) { if (excelConfiguration.PropertyConfigurationDictionary.Values.All(_ => _.PropertySetting.ColumnIndex >= 0) && excelConfiguration.PropertyConfigurationDictionary.Values.Select(_ => _.PropertySetting.ColumnIndex) .Distinct().Count() == excelConfiguration.PropertyConfigurationDictionary.Values.Count) { return; } var colIndexList = new List <int>(excelConfiguration.PropertyConfigurationDictionary.Count); foreach (var item in excelConfiguration.PropertyConfigurationDictionary .Where(_ => !_.Value.PropertySetting.IsIgnored) .OrderBy(_ => _.Value.PropertySetting.ColumnIndex >= 0 ? _.Value.PropertySetting.ColumnIndex : int.MaxValue) .ThenBy(_ => _.Key.Name) .Select(_ => _.Value) ) { while (colIndexList.Contains(item.PropertySetting.ColumnIndex) || item.PropertySetting.ColumnIndex < 0) { if (colIndexList.Count > 0) { item.PropertySetting.ColumnIndex = colIndexList.Max() + 1; } else { item.PropertySetting.ColumnIndex++; } } colIndexList.Add(item.PropertySetting.ColumnIndex); } }
public void RegisterClassMapNonGenericTest() { var config = new ExcelConfiguration(); Assert.IsNull(config.Maps[typeof(TestClass)]); config.RegisterClassMap(typeof(TestClassMappings)); Assert.IsNotNull(config.Maps[typeof(TestClass)]); }
public void AddingMappingsWithGenericMethod2Test() { var config = new ExcelConfiguration(); config.RegisterClassMap <TestClassMappings>(); Assert.AreEqual(2, config.Maps[typeof(TestClass)].PropertyMaps.Count); }
public void AddingMappingsWithInstanceMethodTest() { var config = new ExcelConfiguration(); config.RegisterClassMap(new TestClassMappings()); Assert.AreEqual(2, config.Maps[typeof(TestClass)].PropertyMaps.Count); }
public void RegisterClassInstanceTest() { var config = new ExcelConfiguration(); Assert.IsNull(config.Maps[typeof(TestClass)]); config.RegisterClassMap(new TestClassMappings()); Assert.IsNotNull(config.Maps[typeof(TestClass)]); }
/// <summary> /// GetExcelConfigurationMapping /// </summary> /// <typeparam name="TEntity">TEntity</typeparam> /// <returns>IExcelConfiguration</returns> public static ExcelConfiguration <TEntity> GetExcelConfigurationMapping <TEntity>() { var configuration = (ExcelConfiguration <TEntity>)InternalCache.TypeExcelConfigurationDictionary.GetOrAdd(typeof(TEntity), type => { var excelConfiguration = new ExcelConfiguration <TEntity> { SheetSettings = new Dictionary <int, SheetSetting>() { { 0, new SheetSetting() }, }, FilterSetting = type.GetCustomAttribute <FilterAttribute>()?.FilterSetting, FreezeSettings = type.GetCustomAttributes <FreezeAttribute>().Select(_ => _.FreezeSetting).ToList() }; foreach (var sheetAttribute in type.GetCustomAttributes <SheetAttribute>()) { if (sheetAttribute.SheetIndex >= 0) { excelConfiguration.SheetSettings[sheetAttribute.SheetIndex] = sheetAttribute.SheetSetting; } } var dic = new Dictionary <PropertyInfo, PropertyConfiguration>(); var propertyInfos = Common.CacheUtil.TypePropertyCache.GetOrAdd(type, t => t.GetProperties()); foreach (var propertyInfo in propertyInfos) { var column = propertyInfo.GetCustomAttribute <ColumnAttribute>() ?? new ColumnAttribute(); if (string.IsNullOrWhiteSpace(column.Title)) { column.Title = propertyInfo.Name; } var propertySettingType = typeof(PropertySetting <,>).MakeGenericType(type, propertyInfo.PropertyType); var propertySetting = Activator.CreateInstance(propertySettingType); propertySettingType.GetProperty(nameof(column.PropertySetting.ColumnTitle))?.GetSetMethod()? .Invoke(propertySetting, new object[] { column.PropertySetting.ColumnTitle }); propertySettingType.GetProperty(nameof(column.PropertySetting.ColumnIndex))?.GetSetMethod()? .Invoke(propertySetting, new object[] { column.PropertySetting.ColumnIndex }); propertySettingType.GetProperty(nameof(column.PropertySetting.ColumnFormatter))?.GetSetMethod()? .Invoke(propertySetting, new object[] { column.PropertySetting.ColumnFormatter }); propertySettingType.GetProperty(nameof(column.PropertySetting.IsIgnored))?.GetSetMethod()? .Invoke(propertySetting, new object[] { column.PropertySetting.IsIgnored }); propertySettingType.GetProperty(nameof(column.PropertySetting.ColumnWidth))?.GetSetMethod()? .Invoke(propertySetting, new object[] { column.PropertySetting.ColumnWidth }); var propertyConfigurationType = typeof(PropertyConfiguration <,>).MakeGenericType(type, propertyInfo.PropertyType); var propertyConfiguration = Activator.CreateInstance(propertyConfigurationType, new object[] { propertySetting }); dic.Add(propertyInfo, (PropertyConfiguration)propertyConfiguration); } excelConfiguration.PropertyConfigurationDictionary = dic; return(excelConfiguration); }); return(configuration); }
/// <summary> /// 获取Excel配置映射 /// </summary> /// <typeparam name="TEntity">实体类型</typeparam> public static ExcelConfiguration <TEntity> GetExcelConfigurationMapping <TEntity>() { var type = typeof(TEntity); var excelConfiguration = new ExcelConfiguration <TEntity>() { }; return(excelConfiguration); }
public void PropertyMapOptionalReadTest() { var config = new ExcelConfiguration(); config.RegisterClassMap <AMap>(); config.Maps[typeof(A)].PropertyMap <A>(m => m.AId).OptionalRead(); Assert.AreEqual(true, config.Maps[typeof(A)].PropertyMaps[0].Data.OptionalRead); }
/// <summary> /// GetPropertyColumnDictionary /// </summary> /// <typeparam name="TEntity">TEntity Type</typeparam> /// <returns></returns> public static Dictionary <PropertyInfo, PropertyConfiguration> GetPropertyColumnDictionary <TEntity>( ExcelConfiguration <TEntity> configuration) { AdjustColumnIndex(configuration); return(configuration.PropertyConfigurationDictionary .Where(p => !p.Value.IsIgnored) .ToDictionary(_ => _.Key, _ => _.Value)); }
public void EnsureInternalsAreSetupWhenPasingWriterAndConfigTest() { using (var stream = new MemoryStream()) { var config = new ExcelConfiguration(); using (var excel = new ExcelWriterC1(stream, config)) { Assert.AreSame(config, excel.Configuration); } } }
public void PropertyMapWriteOnlyTest() { var config = new ExcelConfiguration(); config.RegisterClassMap <AMap>(); config.Maps[typeof(A)].PropertyMap <A>(m => m.AId).WriteOnly(); Assert.AreEqual(true, config.Maps[typeof(A)].PropertyMaps[0].Data.WriteOnly); }
public void PropertyMapAccessTest() { var config = new ExcelConfiguration(); config.RegisterClassMap <AMap>(); config.Maps[typeof(A)].PropertyMap <A>(m => m.AId).Ignore(); Assert.AreEqual(true, config.Maps[typeof(A)].PropertyMaps[0].Data.Ignore); }
public ExcelDocumentProccesor() { _validata = new Validata(); _parser = new Parser(); _readExcelData = new ReadExcelData(); _dataNormalization = new DataNormalization(); _excelSheetCongSection = new ExcelSheetCongSection(); _parserSection = new ParserSectionPage(); _excelConfiguration = ConfigurationHolder.ApiConfiguration; }
/// <summary> /// Creates an <see cref="IExcelWriter"/>. /// </summary> /// <param name="stream">The stream used to write the Excel file.</param> /// <param name="configuration">The configuration to use for the writer.</param> /// <returns>The created writer.</returns> public virtual IExcelWriter CreateWriter( Stream stream, ExcelConfiguration configuration) { #if USE_C1_EXCEL return(new ExcelWriterC1(stream, configuration)); #else return(new ExcelWriter(stream, configuration)); #endif }
public void MapMultipleTypesTest() { var config = new ExcelConfiguration(); config.RegisterClassMap <AMap>(); config.RegisterClassMap <BMap>(); Assert.IsNotNull(config.Maps[typeof(A)]); Assert.IsNotNull(config.Maps[typeof(B)]); }
internal NpoiHelper() { _entityType = typeof(TEntity); _excelConfiguration = (ExcelConfiguration <TEntity>)InternalCache.TypeExcelConfigurationDictionary.GetOrAdd(_entityType, t => InternalHelper.GetExcelConfigurationMapping <TEntity>()); _sheetSettings = _excelConfiguration.SheetSettings.AsReadOnly(); InternalHelper.AdjustColumnIndex(_excelConfiguration); _propertyColumnDictionary = _excelConfiguration.PropertyConfigurationDictionary.Where(_ => !_.Value.PropertySetting.IsIgnored).ToDictionary(_ => _.Key, _ => _.Value.PropertySetting); }
/// <summary> /// Creates a new Excel writer using the given <see cref="Stream"/> and <see cref="ExcelConfiguration"/>. /// </summary> /// <param name="stream">The <see cref="Stream"/> used to write the Excel file.</param> /// <param name="configuration">The configuration.</param> public ExcelWriter( Stream stream, ExcelConfiguration configuration) { _configuration = configuration ?? throw new ArgumentNullException(nameof(configuration)); _stream = stream ?? throw new ArgumentNullException(nameof(stream)); _book = new XLWorkbook(XLEventTracking.Disabled); ChangeSheet(0); // Set the default font to Calibri 11, which is the default in newer versions of office DefaultFont = new Font("Calibri", 11, FontStyle.Regular); }
/// <summary> /// Creates a new Excel stream using the given <see cref="Stream"/> and <see cref="ExcelConfiguration"/>. /// </summary> /// <param name="stream">The stream.</param> /// <param name="configuration">The configuration.</param> public ExcelReader( Stream stream, ExcelConfiguration configuration) { if (stream == null) { throw new ArgumentNullException(nameof(stream)); } _configuration = configuration ?? throw new ArgumentNullException(nameof(configuration)); _reader = ExcelReaderFactory.CreateReader(stream); ChangeSheet(0); }
public IDataResult <IRowItem> RowParser (ExcelWorksheet excelWorksheet, int row, ExcelConfiguration excelConfiguration) { IDataResult <IRowItem> dataResult = new DataResult <IRowItem>() { Success = false }; IRowItem rowItem = new RowItem(); List <IColumnItem> columnItems = new List <IColumnItem>(); int end = excelWorksheet.Dimension.End.Column; for (int j = excelWorksheet.Dimension.Start.Column; j <= end; j++) { IExcelWorksheetEntity tmpEntity = new ExcelWorksheetEntity(); tmpEntity.CellNo = j; tmpEntity.RowNo = row; tmpEntity.ExcelWorksheet = excelWorksheet; IExcelWorksheetEntity titleEntity = new ExcelWorksheetEntity(); titleEntity.RowNo = excelConfiguration.DataRowIndex.Title; titleEntity.CellNo = j; titleEntity.ExcelWorksheet = excelWorksheet; IDataResult <string> nametitleResilt = _readExcelData.GetValue(titleEntity); if (!nametitleResilt.Success) { break; } IDataResult <IColumnItem> getDataResult = ColumnParser(tmpEntity, excelConfiguration); dataResult.Message += getDataResult.Message; if (getDataResult.Success) { columnItems.Add(getDataResult.Data); } } rowItem.ColumnItems = columnItems; dataResult.Data = rowItem; dataResult.Success = true; return(dataResult); }
public IDataResult <ExcelConfiguration> GeneratExcelConfig(ExcelWorksheet excelWorksheet) { IDataResult <ExcelConfiguration> dataResult = new DataResult <ExcelConfiguration>(); ExcelConfiguration excelConfiguration = new ExcelConfiguration(); excelConfiguration.DataColumn = new DataColumn(); IExcelWorksheetEntity tmpEntity = new ExcelWorksheetEntity(); tmpEntity.ExcelWorksheet = excelWorksheet; tmpEntity.RowNo = _excelConfiguration.DataRowIndex.Title; List <IDataResult <Data> > dataResults = new List <IDataResult <Data> >(); int endColumn = excelWorksheet.Dimension.End.Column; StringBuilder messegeBuilder = new StringBuilder(); foreach (var data in _excelConfiguration.DataColumn.Datas) { IDataResult <Data> tmResultHelper = HelperGenerat(data, tmpEntity, endColumn); if (!tmResultHelper.Success) { messegeBuilder.Append(data.Name + MessageHolder.GetErrorMessage(MessageType.Space)); } dataResults.Add(tmResultHelper); } excelConfiguration.DataColumn.Datas = dataResults.Select(p => p.Data).ToList(); if (dataResults.Any(p => p.Success == false)) { dataResult.Success = false; dataResult.Message = MessageHolder. GetErrorMessage(MessageType.NotIsTitle) + MessageHolder.GetErrorMessage(MessageType.Space) + messegeBuilder + MessageHolder.GetErrorMessage(MessageType.BackBracket); return(dataResult); } excelConfiguration.DataRowIndex = _excelConfiguration.DataRowIndex; excelConfiguration.NameColumnSection = _excelConfiguration.NameColumnSection; dataResult.Data = excelConfiguration; dataResult.Success = true; return(dataResult); }
public void EnsureInternalsAreSetupWhenPassingReaderAndConfigTest() { using (var stream = new MemoryStream()) { // Make sure the stream is a valid Excel file using (var book = new C1XLBook()) { book.Save(stream); } stream.Position = 0; var config = new ExcelConfiguration(); using (var excel = new ExcelReader(stream, config)) { Assert.AreSame(config, excel.Configuration); } } }
/// <summary> /// Creates a new Excel stream using the given <see cref="Stream"/> and <see cref="ExcelConfiguration"/>. /// </summary> /// <param name="stream">The stream.</param> /// <param name="configuration">The configuration.</param> public ExcelReader( Stream stream, ExcelConfiguration configuration) { if (stream == null) { throw new ArgumentNullException(nameof(stream)); } _configuration = configuration ?? throw new ArgumentNullException(nameof(configuration)); _book = new C1XLBook(); try { _book.Load(stream, FileFormat.OpenXml); } catch { _book.Load(stream, FileFormat.Biff8); } ChangeSheet(0); }
public void wishListPage() { //Reading Excel string directory = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location); string Excelpath = Path.Combine(directory, Resource1.ExcelPath); Console.WriteLine(Excelpath); Global.ExcelConfiguration.PopulateInCollection(Excelpath, "SearchPaint"); //Implict wait to load the page CommonUtilities.implicitWait(20); //Navigate to wishlistpage though url provided Global.Base.driver.Navigate().GoToUrl("https://www.bunnings.com.au/wish-lists"); //getting list of all items from list IList <IWebElement> ItemsList = ListOfWishList.FindElements(By.TagName("small")); for (int i = 0; i < ItemsList.Count; i++) { try { string Exceldata = ExcelConfiguration.ReadData(2, "PaintID"); Console.WriteLine(Exceldata); if (ItemsList[i].Text.Contains(ExcelConfiguration.ReadData(2, "PaintID"))) { ; } { Console.WriteLine(ItemsList[i].Text); // Validating with Id from excel string ExpectedCondition = ExcelConfiguration.ReadData(2, "PaintID"); string ActualCondition = Global.Base.driver.FindElement(By.XPath("//tbody[1]/tr/td[2]/small")).Text; Assert.IsTrue(ActualCondition.Contains(ExpectedCondition)); Base.test.Log(RelevantCodes.ExtentReports.LogStatus.Pass, "Test Pass Paint Addeded to the whishlist"); } } catch (Exception e) { Base.test.Log(RelevantCodes.ExtentReports.LogStatus.Fail, e.Message); } } }
internal NpoiHelper() { _excelConfiguration = (ExcelConfiguration <TEntity>)InternalCache.TypeExcelConfigurationDictionary.GetOrAdd(typeof(TEntity), t => InternalHelper.GetExcelConfigurationMapping <TEntity>()); _sheetSettings = _excelConfiguration.SheetSettings.AsReadOnly(); //AutoAdjustIndex var colIndexList = new List <int>(_excelConfiguration.PropertyConfigurationDictionary.Count); foreach (var item in _excelConfiguration.PropertyConfigurationDictionary.Values.Where(_ => !_.PropertySetting.IsIgnored)) { while (colIndexList.Contains(item.PropertySetting.ColumnIndex)) { item.PropertySetting.ColumnIndex++; } colIndexList.Add(item.PropertySetting.ColumnIndex); } _propertyColumnDictionary = _excelConfiguration.PropertyConfigurationDictionary.Where(_ => !_.Value.PropertySetting.IsIgnored).ToDictionary(_ => _.Key, _ => _.Value.PropertySetting); }
public static async Task <IActionResult> LoadExcelSharepoint([HttpTrigger(AuthorizationLevel.Function, "post", Route = "load/excel/sharepoint")] HttpRequestMessage req, ILogger log) { log.LogInformation($""); ExcelConfiguration conf = await req.Content.ReadAsAsync <ExcelConfiguration>(); var requestUri = new Uri(conf.path); var cred = new SharePointOnlineCredentials(conf.sharepointLogin, conf.sharepointPassword.AsSecureString()); var excelFileContent = await DownloadFile(requestUri, cred); var dataSet = BuildDataset(excelFileContent); if (!dataSet.Tables.Contains(conf.sheet)) { return(new BadRequestObjectResult($"Unable to find {conf.sheet} sheet")); } return(new OkObjectResult(JsonConvert.SerializeObject(dataSet.Tables[conf.sheet]))); }
public IDataResult <List <IColumnItem> > GetCulumnTitleItem (ExcelWorksheet sheet, ExcelConfiguration excelConfiguration) { IDataResult <List <IColumnItem> > columnsResult = new DataResult <List <IColumnItem> >() { Success = true }; List <IColumnItem> columnItems = new List <IColumnItem>(); int start = sheet.Dimension.Start.Column; int end = sheet.Dimension.Columns; int row = excelConfiguration.DataRowIndex.Title; IExcelWorksheetEntity entity = new ExcelWorksheetEntity(); entity.ExcelWorksheet = sheet; entity.RowNo = row; for (int i = start; i < end; i++) { entity.CellNo = i; IDataResult <string> nametitleResilt = _readExcelData.GetValue(entity); if (!nametitleResilt.Success) { break; } IColumnItem column = new ColumnItem(); column.BaseEntity = new BaseEntity(); column.BaseEntity.Value = nametitleResilt.Data; column.ColumNumber = i; column.ColumnType = ColumnType.None; columnItems.Add(column); } columnsResult.Data = columnItems; return(columnsResult); }
ExcelUIViewDetail GetDetailView(ExcelConfiguration conf, string viewName) { foreach (var view in conf.ViewDetails) { if (view.ViewName.ToUpper() == viewName.ToUpper()) return view; } return null; }
//sql string GetJoinStr(ExcelConfiguration conf, string table1, string table2) { string join = " \r\nleft join "+ table2 + " on "; ExcelTableSpec tb = GetTableSpec(conf, table1, 0); int i = 0; foreach (var fd in tb.Fields) { if (fd.FieldForeignKey && fd.FieldForeignKeyTableName.ToUpper() == table2.ToUpper()) { i++; if (i == 1) { join += tb.TableName + "." + fd.FieldName + " = " + fd.FieldForeignKeyTableName + "." + fd.FieldName + " "; } else { join += " AND " + tb.TableName + "." + fd.FieldName + " = " + fd.FieldForeignKeyTableName + "." + fd.FieldName + " "; } } } return join; }
string GetFKTableName(ExcelUIViewDetailItem fd, ExcelConfiguration conf) { ExcelFieldSpec f = GetFieldSpec(conf, GetOnlyTableName(fd), GetOnlyFieldName(fd)); if (f.FieldForeignKey) return f.FieldForeignKeyTableName; else return ""; }
ExcelTableSpec GetTableSpec(ExcelConfiguration conf, string tablename, int rowNumber) { ExcelTableSpec tbOut = null; foreach (var tb in conf.Tables) { if (tb.TableName.ToUpper() == tablename.ToUpper()) tbOut = tb; } try { string name = tbOut.TableName; } catch (Exception ex) { throw new ApplicationException("มีการเรียกใช้ table \"" + tablename + "\" แต่ไม่มี rownum="+rowNumber, ex); } return tbOut; }
ExcelUIViewLookup GetLookupView(ExcelConfiguration conf, string viewName) { foreach (var view in conf.ViewLookup) { if (view.ViewName.ToUpper() == viewName.ToUpper()) return view; } return null; }
//=================// private ExcelWorksheet CreateDetailView_XML(ExcelWorksheet worksheet, int row, string fieldformat, System.Drawing.Color colFromHex,int i,ExcelConfiguration conf) { worksheet.Cells["A1"].Value = "TableName"; worksheet.Cells["B1"].Value = conf.ViewDetails[i].TableName; worksheet.Cells["A2"].Value = "ViewType"; worksheet.Cells["B2"].Value = conf.ViewDetails[i].ViewType; worksheet.Cells["A3"].Value = "ViewName"; worksheet.Cells["B3"].Value = conf.ViewDetails[i].ViewName; worksheet.Cells["A4"].Value = "Title"; worksheet.Cells["B4"].Value = conf.ViewDetails[i].ViewTitle; worksheet.Cells["A5"].Value = "Root Service URL"; worksheet.Cells["B5"].Value = conf.ViewDetails[i].RootServiceUrl; // worksheet.Cells["A6"].Value = "InputParameters"; worksheet.Cells["B6"].Value = conf.ViewDetails[i].InputParameters; worksheet.Cells["A7"].Value = "FilterParameters"; worksheet.Cells["B6"].Value = conf.ViewDetails[i].FilterParameters; //if(ds_columns.Tables[""] worksheet.Cells["A8"].Value = "MaterPage"; worksheet.Cells["B8"].Value = conf.ViewDetails[i].MasterPage; worksheet.Cells["A13"].Value = "SqlSelect"; worksheet.Cells["B13"].Value = conf.ViewDetails[i].SQLSelect; worksheet.Cells["A14"].Value = "SqlInsert"; worksheet.Cells["B14"].Value = conf.ViewDetails[i].SQLInsert; worksheet.Cells["A15"].Value = "SqlUpdate"; worksheet.Cells["B15"].Value = conf.ViewDetails[i].SQLUpdate; worksheet.Cells["A16"].Value = "SqlDelete"; worksheet.Cells["B16"].Value = conf.ViewDetails[i].SQLDelete; worksheet.Cells["E13"].Value = "SqlSelectParameter"; worksheet.Cells["F13"].Value = conf.ViewDetails[i].SQLSelectParams; worksheet.Cells["E14"].Value = "SqlInsertParameter"; worksheet.Cells["F14"].Value = conf.ViewDetails[i].SQLInsertParams; worksheet.Cells["E15"].Value = "SqlUpdateParameter"; worksheet.Cells["F15"].Value = conf.ViewDetails[i].SQLUpdateParams; worksheet.Cells["E16"].Value = "SqlDeleteParameter"; worksheet.Cells["E16"].Value = conf.ViewDetails[i].SQLDeleteParams; // worksheet.Cells["A9"].Value = "ButtonNew"; worksheet.Cells["B9"].Value = conf.ViewDetails[i].IsHasNewButton; worksheet.Cells["C9"].Value = "Caption"; worksheet.Cells["D9"].Value = "เพิ่ม"; worksheet.Cells["E9"].Value = "AppearanceRules"; worksheet.Cells["F9"].Value = "TRUE"; worksheet.Cells["A10"].Value = "ButtonSave"; worksheet.Cells["B10"].Value = conf.ViewDetails[i].IsHasSaveButton; worksheet.Cells["C10"].Value = "Caption"; worksheet.Cells["D10"].Value = "บันทึก"; worksheet.Cells["E10"].Value = "AppearanceRules"; worksheet.Cells["F10"].Value = "TRUE"; worksheet.Cells["A11"].Value = "ButtonDelete"; worksheet.Cells["B11"].Value = conf.ViewDetails[i].IsHasDeleteButton; worksheet.Cells["C11"].Value = "Caption"; worksheet.Cells["D11"].Value = "ลบ"; worksheet.Cells["E11"].Value = "AppearanceRules"; worksheet.Cells["F11"].Value = "TRUE"; worksheet.Cells["A12"].Value = "ButtonSubmitWF"; worksheet.Cells["B12"].Value = conf.ViewDetails[i].IsHasSubmitWf; worksheet.Cells["C12"].Value = "Caption"; worksheet.Cells["D12"].Value = "ส่งงาน"; worksheet.Cells["E12"].Value = "AppearanceRules"; worksheet.Cells["F12"].Value = "TRUE"; ////Add the headers worksheet.Cells["A17"].Value = "ElementType"; worksheet.Cells["B17"].Value = "ElementId"; worksheet.Cells["C17"].Value = "Caption"; worksheet.Cells["D17"].Value = "LV1"; worksheet.Cells["E17"].Value = "LV2"; worksheet.Cells["F17"].Value = "LV3"; worksheet.Cells["G17"].Value = "LV4"; worksheet.Cells["H17"].Value = "LV5"; worksheet.Cells["I17"].Value = "ShowCaption"; worksheet.Cells["J17"].Value = "Data"; worksheet.Cells["K17"].Value = "PlaceHolder"; worksheet.Cells["L17"].Value = "Width"; worksheet.Cells["M17"].Value = "Height"; worksheet.Cells["N17"].Value = "Format"; worksheet.Cells["O17"].Value = "TextAlignment"; worksheet.Cells["P17"].Value = "CustomStyle"; colFromHex = System.Drawing.ColorTranslator.FromHtml("#FFFF00"); worksheet.Cells["A17:P17"].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; worksheet.Cells["A17:P17"].Style.Fill.BackgroundColor.SetColor(colFromHex); row = 18; for (int j = 0; j < conf.ViewDetails[i].Items.Count; j++) { // fieldformat = ""; worksheet.Cells["B" + row.ToString()].Value = conf.ViewDetails[i].Items[j].ElementId; worksheet.Cells["A" + row.ToString()].Value = conf.ViewDetails[i].Items[j].ElementType; worksheet.Cells["C" + row.ToString()].Value = conf.ViewDetails[i].Items[j].Caption; switch (conf.ViewDetails[i].Items[j].LevelValue) { case "1": worksheet.Cells["D" + row.ToString()].Value = conf.ViewDetails[i].Items[j].LevelValue; break; case "2": worksheet.Cells["E" + row.ToString()].Value = conf.ViewDetails[i].Items[j].LevelValue; break; case "3": worksheet.Cells["F" + row.ToString()].Value = conf.ViewDetails[i].Items[j].LevelValue; break; case "4": worksheet.Cells["G" + row.ToString()].Value = conf.ViewDetails[i].Items[j].LevelValue; break; case "5": worksheet.Cells["H" + row.ToString()].Value = conf.ViewDetails[i].Items[j].LevelValue; break; } worksheet.Cells["N" + row.ToString()].Value = conf.ViewDetails[i].Items[j].Formats; worksheet.Cells["I" + row.ToString()].Value = conf.ViewDetails[i].Items[j].ShowCaption; worksheet.Cells["J" + row.ToString()].Value = conf.ViewDetails[i].Items[j].DataBinding; worksheet.Cells["K" + row.ToString()].Value = conf.ViewDetails[i].Items[j].PlaceHolderName; worksheet.Cells["L" + row.ToString()].Value = conf.ViewDetails[i].Items[j].Width; worksheet.Cells["M" + row.ToString()].Value = conf.ViewDetails[i].Items[j].Height; worksheet.Cells["O" + row.ToString()].Value = conf.ViewDetails[i].Items[j].TextAlignment; worksheet.Cells["P" + row.ToString()].Value = conf.ViewDetails[i].Items[j].CustomStyle; row++; } row--; worksheet.Cells["A1:P" + row.ToString()].Style.Border.Top.Style = ExcelBorderStyle.Thin; worksheet.Cells["A1:P" + row.ToString()].Style.Border.Right.Style = ExcelBorderStyle.Thin; worksheet.Cells["A1:P" + row.ToString()].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; worksheet.Cells["A1:P" + row.ToString()].Style.Border.Left.Style = ExcelBorderStyle.Thin; worksheet.Cells["A1:P" + row.ToString()].Style.Font.Name = "Tahoma"; worksheet.Cells["A1:P" + row.ToString()].Style.Font.Size = 10; return worksheet; }
string GetValidateString(ExcelConfiguration conf, string fieldname) { string validStr = ""; if (fieldname==null || fieldname.IndexOf(".") < 0) return ""; string tb = fieldname.Substring(0, fieldname.IndexOf(".")); string fd = fieldname.Substring(fieldname.IndexOf(".")+1); ExcelFieldSpec fdSpec = GetFieldSpec(conf, tb, fd); if(fdSpec == null)return ""; if (fdSpec.IsRequireField) { validStr += "\t\tctl" + fd + ".ValidationSettings.RequiredField.IsRequired = true;\r\n"; validStr += "\t\tctl" + fd + ".ValidationSettings.RequiredField.ErrorText = \"" + fdSpec.IsRequireFieldMsg + "\";\r\n"; } if (fdSpec.MaxLength > 0) { validStr += "\t\tctl" + fd + ".MaxLength = " + fdSpec.MaxLength.ToString() + ";\r\n"; } if (fdSpec.RegularExpression != null && fdSpec.RegularExpression!="") { validStr += "\t\tctl" + fd + ".ValidationSettings.RegularExpression.ValidationExpression = \"" + fdSpec.RegularExpression + "\";\r\n"; validStr += "\t\tctl" + fd + ".ValidationSettings.RegularExpression.ErrorText = \"" + fdSpec.IsRegularExpressionMsg + "\";\r\n"; } return validStr; }
public bool GenerateDetailView(ExcelConfiguration conf, string viewName, string directoryPath) { ExcelUIViewDetail view = GetDetailView(conf, viewName); if (view != null) { FormControlTemplateManager ctls = FormControlTemplateManager.GetTemplateFromPath(ConfigurationManager.AppSettings["HomePath"] + @"\Assets\ControlTemplates"); string fileAspx = directoryPath + "\\" + view.ViewName + ".aspx"; string fileCs = directoryPath + "\\" + view.ViewName + ".aspx.cs"; string fileAspxTemplate = ConfigurationManager.AppSettings["HomePath"] + @"\Assets\ControlTemplates\WebForm.aspx.exclude"; string fileCsTemplate = ConfigurationManager.AppSettings["HomePath"] + @"\Assets\ControlTemplates\WebForm.aspx.cs.exclude"; string aspxStr = File.ReadAllText(fileAspxTemplate); string csStr = File.ReadAllText(fileCsTemplate); string aspxContent = ""; string aspxHeader = ""; string csDeclareVar = ""; string csPopulateData = ""; string csPopulateNewData = ""; string csInitData = ""; string csInsertData = ""; string csUpdateData = ""; string csUpdateKeyData = ""; string csValidateData = ""; string csFormattingData = ""; List<ExcelTableSpec> tables = new List<ExcelTableSpec>(); string[] tableArr = view.TableName.Split(','); int xx=0; foreach (var tb in tableArr) { tables.Add(GetTableSpec(conf, tb, xx * -1)); xx++; } string htmlStr = ""; string buttonStr = ""; string select = ""; string from = tables[0].TableName; string where = " 1=1 "; string sql = ""; string dataSourceStr = ""; string dsSelectParam = ""; string sqlInsert = ""; string sqlUpdate = ""; string sqlDelete = ""; string sqlInsertP2 = ""; string sqlUpdateP2 = ""; #region Gen htmlStr //view.Items.Sort(); for (int i = 0; i < view.Items.Count; ++i ) { ExcelUIViewDetailItem item = view.Items[i]; FormControlTemplate ctl = ctls.GetControlByName(item.ElementType); if (item.ElementType == "listview") { FormListStructure frmList = GenerateListViewStructure(conf, item.LevelValue, directoryPath, false); htmlStr += "<div>" + frmList.HtmlTag + "</div>"; //<div>" + frmList.TopButtonTag + "</div> dataSourceStr += frmList.DataSourceTag; csPopulateData += frmList.PopulateCS; csInitData += frmList.InitCS; csDeclareVar += frmList.VariableDeclareCS; csFormattingData += frmList.FomattingCS; } else if (ctl == null) { throw new ApplicationException("Error: View [" + view.ViewName + "] บรรทัดที่ " + item.RowNumber + " ไม่มี ElementType ใน template"); } else if (ctl.ControlName == "dropdown" || ctl.ControlName == "radiolist") //is dropdown { //find table string tbName = GetFKTableName(item, conf); if (tbName != null && tbName != "") { bool tbNotExisted = true; for (int j = 0; j < tables.Count; ++j) { if (tbName.ToUpper() == tables[j].TableName.ToUpper()) { tbNotExisted = false; break; } } if (tbNotExisted && tbName != null && tbName != "") tables.Add(GetTableSpec(conf, tbName, item.RowNumber)); htmlStr += ctl.GetEditHtml(item, tables, view.ViewName); } else { } } else if (ctl.ControlName != "tab") //not is tab { htmlStr += ctl.GetEditHtml(item, tables, view.ViewName); } else if (ctl.ControlName == "tab") { htmlStr += ctl.GetEditHtmlHead(item, tables, view.ViewName); bool isNotEndTab = true; int countNestedTab = 0; //find tabpage for (int j = i + 1; j < view.Items.Count && isNotEndTab; ++j) { ExcelUIViewDetailItem it = view.Items[j]; if (it.ElementType == "tabpage") { FormControlTemplate c = ctls.GetControlByName(it.ElementType); htmlStr += c.GetEditHtmlHead(it, tables, view.ViewName); } else if (it.ElementType == "tab") { countNestedTab++; } else if (it.ElementType == "endtab") { countNestedTab--; if (countNestedTab == -1) { FormControlTemplate c = ctls.GetControlByName(it.ElementType); htmlStr += c.GetEditHtmlHead(it, tables, view.ViewName); htmlStr += ctl.GetEditHtml(item, tables, view.ViewName); isNotEndTab = false; } } } } } #endregion #region Gen csDeclareVar csInitData csPopulateData where csInitData += "\t\tds" + view.ViewName + ".SelectParameters.Clear();\r\n"; csPopulateData += "\r\n\t\t//" + view.ViewName + "\r\n"; csPopulateData += "\t\tTitle = \"" + view.ViewTitle + "\"; \r\n"; csPopulateData += "\t\tDataView dv = (DataView)ds" + view.ViewName + ".Select(DataSourceSelectArguments.Empty);\r\n"; csPopulateData += "\t\tDataRowView drv = dv[0];\r\n"; csInsertData += "\t\tds" + view.ViewName + ".InsertParameters.Clear();\r\n"; csUpdateData += "\t\tds" + view.ViewName + ".UpdateParameters.Clear();\r\n"; if (view.InputParameters != null && view.InputParameters != "") { string[] fds = view.InputParameters.Split(','); int i = 0; foreach (var f in fds) { i++; where += " AND " + f + "=@" + f.Substring(f.IndexOf('.') + 1); dsSelectParam += "<asp:Parameter Name=\"" + f.Substring(f.IndexOf('.') + 1) + "\" Type=\"Int32\" />"; csDeclareVar += "\tstring inputMain" + f.Substring(f.IndexOf('.') + 1) + "=\"-1\";\r\n"; if(i==1) csDeclareVar += "\tstring key=\"" + f.Substring(f.IndexOf('.') + 1) + "\";\r\n"; csInitData += @" if (Request.QueryString[""" + f.Substring(f.IndexOf('.') + 1) + @"""] != null && Request.QueryString[""" + f.Substring(f.IndexOf('.') + 1) + @"""] != ""-1"") { inputMain" + f.Substring(f.IndexOf('.') + 1) + @" = Request.QueryString[""" + f.Substring(f.IndexOf('.') + 1) + @"""]; } "; csInitData += "\t\tds" + view.ViewName + ".SelectParameters.Add(\"" + f.Substring(f.IndexOf('.') + 1) + "\", System.Data.DbType.Int32, inputMain" + f.Substring(f.IndexOf('.') + 1) + ");\r\n"; csUpdateKeyData += "\t\tds" + view.ViewName + ".UpdateParameters.Add(\"" + f.Substring(f.IndexOf('.') + 1) + "\", System.Data.DbType.Int32, inputMain" + f.Substring(f.IndexOf('.') + 1) + ");\r\n"; } } if (view.FilterParameters != null && view.FilterParameters != "") { string[] fds = view.FilterParameters.Split(','); foreach (var f in fds) { where += " AND " + f + "=@" + f.Substring(f.IndexOf('.') + 1); dsSelectParam += "<asp:Parameter Name=\"" + f.Substring(f.IndexOf('.') + 1) + "\" Type=\"Int32\" />"; csDeclareVar += "string paramMain" + f.Substring(f.IndexOf('.') + 1) + "=\"-1\";\r\n"; csInitData += "\t\tds" + view.ViewName + ".SelectParameters.Add(\"" + f.Substring(f.IndexOf('.') + 1) + "\", System.Data.DbType.Int32, paramMain" + f.Substring(f.IndexOf('.') + 1) + ");\r\n"; } } foreach (var item in view.Items) { if (item.ElementType.ToLower() != "listview" && item.ElementType.ToLower() != "detailview") { csPopulateData += ctls.GetControlByName(item.ElementType).GetEditCsPopulate(item, tables, view.ViewName) + ""; csPopulateNewData += ctls.GetControlByName(item.ElementType).GetNewCsPopulate(item, tables, view.ViewName) + ""; csInsertData += ctls.GetControlByName(item.ElementType).GetInsertCs(item, tables, view.ViewName) + ""; csUpdateData += ctls.GetControlByName(item.ElementType).GetUpdateCs(item, tables, view.ViewName) + ""; csValidateData += GetValidateString(conf, item.DataBinding); csFormattingData += GetFormattingString(item, item.DataBinding); } } //int i = SqlDataSource1.Insert(); //int i = SqlDataSource1.Update(); csUpdateData += csUpdateKeyData; csInsertData += "\t\tint i = ds" + view.ViewName + ".Insert();\r\n"; csUpdateData += "\t\tint i = ds" + view.ViewName + ".Update();\r\n"; #endregion #region Gen button buttonStr += "";// @"<input type=""button"" class='btn btn-primary' value=' Save ' />"; #endregion #region Gen sql dataSourceStr if (view.SQLSelect == null || view.SQLSelect == "") { if (tables.Count > 1) { for (int i = 1; i < tables.Count; ++i) { from += GetJoinStr(conf, tables[0].TableName, tables[i].TableName); } } sqlUpdateP2 = " 1=1 "; foreach (var fd in tables[0].Fields) { if (fd.FieldPrimaryKey) { select += tables[0].TableName + "." + fd.FieldName + "\r\n,"; sqlUpdateP2 += "\r\n AND " + fd.FieldName + "=@" + fd.FieldName + ""; } } foreach (var item in view.Items) { if (item.DataBinding != null && item.DataBinding != "") { string fdAll = item.DataBinding; string tb = fdAll.Substring(0, fdAll.IndexOf(".")); string fd = fdAll.Substring(fdAll.IndexOf(".") + 1); select += fdAll + "\r\n,"; if (tb.ToLower() == tables[0].TableName.ToLower()) { sqlInsert += fd + "\r\n,"; sqlUpdate += fd + "=@" + fd + "\r\n,"; sqlInsertP2 += "@" + fd + "\r\n,"; } } } select = select.Substring(0, select.Length - 1); sqlInsert = sqlInsert.Substring(0, sqlInsert.Length - 1); sqlInsertP2 = sqlInsertP2.Substring(0, sqlInsertP2.Length - 1); sqlUpdate = sqlUpdate.Substring(0, sqlUpdate.Length - 1); ; sql = "select \r\n" + select + " \r\nfrom " + from + " \r\nwhere " + where; sqlInsert = "insert into " + tableArr[0] + "\r\n(\r\n" + sqlInsert + ")\r\nvalues\r\n(\r\n" + sqlInsertP2 + ")"; sqlUpdate = "update " + tableArr[0] + " \r\nset \r\n" + sqlUpdate + " \r\nwhere " + sqlUpdateP2 + ""; sqlDelete = "DELETE FROM " + tables[0].TableName + @" WHERE [" + GetPrimaryKeyName(tables[0]) + @"] = @" + GetPrimaryKeyName(tables[0]) ; } else { sql = view.SQLSelect; sqlInsert = view.SQLInsert; sqlUpdate = view.SQLUpdate; sqlDelete = view.SQLDelete; } //DefaultConnection /* <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DefaultConnection %>" DeleteCommand="DELETE FROM [COMMON_MASTER_ORGANIZE] WHERE [ORGANIZE_ID] = @PK_ID" SelectCommand="SELECT * FROM [COMMON_MASTER_ORGANIZE]" <DeleteParameters> <asp:Parameter Name="ORGANIZE_ID" Type="Int32" /> </DeleteParameters> </asp:SqlDataSource> */ dataSourceStr += @" <asp:SqlDataSource ID=""ds" + view.ViewName + @""" runat=""server"" ConnectionString=""<%$ ConnectionStrings:DefaultConnection %>"" DeleteCommand=""" + sqlDelete + @""" SelectCommand=""" + sql + @""" InsertCommand=""" + sqlInsert + @""" UpdateCommand=""" + sqlUpdate + @""" > <DeleteParameters> <asp:Parameter Name=""" + GetPrimaryKeyName(tables[0]) + @""" Type=""Int32"" /> </DeleteParameters> <SelectParameters> " + dsSelectParam + @" </SelectParameters> </asp:SqlDataSource> "; for (int i = 1; i < tables.Count; ++i) { dataSourceStr += @" <asp:SqlDataSource ID=""ds" + tables[i].TableName + @""" runat=""server"" ConnectionString=""<%$ ConnectionStrings:DefaultConnection %>"" SelectCommand=""select " + tables[i].DefaultDisplay + "," + tables[i].PrimaryKey + " from " + tables[i].TableName + @""" > </asp:SqlDataSource> "; } for (int i = 0; i < view.Items.Count; ++i) { if (view.Items[i].CustomStyle != null && view.Items[i].CustomStyle != "" && view.Items[i].ElementType.ToLower() == "dropdownfix" ) { string[] itemRows = view.Items[i].CustomStyle.Split(','); dataSourceStr += @" <asp:SqlDataSource ID=""ds" + view.Items[i].DataBinding.Substring(view.Items[i].DataBinding.IndexOf(".")+1) + @""" runat=""server"" ConnectionString=""<%$ ConnectionStrings:DefaultConnection %>"" SelectCommand=""select '-1' id, 'กรุณาเลือก' txt "; foreach (var itemRow in itemRows) { if (itemRow.IndexOf(":") >= 0) { string[] itemCols = itemRow.Split(':'); dataSourceStr += " union all select '" + itemCols[0] + "','" + itemCols[1] + "' "; } } dataSourceStr += @""" > </asp:SqlDataSource>"; } else if (view.Items[i].CustomStyle != null && view.Items[i].CustomStyle != "" && (view.Items[i].ElementType.ToLower() == "radiolistfix")) { string[] itemRows = view.Items[i].CustomStyle.Split(','); dataSourceStr += @" <asp:SqlDataSource ID=""ds" + view.Items[i].DataBinding.Substring(view.Items[i].DataBinding.IndexOf(".") + 1) + @""" runat=""server"" ConnectionString=""<%$ ConnectionStrings:DefaultConnection %>"" SelectCommand=""select * from (select '-1' id, 'กรุณาเลือก' txt "; foreach (var itemRow in itemRows) { if (itemRow.IndexOf(":") >= 0) { string[] itemCols = itemRow.Split(':'); dataSourceStr += " union all select '" + itemCols[0] + "','" + itemCols[1] + "' "; } } dataSourceStr += @") aa where id <> '-1'"" > </asp:SqlDataSource>"; } } #endregion #region Gen aspxContent aspxContent = @"<div> <h1>" + view.ViewTitle + @"</h1> </div> <div class=""col-sm-12""> " + htmlStr + @" </div> <div> " + dataSourceStr + @" </div>"; //<div> //" + buttonStr + @" //</div> #endregion #region Replace String //CodeFile="WebForm.aspx.cs" Inherits="Assets_ControlTemplates_WebForm" //MasterPageFile="~/Assets/ControlTemplates/MasterPageLayout.master" aspxStr = aspxStr.Replace("CodeFile=\"WebForm.aspx.cs\"", "CodeFile=\"" + view.ViewName + ".aspx.cs\""); aspxStr = aspxStr.Replace("Inherits=\"Assets_ControlTemplates_WebForm\"", "Inherits=\"" + view.ViewName + "\""); aspxStr = aspxStr.Replace("MasterPageFile=\"~/Assets/ControlTemplates/MasterPageLayout.master\"", "MasterPageFile=\"~/Master/bs.master\""); aspxStr = aspxStr.Replace("{content}", aspxContent).Replace("{head}", aspxHeader); //class Assets_ControlTemplates_WebForm csStr = csStr.Replace("class Assets_ControlTemplates_WebForm", "class " + view.ViewName + ""); csStr = csStr.Replace("{init}", "\r\n" + csInitData + ""); csStr = csStr.Replace("{PopulateDataStr}", "\r\n" + csPopulateData + ""); csStr = csStr.Replace("{PopulateNewDataStr}", "\r\n" + csPopulateNewData + ""); csStr = csStr.Replace("{Insert}", "\r\n" + csInsertData + ""); csStr = csStr.Replace("{Update}", "\r\n" + csUpdateData + ""); csStr = csStr.Replace("{ValidationStr}", "\r\n" + csValidateData + ""); csStr = csStr.Replace("{FormattingStr}", "\r\n" + csFormattingData + ""); csStr = csStr.Replace("{declare}", "\r\n" + csDeclareVar + ""); #endregion File.WriteAllText(fileAspx, aspxStr, System.Text.Encoding.UTF8); File.WriteAllText(fileCs, csStr, System.Text.Encoding.UTF8); return true; } else { return false; } }
private ExcelWorksheet CreateListView_XML(ExcelWorksheet worksheet, int row, string fieldformat, System.Drawing.Color colFromHex, int i, ExcelConfiguration conf) { worksheet.Column(1).Width = 15; worksheet.Column(2).Width = 15; worksheet.Cells["A1"].Value = "TableName"; worksheet.Cells["B1"].Value = conf.ViewList[i].TableName; worksheet.Cells["A2"].Value = "ViewType"; worksheet.Cells["B2"].Value = "ListView"; worksheet.Cells["A3"].Value = "ViewName"; worksheet.Cells["B3"].Value = conf.ViewList[i].ViewName; worksheet.Cells["A4"].Value = "Title"; worksheet.Cells["B4"].Value = conf.ViewList[i].ViewTitle; worksheet.Cells["A5"].Value = "Root Service URL"; worksheet.Cells["B5"].Value = conf.ViewList[i].RootServiceUrl; worksheet.Cells["A6"].Value = "Order By"; worksheet.Cells["B6"].Value = conf.ViewList[i].OrderBy; worksheet.Cells["A7"].Value = "Filter"; worksheet.Cells["B7"].Value = conf.ViewList[i].IsHasFilter; worksheet.Cells["A8"].Value = "PageSize"; worksheet.Cells["B8"].Value = conf.ViewList[i].PageSize; // worksheet.Cells["A9"].Value = "EditView"; worksheet.Cells["A10"].Value = "FilterView"; worksheet.Cells["A11"].Value = "InputParameters"; worksheet.Cells["A12"].Value = "FilterParameters"; worksheet.Cells["A13"].Value = "MasterPage"; worksheet.Cells["B9"].Value = conf.ViewList[i].EditView; worksheet.Cells["B10"].Value = conf.ViewList[i].FilterView; worksheet.Cells["B11"].Value = conf.ViewList[i].InputParameters; worksheet.Cells["B12"].Value = conf.ViewList[i].FilterParameters; worksheet.Cells["B13"].Value = conf.ViewList[i].MasterPage; // worksheet.Cells["A14"].Value = "ButtonNew"; worksheet.Cells["B14"].Value = conf.ViewList[i].IsHasNewButton; worksheet.Cells["C14"].Value = "Caption"; worksheet.Cells["D14"].Value = "เพิ่ม"; worksheet.Cells["E14"].Value = "AppearanceRules"; worksheet.Cells["F14"].Value = "TRUE"; worksheet.Cells["A15"].Value = "ButtonSave"; worksheet.Cells["B15"].Value = conf.ViewList[i].IsHasSaveButton; worksheet.Cells["C15"].Value = "Caption"; worksheet.Cells["D15"].Value = "บันทึก"; worksheet.Cells["E15"].Value = "AppearanceRules"; worksheet.Cells["F15"].Value = "FALSE"; worksheet.Cells["A16"].Value = "ButtonDelete"; worksheet.Cells["B16"].Value = conf.ViewList[i].IsHasDeleteButton; worksheet.Cells["C16"].Value = "Caption"; worksheet.Cells["D16"].Value = "ลบ"; worksheet.Cells["E16"].Value = "AppearanceRules"; worksheet.Cells["F16"].Value = "TRUE"; // worksheet.Cells["A17"].Value = "SqlSelect"; worksheet.Cells["A18"].Value = conf.ViewList[i].SQLSelect; worksheet.Cells["A19"].Value = "SqlUpdate"; worksheet.Cells["A20"].Value = conf.ViewList[i].SQLUpdate; worksheet.Cells["F17"].Value = "SqlSelectParameter"; worksheet.Cells["F18"].Value = conf.ViewList[i].SQLInsertParams; worksheet.Cells["F19"].Value = "SqlUpdateParameter"; worksheet.Cells["F20"].Value = conf.ViewList[i].SQLUpdateParams; // ////Add the headers worksheet.Cells["A21"].Value = "Caption"; worksheet.Cells["B21"].Value = "Column"; worksheet.Cells["C21"].Value = "Format"; worksheet.Cells["D21"].Value = "TextAlignment"; worksheet.Cells["E21"].Value = "IsHideResize"; worksheet.Cells["F21"].Value = "CustomStyle"; colFromHex = System.Drawing.ColorTranslator.FromHtml("#FFFF00"); worksheet.Cells["A21:F21"].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; worksheet.Cells["A21:F21"].Style.Fill.BackgroundColor.SetColor(colFromHex); row = 22; for(int j=0;j<conf.ViewList[i].Items.Count;j++) { //foreach (DataRow dr_columns in ds_columns.Tables["columns"].Rows) //{ worksheet.Cells["A" + row.ToString()].Value = conf.ViewList[i].Items[j].Caption; worksheet.Cells["B" + row.ToString()].Value = conf.ViewList[i].Items[j].DataBinding; worksheet.Cells["C" + row.ToString()].Value = conf.ViewList[i].Items[j].Formats; //worksheet.Cells["D" + row.ToString()].Value = conf.ViewList[i].Items[j].Formats; //worksheet.Cells["E" + row.ToString()].Value = conf.ViewList[i].Items[j].Formats; //worksheet.Cells["F" + row.ToString()].Value = conf.ViewList[i].Items[j].Formats; row++; } row--; worksheet.Cells["A1:F" + row.ToString()].Style.Border.Top.Style = ExcelBorderStyle.Thin; worksheet.Cells["A1:F" + row.ToString()].Style.Border.Right.Style = ExcelBorderStyle.Thin; worksheet.Cells["A1:F" + row.ToString()].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; worksheet.Cells["A1:F" + row.ToString()].Style.Border.Left.Style = ExcelBorderStyle.Thin; worksheet.Cells["A1:F" + row.ToString()].Style.Font.Name = "Tahoma"; worksheet.Cells["A1:F" + row.ToString()].Style.Font.Size = 10; return worksheet; }
public FormListStructure GenerateListViewStructure(ExcelConfiguration conf, string viewName, string directoryPath, bool isInListView) { FormListStructure frm = new FormListStructure(); ExcelUIViewList view = GetListView(conf, viewName); if (view != null) { string[] tables = view.TableName.Split(','); ExcelTableSpec tableSpec = GetTableSpec(conf, tables[0],0); string gridStr = ""; string buttonStr = ""; string select = ""; string from = tables[0]; string where = " 1=1 "; string sql = ""; string dataSourceStr = ""; string dsSelectParam = ""; #region Gen Grid var editView = GetDetailView(conf, view.EditView); try { string xx = editView.ViewName; } catch (Exception ex) { throw new ApplicationException("View " + view.ViewName + " ต้องการ View " + view.EditView + " ** แต่ไม่มี", ex); } var linkDetail = @"<a target=""_blank"" onclick=""OpenChildDetail('fancyBox" + view.ViewName + @"','" + view.EditView + @".aspx?" + editView.GetHyperlinkRequest() + @"', view" + view.ViewName + @");""><img src=""images/edit.png"" style=""border:none"" alt=""edit"" /></a>"; if (isInListView) { linkDetail = @"<a target=""_self"" href=""" + view.EditView + @".aspx?" + editView.GetHyperlinkRequest() + @"""><img src=""images/edit.png"" style=""border:none"" alt=""edit"" /></a>"; } gridStr = @" <dx:ASPxGridView ID=""view" + view.ViewName + @""" runat=""server"" Width=""100%"" AutoGenerateColumns=""False"" Styles-AlternatingRow-BackColor=""White"" Styles-Row-BackColor=""#D9EDF7"" DataSourceID=""ds" + view.ViewName + @""" KeyFieldName=""" + GetPrimaryKeyName(tableSpec) + @""" ClientInstanceName=""view" + view.ViewName + @""" Theme=""Metropolis""> <Columns> <dx:GridViewCommandColumn SelectAllCheckboxMode=""Page"" ShowClearFilterButton=""True"" ShowDeleteButton=""True"" ShowSelectCheckbox=""False"" ButtonType=""Image"" VisibleIndex=""0""> </dx:GridViewCommandColumn> <dx:GridViewDataTextColumn FieldName=""" + GetPrimaryKeyName(tableSpec) + @""" ReadOnly=""True"" Visible=""False"" VisibleIndex=""2""> <EditFormSettings Visible=""False"" /> </dx:GridViewDataTextColumn> <dx:GridViewDataTextColumn VisibleIndex=""1"" Caption=""""> <DataItemTemplate> " + linkDetail + @" </DataItemTemplate> </dx:GridViewDataTextColumn> "; int colNum = 2; foreach (var fd in view.Items) { colNum++; var f = GetFieldSpec(conf, tables[0], fd.DataBinding); //frm.ValidateCS += GetValidateString(conf, f.FieldName); frm.FomattingCS += GetFormattingListString(view, fd); try { if (f==null || f.FieldType == ExcelEntityFieldType.String) { gridStr += @" <dx:GridViewDataTextColumn Caption=""" + fd.Caption + @""" FieldName=""" + GetOnlyFieldName(fd) + @""" VisibleIndex=""" + colNum + @"""> </dx:GridViewDataTextColumn> "; } else if (f.FieldType == ExcelEntityFieldType.Number) { gridStr += @" <dx:GridViewDataSpinEditColumn Caption=""" + fd.Caption + @""" FieldName=""" + GetOnlyFieldName(fd) + @""" VisibleIndex=""" + colNum + @"""> </dx:GridViewDataSpinEditColumn> "; } else if (f.FieldType == ExcelEntityFieldType.DateTime) { gridStr += @" <dx:GridViewDataDateColumn Caption=""" + fd.Caption + @""" FieldName=""" + GetOnlyFieldName(fd) + @""" VisibleIndex=""" + colNum + @"""> </dx:GridViewDataDateColumn> "; } else if (f.FieldType == ExcelEntityFieldType.Boolean) { gridStr += @" <dx:GridViewDataCheckColumn Caption=""" + fd.Caption + @""" FieldName=""" + GetOnlyFieldName(fd) + @""" VisibleIndex=""" + colNum + @"""> </dx:GridViewDataCheckColumn> "; } } catch (Exception ex) { throw new ApplicationException("View " + view.ViewName + " ต้องการใช้ field [" + fd.DataBinding + "] แต่ไม่มีในตาราง [" + GetOnlyTableName(fd)+"]", ex); } } gridStr += @"</Columns> <Settings ShowFilterRow=""True"" ShowFilterRowMenu=""true"" ShowHeaderFilterButton=""false"" ShowGroupPanel=""False"" /> <SettingsBehavior ConfirmDelete=""true"" AllowSelectSingleRowOnly=""true"" /> <SettingsText ConfirmDelete=""ท่านต้องการลบรายการนี้หรือไม่?"" /> <SettingsCommandButton> <DeleteButton> <Image ToolTip=""ลบ"" Url=""Images/del.png"" /> </DeleteButton> </SettingsCommandButton> </dx:ASPxGridView> <a id=""fancyBox" + view.ViewName + @""" class='fancyBox' style=""display:none""></a> "; #endregion #region Gen sql dataSourceStr if (view.SQLSelect == null || view.SQLSelect == "") { ExcelTableSpec tb = GetTableSpec(conf, tables[0], 0); foreach (var fd in tb.Fields) { if (fd.FieldPrimaryKey || fd.FieldForeignKey) { select += tb.TableName + "." + fd.FieldName + "\r\n,"; } } if (tables.Length > 1) { for (int i = 1; i < tables.Length; ++i) { from += GetJoinStr(conf, tables[0], tables[i]); } } foreach (var item in view.Items) { if (item.DataBinding != null && item.DataBinding != "") { select += item.DataBinding + "\r\n,"; } } select = select.Substring(0, select.Length - 1); if (view.InputParameters != null && view.InputParameters != "") { string[] fds = view.InputParameters.Split(','); foreach (var f in fds) { where += " AND " + f + "=@" + f.Substring(f.IndexOf('.') + 1); dsSelectParam += "<asp:Parameter Name=\"" + f.Substring(f.IndexOf('.') + 1) + "\" Type=\"Int32\" />"; } } if (view.FilterParameters != null && view.FilterParameters != "") { string[] fds = view.FilterParameters.Split(','); foreach (var f in fds) { where += " AND " + f + "=@" + f.Substring(f.IndexOf('.') + 1); dsSelectParam += "<asp:Parameter Name=\"" + f.Substring(f.IndexOf('.') + 1) + "\" Type=\"Int32\" />"; } } sql = "select " + select + " from " + from + " where " + where; } else { sql = view.SQLSelect; } //DefaultConnection /* <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DefaultConnection %>" DeleteCommand="DELETE FROM [COMMON_MASTER_ORGANIZE] WHERE [ORGANIZE_ID] = @PK_ID" SelectCommand="SELECT * FROM [COMMON_MASTER_ORGANIZE]" <DeleteParameters> <asp:Parameter Name="ORGANIZE_ID" Type="Int32" /> </DeleteParameters> </asp:SqlDataSource> */ dataSourceStr = @" <asp:SqlDataSource ID=""ds" + view.ViewName + @""" runat=""server"" ConnectionString=""<%$ ConnectionStrings:DefaultConnection %>"" DeleteCommand=""DELETE FROM " + tables[0] + @" WHERE [" + GetPrimaryKeyName(tableSpec) + @"] = @" + GetPrimaryKeyName(tableSpec) + @""" SelectCommand=""" + sql + @""" > <DeleteParameters> <asp:Parameter Name=""" + GetPrimaryKeyName(tableSpec) + @""" Type=""Int32"" /> </DeleteParameters> <SelectParameters> " + dsSelectParam + @" </SelectParameters> </asp:SqlDataSource>"; #endregion #region Gen PopulateData //frm.PopulateCS += "//Grid \r\n"; //frm.PopulateCS += "ds" + view.ViewName + ".SelectParameters.Clear();\r\n"; frm.InitCS += "//Grid "+ view.ViewName+" \r\n"; frm.InitCS += "ds" + view.ViewName + ".SelectParameters.Clear();\r\n"; if (view.InputParameters != null && view.InputParameters != "") { string[] fds = view.InputParameters.Split(','); foreach (var f in fds) { frm.VariableDeclareCS += "string input" + f.Substring(f.IndexOf('.') + 1) + "=\"-1\";\r\n"; //frm.PopulateCS += "ds" + view.ViewName + ".SelectParameters.Add(\"" + f.Substring(f.IndexOf('.') + 1) + "\", System.Data.DbType.Int32, input" + f.Substring(f.IndexOf('.') + 1) + ");\r\n"; frm.InitCS += @" if (Request.QueryString[""" + f.Substring(f.IndexOf('.') + 1) + @"""] != null && Request.QueryString[""" + f.Substring(f.IndexOf('.') + 1) + @"""] != ""-1"") { input" + f.Substring(f.IndexOf('.') + 1) + @" = Request.QueryString[""" + f.Substring(f.IndexOf('.') + 1) + @"""]; } "; frm.InitCS += "ds" + view.ViewName + ".SelectParameters.Add(\"" + f.Substring(f.IndexOf('.') + 1) + "\", System.Data.DbType.Int32, input" + f.Substring(f.IndexOf('.') + 1) + ");\r\n"; } } if (view.FilterParameters != null && view.FilterParameters != "") { string[] fds = view.FilterParameters.Split(','); foreach (var f in fds) { frm.VariableDeclareCS += "string param" + f.Substring(f.IndexOf('.') + 1) + "=\"-1\";\r\n"; //frm.PopulateCS += "ds" + view.ViewName + ".SelectParameters.Add(\"" + f.Substring(f.IndexOf('.') + 1) + "\", System.Data.DbType.Int32, param" + f.Substring(f.IndexOf('.') + 1) + ");\r\n"; frm.InitCS += "ds" + view.ViewName + ".SelectParameters.Add(\"" + f.Substring(f.IndexOf('.') + 1) + "\", System.Data.DbType.Int32, param" + f.Substring(f.IndexOf('.') + 1) + ");\r\n"; } } if (view.SQLSelectParams != null && view.SQLSelectParams != "") { string[] fds = view.SQLSelectParams.Split(','); foreach (var f in fds) { frm.VariableDeclareCS += "string param" + f.Substring(f.IndexOf('.') + 1) + "=\"-1\";\r\n"; frm.InitCS += "ds" + view.ViewName + ".SelectParameters.Add(\"" + f.Substring(f.IndexOf('.') + 1) + "\", System.Data.DbType.Int32, param" + f.Substring(f.IndexOf('.') + 1) + ");\r\n"; } } #endregion #region Gen button buttonStr += "";// @"<input type=""button"" id=""btn" + view.ViewName + @"_new"" class='btn btn-primary' value=' Add ' onclick=""location='" + view.EditView + ".aspx?id=-1';\" />"; #endregion frm.HtmlTag = @"<div> " + gridStr + @" </div>"; frm.DataSourceTag = dataSourceStr; frm.TopButtonTag = "";// buttonStr; return frm; } else { return frm; } }
public bool GenerateLookupView(ExcelConfiguration conf, string viewName, string directoryPath) { ExcelUIViewLookup view = GetLookupView(conf, viewName); if (view != null) { string fileAspx = directoryPath + "\\" + view.ViewName + ".aspx"; string fileCs = directoryPath + "\\" + view.ViewName + ".aspx.cs"; string fileAspxTemplate = ConfigurationManager.AppSettings["HomePath"] + @"\Assets\ControlTemplates\WebFormLku.aspx.exclude"; string fileCsTemplate = ConfigurationManager.AppSettings["HomePath"] + @"\Assets\ControlTemplates\WebFormLku.aspx.cs.exclude"; string aspxStr = File.ReadAllText(fileAspxTemplate); string csStr = File.ReadAllText(fileCsTemplate); string aspxContent = ""; string aspxHeader = ""; string buttonStr = ""; string[] tables = view.TableName.Split(','); ExcelTableSpec tableSpec = GetTableSpec(conf, tables[0], 0); FormListStructure frmList = GenerateLookupViewStructure(conf, viewName, directoryPath); if (view.InputParameters != null && view.InputParameters != "") { string[] inputs = view.InputParameters.Split(','); foreach (var f in inputs) { frmList.VariableDeclareCS += "\tstring key=\"" + f.Substring(f.IndexOf('.') + 1) + "\";\r\n"; break; } } else { frmList.VariableDeclareCS += "\tstring key=\"id\";\r\n"; } #region Gen button buttonStr += "";// @"<input type=""button"" class='btn btn-primary' value=' Add ' onclick=''; />"; #endregion #region Gen aspxContent aspxContent = @"<div> <h1>" + view.ViewTitle + @"</h1> </div> <div> " + buttonStr + @" </div> <div> " + frmList.HtmlTag + @" </div> <div> " + frmList.DataSourceTag + @" </div>"; #endregion #region Replace String //CodeFile="WebForm.aspx.cs" Inherits="Assets_ControlTemplates_WebForm" //MasterPageFile="~/Assets/ControlTemplates/MasterPageLayout.master" aspxStr = aspxStr.Replace("CodeFile=\"WebForm.aspx.cs\"", "CodeFile=\"" + view.ViewName + ".aspx.cs\""); aspxStr = aspxStr.Replace("Inherits=\"Assets_ControlTemplates_WebForm\"", "Inherits=\"" + view.ViewName + "\""); aspxStr = aspxStr.Replace("MasterPageFile=\"~/Assets/ControlTemplates/MasterPageLayout.master\"", "MasterPageFile=\"~/Master/blank.master\""); aspxStr = aspxStr.Replace("{content}", aspxContent).Replace("{head}", aspxHeader); aspxStr = aspxStr.Replace("{fieldpk}", tableSpec.PrimaryKey); aspxStr = aspxStr.Replace("{fielddisplay}", tableSpec.DefaultDisplay); //class Assets_ControlTemplates_WebForm csStr = csStr.Replace("class Assets_ControlTemplates_WebForm", "class " + view.ViewName + ""); csStr = csStr.Replace("{PopulateDataStr}", "\r\n" + frmList.PopulateCS + ""); csStr = csStr.Replace("{declare}", "\r\n" + frmList.VariableDeclareCS + ""); csStr = csStr.Replace("{init}", "\r\n" + frmList.InitCS + ""); #endregion File.WriteAllText(fileAspx, aspxStr); File.WriteAllText(fileCs, csStr); return true; } else { return false; } }
public FormListStructure GenerateLookupViewStructure(ExcelConfiguration conf, string viewName, string directoryPath) { FormListStructure frm = new FormListStructure(); ExcelUIViewLookup view = GetLookupView(conf, viewName); if (view != null) { string[] tables = view.TableName.Split(','); ExcelTableSpec tableSpec = GetTableSpec(conf, tables[0],0); string gridStr = ""; string buttonStr = ""; string select = ""; string from = tables[0]; string where = " 1=1 "; string sql = ""; string dataSourceStr = ""; string dsSelectParam = ""; #region Gen Grid gridStr = @" <dx:ASPxGridView ID=""view" + view.ViewName + @""" runat=""server"" Width=""100%"" Styles-AlternatingRow-BackColor=""White"" Styles-Row-BackColor=""#D9EDF7"" AutoGenerateColumns=""False"" ClientInstanceName=""view" + view.ViewName + @""" DataSourceID=""ds" + view.ViewName + @""" KeyFieldName=""" + GetPrimaryKeyName(tableSpec) + @""" Theme=""Metropolis""> <Columns> <dx:GridViewCommandColumn ShowSelectButton =""True"" SelectAllCheckboxMode=""Page"" ShowClearFilterButton=""True"" ShowDeleteButton=""False"" ShowSelectCheckbox=""False"" ButtonType=""Image"" VisibleIndex=""30""> </dx:GridViewCommandColumn> <dx:GridViewDataTextColumn FieldName=""" + GetPrimaryKeyName(tableSpec) + @""" ReadOnly=""True"" Visible=""False"" VisibleIndex=""2""> <EditFormSettings Visible=""False"" /> </dx:GridViewDataTextColumn> "; int colNum = 2; foreach (var fd in view.Items) { colNum++; gridStr += @" <dx:GridViewDataTextColumn Caption=""" + fd.Caption + @""" FieldName=""" + GetOnlyFieldName(fd) + @""" VisibleIndex=""" + colNum + @"""></dx:GridViewDataTextColumn> "; } gridStr += @"</Columns> <Settings ShowFilterRow=""True"" ShowGroupPanel=""False"" /> <SettingsBehavior ConfirmDelete=""true"" AllowSelectSingleRowOnly=""true"" /> <SettingsCommandButton> <SelectButton> <Image ToolTip=""เลือก"" Url=""Images/select.png"" /> </SelectButton> </SettingsCommandButton> <ClientSideEvents SelectionChanged=""grid_SelectionChanged"" /> </dx:ASPxGridView> "; #endregion #region Gen sql dataSourceStr if (view.SQLSelect == null || view.SQLSelect == "") { foreach (var fd in tableSpec.Fields) { if (fd.FieldPrimaryKey || fd.FieldForeignKey) { select += tableSpec.TableName + "." + fd.FieldName + "\r\n,"; } } if (tables.Length > 1) { for (int i = 1; i < tables.Length; ++i) { from += GetJoinStr(conf, tables[0], tables[i]); } } foreach (var item in view.Items) { if (item.DataBinding != null && item.DataBinding != "") { select += item.DataBinding + "\r\n,"; } } select = select.Substring(0, select.Length - 1); if (view.InputParameters != null && view.InputParameters != "") { string[] fds = view.InputParameters.Split(','); foreach (var f in fds) { where += " AND " + f + "=@" + f.Substring(f.IndexOf('.') + 1); dsSelectParam += "<asp:Parameter Name=\"" + f.Substring(f.IndexOf('.') + 1) + "\" Type=\"Int32\" />"; } } if (view.FilterParameters != null && view.FilterParameters != "") { string[] fds = view.FilterParameters.Split(','); foreach (var f in fds) { where += " AND " + f + "=@" + f.Substring(f.IndexOf('.') + 1); dsSelectParam += "<asp:Parameter Name=\"" + f.Substring(f.IndexOf('.') + 1) + "\" Type=\"Int32\" />"; } } sql = "select " + select + " from " + from + " where " + where; } else { sql = view.SQLSelect; if (view.SQLSelectParams != null && view.SQLSelectParams != "") { string[] fds = view.SQLSelectParams.Split(','); foreach (var f in fds) { dsSelectParam += "<asp:Parameter Name=\"" + f.Substring(f.IndexOf('.') + 1) + "\" Type=\"Int32\" />"; } } } //DefaultConnection /* <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DefaultConnection %>" DeleteCommand="DELETE FROM [COMMON_MASTER_ORGANIZE] WHERE [ORGANIZE_ID] = @PK_ID" SelectCommand="SELECT * FROM [COMMON_MASTER_ORGANIZE]" <DeleteParameters> <asp:Parameter Name="ORGANIZE_ID" Type="Int32" /> </DeleteParameters> </asp:SqlDataSource> */ dataSourceStr = @"<asp:SqlDataSource ID=""ds" + view.ViewName + @""" runat=""server"" ConnectionString=""<%$ ConnectionStrings:DefaultConnection %>"" DeleteCommand=""DELETE FROM " + tables[0] + @" WHERE [" + GetPrimaryKeyName(tableSpec) + @"] = @" + GetPrimaryKeyName(tableSpec) + @""" SelectCommand=""" + sql + @""" > <DeleteParameters> <asp:Parameter Name=""" + GetPrimaryKeyName(tableSpec) + @""" Type=""Int32"" /> </DeleteParameters> <SelectParameters> " + dsSelectParam + @" </SelectParameters> </asp:SqlDataSource>"; #endregion #region Gen PopulateData //frm.PopulateCS += "//Grid \r\n"; //frm.PopulateCS += "ds" + view.ViewName + ".SelectParameters.Clear();\r\n"; frm.InitCS += "//Grid " + view.ViewName + " \r\n"; frm.InitCS += "ds" + view.ViewName + ".SelectParameters.Clear();\r\n"; if (view.InputParameters != null && view.InputParameters != "") { string[] fds = view.InputParameters.Split(','); foreach (var f in fds) { frm.VariableDeclareCS += "string input" + f.Substring(f.IndexOf('.') + 1) + "=\"-1\";\r\n"; //frm.PopulateCS += "ds" + view.ViewName + ".SelectParameters.Add(\"" + f.Substring(f.IndexOf('.') + 1) + "\", System.Data.DbType.Int32, input" + f.Substring(f.IndexOf('.') + 1) + ");\r\n"; frm.InitCS += @" if (Request.QueryString[""" + f.Substring(f.IndexOf('.') + 1) + @"""] != null && Request.QueryString[""" + f.Substring(f.IndexOf('.') + 1) + @"""] != ""-1"") { input" + f.Substring(f.IndexOf('.') + 1) + @" = Request.QueryString[""" + f.Substring(f.IndexOf('.') + 1) + @"""]; } "; frm.InitCS += "ds" + view.ViewName + ".SelectParameters.Add(\"" + f.Substring(f.IndexOf('.') + 1) + "\", System.Data.DbType.Int32, input" + f.Substring(f.IndexOf('.') + 1) + ");\r\n"; } } if (view.FilterParameters != null && view.FilterParameters != "") { string[] fds = view.FilterParameters.Split(','); foreach (var f in fds) { frm.VariableDeclareCS += "string param" + f.Substring(f.IndexOf('.') + 1) + "=\"-1\";\r\n"; //frm.PopulateCS += "ds" + view.ViewName + ".SelectParameters.Add(\"" + f.Substring(f.IndexOf('.') + 1) + "\", System.Data.DbType.Int32, param" + f.Substring(f.IndexOf('.') + 1) + ");\r\n"; frm.InitCS += "ds" + view.ViewName + ".SelectParameters.Add(\"" + f.Substring(f.IndexOf('.') + 1) + "\", System.Data.DbType.Int32, param" + f.Substring(f.IndexOf('.') + 1) + ");\r\n"; } } if (view.SQLSelectParams != null && view.SQLSelectParams != "") { string[] fds = view.SQLSelectParams.Split(','); foreach (var f in fds) { frm.VariableDeclareCS += "string param" + f.Substring(f.IndexOf('.') + 1) + "=\"-1\";\r\n"; frm.InitCS += "ds" + view.ViewName + ".SelectParameters.Add(\"" + f.Substring(f.IndexOf('.') + 1) + "\", System.Data.DbType.Int32, param" + f.Substring(f.IndexOf('.') + 1) + ");\r\n"; } } #endregion #region Gen button //buttonStr += @"<input type=""button"" id=""btn" + view.ViewName + @"_new"" class='btn btn-primary' value=' Add ' onclick="""" />"; #endregion frm.HtmlTag = @"<div> " + gridStr + @" </div>"; frm.DataSourceTag = dataSourceStr; frm.TopButtonTag = buttonStr; return frm; } else { return frm; } }
protected void ASPxButton1_Click(object sender, EventArgs e) { //list inline ExcelConfiguration xlsConf = new ExcelConfiguration(); //ExcelConfiguration xlsConf = ExcelConfiguration.GetExcelConfigurationXls("c:\\pj.xls"); var tb1 = new ExcelTableSpec(); tb1.TableName = "COMMON_MASTER_REGION"; tb1.DefaultDisplay = "REGION_NAME_THA"; tb1.Fields.Add(new ExcelFieldSpec() { FieldName = "REGION_ID", FieldType = ExcelEntityFieldType.Number, FieldPrimaryKey = true, FieldTypeString="Int32" }); tb1.Fields.Add(new ExcelFieldSpec() { FieldName = "REGION_NAME_THA", FieldType = ExcelEntityFieldType.String }); tb1.Fields.Add(new ExcelFieldSpec() { FieldName = "REGION_NAME_ENG", FieldType = ExcelEntityFieldType.String }); tb1.Fields.Add(new ExcelFieldSpec() { FieldName = "UPDATE_DATE", FieldType = ExcelEntityFieldType.DateTime }); var tb2 = new ExcelTableSpec(); tb2.TableName = "COMMON_MASTER_PROVINCE"; tb2.DefaultDisplay = "PROVINCE_NAME";// tb2.Fields.Add(new ExcelFieldSpec() { FieldName = "PROVINCE_ID", FieldType = ExcelEntityFieldType.Number, FieldPrimaryKey = true, FieldTypeString="Int32" }); tb2.Fields.Add(new ExcelFieldSpec() { FieldName = "REGION_ID", FieldType = ExcelEntityFieldType.Number, FieldForeignKey = true, FieldForeignKeyTableName = "COMMON_MASTER_REGION" }); tb2.Fields.Add(new ExcelFieldSpec() { FieldName = "PROVINCE_CODE", FieldType = ExcelEntityFieldType.String, IsRequireField = true, MaxLength = 2, MinLength = 2 }); tb2.Fields.Add(new ExcelFieldSpec() { FieldName = "PROVINCE_NAME", FieldType = ExcelEntityFieldType.String, IsRequireField = true }); tb2.Fields.Add(new ExcelFieldSpec() { FieldName = "UPDATE_DATE", FieldType = ExcelEntityFieldType.DateTime }); tb2.Fields.Add(new ExcelFieldSpec() { FieldName = "OptimisticLockField", FieldType = ExcelEntityFieldType.Number }); var tb3 = new ExcelTableSpec(); tb3.TableName = "COMMON_MASTER_DISTRICT"; tb3.DefaultDisplay = "DISTRICT_NAME";// tb3.Fields.Add(new ExcelFieldSpec() { FieldName = "DISTRICT_ID", FieldType = ExcelEntityFieldType.Number, FieldPrimaryKey = true, FieldTypeString = "Int32" }); tb3.Fields.Add(new ExcelFieldSpec() { FieldName = "PROVINCE_ID", FieldType = ExcelEntityFieldType.Number, FieldForeignKey = true, FieldForeignKeyTableName = "COMMON_MASTER_PROVINCE" }); tb3.Fields.Add(new ExcelFieldSpec() { FieldName = "DISTRICT_CODE", FieldType = ExcelEntityFieldType.String, IsRequireField = true, MaxLength = 100, MinLength = 20 }); tb3.Fields.Add(new ExcelFieldSpec() { FieldName = "DISTRICT_NAME", FieldType = ExcelEntityFieldType.String, IsRequireField = true }); tb3.Fields.Add(new ExcelFieldSpec() { FieldName = "UPDATE_DATE", FieldType = ExcelEntityFieldType.DateTime }); tb3.Fields.Add(new ExcelFieldSpec() { FieldName = "OptimisticLockField", FieldType = ExcelEntityFieldType.Number }); var l1 = new ExcelUIViewList(); l1.ViewName = "COMMON_MASTER_PROVINCE_List2"; l1.ViewTitle = "PROVINCE"; l1.ViewType = "ListView"; l1.TableName = "COMMON_MASTER_PROVINCE,COMMON_MASTER_REGION"; l1.EditView = "COMMON_MASTER_PROVINCE_Detail"; l1.InputParameters = ""; l1.IsHasNewButton = true; l1.IsHasSaveButton = true; l1.IsHasDeleteButton = true; l1.NewButtonCaption = "New"; l1.SaveButtonCaption = "Save"; l1.DeleteButtonCaption = "Delete"; l1.Items.Add(new ExcelUIViewListItem() { DataBinding = "COMMON_MASTER_PROVINCE.PROVINCE_CODE", Caption = "PROVINCE CODE", RowNumber = 1, TextAlignment = "left" }); l1.Items.Add(new ExcelUIViewListItem() { DataBinding = "COMMON_MASTER_PROVINCE.PROVINCE_NAME", Caption = "PROVINCE NAME", RowNumber = 2, TextAlignment = "left" }); l1.Items.Add(new ExcelUIViewListItem() { DataBinding = "COMMON_MASTER_REGION.REGION_ID", Caption = "REGION ID", RowNumber = 3, TextAlignment = "left" }); l1.Items.Add(new ExcelUIViewListItem() { DataBinding = "COMMON_MASTER_REGION.REGION_NAME_THA", Caption = "REGION ID", RowNumber = 4, TextAlignment = "left" }); var l2 = new ExcelUIViewList(); l2.ViewName = "COMMON_MASTER_DISTRICT_List"; l2.ViewTitle = "DISTRICT"; l2.ViewType = "ListView"; l2.TableName = "COMMON_MASTER_DISTRICT,COMMON_MASTER_PROVINCE"; l2.EditView = "COMMON_MASTER_DISTRICT_Detail"; l2.InputParameters = "COMMON_MASTER_DISTRICT.PROVINCE_ID"; l2.IsHasNewButton = true; l2.IsHasSaveButton = true; l2.IsHasDeleteButton = true; l2.NewButtonCaption = "New"; l2.SaveButtonCaption = "Save"; l2.DeleteButtonCaption = "Delete"; l2.Items.Add(new ExcelUIViewListItem() { DataBinding = "COMMON_MASTER_DISTRICT.DISTRICT_CODE", Caption = "code", RowNumber = 1, TextAlignment = "left" }); l2.Items.Add(new ExcelUIViewListItem() { DataBinding = "COMMON_MASTER_DISTRICT.DISTRICT_NAME", Caption = "name", RowNumber = 2, TextAlignment = "left" }); l2.Items.Add(new ExcelUIViewListItem() { DataBinding = "COMMON_MASTER_DISTRICT.UPDATE_DATE", Caption = "updatetime", RowNumber = 3, TextAlignment = "left" }); var d1 = new ExcelUIViewDetail(); d1.ViewName = "COMMON_MASTER_PROVINCE_Detail"; d1.ViewTitle = "PROVINCE"; d1.TableName = "COMMON_MASTER_PROVINCE,COMMON_MASTER_REGION"; d1.IsHasNewButton = true; d1.IsHasSaveButton = true; d1.IsHasDeleteButton = true; d1.InputParameters = "COMMON_MASTER_PROVINCE.PROVINCE_ID"; d1.NewButtonCaption = "New"; d1.SaveButtonCaption = "Save"; d1.DeleteButtonCaption = "Delete"; d1.Items.Add(new ExcelUIViewDetailItem() { RowNumber = 1, ElementType = "tab", ElementId="tabMain1", Level=1, LevelValue="tab12"}); d1.Items.Add(new ExcelUIViewDetailItem() { RowNumber = 2, ElementType = "tabpage", ElementId="tab1", Level = 2, LevelValue = "tabpage" , Caption="Tab1", CustomStyle="active"}); d1.Items.Add(new ExcelUIViewDetailItem() { RowNumber = 3, ElementType = "box", Level = 3, LevelValue = "box12", Caption="Box1" }); d1.Items.Add(new ExcelUIViewDetailItem() { RowNumber = 4, ElementType = "textbox", Level=3, LevelValue="field12", DataBinding = "COMMON_MASTER_PROVINCE.PROVINCE_CODE", Caption = "PROVINCE CODE", Formats="0-0", TextAlignment="right" }); d1.Items.Add(new ExcelUIViewDetailItem() { RowNumber = 5, ElementType = "textbox", Level=3, LevelValue="field12", DataBinding = "COMMON_MASTER_PROVINCE.PROVINCE_NAME", Caption = "PROVINCE NAME"}); d1.Items.Add(new ExcelUIViewDetailItem() { RowNumber = 6, ElementType = "dropdown", Level=3, LevelValue="field12", DataBinding = "COMMON_MASTER_PROVINCE.REGION_ID", Caption = "REGION", Width=200 }); d1.Items.Add(new ExcelUIViewDetailItem() { RowNumber = 7, ElementType = "datepicker", Level = 3, LevelValue = "field12", DataBinding = "COMMON_MASTER_PROVINCE.UPDATE_DATE", Caption = "UPDATE DATE", Width = 200 }); d1.Items.Add(new ExcelUIViewDetailItem() { RowNumber = 8, ElementType = "textint", Level = 3, LevelValue = "field12", DataBinding = "COMMON_MASTER_PROVINCE.OptimisticLockField", Caption = "OptimisticLockField", Width=200, Formats = "N2", TextAlignment = "right" }); d1.Items.Add(new ExcelUIViewDetailItem() { RowNumber = 9, ElementType = "listview", Level = 3, LevelValue = "COMMON_MASTER_DISTRICT_List", Caption = "DISTRICT" }); d1.Items.Add(new ExcelUIViewDetailItem() { RowNumber = 7, ElementType = "endbox", Level = 3, LevelValue = "endbox" }); d1.Items.Add(new ExcelUIViewDetailItem() { RowNumber = 8, ElementType = "endtabpage", Level = 2, LevelValue = "endtabpage" }); d1.Items.Add(new ExcelUIViewDetailItem() { RowNumber = 9, ElementType = "tabpage", ElementId="tab2", Level = 2, LevelValue = "tabpage", Caption="Tab2" }); d1.Items.Add(new ExcelUIViewDetailItem() { RowNumber = 10, ElementType = "box", Level = 3, LevelValue = "box12", Caption="Box2" }); d1.Items.Add(new ExcelUIViewDetailItem() { RowNumber = 11, ElementType = "button", Level = 3, LevelValue = "field12", Caption="button xxx" }); d1.Items.Add(new ExcelUIViewDetailItem() { RowNumber = 12, ElementType = "endbox", Level = 3, LevelValue = "endbox" }); d1.Items.Add(new ExcelUIViewDetailItem() { RowNumber = 13, ElementType = "endtabpage", Level = 2, LevelValue = "endtabpage" }); d1.Items.Add(new ExcelUIViewDetailItem() { RowNumber = 14, ElementType = "endtab", Level = 1, LevelValue = "endtab" }); var d2 = new ExcelUIViewDetail(); d2.ViewName = "COMMON_MASTER_DISTRICT_Detail"; d2.ViewTitle = "DISTRICT_Detail"; d2.InputParameters = "COMMON_MASTER_DISTRICT.DISTRICT_ID,COMMON_MASTER_DISTRICT.PROVINCE_ID"; d2.TableName = "COMMON_MASTER_DISTRICT"; d2.IsHasNewButton = true; d2.IsHasSaveButton = true; d2.IsHasDeleteButton = true; d2.NewButtonCaption = "New"; d2.SaveButtonCaption = "Save"; d2.DeleteButtonCaption = "Delete"; d2.Items.Add(new ExcelUIViewDetailItem() { RowNumber = 4, ElementType = "textbox", Level = 3, LevelValue = "field12", DataBinding = "COMMON_MASTER_DISTRICT.DISTRICT_CODE", Caption = "DISTRICT CODE", Formats = "00", TextAlignment = "right" }); d2.Items.Add(new ExcelUIViewDetailItem() { RowNumber = 5, ElementType = "textbox", Level = 3, LevelValue = "field12", DataBinding = "COMMON_MASTER_DISTRICT.DISTRICT_NAME", Caption = "DISTRICT NAME" }); xlsConf.Tables.Add(tb1); xlsConf.Tables.Add(tb2); xlsConf.Tables.Add(tb3); xlsConf.ViewList.Add(l1); xlsConf.ViewList.Add(l2); xlsConf.ViewDetails.Add(d1); xlsConf.ViewDetails.Add(d2); FormGenerator gen = new FormGenerator(); gen.GenerateDetailView(xlsConf, "COMMON_MASTER_PROVINCE_Detail", @"C:\Users\JOJOJS\Documents\Visual Studio 2012\WebSites\eForm20\Test"); gen.GenerateDetailView(xlsConf, "COMMON_MASTER_DISTRICT_Detail", @"C:\Users\JOJOJS\Documents\Visual Studio 2012\WebSites\eForm20\Test"); if (gen.GenerateListView(xlsConf, "COMMON_MASTER_PROVINCE_List2", @"C:\Users\JOJOJS\Documents\Visual Studio 2012\WebSites\eForm20\Test")) { Response.Redirect("COMMON_MASTER_PROVINCE_List2.aspx"); } //gen.GenerateDetailView(xlsConf, "COMMON_MASTER_PROVINCE_Detail", // @"C:\Users\JOJOJS\Documents\Visual Studio 2012\WebSites\eForm20\Test"); }
ExcelFieldSpec GetFieldSpec(ExcelConfiguration conf, string tablename, string fieldname) { foreach (var tb in conf.Tables) { if (tb.TableName.ToUpper() == tablename.ToUpper()) { foreach (var fd in tb.Fields) { if(fd.FieldName.ToUpper() == fieldname.ToUpper()) return fd; } break; } } return null; }