private void button1_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
_excelApplication = new Excel.Application();
_excelApplication.DisplayAlerts = false;
// add a new workbook
Excel.Workbook workBook = _excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = workBook.Worksheets[1];
// we need some data to display
Excel.Range dataRange = PutSampleData(workSheet);
// create a nice diagram
Excel.ChartObject chart = workSheet.ChartObjects().Add(70, 100, 375, 225);
chart.Chart.SetSourceData(dataRange);
// save the book
string fileExtension = GetDefaultExtension(_excelApplication);
string workbookFile = string.Format("{0}\\Example05{1}", Environment.CurrentDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
_excelApplication.Quit();
_excelApplication.Dispose();
FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile);
fDialog.ShowDialog(this);
}
private void ProceedProductMonthInfo(Excel.Worksheet productSheet, SalesReportProduct itemProduct)
{
int rowIndex = 9;
int iMonthCellIndex = 1;
// we use the native invoker to set the style as string
Excel.Range range = productSheet.get_Range("$A9:$M13");
LateBindingApi.Core.Invoker.PropertySet(range, "Style", "MonthInfos");
productSheet.Cells[rowIndex + 1, iMonthCellIndex].Value = "ManufacturerPriceSummary";
productSheet.Cells[rowIndex + 2, iMonthCellIndex].Value = "SalesPricesSummary";
productSheet.Cells[rowIndex + 3, iMonthCellIndex].Value = "TotalRevenue";
productSheet.Cells[rowIndex + 4, iMonthCellIndex].Value = "CountOfSales";
iMonthCellIndex = 2;;
foreach (SalesReportReportEntity itemMonth in itemProduct.PrevMonths)
{
productSheet.Cells[rowIndex, iMonthCellIndex].Value = GetMonthName(iMonthCellIndex - 2);
productSheet.Cells[rowIndex + 1, iMonthCellIndex].Value = itemMonth.ManufactorPriceSummary;
productSheet.Cells[rowIndex + 2, iMonthCellIndex].Value = itemMonth.SalesPricesSummary;
productSheet.Cells[rowIndex + 3, iMonthCellIndex].Value = itemMonth.OutcomeSummary;
productSheet.Cells[rowIndex + 4, iMonthCellIndex].Value = itemMonth.CountOfSales;
iMonthCellIndex++;
}
string cellAdress1 = Helper.ToCellAdress(itemProduct.PrevMonths.Count + 2, 10);
string cellAdress2 = Helper.ToCellAdress(itemProduct.PrevMonths.Count + 2, 12);
productSheet.get_Range("$B10:$" + cellAdress1).Interior.Color = Helper.ToDouble(System.Drawing.Color.Gainsboro);
productSheet.get_Range("$B12:$" + cellAdress2).Interior.Color = Helper.ToDouble(System.Drawing.Color.Gainsboro);
productSheet.Cells[rowIndex, iMonthCellIndex].Value = GetMonthName(_monthToReport - 1);
productSheet.Cells[rowIndex + 1, iMonthCellIndex].Value = itemProduct.Month.ManufactorPriceSummary;
productSheet.Cells[rowIndex + 2, iMonthCellIndex].Value = itemProduct.Month.SalesPricesSummary;
productSheet.Cells[rowIndex + 3, iMonthCellIndex].Value = itemProduct.Month.OutcomeSummary;
productSheet.Cells[rowIndex + 4, iMonthCellIndex].Value = itemProduct.Month.CountOfSales;
for (int i = itemProduct.PrevMonths.Count + 2; i <= 12; i++)
{
iMonthCellIndex++;
productSheet.Cells[rowIndex, iMonthCellIndex].Value = GetMonthName(i - 1);
}
productSheet.get_Range("$B9:$M9").NumberFormat = "";
productSheet.get_Range("$B9:$M9").Font.Bold = true;
productSheet.get_Range("$B13:$M13").NumberFormat = "";
productSheet.get_Range("$B13:$M13").HorizontalAlignment = XlHAlign.xlHAlignCenter;
if (itemProduct.PrevMonths.Count < 11)
{
string topLeftMergeCellAdress = "$" + Helper.ToCellAdress(itemProduct.PrevMonths.Count + 3, 10);
productSheet.get_Range(topLeftMergeCellAdress + ":$M13").MergeCells = true;
}
}
private void SetProductStorageCountColor(int storageCount, Excel.Range range)
{
if (storageCount <= 50)
{
range.Interior.Color = Helper.ToDouble(System.Drawing.Color.Red);
}
else if (storageCount <= 100)
{
range.Interior.Color = Helper.ToDouble(System.Drawing.Color.Yellow);
}
else
{
range.Interior.Color = Helper.ToDouble(System.Drawing.Color.Green);
}
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application application = new Excel.Application();
application.DisplayAlerts = false;
application.Workbooks.Add();
Excel.Range sampleRange = application.Workbooks[1].Worksheets[1].Cells[1, 1];
// we set the COMVariant ColorIndex from Font of ouer sample range with the invoker class
// sometimes its easier than using COMVariant.ToCOMVariant Method or no overload for the underlying target type exists
Invoker.PropertySet(sampleRange.Font, "ColorIndex", 1);
// creates a native unmanaged ComProxy
object comProxy = Invoker.MethodReturn(application, "Workbooks");
Marshal.ReleaseComObject(comProxy);
application.Quit();
application.Dispose();
}
private void ProceedProductRankingInfo(Excel.Worksheet productSheet, SalesReportProduct itemProduct)
{
int rowIndex = 3;
int columnIndex = 4;
// we use the native invoker to set the style as string
Excel.Range range = productSheet.get_Range("$D3:$F6");
LateBindingApi.Core.Invoker.PropertySet(range, "Style", "StorageAndRanking");
productSheet.Cells[rowIndex, columnIndex].Value = "Count Ranking";
productSheet.Cells[rowIndex, columnIndex].Font.Bold = true;
productSheet.Cells[rowIndex + 1, columnIndex].Value = "Month";
productSheet.Cells[rowIndex + 2, columnIndex].Value = "Year";
productSheet.Cells[rowIndex + 3, columnIndex].Value = "Total";
productSheet.Cells[rowIndex + 1, columnIndex + 2].Value = itemProduct.SalesRankMonth;
productSheet.Cells[rowIndex + 2, columnIndex + 2].Value = itemProduct.SalesRankYear;
productSheet.Cells[rowIndex + 3, columnIndex + 2].Value = itemProduct.SalesRankTotal;
productSheet.get_Range("$D4:$E6").Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlLineStyleNone;
}
private void ProceedProductStorageInfo(Excel.Worksheet productSheet, SalesReportProduct itemProduct)
{
int rowIndex = 3;
int columnIndex = 1;
// we use the native invoker to set the style as string
Excel.Range range = productSheet.get_Range("$A3:$B6");
LateBindingApi.Core.Invoker.PropertySet(range, "Style", "StorageAndRanking");
productSheet.Cells[rowIndex, columnIndex].Value = "Storage Info";
productSheet.Cells[rowIndex, columnIndex].Font.Bold = true;
productSheet.Cells[rowIndex + 1, columnIndex].Value = "Storage Count";
productSheet.Cells[rowIndex + 2, columnIndex].Value = "Sales in Progress";
productSheet.Cells[rowIndex + 3, columnIndex].Value = "Recalc Storage Count ";
productSheet.Cells[rowIndex + 1, columnIndex + 1].Value = itemProduct.StorageCount;
productSheet.Cells[rowIndex + 2, columnIndex + 1].Value = itemProduct.OpenOrders.CountOfSales;
productSheet.Cells[rowIndex + 3, columnIndex + 1].Value = itemProduct.StorageCount - itemProduct.OpenOrders.CountOfSales;
SetProductStorageCountColor(itemProduct.StorageCount, productSheet.Cells[rowIndex + 1, columnIndex + 1]);
SetProductStorageCountColor(itemProduct.StorageCount - itemProduct.OpenOrders.CountOfSales, productSheet.Cells[rowIndex + 3, columnIndex + 1]);
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application application = new Excel.Application();
application.DisplayAlerts = false;
application.Workbooks.Add();
/*
* COMVariant is a LateBindingApi defined Type as substitute for the COM Datatype Variant.
*
* To work with COMVariant use there following properties:
* object UnderlyingObject - the mapped original object
* bool IsCOMProxy - UnderlyingObject is a COM Proxy or native type
* string TypeName - the name of UnderlyingObject type
*
* You can cast the COMVariant directly in another LateBindingApi Type, see the following example
*/
/* Example: */
/* the property Selection from Excel.Application is defined as COMVariant */
/* It can have multiple types at runtime what is currently selected, a worksheet, a range, a window or other*/
// select worksheet for example
application.Workbooks[1].Worksheets[1].Select();
COMVariant myVariant = application.Selection;
if (null != myVariant)
{
switch (myVariant.TypeName)
{
case "Worksheet":
Excel.Worksheet sheet = (Excel.Worksheet)myVariant;
break;
case "Range":
Excel.Range range = (Excel.Range)myVariant;
break;
}
}
/* Another example: */
/* GetOpenFileName returns a COMVariant there can be a string or a boolean in case of user clicks cancel */
myVariant = application.GetOpenFilename("Text Files (*.txt), *.txt");
if (null != myVariant)
{
string name = myVariant.TypeName;
object underlyingObject = myVariant.UnderlyingObject;
string message = string.Format("GetOpenFilename returns a {0}\r\n{1}", name, underlyingObject);
MessageBox.Show(this, message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
/* Last example: */
/* a lot of scalar properties defined as variant in excel (the reason is still unkown)*/
/* use the multiple overload helper function ToCOMVariant */
/* Note: a other way to handle multiple types in a variable is the Invoker, see Tutorial03 */
Excel.Range sampleRange = application.Workbooks[1].Worksheets[1].Cells[1, 1];
int colorIndex = (int)sampleRange.Font.ColorIndex.UnderlyingObject;
sampleRange.Font.ColorIndex = COMVariant.ToCOMVariant(colorIndex + 1);
/*
* COMObject inherited COMVariant and is a LateBindingApi defined Type as substitute for a unkown Dispatch Interface aka System._ComProxy in .NET.
* All LateBindingApi WrapperClasses inherites COMObject
* You can cast COMObject directly in another LateBindingApi Type, see the following example
* To work with COMObject use the following properties:
* object UnderlyingObject - the mapped original System._ComProxy object
* string TypeName - the name of UnderlyingObject type
*/
foreach (Office.COMAddIn item in application.COMAddIns)
{
/* COMAddIn.Application is defined as COMObject*/
string name = item.Application.TypeName;
Excel.Application parentApplication = (Excel.Application)item.Application;
}
application.Quit();
application.Dispose();
}
