epplus c#

Excel a DataTable usando EPPlus-Excel bloqueado para editar

Question

Estoy usando el siguiente código para convertir un Excel en una tabla de datos usando EPPlus:

public DataTable ExcelToDataTable(string path)
{
    var pck = new OfficeOpenXml.ExcelPackage();
    pck.Load(File.OpenRead(path));
    var ws = pck.Workbook.Worksheets.First();
    DataTable tbl = new DataTable();
    bool hasHeader = true;
    foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
    {
        tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
    }
    var startRow = hasHeader ? 2 : 1;
    for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
    {
        var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
        var row = tbl.NewRow();
        foreach (var cell in wsRow)
        {
            row[cell.Start.Column - 1] = cell.Text;
        }
        tbl.Rows.Add(row);
    }
    pck.Dispose();
    return tbl;
}

Crea el Excel, sin embargo, cuando intento abrirlo, me da el mensaje de que está bloqueado para su edición por otro usuario y que solo puedo abrirlo en modo de solo lectura.

Pensé en usar:

pck.Dispose();

resolvería el problema, sin embargo, sigo recibiendo el mismo error.

Además, cuando intento eliminar el archivo, aparece el mensaje: La acción no se puede completar porque el archivo está abierto en WebDev.WebServer40.EXE.

Alguna idea de cómo resolver esto? Gracias por adelantado. :)

Answer 1

Creé un método que convierte un archivo de Excel en una DataTable utilizando EPPlus, y traté de mantener Type Safety. También se manejan los nombres de las columnas duplicadas y con un booleano puede decir el método si la hoja tiene una fila con encabezados. Lo he creado para un proceso de importación complejo que tiene varios pasos después de la carga que requiere la entrada del usuario antes de comprometerse con la base de datos.

private DataTable ExcelToDataTable(byte[] excelDocumentAsBytes, bool hasHeaderRow)
{
    DataTable dt = new DataTable();
    string errorMessages = "";

    //create a new Excel package in a memorystream
    using (MemoryStream stream = new MemoryStream(excelDocumentAsBytes))
    using (ExcelPackage excelPackage = new ExcelPackage(stream))
    {
        ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets[1];

        //check if the worksheet is completely empty
        if (worksheet.Dimension == null)
        {
            return dt;
        }

        //add the columns to the datatable
        for (int j = worksheet.Dimension.Start.Column; j <= worksheet.Dimension.End.Column; j++)
        {
            string columnName = "Column " + j;
            var excelCell = worksheet.Cells[1, j].Value;

            if (excelCell != null)
            {
                var excelCellDataType = excelCell;

                //if there is a headerrow, set the next cell for the datatype and set the column name
                if (hasHeaderRow == true)
                {
                    excelCellDataType = worksheet.Cells[2, j].Value;

                    columnName = excelCell.ToString();

                    //check if the column name already exists in the datatable, if so make a unique name
                    if (dt.Columns.Contains(columnName) == true)
                    {
                        columnName = columnName + "_" + j;
                    }
                }

                //try to determine the datatype for the column (by looking at the next column if there is a header row)
                if (excelCellDataType is DateTime)
                {
                    dt.Columns.Add(columnName, typeof(DateTime));
                }
                else if (excelCellDataType is Boolean)
                {
                    dt.Columns.Add(columnName, typeof(Boolean));
                }
                else if (excelCellDataType is Double)
                {
                    //determine if the value is a decimal or int by looking for a decimal separator
                    //not the cleanest of solutions but it works since excel always gives a double
                    if (excelCellDataType.ToString().Contains(".") || excelCellDataType.ToString().Contains(","))
                    {
                        dt.Columns.Add(columnName, typeof(Decimal));
                    }
                    else
                    {
                        dt.Columns.Add(columnName, typeof(Int64));
                    }
                }
                else
                {
                    dt.Columns.Add(columnName, typeof(String));
                }
            }
            else
            {
                dt.Columns.Add(columnName, typeof(String));
            }
        }

        //start adding data the datatable here by looping all rows and columns
        for (int i = worksheet.Dimension.Start.Row + Convert.ToInt32(hasHeaderRow); i <= worksheet.Dimension.End.Row; i++)
        {
            //create a new datatable row
            DataRow row = dt.NewRow();

            //loop all columns
            for (int j = worksheet.Dimension.Start.Column; j <= worksheet.Dimension.End.Column; j++)
            {
                var excelCell = worksheet.Cells[i, j].Value;

                //add cell value to the datatable
                if (excelCell != null)
                {
                    try
                    {
                        row[j - 1] = excelCell;
                    }
                    catch
                    {
                        errorMessages += "Row " + (i - 1) + ", Column " + j + ". Invalid " + dt.Columns[j - 1].DataType.ToString().Replace("System.", "") + " value:  " + excelCell.ToString() + "<br>";
                    }
                }
            }

            //add the new row to the datatable
            dt.Rows.Add(row);
        }
    }

    //show error messages if needed
    Label1.Text = errorMessages;

    return dt;
}

El botón de formas web hace clic para realizar demostraciones.

