private void DrawFractionChart(Excel.Worksheet activeSheet, Excel.ChartObjects xlCharts, Excel.Range xRange, Excel.Range yRange) { Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(200, 500, 200, 100); Excel.Chart chartPage = myChart.Chart; Excel.SeriesCollection seriesCollection = chartPage.SeriesCollection(); Excel.Series series1 = seriesCollection.NewSeries(); series1.XValues = activeSheet.Range["E1", "E3"]; series1.Values = activeSheet.Range["F1", "F3"]; chartPage.ChartType = Excel.XlChartType.xlDoughnut; Excel.Axis axis = chartPage.Axes(Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary) as Excel.Axis; series1.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowPercent, true, true, false, true, true, true, true); }
public string FastMoveItems(bool FastOrSlowReport, int NumberOfItems, string DateFrom, string DateTo, bool ExportToExcel = false, string ExportPath = "") { try { Excel.Range chartRange; xlApp = new Excel.Application(); xlApp.DisplayAlerts = false; xlApp.Visible = false; xlApp.SheetsInNewWorkbook = 1; xlWorkBook = xlApp.Workbooks.Add(misValue);//misValue string ReportName = (FastOrSlowReport ? "Fast Move Items Report" : "Slow Move Items Report"); DataTable aTable = ReportsMgmt.FastMovItemBasedOnQty(NumberOfItems, DateFrom, DateTo, FastOrSlowReport); if (aTable.Rows.Count > 0) { int RowCnt = 1; xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Name = ReportName; List <string> aHeader = ReportsHelper.ImportReportHeader(0, 1); List <string> aFooter = ReportsHelper.ImportReportHeader(1, 1); for (int i = 0; i < aHeader.Count; i++) { string astringss = aHeader[i]; xlWorkSheet.Cells[RowCnt, 2] = aHeader[i]; RowCnt++; } xlWorkSheet.Cells[RowCnt++, 1] = ReportName; xlWorkSheet.Cells[RowCnt, 2] = "Date From:\t" + DateFrom; xlWorkSheet.Cells[RowCnt, 3] = "Date To:\t" + DateTo; RowCnt++; xlWorkSheet.Cells[RowCnt, 1] = "Item Description"; xlWorkSheet.Cells[RowCnt, 2] = "Qty Sold"; xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).Font.Bold = true; //xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).WrapText = true; xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; RowCnt++; int DataStart = RowCnt; foreach (DataRow aRow in aTable.Rows) { xlWorkSheet.Cells[RowCnt, 1] = aRow["ItemDescription"].ToString(); xlWorkSheet.Cells[RowCnt, 2] = aRow["Summation"].ToString(); RowCnt++; } xlWorkSheet.get_Range("A" + DataStart.ToString(), "K" + RowCnt.ToString()).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; xlWorkSheet.get_Range("A" + DataStart.ToString(), "K" + RowCnt.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; oRng = xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()); oRng.EntireColumn.AutoFit(); Excel.ChartObjects myCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); xlWorkSheet.DisplayRightToLeft = false; int size = aTable.Rows.Count * 100; if (size >= 600) { size = 600; } if (size < 300) { size = 300; } Excel.ChartObject myChart = (Excel.ChartObject)myCharts.Add(0, RowCnt * 15, size, 300); Excel.Chart chartPage = myChart.Chart; Excel.SeriesCollection seriesCollection = chartPage.SeriesCollection(); Excel.Series series1 = seriesCollection.NewSeries(); RowCnt--; //because we started from 1 suppose to be 0 series1.Name = ReportName; series1.XValues = xlWorkSheet.Range["A" + DataStart.ToString(), "A" + RowCnt.ToString()]; series1.Values = xlWorkSheet.Range["B" + DataStart.ToString(), "B" + RowCnt.ToString()]; chartPage.ChartType = Excel.XlChartType.xlColumnClustered; Excel.Axis axis = chartPage.Axes(Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary) as Excel.Axis; series1.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowBubbleSizes); object format = Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml; xlWorkSheet.SaveAs(ReportsHelper.TempOutputPath, format); if (ExportToExcel) { format = Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7; xlWorkSheet.SaveAs(ExportPath, format); } xlApp.UserControl = false; return("TRUE"); } else { return("EMPTY"); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); return("ERROR"); } finally { //Once done close and quit Excel xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); } }
//Revenues Comparison public string RevenuesComparison(int ByDDMMYYYY, string DateFrom, string DateTo, bool ExportToExcel = false, string ExportPath = "") { try { string ReportName = "Revenues Comparison Report"; DataTable aTable = ReportsMgmt.RevenuesComparions(ByDDMMYYYY, DateFrom, DateTo); if (aTable.Rows.Count > 0) { xlApp = new Excel.Application(); xlApp.DisplayAlerts = false; xlApp.Visible = false; xlApp.SheetsInNewWorkbook = 1; xlWorkBook = xlApp.Workbooks.Add(misValue);//misValue int RowCnt = 1; xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Name = ReportName; List <string> aHeader = ReportsHelper.ImportReportHeader(0, 1); List <string> aFooter = ReportsHelper.ImportReportHeader(1, 1); for (int i = 0; i < aHeader.Count; i++) { xlWorkSheet.Cells[RowCnt, 2] = aHeader[i]; RowCnt++; } xlWorkSheet.Cells[RowCnt++, 1] = ReportName; xlWorkSheet.Cells[RowCnt, 2] = "Date From:\t" + DateFrom; xlWorkSheet.Cells[RowCnt, 3] = "Date To:\t" + DateTo; RowCnt++; xlWorkSheet.Cells[RowCnt, 1] = "Date"; xlWorkSheet.Cells[RowCnt, 2] = "Total Cost JOD"; xlWorkSheet.Cells[RowCnt, 3] = "Total Sales JOD"; xlWorkSheet.Cells[RowCnt, 4] = "Gross Profit JOD"; xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).Font.Bold = true; //xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).WrapText = true; xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; RowCnt++; int DataStart = RowCnt; foreach (DataRow aRow in aTable.Rows) { if (ByDDMMYYYY == 0) //BY DAY { xlWorkSheet.Cells[RowCnt, 1] = aRow["Day"].ToString() + '\\' + aRow["Month"].ToString() + "\\" + aRow["Year"].ToString(); } else if (ByDDMMYYYY == 1)//BY MONTH { xlWorkSheet.Cells[RowCnt, 1] = aRow["Month"].ToString() + '\\' + aRow["Year"].ToString(); } else //BY YEAR { xlWorkSheet.Cells[RowCnt, 1] = aRow["Year"].ToString(); } xlWorkSheet.Cells[RowCnt, 2] = Math.Round(double.Parse(aRow["TotalCost"].ToString()), 2); xlWorkSheet.Cells[RowCnt, 3] = Math.Round(double.Parse(aRow["TotalPrice"].ToString()), 2); xlWorkSheet.Cells[RowCnt, 4] = Math.Round(double.Parse(aRow["TotalProfit"].ToString()), 2); RowCnt++; } xlWorkSheet.get_Range("A" + DataStart.ToString(), "K" + RowCnt.ToString()).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; xlWorkSheet.get_Range("A" + DataStart.ToString(), "K" + RowCnt.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; oRng = xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()); oRng.EntireColumn.AutoFit(); Excel.ChartObjects myCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); xlWorkSheet.DisplayRightToLeft = false; int size = aTable.Rows.Count * 100; if (size > 600) { size = 600; } if (size < 300) { size = 300; } Excel.ChartObject myChart = (Excel.ChartObject)myCharts.Add(0, RowCnt * 15, size, 300); Excel.Chart chartPage = myChart.Chart; Excel.SeriesCollection seriesCollection = chartPage.SeriesCollection(); Excel.Series series1 = seriesCollection.NewSeries(); Excel.Series series2 = seriesCollection.NewSeries(); Excel.Series series3 = seriesCollection.NewSeries(); RowCnt--; //because we started from 1 suppose to be 0 series1.Name = "Total Cost"; series1.XValues = xlWorkSheet.Range["A" + DataStart.ToString(), "A" + RowCnt.ToString()]; series1.Values = xlWorkSheet.Range["B" + DataStart.ToString(), "B" + RowCnt.ToString()]; series2.Name = "Total Sales"; series2.XValues = xlWorkSheet.Range["A" + DataStart.ToString(), "A" + RowCnt.ToString()]; series2.Values = xlWorkSheet.Range["C" + DataStart.ToString(), "C" + RowCnt.ToString()]; series3.Name = "Gross Profit"; series3.XValues = xlWorkSheet.Range["A" + DataStart.ToString(), "A" + RowCnt.ToString()]; series3.Values = xlWorkSheet.Range["D" + DataStart.ToString(), "D" + RowCnt.ToString()]; chartPage.ChartType = Excel.XlChartType.xlLineMarkers; Excel.Axis axis = chartPage.Axes(Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary) as Excel.Axis; series1.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowBubbleSizes); series2.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowBubbleSizes); series3.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowBubbleSizes); object format = Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml; xlWorkSheet.SaveAs(ReportsHelper.TempOutputPath, format); if (ExportToExcel) { format = Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7; xlWorkSheet.SaveAs(ExportPath, format); } xlApp.UserControl = false; //Once done close and quit Excel return("TRUE"); } else { return("EMPTY"); } } catch (Exception ex) { MessageBox.Show("{EXCEPTION in Revenues Comparison}" + ex.Message + ex.ToString()); return("ERROR " + ex.Message); } finally { //Once done close and quit Excel xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); } }