将数据读入数据表 [英] Read data into datatable

问题描述

我觉得我的问题更多的是关于C＃，虽然它涉及Excel，但它不是VBA问题。

我不知道为什么我的一些线程已经转移到Excel论坛（我觉得那里的观众少得多），也许我应该从主题标题中删除关键字Excel。

这是我的 。

我们可以得到上面的数据表

请注意，ListBox显示了不需要的列名和数据类型，但是如果您想要遍历列，可能是一个超出此问题范围的映射操作，我已经在企业解决方案中完成了多年前。

I feel that my question is more about C#, although it deals with Excel, but it is not VBA question.

I don't know why some of my threads had been moved to Excel forum (I feel there are much fewer viewers there), maybe I should remove keyword Excel from thread title.

Here is my original thread, which was posted here then moved to Excel forum. How should I incorporate below logic into the sample code. It is just the logic I want to go with.

Maybe add messagebox for error message.

Thank you very much!

int C1SK = 0; //Store_key
int C2MN = 0; //region_key
int C3TC = 0; //store_name
int C4VT = 0; //store_type
int C5TL = 0; //street_address
int C6VN = 0; //city
int C7VA = 0; //state_province

for (int i=1; i <= colCount; i++)
{

    if ((string)(xlWorksheet.Cells[1, i]) == "Store_key")
        C1SK = i;
    else if ((string)(xlWorksheet.Cells[1, i]) == "region_key")
        C2MN = i;
    else if ((string)(xlWorksheet.Cells[1, i]) == "store_name")
        C3TC = i;
    else if ((string)(xlWorksheet.Cells[1, i]) == "store_type")
        C4VT = i;
    else if ((string)(xlWorksheet.Cells[1, i]) == "street_address")
        C5TL = i;
    else if ((string)(xlWorksheet.Cells[1, i]) == "city")
        C6VN = i;
    else if ((string)(xlWorksheet.Cells[1, i]) == "state_province")
        C7VA = i;


}

table.Columns.Add("Store_key", typeof(int));
table.Columns.Add("region_key", typeof(int));
table.Columns.Add("store_name", typeof(string));
table.Columns.Add("store_type", typeof(string));
table.Columns.Add("street_address", typeof(string));
table.Columns.Add("city", typeof(string));
table.Columns.Add("state_province", typeof(string));


//table.Columns.Add("ID", typeof(int));

for (int i = 2; i <= rowCount; i++)
{
    

table.Rows.Add(xlRange.Cells[i, C1SK].Value, xlRange.Cells[i, C2MN].Value, xlRange.Cells[i, C3TC].Value, xlRange.Cells[i, C4VT].Value, xlRange.Cells[i, C5TL].Value, xlRange.Cells[i, C6VN]Value, xlRange.Cells[i, C7VA].Value);

解决方案

Hello,

An easy way to read sheet data such as shown is via OleDb. In this case given

VS2017 source for below.

We can get the above into a DataTable

Note the ListBox shows column names and data types that is not required but can be useful if you do want to traverse columns for perhaps a mapping operation that is out of scope of this question which I've done in enterprise solutions years ago.

这篇关于将数据读入数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！