/// <summary>
/// starting the excel application
/// </summary>
private void InitExcel()
{
if (excelApplication == null)
{
excelApplication = new Microsoft.Office.Interop.Excel.ApplicationClass();
}
excelApplication.Visible = (bool)esh_app_visible;
}
public void AttachApplication()
{
try
{
if (m_ExcelFileName == null || m_ExcelFileName.Length == 0) return;
// Creation of the workbook object
if ((m_Workbook = GetActiveWorkbook(m_ExcelFileName)) == null) return;
// Create the Excel.Application object
m_XlApplication = (Microsoft.Office.Interop.Excel.Application)m_Workbook.Application;
}
catch
{
MessageBox.Show("Impossible de charger le fichier Excel");
return;
}
}
public void Close(bool save)
{
try
{
// Quit Excel and clean up.
if (m_Workbook != null)
{
m_Workbook.Close(save, Missing.Value, Missing.Value);
System.Runtime.InteropServices.Marshal.ReleaseComObject
(m_Workbook);
m_Workbook = null;
}
if (m_XlApplication != null)
{
m_XlApplication.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject
(m_XlApplication);
m_XlApplication = null;
System.GC.Collect();
}
}
catch
{
MessageBox.Show("Failed to close the application");
}
}
/// <summary>
/// starting the excel application
/// </summary>
private void InitExcel()
{
if (excelApplication == null)
{
excelApplication = new Microsoft.Office.Interop.Excel.ApplicationClass();
}
excelApplication.Visible = (bool)esh_app_visible;
}
/// <summary>
/// Updates the chart.
/// </summary>
/// <param name="chartDataObject">The chart data object.</param>
public void UpdateChart(ChartDataObject chartDataObject)
{
Chart chart = null;
Shape shape = null;
Microsoft.Office.Interop.Excel.Workbook Workbook = null;
Microsoft.Office.Interop.Excel.Application Application = null;
try
{
Selection selection = this.Application.ActiveWindow.Selection;
if (selection.Type == PpSelectionType.ppSelectionShapes && selection.ShapeRange.HasChart == Office.MsoTriState.msoTrue)
{
int count = selection.ShapeRange.Count;
for (int i = 0; i < count; i++)
{
//Loop through all shapes
shape = selection.ShapeRange[i + 1];
if (shape.HasChart == Office.MsoTriState.msoTrue)
{
//Set the first chart in the selection
chart = shape.Chart;
if (chart != null)
{
break;
}
}
}
if (chart == null)
{
return;
}
chart = AssignExternalDataToChart(chartDataObject, chart, shape);
chart.ChartData.Activate();
}
}
finally
{
//Close the Excel Workbook and clean up resources
if (chart != null && chart.ChartData != null)
{
Workbook = chart.ChartData.Workbook as Microsoft.Office.Interop.Excel.Workbook;
if (Workbook != null)
{
Application = Workbook.Application;
}
if (Application != null && Workbook != null)
{
//Quit Excel Application
Application.Quit();
Application = null;
Workbook = null;
//Force GC, this is recommended for releasing resources since,
//quitting Excel doesnt ensure resources are cleaned immediatly
GC.Collect();
GC.WaitForPendingFinalizers();
//Repeated calls is necessary is because memory for the Excel
//would have survived for the first pass
//Hence second try to recalim completely.
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
}
}
/// <summary>
/// Inserts the chart.
/// </summary>
/// <param name="chartDataObject">The chart data object.</param>
public void InsertChart(ChartDataObject chartDataObject)
{
Chart chart = null;
Shape shape = null;
Microsoft.Office.Interop.Excel.Workbook Workbook = null;
Microsoft.Office.Interop.Excel.Application Application = null;
try
{
//Add the new slide
Slide slide = this.Application.ActivePresentation.Slides.Add(this.Application.ActivePresentation.Slides.Count + 1, PpSlideLayout.ppLayoutChart);
//Insert the chart
shape = slide.Shapes.AddChart(Office.XlChartType.xlLine);
chart = shape.Chart;
//Clear the chart contents
chart.ChartArea.ClearContents();
object[] Values = new object[chartDataObject.XValues.Count()];
for (int i = 0; i < chartDataObject.Data.Rows.Count; i++)
{
for (int j = 0; j < chartDataObject.XValues.Length; j++)
{
Values[j] = chartDataObject.Data.Rows[i][chartDataObject.XValues[j].ToString()];
}
SeriesCollection SeriesCollection = chart.SeriesCollection() as SeriesCollection;
//Create new series based on the data
Series Series = SeriesCollection.NewSeries();
Series.Name = chartDataObject.Data.Rows[i][chartDataObject.SeriesName].ToString();
Series.XValues = chartDataObject.XValues;
Series.Values = Values;
}
//Activate the chart to refresh with the fresh data.
chart.ChartData.Activate();
}
finally
{
//Close the Excel Workbook and clean up resources.
if (chart != null && chart.ChartData != null)
{
Workbook = chart.ChartData.Workbook as Microsoft.Office.Interop.Excel.Workbook;
if (Workbook != null)
{
Application = Workbook.Application;
}
if (Application != null && Workbook != null)
{
//Quit Excel Application
Application.Quit();
Application = null;
Workbook = null;
//Force the GC, this is recommended for releasing the resources.
//Quitting Excel doesnt ensure resources are cleaned immediatly
GC.Collect();
GC.WaitForPendingFinalizers();
//Repeated calls is necessary is because the memory for the Excel
//would have survived for the first pass.
//Hence the second try to recalim completely.
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
}
}
public void Ribbon_Load(Office.IRibbonUI ribbonUI)
{
this.ribbon = ribbonUI;
ExcelApplication = (Microsoft.Office.Interop.Excel.Application)Globals.ThisAddIn.Application;
ExcelApplication.SheetSelectionChange += new Microsoft.Office.Interop.Excel.AppEvents_SheetSelectionChangeEventHandler(excelApplication_SheetSelectionChange);
}
public void ExportToExcel(string userSavePath)
{
Microsoft.Office.Interop.Excel.Application excelApplicaton = new Microsoft.Office.Interop.Excel.Application();
if (excelApplicaton == null)
{
MessageBox.Show("An error was registered when testing Excel installation. Please verify that Excel is correctly installed :------)");
}
object misValue = System.Reflection.Missing.Value;
Workbook excelWorkBook = excelApplicaton.Workbooks.Add(misValue);
Worksheet excelWorkSheet = (Worksheet)excelWorkBook.Worksheets.get_Item(1);
excelWorkSheet.Cells[1, 2] = "This report was printed " + DateTime.Now;
excelWorkSheet.Cells[3, 2] = "This Excel is a report from scanning your Outlook calendar between the following dates: " + start.Date.ToString("dd-MM-yyyy") + " to " + end.Date.ToString("dd-MM-yyyy");
excelWorkSheet.Cells[6, 3] = "Object";
excelWorkSheet.Cells[6, 4] = "Task";
excelWorkSheet.Cells[6, 5] = "Total";
result = outlookItemsList.GroupBy(x => new { x.Start.Date, BusinessObject = x.Categories }).Select(z => new SummarizedDate {
Date = z.First().Start.Date, SummarizedTime = z.Sum(c => c.Duration), BuesinessObject = z.First().Categories
}).ToList();
//result = outlookItemsList.GroupBy(x => x.Start.Date).Select(z => new SummarizedDate { Date = z.First().Start.Date, SummarizedTime = z.Sum(c => c.Duration), BuesinessObject = z.First().Categories }).ToList();
//need this to be able to sort out per category instead. have to implement this at all places
int taskCounter = 0;
int objectCounter = 7;
//prints workobjects and tasks into excel
foreach (string workObject in listOfObjects)
{
objectCounter += 2;
taskCounter++;
//excelWorkSheet.Cells[objectCounter - 1, 3] = " ";
excelWorkSheet.Cells[objectCounter, 3] = workObject;
taskCounter = objectCounter;
foreach (string taskObject in listOfTasks)
{
excelWorkSheet.Cells[taskCounter, 4] = taskObject;
taskCounter++;
objectCounter++;
}
excelWorkSheet.Cells[taskCounter + 1, 4] = "Total";
}
int startTimeCounter = 0;
//All dates that are to be included into excel
for (int j = 6; j < listOfStartTimes.Count + 6; j++)
{
excelWorkSheet.Cells[6, j] = listOfStartTimes[startTimeCounter];
startTimeCounter++;
//var joined = outlookItemsList.Join(listOfStartTimes, x => x.Start.Date, y => Convert.ToDateTime(y), (x, y) => new JointDateOutlookItem { JoinedDate = x, });
//(x, y) => new Classname { prop1 = y.name, prop2 = y.key } exempelvis
}
//todo: write function to add dates to Excel
for (int i = 0; i > result.Count; i++)
{
}
foreach (SummarizedDate s in result)
{
Range currentFind = null;
Range firstFind = null;
//TODO: Idea: write code in following way: input fields in UI gets input business objects, while the currentFind variable searches for the FIRST PART of user category.
//TODO: implement something to separate the category string. Format is e.g. Grow - Development.
//TODO: right now business task is separated with LINQ right into the result variable.
Range Fruits = excelApplicaton.get_Range("C5", "C10");
// "You should specify all these parameters every time you call this method,
// since they can be overridden in the user interface."
currentFind = Fruits.Find("Grow", missing,
XlFindLookIn.xlValues, XlLookAt.xlPart,
XlSearchOrder.xlByRows, XlSearchDirection.xlNext, false,
missing, missing);
while (currentFind != null)
{
// Keep track of the first range you find.
if (firstFind == null)
{
firstFind = currentFind;
}
// If you didn't move to a new range, you are done.
else if (currentFind.get_Address(XlReferenceStyle.xlA1)
== firstFind.get_Address(XlReferenceStyle.xlA1))
{
break;
}
currentFind.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
currentFind.Font.Bold = true;
currentFind = Fruits.FindNext(currentFind);
string test1 = currentFind.Value2;
string test4 = Convert.ToString(currentFind.Cells);
string test2 = currentFind.Value;
}
}
try
{
excelWorkBook.SaveAs(userSavePath + "/myFabReport", XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
excelWorkBook.Close(true, misValue, misValue);
excelApplicaton.Quit();
Marshal.ReleaseComObject(excelWorkSheet);
Marshal.ReleaseComObject(excelWorkBook);
Marshal.ReleaseComObject(excelApplicaton);
MessageBox.Show("An Excel file has been created at " + userSavePath);
}
catch (COMException)
{
MessageBox.Show("Something went wrong. Either you have an old file with the same name open before running OR chose No at the last prompt when asked to save the Excel.");
}
}
