从Excel文件中读取数据

本文关键字：读取 数据 文件 Excel | 更新日期: 2023-09-27 18:16:34

我搜索了官方文档，但它专注于创建Excel文件，而不是阅读。任何代码片段作为一个例子将帮助我!

如何读取Excel数据?

COMPANY     | DOCUMENT  | 
COMPANYONE  | 123455986 | 
COMPANYTWO  | 123455986 |
COMPANYTHREE| 123455986 |

从Excel文件中读取数据

这是一个读取csv和excel数据并返回数据表的方法

说明-通过nuget安装ExcelDataReader 

using Excel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO; 
using System.Text; 
public static DataTable LoadDataTable(string filePath)
        {
            string fileExtension = Path.GetExtension(filePath);
            switch (fileExtension.ToLower())
            {
                case ".xlsx":
                    return ConvertExcelToDataTable(filePath, true);
                case ".xls":
                    return ConvertExcelToDataTable(filePath);
                case ".csv":
                    return ConvertCsvToDataTable(filePath);
                default:
                    return new DataTable();
            }
        }

public static DataTable ConvertExcelToDataTable(string filePath, bool isXlsx = false)
        {
            FileStream stream = null;
            IExcelDataReader excelReader = null;
            DataTable dataTable = null;
            stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
            excelReader = isXlsx ? ExcelReaderFactory.CreateOpenXmlReader(stream) : ExcelReaderFactory.CreateBinaryReader(stream);
            excelReader.IsFirstRowAsColumnNames = true;
            DataSet result = excelReader.AsDataSet();
            if (result != null && result.Tables.Count > 0)
                dataTable = result.Tables[0];
            return dataTable;
        }
public static DataTable ConvertCsvToDataTable(string filePath)
        {
            DataTable dt = new DataTable();
            using (StreamReader sr = new StreamReader(filePath))
            {
                string[] headers = sr.ReadLine().Split(',');
                foreach (string header in headers)
                {
                    dt.Columns.Add(header);
                }
                while (!sr.EndOfStream)
                {
                    string[] rows = sr.ReadLine().Split(',');
                    DataRow dr = dt.NewRow();
                    for (int i = 0; i < headers.Length; i++)
                    {
                        dr[i] = rows[i];
                    }
                    dt.Rows.Add(dr);
                }
            }
            return dt;
        }

或者使用Excel Interop:

using Microsoft.Office.Interop.Excel;

…

        Microsoft.Office.Interop.Excel.Application xl = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook workbook = xl.Workbooks.Open(@"C:'test.xlsx");
        Microsoft.Office.Interop.Excel.Worksheet sheet = workbook.Sheets[1];

        int numRows = sheet.UsedRange.Rows.Count;
        int numColumns = 2;     // according to your sample
        List<string[]> contents = new List<string[]>();
        string [] record = new string[2];
        for (int rowIndex = 1; rowIndex <= numRows; rowIndex++)  // assuming the data starts at 1,1
        {
            for (int colIndex = 1; colIndex <= numColumns; colIndex++)
            {
                Range cell = (Range)sheet.Cells[rowIndex, colIndex];
                if (cell.Value != null)
                {
                    record[colIndex-1] = Convert.ToString( cell.Value);
                }
            }
            contents.Add(record);
        }
        xl.Quit();
        Marshal.ReleaseComObject(xl);

我之前也遇到过同样的问题。

我使用这个解决方案从一个Excel文件(.xls)在ASP中读取数据。. NET: http://www.dotnetcurry.com/ShowArticle.aspx?ID=138。

只需通过SQL读取范围并将其发布到网页上的GridView。

对于一个没有经验的ASP来说，它看起来更容易理解。NET/C #开发商。

相关文章：