/// <summary> /// FUNCTION FOR EXPORT TO EXCEL /// </summary> /// <param name="dataTable"></param> /// <param name="worksheetName"></param> /// <param name="saveAsLocation"></param> /// <returns></returns> public bool WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType, string progtype, string user, int No_of_individual_participants) { Microsoft.Office.Interop.Excel.Application excel; Microsoft.Office.Interop.Excel.Workbook excelworkBook; Microsoft.Office.Interop.Excel.Worksheet excelSheet; Microsoft.Office.Interop.Excel.Range excelCellrange; try { // Start Excel and get Application object. excel = new Microsoft.Office.Interop.Excel.Application(); // for making Excel visible excel.Visible = true; excel.DisplayAlerts = true; // Creation a new Workbook excelworkBook = excel.Workbooks.Add(Type.Missing); // Workk sheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet; excelSheet.Name = worksheetName; excelSheet.Cells[1, 1] = "Report Name - " + ReporType; excelSheet.Cells[2, 1] = "Date of Report Generation : " + General_methods.get_current_date(); excelSheet.Cells[3, 1] = "Time of Report Generation :" + General_methods.get_current_time(); excelSheet.Cells[4, 1] = "Report Created By :" + user; excelSheet.Cells[5, 1] = "Program Type :" + progtype; // loop through each row and add values to our sheet int rowcount = 6; foreach (DataRow datarow in dataTable.Rows) { //adding one to rowcount rowcount += 1; for (int i = 1; i <= dataTable.Columns.Count; i++) { // on the first iteration we add the column headers if (rowcount == 7) { excelSheet.Cells[6, i] = dataTable.Columns[i - 1].ColumnName; excelSheet.Cells.Font.Color = System.Drawing.Color.Black; } excelSheet.Cells[rowcount, i] = datarow[i - 1].ToString(); //for alternate rows if (rowcount > 7) { if (i == dataTable.Columns.Count) { if (rowcount % 4 == 0) { excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]]; FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black, false); } } } } } // now we resize the columns excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]]; excelCellrange.EntireColumn.AutoFit(); Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders; border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border.Weight = 2d; excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[5, dataTable.Columns.Count + 4]]; FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true); excelSheet.Cells[1, 1].Font.Size = 20; Microsoft.Office.Interop.Excel.Range chartRange; Microsoft.Office.Interop.Excel.ChartObjects xlCharts = (Microsoft.Office.Interop.Excel.ChartObjects)excelSheet.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; chartRange = excelSheet.Range[excelSheet.Cells[7, 1], excelSheet.Cells[rowcount, dataTable.Columns.Count]]; chartPage.SetSourceData(chartRange); chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered; return(true); } catch (Exception ex) { MessageBox.Show(ex.Message); return(false); } finally { excelSheet = null; excelCellrange = null; excelworkBook = null; } }
/* * @brief Btn_RdExcel_Click * @note Test1.xlsx を読み込み、折れ線グラフを作成して、保存後、 * Excelを起動して表示 */ private void Btn_RdExcel_Click(object sender, EventArgs e) { Microsoft.Office.Interop.Excel.Workbooks objBooks; Microsoft.Office.Interop.Excel.Application objApp = null; Microsoft.Office.Interop.Excel._Workbook objBook = null; try { // 読み込み objApp = new Microsoft.Office.Interop.Excel.Application(); objBooks = objApp.Workbooks; objBook = objBooks.Open("C:\\TEMP\\Test1.xlsx"); // グラフを書く Microsoft.Office.Interop.Excel.Worksheet thisWorksheet; thisWorksheet = objBook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet; Microsoft.Office.Interop.Excel.ChartObjects charts = (Microsoft.Office.Interop.Excel.ChartObjects)thisWorksheet.ChartObjects(Type.Missing); // チャート作成(x = 100, y = 100, 幅500 高さ 300) Microsoft.Office.Interop.Excel.ChartObject chartObj = charts.Add(100, 100, 500, 300); Microsoft.Office.Interop.Excel.Chart chart = chartObj.Chart; chart.HasTitle = true; chart.ChartTitle.Text = "LineMarker"; // データをセット. Microsoft.Office.Interop.Excel.Range chartRange = thisWorksheet.get_Range("A1", "D5"); chart.SetSourceData(chartRange, Type.Missing); // 折れ線グラフのチャート指定 // 参考 → http://home.att.ne.jp/zeta/gen/excel/c04p63.htm // chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlLineMarkers; // 折れ線指定 chart.PlotBy = Microsoft.Office.Interop.Excel.XlRowCol.xlColumns; // グラフのデータ系列を列方向 // ファイル保存 //objBook.SaveAs("C:\\TEMP\\Test1.xlsx"); objBook.Save(); // クローズ処理 objBook.Close(); objBooks.Close(); objApp.Quit(); // 作成した Excel 起動 System.Diagnostics.Process p = System.Diagnostics.Process.Start("C:\\TEMP\\Test1.xlsx"); } catch (Exception theException) { // エラーメッセージ出力 Console.Write(theException.ToString()); if (objBook != null) { objBook.Close(); } if (objApp != null) { objApp.Quit(); } } }
public static void generateExcel() { packages = DBConnector.getInstance().getPackages(); buckets = DBConnector.getInstance().getBuckets(); generatePackageList(); checkProcess(); generateBucketList(); /******************** create a workbook *************************/ excel = new Microsoft.Office.Interop.Excel.Application(); excel.Visible = false; excel.DisplayAlerts = false; excelworkBook = excel.Workbooks.Add(Type.Missing); /********************* create new sheet (Activity List) ***************************/ excelSheetAll = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet; excelSheetAll.Name = "Activity List"; int row = 1; int tb1_start_x = row; int tb1_start_y = 1; excelSheetAll.Cells[row, 1] = "Process Name"; excelSheetAll.Cells[row, 2] = "Duration"; excelSheetAll.Cells[row, 3] = "Main Window Title"; row++; foreach (KeyValuePair<string, Activity> pair in activityList) { excelSheetAll.Cells[row, 1] = pair.Value.processName; excelSheetAll.Cells[row, 2] = pair.Value.duration.ToString("g"); excelSheetAll.Cells[row, 3] = pair.Key; row++; } int tb1_end_x = row - 1; int tb1_end_y = 3; excelCellrange = excelSheetAll.Range[excelSheetAll.Cells[tb1_start_x, tb1_start_y], excelSheetAll.Cells[tb1_end_x, tb1_end_y]]; excelCellrange.NumberFormat = "hh:mm:ss.000"; excelCellrange.EntireColumn.AutoFit(); Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders; border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border.Weight = 2d; excelCellrange = excelSheetAll.Range[excelSheetAll.Cells[tb1_start_x, tb1_start_y], excelSheetAll.Cells[tb1_start_x, tb1_end_y]]; FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true); /*************************** create new sheet (Packaged Activity List) ****************************/ excelSheetPackaged = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.Worksheets.Add(); excelSheetPackaged.Name = "Packaged Activity List"; row = 1; int tb2_start_x = row; int tb2_start_y = 1; excelSheetPackaged.Cells[row, 1] = "Package Name"; excelSheetPackaged.Cells[row, 2] = "Duration"; row++; foreach (KeyValuePair<string, TimeSpan> pair in packagedList) { excelSheetPackaged.Cells[row, 1] = pair.Key; excelSheetPackaged.Cells[row, 2] = pair.Value.ToString("g"); row++; } int tb2_end_x = row - 1; int tb2_end_y = 2; excelCellrange = excelSheetPackaged.Range[excelSheetPackaged.Cells[tb2_start_x, tb2_start_y], excelSheetPackaged.Cells[tb2_end_x, tb2_end_y]]; excelCellrange.NumberFormat = "hh:mm:ss.000"; excelCellrange.EntireColumn.AutoFit(); border = excelCellrange.Borders; border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border.Weight = 2d; excelCellrange = excelSheetPackaged.Range[excelSheetPackaged.Cells[tb2_start_x, tb2_start_y], excelSheetPackaged.Cells[tb2_start_x, tb2_end_y]]; FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true); chartObjects = (Microsoft.Office.Interop.Excel.ChartObjects)excelSheetPackaged.ChartObjects(Type.Missing); chartObject = (Microsoft.Office.Interop.Excel.ChartObject)chartObjects.Add(220, 0, 400, 300); chart = chartObject.Chart; chart.HasTitle = true; chart.ChartTitle.Text = "Packaged Activity List"; chartRange = excelSheetPackaged.get_Range("A" + tb2_start_x, "B" + tb2_end_x); chart.SetSourceData(chartRange, System.Reflection.Missing.Value); chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlPie; chartObjects = (Microsoft.Office.Interop.Excel.ChartObjects)excelSheetPackaged.ChartObjects(Type.Missing); chartObject = (Microsoft.Office.Interop.Excel.ChartObject)chartObjects.Add(220, 320, 400, 300); chart = chartObject.Chart; chart.HasTitle = true; chart.ChartTitle.Text = "Packaged Activity List"; chartRange = excelSheetPackaged.get_Range("A" + tb2_start_x, "B" + tb2_end_x); chart.SetSourceData(chartRange, System.Reflection.Missing.Value); chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered; /************************* create new sheet (Bucketed Activity List) ******************************/ excelSheetBucketed = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.Worksheets.Add(); excelSheetBucketed.Name = "Bucketed Activity List"; row = 1; int tb3_start_x = row; int tb3_start_y = 1; excelSheetBucketed.Cells[row, 1] = "Bucket Name"; excelSheetBucketed.Cells[row, 2] = "Duration"; row++; foreach (KeyValuePair<string, TimeSpan> pair in bucketedList) { excelSheetBucketed.Cells[row, 1] = pair.Key; excelSheetBucketed.Cells[row, 2] = pair.Value.ToString("g"); row++; } int tb3_end_x = row - 1; int tb3_end_y = 2; excelCellrange = excelSheetBucketed.Range[excelSheetBucketed.Cells[tb3_start_x, tb3_start_y], excelSheetBucketed.Cells[tb3_end_x, tb3_end_y]]; excelCellrange.NumberFormat = "hh:mm:ss.000"; excelCellrange.EntireColumn.AutoFit(); border = excelCellrange.Borders; border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border.Weight = 2d; excelCellrange = excelSheetBucketed.Range[excelSheetBucketed.Cells[tb3_start_x, tb3_start_y], excelSheetBucketed.Cells[tb3_start_x, tb3_end_y]]; FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true); chartObjects = (Microsoft.Office.Interop.Excel.ChartObjects)excelSheetBucketed.ChartObjects(Type.Missing); chartObject = (Microsoft.Office.Interop.Excel.ChartObject)chartObjects.Add(220, 0, 400, 300); chart = chartObject.Chart; chart.HasTitle = true; chart.ChartTitle.Text = "Buckted Activity List"; chartRange = excelSheetBucketed.get_Range("A" + tb3_start_x, "B" + tb3_end_x); chart.SetSourceData(chartRange, System.Reflection.Missing.Value); chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlPie; chartObjects = (Microsoft.Office.Interop.Excel.ChartObjects)excelSheetBucketed.ChartObjects(Type.Missing); chartObject = (Microsoft.Office.Interop.Excel.ChartObject)chartObjects.Add(220, 320, 400, 300); chart = chartObject.Chart; chart.HasTitle = true; chart.ChartTitle.Text = "Buckted Activity List"; chartRange = excelSheetBucketed.get_Range("A" + tb3_start_x, "B" + tb3_end_x); chart.SetSourceData(chartRange, System.Reflection.Missing.Value); chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered; /*************** save excel *******************/ //UserPrincipal.Current.DisplayName String filePath = "C:\\Users\\" + Environment.UserName + "\\Desktop\\ActivityList-" + Environment.UserName + "-" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ".xlsx"; excelworkBook.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, true, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //excelworkBook.SaveAs(filePath); excelworkBook.Close(); excel.Quit(); Console.WriteLine("-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------"); Console.WriteLine("Export to Excel"); Thread.Sleep(1000); System.Diagnostics.Process.Start(filePath); }
public static void ExportFile(Dictionary <AttributeTypeCode, List <entityParam> > entityParam, EntityInfo entityInfo) { SaveFileDialog sfd = null; DataColumns[] fromatedList = FormatDataForExport(entityParam); String[] columnsHeaderName = new string[] { "Display Name", "Schema Name", "Type", "Target", "Managed/Unmanaged", "IsAuditable", "IsSearchable", "Required Level", "Introduced Version", "CreatedOn", "Percentage Of Use" }; int headerIndex = 9; int lineIndex = headerIndex + 1; if (fromatedList.Length > 0) { sfd = new SaveFileDialog(); sfd.Filter = "Excel (.xlsx)| *.xlsx;*.xls;"; sfd.FileName = entityInfo.entityName + "_EntityKPIsExport_" + DateTime.Now.ToShortDateString().Replace('/', '-') + ".xlsx"; bool fileError = false; if (sfd.ShowDialog() == DialogResult.OK) { if (File.Exists(sfd.FileName)) { try { File.Delete(sfd.FileName); } catch (IOException ex) { fileError = true; MessageBox.Show("It wasn't possible to write the data to the disk." + ex.Message); } } if (!fileError) { try { Microsoft.Office.Interop.Excel._Application XcelApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel._Workbook workbook = XcelApp.Workbooks.Add(Type.Missing); Microsoft.Office.Interop.Excel._Worksheet worksheet = null; #region DataGrid worksheet = workbook.ActiveSheet; if (entityInfo.entityName.Length > 21) { worksheet.Name = entityInfo.entityName.Substring(0, 21) + "_MetaData"; } else { worksheet.Name = entityInfo.entityName + "_MetaData"; } if (entityInfo != null) { for (int i = 1; i <= 6; i++) { worksheet.Cells[i, 1].Font.Bold = true; worksheet.Cells[i, 1].Interior.Color = Color.Wheat; worksheet.Cells[i, 1].Font.Size = 12; } worksheet.Cells[1, 1] = "Entity Display Name"; worksheet.Cells[1, 2] = entityInfo.entityName; worksheet.Cells[2, 1] = "Entity Technical Name"; worksheet.Cells[2, 2] = entityInfo.entityTechnicalName; worksheet.Cells[3, 1] = "CreatedOn"; worksheet.Cells[3, 2] = entityInfo.entityDateOfCreation != DateTime.MinValue ? entityInfo.entityDateOfCreation.ToShortDateString() : String.Empty; worksheet.Cells[4, 1] = "Number Of Fields"; worksheet.Cells[4, 2] = entityInfo.entityFieldsCount; worksheet.Cells[5, 1] = "Number Of Records"; worksheet.Cells[5, 2] = entityInfo.entityRecordsCount; worksheet.Cells[6, 1] = "Entity Fields Volume Usage"; worksheet.Cells[6, 2] = ((entityInfo.entityTotalUseOfColumns * 100) / entityInfo.entityDefaultColumnSize).ToString("0.##\\%"); } for (int i = 1; i < columnsHeaderName.Length + 1; i++) { worksheet.Cells[headerIndex, i] = columnsHeaderName[i - 1]; worksheet.Cells[headerIndex, i].Font.NAME = "Calibri"; worksheet.Cells[headerIndex, i].Font.Bold = true; worksheet.Cells[headerIndex, i].Interior.Color = Color.Wheat; worksheet.Cells[headerIndex, i].Font.Size = 12; } for (int i = 0; i < fromatedList.Length; i++) { worksheet.Cells[i + lineIndex, 1] = fromatedList[i].displayName; worksheet.Cells[i + lineIndex, 2] = fromatedList[i].fieldName; worksheet.Cells[i + lineIndex, 3] = fromatedList[i].fieldType; worksheet.Cells[i + lineIndex, 4] = fromatedList[i].target; worksheet.Cells[i + lineIndex, 5] = fromatedList[i].isManaged; worksheet.Cells[i + lineIndex, 6] = fromatedList[i].isAuditable; worksheet.Cells[i + lineIndex, 7] = fromatedList[i].isSearchable;; worksheet.Cells[i + lineIndex, 8] = fromatedList[i].requiredLevel;; worksheet.Cells[i + lineIndex, 9] = fromatedList[i].introducedVersion; worksheet.Cells[i + lineIndex, 10] = fromatedList[i].dateOfCreation != DateTime.MinValue ? fromatedList[i].dateOfCreation.ToShortDateString() : String.Empty; worksheet.Cells[i + lineIndex, 11] = fromatedList[i].percentageOfUse.Replace(",", "."); if (fromatedList[i].target == String.Empty) { worksheet.Cells[i + lineIndex, 4].Interior.Color = Color.Gainsboro; } } worksheet.Columns.AutoFit(); #endregion var xlSheets = workbook.Sheets as Microsoft.Office.Interop.Excel.Sheets; var xlNewSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(Type.Missing, xlSheets[1], Type.Missing, Type.Missing); xlNewSheet.Name = "Charts"; #region chartManagedUnmanaged //add data xlNewSheet.Cells[2, 2] = "Managed"; xlNewSheet.Cells[2, 2].Font.Color = Color.White; xlNewSheet.Cells[2, 3] = entityInfo.managedFieldsCount; xlNewSheet.Cells[2, 3].Font.Color = Color.White; xlNewSheet.Cells[3, 2] = "Unmanaged"; xlNewSheet.Cells[3, 2].Font.Color = Color.White; xlNewSheet.Cells[3, 3] = entityInfo.unmanagedFieldsCount; xlNewSheet.Cells[3, 3].Font.Color = Color.White; Microsoft.Office.Interop.Excel.Range chartRange; Microsoft.Office.Interop.Excel.ChartObjects xlCharts = (Microsoft.Office.Interop.Excel.ChartObjects)xlNewSheet.ChartObjects(Type.Missing); Microsoft.Office.Interop.Excel.ChartObject myChart = (Microsoft.Office.Interop.Excel.ChartObject)xlCharts.Add(10, 10, 300, 250); Microsoft.Office.Interop.Excel.Chart chartPage = myChart.Chart; chartPage.HasTitle = true; chartPage.ChartTitle.Text = @"Managed\Unmanaged Fields"; chartRange = xlNewSheet.get_Range("B2", "C3"); chartPage.SetSourceData(chartRange, System.Reflection.Missing.Value); chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlDoughnut; #endregion #region EntityFieldsCreated //add data xlNewSheet.Cells[2, 10] = "Available Fields To Create"; xlNewSheet.Cells[2, 10].Font.Color = Color.White; xlNewSheet.Cells[2, 11] = entityInfo.entityDefaultColumnSize - entityInfo.entityTotalUseOfColumns; xlNewSheet.Cells[2, 11].Font.Color = Color.White; xlNewSheet.Cells[3, 10] = "Created Fields"; xlNewSheet.Cells[3, 10].Font.Color = Color.White; xlNewSheet.Cells[3, 11] = entityInfo.entityTotalUseOfColumns; xlNewSheet.Cells[3, 11].Font.Color = Color.White; Microsoft.Office.Interop.Excel.Range chartRangeTotaluse; Microsoft.Office.Interop.Excel.ChartObjects xlChartsTotalUse = (Microsoft.Office.Interop.Excel.ChartObjects)xlNewSheet.ChartObjects(Type.Missing); Microsoft.Office.Interop.Excel.ChartObject totalUseChartChart = (Microsoft.Office.Interop.Excel.ChartObject)xlChartsTotalUse.Add(510, 10, 300, 250); Microsoft.Office.Interop.Excel.Chart chartPageTotalUse = totalUseChartChart.Chart; chartPageTotalUse.HasTitle = true; chartPageTotalUse.ChartTitle.Text = @"Entity Fields Created"; chartRangeTotaluse = xlNewSheet.get_Range("J2", "K3"); chartPageTotalUse.SetSourceData(chartRangeTotaluse, System.Reflection.Missing.Value); chartPageTotalUse.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlDoughnut; #endregion #region CustomStandar //add data xlNewSheet.Cells[2, 20] = "Standard Fields"; xlNewSheet.Cells[2, 20].Font.Color = Color.White; xlNewSheet.Cells[2, 21] = entityInfo.entityStandardFieldsCount; xlNewSheet.Cells[2, 21].Font.Color = Color.White; xlNewSheet.Cells[3, 20] = "Custom Fields"; xlNewSheet.Cells[3, 20].Font.Color = Color.White; xlNewSheet.Cells[3, 21] = entityInfo.entityCustomFieldsCount; xlNewSheet.Cells[3, 21].Font.Color = Color.White; Microsoft.Office.Interop.Excel.Range chartRangeCustomStandard; Microsoft.Office.Interop.Excel.ChartObjects xlChartsCustomStandard = (Microsoft.Office.Interop.Excel.ChartObjects)xlNewSheet.ChartObjects(Type.Missing); Microsoft.Office.Interop.Excel.ChartObject customStandardChart = (Microsoft.Office.Interop.Excel.ChartObject)xlChartsCustomStandard.Add(1010, 10, 300, 250); Microsoft.Office.Interop.Excel.Chart chartPageCustomStandard = customStandardChart.Chart; chartPageCustomStandard.HasTitle = true; chartPageCustomStandard.ChartTitle.Text = @"Custom\Standard Fields"; chartRangeCustomStandard = xlNewSheet.get_Range("T2", "U3"); chartPageCustomStandard.SetSourceData(chartRangeCustomStandard, System.Reflection.Missing.Value); chartPageCustomStandard.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlDoughnut; #endregion #region FieldsType //add data int indicator = 0; foreach (var item in entityParam) { xlNewSheet.Cells[indicator + 21, 10] = item.Key.ToString(); xlNewSheet.Cells[indicator + 21, 10].Font.Color = Color.White; xlNewSheet.Cells[indicator + 21, 11] = item.Value.Count; xlNewSheet.Cells[indicator + 21, 11].Font.Color = Color.White; indicator++; } Microsoft.Office.Interop.Excel.Range chartRangeFieldType; Microsoft.Office.Interop.Excel.ChartObjects xlChartsFieldTypes = (Microsoft.Office.Interop.Excel.ChartObjects)xlNewSheet.ChartObjects(Type.Missing); Microsoft.Office.Interop.Excel.ChartObject fieldTypes = (Microsoft.Office.Interop.Excel.ChartObject)xlCharts.Add(485, 270, 350, 300); Microsoft.Office.Interop.Excel.Chart chartPageFieldTypes = fieldTypes.Chart; chartPageFieldTypes.HasTitle = true; chartPageFieldTypes.ChartTitle.Text = @"Entity Fields Types"; chartRangeFieldType = xlNewSheet.get_Range("J21", ("K" + (21 + (indicator - 1))).ToString()); chartPageFieldTypes.SetSourceData(chartRangeFieldType, System.Reflection.Missing.Value); chartPageFieldTypes.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlDoughnut; #endregion Microsoft.Office.Interop.Excel.Worksheet sheet = workbook.Worksheets[1]; sheet.Activate(); workbook.SaveAs(sfd.FileName); XcelApp.Quit(); ReleaseObject(worksheet); ReleaseObject(xlNewSheet); ReleaseObject(workbook); ReleaseObject(XcelApp); if (MessageBox.Show("Would you like to open it?", "Information", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) { Process.Start(sfd.FileName); } } catch (Exception ex) { MessageBox.Show("Error :" + ex.Message); } } } } }
private void buttonExportarExcel_Click(object sender, EventArgs e) { //FrmGrafico grafico = new FrmGrafico(frecuenciasObservada, frecuenciaEsperada); //Chart chart1 = grafico.devolverGrafico(); //grafico.Dispose(); //chart1.SaveImage(".\\hola.png", ChartImageFormat.Png); // creating Excel Application Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application(); // creating new WorkBook within Excel application Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing); // creating new Excelsheet in workbook Microsoft.Office.Interop.Excel._Worksheet worksheet = null; // see the excel sheet behind the program app.Visible = true; // get the reference of first sheet. By default its name is Sheet1. // store its reference to worksheet worksheet = workbook.Sheets["Hoja1"]; worksheet = workbook.ActiveSheet; // changing the name of active sheet worksheet.Name = "Lista Aleatoria"; // storing header part in Excel for (int i = 1; i < this.dgvMuestra.Columns.Count + 1; i++) { worksheet.Cells[1, i] = this.dgvMuestra.Columns[i - 1].HeaderText; } // storing Each row and column value to excel sheet for (int i = 0; i < this.dgvMuestra.Rows.Count - 1; i++) { for (int j = 0; j < this.dgvMuestra.Columns.Count; j++) { worksheet.Cells[i + 2, j + 1] = this.dgvMuestra.Rows[i].Cells[j].Value.ToString(); } } worksheet.Cells[1, 5] = "Observada"; worksheet.Cells[1, 6] = "Esperada"; worksheet.Cells[2, 4] = "Media"; worksheet.Cells[3, 4] = "Varianza"; worksheet.Cells[2, 5] = this.labelMediaObservada.Text; worksheet.Cells[2, 6] = this.labelVarianzaObservada.Text; worksheet.Cells[3, 5] = "0.5"; worksheet.Cells[3, 6] = "0.0833"; worksheet.Cells[2, 9] = "Estadístico de Prueba"; worksheet.Cells[2, 10] = this.labelChiCuadrado.Text; //worksheet.Columns.Width = 100; for (int i = 1; i < this.dgvFrecuencia.Columns.Count + 1; i++) { worksheet.Cells[5, i + 3] = this.dgvFrecuencia.Columns[i - 1].HeaderText; } for (int i = 0; i < this.dgvFrecuencia.Rows.Count - 1; i++) { for (int j = 0; j < this.dgvFrecuencia.Columns.Count; j++) { worksheet.Cells[i + 6, j + 4] = this.dgvFrecuencia.Rows[i].Cells[j].Value.ToString(); } } 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(500, 100, 500, 300); Microsoft.Office.Interop.Excel.Chart chartPage = myChart.Chart; int cantIntervalos = dgvFrecuencia.Rows.Count; chartRange = worksheet.get_Range("F5", "G" + (cantIntervalos + 5).ToString()); chartPage.SetSourceData(chartRange, System.Reflection.Missing.Value); chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered; //myChart.TopLeftCell = worksheet.Cells[3, 10]; //worksheet.Shapes.AddPicture(".\\hola.jpg", Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 50, 50, 300, 45); // save the application //workbook.SaveAs(".\\output"+documentoExcel.ToString()+".xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //documentoExcel += 1; // Exit from the application //app.Quit(); }