public ReportBuilder()
{
app = new Excel.Application();
appBooks = app.Workbooks;
currentBook = appBooks.Add(Missing.Value);
sheets = currentBook.Worksheets;
currentSheet = (Excel._Worksheet)sheets.get_Item(1);
range = currentSheet.get_Range("A1", Missing.Value);
charts = currentSheet.ChartObjects(Type.Missing);
chartObject = charts.Add(400, LastIndex, chartWidth, chartHeight);
}
/// <summary>
/// создание Excel отчета за день/неделю/месяц/квартал
/// </summary>
/// <param name="values"></param>
/// <param name="win"></param>
/// <param name="conn"></param>
/// <param name="list_day_data"></param>
/// <param name="list_week_data"></param>
/// <param name="list_month_data"></param>
/// <param name="list_quater_data"></param>
public async void Create_Excel_Doc(List <Flovers_WPF.Reply_Window.Data> values, Reply_Window win, SQLite.SQLiteAsyncConnection conn, List <Flovers_WPF.Reply_Window.Data> list_day_data,
List <Flovers_WPF.Reply_Window.Data> list_week_data, List <Flovers_WPF.Reply_Window.Data> list_month_data, List <Flovers_WPF.Reply_Window.Data> list_quater_data)
{
Excel.Application exapp = new Excel.Application();
exapp.SheetsInNewWorkbook = 1;
exapp.Workbooks.Add(Type.Missing);
exapp.DisplayAlerts = true;
exapp.Visible = true;
Excel.Workbooks exappworkbooks = exapp.Workbooks;
Excel.Workbook exappworkbook = exappworkbooks[1];
exappworkbook.Saved = false;
Excel.Sheets excellsheets = exappworkbook.Worksheets;
Excel.Worksheet excellworksheet = (Excel.Worksheet)excellsheets.get_Item(1);
excellworksheet.Activate();
Excel.Range excelcells;
for (int j = 1; j < 4; j++)
{
if (j == 1)
{
excelcells = (Excel.Range)excellworksheet.Cells[1, j];
excelcells.Value2 = "время/дата";
excelcells.Font.Size = 12;
excelcells.Font.Italic = true;
excelcells.Font.Bold = true;
excelcells.HorizontalAlignment = Excel.Constants.xlCenter;
excelcells.VerticalAlignment = Excel.Constants.xlCenter;
}
if (j == 2)
{
excelcells = (Excel.Range)excellworksheet.Cells[1, j];
excelcells.Value2 = "Количество";
excelcells.Font.Size = 12;
excelcells.Font.Italic = true;
excelcells.Font.Bold = true;
excelcells.HorizontalAlignment = Excel.Constants.xlCenter;
excelcells.VerticalAlignment = Excel.Constants.xlCenter;
}
if (j == 3)
{
excelcells = (Excel.Range)excellworksheet.Cells[1, j];
excelcells.Value2 = "Букет";
excelcells.Font.Size = 12;
excelcells.Font.Italic = true;
excelcells.Font.Bold = true;
excelcells.HorizontalAlignment = Excel.Constants.xlCenter;
excelcells.VerticalAlignment = Excel.Constants.xlCenter;
}
}
if (values != null)
{
for (int m = 2; m < values.Count + 2; m++)
{
for (int n = 1; n < 4; n++)
{
if (n == 1)
{
excelcells = (Excel.Range)excellworksheet.Cells[m, n];
excelcells.Value2 = values[m - 2].time.ToString();
excelcells.Font.Size = 12;
excelcells.Font.Italic = true;
excelcells.Font.Bold = false;
excelcells.HorizontalAlignment = Excel.Constants.xlCenter;
excelcells.VerticalAlignment = Excel.Constants.xlCenter;
}
if (n == 2)
{
excelcells = (Excel.Range)excellworksheet.Cells[m, n];
excelcells.Value2 = values[m - 2].count.ToString();
excelcells.Font.Size = 12;
excelcells.Font.Italic = true;
excelcells.Font.Bold = false;
excelcells.HorizontalAlignment = Excel.Constants.xlCenter;
excelcells.VerticalAlignment = Excel.Constants.xlCenter;
}
if (n == 3)
{
Bouquets b = await conn.GetAsync <Bouquets>(values[m - 2].bouqet_id);
excelcells = (Excel.Range)excellworksheet.Cells[m, n];
excelcells.Value2 = b.name.ToString();
excelcells.Font.Size = 12;
excelcells.Font.Italic = true;
excelcells.Font.Bold = false;
excelcells.HorizontalAlignment = Excel.Constants.xlCenter;
excelcells.VerticalAlignment = Excel.Constants.xlCenter;
}
}
}
Excel.ChartObjects chartsobjrcts = (Excel.ChartObjects)excellworksheet.ChartObjects(Type.Missing);
Excel.ChartObject chartsobj = chartsobjrcts.Add(10, 200, 500, 300);
Excel.Chart excelchart = chartsobj.Chart;
excelcells = excellworksheet.get_Range("A1", "B" + (values.Count + 1).ToString());
excelchart.SetSourceData(excelcells, Type.Missing);
excelchart.ChartType = Excel.XlChartType.xlLine;
excelchart.HasTitle = true;
if (values == list_day_data)
{
excelchart.ChartTitle.Text = "Продажи за день";
}
if (values == list_week_data)
{
excelchart.ChartTitle.Text = "Продажи за неделю";
}
if (values == list_month_data)
{
excelchart.ChartTitle.Text = "Продажи за месяц";
}
if (values == list_quater_data)
{
excelchart.ChartTitle.Text = "Продажи за квартал";
}
excelchart.ChartTitle.Font.Size = 14;
excelchart.ChartTitle.Font.Color = 255;
excelchart.ChartTitle.Shadow = true;
}
else
{
System.Windows.MessageBox.Show("нет данных для отчета");
exapp.Quit();
}
}
public static void AnalyseVisits(string fileName)
{
using (ModelMedDBContainer db = new ModelMedDBContainer())
{
object misValue = System.Reflection.Missing.Value;
// Создаём экземпляр нашего приложения
Excel.Application excelApp = new Excel.Application();
// Создаём экземпляр рабочий книги Excel
Excel.Workbook workBook;
// Создаём экземпляр листа Excel
Excel.Worksheet workSheet;
workBook = excelApp.Workbooks.Add(misValue);
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(1);
var specials = (from docs in db.PersonSet where (docs is Doctor)select(docs as Doctor).Job).Distinct().ToArray();
//var workTime = (from works in db.WorkTimeSet group works by works.Start.Date);
var workT = (from works in db.WorkTimeSet select new { works.Start, works.Doctor.Job }).ToList();
//List<DateTime> workDays = new List<DateTime>();
//foreach (DateTime t in workT)
// workDays.Add(t.Date);
DateTime start = (from works in db.WorkTimeSet select works.Start).Min();
DateTime finish = (from works in db.WorkTimeSet select works.Start).Max();
int[,] days = new int[specials.Length, (finish.Date - start.Date).Days + 1];
//Подсчёт прёмов по профессиям и по дням
for (int i = 0; i < specials.Length; i++)
{
foreach (var t in workT)
{
if (specials[i] == t.Job)
{
days[i, (t.Start.Date - start.Date).Days]++;
}
}
}
//Заполнение строчек и столбцов посчитанными значениями
for (int i = 1; i <= specials.Length; i++)
{
workSheet.Cells[i, 1] = specials[i - 1];
for (int j = 2; j <= days.GetLength(1) + 1; j++)
{
workSheet.Cells[i, j] = days[i - 1, j - 2];
}
}
workSheet.Cells[specials.Length + 1, 1] = start;
for (int i = 2; i <= days.GetLength(1) + 1; i++)
{
workSheet.Cells[specials.Length + 1, i] = start;
start = start.AddDays(1);
}
/*
* //Вычисляем сумму этих чисел
* Excel.Range rng = workSheet.Range["A2"];
* rng.Formula = "=SUM(A1:L1)";
* rng.FormulaHidden = false;
*
* // Выделяем границы у этой ячейки
* Excel.Borders border = rng.Borders;
* border.LineStyle = Excel.XlLineStyle.xlContinuous;
*/
Excel.ChartObjects xlCharts = (Excel.ChartObjects)workSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
// Excel.ChartObject chartObj = myChart.Add(5, 50, 300, 300);
Excel.Chart chartPage = myChart.Chart;
//chartPage.ChartType = Excel.XlChartType.xlXYScatterLines;
//chartPage.ChartType = Excel.XlChartType.xlBarClustered;
//chartPage.ChartType = Excel.XlChartType.xlLine;
Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application();
Excel.SeriesCollection seriesCollection = chartPage.SeriesCollection();
//char a = char.ConvertFromUtf32((char.ConvertToUtf32('A',0) + days.GetLength(1)));
Excel.Range rngX = workSheet.Range[workSheet.Cells[specials.Length + 1, 2], workSheet.Cells[specials.Length + 1, days.GetLength(1) + 1]];
// workSheet.Cells[specials.Length+1, 1], workSheet.Cells[specials.Length+1, days.GetLength(1)]];
for (int i = 1; i <= specials.Length; i++)
{
Excel.Series series = seriesCollection.NewSeries();
Excel.Range rng = workSheet.Range[workSheet.Cells[i, 2], workSheet.Cells[i, days.GetLength(1) + 1]];
series.XValues = rngX;
series.Values = rng;
series.Name = specials[i - 1];
}
workSheet.Columns.AutoFit();
workBook.SaveAs(fileName, Excel.XlFileFormat.xlExcel12, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
workBook.Close(true, misValue, misValue);
excelApp.Quit();
/*
* xlChart.Activate();
* xlChart.Select(Type.Missing);
*
*
* //Даем названия осей
* ((Excel.Axis)excelApp.ActiveChart.Axes(Excel.XlAxisType.xlCategory,
* Excel.XlAxisGroup.xlPrimary)).HasTitle = true;
* ((Excel.Axis)excelApp.ActiveChart.Axes(Excel.XlAxisType.xlCategory,
* Excel.XlAxisGroup.xlPrimary)).AxisTitle.Text = "Дата";
* ((Excel.Axis)excelApp.ActiveChart.Axes(Excel.XlAxisType.xlSeriesAxis,
* Excel.XlAxisGroup.xlPrimary)).HasTitle = false;
* ((Excel.Axis)excelApp.ActiveChart.Axes(Excel.XlAxisType.xlValue,
* Excel.XlAxisGroup.xlPrimary)).HasTitle = true;
* ((Excel.Axis)excelApp.ActiveChart.Axes(Excel.XlAxisType.xlValue,
* Excel.XlAxisGroup.xlPrimary)).AxisTitle.Text = "Приёмов";
*
* excelApp.ActiveChart.HasTitle = true;
* excelApp.ActiveChart.ChartTitle.Text = "Количество приёмов на каждую специальность по дням";
*
* //Будем отображать легенду
* excelApp.ActiveChart.HasLegend = true;
* //Расположение легенды
* excelApp.ActiveChart.Legend.Position
* = Excel.XlLegendPosition.xlLegendPositionLeft;
*/
// Открываем созданный excel-файл
//excelApp.Visible = true;
//excelApp.UserControl = true;
}
}
private void diagram_Click(object sender, EventArgs e)
{
KURS.allDataSet kursds = new KURS.allDataSet();
KURS.allDataSetTableAdapters.ZakazTableAdapter zta = new KURS.allDataSetTableAdapters.ZakazTableAdapter();
zta.Fill(kursds.Zakaz);
//
object misValue = System.Reflection.Missing.Value;
//
Excel.Application excelapp = new Excel.Application();
excelapp.Visible = true;
excelapp.SheetsInNewWorkbook = 1;
excelapp.Workbooks.Add(misValue);
Excel.Workbooks excelappworkbooks = excelapp.Workbooks;
Excel.Workbook excelappworkbook = excelappworkbooks[1];
Excel.Sheets excelsheets = excelappworkbook.Worksheets;
Excel.Worksheet excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1);
//excelworksheet.Activate();
//
var c = from p in kursds.Zakaz.AsEnumerable() where p.Data.Month == dateTimePicker1.Value.Month select p;
int count = 0;
excelapp.Cells[1][1] = "date";
excelapp.Cells[2][1] = "summ";
foreach (var p in c)
{
count++;
}
//
int[] a = new int[count]; double[] b = new double[count]; int i = 0; double sum = 0;
while (i < count)
{
foreach (var p in c)
{
a[i] = p.Data.Day;
if (i == 0)
{
sum += p.Summ;
}
if (i > 0)
{
if (a[i] == a[i - 1])
{
sum += p.Summ;
}
else
{
b[i - 1] = sum; sum = p.Summ; i++;
}
}
else
{
i++;
}
}
b[i - 1] = sum;
count = i; i = 0;
break;
}
Dictionary <int, double> dic = new Dictionary <int, double>();
while (i < count)
{
excelapp.Cells[1][i + 2] = a[i];
excelapp.Cells[2][i + 2] = b[i];
dic.Add(a[i], b[i]);
i++;
}
//
string str = (count + 1).ToString();
//
//Excel.Window excelWindow = null;
Excel.Range excelcells = null;
//Определяем диаграммы как объекты Excel.ChartObjects
Excel.ChartObjects chartsobjrcts = (Excel.ChartObjects)excelworksheet.ChartObjects(Type.Missing);
//Добавляем одну диаграмму в Excel.ChartObjects - диаграмма пока не выбрана, но место для нее выделено в методе Add
Excel.ChartObject chartsobjrct = chartsobjrcts.Add(100, 40, 300, 300);
Excel.Chart chartPage = chartsobjrct.Chart;
excelcells = excelworksheet.get_Range("B1", "B" + str);
chartPage.SetSourceData(excelcells, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
Excel.Axis axis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
axis.HasTitle = true;
axis.AxisTitle.Text = "Data";
//
Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chartPage.SeriesCollection(Type.Missing);
Excel.Series series = seriesCollection.Item(1);
series.XValues = a;
//
excelappworkbook.SaveAs(@"D:\siple.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
excelappworkbook.Close(true, misValue, misValue);
excelapp.Quit();
this.chart1.Titles.Add("Отчет за месяц");
this.chart1.Series[0].Points.Clear();
Series ser = this.chart1.Series[0];
ser.LegendText = "Summ";
foreach (KeyValuePair <int, double> pair in dic)
{
ser.Points.AddXY(pair.Key, pair.Value);
}
//
}
private void btnExcelAktar_Click(object sender, EventArgs e)
{
//MessageBox.Show(grupSayisi.ToString());
if (dataGridView1.Rows.Count >= 2)
{
Excel.Application excelDosya = new Excel.Application();
object Missing = Type.Missing;
Workbook calismakitabi = excelDosya.Workbooks.Add(Missing);
Worksheet sheet1 = (Worksheet)calismakitabi.Sheets[1];
sheet1.Name = "Bireysel Notlar";
Worksheet sheet2 = (Worksheet)calismakitabi.Sheets.Add(Missing, Missing, 1, Missing) as Excel.Worksheet;;
sheet2.Name = "Soru Analizi";
Worksheet sheet3 = (Worksheet)calismakitabi.Sheets.Add(Missing, Missing, 1, Missing) as Excel.Worksheet;;
sheet3.Name = "Not Aralıkları";
Excel.Range formatRange;
formatRange = sheet1.get_Range("h:h");
formatRange.NumberFormat = "@";
Excel.Range formatRange2;
formatRange2 = sheet3.get_Range("a:a");
formatRange2.NumberFormat = "@";
//sheet1.Cells[8, 2].NumberFormat = "General";
int sutun = 1;
int satir = 1;
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
Range myrange = (Range)sheet1.Cells[satir, sutun + j];
myrange.Value2 = dataGridView1.Columns[j].HeaderText;
}
satir++;
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
Range myrange = (Range)sheet1.Cells[satir + i, sutun + j];
myrange.Value2 = dataGridView1[j, i].ValueType == null ? "" : dataGridView1[j, i].Value;
//myrange.Select();
}
}
//soru analiz
if (grupSayisi == 1)
{
int analizSatir = 1, analizSutun = 1;
for (int i = 0; i < dtSoruAnalizA.Rows.Count; i++)
{
for (int j = 0; j < dtSoruAnalizA.Columns.Count; j++)
{
Range myrange2 = (Range)sheet2.Cells[analizSatir + i, analizSutun + j];
myrange2.Value2 = dtSoruAnalizA.Rows[i][j] == null ? "" : dtSoruAnalizA.Rows[i][j];
//myrange2.Select();
}
}
}
if (grupSayisi == 2)
{
//MessageBox.Show("Else çalışıyor");
int analizSatirA = 1, analizSutunA = 1, analizSatirB = 1, analizSutunB = 4;
for (int i = 0; i < dtSoruAnalizA.Rows.Count; i++)
{
for (int j = 0; j < dtSoruAnalizA.Columns.Count; j++)
{
Range myrange2 = (Range)sheet2.Cells[analizSatirA + i, analizSutunA + j];
myrange2.Value2 = dtSoruAnalizA.Rows[i][j] == null ? "" : dtSoruAnalizA.Rows[i][j];
//myrange2.Select();
}
}
for (int i = 0; i < dtSoruAnalizB.Rows.Count; i++)
{
for (int j = 0; j < dtSoruAnalizB.Columns.Count; j++)
{
Range myrange2 = (Range)sheet2.Cells[analizSatirB + i, analizSutunB + j];
myrange2.Value2 = dtSoruAnalizB.Rows[i][j] == null ? "" : dtSoruAnalizB.Rows[i][j];
//myrange2.Select();
}
}
}
//Not aralıkları
int aralikSatir = 1, araliksutun = 1;
for (int i = 0; i < dtAralik.Rows.Count; i++)
{
for (int j = 0; j < dtAralik.Columns.Count; j++)
{
Range myrange3 = (Range)sheet3.Cells[aralikSatir + i, araliksutun + j];
myrange3.Value2 = dtAralik.Rows[i][j] == null ? "" : dtAralik.Rows[i][j];
//myrange2.Select();
}
}
//Grafikler
if (grupSayisi == 1)
{
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)sheet2.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(50, 20, 800, 250);
Excel.Chart chartPage = myChart.Chart;
chartRange = sheet2.get_Range("B:A");
chartPage.SetSourceData(chartRange, Missing);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
excelDosya.Visible = true;
}
else
{
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)sheet2.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(50, 20, 800, 250);
Excel.Chart chartPage = myChart.Chart;
chartRange = sheet2.get_Range("B:A");
chartPage.SetSourceData(chartRange, Missing);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
Excel.Range chartRange2;
Excel.ChartObjects xlCharts2 = (Excel.ChartObjects)sheet2.ChartObjects(Type.Missing);
Excel.ChartObject myChart2 = (Excel.ChartObject)xlCharts2.Add(50, 240, 800, 250);
Excel.Chart chartPage2 = myChart2.Chart;
chartRange2 = sheet2.get_Range("D:E");
chartPage2.SetSourceData(chartRange2, Missing);
chartPage2.ChartType = Excel.XlChartType.xlColumnClustered;
excelDosya.Visible = true;
}
//Not Aralıkları
Excel.Range chartRange3;
Excel.ChartObjects xlCharts3 = (Excel.ChartObjects)sheet3.ChartObjects(Type.Missing);
Excel.ChartObject myChart3 = (Excel.ChartObject)xlCharts3.Add(50, 80, 800, 250);
Excel.Chart chartPage3 = myChart3.Chart;
chartRange3 = sheet3.get_Range("A:B");
chartPage3.SetSourceData(chartRange3, Missing);
chartPage3.ChartType = Excel.XlChartType.xlColumnClustered;
excelDosya.Visible = true;
}
else
{
MessageBox.Show("Aktarmaya uygun veri bulunamadı.");
}
}
//// 關閉Excel Message
// Microsoft.Office.Interop.Excel.Application.DisplayAlerts = false;
// Microsoft.Office.Interop.Excel.Application.Visible = false; // 設為true,則一開始就會顯現Excel檔.
// //ExlBook = ExlApp.Workbooks.Add(Server.MapPath(".") + "\\Tool_Prod_sample.xls");
// ExlBook = ExlApp.Workbooks.Add("c:\TAIWAN_BANK_OutSite_Salary_FA_20160111.xls");
protected void Page_Load(object sender, EventArgs e)
{
// xl.Application ExlApp ;
// ExlApp = new xl.ApplicationClass();
// object missValue = System.Reflection.Missing.Value;
// string path=@"c:\\TAIWAN_BANK_OutSite_Salary_FA_20160111.xls";
// Workbook w = ExlApp.Workbooks.Open(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//// (path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
// Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)w.Sheets["Sheet1"];
// //ws.Protect(Contents: false);
// Range r = ws.get_Range("B2","H20");
// r.CopyPicture(XlPictureAppearance.xlScreen, XlCopyPictureFormat.xlBitmap);
// Bitmap image = new Bitmap(Clipboard.GetImage());
// image.Save(@"C:\abc\image.png");
xl.Application xlApp;
xl.Workbook xlWorkBook;
xl.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new xl.Application();
string path = @"c:\\abcd.xls";
xlWorkBook = xlApp.Workbooks.Open(path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (xl.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xl.Range xlRange;
xlRange = xlWorkSheet.get_Range("A1", "d5");
xlRange.CopyPicture(Microsoft.Office.Interop.Excel.XlPictureAppearance.xlScreen, Microsoft.Office.Interop.Excel.XlCopyPictureFormat.xlPicture);
xl.ChartObjects xlCharts = (xl.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
xl.ChartObject myChart = (xl.ChartObject)xlCharts.Add(10, 80, 300, 250);
xl.Chart chartPage = myChart.Chart;
xl.Range chartRange;
chartRange = xlWorkSheet.get_Range("A1", "d5");
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = xl.XlChartType.xlColumnClustered;
//export chart as picture file
//chartPage.Paste();
chartPage.Export(@"C:\excel_chart_export.JPG", "JPG", misValue);
xlCharts.Delete();
//xlWorkBook.SaveAs(@"C:\excel_chart_export.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
static OutputUnits makePerfMonGraphs(string file)
{
Excel.Application excelApp = null;
Excel.Workbook workbook = null;
Excel.Sheets sheets = null;
Excel.Worksheet dataSheet = null;
Excel.Worksheet newSheet = null;
Excel.ChartObjects xlChart = null;
Excel.Range dataY = null;
Excel.Chart memChart = null;
Excel.Chart diskChart = null;
Excel.Chart cpuChart = null;
Excel.Chart netChart = null;
Excel.Axis xAxis = null;
OutputUnits csData = null;
bool leaking = false;
bool highcpu = false;
string exitFile = "";
try {
excelApp = new Excel.Application();
string dir = file.Substring(0, file.LastIndexOf("\\") + 1);
string fm = file.Substring(0, file.Length - 4).Substring(file.LastIndexOf("\\") + 1);
workbook = excelApp.Workbooks.Open(file, 0, false, 6, Type.Missing, Type.Missing, Type.Missing, XlPlatform.xlWindows, ",",
true, false, 0, false, false, false);
sheets = workbook.Sheets;
dataSheet = sheets[1];
dataSheet.Name = "data";
dataSheet.get_Range("A2:A2", Type.Missing).EntireRow.Delete(XlDeleteShiftDirection.xlShiftUp);//garbage row
newSheet = (Worksheet)sheets.Add(Type.Missing, dataSheet, Type.Missing, Type.Missing);
newSheet.Name = "results";
xlChart = (Excel.ChartObjects)newSheet.ChartObjects(Type.Missing);
memChart = xlChart.Add(20, 100, 450, 175).Chart;
diskChart = xlChart.Add(20, 280, 450, 175).Chart;
cpuChart = xlChart.Add(500, 100, 450, 175).Chart;
netChart = xlChart.Add(500, 280, 450, 175).Chart;
int rowTotal = dataSheet.UsedRange.Rows.Count;
int colTotal = dataSheet.UsedRange.Columns.Count;
dataSheet.get_Range("A2", "A" + rowTotal).NumberFormat = "m/d/yyyy h:mm";
string ttime = dataSheet.Cells[2, 1].Value.ToString();
Array availMem = (System.Array)dataSheet.get_Range("C2", "C" + rowTotal).Value;
Array cpuTotal = (System.Array)dataSheet.get_Range("D2", "D" + rowTotal).Value;
Array diskTotal = (System.Array)dataSheet.get_Range("B2", "B" + rowTotal).Value;
dataSheet.Cells[1, colTotal + 1] = "Total LAN (Bytes Total/Sec)";
double[] netties = new double[rowTotal - 1];
for (int i = 2; i <= rowTotal; i++)
{
if (colTotal > 5)
{
Array netLine = (System.Array)dataSheet.get_Range(xlStr(5) + i, xlStr(colTotal) + i).Value;
double netLineTotal = 0;
for (int j = 1; j <= netLine.Length; j++)
{
netLineTotal += Convert.ToDouble(netLine.GetValue(1, j));
}
netties[i - 2] = netLineTotal;
dataSheet.Cells[i, colTotal + 1] = netLineTotal;
}
else
{
dataSheet.Cells[i, colTotal + 1] = "0";
}
}
#region BuildCounters
double[] mems = ColToDouble(availMem);
double[] cpus = ColToDouble(cpuTotal);
double[] disks = ColToDouble(diskTotal);
//netties[]
double avgCPUs = cpus.Average();
PCounter CPU = new PCounter(avgCPUs, cpus.Max(), cpus.Min());
PCounter MEM = new PCounter(mems.Average(), mems.Max(), mems.Min());
PCounter DISK = new PCounter(disks.Average(), disks.Max(), disks.Min());
PCounter NETS = new PCounter(netties.Average(), netties.Max(), netties.Min());
if (avgCPUs > 40)
{
highcpu = true;
}
#endregion
#region leakCheck
double[] eqMB = new double[2];
List <double> memList = new List <double>();
int cX = availMem.Length;
for (int i = 1; i < rowTotal - 1; i++)
{
memList.Add(Convert.ToDouble(availMem.GetValue(i, 1)));
}
eqMB = LeastSquares(memList);
double stdD1 = StandardDev(memList);
List <double> memList2 = sigma(memList, stdD1, eqMB);
cX = memList2.Count();
eqMB = LeastSquares(memList2);
double stdD2 = StandardDev(memList2) * 1.2;
List <double> memList3 = sigma(memList2, stdD2, eqMB);
eqMB = LeastSquares(memList3);
if (eqMB[0] < 0)
{
leaking = true;
newSheet.get_Range("E4", Type.Missing).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Tomato);
}
#endregion
#region formatting
string lan = xlStr(colTotal + 1);
newSheet.get_Range("A1", Type.Missing).EntireColumn.ColumnWidth = 12;
newSheet.get_Range("A1", Type.Missing).EntireColumn.HorizontalAlignment = XlHAlign.xlHAlignRight;
newSheet.get_Range("A2", Type.Missing).EntireRow.HorizontalAlignment = XlHAlign.xlHAlignCenter;
newSheet.Cells[4, 5] = eqMB[0];
newSheet.Cells[2, 2] = "Avg";
newSheet.Cells[2, 3] = "Min";
newSheet.Cells[2, 4] = "Max";
newSheet.Cells[2, 5] = "Slope(3Sigma)";
newSheet.Cells[3, 1] = "CPU";
newSheet.Cells[3, 2] = "=AVERAGE(data!D:D)";
newSheet.Cells[3, 3] = "=MIN(data!D:D)";
newSheet.Cells[3, 4] = "=MAX(data!D:D)";
newSheet.Cells[4, 1] = "Avail.RAM";
newSheet.Cells[4, 2] = "=AVERAGE(data!C:C)";
newSheet.Cells[4, 3] = "=MIN(data!C:C)";
newSheet.Cells[4, 4] = "=MAX(data!C:C)";
newSheet.Cells[5, 1] = "LAN Usage";
newSheet.Cells[5, 2] = "=AVERAGE(data!" + lan + ":" + lan + ")";
newSheet.Cells[5, 3] = "=MIN(data!" + lan + ":" + lan + ")";
newSheet.Cells[5, 4] = "=MAX(data!" + lan + ":" + lan + ")";
newSheet.Cells[6, 1] = "Disk Usage";
newSheet.Cells[6, 2] = "=AVERAGE(data!B:B)";
newSheet.Cells[6, 3] = "=MIN(data!B:B)";
newSheet.Cells[6, 4] = "=MAX(data!B:B)";
#endregion
#region memChart
dataY = dataSheet.Range["C1", "C" + rowTotal];
memChart.SetSourceData(dataY, Type.Missing);
memChart.ChartType = XlChartType.xlXYScatterLinesNoMarkers;
memChart.HasLegend = false;
xAxis = (Axis)memChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
xAxis.MaximumScaleIsAuto = false;
xAxis.MaximumScale = rowTotal + 1;
xAxis.MinimumScaleIsAuto = false;
xAxis.MinimumScale = 0;
#endregion
#region diskChart
dataY = dataSheet.Range["B1", "B" + rowTotal];
diskChart.SetSourceData(dataY, Type.Missing);
diskChart.ChartType = XlChartType.xlXYScatterLinesNoMarkers;
diskChart.HasLegend = false;
xAxis = (Axis)diskChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
xAxis.MaximumScaleIsAuto = false;
xAxis.MaximumScale = rowTotal + 1;
xAxis.MinimumScaleIsAuto = false;
xAxis.MinimumScale = 0;
#endregion
#region cpuChart
dataY = dataSheet.Range["D1", "D" + rowTotal];
cpuChart.SetSourceData(dataY, Type.Missing);
cpuChart.ChartType = XlChartType.xlXYScatterLinesNoMarkers;
cpuChart.HasLegend = false;
xAxis = (Axis)cpuChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
xAxis.MaximumScaleIsAuto = false;
xAxis.MaximumScale = rowTotal + 1;
xAxis.MinimumScaleIsAuto = false;
xAxis.MinimumScale = 0;
#endregion
#region netChart
dataY = dataSheet.Range[xlStr(colTotal + 1) + "1", xlStr(colTotal + 1) + rowTotal];
netChart.SetSourceData(dataY, Type.Missing);
netChart.ChartType = XlChartType.xlXYScatterLinesNoMarkers;
netChart.HasLegend = false;
xAxis = (Axis)netChart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
xAxis.MaximumScaleIsAuto = false;
xAxis.MaximumScale = rowTotal + 1;
xAxis.MinimumScaleIsAuto = false;
xAxis.MinimumScale = 0;
#endregion
string host = Path.GetFileNameWithoutExtension(dir + fm);
csData = new OutputUnits(host, ttime + " time chunks: " + (rowTotal - 1), CPU, MEM, NETS, DISK, leaking, highcpu);
exitFile = dir + fm;
excelApp.DisplayAlerts = false;
workbook.SaveAs(@exitFile, XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, false, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing);
workbook.Close(true, Type.Missing, Type.Missing);
excelApp.Quit();
//releaseObject(sC);
//releaseObject(myChart);
} catch {
Console.WriteLine("Had issues interacting with your Excel installation...maybe try a restart?");
//using (StreamWriter outfile = File.AppendText("output.txt")) {
// outfile.WriteLine("Did have issues interacting with Excel on " + file);
//}
} finally {
releaseObject(xAxis);
releaseObject(dataY);
releaseObject(diskChart);
releaseObject(memChart);
releaseObject(cpuChart);
releaseObject(netChart);
releaseObject(xlChart);
releaseObject(newSheet);
releaseObject(dataSheet);
releaseObject(sheets);
releaseObject(workbook);
releaseObject(excelApp);
}
return(csData);
}
public void отчетToolStripMenuItem_Click(object sender, EventArgs e)
{
SelectDateRange srd = new SelectDateRange();
srd.dateTimePicker1.Value = (DateTime)db.Invoices.First().sale_date;
DialogResult dr = srd.ShowDialog(this);
DateTime startDate = srd.dateTimePicker1.Value;
DateTime endDate = srd.dateTimePicker2.Value;
List <Invoice> invoices = db.Invoices.Where(i => i.sale_date.Value >= startDate && i.sale_date.Value <= endDate).OrderByDescending(i => i.Product.name).ToList();
//string[] head = { "Код", "Название товара", "Цена за единицу", "Заказано КГ.", "Дата продажи", "Общая стоимость" };
if ((dr == DialogResult.OK) && (0 < invoices.Count))
{
// Load Excel application
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
// Create empty workbook
excel.Workbooks.Add();
// Create Worksheet from active sheet
Microsoft.Office.Interop.Excel._Worksheet workSheet = excel.ActiveSheet;
try
{
workSheet.Cells[1, "A"] = "Код накладной";
workSheet.Cells[1, "B"] = "Название товара";
workSheet.Cells[1, "C"] = "Цена за единицу";
workSheet.Cells[1, "D"] = "Заказано (КГ.)";
workSheet.Cells[1, "E"] = "Дата покупки";
workSheet.Cells[1, "F"] = "Сумма за покупку";
workSheet.Cells[1, "H"] = "Название товара";
workSheet.Cells[1, "I"] = "Количество заказанного товара";
//workSheet.Cells[1, "K"] = "Нужно закупить больше:";
//workSheet.Cells[1, "L"] = "Нужно закупить меньше:";
int row = 2;
ArrayList nameArray = new ArrayList();
ArrayList sumArray = new ArrayList();
int sum;
string name;
foreach (Invoice invoice in invoices)
{
workSheet.Cells[row, "A"] = invoice.invoice_code;
workSheet.Cells[row, "B"] = invoice.Product.name;
workSheet.Cells[row, "C"] = string.Format("{0} грн.", invoice.Product.price);
workSheet.Cells[row, "D"] = string.Format("{0} кг.", invoice.quantity);
workSheet.Cells[row, "E"] = invoice.sale_date.Value.Date;
workSheet.Cells[row, "F"] = string.Format("{0} грн.", invoice.price);
name = db.Invoices.Where(nn => nn.product_id == invoice.Product.id && nn.sale_date.Value >= startDate && nn.sale_date.Value <= endDate).First().Product.name.ToString();
sum = (int)db.Invoices.Where(i => i.Product.name == invoice.Product.name && i.sale_date.Value >= startDate && i.sale_date.Value <= endDate).Sum(s => s.quantity);
if (!nameArray.Contains(name))
{
nameArray.Add(name);
}
if (!sumArray.Contains(sum))
{
sumArray.Add(sum);
}
row++;
}
row = 2;
foreach (string nameRow in nameArray)
{
workSheet.Cells[row, "H"] = nameRow;
row++;
}
row = 2;
foreach (int sumRow in sumArray)
{
workSheet.Cells[row, "I"] = sumRow;
row++;
}
Excel.ChartObjects chartObjs = (Excel.ChartObjects)workSheet.ChartObjects(Type.Missing);
Excel.ChartObject chartObj = chartObjs.Add(100, 20, 150, 200);
Excel.Chart xlChart = chartObj.Chart;
Excel.Range rg = workSheet.get_Range("H2:H" + (row - 1).ToString(), "I2:I" + (row - 1).ToString());
xlChart.ChartType = Excel.XlChartType.xlPieExploded;
xlChart.SetSourceData(rg, Type.Missing);
//rg.FormulaLocal = "MAX(I2:I"+(row-1).ToString();
//workSheet.Cells[2, "M"].Value = "=MAX(I2:I" + (row - 1).ToString();
//workSheet.Cells[2, "N"].Value = "=MIN(I2:I" + (row - 1).ToString();
//for (int i = 2; i < row; i++)
// {
// workSheet.Cells[i, "K"].Value = "=ЕСЛИ(M2=I" + i.ToString() + ";H" + i.ToString() + ";0)";
// }
//for (int i = 2; i < row; i++)
// {
// workSheet.Cells[i, "L"].Value = "=ЕСЛИ(N2=I" + i.ToString() + ";H" + i.ToString() + ";0)";
// }
// rg.FormulaLocal = "MAX(I2:I"+(row-1).ToString();
//rg.FormulaLocal = "MIN(I2:I"+(row-1).ToString();
// Apply some predefined styles for data to look nicely :)
workSheet.Range["A1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1);
workSheet.Range["H1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1);
//workSheet.Range["K1"].AutoFormat(Microsoft.Office.Interop.Excel.XlRangeAutoFormat.xlRangeAutoFormatClassic1);
// Define filename
string fileName = string.Format(@"{0}\ExcelData.xlsx", Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory));
// Save this data as a file
workSheet.SaveAs(fileName);
// Display SUCCESS message
MessageBox.Show(string.Format("Файл '{0}' успешно сохранен!", fileName));
}
catch (Exception exception)
{
MessageBox.Show("Exception",
"Ошибка записи файла!\n" + exception.Message,
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
// Quit Excel application
excel.Quit();
//excel.Workbooks.Open(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory));
// Release COM objects (very important!)
if (excel != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
}
if (workSheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workSheet);
}
// Empty variables
excel = null;
workSheet = null;
// Force garbage collector cleaning
GC.Collect();
}
}
else
{
MessageBox.Show("В выбраном диапазоне, нет накладных!");
}
if (dr == DialogResult.Cancel)
{
return;
}
}
// データテーブルをエクセルへエクスポート（箱ヒゲ図付き）
public void ExportToExcelWithBoxPlotChart(DataTable dt)
{
if (dt.Rows.Count == 0)
{
return;
}
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
// column headings
for (int i = 0; i < dt.Columns.Count; i++)
{
xlWorkSheet.Cells[1, (i + 1)] = dt.Columns[i].ColumnName;
}
// rows
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
xlWorkSheet.Cells[(i + 2), (j + 1)] = dt.Rows[i][j];
}
}
int row = xlWorkSheet.UsedRange.Rows.Count;
string address1 = "C1:C" + row.ToString() + ",H1:L" + row.ToString();
string address2 = "M2:M" + row.ToString();
string address3 = "N2:N" + row.ToString();
Excel.Range chartRange1;
Excel.Range chartRange2;
Excel.Range chartRange3;
Excel.ChartObjects xlCharts1 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart1 = (Excel.ChartObject)xlCharts1.Add(800, 10, 600, 250);
Excel.Chart chartPage1 = myChart1.Chart;
chartRange1 = xlWorkSheet.get_Range(address1);
chartRange2 = xlWorkSheet.get_Range(address2);
chartRange3 = xlWorkSheet.get_Range(address3);
chartPage1.SetSourceData(chartRange1, misValue);
chartPage1.ChartType = Excel.XlChartType.xlColumnStacked;
chartPage1.HasTitle = true;
chartPage1.ChartTitle.Text = dt.Rows[0]["inspect"].ToString() + " " + dt.Rows[0]["line"].ToString();
chartPage1.HasLegend = false;
Excel.Axis xAxis1 = (Excel.Axis)chartPage1.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
xAxis1.CategoryType = Excel.XlCategoryType.xlCategoryScale;
Excel.SeriesCollection oSeriesCollection = (Excel.SeriesCollection)myChart1.Chart.SeriesCollection(misValue);
Excel.Series s5 = oSeriesCollection.Item(5);
s5.ChartType = Excel.XlChartType.xlLine;
s5.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbDarkOrchid;
Excel.Series s4 = oSeriesCollection.Item(4);
s4.ChartType = Excel.XlChartType.xlLine;
s4.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbDarkOrchid;
Excel.Series s1 = oSeriesCollection.Item(1);
s1.Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbWhite;
s1.Format.Fill.Transparency = 1;
s1.Format.Line.Weight = 0;
s1.HasErrorBars = true;
s1.ErrorBar(Excel.XlErrorBarDirection.xlY, Excel.XlErrorBarInclude.xlErrorBarIncludeMinusValues,
Excel.XlErrorBarType.xlErrorBarTypeCustom, chartRange2, chartRange2);
Excel.Series s3 = oSeriesCollection.Item(3);
s3.Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbAqua;
s3.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbBlack;
s3.Format.Line.Weight = 1.0F;
s3.HasErrorBars = true;
s3.ErrorBar(Excel.XlErrorBarDirection.xlY, Excel.XlErrorBarInclude.xlErrorBarIncludePlusValues,
Excel.XlErrorBarType.xlErrorBarTypeCustom, chartRange3, chartRange3);
Excel.Series s2 = oSeriesCollection.Item(2);
s2.Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbAqua;
s2.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbBlack;
s2.Format.Line.Weight = 1.0F;
xlApp.Visible = true;
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlApp.DisplayAlerts = false;
//xlApp.Visible = true;
xlWorkBook = xlApp.Workbooks.Open(fileUrl);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
//Left, Top, Width, Height
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(0, 0, 1000, 600);
Excel.Chart chartPage = myChart.Chart;
int lastRow = xlWorkSheet.UsedRange.Rows.Count;
chartRange = xlWorkSheet.get_Range("B1", "D" + lastRow);
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlXYScatterSmoothNoMarkers;
Excel.Axis horizAxis = chartPage.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
horizAxis.MaximumScaleIsAuto = false;
horizAxis.MaximumScale = lastRow;
horizAxis.MinimumScaleIsAuto = false;
horizAxis.MinimumScale = 0;
horizAxis.HasTitle = false;
//horizAxis.AxisTitle.Text = "across the bottom";
Excel.Axis vertAxis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
vertAxis.HasMajorGridlines = false; // change this to whatever you wish
vertAxis.HasTitle = true;
vertAxis.AxisTitle.Text = "Volts";
vertAxis.MaximumScaleIsAuto = true;
//vertAxis.MaximumScale = 4; // you can pick this based on your input
vertAxis.MinimumScaleIsAuto = true;
// vertAxis.MinimumScale = -4;
//chartPage.ApplyChartTemplate("c:\\ctmp.crtx");
chartPage.Export(fileDir + "/Chart1.PNG", "PNG", false);
Image image = Image.FromFile(fileDir + "/Chart1.PNG");
pictureBox1.Image = image;
pictureBox1.SizeMode = PictureBoxSizeMode.StretchImage;
//xlWorkBook.SaveAs(fileUrlNoExt, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlNoChange, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
public ChartExam()
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlApp.Visible = true;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//add data
xlWorkSheet.Cells[1, 1] = "";
xlWorkSheet.Cells[1, 2] = "Student1";
xlWorkSheet.Cells[1, 3] = "Student2";
xlWorkSheet.Cells[1, 4] = "Student3";
xlWorkSheet.Cells[2, 1] = "Term1";
xlWorkSheet.Cells[2, 2] = "80";
xlWorkSheet.Cells[2, 3] = "65";
xlWorkSheet.Cells[2, 4] = "45";
xlWorkSheet.Cells[3, 1] = "Term2";
xlWorkSheet.Cells[3, 2] = "78";
xlWorkSheet.Cells[3, 3] = "72";
xlWorkSheet.Cells[3, 4] = "60";
xlWorkSheet.Cells[4, 1] = "Term3";
xlWorkSheet.Cells[4, 2] = "82";
xlWorkSheet.Cells[4, 3] = "80";
xlWorkSheet.Cells[4, 4] = "65";
xlWorkSheet.Cells[5, 1] = "Term4";
xlWorkSheet.Cells[5, 2] = "75";
xlWorkSheet.Cells[5, 3] = "82";
xlWorkSheet.Cells[5, 4] = "68";
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", "d5");
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
xlApp.ScreenUpdating = false;
xlWorkBook.SaveAs(Path.Combine(Environment.CurrentDirectory, "csharp.net-informations.xls"), Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
/// <summary>
/// 给工作表添加一个图表
/// </summary>
/// <param name="firstRange">数据工作表</param>
/// <param name="xData">x轴数据区域：格式A1:A14</param>
/// <param name="ydata">y轴数据区域：格式B1:E14</param>
/// <param name="chartName">图标名称</param>
/// <param name="xname">y轴名称</param>
/// <param name="yname">y轴名称</param>
/// <param name="chartType">图表类型</param>
public static void createInnerChart(Excel.Worksheet dataSheet, List <ChartStuct> charts, string chartName, string categoryName)
{
Excel.ChartObjects ChartObjects = (Excel.ChartObjects)dataSheet.ChartObjects(Missing.Value);
if (charts.Count < 1)
{
return; //无数不处理
}
//绘制第一维图表
Excel.Range firstRange = charts[0].dataRange;
Excel.XlChartType chartType = charts[0].chartType;
string yname = charts[0].yname;
int cols = 0;
foreach (ChartStuct chartStuct in charts)
{
cols += chartStuct.dataRange.Columns.Count;
}
Excel.ChartObject chartObject = ChartObjects.Add(cols * 60, 30, 800, 350);
chartObject.Chart.ChartWizard(firstRange, chartType, Missing.Value, Excel.XlRowCol.xlColumns, 1, 1, true, chartName, categoryName, yname, Missing.Value);
chartObject.Chart.ChartArea.ClearFormats();
chartObject.Chart.ChartType = chartType;
chartObject.Chart.ChartArea.Fill.BackColor.SchemeColor = 2;//整个图表区背景
//Console.WriteLine(chartObject.Chart.ChartArea.Fill.ForeColor.SchemeColor);
//Console.WriteLine(chartObject.Chart.ChartArea.Fill.ForeColor.RGB);
chartObject.Chart.ChartArea.Fill.ForeColor.SchemeColor = 2;
//Console.WriteLine(chartObject.Chart.ChartArea.Fill.ForeColor.RGB);
//Console.WriteLine(chartObject.Chart.PlotArea.Interior.ColorIndex);
//chartObject.Chart.PlotArea.Interior.PatternColorIndex = 19;
chartObject.Chart.PlotArea.Interior.ColorIndex = 2;//图例区域背景
Excel.Axis firstAxis = (Excel.Axis)chartObject.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
firstAxis.HasMajorGridlines = true;//不显示横向网格线
//firstAxis.HasTickLabels = false;
//chartObject.Chart.PlotArea.Border.LineStyle = Excel.XlLineStyle.xlLineStyleNone;//设置绘图区边框线条
Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chartObject.Chart.SeriesCollection(Type.Missing);
//xlSeries1.Fill=Microsoft.Office.Interop.Excel.ChartFillFormat.
if (seriesCollection.Count > 0)
{
Excel.Series seires1 = (Excel.Series)seriesCollection.Item(1);//具体数据图表区颜色
//seires1.Fill.BackColor.SchemeColor = 18;
seires1.Fill.ForeColor.SchemeColor = 46;
seires1.MarkerForegroundColorIndex = Excel.XlColorIndex.xlColorIndexNone;
seires1.HasLeaderLines = false;
}
int sc = seriesCollection.Count;
//绘制第二维图表
//修改第一维图表的各个序列的图例,这里有个bug，会用最后的标题覆盖前面的
// for (int i = 0; i < charts[0].seriesTitles.Length; i++)
//{
//string stitle = charts[0].seriesTitles[i];
//Excel.Series tmpSeires = (Excel.Series)seriesCollection.Item(1);
//tmpSeires.Name = stitle;
//}
//绘制第二个图表,多维这里设置 当有图表类型为XlChartType.xl3DColumn或其他3d效果时，抛 xlSeries.AxisGroup = Excel.XlAxisGroup.xlSecondary;异常
for (int i = 1; i < charts.Count; i++)
{
ChartStuct cs = charts[i];
string stitle = cs.seriesTitles[0];
Excel.Series xlSeries = (Excel.Series)seriesCollection.NewSeries();
xlSeries.Name = stitle;
xlSeries.HasLeaderLines = true;
xlSeries.Fill.ForeColor.SchemeColor = 46;
//xlSeries.Has3DEffect = true;
//xlSeries.HasDataLabels = true;
try
{
xlSeries.AxisGroup = Excel.XlAxisGroup.xlSecondary;
}
catch (Exception eee) {
Console.WriteLine(eee.Message);
}
xlSeries.ChartType = cs.chartType;
xlSeries.Values = cs.dataRange;
Excel.Axis valueAxis = (Excel.Axis)chartObject.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlSecondary);
valueAxis.HasTitle = true;
valueAxis.AxisTitle.Text = cs.yname;
}
}
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_IADE_V2\LPA_IADE.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, 255, 0, 0);
chartPage.SeriesCollection(1).Points(2).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 255, 0, 0);
chartPage.SeriesCollection(1).Points(3).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 255, 0, 0);
chartPage.SeriesCollection(1).Points(4).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 255, 0, 0);
chartPage.SeriesCollection(1).Points(5).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 255, 0, 0);
chartPage.SeriesCollection(1).Points(6).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 255, 0, 0);
chartPage.SeriesCollection(1).Points(7).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 255, 0, 0);
chartPage.SeriesCollection(1).Points(8).Format.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(0, 255, 0, 0);
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 = XlRgbColor.rgbRed;
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.Red;
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 = XlRgbColor.rgbRed;
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.Red;
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 = XlRgbColor.rgbRed;
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.Red;
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 = XlRgbColor.rgbRed;
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.Red;
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 = XlRgbColor.rgbRed;
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.Red;
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 = XlRgbColor.rgbRed;
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.Red;
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 = XlRgbColor.rgbRed;
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.Red;
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 = XlRgbColor.rgbRed;
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.Red;
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;
}
public void generateResponsesSlides(Office.IRibbonControl control)
{
JObject responses = this.addin.getFormResponses(globalUrl);
string fileName = this.addin.getTitle(globalUrl);
string paramWorkbookPath = @".\" + fileName + ".xlsx";
string paramPowerpointPath = @".\" + fileName + ".pptx";
List <Question> questions = parseResponses(responses);
PowerPoint.CustomLayout layout = this.addin.Application.ActivePresentation.SlideMaster.CustomLayouts[6];
for (int i = 0; i < questions.Count(); i++)
{
//Adicionar novo slide e mudar slide de foco
pptSlide = this.addin.Application.ActivePresentation.Slides.AddSlide(this.addin.currentSlide + 1, layout);
pptSlides.Add(pptSlide);
this.addin.Application.ActiveWindow.Presentation.Slides[this.addin.currentSlide + 1].Select();
this.addin.currentSlide++;
//Output do Titulo da Pergunta
this.addin.Application.ActiveWindow.View.Slide.Shapes.Title.TextFrame.TextRange.Text = questions[i].Title;
Excel.Worksheet dataSheet;
if (i == 0)
{
//Create instance to Excel workbook to work with chart data
Excel.Application excelApp = new Excel.Application();
excelApp.Visible = true;
if (!File.Exists(paramWorkbookPath))
{
dataWorkbook = excelApp.Workbooks.Add();
dataWorkbooks.Add(dataWorkbook);
dataWorkbook.Windows[1].WindowState = Excel.XlWindowState.xlMinimized;
//Accessing the data worksheet for chart
dataSheet = ((Excel.Worksheet)dataWorkbook.Worksheets[i + 1]);
dataWorkSheets.Add(dataSheet);
}
else
{
dataWorkbook = excelApp.Workbooks.Open(paramWorkbookPath);
dataWorkbooks.Add(dataWorkbook);
dataWorkbook.Windows[1].WindowState = Excel.XlWindowState.xlMinimized;
//Accessing the data worksheet for chart
dataSheet = ((Excel.Worksheet)dataWorkbook.Worksheets[i + 1]);
dataWorkSheets.Add(dataSheet);
}
}
else
{
dataSheet = dataWorkbook.Worksheets.Add();
dataWorkSheets.Add(dataSheet);
}
//Setting the range of chart
string[] rangeids = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
string lowerRange = "1";
int upRange = questions[i].Choices.Count() + 1;
string upperRange = upRange.ToString();
lowerRange = "A" + lowerRange;
//TODO TIRAR ESTE "F" HARDCODED
if (questions[i].Type == "GRID")
{
upperRange = "F" + upRange / 2;
int aux = 0;
string categoria1 = questions[i].Choices[0].row;
for (int y = 0; y < questions[i].Choices.Count; y++)
{
if (questions[i].Choices[y].row == categoria1)
{
aux++;
}
}
upperRange = rangeids[aux] + (questions[i].Choices.Count / 2 - 1).ToString();
gridUpperRangeGlobal = upperRange;
dataSheet.Cells.get_Range("A50").FormulaR1C1 = upperRange;
}
else
{
upperRange = "B" + upperRange;
dataSheet.Cells.get_Range("A50").FormulaR1C1 = upperRange;
}
Excel.Range tRange = dataSheet.Cells.get_Range(lowerRange, upperRange);
Excel.ListObject tbl1;
string tableName = "Tabela";
//Applying the set range on chart data table
tableName = tableName + i;
dataSheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, tRange, Type.Missing, Excel.XlYesNoGuess.xlYes, Type.Missing).Name = tableName;
tbl1 = dataSheet.ListObjects[tableName];
tbl1.Resize(tRange);
//Setting values for categories and respective series data
string option = "A";
string count = "B";
List <string> rows = new List <string>();
if (questions[i].Type == "GRID")
{
int iteraux = 2;
for (var j = 0; j < questions[i].Choices.Count; j++)
{
string optionaux = option + iteraux;
if (!rows.Contains(questions[i].Choices[j].row))
{
dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = questions[i].Choices[j].row;
rows.Add(questions[i].Choices[j].row);
iteraux++;
}
}
string[] columnids = { "A", "B", "C", "D", "E", "F", "G", "H" };
int column_index = 0;
bool series_written = false;
for (int v = 0; v < rows.Count; v++)
{
string categoria = rows[v];
column_index = 0;
for (int u = 0; u < questions[i].Choices.Count; u++)
{
if (questions[i].Choices[u].row == categoria)
{
string celula = columnids[column_index + 1] + (v + 2).ToString();
dataSheet.Cells.get_Range(celula).FormulaR1C1 = questions[i].Choices[u].count.ToString();
if (!series_written)
{
dataSheet.Cells.get_Range(columnids[column_index + 1] + (v + 1).ToString()).FormulaR1C1 = questions[i].Choices[u].option;
}
column_index++;
}
}
series_written = true;
}
dataSheet.Cells.get_Range("A1").FormulaR1C1 = "Try1";
}
else if (questions[i].Type == "SCALE")
{
for (var j = 0; j < questions[i].Choices.Count; j++)
{
int index1 = j + 2;
string optionaux = option + index1;
dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = "- " + questions[i].Choices[j].option.ToString() + " -";
}
for (var k = 0; k < questions[i].Choices.Count; k++)
{
int index2 = k + 2;
string countaux = count + index2;
dataSheet.Cells.get_Range(countaux).FormulaR1C1 = questions[i].Choices[k].count.ToString();
}
dataSheet.Cells.get_Range("A1").FormulaR1C1 = "Categoria";
dataSheet.Cells.get_Range("B1").FormulaR1C1 = "Try1";
}
else
{
for (var j = 0; j < questions[i].Choices.Count; j++)
{
int index1 = j + 2;
string optionaux = option + index1;
dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = questions[i].Choices[j].option;
}
for (var k = 0; k < questions[i].Choices.Count; k++)
{
int index2 = k + 2;
string countaux = count + index2;
dataSheet.Cells.get_Range(countaux).FormulaR1C1 = questions[i].Choices[k].count.ToString();
}
dataSheet.Cells.get_Range("A1").FormulaR1C1 = "Categoria";
dataSheet.Cells.get_Range("B1").FormulaR1C1 = "Try1";
}
// Insert graphic in Excel
//Output do Gráfico
float width = 500F;
float left = 230F;
if (questions[i].Choices.Count > 6)
{
width = 750F;
left = 130F;
}
else if (questions[i].Choices.Count > 12)
{
width = 1100;
left = 80F;
}
//Save value of last
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)dataSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = xlCharts.Add(50, 150, width, 250);
Excel.Chart chartPage = myChart.Chart;
object paramMissing = Type.Missing;
// Declare variables for the Chart.ChartWizard method.
object paramChartFormat = 1;
object paramCategoryLabels = 0;
object paramSeriesLabels = 0;
bool paramHasLegend = true;
// Create a new chart of the data.
myChart.Chart.ChartWizard(tRange, Excel.XlChartType.xlColumnClustered, paramChartFormat, Excel.XlRowCol.xlRows,
paramCategoryLabels, paramSeriesLabels, paramHasLegend, paramMissing, paramMissing, paramMissing, paramMissing);
chartRange = dataSheet.get_Range(lowerRange, upperRange);
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
if (questions[i].Type != "GRID")
{
chartPage.ChartTitle.Delete();
}
Excel.Axis excelaxis = chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
int maxScale = 0;
for (int h = 0; h < questions[i].Choices.Count; h++)
{
if (questions[i].Choices[h].count > maxScale)
{
maxScale = questions[i].Choices[h].count;
}
}
excelaxis.MajorUnit = (int)(maxScale + 10.0) / 5;
excelaxis.MinorUnit = (int)(maxScale + 10.0) / 10;
excelaxis.MinimumScale = 0;
excelaxis.MaximumScale = maxScale + 10.0;
myChart.Copy();
shapeRange = pptSlide.Shapes.Paste();
// Position the chart on the slide.
shapeRange.Left = left;
shapeRange.Top = 160F;
shapeRange.Height = 350F;
shapeRange.Width = width;
//Copy chart to xlsCharts
xlsCharts.Add(myChart);
}
// Save excel file
if (newTryBool == true)
{
//dataWorkbook.Save();
}
else
{
//dataWorkbook.SaveAs(paramWorkbookPath, paramMissing, paramMissing, paramMissing, paramMissing,
//paramMissing, Excel.XlSaveAsAccessMode.xlNoChange, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing);
}
pptSlides[0].Select();
// Save the presentation.
//this.addin.Application.ActivePresentation.SaveAs(paramPowerpointPath, PowerPoint.PpSaveAsFileType.ppSaveAsOpenXMLPresentation, Office.MsoTriState.msoTrue);
}
public void newTry(Office.IRibbonControl control)
{
//Limpar as respostas existentes no Inquérito
//this.addin.deleteResponses(globalUrl);
JObject responses = this.addin.getFormResponses(globalUrl);
List <Question> questions = parseResponses(responses);
int i = 0;
foreach (Excel.ChartObject xlsChart in xlsCharts)
{
xlsChart.Delete();
Excel.Worksheet dataSheet = dataWorkSheets[i];
//Setting the range of chart
string[] rangeids = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
string lowerRange = "1";
int upRange = questions[i].Choices.Count() + 1;
string upperRange = upRange.ToString();
int lastCellUsed = 0;
//Descobrir o initial range
for (int x = 0; x < 70; x++)
{
if (dataSheet.Cells[2, x + 1].Value != null)
{
lastCellUsed = x;
}
}
lowerRange = rangeids[lastCellUsed + 2] + lowerRange;
lowerRangeGlobal = lowerRange[0].ToString();
if (questions[i].Type == "GRID")
{
int aux = 0;
string categoria1 = questions[i].Choices[0].row;
for (int y = 0; y < questions[i].Choices.Count; y++)
{
if (questions[i].Choices[y].row == categoria1)
{
aux++;
}
}
upperRange = rangeids[aux + lastCellUsed + 2] + (questions[i].Choices.Count / 2 - 1).ToString();
gridUpperRangeGlobal = upperRange[0].ToString();
}
else
{
upperRange = rangeids[lastCellUsed + 3] + upperRange;
upperRangeGlobal = upperRange[0].ToString();
}
Excel.Range tRange = dataSheet.Cells.get_Range(lowerRange, upperRange);
Excel.ListObject tbl1;
string tableName = "Tabela";
//Applying the set range on chart data table
DateTime date1 = DateTime.Now;
dataSheet.Cells.get_Range("A50").FormulaR1C1 = tRange;
tableName = date1.ToString();
dataSheet.Cells.get_Range("A51").FormulaR1C1 = tableName;
dataSheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, tRange, Type.Missing, Excel.XlYesNoGuess.xlYes, Type.Missing).Name = tableName;
tbl1 = dataSheet.ListObjects[tableName];
tbl1.Resize(tRange);
//Setting values for categories and respective series data
string option = rangeids[lastCellUsed + 2];
string count = rangeids[lastCellUsed + 3];
List <string> rows = new List <string>();
if (questions[i].Type == "GRID")
{
int iteraux = 2;
for (var j = 0; j < questions[i].Choices.Count; j++)
{
string optionaux = option + iteraux;
if (!rows.Contains(questions[i].Choices[j].row))
{
dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = questions[i].Choices[j].row;
rows.Add(questions[i].Choices[j].row);
iteraux++;
}
}
string[] columnids = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
int column_index = 0;
bool series_written = false;
for (int v = 0; v < rows.Count; v++)
{
string categoria = rows[v];
column_index = lastCellUsed + 2;
for (int u = 0; u < questions[i].Choices.Count; u++)
{
if (questions[i].Choices[u].row == categoria)
{
string celula = columnids[column_index + 1] + (v + 2).ToString();
dataSheet.Cells.get_Range(celula).FormulaR1C1 = questions[i].Choices[u].count.ToString();
if (!series_written)
{
dataSheet.Cells.get_Range(columnids[column_index + 1] + (v + 1).ToString()).FormulaR1C1 = questions[i].Choices[u].option;
}
column_index++;
}
}
series_written = true;
}
dataSheet.Cells.get_Range(lowerRange).FormulaR1C1 = "Try " + (newTryCount + 2).ToString();
}
else if (questions[i].Type == "SCALE")
{
for (var j = 0; j < questions[i].Choices.Count; j++)
{
int index1 = j + 2;
string optionaux = option + index1;
dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = "- " + questions[i].Choices[j].option.ToString() + " -";
}
for (var k = 0; k < questions[i].Choices.Count; k++)
{
int index2 = k + 2;
string countaux = count + index2;
dataSheet.Cells.get_Range(countaux).FormulaR1C1 = questions[i].Choices[k].count.ToString();
}
dataSheet.Cells.get_Range(lowerRange).FormulaR1C1 = "Categoria";
string auxUpperRange = upperRange[0] + "1";
dataSheet.Cells.get_Range(auxUpperRange).FormulaR1C1 = "Try " + (newTryCount + 2).ToString();
}
else
{
for (var j = 0; j < questions[i].Choices.Count; j++)
{
int index1 = j + 2;
string optionaux = option + index1;
dataSheet.Cells.get_Range(optionaux).FormulaR1C1 = questions[i].Choices[j].option;
}
for (var k = 0; k < questions[i].Choices.Count; k++)
{
int index2 = k + 2;
string countaux = count + index2;
dataSheet.Cells.get_Range(countaux).FormulaR1C1 = questions[i].Choices[k].count.ToString();
}
dataSheet.Cells.get_Range(lowerRange).FormulaR1C1 = "Categoria";
string auxUpperRange = upperRange[0] + "1";
dataSheet.Cells.get_Range(auxUpperRange).FormulaR1C1 = "Try " + (newTryCount + 2).ToString();
}
// Insert graphic in Excel
//Output do Gráfico
float width = 500F;
float left = 230F;
if (questions[i].Choices.Count > 6)
{
width = 750F;
left = 130F;
}
else if (questions[i].Choices.Count > 12)
{
width = 1100;
left = 80F;
}
//Save value of last
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)dataSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = xlCharts.Add(50, 150, width, 250);
Excel.Chart chartPage = myChart.Chart;
object paramMissing = Type.Missing;
// Declare variables for the Chart.ChartWizard method.
object paramChartFormat = 1;
object paramCategoryLabels = 0;
object paramSeriesLabels = 0;
bool paramHasLegend = true;
// Create a new chart of the data.
myChart.Chart.ChartWizard(tRange, Excel.XlChartType.xlColumnClustered, paramChartFormat, Excel.XlRowCol.xlRows,
paramCategoryLabels, paramSeriesLabels, paramHasLegend, paramMissing, paramMissing, paramMissing, paramMissing);
chartRange = dataSheet.get_Range(lowerRange, upperRange);
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
Excel.Axis excelaxis = chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
int maxScale = 0;
for (int h = 0; h < questions[i].Choices.Count; h++)
{
if (questions[i].Choices[h].count > maxScale)
{
maxScale = questions[i].Choices[h].count;
}
}
if (questions[i].Type != "GRID")
{
chartPage.ChartTitle.Delete();
}
excelaxis.MajorUnit = (int)(maxScale + 10.0) / 5;
excelaxis.MinorUnit = (int)(maxScale + 10.0) / 10;
excelaxis.MinimumScale = 0;
excelaxis.MaximumScale = maxScale + 10.0;
PowerPoint.Slide currentSlide = this.addin.Application.ActiveWindow.View.Slide;
pptSlides[i].Select();
try
{
myChart.Copy();
}
catch (COMException e)
{
Thread.Sleep(500);
myChart.Copy();
}
shapeRange = pptSlides[i].Shapes.Paste();
PowerPoint.Shape previousGraph = pptSlides[i].Shapes[2];
previousGraph.Delete();
shapeRange.Left = left;
shapeRange.Top = 160F;
shapeRange.Height = 350F;
shapeRange.Width = width;
xlsChartsTemp.Add(myChart);
i++;
}
xlsCharts = xlsChartsTemp;
xlsChartsTemp = new List <Excel.ChartObject>();
newTryBool = true;
newTryCount++;
// dataWorkbook.Save();
}
private void btnCrossPlotExcle_Click(object sender, EventArgs e)
{
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Excel is not installed.");
return;
}
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//add data
xlWorkSheet.Cells[1, 1] = "";
xlWorkSheet.Cells[1, 2] = "Student1";
xlWorkSheet.Cells[1, 3] = "Student2";
xlWorkSheet.Cells[1, 4] = "Student3";
xlWorkSheet.Cells[2, 1] = "Term1";
xlWorkSheet.Cells[2, 2] = "80";
xlWorkSheet.Cells[2, 3] = "65";
xlWorkSheet.Cells[2, 4] = "45";
xlWorkSheet.Cells[3, 1] = "Term2";
xlWorkSheet.Cells[3, 2] = "78";
xlWorkSheet.Cells[3, 3] = "72";
xlWorkSheet.Cells[3, 4] = "60";
xlWorkSheet.Cells[4, 1] = "Term3";
xlWorkSheet.Cells[4, 2] = "82";
xlWorkSheet.Cells[4, 3] = "80";
xlWorkSheet.Cells[4, 4] = "65";
xlWorkSheet.Cells[5, 1] = "Term4";
xlWorkSheet.Cells[5, 2] = "75";
xlWorkSheet.Cells[5, 3] = "82";
xlWorkSheet.Cells[5, 4] = "68";
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", "d5");
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
xlWorkBook.SaveAs("D:\\1.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
// データテーブルをエクセルへエクスポート（ＸＲ管理図・ヒストグラム付き）
public void ExportToExcelWithXrChart(DataTable dt)
{
if (dt.Rows.Count == 0)
{
return;
}
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
// column headings
for (int i = 0; i < dt.Columns.Count; i++)
{
xlWorkSheet.Cells[1, (i + 1)] = dt.Columns[i].ColumnName;
}
// rows
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
xlWorkSheet.Cells[(i + 2), (j + 1)] = dt.Rows[i][j];
}
}
int row = xlWorkSheet.UsedRange.Rows.Count;
string address1 = "B1:B" + row.ToString() + ",K1:K" + row.ToString() + ",M1:N" + row.ToString();
string address2 = "B1:B" + row.ToString() + ",L1:L" + row.ToString();
string address3 = "AB41:AC53";
string address4 = "F1:J" + row.ToString();
// チャート１（Ｘ－Ｒ管理図１）
Excel.Range chartRange1;
Excel.ChartObjects xlCharts1 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart1 = (Excel.ChartObject)xlCharts1.Add(800, 10, 600, 250);
Excel.Chart chartPage1 = myChart1.Chart;
chartRange1 = xlWorkSheet.get_Range(address1);
chartPage1.SetSourceData(chartRange1, misValue);
chartPage1.ChartType = Excel.XlChartType.xlLine;
chartPage1.HasLegend = false;
chartPage1.HasTitle = true;
chartPage1.ChartTitle.Text = "X " + dt.Rows[0]["inspect"].ToString() + " " + dt.Rows[0]["line"].ToString();
Excel.Axis xAxis1 = (Excel.Axis)chartPage1.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
xAxis1.CategoryType = Excel.XlCategoryType.xlCategoryScale;
Excel.SeriesCollection SeriesCollection1 = (Excel.SeriesCollection)myChart1.Chart.SeriesCollection(misValue);
Excel.Series s2 = SeriesCollection1.Item(2);
s2.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbCoral;
Excel.Series s3 = SeriesCollection1.Item(3);
s3.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbCoral;
// チャート２（Ｘ－Ｒ管理図２）
Excel.Range chartRange2;
Excel.ChartObjects xlCharts2 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart2 = (Excel.ChartObject)xlCharts1.Add(800, 280, 600, 250);
Excel.Chart chartPage2 = myChart2.Chart;
chartRange2 = xlWorkSheet.get_Range(address2);
chartPage2.SetSourceData(chartRange2, misValue);
chartPage2.ChartType = Excel.XlChartType.xlLine;
chartPage2.HasLegend = false;
chartPage2.HasTitle = true;
chartPage2.ChartTitle.Text = "R " + dt.Rows[0]["inspect"].ToString() + " " + dt.Rows[0]["line"].ToString();
Excel.Axis xAxis2 = (Excel.Axis)chartPage2.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
xAxis2.CategoryType = Excel.XlCategoryType.xlCategoryScale;
// チャート３（ヒストグラム）
Excel.Range chartRange3;
Excel.ChartObjects xlCharts3 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart3 = (Excel.ChartObject)xlCharts1.Add(800, 550, 350, 250);
Excel.Chart chartPage3 = myChart3.Chart;
string[,] formulas = new string[13, 3];
string[] formula1 = new string[]
{
"BIN",
"=MIN(" + address4 + ")",
"=AA42+(AA$53-AA$42)/10",
"=AA43+(AA$53-AA$42)/10",
"=AA44+(AA$53-AA$42)/10",
"=AA45+(AA$53-AA$42)/10",
"=AA46+(AA$53-AA$42)/10",
"=AA47+(AA$53-AA$42)/10",
"=AA48+(AA$53-AA$42)/10",
"=AA49+(AA$53-AA$42)/10",
"=AA50+(AA$53-AA$42)/10",
"=AA51+(AA$53-AA$42)/10",
"=MAX(" + address4 + ")",
};
string[] formula2 = new string[]
{
@"LABEL",
@"=TEXT(AA42,""0.0"")",
@"=TEXT(AA42,""0.0"")&"" - ""&TEXT(AA43,""0.0"")",
@"=TEXT(AA43,""0.0"")&"" - ""&TEXT(AA44,""0.0"")",
@"=TEXT(AA44,""0.0"")&"" - ""&TEXT(AA45,""0.0"")",
@"=TEXT(AA45,""0.0"")&"" - ""&TEXT(AA46,""0.0"")",
@"=TEXT(AA46,""0.0"")&"" - ""&TEXT(AA47,""0.0"")",
@"=TEXT(AA47,""0.0"")&"" - ""&TEXT(AA48,""0.0"")",
@"=TEXT(AA48,""0.0"")&"" - ""&TEXT(AA49,""0.0"")",
@"=TEXT(AA49,""0.0"")&"" - ""&TEXT(AA50,""0.0"")",
@"=TEXT(AA50,""0.0"")&"" - ""&TEXT(AA51,""0.0"")",
@"=TEXT(AA51,""0.0"")&"" - ""&TEXT(AA52,""0.0"")",
@"=TEXT(AA53,""0.0"")"
};
string[] formula3 = new string[]
{
@"FREQUENCY",
@"=COUNTIF(" + address4 + @",""<=""&AA42)",
@"=COUNTIF(" + address4 + @","">""&AA42)-COUNTIF(" + address4 + @","">""&AA43)",
@"=COUNTIF(" + address4 + @","">""&AA43)-COUNTIF(" + address4 + @","">""&AA44)",
@"=COUNTIF(" + address4 + @","">""&AA44)-COUNTIF(" + address4 + @","">""&AA45)",
@"=COUNTIF(" + address4 + @","">""&AA45)-COUNTIF(" + address4 + @","">""&AA46)",
@"=COUNTIF(" + address4 + @","">""&AA46)-COUNTIF(" + address4 + @","">""&AA47)",
@"=COUNTIF(" + address4 + @","">""&AA47)-COUNTIF(" + address4 + @","">""&AA48)",
@"=COUNTIF(" + address4 + @","">""&AA48)-COUNTIF(" + address4 + @","">""&AA49)",
@"=COUNTIF(" + address4 + @","">""&AA49)-COUNTIF(" + address4 + @","">""&AA50)",
@"=COUNTIF(" + address4 + @","">""&AA50)-COUNTIF(" + address4 + @","">""&AA51)",
@"=COUNTIF(" + address4 + @","">""&AA51)-COUNTIF(" + address4 + @","">=""&AA52)",
@"=COUNTIF(" + address4 + @","">=""&AA53)"
};
for (int i = 0; i < 13; i++)
{
xlWorkSheet.Cells[41 + i, 27].Formula = formula1[i];
xlWorkSheet.Cells[41 + i, 28].Formula = formula2[i];
xlWorkSheet.Cells[41 + i, 29].Formula = formula3[i];
}
chartRange3 = xlWorkSheet.get_Range(address3);
chartPage3.SetSourceData(chartRange3, misValue);
chartPage3.ChartType = Excel.XlChartType.xlColumnClustered;
chartPage3.HasLegend = false;
chartPage3.HasTitle = true;
chartPage3.ChartTitle.Text = "Frequency " + dt.Rows[0]["inspect"].ToString() + " " + dt.Rows[0]["line"].ToString();
Excel.ChartGroup ChartGroup1 = (Excel.ChartGroup)myChart3.Chart.ChartGroups(1);
ChartGroup1.GapWidth = 0;
xlApp.Visible = true;
}
public void completarTablas(double[] aleatorios, double[,] frecuencias, double chi, int intervalos)
{
//Completa tabla de numeros aleatorios
xlWorkSheet.Range["D1:H1"].Merge();
xlWorkSheet.Range["D1:H1"].Value = "Chi Cuadrado";
xlWorkSheet.Range["D1:H1"].Font.Size = 15;
xlWorkSheet.Range["D1:H1"].HorizontalAlignment = HorizontalAlignment.Center;
xlWorkSheet.Cells[3, 1] = "N";
xlWorkSheet.Cells[3, 2] = "Aleatorio";
//Formatea los campos numericos a 4 decimales
xlWorkSheet.Range["B:B"].NumberFormat = "0.0000";
xlWorkSheet.Range["E:E"].NumberFormat = "0.0000";
xlWorkSheet.Range["F:F"].NumberFormat = "0.0000";
for (int i = 0; i < aleatorios.Length; i++)
{
xlWorkSheet.Cells[i + 4, "A"] = i + 1;
xlWorkSheet.Cells[i + 4, "B"] = aleatorios[i];
}
//cabecera de la tabla
xlWorkSheet.Cells[3, 5] = "Mínimo";
xlWorkSheet.Cells[3, 6] = "Máximo";
xlWorkSheet.Cells[3, 7] = "O(i)";
xlWorkSheet.Cells[3, 8] = "E(i)";
xlWorkSheet.Cells[3, 9] = "(O-E)^2/E";
//Cargamos los datos
for (int i = 0; i < frecuencias.GetLength(1); i++)
{
xlWorkSheet.Cells[i + 4, "D"] = i + 1;
xlWorkSheet.Cells[i + 4, "E"] = frecuencias[0, i];
xlWorkSheet.Cells[i + 4, "F"] = frecuencias[1, i];
xlWorkSheet.Cells[i + 4, "G"] = frecuencias[2, i];
xlWorkSheet.Cells[i + 4, "H"] = frecuencias[3, i];
xlWorkSheet.Cells[i + 4, "I"] = frecuencias[4, i];
}
//Coloca el resultado final
xlWorkSheet.Cells[frecuencias.GetLength(1) + 4, "H"].value = "X^2";
xlWorkSheet.Cells[frecuencias.GetLength(1) + 4, "I"].value = chi;
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;
int inter = 3 + intervalos;
chartRange = xlWorkSheet.get_Range("G3", "H" + inter);
chartPage.HasTitle = true;
chartPage.ChartTitle.Caption = "Chi Cuadrado de los Números Aleatorios Generados";
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
chartPage.HasLegend = true;
chartPage.ShowDataLabelsOverMaximum = true;
//Crea Excel
//xlWorkBook.SaveAs("csharp.net-informations.xls", 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();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
private void addGraphic(String seriesDate)
{
String currentPath = Directory.GetCurrentDirectory();
String prefix = metroTextBox7.Text;
String expNewPath = currentPath + "\\Experiments" + "\\" + prefix + "Series" + seriesDate + "{" + SeriesNumber + "}";
String logFileName = "\\Log.txt";
if (Directory.Exists(expNewPath))
{
List <int> solvedTrialsList = new List <int>();
int numberOfTrials = 0;
DirectoryInfo directory = new DirectoryInfo(expNewPath);
DirectoryInfo[] scopeDirectories = directory.GetDirectories();
foreach (DirectoryInfo dinf in scopeDirectories)
{
String logPath = dinf.FullName + logFileName;
if (File.Exists(logPath))
{
StreamReader reader = new StreamReader(logPath);
bool isSolved = false;
while (!reader.EndOfStream)
{
String line = reader.ReadLine();
if (line.Contains(GLOBAL_OPTIMUM_FOUND))
{
isSolved = true;
}
if (line.Contains(NUMBER_OF_TRIALS) && isSolved)
{
int.TryParse(string.Join(String.Empty, reader.ReadLine().Where(ch => char.IsDigit(ch))), out numberOfTrials);
solvedTrialsList.Add(numberOfTrials);
break;
}
}
reader.Close();
}
}
int c = 1;
int delta = Convert.ToInt32(DeltaTextBox.Text);
int NMax = Convert.ToInt32(NMaxTextBox.Text);
int solvedCounter = 0;
Dictionary <int, int> graphicParameters = new Dictionary <int, int>();
while ((c * delta) <= NMax)
{
foreach (int countOfTrials in solvedTrialsList.ToArray())
{
if ((c * delta) > countOfTrials)
{
solvedCounter++;
solvedTrialsList.Remove(countOfTrials);
}
}
int percent = Convert.ToInt32(((double)solvedCounter / ComboSize) * 100);
graphicParameters.Add(c * delta, percent);
c++;
}
List <int> deltaValues = graphicParameters.Keys.ToList();
List <int> percentValues = graphicParameters.Values.ToList();
excelApp = new Excel.Application();
excelApp.Visible = false;
Excel.Workbook excelWorkBook = excelApp.Workbooks.Add(Type.Missing);
Excel.Worksheet excelWorkSheet = (Excel.Worksheet)excelWorkBook.Worksheets.Item[1];
excelWorkSheet.Name = "Operation Characteristics";
excelWorkSheet.Cells[1][1] = "Delta";
excelWorkSheet.Cells[2][1] = "Solved percent";
for (int i = 0; i < deltaValues.Count; i++)
{
excelWorkSheet.Cells[1][i + 2] = deltaValues[i];
}
for (int i = 0; i < percentValues.Count; i++)
{
excelWorkSheet.Cells[2][i + 2] = percentValues[i];
}
Excel.Range range = (Excel.Range)excelWorkSheet.Range[excelWorkSheet.Cells[1][2], excelWorkSheet.Cells[2][percentValues.Count + 1]];
range.Cells.Font.Name = "Arial";
range.Cells.Font.Size = 12;
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
range.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
range.EntireColumn.AutoFit();
range.EntireRow.AutoFit();
range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders.get_Item(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders.get_Item(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous;
/* xlLine */
excelWorkSheet.Activate();
Excel.ChartObjects lineChartsObjects = (Excel.ChartObjects)excelWorkSheet.ChartObjects();
Excel.ChartObject lineChartsObject = lineChartsObjects.Add(200, 10, 350, 250);
lineChartsObject.Chart.ChartWizard(range, Excel.XlChartType.xlLine, 1, Excel.XlRowCol.xlColumns, 1,
0, false, "Operating Characterisitics", "Delta", "Percent", Type.Missing);
String xlLineName = "\\Line Graphic.bmp";
lineChartsObject.Chart.Export(expNewPath + xlLineName, Type.Missing, Type.Missing);
/* xlColumn */
Excel.ChartObjects columnChartsObjects = (Excel.ChartObjects)excelWorkSheet.ChartObjects();
Excel.ChartObject columnChartsObject = columnChartsObjects.Add(570, 10, 350, 250);
columnChartsObject.Chart.ChartWizard(range, Excel.XlChartType.xlColumnClustered, 1, Excel.XlRowCol.xlColumns, 1,
0, false, "Operating Characterisitics", "Delta", "Percent", Type.Missing);
String xlColumnName = "\\Column Graphic.bmp";
columnChartsObject.Chart.Export(expNewPath + xlColumnName, Type.Missing, Type.Missing);
String fileName = "\\Operating Characteristics.xlsx";
excelApp.Application.ActiveWorkbook.SaveAs(expNewPath + fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
deallocateObject(excelWorkSheet);
deallocateObject(excelWorkBook);
deallocateObject(excelApp);
}
}
static void makeGraphs(string file)
{
Excel.Application excelApp = null;
Excel.Workbook workbook = null;
Excel.Sheets sheets = null;
Excel.Worksheet dataSheet = null;
Excel.Worksheet newSheet = null;
Excel.Worksheet chartSheet = null;
Excel.Range range = null;
Excel.Range dataR = null;
int rowC = 0;
try {
excelApp = new Excel.Application();
string dir = file.Substring(0, file.LastIndexOf("\\") + 1);
string fm = file.Substring(0, file.Length - 4).Substring(file.LastIndexOf("\\") + 1);
workbook = excelApp.Workbooks.Open(file, 0, false, 6, Type.Missing, Type.Missing, Type.Missing, XlPlatform.xlWindows, ",",
true, false, 0, false, false, false);
sheets = workbook.Sheets;
dataSheet = sheets[1];
dataSheet.Name = "data";
newSheet = (Worksheet)sheets.Add(Type.Missing, dataSheet, Type.Missing, Type.Missing);
newSheet.Name = "table";
chartSheet = (Worksheet)sheets.Add(Type.Missing, dataSheet, Type.Missing, Type.Missing);
chartSheet.Name = "graph";
Excel.ChartObjects xlChart = (Excel.ChartObjects)chartSheet.ChartObjects(Type.Missing);
dataR = dataSheet.UsedRange;
rowC = dataR.Rows.Count;
range = newSheet.get_Range("A1");
PivotCaches pCs = workbook.PivotCaches();
PivotCache pC = pCs.Create(XlPivotTableSourceType.xlDatabase, dataR, Type.Missing);
PivotTable pT = pC.CreatePivotTable(TableDestination: range, TableName: "PivotTable1");
PivotField fA = pT.PivotFields("Time");
PivotField fB = pT.PivotFields("Command");
fA.Orientation = XlPivotFieldOrientation.xlRowField;
fA.Position = 1;
fB.Orientation = XlPivotFieldOrientation.xlColumnField;
fB.Position = 1;
pT.AddDataField(pT.PivotFields("%CPU"), "Sum of %CPU", XlConsolidationFunction.xlSum);
ChartObject pChart = (Excel.ChartObject)xlChart.Add(0, 0, 650, 450);
Chart chartP = pChart.Chart;
chartP.SetSourceData(pT.TableRange1, Type.Missing);
chartP.ChartType = XlChartType.xlLine;
excelApp.DisplayAlerts = false;
workbook.SaveAs(@dir + fm, XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, false, false, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing);
workbook.Close(true, Type.Missing, Type.Missing);
excelApp.Quit();
} catch {
Console.WriteLine("Had issues interacting with your Excel installation...maybe try a restart?");
using (StreamWriter outfile = File.AppendText("output.txt")) {
outfile.WriteLine("Did have issues interacting with Excel on " + file);
}
} finally {
/*Excel.Application excelApp = null;
* Excel.Workbook workbook = null;
* Excel.Sheets sheets = null;
* Excel.Worksheet dataSheet = null;
* Excel.Worksheet newSheet = null;
* Excel.Worksheet chartSheet = null;
* Excel.Range range = null;
* Excel.Range dataR = null;*/
releaseObject(dataR);
releaseObject(range);
releaseObject(chartSheet);
releaseObject(newSheet);
releaseObject(dataSheet);
releaseObject(sheets);
releaseObject(workbook);
releaseObject(excelApp);
}
}
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.TopMargin = application.CentimetersToPoints((float)Configuration_Class.doc_Top_Merge);
document.Sections.PageSetup.RightMargin = application.CentimetersToPoints((float)Configuration_Class.doc_Right_Merge);
range.Text = Configuration_Class.Organization_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;
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 Romman";
//Индексикация столбцов и строк в 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
{
}
finally
{
switch (format)
{
case Document_format.Word:
//Сохранение документа с названием из метода и формата doc
document.SaveAs2(name, word.WdSaveFormat.wdFormatDocument);
break;
case Document_format.PDF:
//Сохранение документа в формате PDF
document.SaveAs2(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;
//Диапазон значений по оси Х
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
{
//Сохранение книги
worksheet.SaveAs(name, application_ex.DefaultSaveFormat);
//Закрытие книги
workbook.Close();
//Завершение процесса
application_ex.Quit();
}
break;
}
break;
case false:
System.Windows.Forms.MessageBox.Show("Введите название документа");
break;
}
}
private void ExportToExcel()
{
// Creating a Excel object.
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
try
{
worksheet = workbook.ActiveSheet;
worksheet.Name = "Работники";
int cellRowIndex = 1;
int cellColumnIndex = 1;
//Loop through each row and read value from each column.
int rowCount = 0;
for (int i = 0; i < dgv.Rows.Count; i++)
{
for (int j = 0; j < dgv.Columns.Count; j++)
{
if (cellRowIndex == 1)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dgv.Columns[j].HeaderText;
}
else
{
if (dgv.Rows[i].Cells[j].Value != DBNull.Value)
{
worksheet.Cells[cellRowIndex, cellColumnIndex] = dgv.Rows[i - 1].Cells[j].Value.ToString();
}
else
{
break;
}
}
cellColumnIndex++;
}
cellColumnIndex = 1;
cellRowIndex++;
rowCount = i;
}
// Set formatting
Range formatRange;
formatRange = worksheet.get_Range("a1", "d1");
formatRange.EntireRow.Font.Bold = true;
formatRange.Borders.Weight = XlBorderWeight.xlThin;
worksheet.Range["A1", "D4"].Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
worksheet.Range["A2", "D" + rowCount].Style.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
rowCount++;
worksheet.get_Range("a2", "d" + rowCount).Borders.Weight = XlBorderWeight.xlMedium;
((Range)worksheet.Columns["A", System.Type.Missing]).EntireColumn.ColumnWidth = 50;
((Range)worksheet.Columns["B", System.Type.Missing]).EntireColumn.ColumnWidth = 25;
((Range)worksheet.Columns["C", System.Type.Missing]).EntireColumn.ColumnWidth = 25;
((Range)worksheet.Columns["D", System.Type.Missing]).EntireColumn.ColumnWidth = 25;
// Biuld histogram chart
object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Range chartRange;
Microsoft.Office.Interop.Excel.ChartObjects xlCharts = (Microsoft.Office.Interop.Excel.ChartObjects)worksheet.ChartObjects(Type.Missing);
Microsoft.Office.Interop.Excel.ChartObject myChart = (Microsoft.Office.Interop.Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
Microsoft.Office.Interop.Excel.Chart chartPage = myChart.Chart;
Range fCol = worksheet.get_Range("A2", "A" + rowCount);
Range sCol = worksheet.get_Range("D2", "D" + rowCount);
chartRange = worksheet.get_Range(fCol, sCol);
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlColumnClustered;
//Getting the location and file name of the excel to save from user.
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
saveDialog.FilterIndex = 2;
if (saveDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
workbook.SaveAs(saveDialog.FileName);
MessageBox.Show("Export Successful");
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
excel.Quit();
workbook = null;
excel = null;
}
}
private void btnConvert_Click(object sender, EventArgs e)
{
string inputPath = this.textBox2.Text;
string outputPath = this.textBox3.Text;
Object misValue = System.Reflection.Missing.Value;
Excel.Application xls = new Excel.Application();
String excelExt = "xlsx";
Directory.CreateDirectory(outputPath);
this.textBox4.Clear();
int numCheckedStations = checkedListBox2.CheckedItems.Count;
int numStationsProcessed = 0;
foreach (string stationCode in checkedListBox2.CheckedItems)
{
numStationsProcessed++;
Excel.Workbook xlsWorkBook = xls.Workbooks.Add(misValue);
Excel.Worksheet xlsWorkSheet = (Excel.Worksheet)xlsWorkBook.Sheets[1];
System.IO.StreamReader fileReader;
String info1, info2;
int nt = 0;
double dt;
String filePathPrefix = inputPath + "\\" + stationCode;
String outputFile = outputPath + "\\" + stationCode + "." + excelExt;
//this.textBox4.Text +="Processing "+stationCode + "("+numStationsProcessed+"/"+numCheckedStations+") ";
this.textBox4.AppendText("Processing " + stationCode + "(" + numStationsProcessed + "/" + numCheckedStations + ") ");
xlsWorkSheet.Cells[1, 1] = "Component";
xlsWorkSheet.Cells[2, 1] = "No. of timesteps";
xlsWorkSheet.Cells[3, 1] = "Size of timesteps (seconds)";
xlsWorkSheet.Cells[4, 1] = "Time (seconds)";
xlsWorkSheet.Cells[5, 1] = "Acceleration";
String[] componentsCode = { "000", "090", "ver" };
String[] componentsStr = { "X-axis (000)", "Y-axis (090)", "Z-axis (ver)" };
for (int k = 0; k < 3; k++) //Iterates each component file
{
this.textBox4.AppendText(Convert.ToString(k + 1) + "...");
String ext = componentsCode[k];
fileReader = new StreamReader(new FileStream(filePathPrefix + "." + ext, FileMode.Open));
info1 = fileReader.ReadLine();
info2 = fileReader.ReadLine();
int LastNonEmpty = -1;
String[] info2Array = info2.Split();
for (int i = 0; i < info2Array.Length; i++)
{
if (info2Array[i] != "")
{
LastNonEmpty += 1;
info2Array[LastNonEmpty] = info2Array[i];
}
}
nt = Convert.ToInt32(info2Array[0]); //number of time steps
dt = Convert.ToDouble(info2Array[1]); //time step size
Double[,] timeRange = new double[nt, 1];
xlsWorkSheet.Cells[1, 2 + k] = componentsStr[k];
xlsWorkSheet.Cells[2, 2 + k] = nt;
xlsWorkSheet.Cells[3, 2 + k] = dt;
LastNonEmpty = -1;
String lines = fileReader.ReadToEnd();
String[] strArray = lines.Split();
Double[,] dblArray = new Double[nt, 1]; //needs to be 2-d to be able to bulk write
for (int i = 0; i < strArray.Length; i++)
{
if (strArray[i] != "")
{
LastNonEmpty += 1;
timeRange[LastNonEmpty, 0] = LastNonEmpty * dt;
dblArray[LastNonEmpty, 0] = Convert.ToDouble(strArray[i].Replace("\n", ""));
}
}
int nt2 = LastNonEmpty + 1;
if (k == 0)
{
xlsWorkSheet.Range["A5"].Resize[nt2, 1].Value = timeRange; // first column filled with time steps
}
Excel.Range cell;
cell = xlsWorkSheet.Cells[5, 2 + k];
xlsWorkSheet.Range[cell, cell].Resize[nt2, 1].Value = dblArray; //bulk write at column "B, C, D"
}
Excel.WorksheetFunction wsf = xls.WorksheetFunction;
Double maxAcc = wsf.Max(xlsWorkSheet.Range[xlsWorkSheet.Cells[5, 4], xlsWorkSheet.Cells[nt + 4, 4]]); //find the maximum and minimum
Double minAcc = wsf.Min(xlsWorkSheet.Range[xlsWorkSheet.Cells[5, 4], xlsWorkSheet.Cells[nt + 4, 4]]);
Double maxAmpAcc = Math.Max(maxAcc, -1.0 * minAcc); //Select the max of absolute value of two
for (int k = 0; k < 3; k++) // Plotting
{
String ext = componentsCode[k];
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlsWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(200, 80 + 300 * k, 600, 250);
Excel.Chart chartPage = myChart.Chart;
chartRange = xlsWorkSheet.Range[xlsWorkSheet.Cells[5, 2 + k], xlsWorkSheet.Cells[nt + 4, 2 + k]];
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlLine;
chartPage.HasTitle = true;
Excel.Series series = (Excel.Series)chartPage.SeriesCollection(1);
Microsoft.Office.Interop.Excel.Axis xAxis = (Microsoft.Office.Interop.Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
Microsoft.Office.Interop.Excel.Axis yAxis = (Microsoft.Office.Interop.Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
xAxis.HasTitle = true;
xAxis.AxisTitle.Text = "Time (sec)";
xAxis.CategoryNames = (Excel.Range)xlsWorkSheet.Range["A5"].Resize[nt, 1];
xAxis.TickLabelPosition = Excel.XlTickLabelPosition.xlTickLabelPositionLow;
yAxis.HasTitle = true;
yAxis.AxisTitle.Text = "Acceleration (cm/s^2)";
yAxis.MinimumScale = (-1 * maxAmpAcc * 3.0);
yAxis.MaximumScale = (maxAmpAcc * 3.0);
series.Name = ext;
switch (k)
{
case 0:
chartPage.ChartTitle.Text = "[" + stationCode + "]: " + "Acceleration along X-axis (" + ext + ")";
series.Border.Color = (int)Excel.XlRgbColor.rgbRed;
break;
case 1:
chartPage.ChartTitle.Text = "[" + stationCode + "]: " + "Acceleration along Y-axis (" + ext + ")";
series.Border.Color = (int)Excel.XlRgbColor.rgbBlue;
break;
case 2:
chartPage.ChartTitle.Text = "[" + stationCode + "]: " + "Acceleration along Z-axis (" + ext + ")";
series.Border.Color = (int)Excel.XlRgbColor.rgbGreen;
break;
}
}
this.textBox4.AppendText("Done..!" + Environment.NewLine);
if (File.Exists(outputFile)) // delete file if it already exists
{
File.Delete(outputFile);
}
xlsWorkBook.SaveAs(outputFile, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, misValue, misValue,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
misValue, misValue, misValue, misValue, misValue);
xlsWorkBook.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWorkSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlsWorkBook);
}
xls.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xls);
//MessageBox.Show("Done!");
this.textBox4.AppendText("Finished!!" + Environment.NewLine);
}
public void AddCharts(string reportFile)
{
var excel = new Excel.Application()
{
DisplayAlerts = false
};
excel.Workbooks.Open(reportFile);
Excel._Worksheet workSheet = (Excel.Worksheet)excel.ActiveSheet;
var sheetNumber = 1;
var chartPositionNumber = 2;
foreach (Excel.Worksheet sheet in excel.Worksheets)
{
if (sheet.Name != "Charts")
{
// Set the datasheet for the source of the data
Excel.Worksheet dataSheet = excel.Worksheets[sheetNumber];
// Open the chart sheet to save the charts
workSheet = excel.ActiveWorkbook.Sheets["Charts"];
workSheet.Select();
// Get column & row length
var colomn = dataSheet.UsedRange.Columns.Count;
var rows = dataSheet.UsedRange.Rows.Count - 1;
// Chart settings and stuff
Excel.ChartObjects xlCharts = (Excel.ChartObjects)workSheet.ChartObjects(Type.Missing);
Excel.ChartObject runChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
Excel.Chart runChartPage = runChart.Chart;
runChartPage.ChartType = Excel.XlChartType.xlLine;
// set ChartStyle based on Office version
var chartStyle = 301;
if (Type.GetTypeFromProgID("Excel.Application.14") != null)
{
chartStyle = 2;
}
runChartPage.HasTitle = true;
runChartPage.HasLegend = true;
runChartPage.ChartTitle.Text = sheet.Name;
runChartPage.ChartStyle = chartStyle;
// Position of chart
var runChartPosition = "B" + chartPositionNumber;
Excel.Range runChartPlacementRange = workSheet.get_Range(runChartPosition, runChartPosition);
runChart.Top = runChartPlacementRange.Top;
runChart.Left = runChartPlacementRange.Left;
chartPositionNumber = chartPositionNumber + 21;
// Size of Chart
runChart.Width = 500;
runChart.Height = 250;
Excel.SeriesCollection runSeriesCollection = runChartPage.SeriesCollection();
// Create run line chart
for (int i = 2; i <= (colomn); i++)
{
Excel.Series runSeries = runSeriesCollection.NewSeries();
runSeries.Name = dataSheet.Cells[1, i].Value;
// set correct range for chart data
var ia = i;
// Time range
var xValuesBegin = ParseColumnName(1) + "2";
var xValuesEnd = ParseColumnName(1) + (rows.ToString());
var valuesBegin = ParseColumnName(ia) + (2).ToString();
var valuesEnd = ParseColumnName(ia) + (rows + 1).ToString();
runSeries.XValues = dataSheet.get_Range(xValuesBegin, xValuesEnd);
runSeries.Values = dataSheet.get_Range(valuesBegin, valuesEnd);
}
}
sheetNumber++;
}
var error = new ErrorHandler();
try
{
// Save excel sheet
workSheet.SaveAs(reportFile);
}
catch (Exception)
{
excel.Quit();
error.Exit(93);
}
// Close sheet
excel.Quit();
}
private void ButtonGenerateReport_Click(object sender, System.EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet previousImplementationsSheet;
Excel.Worksheet departmentalIssuesSheet;
Excel.Worksheet departmentalSuggestionsSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
previousImplementationsSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
departmentalIssuesSheet = (Excel.Worksheet)xlWorkBook.Worksheets.Add(misValue, misValue, misValue, misValue);
departmentalSuggestionsSheet = (Excel.Worksheet)xlWorkBook.Worksheets.Add(misValue, misValue, misValue, misValue);
//add data
MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder();
builder.Server = "scrumdevils.cis440.com";
builder.Database = "scrumdevils_";
builder.UserID = "scrumdevils";
builder.Password = "******";
MySqlConnection connection3 = new MySqlConnection(builder.ToString());
connection3.Open();
string selectDepartmentTitle = "SELECT `post title` FROM usaa where `feedback type` = 'Current Departmental Issues' ORDER BY ranking DESC LIMIT 0,1";
MySqlCommand selectCurrentDepartmentCommand = new MySqlCommand(selectDepartmentTitle, connection3);
string departmentTitle;
departmentTitle = selectCurrentDepartmentCommand.ExecuteScalar().ToString();
string selectDepartmentBody = "SELECT `post title` FROM usaa where `feedback type` = 'Current Departmental Issues' ORDER BY ranking DESC LIMIT 0,1";
MySqlCommand selectCurrentDepartmentBody = new MySqlCommand(selectDepartmentBody, connection3);
string departmentBody;
departmentBody = selectCurrentDepartmentBody.ExecuteScalar().ToString();
string selectDepartmentRanking = "SELECT `Ranking` FROM usaa where `feedback type` = 'Current Departmental Issues' ORDER BY ranking DESC LIMIT 0,1";
MySqlCommand selectCurrentDepartmentRanking = new MySqlCommand(selectDepartmentRanking, connection3);
string departmentRanking;
departmentRanking = selectCurrentDepartmentRanking.ExecuteScalar().ToString();
string selectImplementTitle = "SELECT `post title` FROM usaa where `feedback type` = 'Previous Implementations' ORDER BY ranking DESC LIMIT 0,1";
MySqlCommand selectCurrentImplementCommand = new MySqlCommand(selectImplementTitle, connection3);
string implementTitle;
implementTitle = selectCurrentImplementCommand.ExecuteScalar().ToString();
string selectImplementBody = "SELECT `post title` FROM usaa where `feedback type` = 'Previous Implementations' ORDER BY ranking DESC LIMIT 0,1";
MySqlCommand selectCurrentImplementBody = new MySqlCommand(selectImplementBody, connection3);
string implementBody;
implementBody = selectCurrentImplementBody.ExecuteScalar().ToString();
string selectRanking = "SELECT `Ranking` FROM usaa where `feedback type` = 'Previous Implementations' ORDER BY ranking DESC LIMIT 0,1";
MySqlCommand selectImplementRanking = new MySqlCommand(selectRanking, connection3);
string implementRanking;
implementRanking = selectImplementRanking.ExecuteScalar().ToString();
string selectSuggestTitle = "SELECT `post title` FROM usaa where `feedback type` = 'Departmental Suggestion' ORDER BY ranking DESC LIMIT 0,1";
MySqlCommand selectSuggestCommand = new MySqlCommand(selectSuggestTitle, connection3);
string suggestTitle;
suggestTitle = selectSuggestCommand.ExecuteScalar().ToString();
string selectSuggestBody = "SELECT `post title` FROM usaa where `feedback type` = 'Departmental Suggestion' ORDER BY ranking DESC LIMIT 0,1";
MySqlCommand selectSuggestBodyCommand = new MySqlCommand(selectSuggestBody, connection3);
string suggestBody;
suggestBody = selectSuggestBodyCommand.ExecuteScalar().ToString();
string suggestRanking = "SELECT `Ranking` FROM usaa where `feedback type` = 'Departmental Suggestion' ORDER BY ranking DESC LIMIT 0,1";
MySqlCommand selectSuggestRanking = new MySqlCommand(suggestRanking, connection3);
string suggestTopRanking;
suggestTopRanking = selectSuggestRanking.ExecuteScalar().ToString();
previousImplementationsSheet.Cells[19, 1] = "";
previousImplementationsSheet.Cells[19, 2] = "Previous Implementation Issues";
previousImplementationsSheet.Cells[20, 1] = implementTitle;
previousImplementationsSheet.Cells[20, 2] = implementRanking;
previousImplementationsSheet.Cells[20, 3] = implementBody;
previousImplementationsSheet.Cells[21, 1] = "Bring your own device to work";
previousImplementationsSheet.Cells[21, 2] = "15";
previousImplementationsSheet.Cells[21, 3] = "The implementation of the new security system which has allowed portability to work from home and office. ";
previousImplementationsSheet.Cells[22, 1] = "the new training program ";
previousImplementationsSheet.Cells[22, 2] = "12";
previousImplementationsSheet.Cells[22, 3] = "I like the new training programs we get every month they helping us to learn more. ";
previousImplementationsSheet.Cells[23, 1] = "Ticketing Service ";
previousImplementationsSheet.Cells[23, 2] = "8";
previousImplementationsSheet.Cells[23, 3] = "I think moving to the new software provider for our ticketing services was a great implentation. It has really helped lower the time taken to process clients in order to help them with their current issues";
previousImplementationsSheet.Cells[24, 1] = "Increase days for paternal leave";
previousImplementationsSheet.Cells[24, 2] = "4";
previousImplementationsSheet.Cells[24, 3] = "I think increasing days for increasing paternal leave to 3 weeks boosted the morale of employees as they feel the organization cares about their work life balance.";
departmentalIssuesSheet.Cells[19, 1] = "";
departmentalIssuesSheet.Cells[19, 2] = "Departmental Issues";
departmentalIssuesSheet.Cells[20, 1] = departmentTitle;
departmentalIssuesSheet.Cells[20, 2] = departmentRanking;
departmentalIssuesSheet.Cells[20, 3] = departmentBody;
departmentalIssuesSheet.Cells[21, 1] = "Transition of Database";
departmentalIssuesSheet.Cells[21, 2] = "19";
departmentalIssuesSheet.Cells[21, 3] = "The organization has been using an old aged and database and we need to transition into a newer database for efficient data extraction and cost minimization.";
departmentalIssuesSheet.Cells[22, 1] = "Extended working hours ";
departmentalIssuesSheet.Cells[22, 2] = "14";
departmentalIssuesSheet.Cells[22, 3] = "sometimes we have to stay extra hours to finish a project this has a negative impact on our family life and health. in addition to making us lose motivation. ";
departmentalIssuesSheet.Cells[23, 1] = "Attention to all team members";
departmentalIssuesSheet.Cells[23, 2] = "8";
departmentalIssuesSheet.Cells[23, 3] = "I have been faced in a recurring situation for the last 9 weeks. Every Friday, for the last 9 weeks, I have spent the first 30 minutes of the weekly meeting without the ability to be recognized as a speaker and reflect on my week. The same peers are targeted by management and the same ones receiving benefits, promotions, etc. How can management better recognize the hard work of the greater employees instead of just a specific group?";
departmentalIssuesSheet.Cells[24, 1] = "My voice isn't being heard";
departmentalIssuesSheet.Cells[24, 2] = "6";
departmentalIssuesSheet.Cells[24, 3] = "My managers dismisses any suggestions or feedback I give them for improving workplace productivity. I'm never taken seriously. How can I resolve this?";
departmentalSuggestionsSheet.Cells[19, 1] = "";
departmentalSuggestionsSheet.Cells[19, 2] = "Departmental Suggestions";
departmentalSuggestionsSheet.Cells[20, 1] = suggestTitle;
departmentalSuggestionsSheet.Cells[20, 2] = suggestTopRanking;
departmentalSuggestionsSheet.Cells[20, 3] = suggestBody;
departmentalSuggestionsSheet.Cells[21, 1] = "Bi-Monthly Town Hall with Senior Management";
departmentalSuggestionsSheet.Cells[21, 2] = "17";
departmentalSuggestionsSheet.Cells[21, 3] = "It would be easier to manage time and tasks if senior management routinely informed the department about new integrations and changes to the department.";
departmentalSuggestionsSheet.Cells[22, 1] = "Healthy Eating Incentives";
departmentalSuggestionsSheet.Cells[22, 2] = "14";
departmentalSuggestionsSheet.Cells[22, 3] = "It would be neat if employees received health insurance discounts and other perks for eating healthy. Everyone would benefit.";
departmentalSuggestionsSheet.Cells[23, 1] = "Mentorship Program ";
departmentalSuggestionsSheet.Cells[23, 2] = "9";
departmentalSuggestionsSheet.Cells[23, 3] = "As a senior employee I would like to offer the opportunity to mentor new hires. I think it could be a good idea if we created a mentorship program where senior employees can be available to help new hires transtition succesfully into our department ";
departmentalSuggestionsSheet.Cells[24, 1] = "Casual Fridays ";
departmentalSuggestionsSheet.Cells[24, 2] = "6";
departmentalSuggestionsSheet.Cells[24, 3] = "I think it could be a good idea to implement casual Fridays in our office. I have recently read of the success a lot of companies are having with this implementation and I think it could be a good idea ";
Excel.Range chartRange;
Excel.Range departmentRange;
Excel.Range departmentSuggestRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)previousImplementationsSheet.ChartObjects();
Excel.ChartObjects departmentCharts = (Excel.ChartObjects)departmentalIssuesSheet.ChartObjects();
Excel.ChartObjects departmentSuggestCharts = (Excel.ChartObjects)departmentalSuggestionsSheet.ChartObjects();
Excel.ChartObject myChart = xlCharts.Add(10, 10, 350, 250);
Excel.ChartObject departChart = departmentCharts.Add(10, 10, 350, 250);
Excel.ChartObject departSugChart = departmentSuggestCharts.Add(10, 10, 350, 250);
Excel.Chart chartPage = myChart.Chart;
Excel.Chart departPage = departChart.Chart;
Excel.Chart departSugPage = departSugChart.Chart;
chartRange = previousImplementationsSheet.get_Range("A19", "b24");
departmentRange = departmentalIssuesSheet.get_Range("A19", "b24");
departmentSuggestRange = departmentalSuggestionsSheet.get_Range("A19", "b24");
chartPage.SetSourceData(chartRange, misValue);
departPage.SetSourceData(departmentRange, misValue);
departSugPage.SetSourceData(departmentSuggestRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
departPage.ChartType = Excel.XlChartType.xlColumnClustered;
departSugPage.ChartType = Excel.XlChartType.xlColumnClustered;
Excel.Application excelApp = new Excel.Application();
xlWorkBook.SaveAs("ManagerReport.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
// replace with presenters excel location
//System.Diagnostics.Process.Start(@"C:\Users\russe\Documents\ManagerReport.xls");
}
static void Main(string[] args)
{
RemoteWebDriver web = NewMethod2();
web.Navigate().GoToUrl("http://google.pl/");
ExcelApp excel = new ExcelApp();
excel.Visible = true;
Workbook wb = excel.Workbooks.Open("C:/Users/Bartosz/Desktop/ING.xlsx");
Worksheet ws = wb.ActiveSheet;
int wiersz = 1;
int kolumna = 1;
int valueOFcities = 0;
while (excel.Cells[wiersz, kolumna].Value != null)
{
valueOFcities++;
wiersz++;
}
string[] cities = new string[valueOFcities];
int[] temp = new int[valueOFcities];
for (int i = 0; i < valueOFcities; i++)
{
web.FindElementById("lst-ib").Clear();
string miasto = excel.Cells[i + 1, 1].Value;
web.FindElementById("lst-ib").SendKeys("pogoda " + miasto + Keys.Enter);
cities[i] = miasto;
//temperatura
string temperatura = web.FindElementById("wob_tm").Text;
temp[i] = int.Parse(temperatura);
excel.Cells[i + 1, 2].Value = temperatura;
//data
string data;
DateTime dat = DateTime.Now;
data = dat.ToString();
excel.Cells[i + 1, 4].Value = data;
// pogoda
string pogoda;
pogoda = web.FindElementById("wob_dc").Text;
excel.Cells[i + 1, 3].Value = pogoda;
}
int maxtemp = temp.Max();
int mintemp = temp.Min();
// wybor miast
for (int i = 0; i < valueOFcities; i++)
{
if (temp[i] == maxtemp)
{
string cityofmaxtemp = cities[i];
excel.Cells[4, 8].Value = String.Format("najgorętszym miastem jest: " + cityofmaxtemp);
}
if (temp[i] == mintemp)
{
string cityofmintemp = cities[i];
excel.Cells[5, 8].Value = String.Format("najchłodniejszym miastem jest: " + cityofmintemp);
}
}
//rysowanie wykresu
object misValue = System.Reflection.Missing.Value;
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)ws.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(200, 200, 200, 200);
Excel.Chart chartPage = myChart.Chart;
chartRange = ws.get_Range("A1", "B13");
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
releaseObject(ws);
releaseObject(wb);
releaseObject(excel);
web.Quit();
//wb.Save();
//excel.Quit();
}
private void button1_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//add data
xlWorkSheet.Cells[1, 1] = "";
xlWorkSheet.Cells[1, 2] = "Student1";
xlWorkSheet.Cells[1, 3] = "Student2";
xlWorkSheet.Cells[1, 4] = "Student3";
xlWorkSheet.Cells[2, 1] = "Term1";
xlWorkSheet.Cells[2, 2] = "80";
xlWorkSheet.Cells[2, 3] = "65";
xlWorkSheet.Cells[2, 4] = "45";
xlWorkSheet.Cells[3, 1] = "Term2";
xlWorkSheet.Cells[3, 2] = "78";
xlWorkSheet.Cells[3, 3] = "72";
xlWorkSheet.Cells[3, 4] = "60";
xlWorkSheet.Cells[4, 1] = "Term3";
xlWorkSheet.Cells[4, 2] = "82";
xlWorkSheet.Cells[4, 3] = "80";
xlWorkSheet.Cells[4, 4] = "65";
xlWorkSheet.Cells[5, 1] = "Term4";
xlWorkSheet.Cells[5, 2] = "75";
xlWorkSheet.Cells[5, 3] = "82";
xlWorkSheet.Cells[5, 4] = "68";
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", "C12");
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlConeCol; //xlCylinderCol;//xlLine;//xlColumnClustered;
//export chart as picture file
chartPage.Export(@"H:\img\excel_chart_export.png", "PNG", misValue);
xlWorkBook.SaveAs(@"H:\img\csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
}
private void btnChart_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//add data
xlWorkSheet.Cells[1, 2] = "Overview";
xlWorkSheet.Cells[1, 3] = "Time";
xlWorkSheet.Cells[2, 1] = "Planned \n" + toTime(totalPlanned).ToString();
xlWorkSheet.Cells[2, 2] = totalPlanned;
xlWorkSheet.Cells[3, 1] = "Not Planned \n" + toTime(totalNotPlanned).ToString();
xlWorkSheet.Cells[3, 2] = totalNotPlanned;
xlWorkSheet.Cells[4, 1] = "Auto Mode \n" + toTime(totalAutoMode).ToString();
xlWorkSheet.Cells[4, 2] = totalAutoMode;
xlWorkSheet.Cells[4, 3] = toTime(totalAutoMode).ToString();
//xlWorkBook.SaveAs("csharp-Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 80, 300, 250);
Excel.Chart chartPage = myChart.Chart;
chartRange = xlWorkSheet.get_Range("A1", "c4");
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlPie;
//chartPage.SeriesCollection(2).Format.Fill.ForeColor.RGB = System.Drawing.Color.Red.ToArgb();
//chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
// if (!File.Exists(@"C:\excel_chart_export.bmp"))
//{
chartPage.Export(@"C:\excel_chart_export.bmp", "BMP", misValue);
//}
//export chart as picture file
//xlWorkBook.SaveAs("csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(false, misValue, misValue);
xlApp.Quit();
this.Hide();
Image img;
using (var bmpTemp = new Bitmap(@"C:\excel_chart_export.bmp"))
{
img = new Bitmap(bmpTemp);
}
using (Form form = new Form())
{
form.StartPosition = FormStartPosition.CenterScreen;
form.Size = new Size(510, 460);
PictureBox pb = new PictureBox();
pb.Dock = DockStyle.Fill;
pb.Image = img;
form.Controls.Add(pb);
form.ShowDialog();
}
this.Show();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
releaseObject(chartPage);
File.Delete(@"C:\excel_chart_export.bmp");
}
/// <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_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 = 11;
stat_table.Range.Font.Name = "Times New Roman";
//Индексация столбцов и строк в Word начинается с 1,1
for (int row = 1; row <= table.Rows.Count; row++)
{
for (int col = 1; col <= table.Columns.Count; col++)
{
stat_table.Cell(row, col).Range.Text
= table.Rows[row - 1][col - 1].ToString();
}
}
document.Paragraphs.Add();
document.Paragraphs.Add();
//Парадграф с фиксациейц даты создания документа
word.Paragraph Footparg = document.Paragraphs.Add();
Footparg.Range.Text =
string.Format("Дата создания \t\t\t{0}",
DateTime.Now.ToString("dd.MM.yyyy"));
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
}
finally
{
switch (format)
{
case Document_Format.Word:
//Сохранение документа с названием из метода,
//и в формате doc
document.SaveAs2(string.Format("{0}\\{1}", Environment.CurrentDirectory, name),
word.WdSaveFormat.wdFormatDocument);
break;
case Document_Format.PDF:
//Сохранение документа в формате PDF
document.SaveAs2(string.Format("{0}\\{1}", Environment.CurrentDirectory, name),
word.WdSaveFormat.wdFormatPDF);
break;
}
//Закрываем документ
document.Close();
//Выходим из процесса с его закрытием
application.Quit();
}
break;
case Document_Format.Excel:
//Создание процесса Excel
excel.Application application_ex
= new excel.Application();
//Создание книги
excel.Workbook workbook
= application_ex.Workbooks.Add();
//Создание страницы
excel.Worksheet worksheet
= (excel.Worksheet)workbook.ActiveSheet;
try
{
switch (type)
{
case Document_Type.Report:
//Название страницы
worksheet.Name = "Отчёт";
for (int row = 0; row < table.Rows.Count; row++)
{
for (int col = 0; col < table.Columns.Count; col++)
{
//ЗАнесение данных в ячейку
worksheet.Cells[row + 1][col + 1]
= table.Rows[row][col].ToString();
}
}
//Указание диапазона работы с ячеёками листа
excel.Range border
//Начало диапазона
= worksheet.Range[worksheet.Cells[1, 1],
//Динамический конец диапазона в зависимости от
//выдодимых данных
worksheet.Cells[table.Rows.Count + 1]
[table.Columns.Count + 1]];
//Стиль линий границ ячеек
border.Borders.LineStyle = excel.XlLineStyle.xlContinuous;
//Выравнивание во высоте
border.VerticalAlignment = excel.XlHAlign.xlHAlignCenter;
//Выравнивание по ширине
border.HorizontalAlignment = excel.XlHAlign.xlHAlignCenter;
//Внесение даты создания документа
worksheet.Cells[table.Rows.Count + 3][2]
= string.Format("Дата создания {0}",
DateTime.Now.ToString());
//Объединение ячеек
worksheet.Range[worksheet.Cells[table.Rows.Count + 3, 2],
worksheet.Cells[table.Rows.Count + 2,
table.Columns.Count + 2]].Merge();
break;
case Document_Type.Statistic:
worksheet.Name = "Статистический отчёт";
for (int row = 0; row < table.Rows.Count; row++)
{
for (int col = 0; col < table.Columns.Count; col++)
{
worksheet.Cells[row + 1][col + 1]
= table.Rows[row][col].ToString();
}
}
excel.Range border1
= worksheet.Range[worksheet.Cells[1, 1],
worksheet.Cells[table.Rows.Count + 1]
[table.Columns.Count + 1]];
border1.Borders.LineStyle
= excel.XlLineStyle.xlContinuous;
border1.VerticalAlignment
= excel.XlHAlign.xlHAlignCenter;
border1.HorizontalAlignment
= excel.XlHAlign.xlHAlignCenter;
worksheet.Cells[table.Rows.Count + 3][2]
= string.Format("Дата создания {0}",
DateTime.Now.ToString());
worksheet.Range[worksheet.Cells[table.Rows.Count + 3, 2],
worksheet.Cells[table.Rows.Count + 2,
table.Columns.Count + 2]].Merge();
//Класс области графиков
excel.ChartObjects chartObjects
= (excel.ChartObjects)worksheet.ChartObjects(
Type.Missing);
//Область размещения графиков: отступы слева сверху,
//размер ширина и высота
excel.ChartObject chartObject
= chartObjects.Add(300, 50, 250, 250);
//Объявление области графика
excel.Chart chart = chartObject.Chart;
//Объявление колекции построений графиков
excel.SeriesCollection seriesCollection
= (excel.SeriesCollection)chart.SeriesCollection(
Type.Missing);
//Объявление посторения графика
excel.Series series = seriesCollection.NewSeries();
//Тип графика
chart.ChartType = excel.XlChartType.xl3DColumn;
//Диапазон значений по оси X
series.XValues =
worksheet.get_Range("B2", "B" + table.Rows.Count + 1);
//Диапазон значений по оси Y
series.Values =
worksheet.get_Range("C2", "C" + table.Rows.Count + 1);
break;
}
}
catch
{
}
finally
{
//Сохранение книги
workbook.SaveAs(string.Format("{0}\\{1}", Environment.CurrentDirectory, name), application_ex.DefaultSaveFormat);
//Закрытие книги
workbook.Close();
//Завершение процесса
application_ex.Quit();
}
break;
}
break;
case false:
System.Windows.Forms.MessageBox.Show
("Введите название документп");
break;
}
}
private void ButtonGenerateReport_Click(object sender, System.EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet previousImplementationsSheet;
Excel.Worksheet departmentalIssuesSheet;
Excel.Worksheet departmentalSuggestionsSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
previousImplementationsSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
departmentalIssuesSheet = (Excel.Worksheet)xlWorkBook.Worksheets.Add(misValue, misValue, misValue, misValue);
departmentalSuggestionsSheet = (Excel.Worksheet)xlWorkBook.Worksheets.Add(misValue, misValue, misValue, misValue);
//add data
previousImplementationsSheet.Cells[1, 1] = "";
previousImplementationsSheet.Cells[1, 2] = "Previous Implementation Issues";
previousImplementationsSheet.Cells[2, 1] = "Issue 1";
previousImplementationsSheet.Cells[2, 2] = "100";
previousImplementationsSheet.Cells[2, 3] = "Full Issue 1 Information (body)";
previousImplementationsSheet.Cells[3, 1] = "Issue 2";
previousImplementationsSheet.Cells[3, 2] = "88";
previousImplementationsSheet.Cells[3, 3] = "Full Issue 2 Information (body)";
previousImplementationsSheet.Cells[4, 1] = "Issue 3";
previousImplementationsSheet.Cells[4, 2] = "44";
previousImplementationsSheet.Cells[4, 3] = "Full Issue 3 Information (body)";
previousImplementationsSheet.Cells[5, 1] = "Issue 4";
previousImplementationsSheet.Cells[5, 2] = "30";
previousImplementationsSheet.Cells[5, 3] = "Full Issue 4 Information (body)";
previousImplementationsSheet.Cells[6, 1] = "Issue 5";
previousImplementationsSheet.Cells[6, 2] = "12";
previousImplementationsSheet.Cells[6, 3] = "Full Issue 5 Information (body)";
departmentalIssuesSheet.Cells[1, 1] = "";
departmentalIssuesSheet.Cells[1, 2] = "Departmental Issues";
departmentalIssuesSheet.Cells[2, 1] = "Issue 1";
departmentalIssuesSheet.Cells[2, 2] = "56";
departmentalIssuesSheet.Cells[2, 3] = "Full Issue 1 Information (body)";
departmentalIssuesSheet.Cells[3, 1] = "Issue 2";
departmentalIssuesSheet.Cells[3, 2] = "32";
departmentalIssuesSheet.Cells[3, 3] = "Full Issue 2 Information (body)";
departmentalIssuesSheet.Cells[4, 1] = "Issue 3";
departmentalIssuesSheet.Cells[4, 2] = "10";
departmentalIssuesSheet.Cells[4, 3] = "Full Issue 3 Information (body)";
departmentalIssuesSheet.Cells[5, 1] = "Issue 4";
departmentalIssuesSheet.Cells[5, 2] = "8";
departmentalIssuesSheet.Cells[5, 3] = "Full Issue 4 Information (body)";
departmentalIssuesSheet.Cells[6, 1] = "Issue 5";
departmentalIssuesSheet.Cells[6, 2] = "4";
departmentalIssuesSheet.Cells[6, 3] = "Full Issue 5 Information (body)";
departmentalSuggestionsSheet.Cells[1, 1] = "";
departmentalSuggestionsSheet.Cells[1, 2] = "Departmental Suggestions";
departmentalSuggestionsSheet.Cells[2, 1] = "Issue 1";
departmentalSuggestionsSheet.Cells[2, 2] = "89";
departmentalSuggestionsSheet.Cells[2, 3] = "Full Issue 1 Information (body)";
departmentalSuggestionsSheet.Cells[3, 1] = "Issue 2";
departmentalSuggestionsSheet.Cells[3, 2] = "85";
departmentalSuggestionsSheet.Cells[3, 3] = "Full Issue 2 Information (body)";
departmentalSuggestionsSheet.Cells[4, 1] = "Issue 3";
departmentalSuggestionsSheet.Cells[4, 2] = "70";
departmentalSuggestionsSheet.Cells[4, 3] = "Full Issue 3 Information (body)";
departmentalSuggestionsSheet.Cells[5, 1] = "Issue 4";
departmentalSuggestionsSheet.Cells[5, 2] = "36";
departmentalSuggestionsSheet.Cells[5, 3] = "Full Issue 4 Information (body)";
departmentalSuggestionsSheet.Cells[6, 1] = "Issue 5";
departmentalSuggestionsSheet.Cells[6, 2] = "22";
departmentalSuggestionsSheet.Cells[6, 3] = "Full Issue 1 Information (body)";
Excel.Range chartRange;
Excel.Range departmentRange;
Excel.Range departmentSuggestRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)previousImplementationsSheet.ChartObjects();
Excel.ChartObjects departmentCharts = (Excel.ChartObjects)departmentalIssuesSheet.ChartObjects();
Excel.ChartObjects departmentSuggestCharts = (Excel.ChartObjects)departmentalSuggestionsSheet.ChartObjects();
Excel.ChartObject myChart = xlCharts.Add(10, 120, 350, 250);
Excel.ChartObject departChart = departmentCharts.Add(10, 120, 350, 250);
Excel.ChartObject departSugChart = departmentSuggestCharts.Add(10, 120, 350, 250);
Excel.Chart chartPage = myChart.Chart;
Excel.Chart departPage = departChart.Chart;
Excel.Chart departSugPage = departSugChart.Chart;
chartRange = previousImplementationsSheet.get_Range("A1", "b6");
departmentRange = departmentalIssuesSheet.get_Range("A1", "b6");
departmentSuggestRange = departmentalSuggestionsSheet.get_Range("A1", "b6");
chartPage.SetSourceData(chartRange, misValue);
departPage.SetSourceData(departmentRange, misValue);
departSugPage.SetSourceData(departmentSuggestRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
departPage.ChartType = Excel.XlChartType.xlColumnClustered;
departSugPage.ChartType = Excel.XlChartType.xlColumnClustered;
Excel.Application excelApp = new Excel.Application();
xlWorkBook.SaveAs("ManagerReport.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
//System.Diagnostics.Process.Start(@"C:\Users\russe\Documents\ManagerReport.xls");
}
public static void excele_grafik_ekle(string shift_str, string operator_str, string width_str, string roll_number_str, string alloy_str, string measurement_id, string prod_line_str)
{
yazici.durum_yazici("excele_grafik_ekle started");
Excel.Range range = ExcelSayfa.UsedRange;
satir = range.Rows.Count;
double chart_position_left = ExcelSayfa.get_Range("A:G", System.Type.Missing).Width;
double chart_position_up = ExcelSayfa.get_Range("A1:A6", System.Type.Missing).Height;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)ExcelSayfa.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(chart_position_left, chart_position_up, 600, 150);
Excel.Chart chartPage = myChart.Chart;
Excel.Range chartRange = ExcelSayfa.get_Range("B1", ("B" + Convert.ToString(satir)));
chartPage.SetSourceData(chartRange, Missing);
var series = (Excel.Series)chartPage.SeriesCollection(1);
series.XValues = ExcelSayfa.get_Range("A2", ("A" + Convert.ToString(satir)));
chartPage.ChartType = Excel.XlChartType.xlLine;
//chartPage.Legend.
ExcelSayfa.Cells[2, 4] = text_file_list[18];
ExcelSayfa.Cells[3, 4] = text_file_list[19];
ExcelSayfa.Cells[4, 4] = text_file_list[20];
ExcelSayfa.Cells[5, 4] = text_file_list[21];
ExcelSayfa.Cells[6, 4] = text_file_list[22];
ExcelSayfa.Cells[7, 4] = text_file_list[23];
ExcelSayfa.Cells[8, 4] = text_file_list[53];
ExcelSayfa.Cells[9, 4] = text_file_list[54];
ExcelSayfa.Cells[2, 5].NumberFormat = "@";
ExcelSayfa.Cells[2, 5] = measurement_id;
ExcelSayfa.Cells[2, 5].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ExcelSayfa.Cells[3, 5].NumberFormat = "@";
ExcelSayfa.Cells[3, 5] = operator_str;
ExcelSayfa.Cells[3, 5].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
DateTime _time = DateTime.Now; // Use current time
string _tarih = _time.ToString(format, CultureInfo.InvariantCulture);
ExcelSayfa.Cells[4, 5].NumberFormat = "@";
ExcelSayfa.Cells[4, 5] = _tarih;
ExcelSayfa.Cells[4, 5].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ExcelSayfa.Cells[5, 5].NumberFormat = "@";
ExcelSayfa.Cells[5, 5] = width_str;
ExcelSayfa.Cells[5, 5].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ExcelSayfa.Cells[6, 5].NumberFormat = "@";
ExcelSayfa.Cells[6, 5] = roll_number_str;
ExcelSayfa.Cells[6, 5].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ExcelSayfa.Cells[7, 5].NumberFormat = "@";
ExcelSayfa.Cells[7, 5] = alloy_str;
ExcelSayfa.Cells[7, 5].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ExcelSayfa.Cells[8, 5].NumberFormat = "@";
ExcelSayfa.Cells[8, 5] = shift_str;
ExcelSayfa.Cells[8, 5].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
ExcelSayfa.Cells[9, 5].NumberFormat = "@";
ExcelSayfa.Cells[9, 5] = prod_line_str;
ExcelSayfa.Cells[9, 5].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
range = ExcelSayfa.get_Range("A1", "H7");
range.EntireColumn.AutoFit();
ExcelUygulama.Visible = true;
try
{
//excel_kullanimda_mi = 0;
ExcelProje.SaveAs(dosya_yolu_full, Excel.XlFileFormat.xlWorkbookDefault, Missing, Missing, false, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlLocalSessionChanges, Missing, Missing);
}
catch (Exception ex)
{
string msg = text_file_list[24] + "\n\r\n\r" + text_file_list[16] + "\n\r\n\r" + text_file_list[17] + "\n\r\n\r" + ex.Message;
MessageBox.Show(msg);
yazici.durum_yazici(msg);
}
yazici.durum_yazici("excele_grafik_ekle ended.");
}