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);
}
public void AddInfo(FunctionTableItem[] fun, ValueIs value = ValueIs.Magnitude, bool addChart = false)
{
Excel.Series series;
Excel.Range temp;
range = range.get_Resize(2, fun.Length);
range.set_Value(Missing.Value, GetArray(fun, value));
if (addChart)
{
LastIndex += chartHeight + 10;
chartObject = charts.Add(400, LastIndex, chartWidth, chartHeight);
}
currentChart = chartObject.Chart;
series = ((Excel.SeriesCollection) currentChart.SeriesCollection(Type.Missing)).NewSeries();
temp = range.get_Resize(1, fun.Length);
series.Values = temp;
series.XValues = temp.get_Offset(1, 0);
currentChart.ChartType = Excel.XlChartType.xlXYScatterSmoothNoMarkers;
range = range.get_Offset(3, 0);
}
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 button2_Click(object sender, EventArgs e)
{
try
{
string fileName = Dir + "\\" + string.Format("{0}.xls", Guid.NewGuid()).Replace(" ", "_");
//if (File.Exists(fileName))
//{
// try
// {
// File.Delete(fileName);
// }
// catch (Exception)
// {
// MessageBox.Show(string.Format("В настоящий момент используется файл:\r\n{0}\r\nДля создания договора закройте пожалуйста файл.", fileName),
// "Невозможно создать договор", MessageBoxButtons.OK, MessageBoxIcon.Warning);
// return;
// }
//}
Excel.Application xla = new Excel.Application();
xla.Visible = true;
Excel.Workbook wb = xla.Workbooks.Add(Excel.XlSheetType.xlWorksheet);
Excel.Worksheet ws = (Excel.Worksheet)xla.ActiveSheet;
// Now create the chart.
Excel.ChartObjects chartObjs = (Excel.ChartObjects)ws.ChartObjects();
Excel.ChartObject chartObj = chartObjs.Add(512, 80, 300, 300);
Excel.Chart xlChart = chartObj.Chart;
xlChart.ChartType = Excel.XlChartType.xlBarClustered;
// generate some random data
dateTimePicker1.Value = dateTimePicker1.Value.Date.AddDays(1 - dateTimePicker1.Value.Day);
dateTimePicker2.Value = dateTimePicker2.Value.Date.AddMonths(1).AddDays(-dateTimePicker2.Value.Day);
ws.Cells[2, 2] = string.Format("Результативность работы фирмы за период с {0} по {1}", dateTimePicker1.Value, dateTimePicker2.Value);
ws.Cells[4, 3] = "Период";
ws.Cells[4, 4] = "Количество сделок";
ws.Cells[4, 5] = "Сумма";
int row = 5;
for (var dateFrom = dateTimePicker1.Value; dateFrom <= dateTimePicker2.Value; dateFrom = dateFrom.AddMonths(1))
{
var dateTo = dateFrom.AddMonths(1).AddDays(-1);
var trips = dbj.Jurnal.Where(t => t.data >= dateFrom && t.data <= dateTo);
foreach (var i in trips)
{
ws.Cells[row, 3] = GetMonth(dateFrom.Month);
ws.Cells[row, 4] = trips.Count();
ws.Cells[row, 5] = i.summa;
}
row++;
}
Excel.Range xValues = ws.Range["C5", "C" + (row - 1)];
Excel.Range values = ws.Range["E5", "E" + (row - 1)];
Excel.SeriesCollection seriesCollection = xlChart.SeriesCollection();
Excel.Series series1 = seriesCollection.NewSeries();
series1.XValues = xValues;
series1.Values = values;
//xla.ActiveChart.HasTitle = true;
//xla.ActiveChart.ChartTitle.Text = "Тестовая диаграмма";
//xla.ActiveChart.ChartTitle.Font.Size = 13;
//xla.ActiveChart.ChartTitle.Font.Color = 254;
//xla.ActiveChart.ChartTitle.Shadow = true;
//xla.ActiveChart.ChartTitle.Border.LineStyle = Excel.Constants.xlSolid;
}
catch { MessageBox.Show("Ошибка Excel не найден"); }
}
internal void newChart(int n)
{
Excel.Range chartText1 = BottomCell().get_Offset(2, 0).get_Resize(1, 3);
Excel.Range chartText2 = chartText1.get_Offset(0, 6).get_Resize(1, 8);
((Excel.Range)chartText1[1, 1]).Value2 = "The plots below show the actual energy consumption versus the independent variables for the model year.";
((Excel.Range)chartText2[1, 1]).Value2 = "The line labeled \"Actuals\" in the plot below has not been adjusted. This is the original data entered by the user. The line labeled \"Model\" is the predicted energy consumption using the model selected above.";
chartText1.EntireRow.RowHeight = 75;
chartText1.EntireRow.Font.Bold = true;
chartText1.EntireRow.WrapText = true;
chartText1.Merge();
chartText2.Merge();
Excel.Range start = BottomCell().get_Offset(1, 0);
double topleft;
if (!double.TryParse(start.Top.ToString(), out topleft))
{
topleft = 0;
}
start.EntireRow.RowHeight = Utilities.Constants.CHART_HEIGHT * 1.1;
ArrayList lst = this.Source.Models.ModelSort();
string[] variables = new string[] {};
int longest = 0;
foreach (Utilities.Model model in lst)
{
if (model.VariableNames.Length > longest)
{
longest = model.VariableNames.Length;
variables = model.VariableNames;
}
}
object[] sources = new object[variables.Length];
int k = 0;
for (int i = 0; i < variables.Length; i++)
{
double[] temp = new double[Source.knownXs.Rows.Count];
foreach (System.Data.DataRow row in Source.knownXs.Rows)
{
temp[k] = Convert.ToDouble(row.ItemArray[i]);
k++;
}
k = 0;
sources[i] = temp;
}
for (int i = 0; i < variables.Length; i++)
{
if (i.Equals(0))
{
Excel.ChartObject CO = ((Excel.ChartObjects)WS.ChartObjects(System.Type.Missing))
.Add(10, topleft, Utilities.Constants.CHART_WIDTH, Utilities.Constants.CHART_HEIGHT);
CO.Chart.HasLegend = false;
CO.Chart.ChartType = Excel.XlChartType.xlXYScatter;
CO.Chart.HasTitle = false;
Excel.Series newSeries = ((Excel.SeriesCollection)CO.Chart.SeriesCollection(System.Type.Missing)).NewSeries();
newSeries.Values = Source.Ys;
newSeries.XValues = sources[i];
Excel.Axis COYaxis = (Excel.Axis)CO.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
COYaxis.TickLabels.NumberFormat = "###,##0";
COYaxis.HasTitle = true;
COYaxis.AxisTitle.Text = WS.Name;
Excel.Axis COXaxis = (Excel.Axis)CO.Chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
COXaxis.TickLabels.NumberFormat = "###,##0";
COXaxis.HasTitle = true;
COXaxis.AxisTitle.Text = variables[i];
}
else
{
Excel.Range afterFirst = BottomCell().get_Offset(1, 0);
afterFirst.EntireRow.RowHeight = Utilities.Constants.CHART_HEIGHT * 1.1;
double topleftinner;
if (!double.TryParse(afterFirst.Top.ToString(), out topleftinner))
{
topleftinner = 0;
}
Excel.ChartObject CO = ((Excel.ChartObjects)WS.ChartObjects(System.Type.Missing))
.Add(10, topleftinner, Utilities.Constants.CHART_WIDTH, Utilities.Constants.CHART_HEIGHT);
CO.Chart.HasLegend = false;
CO.Chart.ChartType = Excel.XlChartType.xlXYScatter;
CO.Chart.HasTitle = false;
Excel.Series newSeries = ((Excel.SeriesCollection)CO.Chart.SeriesCollection(System.Type.Missing)).NewSeries();
newSeries.Values = this.Source.Ys;
newSeries.XValues = sources[i];
Excel.Axis COYaxis = (Excel.Axis)CO.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
COYaxis.TickLabels.NumberFormat = "###,##0";
COYaxis.HasTitle = true;
COYaxis.AxisTitle.Text = WS.Name;
Excel.Axis COXaxis = (Excel.Axis)CO.Chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
COXaxis.TickLabels.NumberFormat = "###,##0";
COXaxis.HasTitle = true;
COXaxis.AxisTitle.Text = variables[i];
}
}
for (int j = 0; j < Math.Min(n, lst.Count); j++)
{
Utilities.Model mdl = this.Source.Models.Item(j);
Excel.ChartObject CO = ((Excel.ChartObjects)WS.ChartObjects(System.Type.Missing))
.Add(400, topleft, Utilities.Constants.CHART_WIDTH, Utilities.Constants.CHART_HEIGHT);
CO.Placement = Excel.XlPlacement.xlMove;
CO.Name = "Model " + mdl.ModelNumber;
CO.Visible = false;
CO.Chart.ChartType = Excel.XlChartType.xlLineMarkers;
CO.Chart.ChartStyle = 5;
Excel.Axis CharObj2Yaxis = (Excel.Axis)CO.Chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
CharObj2Yaxis.TickLabels.NumberFormat = "###,##0";
CharObj2Yaxis.HasTitle = true;
CharObj2Yaxis.AxisTitle.Text = "Input Interval";
Excel.Axis CharObj2Xaxis = (Excel.Axis)CO.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
CharObj2Xaxis.TickLabels.NumberFormat = "###,##0";
CharObj2Xaxis.HasTitle = true;
CharObj2Xaxis.AxisTitle.Text = WS.Name;
//Modified by suman: TFS ticket :69333
Excel.Series newSeries = ((Excel.SeriesCollection)CO.Chart.SeriesCollection(System.Type.Missing)).NewSeries();
newSeries.Values = this.Source.Ys;
newSeries.Name = "Actuals";
Excel.Series modelSeries = ((Excel.SeriesCollection)CO.Chart.SeriesCollection(System.Type.Missing)).NewSeries();
modelSeries.Values = mdl.PredictedYs();
modelSeries.Name = "Model " + mdl.ModelNumber;
newSeries.Format.Line.ForeColor.RGB = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DarkRed);// 0x4177B8;//12089153; // Calculated from color calculator
newSeries.MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleSquare;
newSeries.MarkerBackgroundColorIndex = (Microsoft.Office.Interop.Excel.XlColorIndex) 9;
newSeries.Format.Line.Transparency = 1.0f;
if (mdl == this.Source.BestModel())
{
CO.Visible = true;
}
CO.Chart.AutoScaling = true;
CO.Chart.Refresh();
CO.Chart.HasTitle = true;
CO.Chart.ChartTitle.Text = "Comparison of Actual and Model Data";
CO.Chart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionTop;
}
}
// データテーブルをエクセルへエクスポート（箱ヒゲ図付き）
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;
}
/// <summary>
/// Handles the Click event of the btnExcelExport control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="EventArgs"/> instance containing the event data.</param>
private void btnExcelExport_Click(object sender, EventArgs e)
{
List <ExcelData> excelList = new List <ExcelData>(); //store the list of excel data in here
try
{
Byte[] data = ObjectToByteArray(new ExcelData());
stream = server.GetStream();
stream.Write(data, 0, data.Length);
List <byte[]> listObject = new List <byte[]>();
byte[] bytes = new byte[8192];
byte[] fullObjectBytes;
// Loop to receive all the data sent by the client.
stream.Read(bytes, 0, bytes.Length);
listObject.Add(bytes);
var bformatter = new BinaryFormatter();
fullObjectBytes = bytes;
Stream fullObjectStream = new MemoryStream(fullObjectBytes);
object objFromServer = bformatter.Deserialize(fullObjectStream);
Type objType = objFromServer.GetType();
if (objType == typeof(List <ExcelData>))
{
excelList = (List <ExcelData>)objFromServer;
}
}
catch (ArgumentNullException ex)
{
Console.WriteLine("ArgumentNullException: {0}", ex);
stream.Close();
}
catch (SocketException ex)
{
Console.WriteLine("SocketException: {0}", ex);
stream.Close();
}
catch (SerializationException ex)
{
Console.WriteLine("SerializationException: {0}", ex);
stream.Close();
}
catch (IOException io)
{
MessageBox.Show("IOException: " + io.Message);
stream.Close();
this.Close();
}
BinaryFormatter formatter = new BinaryFormatter();
object missing = Type.Missing;
Excel.Application oXL = null;
Excel.Workbooks oWBs = null;
Excel.Workbook oWB = null;
Excel.Worksheet oSheet = null;
Excel.Range oCells = null;
Excel.Range chartRange = null; //range for the chart
Excel.ChartObjects xlCharts = null;
Excel.ChartObject myChart = null;
Excel.Chart chartPage = null;
try
{
// Create an instance of Microsoft Excel and make it invisible.
oXL = new Excel.Application();
oXL.Visible = false;
// Create a new Workbook.
oWBs = oXL.Workbooks;
oWB = oWBs.Add(missing);
// Get the active Worksheet and set its name.
oSheet = oWB.ActiveSheet as Excel.Worksheet;
oSheet.Name = "Question Report";
// Set the column headers
oSheet.Cells.EntireColumn.ColumnWidth = 30;
oCells = oSheet.Cells;
oCells[1, 1] = "Question Number";
oCells[1, 2] = "Question";
oCells[1, 3] = "Average Completion Time";
oCells[1, 4] = "Percent Correct Answers";
int count = 1;
foreach (ExcelData ed in excelList)
{ //set data for the cells
count++;
oCells[count, 1] = ed.questionNumber;
oCells[count, 2] = ed.questionText;
oCells[count, 3] = ed.avgTime;
oCells[count, 4] = ed.percentCorrect;
}
xlCharts = (Excel.ChartObjects)oSheet.ChartObjects(Type.Missing); //create the chart
myChart = (Excel.ChartObject)xlCharts.Add(700, 10, 300, 250); //where to make the chart
chartPage = myChart.Chart;
chartRange = oSheet.get_Range("A1", ("C" + Convert.ToString(count - 1))); //set the range for the chart
chartPage.SetSourceData(chartRange, Excel.XlRowCol.xlColumns); //set source data for chart
chartPage.ChartType = Excel.XlChartType.xlColumnClustered; //set type
chartPage.HasLegend = false; //no legend
// Save the workbook as a xlsx file and close it
string fileName = Path.GetDirectoryName(
Assembly.GetExecutingAssembly().Location) + "\\QuestionReport.xlsx";
oWB.SaveAs(fileName, Excel.XlFileFormat.xlOpenXMLWorkbook,
missing, missing, missing, missing,
Excel.XlSaveAsAccessMode.xlNoChange,
missing, missing, missing, missing, missing);
oWB.Close(missing, missing, missing);
// Quit the Excel application
oXL.UserControl = true;
oXL.Quit();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
// Clean up the unmanaged Excel COM resources
if (chartRange != null)
{
Marshal.FinalReleaseComObject(chartRange);
chartRange = null;
}
if (xlCharts != null)
{
Marshal.FinalReleaseComObject(xlCharts);
xlCharts = null;
}
if (myChart != null)
{
Marshal.FinalReleaseComObject(myChart);
myChart = null;
}
if (chartPage != null)
{
Marshal.FinalReleaseComObject(chartPage);
chartPage = null;
}
if (oCells != null)
{
Marshal.FinalReleaseComObject(oCells);
oCells = null;
}
if (oSheet != null)
{
Marshal.FinalReleaseComObject(oSheet);
oSheet = null;
}
if (oWB != null)
{
Marshal.FinalReleaseComObject(oWB);
oWB = null;
}
if (oWBs != null)
{
Marshal.FinalReleaseComObject(oWBs);
oWBs = null;
}
if (oXL != null)
{
Marshal.FinalReleaseComObject(oXL);
oXL = null;
}
}
}
public void exportarTablaExponencial(Fila[] tabla, double mediaObservada, double desvObservada, int cantNum, double lambda, int intervalos)
{
try
{
double cantNumeros = Convert.ToDouble(cantNum);
double chi = 0.0;
double sumaChi = 0.0;
SetNewCurrentCulture();
//Crea el titulo
xlWorkSheet.Range["A3:F3"].Merge();
xlWorkSheet.Range["A3:F3"].Font.Size = 20;
xlWorkSheet.Range["A3:F3"].Value = "Distribución Exponencial Negativa";
xlWorkSheet.Range["A3:M4"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Range["A3:M4"].Font.Bold = true;
xlWorkSheet.Range["H5:I5"].Font.Bold = true;
xlWorkSheet.Range["H5:I5"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
xlWorkSheet.Range["A5:A" + (5 + intervalos)].EntireColumn.NumberFormat = "00.0000";
xlWorkSheet.Range["B5:B" + (5 + intervalos)].EntireColumn.NumberFormat = "00.0000";
xlWorkSheet.Range["C5:C" + (5 + intervalos)].EntireColumn.NumberFormat = "0.000";
xlWorkSheet.Range["D5:D" + (5 + intervalos)].EntireColumn.NumberFormat = "0";
xlWorkSheet.Range["E5:E" + (5 + intervalos)].EntireColumn.NumberFormat = "00.0000";
xlWorkSheet.Range["F5:F" + (5 + intervalos)].EntireColumn.NumberFormat = "00.0000";
//Crea las cabeceras
xlWorkSheet.Range["A4:M4"].Font.Size = 8;
xlWorkSheet.Cells[4, "A"] = "Limite inferior";
xlWorkSheet.Cells[4, "B"] = "Limite superior";
//xlWorkSheet.Cells[4, "C"] = "Marca de clase";
xlWorkSheet.Cells[4, "D"] = "Frecuencia";
// xlWorkSheet.Cells[4, "E"] = "Frecuencia Relativa";
xlWorkSheet.Cells[4, "E"] = "F. Esperada Dist. Exp. Negativa";
xlWorkSheet.Cells[4, "F"] = "F. Chi Cua. Dist. Exp. Negativa";
// obtencion de parametros para distribuciones falta calcular estos parametros en base a datos del archivo ingresado
for (int i = 0; i < tabla.Length; i++)
{
xlWorkSheet.Cells[i + 5, "A"] = tabla[i].LimiteInferior;
xlWorkSheet.Cells[i + 5, "B"] = tabla[i].LimiteSuperior;
xlWorkSheet.Cells[i + 5, "C"] = tabla[i].conocerMedia();
xlWorkSheet.Cells[i + 5, "D"] = tabla[i].Frecuencia;
// xlWorkSheet.Cells[i + 5, "E"] = tabla[i].Frecuencia / cantNumeros;
//distribuciones esperadas
double observado = tabla[i].Frecuencia;
double esperadoExpo = ProbDistrExpo(lambda, tabla[i].LimiteSuperior, tabla[i].LimiteInferior) * cantNumeros;
xlWorkSheet.Cells[i + 5, "E"] = esperadoExpo;
// calculo de valores de chi cuadrado
chi = Math.Pow(observado - esperadoExpo, 2) / esperadoExpo;
xlWorkSheet.Cells[i + 5, "F"] = chi;
sumaChi = sumaChi + chi;
}
//muestra Sumatorias Chi cuadrado
xlWorkSheet.Cells[5, "H"] = "Sumatoria x^2";
xlWorkSheet.Cells[5, "I"] = sumaChi;
TablaChi.Class1 chi2 = new Class1();
xlWorkSheet.Cells[6, "H"] = "Valor x^2";
xlWorkSheet.Cells[6, "I"] = chi2.ValorChi2(0.95, intervalos, 1);
//xlWorkSheet.Cells[6, "I"].Formula="CHISQ.INV.RT(0,05;"+(intervalos-2)+")"; //"11.07";
//xlWorkSheet.Cells[6, "H"] = "Valor de la Tabla";
//Crea el grafico
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(20, 120, 400, 300);
Excel.Chart chartPage = myChart.Chart;
int inter = 4 + intervalos;
chartRange = xlWorkSheet.get_Range("C4", "E" + inter);
chartPage.HasTitle = true;
chartPage.ChartTitle.Caption = "Frecuencia Observada Vs. Frecuencia Esperada";
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
// chartPage.HasAxis = xlWorkSheet.Range["C5:C" + (5 + intervalos)];
chartPage.HasLegend = true;
chartPage.ShowDataLabelsOverMaximum = true;
}
finally
{
ResetCurrentCulture();
}
}
private 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;
}
}
private void SecondWay()
{
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);
string filePath = @"C:\Kalya Solutions\GMB insights.xlsx";
string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + filePath + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";";
DataTable resultsDataset = new DataTable();
using (OleDbConnection conn = new OleDbConnection(connString))
{
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [GMB insights (Discovery Report)$]", conn);
conn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
adapter.Fill(resultsDataset);
}
if (resultsDataset.Rows.Count > 3)
{
//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";
xlWorkSheet.Cells[6, 1] = "Term6";
xlWorkSheet.Cells[6, 2] = "75";
xlWorkSheet.Cells[6, 3] = "82";
xlWorkSheet.Cells[6, 4] = "68";
xlWorkSheet.Cells[7, 1] = "Term7";
xlWorkSheet.Cells[7, 2] = "75";
xlWorkSheet.Cells[7, 3] = "82";
xlWorkSheet.Cells[7, 4] = "68";
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(10, 200, 300, 250);
Excel.Chart chartPage = myChart.Chart;
//chartPage.Location(Excel.XlChartLocation.xlLocationAutomatic, "Chart1");
chartRange = xlWorkSheet.get_Range("A1", "d5");
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlDoughnutExploded;
xlWorkBook.ExportAsFixedFormat(Excel.XlFixedFormatType.xlTypePDF, "Test3.pdf");
}
//xlWorkBook.SaveAs("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);
//MessageBox.Show("Excel file created , you can find the file c:\\csharp.net-informations.xls");
}
public void UseCopyPaste()
{
// Declare variables to hold references to PowerPoint objects.
pptNS.Application powerpointApplication = null;
pptNS.Presentation pptPresentation = null;
pptNS.Slide pptSlide = null;
pptNS.ShapeRange shapeRange = null;
// Declare variables to hold references to Excel objects.
xlNS.Application excelApplication = null;
xlNS.Workbook excelWorkBook = null;
xlNS.Worksheet targetSheet = null;
xlNS.ChartObjects chartObjects = null;
xlNS.ChartObject existingChartObject = null;
string paramPresentationPath = System.Windows.Forms.Application.StartupPath + @"\ChartTest.pptx";
string paramWorkbookPath = System.Windows.Forms.Application.StartupPath + @"\ChartData.xlsx";
object paramMissing = Type.Missing;
try
{
// Create an instance of PowerPoint.
powerpointApplication = new pptNS.Application();
// Create an instance Excel.
excelApplication = new xlNS.Application();
// Open the Excel workbook containing the worksheet with the chart data.
excelWorkBook = excelApplication.Workbooks.Open(paramWorkbookPath,
paramMissing, paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing, paramMissing, paramMissing);
// Get the worksheet that contains the chart.
targetSheet =
(xlNS.Worksheet)(excelWorkBook.Worksheets["Quarterly Sales"]);
// Get the ChartObjects collection for the sheet.
chartObjects =
(xlNS.ChartObjects)(targetSheet.ChartObjects(paramMissing));
// Get the chart to copy.
existingChartObject =
(xlNS.ChartObject)(chartObjects.Item("Sales Chart"));
// Create a PowerPoint presentation.
pptPresentation =
powerpointApplication.Presentations.Add(
Microsoft.Office.Core.MsoTriState.msoTrue);
// Add a blank slide to the presentation.
pptSlide =
pptPresentation.Slides.Add(1, pptNS.PpSlideLayout.ppLayoutBlank);
// Copy the chart from the Excel worksheet to the clipboard.
existingChartObject.Copy();
// Paste the chart into the PowerPoint presentation.
shapeRange = pptSlide.Shapes.Paste();
// Position the chart on the slide.
shapeRange.Left = 60;
shapeRange.Top = 100;
// Save the presentation.
pptPresentation.SaveAs(paramPresentationPath, pptNS.PpSaveAsFileType.ppSaveAsOpenXMLPresentation, Microsoft.Office.Core.MsoTriState.msoTrue);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
// Release the PowerPoint slide object.
shapeRange = null;
pptSlide = null;
// Close and release the Presentation object.
if (pptPresentation != null)
{
pptPresentation.Close();
pptPresentation = null;
}
// Quit PowerPoint and release the ApplicationClass object.
if (powerpointApplication != null)
{
powerpointApplication.Quit();
powerpointApplication = null;
}
// Release the Excel objects.
targetSheet = null;
chartObjects = null;
existingChartObject = null;
// Close and release the Excel Workbook object.
if (excelWorkBook != null)
{
excelWorkBook.Close(false, paramMissing, paramMissing);
excelWorkBook = null;
}
// Quit Excel and release the ApplicationClass object.
if (excelApplication != null)
{
excelApplication.Quit();
excelApplication = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
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ı.");
}
}
private void Form1_Load(object sender, EventArgs e)
{
pptNS.ApplicationClass powerpointApplication = null;
pptNS.Presentation pptPresentation = null;
pptNS.Slide pptSlide = null;
pptNS.ShapeRange shapeRange = null;
pptNS.Shape oTxtShape = null;
pptNS.Shape oShape = null;
pptNS.Shape oPicShape = null;
xlNS.ApplicationClass excelApplication = null;
xlNS.Workbook excelWorkBook = null;
xlNS.Worksheet targetSheet = null;
xlNS.ChartObjects chartObjects = null;
xlNS.ChartObject existingChartObject = null;
xlNS.Range destRange = null;
string paramPresentationPath = @"D:\test\Test Slide.pptx";
string paramWorkbookPath = @"D:\test\NPS.xlsx";
object paramMissing = Type.Missing;
try
{
// Create an instance of PowerPoint.
powerpointApplication = new pptNS.ApplicationClass();
// Create an instance Excel.
excelApplication = new xlNS.ApplicationClass();
// Open the Excel workbook containing the worksheet with the chart
// data.
excelWorkBook = excelApplication.Workbooks.Open(paramWorkbookPath,
paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing, paramMissing,
paramMissing, paramMissing);
// Get the worksheet that contains the chart.
targetSheet =
(xlNS.Worksheet)(excelWorkBook.Worksheets["Spain"]);
// Get the ChartObjects collection for the sheet.
chartObjects =
(xlNS.ChartObjects)(targetSheet.ChartObjects(paramMissing));
// Create a PowerPoint presentation.
pptPresentation = powerpointApplication.Presentations.Add(
Microsoft.Office.Core.MsoTriState.msoTrue);
// Add a blank slide to the presentation.
pptSlide = pptPresentation.Slides.Add(1, pptNS.PpSlideLayout.ppLayoutBlank);
// capture range
//var writeRange = targetSheet.Range["A1:B15"];
destRange = targetSheet.get_Range("A1:B21");
oTxtShape = pptSlide.Shapes.AddTextbox(Microsoft.Office.Core.MsoTextOrientation.msoTextOrientationHorizontal, Left: 70, Top: 30, Width: 550, Height: 340);
oTxtShape.TextFrame.TextRange.Text = "GB NPS 03/01/2017 to 26/01/2017";
oTxtShape.TextEffect.FontName = "Arial";
oTxtShape.TextEffect.FontSize = 32;
oTxtShape.TextEffect.FontBold = Microsoft.Office.Core.MsoTriState.msoTrue;
oTxtShape.TextEffect.Alignment = Microsoft.Office.Core.MsoTextEffectAlignment.msoTextEffectAlignmentCentered;
System.Array myvalues = (System.Array)destRange.Cells.Value;
List <Tuple <string, string> > cellData = GetData(myvalues);
int iRows = cellData.Count + 1;
int iColumns = 2;
int row = 2;
oShape = pptSlide.Shapes.AddTable(iRows, iColumns, 500, 110, 160, 120);
oShape.Table.ApplyStyle("{5940675A-B579-460E-94D1-54222C63F5DA}");
//oShape.Table.ApplyStyle("{0660B408-B3CF-4A94-85FC-2B1E0A45F4A2}");
// table style guide https://msdn.microsoft.com/en-us/library/office/hh273476(v=office.14).aspx
oShape.Table.Cell(1, 1).Merge(oShape.Table.Cell(1, 2));
//oShape.Table.Cell(1, 1).Shape.TextFrame.TextRange.Text = "Spain Data 01/01/2017 to 20/01/2017";
//oShape.Table.Cell(1, 1).Shape.TextFrame.TextRange.Font.Name = "Verdana";
//oShape.Table.Cell(1, 1).Shape.TextFrame.TextRange.Font.Size = 8;
foreach (Tuple <string, string> item in cellData)
{
string strdate = item.Item1;
string strValue = item.Item2;
oShape.Table.Cell(row, 1).Shape.TextFrame.TextRange.Text = strdate;
oShape.Table.Cell(row, 1).Shape.TextFrame.TextRange.Font.Name = "Arial";
oShape.Table.Cell(row, 1).Shape.TextFrame.TextRange.Font.Size = 10;
oShape.Table.Cell(row, 2).Shape.TextFrame.TextRange.Text = (strValue.StartsWith("0") ? "0%" : (strValue + "0%"));
oShape.Table.Cell(row, 2).Shape.TextFrame.TextRange.Font.Name = "Arial";
oShape.Table.Cell(row, 2).Shape.TextFrame.TextRange.Font.Size = 10;
//if (row == 1)
//{
// oShape.Table.Cell(row, 1).Shape.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(208, 208, 208).ToArgb();
// oShape.Table.Cell(row, 1).Shape.Fill.Visible = Microsoft.Office.Core.MsoTriState.msoTrue;
// oShape.Table.Cell(row, 2).Shape.Fill.ForeColor.RGB = System.Drawing.Color.FromArgb(208, 208, 208).ToArgb();
// oShape.Table.Cell(row, 2).Shape.Fill.Visible = Microsoft.Office.Core.MsoTriState.msoTrue;
//}
row++;
}
oShape.Top = 100;
oShape.Left = 30;
oPicShape = pptSlide.Shapes.AddPicture(@"D:\test\chart.png",
Microsoft.Office.Core.MsoTriState.msoFalse,
Microsoft.Office.Core.MsoTriState.msoTrue, 200, 100, 500, 300);
//copy range
//destRange.Copy();
// Paste the chart into the PowerPoint presentation.
//shapeRange = pptSlide.Shapes.Paste();
//var table = pptSlide.Shapes.AddTable();
// Position the chart on the slide.
//shapeRange.Left = 60;
//shapeRange.Top = 100;
// Get or capture the chart to copy.
//existingChartObject = (xlNS.ChartObject)(chartObjects.Item(1));
// Copy the chart from the Excel worksheet to the clipboard.
//existingChartObject.Copy();
// Paste the chart into the PowerPoint presentation.
//shapeRange = pptSlide.Shapes.Paste();
//Position the chart on the slide.
//shapeRange.Left = 90;
//shapeRange.Top = 100;
// Save the presentation.
pptPresentation.SaveAs(paramPresentationPath,
pptNS.PpSaveAsFileType.ppSaveAsOpenXMLPresentation,
Microsoft.Office.Core.MsoTriState.msoTrue);
pptPresentation.SaveCopyAs(String.Format(@"D:\test\Export", "video_of_presentation"),
pptNS.PpSaveAsFileType.ppSaveAsEMF,
Microsoft.Office.Core.MsoTriState.msoCTrue);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
// Release the PowerPoint slide object.
shapeRange = null;
pptSlide = null;
// Close and release the Presentation object.
if (pptPresentation != null)
{
pptPresentation.Close();
pptPresentation = null;
}
// Quit PowerPoint and release the ApplicationClass object.
if (powerpointApplication != null)
{
powerpointApplication.Quit();
powerpointApplication = null;
}
// Release the Excel objects.
targetSheet = null;
chartObjects = null;
existingChartObject = null;
// Close and release the Excel Workbook object.
if (excelWorkBook != null)
{
excelWorkBook.Close(false, paramMissing, paramMissing);
excelWorkBook = null;
}
// Quit Excel and release the ApplicationClass object.
if (excelApplication != null)
{
excelApplication.Quit();
excelApplication = null;
}
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
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;
}
}
//// 關閉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);
}
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 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);
}
/// <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 static void ExcelGraphGeneration()
{
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 columns
xlWorkSheet.Cells[1, 1] = "SL";
xlWorkSheet.Cells[1, 2] = "Name";
xlWorkSheet.Cells[1, 3] = "CTC";
xlWorkSheet.Cells[1, 4] = "DA";
xlWorkSheet.Cells[1, 5] = "HRA";
xlWorkSheet.Cells[1, 6] = "Conveyance";
xlWorkSheet.Cells[1, 7] = "Medical Expenses";
xlWorkSheet.Cells[1, 8] = "Special";
xlWorkSheet.Cells[1, 9] = "Bonus";
xlWorkSheet.Cells[1, 10] = "TA";
xlWorkSheet.Cells[1, 11] = "TOTAL";
xlWorkSheet.Cells[1, 11] = "Contribution to PF";
xlWorkSheet.Cells[1, 12] = "Profession Tax";
xlWorkSheet.Cells[1, 13] = "TDS";
xlWorkSheet.Cells[1, 14] = "Salary Advance";
xlWorkSheet.Cells[1, 15] = "TOTAL";
xlWorkSheet.Cells[1, 16] = "NET PAY";
Excel.Application xlApp1 = new Excel.Application();
Excel.Workbook xlWorkbook = xlApp1.Workbooks.Open
(@"C:\Users\v-mapall\source\repos\ExcelCustomApps\AppServicesForExcel\Sample Data\Sample Data2.xlsx");
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Excel.Range xlRange = xlWorksheet.UsedRange;
int rowCount = xlRange.Rows.Count;
int colCount = xlRange.Columns.Count;
//for (int i = 1; i <= rowCount; i++)
for (int i = 1; i <= 2; i++)
{
for (int j = 1; j <= colCount; j++)
{
Console.WriteLine(xlRange.Cells[i, j].Value2.ToString());
xlWorkSheet.Cells[i, j] = xlRange.Cells[i, j]
.Value2.ToString();
}
}
//Console.ReadLine();
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", "R22");
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
// Export chart as picture file
chartPage.Export(@"C:\Users\v-mapall\source\repos\ExcelCustomApps\AppServicesForExcel\Sample Data\EmployeeExportData.pdf",
"PDF", misValue);
xlWorkBook.SaveAs("EmployeeExportData.xls",
Excel.XlFileFormat.xlWorkbookNormal, misValue,
misValue, misValue, misValue,
Excel.XlSaveAsAccessMode.xlExclusive, misValue,
misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
DeallocateObject(xlWorkSheet);
DeallocateObject(xlWorkBook);
DeallocateObject(xlApp);
DeallocateObject(xlApp1);
}
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);
}
//
}
public void CreateNewChartInExcel()
{
// Declare a variable for the Excel ApplicationClass instance.
Microsoft.Office.Interop.Excel.Application excelApplication = new xlNS.Application();//new Microsoft.Office.Interop.Excel.ApplicationClass();
// Declare variables for the Workbooks.Open method parameters.
string paramWorkbookPath = System.Windows.Forms.Application.StartupPath + @"\ChartData.xlsx";
object paramMissing = Type.Missing;
// Declare variables for the Chart.ChartWizard method.
object paramChartFormat = 1;
object paramCategoryLabels = 0;
object paramSeriesLabels = 0;
bool paramHasLegend = true;
object paramTitle = "Sales by Quarter";
object paramCategoryTitle = "Fiscal Quarter";
object paramValueTitle = "Billions";
try
{
// Create an instance of the Excel ApplicationClass object.
// excelApplication = new Microsoft.Office.Interop.Excel.ApplicationClass();
// Create a new workbook with 1 sheet in it.
xlNS.Workbook newWorkbook = excelApplication.Workbooks.Add(xlNS.XlWBATemplate.xlWBATWorksheet);
// Change the name of the sheet.
xlNS.Worksheet targetSheet = (xlNS.Worksheet)(newWorkbook.Worksheets[1]);
targetSheet.Name = "Quarterly Sales";
// Insert some data for the chart into the sheet.
// A B C D E
// 1 Q1 Q2 Q3 Q4
// 2 N. America 1.5 2 1.5 2.5
// 3 S. America 2 1.75 2 2
// 4 Europe 2.25 2 2.5 2
// 5 Asia 2.5 2.5 2 2.75
SetCellValue(targetSheet, "A2", "N. America");
SetCellValue(targetSheet, "A3", "S. America");
SetCellValue(targetSheet, "A4", "Europe");
SetCellValue(targetSheet, "A5", "Asia");
SetCellValue(targetSheet, "B1", "Q1");
SetCellValue(targetSheet, "B2", 1.5);
SetCellValue(targetSheet, "B3", 2);
SetCellValue(targetSheet, "B4", 2.25);
SetCellValue(targetSheet, "B5", 2.5);
SetCellValue(targetSheet, "C1", "Q2");
SetCellValue(targetSheet, "C2", 2);
SetCellValue(targetSheet, "C3", 1.75);
SetCellValue(targetSheet, "C4", 2);
SetCellValue(targetSheet, "C5", 2.5);
SetCellValue(targetSheet, "D1", "Q3");
SetCellValue(targetSheet, "D2", 1.5);
SetCellValue(targetSheet, "D3", 2);
SetCellValue(targetSheet, "D4", 2.5);
SetCellValue(targetSheet, "D5", 2);
SetCellValue(targetSheet, "E1", "Q4");
SetCellValue(targetSheet, "E2", 2.5);
SetCellValue(targetSheet, "E3", 2);
SetCellValue(targetSheet, "E4", 2);
SetCellValue(targetSheet, "E5", 2.75);
// Get the range holding the chart data.
xlNS.Range dataRange = targetSheet.get_Range("A1", "E5");
// Get the ChartObjects collection for the sheet.
xlNS.ChartObjects chartObjects = (xlNS.ChartObjects)(targetSheet.ChartObjects(paramMissing));
// Add a Chart to the collection.
xlNS.ChartObject newChartObject = chartObjects.Add(0, 100, 600, 300);
newChartObject.Name = "Sales Chart";
// Create a new chart of the data.
newChartObject.Chart.ChartWizard(dataRange, xlNS.XlChartType.xl3DColumn, paramChartFormat, xlNS.XlRowCol.xlRows,
paramCategoryLabels, paramSeriesLabels, paramHasLegend, paramTitle, paramCategoryTitle, paramValueTitle, paramMissing);
// Save the workbook.
newWorkbook.SaveAs(paramWorkbookPath, paramMissing, paramMissing, paramMissing, paramMissing,
paramMissing, xlNS.XlSaveAsAccessMode.xlNoChange, paramMissing, paramMissing, paramMissing, paramMissing, paramMissing);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
if (excelApplication != null)
{
// Close Excel.
excelApplication.Quit();
}
}
}
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();
}
public void ExportExcel(ShoppingCartLogic ccp)
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
Console.WriteLine("EXCEL could not be started. Check that your office installation and project references are correct.");
return;
}
xlApp.Visible = true;
var wb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
var ws = (Worksheet)wb.Worksheets[1];
if (ws == null)
{
Console.WriteLine("Worksheet could not be created. Check that your office installation and project references are correct.");
}
var lst = new Dictionary <string, Cell>();
/* It is much better to divide a method into smaller "centered" methods.. It is a lot more readable, understandable, maintaiable, debuggable and scalable.
* Consider: Each block of code which does some operation can be extracted into a method.
*/
#region Build Table
foreach (var chain in ccp.Chains.Values)
{
ws.Cells[_row, _col] = chain.ChainName;
foreach (var item in chain.Items.Values.OrderBy(item => item.ItemName))
{
++_row;
ws.Cells[_row, 1] = item.ItemName;
ws.Cells[_row, _col] = item.Price;
if (lst.ContainsKey(item.ItemName))
{
if (lst[item.ItemName].price > double.Parse(item.Price))
{
lst[item.ItemName].price = double.Parse(item.Price);
lst[item.ItemName].row = _row;
lst[item.ItemName].col = _col;
}
}
else
{
lst.Add(item.ItemName, new Cell(_row, _col, double.Parse(item.Price)));
}
}
_row = 1;
++_col;
}
#endregion
//mark lowest price
_col = 2;
foreach (var cell in lst)
{
ws.Cells[cell.Value.row, cell.Value.col].Interior.Color = XlRgbColor.rgbLightGreen;
}
#region Build Histogram
Microsoft.Office.Interop.Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)ws.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(LEFT, TOP, WIDTH * (ccp.Chains.Values.First().Items.Count + 1), HEIGHT);
Excel.Chart chartPage = myChart.Chart;
chartRange = ws.get_Range("A1", "D" + (ccp.Chains.Values.First().Items.Count + 1));
chartPage.SetSourceData(chartRange, Type.Missing);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
#endregion
}
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;
}
}
static void Main(string[] args)
{
Console.WriteLine("Запуск Microsoft Excel...");
//настраиваем запуск Excel и книгу и листы.
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
Console.WriteLine("Excel is not properly installed!!");
return;
}
xlApp.Visible = true;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//предыдущая таблица создавалась для 11 варианта
string[,] text = new string[, ] {
{ "Привлеченные средства коммерческого банка", "Сумма млн.грн." },
{ "Депозиты государственных предприятий", "2000" },
{ "Депозиты с/ х предприятий", "850" },
{ "Депозиты СП", "700" },
{ "Вклады населения", "4000" },
{ "Депозиты внебюджетных фондов", "1000" },
{ "Депозиты АО и ТОО", "1200" },
{ "Остатки на расчетных и текущих счетах клиентов", "8000" },
{ "Депозиты юридических лиц в валюте(в грн.)", "5000" }
};
int rows = text.GetLength(0),
columns = text.GetLength(1);
//Вставляем значения в ячейки
for (int i = 1; i <= rows; i++)
{
for (int j = 1; j <= columns; j++)
{
xlWorkSheet.Cells[i, j] = text[i - 1, j - 1];
if (j == 1)
{
((Excel.Range)xlWorkSheet.Columns[1]).ColumnWidth = 45;//устанавливаем ширину для первого столбца
}
else
{
((Excel.Range)xlWorkSheet.Columns[j]).ColumnWidth = 15;//устанавливаем ширину для остальных столбцов
}
}
}
string[] range = { "B2:B9" }; //диапазоны для расчетов по столбцам"B2:B5","C2:C5","D2:D5","E2:E5"//
double[] sumResult = new double[range.Length]; //сумма для каждого диапазона значений
double[] avgResult = new double[range.Length]; //среднее для каждого диапазона значений
Excel.Range xlRng;
//считаем среднее и сумму всех строк
for (int i = 0; i < range.Length; i++)
{
xlRng = xlWorkSheet.Range[range[i]]; //получаем диапазон значений
sumResult[i] = xlApp.WorksheetFunction.Sum(xlRng); //считаем сумму
avgResult[i] = xlApp.WorksheetFunction.Average(xlRng); //считаем среднее
}
xlWorkSheet.Cells[10, 1] = "Общая сумма млн. грн.:"; //"Сумма в каждом квартале: ";
xlWorkSheet.Cells[11, 1] = "Среднее млн. грн."; //"Среднее в каждом квартале: ";
//выводим итоги
for (int j = 0; j < range.Length; j++)
{
xlWorkSheet.Cells[10, j + 2] = sumResult[j];
xlWorkSheet.Cells[11, j + 2] = avgResult[j];
}
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(330, 0, 540, 360);
Excel.Chart chartPage = myChart.Chart;
chartRange = xlWorkSheet.get_Range("A1", "B9");
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
xlWorkBook.Close();
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
Console.WriteLine("Таблица успешно создана!");
Console.ReadKey();
}
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");
}
// データテーブルをエクセルへエクスポート（ＸＲ管理図・ヒストグラム付き）
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;
}
/// <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 WriteToExcelFile(string path, List <string[]> listasMin, List <string[]> listasMax, List <string[]> listasAvg, string[] parametros)
{
Excel.Application excelApplication = new Excel.Application();
excelApplication.Visible = false;
Excel.Workbook excelWorkbook = excelApplication.Workbooks.Open(path);
Excel.Worksheet excelWorksheet = excelWorkbook.ActiveSheet;
//escrever dados para o excel
int linha;
int coluna = 600;
int aux = 0;
foreach (string[] lista in listasMin)
{
linha = 0;
Excel.Chart myChart = null;
Excel.ChartObjects charts = excelWorksheet.ChartObjects();
Excel.ChartObject chartObject = charts.Add(10, (300 * aux) + 10, 300, 300); //left, top, width, heigh
myChart = chartObject.Chart;
foreach (string dado in lista)
{
linha++;
string dadoaux = dado.Replace(",", ".");
excelWorksheet.Cells[linha, coluna].Value = dadoaux;
}
//set chart range
Excel.Range c1 = excelWorksheet.Cells[1, coluna];
Excel.Range c2 = excelWorksheet.Cells[linha, coluna];
Excel.Range myrange = excelWorksheet.get_Range(c1, c2);
myChart.SetSourceData(myrange);
//chart properties using the named parameters and default parameters functionality in the .NET
myChart.ChartType = Excel.XlChartType.xlLine;
myChart.ChartWizard(Source: myrange,
Title: parametros[aux],
CategoryTitle: "Hours",
ValueTitle: "Min Values");
coluna++;
aux++;
}
aux = 0;
coluna++;
foreach (string[] lista in listasMax)
{
linha = 0;
Excel.Chart myChart = null;
Excel.ChartObjects charts = excelWorksheet.ChartObjects();
Excel.ChartObject chartObject = charts.Add(320, (300 * aux) + 10, 300, 300); //left, top, width, heigh
myChart = chartObject.Chart;
foreach (string dado in lista)
{
linha++;
string dadoaux = dado.Replace(",", ".");
excelWorksheet.Cells[linha, coluna].Value = dadoaux;
}
//set chart range
Excel.Range c1 = excelWorksheet.Cells[1, coluna];
Excel.Range c2 = excelWorksheet.Cells[linha, coluna];
Excel.Range myrange = excelWorksheet.get_Range(c1, c2);
myChart.SetSourceData(myrange);
//chart properties using the named parameters and default parameters functionality in the .NET
myChart.ChartType = Excel.XlChartType.xlLine;
myChart.ChartWizard(Source: myrange,
Title: parametros[aux],
CategoryTitle: "Hours",
ValueTitle: "Max Values");
coluna++;
aux++;
}
aux = 0;
coluna++;
foreach (string[] lista in listasAvg)
{
linha = 0;
Excel.Chart myChart = null;
Excel.ChartObjects charts = excelWorksheet.ChartObjects();
Excel.ChartObject chartObject = charts.Add(640, (300 * aux) + 10, 300, 300); //left, top, width, heigh
myChart = chartObject.Chart;
foreach (string dado in lista)
{
linha++;
string dadoaux = dado.Replace(",", ".");
excelWorksheet.Cells[linha, coluna].Value = dadoaux;
}
//set chart range
Excel.Range c1 = excelWorksheet.Cells[1, coluna];
Excel.Range c2 = excelWorksheet.Cells[linha, coluna];
Excel.Range myrange = excelWorksheet.get_Range(c1, c2);
myChart.SetSourceData(myrange);
//chart properties using the named parameters and default parameters functionality in the .NET
myChart.ChartType = Excel.XlChartType.xlLine;
myChart.ChartWizard(Source: myrange,
Title: parametros[aux],
CategoryTitle: "Hours",
ValueTitle: "Average Values");
coluna++;
aux++;
}
excelWorkbook.Save();
excelWorkbook.Close();
excelApplication.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
excelWorkbook = null;
//Don’t forget to free the memory used by excel objects
//...
GC.Collect();
}
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");
}
/// <summary>
/// Method sets the sheet with common results
/// </summary>
/// <param name="worksheet">Worksheet where the information will be saved</param>
/// <param name="calculationTimes">Time results for each calculation types</param>
/// <param name="results">results for each calculation types</param>
private static void SetCommonResults(Excel.Worksheet worksheet, Dictionary <CalculationTypeName, double> calculationTimes,
Dictionary <CalculationTypeName, List <DEVariable> > results)
{
worksheet.Name = "Common results";
int rowIndex = 1;
int columnIndex = 1;
worksheet.Cells[rowIndex, columnIndex] = "Calculation results";
rowIndex++;
int i = 0;
foreach (KeyValuePair <CalculationTypeName, List <DEVariable> > item in results)
{
int j = 0;
List <DEVariable> result;
if (i == 0)
{
result = item.Value;
foreach (DEVariable variable in result)
{
worksheet.Cells[rowIndex + j + 1, columnIndex] = variable.Name;
j++;
}
}
worksheet.Cells[rowIndex, columnIndex + i + 1] = item.Key.ToString();
j = 0;
result = item.Value;
foreach (DEVariable variable in result)
{
worksheet.Cells[rowIndex + 1 + j, columnIndex + i + 1] = variable.Value;
j++;
}
i++;
}
rowIndex += 3;
worksheet.Cells[rowIndex, columnIndex] = "Time results";
rowIndex++;
i = 0;
foreach (KeyValuePair <CalculationTypeName, double> calculationTime in calculationTimes)
{
worksheet.Cells[rowIndex, columnIndex + i] = calculationTime.Key.ToString();
worksheet.Cells[rowIndex + 1, columnIndex + i] = calculationTime.Value;
i++;
}
string leftTopTimeChart = GetExcelColumnName(1) + rowIndex.ToString();
string rightDownTimeChart = GetExcelColumnName(calculationTimes.Count) + (rowIndex + 1).ToString();
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)worksheet.ChartObjects(Type.Missing);
Excel.ChartObject mychart = xlCharts.Add(10, 80, 500, 450);
Excel.Chart chartPage = mychart.Chart;
chartRange = worksheet.get_Range(leftTopTimeChart, rightDownTimeChart);
chartPage.SetSourceData(chartRange);
chartPage.ChartType = Excel.XlChartType.xl3DColumnClustered;
chartPage.SeriesCollection(1).Name = "Calculation times";
}
private void getChartInSheet(int index)
{
comboChartInSheet.Items.Clear();
if (index != 0)
{
ws = (Excel.Worksheet)xls.Sheets[index];
chobjs = (Excel.ChartObjects)ws.ChartObjects(nullobj);
//Console.WriteLine(chobjs.Count);
for (int i = 1; i <= chobjs.Count; i++)
{
chobj = (Excel.ChartObject)ws.ChartObjects(i);
ch = chobj.Chart;
string str = chobj.Name + ": ";
try
{
string title = ch.ChartTitle.Text;
str += title;
}
catch { str += "(未命名图表)"; }
comboChartInSheet.Items.Add(str);
}
}
if (comboChartInSheet.Items.Count == 0)
{
comboChartInSheet.Items.Add("(该页无图表！)");
buttonGraph.Enabled = false;
groupGraphPoint.Enabled = false;
}
else
{
buttonGraph.Enabled = true;
groupGraphPoint.Enabled = true;
}
comboChartInSheet.SelectedIndex = 0;
}
//获取学生文件的第一幅图表
private Excel.Chart getStudentChartPosition()
{
Excel.Chart res = null;
int tot = stuXls.Sheets.Count;
int ch_in_work_tot = 0; //工作簿中的图表数
for (int i = 1; i <= tot; i++)
{
if (stuCate[i] == 0) //工作簿
{
ch_in_work_tot = ((Excel.ChartObjects)((Excel.Worksheet)stuXls.Sheets[i]).ChartObjects(nullobj)).Count;
if (ch_in_work_tot > 0)
{
res = ((Excel.ChartObject)((Excel.Worksheet)stuXls.Sheets[i]).ChartObjects(1)).Chart;
stuObj = (Excel.ChartObject)((Excel.Worksheet)stuXls.Sheets[i]).ChartObjects(1);
break;
}
}
else //单独图表
{
res = (Excel.Chart)stuXls.Sheets[i];
stuObj = null;
break;
}
}
return res;
}
/// <summary>
/// Initializes Excel vareables
/// </summary>
public static void ExcelPrep()
{
UsingExcel = true;
R1EPCsDic.Clear();
R2EPCsDic.Clear();
#region config Excel application and workbook
oXL = new Excel.Application
{
Visible = true,
WindowState = Excel.XlWindowState.xlMaximized
};
oWB = oXL.Workbooks.Add(Missing.Value);
#endregion
try
{
#region config sheets
oSheetDataRSSIs = (Excel._Worksheet)oWB.ActiveSheet;
oSheetDataRSSIs.Name = "Rssis Data";
oSheetDataRSSIs.get_Range("A1:Z1").Font.Bold = true;
oSheetDataTags = (Excel._Worksheet)oWB.Sheets.Add(Type.Missing, oSheetDataRSSIs, Type.Missing, Type.Missing);
oSheetDataTags.Name = "Tags Data";
oSheetDataTags.get_Range("A1:Z2").Font.Bold = true;
oSheetDataTags.get_Range("A1:Z2").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
oSheetCharts = (Excel._Worksheet)oWB.Sheets.Add(Type.Missing, oSheetDataTags, Type.Missing, Type.Missing);
oSheetCharts.Name = "Charts";
oSheetTagFlags = (Excel._Worksheet)oWB.Sheets.Add(Type.Missing, oSheetCharts, Type.Missing, Type.Missing);
oSheetTagFlags.Name = "Flags";
oSheetTagFlags.Cells[1, 1] = "A";
oSheetTagFlags.Cells[1, 2] = "B";
// Selects sheet if needed
//oSheetDataTags.Activate();
#endregion
#region config charts
Excel.ChartObjects xlCharts = oSheetCharts.ChartObjects(Type.Missing);
#region config RSSI chart
oRng = oSheetCharts.get_Range("B2:K36");
oChartRSSIs = xlCharts.Add(oRng.Left, oRng.Top, oRng.Width, oRng.Height);
oChartRSSIs.Chart.ChartWizard(
Gallery: Excel.XlChartType.xlLine,
Format: 4,
PlotBy: Excel.XlRowCol.xlColumns,
CategoryLabels: false,
SeriesLabels: 5,
HasLegend: true,
Title: "RSSIs",
CategoryTitle: "Values_Range",
ValueTitle: "RSSI"
);
oChartRSSIs.Chart.SetSourceData(oSheetCharts.get_Range("A1:A2"));
#region RSSIs chart axis
Excel.Axis axis = oChartRSSIs.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
axis.MinimumScaleIsAuto = false;
axis.MaximumScaleIsAuto = false;
axis.MajorUnit = 1;
axis.MinimumScale = 170;
axis.MaximumScale = 220;
#endregion
#endregion
#region config Tags chart
oRng = oSheetCharts.get_Range("M2:U36");
oChartTags = xlCharts.Add(oRng.Left, oRng.Top, oRng.Width, oRng.Height);
oChartTags.Chart.ChartWizard(
Gallery: Excel.XlChartType.xlXYScatter,
Format: 3,
CategoryLabels: true,
SeriesLabels: 5,
HasLegend: true,
Title: "Tags Location",
CategoryTitle: "X",
ValueTitle: "Y"
);
oChartTags.Chart.ChartType = Excel.XlChartType.xlXYScatter;
oChartTags.Chart.SetSourceData(oSheetCharts.get_Range("A1:A2"));
#region Tags chart axis
axis = oChartTags.Chart.Axes(Excel.XlAxisType.xlValue, Excel.XlAxisGroup.xlPrimary);
axis.MinimumScaleIsAuto = false;
axis.MaximumScaleIsAuto = false;
axis.MajorUnit = 1;
axis.MinimumScale = 0;
axis.MaximumScale = 10;
axis = oChartTags.Chart.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
axis.MinimumScaleIsAuto = false;
axis.MaximumScaleIsAuto = false;
axis.MajorUnit = 1;
axis.MinimumScale = -4;
axis.MaximumScale = 4;
#endregion
#endregion
#endregion
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}