how to insert a new column and rows to an excel hosted on the server?

Question

how to insert a new column and rows to an excel hosted on the server?

Navigation

#1 by (0 votes)

1

Good afternoon community question C # language? I'm making some modifications to an excel hosted on my server I need to go through it, check if the rows are empty add a new row with header and fill the new row with the sum of two fields, and reviewed many link pages on how to do it and could not To give a solution, I was commissioned to use the office interop library. This is my code:

protected void btnACS_Click(object sender, EventArgs e)
{
    //variables para poder manipular el excel 
    Excel.Application xlA;
    Excel._Workbook xlWB;
    Excel._Worksheet xlWS;
    object misValue = System.Reflection.Missing.Value;
    Excel.Range Rng;


    //Inicializar el excel y obtener el objeto de la aplicacion 
    xlA = new Excel.Application();
    xlA.Visible = true;

    //abrir excel para manipular sus datos
    xlWB = xlA.Workbooks.Open(Server.MapPath("." + 
    "/Carpetadondeestaelarchivo/nombreexcel.xlsx"), 1, true, 5, "", "", true, 
    Excel.XlPlatform.xlWindows, "/t", false, false, 1, true, 1, 1);
    xlWS = (Excel.Worksheet)xlWB.Worksheets[1];

    // seleccionar rango activo
    Rng = xlWS.UsedRange;

    // leer las celdas
    int rows = Rng.Rows.Count;
    int cols = Rng.Columns.Count;

    //recorrer las celdas e introducir una fila nueva con una formula
    if (rows != null && cols != null)
    {
        for(int i=1; i<rows; i++)
        {
            for (int j = 1; j < cols; j++)
            {
                xlWS.Cells[1, 6]="Titulo";//Inserta titulo 
                xlWS.Cells[2, 6] = "=([@filaseleccionada]-[@filaseleccionada]+[@filaseleccionada])";//Inserta formula 
            }
        }
    }

    //Para guardar los cambios y cerrar todo en el archivo
    xlWB.Save();
    xlWB.Close(true,misValue,misValue);
    xlA.Quit();

    releaseObject(xlWS);
    releaseObject(xlWB);
    releaseObject(xlA);
}

I need to combine where the for with the injection of the formula is new in the world of the c #

c#

asked by Rodrigo Riveros 03.09.2018 в 18:55

source

1 answer

When adding banner the RecyclerView does not add a new position add the value of the items in the list

score 0 · Accepted Answer

Solved the problem in every way, I leave the commented code in case someone serves, this button updates an excel on a server, adds a row with a formula and downloads it, giving the user the choice of where to save it

protected void btnACS_Click(object sender, EventArgs e)
{
    //variables para poder manipular el excel 
    Excel.Application xlA;
    Excel._Workbook xlWB;
    Excel._Worksheet xlWS;
    Excel.Range Rng;


    //Inicializar el excel y obtener el objeto de la aplicacion 
    xlA = new Excel.Application();
    xlA.Visible = true;

    //abrir excel para manipular sus datos
    xlWB = xlA.Workbooks.Open(Server.MapPath("." + 
    "/carpetaenelservidor/nombreexcel.xlsx"), Type.Missing, Type.Missing, 
    Type.Missing, Type.Missing, Type.Missing,
    Type.Missing, Excel.XlPlatform.xlWindows, Type.Missing, Type.Missing, 
    Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
    xlWS = (Excel.Worksheet)xlWB.Worksheets[1];

    // seleccionar rango activo
    Rng = xlWS.UsedRange;

    // leer las celdas
    int rows = Rng.Rows.Count;
    int cols = Rng.Columns.Count;

    //recorrer las celdas e introducir una fila nueva con una formula
    if (rows != null && cols != null)
    {
        for(int i=1; i<rows; i++)
        {
            for (int j = 1; j < cols; j++)
            {
                xlWS.Cells[1, 6] = "Tituloencabeceradelafila";
                xlWS.Cells[2, 6] = "=([@filaseleccionadaenexcel]+ 
                [@filaseleccionadaenexcel]*[@filaseleccionadaenexcel])";
            }
        }
    }

    //Para guardar los cambios y cerrar todo en el archivo
    xlWB.Close(true, Type.Missing, Type.Missing);
    xlA.Quit();

    releaseObject(xlWS);
    releaseObject(xlWB);
    releaseObject(xlA);
}
private void releaseObject(object obj)
{
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        obj = null;
    }
    catch (Exception ex)
    {
        obj = null;
        MessageBox.Show("No se ha podido liberar el objeto " + 
        ex.ToString());
    }
    finally
    {
        GC.Collect();
    }
}