如何将stream excel文件转换为datatable C＃？ [英] How convert stream excel file to datatable C#?

问题描述

我使用Epplus从流中读取xlsx文件。

I use Epplus to reading xlsx files from stream.

它有一个错误，它不能读取我的工作簿中的一些列。如何从流读取xlsx文件datatable没有epplus？

It has a bug , it cant read some columns in my workbook.How can read xlsx files from stream to datatable without epplus ?

我的旧代码：

 public static DataSet ReadExcelFile(Stream stream)
    {
        try
        {
            //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
            IExcelDataReader excelReader =    
                             ExcelReaderFactory.CreateOpenXmlReader(stream);
            //...
            DataSet result = excelReader.AsDataSet();

            return result;

        }
        catch (Exception x)
        {
            throw x;
        }
    }

我没有报告，但我尝试了这么多组合。如果工作表中有空列，则epplus读取器无法正确读取列值。

I didnt report it, but i tried so much combinations.If there are empty columns in worksheet ,epplus reader cant read correctly column values.

推荐答案

它有一个错误，它不能读取我的工作簿中的一些列

你可以描述错误，你有已经报告了，还是已经知道你在使用什么版本？

Can you describe the bug, have you reported it or is it already known, what version are you using?

这是一个简单的方法，将Excel文件加载到EPPlus的 DataTable 中。

Here's a simple approach to load an excel file into a DataTable with EPPlus.

public static DataTable getDataTableFromExcel(string path)
{
    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();
        bool hasHeader = true; // adjust it accordingly( i've mentioned that this is a simple approach)
        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);
        }
        return tbl;
    }
}

这篇关于如何将stream excel文件转换为datatable C＃？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！