我正在阅读excel，我首先查询了我的表（没有记录）并得到了Datable。 我查询我的表以使用别名获取我的Excel工作表中的列名称。
var dal = new clsConn(); var sqlQuery = "SELECT FETAPE_THEIR_TRANDATE \"Date\" ,ISSUER Issuer, ISSU_BRAN Branch , STAN_NUMB STAN, TERMID TermID, ACQUIRER Acquirer,DEBIT_AMOUNT Debit,CREDIT_AMOUNT Credit,CARD_NUMB \"Card Number\" , DESCRIPTION Description FROM ALLTRANSACTIONS"; var returntable = dal.ReadData(sqlQuery); DataRow ds = returntable.NewRow(); var dtExcelData = returntable;所以我的数据表看起来像这样，
然后我从excel表中读取记录
OleDbConnection con = null; if (ext == ".xls") { con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;"); } else if (ext == ".xlsx") { con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=\"Excel 12.0;IMEX=2;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\""); } con.Open(); dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string getExcelSheetName = dt.Rows[0]["Table_Name"].ToString(); //OleDbCommand ExcelCommand = new OleDbCommand(@"SELECT * FROM [" + getExcelSheetName + @"]", con); OleDbCommand ExcelCommand = new OleDbCommand("SELECT F1, F2, F3, F4, F5,F6,F7,F8,F9,F10 FROM [Sheet1$]", con); OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand); try { ExcelAdapter.Fill(dtExcelData); //Here I give the datatable which i made previously } catch (Exception ex) { //lblAlert2.CssClass = "message-error"; //lblAlert2.Text = ex.Message; }它成功读取并填充数据表中的数据但在数据表中创建自己的列，如F1到F10如何将此数据移动到与数据表中定义的列完全匹配
我将如何管理这个不创建其他列（f1，f2..f10）任何解决方法将是明显的或请解释我做错了什么，我怎样才能实现这一目标。
更新：我的Excel文件如下所示
I am reading excel having like million of records first i query my table (no records) and get Datable. i query my table to get columns name as define in my excel sheet using alias.
var dal = new clsConn(); var sqlQuery = "SELECT FETAPE_THEIR_TRANDATE \"Date\" ,ISSUER Issuer, ISSU_BRAN Branch , STAN_NUMB STAN, TERMID TermID, ACQUIRER Acquirer,DEBIT_AMOUNT Debit,CREDIT_AMOUNT Credit,CARD_NUMB \"Card Number\" , DESCRIPTION Description FROM ALLTRANSACTIONS"; var returntable = dal.ReadData(sqlQuery); DataRow ds = returntable.NewRow(); var dtExcelData = returntable;So my datatable looks like this,
Then i read records from excel sheet
OleDbConnection con = null; if (ext == ".xls") { con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;"); } else if (ext == ".xlsx") { con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=\"Excel 12.0;IMEX=2;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\""); } con.Open(); dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string getExcelSheetName = dt.Rows[0]["Table_Name"].ToString(); //OleDbCommand ExcelCommand = new OleDbCommand(@"SELECT * FROM [" + getExcelSheetName + @"]", con); OleDbCommand ExcelCommand = new OleDbCommand("SELECT F1, F2, F3, F4, F5,F6,F7,F8,F9,F10 FROM [Sheet1$]", con); OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand); try { ExcelAdapter.Fill(dtExcelData); //Here I give the datatable which i made previously } catch (Exception ex) { //lblAlert2.CssClass = "message-error"; //lblAlert2.Text = ex.Message; }It reads successfully and fill data in datatable but creating its own column in data table like F1 to F10 how can i move this data to exactly match with my defined columns in datatable
How Will i manage this to not create other columns (f1,f2..f10) any workaround will be appreciable or Please explain what i am doing wrong and how can i achieve this.
UPDATE : My Excel file looks like this
最满意答案
Microsoft.ACE.OLEDB.12.0驱动程序将处理两种类型的Excel电子表格并使用相同的扩展属性。 即“Excel 12.0”将打开.xls和.xlsx。
保留HDR = NO，因为OLEDB期望它们在第一行，它们实际上在第11行。
遗憾的是，“TypeGuessRows = 0; ImportMixedTypes = Text”被Microsoft.ACE.OLEDB.12.0完全忽略，你必须使用注册表（yuk）。 将IMEX = 2更改为IMEX = 1，以确保将混合数据类型视为文本处理。
改回使用“Select * From [Sheet1 $]”然后我就会发现你将不得不手动处理源数据。
OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO\""); con.Open(); DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string getExcelSheetName = (string)dt.Rows[0]["Table_Name"]; DataTable xlWorksheet = new DataTable(); xlWorksheet.Load(new OleDbCommand("Select * From [" + getExcelSheetName + "]", con).ExecuteReader()); //More than 11 rows implies 11 header rows and at least 1 data row if (xlWorksheet.Rows.Count > 11 & xlWorksheet.Columns.Count >= 10) { for (int nRow = 11; nRow < xlWorksheet.Rows.Count; nRow++) { DataRow returnRow = returntable.NewRow(); for (int nColumn = 0; nColumn < 10; nColumn++) { //Note you will probably get conversion problems here that you will have to handle returnRow[nColumn] = xlWorksheet.Rows[nRow].ItemArray[nColumn]; } returntable.Rows.Add(returnRow); } }我猜你只是想将excel数据添加到你的ALLTRANSACTION表中？ 你没有指明，但似乎可能的结果。 如果是这样，这是一种可怕的方式。 您不需要将整个表读入内存附加数据，然后更新数据库。 您需要做的就是读取excel文件并将数据插入Oracle表。
一些想法，你的可返回将包含数据，所以如果你只是想要表的结构然后在Select语句中添加“Where RowNum = 0”。 要将数据添加到Oracle数据库，您可以1）转换为使用Oracle数据提供程序（ODP），然后使用OracleBulkCopy类或2）只需修改上述内容即可在读取数据时逐行插入。 只要您的Excel电子表格中没有大量数据，它就可以正常工作。 说过一百万行很多，所以也许不是最好的选择。 您需要验证输入，因为Excel不是真正的最佳数据源。
The Microsoft.ACE.OLEDB.12.0 driver will handle both types of excel spreadsheets and using the same Extended Properties. i.e. "Excel 12.0" will open both .xls and .xlsx.
Leave the HDR=NO as OLEDB expects them in the first row and they are actually in row 11.
Sadly "TypeGuessRows=0;ImportMixedTypes=Text" is completely ignored by Microsoft.ACE.OLEDB.12.0, you've got to play around with the registry (yuk). Change your IMEX=2 to IMEX=1 to ensure that mixed data types as handled as text.
Change back to using "Select * From [Sheet1$]" and then I'm afriad that you are going to have to handle the source data manually.
OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO\""); con.Open(); DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string getExcelSheetName = (string)dt.Rows[0]["Table_Name"]; DataTable xlWorksheet = new DataTable(); xlWorksheet.Load(new OleDbCommand("Select * From [" + getExcelSheetName + "]", con).ExecuteReader()); //More than 11 rows implies 11 header rows and at least 1 data row if (xlWorksheet.Rows.Count > 11 & xlWorksheet.Columns.Count >= 10) { for (int nRow = 11; nRow < xlWorksheet.Rows.Count; nRow++) { DataRow returnRow = returntable.NewRow(); for (int nColumn = 0; nColumn < 10; nColumn++) { //Note you will probably get conversion problems here that you will have to handle returnRow[nColumn] = xlWorksheet.Rows[nRow].ItemArray[nColumn]; } returntable.Rows.Add(returnRow); } }I'm guessing you simply want to add the excel data into your ALLTRANSACTION table? You don't specify but it seems the likely outcome of this. If so this is a terrible way to do it. You don't need to read the whole table into memory append data and then update the database. All you need to do is read the excel file and insert the data to the Oracle table.
Some thoughts, your returntable will contain data so if you just want the structure of the table then add a "Where RowNum=0" to the Select statement. To add the data to your Oracle Database you could 1) Convert to using the Oracle Data Provider (ODP) and then use using OracleBulkCopy Class or 2) simply modify the above to insert row by row as you read the data. As long as you don't have a LOT of data in your Excel spreadsheet it will work just fine. Having said that a Million rows is a LOT so perhaps not the best option. You will need to validate the input as Excel is not the best data source really.
更多推荐
发布评论