Load specific cells from an Excel to a datagridview from winform in C #

0

Saudos, In a tab control I have two tabs, each has a grid the first grid must insert n number of values in specific cells of an excel and the second must receive the result of operations performed in the same excel but in another sheet and return them in the second grid also of specific cells, I have only achieved the import of excel but this brings me everything that is in the excel grid. I would appreciate your support.

class Importar
    {
        OleDbConnection conexion;
        OleDbDataAdapter MyDataAdapter;
        DataTable dt;
        public string celda { get; set; }

        public void ImportarExcel (DataGridView dgv, string nombreHoja)
        {
            string ruta = "";
            try
            {
                OpenFileDialog openfile = new OpenFileDialog();
                openfile.Filter = "Excel Files |*.xls;*.xlsx;*.xlsm";
                openfile.Title = "";

                if (openfile.ShowDialog () == System.Windows.Forms.DialogResult.OK)
                {
                    if (openfile.FileName.Equals("") == false)
                    {
                        ruta = openfile.FileName;
                    }
                }

                conexion = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;"+"Data Source=" + ruta + ";Extended Properties='Excel 12.0 Xml;HDR=YES;'");
                MyDataAdapter = new OleDbDataAdapter("Select * From [" + nombreHoja + "$]", conexion);
                dt = new DataTable();
                MyDataAdapter.Fill(dt);
                //celda = dt.Rows[6][2].ToString();
                dgv.DataSource = dt;


            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
    }
    
asked by Pet 23.08.2018 в 17:29
source

1 answer

0

with the help of a partner and googling out there, I managed to interop both the import of the grid and the export, in case I served someone there I leave the code, Regards.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;

namespace excelEPPlus
{
class MyExcel
{

    public static BindingList<CROMA> cromaList = new BindingList<CROMA>();
    //public static BindingList<CROMA2> cromaList2 = new BindingList<CROMA2>();
    private static Excel.Application Myapp = null;
    private static Excel.Workbook LibroExcel = null;
    private static Excel.Worksheet HojaExcel = null;
    private static Excel.Worksheet HojaExcelEnt = null;
    private static int lastRow = 8;

    public static List<string> ListaRangos = new List<string>() { "C5:C22", "F9:F26", "I2:I20", "L10:L27" };

    public static void InicializarExcel()
    {
        Myapp = new Excel.Application();
        LibroExcel = Myapp.Workbooks.Open(@"C:\Users\One323\Documents\Visual Studio 2015\Projects\excelEPPlus\excelEPPlus\Archivos\prueba.xlsx");
        HojaExcel = (Excel.Worksheet)LibroExcel.Sheets["Hoja2"];
        HojaExcelEnt = (Excel.Worksheet)LibroExcel.Sheets["Hoja4"];
        //lastRow = HojaExcelEnt.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
    }

    public static BindingList<CROMA> ReadMyExcel()
    {
        cromaList.Clear();
        foreach (string Rango in ListaRangos)
        {
            var datoexcel = HojaExcel.Cells.Range[Rango].Value2;

            cromaList.Add(new CROMA
            {
                ID_EXCEL = datoexcel.GetValue(1, 1).ToString(),
                TAG = datoexcel.GetValue(3, 1).ToString(),
                NOMBRE = datoexcel.GetValue(4, 1).ToString(),
                Qg = datoexcel.GetValue(6, 1).ToString(),
                C1 = datoexcel.GetValue(8, 1).ToString(),
                C2 = datoexcel.GetValue(9, 1).ToString(),
                C3 = datoexcel.GetValue(10, 1).ToString(),
                iC4 = datoexcel.GetValue(11, 1).ToString(),
                nC4 = datoexcel.GetValue(12, 1).ToString(),
                iC5 = datoexcel.GetValue(13, 1).ToString(),
                nC5 = datoexcel.GetValue(14, 1).ToString(),
                C6 = datoexcel.GetValue(15, 1).ToString(),
                CO2 = datoexcel.GetValue(16, 1).ToString(),
                H2S = datoexcel.GetValue(17, 1).ToString(),
                N2 = datoexcel.GetValue(18, 1).ToString()
            });
        }
        return cromaList;
    }


    public static void WriteToExcel2(CROMA lsc)
    {
        //if (lastRow != null)
        try
            {
                lastRow += 1;
                HojaExcelEnt.Cells[lastRow, 2] = lsc.ID_EXCEL;
                HojaExcelEnt.Cells[lastRow, 4] = lsc.TAG;
                HojaExcelEnt.Cells[lastRow, 5] = lsc.NOMBRE;
                HojaExcelEnt.Cells[lastRow, 7] = lsc.Qg;
                HojaExcelEnt.Cells[lastRow, 10] = lsc.C1;
                HojaExcelEnt.Cells[lastRow, 11] = lsc.C2;

                //cromaList.Add(cromaExcel);

            }
            catch (Exception ex)
        { }
        LibroExcel.Save();
    }

    public static void Close()
    {
        LibroExcel.Close(true);
        Myapp.Quit();
    }
  }
}

The ReadMyExcel method is called po run button to load it to the DGV

  private void btnCroma_Click_1(object sender, EventArgs e)
    {
        MyExcel.InicializarExcel();
        dgvCroma.DataSource = MyExcel.ReadMyExcel();
        MyExcel.Close();
    }

The WriteToExcel2 method is launched by the other button

 private void btnGuardar2_Click(object sender, EventArgs e)
    {
        List<CROMA> lsC = new List<CROMA>();


        MyExcel.InicializarExcel();

        for (int i = 0; i < dgvEntrada.Rows.Count - 1; i++)
        {
            //for (int j = 0; j < dgvEntrada.Columns.Count; j++)
            //{
                //if (dgvEntrada.Rows[i].Cells[j + 1].Value != null)
                //{
                    CROMA cromanew = new CROMA
                    {

                        ID_EXCEL = dgvEntrada.Rows[i].Cells[0].Value == null ? "" : dgvEntrada.Rows[i].Cells[0].Value.ToString(),
                        TAG = dgvEntrada.Rows[i].Cells[1].Value == null ? "" : dgvEntrada.Rows[i].Cells[1].Value.ToString(),
                        NOMBRE = dgvEntrada.Rows[i].Cells[2].Value == null ? "" : dgvEntrada.Rows[i].Cells[2].Value.ToString(),
                        Qg = dgvEntrada.Rows[i].Cells[3].Value == null ? "" : dgvEntrada.Rows[i].Cells[3].Value.ToString(),
                        C1 = dgvEntrada.Rows[i].Cells[4].Value == null ? "" : dgvEntrada.Rows[i].Cells[4].Value.ToString(),
                        C2 = dgvEntrada.Rows[i].Cells[5].Value == null ? "" : dgvEntrada.Rows[i].Cells[5].Value.ToString()


                    };

                   //lsC.Add(cromanew);

                    MyExcel.WriteToExcel2(cromanew);

                //}


            //}

        }

        dgvEntrada.Rows.Clear();
        dgvEntrada.Refresh();
        MyExcel.Close();
    }

At least I stay in this way, maybe someone can optimize it more, greeting.

    
answered by 01.09.2018 / 01:02
source