如何使用c＃将Excel数据转换为数据表而不使用共享点excel服务进行循环？ [英] How to convert Excel data to datatable without looping from share point excel service using c#?

问题描述

我通过在sharepoint 2010中使用Excel服务，从excel文件（sales.xlsx）获得指定范围的数据。我必须将此excel服务数据转换为数据表而不在c＃中循环。

请参考http://stackoverflow.com/questions/18888870/how-to-convert-excel-service-data-with-ranges-to-datatable-from-share-point-2010

我该怎么做？

解决方案

你可以在程序中使用ACE提供程序，并调用程序：



如何将数据从SQL导出到Excel以及标题和表属性 [ ^ ]

http://www.excel-sql-server.com /excel-import-to-sql-server-using-distributed-queries.htm [ ^ ]



请注意

从lasttable中选择*到newtable 

是你想要的好方法。

< blockquote>

 private DataTable CopyExceltoDatatable（string path，string filename）
 {
 string sheetname =; 
 DataTable dtexcel = new DataTable（）; 
 string excelConnString =Provider = Microsoft.ACE.OLEDB.12.0; OLE DB Services = -4; Data Source =+ path +\\+ filename +; Excel 12.0; HDR = Yes; IMEX = 1\" ; 
使用（OleDbConnection excelConnection = new OleDbConnection（excelConnString））
 {
 excelConnection.Open（）; 
 //创建OleDbCommand以从Excel获取数据
 DataTable Sheets = excelConnection.GetOleDbSchemaTable（OleDbSchemaGuid.Tables，null）; 
 DataRow schemaRow = Sheets.Rows [0]; 
 sheetname = schemaRow [TABLE_NAME]。ToString（）; 
 if（！sheetname.EndsWith（_））
 {
 string query =SELECT * FROM [+ sheetname +]; 
 OleDbDataAdapter daexcel = new OleDbDataAdapter（query，excelConnection）; 
 daexcel.Fill（dtexcel）; 
} 
返回dtexcel; 
} 
} 

Hi,
I have data with specified range from excel file(sales.xlsx) by using Excel service in sharepoint 2010.I have to convert this excel service data to datatable without looping in c#.
please refer http://stackoverflow.com/questions/18888870/how-to-convert-excel-service-data-with-ranges-to-datatable-from-share-point-2010"
How do I do this?

解决方案

Your can use ACE provider in a procedure, and call the procedure :

How to export data from SQL to excel along with headers and table attributes[^]
http://www.excel-sql-server.com/excel-import-to-sql-server-using-distributed-queries.htm[^]

please be noted

select * into newtable from lasttable

is a good way for what you want.

private DataTable CopyExceltoDatatable(string path, string filename)
    {
        string sheetname="";
        DataTable dtexcel = new DataTable();
        string excelConnString = "Provider=Microsoft.ACE.OLEDB.12.0;OLE DB Services=-4;Data Source=" + path +"\\"+filename + ";Excel 12.0;HDR=Yes;IMEX=1";
        using (OleDbConnection excelConnection = new OleDbConnection(excelConnString))
        {
            excelConnection.Open();
            //Create OleDbCommand to fetch data from Excel 
             DataTable Sheets = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
             DataRow schemaRow = Sheets.Rows[0];
             sheetname = schemaRow["TABLE_NAME"].ToString();
                if (!sheetname.EndsWith("_"))
                {
                    string query = "SELECT  * FROM [" + sheetname + "]";
                    OleDbDataAdapter daexcel = new OleDbDataAdapter(query, excelConnection);
                    daexcel.Fill(dtexcel);
                }
                return dtexcel;
        }
    }

这篇关于如何使用c＃将Excel数据转换为数据表而不使用共享点excel服务进行循环？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！