用c# .net逐行读取excel数据

本文关键字：excel 数据 读取 逐行 net | 更新日期: 2023-09-27 18:10:29

有谁知道如何用c#逐行读取excel文件吗?

我找到了这段代码，它将从excel返回数据，并在c#中显示grindview。然而，我只是徘徊如何可能读取数据行逐行在服务器端，而不是?

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;    
using System.Data;
using System.Data.OleDb;
using System.IO;
namespace site
{
    public partial class pgTest : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        }
        protected void btnImport_Click(object sender, EventArgs e)
        {
            string connString = "";
            string strFileType = Path.GetExtension(fileuploadExcel.FileName).ToLower();
            string path = fileuploadExcel.PostedFile.FileName;
            //Connection String to Excel Workbook
            if (strFileType.Trim() == ".xls")
            {
                connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties='"Excel 8.0;HDR=Yes;IMEX=2'"";
            }
            else if (strFileType.Trim() == ".xlsx")
            {
                connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='"Excel 12.0;HDR=Yes;IMEX=2'"";
            }
            string query = "SELECT [username],[age],[phone] FROM [Sheet1$]";
            OleDbConnection conn = new OleDbConnection(connString);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            OleDbCommand cmd = new OleDbCommand(query, conn);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            grvExcelData.DataSource = ds.Tables[0];
            grvExcelData.DataBind();
            da.Dispose();
            conn.Close();
            conn.Dispose();
        }    
    }
}

用c# .net逐行读取excel数据

由于Excel使用范围，您应该首先获得您想要读取的单元格范围。之后，您现在可以使用for循环浏览它们。你可以看到下面的例子:

    Excel.Application xlApp = new Excel.Application();
    Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:'myexcel.xlsx");
    Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
    Excel.Range xlRange = xlWorksheet.UsedRange;
    int rowCount = xlRange.Rows.Count;
    int colCount = xlRange.Columns.Count;
    for (int i = 1; i <= rowCount; i++)
    {
        for (int j = 1; j <= colCount; j++)
        {
            MessageBox.Show(xlRange.Cells[i, j].Value2.ToString());
        }
    }

关于这个代码块的更详细的解释可以在这里找到。

您可以使用以下OleDbDataReader 

using (OleDbConnection connection = new OleDbConnection(connectionString))
{
    OleDbCommand command = new OleDbCommand(queryString, connection);
    connection.Open();
    OleDbDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        var val1= reader[0].ToString();
    }
    reader.Close();
}

你必须试试这个

        string connectionString = "";
        string strFileType = "Type";
        string path = @"C:'Users'UserName'Downloads'";
        string filename = "filename.xls";
        if (fielname.Contains(.xls))
        {
            connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + filename + ";Extended Properties='"Excel 8.0;HDR=Yes;IMEX=2'"";
        }
        else if (fielname.Contains(.xlsx)
        {
            connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + filename + ";Extended Properties='"Excel 12.0;HDR=Yes;IMEX=2'"";
        }

        string query = "SELECT * FROM [SheetName$]";
        using (OleDbConnection connection = new OleDbConnection(connectionString))
        {
            OleDbCommand command = new OleDbCommand(query, connection);
            connection.Open();
            OleDbDataReader reader = command.ExecuteReader();
            var lines = new List<string>();
            while (reader.Read())
            {
                var fieldCount = reader.FieldCount;
                var fieldIncrementor = 1;
                var fields = new List<string>();
                while (fieldCount >= fieldIncrementor)
                {
                    fields.Add(reader[fieldIncrementor - 1].ToString());
                    fieldIncrementor++;
                }
                lines.Add(string.Join("'t", fields));
            }
            reader.Close();
        }

我尝试了OleDbConnection的解决方案，但它没有工作，因为我没有安装的东西。然后我在这里找到了这个解决方案，它像魅力一样工作:

https://www.codeproject.com/Tips/801032/Csharp-How-To-Read-xlsx-Excel-File-With-Lines-of

在那里你可以下载一个小文件excel .dll，将它添加到你的项目中，并逐个单元浏览excel文件。

将数据表转换为可枚举对象的简单方法是使用Json方法。

举个例子，用简单的方法，只使用。net库，你就可以把数据表转换成特定对象的列表。

        using System.Data;
    
        private static IEnumerable<T> ConvertDataTable<T>(DataTable dataTable)
        {
            if (dataTable is null ||
                !dataTable.AsEnumerable().Any())
            {
                return Enumerable.Empty<T>();
            }
    
            var data = dataTable.Rows.OfType<DataRow>()
                .Select(row => dataTable.Columns.OfType<DataColumn>()
                    .ToDictionary(col => col.ColumnName, c => row[c]));
            
            var jsonTextObject = System.Text.Json.JsonSerializer.Serialize(data);
    
            return System.Text.Json.JsonSerializer.Deserialize<IEnumerable<T>>(jsonTextObject)
                ?? Enumerable.Empty<T>();
        }
相关文章：