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