private static void CreateBoxCart(Excel.Worksheet targetSheet, Excel.Range xValues,
String kwartyl1Desc, Excel.Range kwartyl1Values,
String medianaDesc, Excel.Range medianaValues,
String kwartyl3Desc, Excel.Range kwartyl3Values,
Excel.Range ErrorMaksimum, Excel.Range ErrorMadiana, Excel.Range ErrorMinimum)
{
Excel.ChartObjects xlCharts = (Excel.ChartObjects)targetSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 100, 300, 250);
Excel.Chart chartPage = myChart.Chart;
chartPage.HasLegend = false;
chartPage.HasTitle = true;
SeriesCollection seriesCollection = chartPage.SeriesCollection();
Series series1 = seriesCollection.NewSeries();
series1.Name = kwartyl1Desc;
series1.XValues = xValues;
series1.Values = kwartyl1Values;
series1.HasErrorBars = true;
series1.ErrorBar(XlErrorBarDirection.xlY, XlErrorBarInclude.xlErrorBarIncludeMinusValues, XlErrorBarType.xlErrorBarTypeCustom, ErrorMinimum, ErrorMinimum);
series1.Format.Fill.Visible = Microsoft.Office.Core.MsoTriState.msoFalse;
series1 = seriesCollection.NewSeries();
series1.Name = medianaDesc;
series1.XValues = xValues;
series1.Values = medianaValues;
//series1.Format.Line.Parent
series1.Format.Line.Weight = 2.0F;
series1.Format.Line.Visible = Microsoft.Office.Core.MsoTriState.msoTriStateMixed; //Tri-State
series1.Format.Line.ForeColor.RGB = (int)Microsoft.Office.Interop.Excel.XlRgbColor.rgbBlack;
//series1.Format.Line.ForeColor.RGB = (int)XlRgbColor.rgbBlack;
//series1.Format.Line.Weight = 5;
series1 = seriesCollection.NewSeries();
series1.Name = kwartyl3Desc;
series1.XValues = xValues;
series1.Values = kwartyl3Values;
series1.Format.Line.Weight = 2.0F;
series1.Format.Line.Visible = Microsoft.Office.Core.MsoTriState.msoTriStateMixed; //Tri-State
series1.Format.Line.ForeColor.RGB = (int)Microsoft.Office.Interop.Excel.XlRgbColor.rgbBlack;
series1.HasErrorBars = true;
series1.ErrorBar(XlErrorBarDirection.xlY, XlErrorBarInclude.xlErrorBarIncludePlusValues, XlErrorBarType.xlErrorBarTypeCustom, ErrorMaksimum, ErrorMaksimum);
chartPage.ChartType = Excel.XlChartType.xlColumnStacked;
}
private void EditChart(Excel.Chart chart, int index)
{
chart.ChartType = Excel.XlChartType.xlColumnClustered;
var range1 = shAuswertung1.Range[shAuswertung1.Cells[1, 1], shAuswertung1.Cells[index, 1]];
var range2 = shAuswertung1.Range[shAuswertung1.Cells[1, 5], shAuswertung1.Cells[index, 5]];
var range3 = shAuswertung1.Range[shAuswertung1.Cells[1, 9], shAuswertung1.Cells[index, 9]];
var range4 = shAuswertung1.Range[shAuswertung1.Cells[1, 10], shAuswertung1.Cells[index, 10]];
chart.SetSourceData(Source: Globals.ThisAddIn.Application.Union(range1, range2, range3, range4));
chart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionBottom;
chart.Legend.Font.Size = 8;
chart.Legend.Font.Name = "Arial";
chart.SeriesCollection(1).Format.Fill.ForeColor.RGB = Color.FromArgb(192, 0, 0);
chart.SeriesCollection(2).Format.Fill.ForeColor.RGB = Color.FromArgb(0, 112, 192);
chart.SeriesCollection(3).Format.Fill.ForeColor.RGB = Color.FromArgb(255, 140, 0);
chart.Axes(Excel.XlAxisType.xlCategory).CategoryType = Excel.XlCategoryType.xlCategoryScale;
chart.Axes(Excel.XlAxisType.xlCategory).TickLabels.Font.Size = 8;
chart.Axes(Excel.XlAxisType.xlCategory).TickLabels.Font.Name = "Arial";
chart.Axes(Excel.XlAxisType.xlCategory).TickLabels.Orientation = 90;
chart.Axes(Excel.XlAxisType.xlValue).TickLabels.Font.Size = 8;
chart.Axes(Excel.XlAxisType.xlValue).TickLabels.Font.Name = "Arial";
chart.Axes(Excel.XlAxisType.xlValue).MinimumScale = 0;
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application xlApp = new Excel.Application();
Excel.Workbook wb = xlApp.Workbooks.Add(Missing.Value);
Excel.Worksheet ws = wb.ActiveSheet;
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
ws.Cells[1, i + 2].Value = dataGridView1.Rows[i].Cells[0].Value.ToString();
}
ws.Cells[2, 1].Value = "Amenities Sold";
ws.Cells[3, 1].Value = "Tickets Sold";
ws.Cells[4, 1].Value = "Commission Earned";
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
ws.Cells[2, i + 2].Value = dataGridView1.Rows[i].Cells[1].Value.ToString();
ws.Cells[3, i + 2].Value = dataGridView1.Rows[i].Cells[2].Value.ToString();
ws.Cells[4, i + 2].Value = dataGridView1.Rows[i].Cells[3].Value.ToString();
}
Excel.ChartObjects xlObj = ws.ChartObjects(Missing.Value);
Excel.ChartObject obj = xlObj.Add(10, 50, 450, 250);
Excel.Chart chart = obj.Chart;
Excel.Series s = chart.SeriesCollection().Add(ws.Range[ws.Cells[1, 1], ws.Cells[4, dataGridView1.Rows.Count]]);
//s.XValues = ws.Range[ws.Cells[1, 2], ws.Cells[1, dataGridView1.Rows.Count - 1]];
//Excel.Range rangeSourceChart = ws.Range[ws.Cells[1, 1], ws.Cells[4, dataGridView1.Rows.Count - 1]];
//chart.SetSourceData(rangeSourceChart, Excel.XlRowCol.xlRows);
using (SaveFileDialog sf = new SaveFileDialog())
{
sf.Filter = "Excel Files|*.xlsx";
sf.FileName = "CommissionReport";
if (sf.ShowDialog() == DialogResult.OK)
{
wb.SaveAs(sf.FileName);
wb.Close();
xlApp.Quit();
}
}
}
private void DrawChart_FeedbackVsQuarter_per_FeedbackCategory_individualDepartment(Department department, int startYear, bool isPercentage = false)
{
Excel.Chart chart = (startYear == reportDate.Year) ?
InitialiseChart($"Feedback category in {reportDate.Year}-Q{reportDateQuarter} for {department.Name}{(isPercentage ? " (percentage)" : "")}", isPercentage) :
InitialiseChart($"Feedback category trend since {startYear} for {department.Name}{(isPercentage ? " (percentage)" : "")}", isPercentage);
var data = new Dictionary <string, IList <Feedback> >();
for (int year = startYear; year <= reportDate.Year; year++)
{
for (int quarter = 1; quarter <= 4; quarter++)
{
data.Add($"{year}-Q{quarter}", feedbacks.Where(f => f.ResponsibleDepartment == department.Name && f.DateReceived.Year == year && ((f.DateReceived.Month + 2) / 3) == quarter).ToList());
if (year == reportDate.Year && quarter == reportDateQuarter)
{
break;
}
}
}
var seriesCollection = (Excel.SeriesCollection)chart.SeriesCollection();
foreach (var category in department.Categories)
{
var series = seriesCollection.NewSeries();
series.Name = category;
series.XValues = data.Keys.ToArray();
if (isPercentage)
{
series.Values = data.Values.Select(v => GetRatio(v.Where(f => f.Category == category).Count(), v.Count)).ToArray();
}
else
{
series.Values = data.Values.Select(v => v.Where(f => f.Category == category).Count()).ToArray();
}
series.ApplyDataLabels();
if (isPercentage)
{
series.DataLabels().NumberFormat = "0%";
}
}
ExportChart(chart);
}
/// <summary>
/// Обновление Диаграммы
/// </summary>
internal void UpdateDiagramm()
{
Excel.ChartObject shp = SheetUrv11.ChartObjects("Chart 2");
Excel.Chart chartPage = shp.Chart;
Excel.SeriesCollection seriesCol = (Excel.SeriesCollection)chartPage.SeriesCollection();
Excel.FullSeriesCollection fullColl = chartPage.FullSeriesCollection();
Debug.WriteLine(fullColl.Count);
int lastCol = GetLastColumnUrv(SheetUrv11, _rowStart);
int lastRow = GetLastRowUrv11();
int ix = 1;
string letterCost = "G";
fullColl.Item(ix).Name = $"={SheetUrv11.Name}!${letterCost}10";
fullColl.Item(ix).Values = $"={SheetUrv11.Name}!${letterCost}{_rowStart}:${letterCost}{lastRow}";
fullColl.Item(ix).XValues = $"={SheetUrv11.Name}!$C{_rowStart}:$C{lastRow}";
for (int col = 9; col <= lastCol; col += 3)
{
Excel.Range cellFirstCost = SheetUrv11.Cells[_rowStart, col];
string text = cellFirstCost.Value?.ToString() ?? "";
if (string.IsNullOrEmpty(text))
{
continue;
}
letterCost = ExcelHelper.GetColumnLetter(cellFirstCost);
ix++;
if (ix > fullColl.Count)
{
seriesCol.NewSeries();
}
fullColl.Item(ix).Name = $"={SheetUrv11.Name}!${letterCost}10";
fullColl.Item(ix).Values = $"={SheetUrv11.Name}!${letterCost}{_rowStart}:${letterCost}{lastRow}";
fullColl.Item(ix).XValues = $"={SheetUrv11.Name}!$C{_rowStart}:$C{lastRow}";
}
if (ix < fullColl.Count)
{
for (int i = ix + 1; i <= fullColl.Count; i++)
{
fullColl.Item(i).Delete();
}
}
}
public void AddInfo(FunctionTableItem[] fun, ValueIs value = ValueIs.Magnitude, bool addChart = false)
{
Excel.Series series;
Excel.Range temp;
range = range.get_Resize(2, fun.Length);
range.set_Value(Missing.Value, GetArray(fun, value));
if (addChart)
{
LastIndex += chartHeight + 10;
chartObject = charts.Add(400, LastIndex, chartWidth, chartHeight);
}
currentChart = chartObject.Chart;
series = ((Excel.SeriesCollection)currentChart.SeriesCollection(Type.Missing)).NewSeries();
temp = range.get_Resize(1, fun.Length);
series.Values = temp;
series.XValues = temp.get_Offset(1, 0);
currentChart.ChartType = Excel.XlChartType.xlXYScatterSmoothNoMarkers;
range = range.get_Offset(3, 0);
}
private static void SetChartData(Excel.Chart chart, Dictionary <string, IList <Feedback> > data, IEnumerable <string> feedbackNatures, bool isPercentage)
{
var seriesCollection = (Excel.SeriesCollection)chart.SeriesCollection();
foreach (var feedbackNature in feedbackNatures)
{
var series = seriesCollection.NewSeries();
series.Name = feedbackNature;
series.XValues = data.Keys.ToArray();
if (feedbackNature == FeedbackNature.TotalFeedbacks)
{
if (isPercentage)
{
series.Values = data.Values.Select(v => GetRatio(v.Count, v.Count)).ToArray();
}
else
{
series.Values = data.Values.Select(v => v.Count()).ToArray();
}
}
else
{
if (isPercentage)
{
series.Values = data.Values.Select(v => GetRatio(v.Where(f => f.FeedbackNature == feedbackNature).Count(), v.Count)).ToArray();
}
else
{
series.Values = data.Values.Select(v => v.Where(f => f.FeedbackNature == feedbackNature).Count()).ToArray();
}
}
series.ApplyDataLabels();
if (isPercentage)
{
(series.DataLabels()).NumberFormat = "0%";
}
}
}
public void AddInfo(FunctionTableItem[] fun, ValueIs value = ValueIs.Magnitude, bool addChart = false)
{
Excel.Series series;
Excel.Range temp;
range = range.get_Resize(2, fun.Length);
range.set_Value(Missing.Value, GetArray(fun, value));
if (addChart)
{
LastIndex += chartHeight + 10;
chartObject = charts.Add(400, LastIndex, chartWidth, chartHeight);
}
currentChart = chartObject.Chart;
series = ((Excel.SeriesCollection) currentChart.SeriesCollection(Type.Missing)).NewSeries();
temp = range.get_Resize(1, fun.Length);
series.Values = temp;
series.XValues = temp.get_Offset(1, 0);
currentChart.ChartType = Excel.XlChartType.xlXYScatterSmoothNoMarkers;
range = range.get_Offset(3, 0);
}
/// <summary>
/// Initialize Excel Application
/// </summary>
/// <param name="sheetname">Desired Sheetname</param>
/// <param name="visible">Determine whether Excel-Sheet is visible or not</param>
public static void InitializeExcel(string sheetname, bool visible = true)
{
// Create a new Excel Application
_app = new Excel.Application();
_app.Visible = visible;
// Create a new, empty workbook and add it to the collection returned
_workbook = _app.Workbooks.Add(Missing.Value);
_workbook.Worksheets.Item[1].Name = sheetname;
// Initialize _mainChart.
var worksheet = _workbook.Worksheets.Item[1] as
Microsoft.Office.Interop.Excel.Worksheet;
var charts = worksheet.ChartObjects() as
Microsoft.Office.Interop.Excel.ChartObjects;
var chartObject = charts.Add(10, 10, 800, 450) as
Microsoft.Office.Interop.Excel.ChartObject;
_mainChart = chartObject.Chart;
_mainChart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlXYScatterLinesNoMarkers;
_mainChart.ChartWizard(Title: sheetname);
_mainChartSeriesCollection = _mainChart.SeriesCollection();
}
private void GerarGraficoExcel(String c1, String c2, int tamanho, double left, double top, double width, double height)
{
// Cria um Chart(Gráfico)
Ex.ChartObjects cb = (Ex.ChartObjects)oSheetChart.ChartObjects(Type.Missing);
Ex.ChartObject cbc = (Ex.ChartObject)cb.Add(left, top, width, height);
Ex.Chart cp = cbc.Chart;
Ex.Range valores = oSheet.get_Range(c1, c2);
// Seta o título do gráfico
cp.HasTitle = true;
cp.ChartTitle.Text = "MÉDIA DE TEMPO EM VETORES DE TAMANHO " + tamanho + " EM " + (tamanho <= 1000 ? "NANO" : "MILLI");
cp.ChartTitle.Font.Name = "Arial";
// Seta os nomes das colunas
Ex.SeriesCollection seriesCollection = cp.SeriesCollection();
Ex.Series series = seriesCollection.NewSeries();
series.Values = valores;
series.XValues = oSheet.get_Range("B1", "J1");
// Oculta a legenda da serie
cp.Legend.Clear();
}
/// <summary>
/// Рисует график
public void drawGraph(string filename, int numSheets, string firstCell, string lastCell /*, double[] approxY*/)
{
ObjWorkBooks = ObjWorkExcel.Workbooks;
ObjSheets = ObjWorkBook.Worksheets;
Excel.ChartObjects chartsobjrcts = (Excel.ChartObjects)ObjWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject chartsobjrct = chartsobjrcts.Add(1, 360, 400, 250);
Excel.Chart chart = chartsobjrct.Chart;
Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chart.SeriesCollection(Type.Missing);
Excel.Series series = seriesCollection.NewSeries();
Excel.Trendlines trendlines = (Excel.Trendlines)series.Trendlines(System.Type.Missing);
Excel.Trendline newTrendline = trendlines.Add(Excel.XlTrendlineType.xlPower, 2, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true, true, Type.Missing);
newTrendline.Select();
chart.Legend.Delete();
series.XValues = ObjWorkSheet.get_Range("E7", "E26");
series.Values = ObjWorkSheet.get_Range("D7", "D26");
chart.ChartType = Excel.XlChartType.xlXYScatter;
}
/// <summary>
/// Method fills the sheet with calculation results
/// </summary>
/// <param name="worksheet">Worksheet to save the results there</param>
/// <param name="calculationTypeName">CalculationType</param>
/// <param name="result">Container with result variables</param>
/// <param name="allVariables">Cantainer with variables values for each time step</param>
/// <param name="calcTime">Time reqiured for calculation</param>
private static void SetCalculationResults(Excel.Worksheet worksheet, CalculationTypeName calculationTypeName,
List <Variable> leftVariables, List <DEVariable> result, List <List <DEVariable> > allVariables, double calcTime)
{
worksheet.Name = calculationTypeName.ToString();
int rowIndex = 1;
int columnIndex = 1;
for (int i = 0; i < result.Count; i++)
{
worksheet.Cells[rowIndex, columnIndex] = $"{result[i].Name} = ";
worksheet.Cells[rowIndex, columnIndex + 1] = result[i].Value;
rowIndex++;
}
rowIndex++;
worksheet.Cells[rowIndex, columnIndex] = "Calculation time = ";
worksheet.Cells[rowIndex, columnIndex + 1] = calcTime;
rowIndex++;
if (allVariables != null)
{
rowIndex++;
worksheet.Cells[rowIndex, columnIndex] = "Detailed results";
rowIndex++;
for (int i = 0; i < allVariables[0].Count; i++)
{
worksheet.Cells[rowIndex, columnIndex + i] = allVariables[0][i].Name;
}
rowIndex++;
string leftTopCellforChart = GetExcelColumnName(columnIndex) + rowIndex.ToString();
string horinzontalAxisTop = GetExcelColumnName(columnIndex + allVariables[0].Count - 1) + rowIndex.ToString();
int idxStart = rowIndex;
for (int i = 0; i < allVariables.Count; i++)
{
for (int j = 0; j < allVariables[0].Count; j++)
{
worksheet.Cells[rowIndex, columnIndex + j] = allVariables[i][j].Value;
}
rowIndex++;
}
string rightDownCellForChart = GetExcelColumnName(columnIndex + allVariables[0].Count - 2) + (allVariables.Count - 1 + idxStart).ToString();
string horizontalAlignmentDown = GetExcelColumnName(columnIndex + allVariables[0].Count - 1) + (allVariables.Count - 1 + idxStart).ToString();
Excel.Range chartRange;
Excel.Range horizontalAlignmentRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)worksheet.ChartObjects(Type.Missing);
Excel.ChartObject mychart = xlCharts.Add(10, 80, 500, 450);
Excel.Chart chartPage = mychart.Chart;
chartRange = worksheet.get_Range(leftTopCellforChart, rightDownCellForChart);
chartPage.SetSourceData(chartRange);
chartPage.ChartType = Excel.XlChartType.xlLine;
horizontalAlignmentRange = worksheet.get_Range(horinzontalAxisTop, horizontalAlignmentDown);
chartPage.SeriesCollection(1).XValues = horizontalAlignmentRange;
for (int i = 0; i < leftVariables.Count; i++)
{
chartPage.SeriesCollection(i + 1).Name = leftVariables[i].Name;
}
}
}
public void CreateExcelGraph_AnimalsCountPerGroup(List <Model.AnimalCountPerGroup> Data)
{
try
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//add data
// TODO
xlWorkSheet.Cells[1, 1] = "";
xlWorkSheet.Cells[2, 1] = "Count";
int col = 2, row = 2;
foreach (Model.AnimalCountPerGroup animalGroup in Data)
{
xlWorkSheet.Cells[1, col] = "Group " + animalGroup.Group_ID;
xlWorkSheet.Cells[row, col++] = "" + animalGroup.Count;
}
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
Excel.Chart chartPage = myChart.Chart;
chartRange = xlWorkSheet.get_Range("A1", (char)('A' + (col - 2)) + "2");
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xl3DPie;
chartPage.ChartTitle.Caption = "Animal Count Per Group";
Excel.Series series1 = (Excel.Series)chartPage.SeriesCollection(1);
series1.HasDataLabels = true;
string filename = "csharp.net-" + System.DateTime.Now.Ticks + ".xls";
xlWorkBook.SaveAs(filename, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
string filepath = xlWorkBook.Path + "\\" + filename;
Console.WriteLine("Excel saved at " + xlWorkBook.Path);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
FileInfo fi = new FileInfo(filepath);
if (fi.Exists)
{
System.Diagnostics.Process.Start(filepath);
}
else
{
//file doesn't exist
}
}
catch (Exception e)
{
}
}
public void click_importData(Office.IRibbonControl control)
{
// Initialize Open File Dialog
OpenFileDialog dialog_openFile = new OpenFileDialog();
dialog_openFile.Filter = "Excel Files (*.xls;*.xlsx; *.xlsm; *.csv)| *.xls; *.csv; *.xlsx; *.xlsm|Unicode Text|*.txt";
dialog_openFile.Title = "Select Files To Import...";
dialog_openFile.Multiselect = true;
// Set Active Elements
Excel.Workbook workbook_active = GetActiveWorkbook();
// Set Default Directory
SetDefaultDirectory(dialog_openFile, workbook_active);
// Show Open File Dialog
DialogResult result_openFile = dialog_openFile.ShowDialog();
if (result_openFile == System.Windows.Forms.DialogResult.OK)
{
DataSet dataset_import = new DataSet();
foreach (String fileName in dialog_openFile.FileNames)
{
try
{
DataTable datatable_temp = new DataTable(Path.GetFileNameWithoutExtension(fileName));
string ext = Path.GetExtension(fileName);
using (TextFieldParser parser = new TextFieldParser(@fileName))
{
parser.TextFieldType = FieldType.Delimited;
string delimeter = ConfigurationManager.AppSettings[ext + "_delimeter"];
parser.SetDelimiters(delimeter);
parser.HasFieldsEnclosedInQuotes = Convert.ToBoolean(ConfigurationManager.AppSettings[ext + "_quotes"]);
if (ext == ".csv")
{
string str_checkName = ConfigurationManager.AppSettings[ext + "_name"];
string str_checkValue = ConfigurationManager.AppSettings[ext + "_value"];
string str_checkTitle = ConfigurationManager.AppSettings[ext + "_title"];
while (!parser.EndOfData)
{
// TODO: DO NOT HARDCODE FIRST VAL and TYPEOF VALUES
string[] fields_temp = parser.ReadFields();
if (fields_temp[0] == str_checkName)
{
for (int i = 1; i < fields_temp.Length; i++)
{
datatable_temp.Columns.Add(new DataColumn(fields_temp[i], typeof(double)));
}
}
else if (fields_temp[0] == str_checkValue)
{
double[] values_temp = Array.ConvertAll <string, double>(SubArray <string>(fields_temp, 1, fields_temp.Length - 1), Convert.ToDouble);
if (values_temp.Length != datatable_temp.Columns.Count)
{
throw new Exception("Column mismatch.");
}
DataRow datarow_temp = datatable_temp.NewRow();
for (int i = 0; i < values_temp.Length; i++)
{
datarow_temp[i] = values_temp[i];
}
datatable_temp.Rows.Add(datarow_temp);
}
else if (fields_temp[1] == str_checkTitle)
{
datatable_temp.TableName = fields_temp[2];
}
}
}
} // End of 'using'
dataset_import.Tables.Add(datatable_temp);
}
catch (Exception IE)
{
MessageBox.Show(IE.Message + " error: could not load file");
continue;
}
}
Excel.Worksheet worksheet_new = CreateNewWorksheet();
string[] arr_names = new string[dataset_import.Tables.Count + 1];
DataTable voltage_table = dataset_import.Tables[0];
arr_names[0] = "Vgs";
double[] voltage_values = voltage_table.AsEnumerable().Select(r => r.Field <double>("Vgs")).ToArray();
for (int v = 0; v < voltage_table.Rows.Count; v++)
{
worksheet_new.Cells[v + 2, 1] = voltage_values[v];
}
for (int i = 0; i < dataset_import.Tables.Count; i++)
{
DataTable datatable_temp = dataset_import.Tables[i];
arr_names[i + 1] = "Id of " + datatable_temp.TableName;
double[] values_fromTable = datatable_temp.AsEnumerable().Select(r => r.Field <double>("Id")).ToArray();
for (int j = 0; j < datatable_temp.Rows.Count; j++)
{
worksheet_new.Cells[j + 2, i + 2] = -1 * values_fromTable[j];
}
}
for (int h = 0; h < arr_names.Length; h++)
{
worksheet_new.Cells[1, h + 1] = arr_names[h];
}
object misValue = System.Reflection.Missing.Value;
Excel.Range range_chart;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)worksheet_new.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(50, 150, 500, 250);
Excel.Chart chartPage = myChart.Chart;
range_chart = worksheet_new.UsedRange;
Excel.Range range_tempA = range_chart.Columns[1].Find("-40");
range_chart = range_chart.Resize[range_tempA.Row];
Excel.Range range_tempB = worksheet_new.Cells[range_chart.Columns[1].Find("-20").Row, 2];
Excel.Range c2 = worksheet_new.Cells[range_chart.Rows.Count, range_chart.Columns.Count];
Excel.Range range_trendline = (Excel.Range)worksheet_new.get_Range(range_tempB, c2);
range_chart.Cells[1, 1].Value = "";
chartPage.SetSourceData(range_chart, misValue);
//chartPage.SetSourceData(range_trendline, misValue);
chartPage.ChartType = Excel.XlChartType.xlXYScatter;
foreach (Excel.Series series in chartPage.SeriesCollection())
{
Excel.Trendline trendline = series.Trendlines().Add(Excel.XlTrendlineType.xlLinear, System.Type.Missing, System.Type.Missing, 20, System.Type.Missing, System.Type.Missing, true, true, System.Type.Missing);
Excel.DataLabel datalabel_temp = trendline.DataLabel;
datalabel_temp.NumberFormat = "0.0000E+00";
}
Excel.Range a1 = range_chart.Columns[1].Find("-20");
Excel.Range a2 = worksheet_new.Cells[range_chart.Rows.Count, 1];
Excel.Range a3 = (Excel.Range)worksheet_new.get_Range(a1, a2);
for (int i = 1; i <= range_trendline.Columns.Count; i++)
{
Excel.Series series_temp = chartPage.SeriesCollection().Add(range_trendline.Columns[i]);
series_temp.XValues = a3;
Excel.Trendlines trendlines_temp = series_temp.Trendlines();
Excel.Trendline trendline_temp = trendlines_temp.Add(Excel.XlTrendlineType.xlLinear, System.Type.Missing, System.Type.Missing, 20, System.Type.Missing, System.Type.Missing, true, true, System.Type.Missing);
Excel.DataLabel datalabel_temp = trendline_temp.DataLabel;
datalabel_temp.NumberFormat = "0.0000E+00";
}
}
}
public void click_analyzeChart(Office.IRibbonControl control)
{
Excel.Chart chart_active = GetActiveChart();
List <double> x_intercepts = new List <double>();
foreach (Excel.Series series in chart_active.SeriesCollection())
{
Excel.Trendlines trendlines_temp = series.Trendlines();
if (trendlines_temp.Count > 0)
{
Excel.DataLabel dlabel_temp = trendlines_temp.Item(1).DataLabel;
string label = dlabel_temp.Text;
int x = label.IndexOf("R²");
int y = label.IndexOf("y");
string equation = label.Substring(y, x);
string r_val = label.Substring(x);
Regex pattern = new Regex(@"[\d]*\.?[\d]+(E[-+][\d]+)?");
Match match = pattern.Match(r_val);
double r_squared = Double.Parse(match.Value, System.Globalization.NumberStyles.Float);
if (r_squared < 0.9)
{
trendlines_temp.Item(1).Delete();
if (r_squared < 0.7)
{
series.Delete();
}
continue;
}
Regex pattern_x = new Regex(@"[\d]*\.?[\d]+(E[-+][\d]+)?");
MatchCollection matches = pattern_x.Matches(equation);
Stack <double> eq_vals = new Stack <double>();
foreach (Match match_temp in matches)
{
double val = Double.Parse(match_temp.Value, System.Globalization.NumberStyles.Float);
eq_vals.Push(val);
}
double y_int = eq_vals.Pop();
double y_slope = eq_vals.Pop();
if (y_slope < 1e-8)
{
trendlines_temp.Item(1).Delete();
series.Delete();
continue;
}
double x_int = (-1 * y_int) / y_slope;
x_intercepts.Add(x_int);
}
}
if (x_intercepts.Count > 0)
{
MessageBox.Show(x_intercepts.Average().ToString());
}
}
private Excel.Chart SetStyleXlsChart(Excel.Chart xlChart)
{
Excel.SeriesCollection sc = xlChart.SeriesCollection();
Excel.Series xlSeries;
for (int i = 0; i < sc.Count; i++)
{
xlSeries = sc.Item(i + 1);
xlSeries.ChartType = GetSeriesChartType(chart.Series[i]);
if (chart.Series[i].ChartType == SeriesChartType.Bar || chart.Series[i].ChartType == SeriesChartType.Column)
{
for (int j = 1; j <= chart.Series[i].Points.Count; j++)
{
xlSeries.Points(j).Interior.Color = ColorTranslator.ToOle(chart.Series[i].Color);
}
}
else
{
xlSeries.Border.Color = chart.Series[i].Color;
}
xlSeries.Border.LineStyle = GetLineDashStyle(chart.Series[i]);
xlSeries.Border.Weight = chart.Series[i].BorderWidth;
xlSeries.MarkerStyle = GetSeriesMarkerStyle(chart.Series[i]);
xlSeries.Name = chart.Series[i].Name;
progress++;
}
if (!string.IsNullOrWhiteSpace(chart.Titles[0].Text))
{
xlChart.HasTitle = true;
xlChart.ChartTitle.Text = chart.Titles[0].Text;
}
else
{
xlChart.HasTitle = false;
}
progress++;
xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).HasTitle = true;
xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).AxisTitle.Text = chart.ChartAreas[0].AxisX.Title;
xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).HasMajorGridlines = chart.ChartAreas[0].AxisX.MajorGrid.Enabled;
xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).HasMinorGridlines = chart.ChartAreas[0].AxisX.MinorGrid.Enabled;
if (xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).HasMajorGridlines)
{
xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).MajorGridlines.Border.LineStyle = GetLineDashStyle(chart.ChartAreas[0].AxisX.MajorGrid);
}
if (xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).HasMinorGridlines)
{
xlChart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary).MinorGridlines.Border.LineStyle = GetLineDashStyle(chart.ChartAreas[0].AxisX.MinorGrid);
}
progress += 4;
xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).HasTitle = true;
xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).AxisTitle.Text = chart.ChartAreas[0].AxisY.Title;
xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).HasMajorGridlines = chart.ChartAreas[0].AxisY.MajorGrid.Enabled;
xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).HasMinorGridlines = chart.ChartAreas[0].AxisY.MinorGrid.Enabled;
if (xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).HasMajorGridlines)
{
xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).MajorGridlines.Border.LineStyle = GetLineDashStyle(chart.ChartAreas[0].AxisY.MajorGrid);
}
if (xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).HasMinorGridlines)
{
xlChart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary).MinorGridlines.Border.LineStyle = GetLineDashStyle(chart.ChartAreas[0].AxisY.MinorGrid);
}
progress += 4;
xlChart.HasLegend = true;
progress++;
return(xlChart);
}
//формируем отчет в ексель по методу поспелова
private void Client_GetListExpertiseReportPospelovCompleted(object sender, ServiceReference1.GetListExpertiseReportPospelovCompletedEventArgs e)
{
if (e.Error == null)
{
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet = null;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add();
var experts = e.Result.list_marks_factors.GroupBy(o => o.id_expert).ToList();
for (int i = 0; i < experts.Count(); i++)
{
int id_expert = experts[i].Key;
if (i == 0)
{
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Name = "Отчет эксперта " + e.Result.list_marks_factors.Where(o => o.id_expert == id_expert).FirstOrDefault().experts.first_name;
//e.Result.list_marks.Where(o => o.id_expert == id_expert).FirstOrDefault().experts.first_name + " " + e.Result.list_marks.Where(o => o.id_expert == id_expert).FirstOrDefault().experts.second_name + " " + e.Result.list_marks.Where(o => o.id_expert == id_expert).FirstOrDefault().experts.patronymic;
}
else
{
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.Add(Type.Missing, xlWorkSheet, Type.Missing, Type.Missing);
xlWorkSheet.Name = "Отчет эксперта " + e.Result.list_marks_factors.Where(o => o.id_expert == id_expert).FirstOrDefault().experts.first_name;
}
var ls_marks_factors_expert = e.Result.list_marks_factors.Where(o => o.id_expert == id_expert).OrderBy(o => o.factors.priority).ToList();
var ls_marks_ribs_expert = e.Result.list_marks_ribs.Where(o => o.id_expert == id_expert).OrderBy(o => o.ribs.factors.priority).ToList();
var list_priority = e.Result.list_marks_factors.Select(o => o.factors.priority).GroupBy(o => o).OrderBy(o => o.Key).ToList();
int number_row = 0;
for (int j = 0; j < list_priority.Count() - 1; j++)
{
int number_col = 1;
number_row++;
int count_pr_row = ls_marks_factors_expert.Where(o => o.factors.priority == list_priority[j].Key + 1).Count();
for (int t = 0; t < ls_marks_factors_expert.Count(); t++)
{
//if (ls_marks_factors_expert[t].factors.priority == 0)
//{
// number_col++;
// xlWorkSheet.Cells[1,number_col] = ls_marks_factors_expert[t].factors.name
//}
if (ls_marks_factors_expert[t].factors.priority == list_priority[j].Key)
{
number_col++;
xlWorkSheet.Cells[number_row, number_col] = ls_marks_factors_expert[t].factors.name + "\n" + " " + Math.Round(ls_marks_factors_expert[t].value * 100);
//number_row++;
}
else if (ls_marks_factors_expert[t].factors.priority == list_priority[j].Key + 1)
{
number_row++;
//xlWorkSheet.Cells.Style.WrapText = true;
xlWorkSheet.Cells[number_row, 1] = ls_marks_factors_expert[t].factors.name + "\n" + Math.Round(ls_marks_factors_expert[t].value * 100);
var values = ls_marks_ribs_expert.Where(o => o.ribs.id_factor_in == ls_marks_factors_expert[t].factors.id_factor).ToList();
for (int l = 0; l < values.Count(); l++)
{
xlWorkSheet.Cells[number_row, 2 + l] = Math.Round(values[l].value * 100);
}
//var mark_rib = ls_marks_ribs_expert.Where(o=>o.ribs.factors.name == )
}
}
number_row++;
}
//Постройка диаграммы
int last_priority2 = list_priority.Max(o => o.Key);
var ls_res_fact_last2 = e.Result.list_marks_factors.Where(o => o.factors.priority == last_priority2 && o.id_expert == id_expert).OrderBy(o => o.factors.priority).ToList();
List <double> masY2 = new List <double>();
List <string> masX2 = new List <string>();
for (int t = 0; t < ls_res_fact_last2.Count(); t++)
{
masX2.Add(ls_res_fact_last2[t].factors.name);
masY2.Add(Math.Round(ls_res_fact_last2[t].value * 100));
}
//строим диаграмму
Excel.ChartObjects _xlCharts2 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject _myChart2 = (Excel.ChartObject)_xlCharts2.Add(10 * last_priority2, 80, 300, 250);
Excel.Chart _chartPage2 = _myChart2.Chart;
SeriesCollection _seriesCollection2 = (SeriesCollection)_chartPage2.SeriesCollection(Type.Missing);
Series _series2 = _seriesCollection2.NewSeries();
_series2.XValues = masX2.ToArray();
_series2.Values = masY2.ToArray();
_chartPage2.ApplyLayout(2, _chartPage2.ChartType);
_chartPage2.Legend.LegendEntries(_chartPage2.Legend.LegendEntries().Count).Delete();
_chartPage2.ChartTitle.Text = "Результат";
}
//Строим итоговую таблицу
var ls_results_factors = e.Result.list_results_factors.OrderBy(o => o.factors.priority).ToList();
var ls_results_ribs_expert = e.Result.list_results_ribs.OrderBy(o => o.ribs.factors.priority).ToList();
var list_priority_result = e.Result.list_results_factors.Select(o => o.factors.priority).GroupBy(o => o).OrderBy(o => o.Key).ToList();
int number_row2 = 0;
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.Add(Type.Missing, xlWorkSheet, Type.Missing, Type.Missing);
xlWorkSheet.Name = "Отчет общий ";
for (int j = 0; j < list_priority_result.Count() - 1; j++)
{
int number_col = 1;
number_row2++;
int count_pr_row = ls_results_factors.Where(o => o.factors.priority == list_priority_result[j].Key + 1).Count();
for (int t = 0; t < ls_results_factors.Count(); t++)
{
if (ls_results_factors[t].factors.priority == list_priority_result[j].Key)
{
number_col++;
xlWorkSheet.Cells[number_row2, number_col] = ls_results_factors[t].factors.name + "\n" + Math.Round(ls_results_factors[t].value * 100);
//number_row++;
}
else if (ls_results_factors[t].factors.priority == list_priority_result[j].Key + 1)
{
number_row2++;
//xlWorkSheet.Cells.Style.WrapText = true;
xlWorkSheet.Cells[number_row2, 1] = ls_results_factors[t].factors.name + "\n" + Math.Round(ls_results_factors[t].value * 100);
var values = ls_results_ribs_expert.Where(o => o.ribs.id_factor_in == ls_results_factors[t].factors.id_factor).ToList();
for (int l = 0; l < values.Count(); l++)
{
xlWorkSheet.Cells[number_row2, 2 + l] = Math.Round(values[l].value * 100);
}
//var mark_rib = ls_marks_ribs_expert.Where(o=>o.ribs.factors.name == )
}
}
number_row2++;
}
int last_priority = list_priority_result.Max(o => o.Key);
var ls_res_fact_last = e.Result.list_results_factors.Where(o => o.factors.priority == last_priority).OrderBy(o => o.factors.priority).ToList();
List <double> masY = new List <double>();
List <string> masX = new List <string>();
for (int i = 0; i < ls_res_fact_last.Count(); i++)
{
masX.Add(ls_res_fact_last[i].factors.name);
masY.Add(Math.Round(ls_res_fact_last[i].value * 100));
}
//строим диаграмму
Excel.ChartObjects _xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject _myChart = (Excel.ChartObject)_xlCharts.Add(10 * last_priority, 80, 300, 250);
Excel.Chart _chartPage = _myChart.Chart;
SeriesCollection _seriesCollection = (SeriesCollection)_chartPage.SeriesCollection(Type.Missing);
Series _series = _seriesCollection.NewSeries();
_series.XValues = masX.ToArray();
_series.Values = masY.ToArray();
_chartPage.ApplyLayout(2, _chartPage.ChartType);
_chartPage.Legend.LegendEntries(_chartPage.Legend.LegendEntries().Count).Delete();
_chartPage.ChartTitle.Text = "Результат";
//chartPage.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowValue, false, true, false, false, false, false, true, false, false);
//_chartPage.ChartType = Excel.XlChartType.xlPie;
//Excel.Range cellRange = (Excel.Range)xlWorkSheet.Cells[1, 1];
//Excel.Range rowRange = cellRange.EntireRow;
//rowRange.Insert(Excel.XlInsertShiftDirection.xlShiftDown, false);
//xlWorkSheet.Cells[1,1] = "awdwad";
//xlWorkSheet.Columns.AutoFit();
xlWorkSheet.Cells.Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Cells.Style.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
SaveFileDialog sFile = new SaveFileDialog();
sFile.Filter = "Excel files (*.xls)|*.xls|All files (*.*)|*.*";
try
{
if (sFile.ShowDialog() == true)
{
string path = sFile.FileName;
xlWorkBook.Application.DisplayAlerts = false;
//MessageBox.Show(path);
xlWorkBook.SaveAs(path, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
//xlApp.Visible = true;
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
MessageBox.Show("Ексель файл создан. Вы можете найти его по пути " + path);
}
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);
GC.Collect();
GC.WaitForPendingFinalizers();
}
catch (Exception ex)
{
MessageBox.Show("Error", ex.Message);
}
}
else
{
MessageBox.Show(e.Error.Message);
}
cLoading.stop();
}
private void button2_Click(object sender, EventArgs e)
{
try
{
string fileName = Dir + "\\" + string.Format("{0}.xls", Guid.NewGuid()).Replace(" ", "_");
//if (File.Exists(fileName))
//{
// try
// {
// File.Delete(fileName);
// }
// catch (Exception)
// {
// MessageBox.Show(string.Format("В настоящий момент используется файл:\r\n{0}\r\nДля создания договора закройте пожалуйста файл.", fileName),
// "Невозможно создать договор", MessageBoxButtons.OK, MessageBoxIcon.Warning);
// return;
// }
//}
Excel.Application xla = new Excel.Application();
xla.Visible = true;
Excel.Workbook wb = xla.Workbooks.Add(Excel.XlSheetType.xlWorksheet);
Excel.Worksheet ws = (Excel.Worksheet)xla.ActiveSheet;
// Now create the chart.
Excel.ChartObjects chartObjs = (Excel.ChartObjects)ws.ChartObjects();
Excel.ChartObject chartObj = chartObjs.Add(512, 80, 300, 300);
Excel.Chart xlChart = chartObj.Chart;
xlChart.ChartType = Excel.XlChartType.xlBarClustered;
// generate some random data
dateTimePicker1.Value = dateTimePicker1.Value.Date.AddDays(1 - dateTimePicker1.Value.Day);
dateTimePicker2.Value = dateTimePicker2.Value.Date.AddMonths(1).AddDays(-dateTimePicker2.Value.Day);
ws.Cells[2, 2] = string.Format("Результативность работы фирмы за период с {0} по {1}", dateTimePicker1.Value, dateTimePicker2.Value);
ws.Cells[4, 3] = "Период";
ws.Cells[4, 4] = "Количество сделок";
ws.Cells[4, 5] = "Сумма";
int row = 5;
for (var dateFrom = dateTimePicker1.Value; dateFrom <= dateTimePicker2.Value; dateFrom = dateFrom.AddMonths(1))
{
var dateTo = dateFrom.AddMonths(1).AddDays(-1);
var trips = dbj.Jurnal.Where(t => t.data >= dateFrom && t.data <= dateTo);
foreach (var i in trips)
{
ws.Cells[row, 3] = GetMonth(dateFrom.Month);
ws.Cells[row, 4] = trips.Count();
ws.Cells[row, 5] = i.summa;
}
row++;
}
Excel.Range xValues = ws.Range["C5", "C" + (row - 1)];
Excel.Range values = ws.Range["E5", "E" + (row - 1)];
Excel.SeriesCollection seriesCollection = xlChart.SeriesCollection();
Excel.Series series1 = seriesCollection.NewSeries();
series1.XValues = xValues;
series1.Values = values;
//xla.ActiveChart.HasTitle = true;
//xla.ActiveChart.ChartTitle.Text = "Тестовая диаграмма";
//xla.ActiveChart.ChartTitle.Font.Size = 13;
//xla.ActiveChart.ChartTitle.Font.Color = 254;
//xla.ActiveChart.ChartTitle.Shadow = true;
//xla.ActiveChart.ChartTitle.Border.LineStyle = Excel.Constants.xlSolid;
}
catch { MessageBox.Show("Ошибка Excel не найден"); }
}
private void AddDataPoints(Excel.Chart bubbleChart, Excel.Worksheet sourceWorksheet)
{
var dataMatrix = GetDataMatrixFromExcelWorksheet(sourceWorksheet);
var seriesCollection = bubbleChart.SeriesCollection();
var minXValue = double.MaxValue;
var minYValue = double.MaxValue;
var maxXValue = double.MinValue;
var maxYValue = double.MinValue;
foreach (var row in dataMatrix)
{
var series = seriesCollection.NewSeries();
series.Has3DEffect = true;
series.HasDataLabels = true;
var dataLabelText = row[0];
var xValue = Convert.ToDouble(row[1]);
var yValue = Convert.ToDouble(row[2]);
var revenue = Convert.ToDouble(row[3]) / 1000;
var projectTypeId = GetProjectTypeIdFromProjectTypeString(row[4]);
var bubbleSize = GetBubbleSizeFromRevenue(revenue);
series.Format.Fill.ForeColor.RGB = _projectTypeColors[projectTypeId];
series.XValues = new[] { xValue };
series.Values = new[] { yValue };
series.BubbleSizes = new[] { bubbleSize };
Excel.DataLabel dataLabel = series.DataLabels(1);
dataLabel.Text = dataLabelText;
if (xValue < minXValue)
{
minXValue = xValue;
}
if (yValue < minYValue)
{
minYValue = yValue;
}
if (xValue > maxXValue)
{
maxXValue = xValue;
}
if (yValue > maxYValue)
{
maxYValue = yValue;
}
}
var xAxis = (Excel.Axis)bubbleChart.Axes(Excel.XlAxisType.xlCategory);
xAxis.MinimumScale = (Math.Floor(minXValue / 100) - 1) * 100;
xAxis.MaximumScale = (Math.Ceiling(maxXValue / 100) + 1) * 100;
var yAxis = (Excel.Axis)bubbleChart.Axes(Excel.XlAxisType.xlValue);
yAxis.MinimumScale = (Math.Floor(minYValue * 10) - 1) / 10;
yAxis.MaximumScale = (Math.Ceiling(maxYValue * 10) + 1) / 10;
}
private void DrawBubbleLegend(Excel.Chart chart)
{
var revenues = new double[] { 200, 400, 600, 800, 1000 };
const double revenueIncrement = 200;
var zeroArray = revenues.Select(x => x).ToArray();
var bubbleSizes = revenues.Select(x => GetBubbleSizeFromRevenue(x - revenueIncrement / 2)).ToArray();
Excel.Series dummyBubbleSeries = chart.SeriesCollection().NewSeries();
dummyBubbleSeries.XValues = zeroArray;
dummyBubbleSeries.Values = zeroArray;
dummyBubbleSeries.BubbleSizes = bubbleSizes;
var bubbleWidths = new List <float>();
foreach (Excel.Point bubble in dummyBubbleSeries.Points())
{
bubbleWidths.Add((float)bubble.Width);
}
var bubbleLegendNeededSpace = bubbleWidths.Max() + BubbleLegendTop;
chart.PlotArea.Height -= bubbleLegendNeededSpace;
float currentLeft = BubbleLegendLeft + RevenueTitleLength;
for (var i = 0; i < bubbleWidths.Count; i++)
{
var bubbleWidth = bubbleWidths[i];
var topMargin = (float)chart.PlotArea.Height + BubbleLegendTop + bubbleWidths.Max() / 2 - bubbleWidth / 2;
var oval = chart.Shapes.AddShape(MsoAutoShapeType.msoShapeOval,
currentLeft,
topMargin,
bubbleWidth,
bubbleWidth);
oval.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbWhite;
oval.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbBlack;
var label = chart.Shapes.AddLabel(MsoTextOrientation.msoTextOrientationHorizontal,
currentLeft + bubbleWidth + BubbleLegendLabelMargin,
topMargin + bubbleWidth / 2 - RevenueTextLabelHeight / 2,
RevenueTextLabelLength,
RevenueTextLabelHeight);
var revenueText = string.Empty;
if (i == 0)
{
revenueText = "<" + revenues[i];
}
else if (i == revenues.Length - 1)
{
revenueText = ">" + revenues[i - 1];
}
else
{
revenueText = revenues[i - 1] + "-" + revenues[i];
}
label.TextFrame.Characters().Text = revenueText;
currentLeft += bubbleWidth + RevenueTextLabelLength + BubbleLegendLabelMargin * 2;
}
var revenueLabel = chart.Shapes.AddLabel(MsoTextOrientation.msoTextOrientationHorizontal,
BubbleLegendLeft,
BubbleLegendTop + bubbleWidths.Max() / 2 + (float)chart.PlotArea.Height - RevenueTextLabelHeight / 2,
RevenueTitleLength,
RevenueTextLabelHeight);
revenueLabel.TextFrame.Characters().Text = "Turnover kkr:";
dummyBubbleSeries.Delete();
}
/// <summary>
/// Writes an Excel worksheet containing a Respiratory Signal's values and Plot
/// </summary>
/// <param name="ws"> The Excel worksheet object </param>
/// <param name="SignalName"> The name of the respiratory signal </param>
/// <param name="table"> The respiratory signal values and peak/onset locations </param>
/// <param name="ROWS"> The number of rows in the table </param>
/// <param name="COLUMNS"> The number of columns in the table </param>
public static void AddRespiratorySignalToWorksheet(Excel.Worksheet ws, string SignalName, object[,] table, int ROWS, int COLUMNS)
{
// Make Table with Values
Excel.Range range = ws.Range[ws.Cells[3, 2], ws.Cells[3 + ROWS - 1, 2 + COLUMNS - 1]];
range.Value = table;
ws.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, range, System.Reflection.Missing.Value, Excel.XlYesNoGuess.xlGuess, System.Reflection.Missing.Value).Name = ws.Name;
ws.ListObjects[ws.Name].TableStyle = "TableStyleLight9";
ws.Columns["A:I"].ColumnWidth = 20;
ws.Columns["E:H"].Hidden = true;
ws.Columns["B:H"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
// Add Conditional Formatting
Excel.Range range2 = ws.Range[ws.Cells[4, 2], ws.Cells[3 + ROWS - 1, 2 + COLUMNS - 1]];
range2.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, System.Reflection.Missing.Value, "=NOT(ISBLANK($E4))", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
range2.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, System.Reflection.Missing.Value, "=NOT(ISBLANK($F4))", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
range2.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, System.Reflection.Missing.Value, "=NOT(ISBLANK($G4))", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
range2.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, System.Reflection.Missing.Value, "=NOT(ISBLANK($H4))", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
range2.FormatConditions[1].Interior.Color = 5296274;
range2.FormatConditions[2].Interior.Color = 255;
range2.FormatConditions[3].Interior.Color = 65535;
range2.FormatConditions[4].Interior.Color = 15773696;
range2.Columns[2].NumberFormat = "m/d/yyyy h:mm:ss.000";
// Add Chart
Excel.Chart chart = ((Excel.ChartObject)((Excel.ChartObjects)ws.ChartObjects()).Add(500, 100, 900, 500)).Chart;
chart.SetSourceData(range.Columns["B:G"]);
chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlXYScatterLines;
chart.ChartWizard(Source: range.Columns["B:G"], Title: SignalName, CategoryTitle: "Time", ValueTitle: SignalName);
chart.PlotVisibleOnly = false;
((Excel.Series)chart.SeriesCollection(1)).ChartType = Excel.XlChartType.xlXYScatterLinesNoMarkers;
((Excel.Series)chart.SeriesCollection(2)).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare;
((Excel.Series)chart.SeriesCollection(3)).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare;
((Excel.Series)chart.SeriesCollection(4)).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare;
((Excel.Series)chart.SeriesCollection(5)).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare;
((Excel.Series)chart.SeriesCollection(2)).Format.Fill.ForeColor.RGB = 5296274;
((Excel.Series)chart.SeriesCollection(3)).Format.Fill.ForeColor.RGB = 255;
((Excel.Series)chart.SeriesCollection(4)).Format.Fill.ForeColor.RGB = 65535;
((Excel.Series)chart.SeriesCollection(5)).Format.Fill.ForeColor.RGB = 15773696;
((Excel.Series)chart.SeriesCollection(1)).Format.Line.ForeColor.RGB = 38450;
((Excel.Series)chart.SeriesCollection(2)).Format.Line.ForeColor.RGB = 5296274;
((Excel.Series)chart.SeriesCollection(3)).Format.Line.ForeColor.RGB = 255;
((Excel.Series)chart.SeriesCollection(4)).Format.Line.ForeColor.RGB = 65535;
((Excel.Series)chart.SeriesCollection(5)).Format.Line.ForeColor.RGB = 15773696;
System.Runtime.InteropServices.Marshal.ReleaseComObject(chart);
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
System.Runtime.InteropServices.Marshal.ReleaseComObject(range2);
}
private void CreateDocument()
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
app.Visible = true;
app.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized;
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Workbook wb = app.Workbooks.Open(@"C:\LPA_IPAMLx_V2\LPA_IPAM.xlsx",
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
Worksheet ws = wb.Worksheets[1];
int col = 1;
int row = 16;
ws.Range["B8"].Value = Convert.ToString(replaceNome).TrimStart();
ws.Range["B9"].Value = Convert.ToString(replaceNrAluno).TrimStart();
ws.Range["B10"].Value = Convert.ToString(replaceCurso).TrimStart();
ws.Range["B11"].Value = Convert.ToString(replaceData).TrimStart();
GetNotas();
foreach (Notas nota in listaNotas)
{
ws.Cells[row, col].Font.Size = 10;
ws.Cells[row, col].Value = nota.UC.TrimStart();
ws.Cells[row, col + 5].Font.Size = 10;
ws.Cells[row, col + 5].Value = nota.Nota;
ws.Cells[row, col + 6].Font.Size = 10;
ws.Cells[row, col + 6].Value = nota.ECTS;
row++;
}
GetMedia((string)replaceNrAluno, (string)replaceCdCurso);
ws.Range["B59"].Value = (string)replaceMedia + " valores";
ws.Range["B73"].Value = Convert.ToString(replaceNome).TrimStart();
ws.Range["B74"].Value = Convert.ToString(replaceNrAluno).TrimStart();
ws.Range["B75"].Value = Convert.ToString(replaceCurso).TrimStart();
ws.Range["B76"].Value = Convert.ToString(replaceData).TrimStart();
Competencias competencias = GetDadosCompetencias((string)replaceNrAluno);
Microsoft.Office.Interop.Excel.ChartObjects xlCharts = (Microsoft.Office.Interop.Excel.ChartObjects)ws.ChartObjects(Type.Missing);
Microsoft.Office.Interop.Excel.ChartObject myChart = (Microsoft.Office.Interop.Excel.ChartObject)xlCharts.Add(5, 1165, 470, 300);
Microsoft.Office.Interop.Excel.Chart chartPage = myChart.Chart;
Microsoft.Office.Interop.Excel.SeriesCollection seriesCollection = (Microsoft.Office.Interop.Excel.SeriesCollection)chartPage.SeriesCollection();
var ser = seriesCollection.NewSeries();
chartPage.Legend.Delete();
ser.Values = new double[] { competencias.adaptacao, competencias.trabalhar, competencias.decisoes, competencias.objactivos, competencias.ideias, competencias.aprendizagem, competencias.mentalidadeGlobal, competencias.gestaoEquipas };
ser.XValues = new string[] { "Adaptação", "Trabalhar com os Outros", "Tomar Decisões", "Alcançar Objetivos", "Geração de Ideias", "Aprendizagem", "Mentalidade Global", "Gestão de Equipas" };
//chartRange = ws.get_Range("A1", "d5");
//chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlBarClustered;
chartPage.SeriesCollection(1).Points(1).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
chartPage.SeriesCollection(1).Points(2).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
chartPage.SeriesCollection(1).Points(3).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
chartPage.SeriesCollection(1).Points(4).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
chartPage.SeriesCollection(1).Points(5).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
chartPage.SeriesCollection(1).Points(6).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
chartPage.SeriesCollection(1).Points(7).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
chartPage.SeriesCollection(1).Points(8).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Range["B127"].Value = Convert.ToString(replaceNome).TrimStart();
ws.Range["B128"].Value = Convert.ToString(replaceNrAluno).TrimStart();
ws.Range["B129"].Value = Convert.ToString(replaceCurso).TrimStart();
ws.Range["B130"].Value = Convert.ToString(replaceData).TrimStart();
GetLinguas((string)replaceNrAluno);
GetRespAcademicas((string)replaceNrAluno);
GetEstagios((string)replaceNrAluno);
GetMobilidade((string)replaceNrAluno);
GetPremios((string)replaceNrAluno);
GetAtividades((string)replaceNrAluno);
GetOutrasExperiencias((string)replaceNrAluno);
GetRespSocial((string)replaceNrAluno);
col = 1;
row = 135;
if (listaLinguas.Count > 0)
{
string header = "LÍNGUAS";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (Linguas l in listaLinguas)
{
row++;
ws.Cells[row, col].Value = " > " + l.lingua + " | Nível " + l.nivel;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaRespAcademicas.Count > 0)
{
string header = "RESPONSABILIDADES ACADÉMICAS";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (ResponsabilidadesAcademicas l in listaRespAcademicas)
{
row++;
ws.Cells[row, col].Value = " > " + l.responsabilidade + " | " + l.anoLetivo;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaEstagios.Count > 0)
{
string header = "ESTÁGIOS";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (Estagios l in listaEstagios)
{
if (l.dataInicio != "01/01/1900")
{
row++;
ws.Cells[row, col].Value = " > " + l.tipoEstagio + " na " + l.empresa + " | De " + l.dataInicio.Substring(0, 10) + " a " + l.dataFim.Substring(0, 10);
}
else
{
row++;
ws.Cells[row, col].Value = " > " + l.tipoEstagio + " na " + l.empresa;
}
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaMobilidade.Count > 0)
{
string header = "MOBILIDADE INTERNACIONAL";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (Mobilidade l in listaMobilidade)
{
row++;
ws.Cells[row, col].Value = " > " + l.tipo + " na " + l.programa + " | " + l.anoLetivo;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaRespSocial.Count > 0)
{
string header = "RESPONSABILIDADE SOCIAL & VOLUNTARIADO";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (ResposabilidadeSocial l in listaRespSocial)
{
row++;
ws.Cells[row, col].Value = " > " + l.AcaoSocial + " | " + l.AnoLetivo;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaPremios.Count > 0)
{
string header = "PRÉMIOS & RECONHECIMENTOS";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (Premios l in listaPremios)
{
row++;
ws.Cells[row, col].Value = " > " + l.premio + " | " + l.anoLetivo;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaAtividades.Count > 0)
{
string header = "ATIVIDADES DESPORTIVAS";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (ActDesportivas l in listaAtividades)
{
row++;
ws.Cells[row, col].Value = " > " + l.atividade + " | " + l.anoLetivo;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
if (listaExperiencias.Count > 0)
{
string header = "OUTRAS EXPERIÊNCIAS";
ws.Cells[row, col].Font.Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
ws.Cells[row, col].Font.Bold = true;
ws.Cells[row, col].Value = header;
Microsoft.Office.Interop.Excel.Range cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
Microsoft.Office.Interop.Excel.Borders border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 4d;
border[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.Color.FromArgb(0, 212, 83, 10);
foreach (OutrasExperiencias l in listaExperiencias)
{
row++;
ws.Cells[row, col].Value = " > " + l.experiencia + " | " + l.anoLetivo;
}
cells = ws.Range[ws.Cells[row, col], ws.Cells[row, col + 6]];
border = cells.Borders;
border[XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border[XlBordersIndex.xlEdgeBottom].Weight = 2d;
row += 2;
}
string data = Convert.ToString(DateTime.Now.ToLongDateString());
ws.Cells[164, 1].Value = "Lisboa, " + data;
int nrCertificado = GravaCertificado(competencias);
string anoLetivo = getAnoLetivo((string)replaceNrAluno, (string)replaceCdCurso);
ws.Range["E11"].Value = nrCertificado + " | POR | " + anoLetivo;
ws.Range["E76"].Value = nrCertificado + " | POR | " + anoLetivo;
ws.Range["E130"].Value = nrCertificado + " | POR | " + anoLetivo;
}
/// <summary>
/// Метод создания и сохранения документов
/// в форматах Microsoft Word (doc, PDF),
/// Excel (exls)
/// </summary>
/// <param name="type">Тип создаваемого документа
/// отчёт или статистика</param>
/// <param name="format">Формат сохранения
/// документ или таблица</param>
/// <param name="name">Название документа</param>
/// <param name="table">Входная таблица с данными</param>
public void Document_Create(Document_Type type,
Document_Format format, string name,
DataTable table)
{
//Получение данных о конфигурации документа
Configuration_class configuration_Class
= new Configuration_class();
configuration_Class.Document_Configuration_Get();
//Проверка на пустоту названия
switch (name != "" || name != null)
{
case true:
//Выбор формата либо Word либо Excel
switch (format)
{
case Document_Format.Word:
//Запуск процесса в дистпечере задач
word.Application application
= new word.Application();
//создание документа в процессе
word.Document document
//Присвоение документа процессу, Visible: true
//возможность редактирования документа
= application.Documents.Add(Visible: true);
try
{
//Объявление дипапазона для формирования текста
word.Range range = document.Range(0, 0);
//89Настройка отступов в документе
document.Sections.PageSetup.LeftMargin
= application.CentimetersToPoints(
(float)Configuration_class.
doc_Left_Merge);
document.Sections.PageSetup.TopMargin
= application.CentimetersToPoints(
(float)Configuration_class.
doc_Top_Merge);
document.Sections.PageSetup.RightMargin
= application.
CentimetersToPoints((float)
Configuration_class.doc_Right_Merg);
document.Sections.PageSetup.BottomMargin
= application.CentimetersToPoints(
(float)Configuration_class.
doc_Bottom_Merge);
//Присвоение текстового знеачения в дипазон
range.Text =
"Мвидео";
//Настройка выравнивания текста
range.ParagraphFormat.Alignment =
word.WdParagraphAlignment.
wdAlignParagraphCenter;
//Настройка интервала после абзаца
range.ParagraphFormat.SpaceAfter = 1;
//Настройка интервала перед абзаца
range.ParagraphFormat.SpaceBefore = 1;
//Настройка межстрочного интервала
range.ParagraphFormat.LineSpacingRule
= word.WdLineSpacing.wdLineSpaceSingle;
//Настройка названия шрифта
range.Font.Name = "Times New Roman";
//Настройка размера шрифта
range.Font.Size = 12;
//Добавление параграфов
document.Paragraphs.Add(); //В конце текста
document.Paragraphs.Add(); //Свободный
document.Paragraphs.Add(); //Для будущего текста
//Параграф для названия документа
word.Paragraph Document_Name
= document.Paragraphs.Add();
//Настройка параграфа через свойство диапазона
Document_Name.Format.Alignment
= word.WdParagraphAlignment.wdAlignParagraphCenter;
Document_Name.Range.Font.Name = "Times New Roman";
Document_Name.Range.Font.Size = 16;
//Проверка на тип документа, отчёт или статистика
switch (type)
{
case Document_Type.Report:
Document_Name.Range.Text = "ОТЧЁТ";
break;
case Document_Type.Statistic:
Document_Name.Range.Text = "СТАТИСТИЧЕСКИЙ ОТЧЁТ";
break;
}
document.Paragraphs.Add();
document.Paragraphs.Add();
document.Paragraphs.Add();
word.Paragraph statparg = document.Paragraphs.Add();
//Создание области таблицы в документе
word.Table stat_table
//Добавление таблицы в область документа
//Указывается параграф в котором документ создан
//Количество строк и столбцов
= document.Tables.Add(statparg.Range,
table.Rows.Count, table.Columns.Count);
//Настройка границ таблицы внутренние
stat_table.Borders.InsideLineStyle
= word.WdLineStyle.wdLineStyleSingle;
//Настройка границ таблицы внешние
stat_table.Borders.OutsideLineStyle
= word.WdLineStyle.wdLineStyleSingle;
//Выравнивание текста внутри ячеек по ширине
stat_table.Rows.Alignment
= word.WdRowAlignment.wdAlignRowCenter;
//Выравнивание текста внутри ячеек по высоте
stat_table.Range.Cells.VerticalAlignment =
word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;
stat_table.Range.Font.Size = 11;
stat_table.Range.Font.Name = "Times New Roman";
//Индексация столбцов и строк в Word начинается с 1,1
for (int row = 1; row <= table.Rows.Count; row++)
{
for (int col = 1; col <= table.Columns.Count; col++)
{
stat_table.Cell(row, col).Range.Text
= table.Rows[row - 1][col - 1].ToString();
}
}
document.Paragraphs.Add();
document.Paragraphs.Add();
//Парадграф с фиксациейц даты создания документа
word.Paragraph Footparg = document.Paragraphs.Add();
Footparg.Range.Text =
string.Format("Дата создания \t\t\t{0}",
DateTime.Now.ToString("dd.MM.yyyy"));
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
}
finally
{
switch (format)
{
case Document_Format.Word:
//Сохранение документа с названием из метода,
//и в формате doc
document.SaveAs2(string.Format("{0}\\{1}", Environment.CurrentDirectory, name),
word.WdSaveFormat.wdFormatDocument);
break;
case Document_Format.PDF:
//Сохранение документа в формате PDF
document.SaveAs2(string.Format("{0}\\{1}", Environment.CurrentDirectory, name),
word.WdSaveFormat.wdFormatPDF);
break;
}
//Закрываем документ
document.Close();
//Выходим из процесса с его закрытием
application.Quit();
}
break;
case Document_Format.Excel:
//Создание процесса Excel
excel.Application application_ex
= new excel.Application();
//Создание книги
excel.Workbook workbook
= application_ex.Workbooks.Add();
//Создание страницы
excel.Worksheet worksheet
= (excel.Worksheet)workbook.ActiveSheet;
try
{
switch (type)
{
case Document_Type.Report:
//Название страницы
worksheet.Name = "Отчёт";
for (int row = 0; row < table.Rows.Count; row++)
{
for (int col = 0; col < table.Columns.Count; col++)
{
//ЗАнесение данных в ячейку
worksheet.Cells[row + 1][col + 1]
= table.Rows[row][col].ToString();
}
}
//Указание диапазона работы с ячеёками листа
excel.Range border
//Начало диапазона
= worksheet.Range[worksheet.Cells[1, 1],
//Динамический конец диапазона в зависимости от
//выдодимых данных
worksheet.Cells[table.Rows.Count + 1]
[table.Columns.Count + 1]];
//Стиль линий границ ячеек
border.Borders.LineStyle = excel.XlLineStyle.xlContinuous;
//Выравнивание во высоте
border.VerticalAlignment = excel.XlHAlign.xlHAlignCenter;
//Выравнивание по ширине
border.HorizontalAlignment = excel.XlHAlign.xlHAlignCenter;
//Внесение даты создания документа
worksheet.Cells[table.Rows.Count + 3][2]
= string.Format("Дата создания {0}",
DateTime.Now.ToString());
//Объединение ячеек
worksheet.Range[worksheet.Cells[table.Rows.Count + 3, 2],
worksheet.Cells[table.Rows.Count + 2,
table.Columns.Count + 2]].Merge();
break;
case Document_Type.Statistic:
worksheet.Name = "Статистический отчёт";
for (int row = 0; row < table.Rows.Count; row++)
{
for (int col = 0; col < table.Columns.Count; col++)
{
worksheet.Cells[row + 1][col + 1]
= table.Rows[row][col].ToString();
}
}
excel.Range border1
= worksheet.Range[worksheet.Cells[1, 1],
worksheet.Cells[table.Rows.Count + 1]
[table.Columns.Count + 1]];
border1.Borders.LineStyle
= excel.XlLineStyle.xlContinuous;
border1.VerticalAlignment
= excel.XlHAlign.xlHAlignCenter;
border1.HorizontalAlignment
= excel.XlHAlign.xlHAlignCenter;
worksheet.Cells[table.Rows.Count + 3][2]
= string.Format("Дата создания {0}",
DateTime.Now.ToString());
worksheet.Range[worksheet.Cells[table.Rows.Count + 3, 2],
worksheet.Cells[table.Rows.Count + 2,
table.Columns.Count + 2]].Merge();
//Класс области графиков
excel.ChartObjects chartObjects
= (excel.ChartObjects)worksheet.ChartObjects(
Type.Missing);
//Область размещения графиков: отступы слева сверху,
//размер ширина и высота
excel.ChartObject chartObject
= chartObjects.Add(300, 50, 250, 250);
//Объявление области графика
excel.Chart chart = chartObject.Chart;
//Объявление колекции построений графиков
excel.SeriesCollection seriesCollection
= (excel.SeriesCollection)chart.SeriesCollection(
Type.Missing);
//Объявление посторения графика
excel.Series series = seriesCollection.NewSeries();
//Тип графика
chart.ChartType = excel.XlChartType.xl3DColumn;
//Диапазон значений по оси X
series.XValues =
worksheet.get_Range("B2", "B" + table.Rows.Count + 1);
//Диапазон значений по оси Y
series.Values =
worksheet.get_Range("C2", "C" + table.Rows.Count + 1);
break;
}
}
catch
{
}
finally
{
//Сохранение книги
workbook.SaveAs(string.Format("{0}\\{1}", Environment.CurrentDirectory, name), application_ex.DefaultSaveFormat);
//Закрытие книги
workbook.Close();
//Завершение процесса
application_ex.Quit();
}
break;
}
break;
case false:
System.Windows.Forms.MessageBox.Show
("Введите название документа");
break;
}
}
/// <summary>
/// Method sets the sheet with common results
/// </summary>
/// <param name="worksheet">Worksheet where the information will be saved</param>
/// <param name="calculationTimes">Time results for each calculation types</param>
/// <param name="results">results for each calculation types</param>
private static void SetCommonResults(Excel.Worksheet worksheet, Dictionary <CalculationTypeName, double> calculationTimes,
Dictionary <CalculationTypeName, List <DEVariable> > results)
{
worksheet.Name = "Common results";
int rowIndex = 1;
int columnIndex = 1;
worksheet.Cells[rowIndex, columnIndex] = "Calculation results";
rowIndex++;
int i = 0;
foreach (KeyValuePair <CalculationTypeName, List <DEVariable> > item in results)
{
int j = 0;
List <DEVariable> result;
if (i == 0)
{
result = item.Value;
foreach (DEVariable variable in result)
{
worksheet.Cells[rowIndex + j + 1, columnIndex] = variable.Name;
j++;
}
}
worksheet.Cells[rowIndex, columnIndex + i + 1] = item.Key.ToString();
j = 0;
result = item.Value;
foreach (DEVariable variable in result)
{
worksheet.Cells[rowIndex + 1 + j, columnIndex + i + 1] = variable.Value;
j++;
}
i++;
}
rowIndex += 3;
worksheet.Cells[rowIndex, columnIndex] = "Time results";
rowIndex++;
i = 0;
foreach (KeyValuePair <CalculationTypeName, double> calculationTime in calculationTimes)
{
worksheet.Cells[rowIndex, columnIndex + i] = calculationTime.Key.ToString();
worksheet.Cells[rowIndex + 1, columnIndex + i] = calculationTime.Value;
i++;
}
string leftTopTimeChart = GetExcelColumnName(1) + rowIndex.ToString();
string rightDownTimeChart = GetExcelColumnName(calculationTimes.Count) + (rowIndex + 1).ToString();
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)worksheet.ChartObjects(Type.Missing);
Excel.ChartObject mychart = xlCharts.Add(10, 80, 500, 450);
Excel.Chart chartPage = mychart.Chart;
chartRange = worksheet.get_Range(leftTopTimeChart, rightDownTimeChart);
chartPage.SetSourceData(chartRange);
chartPage.ChartType = Excel.XlChartType.xl3DColumnClustered;
chartPage.SeriesCollection(1).Name = "Calculation times";
}
private void GenerateReportTopGenres(Dictionary <string, double> dictionary)
{
excel.Application excelApp = new excel.Application();
excel.Workbook workbook;
excel.Worksheet workSheet;
workbook = excelApp.Workbooks.Add();
//workSheet = (excel.Worksheet)workbook.Worksheets.get_Item(1);
workSheet = workbook.ActiveSheet;
int i = 1;
foreach (var word in dictionary.OrderByDescending(q => q.Value))
{
workSheet.Cells[1, i] = word.Key;
workSheet.Cells[2, i] = word.Value;
i++;
}
excel.Range Erange = workSheet.Range["B3"];
Erange.Formula = "=SUM(A2:J2)";
Erange.FormulaHidden = false;
excel.Borders border = Erange.Borders;
border.LineStyle = excel.XlLineStyle.xlContinuous;
excel.ChartObjects chObs = (excel.ChartObjects)workSheet.ChartObjects();
excel.ChartObject chOb = chObs.Add(5, 50, 300, 300);
excel.Chart xlchart = chOb.Chart;
excel.Range Erange2 = workSheet.Range["A1:J1"];
excel.Range Erange3 = workSheet.Range["A3:J1"];
xlchart.ChartType = excel.XlChartType.xlColumnClustered;
excel.SeriesCollection seriesCollection = (excel.SeriesCollection)xlchart.SeriesCollection(Type.Missing);
excel.Series series = seriesCollection.NewSeries();
//series.XValues = workSheet.Range["A1:J1"];
string[] matrix = new string[10];
for (int j = 0; j < 10; j++)
{
matrix[j] = (string)(workSheet.Cells[1, j + 1] as excel.Range).Value;
}
//series.XValues = workSheet.Range[workSheet.Cells[1, 3]];
series.XValues = matrix;
series.Values = workSheet.get_Range("A2", "J2");
xlchart.HasTitle = true;
xlchart.ChartTitle.Text = "Жанры и их рейтинги";
xlchart.HasLegend = true;
series.Name = "Жанры";
excelApp.Visible = true;
excelApp.UserControl = true;
string outputPath = @"C:\Users\acer\Desktop\Учеба\АИС\Отчет по рейтингу жанров (" + Guid.NewGuid().ToString() + ").xlsx";
workbook.SaveAs(outputPath);
object misValue = System.Reflection.Missing.Value;
xlchart.Export("C:\\Users\\acer\\Desktop\\Учеба\\АИС\\Graf.bmp", "BMP", misValue);
string path = @"C:\Users\acer\Desktop\Учеба\АИС\Отчет по рейтингу жанров.doc";
WordReportTopBookOfGenre wordReport = new WordReportTopBookOfGenre(path);
wordReport.GenerateReportTopGenres(dictionary);
excelApp.Quit();
}
//Revenues Comparison
public string RevenuesComparison(int ByDDMMYYYY, string DateFrom, string DateTo, bool ExportToExcel = false, string ExportPath = "")
{
try
{
string ReportName = "Revenues Comparison Report";
DataTable aTable = ReportsMgmt.RevenuesComparions(ByDDMMYYYY, DateFrom, DateTo);
if (aTable.Rows.Count > 0)
{
xlApp = new Excel.Application();
xlApp.DisplayAlerts = false;
xlApp.Visible = false;
xlApp.SheetsInNewWorkbook = 1;
xlWorkBook = xlApp.Workbooks.Add(misValue);//misValue
int RowCnt = 1;
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Name = ReportName;
List <string> aHeader = ReportsHelper.ImportReportHeader(0, 1);
List <string> aFooter = ReportsHelper.ImportReportHeader(1, 1);
for (int i = 0; i < aHeader.Count; i++)
{
xlWorkSheet.Cells[RowCnt, 2] = aHeader[i];
RowCnt++;
}
xlWorkSheet.Cells[RowCnt++, 1] = ReportName;
xlWorkSheet.Cells[RowCnt, 2] = "Date From:\t" + DateFrom;
xlWorkSheet.Cells[RowCnt, 3] = "Date To:\t" + DateTo;
RowCnt++;
xlWorkSheet.Cells[RowCnt, 1] = "Date";
xlWorkSheet.Cells[RowCnt, 2] = "Total Cost JOD";
xlWorkSheet.Cells[RowCnt, 3] = "Total Sales JOD";
xlWorkSheet.Cells[RowCnt, 4] = "Gross Profit JOD";
xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).Font.Bold = true;
//xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).WrapText = true;
xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
RowCnt++;
int DataStart = RowCnt;
foreach (DataRow aRow in aTable.Rows)
{
if (ByDDMMYYYY == 0) //BY DAY
{
xlWorkSheet.Cells[RowCnt, 1] = aRow["Day"].ToString() + '\\' + aRow["Month"].ToString() + "\\" + aRow["Year"].ToString();
}
else if (ByDDMMYYYY == 1)//BY MONTH
{
xlWorkSheet.Cells[RowCnt, 1] = aRow["Month"].ToString() + '\\' + aRow["Year"].ToString();
}
else //BY YEAR
{
xlWorkSheet.Cells[RowCnt, 1] = aRow["Year"].ToString();
}
xlWorkSheet.Cells[RowCnt, 2] = Math.Round(double.Parse(aRow["TotalCost"].ToString()), 2);
xlWorkSheet.Cells[RowCnt, 3] = Math.Round(double.Parse(aRow["TotalPrice"].ToString()), 2);
xlWorkSheet.Cells[RowCnt, 4] = Math.Round(double.Parse(aRow["TotalProfit"].ToString()), 2);
RowCnt++;
}
xlWorkSheet.get_Range("A" + DataStart.ToString(), "K" + RowCnt.ToString()).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
xlWorkSheet.get_Range("A" + DataStart.ToString(), "K" + RowCnt.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
oRng = xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString());
oRng.EntireColumn.AutoFit();
Excel.ChartObjects myCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
xlWorkSheet.DisplayRightToLeft = false;
int size = aTable.Rows.Count * 100;
if (size > 600)
{
size = 600;
}
if (size < 300)
{
size = 300;
}
Excel.ChartObject myChart = (Excel.ChartObject)myCharts.Add(0, RowCnt * 15, size, 300);
Excel.Chart chartPage = myChart.Chart;
Excel.SeriesCollection seriesCollection = chartPage.SeriesCollection();
Excel.Series series1 = seriesCollection.NewSeries();
Excel.Series series2 = seriesCollection.NewSeries();
Excel.Series series3 = seriesCollection.NewSeries();
RowCnt--; //because we started from 1 suppose to be 0
series1.Name = "Total Cost";
series1.XValues = xlWorkSheet.Range["A" + DataStart.ToString(), "A" + RowCnt.ToString()];
series1.Values = xlWorkSheet.Range["B" + DataStart.ToString(), "B" + RowCnt.ToString()];
series2.Name = "Total Sales";
series2.XValues = xlWorkSheet.Range["A" + DataStart.ToString(), "A" + RowCnt.ToString()];
series2.Values = xlWorkSheet.Range["C" + DataStart.ToString(), "C" + RowCnt.ToString()];
series3.Name = "Gross Profit";
series3.XValues = xlWorkSheet.Range["A" + DataStart.ToString(), "A" + RowCnt.ToString()];
series3.Values = xlWorkSheet.Range["D" + DataStart.ToString(), "D" + RowCnt.ToString()];
chartPage.ChartType = Excel.XlChartType.xlLineMarkers;
Excel.Axis axis = chartPage.Axes(Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary) as Excel.Axis;
series1.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowBubbleSizes);
series2.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowBubbleSizes);
series3.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowBubbleSizes);
object format = Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml;
xlWorkSheet.SaveAs(ReportsHelper.TempOutputPath, format);
if (ExportToExcel)
{
format = Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7;
xlWorkSheet.SaveAs(ExportPath, format);
}
xlApp.UserControl = false;
//Once done close and quit Excel
return("TRUE");
}
else
{
return("EMPTY");
}
}
catch (Exception ex)
{
MessageBox.Show("{EXCEPTION in Revenues Comparison}" + ex.Message + ex.ToString());
return("ERROR " + ex.Message);
}
finally
{
//Once done close and quit Excel
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
}
//формируем отчет в ексель по методу паттерна
private void Client_GetListExpertiseReportCompleted(object sender, ServiceReference1.GetListExpertiseReportCompletedEventArgs e)
{
if (e.Error == null)
{
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet = null;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add();
var experts = e.Result.list_marks.GroupBy(o => o.id_expert).ToList();
int count_project = e.Result.list_marks.GroupBy(o => o.id_project).ToList().Count();
var count_criterion = e.Result.list_marks.GroupBy(o => o.id_criterion).ToList().Count();
for (int i = 0; i < experts.Count(); i++)
{
int id_expert = experts[i].Key;
if (i == 0)
{
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Name = "Отчет эксперта " + e.Result.list_marks.Where(o => o.id_expert == id_expert).FirstOrDefault().experts.first_name;
//e.Result.list_marks.Where(o => o.id_expert == id_expert).FirstOrDefault().experts.first_name + " " + e.Result.list_marks.Where(o => o.id_expert == id_expert).FirstOrDefault().experts.second_name + " " + e.Result.list_marks.Where(o => o.id_expert == id_expert).FirstOrDefault().experts.patronymic;
}
else
{
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.Add(Type.Missing, xlWorkSheet, Type.Missing, Type.Missing);
xlWorkSheet.Name = "Отчет эксперта " + e.Result.list_marks.Where(o => o.id_expert == id_expert).FirstOrDefault().experts.first_name;
}
var ls_result_expert = e.Result.list_res_ex.Where(o => o.id_expert == id_expert).OrderBy(o => o.id_project).ToList();
var ls_marks = e.Result.list_marks.Where(o => o.id_expert == id_expert).ToList();
var ls_criterion = e.Result.list_marks.Where(o => o.id_expert == id_expert).OrderBy(o => o.id_criterion).GroupBy(o => o.id_criterion).ToList();
for (int j = 0; j < ls_criterion.Count(); j++)
{
int id_criterion = ls_criterion[j].Key;
if (j == 0)
{
xlWorkSheet.Cells[1, 1] = "Критерии";
var projects = ls_marks.OrderBy(o => o.id_project).GroupBy(o => o.id_project).ToList();
for (int k = 0; k < projects.Count(); k++)
{
string name_project = ls_marks.Where(o => o.id_project == projects[k].Key).FirstOrDefault().projects.name;
xlWorkSheet.Cells[1, k + 1 + 1] = name_project;
}
}
string name_criterion = ls_marks.Where(o => o.id_criterion == id_criterion).FirstOrDefault().criterions.name;
var values = ls_marks.Where(o => o.id_criterion == id_criterion).OrderBy(o => o.id_project).ToList();
for (int k = 0; k < values.Count(); k++)
{
xlWorkSheet.Cells[j + 2, 2 + k] = Math.Round(values[k].value * 100);
}
xlWorkSheet.Cells[j + 2, 1] = name_criterion;
}
xlWorkSheet.Cells[count_criterion + 2, 1] = "ИТОГ";
for (int j = 0; j < ls_result_expert.Count(); j++)
{
xlWorkSheet.Cells[count_criterion + 2, j + 2] = Math.Round(ls_result_expert[j].value * 100);
}
xlWorkSheet.Columns.AutoFit();
//строим диаграмму
Excel.ChartObjects _xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject _myChart = (Excel.ChartObject)_xlCharts.Add(10, 80, 300, 250);
Excel.Chart _chartPage = _myChart.Chart;
SeriesCollection _seriesCollection = (SeriesCollection)_chartPage.SeriesCollection(Type.Missing);
Series _series = _seriesCollection.NewSeries();
_series.XValues = xlWorkSheet.get_Range("B1", xlWorkSheet.Cells[1, 1 + count_project] as Range);
_series.Values = xlWorkSheet.get_Range(xlWorkSheet.Cells[2 + count_criterion, 2] as Range, xlWorkSheet.Cells[2 + count_criterion, 1 + count_project] as Range);
_chartPage.ApplyLayout(2, _chartPage.ChartType);
_chartPage.ChartTitle.Text = "Отчет(" + e.Result.list_marks.Where(o => o.id_expert == id_expert).FirstOrDefault().experts.first_name + ")";
//chartPage.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowValue, false, true, false, false, false, false, true, false, false);
_chartPage.ChartType = Excel.XlChartType.xlPie;
}
//строим итоговую таблицу
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.Add(Type.Missing, xlWorkSheet, Type.Missing, Type.Missing);
xlWorkSheet.Name = "Отчет общий";
var ls_expertise_crt = e.Result.list_res_ex_cr.ToList();
for (int i = 0; i < ls_expertise_crt.Count(); i++)
{
xlApp.Columns.AutoFit();
var ls_criterion = ls_expertise_crt.OrderBy(o => o.id_criterion).GroupBy(o => o.id_criterion).ToList();
for (int j = 0; j < ls_criterion.Count(); j++)
{
int id_criterion = ls_criterion[j].Key;
if (j == 0)
{
xlWorkSheet.Cells[1, 1] = "Критерии";
var projects = ls_expertise_crt.OrderBy(o => o.id_project).GroupBy(o => o.id_project).ToList();
for (int k = 0; k < projects.Count(); k++)
{
string name_project = ls_expertise_crt.Where(o => o.id_project == projects[k].Key).FirstOrDefault().projects.name;
xlWorkSheet.Cells[1, k + 1 + 1] = name_project;
}
}
string name_criterion = ls_expertise_crt.Where(o => o.id_criterion == id_criterion).FirstOrDefault().criterions.name;
var values = ls_expertise_crt.Where(o => o.id_criterion == id_criterion).OrderBy(o => o.id_project).ToList();
for (int k = 0; k < values.Count(); k++)
{
xlWorkSheet.Cells[j + 2, 2 + k] = Math.Round(values[k].value * 100);
}
xlWorkSheet.Cells[j + 2, 1] = name_criterion;
}
var ls_result_all = e.Result.list_res_exppertise.ToList();
xlWorkSheet.Cells[count_criterion + 2, 1] = "ИТОГ";
for (int j = 0; j < ls_result_all.Count(); j++)
{
xlWorkSheet.Cells[count_criterion + 2, j + 2] = Math.Round(ls_result_all[j].value * 100);
}
}
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, count_criterion * 25, 300, 250);
Excel.Chart chartPage = myChart.Chart;
SeriesCollection seriesCollection = (SeriesCollection)chartPage.SeriesCollection(Type.Missing);
Series series = seriesCollection.NewSeries();
series.XValues = xlWorkSheet.get_Range("B1", xlWorkSheet.Cells[1, 1 + count_project] as Range);
series.Values = xlWorkSheet.get_Range(xlWorkSheet.Cells[2 + count_criterion, 2] as Range, xlWorkSheet.Cells[2 + count_criterion, 1 + count_project] as Range);
chartPage.ApplyLayout(2, chartPage.ChartType);
chartPage.ChartTitle.Text = "Отчет(Общий)";
//chartPage.ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowValue, false, true, false, false, false, false, true, false, false);
chartPage.ChartType = Excel.XlChartType.xlPie;
SaveFileDialog sFile = new SaveFileDialog();
sFile.Filter = "Excel files (*.xls)|*.xls|All files (*.*)|*.*";
try
{
if (sFile.ShowDialog() == true)
{
string path = sFile.FileName;
xlWorkBook.Application.DisplayAlerts = false;
//MessageBox.Show(path);
xlWorkBook.SaveAs(path, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
//xlApp.Visible = true;
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
MessageBox.Show("Ексель файл создан. Вы можете найти его по пути " + path);
}
}
catch (Exception ex)
{
MessageBox.Show("Error", ex.Message);
}
//xlWorkBook.Close(false, false, false);
//xlApp.Quit();
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);
GC.Collect();
GC.WaitForPendingFinalizers();
}
else
{
MessageBox.Show(e.Error.Message);
}
cLoading.stop();
}
public void Document_Create(Document_Type type, Document_Format format, string name, DataTable table)
{
Configuration_class configuration_Class = new Configuration_class();
configuration_Class.Document_Configuration_Get();
switch (name != "" || name != null)
{
case true:
switch (format)
{
case Document_Format.Word:
word.Application application = new word.Application();
word.Document document = application.Documents.Add(Visible: true);
try
{
word.Range range = document.Range(0, 0);
document.Sections.PageSetup.LeftMargin = application.CentimetersToPoints((float)Configuration_class.doc_Left_Merge);
document.Sections.PageSetup.RightMargin = application.CentimetersToPoints((float)Configuration_class.doc_Right_Merge);
document.Sections.PageSetup.TopMargin = application.CentimetersToPoints((float)Configuration_class.doc_Top_Merge);
document.Sections.PageSetup.BottomMargin = application.CentimetersToPoints((float)Configuration_class.doc_Bottom_Merge);
range.Text = Configuration_class.Organiztion_Name;
range.ParagraphFormat.Alignment = word.WdParagraphAlignment.wdAlignParagraphCenter;
range.ParagraphFormat.SpaceAfter = 1;
range.ParagraphFormat.SpaceBefore = 1;
range.ParagraphFormat.LineSpacingRule = word.WdLineSpacing.wdLineSpaceSingle;
range.Font.Name = "Times New Roman";
range.Font.Size = 12;
document.Paragraphs.Add();
document.Paragraphs.Add();
document.Paragraphs.Add();
word.Paragraph Document_Name = document.Paragraphs.Add();
Document_Name.Format.Alignment = word.WdParagraphAlignment.wdAlignParagraphCenter;
Document_Name.Range.Font.Name = "Times New Roman";
Document_Name.Range.Font.Size = 16;
switch (type)
{
case Document_Type.Report:
Document_Name.Range.Text = "ОТЧЁТ";
break;
case Document_Type Statistic:
Document_Name.Range.Text = "СТАТИСТИЧЕСКИЙ ОТЧЁТ";
break;
}
document.Paragraphs.Add();
document.Paragraphs.Add();
document.Paragraphs.Add();
word.Paragraph statparg = document.Paragraphs.Add();
word.Table stat_table = document.Tables.Add(statparg.Range, table.Rows.Count, table.Columns.Count);
stat_table.Borders.InsideLineStyle = word.WdLineStyle.wdLineStyleSingle;
stat_table.Borders.OutsideLineStyle = word.WdLineStyle.wdLineStyleSingle;
stat_table.Rows.Alignment = word.WdRowAlignment.wdAlignRowCenter;
stat_table.Range.Cells.VerticalAlignment = word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;
stat_table.Range.Font.Size = 12;
stat_table.Range.Font.Name = "Times New Roman";
for (int row = 1; row <= table.Rows.Count; row++)
{
for (int col = 1; col <= table.Columns.Count; col++)
{
stat_table.Cell(row, col).Range.Text = table.Rows[row - 1][col - 1].ToString();
}
}
document.Paragraphs.Add();
document.Paragraphs.Add();
word.Paragraph Footparg = document.Paragraphs.Add();
Footparg.Range.Text = string.Format("Дата создания \t\t\t{0}", DateTime.Now.ToString("dd.MM.yyyy"));
}
catch
{
}
finally
{
switch (format)
{
case Document_Format.Word:
document.SaveAs2(name, word.WdSaveFormat.wdFormatDocument);
break;
case Document_Format.PDF:
document.SaveAs2(name, word.WdSaveFormat.wdFormatPDF);
break;
}
document.Close();
application.Quit();
}
break;
case Document_Format.Excel:
excel.Application application_ex = new excel.Application();
excel.Workbook workbook = application_ex.Workbooks.Add();
excel.Worksheet worksheet = (excel.Worksheet)workbook.ActiveSheet();
try
{
switch (type)
{
case Document_Type.Report:
worksheet.Name = "Отчет";
for (int row = 0; row < table.Rows.Count; row++)
{
for (int col = 0; col < table.Columns.Count; col++)
{
worksheet.Cells[row + 1][col + 1] = table.Rows[row][col].ToString();
}
}
excel.Range border = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[worksheet.Rows.Count + 1][table.Columns.Count + 1]];
border.Borders.LineStyle = excel.XlLineStyle.xlContinuous;
border.VerticalAlignment = excel.XlHAlign.xlHAlignCenter;
border.HorizontalAlignment = excel.XlHAlign.xlHAlignCenter;
worksheet.Cells[table.Rows.Count + 3][2] = string.Format("Дата создания {0}", DateTime.Now.ToString());
worksheet.Range[worksheet.Cells[table.Rows.Count + 3, 2], worksheet.Cells[table.Rows.Count + 2, table.Columns.Count + 2]].Merge();
break;
case Document_Type.Statistic:
worksheet.Name = "Статистический отчет";
for (int row = 0; row < table.Rows.Count; row++)
{
for (int col = 0; col < table.Columns.Count; col++)
{
worksheet.Cells[row + 1][col + 1] = table.Rows[row][col].ToString();
}
}
excel.Range border1 = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[worksheet.Rows.Count + 1][table.Columns.Count + 1]];
border1.Borders.LineStyle = excel.XlLineStyle.xlContinuous;
border1.VerticalAlignment = excel.XlHAlign.xlHAlignCenter;
border1.HorizontalAlignment = excel.XlHAlign.xlHAlignCenter;
worksheet.Cells[table.Rows.Count + 3][2] = string.Format("Дата создания {0}", DateTime.Now.ToString());
excel.ChartObjects chartObjects = (excel.ChartObjects)worksheet.ChartObjects(Type.Missing);
excel.ChartObject chartObject = chartObjects.Add(300, 50, 250, 250);
excel.Chart chart = chartObject.Chart;
excel.SeriesCollection seriesCollection = (excel.SeriesCollection)chart.SeriesCollection(Type.Missing);
excel.Series series = seriesCollection.NewSeries();
chart.ChartType = excel.XlChartType.xl3DColumn;
series.XValues = worksheet.get_Range("B2", "B" + table.Rows.Count + 1);
series.Values = worksheet.get_Range("C2", "C" + table.Rows.Count + 1);
break;
}
}
catch
{
}
finally
{
workbook.SaveAs(name, application_ex.DefaultSaveFormat);
workbook.Close();
application_ex.Quit();
}
break;
}
break;
case false:
System.Windows.Forms.MessageBox.Show("Введите название документа");
break;
}
}
public string FastMoveItems(bool FastOrSlowReport, int NumberOfItems, string DateFrom, string DateTo, bool ExportToExcel = false, string ExportPath = "")
{
try
{
Excel.Range chartRange;
xlApp = new Excel.Application();
xlApp.DisplayAlerts = false;
xlApp.Visible = false;
xlApp.SheetsInNewWorkbook = 1;
xlWorkBook = xlApp.Workbooks.Add(misValue);//misValue
string ReportName = (FastOrSlowReport ? "Fast Move Items Report" : "Slow Move Items Report");
DataTable aTable = ReportsMgmt.FastMovItemBasedOnQty(NumberOfItems, DateFrom, DateTo, FastOrSlowReport);
if (aTable.Rows.Count > 0)
{
int RowCnt = 1;
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Name = ReportName;
List <string> aHeader = ReportsHelper.ImportReportHeader(0, 1);
List <string> aFooter = ReportsHelper.ImportReportHeader(1, 1);
for (int i = 0; i < aHeader.Count; i++)
{
string astringss = aHeader[i];
xlWorkSheet.Cells[RowCnt, 2] = aHeader[i];
RowCnt++;
}
xlWorkSheet.Cells[RowCnt++, 1] = ReportName;
xlWorkSheet.Cells[RowCnt, 2] = "Date From:\t" + DateFrom;
xlWorkSheet.Cells[RowCnt, 3] = "Date To:\t" + DateTo;
RowCnt++;
xlWorkSheet.Cells[RowCnt, 1] = "Item Description";
xlWorkSheet.Cells[RowCnt, 2] = "Qty Sold";
xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).Font.Bold = true;
//xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).WrapText = true;
xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
RowCnt++;
int DataStart = RowCnt;
foreach (DataRow aRow in aTable.Rows)
{
xlWorkSheet.Cells[RowCnt, 1] = aRow["ItemDescription"].ToString();
xlWorkSheet.Cells[RowCnt, 2] = aRow["Summation"].ToString();
RowCnt++;
}
xlWorkSheet.get_Range("A" + DataStart.ToString(), "K" + RowCnt.ToString()).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
xlWorkSheet.get_Range("A" + DataStart.ToString(), "K" + RowCnt.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
oRng = xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString());
oRng.EntireColumn.AutoFit();
Excel.ChartObjects myCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
xlWorkSheet.DisplayRightToLeft = false;
int size = aTable.Rows.Count * 100;
if (size >= 600)
{
size = 600;
}
if (size < 300)
{
size = 300;
}
Excel.ChartObject myChart = (Excel.ChartObject)myCharts.Add(0, RowCnt * 15, size, 300);
Excel.Chart chartPage = myChart.Chart;
Excel.SeriesCollection seriesCollection = chartPage.SeriesCollection();
Excel.Series series1 = seriesCollection.NewSeries();
RowCnt--; //because we started from 1 suppose to be 0
series1.Name = ReportName;
series1.XValues = xlWorkSheet.Range["A" + DataStart.ToString(), "A" + RowCnt.ToString()];
series1.Values = xlWorkSheet.Range["B" + DataStart.ToString(), "B" + RowCnt.ToString()];
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
Excel.Axis axis = chartPage.Axes(Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary) as Excel.Axis;
series1.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowBubbleSizes);
object format = Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml;
xlWorkSheet.SaveAs(ReportsHelper.TempOutputPath, format);
if (ExportToExcel)
{
format = Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7;
xlWorkSheet.SaveAs(ExportPath, format);
}
xlApp.UserControl = false;
return("TRUE");
}
else
{
return("EMPTY");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
return("ERROR");
}
finally
{
//Once done close and quit Excel
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
}
public void CreateChart(string pivotTableName, int chartTop, string cTitle)
{
Excel.Worksheet activeSheet = null;
Excel.Range selectedRange = null;
Excel.Shapes shapes = null;
Excel.Chart chart = null;
Excel.ChartTitle chartTitle = null;
try
{
activeSheet = (Excel.Worksheet)(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveSheet;
Excel.Range rangePivot;
rangePivot = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A1", "AA20000");
selectedRange = IdentifyPivotRangesByName(pivotTableName);
shapes = activeSheet.Shapes;
// Width original 255
if (Globals.ExcelVersion == "15.0" || Globals.ExcelVersion == "16.0")
{
// 204 is a nice style with shadow
shapes.AddChart2(Style: 259, XlChartType: Excel.XlChartType.xlColumnClustered,
Left: 10, Top: chartTop, Width: 450,
Height: 210, NewLayout: true).Select();
}
else
{
shapes.AddChart(XlChartType: Excel.XlChartType.xlColumnClustered,
Left: 10, Top: chartTop, Width: 450,
Height: 210).Select();
}
chart = (ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.ActiveChart;
chart.HasTitle = true;
chart.ChartTitle.Text = cTitle;
chart.ChartTitle.Format.TextFrame2.TextRange.Font.Caps = Microsoft.Office.Core.MsoTextCaps.msoNoCaps;
chart.ChartArea.Interior.Color = System.Drawing.Color.FromArgb(242, 244, 244); // Change chart to light gray
// chart.ChartArea.Interior.Color = System.Drawing.Color.FromRgb(0, 255, 0);
// chart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowValue, true, true, true, true, true, true, true, true, true); // Turn on data labels
chart.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowValue, true, true, true, false, false, true, true, true, true); // Turn on data labels
chart.SetSourceData(selectedRange);
chart.HasLegend = false;
chart.ApplyDataLabels();
if (Globals.ExcelVersion == "15.0" || Globals.ExcelVersion == "16.0")
{
chart.FullSeriesCollection(1).DataLabels.ShowValue = true;
}
else
{
chart.SeriesCollection(1).DataLabels.ShowValue = true;
}
if (pivotTableName == "PivotTableApplicationName")
{
chart.Axes(Excel.XlAxisType.xlCategory).TickLabels.Orientation = Excel.XlOrientation.xlUpward;
}
selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].ColorIndex = 0;
selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].TintAndShade = 0;
selectedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;
selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].ColorIndex = 0;
selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].TintAndShade = 0;
selectedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;
Globals.ChartBottom = (int)chart.ChartArea.Top + (int)chart.ChartArea.Height + 15;
(ADXAddinModule.CurrentInstance as AddinModule).ExcelApp.get_Range("A2", "A2").Select();
// chart.Export(pivotTableName + ".png");
}
catch (Exception ex)
{
// ex.Data.Add("ExcelHelper", "Error occurred in the CreateChart() method");
ex.Data.Add("ExcelHelper", " Error occurred in the CreateChart() method\r\n" + ex.Message);
throw;
}
finally
{
if (chartTitle != null)
{
Marshal.ReleaseComObject(chartTitle);
}
if (chart != null)
{
Marshal.ReleaseComObject(chart);
}
if (shapes != null)
{
Marshal.ReleaseComObject(shapes);
}
if (selectedRange != null)
{
Marshal.ReleaseComObject(selectedRange);
}
}
}
// Generates an excel spreadsheet with graphs of each summary element by depth and saves each graph as a PNG file.
public static void CreateGTGraphs(string domainName, string timeStamp, int depth, List <List <Tuple <String, String> > > summaries)
{
// The top level log directory.
string directory = Parser.GetTopDirectory() + @"GameTrees\" + domainName + @"\" + timeStamp + @"\";
// The graph image directory.
string imageDir = directory + @"graphs\";
// Check if the image directory exists.
if (!File.Exists(imageDir))
{
// If not, create it.
Directory.CreateDirectory(imageDir);
}
// This is needed for a lot of the Excel initialization tasks.
object misValue = System.Reflection.Missing.Value;
// Create an Excel application object.
Excel.Application xl = new Excel.Application();
// Create an Excel work sheet variable.
Excel.Worksheet xlWorkSheet;
// Create an Excel work books variable and point it at the application object's work books..
Excel.Workbooks xlWorkBooks = xl.Workbooks;
// Make the Excel application visible? Not sure, it's magic.
xl.Visible = true;
// Open the summary CSV file in the Excel work book.
xlWorkBooks.OpenText(directory + "summary.csv", misValue, misValue, Excel.XlTextParsingType.xlDelimited,
Excel.XlTextQualifier.xlTextQualifierNone, misValue, misValue,
misValue, misValue, misValue, misValue, misValue, misValue, misValue,
misValue, misValue, misValue, misValue);
// Grab the work sheet that represents the CSV file.
xlWorkSheet = (Excel.Worksheet)xlWorkBooks[1].Worksheets.get_Item(1);
// Loop through every summary element, excluding the depth count which should be first.
for (int summary = 1; summary < summaries[0].Count; summary++)
{
// Create a new Excel chart holder in the work sheet.
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
// Create a new Excel chart and position it below the table and other charts.
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, (15 * summaries[0].Count) + (220 * (summary - 1)), 360, 210);
// Select the chart object's chart. Don't ask me, this Excel interface is strange.
Excel.Chart chartPage = myChart.Chart;
// Make the chart object active.
myChart.Select();
// Set the chart's style. 227 should be a white background with a blue line.
chartPage.ChartStyle = 227;
// Set the type of chart. We are using a line chart.
chartPage.ChartType = Excel.XlChartType.xlLine;
// Turn the legend off.
chartPage.HasLegend = false;
// Create a new series collection to hold the series that will contain the chart data. Again, Excel's interface is dumb.
Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chartPage.SeriesCollection();
// Create a new series.
Excel.Series series1 = seriesCollection.NewSeries();
// Select the chart's X values. These should be the depth counts.
series1.XValues = xlWorkSheet.Range["summary!$A$2:$A$" + depth];
// Select the chart's Y values. These should be the current summary element data.
series1.Values = xlWorkSheet.Range["summary!$" + ToLetter(summary) + "$2:$" + ToLetter(summary) + "$" + depth];
// Name the chart according to the current summary element.
series1.Name = summaries[0][summary].First;
// Export the current chart as a PNG image.
chartPage.Export(imageDir + series1.Name + ".png", "PNG", false);
}
// Save the current work book as an XLS file.
xlWorkBooks[1].SaveAs(directory + "graphsummary-" + timeStamp + ".xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
// Close the current work book.
xlWorkBooks[1].Close(true, misValue, misValue);
// Quit the Excel application.
xl.Quit();
// Do some garbage collection? Not sure, all examples I saw had this.
releaseObject(xlWorkSheet);
releaseObject(xlWorkBooks);
releaseObject(xl);
}
