public static void SortDescending(ExcelTemplateView view, IBindingContextItem contextItem)
{
ITemplateDefinition templateDefinition = contextItem.ParentElement.ParentPart.TemplateDefinitionPart.Parent;
ISorterDefinition sortDefinition = SortDefinitionFactory.CreateInstance(templateDefinition, contextItem.BindingDefinition, true, false);
ExecuteSort(view, sortDefinition);
}
public AccessorParametersManager(ExcelTemplateView view, IEnumerable <object> parameters)
{
View = view;
Parameters = parameters;
if (Parameters != null && Parameters.Any())
{
foreach (object param in Parameters)
{
if (param is ExcelInterop.Range)
{
rangesToListen.Add(param as ExcelInterop.Range);
}
else if (param.GetType().GetInterfaces().Any(i => i.IsGenericType && i.GetGenericTypeDefinition() == typeof(IEnumerable <>)))
{
Type genericType = param.GetType().GetGenericArguments()[0];
MethodInfo convertCollection = typeof(TypeConvertor).GetMethod("ConvertCollection").MakeGenericMethod(genericType);
IEnumerable <ExcelInterop.Range> ranges = convertCollection.Invoke(null, new object[] { genericType, param }) as IEnumerable <ExcelInterop.Range>;
rangesToListen.AddRange(ranges);
}
}
if (rangesToListen.Count > 0)
{
ExcelInterop.Worksheet sheet = View.ViewSheet;
sheet.Change += OnParametersChanged;
ExcelApplication.ReleaseComObject(sheet);
sheet = null;
}
}
}
public ExcelNotityPropertyContext(IBindingContextItem contextItem, ExcelTemplateView view, KeyValuePair <int, int> param, bool changeColor = false)
{
ContextItem = contextItem;
View = view;
Param = param;
ChangeColor = changeColor;
}
private ExcelTemplateView CreateView(ExcelInterop.Worksheet sheetContainer, string templateName, ExcelInterop.Worksheet sheetDestination, ExcelInterop.Range firstOutputCell, ExcelInterop.Range clearingCell)
{
if (sheetContainer == null)
{
throw new ArgumentNullException("Template container sheet is mandatory");
}
if (string.IsNullOrEmpty(templateName))
{
throw new ArgumentNullException("Template name is mandatory");
}
if (sheetDestination == null)
{
throw new ArgumentNullException("Template destination sheet is mandatory");
}
if (firstOutputCell == null)
{
throw new ArgumentNullException("Template first output cell is mandatory");
}
if (clearingCell == null)
{
try
{
clearingCell = sheetContainer.Cells[1, 1];
}
catch
{
throw new ArgumentException("The clearing cell value");
}
}
ExcelInterop.Range range = sheetContainer.Cells.Find(string.Format(TEMPLATE_START_FORMAT, templateName), Type.Missing, ExcelInterop.XlFindLookIn.xlValues, ExcelInterop.XlLookAt.xlPart, ExcelInterop.XlSearchOrder.xlByRows, ExcelInterop.XlSearchDirection.xlNext, false);
if (range == null)
{
throw new EtkException($"Cannot find the template '{templateName.EmptyIfNull()}' in sheet '{sheetContainer.Name.EmptyIfNull()}'");
}
string templateDescriptionKey = $"{sheetContainer.Name}-{templateName}";
TemplateDefinition templateDefinition = bindingTemplateManager.GetTemplateDefinition(templateDescriptionKey);
if (templateDefinition == null)
{
templateDefinition = ExcelTemplateDefinitionFactory.CreateInstance(templateName, range);
bindingTemplateManager.RegisterTemplateDefinition(templateDefinition);
}
ExcelTemplateView view = new ExcelTemplateView(templateDefinition, sheetDestination, firstOutputCell, clearingCell);
bindingTemplateManager.AddView(view);
log.LogFormat(LogType.Debug, "Sheet '{0}', View '{1}'.'{2}' created.", sheetDestination.Name.EmptyIfNull(), sheetContainer.Name.EmptyIfNull(), templateName.EmptyIfNull());
range = null;
return(view);
}
/// <summary> Implements <see cref="IExcelTemplateManager.RemoveView"/> </summary>
public void RemoveView(IExcelTemplateView view)
{
ExcelTemplateView excelView = view as ExcelTemplateView;
if (excelView == null)
{
return;
}
RemoveViews(new IExcelTemplateView[] { view });
}
/// <summary> Implements <see cref="IExcelTemplateManager.ClearViews"/> </summary>
public void ClearViews(IEnumerable <IExcelTemplateView> views)
{
if (views == null)
{
return;
}
views = views.Where(v => v != null);
if (!views.Any())
{
return;
}
try
{
lock (syncRoot)
{
if (ExcelApplication.IsInEditMode())
{
ExcelApplication.DisplayMessageBox(null, "'Clear views' is not allowed: Excel is in Edit mode", System.Windows.Forms.MessageBoxIcon.Warning);
}
else
{
using (new FreezeExcel(this.ExcelApplication.KeepStatusVisible))
{
foreach (IExcelTemplateView view in views)
{
ExcelTemplateView excelView = view as ExcelTemplateView;
if (excelView?.ViewSheet != null)
{
try
{
excelView.ViewSheet.Unprotect(System.Type.Missing);
excelView.Clear();
}
finally
{
excelView.ProtectSheet();
}
}
}
}
}
}
}
catch (Exception ex)
{
string message = "'Clear views' failed.";
Logger.Instance.LogException(LogType.Error, ex, message);
throw new EtkException(message, ex);
//ExcelApplication.DisplayException(null, message, ex);
}
}
private static void ExecuteSort(ExcelTemplateView view, ISorterDefinition sortDefinition)
{
view.SorterDefinition = sortDefinition;
object currentDataSource = view.GetDataSource();
ETKExcel.TemplateManager.ClearView(view);
// We reinject the datasource to force the filtering
view.CreateBindingContext(currentDataSource);
//view.SetDataSource(currentDataSource);
// RenderView the view to see the filering application
ETKExcel.TemplateManager.Render(view);
}
/// <summary> Implements <see cref="IExcelTemplateManager.RenderDataOnly"/> </summary>
public void RenderDataOnly(IEnumerable <IExcelTemplateView> views)
{
if (views == null)
{
return;
}
try
{
lock (syncRoot)
{
if (ExcelApplication.IsInEditMode())
{
ExcelApplication.DisplayMessageBox(null, "'Render data only' is not allowed: Excel is in Edit mode", System.Windows.Forms.MessageBoxIcon.Warning);
}
else
{
ExcelInterop.Range selectedRange = ExcelApplication.Application.Selection as ExcelInterop.Range;
using (new FreezeExcel(ExcelApplication.KeepStatusVisible))
{
foreach (IExcelTemplateView view in views)
{
ExcelTemplateView excelTemplateView = view as ExcelTemplateView;
if (excelTemplateView != null)
{
try
{
excelTemplateView.ViewSheet.Unprotect(System.Type.Missing);
excelTemplateView.RenderViewDataOnly();
}
finally
{
excelTemplateView.ProtectSheet();
}
}
}
}
selectedRange?.Select();
}
}
}
catch (Exception ex)
{
string message = "'RenderDataOnly' failed.";
Logger.Instance.LogException(LogType.Error, ex, message);
throw new EtkException(message, ex);
//ExcelApplication.DisplayException(null, message, ex);
}
}
/// <summary> Implements <see cref="IExcelTemplateManager.AddView"/> </summary>
public IExcelTemplateView AddView(ExcelInterop.Worksheet sheetContainer, string templateName, ExcelInterop.Worksheet sheetDestination, ExcelInterop.Range destinationRange, ExcelInterop.Range clearingCell)
{
try
{
lock (syncRoot)
{
ExcelTemplateView view = CreateView(sheetContainer, templateName, sheetDestination, destinationRange, clearingCell);
RegisterView(view);
return(view);
}
}
catch (Exception ex)
{
string message = $"Sheet '{(sheetDestination != null ? sheetDestination.Name.EmptyIfNull() : string.Empty)}', cannot add the View from template '{(sheetContainer != null ? sheetContainer.Name.EmptyIfNull() : string.Empty)}.{templateName.EmptyIfNull()}'"; Logger.Instance.LogException(LogType.Error, ex, message);
throw new EtkException(message, ex);
}
}
/// <summary>
/// Manage the contextual menus
/// </summary>
public IContextualMenu GetMenus(ExcelTemplateView view, Microsoft.Office.Interop.Excel.Range range, IBindingContextItem contextItem)
{
IBindingDefinition bindingDefinition = contextItem.BindingDefinition;
if (bindingDefinition == null || !bindingDefinition.IsBoundWithData || bindingDefinition.BindingType == null)
{
return(null);
}
if (!((TemplateDefinition)contextItem.ParentElement.ParentPart.ParentContext.TemplateDefinition).CanSort)
{
return(null);
}
foreach (IContextualPart menuPart in sortSearchAndFilersMenu.Items)
{
ContextualMenuItem menuItem = menuPart as ContextualMenuItem;
menuItem.SetAction(() => menuItem.MethodInfo.Invoke(null, new object[] { view, contextItem }));
}
return(sortSearchAndFilersMenu);
}
private void RegisterView(ExcelTemplateView view)
{
if (view == null)
{
return;
}
try
{
if (!viewsBySheet.ContainsKey(view.ViewSheet))
{
viewsBySheet[view.ViewSheet] = new List <ExcelTemplateView>();
view.ViewSheet.Change += OnSheetChange;
view.ViewSheet.SelectionChange += OnSelectionChange;
view.ViewSheet.BeforeDoubleClick += OnBeforeBoubleClick;
ExcelInterop.Workbook book = view.ViewSheet.Parent as ExcelInterop.Workbook;
if (book != null)
{
book.SheetCalculate -= OnSheetCalculate;
book.SheetCalculate += OnSheetCalculate;
book.SheetActivate -= OnSheetActivation;
book.SheetActivate += OnSheetActivation;
book.SheetDeactivate -= OnSheetDeactivation;
book.SheetDeactivate += OnSheetDeactivation;
ExcelApplication.ReleaseComObject(book);
book = null;
}
}
viewsBySheet[view.ViewSheet].Add(view);
}
catch (Exception ex)
{
throw new EtkException("View registration failed", ex);
}
}
public ExcelRootRenderer(ExcelTemplateView view) : base(null, view.TemplateDefinition, view.BindingContext, view.FirstOutputCell, null)
{
View = view;
RowDecorators = new List <ExcelElementDecorator>();
//@@ sortAndFilterButton = new ExcelSortAndFilterButton(View);
}
override protected void RealExecute(IExcelTemplateView view)
{
ExcelTemplateView excelView = view as ExcelTemplateView;
if (excelView.RenderedArea == null || excelView.Renderer == null)
{
StepsErrorMessages.Add("Rendered area must not be null");
return;
}
// Header
if (excelView.Renderer.HeaderPartRenderer == null || excelView.Renderer.HeaderPartRenderer.RenderedArea == null)
{
StepsErrorMessages.Add("Header rendered area must not be null");
}
else
{
if (excelView.Renderer.HeaderPartRenderer.Width != 4 || excelView.Renderer.HeaderPartRenderer.Height != 2)
{
StepsErrorMessages.Add("Header rendered area must 2*1");
}
if (excelView.Renderer.HeaderPartRenderer.RenderedRange[1, 1].Value != "Shops")
{
StepsErrorMessages.Add("Header Cell[1,1] must contains 'ID'");
}
if (excelView.Renderer.HeaderPartRenderer.RenderedRange[2, 1].Value != "ID")
{
StepsErrorMessages.Add("Header Cell[2,1] must contains 'ID'");
}
}
// Body
if (excelView.Renderer.BodyPartRenderer == null || excelView.Renderer.BodyPartRenderer.RenderedArea == null)
{
StepsErrorMessages.Add("Body rendered area must not be null");
}
else
{
if (excelView.Renderer.BodyPartRenderer.Width != 4 || excelView.Renderer.BodyPartRenderer.Height != 4)
{
StepsErrorMessages.Add("Body Rendered area must be 4*4");
}
if (excelView.Renderer.BodyPartRenderer.RenderedRange[1, 1].Value != 1)
{
StepsErrorMessages.Add("Body First cell must contains '1'");
}
if (excelView.Renderer.BodyPartRenderer.RenderedRange[4, 4].Value != "Fourth Shop Reception Phone number")
{
StepsErrorMessages.Add("Body Last cell must contains 'Fourth Shop Reception Phone number'");
}
}
// Footer
if (excelView.Renderer.FooterPartRenderer == null || excelView.Renderer.FooterPartRenderer.RenderedArea == null)
{
StepsErrorMessages.Add("Footer rendered area must not be null");
}
else
{
if (excelView.Renderer.FooterPartRenderer.Width != 4 || excelView.Renderer.HeaderPartRenderer.Height != 2)
{
StepsErrorMessages.Add("Footer rendered area must 4*2");
}
if (excelView.Renderer.FooterPartRenderer.RenderedRange[1, 1].Value != "Footer")
{
StepsErrorMessages.Add("Footer Cell[1,1] must contains 'Footer'");
}
if (excelView.Renderer.FooterPartRenderer.RenderedRange[2, 1].Value != "Shops")
{
StepsErrorMessages.Add("Footer Cell[2,1] must contains 'Shops'");
}
}
}
public ExcelSortAndFilterButton(ExcelTemplateView templateView)
{
this.View = templateView;
ExcelInterop.Worksheet worksheet = null;
ExcelInterop.Shapes shapes = null;
ExcelInterop.Shape shape = null;
try
{
worksheet = View.ViewSheet;
OwnerRange = View.FirstOutputCell;
Name = $"ExcelBtn{Interlocked.Increment(ref cpt)}";
shapes = worksheet.Shapes;
shape = (ExcelInterop.Shape)shapes.AddOLEObject("Forms.CommandButton.1",
Type.Missing,
false,
false,
Type.Missing,
Type.Missing,
Type.Missing,
OwnerRange.Left,
OwnerRange.Top,
20,
20);
shape.Name = Name;
object s = worksheet.GetType().InvokeMember(Name, BindingFlags.GetProperty, null, worksheet, null);
commandButton = s as ExcelForms.CommandButton;
commandButton.FontName = "Arial";
commandButton.Font.Size = 8;
commandButton.Caption = "S/F";
commandButton.ForeColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
commandButton.Click += () =>
{
using (ExcelMainWindow excelWindow = new ExcelMainWindow(ETKExcel.ExcelApplication.Application.Hwnd))
{
//@@SortAndFilterManagement.DisplaySortAndFilterWindow(excelWindow, View);
}
};
}
finally
{
if (shape != null)
{
ExcelApplication.ReleaseComObject(shape);
}
if (shapes != null)
{
ExcelApplication.ReleaseComObject(shapes);
}
if (worksheet != null)
{
ExcelApplication.ReleaseComObject(worksheet);
}
shape = null;
shapes = null;
worksheet = null;
}
}
public ExcelRequestDefinition(string name, string description, ExcelTemplateView view)
{
Name = name;
Description = description;
View = view;
}
/// <summary> Implements <see cref="IExcelTemplateManager.Render"/> </summary>
public void Render(IEnumerable <IExcelTemplateView> views)
{
if (views == null)
{
return;
}
try
{
lock (syncRoot)
{
if (ExcelApplication.IsInEditMode())
{
ExcelApplication.DisplayMessageBox(null, "'Render' is not allowed: Excel is in Edit mode", System.Windows.Forms.MessageBoxIcon.Warning);
}
else
{
ExcelInterop.Range selectedRange = ExcelApplication.Application.Selection as ExcelInterop.Range;
using (new FreezeExcel(ExcelApplication.KeepStatusVisible))
{
foreach (IExcelTemplateView view in views)
{
ExcelTemplateView excelTemplateView = view as ExcelTemplateView;
if (excelTemplateView != null)
{
try
{
excelTemplateView.ViewSheet.Unprotect(Type.Missing);
excelTemplateView.RenderView();
if (!string.IsNullOrEmpty(view.SearchValue))
{
excelTemplateView.ExecuteSearch();
}
else
{
excelTemplateView.ManageExpander();
}
}
finally
{
excelTemplateView.ProtectSheet();
}
}
}
}
if (selectedRange != null)
{
selectedRange.Select();
//ExcelApplication.ReleaseComObject(selectedRange);
selectedRange = null;
}
}
}
}
catch (Exception ex)
{
string message = "'Render' failed.";
Logger.Instance.LogException(LogType.Error, ex, message);
throw new EtkException(message, ex);
//ExcelApplication.DisplayException(null, message, ex);
}
}
/// <summary> Implements <see cref="IExcelTemplateManager.RemoveViews"/> </summary>
public void RemoveViews(IEnumerable <IExcelTemplateView> views)
{
if (views == null)
{
return;
}
try
{
lock (syncRoot)
{
if (ExcelApplication.IsInEditMode())
{
ExcelApplication.DisplayMessageBox(null, "'Clear views' is not allowed: Excel is in Edit mode", System.Windows.Forms.MessageBoxIcon.Warning);
}
else
{
bool success = true;
foreach (IExcelTemplateView view in views)
{
ExcelTemplateView excelView = view as ExcelTemplateView;
if (view != null)
{
try
{
ClearView(excelView);
KeyValuePair <ExcelInterop.Worksheet, List <ExcelTemplateView> > kvp = viewsBySheet.FirstOrDefault(s => s.Value.FirstOrDefault(v => v.Equals(view)) != null);
if (kvp.Key != null && kvp.Value != null && kvp.Value.Count > 0)
{
viewsBySheet[kvp.Key].Remove(excelView);
}
if (log.GetLogLevel() == LogType.Debug)
{
log.LogFormat(LogType.Debug, "View '{0}' from '{1}' removed.", excelView.Ident, excelView.TemplateDefinition.Name);
}
bindingTemplateManager.RemoveView(excelView);
}
catch (Exception ex)
{
string message = "Remove View failed.";
Logger.Instance.LogException(LogType.Error, ex, message);
success = false;
}
}
}
if (!success)
{
throw new EtkException("No all views have been removed. Please check the logs.");
}
}
}
}
catch (Exception ex)
{
string message = "'Remove views' failed.";
Logger.Instance.LogException(LogType.Error, ex, message);
throw new EtkException(message, ex);
//ExcelApplication.DisplayException(null, message, ex);
}
}
private void BtnStartExcelWork_Click(object sender, RoutedEventArgs e)
{
// do stm
if (MessageBox.Show("Excel 작업을 시작하시겠습니까?", "Question", MessageBoxButton.YesNo, MessageBoxImage.Warning) == MessageBoxResult.No)
{
}
else
{
ExcelTemplateView.SelectAll();
string templatePath = (string)ExcelTemplateView.SelectedItem;
if (templatePath == null)
{
MessageBox.Show("탬플릿 파일이 선택되지 않았습니다.", "Alert", MessageBoxButton.OK, MessageBoxImage.Warning);
return;
}
if (ExcelWorkQueue.Count == 0)
{
MessageBox.Show("회사 파일이 선택되지 않았습니다.", "Alert", MessageBoxButton.OK, MessageBoxImage.Warning);
return;
}
foreach (ExcelWorkQueueDataStruct dataStruct in ExcelWorkQueue)
{
if (dataStruct.jObjectList == null)
{
MessageBox.Show("회사가 선택되지 않았거나,\n중복된 파일을 선택했습니다.", "Alert", MessageBoxButton.OK, MessageBoxImage.Warning);
return;
}
}
int c = 1;
List <string> workFailList = new List <string>();
foreach (ExcelWorkQueueDataStruct dataStruct in ExcelWorkQueue)
{
string extension = System.IO.Path.GetExtension(templatePath);
string savePath = System.IO.Path.GetFullPath(dataStruct.PathInfo);
string filename = System.IO.Path.GetFileNameWithoutExtension(dataStruct.PathInfo);
StringBuilder temp = new StringBuilder(savePath.Remove(savePath.LastIndexOf("\\"), savePath.Length - savePath.LastIndexOf("\\")));
temp.Append("\\작업");
DirectoryInfo di = new DirectoryInfo(temp.ToString());
if (di.Exists == false)
{
di.Create();
}
temp.Append("\\" + filename + "_수정" + extension);
savePath = temp.ToString();
Console.WriteLine(savePath);
WriteDebugLine("작업중입니다.. (" + c + "/" + ExcelWorkQueue.Count + ")");
UpdateWindow();
string ErrorCode = "";
ErrorCode = excelActivity.Work(dataStruct.PathInfo, templatePath, savePath, dataStruct.jObjectList);
if (ErrorCode != null)
{
//MessageBox.Show(ErrorCode, "Alert", MessageBoxButton.OK, MessageBoxImage.Warning);
WriteDebugLine(ErrorCode);
WriteDebugLine("작업이 비정상적으로 종료되었습니다.\n");
c++;
workFailList.Add(filename);
ExcelWorkFailView.Items.Add(savePath);
UpdateWindow();
continue;
}
else
{
ExcelWorkEndView.Items.Add(savePath);
WriteDebugLine("작업이 끝났습니다. (" + c + "/" + ExcelWorkQueue.Count + ")\n");
c++;
}
}
ClearAllCurrentQueueData(0);
string temps = "";
if (workFailList.Count == 0)
{
temps = "없음";
}
foreach (string s in workFailList)
{
temps += s;
temps += '\n';
}
MessageBox.Show("작업이 끝났습니다.\n" +
"작업 실패 리스트 : " +
temps, "Alert", MessageBoxButton.OK, MessageBoxImage.Warning);
WriteDebugLine("================================\n");
}
}
