Home / Questions / 149796
Accepted
Aritzbn
Asked: 2020-03-24 00:10:41 +0800 CST 2020-03-24 00:10:41 +0800 CST

Any way to optimize this type of code?

  • 772

I was working with sheets in excel and I have to create and delete several of these. The problem is that, doing it as I do, I have a rather " dirty " code in my opinion and I was wondering if there is any way to make it cleaner. 

            //Añadimos una hoja auxiliar que guardamos en una variable auxiliar
        Worksheet aux = null;
        try
        {
            //Añado la hoja
            aux = workbook.Worksheets.Add("DATOS AUX");//Si existe lanza argumentException

        }
        catch (ArgumentException)
        {
            aux = workbook.Worksheets["DATOS AUX"];//Igualo Aux a la hoja que se llame "DATOS AUX"
            aux.Clear(); //La vacío por si contiene algún tipo de 
        }
        try
        {
            workbook.Worksheets.Add("Hoja1");//Añado hoja si existe lanza ARgumentException
        }
        catch (ArgumentException)
        {
            var hoja1= workbook.Worksheets["Hoja1"];//Igualo hoja1 a la hoja
            hoja1.Clear(); //la vacío 
        }

I use ArgumentExceptionit because it is the exception that it gives me when the sheet that I try to create already exists and in the library that I use I do not see any method to do something likeif(Workbook.worksheet[indice].exists)...

c#

1 Answers

  1. Best Answer

    You can use LINQ to check if a sheet exists. To do this you must do a previous step, a cast of the collection Worksheetsto the type Excel.Worksheet, and later you can check if the name exists:

    bool existe = workbook.Worksheets.OfType<Excel.Worksheet>().Select(x=>x.Name).Contains("DATOS AUX");

    If what you want is to return the sheet if it exists, or null otherwise, you can use FirstOrDefault:

    var hoja = workbook.Worksheets.OfType<Excel.Worksheet>()
                              .FirstOrDefault(x => x.Name == "DATOS AUX");
if (hoja == null)
{
    //no existe, la añadimos
    aux = workbook.Worksheets.Add("DATOS AUX");
}
else
{
    //existe, la asignamos en aux
    aux = hoja;
}

    If the sheet exists, it returns it to you hoja, otherwise it hojawill be null.

    Edit

    If you are not allowed to use Linq (there are some very weird people), you can create a simple method:

    private Excel.Worksheet ObtenerHoja(Workbook w, string nombre)
{
    foreach(Excel.Worksheet hoja in w.Worksheets)
    {
        if (hoja.Name == nombre)
        {
            return hoja;
        } 
    }
    return null;
}
    • 7