在 C# 中读取 Excel，其中某些列为空 [英] Reading Excel in c# where some columns are empty

问题描述

我有一个这样的 Excel 模板

I have an Excel template like this

我在阅读本文时遇到了一些问题(我无法使用 3rd 方库).我的解决方案:

and I have some problems reading this (I can't use 3rd-party libraries). My solution:

public partial class CaricaDocumento : System.Web.UI.Page
{

    static string HDR; // "Yes" indicates that the first row contains column names, not data
    Regex regex = new Regex("([0-9]+)(.*)");


    protected void Page_Load(object sender, EventArgs e)
    {
        string ind = "C:\My_Template.xlsx";
        string sheetName = "Page1";

        DataTable dt = FromXLSX(ind, sheetName, true); 

        DataToView(dt);
    }


    // Bind data to the page
    private void DataToView(DataTable dt)
    {
        LblFattura.Text = GetValue("AO10", dt);
        LblDataFattura.Text = GetValue("AX10", dt);
        LblCognomeOrRagioneSociale.Text = GetValue("B18", dt);
        LblNome.Text = GetValue("AB18", dt);
    }


    // return the value from the cell, indicate a code like "A1", "B3", "AO10"...
    public string GetValue(string codeCell, DataTable dt)
    {
        string[] substrings = regex.Split(codeCell);
        string letterString = substrings[0]; // 'A' or 'B' ... 'AZ' ... 
        int letter = ColumnLetterToNumber(letterString); // transform the letter in a column index

        int num = 1;
        if (HDR == "Yes")
            num = 2;

        // if the first row is an header, do -2
        // if the first row is a simple data row, do -1
        int number = Int32.Parse(substrings[1]) - num; // the right row index
        return dt.Rows[number][letter].ToString();
    }


    //  transform the letter in a column index
    public static int ColumnLetterToNumber(string columnName)
    {
        if (string.IsNullOrEmpty(columnName)) throw new ArgumentNullException("columnName");

        columnName = columnName.ToUpperInvariant();

        int sum = 0;
        for (int i = 0; i < columnName.Length; i++)
        {
            sum *= 26;
            char letter = columnName[i];
            sum += (letter - ('A' - 1));
        }
        sum--;
        return sum;
    }


    // return the DataTable
    public static DataTable FromXLSX(string filePath, string sheet, bool hasHeaders)
    {
        try
        {
            // Create the new datatable.
            DataTable dtexcel = new DataTable();

            // Define the SQL for querying the Excel spreadsheet.
            HDR = hasHeaders ? "Yes" : "No"; // "HDR=Yes;" indicates that the first row contains column names, not data
            string IMEX = "1";

            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath 
                    + ";Extended Properties="Excel 12.0;HDR=" + HDR + ";IMEX=" + IMEX + ";"";

            // Create connection:
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();

            if (!sheet.EndsWith("_"))
            {
                // Query data from the sheet
                string query = "SELECT  * FROM [" + sheet + "$]";
                OleDbDataAdapter daexcel = new OleDbDataAdapter(query, conn);
                dtexcel.Locale = CultureInfo.CurrentCulture;

                // Fill the datatable:
                daexcel.Fill(dtexcel);
            }

            // Close connection.
            conn.Close();

            // Set the datatable.
            return dtexcel;
        }
        catch { throw; }
    }
}

但是我注意到了这个问题:如果数据不是从'A'列开始，DataTable 会从带有数据的第一列读取数据！是索引的噩梦.例如:

But I have noticed this issue: if datas don't start from the column 'A', the DataTable read data from the first column with data! Is a nightmare for the indexes. For example:

...在这种情况下，列 'A' 被忽略(数据表从 'B' 开始获取数据)并且这使单元格代码的使用无效(如A1"、B5"、AO11"...) 因为方法 ColumnLetterToNumber(string columnName) 被扭曲了.

... in this case the column 'A' is ignored (the DataTable takes datas starting from 'B') and this invalidates the use of cell codes (like "A1", "B5", "AO11"...) because the method ColumnLetterToNumber(string columnName) is distorted.

有人知道我如何强制 DataTable 从A"列开始获取数据?或者使用单元格代码从 Excel 获取数据的其他方法?

Someone knows how I can impose that the DataTable gets datas starting from the 'A' column? Or alternative ways to get data from Excel using cell codes?

推荐答案

您可以使用此查询:

string query = "SELECT NULL AS EmptyColumn, * FROM [" + sheet + "$]";

这篇关于在 C# 中读取 Excel，其中某些列为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！