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();
}
