如果Excel工作表名称包含空格字符，即“人员详细信息”，如何从Excel工作表获取列 [英] how to get column from Excel Sheet,if Excel sheet name contain space character i.e."Person Details"

问题描述

在这里，我使用ADO.Net通过可以正常工作的指定connectionString连接到Excel文件，我唯一关心的是工作表名称是否具有空格字符，而不是其不返回相应工作表的列名称。
我的代码：

here i m using ADO.Net for connect to Excel file using specified connectionString that work fine, my only concern is if sheet name has space character than its not return column names form corresponding sheet. my code :

        if (extension == ".xls")
        {
            connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
            "Data Source=" + Server.MapPath("~\\ExcelUpload\\Excelsheets\\temp.xls") + ";" +
            @"Extended Properties=" + "\"Excel 8.0;HDR=YES;\"";
        }
        else if (extension == ".xlsx")
        {
            connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
            "Data Source=" + Server.MapPath("~\\ExcelUpload\\Excelsheets\\temp.xlsx") + ";" +
            @"Extended Properties=" + "\"Excel 12.0;HDR=YES;\"";
        }


        DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
        DbConnection connection = factory.CreateConnection();
        connection.ConnectionString = connectionString;
        connection.Open();

        DataTable dt;
        String[] restrection = { null, null, sheetName + "$", null };
        dt = connection.GetSchema("Columns", restrection);

那么我们如何将具有空格字符的excelsheet名称停顿下来？

so how can we paas excelsheet name that has space character?

推荐答案

尝试

String[] restrictions = { null, null, "['" + sheetName + "$']", null };

因此，如果您有工作表TEST WORKSHEET，则成为 TEST WORKSHEETHEET $。

So if you have a worksheet TEST WORKSHEET it became 'TEST WORKSHEET$'

为什么我们必须遵循这种更奇怪的命名约定

It's escapes me why we have to follow such WIERD naming conventions

这篇关于如果Excel工作表名称包含空格字符，即“人员详细信息”，如何从Excel工作表获取列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！