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 enviarExcel()
{
Excel.Application XlApp;
Excel.Workbook XlWorkBook;
Excel.Worksheet XlWorkSheet;
object misValue = System.Reflection.Missing.Value;
//cria planilia temporaria
XlApp = new Excel.Application();
XlWorkBook = XlApp.Workbooks.Add(misValue);
XlWorkSheet = (Excel.Worksheet)XlWorkBook.Worksheets.get_Item(1);
XlWorkSheet.Cells[1, 1] = "Dados da avaliação";
XlWorkSheet.Cells[2, 1] = "Muito Ruim";
XlWorkSheet.Cells[3, 1] = "Ruim";
XlWorkSheet.Cells[4, 1] = "Médio";
XlWorkSheet.Cells[5, 1] = "Bom";
XlWorkSheet.Cells[6, 1] = "Muito Bom";
XlWorkSheet.Cells[7, 1] = "Ótimo";
XlWorkSheet.Cells[8, 1] = "QTD Total votos";
XlWorkSheet.Cells[9, 1] = "Média";
XlWorkSheet.Cells[1, 2] = "QTD Votos";
XlWorkSheet.Cells[2, 2] = mtruim.Text;
XlWorkSheet.Cells[3, 2] = label1.Text;
XlWorkSheet.Cells[4, 2] = label2.Text;
XlWorkSheet.Cells[5, 2] = label3.Text;
XlWorkSheet.Cells[6, 2] = mtbom.Text;
XlWorkSheet.Cells[7, 2] = otimo.Text;
XlWorkSheet.Cells[8, 2] = label8.Text;
XlWorkSheet.Cells[9, 2] = label5.Text;
XlWorkSheet.Cells[1, 3] = "Relatorio NTP";
XlWorkSheet.Cells[2, 3] = "Valor NTP";
XlWorkSheet.Cells[2, 4] = label16.Text;
XlWorkSheet.Cells[6, 3] = "% de avaliação";
XlWorkSheet.Cells[7, 3] = "Baixo";
XlWorkSheet.Cells[8, 3] = "medio";
XlWorkSheet.Cells[9, 3] = "alto";
XlWorkSheet.Cells[7, 4] = label17.Text;
XlWorkSheet.Cells[8, 4] = label18.Text;
XlWorkSheet.Cells[9, 4] = label19.Text;
XlWorkSheet.Cells[1, 5] = "Estrelas";
XlWorkSheet.Cells[2, 5] = label6.Text;
XlWorkSheet.Cells[3, 5] = label7.Text;
XlWorkSheet.Cells[1, 6] = label4.Text;
XlWorkSheet.Cells[11, 11] = "Os graficos estao sobreposto, arraste para separalos.";
//erro grafico 1 n aparece e grafico 2 sim como resolver.
//grafico 2
Excel.Range chartRange;
Excel.ChartObjects xlCharts = (Excel.ChartObjects)XlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart = (Excel.ChartObject)xlCharts.Add(100, 180, 500, 300);
Excel.Chart chartPage = myChart.Chart;
chartRange = XlWorkSheet.get_Range("A2", "B7");
chartPage.SetSourceData(chartRange, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
//fim do grafico 1
//grafico1
Excel.Range chartRange1;
Excel.ChartObjects xlCharts1 = (Excel.ChartObjects)XlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart1 = (Excel.ChartObject)xlCharts1.Add(100, 180, 500, 300);
Excel.Chart chartPage1 = myChart1.Chart;
chartRange1 = XlWorkSheet.get_Range("C7", "d9");
chartPage1.SetSourceData(chartRange1, misValue);
chartPage1.ChartType = Excel.XlChartType.xlColumnClustered;
XlWorkBook.SaveAs(dia + "0" + mes + ano + ".xls",
Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
XlWorkBook.Close(true, misValue, misValue);
XlApp.Quit();
string folder1 = @"C:/Relatorios/";
if (!Directory.Exists(folder1))
{
//Criamos um com o nome folder
Directory.CreateDirectory(folder1);
}
MessageBox.Show(folder1 + dia + "0" + mes + ano + "xls" + " " + " caso não esteja la verifique em documentos");
}
/// <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";
}
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();
}
// データテーブルをエクセルへエクスポート（ＸＲ管理図・ヒストグラム付き）
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;
}
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();
}
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 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();
}
private void diagram_Click(object sender, EventArgs e)
{
KURS.allDataSet kursds = new KURS.allDataSet();
KURS.allDataSetTableAdapters.ZakazTableAdapter zta = new KURS.allDataSetTableAdapters.ZakazTableAdapter();
zta.Fill(kursds.Zakaz);
//
object misValue = System.Reflection.Missing.Value;
//
Excel.Application excelapp = new Excel.Application();
excelapp.Visible = true;
excelapp.SheetsInNewWorkbook = 1;
excelapp.Workbooks.Add(misValue);
Excel.Workbooks excelappworkbooks = excelapp.Workbooks;
Excel.Workbook excelappworkbook = excelappworkbooks[1];
Excel.Sheets excelsheets = excelappworkbook.Worksheets;
Excel.Worksheet excelworksheet = (Excel.Worksheet)excelsheets.get_Item(1);
//excelworksheet.Activate();
//
var c = from p in kursds.Zakaz.AsEnumerable() where p.Data.Month == dateTimePicker1.Value.Month select p;
int count = 0;
excelapp.Cells[1][1] = "date";
excelapp.Cells[2][1] = "summ";
foreach (var p in c)
{
count++;
}
//
int[] a = new int[count]; double[] b = new double[count]; int i = 0; double sum = 0;
while (i < count)
{
foreach (var p in c)
{
a[i] = p.Data.Day;
if (i == 0)
{
sum += p.Summ;
}
if (i > 0)
{
if (a[i] == a[i - 1])
{
sum += p.Summ;
}
else
{
b[i - 1] = sum; sum = p.Summ; i++;
}
}
else
{
i++;
}
}
b[i - 1] = sum;
count = i; i = 0;
break;
}
Dictionary <int, double> dic = new Dictionary <int, double>();
while (i < count)
{
excelapp.Cells[1][i + 2] = a[i];
excelapp.Cells[2][i + 2] = b[i];
dic.Add(a[i], b[i]);
i++;
}
//
string str = (count + 1).ToString();
//
//Excel.Window excelWindow = null;
Excel.Range excelcells = null;
//Определяем диаграммы как объекты Excel.ChartObjects
Excel.ChartObjects chartsobjrcts = (Excel.ChartObjects)excelworksheet.ChartObjects(Type.Missing);
//Добавляем одну диаграмму в Excel.ChartObjects - диаграмма пока не выбрана, но место для нее выделено в методе Add
Excel.ChartObject chartsobjrct = chartsobjrcts.Add(100, 40, 300, 300);
Excel.Chart chartPage = chartsobjrct.Chart;
excelcells = excelworksheet.get_Range("B1", "B" + str);
chartPage.SetSourceData(excelcells, misValue);
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
Excel.Axis axis = (Excel.Axis)chartPage.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
axis.HasTitle = true;
axis.AxisTitle.Text = "Data";
//
Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)chartPage.SeriesCollection(Type.Missing);
Excel.Series series = seriesCollection.Item(1);
series.XValues = a;
//
excelappworkbook.SaveAs(@"D:\siple.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
excelappworkbook.Close(true, misValue, misValue);
excelapp.Quit();
this.chart1.Titles.Add("Отчет за месяц");
this.chart1.Series[0].Points.Clear();
Series ser = this.chart1.Series[0];
ser.LegendText = "Summ";
foreach (KeyValuePair <int, double> pair in dic)
{
ser.Points.AddXY(pair.Key, pair.Value);
}
//
}
private PdeExports GetExportedData(Excel.Workbook wExcel)
{
if (wExcel == null)
{
return(null);
}
InternalBookmark ibm = GetPdeInternalBookmark(wExcel);
PdeExports export = new PdeExports();
DomainExportItem expDomain = new DomainExportItem();
expDomain.DomainName = "DomainName";
#region get tags
foreach (Excel.Name name in wExcel.Names)
{
ExportItem expItem = new ExportItem();
expItem.IsUsed = false; // default is not used
bool isAdd = false;
string strName = name.Name;
if (string.IsNullOrWhiteSpace(strName))
{
continue;
}
// update field
if (strName.EndsWith(BaseProntoMarkup.KeySelect))
{
expItem.MapType = MapType.SingleCell;
expItem.DataType = GetPdeDataType(strName, ibm);
isAdd = true;
}
// update table
if (strName.EndsWith(BaseProntoMarkup.KeyTable))
{
expItem.MapType = MapType.Table;
Excel.Range range = name.RefersToRange;
Excel.ListObject listColumn = range.ListObject;
expItem.Columns = new System.Collections.Generic.List <ColumnExportItem>();
// update columns
foreach (Excel.ListColumn lstCol in listColumn.ListColumns)
{
ColumnExportItem expColumn = new ColumnExportItem();
expColumn.ColumnName = lstCol.Name;
expColumn.ExcelName = lstCol.Range.Name.Name;
expColumn.TreeNodeName = expColumn.ExcelName;
expColumn.ExcelAddress = lstCol.Range.AddressLocal;
expColumn.ParentName = strName;
expColumn.DomainName = expDomain.DomainName;
expColumn.IsUsed = false;
expColumn.DataType = GetPdeDataType(expColumn.ExcelName, ibm);
expItem.Columns.Add(expColumn);
}
isAdd = true;
}
if (isAdd)
{
expItem.ExcelSheetName = name.RefersToRange.Parent.Name;
expItem.ExcelName = strName;
expItem.ExcelAddress = name.RefersToRange.AddressLocal;
expItem.TreeNodeName = expItem.ExcelName;
expItem.ParentName = expDomain.DomainName;
expDomain.Items.Add(expItem);
}
}
#endregion
#region get charts
foreach (Excel.Worksheet sheet in wExcel.Sheets)
{
Excel.ChartObjects charts = sheet.ChartObjects(Type.Missing);
if (charts != null)
{
foreach (Excel.ChartObject chart in charts)
{
ExportItem exportItem = new ExportItem();
exportItem.MapType = MapType.Chart;
exportItem.ExcelName = chart.Name;
exportItem.TreeNodeName = exportItem.ExcelName;
exportItem.ParentName = expDomain.DomainName;
exportItem.ExcelSheetName = sheet.Name;
exportItem.Chart = new ExportItemChart();
exportItem.Chart.Name = chart.Name;
string filePath = AssetManager.FileAdapter.GenRandomFilePath(ChartExtension);
chart.Chart.Export(filePath, "JPG");
exportItem.Chart.Content = ProntoDoc.Framework.Utils.FileHelper.FileToBase64(filePath);
System.IO.File.Delete(filePath);
expDomain.Items.Add(exportItem);
}
}
}
#endregion
export.Items = new System.Collections.Generic.List <DomainExportItem>();
export.Items.Add(expDomain);
return(export);
}
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 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 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 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 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();
}
}
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;
}
}
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 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");
}
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;
}
}
/// <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 void ExportToExcelWithBoxPlotChart(DataTable dt)
{
if (dt.Rows.Count == 0)
{
return;
}
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
// column headings
for (int i = 0; i < dt.Columns.Count; i++)
{
xlWorkSheet.Cells[1, (i + 1)] = dt.Columns[i].ColumnName;
}
// rows
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
xlWorkSheet.Cells[(i + 2), (j + 1)] = dt.Rows[i][j];
}
}
int row = xlWorkSheet.UsedRange.Rows.Count;
string address1 = "C1:C" + row.ToString() + ",H1:L" + row.ToString();
string address2 = "M2:M" + row.ToString();
string address3 = "N2:N" + row.ToString();
Excel.Range chartRange1;
Excel.Range chartRange2;
Excel.Range chartRange3;
Excel.ChartObjects xlCharts1 = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
Excel.ChartObject myChart1 = (Excel.ChartObject)xlCharts1.Add(800, 10, 600, 250);
Excel.Chart chartPage1 = myChart1.Chart;
chartRange1 = xlWorkSheet.get_Range(address1);
chartRange2 = xlWorkSheet.get_Range(address2);
chartRange3 = xlWorkSheet.get_Range(address3);
chartPage1.SetSourceData(chartRange1, misValue);
chartPage1.ChartType = Excel.XlChartType.xlColumnStacked;
chartPage1.HasTitle = true;
chartPage1.ChartTitle.Text = dt.Rows[0]["inspect"].ToString() + " " + dt.Rows[0]["line"].ToString();
chartPage1.HasLegend = false;
Excel.Axis xAxis1 = (Excel.Axis)chartPage1.Axes(Excel.XlAxisType.xlCategory, Excel.XlAxisGroup.xlPrimary);
xAxis1.CategoryType = Excel.XlCategoryType.xlCategoryScale;
Excel.SeriesCollection oSeriesCollection = (Excel.SeriesCollection)myChart1.Chart.SeriesCollection(misValue);
Excel.Series s5 = oSeriesCollection.Item(5);
s5.ChartType = Excel.XlChartType.xlLine;
s5.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbDarkOrchid;
Excel.Series s4 = oSeriesCollection.Item(4);
s4.ChartType = Excel.XlChartType.xlLine;
s4.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbDarkOrchid;
Excel.Series s1 = oSeriesCollection.Item(1);
s1.Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbWhite;
s1.Format.Fill.Transparency = 1;
s1.Format.Line.Weight = 0;
s1.HasErrorBars = true;
s1.ErrorBar(Excel.XlErrorBarDirection.xlY, Excel.XlErrorBarInclude.xlErrorBarIncludeMinusValues,
Excel.XlErrorBarType.xlErrorBarTypeCustom, chartRange2, chartRange2);
Excel.Series s3 = oSeriesCollection.Item(3);
s3.Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbAqua;
s3.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbBlack;
s3.Format.Line.Weight = 1.0F;
s3.HasErrorBars = true;
s3.ErrorBar(Excel.XlErrorBarDirection.xlY, Excel.XlErrorBarInclude.xlErrorBarIncludePlusValues,
Excel.XlErrorBarType.xlErrorBarTypeCustom, chartRange3, chartRange3);
Excel.Series s2 = oSeriesCollection.Item(2);
s2.Format.Fill.ForeColor.RGB = (int)Excel.XlRgbColor.rgbAqua;
s2.Format.Line.ForeColor.RGB = (int)Excel.XlRgbColor.rgbBlack;
s2.Format.Line.Weight = 1.0F;
xlApp.Visible = true;
}
private void 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>
/// 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;
}
}
}
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 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 не найден"); }
}
/// <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);
}
}
/// <summary>
/// Метод создания и сохранения документов
/// в форматах Microsoft Word (doc, PDF),
/// Excel (exls)
/// </summary>
/// <param name="type">Тип создаваемого документа
/// отчёт или статистика</param>
/// <param name="format">Формат сохранения
/// документ или таблица</param>
/// <param name="name">Название документа</param>
/// <param name="table">Входная таблица с данными</param>
public void Document_Create(Document_Type type,
Document_Format format, string name,
DataTable table)
{
//Получение данных о конфигурации документа
Configuration_class configuration_Class
= new Configuration_class();
configuration_Class.Document_Configuration_Get();
//Проверка на пустоту названия
switch (name != "" || name != null)
{
case true:
//Выбор формата либо Word либо Excel
switch (format)
{
case Document_Format.Word:
//Запуск процесса в дистпечере задач
word.Application application
= new word.Application();
//создание документа в процессе
word.Document document
//Присвоение документа процессу, Visible: true
//возможность редактирования документа
= application.Documents.Add(Visible: true);
try
{
//Объявление дипапазона для формирования текста
word.Range range = document.Range(0, 0);
//89Настройка отступов в документе
document.Sections.PageSetup.LeftMargin
= application.CentimetersToPoints(
(float)Configuration_class.
doc_Left_Merge);
document.Sections.PageSetup.TopMargin
= application.CentimetersToPoints(
(float)Configuration_class.
doc_Top_Merge);
document.Sections.PageSetup.RightMargin
= application.
CentimetersToPoints((float)
Configuration_class.doc_Right_Merg);
document.Sections.PageSetup.BottomMargin
= application.CentimetersToPoints(
(float)Configuration_class.
doc_Bottom_Merge);
//Присвоение текстового знеачения в дипазон
range.Text =
"Мвидео";
//Настройка выравнивания текста
range.ParagraphFormat.Alignment =
word.WdParagraphAlignment.
wdAlignParagraphCenter;
//Настройка интервала после абзаца
range.ParagraphFormat.SpaceAfter = 1;
//Настройка интервала перед абзаца
range.ParagraphFormat.SpaceBefore = 1;
//Настройка межстрочного интервала
range.ParagraphFormat.LineSpacingRule
= word.WdLineSpacing.wdLineSpaceSingle;
//Настройка названия шрифта
range.Font.Name = "Times New Roman";
//Настройка размера шрифта
range.Font.Size = 12;
//Добавление параграфов
document.Paragraphs.Add(); //В конце текста
document.Paragraphs.Add(); //Свободный
document.Paragraphs.Add(); //Для будущего текста
//Параграф для названия документа
word.Paragraph Document_Name
= document.Paragraphs.Add();
//Настройка параграфа через свойство диапазона
Document_Name.Format.Alignment
= word.WdParagraphAlignment.wdAlignParagraphCenter;
Document_Name.Range.Font.Name = "Times New Roman";
Document_Name.Range.Font.Size = 16;
//Проверка на тип документа, отчёт или статистика
switch (type)
{
case Document_Type.Report:
Document_Name.Range.Text = "ОТЧЁТ";
break;
case Document_Type.Statistic:
Document_Name.Range.Text = "СТАТИСТИЧЕСКИЙ ОТЧЁТ";
break;
}
document.Paragraphs.Add();
document.Paragraphs.Add();
document.Paragraphs.Add();
word.Paragraph statparg = document.Paragraphs.Add();
//Создание области таблицы в документе
word.Table stat_table
//Добавление таблицы в область документа
//Указывается параграф в котором документ создан
//Количество строк и столбцов
= document.Tables.Add(statparg.Range,
table.Rows.Count, table.Columns.Count);
//Настройка границ таблицы внутренние
stat_table.Borders.InsideLineStyle
= word.WdLineStyle.wdLineStyleSingle;
//Настройка границ таблицы внешние
stat_table.Borders.OutsideLineStyle
= word.WdLineStyle.wdLineStyleSingle;
//Выравнивание текста внутри ячеек по ширине
stat_table.Rows.Alignment
= word.WdRowAlignment.wdAlignRowCenter;
//Выравнивание текста внутри ячеек по высоте
stat_table.Range.Cells.VerticalAlignment =
word.WdCellVerticalAlignment.wdCellAlignVerticalCenter;
stat_table.Range.Font.Size = 11;
stat_table.Range.Font.Name = "Times New Roman";
//Индексация столбцов и строк в Word начинается с 1,1
for (int row = 1; row <= table.Rows.Count; row++)
{
for (int col = 1; col <= table.Columns.Count; col++)
{
stat_table.Cell(row, col).Range.Text
= table.Rows[row - 1][col - 1].ToString();
}
}
document.Paragraphs.Add();
document.Paragraphs.Add();
//Парадграф с фиксациейц даты создания документа
word.Paragraph Footparg = document.Paragraphs.Add();
Footparg.Range.Text =
string.Format("Дата создания \t\t\t{0}",
DateTime.Now.ToString("dd.MM.yyyy"));
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
}
finally
{
switch (format)
{
case Document_Format.Word:
//Сохранение документа с названием из метода,
//и в формате doc
document.SaveAs2(string.Format("{0}\\{1}", Environment.CurrentDirectory, name),
word.WdSaveFormat.wdFormatDocument);
break;
case Document_Format.PDF:
//Сохранение документа в формате PDF
document.SaveAs2(string.Format("{0}\\{1}", Environment.CurrentDirectory, name),
word.WdSaveFormat.wdFormatPDF);
break;
}
//Закрываем документ
document.Close();
//Выходим из процесса с его закрытием
application.Quit();
}
break;
case Document_Format.Excel:
//Создание процесса Excel
excel.Application application_ex
= new excel.Application();
//Создание книги
excel.Workbook workbook
= application_ex.Workbooks.Add();
//Создание страницы
excel.Worksheet worksheet
= (excel.Worksheet)workbook.ActiveSheet;
try
{
switch (type)
{
case Document_Type.Report:
//Название страницы
worksheet.Name = "Отчёт";
for (int row = 0; row < table.Rows.Count; row++)
{
for (int col = 0; col < table.Columns.Count; col++)
{
//ЗАнесение данных в ячейку
worksheet.Cells[row + 1][col + 1]
= table.Rows[row][col].ToString();
}
}
//Указание диапазона работы с ячеёками листа
excel.Range border
//Начало диапазона
= worksheet.Range[worksheet.Cells[1, 1],
//Динамический конец диапазона в зависимости от
//выдодимых данных
worksheet.Cells[table.Rows.Count + 1]
[table.Columns.Count + 1]];
//Стиль линий границ ячеек
border.Borders.LineStyle = excel.XlLineStyle.xlContinuous;
//Выравнивание во высоте
border.VerticalAlignment = excel.XlHAlign.xlHAlignCenter;
//Выравнивание по ширине
border.HorizontalAlignment = excel.XlHAlign.xlHAlignCenter;
//Внесение даты создания документа
worksheet.Cells[table.Rows.Count + 3][2]
= string.Format("Дата создания {0}",
DateTime.Now.ToString());
//Объединение ячеек
worksheet.Range[worksheet.Cells[table.Rows.Count + 3, 2],
worksheet.Cells[table.Rows.Count + 2,
table.Columns.Count + 2]].Merge();
break;
case Document_Type.Statistic:
worksheet.Name = "Статистический отчёт";
for (int row = 0; row < table.Rows.Count; row++)
{
for (int col = 0; col < table.Columns.Count; col++)
{
worksheet.Cells[row + 1][col + 1]
= table.Rows[row][col].ToString();
}
}
excel.Range border1
= worksheet.Range[worksheet.Cells[1, 1],
worksheet.Cells[table.Rows.Count + 1]
[table.Columns.Count + 1]];
border1.Borders.LineStyle
= excel.XlLineStyle.xlContinuous;
border1.VerticalAlignment
= excel.XlHAlign.xlHAlignCenter;
border1.HorizontalAlignment
= excel.XlHAlign.xlHAlignCenter;
worksheet.Cells[table.Rows.Count + 3][2]
= string.Format("Дата создания {0}",
DateTime.Now.ToString());
worksheet.Range[worksheet.Cells[table.Rows.Count + 3, 2],
worksheet.Cells[table.Rows.Count + 2,
table.Columns.Count + 2]].Merge();
//Класс области графиков
excel.ChartObjects chartObjects
= (excel.ChartObjects)worksheet.ChartObjects(
Type.Missing);
//Область размещения графиков: отступы слева сверху,
//размер ширина и высота
excel.ChartObject chartObject
= chartObjects.Add(300, 50, 250, 250);
//Объявление области графика
excel.Chart chart = chartObject.Chart;
//Объявление колекции построений графиков
excel.SeriesCollection seriesCollection
= (excel.SeriesCollection)chart.SeriesCollection(
Type.Missing);
//Объявление посторения графика
excel.Series series = seriesCollection.NewSeries();
//Тип графика
chart.ChartType = excel.XlChartType.xl3DColumn;
//Диапазон значений по оси X
series.XValues =
worksheet.get_Range("B2", "B" + table.Rows.Count + 1);
//Диапазон значений по оси Y
series.Values =
worksheet.get_Range("C2", "C" + table.Rows.Count + 1);
break;
}
}
catch
{
}
finally
{
//Сохранение книги
workbook.SaveAs(string.Format("{0}\\{1}", Environment.CurrentDirectory, name), application_ex.DefaultSaveFormat);
//Закрытие книги
workbook.Close();
//Завершение процесса
application_ex.Quit();
}
break;
}
break;
case false:
System.Windows.Forms.MessageBox.Show
("Введите название документа");
break;
}
}
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");
}
//highest slowest revenues
public string HighestRevenuesItems(bool FastOrSlowReport, int NumberOfItems, string DateFrom, string DateTo, bool ExportToExcel = false, string ExportPath = "")
{
try
{
xlApp = new Excel.Application();
xlApp.DisplayAlerts = false;
xlApp.Visible = false;
xlApp.SheetsInNewWorkbook = 1;
xlWorkBook = xlApp.Workbooks.Add(misValue);//misValue
string ReportName = (FastOrSlowReport ? "Highest Revenues Items Report" : "Lowest Revenues Items Report");
DataTable aTable = ReportsMgmt.HighestRevenuesItems(NumberOfItems, DateFrom, DateTo, FastOrSlowReport);
if (aTable.Rows.Count > 0)
{
int RowCnt = 1;
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Name = ReportName;
List <string> aHeader = ReportsHelper.ImportReportHeader(0, 1);
List <string> aFooter = ReportsHelper.ImportReportHeader(1, 1);
for (int i = 0; i < aHeader.Count; i++)
{
xlWorkSheet.Cells[RowCnt, 2] = aHeader[i];
RowCnt++;
}
xlWorkSheet.Cells[RowCnt++, 1] = ReportName;
xlWorkSheet.Cells[RowCnt, 2] = "Date From:\t" + DateFrom;
xlWorkSheet.Cells[RowCnt, 3] = "Date To:\t" + DateTo;
RowCnt++;
xlWorkSheet.Cells[RowCnt, 1] = "Item Description";
xlWorkSheet.Cells[RowCnt, 2] = "Total Revenue JOD";
xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).Font.Bold = true;
//xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).WrapText = true;
xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
RowCnt++;
int DataStart = RowCnt;
foreach (DataRow aRow in aTable.Rows)
{
xlWorkSheet.Cells[RowCnt, 1] = aRow["ItemDescription"].ToString();
xlWorkSheet.Cells[RowCnt, 2] = aRow["Summation"].ToString();
RowCnt++;
}
xlWorkSheet.get_Range("A" + DataStart.ToString(), "K" + RowCnt.ToString()).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
xlWorkSheet.get_Range("A" + DataStart.ToString(), "K" + RowCnt.ToString()).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
oRng = xlWorkSheet.get_Range("A1", "K" + RowCnt.ToString());
oRng.EntireColumn.AutoFit();
Excel.ChartObjects myCharts = (Excel.ChartObjects)xlWorkSheet.ChartObjects(Type.Missing);
xlWorkSheet.DisplayRightToLeft = false;
int size = aTable.Rows.Count * 100;
if (size >= 600)
{
size = 600;
}
if (size < 300)
{
size = 300;
}
Excel.ChartObject myChart = (Excel.ChartObject)myCharts.Add(0, RowCnt * 15, size, 300);
Excel.Chart chartPage = myChart.Chart;
Excel.SeriesCollection seriesCollection = chartPage.SeriesCollection();
Excel.Series series1 = seriesCollection.NewSeries();
RowCnt--; //because we started from 1 suppose to be 0
series1.Name = ReportName;
series1.XValues = xlWorkSheet.Range["A" + DataStart.ToString(), "A" + RowCnt.ToString()];
series1.Values = xlWorkSheet.Range["B" + DataStart.ToString(), "B" + RowCnt.ToString()];
chartPage.ChartType = Excel.XlChartType.xlColumnClustered;
Excel.Axis axis = chartPage.Axes(Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary) as Excel.Axis;
series1.ApplyDataLabels(Excel.XlDataLabelsType.xlDataLabelsShowBubbleSizes);
object format = Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml;
xlWorkSheet.SaveAs(ReportsHelper.TempOutputPath, format);
if (ExportToExcel)
{
format = Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7;
xlWorkSheet.SaveAs(ExportPath, format);
}
xlApp.UserControl = false;
return("TRUE");
}
else
{
return("EMPTY");
}
}
catch (Exception)
{
return("ERROR");
}
finally
{
//Once done close and quit Excel
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
}
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);
}