private void ProceedSummaryPrintSettings(Excel.Worksheet summarySheet)
{
summarySheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
summarySheet.PageSetup.Zoom = false;
summarySheet.PageSetup.FitToPagesTall = 1;
summarySheet.PageSetup.FitToPagesWide = 1;
}
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 static Excel.Range PutSampleData(Excel.Worksheet workSheet)
{
workSheet.Cells[2, 2].Value = "Datum";
workSheet.Cells[3, 2].Value = DateTime.Now.ToShortDateString();
workSheet.Cells[4, 2].Value = DateTime.Now.ToShortDateString();
workSheet.Cells[5, 2].Value = DateTime.Now.ToShortDateString();
workSheet.Cells[6, 2].Value = DateTime.Now.ToShortDateString();
workSheet.Cells[2, 3].Value = "Column1";
workSheet.Cells[3, 3].Value = 25;
workSheet.Cells[4, 3].Value = 33;
workSheet.Cells[5, 3].Value = 30;
workSheet.Cells[6, 3].Value = 22;
workSheet.Cells[2, 4].Value = "Column2";
workSheet.Cells[3, 4].Value = 25;
workSheet.Cells[4, 4].Value = 33;
workSheet.Cells[5, 4].Value = 30;
workSheet.Cells[6, 4].Value = 22;
workSheet.Cells[2, 5].Value = "Column3";
workSheet.Cells[3, 5].Value = 25;
workSheet.Cells[4, 5].Value = 33;
workSheet.Cells[5, 5].Value = 30;
workSheet.Cells[6, 5].Value = 22;
return(workSheet.get_Range("$B2:$E6"));
}
private void ProceedProductMonthCharts(Excel.Worksheet productSheet, SalesReportProduct itemProduct)
{
double chartTop = productSheet.Rows[15].Top;
double chartWidth = productSheet.Columns[14].Left;
double chartHeight = productSheet.Rows[30].Top - productSheet.Rows[15].Top;
Excel.ChartObject chartMonths = productSheet.ChartObjects().Add(1, chartTop, chartWidth, chartHeight);
chartMonths.Chart.SetSourceData(productSheet.get_Range("$A9:$M12"));
chartTop = productSheet.Rows[31].Top;
chartWidth = productSheet.Columns[14].Left;
chartHeight = productSheet.Rows[40].Top - productSheet.Rows[33].Top;
Excel.ChartObject chartCountMonths = productSheet.ChartObjects().Add(1, chartTop, chartWidth, chartHeight);
chartCountMonths.Chart.ChartType = XlChartType.xlLine;
chartCountMonths.Chart.SetSourceData(productSheet.get_Range("$A13:$M13"));
double chartLeft = productSheet.Columns[15].Left;
chartTop = productSheet.Rows[15].Top;
chartWidth = productSheet.Columns[19].Left - productSheet.Columns[15].Left;
chartHeight = productSheet.Rows[30].Top - productSheet.Rows[15].Top;
Excel.ChartObject chartCountYears = productSheet.ChartObjects().Add(chartLeft, chartTop, chartWidth, chartHeight);
chartCountYears.Chart.ChartType = XlChartType.xlCylinderColClustered;
chartCountYears.Chart.SetSourceData(productSheet.get_Range("$O9:$P12"));
}
private void ProceedProductPrintSettings(Excel.Worksheet productSheet)
{
productSheet.PageSetup.Orientation = XlPageOrientation.xlLandscape;
productSheet.PageSetup.Zoom = false;
productSheet.PageSetup.FitToPagesTall = 1;
productSheet.PageSetup.FitToPagesWide = 1;
productSheet.PageSetup.PrintArea = "$A$1:$R$39";
}
private void ProceedSummaryMatrix(SalesReport report, Excel.Worksheet summarySheet, Excel.Style matrixStyle)
{
// table columns
summarySheet.get_Range("B2").Value = "Count";
summarySheet.get_Range("C2").Value = "Revenue";
summarySheet.get_Range("D2").Value = "%";
summarySheet.get_Range("E2").Value = "Storage";
string leftBottomCellAdress = Helper.ToCellAdress(1, 3 + report.Products.Length);
string rightBottomCellAdress = Helper.ToCellAdress(5, 3 + report.Products.Length);
summarySheet.get_Range("$A2:$" + rightBottomCellAdress).Style = matrixStyle;
int rowIndex = 3;
int columnIndex = 1;
int i = 0;
foreach (SalesReportProduct itemProduct in report.Products)
{
string prodName = itemProduct.ProductName;
int prodId = itemProduct.ProductId;
summarySheet.Cells[rowIndex, columnIndex].Value = prodName;
string formula = string.Format("='{0}-{1}'!{2}", itemProduct.ProductName, itemProduct.ProductId, Helper.ToCellAdress(_monthToReport + 1, 13));
summarySheet.Cells[rowIndex, columnIndex + 1].Value = formula;
formula = string.Format("='{0}-{1}'!{2}", itemProduct.ProductName, itemProduct.ProductId, Helper.ToCellAdress(_monthToReport + 1, 12));
summarySheet.Cells[rowIndex, columnIndex + 2].Value = formula;
formula = string.Format("={0}*100/{1}", Helper.ToCellAdress(3, rowIndex), Helper.ToCellAdress(3, 3 + report.Products.Length));
summarySheet.Cells[rowIndex, columnIndex + 3].Formula = formula;
formula = string.Format("='{0}-{1}'!{2}", itemProduct.ProductName, itemProduct.ProductId, "B6");
summarySheet.Cells[rowIndex, columnIndex + 4].Value = formula;
int storeCount = Convert.ToInt16(summarySheet.Cells[rowIndex, columnIndex + 4].Value);
if ((i % 2) == 0)
{
summarySheet.get_Range("$A" + (i + 3).ToString() + ":$E" + (i + 3).ToString()).Interior.Color = Helper.ToDouble(System.Drawing.Color.Gainsboro);
}
rowIndex++;
i++;
}
string sumFormula = string.Format("=Sum({0}:{1})", "C3", "C" + (report.Products.Length + 3 - 1).ToString());
summarySheet.Cells[rowIndex, columnIndex + 2].Value = sumFormula;
summarySheet.get_Range("$C3:$C" + (report.Products.Length + 3).ToString()).NumberFormat = "#,##0.00 €";
summarySheet.get_Range("$D3:$D" + (report.Products.Length + 3).ToString()).NumberFormat = "0\"%\"";
summarySheet.Cells[3 + report.Products.Length, 1].Value = "Total:";
summarySheet.get_Range("D2").HorizontalAlignment = XlHAlign.xlHAlignCenter;
summarySheet.get_Range("$B2:$E2").Font.Bold = true;
summarySheet.get_Range(leftBottomCellAdress + ":" + rightBottomCellAdress).Font.Bold = true;
summarySheet.get_Range(leftBottomCellAdress + ":" + rightBottomCellAdress).BorderAround(XlLineStyle.xlDouble, XlBorderWeight.xlMedium);
}
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];
/*do background color for cells*/
string listSeperator = System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator;
// draw the face
string rangeAdressFace = string.Format("$C10:$M10{0}$C30:$M30{0}$C11:$C30{0}$M11:$M30", listSeperator);
workSheet.get_Range(rangeAdressFace).Interior.Color = ToDouble(Color.DarkGreen);
string rangeAdressEyes = string.Format("$F14{0}$J14", listSeperator);
workSheet.get_Range(rangeAdressEyes).Interior.Color = ToDouble(Color.Black);
string rangeAdressNoise = string.Format("$G18:$I19", listSeperator);
workSheet.get_Range(rangeAdressNoise).Interior.Color = ToDouble(Color.DarkGreen);
string rangeAdressMouth = string.Format("$F26{0}$J26{0}$G27:$I27", listSeperator);
workSheet.get_Range(rangeAdressMouth).Interior.Color = ToDouble(Color.DarkGreen);
// border the face with the border arround method
workSheet.get_Range(rangeAdressFace).BorderAround(XlLineStyle.xlDashDot, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexNone, Color.BlueViolet.ToArgb());
workSheet.get_Range(rangeAdressEyes).BorderAround(XlLineStyle.xlDashDot, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexNone, Color.BlueViolet.ToArgb());
workSheet.get_Range(rangeAdressNoise).BorderAround(XlLineStyle.xlDouble, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexNone, Color.BlueViolet.ToArgb());
// border explicitly
workSheet.get_Range(rangeAdressMouth).Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlDouble;
workSheet.get_Range(rangeAdressMouth).Borders[XlBordersIndex.xlEdgeBottom].Weight = 4;
workSheet.get_Range(rangeAdressMouth).Borders[XlBordersIndex.xlEdgeBottom].Color = ToDouble(Color.Black);
// save the book
string fileExtension = GetDefaultExtension(_excelApplication);
string workbookFile = string.Format("{0}\\Example01{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 button1_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
_excelApplication = new Excel.Application();
_excelApplication.DisplayAlerts = false;
_excelApplication.ScreenUpdating = false;
// add a new workbook
Excel.Workbook workBook = _excelApplication.Workbooks.Add();
// we use the first sheet as summary sheet and remove the 2 last sheets
Excel.Worksheet summarySheet = workBook.Worksheets[1];
workBook.Worksheets[3].Delete();
workBook.Worksheets[2].Delete();
// we get the data & perform the report
_report = new SalesReport(_yearToReport, _monthToReport);
_report.Proceed();
// we create named styles for the range.Style property
CreateStorageAndRankingStyle(workBook, "StorageAndRanking");
CreateMonthStyle(workBook, "MonthInfos");
CreateMonthStyle(workBook, "YearTotalInfos");
// write product sheets
Excel.Worksheet productSheet = null;
foreach (SalesReportProduct itemProduct in _report.Products)
{
productSheet = workBook.Worksheets.Add();
ProceedProductWorksheet(productSheet, itemProduct);
productSheet.Move(Missing.Value, workBook.Worksheets[workBook.Worksheets.Count]);
}
// write summary sheet
ProceedSummaryWorksheet(_report, workBook, summarySheet, productSheet);
summarySheet.get_Range("$A2").Select();
// save the book
string fileExtension = Helper.GetDefaultExtension(_excelApplication);
string workbookFile = string.Format("{0}\\Example10{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 ProceedProductWorksheetHeader(Excel.Worksheet productSheet, SalesReportProduct itemProduct)
{
int rowIndex = 1;
int columnIndex = 1;
productSheet.PageSetup.LeftHeader = "&D created";
productSheet.PageSetup.CenterHeader = "Vintage Digital Inc.";
productSheet.PageSetup.RightHeader = string.Format("Monthly Sales Report {1:00}/{0}", _yearToReport, _monthToReport);
productSheet.Cells[rowIndex, columnIndex].Value = itemProduct.ProductName;
productSheet.Cells[rowIndex, columnIndex].Font.Bold = true;
productSheet.Cells[rowIndex, columnIndex].Font.Underline = true;
productSheet.Cells[rowIndex, columnIndex].Font.Size = 14;
productSheet.Cells[rowIndex, columnIndex].Font.Name = "Verdana";
}
private void ProceedProductWorksheet(Excel.Worksheet productSheet, SalesReportProduct itemProduct)
{
string sheetName = string.Format("{0}-{1}", itemProduct.ProductName, itemProduct.ProductId.ToString());
productSheet.Name = sheetName;
// its not a random chain, write data first and create charts second
ProceedProductStorageInfo(productSheet, itemProduct);
ProceedProductMonthInfo(productSheet, itemProduct);
ProceedProductYearTotalInfo(productSheet, itemProduct);
ProceedProductMonthCharts(productSheet, itemProduct);
ProceedProductPrintSettings(productSheet);
productSheet.Columns.AutoFit(); // proceed AutoFit before header & ranking
ProceedProductWorksheetHeader(productSheet, itemProduct);
ProceedProductRankingInfo(productSheet, itemProduct);
productSheet.Columns[14].ColumnWidth = 2.14;
productSheet.Columns[17].ColumnWidth = 5.14;
}
private void ProceedProductYearTotalInfo(Excel.Worksheet productSheet, SalesReportProduct itemProduct)
{
int ColumnIndex = 15;
int RowIndex = 9;
LateBindingApi.Core.Invoker.PropertySet(productSheet.get_Range("$O9:$R13"), "Style", "YearTotalInfos");
productSheet.Cells[RowIndex, ColumnIndex].Value = "Year " + _yearToReport.ToString();
productSheet.Cells[RowIndex + 1, ColumnIndex].Value = itemProduct.Year.ManufactorPriceSummary;
productSheet.Cells[RowIndex + 2, ColumnIndex].Value = itemProduct.Year.SalesPricesSummary;
productSheet.Cells[RowIndex + 3, ColumnIndex].Value = itemProduct.Year.OutcomeSummary;
productSheet.Cells[RowIndex + 4, ColumnIndex].Value = itemProduct.Year.CountOfSales;
productSheet.Cells[RowIndex, ColumnIndex + 1].Value = "Year " + (_yearToReport - 1).ToString();
productSheet.Cells[RowIndex + 1, ColumnIndex + 1].Value = itemProduct.PrevYear.ManufactorPriceSummary;
productSheet.Cells[RowIndex + 2, ColumnIndex + 1].Value = itemProduct.PrevYear.SalesPricesSummary;
productSheet.Cells[RowIndex + 3, ColumnIndex + 1].Value = itemProduct.PrevYear.OutcomeSummary;
productSheet.Cells[RowIndex + 4, ColumnIndex + 1].Value = itemProduct.PrevYear.CountOfSales;
productSheet.get_Range("$O10:$P10").Interior.Color = Helper.ToDouble(System.Drawing.Color.Gainsboro);
productSheet.get_Range("$O12:$P12").Interior.Color = Helper.ToDouble(System.Drawing.Color.Gainsboro);
ColumnIndex = 18;
RowIndex = 9;
productSheet.Cells[RowIndex, ColumnIndex].Value = "Total";
productSheet.Cells[RowIndex + 1, ColumnIndex].Value = itemProduct.Total.ManufactorPriceSummary;
productSheet.Cells[RowIndex + 2, ColumnIndex].Value = itemProduct.Total.SalesPricesSummary;
productSheet.Cells[RowIndex + 3, ColumnIndex].Value = itemProduct.Total.OutcomeSummary;
productSheet.Cells[RowIndex + 4, ColumnIndex].Value = itemProduct.Total.CountOfSales;
productSheet.get_Range("$R10").Interior.Color = Helper.ToDouble(System.Drawing.Color.Gainsboro);
productSheet.get_Range("$R12").Interior.Color = Helper.ToDouble(System.Drawing.Color.Gainsboro);
productSheet.get_Range("$O9:$R9").NumberFormat = "";
productSheet.get_Range("$O9:$R9").Font.Bold = true;
productSheet.get_Range("$O13:$R13").NumberFormat = "";
productSheet.get_Range("$O13:$R13").HorizontalAlignment = XlHAlign.xlHAlignCenter;
productSheet.get_Range("$Q9:$Q13").MergeCells = true;
}
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];
workSheet.Cells[1, 1].Value = "these sample shapes was dynamicly created by code.";
// create a star
Excel.Shape starShape = workSheet.Shapes.AddShape(MsoAutoShapeType.msoShape32pointStar, 10, 50, 200, 20);
// create a simple textbox
Excel.Shape textBox = workSheet.Shapes.AddTextbox(MsoTextOrientation.msoTextOrientationHorizontal, 10, 150, 200, 50);
textBox.TextFrame.Characters().Text = "text";
textBox.TextFrame.Characters().Font.Size = 14;
// create a wordart
Excel.Shape textEffect = workSheet.Shapes.AddTextEffect(MsoPresetTextEffect.msoTextEffect14, "WordArt", "Arial", 12,
MsoTriState.msoTrue, MsoTriState.msoFalse, 10, 250);
// create text effect
Excel.Shape textDiagram = workSheet.Shapes.AddTextEffect(MsoPresetTextEffect.msoTextEffect11, "Effect", "Arial", 14,
MsoTriState.msoFalse, MsoTriState.msoFalse, 10, 350);
// save the book
string fileExtension = GetDefaultExtension(_excelApplication);
string workbookFile = string.Format("{0}\\Example04{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 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 ProceedSummaryWorksheetCharts(Excel.Worksheet summarySheet, int countOfProducts)
{
string captionRangeAdress = "$A2:$" + Helper.ToCellAdress(1, 1 + countOfProducts);
string fieldRangeAdress = "$C2:$" + Helper.ToCellAdress(3, 1 + countOfProducts);
double chartTopPosition = summarySheet.Rows[countOfProducts + 5].Top;
double chartWidth = summarySheet.Columns[13].Left;
Excel.ChartObject chartSummary = summarySheet.ChartObjects().Add(1, chartTopPosition, chartWidth, 260);
chartSummary.Chart.SetSourceData(summarySheet.get_Range(captionRangeAdress + ";" + fieldRangeAdress));
fieldRangeAdress = "$D2:$" + Helper.ToCellAdress(4, 1 + countOfProducts);
chartTopPosition = summarySheet.Rows[2].Top;
double chartLeftPosition = summarySheet.Columns[8].Left;
double chartHeight = summarySheet.Rows[countOfProducts + 3].Top - chartTopPosition;
chartWidth = summarySheet.Columns[13].Left - summarySheet.Columns[8].Left;
Excel.ChartObject chartPercentOutcome = summarySheet.ChartObjects().Add(chartLeftPosition, chartTopPosition, chartWidth, chartHeight);
chartPercentOutcome.Chart.ChartType = XlChartType.xlPie;
chartPercentOutcome.Chart.SetSourceData(summarySheet.get_Range(captionRangeAdress + ";" + fieldRangeAdress));
}
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)
{
// start excel and turn Application 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];
/* some kind of numerics */
// the given thread culture in all latebinding calls are stored in Core.Settings.
// you can change the culture. default is en-us.
CultureInfo cultureInfo = LateBindingApi.Core.Settings.ThreadCulture;
string Pattern1 = string.Format("0{0}00", cultureInfo.NumberFormat.CurrencyDecimalSeparator);
string Pattern2 = string.Format("#{1}##0{0}00", cultureInfo.NumberFormat.CurrencyDecimalSeparator, cultureInfo.NumberFormat.CurrencyGroupSeparator);
workSheet.get_Range("A1").Value = "Type";
workSheet.get_Range("B1").Value = "Value";
workSheet.get_Range("C1").Value = "Formatted " + Pattern1;
workSheet.get_Range("D1").Value = "Formatted " + Pattern2;
int integerValue = 532234;
workSheet.get_Range("A3").Value = "Integer";
workSheet.get_Range("B3").Value = integerValue;
workSheet.get_Range("C3").Value = integerValue;
workSheet.get_Range("C3").NumberFormat = Pattern1;
workSheet.get_Range("D3").Value = integerValue;
workSheet.get_Range("D3").NumberFormat = Pattern2;
double doubleValue = 23172.64;
workSheet.get_Range("A4").Value = "double";
workSheet.get_Range("B4").Value = doubleValue;
workSheet.get_Range("C4").Value = doubleValue;
workSheet.get_Range("C4").NumberFormat = Pattern1;
workSheet.get_Range("D4").Value = doubleValue;
workSheet.get_Range("D4").NumberFormat = Pattern2;
float floatValue = 84345.9132f;
workSheet.get_Range("A5").Value = "float";
workSheet.get_Range("B5").Value = floatValue;
workSheet.get_Range("C5").Value = floatValue;
workSheet.get_Range("C5").NumberFormat = Pattern1;
workSheet.get_Range("D5").Value = floatValue;
workSheet.get_Range("D5").NumberFormat = Pattern2;
Decimal decimalValue = 7251231.313367m;
workSheet.get_Range("A6").Value = "Decimal";
workSheet.get_Range("B6").Value = decimalValue;
workSheet.get_Range("C6").Value = decimalValue;
workSheet.get_Range("C6").NumberFormat = Pattern1;
workSheet.get_Range("D6").Value = decimalValue;
workSheet.get_Range("D6").NumberFormat = Pattern2;
workSheet.get_Range("A9").Value = "DateTime";
workSheet.get_Range("B10").Value = LateBindingApi.Core.Settings.ThreadCulture.DateTimeFormat.FullDateTimePattern;
workSheet.get_Range("C10").Value = LateBindingApi.Core.Settings.ThreadCulture.DateTimeFormat.LongDatePattern;
workSheet.get_Range("D10").Value = LateBindingApi.Core.Settings.ThreadCulture.DateTimeFormat.ShortDatePattern;
workSheet.get_Range("E10").Value = LateBindingApi.Core.Settings.ThreadCulture.DateTimeFormat.LongTimePattern;
workSheet.get_Range("F10").Value = LateBindingApi.Core.Settings.ThreadCulture.DateTimeFormat.ShortTimePattern;
// DateTime
DateTime dateTimeValue = DateTime.Now;
workSheet.get_Range("B11").Value = dateTimeValue;
workSheet.get_Range("B11").NumberFormat = LateBindingApi.Core.Settings.ThreadCulture.DateTimeFormat.FullDateTimePattern;
workSheet.get_Range("C11").Value = dateTimeValue;
workSheet.get_Range("C11").NumberFormat = LateBindingApi.Core.Settings.ThreadCulture.DateTimeFormat.LongDatePattern;
workSheet.get_Range("D11").Value = dateTimeValue;
workSheet.get_Range("D11").NumberFormat = LateBindingApi.Core.Settings.ThreadCulture.DateTimeFormat.ShortDatePattern;
workSheet.get_Range("E11").Value = dateTimeValue;
workSheet.get_Range("E11").NumberFormat = LateBindingApi.Core.Settings.ThreadCulture.DateTimeFormat.LongTimePattern;
workSheet.get_Range("F11").Value = dateTimeValue;
workSheet.get_Range("F11").NumberFormat = LateBindingApi.Core.Settings.ThreadCulture.DateTimeFormat.ShortTimePattern;
// string
workSheet.get_Range("A14").Value = "String";
workSheet.get_Range("B14").Value = "This is a sample String";
workSheet.get_Range("B14").NumberFormat = "@";
// number as string
workSheet.get_Range("B15").Value = "513";
workSheet.get_Range("B15").NumberFormat = "@";
// set colums
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].AutoFit();
workSheet.Columns[3].AutoFit();
workSheet.Columns[4].AutoFit();
// save the book
string fileExtension = GetDefaultExtension(_excelApplication);
string workbookFile = string.Format("{0}\\Example03{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 button1_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
_excelApplication = new Excel.Application();
_excelApplication.DisplayAlerts = false;
// dont show dialogs with an invisible excel
_excelApplication.Visible = true;
// add a new workbook
Excel.Workbook workBook = _excelApplication.Workbooks.Add();
Excel.Worksheet workSheet = workBook.Worksheets[1];
// show selected window and display user clicks ok or cancel
bool returnValue = false;
RadioButton radioSelectButton = GetSelectedRadioButton();
switch (radioSelectButton.Text)
{
case "xlDialogAddinManager":
returnValue = _excelApplication.Dialogs[XlBuiltInDialog.xlDialogAddinManager].Show();
break;
case "xlDialogFont":
returnValue = _excelApplication.Dialogs[XlBuiltInDialog.xlDialogFont].Show();
break;
case "xlDialogEditColor":
returnValue = _excelApplication.Dialogs[XlBuiltInDialog.xlDialogEditColor].Show();
break;
case "xlDialogGallery3dBar":
returnValue = _excelApplication.Dialogs[XlBuiltInDialog.xlDialogGallery3dBar].Show();
break;
case "xlDialogSearch":
returnValue = _excelApplication.Dialogs[XlBuiltInDialog.xlDialogSearch].Show();
break;
case "xlDialogPrinterSetup":
returnValue = _excelApplication.Dialogs[XlBuiltInDialog.xlDialogPrinterSetup].Show();
break;
case "xlDialogFormatNumber":
returnValue = _excelApplication.Dialogs[XlBuiltInDialog.xlDialogFormatNumber].Show();
break;
case "xlDialogApplyStyle":
returnValue = _excelApplication.Dialogs[XlBuiltInDialog.xlDialogApplyStyle].Show();
break;
default:
throw (new Exception("Unkown dialog selected."));
}
string message = string.Format("The dialog returns {0}.", returnValue);
MessageBox.Show(this, message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
// close excel and dispose reference
_excelApplication.Quit();
_excelApplication.Dispose();
}
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();
}
private void ProceedSummaryWorksheetHeader(Excel.Worksheet summarySheet)
{
summarySheet.PageSetup.LeftHeader = "&D created";
summarySheet.PageSetup.CenterHeader = "Vintage Digital Inc.";
summarySheet.PageSetup.RightHeader = string.Format("Monthly Sales Report {1:00}/{0}", _yearToReport, _monthToReport);
}
private void ProceedSummaryWorksheet(SalesReport report, Excel.Workbook workBook, Excel.Worksheet summarySheet, Excel.Worksheet afterSheet)
{
summarySheet.Name = "Summary";
Excel.Style matrixStyle = CreateSummaryStyle(workBook, "MatrixStyle");
ProceedSummaryMatrix(report, summarySheet, matrixStyle);
ProceedSummaryWorksheetCharts(summarySheet, report.Products.Length + 1);
ProceedSummaryPrintSettings(summarySheet);
summarySheet.Columns.AutoFit();// proceed AutoFit before header
ProceedSummaryWorksheetHeader(summarySheet);
summarySheet.Select();
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application application = new LateBindingApi.Excel.Application();
application.DisplayAlerts = false;
/*
* LateBindingApi.Excel manages COM Proxies for you to avoid any kind of memory leaks
* and make sure your excel instance removes from process list if you want.
*/
Excel.Workbook book = application.Workbooks.Add();
/*
* now we have 2 new COM Proxies created.
*
* the first proxy was created while accessing the Workbooks collection from application
* the second proxy was created by the Add() method from Workbooks and stored now in book
*/
Excel.Worksheet sheet = book.Worksheets.Add();
/*
* we create also 2 new COM Proxies.
*
* the first proxy was created while accessing the Worksheets collection from book
* the second proxy was created by the Add() method from Worksheets and stored now in sheet
*
* the 2 proxies was created about the book object and are managed now by the book object
* they are now 'child proxies' collected in a internal list.
*/
book.Dispose();
/* now we have disposed the Workbook, the inner Proxy will be released */
/* all child proxies there was created about the book are also released now */
/* this means the 2 proxies there we have created while add a new worksheet */
/* the local stored variable Excel.Worksheet sheet are not longer valid */
/*
* SUMMARY:
* With LateBindingApi.Excel you have the choice to use Dispose() for any object or doesnt.
* In case of doesn't dont forget to dispose the application and all child proxies will be released automaticly.
* It is necessary to understand the demonstrated child proxy concept of LateBindingApi.Excel to avoid any suprises.
* With these concept you have a powerfull third way. Use child proxies without Dispose() to get more readable & smaller code.
* Dispose the parent object of your childs and thats all.
*/
/* another example for efficient using child proxy management */
book = application.Workbooks.Add();
foreach (Excel.Worksheet item in book.Worksheets)
{
for (int i = 1; i <= 100; i++)
{
item.Cells[1, i].Value = "Hello World";
}
}
book.Dispose();
/*
* we create a new workbook with default 3 new worksheets
* we do a foreach to all worksheets and write 100 times "Hello World" in cells
* we have created now 300 range proxies 3 worksheet proxies 1 worksheet enumerator proxy and the book proxy
* the parent object is ouer book, diposed it and the child proxies will be also disposed
* Note: its not allowed in the MS Interop Assemblies to use an enumerator like these.
* you must store it in a local variable first and dispose explicitly after using.
* With LateBindingApi.Excel you can do this without any problems.
*/
application.Quit();
application.Dispose();
/*the application object is ouer root object*/
/*dispose them will release himself and any childs*/
/*the excel instance are now removed from process list*/
MessageBox.Show(this, "Done!", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
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];
// font action
workSheet.get_Range("A1").Value = "Arial Size:8 Bold Italic Underline";
workSheet.get_Range("A1").Font.Name = "Arial";
workSheet.get_Range("A1").Font.Size = 8;
workSheet.get_Range("A1").Font.Bold = true;
workSheet.get_Range("A1").Font.Italic = true;
workSheet.get_Range("A1").Font.Underline = true;
workSheet.get_Range("A1").Font.Color = Color.Violet.ToArgb();
workSheet.get_Range("A3").Value = "Times New Roman Size:10";
workSheet.get_Range("A3").Font.Name = "Times New Roman";
workSheet.get_Range("A3").Font.Size = 10;
workSheet.get_Range("A3").Font.Color = Color.Orange.ToArgb();
workSheet.get_Range("A5").Value = "Comic Sans MS Size:12 WrapText";
workSheet.get_Range("A5").Font.Name = "Comic Sans MS";
workSheet.get_Range("A5").Font.Size = 12;
workSheet.get_Range("A5").WrapText = true;
workSheet.get_Range("A5").Font.Color = Color.Navy.ToArgb();
// HorizontalAlignment
workSheet.get_Range("A7").Value = "xlHAlignLeft";
workSheet.get_Range("A7").HorizontalAlignment = XlHAlign.xlHAlignLeft;
workSheet.get_Range("B7").Value = "xlHAlignCenter";
workSheet.get_Range("B7").HorizontalAlignment = XlHAlign.xlHAlignCenter;
workSheet.get_Range("C7").Value = "xlHAlignRight";
workSheet.get_Range("C7").HorizontalAlignment = XlHAlign.xlHAlignRight;
workSheet.get_Range("D7").Value = "xlHAlignJustify";
workSheet.get_Range("D7").HorizontalAlignment = XlHAlign.xlHAlignJustify;
workSheet.get_Range("E7").Value = "xlHAlignDistributed";
workSheet.get_Range("E7").HorizontalAlignment = XlHAlign.xlHAlignDistributed;
// VerticalAlignment
workSheet.get_Range("A9").Value = "xlVAlignTop";
workSheet.get_Range("A9").VerticalAlignment = XlVAlign.xlVAlignTop;
workSheet.get_Range("B9").Value = "xlVAlignCenter";
workSheet.get_Range("B9").VerticalAlignment = XlVAlign.xlVAlignCenter;
workSheet.get_Range("C9").Value = "xlVAlignBottom";
workSheet.get_Range("C9").VerticalAlignment = XlVAlign.xlVAlignBottom;
workSheet.get_Range("D9").Value = "xlVAlignDistributed";
workSheet.get_Range("D9").VerticalAlignment = XlVAlign.xlVAlignDistributed;
workSheet.get_Range("E9").Value = "xlVAlignJustify";
workSheet.get_Range("E9").VerticalAlignment = XlVAlign.xlVAlignJustify;
// setup rows and columns
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].ColumnWidth = 25;
workSheet.Columns[3].ColumnWidth = 25;
workSheet.Columns[4].ColumnWidth = 25;
workSheet.Columns[5].ColumnWidth = 25;
workSheet.Rows[9].RowHeight = 35;
// save the book
string fileExtension = GetDefaultExtension(_excelApplication);
string workbookFile = string.Format("{0}\\Example02{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);
}
