public ReportBuilder() { app = new Excel.Application(); appBooks = app.Workbooks; currentBook = appBooks.Add(Missing.Value); sheets = currentBook.Worksheets; currentSheet = (Excel._Worksheet)sheets.get_Item(1); range = currentSheet.get_Range("A1", Missing.Value); charts = currentSheet.ChartObjects(Type.Missing); chartObject = charts.Add(400, LastIndex, chartWidth, chartHeight); }
public void enviarExcel() { Excel.Application XlApp; Excel.Workbook XlWorkBook; Excel.Worksheet XlWorkSheet; object misValue = System.Reflection.Missing.Value; //cria planilia temporaria XlApp = new Excel.Application(); XlWorkBook = XlApp.Workbooks.Add(misValue); XlWorkSheet = (Excel.Worksheet)XlWorkBook.Worksheets.get_Item(1); XlWorkSheet.Cells[1, 1] = "Dados da avaliação"; XlWorkSheet.Cells[2, 1] = "Muito Ruim"; XlWorkSheet.Cells[3, 1] = "Ruim"; XlWorkSheet.Cells[4, 1] = "Médio"; XlWorkSheet.Cells[5, 1] = "Bom"; XlWorkSheet.Cells[6, 1] = "Muito Bom"; XlWorkSheet.Cells[7, 1] = "Ótimo"; XlWorkSheet.Cells[8, 1] = "QTD Total votos"; XlWorkSheet.Cells[9, 1] = "Média"; XlWorkSheet.Cells[1, 2] = "QTD Votos"; XlWorkSheet.Cells[2, 2] = mtruim.Text; XlWorkSheet.Cells[3, 2] = label1.Text; XlWorkSheet.Cells[4, 2] = label2.Text; XlWorkSheet.Cells[5, 2] = label3.Text; XlWorkSheet.Cells[6, 2] = mtbom.Text; XlWorkSheet.Cells[7, 2] = otimo.Text; XlWorkSheet.Cells[8, 2] = label8.Text; XlWorkSheet.Cells[9, 2] = label5.Text; XlWorkSheet.Cells[1, 3] = "Relatorio NTP"; XlWorkSheet.Cells[2, 3] = "Valor NTP"; XlWorkSheet.Cells[2, 4] = label16.Text; XlWorkSheet.Cells[6, 3] = "% de avaliação"; XlWorkSheet.Cells[7, 3] = "Baixo"; XlWorkSheet.Cells[8, 3] = "medio"; XlWorkSheet.Cells[9, 3] = "alto"; XlWorkSheet.Cells[7, 4] = label17.Text; XlWorkSheet.Cells[8, 4] = label18.Text; XlWorkSheet.Cells[9, 4] = label19.Text; XlWorkSheet.Cells[1, 5] = "Estrelas"; XlWorkSheet.Cells[2, 5] = label6.Text; XlWorkSheet.Cells[3, 5] = label7.Text; XlWorkSheet.Cells[1, 6] = label4.Text; XlWorkSheet.Cells[11, 11] = "Os graficos estao sobreposto, arraste para separalos."; //erro grafico 1 n aparece e grafico 2 sim como resolver. //grafico 2 Excel.Range chartRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)XlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(100, 180, 500, 300); Excel.Chart chartPage = myChart.Chart; chartRange = XlWorkSheet.get_Range("A2", "B7"); chartPage.SetSourceData(chartRange, misValue); chartPage.ChartType = Excel.XlChartType.xlColumnClustered; //fim do grafico 1 //grafico1 Excel.Range chartRange1; Excel.ChartObjects xlCharts1 = (Excel.ChartObjects)XlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart1 = (Excel.ChartObject)xlCharts1.Add(100, 180, 500, 300); Excel.Chart chartPage1 = myChart1.Chart; chartRange1 = XlWorkSheet.get_Range("C7", "d9"); chartPage1.SetSourceData(chartRange1, misValue); chartPage1.ChartType = Excel.XlChartType.xlColumnClustered; XlWorkBook.SaveAs(dia + "0" + mes + ano + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); XlWorkBook.Close(true, misValue, misValue); XlApp.Quit(); string folder1 = @"C:/Relatorios/"; if (!Directory.Exists(folder1)) { //Criamos um com o nome folder Directory.CreateDirectory(folder1); } MessageBox.Show(folder1 + dia + "0" + mes + ano + "xls" + " " + " caso não esteja la verifique em documentos"); }
/// <summary> /// Method sets the sheet with common results /// </summary> /// <param name="worksheet">Worksheet where the information will be saved</param> /// <param name="calculationTimes">Time results for each calculation types</param> /// <param name="results">results for each calculation types</param> private static void SetCommonResults(Excel.Worksheet worksheet, Dictionary <CalculationTypeName, double> calculationTimes, Dictionary <CalculationTypeName, List <DEVariable> > results) { worksheet.Name = "Common results"; int rowIndex = 1; int columnIndex = 1; worksheet.Cells[rowIndex, columnIndex] = "Calculation results"; rowIndex++; int i = 0; foreach (KeyValuePair <CalculationTypeName, List <DEVariable> > item in results) { int j = 0; List <DEVariable> result; if (i == 0) { result = item.Value; foreach (DEVariable variable in result) { worksheet.Cells[rowIndex + j + 1, columnIndex] = variable.Name; j++; } } worksheet.Cells[rowIndex, columnIndex + i + 1] = item.Key.ToString(); j = 0; result = item.Value; foreach (DEVariable variable in result) { worksheet.Cells[rowIndex + 1 + j, columnIndex + i + 1] = variable.Value; j++; } i++; } rowIndex += 3; worksheet.Cells[rowIndex, columnIndex] = "Time results"; rowIndex++; i = 0; foreach (KeyValuePair <CalculationTypeName, double> calculationTime in calculationTimes) { worksheet.Cells[rowIndex, columnIndex + i] = calculationTime.Key.ToString(); worksheet.Cells[rowIndex + 1, columnIndex + i] = calculationTime.Value; i++; } string leftTopTimeChart = GetExcelColumnName(1) + rowIndex.ToString(); string rightDownTimeChart = GetExcelColumnName(calculationTimes.Count) + (rowIndex + 1).ToString(); Excel.Range chartRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)worksheet.ChartObjects(Type.Missing); Excel.ChartObject mychart = xlCharts.Add(10, 80, 500, 450); Excel.Chart chartPage = mychart.Chart; chartRange = worksheet.get_Range(leftTopTimeChart, rightDownTimeChart); chartPage.SetSourceData(chartRange); chartPage.ChartType = Excel.XlChartType.xl3DColumnClustered; chartPage.SeriesCollection(1).Name = "Calculation times"; }
public static void WriteToExcelFile(string path, List <string[]> listasMin, List <string[]> listasMax, List <string[]> listasAvg, string[] parametros) { Excel.Application excelApplication = new Excel.Application(); excelApplication.Visible = false; Excel.Workbook excelWorkbook = excelApplication.Workbooks.Open(path); Excel.Worksheet excelWorksheet = excelWorkbook.ActiveSheet; //escrever dados para o excel int linha; int coluna = 600; int aux = 0; foreach (string[] lista in listasMin) { linha = 0; Excel.Chart myChart = null; Excel.ChartObjects charts = excelWorksheet.ChartObjects(); Excel.ChartObject chartObject = charts.Add(10, (300 * aux) + 10, 300, 300); //left, top, width, heigh myChart = chartObject.Chart; foreach (string dado in lista) { linha++; string dadoaux = dado.Replace(",", "."); excelWorksheet.Cells[linha, coluna].Value = dadoaux; } //set chart range Excel.Range c1 = excelWorksheet.Cells[1, coluna]; Excel.Range c2 = excelWorksheet.Cells[linha, coluna]; Excel.Range myrange = excelWorksheet.get_Range(c1, c2); myChart.SetSourceData(myrange); //chart properties using the named parameters and default parameters functionality in the .NET myChart.ChartType = Excel.XlChartType.xlLine; myChart.ChartWizard(Source: myrange, Title: parametros[aux], CategoryTitle: "Hours", ValueTitle: "Min Values"); coluna++; aux++; } aux = 0; coluna++; foreach (string[] lista in listasMax) { linha = 0; Excel.Chart myChart = null; Excel.ChartObjects charts = excelWorksheet.ChartObjects(); Excel.ChartObject chartObject = charts.Add(320, (300 * aux) + 10, 300, 300); //left, top, width, heigh myChart = chartObject.Chart; foreach (string dado in lista) { linha++; string dadoaux = dado.Replace(",", "."); excelWorksheet.Cells[linha, coluna].Value = dadoaux; } //set chart range Excel.Range c1 = excelWorksheet.Cells[1, coluna]; Excel.Range c2 = excelWorksheet.Cells[linha, coluna]; Excel.Range myrange = excelWorksheet.get_Range(c1, c2); myChart.SetSourceData(myrange); //chart properties using the named parameters and default parameters functionality in the .NET myChart.ChartType = Excel.XlChartType.xlLine; myChart.ChartWizard(Source: myrange, Title: parametros[aux], CategoryTitle: "Hours", ValueTitle: "Max Values"); coluna++; aux++; } aux = 0; coluna++; foreach (string[] lista in listasAvg) { linha = 0; Excel.Chart myChart = null; Excel.ChartObjects charts = excelWorksheet.ChartObjects(); Excel.ChartObject chartObject = charts.Add(640, (300 * aux) + 10, 300, 300); //left, top, width, heigh myChart = chartObject.Chart; foreach (string dado in lista) { linha++; string dadoaux = dado.Replace(",", "."); excelWorksheet.Cells[linha, coluna].Value = dadoaux; } //set chart range Excel.Range c1 = excelWorksheet.Cells[1, coluna]; Excel.Range c2 = excelWorksheet.Cells[linha, coluna]; Excel.Range myrange = excelWorksheet.get_Range(c1, c2); myChart.SetSourceData(myrange); //chart properties using the named parameters and default parameters functionality in the .NET myChart.ChartType = Excel.XlChartType.xlLine; myChart.ChartWizard(Source: myrange, Title: parametros[aux], CategoryTitle: "Hours", ValueTitle: "Average Values"); coluna++; aux++; } excelWorkbook.Save(); excelWorkbook.Close(); excelApplication.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook); excelWorkbook = null; //Don’t forget to free the memory used by excel objects //... GC.Collect(); }
// データテーブルをエクセルへエクスポート(XR管理図・ヒストグラム付き) public void ExportToExcelWithXrChart(DataTable dt) { if (dt.Rows.Count == 0) { return; } Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); // column headings for (int i = 0; i < dt.Columns.Count; i++) { xlWorkSheet.Cells[1, (i + 1)] = dt.Columns[i].ColumnName; } // rows for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { xlWorkSheet.Cells[(i + 2), (j + 1)] = dt.Rows[i][j]; } } int row = xlWorkSheet.UsedRange.Rows.Count; string address1 = "B1:B" + row.ToString() + ",K1:K" + row.ToString() + ",M1:N" + row.ToString(); string address2 = "B1:B" + row.ToString() + ",L1:L" + row.ToString(); string address3 = "AB41:AC53"; string address4 = "F1:J" + row.ToString(); // チャート1(X-R管理図1) Excel.Range chartRange1; Excel.ChartObjects xlCharts1 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart1 = (Excel.ChartObject)xlCharts1.Add(800, 10, 600, 250); Excel.Chart chartPage1 = myChart1.Chart; chartRange1 = xlWorkSheet.get_Range(address1); chartPage1.SetSourceData(chartRange1, misValue); chartPage1.ChartType = Excel.XlChartType.xlLine; chartPage1.HasLegend = false; chartPage1.HasTitle = true; chartPage1.ChartTitle.Text = "X " + dt.Rows[0]["inspect"].ToString() + " " + dt.Rows[0]["line"].ToString(); Excel.Axis xAxis1 = (Excel.Axis)chartPage1.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary); xAxis1.CategoryType = Excel.XlCategoryType.xlCategoryScale; Excel.SeriesCollection SeriesCollection1 = (Excel.SeriesCollection)myChart1.Chart.SeriesCollection(misValue); Excel.Series s2 = SeriesCollection1.Item(2); s2.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbCoral; Excel.Series s3 = SeriesCollection1.Item(3); s3.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbCoral; // チャート2(X-R管理図2) Excel.Range chartRange2; Excel.ChartObjects xlCharts2 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart2 = (Excel.ChartObject)xlCharts1.Add(800, 280, 600, 250); Excel.Chart chartPage2 = myChart2.Chart; chartRange2 = xlWorkSheet.get_Range(address2); chartPage2.SetSourceData(chartRange2, misValue); chartPage2.ChartType = Excel.XlChartType.xlLine; chartPage2.HasLegend = false; chartPage2.HasTitle = true; chartPage2.ChartTitle.Text = "R " + dt.Rows[0]["inspect"].ToString() + " " + dt.Rows[0]["line"].ToString(); Excel.Axis xAxis2 = (Excel.Axis)chartPage2.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary); xAxis2.CategoryType = Excel.XlCategoryType.xlCategoryScale; // チャート3(ヒストグラム) Excel.Range chartRange3; Excel.ChartObjects xlCharts3 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart3 = (Excel.ChartObject)xlCharts1.Add(800, 550, 350, 250); Excel.Chart chartPage3 = myChart3.Chart; string[,] formulas = new string[13, 3]; string[] formula1 = new string[] { "BIN", "=MIN(" + address4 + ")", "=AA42+(AA$53-AA$42)/10", "=AA43+(AA$53-AA$42)/10", "=AA44+(AA$53-AA$42)/10", "=AA45+(AA$53-AA$42)/10", "=AA46+(AA$53-AA$42)/10", "=AA47+(AA$53-AA$42)/10", "=AA48+(AA$53-AA$42)/10", "=AA49+(AA$53-AA$42)/10", "=AA50+(AA$53-AA$42)/10", "=AA51+(AA$53-AA$42)/10", "=MAX(" + address4 + ")", }; string[] formula2 = new string[] { @"LABEL", @"=TEXT(AA42,""0.0"")", @"=TEXT(AA42,""0.0"")&"" - ""&TEXT(AA43,""0.0"")", @"=TEXT(AA43,""0.0"")&"" - ""&TEXT(AA44,""0.0"")", @"=TEXT(AA44,""0.0"")&"" - ""&TEXT(AA45,""0.0"")", @"=TEXT(AA45,""0.0"")&"" - ""&TEXT(AA46,""0.0"")", @"=TEXT(AA46,""0.0"")&"" - ""&TEXT(AA47,""0.0"")", @"=TEXT(AA47,""0.0"")&"" - ""&TEXT(AA48,""0.0"")", @"=TEXT(AA48,""0.0"")&"" - ""&TEXT(AA49,""0.0"")", @"=TEXT(AA49,""0.0"")&"" - ""&TEXT(AA50,""0.0"")", @"=TEXT(AA50,""0.0"")&"" - ""&TEXT(AA51,""0.0"")", @"=TEXT(AA51,""0.0"")&"" - ""&TEXT(AA52,""0.0"")", @"=TEXT(AA53,""0.0"")" }; string[] formula3 = new string[] { @"FREQUENCY", @"=COUNTIF(" + address4 + @",""<=""&AA42)", @"=COUNTIF(" + address4 + @","">""&AA42)-COUNTIF(" + address4 + @","">""&AA43)", @"=COUNTIF(" + address4 + @","">""&AA43)-COUNTIF(" + address4 + @","">""&AA44)", @"=COUNTIF(" + address4 + @","">""&AA44)-COUNTIF(" + address4 + @","">""&AA45)", @"=COUNTIF(" + address4 + @","">""&AA45)-COUNTIF(" + address4 + @","">""&AA46)", @"=COUNTIF(" + address4 + @","">""&AA46)-COUNTIF(" + address4 + @","">""&AA47)", @"=COUNTIF(" + address4 + @","">""&AA47)-COUNTIF(" + address4 + @","">""&AA48)", @"=COUNTIF(" + address4 + @","">""&AA48)-COUNTIF(" + address4 + @","">""&AA49)", @"=COUNTIF(" + address4 + @","">""&AA49)-COUNTIF(" + address4 + @","">""&AA50)", @"=COUNTIF(" + address4 + @","">""&AA50)-COUNTIF(" + address4 + @","">""&AA51)", @"=COUNTIF(" + address4 + @","">""&AA51)-COUNTIF(" + address4 + @","">=""&AA52)", @"=COUNTIF(" + address4 + @","">=""&AA53)" }; for (int i = 0; i < 13; i++) { xlWorkSheet.Cells[41 + i, 27].Formula = formula1[i]; xlWorkSheet.Cells[41 + i, 28].Formula = formula2[i]; xlWorkSheet.Cells[41 + i, 29].Formula = formula3[i]; } chartRange3 = xlWorkSheet.get_Range(address3); chartPage3.SetSourceData(chartRange3, misValue); chartPage3.ChartType = Excel.XlChartType.xlColumnClustered; chartPage3.HasLegend = false; chartPage3.HasTitle = true; chartPage3.ChartTitle.Text = "Frequency " + dt.Rows[0]["inspect"].ToString() + " " + dt.Rows[0]["line"].ToString(); Excel.ChartGroup ChartGroup1 = (Excel.ChartGroup)myChart3.Chart.ChartGroups(1); ChartGroup1.GapWidth = 0; xlApp.Visible = true; }
static void Main(string[] args) { Console.WriteLine("Запуск Microsoft Excel..."); //настраиваем запуск Excel и книгу и листы. Excel.Application xlApp = new Excel.Application(); if (xlApp == null) { Console.WriteLine("Excel is not properly installed!!"); return; } xlApp.Visible = true; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); //предыдущая таблица создавалась для 11 варианта string[,] text = new string[, ] { { "Привлеченные средства коммерческого банка", "Сумма млн.грн." }, { "Депозиты государственных предприятий", "2000" }, { "Депозиты с/ х предприятий", "850" }, { "Депозиты СП", "700" }, { "Вклады населения", "4000" }, { "Депозиты внебюджетных фондов", "1000" }, { "Депозиты АО и ТОО", "1200" }, { "Остатки на расчетных и текущих счетах клиентов", "8000" }, { "Депозиты юридических лиц в валюте(в грн.)", "5000" } }; int rows = text.GetLength(0), columns = text.GetLength(1); //Вставляем значения в ячейки for (int i = 1; i <= rows; i++) { for (int j = 1; j <= columns; j++) { xlWorkSheet.Cells[i, j] = text[i - 1, j - 1]; if (j == 1) { ((Excel.Range)xlWorkSheet.Columns[1]).ColumnWidth = 45;//устанавливаем ширину для первого столбца } else { ((Excel.Range)xlWorkSheet.Columns[j]).ColumnWidth = 15;//устанавливаем ширину для остальных столбцов } } } string[] range = { "B2:B9" }; //диапазоны для расчетов по столбцам"B2:B5","C2:C5","D2:D5","E2:E5"// double[] sumResult = new double[range.Length]; //сумма для каждого диапазона значений double[] avgResult = new double[range.Length]; //среднее для каждого диапазона значений Excel.Range xlRng; //считаем среднее и сумму всех строк for (int i = 0; i < range.Length; i++) { xlRng = xlWorkSheet.Range[range[i]]; //получаем диапазон значений sumResult[i] = xlApp.WorksheetFunction.Sum(xlRng); //считаем сумму avgResult[i] = xlApp.WorksheetFunction.Average(xlRng); //считаем среднее } xlWorkSheet.Cells[10, 1] = "Общая сумма млн. грн.:"; //"Сумма в каждом квартале: "; xlWorkSheet.Cells[11, 1] = "Среднее млн. грн."; //"Среднее в каждом квартале: "; //выводим итоги for (int j = 0; j < range.Length; j++) { xlWorkSheet.Cells[10, j + 2] = sumResult[j]; xlWorkSheet.Cells[11, j + 2] = avgResult[j]; } Excel.Range chartRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(330, 0, 540, 360); Excel.Chart chartPage = myChart.Chart; chartRange = xlWorkSheet.get_Range("A1", "B9"); chartPage.SetSourceData(chartRange, misValue); chartPage.ChartType = Excel.XlChartType.xlColumnClustered; xlWorkBook.Close(); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); Console.WriteLine("Таблица успешно создана!"); Console.ReadKey(); }
public void ExportExcel(ShoppingCartLogic ccp) { Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp == null) { Console.WriteLine("EXCEL could not be started. Check that your office installation and project references are correct."); return; } xlApp.Visible = true; var wb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet); var ws = (Worksheet)wb.Worksheets[1]; if (ws == null) { Console.WriteLine("Worksheet could not be created. Check that your office installation and project references are correct."); } var lst = new Dictionary <string, Cell>(); /* It is much better to divide a method into smaller "centered" methods.. It is a lot more readable, understandable, maintaiable, debuggable and scalable. * Consider: Each block of code which does some operation can be extracted into a method. */ #region Build Table foreach (var chain in ccp.Chains.Values) { ws.Cells[_row, _col] = chain.ChainName; foreach (var item in chain.Items.Values.OrderBy(item => item.ItemName)) { ++_row; ws.Cells[_row, 1] = item.ItemName; ws.Cells[_row, _col] = item.Price; if (lst.ContainsKey(item.ItemName)) { if (lst[item.ItemName].price > double.Parse(item.Price)) { lst[item.ItemName].price = double.Parse(item.Price); lst[item.ItemName].row = _row; lst[item.ItemName].col = _col; } } else { lst.Add(item.ItemName, new Cell(_row, _col, double.Parse(item.Price))); } } _row = 1; ++_col; } #endregion //mark lowest price _col = 2; foreach (var cell in lst) { ws.Cells[cell.Value.row, cell.Value.col].Interior.Color = XlRgbColor.rgbLightGreen; } #region Build Histogram Microsoft.Office.Interop.Excel.Range chartRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)ws.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(LEFT, TOP, WIDTH * (ccp.Chains.Values.First().Items.Count + 1), HEIGHT); Excel.Chart chartPage = myChart.Chart; chartRange = ws.get_Range("A1", "D" + (ccp.Chains.Values.First().Items.Count + 1)); chartPage.SetSourceData(chartRange, Type.Missing); chartPage.ChartType = Excel.XlChartType.xlColumnClustered; #endregion }
public void CreateNewChartInExcel() { // Declare a variable for the Excel ApplicationClass instance. Microsoft.Office.Interop.Excel.Application excelApplication = new xlNS.Application();//new Microsoft.Office.Interop.Excel.ApplicationClass(); // Declare variables for the Workbooks.Open method parameters. string paramWorkbookPath = System.Windows.Forms.Application.StartupPath + @"\ChartData.xlsx"; object paramMissing = Type.Missing; // Declare variables for the Chart.ChartWizard method. object paramChartFormat = 1; object paramCategoryLabels = 0; object paramSeriesLabels = 0; bool paramHasLegend = true; object paramTitle = "Sales by Quarter"; object paramCategoryTitle = "Fiscal Quarter"; object paramValueTitle = "Billions"; try { // Create an instance of the Excel ApplicationClass object. // excelApplication = new Microsoft.Office.Interop.Excel.ApplicationClass(); // Create a new workbook with 1 sheet in it. xlNS.Workbook newWorkbook = excelApplication.Workbooks.Add(xlNS.XlWBATemplate.xlWBATWorksheet); // Change the name of the sheet. xlNS.Worksheet targetSheet = (xlNS.Worksheet)(newWorkbook.Worksheets[1]); targetSheet.Name = "Quarterly Sales"; // Insert some data for the chart into the sheet. // A B C D E // 1 Q1 Q2 Q3 Q4 // 2 N. America 1.5 2 1.5 2.5 // 3 S. America 2 1.75 2 2 // 4 Europe 2.25 2 2.5 2 // 5 Asia 2.5 2.5 2 2.75 SetCellValue(targetSheet, "A2", "N. America"); SetCellValue(targetSheet, "A3", "S. America"); SetCellValue(targetSheet, "A4", "Europe"); SetCellValue(targetSheet, "A5", "Asia"); SetCellValue(targetSheet, "B1", "Q1"); SetCellValue(targetSheet, "B2", 1.5); SetCellValue(targetSheet, "B3", 2); SetCellValue(targetSheet, "B4", 2.25); SetCellValue(targetSheet, "B5", 2.5); SetCellValue(targetSheet, "C1", "Q2"); SetCellValue(targetSheet, "C2", 2); SetCellValue(targetSheet, "C3", 1.75); SetCellValue(targetSheet, "C4", 2); SetCellValue(targetSheet, "C5", 2.5); SetCellValue(targetSheet, "D1", "Q3"); SetCellValue(targetSheet, "D2", 1.5); SetCellValue(targetSheet, "D3", 2); SetCellValue(targetSheet, "D4", 2.5); SetCellValue(targetSheet, "D5", 2); SetCellValue(targetSheet, "E1", "Q4"); SetCellValue(targetSheet, "E2", 2.5); SetCellValue(targetSheet, "E3", 2); SetCellValue(targetSheet, "E4", 2); SetCellValue(targetSheet, "E5", 2.75); // Get the range holding the chart data. xlNS.Range dataRange = targetSheet.get_Range("A1", "E5"); // Get the ChartObjects collection for the sheet. xlNS.ChartObjects chartObjects = (xlNS.ChartObjects)(targetSheet.ChartObjects(paramMissing)); // Add a Chart to the collection. xlNS.ChartObject newChartObject = chartObjects.Add(0, 100, 600, 300); newChartObject.Name = "Sales Chart"; // Create a new chart of the data. newChartObject.Chart.ChartWizard(dataRange, xlNS.XlChartType.xl3DColumn, paramChartFormat, xlNS.XlRowCol.xlRows, paramCategoryLabels, paramSeriesLabels, paramHasLegend, paramTitle, paramCategoryTitle, paramValueTitle, paramMissing); // Save the workbook. newWorkbook.SaveAs(paramWorkbookPath, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, xlNS.XlSaveAsAccessMode.xlNoChange, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing); } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { if (excelApplication != null) { // Close Excel. excelApplication.Quit(); } } }
static void Main(string[] args) { RemoteWebDriver web = NewMethod2(); web.Navigate().GoToUrl("http://google.pl/"); ExcelApp excel = new ExcelApp(); excel.Visible = true; Workbook wb = excel.Workbooks.Open("C:/Users/Bartosz/Desktop/ING.xlsx"); Worksheet ws = wb.ActiveSheet; int wiersz = 1; int kolumna = 1; int valueOFcities = 0; while (excel.Cells[wiersz, kolumna].Value != null) { valueOFcities++; wiersz++; } string[] cities = new string[valueOFcities]; int[] temp = new int[valueOFcities]; for (int i = 0; i < valueOFcities; i++) { web.FindElementById("lst-ib").Clear(); string miasto = excel.Cells[i + 1, 1].Value; web.FindElementById("lst-ib").SendKeys("pogoda " + miasto + Keys.Enter); cities[i] = miasto; //temperatura string temperatura = web.FindElementById("wob_tm").Text; temp[i] = int.Parse(temperatura); excel.Cells[i + 1, 2].Value = temperatura; //data string data; DateTime dat = DateTime.Now; data = dat.ToString(); excel.Cells[i + 1, 4].Value = data; // pogoda string pogoda; pogoda = web.FindElementById("wob_dc").Text; excel.Cells[i + 1, 3].Value = pogoda; } int maxtemp = temp.Max(); int mintemp = temp.Min(); // wybor miast for (int i = 0; i < valueOFcities; i++) { if (temp[i] == maxtemp) { string cityofmaxtemp = cities[i]; excel.Cells[4, 8].Value = String.Format("najgorętszym miastem jest: " + cityofmaxtemp); } if (temp[i] == mintemp) { string cityofmintemp = cities[i]; excel.Cells[5, 8].Value = String.Format("najchłodniejszym miastem jest: " + cityofmintemp); } } //rysowanie wykresu object misValue = System.Reflection.Missing.Value; Excel.Range chartRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)ws.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(200, 200, 200, 200); Excel.Chart chartPage = myChart.Chart; chartRange = ws.get_Range("A1", "B13"); chartPage.SetSourceData(chartRange, misValue); chartPage.ChartType = Excel.XlChartType.xlColumnClustered; releaseObject(ws); releaseObject(wb); releaseObject(excel); web.Quit(); //wb.Save(); //excel.Quit(); }
private void diagram_Click(object sender, EventArgs e) { KURS.allDataSet kursds = new KURS.allDataSet(); KURS.allDataSetTableAdapters.ZakazTableAdapter zta = new KURS.allDataSetTableAdapters.ZakazTableAdapter(); zta.Fill(kursds.Zakaz); // object misValue = System.Reflection.Missing.Value; // Excel.Application excelapp = new Excel.Application(); excelapp.Visible = true; excelapp.SheetsInNewWorkbook = 1; excelapp.Workbooks.Add(misValue); Excel.Workbooks excelappworkbooks = excelapp.Workbooks; Excel.Workbook excelappworkbook = excelappworkbooks[1]; Excel.Sheets excelsheets = excelappworkbook.Worksheets; Excel.Worksheet excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1); //excelworksheet.Activate(); // var c = from p in kursds.Zakaz.AsEnumerable() where p.Data.Month == dateTimePicker1.Value.Month select p; int count = 0; excelapp.Cells[1][1] = "date"; excelapp.Cells[2][1] = "summ"; foreach (var p in c) { count++; } // int[] a = new int[count]; double[] b = new double[count]; int i = 0; double sum = 0; while (i < count) { foreach (var p in c) { a[i] = p.Data.Day; if (i == 0) { sum += p.Summ; } if (i > 0) { if (a[i] == a[i - 1]) { sum += p.Summ; } else { b[i - 1] = sum; sum = p.Summ; i++; } } else { i++; } } b[i - 1] = sum; count = i; i = 0; break; } Dictionary <int, double> dic = new Dictionary <int, double>(); while (i < count) { excelapp.Cells[1][i + 2] = a[i]; excelapp.Cells[2][i + 2] = b[i]; dic.Add(a[i], b[i]); i++; } // string str = (count + 1).ToString(); // //Excel.Window excelWindow = null; Excel.Range excelcells = null; //Определяем диаграммы как объекты Excel.ChartObjects Excel.ChartObjects chartsobjrcts = (Excel.ChartObjects)excelworksheet.ChartObjects(Type.Missing); //Добавляем одну диаграмму в Excel.ChartObjects - диаграмма пока не выбрана, но место для нее выделено в методе Add Excel.ChartObject chartsobjrct = chartsobjrcts.Add(100, 40, 300, 300); Excel.Chart chartPage = chartsobjrct.Chart; excelcells = excelworksheet.get_Range("B1", "B" + str); chartPage.SetSourceData(excelcells, misValue); chartPage.ChartType = Excel.XlChartType.xlColumnClustered; Excel.Axis axis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary); axis.HasTitle = true; axis.AxisTitle.Text = "Data"; // Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chartPage.SeriesCollection(Type.Missing); Excel.Series series = seriesCollection.Item(1); series.XValues = a; // excelappworkbook.SaveAs(@"D:\siple.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); excelappworkbook.Close(true, misValue, misValue); excelapp.Quit(); this.chart1.Titles.Add("Отчет за месяц"); this.chart1.Series[0].Points.Clear(); Series ser = this.chart1.Series[0]; ser.LegendText = "Summ"; foreach (KeyValuePair <int, double> pair in dic) { ser.Points.AddXY(pair.Key, pair.Value); } // }
private PdeExports GetExportedData(Excel.Workbook wExcel) { if (wExcel == null) { return(null); } InternalBookmark ibm = GetPdeInternalBookmark(wExcel); PdeExports export = new PdeExports(); DomainExportItem expDomain = new DomainExportItem(); expDomain.DomainName = "DomainName"; #region get tags foreach (Excel.Name name in wExcel.Names) { ExportItem expItem = new ExportItem(); expItem.IsUsed = false; // default is not used bool isAdd = false; string strName = name.Name; if (string.IsNullOrWhiteSpace(strName)) { continue; } // update field if (strName.EndsWith(BaseProntoMarkup.KeySelect)) { expItem.MapType = MapType.SingleCell; expItem.DataType = GetPdeDataType(strName, ibm); isAdd = true; } // update table if (strName.EndsWith(BaseProntoMarkup.KeyTable)) { expItem.MapType = MapType.Table; Excel.Range range = name.RefersToRange; Excel.ListObject listColumn = range.ListObject; expItem.Columns = new System.Collections.Generic.List <ColumnExportItem>(); // update columns foreach (Excel.ListColumn lstCol in listColumn.ListColumns) { ColumnExportItem expColumn = new ColumnExportItem(); expColumn.ColumnName = lstCol.Name; expColumn.ExcelName = lstCol.Range.Name.Name; expColumn.TreeNodeName = expColumn.ExcelName; expColumn.ExcelAddress = lstCol.Range.AddressLocal; expColumn.ParentName = strName; expColumn.DomainName = expDomain.DomainName; expColumn.IsUsed = false; expColumn.DataType = GetPdeDataType(expColumn.ExcelName, ibm); expItem.Columns.Add(expColumn); } isAdd = true; } if (isAdd) { expItem.ExcelSheetName = name.RefersToRange.Parent.Name; expItem.ExcelName = strName; expItem.ExcelAddress = name.RefersToRange.AddressLocal; expItem.TreeNodeName = expItem.ExcelName; expItem.ParentName = expDomain.DomainName; expDomain.Items.Add(expItem); } } #endregion #region get charts foreach (Excel.Worksheet sheet in wExcel.Sheets) { Excel.ChartObjects charts = sheet.ChartObjects(Type.Missing); if (charts != null) { foreach (Excel.ChartObject chart in charts) { ExportItem exportItem = new ExportItem(); exportItem.MapType = MapType.Chart; exportItem.ExcelName = chart.Name; exportItem.TreeNodeName = exportItem.ExcelName; exportItem.ParentName = expDomain.DomainName; exportItem.ExcelSheetName = sheet.Name; exportItem.Chart = new ExportItemChart(); exportItem.Chart.Name = chart.Name; string filePath = AssetManager.FileAdapter.GenRandomFilePath(ChartExtension); chart.Chart.Export(filePath, "JPG"); exportItem.Chart.Content = ProntoDoc.Framework.Utils.FileHelper.FileToBase64(filePath); System.IO.File.Delete(filePath); expDomain.Items.Add(exportItem); } } } #endregion export.Items = new System.Collections.Generic.List <DomainExportItem>(); export.Items.Add(expDomain); return(export); }
public void AddCharts(string reportFile) { var excel = new Excel.Application() { DisplayAlerts = false }; excel.Workbooks.Open(reportFile); Excel._Worksheet workSheet = (Excel.Worksheet)excel.ActiveSheet; var sheetNumber = 1; var chartPositionNumber = 2; foreach (Excel.Worksheet sheet in excel.Worksheets) { if (sheet.Name != "Charts") { // Set the datasheet for the source of the data Excel.Worksheet dataSheet = excel.Worksheets[sheetNumber]; // Open the chart sheet to save the charts workSheet = excel.ActiveWorkbook.Sheets["Charts"]; workSheet.Select(); // Get column & row length var colomn = dataSheet.UsedRange.Columns.Count; var rows = dataSheet.UsedRange.Rows.Count - 1; // Chart settings and stuff Excel.ChartObjects xlCharts = (Excel.ChartObjects)workSheet.ChartObjects(Type.Missing); Excel.ChartObject runChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250); Excel.Chart runChartPage = runChart.Chart; runChartPage.ChartType = Excel.XlChartType.xlLine; // set ChartStyle based on Office version var chartStyle = 301; if (Type.GetTypeFromProgID("Excel.Application.14") != null) { chartStyle = 2; } runChartPage.HasTitle = true; runChartPage.HasLegend = true; runChartPage.ChartTitle.Text = sheet.Name; runChartPage.ChartStyle = chartStyle; // Position of chart var runChartPosition = "B" + chartPositionNumber; Excel.Range runChartPlacementRange = workSheet.get_Range(runChartPosition, runChartPosition); runChart.Top = runChartPlacementRange.Top; runChart.Left = runChartPlacementRange.Left; chartPositionNumber = chartPositionNumber + 21; // Size of Chart runChart.Width = 500; runChart.Height = 250; Excel.SeriesCollection runSeriesCollection = runChartPage.SeriesCollection(); // Create run line chart for (int i = 2; i <= (colomn); i++) { Excel.Series runSeries = runSeriesCollection.NewSeries(); runSeries.Name = dataSheet.Cells[1, i].Value; // set correct range for chart data var ia = i; // Time range var xValuesBegin = ParseColumnName(1) + "2"; var xValuesEnd = ParseColumnName(1) + (rows.ToString()); var valuesBegin = ParseColumnName(ia) + (2).ToString(); var valuesEnd = ParseColumnName(ia) + (rows + 1).ToString(); runSeries.XValues = dataSheet.get_Range(xValuesBegin, xValuesEnd); runSeries.Values = dataSheet.get_Range(valuesBegin, valuesEnd); } } sheetNumber++; } var error = new ErrorHandler(); try { // Save excel sheet workSheet.SaveAs(reportFile); } catch (Exception) { excel.Quit(); error.Exit(93); } // Close sheet excel.Quit(); }
private void Form1_Load(object sender, EventArgs e) { pptNS.ApplicationClass powerpointApplication = null; pptNS.Presentation pptPresentation = null; pptNS.Slide pptSlide = null; pptNS.ShapeRange shapeRange = null; pptNS.Shape oTxtShape = null; pptNS.Shape oShape = null; pptNS.Shape oPicShape = null; xlNS.ApplicationClass excelApplication = null; xlNS.Workbook excelWorkBook = null; xlNS.Worksheet targetSheet = null; xlNS.ChartObjects chartObjects = null; xlNS.ChartObject existingChartObject = null; xlNS.Range destRange = null; string paramPresentationPath = @"D:\test\Test Slide.pptx"; string paramWorkbookPath = @"D:\test\NPS.xlsx"; object paramMissing = Type.Missing; try { // Create an instance of PowerPoint. powerpointApplication = new pptNS.ApplicationClass(); // Create an instance Excel. excelApplication = new xlNS.ApplicationClass(); // Open the Excel workbook containing the worksheet with the chart // data. excelWorkBook = excelApplication.Workbooks.Open(paramWorkbookPath, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing); // Get the worksheet that contains the chart. targetSheet = (xlNS.Worksheet)(excelWorkBook.Worksheets["Spain"]); // Get the ChartObjects collection for the sheet. chartObjects = (xlNS.ChartObjects)(targetSheet.ChartObjects(paramMissing)); // Create a PowerPoint presentation. pptPresentation = powerpointApplication.Presentations.Add( Microsoft.Office.Core.MsoTriState.msoTrue); // Add a blank slide to the presentation. pptSlide = pptPresentation.Slides.Add(1, pptNS.PpSlideLayout.ppLayoutBlank); // capture range //var writeRange = targetSheet.Range["A1:B15"]; destRange = targetSheet.get_Range("A1:B21"); oTxtShape = pptSlide.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, Left: 70, Top: 30, Width: 550, Height: 340); oTxtShape.TextFrame.TextRange.Text = "GB NPS 03/01/2017 to 26/01/2017"; oTxtShape.TextEffect.FontName = "Arial"; oTxtShape.TextEffect.FontSize = 32; oTxtShape.TextEffect.FontBold = Microsoft.Office.Core.MsoTriState.msoTrue; oTxtShape.TextEffect.Alignment = Microsoft.Office.Core.MsoTextEffectAlignment.msoTextEffectAlignmentCentered; System.Array myvalues = (System.Array)destRange.Cells.Value; List <Tuple <string, string> > cellData = GetData(myvalues); int iRows = cellData.Count + 1; int iColumns = 2; int row = 2; oShape = pptSlide.Shapes.AddTable(iRows, iColumns, 500, 110, 160, 120); oShape.Table.ApplyStyle("{5940675A-B579-460E-94D1-54222C63F5DA}"); //oShape.Table.ApplyStyle("{0660B408-B3CF-4A94-85FC-2B1E0A45F4A2}"); // table style guide https://msdn.microsoft.com/en-us/library/office/hh273476(v=office.14).aspx oShape.Table.Cell(1, 1).Merge(oShape.Table.Cell(1, 2)); //oShape.Table.Cell(1, 1).Shape.TextFrame.TextRange.Text = "Spain Data 01/01/2017 to 20/01/2017"; //oShape.Table.Cell(1, 1).Shape.TextFrame.TextRange.Font.Name = "Verdana"; //oShape.Table.Cell(1, 1).Shape.TextFrame.TextRange.Font.Size = 8; foreach (Tuple <string, string> item in cellData) { string strdate = item.Item1; string strValue = item.Item2; oShape.Table.Cell(row, 1).Shape.TextFrame.TextRange.Text = strdate; oShape.Table.Cell(row, 1).Shape.TextFrame.TextRange.Font.Name = "Arial"; oShape.Table.Cell(row, 1).Shape.TextFrame.TextRange.Font.Size = 10; oShape.Table.Cell(row, 2).Shape.TextFrame.TextRange.Text = (strValue.StartsWith("0") ? "0%" : (strValue + "0%")); oShape.Table.Cell(row, 2).Shape.TextFrame.TextRange.Font.Name = "Arial"; oShape.Table.Cell(row, 2).Shape.TextFrame.TextRange.Font.Size = 10; //if (row == 1) //{ // oShape.Table.Cell(row, 1).Shape.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(208, 208, 208).ToArgb(); // oShape.Table.Cell(row, 1).Shape.Fill.Visible = Microsoft.Office.Core.MsoTriState.msoTrue; // oShape.Table.Cell(row, 2).Shape.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(208, 208, 208).ToArgb(); // oShape.Table.Cell(row, 2).Shape.Fill.Visible = Microsoft.Office.Core.MsoTriState.msoTrue; //} row++; } oShape.Top = 100; oShape.Left = 30; oPicShape = pptSlide.Shapes.AddPicture(@"D:\test\chart.png", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, 200, 100, 500, 300); //copy range //destRange.Copy(); // Paste the chart into the PowerPoint presentation. //shapeRange = pptSlide.Shapes.Paste(); //var table = pptSlide.Shapes.AddTable(); // Position the chart on the slide. //shapeRange.Left = 60; //shapeRange.Top = 100; // Get or capture the chart to copy. //existingChartObject = (xlNS.ChartObject)(chartObjects.Item(1)); // Copy the chart from the Excel worksheet to the clipboard. //existingChartObject.Copy(); // Paste the chart into the PowerPoint presentation. //shapeRange = pptSlide.Shapes.Paste(); //Position the chart on the slide. //shapeRange.Left = 90; //shapeRange.Top = 100; // Save the presentation. pptPresentation.SaveAs(paramPresentationPath, pptNS.PpSaveAsFileType.ppSaveAsOpenXMLPresentation, Microsoft.Office.Core.MsoTriState.msoTrue); pptPresentation.SaveCopyAs(String.Format(@"D:\test\Export", "video_of_presentation"), pptNS.PpSaveAsFileType.ppSaveAsEMF, Microsoft.Office.Core.MsoTriState.msoCTrue); } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { // Release the PowerPoint slide object. shapeRange = null; pptSlide = null; // Close and release the Presentation object. if (pptPresentation != null) { pptPresentation.Close(); pptPresentation = null; } // Quit PowerPoint and release the ApplicationClass object. if (powerpointApplication != null) { powerpointApplication.Quit(); powerpointApplication = null; } // Release the Excel objects. targetSheet = null; chartObjects = null; existingChartObject = null; // Close and release the Excel Workbook object. if (excelWorkBook != null) { excelWorkBook.Close(false, paramMissing, paramMissing); excelWorkBook = null; } // Quit Excel and release the ApplicationClass object. if (excelApplication != null) { excelApplication.Quit(); excelApplication = null; } GC.Collect(); GC.WaitForPendingFinalizers(); } }
private void btnExcelAktar_Click(object sender, EventArgs e) { //MessageBox.Show(grupSayisi.ToString()); if (dataGridView1.Rows.Count >= 2) { Excel.Application excelDosya = new Excel.Application(); object Missing = Type.Missing; Workbook calismakitabi = excelDosya.Workbooks.Add(Missing); Worksheet sheet1 = (Worksheet)calismakitabi.Sheets[1]; sheet1.Name = "Bireysel Notlar"; Worksheet sheet2 = (Worksheet)calismakitabi.Sheets.Add(Missing, Missing, 1, Missing) as Excel.Worksheet;; sheet2.Name = "Soru Analizi"; Worksheet sheet3 = (Worksheet)calismakitabi.Sheets.Add(Missing, Missing, 1, Missing) as Excel.Worksheet;; sheet3.Name = "Not Aralıkları"; Excel.Range formatRange; formatRange = sheet1.get_Range("h:h"); formatRange.NumberFormat = "@"; Excel.Range formatRange2; formatRange2 = sheet3.get_Range("a:a"); formatRange2.NumberFormat = "@"; //sheet1.Cells[8, 2].NumberFormat = "General"; int sutun = 1; int satir = 1; for (int j = 0; j < dataGridView1.Columns.Count; j++) { Range myrange = (Range)sheet1.Cells[satir, sutun + j]; myrange.Value2 = dataGridView1.Columns[j].HeaderText; } satir++; for (int i = 0; i < dataGridView1.Rows.Count; i++) { for (int j = 0; j < dataGridView1.Columns.Count; j++) { Range myrange = (Range)sheet1.Cells[satir + i, sutun + j]; myrange.Value2 = dataGridView1[j, i].ValueType == null ? "" : dataGridView1[j, i].Value; //myrange.Select(); } } //soru analiz if (grupSayisi == 1) { int analizSatir = 1, analizSutun = 1; for (int i = 0; i < dtSoruAnalizA.Rows.Count; i++) { for (int j = 0; j < dtSoruAnalizA.Columns.Count; j++) { Range myrange2 = (Range)sheet2.Cells[analizSatir + i, analizSutun + j]; myrange2.Value2 = dtSoruAnalizA.Rows[i][j] == null ? "" : dtSoruAnalizA.Rows[i][j]; //myrange2.Select(); } } } if (grupSayisi == 2) { //MessageBox.Show("Else çalışıyor"); int analizSatirA = 1, analizSutunA = 1, analizSatirB = 1, analizSutunB = 4; for (int i = 0; i < dtSoruAnalizA.Rows.Count; i++) { for (int j = 0; j < dtSoruAnalizA.Columns.Count; j++) { Range myrange2 = (Range)sheet2.Cells[analizSatirA + i, analizSutunA + j]; myrange2.Value2 = dtSoruAnalizA.Rows[i][j] == null ? "" : dtSoruAnalizA.Rows[i][j]; //myrange2.Select(); } } for (int i = 0; i < dtSoruAnalizB.Rows.Count; i++) { for (int j = 0; j < dtSoruAnalizB.Columns.Count; j++) { Range myrange2 = (Range)sheet2.Cells[analizSatirB + i, analizSutunB + j]; myrange2.Value2 = dtSoruAnalizB.Rows[i][j] == null ? "" : dtSoruAnalizB.Rows[i][j]; //myrange2.Select(); } } } //Not aralıkları int aralikSatir = 1, araliksutun = 1; for (int i = 0; i < dtAralik.Rows.Count; i++) { for (int j = 0; j < dtAralik.Columns.Count; j++) { Range myrange3 = (Range)sheet3.Cells[aralikSatir + i, araliksutun + j]; myrange3.Value2 = dtAralik.Rows[i][j] == null ? "" : dtAralik.Rows[i][j]; //myrange2.Select(); } } //Grafikler if (grupSayisi == 1) { Excel.Range chartRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)sheet2.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(50, 20, 800, 250); Excel.Chart chartPage = myChart.Chart; chartRange = sheet2.get_Range("B:A"); chartPage.SetSourceData(chartRange, Missing); chartPage.ChartType = Excel.XlChartType.xlColumnClustered; excelDosya.Visible = true; } else { Excel.Range chartRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)sheet2.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(50, 20, 800, 250); Excel.Chart chartPage = myChart.Chart; chartRange = sheet2.get_Range("B:A"); chartPage.SetSourceData(chartRange, Missing); chartPage.ChartType = Excel.XlChartType.xlColumnClustered; Excel.Range chartRange2; Excel.ChartObjects xlCharts2 = (Excel.ChartObjects)sheet2.ChartObjects(Type.Missing); Excel.ChartObject myChart2 = (Excel.ChartObject)xlCharts2.Add(50, 240, 800, 250); Excel.Chart chartPage2 = myChart2.Chart; chartRange2 = sheet2.get_Range("D:E"); chartPage2.SetSourceData(chartRange2, Missing); chartPage2.ChartType = Excel.XlChartType.xlColumnClustered; excelDosya.Visible = true; } //Not Aralıkları Excel.Range chartRange3; Excel.ChartObjects xlCharts3 = (Excel.ChartObjects)sheet3.ChartObjects(Type.Missing); Excel.ChartObject myChart3 = (Excel.ChartObject)xlCharts3.Add(50, 80, 800, 250); Excel.Chart chartPage3 = myChart3.Chart; chartRange3 = sheet3.get_Range("A:B"); chartPage3.SetSourceData(chartRange3, Missing); chartPage3.ChartType = Excel.XlChartType.xlColumnClustered; excelDosya.Visible = true; } else { MessageBox.Show("Aktarmaya uygun veri bulunamadı."); } }
private void ExportToExcel() { // Creating a Excel object. Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel.Worksheet worksheet = null; try { worksheet = workbook.ActiveSheet; worksheet.Name = "Работники"; int cellRowIndex = 1; int cellColumnIndex = 1; //Loop through each row and read value from each column. int rowCount = 0; for (int i = 0; i < dgv.Rows.Count; i++) { for (int j = 0; j < dgv.Columns.Count; j++) { if (cellRowIndex == 1) { worksheet.Cells[cellRowIndex, cellColumnIndex] = dgv.Columns[j].HeaderText; } else { if (dgv.Rows[i].Cells[j].Value != DBNull.Value) { worksheet.Cells[cellRowIndex, cellColumnIndex] = dgv.Rows[i - 1].Cells[j].Value.ToString(); } else { break; } } cellColumnIndex++; } cellColumnIndex = 1; cellRowIndex++; rowCount = i; } // Set formatting Range formatRange; formatRange = worksheet.get_Range("a1", "d1"); formatRange.EntireRow.Font.Bold = true; formatRange.Borders.Weight = XlBorderWeight.xlThin; worksheet.Range["A1", "D4"].Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; worksheet.Range["A2", "D" + rowCount].Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; rowCount++; worksheet.get_Range("a2", "d" + rowCount).Borders.Weight = XlBorderWeight.xlMedium; ((Range)worksheet.Columns["A", System.Type.Missing]).EntireColumn.ColumnWidth = 50; ((Range)worksheet.Columns["B", System.Type.Missing]).EntireColumn.ColumnWidth = 25; ((Range)worksheet.Columns["C", System.Type.Missing]).EntireColumn.ColumnWidth = 25; ((Range)worksheet.Columns["D", System.Type.Missing]).EntireColumn.ColumnWidth = 25; // Biuld histogram chart object misValue = System.Reflection.Missing.Value; Microsoft.Office.Interop.Excel.Range chartRange; Microsoft.Office.Interop.Excel.ChartObjects xlCharts = (Microsoft.Office.Interop.Excel.ChartObjects)worksheet.ChartObjects(Type.Missing); Microsoft.Office.Interop.Excel.ChartObject myChart = (Microsoft.Office.Interop.Excel.ChartObject)xlCharts.Add(10, 80, 300, 250); Microsoft.Office.Interop.Excel.Chart chartPage = myChart.Chart; Range fCol = worksheet.get_Range("A2", "A" + rowCount); Range sCol = worksheet.get_Range("D2", "D" + rowCount); chartRange = worksheet.get_Range(fCol, sCol); chartPage.SetSourceData(chartRange, misValue); chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered; //Getting the location and file name of the excel to save from user. SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*"; saveDialog.FilterIndex = 2; if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK) { workbook.SaveAs(saveDialog.FileName); MessageBox.Show("Export Successful"); } } catch (System.Exception ex) { MessageBox.Show(ex.Message); } finally { excel.Quit(); workbook = null; excel = null; } }
//// 關閉Excel Message // Microsoft.Office.Interop.Excel.Application.DisplayAlerts = false; // Microsoft.Office.Interop.Excel.Application.Visible = false; // 設為true,則一開始就會顯現Excel檔. // //ExlBook = ExlApp.Workbooks.Add(Server.MapPath(".") + "\\Tool_Prod_sample.xls"); // ExlBook = ExlApp.Workbooks.Add("c:\TAIWAN_BANK_OutSite_Salary_FA_20160111.xls"); protected void Page_Load(object sender, EventArgs e) { // xl.Application ExlApp ; // ExlApp = new xl.ApplicationClass(); // object missValue = System.Reflection.Missing.Value; // string path=@"c:\\TAIWAN_BANK_OutSite_Salary_FA_20160111.xls"; // Workbook w = ExlApp.Workbooks.Open(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //// (path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); // Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)w.Sheets["Sheet1"]; // //ws.Protect(Contents: false); // Range r = ws.get_Range("B2","H20"); // r.CopyPicture(XlPictureAppearance.xlScreen, XlCopyPictureFormat.xlBitmap); // Bitmap image = new Bitmap(Clipboard.GetImage()); // image.Save(@"C:\abc\image.png"); xl.Application xlApp; xl.Workbook xlWorkBook; xl.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new xl.Application(); string path = @"c:\\abcd.xls"; xlWorkBook = xlApp.Workbooks.Open(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (xl.Worksheet)xlWorkBook.Worksheets.get_Item(1); xl.Range xlRange; xlRange = xlWorkSheet.get_Range("A1", "d5"); xlRange.CopyPicture(Microsoft.Office.Interop.Excel.XlPictureAppearance.xlScreen, Microsoft.Office.Interop.Excel.XlCopyPictureFormat.xlPicture); xl.ChartObjects xlCharts = (xl.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); xl.ChartObject myChart = (xl.ChartObject)xlCharts.Add(10, 80, 300, 250); xl.Chart chartPage = myChart.Chart; xl.Range chartRange; chartRange = xlWorkSheet.get_Range("A1", "d5"); chartPage.SetSourceData(chartRange, misValue); chartPage.ChartType = xl.XlChartType.xlColumnClustered; //export chart as picture file //chartPage.Paste(); chartPage.Export(@"C:\excel_chart_export.JPG", "JPG", misValue); xlCharts.Delete(); //xlWorkBook.SaveAs(@"C:\excel_chart_export.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); }
public void UseCopyPaste() { // Declare variables to hold references to PowerPoint objects. pptNS.Application powerpointApplication = null; pptNS.Presentation pptPresentation = null; pptNS.Slide pptSlide = null; pptNS.ShapeRange shapeRange = null; // Declare variables to hold references to Excel objects. xlNS.Application excelApplication = null; xlNS.Workbook excelWorkBook = null; xlNS.Worksheet targetSheet = null; xlNS.ChartObjects chartObjects = null; xlNS.ChartObject existingChartObject = null; string paramPresentationPath = System.Windows.Forms.Application.StartupPath + @"\ChartTest.pptx"; string paramWorkbookPath = System.Windows.Forms.Application.StartupPath + @"\ChartData.xlsx"; object paramMissing = Type.Missing; try { // Create an instance of PowerPoint. powerpointApplication = new pptNS.Application(); // Create an instance Excel. excelApplication = new xlNS.Application(); // Open the Excel workbook containing the worksheet with the chart data. excelWorkBook = excelApplication.Workbooks.Open(paramWorkbookPath, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing); // Get the worksheet that contains the chart. targetSheet = (xlNS.Worksheet)(excelWorkBook.Worksheets["Quarterly Sales"]); // Get the ChartObjects collection for the sheet. chartObjects = (xlNS.ChartObjects)(targetSheet.ChartObjects(paramMissing)); // Get the chart to copy. existingChartObject = (xlNS.ChartObject)(chartObjects.Item("Sales Chart")); // Create a PowerPoint presentation. pptPresentation = powerpointApplication.Presentations.Add( Microsoft.Office.Core.MsoTriState.msoTrue); // Add a blank slide to the presentation. pptSlide = pptPresentation.Slides.Add(1, pptNS.PpSlideLayout.ppLayoutBlank); // Copy the chart from the Excel worksheet to the clipboard. existingChartObject.Copy(); // Paste the chart into the PowerPoint presentation. shapeRange = pptSlide.Shapes.Paste(); // Position the chart on the slide. shapeRange.Left = 60; shapeRange.Top = 100; // Save the presentation. pptPresentation.SaveAs(paramPresentationPath, pptNS.PpSaveAsFileType.ppSaveAsOpenXMLPresentation, Microsoft.Office.Core.MsoTriState.msoTrue); } catch (Exception ex) { Console.WriteLine(ex.Message); } finally { // Release the PowerPoint slide object. shapeRange = null; pptSlide = null; // Close and release the Presentation object. if (pptPresentation != null) { pptPresentation.Close(); pptPresentation = null; } // Quit PowerPoint and release the ApplicationClass object. if (powerpointApplication != null) { powerpointApplication.Quit(); powerpointApplication = null; } // Release the Excel objects. targetSheet = null; chartObjects = null; existingChartObject = null; // Close and release the Excel Workbook object. if (excelWorkBook != null) { excelWorkBook.Close(false, paramMissing, paramMissing); excelWorkBook = null; } // Quit Excel and release the ApplicationClass object. if (excelApplication != null) { excelApplication.Quit(); excelApplication = null; } GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.WaitForPendingFinalizers(); } }
public static void AnalyseVisits(string fileName) { using (ModelMedDBContainer db = new ModelMedDBContainer()) { object misValue = System.Reflection.Missing.Value; // Создаём экземпляр нашего приложения Excel.Application excelApp = new Excel.Application(); // Создаём экземпляр рабочий книги Excel Excel.Workbook workBook; // Создаём экземпляр листа Excel Excel.Worksheet workSheet; workBook = excelApp.Workbooks.Add(misValue); workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(1); var specials = (from docs in db.PersonSet where (docs is Doctor)select(docs as Doctor).Job).Distinct().ToArray(); //var workTime = (from works in db.WorkTimeSet group works by works.Start.Date); var workT = (from works in db.WorkTimeSet select new { works.Start, works.Doctor.Job }).ToList(); //List<DateTime> workDays = new List<DateTime>(); //foreach (DateTime t in workT) // workDays.Add(t.Date); DateTime start = (from works in db.WorkTimeSet select works.Start).Min(); DateTime finish = (from works in db.WorkTimeSet select works.Start).Max(); int[,] days = new int[specials.Length, (finish.Date - start.Date).Days + 1]; //Подсчёт прёмов по профессиям и по дням for (int i = 0; i < specials.Length; i++) { foreach (var t in workT) { if (specials[i] == t.Job) { days[i, (t.Start.Date - start.Date).Days]++; } } } //Заполнение строчек и столбцов посчитанными значениями for (int i = 1; i <= specials.Length; i++) { workSheet.Cells[i, 1] = specials[i - 1]; for (int j = 2; j <= days.GetLength(1) + 1; j++) { workSheet.Cells[i, j] = days[i - 1, j - 2]; } } workSheet.Cells[specials.Length + 1, 1] = start; for (int i = 2; i <= days.GetLength(1) + 1; i++) { workSheet.Cells[specials.Length + 1, i] = start; start = start.AddDays(1); } /* * //Вычисляем сумму этих чисел * Excel.Range rng = workSheet.Range["A2"]; * rng.Formula = "=SUM(A1:L1)"; * rng.FormulaHidden = false; * * // Выделяем границы у этой ячейки * Excel.Borders border = rng.Borders; * border.LineStyle = Excel.XlLineStyle.xlContinuous; */ Excel.ChartObjects xlCharts = (Excel.ChartObjects)workSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250); // Excel.ChartObject chartObj = myChart.Add(5, 50, 300, 300); Excel.Chart chartPage = myChart.Chart; //chartPage.ChartType = Excel.XlChartType.xlXYScatterLines; //chartPage.ChartType = Excel.XlChartType.xlBarClustered; //chartPage.ChartType = Excel.XlChartType.xlLine; Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application(); Excel.SeriesCollection seriesCollection = chartPage.SeriesCollection(); //char a = char.ConvertFromUtf32((char.ConvertToUtf32('A',0) + days.GetLength(1))); Excel.Range rngX = workSheet.Range[workSheet.Cells[specials.Length + 1, 2], workSheet.Cells[specials.Length + 1, days.GetLength(1) + 1]]; // workSheet.Cells[specials.Length+1, 1], workSheet.Cells[specials.Length+1, days.GetLength(1)]]; for (int i = 1; i <= specials.Length; i++) { Excel.Series series = seriesCollection.NewSeries(); Excel.Range rng = workSheet.Range[workSheet.Cells[i, 2], workSheet.Cells[i, days.GetLength(1) + 1]]; series.XValues = rngX; series.Values = rng; series.Name = specials[i - 1]; } workSheet.Columns.AutoFit(); workBook.SaveAs(fileName, Excel.XlFileFormat.xlExcel12, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); workBook.Close(true, misValue, misValue); excelApp.Quit(); /* * xlChart.Activate(); * xlChart.Select(Type.Missing); * * * //Даем названия осей * ((Excel.Axis)excelApp.ActiveChart.Axes(Excel.XlAxisType.xlCategory, * Excel.XlAxisGroup.xlPrimary)).HasTitle = true; * ((Excel.Axis)excelApp.ActiveChart.Axes(Excel.XlAxisType.xlCategory, * Excel.XlAxisGroup.xlPrimary)).AxisTitle.Text = "Дата"; * ((Excel.Axis)excelApp.ActiveChart.Axes(Excel.XlAxisType.xlSeriesAxis, * Excel.XlAxisGroup.xlPrimary)).HasTitle = false; * ((Excel.Axis)excelApp.ActiveChart.Axes(Excel.XlAxisType.xlValue, * Excel.XlAxisGroup.xlPrimary)).HasTitle = true; * ((Excel.Axis)excelApp.ActiveChart.Axes(Excel.XlAxisType.xlValue, * Excel.XlAxisGroup.xlPrimary)).AxisTitle.Text = "Приёмов"; * * excelApp.ActiveChart.HasTitle = true; * excelApp.ActiveChart.ChartTitle.Text = "Количество приёмов на каждую специальность по дням"; * * //Будем отображать легенду * excelApp.ActiveChart.HasLegend = true; * //Расположение легенды * excelApp.ActiveChart.Legend.Position * = Excel.XlLegendPosition.xlLegendPositionLeft; */ // Открываем созданный excel-файл //excelApp.Visible = true; //excelApp.UserControl = true; } }
public void exportarTablaExponencial(Fila[] tabla, double mediaObservada, double desvObservada, int cantNum, double lambda, int intervalos) { try { double cantNumeros = Convert.ToDouble(cantNum); double chi = 0.0; double sumaChi = 0.0; SetNewCurrentCulture(); //Crea el titulo xlWorkSheet.Range["A3:F3"].Merge(); xlWorkSheet.Range["A3:F3"].Font.Size = 20; xlWorkSheet.Range["A3:F3"].Value = "Distribución Exponencial Negativa"; xlWorkSheet.Range["A3:M4"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Range["A3:M4"].Font.Bold = true; xlWorkSheet.Range["H5:I5"].Font.Bold = true; xlWorkSheet.Range["H5:I5"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; xlWorkSheet.Range["A5:A" + (5 + intervalos)].EntireColumn.NumberFormat = "00.0000"; xlWorkSheet.Range["B5:B" + (5 + intervalos)].EntireColumn.NumberFormat = "00.0000"; xlWorkSheet.Range["C5:C" + (5 + intervalos)].EntireColumn.NumberFormat = "0.000"; xlWorkSheet.Range["D5:D" + (5 + intervalos)].EntireColumn.NumberFormat = "0"; xlWorkSheet.Range["E5:E" + (5 + intervalos)].EntireColumn.NumberFormat = "00.0000"; xlWorkSheet.Range["F5:F" + (5 + intervalos)].EntireColumn.NumberFormat = "00.0000"; //Crea las cabeceras xlWorkSheet.Range["A4:M4"].Font.Size = 8; xlWorkSheet.Cells[4, "A"] = "Limite inferior"; xlWorkSheet.Cells[4, "B"] = "Limite superior"; //xlWorkSheet.Cells[4, "C"] = "Marca de clase"; xlWorkSheet.Cells[4, "D"] = "Frecuencia"; // xlWorkSheet.Cells[4, "E"] = "Frecuencia Relativa"; xlWorkSheet.Cells[4, "E"] = "F. Esperada Dist. Exp. Negativa"; xlWorkSheet.Cells[4, "F"] = "F. Chi Cua. Dist. Exp. Negativa"; // obtencion de parametros para distribuciones falta calcular estos parametros en base a datos del archivo ingresado for (int i = 0; i < tabla.Length; i++) { xlWorkSheet.Cells[i + 5, "A"] = tabla[i].LimiteInferior; xlWorkSheet.Cells[i + 5, "B"] = tabla[i].LimiteSuperior; xlWorkSheet.Cells[i + 5, "C"] = tabla[i].conocerMedia(); xlWorkSheet.Cells[i + 5, "D"] = tabla[i].Frecuencia; // xlWorkSheet.Cells[i + 5, "E"] = tabla[i].Frecuencia / cantNumeros; //distribuciones esperadas double observado = tabla[i].Frecuencia; double esperadoExpo = ProbDistrExpo(lambda, tabla[i].LimiteSuperior, tabla[i].LimiteInferior) * cantNumeros; xlWorkSheet.Cells[i + 5, "E"] = esperadoExpo; // calculo de valores de chi cuadrado chi = Math.Pow(observado - esperadoExpo, 2) / esperadoExpo; xlWorkSheet.Cells[i + 5, "F"] = chi; sumaChi = sumaChi + chi; } //muestra Sumatorias Chi cuadrado xlWorkSheet.Cells[5, "H"] = "Sumatoria x^2"; xlWorkSheet.Cells[5, "I"] = sumaChi; TablaChi.Class1 chi2 = new Class1(); xlWorkSheet.Cells[6, "H"] = "Valor x^2"; xlWorkSheet.Cells[6, "I"] = chi2.ValorChi2(0.95, intervalos, 1); //xlWorkSheet.Cells[6, "I"].Formula="CHISQ.INV.RT(0,05;"+(intervalos-2)+")"; //"11.07"; //xlWorkSheet.Cells[6, "H"] = "Valor de la Tabla"; //Crea el grafico Excel.Range chartRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(20, 120, 400, 300); Excel.Chart chartPage = myChart.Chart; int inter = 4 + intervalos; chartRange = xlWorkSheet.get_Range("C4", "E" + inter); chartPage.HasTitle = true; chartPage.ChartTitle.Caption = "Frecuencia Observada Vs. Frecuencia Esperada"; chartPage.SetSourceData(chartRange, misValue); chartPage.ChartType = Excel.XlChartType.xlColumnClustered; // chartPage.HasAxis = xlWorkSheet.Range["C5:C" + (5 + intervalos)]; chartPage.HasLegend = true; chartPage.ShowDataLabelsOverMaximum = true; } finally { ResetCurrentCulture(); } }
private void btnChart_Click(object sender, EventArgs e) { Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); //add data xlWorkSheet.Cells[1, 2] = "Overview"; xlWorkSheet.Cells[1, 3] = "Time"; xlWorkSheet.Cells[2, 1] = "Planned \n" + toTime(totalPlanned).ToString(); xlWorkSheet.Cells[2, 2] = totalPlanned; xlWorkSheet.Cells[3, 1] = "Not Planned \n" + toTime(totalNotPlanned).ToString(); xlWorkSheet.Cells[3, 2] = totalNotPlanned; xlWorkSheet.Cells[4, 1] = "Auto Mode \n" + toTime(totalAutoMode).ToString(); xlWorkSheet.Cells[4, 2] = totalAutoMode; xlWorkSheet.Cells[4, 3] = toTime(totalAutoMode).ToString(); //xlWorkBook.SaveAs("csharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); Excel.Range chartRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250); Excel.Chart chartPage = myChart.Chart; chartRange = xlWorkSheet.get_Range("A1", "c4"); chartPage.SetSourceData(chartRange, misValue); chartPage.ChartType = Excel.XlChartType.xlPie; //chartPage.SeriesCollection(2).Format.Fill.ForeColor.RGB = System.Drawing.Color.Red.ToArgb(); //chartPage.ChartType = Excel.XlChartType.xlColumnClustered; // if (!File.Exists(@"C:\excel_chart_export.bmp")) //{ chartPage.Export(@"C:\excel_chart_export.bmp", "BMP", misValue); //} //export chart as picture file //xlWorkBook.SaveAs("csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(false, misValue, misValue); xlApp.Quit(); this.Hide(); Image img; using (var bmpTemp = new Bitmap(@"C:\excel_chart_export.bmp")) { img = new Bitmap(bmpTemp); } using (Form form = new Form()) { form.StartPosition = FormStartPosition.CenterScreen; form.Size = new Size(510, 460); PictureBox pb = new PictureBox(); pb.Dock = DockStyle.Fill; pb.Image = img; form.Controls.Add(pb); form.ShowDialog(); } this.Show(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); releaseObject(chartPage); File.Delete(@"C:\excel_chart_export.bmp"); }
private void отчетToolStripMenuItem_Click(object sender, EventArgs e) { SelectDateRange srd = new SelectDateRange(); srd.dateTimePicker1.Value = (DateTime)db.Invoices.First().sale_date; DialogResult dr = srd.ShowDialog(this); DateTime startDate = srd.dateTimePicker1.Value; DateTime endDate = srd.dateTimePicker2.Value; List <Invoice> invoices = db.Invoices.Where(i => i.sale_date.Value >= startDate && i.sale_date.Value <= endDate).OrderByDescending(i => i.Product.name).ToList(); //string[] head = { "Код", "Название товара", "Цена за единицу", "Заказано КГ.", "Дата продажи", "Общая стоимость" }; if ((dr == DialogResult.OK) && (0 < invoices.Count)) { // Load Excel application Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); // Create empty workbook excel.Workbooks.Add(); // Create Worksheet from active sheet Microsoft.Office.Interop.Excel._Worksheet workSheet = excel.ActiveSheet; try { workSheet.Cells[1, "A"] = "Код накладной"; workSheet.Cells[1, "B"] = "Название товара"; workSheet.Cells[1, "C"] = "Цена за единицу"; workSheet.Cells[1, "D"] = "Заказано (КГ.)"; workSheet.Cells[1, "E"] = "Дата покупки"; workSheet.Cells[1, "F"] = "Сумма за покупку"; workSheet.Cells[1, "H"] = "Название товара"; workSheet.Cells[1, "I"] = "Количество заказанного товара"; workSheet.Cells[1, "K"] = "Нужно закупить больше:"; workSheet.Cells[1, "L"] = "Нужно закупить меньше:"; int row = 2; ArrayList nameArray = new ArrayList(); ArrayList sumArray = new ArrayList(); int sum; string name; foreach (Invoice invoice in invoices) { workSheet.Cells[row, "A"] = invoice.invoice_code; workSheet.Cells[row, "B"] = invoice.Product.name; workSheet.Cells[row, "C"] = string.Format("{0} грн.", invoice.Product.price); workSheet.Cells[row, "D"] = string.Format("{0} кг.", invoice.quantity); workSheet.Cells[row, "E"] = invoice.sale_date.Value.Date; workSheet.Cells[row, "F"] = string.Format("{0} грн.", invoice.price); name = db.Invoices.Where(nn => nn.product_id == invoice.Product.id && nn.sale_date.Value >= startDate && nn.sale_date.Value <= endDate).First().Product.name.ToString(); sum = (int)db.Invoices.Where(i => i.Product.name == invoice.Product.name && i.sale_date.Value >= startDate && i.sale_date.Value <= endDate).Sum(s => s.quantity); if (!nameArray.Contains(name)) { nameArray.Add(name); } if (!sumArray.Contains(sum)) { sumArray.Add(sum); } row++; } row = 2; foreach (string nameRow in nameArray) { workSheet.Cells[row, "H"] = nameRow; row++; } row = 2; foreach (int sumRow in sumArray) { workSheet.Cells[row, "I"] = sumRow; row++; } Excel.ChartObjects chartObjs = (Excel.ChartObjects)workSheet.ChartObjects(Type.Missing); Excel.ChartObject chartObj = chartObjs.Add(100, 20, 150, 200); Excel.Chart xlChart = chartObj.Chart; Excel.Range rg = workSheet.get_Range("H2:H" + (row - 1).ToString(), "I2:I" + (row - 1).ToString()); xlChart.ChartType = Excel.XlChartType.xlPieExploded; xlChart.SetSourceData(rg, Type.Missing); //rg.FormulaLocal = "MAX(I2:I"+(row-1).ToString(); //workSheet.Cells[2, "M"].Value = "=MAX(I2:I" + (row - 1).ToString(); //workSheet.Cells[2, "N"].Value = "=MIN(I2:I" + (row - 1).ToString(); //for (int i = 2; i < row; i++) // { // workSheet.Cells[i, "K"].Value = "=ЕСЛИ(M2=I" + i.ToString() + ";H" + i.ToString() + ";0)"; // } //for (int i = 2; i < row; i++) // { // workSheet.Cells[i, "L"].Value = "=ЕСЛИ(N2=I" + i.ToString() + ";H" + i.ToString() + ";0)"; // } // rg.FormulaLocal = "MAX(I2:I"+(row-1).ToString(); //rg.FormulaLocal = "MIN(I2:I"+(row-1).ToString(); // Apply some predefined styles for data to look nicely :) workSheet.Range["A1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1); workSheet.Range["H1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1); workSheet.Range["K1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1); // Define filename string fileName = string.Format(@"{0}\ExcelData.xlsx", Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory)); // Save this data as a file workSheet.SaveAs(fileName); // Display SUCCESS message MessageBox.Show(string.Format("Файл '{0}' успешно сохранен!", fileName)); } catch (Exception exception) { MessageBox.Show("Exception", "Ошибка записи файла!\n" + exception.Message, MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { // Quit Excel application excel.Quit(); //excel.Workbooks.Open(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory)); // Release COM objects (very important!) if (excel != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); } if (workSheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet); } // Empty variables excel = null; workSheet = null; // Force garbage collector cleaning GC.Collect(); } } else { MessageBox.Show("В выбраном диапазоне, нет накладных!"); } if (dr == DialogResult.Cancel) { return; } }
/// <summary> /// создание Excel отчета за день/неделю/месяц/квартал /// </summary> /// <param name="values"></param> /// <param name="win"></param> /// <param name="conn"></param> /// <param name="list_day_data"></param> /// <param name="list_week_data"></param> /// <param name="list_month_data"></param> /// <param name="list_quater_data"></param> public async void Create_Excel_Doc(List <Flovers_WPF.Reply_Window.Data> values, Reply_Window win, SQLite.SQLiteAsyncConnection conn, List <Flovers_WPF.Reply_Window.Data> list_day_data, List <Flovers_WPF.Reply_Window.Data> list_week_data, List <Flovers_WPF.Reply_Window.Data> list_month_data, List <Flovers_WPF.Reply_Window.Data> list_quater_data) { Excel.Application exapp = new Excel.Application(); exapp.SheetsInNewWorkbook = 1; exapp.Workbooks.Add(Type.Missing); exapp.DisplayAlerts = true; exapp.Visible = true; Excel.Workbooks exappworkbooks = exapp.Workbooks; Excel.Workbook exappworkbook = exappworkbooks[1]; exappworkbook.Saved = false; Excel.Sheets excellsheets = exappworkbook.Worksheets; Excel.Worksheet excellworksheet = (Excel.Worksheet)excellsheets.get_Item(1); excellworksheet.Activate(); Excel.Range excelcells; for (int j = 1; j < 4; j++) { if (j == 1) { excelcells = (Excel.Range)excellworksheet.Cells[1, j]; excelcells.Value2 = "время/дата"; excelcells.Font.Size = 12; excelcells.Font.Italic = true; excelcells.Font.Bold = true; excelcells.HorizontalAlignment = Excel.Constants.xlCenter; excelcells.VerticalAlignment = Excel.Constants.xlCenter; } if (j == 2) { excelcells = (Excel.Range)excellworksheet.Cells[1, j]; excelcells.Value2 = "Количество"; excelcells.Font.Size = 12; excelcells.Font.Italic = true; excelcells.Font.Bold = true; excelcells.HorizontalAlignment = Excel.Constants.xlCenter; excelcells.VerticalAlignment = Excel.Constants.xlCenter; } if (j == 3) { excelcells = (Excel.Range)excellworksheet.Cells[1, j]; excelcells.Value2 = "Букет"; excelcells.Font.Size = 12; excelcells.Font.Italic = true; excelcells.Font.Bold = true; excelcells.HorizontalAlignment = Excel.Constants.xlCenter; excelcells.VerticalAlignment = Excel.Constants.xlCenter; } } if (values != null) { for (int m = 2; m < values.Count + 2; m++) { for (int n = 1; n < 4; n++) { if (n == 1) { excelcells = (Excel.Range)excellworksheet.Cells[m, n]; excelcells.Value2 = values[m - 2].time.ToString(); excelcells.Font.Size = 12; excelcells.Font.Italic = true; excelcells.Font.Bold = false; excelcells.HorizontalAlignment = Excel.Constants.xlCenter; excelcells.VerticalAlignment = Excel.Constants.xlCenter; } if (n == 2) { excelcells = (Excel.Range)excellworksheet.Cells[m, n]; excelcells.Value2 = values[m - 2].count.ToString(); excelcells.Font.Size = 12; excelcells.Font.Italic = true; excelcells.Font.Bold = false; excelcells.HorizontalAlignment = Excel.Constants.xlCenter; excelcells.VerticalAlignment = Excel.Constants.xlCenter; } if (n == 3) { Bouquets b = await conn.GetAsync <Bouquets>(values[m - 2].bouqet_id); excelcells = (Excel.Range)excellworksheet.Cells[m, n]; excelcells.Value2 = b.name.ToString(); excelcells.Font.Size = 12; excelcells.Font.Italic = true; excelcells.Font.Bold = false; excelcells.HorizontalAlignment = Excel.Constants.xlCenter; excelcells.VerticalAlignment = Excel.Constants.xlCenter; } } } Excel.ChartObjects chartsobjrcts = (Excel.ChartObjects)excellworksheet.ChartObjects(Type.Missing); Excel.ChartObject chartsobj = chartsobjrcts.Add(10, 200, 500, 300); Excel.Chart excelchart = chartsobj.Chart; excelcells = excellworksheet.get_Range("A1", "B" + (values.Count + 1).ToString()); excelchart.SetSourceData(excelcells, Type.Missing); excelchart.ChartType = Excel.XlChartType.xlLine; excelchart.HasTitle = true; if (values == list_day_data) { excelchart.ChartTitle.Text = "Продажи за день"; } if (values == list_week_data) { excelchart.ChartTitle.Text = "Продажи за неделю"; } if (values == list_month_data) { excelchart.ChartTitle.Text = "Продажи за месяц"; } if (values == list_quater_data) { excelchart.ChartTitle.Text = "Продажи за квартал"; } excelchart.ChartTitle.Font.Size = 14; excelchart.ChartTitle.Font.Color = 255; excelchart.ChartTitle.Shadow = true; } else { System.Windows.MessageBox.Show("нет данных для отчета"); exapp.Quit(); } }
// データテーブルをエクセルへエクスポート(箱ヒゲ図付き) public void ExportToExcelWithBoxPlotChart(DataTable dt) { if (dt.Rows.Count == 0) { return; } Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); // column headings for (int i = 0; i < dt.Columns.Count; i++) { xlWorkSheet.Cells[1, (i + 1)] = dt.Columns[i].ColumnName; } // rows for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { xlWorkSheet.Cells[(i + 2), (j + 1)] = dt.Rows[i][j]; } } int row = xlWorkSheet.UsedRange.Rows.Count; string address1 = "C1:C" + row.ToString() + ",H1:L" + row.ToString(); string address2 = "M2:M" + row.ToString(); string address3 = "N2:N" + row.ToString(); Excel.Range chartRange1; Excel.Range chartRange2; Excel.Range chartRange3; Excel.ChartObjects xlCharts1 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart1 = (Excel.ChartObject)xlCharts1.Add(800, 10, 600, 250); Excel.Chart chartPage1 = myChart1.Chart; chartRange1 = xlWorkSheet.get_Range(address1); chartRange2 = xlWorkSheet.get_Range(address2); chartRange3 = xlWorkSheet.get_Range(address3); chartPage1.SetSourceData(chartRange1, misValue); chartPage1.ChartType = Excel.XlChartType.xlColumnStacked; chartPage1.HasTitle = true; chartPage1.ChartTitle.Text = dt.Rows[0]["inspect"].ToString() + " " + dt.Rows[0]["line"].ToString(); chartPage1.HasLegend = false; Excel.Axis xAxis1 = (Excel.Axis)chartPage1.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary); xAxis1.CategoryType = Excel.XlCategoryType.xlCategoryScale; Excel.SeriesCollection oSeriesCollection = (Excel.SeriesCollection)myChart1.Chart.SeriesCollection(misValue); Excel.Series s5 = oSeriesCollection.Item(5); s5.ChartType = Excel.XlChartType.xlLine; s5.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbDarkOrchid; Excel.Series s4 = oSeriesCollection.Item(4); s4.ChartType = Excel.XlChartType.xlLine; s4.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbDarkOrchid; Excel.Series s1 = oSeriesCollection.Item(1); s1.Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbWhite; s1.Format.Fill.Transparency = 1; s1.Format.Line.Weight = 0; s1.HasErrorBars = true; s1.ErrorBar(Excel.XlErrorBarDirection.xlY, Excel.XlErrorBarInclude.xlErrorBarIncludeMinusValues, Excel.XlErrorBarType.xlErrorBarTypeCustom, chartRange2, chartRange2); Excel.Series s3 = oSeriesCollection.Item(3); s3.Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbAqua; s3.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbBlack; s3.Format.Line.Weight = 1.0F; s3.HasErrorBars = true; s3.ErrorBar(Excel.XlErrorBarDirection.xlY, Excel.XlErrorBarInclude.xlErrorBarIncludePlusValues, Excel.XlErrorBarType.xlErrorBarTypeCustom, chartRange3, chartRange3); Excel.Series s2 = oSeriesCollection.Item(2); s2.Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbAqua; s2.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbBlack; s2.Format.Line.Weight = 1.0F; xlApp.Visible = true; }
private void ButtonGenerateReport_Click(object sender, System.EventArgs e) { Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet previousImplementationsSheet; Excel.Worksheet departmentalIssuesSheet; Excel.Worksheet departmentalSuggestionsSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); previousImplementationsSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); departmentalIssuesSheet = (Excel.Worksheet)xlWorkBook.Worksheets.Add(misValue, misValue, misValue, misValue); departmentalSuggestionsSheet = (Excel.Worksheet)xlWorkBook.Worksheets.Add(misValue, misValue, misValue, misValue); //add data previousImplementationsSheet.Cells[1, 1] = ""; previousImplementationsSheet.Cells[1, 2] = "Previous Implementation Issues"; previousImplementationsSheet.Cells[2, 1] = "Issue 1"; previousImplementationsSheet.Cells[2, 2] = "100"; previousImplementationsSheet.Cells[2, 3] = "Full Issue 1 Information (body)"; previousImplementationsSheet.Cells[3, 1] = "Issue 2"; previousImplementationsSheet.Cells[3, 2] = "88"; previousImplementationsSheet.Cells[3, 3] = "Full Issue 2 Information (body)"; previousImplementationsSheet.Cells[4, 1] = "Issue 3"; previousImplementationsSheet.Cells[4, 2] = "44"; previousImplementationsSheet.Cells[4, 3] = "Full Issue 3 Information (body)"; previousImplementationsSheet.Cells[5, 1] = "Issue 4"; previousImplementationsSheet.Cells[5, 2] = "30"; previousImplementationsSheet.Cells[5, 3] = "Full Issue 4 Information (body)"; previousImplementationsSheet.Cells[6, 1] = "Issue 5"; previousImplementationsSheet.Cells[6, 2] = "12"; previousImplementationsSheet.Cells[6, 3] = "Full Issue 5 Information (body)"; departmentalIssuesSheet.Cells[1, 1] = ""; departmentalIssuesSheet.Cells[1, 2] = "Departmental Issues"; departmentalIssuesSheet.Cells[2, 1] = "Issue 1"; departmentalIssuesSheet.Cells[2, 2] = "56"; departmentalIssuesSheet.Cells[2, 3] = "Full Issue 1 Information (body)"; departmentalIssuesSheet.Cells[3, 1] = "Issue 2"; departmentalIssuesSheet.Cells[3, 2] = "32"; departmentalIssuesSheet.Cells[3, 3] = "Full Issue 2 Information (body)"; departmentalIssuesSheet.Cells[4, 1] = "Issue 3"; departmentalIssuesSheet.Cells[4, 2] = "10"; departmentalIssuesSheet.Cells[4, 3] = "Full Issue 3 Information (body)"; departmentalIssuesSheet.Cells[5, 1] = "Issue 4"; departmentalIssuesSheet.Cells[5, 2] = "8"; departmentalIssuesSheet.Cells[5, 3] = "Full Issue 4 Information (body)"; departmentalIssuesSheet.Cells[6, 1] = "Issue 5"; departmentalIssuesSheet.Cells[6, 2] = "4"; departmentalIssuesSheet.Cells[6, 3] = "Full Issue 5 Information (body)"; departmentalSuggestionsSheet.Cells[1, 1] = ""; departmentalSuggestionsSheet.Cells[1, 2] = "Departmental Suggestions"; departmentalSuggestionsSheet.Cells[2, 1] = "Issue 1"; departmentalSuggestionsSheet.Cells[2, 2] = "89"; departmentalSuggestionsSheet.Cells[2, 3] = "Full Issue 1 Information (body)"; departmentalSuggestionsSheet.Cells[3, 1] = "Issue 2"; departmentalSuggestionsSheet.Cells[3, 2] = "85"; departmentalSuggestionsSheet.Cells[3, 3] = "Full Issue 2 Information (body)"; departmentalSuggestionsSheet.Cells[4, 1] = "Issue 3"; departmentalSuggestionsSheet.Cells[4, 2] = "70"; departmentalSuggestionsSheet.Cells[4, 3] = "Full Issue 3 Information (body)"; departmentalSuggestionsSheet.Cells[5, 1] = "Issue 4"; departmentalSuggestionsSheet.Cells[5, 2] = "36"; departmentalSuggestionsSheet.Cells[5, 3] = "Full Issue 4 Information (body)"; departmentalSuggestionsSheet.Cells[6, 1] = "Issue 5"; departmentalSuggestionsSheet.Cells[6, 2] = "22"; departmentalSuggestionsSheet.Cells[6, 3] = "Full Issue 1 Information (body)"; Excel.Range chartRange; Excel.Range departmentRange; Excel.Range departmentSuggestRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)previousImplementationsSheet.ChartObjects(); Excel.ChartObjects departmentCharts = (Excel.ChartObjects)departmentalIssuesSheet.ChartObjects(); Excel.ChartObjects departmentSuggestCharts = (Excel.ChartObjects)departmentalSuggestionsSheet.ChartObjects(); Excel.ChartObject myChart = xlCharts.Add(10, 120, 350, 250); Excel.ChartObject departChart = departmentCharts.Add(10, 120, 350, 250); Excel.ChartObject departSugChart = departmentSuggestCharts.Add(10, 120, 350, 250); Excel.Chart chartPage = myChart.Chart; Excel.Chart departPage = departChart.Chart; Excel.Chart departSugPage = departSugChart.Chart; chartRange = previousImplementationsSheet.get_Range("A1", "b6"); departmentRange = departmentalIssuesSheet.get_Range("A1", "b6"); departmentSuggestRange = departmentalSuggestionsSheet.get_Range("A1", "b6"); chartPage.SetSourceData(chartRange, misValue); departPage.SetSourceData(departmentRange, misValue); departSugPage.SetSourceData(departmentSuggestRange, misValue); chartPage.ChartType = Excel.XlChartType.xlColumnClustered; departPage.ChartType = Excel.XlChartType.xlColumnClustered; departSugPage.ChartType = Excel.XlChartType.xlColumnClustered; Excel.Application excelApp = new Excel.Application(); xlWorkBook.SaveAs("ManagerReport.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); //System.Diagnostics.Process.Start(@"C:\Users\russe\Documents\ManagerReport.xls"); }
/// <summary> /// Handles the Click event of the btnExcelExport control. /// </summary> /// <param name="sender">The source of the event.</param> /// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param> private void btnExcelExport_Click(object sender, EventArgs e) { List <ExcelData> excelList = new List <ExcelData>(); //store the list of excel data in here try { Byte[] data = ObjectToByteArray(new ExcelData()); stream = server.GetStream(); stream.Write(data, 0, data.Length); List <byte[]> listObject = new List <byte[]>(); byte[] bytes = new byte[8192]; byte[] fullObjectBytes; // Loop to receive all the data sent by the client. stream.Read(bytes, 0, bytes.Length); listObject.Add(bytes); var bformatter = new BinaryFormatter(); fullObjectBytes = bytes; Stream fullObjectStream = new MemoryStream(fullObjectBytes); object objFromServer = bformatter.Deserialize(fullObjectStream); Type objType = objFromServer.GetType(); if (objType == typeof(List <ExcelData>)) { excelList = (List <ExcelData>)objFromServer; } } catch (ArgumentNullException ex) { Console.WriteLine("ArgumentNullException: {0}", ex); stream.Close(); } catch (SocketException ex) { Console.WriteLine("SocketException: {0}", ex); stream.Close(); } catch (SerializationException ex) { Console.WriteLine("SerializationException: {0}", ex); stream.Close(); } catch (IOException io) { MessageBox.Show("IOException: " + io.Message); stream.Close(); this.Close(); } BinaryFormatter formatter = new BinaryFormatter(); object missing = Type.Missing; Excel.Application oXL = null; Excel.Workbooks oWBs = null; Excel.Workbook oWB = null; Excel.Worksheet oSheet = null; Excel.Range oCells = null; Excel.Range chartRange = null; //range for the chart Excel.ChartObjects xlCharts = null; Excel.ChartObject myChart = null; Excel.Chart chartPage = null; try { // Create an instance of Microsoft Excel and make it invisible. oXL = new Excel.Application(); oXL.Visible = false; // Create a new Workbook. oWBs = oXL.Workbooks; oWB = oWBs.Add(missing); // Get the active Worksheet and set its name. oSheet = oWB.ActiveSheet as Excel.Worksheet; oSheet.Name = "Question Report"; // Set the column headers oSheet.Cells.EntireColumn.ColumnWidth = 30; oCells = oSheet.Cells; oCells[1, 1] = "Question Number"; oCells[1, 2] = "Question"; oCells[1, 3] = "Average Completion Time"; oCells[1, 4] = "Percent Correct Answers"; int count = 1; foreach (ExcelData ed in excelList) { //set data for the cells count++; oCells[count, 1] = ed.questionNumber; oCells[count, 2] = ed.questionText; oCells[count, 3] = ed.avgTime; oCells[count, 4] = ed.percentCorrect; } xlCharts = (Excel.ChartObjects)oSheet.ChartObjects(Type.Missing); //create the chart myChart = (Excel.ChartObject)xlCharts.Add(700, 10, 300, 250); //where to make the chart chartPage = myChart.Chart; chartRange = oSheet.get_Range("A1", ("C" + Convert.ToString(count - 1))); //set the range for the chart chartPage.SetSourceData(chartRange, Excel.XlRowCol.xlColumns); //set source data for chart chartPage.ChartType = Excel.XlChartType.xlColumnClustered; //set type chartPage.HasLegend = false; //no legend // Save the workbook as a xlsx file and close it string fileName = Path.GetDirectoryName( Assembly.GetExecutingAssembly().Location) + "\\QuestionReport.xlsx"; oWB.SaveAs(fileName, Excel.XlFileFormat.xlOpenXMLWorkbook, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing); oWB.Close(missing, missing, missing); // Quit the Excel application oXL.UserControl = true; oXL.Quit(); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { // Clean up the unmanaged Excel COM resources if (chartRange != null) { Marshal.FinalReleaseComObject(chartRange); chartRange = null; } if (xlCharts != null) { Marshal.FinalReleaseComObject(xlCharts); xlCharts = null; } if (myChart != null) { Marshal.FinalReleaseComObject(myChart); myChart = null; } if (chartPage != null) { Marshal.FinalReleaseComObject(chartPage); chartPage = null; } if (oCells != null) { Marshal.FinalReleaseComObject(oCells); oCells = null; } if (oSheet != null) { Marshal.FinalReleaseComObject(oSheet); oSheet = null; } if (oWB != null) { Marshal.FinalReleaseComObject(oWB); oWB = null; } if (oWBs != null) { Marshal.FinalReleaseComObject(oWBs); oWBs = null; } if (oXL != null) { Marshal.FinalReleaseComObject(oXL); oXL = null; } } }
private void getChartInSheet(int index) { comboChartInSheet.Items.Clear(); if (index != 0) { ws = (Excel.Worksheet)xls.Sheets[index]; chobjs = (Excel.ChartObjects)ws.ChartObjects(nullobj); //Console.WriteLine(chobjs.Count); for (int i = 1; i <= chobjs.Count; i++) { chobj = (Excel.ChartObject)ws.ChartObjects(i); ch = chobj.Chart; string str = chobj.Name + ": "; try { string title = ch.ChartTitle.Text; str += title; } catch { str += "(未命名图表)"; } comboChartInSheet.Items.Add(str); } } if (comboChartInSheet.Items.Count == 0) { comboChartInSheet.Items.Add("(该页无图表!)"); buttonGraph.Enabled = false; groupGraphPoint.Enabled = false; } else { buttonGraph.Enabled = true; groupGraphPoint.Enabled = true; } comboChartInSheet.SelectedIndex = 0; }
private void button2_Click(object sender, EventArgs e) { try { string fileName = Dir + "\\" + string.Format("{0}.xls", Guid.NewGuid()).Replace(" ", "_"); //if (File.Exists(fileName)) //{ // try // { // File.Delete(fileName); // } // catch (Exception) // { // MessageBox.Show(string.Format("В настоящий момент используется файл:\r\n{0}\r\nДля создания договора закройте пожалуйста файл.", fileName), // "Невозможно создать договор", MessageBoxButtons.OK, MessageBoxIcon.Warning); // return; // } //} Excel.Application xla = new Excel.Application(); xla.Visible = true; Excel.Workbook wb = xla.Workbooks.Add(Excel.XlSheetType.xlWorksheet); Excel.Worksheet ws = (Excel.Worksheet)xla.ActiveSheet; // Now create the chart. Excel.ChartObjects chartObjs = (Excel.ChartObjects)ws.ChartObjects(); Excel.ChartObject chartObj = chartObjs.Add(512, 80, 300, 300); Excel.Chart xlChart = chartObj.Chart; xlChart.ChartType = Excel.XlChartType.xlBarClustered; // generate some random data dateTimePicker1.Value = dateTimePicker1.Value.Date.AddDays(1 - dateTimePicker1.Value.Day); dateTimePicker2.Value = dateTimePicker2.Value.Date.AddMonths(1).AddDays(-dateTimePicker2.Value.Day); ws.Cells[2, 2] = string.Format("Результативность работы фирмы за период с {0} по {1}", dateTimePicker1.Value, dateTimePicker2.Value); ws.Cells[4, 3] = "Период"; ws.Cells[4, 4] = "Количество сделок"; ws.Cells[4, 5] = "Сумма"; int row = 5; for (var dateFrom = dateTimePicker1.Value; dateFrom <= dateTimePicker2.Value; dateFrom = dateFrom.AddMonths(1)) { var dateTo = dateFrom.AddMonths(1).AddDays(-1); var trips = dbj.Jurnal.Where(t => t.data >= dateFrom && t.data <= dateTo); foreach (var i in trips) { ws.Cells[row, 3] = GetMonth(dateFrom.Month); ws.Cells[row, 4] = trips.Count(); ws.Cells[row, 5] = i.summa; } row++; } Excel.Range xValues = ws.Range["C5", "C" + (row - 1)]; Excel.Range values = ws.Range["E5", "E" + (row - 1)]; Excel.SeriesCollection seriesCollection = xlChart.SeriesCollection(); Excel.Series series1 = seriesCollection.NewSeries(); series1.XValues = xValues; series1.Values = values; //xla.ActiveChart.HasTitle = true; //xla.ActiveChart.ChartTitle.Text = "Тестовая диаграмма"; //xla.ActiveChart.ChartTitle.Font.Size = 13; //xla.ActiveChart.ChartTitle.Font.Color = 254; //xla.ActiveChart.ChartTitle.Shadow = true; //xla.ActiveChart.ChartTitle.Border.LineStyle = Excel.Constants.xlSolid; } catch { MessageBox.Show("Ошибка Excel не найден"); } }
/// <summary> /// Initializes Excel vareables /// </summary> public static void ExcelPrep() { UsingExcel = true; R1EPCsDic.Clear(); R2EPCsDic.Clear(); #region config Excel application and workbook oXL = new Excel.Application { Visible = true, WindowState = Excel.XlWindowState.xlMaximized }; oWB = oXL.Workbooks.Add(Missing.Value); #endregion try { #region config sheets oSheetDataRSSIs = (Excel._Worksheet)oWB.ActiveSheet; oSheetDataRSSIs.Name = "Rssis Data"; oSheetDataRSSIs.get_Range("A1:Z1").Font.Bold = true; oSheetDataTags = (Excel._Worksheet)oWB.Sheets.Add(Type.Missing, oSheetDataRSSIs, Type.Missing, Type.Missing); oSheetDataTags.Name = "Tags Data"; oSheetDataTags.get_Range("A1:Z2").Font.Bold = true; oSheetDataTags.get_Range("A1:Z2").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; oSheetCharts = (Excel._Worksheet)oWB.Sheets.Add(Type.Missing, oSheetDataTags, Type.Missing, Type.Missing); oSheetCharts.Name = "Charts"; oSheetTagFlags = (Excel._Worksheet)oWB.Sheets.Add(Type.Missing, oSheetCharts, Type.Missing, Type.Missing); oSheetTagFlags.Name = "Flags"; oSheetTagFlags.Cells[1, 1] = "A"; oSheetTagFlags.Cells[1, 2] = "B"; // Selects sheet if needed //oSheetDataTags.Activate(); #endregion #region config charts Excel.ChartObjects xlCharts = oSheetCharts.ChartObjects(Type.Missing); #region config RSSI chart oRng = oSheetCharts.get_Range("B2:K36"); oChartRSSIs = xlCharts.Add(oRng.Left, oRng.Top, oRng.Width, oRng.Height); oChartRSSIs.Chart.ChartWizard( Gallery: Excel.XlChartType.xlLine, Format: 4, PlotBy: Excel.XlRowCol.xlColumns, CategoryLabels: false, SeriesLabels: 5, HasLegend: true, Title: "RSSIs", CategoryTitle: "Values_Range", ValueTitle: "RSSI" ); oChartRSSIs.Chart.SetSourceData(oSheetCharts.get_Range("A1:A2")); #region RSSIs chart axis Excel.Axis axis = oChartRSSIs.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary); axis.MinimumScaleIsAuto = false; axis.MaximumScaleIsAuto = false; axis.MajorUnit = 1; axis.MinimumScale = 170; axis.MaximumScale = 220; #endregion #endregion #region config Tags chart oRng = oSheetCharts.get_Range("M2:U36"); oChartTags = xlCharts.Add(oRng.Left, oRng.Top, oRng.Width, oRng.Height); oChartTags.Chart.ChartWizard( Gallery: Excel.XlChartType.xlXYScatter, Format: 3, CategoryLabels: true, SeriesLabels: 5, HasLegend: true, Title: "Tags Location", CategoryTitle: "X", ValueTitle: "Y" ); oChartTags.Chart.ChartType = Excel.XlChartType.xlXYScatter; oChartTags.Chart.SetSourceData(oSheetCharts.get_Range("A1:A2")); #region Tags chart axis axis = oChartTags.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary); axis.MinimumScaleIsAuto = false; axis.MaximumScaleIsAuto = false; axis.MajorUnit = 1; axis.MinimumScale = 0; axis.MaximumScale = 10; axis = oChartTags.Chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary); axis.MinimumScaleIsAuto = false; axis.MaximumScaleIsAuto = false; axis.MajorUnit = 1; axis.MinimumScale = -4; axis.MaximumScale = 4; #endregion #endregion #endregion } catch (Exception ex) { Console.WriteLine(ex.Message); } }
/// <summary> /// Метод создания и сохранения документов /// в форматах Microsoft Word (doc, PDF), /// Excel (exls) /// </summary> /// <param name="type">Тип создаваемого документа /// отчёт или статистика</param> /// <param name="format">Формат сохранения /// документ или таблица</param> /// <param name="name">Название документа</param> /// <param name="table">Входная таблица с данными</param> public void Document_Create(Document_Type type, Document_Format format, string name, DataTable table) { //Получение данных о конфигурации документа Configuration_class configuration_Class = new Configuration_class(); configuration_Class.Document_Configuration_Get(); //Проверка на пустоту названия switch (name != "" || name != null) { case true: //Выбор формата либо Word либо Excel switch (format) { case Document_Format.Word: //Запуск процесса в дистпечере задач word.Application application = new word.Application(); //создание документа в процессе word.Document document //Присвоение документа процессу, Visible: true //возможность редактирования документа = application.Documents.Add(Visible: true); try { //Объявление дипапазона для формирования текста word.Range range = document.Range(0, 0); //89Настройка отступов в документе document.Sections.PageSetup.LeftMargin = application.CentimetersToPoints( (float)Configuration_class. doc_Left_Merge); document.Sections.PageSetup.TopMargin = application.CentimetersToPoints( (float)Configuration_class. doc_Top_Merge); document.Sections.PageSetup.RightMargin = application. CentimetersToPoints((float) Configuration_class.doc_Right_Merg); document.Sections.PageSetup.BottomMargin = application.CentimetersToPoints( (float)Configuration_class. doc_Bottom_Merge); //Присвоение текстового знеачения в дипазон range.Text = "Мвидео"; //Настройка выравнивания текста range.ParagraphFormat.Alignment = word.WdParagraphAlignment. wdAlignParagraphCenter; //Настройка интервала после абзаца range.ParagraphFormat.SpaceAfter = 1; //Настройка интервала перед абзаца range.ParagraphFormat.SpaceBefore = 1; //Настройка межстрочного интервала range.ParagraphFormat.LineSpacingRule = word.WdLineSpacing.wdLineSpaceSingle; //Настройка названия шрифта range.Font.Name = "Times New Roman"; //Настройка размера шрифта range.Font.Size = 12; //Добавление параграфов document.Paragraphs.Add(); //В конце текста document.Paragraphs.Add(); //Свободный document.Paragraphs.Add(); //Для будущего текста //Параграф для названия документа word.Paragraph Document_Name = document.Paragraphs.Add(); //Настройка параграфа через свойство диапазона Document_Name.Format.Alignment = word.WdParagraphAlignment.wdAlignParagraphCenter; Document_Name.Range.Font.Name = "Times New Roman"; Document_Name.Range.Font.Size = 16; //Проверка на тип документа, отчёт или статистика switch (type) { case Document_Type.Report: Document_Name.Range.Text = "ОТЧЁТ"; break; case Document_Type.Statistic: Document_Name.Range.Text = "СТАТИСТИЧЕСКИЙ ОТЧЁТ"; break; } document.Paragraphs.Add(); document.Paragraphs.Add(); document.Paragraphs.Add(); word.Paragraph statparg = document.Paragraphs.Add(); //Создание области таблицы в документе word.Table stat_table //Добавление таблицы в область документа //Указывается параграф в котором документ создан //Количество строк и столбцов = document.Tables.Add(statparg.Range, table.Rows.Count, table.Columns.Count); //Настройка границ таблицы внутренние stat_table.Borders.InsideLineStyle = word.WdLineStyle.wdLineStyleSingle; //Настройка границ таблицы внешние stat_table.Borders.OutsideLineStyle = word.WdLineStyle.wdLineStyleSingle; //Выравнивание текста внутри ячеек по ширине stat_table.Rows.Alignment = word.WdRowAlignment.wdAlignRowCenter; //Выравнивание текста внутри ячеек по высоте stat_table.Range.Cells.VerticalAlignment = word.WdCellVerticalAlignment.wdCellAlignVerticalCenter; stat_table.Range.Font.Size = 11; stat_table.Range.Font.Name = "Times New Roman"; //Индексация столбцов и строк в Word начинается с 1,1 for (int row = 1; row <= table.Rows.Count; row++) { for (int col = 1; col <= table.Columns.Count; col++) { stat_table.Cell(row, col).Range.Text = table.Rows[row - 1][col - 1].ToString(); } } document.Paragraphs.Add(); document.Paragraphs.Add(); //Парадграф с фиксациейц даты создания документа word.Paragraph Footparg = document.Paragraphs.Add(); Footparg.Range.Text = string.Format("Дата создания \t\t\t{0}", DateTime.Now.ToString("dd.MM.yyyy")); } catch (Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message); } finally { switch (format) { case Document_Format.Word: //Сохранение документа с названием из метода, //и в формате doc document.SaveAs2(string.Format("{0}\\{1}", Environment.CurrentDirectory, name), word.WdSaveFormat.wdFormatDocument); break; case Document_Format.PDF: //Сохранение документа в формате PDF document.SaveAs2(string.Format("{0}\\{1}", Environment.CurrentDirectory, name), word.WdSaveFormat.wdFormatPDF); break; } //Закрываем документ document.Close(); //Выходим из процесса с его закрытием application.Quit(); } break; case Document_Format.Excel: //Создание процесса Excel excel.Application application_ex = new excel.Application(); //Создание книги excel.Workbook workbook = application_ex.Workbooks.Add(); //Создание страницы excel.Worksheet worksheet = (excel.Worksheet)workbook.ActiveSheet; try { switch (type) { case Document_Type.Report: //Название страницы worksheet.Name = "Отчёт"; for (int row = 0; row < table.Rows.Count; row++) { for (int col = 0; col < table.Columns.Count; col++) { //ЗАнесение данных в ячейку worksheet.Cells[row + 1][col + 1] = table.Rows[row][col].ToString(); } } //Указание диапазона работы с ячеёками листа excel.Range border //Начало диапазона = worksheet.Range[worksheet.Cells[1, 1], //Динамический конец диапазона в зависимости от //выдодимых данных worksheet.Cells[table.Rows.Count + 1] [table.Columns.Count + 1]]; //Стиль линий границ ячеек border.Borders.LineStyle = excel.XlLineStyle.xlContinuous; //Выравнивание во высоте border.VerticalAlignment = excel.XlHAlign.xlHAlignCenter; //Выравнивание по ширине border.HorizontalAlignment = excel.XlHAlign.xlHAlignCenter; //Внесение даты создания документа worksheet.Cells[table.Rows.Count + 3][2] = string.Format("Дата создания {0}", DateTime.Now.ToString()); //Объединение ячеек worksheet.Range[worksheet.Cells[table.Rows.Count + 3, 2], worksheet.Cells[table.Rows.Count + 2, table.Columns.Count + 2]].Merge(); break; case Document_Type.Statistic: worksheet.Name = "Статистический отчёт"; for (int row = 0; row < table.Rows.Count; row++) { for (int col = 0; col < table.Columns.Count; col++) { worksheet.Cells[row + 1][col + 1] = table.Rows[row][col].ToString(); } } excel.Range border1 = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[table.Rows.Count + 1] [table.Columns.Count + 1]]; border1.Borders.LineStyle = excel.XlLineStyle.xlContinuous; border1.VerticalAlignment = excel.XlHAlign.xlHAlignCenter; border1.HorizontalAlignment = excel.XlHAlign.xlHAlignCenter; worksheet.Cells[table.Rows.Count + 3][2] = string.Format("Дата создания {0}", DateTime.Now.ToString()); worksheet.Range[worksheet.Cells[table.Rows.Count + 3, 2], worksheet.Cells[table.Rows.Count + 2, table.Columns.Count + 2]].Merge(); //Класс области графиков excel.ChartObjects chartObjects = (excel.ChartObjects)worksheet.ChartObjects( Type.Missing); //Область размещения графиков: отступы слева сверху, //размер ширина и высота excel.ChartObject chartObject = chartObjects.Add(300, 50, 250, 250); //Объявление области графика excel.Chart chart = chartObject.Chart; //Объявление колекции построений графиков excel.SeriesCollection seriesCollection = (excel.SeriesCollection)chart.SeriesCollection( Type.Missing); //Объявление посторения графика excel.Series series = seriesCollection.NewSeries(); //Тип графика chart.ChartType = excel.XlChartType.xl3DColumn; //Диапазон значений по оси X series.XValues = worksheet.get_Range("B2", "B" + table.Rows.Count + 1); //Диапазон значений по оси Y series.Values = worksheet.get_Range("C2", "C" + table.Rows.Count + 1); break; } } catch { } finally { //Сохранение книги workbook.SaveAs(string.Format("{0}\\{1}", Environment.CurrentDirectory, name), application_ex.DefaultSaveFormat); //Закрытие книги workbook.Close(); //Завершение процесса application_ex.Quit(); } break; } break; case false: System.Windows.Forms.MessageBox.Show ("Введите название документа"); break; } }
private void SecondWay() { Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); string filePath = @"C:\Kalya Solutions\GMB insights.xlsx"; string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + filePath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";"; DataTable resultsDataset = new DataTable(); using (OleDbConnection conn = new OleDbConnection(connString)) { OleDbCommand cmd = new OleDbCommand("SELECT * FROM [GMB insights (Discovery Report)$]", conn); conn.Open(); OleDbDataAdapter adapter = new OleDbDataAdapter(cmd); adapter.Fill(resultsDataset); } if (resultsDataset.Rows.Count > 3) { //add data xlWorkSheet.Cells[1, 1] = ""; xlWorkSheet.Cells[1, 2] = "Student1"; xlWorkSheet.Cells[1, 3] = "Student2"; xlWorkSheet.Cells[1, 4] = "Student3"; xlWorkSheet.Cells[2, 1] = "Term1"; xlWorkSheet.Cells[2, 2] = "80"; xlWorkSheet.Cells[2, 3] = "65"; xlWorkSheet.Cells[2, 4] = "45"; xlWorkSheet.Cells[3, 1] = "Term2"; xlWorkSheet.Cells[3, 2] = "78"; xlWorkSheet.Cells[3, 3] = "72"; xlWorkSheet.Cells[3, 4] = "60"; xlWorkSheet.Cells[4, 1] = "Term3"; xlWorkSheet.Cells[4, 2] = "82"; xlWorkSheet.Cells[4, 3] = "80"; xlWorkSheet.Cells[4, 4] = "65"; xlWorkSheet.Cells[5, 1] = "Term4"; xlWorkSheet.Cells[5, 2] = "75"; xlWorkSheet.Cells[5, 3] = "82"; xlWorkSheet.Cells[5, 4] = "68"; xlWorkSheet.Cells[6, 1] = "Term6"; xlWorkSheet.Cells[6, 2] = "75"; xlWorkSheet.Cells[6, 3] = "82"; xlWorkSheet.Cells[6, 4] = "68"; xlWorkSheet.Cells[7, 1] = "Term7"; xlWorkSheet.Cells[7, 2] = "75"; xlWorkSheet.Cells[7, 3] = "82"; xlWorkSheet.Cells[7, 4] = "68"; Excel.Range chartRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 200, 300, 250); Excel.Chart chartPage = myChart.Chart; //chartPage.Location(Excel.XlChartLocation.xlLocationAutomatic, "Chart1"); chartRange = xlWorkSheet.get_Range("A1", "d5"); chartPage.SetSourceData(chartRange, misValue); chartPage.ChartType = Excel.XlChartType.xlDoughnutExploded; xlWorkBook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, "Test3.pdf"); } //xlWorkBook.SaveAs("csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); //xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); //MessageBox.Show("Excel file created , you can find the file c:\\csharp.net-informations.xls"); }
//highest slowest revenues public string HighestRevenuesItems(bool FastOrSlowReport, int NumberOfItems, string DateFrom, string DateTo, bool ExportToExcel = false, string ExportPath = "") { try { xlApp = new Excel.Application(); xlApp.DisplayAlerts = false; xlApp.Visible = false; xlApp.SheetsInNewWorkbook = 1; xlWorkBook = xlApp.Workbooks.Add(misValue);//misValue string ReportName = (FastOrSlowReport ? "Highest Revenues Items Report" : "Lowest Revenues Items Report"); DataTable aTable = ReportsMgmt.HighestRevenuesItems(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++) { 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] = "Total Revenue 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) { 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) { return("ERROR"); } finally { //Once done close and quit Excel xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); } }
private static void ExcelGraphGeneration() { Excel.Application xlApp; Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook .Worksheets.get_Item(1); // Add data columns xlWorkSheet.Cells[1, 1] = "SL"; xlWorkSheet.Cells[1, 2] = "Name"; xlWorkSheet.Cells[1, 3] = "CTC"; xlWorkSheet.Cells[1, 4] = "DA"; xlWorkSheet.Cells[1, 5] = "HRA"; xlWorkSheet.Cells[1, 6] = "Conveyance"; xlWorkSheet.Cells[1, 7] = "Medical Expenses"; xlWorkSheet.Cells[1, 8] = "Special"; xlWorkSheet.Cells[1, 9] = "Bonus"; xlWorkSheet.Cells[1, 10] = "TA"; xlWorkSheet.Cells[1, 11] = "TOTAL"; xlWorkSheet.Cells[1, 11] = "Contribution to PF"; xlWorkSheet.Cells[1, 12] = "Profession Tax"; xlWorkSheet.Cells[1, 13] = "TDS"; xlWorkSheet.Cells[1, 14] = "Salary Advance"; xlWorkSheet.Cells[1, 15] = "TOTAL"; xlWorkSheet.Cells[1, 16] = "NET PAY"; Excel.Application xlApp1 = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp1.Workbooks.Open (@"C:\Users\v-mapall\source\repos\ExcelCustomApps\AppServicesForExcel\Sample Data\Sample Data2.xlsx"); Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1]; Excel.Range xlRange = xlWorksheet.UsedRange; int rowCount = xlRange.Rows.Count; int colCount = xlRange.Columns.Count; //for (int i = 1; i <= rowCount; i++) for (int i = 1; i <= 2; i++) { for (int j = 1; j <= colCount; j++) { Console.WriteLine(xlRange.Cells[i, j].Value2.ToString()); xlWorkSheet.Cells[i, j] = xlRange.Cells[i, j] .Value2.ToString(); } } //Console.ReadLine(); Excel.Range chartRange; Excel.ChartObjects xlCharts = (Excel.ChartObjects) xlWorkSheet.ChartObjects(Type.Missing); Excel.ChartObject myChart = (Excel.ChartObject) xlCharts.Add(10, 80, 300, 250); Excel.Chart chartPage = myChart.Chart; chartRange = xlWorkSheet.get_Range("A1", "R22"); chartPage.SetSourceData(chartRange, misValue); chartPage.ChartType = Excel.XlChartType.xlColumnClustered; // Export chart as picture file chartPage.Export(@"C:\Users\v-mapall\source\repos\ExcelCustomApps\AppServicesForExcel\Sample Data\EmployeeExportData.pdf", "PDF", misValue); xlWorkBook.SaveAs("EmployeeExportData.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); DeallocateObject(xlWorkSheet); DeallocateObject(xlWorkBook); DeallocateObject(xlApp); DeallocateObject(xlApp1); }