public void DatatableToExcel(DataTable _table, string _sheetName) { wb.Sheets.Add(); Excel.Worksheet ws = (Excel.Worksheet)wb.Sheets[1]; ws.Name = _sheetName; for (int i = 0; i < _table.Columns.Count; i++) { string rowIndex = "1"; string columnIndex = NumberToLetter(i + 1); string header = _table.Columns[i].Caption; Excel.Range aRange = ws.get_Range(columnIndex + rowIndex, columnIndex + rowIndex); object[] args = new object[1]; args[0] = header; aRange.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, aRange, args); } for (int j = 0; j < _table.Rows.Count; j++) { { for (int i = 0; i < _table.Columns.Count; i++) { string rowIndex = (j + 2).ToString(); string columnIndex = NumberToLetter(i + 1); Excel.Range aRange = ws.get_Range(columnIndex + rowIndex); object[] args = new object[1]; args[0] = _table.Rows[j][i]; aRange.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, aRange, args); } } } }
private void exportToExcelToolStripMenuItem_Click(object sender, EventArgs e) { //var orders = (lbClients.SelectedItem as Client).Orders; var orders = this._repo.GetOrders(); Excel.Application excelApp = new Excel.Application(); excelApp.Visible = true; Excel.Workbook wb = excelApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1]; Excel.Range aRange = ws.get_Range("C1", "C7"); Object[] args = new Object[1]; args[0] = 6; aRange.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, aRange, args); }
public void Experiments() { XL.Application xlApp = new XL.Application(); if (xlApp != null) { xlApp.Visible = true; Workbook wb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet); Worksheet ws = (Worksheet)wb.Worksheets[1]; ws.Name = "Test"; wb.Worksheets.Add(); Worksheet w2 = (Worksheet)wb.Worksheets[wb.Worksheets.Count]; w2.Name = "X222"; ws = (Worksheet)wb.Worksheets[1]; if (ws != null) { XL.Range aRange = ws.get_Range("C1", "C7"); if (aRange != null) { // Fill the cells in the C1 to C7 range of the worksheet with the number 6. Object[] args = new Object[1]; args[0] = 17; aRange.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, aRange, args); // Change the cells in the C1 to C7 range of the worksheet to the number 8. aRange.Value2 = 8; //aRange.Text = "Xc"; } XL.Range bRange = ws.get_Range("C4", "C4"); bRange.Interior.Color = 52479; // This is GOLD XL.Range cRange = ws.get_Range("C7", "C7"); object cf = wb.Colors; cRange.Interior.Color = (int)(19749); ws.Cells[1, 1] = "T11"; ws.Cells[1, 2] = "T12"; ws.Cells[2, 1] = "T21"; cRange = (XL.Range)(w2.Cells[3, 3]); string a1 = ((XL.Range)(w2.Cells[1, 3])).get_Address(); string a2 = ((XL.Range)(w2.Cells[2, 3])).get_Address(); w2.Cells[3, 3] = "=" + a1 + "*" + a2; cRange.Interior.Color = System.Drawing.Color.Goldenrod.ToExcelColor(); cRange = (XL.Range)(ws.Cells[5, 5]); cRange.Interior.Color = (int)(43791); } } }
public ManejoExcel(List <persona> datos) { this.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; Excel.Workbook wb = xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1]; if (ws == null) { Console.WriteLine("Worksheet could not be created. Check that your office installation and project references are correct."); } // Select the Excel cells, in the range c1 to c7 in the worksheet. /*Range aRange = ws.get_Range("c1", "c7"); * * if (aRange == null) * { * Console.WriteLine("Could not get a range. Check to be sure you have the correct versions of the office DLLs."); * } * * // Fill the cells in the C1 to C7 range of the worksheet with the number 6. * Object[] args = new Object[1]; * args[0] = 6; * aRange.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, aRange, args); * /* * // Change the cells in the C1 to C7 range of the worksheet to the number 8. * aRange.Value2 = 8;*/ //selecinamos celdas de la hoja Excel.Range rangoCeldas = ws.get_Range("a1"); if (rangoCeldas == null) { MessageBox.Show("no tielne la version correcta de office"); } //llenamos las celdas de a1 a f1 en la hoja Object[] enc = new object[1]; enc[0] = "Identificacion"; rangoCeldas.GetType().InvokeMember("value", BindingFlags.SetProperty, null, rangoCeldas, enc); rangoCeldas = ws.get_Range("b1"); enc[0] = "Nombre"; rangoCeldas.GetType().InvokeMember("value", BindingFlags.SetProperty, null, rangoCeldas, enc); rangoCeldas = ws.get_Range("c1"); enc[0] = "Apellido"; rangoCeldas.GetType().InvokeMember("value", BindingFlags.SetProperty, null, rangoCeldas, enc); int index = 1; rangoCeldas = ws.get_Range($"d{index}"); enc[0] = "Direccion"; rangoCeldas.GetType().InvokeMember("value", BindingFlags.SetProperty, null, rangoCeldas, enc); rangoCeldas = ws.get_Range($"e{index}"); enc[0] = "telefono"; rangoCeldas.GetType().InvokeMember("value", BindingFlags.SetProperty, null, rangoCeldas, enc); rangoCeldas = ws.get_Range($"f{index}"); enc[0] = "cumpleaños"; rangoCeldas.GetType().InvokeMember("value", BindingFlags.SetProperty, null, rangoCeldas, enc); foreach (persona a in datos) { index++; rangoCeldas = ws.get_Range($"a{index}"); enc = new object[1]; enc[0] = a.Cc; rangoCeldas.GetType().InvokeMember("value", BindingFlags.SetProperty, null, rangoCeldas, enc); rangoCeldas = ws.get_Range($"b{index}"); enc[0] = a.Nombre; rangoCeldas.GetType().InvokeMember("value", BindingFlags.SetProperty, null, rangoCeldas, enc); rangoCeldas = ws.get_Range($"c{index}"); enc[0] = a.Apellido; rangoCeldas.GetType().InvokeMember("value", BindingFlags.SetProperty, null, rangoCeldas, enc); rangoCeldas = ws.get_Range($"d{index}"); enc[0] = a.Direccion; rangoCeldas.GetType().InvokeMember("value", BindingFlags.SetProperty, null, rangoCeldas, enc); rangoCeldas = ws.get_Range($"e{index}"); enc[0] = a.Telefono; rangoCeldas.GetType().InvokeMember("value", BindingFlags.SetProperty, null, rangoCeldas, enc); rangoCeldas = ws.get_Range($"f{index}"); enc[0] = a.Cumpleaños; rangoCeldas.GetType().InvokeMember("value", BindingFlags.SetProperty, null, rangoCeldas, enc); } /*Object[] encabezados = new object[7]; * encabezados[0] = "identificacion"; * encabezados[1] = "Nombre"; * encabezados[2] = "Apellido"; * encabezados[3] = "direccion"; * encabezados[4] = "telefono"; * encabezados[5] = "cumpleaños"; * Object[] per = new object[1]; * per[0] = "A"; * rangoCeldas.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, rangoCeldas, per);*/ foreach (persona a in datos) { index++; try { ws.get_Range("A" + index).Value2 = a.Nombre + " " + a.Apellido; ws.get_Range("B" + index).Value2 = a.Cc; ws.get_Range("C" + index).Value2 = a.Cumpleaños; ws.get_Range("D" + index).Value2 = a.Direccion; ws.get_Range("E" + index).Value2 = a.Telefono; //Agregamos la Suma de los Trimestres usando la formula que obtuvimos en el //documento de Excel al crear la Macro. ws.get_Range("F" + index).FormulaR1C1 = String.Format("=SUM(RC[-{0}]:RC[-1])", 4); //Agregamos el promedio usando otra vez una formula de una Macro en Excel ws.get_Range("G" + index).FormulaR1C1 = String.Format("=RC[-1]/{0}", 4); index++; } catch (System.Runtime.InteropServices.COMException e) { throw e; } } ws.get_Range("A" + index).Value2 = "Ventas Totales"; ws.get_Range("A" + (index + 1)).Value2 = "Promedio"; /* foreach (char col in columns) * { * //Agregamos las Ventas Totales - Generalizando la formula VBA * worksheet.get_Range(col + "" + cont).FormulaR1C1 = String.Format("=SUM(R[-{0}]C:R[-1]C)", lista.Count); * * //Agregamos el Promedio * worksheet.get_Range(col + "" + (cont + 1)).FormulaR1C1 = String.Format("=R[-1]C/{0}", lista.Count); * }*/ //Agregamos el Grafico index++; ws.Shapes.AddChart(Excel.XlChartType.xlColumnClustered) .Chart.SetSourceData(ws.get_Range("A" + index + ":" + "G" + (index + 4))); ws.get_Range("A1").Select(); }
public ExcelRange(Excel.Range basedOnRange) { m_aRangeBasedOn = basedOnRange; m_typeRange = basedOnRange.GetType(); // so the properties in the base class work }
public void SetRangeValue(string range, object[,] value) { this.range = (ExcelApp.Range)_currentSheet.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, _currentSheet, new object[] { range }); range.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, range, new object[] { value }); }
private void btnReport_Click(object sender, System.EventArgs e) { if (!CReg.CanRun()) { MessageBox.Show("Please register software!!", "Happy Code"); return; } if (dgResult.Rows.Count == 0) { MessageBox.Show("Check Result is Empty!!", "Happy Code"); return; } copyAlltoClipboard(); Microsoft.Office.Interop.Excel.Application xlApp; Microsoft.Office.Interop.Excel.Workbook xlWorkBook; Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.Application(); if (xlApp == null) { MessageBox.Show("Excel is not properly installed!!", "Happy Code"); return; } //xlApp.Visible = true; CultureInfo ci = new CultureInfo("en-US"); xlWorkBook = (Excel.Workbook)xlApp.Workbooks.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, null, xlApp.Workbooks, null, ci); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[2, 1]; CR.GetType().InvokeMember("Select", BindingFlags.InvokeMethod, null, CR, null, ci); var p = new object[] { CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true }; xlWorkSheet.GetType().InvokeMember("PasteSpecial", BindingFlags.InvokeMethod, null, xlWorkSheet, p, ci); for (int i = 0; i < dgResult.ColumnCount; i++) { p = new object[] { dgResult.Columns[i].HeaderText }; CR = (Excel.Range)xlWorkSheet.Cells[1, i + 1]; CR.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, CR, p, ci); } CR = xlWorkSheet.get_Range((Excel.Range)xlWorkSheet.Cells[1, 1], (Excel.Range)xlWorkSheet.Cells[1, dgResult.ColumnCount]); var interior = CR.GetType().InvokeMember("Interior", BindingFlags.GetProperty, null, CR, null, ci); interior.GetType().InvokeMember("ColorIndex", BindingFlags.SetProperty, null, interior, new object[] { 23 }, ci); var font = CR.GetType().InvokeMember("Font", BindingFlags.GetProperty, null, CR, null, ci); font.GetType().InvokeMember("Bold", BindingFlags.SetProperty, null, font, new object[] { true }, ci); font.GetType().InvokeMember("ColorIndex", BindingFlags.SetProperty, null, font, new object[] { 2 }, ci); font.GetType().InvokeMember("Size", BindingFlags.SetProperty, null, font, new object[] { 14 }, ci); CR = xlWorkSheet.get_Range((Excel.Range)xlWorkSheet.Cells[1, 1], (Excel.Range)xlWorkSheet.Cells[dgResult.RowCount, dgResult.ColumnCount]); CR.Columns.GetType().InvokeMember("AutoFit", BindingFlags.InvokeMethod, null, CR.Columns, null, ci); CR = (Excel.Range)xlWorkSheet.Cells[1, 1]; CR.GetType().InvokeMember("Select", BindingFlags.InvokeMethod, null, CR, null, ci); string tmpPath = System.Environment.GetEnvironmentVariable("TEMP"); string extname = ".xls"; string prefix = "MemoryLeakReport_"; string outputFilename = prefix + DateTime.Now.ToString("yyyyMMddHHmmss") + extname; string fullFilename = Path.Combine(tmpPath, outputFilename); p = new object[] { fullFilename, Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing }; xlWorkBook.GetType().InvokeMember("SaveAs", BindingFlags.InvokeMethod, null, xlWorkBook, p, ci); p = new object[] { Type.Missing, Type.Missing }; xlWorkBook.GetType().InvokeMember("Close", BindingFlags.InvokeMethod, null, xlWorkBook, p, ci); xlApp.Quit(); if (CR != null) { releaseObject(CR); } if (xlWorkSheet != null) { releaseObject(xlWorkSheet); } if (xlWorkSheet != null) { releaseObject(xlWorkSheet); } if (xlWorkBook != null) { releaseObject(xlWorkBook); } if (xlApp != null) { releaseObject(xlApp); } System.Diagnostics.Process.Start(fullFilename); }