public void Dispose()
{
if (commandButton != null)
{
IsDisposed = true;
if (CurrentCommand != null)
{
commandButton.Click -= CurrentCommand;
}
ExcelInterop.Worksheet worksheet = OwnerRange.Worksheet;
ExcelInterop.OLEObject obj = worksheet.OLEObjects(Name);
obj.Delete();
ExcelApplication.ReleaseComObject(obj);
ExcelApplication.ReleaseComObject(commandButton);
ExcelApplication.ReleaseComObject(OwnerRange);
ExcelApplication.ReleaseComObject(worksheet);
obj = null;
commandButton = null;
OwnerRange = null;
worksheet = null;
}
}
public void OnSheetCalculate()
{
if ((Range.HasFormula && Range.Formula != currentFormula) || !object.Equals(Range.Value2, currentValue))
{
Microsoft.Office.Interop.Excel.WorksheetFunction worksheetFunction = ETKExcel.ExcelApplication.Application.WorksheetFunction;
if (Range.HasFormula && worksheetFunction.IsError(Range))
{
if (excelBindingDefinitionWithFormula.TargetBindingDefinition != null)
{
Type type = excelBindingDefinitionWithFormula.TargetBindingDefinition.BindingType;
if (type != null)
{
object nullValue = type.IsValueType ? Activator.CreateInstance(type) : null;
excelBindingDefinitionWithFormula.TargetBindingDefinition.UpdateDataSource(DataSource, nullValue);
}
}
}
else
{
if (excelBindingDefinitionWithFormula.TargetBindingDefinition != null)
{
excelBindingDefinitionWithFormula.TargetBindingDefinition.UpdateDataSource(DataSource, Range.Value2);
}
}
currentValue = Range.Value2;
currentFormula = Range.HasFormula ? Range.Formula : null;
if (worksheetFunction != null)
{
ExcelApplication.ReleaseComObject(worksheetFunction);
worksheetFunction = null;
}
}
}
public void CreateSimpleFile()
{
using (Application app = ExcelApplication.Create()) {
String s = app.Version;
app.Visible = true; // make excel visible
using (Workbook wb = app.Workbooks.Add(Type.Missing)) {
wb.Title = "new workbook";
using (Worksheets worksheets = wb.Sheets) {
int count_sheets = worksheets.Count; //try to get count of sheets
using (Worksheet ws = wb.Sheets[1]) {
Range usedrange = ws.UsedRange; //try to get used range
//try to assign some values to some cells
using (Range cells = ws.Cells) {
for (int i = 1; i < 10; ++i)
{
using (Range r = cells[i, i]) {
r.Value = i * i;
}
}
}
}
}
// wb.Saved = true;
// wb.Close();
}
// app.Quit();
}
}
public static IExcelApplication Open(string filePath)
{
var excel = new ExcelApplication();
_ = excel.Open(filePath);
return(excel);
}
private string GetBlockOfExcelData(string fileName, string sheetName, int rowStart, int rowEnd)
{
const int MAX_COLUMN_NUMBER = 256;
ExcelFactory factory = ExcelFactory.CreateInstance(ExcelApplicationType.TMS, ExcelGarbageCollectionStyle.DesktopApplication);
ExcelApplication app = factory.AddApplication();
ExcelWorkbook bookInitial = app.AddWorkbook("initial", fileName);
ExcelWorksheet sheetInitial = bookInitial[sheetName];
ExcelWorkbook bookResult = app.AddWorkbook("final");
_context.Log.AddLogInformation("XLS-файл успешно загружен в память. Создан пустая результирующая книга.");
bookResult.CopyRange(bookInitial, sheetInitial.Number, rowStart, 1, rowEnd, MAX_COLUMN_NUMBER, 2, 1, 1);
_context.Log.AddLogInformation("Диапазон скопирован в новую книгу");
ExcelWorksheet sheetResult = bookResult[1];
for (int i = 0; i < MAX_COLUMN_NUMBER; i++)
{
sheetResult.WriteValue(1, i + 1, 1);
}
_context.Log.AddLogInformation("Заполнили единицами первую строку результирующего листа");
string fileNameResult = Path.Combine(_context.ExecutedPackage.PackagePath, Guid.NewGuid().ToString("N") + ".xls");
_context.Log.AddLogInformation("Готовы сохранить результирующий файл в '" + fileNameResult + "'");
bookResult.Save(fileNameResult);
_context.Log.AddLogInformation("Результирующий файл сохранен.");
return(fileNameResult);
}
/// <summary> MAnage the double click on a cell</summary>
private void OnBeforeBoubleClick(ExcelInterop.Range target, ref bool cancel)
{
ExcelInterop.Range realTarget = target.Cells[1, 1];
ExcelInterop.Worksheet worksheet = target.Worksheet;
try
{
List <ExcelTemplateView> views;
if (viewsBySheet.TryGetValue(worksheet, out views))
{
if (views != null)
{
foreach (ExcelTemplateView view in views)
{
if (!view.IsDisposed && view.IsRendered)
{
if (view.OnBeforeBoubleClick(realTarget, ref cancel))
{
break;
}
}
}
}
}
}
finally
{
ExcelApplication.ReleaseComObject(worksheet);
worksheet = null;
//ExcelApplication.ReleaseComObject(realTarget);
realTarget = null;
}
}
public static IExcelApplication CreateFrom(string templatePath)
{
var excel = new ExcelApplication();
_ = excel.CreateFrom(templatePath);
return(excel);
}
internal ExcelTemplateDefinition GetTemplateDefinitionFromLink(ExcelTemplateDefinitionPart parent, TemplateLink templateLink)
{
try
{
string[] tos = templateLink.To.Split('.');
ExcelInterop.Worksheet sheetContainer = null;
string templateName;
if (tos.Count() == 1)
{
sheetContainer = parent.DefinitionCells.Worksheet;
templateName = tos[0].EmptyIfNull().Trim();
}
else
{
string worksheetContainerName = tos[0].EmptyIfNull().Trim();
templateName = tos[1].EmptyIfNull().Trim();
ExcelInterop.Worksheet parentWorkSheet = parent.DefinitionCells.Worksheet;
ExcelInterop.Workbook workbook = parentWorkSheet.Parent as ExcelInterop.Workbook;
if (workbook == null)
{
throw new EtkException("Cannot retrieve the workbook that owned the template destination sheet");
}
List <ExcelInterop.Worksheet> sheets = new List <ExcelInterop.Worksheet>(workbook.Worksheets.Cast <ExcelInterop.Worksheet>());
sheetContainer = sheets.FirstOrDefault(s => !string.IsNullOrEmpty(s.Name) && s.Name.Equals(worksheetContainerName));
if (sheetContainer == null)
{
throw new EtkException($"Cannot find the sheet '{worksheetContainerName}' in the current workbook", false);
}
ExcelApplication.ReleaseComObject(workbook);
workbook = null;
}
string templateDescriptionKey = $"{sheetContainer.Name}-{templateName}";
ExcelTemplateDefinition templateDefinition = bindingTemplateManager.GetTemplateDefinition(templateDescriptionKey) as ExcelTemplateDefinition;
if (templateDefinition == null)
{
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()}'");
}
templateDefinition = ExcelTemplateDefinitionFactory.CreateInstance(templateName, range);
bindingTemplateManager.RegisterTemplateDefinition(templateDefinition);
range = null;
}
ExcelApplication.ReleaseComObject(sheetContainer);
sheetContainer = null;
return(templateDefinition);
}
catch (Exception ex)
{
string message = $"Cannot create the template dataAccessor. {ex.Message}";
throw new EtkException(message, false);
}
}
public void OnSheetCalculate()
{
if (!Range.HasFormula)
{
return;
}
object resolvedBinding = excelBindingDefinitionFormulaResult.NestedBindingDefinition.ResolveBinding(DataSource);
if (Range.FormulaLocal != resolvedBinding || !object.Equals(Range.Value2, currentValue))
{
ExcelInterop.WorksheetFunction worksheetFunction = ETKExcel.ExcelApplication.Application.WorksheetFunction;
if (worksheetFunction.IsError(Range))
{
Type type = excelBindingDefinitionFormulaResult.NestedBindingDefinition.BindingType;
if (type != null)
{
object nullValue = type.IsValueType ? Activator.CreateInstance(type) : null;
excelBindingDefinitionFormulaResult.NestedBindingDefinition.UpdateDataSource(DataSource, nullValue);
}
}
else
{
excelBindingDefinitionFormulaResult.NestedBindingDefinition.UpdateDataSource(DataSource, Range.Value2);
}
currentValue = Range.Value2;
if (worksheetFunction != null)
{
ExcelApplication.ReleaseComObject(worksheetFunction);
worksheetFunction = null;
}
}
}
public void Render()
{
ExcelInterop.Worksheet worksheetTo = currentRenderingTo.Worksheet;
if (bindingContextPart != null)
// && ((bindingContextPart is LinkedTemplateDefinition && ((LinkedTemplateDefinition) bindingContextPart).MinOccurencesMethod != null || bindingContextPart.ElementsToRender.ElementsToRender != null && bindingContextPart.ElementsToRender.ElementsToRender.Any())
{
if (partToRenderDefinition.HasLinkedTemplates || partToRenderDefinition.ContainMultiLinesCells)
{
ManageTemplateWithLinkedTemplates();
}
else
{
ManageTemplateWithoutLinkedTemplates();
}
}
if (Width > 0 && Height > 0)
{
//RenderedArea = new RenderedArea(firstRangeTo.Column, firstRangeTo.Row, Width, Height);
RenderedArea = new RenderedArea(firstRangeTo.Column, firstRangeTo.Row, Width, Height);
RenderedRange = firstRangeTo.Resize[Height, Width];
}
ExcelApplication.ReleaseComObject(worksheetTo);
worksheetTo = null;
elementFirstRangeTo = null;
currentRenderingFrom = null;
currentRenderingTo = null;
}
public string Save(string filename, bool savePopupFlag = true, string password = "")
{
string fileExt = Path.GetExtension(filename);
if (savePopupFlag)
{
ExcelApplication.DisplayAlerts = true;
xSaveName = ExcelApplication.GetSaveAsFilename(Path.GetFileNameWithoutExtension(filename), "Excel Workbook (*" + fileExt + "), *" + fileExt);
ExcelApplication.DisplayAlerts = false;
}
else
{
if (!File.Exists(filename))
{
xSaveName = filename;
}
else
{
xSaveName = Path.Combine(Path.GetDirectoryName(filename), Path.GetFileNameWithoutExtension(filename) + Path.GetExtension(filename));
}
}
XlFileFormat fileFormat = fileExt switch
{
".xlsx" => XlFileFormat.xlOpenXMLWorkbook,
".xlsm" => XlFileFormat.xlOpenXMLWorkbookMacroEnabled,
".xls" => XlFileFormat.xlExcel8,
".csv" => XlFileFormat.xlCSV,
".txt" => XlFileFormat.xlTextWindows,
_ => XlFileFormat.xlWorkbookDefault,
};
ExcelApplication.DisplayAlerts = false;
Workbook.SaveAs(Filename: xSaveName, FileFormat: fileFormat, CreateBackup: false, Password: password, ConflictResolution: XlSaveConflictResolution.xlLocalSessionChanges);
return(xSaveName);
}
public override void Dispose()
{
lock (syncRoot)
{
if (!IsDisposed)
{
//Expander = null;
if (AccessorParametersManager != null)
{
AccessorParametersManager.Dispose();
AccessorParametersManager = null;
}
if (Renderer != null)
{
Renderer.Dispose();
Renderer = null;
}
CellsThatContainSearchValue.Clear();
if (ViewSheet != null)
{
ExcelApplication.ReleaseComObject(ViewSheet);
ViewSheet = null;
}
FirstOutputCell = null;
currentSelectedRange = null;
ClearingCell = null;
base.Dispose();
}
}
}
private void HighlightSelection(ExcelInterop.Range selectedCell)
{
ExcelInterop.Range viewSelectedRange = null;
ExcelInterop.Worksheet sheet = (ExcelInterop.Worksheet)RenderedRange.Parent;
if (TemplateDefinition.Orientation == Orientation.Vertical)
{
viewSelectedRange = sheet.Cells[selectedCell.Row, RenderedRange.Column];
viewSelectedRange = viewSelectedRange.Resize[1, RenderedRange.Columns.Count];
currentSelectedRange = viewSelectedRange;
}
else
{
viewSelectedRange = sheet.Cells[RenderedRange.Row, selectedCell.Column];
viewSelectedRange = viewSelectedRange.Resize[RenderedRange.Rows.Count, 1];
currentSelectedRange = viewSelectedRange;
}
for (int i = 1; i <= currentSelectedRange.Cells.Count; i++)
{
ExcelInterop.Range cell = currentSelectedRange.Cells[1, i];
if (CurrentSelectedCell.Column != cell.Column || CurrentSelectedCell.Row != cell.Row)
{
ExcelInterop.Interior interior = cell.Interior;
try
{
if (interior.Gradient != null)
{
currentSelectedRangePattern.Add(null);
}
else
{
currentSelectedRangePattern.Add(new SelectionPattern(ref interior));
interior.Pattern = ExcelInterop.XlPattern.xlPatternGray8;
interior.PatternColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DimGray);
}
}
catch
{ }
ExcelApplication.ReleaseComObject(interior);
interior = null;
}
else
{
currentSelectedRangePattern.Add(null);
}
}
// Redraw the borders of the current selection
if (((TemplateDefinition)TemplateDefinition).AddBorder)
{
Renderer.BorderAround(currentSelectedRange, ExcelInterop.XlLineStyle.xlContinuous, ExcelInterop.XlBorderWeight.xlThin, 1);
}
viewSelectedRange = null;
ExcelApplication.ReleaseComObject(sheet);
sheet = null;
}
public ExcelTemplateManager([Import] ExcelApplication excelApplication,
[Import] ContextualMenuManager contextualMenuManager,
[Import] ExcelDecoratorsManager excelDecoratorsManager,
[Import] EventExcelCallbacksManager eventCallbacksManager,
[Import] BindingTemplateManager bindingTemplateManager)
{
if (excelApplication == null)
{
throw new EtkException("'ExcelBindingTemplateManager' initialization: the 'application' parameter is mandatory");
}
ExcelApplication = excelApplication;
CallbacksManager = eventCallbacksManager;
this.excelDecoratorsManager = excelDecoratorsManager;
this.contextualMenuManager = contextualMenuManager;
this.bindingTemplateManager = bindingTemplateManager;
// Create the notify property manager .
ExcelNotifyPropertyManager = new ExcelNotifyPropertyManager(ExcelApplication);
// Create the template contextual menus manager.
//@@templateContextualMenuManager = new TemplateContextualMenuManager(contextualMenuManager);
// Declare the contextual menus activators for the template views.
contextualMenuManager.OnContextualMenusRequested += ManageViewsContextualMenu;
sortSearchAndFilterMenuManager = new SortSearchAndFilterMenuManager();
eventCallbacksManager.RegisterSpecificCallBack();
}
public void Dispose()
{
if (ExcelApplication != null)
{
ExcelApplication.Dispose();
}
}
/// <summary> Implements <see cref="IExcelTemplateManager.GetSheetViews"/> </summary>
public IEnumerable <IExcelTemplateView> GetActiveSheetViews()
{
IEnumerable <IExcelTemplateView> iViews = new List <IExcelTemplateView>();
ExcelInterop.Worksheet activeSheet = null;
try
{
activeSheet = ExcelApplication.GetActiveSheet();
if (activeSheet != null)
{
iViews = GetSheetViews(activeSheet);
}
}
catch (Exception ex)
{
string message = "'GetActiveSheetViews' failed";
Logger.Instance.LogException(LogType.Error, ex, message);
throw new EtkException(message, ex);
//ExcelApplication.DisplayException(null, message, ex);
}
finally
{
if (activeSheet != null)
{
ExcelApplication.ReleaseComObject(activeSheet);
activeSheet = null;
}
}
return(iViews);
}
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;
}
}
}
protected void GenerateXLSFromDataTable(string FileName, string[] ColumnNames, DataTable DataForXLS, DataTable HeaderData)
{
ExcelApplication xla = new ExcelApplication();
Workbook wb = xla.Create();
Worksheet FirstSheet = wb.Worksheets[0];
FirstSheet.Cells[0, 0].Value = "Exported By:";
FirstSheet.Cells[1, 0].Value = "Group:";
FirstSheet.Cells[2, 0].Value = "Exported Date:";
if (HeaderData.Rows.Count > 0)
{
FirstSheet.Cells[0, 1].Value = HeaderData.Rows[0]["FirstName"].ToString() + " " + HeaderData.Rows[0]["LastName"].ToString();
FirstSheet.Cells[1, 1].Value = HeaderData.Rows[0]["InstitutionName"].ToString();
FirstSheet.Cells[2, 1].Value = DateTime.Now.ToString();
}
for (int c = 0; c < ColumnNames.Length; c++)
FirstSheet.Cells[4, c].Value = ColumnNames[c];
Cell ImportCell = FirstSheet.Cells[5, 0];
Area MyData = FirstSheet.ImportData(DataForXLS, ImportCell);
xla.Save(wb, Page.Response, FileName + ".xls", true);
}
public ExcelCheckBox(ExcelInterop.Range range, ExcelCheckBoxDefinition definition)
{
OwnerRange = range;
OwnerRange.Value2 = null;
ExcelInterop.Worksheet worksheet = null;
ExcelInterop.OLEObjects oleObjects = null;
ExcelInterop.OLEObject oleObject = null;
try
{
worksheet = OwnerRange.Worksheet;
Name = $"ExcelCB{Interlocked.Increment(ref cpt)}";
oleObjects = worksheet.OLEObjects();
oleObject = oleObjects.Add("Forms.CheckBox.1",
Type.Missing,
true,
false,
Type.Missing,
Type.Missing,
Type.Missing,
OwnerRange.Left + 3,
OwnerRange.Top + 1,
12,
12);
oleObject.Name = Name;
oleObject.Placement = ExcelInterop.XlPlacement.xlMove;
CheckBox = worksheet.GetType().InvokeMember(Name, BindingFlags.Default | BindingFlags.GetProperty, null, worksheet, null) as ExcelForms.CheckBox;
CheckBox.SpecialEffect = ExcelForms.fmButtonEffect.fmButtonEffectSunken;
CheckBox.TripleState = false;
CheckBox.Caption = string.Empty;
CheckBox.BackColor = (int)OwnerRange.Interior.Color;
CheckBox.BackStyle = ExcelForms.fmBackStyle.fmBackStyleTransparent;
oleObject.Interior.ColorIndex = -4142;
CheckBox.AutoSize = false;
oleObject = null;
}
finally
{
if (oleObject != null)
{
ExcelApplication.ReleaseComObject(oleObject);
}
if (oleObjects != null)
{
ExcelApplication.ReleaseComObject(oleObjects);
}
if (worksheet != null)
{
ExcelApplication.ReleaseComObject(worksheet);
}
oleObject = null;
oleObjects = null;
worksheet = null;
}
}
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
#region VSTO generated code
menu = new Menu(this.Application);
officeApplication = new ExcelApplication(this.Application);
ExcelApplication.MenuListener = menu;
#endregion
}
public static IExcelApplication BlankWorkbook()
{
var excel = new ExcelApplication();
var book = excel.BlankWorkbook();
_ = book.NewSheet();
return(excel);
}
public void GetWorkBooksTest()
{
var excel = StartExcel();
var test = new ExcelApplication(excel);
var workbooks = test.GetWorkbooks();
Assert.AreNotEqual(null, workbooks);
excel.Quit();
}
public void GetWorkbookByNameTest()
{
var excel = StartExcel();
var test = new ExcelApplication(excel);
var workbook = test.GetLoadedWorkbook("Demo.xls");
excel.Quit();
Assert.AreEqual(null, workbook);
}
private void GetObjects_Click(object sender, RibbonControlEventArgs e)
{
var myExcelApp = new ExcelApplication(Globals.ThisAddIn.Application);
var workbook = myExcelApp.FindWorkbook("Book1");
var retrievedList = workbook.FindOrCreateWorksheet("demo").GetListObjectByName("eerste");
var helper = GetHelperForBaseModel();
var retrieveddata = helper.TransferFromExcelFormat(retrievedList).GetObjectsFromExcel();
System.Windows.Forms.MessageBox.Show(string.Format("max value : {0}", GetMaxValueDynamicInts(retrieveddata)));
}
public ExcelButton(ExcelInterop.Range range, ExcelButtonDefinition definition)
{
OwnerRange = range;
OwnerRange.Value2 = null;
ExcelInterop.Worksheet worksheet = null;
ExcelInterop.OLEObjects oleObjects = null;
ExcelInterop.OLEObject obj = null;
try
{
worksheet = OwnerRange.Worksheet;
Name = $"ExcelBtn{Interlocked.Increment(ref cpt)}";
oleObjects = worksheet.OLEObjects();
obj = oleObjects.Add("Forms.CommandButton.1",
Type.Missing,
false,
false,
Type.Missing,
Type.Missing,
Type.Missing,
OwnerRange.Left + definition.X,
OwnerRange.Top + definition.Y,
definition.W == 0 ? OwnerRange.Width : definition.W,
definition.H == 0 ? OwnerRange.Height : definition.H);
obj.Name = Name;
object s = worksheet.GetType().InvokeMember(Name, BindingFlags.Default | BindingFlags.GetProperty, null, worksheet, null);
commandButton = s as ExcelForms.CommandButton;
commandButton.FontName = "Arial";
commandButton.Font.Size = 8;
commandButton.Caption = definition.Label;
//if (excelTemplateDefinition.W == 0 && excelTemplateDefinition.H == 0)
// commandButton.AutoSize = true;
obj.Placement = ExcelInterop.XlPlacement.xlMove;
}
finally
{
if (obj != null)
{
ExcelApplication.ReleaseComObject(obj);
}
if (oleObjects != null)
{
ExcelApplication.ReleaseComObject(oleObjects);
}
if (worksheet != null)
{
ExcelApplication.ReleaseComObject(worksheet);
}
obj = null;
oleObjects = null;
worksheet = null;
}
}
/// <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);
}
}
public void CreateWorkbookTest()
{
var excel = StartExcel();
var test = new ExcelApplication(excel);
var NumberOfworkbooks = test.GetWorkbooks().Count;
var workbook = test.AddNewWorkbook();
Assert.AreNotEqual(null, workbook);
Assert.AreNotEqual(NumberOfworkbooks + 1, test.GetWorkbooks());
workbook.Close(false);
excel.Quit();
}
public static List <ExcelWorksheet> GetWorksheets(this ExcelApplication application)
{
var Worksheets = application.GetInteropVersion().Worksheets;
var result = new List <ExcelWorksheet>();
for (int i = 0; i < Worksheets.Count; i++)
{
result.Add(new ExcelWorksheet(Worksheets[i + 1])); //one based
}
return(result);
}
void OnParametersChanged(ExcelInterop.Range target)
{
bool parametersChanged = false;
foreach (ExcelInterop.Range range in rangesToListen)
{
if (ETKExcel.ExcelApplication.Application.Intersect(range, target) != null)
{
parametersChanged = true;
break;
}
}
if (parametersChanged)
{
List <object> parameters = new List <object>();
foreach (object param in Parameters)
{
if (param == null)
{
parameters.Add(null);
}
else if (param is ExcelInterop.Range)
{
parameters.Add((param as ExcelInterop.Range).Value);
}
else
{
parameters.Add(param);
}
}
ETKExcel.TemplateManager.ClearView(View);
ExcelApplication application = (ETKExcel.TemplateManager as ExcelTemplateManager).ExcelApplication;
application.PostAsynchronousAction(() => {
(View as ExcelTemplateView).FirstOutputCell.Value2 = "#Retrieving Data";
Task task = new Task(() =>
{
object result = View.TemplateDefinition.DataAccessor.Invoke(parameters);
View.SetDataSource(result);
ExcelApplication application2 = (ETKExcel.TemplateManager as ExcelTemplateManager).ExcelApplication;
application2.PostAsynchronousAction(() => {
(View as ExcelTemplateView).FirstOutputCell.Value2 = string.Empty;
ETKExcel.TemplateManager.Render(View as ExcelTemplateView);
});
});
task.Start();
});
}
}
public void Dispose()
{
if (_disposed)
{
return;
}
_epplusLogger.DetachLogger();
_package.Dispose();
ExcelApplication.Dispose();
_disposed = true;
}
public static ExcelWorkbook FindWorkbook(this ExcelApplication application, string name)
{
var workbooks = application.GetInteropVersion().Workbooks;
ExcelWorkbook result = null;
for (int i = 1; i <= workbooks.Count; i++)
{
if (workbooks[i].Name == name)
{
result = new ExcelWorkbook(workbooks[i]);
break;
}
}
return(result);
}
protected void createButtonAction(object sender, EventArgs e)
{
try
{
if (validate())
{
using (SPSite siteCollection = new SPSite(SPContext.Current.Web.Url))
{
using (SPWeb web = siteCollection.OpenWeb())
{
SPList list = web.GetList(List_AssetUrlSelector.AssetUrl);
SPFile template;
SPUserCustomAction action = list.UserCustomActions.Add();
action.Title = Title_TextBox.Text;
action.Description = Discription_TextBox.Text;
if (templateSelection_RadioButtonList.SelectedValue == "existingTemplate")
{
template = web.GetFile(Template_AssetUrlSelector.AssetUrl);
}
else
{
ExcelApplication xla = new ExcelApplication();
Workbook wb = xla.Open(Page.MapPath("template/OfficeWriterStarterTemplate.xls"));
wb.DocumentProperties.Title = Title_TextBox.Text + " Template";
wb.GetNamedRange("Title").Areas[0][0, 0].Value = Title_TextBox.Text + " Template";
int startRow = wb.GetNamedRange("dataMarkerStart").Areas[0].FirstRow;
int startCol = wb.GetNamedRange("dataMarkerStart").Areas[0].FirstColumn;
Worksheet ws = wb[wb.GetNamedRange("dataMarkerStart").Areas[0].WorksheetIndex];
int col = 0;
foreach (ListItemCollection collection in new ListItemCollection[2] { new_template_col_selection_default_CheckBoxList.Items, new_template_col_selection_hidden_CheckBoxList.Items })
{
foreach (ListItem item in collection)
{
if (item.Selected)
{
ws.Cells[startRow, startCol + col].Value = item.Text;
ws.Cells[startRow + 1, startCol + col].Value = "%%=[data].[" + item.Text + "]";
col++;
}
}
}
Stream mStream = new MemoryStream();
xla.Save(wb, mStream);
template = web.GetFolder(newTemplateLocation_AssetUrlSelector.AssetUrl).Files.Add(newTemplateFileName_TextBox.Text + ".xls", mStream);
}
action.Url = "~site/_layouts/SoftArtisans/List2Excel/Gen.aspx?List={ListId}&TemplateLocation=" + (template.Web.Url + "/" + template.Url);
action.ImageUrl = "~site/_layouts/images/SoftArtisans/icons/excelwriter.png";
action.Location = "CommandUI.Ribbon.ListView";
string extXml = " <CommandUIExtension xmlns='http://schemas.microsoft.com/sharepoint/'><CommandUIDefinitions><CommandUIDefinition Location='Ribbon.ListItem.Actions.Controls._children'>";
extXml += "<Button Id='" + action.Id.ToString() + "' ";
extXml += "Command='" + action.Id.ToString() + "action" + @"' ";
extXml += "Image32by32='~site/_layouts/images/SoftArtisans/icons/excelwriter.png' Image16by16='~site/_layouts/images/SoftArtisans/icons/xlsm.png' Sequence='0' ";
extXml += "LabelText='" + action.Title + "' ";
extXml += "Description='" + action.Description + "' ";
extXml += "TemplateAlias='o1' /></CommandUIDefinition></CommandUIDefinitions><CommandUIHandlers>";
extXml += "<CommandUIHandler Command='" + action.Id.ToString() + "action" + @"' ";
extXml += "CommandAction='" + action.Url + "' />";
extXml += "</CommandUIHandlers></CommandUIExtension>";
action.CommandUIExtension = extXml;
action.Update();
web.AllowUnsafeUpdates = false;
Message_Literal.Text = "<div class='ui-state-highlight'><h2>'" + action.Title + "' Button Sucessfully Created</h2>";
Message_Literal.Text += "<p>Your newly created button should now be available in the Ribbon on the ";
Message_Literal.Text += "<a href='" + list.DefaultViewUrl + "'>" + list.Title + "</a> list</p></div>";
Message_Literal.Visible = true;
resetForm();
}
}
}
}
catch (Exception ex)
{
Message_Literal.Text = "<div class='ui-state-error'><h2>Error</h2>";
Message_Literal.Text += "<p>" + ex.Message + "</p></div>";
Message_Literal.Visible = true;
List_AssetUrlSelector.Focus();
}
}
static void Main(string[] args)
{
ExcelApplication app = new ExcelApplication();
Workbook workbook = app.Create(ExcelApplication.FileFormat.Xlsx);
for (int i = 0; i < workbook.Worksheets.Count; i++)
workbook.Worksheets.Delete(0);
Worksheet sheet = workbook.Worksheets.CreateWorksheet("Data");
var shipTimes = new List<double>();
var orderPrices = new List<double>();
var orderCount = new List<double>();
using (var db = new DatabaseEntities())
{
var query = from b in db.Orders select b;
var i = 0;
sheet[i, 0].Value = "Order Date";
sheet[i, 1].Value = "Customer";
sheet[i, 2].Value = "Required Date";
sheet[i, 3].Value = "Shipped Date";
i++;
// Write the order data out to Excel
foreach (var item in query)
{
sheet[i, 0].Value = item.Order_Date;
sheet[i, 1].Value = item.Customer.Company_Name;
sheet[i, 2].Value = item.Required_Date;
sheet[i, 3].Value = item.Shipped_Date;
sheet[i, 0].Style.NumberFormat = NumberFormat.DateFormat.MonthDayYear;
sheet[i, 2].Style.NumberFormat = NumberFormat.DateFormat.MonthDayYear;
sheet[i, 3].Style.NumberFormat = NumberFormat.DateFormat.MonthDayYear;
// Calculate the time from order to delivery
if ((item.Shipped_Date != null) && (item.Order_Date != null))
shipTimes.Add((double)(item.Shipped_Date.Value.Subtract(item.Order_Date.Value)).TotalDays);
// Calculate the price of each order
decimal totalPrice = 0;
foreach (Order_Detail detail in item.Order_Details)
totalPrice += detail.Unit_Price;
orderPrices.Add((double)totalPrice);
orderCount.Add(item.Order_Details.Count);
i++;
}
}
sheet = workbook.Worksheets.CreateWorksheet("DeliveryTime");
WriteHistogramToSheet(sheet, shipTimes);
sheet = workbook.Worksheets.CreateWorksheet("OrderValue");
WriteHistogramToSheet(sheet, orderPrices);
sheet = workbook.Worksheets.CreateWorksheet("OrderCount");
WriteHistogramToSheet(sheet, orderCount);
app.Save(workbook, "C:\\Users\\sethm\\Desktop\\HelloWorld.xlsx");
}
private DataTable PopulateDataTable(string SourceFileName)
{
ExcelApplication xlw = new ExcelApplication();
Workbook wb = xlw.Open(@"\\cc-srv-01\lptemp\" + SourceFileName);
Worksheet ImportSheet = wb.Worksheets[0];
// Only the first 60,000 rows of the spreadsheet will be used
int ColumnsCount = 0;
switch (strImportType)
{
case "ACT":
ColumnsCount = 11;
break;
case "SAT":
ColumnsCount = 8;
break;
case "GPA":
ColumnsCount = 4;
break;
}
Area ImportArea = ImportSheet.CreateArea(0, 0, 60000, ColumnsCount);
// Call conversion method below to convert Area to DataTable
DataTable dtTempData = AreaToDataTable(ImportArea, true, new int[] { -1 }, "ImportData");
for (int c = 0; c < dtTempData.Rows.Count; c++)
{
if (dtTempData.Rows[c][1] != DBNull.Value)
{
DateTime temp = CellDateToDateTime(dtTempData.Rows[c][1]);
//DateTime temp = ExcelSerialDateToDateTime(Convert.ToInt32(dtTempData.Rows[c][1]));
dtTempData.Rows[c][1] = temp.ToShortDateString();
}
}
return dtTempData;
}
private DataTable PopulateDataTable(string SourceFileName)
{
ExcelApplication xlw = new ExcelApplication();
Workbook wb = xlw.Open(@"\\CCStorage\schoolusername\" + SourceFileName);
Worksheet ImportSheet = wb.Worksheets[0];
// Only the first 60,000 rows of the spreadsheet will be used
int ColumnsCount = 0;
switch (strImportType)
{
case "ACT":
ColumnsCount = 12;
break;
case "SAT":
ColumnsCount = 9;
break;
case "GPA":
ColumnsCount = 5;
break;
}
Area ImportArea = ImportSheet.CreateArea(0, 0, 60000, ColumnsCount);
// Call conversion method below to convert Area to DataTable
DataTable dtTempData = AreaToDataTable(ImportArea, false, new int[] { -1 }, "ImportData");
return dtTempData;
}