protected void Button1_Click(object sender, EventArgs e)
{
    if (FileUpload1.HasFile)
    {
        DataTable dt = ExcelToDataTable(FileUpload1.FileBytes, CheckBox1.Checked);

        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
}

Answer 2

Esto es una mejora al genérico anterior. El uso es si tiene una clase con las siguientes propiedades, "Nombre", "Apellido", "Teléfono", "Fax" y tiene una hoja de Excel con la primera fila con los mismos nombres, cargará las filas de Excel en una objeto de clase y pop en una lista

public static List<T> GetClassFromExcel<T>(string path, int fromRow, int fromColumn, int toRow = 0, int toColumn = 0)
{
if (toColumn != 0 && toColumn < fromColumn) throw new          Exception("toColumn can not be less than fromColumn");
if (toRow != 0 && toRow < fromRow) throw new Exception("toRow can not be less than fromRow");
List<T> retList = new List<T>();
using (var pck = new ExcelPackage())
{
            using (var stream = File.OpenRead(path))
            {
                pck.Load(stream);
            }
            //Retrieve first Worksheet
            var ws = pck.Workbook.Worksheets.First();
            //If the to column is empty or 0, then make the tocolumn to the count of the properties
            //Of the class object inserted
            toColumn = toColumn == 0 ? typeof(T).GetProperties().Count() : toColumn;

            //Read the first Row for the column names and place into a list so that
            //it can be used as reference to properties
            Dictionary<string, int> columnNames = new Dictionary<string, int>();
            // wsRow = ws.Row(0);
            var colPosition = 0;
            foreach (var cell in ws.Cells[1, 1, 1, toColumn == 0 ? ws.Dimension.Columns : toColumn])
            {
                columnNames.Add(cell.Value.ToString(), colPosition);
                colPosition++;
            }
            //create a instance of T
            T objT = Activator.CreateInstance<T>();
            //Retrieve the type of T
            Type myType = typeof(T);
            //Get all the properties associated with T
            PropertyInfo[] myProp = myType.GetProperties();


            //Loop through the rows of the excel sheet
            for (var rowNum = fromRow; rowNum <= (toRow == 0? ws.Dimension.End.Row : toRow); rowNum++)
            {
                var wsRow = ws.Cells[rowNum, fromColumn, rowNum, ws.Cells.Count()];

                foreach (var propertyInfo in myProp)
                {
                    if (columnNames.ContainsKey(propertyInfo.Name))
                    {
                        int position = 0;
                        columnNames.TryGetValue(propertyInfo.Name, out position);
                        //int position = columnNames.IndexOf(propertyInfo.Name);
                        //To prevent an exception cast the value to the type of the property.
                        propertyInfo.SetValue(objT, Convert.ChangeType(wsRow[rowNum, position + 1].Value, propertyInfo.PropertyType));
                    }
                }

                retList.Add(objT);
            }

        }
        return retList;
    }

ahora puedes usar la lista como una fuente de enlace de datos si lo necesitas ... Un regalo mío para mí ... :) Daniel C. Vrey

Lo actualicé para toColumn para trabajar y agregué toRow y seguí las sugerencias de Andreas. Pulgares arriba para Andreas

Answer 3

Una versión de extensión de la respuesta de Tim Schmelter.

