Excel中的数据表使用EPPlus - Excel的编辑锁定 [英] Excel to DataTable using EPPlus - excel locked for editing

问题描述

我用下面的code到Excel转换到DataTable使用EPPlus：

I'm using the following code to convert an Excel to a datatable using 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;
}

它创建Excel中，但是，当我尝试打开它，它给了我，它是由其他用户锁定用于编辑的消息，我只能以只读方式打开它。

It creates the Excel, however, when I try to open it, it gives me the message that it is locked for editing by another user and that I can only open it in Read-Only mode.

我想用：

pck.Dispose();

就解决了问题，但我仍然得到同样的错误。

would solve the issue, however I'm still getting the same error.

此外，当我尝试删除该文件，我得到的消息：无法完成的操作，因为该文件是在WebDev.WebServer40.EXE打开

Also, when I try to delete the file, I get the message: The action can't be completed because the file is open in WebDev.WebServer40.EXE.

任何想法如何解决这个问题？
提前致谢。 ：）

Any ideas how to resolve this? Thanks in advance. :)

推荐答案

我明白了，这就是我最近张贴在这里（现予以更正）。它可以因为 ExcelPackage 和的FileStream （从 File.OpenRead ）不使用处理后。

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

这篇关于Excel中的数据表使用EPPlus - Excel的编辑锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！