Excel to Datatable Using Epplus - Excel Locked for Editing

Excel to DataTable using EPPlus - excel locked for editing

I see, that's what i've posted recently here(now corrected). It can be improved since the ExcelPackage and the FileStream(from File.OpenRead) are not disposed after using.

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

Import a CSV or XLSX Into A DataTable Using EPPLus

For work with csv, you must use LoadFromText. Also note that EPPlus can not work with xls, it can read xlsx and xlsm and write xlsx.

char csvDelimiter = ';';
using(var pck = new ExcelPackage())
{
    ExcelWorksheet ws = null;
    if(path.EndsWith(".csv"))
    {
        ws = pck.Workbook.Worksheets.Add("Sheet1");
        ExcelTextFormat format = new ExcelTextFormat()
        {
            Delimiter = csvDelimiter 
        };
        ws.Cells[1, 1].LoadFromText(File.ReadAllText(path), format);
    }
    else
    {
        using (var stream = File.OpenRead(path))
        {
            pck.Load(stream);
        }
        ws = pck.Workbook.Worksheets.First();
    }

    //The rest of your code

}

Another option is to use ExcelDataReader, as it can read xls, xlsx and csv, but cannot write. It has an extension that converts a file to a DataSet

How to delete row with locked cells using EPPLUS

You can achieve that by inserting macro in your sheet:

as answered here

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address Like "$" & Target.Row & ":$" & Target.Row Then
ActiveSheet.Unprotect
Else
ActiveSheet.Protect
End If
End Sub

Using Epplus after you do whatever operations on your file just add the following:

string code = "Private Sub Worksheet_SelectionChange(ByVal 

Target As Range)\n" +
"If Target.Address Like \"$\" & Target.Row & \":$\" & Target.Row Then\n" +
"ActiveSheet.Unprotect\n" +
"Else\n" +
"ActiveSheet.Protect\n" +
"End If\n" +
"End Sub\n" ;
package.Workbook.CreateVBAProject();
worksheet.CodeModule.Code = code;

The above code will protect your sheet and still allow user to delete rows.

---

Related Topics