public static DataTable ToDataTable(this ExcelWorksheet ws, bool hasHeaderRow = true)
{
    var tbl = new DataTable();
    foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
        tbl.Columns.Add(hasHeaderRow ?
            firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
    var startRow = hasHeaderRow ? 2 : 1;
    for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
    {
        var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
        var row = tbl.NewRow();
        foreach (var cell in wsRow) row[cell.Start.Column - 1] = cell.Text;
        tbl.Rows.Add(row);
    }
    return tbl;
}

Answer 4

Ya veo, eso es lo que he publicado recientemente aquí (ahora corregido). Se puede mejorar ya que ExcelPackage y FileStream (de File.OpenRead ) no se eliminan después de su uso.

public static DataTable GetDataTableFromExcel(string path, bool hasHeader = true)
{
    using (var pck = new OfficeOpenXml.ExcelPackage())
    {
        using (var stream = File.OpenRead(path))
        {
            pck.Load(stream);
        }
        var ws = pck.Workbook.Worksheets.First();  
        DataTable tbl = new DataTable();
        foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
        {
            tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
        }
        var startRow = hasHeader ? 2 : 1;
        for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
        {
            var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
            DataRow row = tbl.Rows.Add();
            foreach (var cell in wsRow)
            {
                row[cell.Start.Column - 1] = cell.Text;
            }
        }
        return tbl;
    }
}

Answer 5

public static List<T> GetClassFromExcel<T>(string path, int fromRow, int fromColumn, int toRow = 0, int toColumn = 0) where T: class, new()
{
        if (toColumn != 0 && toColumn < fromColumn) throw new Exception("toColumn can not be less than fromColumn");
        if (toRow != 0 && toRow < fromRow) throw new Exception("toRow can not be less than fromRow");
        List<T> retList = new List<T>();
        using (var pck = new ExcelPackage())
        {
            using (var stream = File.OpenRead(path))
            {
                pck.Load(stream);
            }
            //Retrieve first Worksheet
            var ws = pck.Workbook.Worksheets.First();

            toColumn = toColumn == 0 ? typeof(T).GetProperties().Count() : toColumn; //If the to column is empty or 0, then make the tocolumn to the count of the properties Of the class object inserted

            //Read the first Row for the column names and place into a list so that
            //it can be used as reference to properties
            Dictionary<string, int> columnNames = new Dictionary<string, int>();
            // wsRow = ws.Row(0);
            var colPosition = 0;
            foreach (var cell in ws.Cells[1, 1, 1, toColumn == 0 ? ws.Dimension.Columns : toColumn])
            {
                columnNames.Add(cell.Value.ToString(), colPosition);
                colPosition++;
            }

            //Retrieve the type of T
            Type myType = typeof(T);

            //Get all the properties associated with T
            PropertyInfo[] myProp = myType.GetProperties();

            //Loop through the rows of the excel sheet
            for (var rowNum = fromRow + 1; rowNum <= (toRow == 0 ? ws.Dimension.End.Row : toRow); rowNum++) // fromRow + 1 to read from next row after columnheader
            {

                //create a instance of T
                //T objT = Activator.CreateInstance<T>();
                T objT = new T();

                // var wsRow = ws.Cells[rowNum, fromColumn, rowNum, ws.Cells.Count()]; //ws.Cells.Count() causing out of range error hence using ws.Dimension.Columns to get last column index 
                var wsRow = ws.Cells[rowNum, fromColumn, rowNum, ws.Dimension.Columns];
                foreach (var propertyInfo in myProp)
                {
                    var attribute = propertyInfo.GetCustomAttributes(typeof(DisplayNameAttribute), true).Cast<DisplayNameAttribute>().SingleOrDefault();
                    string displayName = attribute != null && !string.IsNullOrEmpty(attribute.DisplayName) ? attribute.DisplayName : propertyInfo.Name; // If DisplayName annotation not used then get property name itself                       
                    if (columnNames.ContainsKey(displayName))
                    {
                        int position = 0;                           
                        columnNames.TryGetValue(displayName, out position);
                        ////int position = columnNames.IndexOf(propertyInfo.Name);
                        ////To prevent an exception cast the value to the type of the property.
                        propertyInfo.SetValue(objT, Convert.ChangeType(wsRow[rowNum, position + 1].Value, propertyInfo.PropertyType));
                    }
                }                   
                retList.Add(objT);
            }

        }
        return retList;
    }
//IMPLEMENTATION DONE BY PLACING Code IT IN SEPARATE Helpers.CS file  and 
//Consuming it in this manner
List<CustomerExcelModel> records = 
Helpers.GetClassFromExcel<CustomerExcelModel>(filelocation, 1, 1);

Muchas gracias al usuario que ha enviado el código y a Andreas por su sugerencia. Aquí están los siguientes cambios realizados, soy nuevo en genéricos, así que perdónenme y corríjanme por cualquier error. Por favor, busquen el código modificado a continuación; podría ayudar a alguien

• Se agregó un modelo de entidad de Anotación de pantalla para mapear con el nombre de columna de Excel, de modo que también se pueda manejar el Nombre de columna con espacios.
• tenía el problema "T objT" ya que estaba fuera del ciclo for y por lo tanto hizo que el mismo valor se insertara repetidamente en la lista.
  instanciar el bucle interno, es decir, usar "nuevo T ()"
• Se corrigió el error de columna fuera de rango utilizando "ws.Dimension.Columns" para obtener el recuento de columnas, en lugar de ws.Cells.Count () ya que causaba un error de columna de rango
• para el bucle a través de los datos de la fila añadidos +1 a él, ya que RowNum = 1 estaba leyendo el nombre del encabezado, así que se hizo un cambio menor de "rowNum = fromRow + 1"

Answer 6

public static List<T> getClassFromExcel<T>(string path, int fromRow, int fromColumn, int toColumn = 0) where T : class
        {
            using (var pck = new OfficeOpenXml.ExcelPackage())
            {
                List<T> retList = new List<T>();

                using (var stream = File.OpenRead(path))
                {
                    pck.Load(stream);
                }
                var ws = pck.Workbook.Worksheets.First();
                toColumn = toColumn == 0 ? typeof(T).GetProperties().Count() : toColumn;

                for (var rowNum = fromRow; rowNum <= ws.Dimension.End.Row; rowNum++)
                {
                    T objT = Activator.CreateInstance<T>();
                    Type myType = typeof(T);
                    PropertyInfo[] myProp = myType.GetProperties();

                    var wsRow = ws.Cells[rowNum, fromColumn, rowNum, toColumn];

                    for (int i = 0; i < myProp.Count(); i++)
                    {
                        myProp[i].SetValue(objT, wsRow[rowNum, fromColumn + i].Text);
                    }
                    retList.Add(objT);
                }
                return retList;
            }
        }