public static void Write(List <SimulationData> excelData) { foreach (var process in System.Diagnostics.Process.GetProcessesByName("Excel")) { process.Kill(); } oXL = new Microsoft.Office.Interop.Excel.Application { Visible = true }; //Add new Excel workbook oWB = oXL.Workbooks.Add(""); oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet; oSheet.EnableSelection = Microsoft.Office.Interop.Excel.XlEnableSelection.xlNoSelection; var i = 1; foreach (var data in excelData) { //Populate excel worksheet with data oSheet.Cells[i, 1] = data.Time; oSheet.Cells[i, 2] = data.Buffer; oSheet.Cells[i, 3] = data.BandWidth; oSheet.Cells[i, 4] = data.BitRate; i += 1; } //Draw line chart based on data Microsoft.Office.Interop.Excel.ChartObjects xlCharts = (Microsoft.Office.Interop.Excel.ChartObjects)oSheet.ChartObjects(Type.Missing); Microsoft.Office.Interop.Excel.ChartObject myChart = xlCharts.Add(10, 80, 300, 250); Microsoft.Office.Interop.Excel.Chart chartPage = myChart.Chart; chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlLine; var seriesCollection = (Microsoft.Office.Interop.Excel.SeriesCollection)chartPage.SeriesCollection(); Microsoft.Office.Interop.Excel.Series series1 = seriesCollection.NewSeries(); series1.Name = "Długość Bufora"; series1.XValues = oSheet.get_Range("A1", "A" + i); series1.Values = oSheet.get_Range("B1", "B" + i); Microsoft.Office.Interop.Excel.Series series2 = seriesCollection.NewSeries(); series2.Name = "Pasmo"; series2.XValues = oSheet.get_Range("A1", "A" + i); series2.Values = oSheet.get_Range("C1", "C" + i); Microsoft.Office.Interop.Excel.Series series3 = seriesCollection.NewSeries(); series3.Name = "Przeplywnosc fragmentu"; series3.XValues = oSheet.get_Range("A1", "A" + i); series3.Values = oSheet.get_Range("D1", "D" + i); }
public void Chart_Hinzufuegen(string DiagrammTitle, string TextXAchse, string TextYAchse, Microsoft.Office.Interop.Excel.XlChartType ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlXYScatter) { this.CanClose = true; this.XlWorkBook = XlApp.Workbooks.Add(); this.XlChart = XlWorkBook.Charts.Add(); this.XlChart.HasTitle = true; this.XlChart.ChartTitle.Text = DiagrammTitle; this.XlChart.ChartType = ChartType; this.XlChart.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary).HasTitle = true; this.XlChart.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary).AxisTitle.Text = TextYAchse; this.XlChart.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlCategory, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary).HasTitle = true; this.XlChart.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlCategory, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary).AxisTitle.Text = TextXAchse; }
public void Excel_Image_Test() { Microsoft.Office.Interop.Excel._Application xlApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook workbook = xlApp.Workbooks.Add(); Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.Worksheets.get_Item(1); Microsoft.Office.Interop.Excel.ChartObjects xlCharts = (Microsoft.Office.Interop.Excel.ChartObjects)worksheet.ChartObjects(); Microsoft.Office.Interop.Excel.ChartObject chart = xlCharts.Add(100, 100, 600, 100); Microsoft.Office.Interop.Excel.Chart chartPage = chart.Chart; chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered; Microsoft.Office.Interop.Excel.SeriesCollection seriesCollection = chartPage.SeriesCollection(); Microsoft.Office.Interop.Excel.Series series = seriesCollection.NewSeries(); series.Values = new double[] { 1d, 3d, 2d, 5d, 6d, 7d }; series.XValues = new double[] { 1d, 2d, 3d, 4d, 7d, 19d }; chartPage.ApplyLayout(9, Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered); chartPage.ChartTitle.Select(); xlApp.Selection.Delete(); chartPage.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlValue).AxisTitle.Select(); xlApp.Selection.Delete(); chartPage.Legend.Select(); xlApp.Selection.Delete(); chartPage.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlCategory, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary).AxisTitle.Text = TaskRunnerServiceRes.NumberOfRunsUsedByYear; chartPage.Export(@"C:\Users\leblancc\Desktop\TestHTML\test.png", "PNG", false); if (workbook != null) { workbook.Close(false); } if (xlApp != null) { xlApp.Quit(); } }
private void tsbtn_Build_Click(object sender, EventArgs e) { CloseProcess("EXCEL"); //关闭所有Excel进程 object missing = System.Reflection.Missing.Value; //定义object缺省值 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); //实例化Excel对象 //打开Excel文件 Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Open(tstxt_Excel.Text, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing); Microsoft.Office.Interop.Excel.Worksheet worksheet; //声明工作表 worksheet = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[tscbox_Sheet.Text]); //获取选择的工作表 Microsoft.Office.Interop.Excel.Range searchRange = worksheet.get_Range("A1", "E1"); //定义标题范围 object[] P_obj_Items = { "编程词典", "VC编程词典", "JAVA编程词典", "ASP.NET编程词典", "C#编程词典" }; searchRange.set_Value(missing, P_obj_Items); //绘制标题 searchRange.Font.Bold = true; //设置字体加粗 searchRange.Font.Name = "宋体"; //设置字体样式 searchRange.Font.Size = 10; //设置字体大小 searchRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //设置标题对齐方式 //获得要生成图表的数据 for (int i = 0; i < 13; i++) { worksheet.Cells[2 + i, 1] = i; worksheet.Cells[2 + i, 2] = i + 1; worksheet.Cells[2 + i, 3] = i + 2; worksheet.Cells[2 + i, 4] = i + 3; worksheet.Cells[2 + i, 5] = i + 4; } //实例化Excel绘图对象 Microsoft.Office.Interop.Excel.Chart chart = (Microsoft.Office.Interop.Excel.Chart)workbook.Charts.Add(missing, missing, missing, missing); Microsoft.Office.Interop.Excel.Range chartRange = worksheet.get_Range("A1:A14", "B1:E14");//定义绘制图表范围 //在指定范围绘制图表 chart.ChartWizard(chartRange, Microsoft.Office.Interop.Excel.XlChartType.xl3DColumn, missing, Microsoft.Office.Interop.Excel.XlRowCol.xlColumns, 1, 1, true, "编程词典销量分析", "月份", "销量", missing); excel.DisplayAlerts = false; //设置保存Excel时不显示对话框 workbook.Save(); //保存工作簿 workbook.Close(false, missing, missing); //关闭工作簿 CloseProcess("EXCEL"); //关闭所有Excel进程 WBrowser_Excel.Navigate(tstxt_Excel.Text); //在窗体中重新显示Excel文件内容 }
void _ExcelApplication_WorkbookNewChart(Microsoft.Office.Interop.Excel.Workbook Wb, Microsoft.Office.Interop.Excel.Chart Ch) { DisplayInWatchWindow(WorkbookNewChart++, System.Reflection.MethodInfo.GetCurrentMethod().Name); }
/// <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; } }
private bool GenerateHTMLSUBSECTOR_MWQM_SITES_NUMBER_OF_SITES_BY_YEAR(StringBuilder sbTemp) { int Percent = 10; string NotUsed = ""; _TaskRunnerBaseService.SendPercentToDB(_TaskRunnerBaseService._BWObj.appTaskModel.AppTaskID, Percent); _TaskRunnerBaseService.SendStatusTextToDB(_TaskRunnerBaseService.GetTextLanguageFormat1List("Creating_", ReportGenerateObjectsKeywordEnum.SUBSECTOR_MWQM_SITES_NUMBER_OF_SITES_BY_YEAR.ToString())); List <string> ParamValueList = Parameters.Split("|||".ToCharArray(), StringSplitOptions.RemoveEmptyEntries).ToList(); // TVItemID and Year already loaded TVItemModel tvItemModelSubsector = _TVItemService.GetTVItemModelWithTVItemIDDB(TVItemID); if (!string.IsNullOrWhiteSpace(tvItemModelSubsector.Error)) { NotUsed = string.Format(TaskRunnerServiceRes.CouldNotFind_With_Equal_, TaskRunnerServiceRes.TVItem, TaskRunnerServiceRes.TVItemID, TVItemID.ToString()); _TaskRunnerBaseService._BWObj.TextLanguageList = _TaskRunnerBaseService.GetTextLanguageFormat3List("CouldNotFind_With_Equal_", TaskRunnerServiceRes.TVItem, TaskRunnerServiceRes.TVItemID, TVItemID.ToString()); return(false); } string ServerPath = _TVFileService.GetServerFilePath(tvItemModelSubsector.TVItemID); _TaskRunnerBaseService.SendPercentToDB(_TaskRunnerBaseService._BWObj.appTaskModel.AppTaskID, 5); List <TVItemModel> tvItemModelListMWQMSites = _TVItemService.GetChildrenTVItemModelListWithTVItemIDAndTVTypeDB(tvItemModelSubsector.TVItemID, TVTypeEnum.MWQMSite).Where(c => c.IsActive == true).ToList(); List <MWQMSiteModel> mwqmSiteModelList = _MWQMSiteService.GetMWQMSiteModelListWithSubsectorTVItemIDDB(TVItemID); List <MWQMRunModel> mwqmRunModelList = _MWQMRunService.GetMWQMRunModelListWithSubsectorTVItemIDDB(TVItemID); List <MWQMSampleModel> mwqmSampleModelList = _MWQMSampleService.GetMWQMSampleModelListWithSubsectorTVItemIDDB(TVItemID); //------------------------------------------------------------------------------ // doing number of sites by year //------------------------------------------------------------------------------ List <int> YearList = new List <int>(); List <int> CountPerYear = new List <int>(); int MaxYear = Math.Min(DateTime.Now.Year, Year); for (int i = MaxYear; i > 1979; i--) { YearList.Add(i); int count = (from s in mwqmSiteModelList from samp in mwqmSampleModelList where s.MWQMSiteTVItemID == samp.MWQMSiteTVItemID && samp.SampleDateTime_Local.Year == i select s.MWQMSiteTVItemID).Distinct().Count(); CountPerYear.Add(count); } Percent = 30; _TaskRunnerBaseService.SendPercentToDB(_TaskRunnerBaseService._BWObj.appTaskModel.AppTaskID, Percent); if (xlApp == null) { xlApp = new Microsoft.Office.Interop.Excel.Application(); workbook = xlApp.Workbooks.Add(); worksheet = workbook.Worksheets.get_Item(1); xlCharts = (Microsoft.Office.Interop.Excel.ChartObjects)worksheet.ChartObjects(); } Microsoft.Office.Interop.Excel.ChartObject chart = xlCharts.Add(100, 100, 600, 200); Microsoft.Office.Interop.Excel.Chart chartPage = chart.Chart; chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered; Microsoft.Office.Interop.Excel.SeriesCollection seriesCollection = chartPage.SeriesCollection(); Microsoft.Office.Interop.Excel.Series series = seriesCollection.NewSeries(); _TaskRunnerBaseService.SendPercentToDB(_TaskRunnerBaseService._BWObj.appTaskModel.AppTaskID, 40); series.XValues = YearList.ToArray(); series.Values = CountPerYear.ToArray(); chartPage.ApplyLayout(9, Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered); chartPage.ChartTitle.Select(); xlApp.Selection.Delete(); chartPage.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlValue).AxisTitle.Select(); xlApp.Selection.Delete(); chartPage.Legend.Select(); xlApp.Selection.Delete(); chartPage.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlCategory).MajorTickMark = Microsoft.Office.Interop.Excel.Constants.xlOutside; chartPage.Parent.RoundedCorners = true; chartPage.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlCategory, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary).AxisTitle.Text = TaskRunnerServiceRes.NumberOfMWQMSitesByYear; // need to save the file with a unique name under the TVItemID FileInfo fiImageNumberOfSitesByYearStat = new FileInfo(fi.DirectoryName + @"\NumberOfSitesByYearStat" + FileNameExtra + ".png"); DirectoryInfo di = new DirectoryInfo(fi.DirectoryName); if (!di.Exists) { try { di.Create(); } catch (Exception ex) { NotUsed = string.Format(TaskRunnerServiceRes.CouldNotCreateDirectory__, di.FullName, ex.Message + (ex.InnerException != null ? " Inner: " + ex.InnerException.Message : "")); _TaskRunnerBaseService._BWObj.TextLanguageList = _TaskRunnerBaseService.GetTextLanguageFormat2List("CouldNotCreateDirectory__", di.FullName, ex.Message + (ex.InnerException != null ? " Inner: " + ex.InnerException.Message : "")); return(false); } } chartPage.Export(fiImageNumberOfSitesByYearStat.FullName, "PNG", false); sbTemp.AppendLine($@"<div class=""textAlignCenter"">|||Image|FileName,{ fiImageNumberOfSitesByYearStat.FullName }|width,400|height,150|||</div>"); sbTemp.AppendLine($@"<div>|||FigureCaption| { TaskRunnerServiceRes.NumberOfMWQMSitesByYear }|||</div>"); Percent = 80; _TaskRunnerBaseService.SendPercentToDB(_TaskRunnerBaseService._BWObj.appTaskModel.AppTaskID, 80); return(true); }
private void imgBtn_Click(object sender, RibbonControlEventArgs e) { Microsoft.Office.Interop.Excel.Chart myChart = Globals.ThisAddIn.Application.ActiveChart; if (myChart == null) { MessageBox.Show("Please select a chart!", "Active Chart", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } // Copy chart from Excel myChart.ChartArea.Copy(); // Get Image object from clipboard Image image = null; if (Clipboard.GetDataObject() != null) { IDataObject data = Clipboard.GetDataObject(); if (data.GetDataPresent(DataFormats.Bitmap)) { image = (Image)data.GetData(DataFormats.Bitmap, true); } } // Ask user where to save the image file and its format SaveFileDialog mySaveFileDialog = new SaveFileDialog(); mySaveFileDialog.Filter = "Bitmap Image (*.bmp)|*.bmp|JPEG Image (*.jpeg)|*.jpeg|Png Image (*.png)|*.png|Tiff Image (*.tiff)|*.tiff"; DialogResult myDialogResult = mySaveFileDialog.ShowDialog(); string filename = null; string format = null; // Save the image file if (myDialogResult == DialogResult.OK) { filename = mySaveFileDialog.FileName; format = Path.GetExtension(filename); switch (format.ToLower()) { case ".bmp": image.Save(filename, System.Drawing.Imaging.ImageFormat.Bmp); break; case ".jpeg": image.Save(filename, System.Drawing.Imaging.ImageFormat.Jpeg); break; case ".png": image.Save(filename, System.Drawing.Imaging.ImageFormat.Png); break; case ".tiff": image.Save(filename, System.Drawing.Imaging.ImageFormat.Tiff); break; } MessageBox.Show("Exported to " + filename, "Finished Exporting", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { //MessageBox.Show("Please select a location to save the output file.", "Save location", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private void pdfBtn_Click(object sender, RibbonControlEventArgs e) { Microsoft.Office.Interop.Excel.Chart myChart = Globals.ThisAddIn.Application.ActiveChart; if (myChart == null) { MessageBox.Show("Please select a chart!", "Active Chart", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } // Getting height and width of the active chart Double chHeight = myChart.ChartArea.Height; Double chWidth = myChart.ChartArea.Width; // Openning a blank Word document dynamic app = null; dynamic doc = null; try { Type type = Type.GetTypeFromProgID("Word.Application"); app = Activator.CreateInstance(type); //app.Visible = true; doc = app.Documents.Add(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); return; } // Setting pagesize and margins doc.PageSetup.PageHeight = chHeight + 4.7; doc.PageSetup.PageWidth = chWidth + 4.7; doc.PageSetup.BottomMargin = 0; doc.PageSetup.LeftMargin = 0; doc.PageSetup.RightMargin = 0; doc.PageSetup.TopMargin = 0; // Copy chart from Excel myChart.ChartArea.Copy(); // Paste chart to Word, keeping source format doc.Range.PasteAndFormat(16); // Ask user where to save the PDF SaveFileDialog mySaveFileDialog = new SaveFileDialog(); mySaveFileDialog.Filter = "PDF document (*.pdf)|*.pdf"; DialogResult myDialogResult = mySaveFileDialog.ShowDialog(); string filename = null; // Save the PDF file if (myDialogResult == DialogResult.OK) { filename = mySaveFileDialog.FileName; doc.SaveAs2(filename, 17); MessageBox.Show("Exported to " + filename, "Finished Exporting", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { //MessageBox.Show("Please select a location to save the output file.", "Save location", MessageBoxButtons.OK, MessageBoxIcon.Error); } doc.Close(0); app.Quit(); }
public void ImportChart(Microsoft.Office.Interop.Excel.Chart eChart) { ContextManager contextMgr = new ContextManager(); contextMgr.ImportPdeChart(eChart, Wkl.MainCtrl.CommonCtrl.CommonProfile.ActiveDoc); }
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 void ImportPdeChart(Microsoft.Office.Interop.Excel.Chart eChart, Document wDoc) { PdeService.ImportPdeChart(eChart, wDoc); }
private void ExportToExcel() { try { if (InvokeRequired) { this.Invoke(new MethodInvoker(delegate { bool firstCol = true; progressBar1.Visible = true; int c = 0; foreach (var series in chart1.Series) { c += series.Points.Count; } progressBar1.Minimum = 0; progressBar1.Maximum = c; progressBar1.Value = 0; Microsoft.Office.Interop.Excel.Application xlApp; Microsoft.Office.Interop.Excel.Workbook xlWorkBook; Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Microsoft.Office.Interop.Excel.Application(); xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); Microsoft.Office.Interop.Excel.ChartObjects xlCharts = (Microsoft.Office.Interop.Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing); Microsoft.Office.Interop.Excel.ChartObject myChart = (Microsoft.Office.Interop.Excel.ChartObject)xlCharts.Add(500, 100, 500, 500); Microsoft.Office.Interop.Excel.Chart chartPage = myChart.Chart; Microsoft.Office.Interop.Excel.SeriesCollection ser = chartPage.SeriesCollection(); chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xl3DLine; int SeresNmae = 1; int XRange = 0; string W = ""; int n = 2; xlWorkSheet.Cells[1] = "Time(Sec)"; int Xwvae = 0; for (int m = 0; m < 6; m++) { if (timeSpecW[m] != 0) { xlWorkSheet.Cells[2, n] = timeSpecW[m]; n++; } } foreach (var item in chart1.Series) { int m = 0; switch (SeresNmae) { case 1: W = "(" + txtW1.Text + "nm)"; break; case 2: W = "(" + txtW2.Text + "nm)"; break; case 3: W = "(" + txtW3.Text + "nm)"; break; case 4: W = "(" + txtW4.Text + "nm)"; break; case 5: W = "(" + txtW5.Text + "nm)"; break; case 6: W = "(" + txtW6.Text + "nm)"; break; } xlWorkSheet.Cells[SeresNmae + 1] = item.Name + W; SeresNmae++; int i = 3; XRange += 2; //if (item.Name == ExperimentName) // wait = true; if (chart1.Series.Count > 1 && chart1.Series[1].Points.Count > item.Points.Count) { int k = chart1.Series[1].Points.Count - item.Points.Count; i = i + k; } for (int j = 0; j < item.Points.Count; j++) { progressBar1.Value++; if (firstCol) { xlWorkSheet.Cells[i, 1] = item.Points[j].XValue; } xlWorkSheet.Cells[i, SeresNmae] = item.Points[j].YValues; i++; } firstCol = false; Xwvae++; Microsoft.Office.Interop.Excel.Series series1 = ser.NewSeries(); series1.Name = item.Name; string colY = Number2String((SeresNmae)) + "2" + ":" + Number2String((SeresNmae)) + i.ToString(); string colX = "A2:A" + i.ToString(); series1.Values = xlWorkSheet.get_Range(colY); series1.XValues = xlWorkSheet.get_Range(colX); // wait = false; } Microsoft.Office.Interop.Excel.Range chartRange; string col1 = "1:" + chart1.Series[0].Points.Count; string col2 = chart1.Series.Count + ":" + chart1.Series[chart1.Series.Count - 1].Points.Count; //chartRange = xlWorkSheet.get_Range(col1, col2); //chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlLine; //chartPage.SetSourceData(chartRange, misValue); xlWorkBook.SaveAs(saveExcel.FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); System.Diagnostics.Process.Start(saveExcel.FileName); message("Excel file created ", true); progressBar1.Visible = false; })); } }catch (Exception ex) { MessageBox.Show(ex.ToString()); } }
/* * @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 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 static void CreateChart(List <SignalGenerator.Day> data, string symbol, string templatePath) { Console.WriteLine("Generating..."); var app = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbooks books = null; Microsoft.Office.Interop.Excel.Workbook book = null; try { books = app.Workbooks; book = books.Open(templatePath); app.Calculation = Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationManual; app.ScreenUpdating = false; Microsoft.Office.Interop.Excel.Chart sheetG = book.Charts[1]; Microsoft.Office.Interop.Excel.Worksheet sheet = book.Worksheets[1]; sheet.Activate(); var mindate = data.Min(d => d.Date); var maxdate = data.Max(d => d.Date); var lowest = data.Min(d => d.LowestValue()); var highest = data.Max(d => d.HighestValue()); var range = highest - lowest; lowest -= (range * 0.05); highest += (range * 0.05); if (lowest < 0) { lowest = 0; } var title = $"{symbol.ToUpper()}: {mindate:yyyy-MM-dd} - {maxdate:yyyy-Mm-dd}"; ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[1, 10]).Value = title; var row = 1; var isin = data[0].In; var p = 0; foreach (var day in data) { var np = row * 100 / data.Count; if (np != p) { Console.WriteLine($"{np}%"); p = np; } row++; ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[row, 1]).Value = day.Date; ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[row, 2]).Value = day.Open; ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[row, 3]).Value = day.High; ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[row, 4]).Value = day.Low; ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[row, 5]).Value = day.Close; ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[row, 6]).Value = day.BuyLine; ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[row, 7]).Value = day.SellLine; if (day.StopLoss > 0) { ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[row, 8]).Value = day.StopLoss; } if (day.In != isin) { ((Microsoft.Office.Interop.Excel.Range)sheet.Cells[row, 9]).Value = day.In ? 1 : 0; isin = day.In; } } sheetG.Activate(); Console.WriteLine("Done"); app.Calculation = Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationAutomatic; app.ScreenUpdating = true; var axis = sheetG.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary) as Microsoft.Office.Interop.Excel.Axis; lowest -= lowest % axis.MajorUnit; highest += axis.MajorUnit; highest -= highest % axis.MajorUnit; axis.MinimumScale = lowest; axis.MaximumScale = highest; app.Visible = true; Console.WriteLine("Press any key when ready"); Console.ReadKey(); } catch (Exception ex) { Console.WriteLine($"Exception: {ex.Message}"); } try { if (book != null) { book.Close(false); } if (books != null) { books.Close(); } app.Quit(); for (var i = 0; i < 10; i++) { GC.Collect(); } } catch { } }
private bool GenerateHTMLSubsectorMWQMSites(FileInfo fi, StringBuilder sbHTML, string parameters, ReportTypeModel reportTypeModel) { string NotUsed = ""; int TVItemID = 0; Random random = new Random(); string FileNameExtra = ""; for (int i = 0; i < 10; i++) { FileNameExtra = FileNameExtra + (char)random.Next(97, 122); } _TaskRunnerBaseService.SendPercentToDB(_TaskRunnerBaseService._BWObj.appTaskModel.AppTaskID, 3); if (!GetTopHTML(sbHTML)) { return(false); } List <string> ParamValueList = parameters.Split("|||".ToCharArray(), StringSplitOptions.RemoveEmptyEntries).ToList(); if (!int.TryParse(GetParameters("TVItemID", ParamValueList), out TVItemID)) { NotUsed = string.Format(TaskRunnerServiceRes.CouldNotFind__, TaskRunnerServiceRes.Parameter, TaskRunnerServiceRes.TVItemID); _TaskRunnerBaseService._BWObj.TextLanguageList = _TaskRunnerBaseService.GetTextLanguageFormat2List("CouldNotFind__", TaskRunnerServiceRes.Parameter, TaskRunnerServiceRes.TVItemID); return(false); } TVItemModel tvItemModelSubsector = _TVItemService.GetTVItemModelWithTVItemIDDB(TVItemID); if (!string.IsNullOrWhiteSpace(tvItemModelSubsector.Error)) { NotUsed = string.Format(TaskRunnerServiceRes.CouldNotFind_With_Equal_, TaskRunnerServiceRes.TVItem, TaskRunnerServiceRes.TVItemID, TVItemID.ToString()); _TaskRunnerBaseService._BWObj.TextLanguageList = _TaskRunnerBaseService.GetTextLanguageFormat3List("CouldNotFind_With_Equal_", TaskRunnerServiceRes.TVItem, TaskRunnerServiceRes.TVItemID, TVItemID.ToString()); return(false); } string ServerPath = _TVFileService.GetServerFilePath(tvItemModelSubsector.TVItemID); _TaskRunnerBaseService.SendPercentToDB(_TaskRunnerBaseService._BWObj.appTaskModel.AppTaskID, 5); List <TVItemModel> tvItemModelListMWQMSites = _TVItemService.GetChildrenTVItemModelListWithTVItemIDAndTVTypeDB(tvItemModelSubsector.TVItemID, TVTypeEnum.MWQMSite).Where(c => c.IsActive == true).ToList(); List <MWQMSiteModel> mwqmSiteModelList = _MWQMSiteService.GetMWQMSiteModelListWithSubsectorTVItemIDDB(TVItemID); List <MWQMRunModel> mwqmRunModelList = _MWQMRunService.GetMWQMRunModelListWithSubsectorTVItemIDDB(TVItemID); List <MWQMSampleModel> mwqmSampleModelList = _MWQMSampleService.GetMWQMSampleModelListWithSubsectorTVItemIDDB(TVItemID); sbHTML.AppendLine($@" <h3>{ TaskRunnerServiceRes.MWQMSiteSampleDataAvailability }</h3>"); sbHTML.AppendLine($@" <table cellpadding=""5"">"); sbHTML.AppendLine($@" <tr>"); sbHTML.AppendLine($@" <th>{ TaskRunnerServiceRes.Site }</th>"); bool FirstHit = false; for (int year = DateTime.Now.Year; year > 1975; year--) { if (year % 5 == 0) { FirstHit = true; int colSpan = 5; if (year == 1980) { colSpan = 4; } sbHTML.AppendLine($@" <th class=""textAlignLeftAndLeftBorder"" colspan=""{ colSpan }"">{ year }</th>"); } if (!FirstHit) { sbHTML.AppendLine($@" <th> </th>"); } } sbHTML.AppendLine($@" </tr>"); int countSite = 0; foreach (MWQMSiteModel mwqmSiteModel in mwqmSiteModelList) { TVItemModel tvItemModel = tvItemModelListMWQMSites.Where(c => c.TVItemID == mwqmSiteModel.MWQMSiteTVItemID).FirstOrDefault(); if (tvItemModel != null) { if (tvItemModel.IsActive) { countSite += 1; string bottomClass = ""; if (countSite % 5 == 0) { bottomClass = "bottomBorder"; } sbHTML.AppendLine($@" <tr>"); sbHTML.AppendLine($@" <td class=""{ bottomClass }"">{ mwqmSiteModel.MWQMSiteTVText }</td>"); for (int year = DateTime.Now.Year; year > 1979; year--) { string leftClass = year % 5 == 0 ? "leftBorder" : ""; bool hasSamples = mwqmSampleModelList.Where(c => c.MWQMSiteTVItemID == mwqmSiteModel.MWQMSiteTVItemID && c.SampleDateTime_Local.Year == year && c.SampleTypesText.Contains(((int)SampleTypeEnum.Routine).ToString())).Any(); if (hasSamples) { if (leftClass != "") { if (bottomClass != "") { sbHTML.AppendLine($@" <td class=""bggreenfLeftAndBottomBorder""> </td>"); } else { sbHTML.AppendLine($@" <td class=""bggreenfLeftBorder""> </td>"); } } else { if (bottomClass != "") { sbHTML.AppendLine($@" <td class=""bggreenfBottomBorder""> </td>"); } else { sbHTML.AppendLine($@" <td class=""bggreenf""> </td>"); } } } else { if (leftClass != "") { if (bottomClass != "") { sbHTML.AppendLine($@" <td class=""leftAndBottomBorder""> </td>"); } else { sbHTML.AppendLine($@" <td class=""leftBorder""> </td>"); } } else { if (bottomClass != "") { sbHTML.AppendLine($@" <td class=""bottomBorder""> </td>"); } else { sbHTML.AppendLine($@" <td> </td>"); } } } } sbHTML.AppendLine($@" </tr>"); } } } sbHTML.AppendLine($@" </table>"); sbHTML.AppendLine(@"<span>|||PageBreak|||</span>"); _TaskRunnerBaseService.SendPercentToDB(_TaskRunnerBaseService._BWObj.appTaskModel.AppTaskID, 10); // --------------------------------------------------------------------------------------------- // MWQM Sites Summary // --------------------------------------------------------------------------------------------- //------------------------------------------------------------------------------ // doing number of sites by year //------------------------------------------------------------------------------ List <int> YearList = new List <int>(); List <int> CountPerYear = new List <int>(); for (int i = 1980; i < DateTime.Now.Year + 1; i++) { YearList.Add(i); int count = (from s in mwqmSiteModelList from samp in mwqmSampleModelList where s.MWQMSiteTVItemID == samp.MWQMSiteTVItemID && samp.SampleDateTime_Local.Year == i select s.MWQMSiteTVItemID).Distinct().Count(); CountPerYear.Add(count); } Microsoft.Office.Interop.Excel._Application xlApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Workbook workbook = xlApp.Workbooks.Add(); Microsoft.Office.Interop.Excel.Worksheet worksheet = workbook.Worksheets.get_Item(1); Microsoft.Office.Interop.Excel.ChartObjects xlCharts = (Microsoft.Office.Interop.Excel.ChartObjects)worksheet.ChartObjects(); Microsoft.Office.Interop.Excel.ChartObject chart = xlCharts.Add(100, 100, 600, 200); Microsoft.Office.Interop.Excel.Chart chartPage = chart.Chart; chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered; Microsoft.Office.Interop.Excel.SeriesCollection seriesCollection = chartPage.SeriesCollection(); Microsoft.Office.Interop.Excel.Series series = seriesCollection.NewSeries(); _TaskRunnerBaseService.SendPercentToDB(_TaskRunnerBaseService._BWObj.appTaskModel.AppTaskID, 40); series.XValues = YearList.ToArray(); series.Values = CountPerYear.ToArray(); chartPage.ApplyLayout(9, Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered); chartPage.ChartTitle.Select(); xlApp.Selection.Delete(); chartPage.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlValue).AxisTitle.Select(); xlApp.Selection.Delete(); chartPage.Legend.Select(); xlApp.Selection.Delete(); //chartPage.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlCategory).TickLabelSpacing = 5; chartPage.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlCategory).MajorTickMark = Microsoft.Office.Interop.Excel.Constants.xlOutside; chartPage.Parent.RoundedCorners = true; chartPage.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlCategory, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary).AxisTitle.Text = TaskRunnerServiceRes.NumberOfMWQMSitesByYear; // need to save the file with a unique name under the TVItemID FileInfo fiImageNumberOfSitesByYearStat = new FileInfo(fi.DirectoryName + @"\NumberOfSitesByYearStat" + FileNameExtra + ".png"); DirectoryInfo di = new DirectoryInfo(fi.DirectoryName); if (!di.Exists) { try { di.Create(); } catch (Exception ex) { NotUsed = string.Format(TaskRunnerServiceRes.CouldNotCreateDirectory__, di.FullName, ex.Message + (ex.InnerException != null ? " Inner: " + ex.InnerException.Message : "")); _TaskRunnerBaseService._BWObj.TextLanguageList = _TaskRunnerBaseService.GetTextLanguageFormat2List("CouldNotCreateDirectory__", di.FullName, ex.Message + (ex.InnerException != null ? " Inner: " + ex.InnerException.Message : "")); return(false); } } chartPage.Export(fiImageNumberOfSitesByYearStat.FullName, "PNG", false); //------------------------------------------------------------------------------ // doing number of runs by year //------------------------------------------------------------------------------ YearList = new List <int>(); CountPerYear = new List <int>(); for (int i = 1980; i < DateTime.Now.Year + 1; i++) { YearList.Add(i); int count = (from r in mwqmRunModelList from samp in mwqmSampleModelList where r.MWQMRunTVItemID == samp.MWQMRunTVItemID && samp.SampleDateTime_Local.Year == i select r.MWQMRunTVItemID).Distinct().Count(); CountPerYear.Add(count); } chart = xlCharts.Add(100, 100, 600, 200); chartPage = chart.Chart; chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered; seriesCollection = chartPage.SeriesCollection(); series = seriesCollection.NewSeries(); _TaskRunnerBaseService.SendPercentToDB(_TaskRunnerBaseService._BWObj.appTaskModel.AppTaskID, 40); series.XValues = YearList.ToArray(); series.Values = CountPerYear.ToArray(); chartPage.ApplyLayout(9, Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered); chartPage.ChartTitle.Select(); xlApp.Selection.Delete(); chartPage.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlValue).AxisTitle.Select(); xlApp.Selection.Delete(); chartPage.Legend.Select(); xlApp.Selection.Delete(); //chartPage.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlCategory).TickLabelSpacing = 5; chartPage.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlCategory).MajorTickMark = Microsoft.Office.Interop.Excel.Constants.xlOutside; chartPage.Parent.RoundedCorners = true; chartPage.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlCategory, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary).AxisTitle.Text = TaskRunnerServiceRes.NumberOfMWQMRunsByYear; // need to save the file with a unique name under the TVItemID FileInfo fiImageNumberOfRunsByYearStat = new FileInfo(fi.DirectoryName + @"\NumberOfRunsByYearStat" + FileNameExtra + ".png"); di = new DirectoryInfo(fi.DirectoryName); if (!di.Exists) { try { di.Create(); } catch (Exception ex) { NotUsed = string.Format(TaskRunnerServiceRes.CouldNotCreateDirectory__, di.FullName, ex.Message + (ex.InnerException != null ? " Inner: " + ex.InnerException.Message : "")); _TaskRunnerBaseService._BWObj.TextLanguageList = _TaskRunnerBaseService.GetTextLanguageFormat2List("CouldNotCreateDirectory__", di.FullName, ex.Message + (ex.InnerException != null ? " Inner: " + ex.InnerException.Message : "")); return(false); } } chartPage.Export(fiImageNumberOfRunsByYearStat.FullName, "PNG", false); //------------------------------------------------------------------------------ // doing number of samples by year //------------------------------------------------------------------------------ YearList = new List <int>(); CountPerYear = new List <int>(); for (int i = 1980; i < DateTime.Now.Year + 1; i++) { YearList.Add(i); int count = (from samp in mwqmSampleModelList where samp.SampleDateTime_Local.Year == i select samp.MWQMSampleID).Distinct().Count(); CountPerYear.Add(count); } chart = xlCharts.Add(100, 100, 600, 200); chartPage = chart.Chart; chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered; seriesCollection = chartPage.SeriesCollection(); series = seriesCollection.NewSeries(); _TaskRunnerBaseService.SendPercentToDB(_TaskRunnerBaseService._BWObj.appTaskModel.AppTaskID, 40); series.XValues = YearList.ToArray(); series.Values = CountPerYear.ToArray(); chartPage.ApplyLayout(9, Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered); chartPage.ChartTitle.Select(); xlApp.Selection.Delete(); chartPage.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlValue).AxisTitle.Select(); xlApp.Selection.Delete(); chartPage.Legend.Select(); xlApp.Selection.Delete(); //chartPage.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlCategory).TickLabelSpacing = 5; chartPage.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlCategory).MajorTickMark = Microsoft.Office.Interop.Excel.Constants.xlOutside; chartPage.Parent.RoundedCorners = true; chartPage.Axes(Microsoft.Office.Interop.Excel.XlAxisType.xlCategory, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary).AxisTitle.Text = TaskRunnerServiceRes.NumberOfSamplesByYear; // need to save the file with a unique name under the TVItemID FileInfo fiImageNumberOfSamplesByYearStat = new FileInfo(fi.DirectoryName + @"\NumberOfSamplesByYearStat" + FileNameExtra + ".png"); di = new DirectoryInfo(fi.DirectoryName); if (!di.Exists) { try { di.Create(); } catch (Exception ex) { NotUsed = string.Format(TaskRunnerServiceRes.CouldNotCreateDirectory__, di.FullName, ex.Message + (ex.InnerException != null ? " Inner: " + ex.InnerException.Message : "")); _TaskRunnerBaseService._BWObj.TextLanguageList = _TaskRunnerBaseService.GetTextLanguageFormat2List("CouldNotCreateDirectory__", di.FullName, ex.Message + (ex.InnerException != null ? " Inner: " + ex.InnerException.Message : "")); return(false); } } chartPage.Export(fiImageNumberOfSamplesByYearStat.FullName, "PNG", false); if (workbook != null) { workbook.Close(false); } if (xlApp != null) { xlApp.Quit(); } sbHTML.AppendLine($@" <h3>{ TaskRunnerServiceRes.MWQMSitesSummary }</h3>"); sbHTML.AppendLine($@" <br /"); sbHTML.AppendLine($@" <h4>{ TaskRunnerServiceRes.NumberOfMWQMSitesByYear }</h4>"); sbHTML.AppendLine($@"<div class=""textAlignCenter"">|||Image|FileName,{ fiImageNumberOfSitesByYearStat.FullName }|width,400|height,150|||</div>"); sbHTML.AppendLine($@" <h4>{ TaskRunnerServiceRes.NumberOfMWQMRunsByYear }</h4>"); sbHTML.AppendLine($@"<div class=""textAlignCenter"">|||Image|FileName,{ fiImageNumberOfRunsByYearStat.FullName }|width,400|height,150|||</div>"); sbHTML.AppendLine($@" <h4>{ TaskRunnerServiceRes.NumberOfSamplesByYear }</h4>"); sbHTML.AppendLine($@"<div class=""textAlignCenter"">|||Image|FileName,{ fiImageNumberOfSamplesByYearStat.FullName }|width,400|height,150|||</div>"); sbHTML.AppendLine(@"<span>|||PageBreak|||</span>"); _TaskRunnerBaseService.SendPercentToDB(_TaskRunnerBaseService._BWObj.appTaskModel.AppTaskID, 40); sbHTML.AppendLine($@" <h3>{ TaskRunnerServiceRes.MWQMSitesInformation }</h3>"); sbHTML.AppendLine($@" <table cellpadding=""5"" class=""textAlignCenter"">"); sbHTML.AppendLine($@" <tr>"); sbHTML.AppendLine($@" <th colspan=""2"">{ TaskRunnerServiceRes.Site }</th>"); sbHTML.AppendLine($@" <th>{ TaskRunnerServiceRes.Coordinates }</th>"); sbHTML.AppendLine($@" <th>{ TaskRunnerServiceRes.Description }</th>"); sbHTML.AppendLine($@" <th>{ TaskRunnerServiceRes.Photos }</th>"); sbHTML.AppendLine($@" </tr>"); foreach (MWQMSiteModel mwqmSiteModel in mwqmSiteModelList) { TVItemModel tvItemModel = tvItemModelListMWQMSites.Where(c => c.TVItemID == mwqmSiteModel.MWQMSiteTVItemID).FirstOrDefault(); if (tvItemModel != null) { if (tvItemModel.IsActive) { string classificationLetter = ""; string classificationColor = ""; classificationLetter = GetLastClassificationInitial(mwqmSiteModel.MWQMSiteLatestClassification); classificationColor = GetLastClassificationColor(mwqmSiteModel.MWQMSiteLatestClassification); sbHTML.AppendLine($@" <tr>"); sbHTML.AppendLine($@" <td>{ mwqmSiteModel.MWQMSiteTVText }</td>"); sbHTML.AppendLine($@" <td class=""{ classificationColor }"">{ classificationLetter }</td>"); List <MapInfoPointModel> mapInfoPointModelList = _MapInfoService._MapInfoPointService.GetMapInfoPointModelListWithTVItemIDAndTVTypeAndMapInfoDrawTypeDB(mwqmSiteModel.MWQMSiteTVItemID, TVTypeEnum.MWQMSite, MapInfoDrawTypeEnum.Point); if (mapInfoPointModelList.Count > 0) { sbHTML.AppendLine($@" <td>{ mapInfoPointModelList[0].Lat.ToString("F5") } { mapInfoPointModelList[0].Lng.ToString("F5") }</td>"); } else { sbHTML.AppendLine($@" <td> </td>"); } sbHTML.AppendLine($@" <td class=""textAlignLeft"">{ mwqmSiteModel.MWQMSiteDescription }</td>"); sbHTML.AppendLine($@" <td>Photo</td>"); sbHTML.AppendLine($@" </tr>"); } } } sbHTML.AppendLine($@" </table>"); sbHTML.AppendLine(@"<span>|||PageBreak|||</span>"); _TaskRunnerBaseService.SendPercentToDB(_TaskRunnerBaseService._BWObj.appTaskModel.AppTaskID, 50); List <MWQMSiteModel> mwqmSiteModelList2 = (from s in mwqmSiteModelList from t in tvItemModelListMWQMSites where s.MWQMSiteTVItemID == t.TVItemID && t.IsActive == true orderby s.MWQMSiteTVText select s).ToList(); int skip = 0; int take = 15; bool HasData = true; int countRun = 0; while (HasData) { countRun += 1; if (countRun > 2) { break; } List <MWQMRunModel> mwqmRunModelList2 = mwqmRunModelList.Where(c => c.RunSampleType == SampleTypeEnum.Routine).OrderByDescending(c => c.DateTime_Local).Skip(skip).Take(take).ToList(); if (mwqmRunModelList2.Count > 0) { sbHTML.AppendLine($@" <h4>{ TaskRunnerServiceRes.ActiveMWQMSites } { TaskRunnerServiceRes.FCDensities } ({ TaskRunnerServiceRes.Routine }) { mwqmRunModelList2[0].DateTime_Local.ToString("yyyy MMMM dd") } { TaskRunnerServiceRes.To } { mwqmRunModelList2[mwqmRunModelList2.Count -1].DateTime_Local.ToString("yyyy MMMM dd") }</h4>"); sbHTML.AppendLine($@" <table class=""FCSalTempDataTableClass"">"); sbHTML.AppendLine($@" <tr>"); sbHTML.AppendLine($@" <th class=""rightBottomBorder"">{ TaskRunnerServiceRes.Site }</th>"); foreach (MWQMRunModel mwqmRunModel in mwqmRunModelList2) { sbHTML.AppendLine($@" <th class=""bottomBorder"">{ mwqmRunModel.DateTime_Local.ToString("yyyy") }<br />{ mwqmRunModel.DateTime_Local.ToString("MMM") }<br />{ mwqmRunModel.DateTime_Local.ToString("dd") }</th>"); } sbHTML.AppendLine($@" </tr>"); foreach (MWQMSiteModel mwqmSiteModel in mwqmSiteModelList2) { sbHTML.AppendLine($@" <tr>"); sbHTML.AppendLine($@" <td class=""rightBorder"">{ mwqmSiteModel.MWQMSiteTVText }</td>"); foreach (MWQMRunModel mwqmRunModel in mwqmRunModelList2) { float?value = (from s in mwqmSampleModelList where s.MWQMRunTVItemID == mwqmRunModel.MWQMRunTVItemID && s.MWQMSiteTVItemID == mwqmSiteModel.MWQMSiteTVItemID select s.FecCol_MPN_100ml).FirstOrDefault(); string valueStr = value != null ? (value == 1 ? "< 2" : ((float)value).ToString("F0")) : "--"; sbHTML.AppendLine($@" <td>{ valueStr }</td>"); } sbHTML.AppendLine($@" </tr>"); } sbHTML.AppendLine($@" <tr>"); sbHTML.AppendLine($@" <td class=""topRightBorder"">{ TaskRunnerServiceRes.StartTide }<br />{ TaskRunnerServiceRes.EndTide }</td>"); foreach (MWQMRunModel mwqmRunModel in mwqmRunModelList2) { string StartTide = GetTideInitial(mwqmRunModel.Tide_Start); string EndTide = GetTideInitial(mwqmRunModel.Tide_End); sbHTML.AppendLine($@" <td class=""topRightBorder"">{ StartTide }<br />{ EndTide }</td>"); } sbHTML.AppendLine($@" </tr>"); sbHTML.AppendLine($@" <tr>"); sbHTML.AppendLine($@" <td class=""topRightBorder"">{ TaskRunnerServiceRes.Rain }(mm)<br />{ TaskRunnerServiceRes.Minus1Day }<br />{ TaskRunnerServiceRes.Minus2Day }<br />{ TaskRunnerServiceRes.Minus3Day }<br />{ TaskRunnerServiceRes.Minus4Day }<br />{ TaskRunnerServiceRes.Minus5Day }</td>"); foreach (MWQMRunModel mwqmRunModel in mwqmRunModelList2) { string RainDay1 = mwqmRunModel.RainDay1_mm != null ? ((double)mwqmRunModel.RainDay1_mm).ToString("F0") : "--"; string RainDay2 = mwqmRunModel.RainDay2_mm != null ? ((double)mwqmRunModel.RainDay2_mm).ToString("F0") : "--"; string RainDay3 = mwqmRunModel.RainDay3_mm != null ? ((double)mwqmRunModel.RainDay3_mm).ToString("F0") : "--"; string RainDay4 = mwqmRunModel.RainDay4_mm != null ? ((double)mwqmRunModel.RainDay4_mm).ToString("F0") : "--"; string RainDay5 = mwqmRunModel.RainDay5_mm != null ? ((double)mwqmRunModel.RainDay5_mm).ToString("F0") : "--"; sbHTML.AppendLine($@" <td class=""topRightBorder""> <br />{ RainDay1 }<br />{ RainDay2 }<br />{ RainDay3 }<br />{ RainDay4 }<br />{ RainDay5 }</td>"); } sbHTML.AppendLine($@" </tr>"); sbHTML.AppendLine($@" </table>"); sbHTML.AppendLine(@"<span>|||PageBreak|||</span>"); skip += take; } else { HasData = false; } } _TaskRunnerBaseService.SendPercentToDB(_TaskRunnerBaseService._BWObj.appTaskModel.AppTaskID, 60); skip = 0; take = 15; HasData = true; countRun = 0; while (HasData) { countRun += 1; if (countRun > 2) { break; } List <MWQMRunModel> mwqmRunModelList2 = mwqmRunModelList.Where(c => c.RunSampleType == SampleTypeEnum.Routine).OrderByDescending(c => c.DateTime_Local).Skip(skip).Take(take).ToList(); if (mwqmRunModelList2.Count > 0) { sbHTML.AppendLine($@" <h4>{ TaskRunnerServiceRes.ActiveMWQMSites } { TaskRunnerServiceRes.Salinity } ({ TaskRunnerServiceRes.Routine }) { mwqmRunModelList2[0].DateTime_Local.ToString("yyyy MMMM dd") } { TaskRunnerServiceRes.To } { mwqmRunModelList2[mwqmRunModelList2.Count - 1].DateTime_Local.ToString("yyyy MMMM dd") }</h4>"); sbHTML.AppendLine($@" <table class=""FCSalTempDataTableClass"">"); sbHTML.AppendLine($@" <tr>"); sbHTML.AppendLine($@" <th class=""rightBottomBorder"">{ TaskRunnerServiceRes.Site }</th>"); foreach (MWQMRunModel mwqmRunModel in mwqmRunModelList2) { sbHTML.AppendLine($@" <th class=""bottomBorder"">{ mwqmRunModel.DateTime_Local.ToString("yyyy") }<br />{ mwqmRunModel.DateTime_Local.ToString("MMM") }<br />{ mwqmRunModel.DateTime_Local.ToString("dd") }</th>"); } sbHTML.AppendLine($@" </tr>"); foreach (MWQMSiteModel mwqmSiteModel in mwqmSiteModelList2) { sbHTML.AppendLine($@" <tr>"); sbHTML.AppendLine($@" <td class=""rightBorder"">{ mwqmSiteModel.MWQMSiteTVText }</td>"); foreach (MWQMRunModel mwqmRunModel in mwqmRunModelList2) { float?value = (float?)(from s in mwqmSampleModelList where s.MWQMRunTVItemID == mwqmRunModel.MWQMRunTVItemID && s.MWQMSiteTVItemID == mwqmSiteModel.MWQMSiteTVItemID select s.Salinity_PPT).FirstOrDefault(); string valueStr = value != null ? (value == 1 ? "< 2" : ((float)value).ToString("F0")) : "--"; sbHTML.AppendLine($@" <td>{ valueStr }</td>"); } sbHTML.AppendLine($@" </tr>"); } sbHTML.AppendLine($@" <tr>"); sbHTML.AppendLine($@" <td class=""topRightBorder"">{ TaskRunnerServiceRes.StartTide }<br />{ TaskRunnerServiceRes.EndTide }</td>"); foreach (MWQMRunModel mwqmRunModel in mwqmRunModelList2) { string StartTide = GetTideInitial(mwqmRunModel.Tide_Start); string EndTide = GetTideInitial(mwqmRunModel.Tide_End); sbHTML.AppendLine($@" <td class=""topRightBorder"">{ StartTide }<br />{ EndTide }</td>"); } sbHTML.AppendLine($@" </tr>"); sbHTML.AppendLine($@" <tr>"); sbHTML.AppendLine($@" <td class=""topRightBorder"">{ TaskRunnerServiceRes.Rain }(mm)<br />{ TaskRunnerServiceRes.Minus1Day }<br />{ TaskRunnerServiceRes.Minus2Day }<br />{ TaskRunnerServiceRes.Minus3Day }<br />{ TaskRunnerServiceRes.Minus4Day }<br />{ TaskRunnerServiceRes.Minus5Day }</td>"); foreach (MWQMRunModel mwqmRunModel in mwqmRunModelList2) { string RainDay1 = mwqmRunModel.RainDay1_mm != null ? ((double)mwqmRunModel.RainDay1_mm).ToString("F0") : "--"; string RainDay2 = mwqmRunModel.RainDay2_mm != null ? ((double)mwqmRunModel.RainDay2_mm).ToString("F0") : "--"; string RainDay3 = mwqmRunModel.RainDay3_mm != null ? ((double)mwqmRunModel.RainDay3_mm).ToString("F0") : "--"; string RainDay4 = mwqmRunModel.RainDay4_mm != null ? ((double)mwqmRunModel.RainDay4_mm).ToString("F0") : "--"; string RainDay5 = mwqmRunModel.RainDay5_mm != null ? ((double)mwqmRunModel.RainDay5_mm).ToString("F0") : "--"; sbHTML.AppendLine($@" <td class=""topRightBorder""> <br />{ RainDay1 }<br />{ RainDay2 }<br />{ RainDay3 }<br />{ RainDay4 }<br />{ RainDay5 }</td>"); } sbHTML.AppendLine($@" </tr>"); sbHTML.AppendLine($@" </table>"); sbHTML.AppendLine(@"<span>|||PageBreak|||</span>"); skip += take; } else { HasData = false; } } _TaskRunnerBaseService.SendPercentToDB(_TaskRunnerBaseService._BWObj.appTaskModel.AppTaskID, 70); skip = 0; take = 15; HasData = true; countRun = 0; while (HasData) { countRun += 1; if (countRun > 2) { break; } List <MWQMRunModel> mwqmRunModelList2 = mwqmRunModelList.Where(c => c.RunSampleType == SampleTypeEnum.Routine).OrderByDescending(c => c.DateTime_Local).Skip(skip).Take(take).ToList(); if (mwqmRunModelList2.Count > 0) { sbHTML.AppendLine($@" <h4>{ TaskRunnerServiceRes.ActiveMWQMSites } { TaskRunnerServiceRes.Temperature } ({ TaskRunnerServiceRes.Routine }) { mwqmRunModelList2[0].DateTime_Local.ToString("yyyy MMMM dd") } { TaskRunnerServiceRes.To } { mwqmRunModelList2[mwqmRunModelList2.Count - 1].DateTime_Local.ToString("yyyy MMMM dd") }</h4>"); sbHTML.AppendLine($@" <table class=""FCSalTempDataTableClass"">"); sbHTML.AppendLine($@" <tr>"); sbHTML.AppendLine($@" <th class=""rightBottomBorder"">{ TaskRunnerServiceRes.Site }</th>"); foreach (MWQMRunModel mwqmRunModel in mwqmRunModelList2) { sbHTML.AppendLine($@" <th class=""bottomBorder"">{ mwqmRunModel.DateTime_Local.ToString("yyyy") }<br />{ mwqmRunModel.DateTime_Local.ToString("MMM") }<br />{ mwqmRunModel.DateTime_Local.ToString("dd") }</th>"); } sbHTML.AppendLine($@" </tr>"); foreach (MWQMSiteModel mwqmSiteModel in mwqmSiteModelList2) { sbHTML.AppendLine($@" <tr>"); sbHTML.AppendLine($@" <td class=""rightBorder"">{ mwqmSiteModel.MWQMSiteTVText }</td>"); foreach (MWQMRunModel mwqmRunModel in mwqmRunModelList2) { float?value = (float?)(from s in mwqmSampleModelList where s.MWQMRunTVItemID == mwqmRunModel.MWQMRunTVItemID && s.MWQMSiteTVItemID == mwqmSiteModel.MWQMSiteTVItemID select s.WaterTemp_C).FirstOrDefault(); string valueStr = value != null ? (value == 1 ? "< 2" : ((float)value).ToString("F0")) : "--"; sbHTML.AppendLine($@" <td>{ valueStr }</td>"); } sbHTML.AppendLine($@" </tr>"); } sbHTML.AppendLine($@" <tr>"); sbHTML.AppendLine($@" <td class=""topRightBorder"">{ TaskRunnerServiceRes.StartTide }<br />{ TaskRunnerServiceRes.EndTide }</td>"); foreach (MWQMRunModel mwqmRunModel in mwqmRunModelList2) { string StartTide = GetTideInitial(mwqmRunModel.Tide_Start); string EndTide = GetTideInitial(mwqmRunModel.Tide_End); sbHTML.AppendLine($@" <td class=""topRightBorder"">{ StartTide }<br />{ EndTide }</td>"); } sbHTML.AppendLine($@" </tr>"); sbHTML.AppendLine($@" <tr>"); sbHTML.AppendLine($@" <td class=""topRightBorder"">{ TaskRunnerServiceRes.Rain }(mm)<br />{ TaskRunnerServiceRes.Minus1Day }<br />{ TaskRunnerServiceRes.Minus2Day }<br />{ TaskRunnerServiceRes.Minus3Day }<br />{ TaskRunnerServiceRes.Minus4Day }<br />{ TaskRunnerServiceRes.Minus5Day }</td>"); foreach (MWQMRunModel mwqmRunModel in mwqmRunModelList2) { string RainDay1 = mwqmRunModel.RainDay1_mm != null ? ((double)mwqmRunModel.RainDay1_mm).ToString("F0") : "--"; string RainDay2 = mwqmRunModel.RainDay2_mm != null ? ((double)mwqmRunModel.RainDay2_mm).ToString("F0") : "--"; string RainDay3 = mwqmRunModel.RainDay3_mm != null ? ((double)mwqmRunModel.RainDay3_mm).ToString("F0") : "--"; string RainDay4 = mwqmRunModel.RainDay4_mm != null ? ((double)mwqmRunModel.RainDay4_mm).ToString("F0") : "--"; string RainDay5 = mwqmRunModel.RainDay5_mm != null ? ((double)mwqmRunModel.RainDay5_mm).ToString("F0") : "--"; sbHTML.AppendLine($@" <td class=""topRightBorder""> <br />{ RainDay1 }<br />{ RainDay2 }<br />{ RainDay3 }<br />{ RainDay4 }<br />{ RainDay5 }</td>"); } sbHTML.AppendLine($@" </tr>"); sbHTML.AppendLine($@" </table>"); sbHTML.AppendLine(@"<span>|||PageBreak|||</span>"); skip += take; } else { HasData = false; } } sbHTML.AppendLine($@"|||FileNameExtra|Random,{ FileNameExtra }|||"); sbHTML.AppendLine(@"<span>|||PageBreak|||</span>"); if (!GetBottomHTML(sbHTML, fi, parameters)) { return(false); } _TaskRunnerBaseService.SendPercentToDB(_TaskRunnerBaseService._BWObj.appTaskModel.AppTaskID, 80); return(true); }
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(); }