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);
}