在Csharp中使用oledb conn在文本框中导入Excel数据 [英] Import Excel data in textbox using oledb conn in Csharp

问题描述

我想使用oledb连接在richtextbox/textbox中导入excel数据可以提前1个帮助.... thnks.

i want import excel data in richtextbox/textbox using oledb connection can any 1 help.... thnks in advance.

推荐答案

我的应用程序以以下方式

I have done this in one of my application in following way

private void button1_Click(object sender, EventArgs e)
        {
            try
            {

                string fname = SelectTextFile("Excel File");
                if (fname == null || string.IsNullOrEmpty(fname.Trim()))
                {
                    MessageBox.Show("No Excel Sheet is selected to upload.");
                }
                else
                {
                    string ifname = fname.Substring(fname.LastIndexOf(''\\'') + 1);
                    string[] filename = ifname.Split(''.'');
                    getDataFromXLS(fname);
                    StringBuilder sb = new StringBuilder();
                    int i = 0;
//Putting Data in String Builder
                    foreach (DataRow dr in dtCSV.Rows)
                    {
                        for (int k = 0; k < dtCSV.Columns.Count; k++)
                        {
                            sb.Append(dr.ItemArray.GetValue(k).ToString() + ",");
                        }
                        sb.Append("\n");
                        i++;
                    }
//Write RichTextBox
richTextBox1.Text = sb.ToString();
                }

            }
        }
        private string SelectTextFile(string strTitle)
        {
            OpenFileDialog dialog = new OpenFileDialog();
            dialog.Filter = "xls file (*.xls)|*.xls |xlsx file(*.xlsx)|*.xlsx|all file(*.*)|*.*";
            dialog.Title = strTitle; //"Select a lead";

            return (dialog.ShowDialog() == DialogResult.OK)
               ? dialog.FileName : null;

        }
        private void getDataFromXLS(string strFilePath)
        {
            string strConn = string.Empty;
            try
            {
                if (System.IO.Path.GetExtension(strFilePath).Equals(".xlsx"))
                {
                    strConn = "provider=Microsoft.ACE.OLEDB.12.0;Data Source=''" + strFilePath + "'';Extended Properties=Excel 12.0;";
                }
                else
                {
                    strConn = "provider=Microsoft.Jet.OLEDB.4.0;Data Source=''" + strFilePath + "'';Extended Properties=Excel 8.0;";
                }
                OleDbConnection cnCSV = new OleDbConnection(strConn);
                cnCSV.Open();
                System.Data.DataTable dtsheetName = new System.Data.DataTable();
                dtsheetName = cnCSV.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string excelSheets = string.Empty;
                int i = 0;
                foreach (DataRow row in dtsheetName.Rows)
                {
                    excelSheets = row["TABLE_NAME"].ToString();
                    break;
                }

                OleDbCommand cmdSelect = new OleDbCommand("SELECT * FROM [" + excelSheets + "]", cnCSV);
                OleDbDataAdapter daCSV = new OleDbDataAdapter();
                daCSV.SelectCommand = cmdSelect;
                dtCSV.Clear();
                daCSV.Fill(dtCSV);
                richTextBox1.Text = dtCSV.ToString();
                cnCSV.Close();
                daCSV.Dispose();
                cmdSelect.Dispose();
            }
            catch (Exception ex)
            {
                string Message = ex.Message.ToString();
                MessageBox.Show(Message);
            }
        }

这可能会对您有所帮助.

This may help you.

尝试一下:

try this:

int Count = 0;
string myconnection = @"provider=Microsoft.Jet.OLEDB.4.0; data source=c:/test.xls;Extended Properties=''Excel 8.0;IMEX=1'';";
OleDbConnection Connection = new OleDbConnection(myconnection);
OleDbCommand mycommand = new OleDbCommand("Select * FROM [ADJUSTMENT

", Connection); Connection.Open(); DbDataReader dr = mycommand.ExecuteReader(); while (dr.Read()) { if(Count == 2) txtTest.Text = dr[0].ToString Count += 1; }

", Connection); Connection.Open(); DbDataReader dr = mycommand.ExecuteReader(); while (dr.Read()) { if(Count == 2) txtTest.Text = dr[0].ToString Count += 1; }

Count 是行，而dr(dr [index])的 index 是列.
根据需要更改连接字符串(myconnection).

希望有帮助.

Count is the row, while the index of dr(dr[index]) is the column.
Change the connection string(myconnection) according to your need.

Hope this help.

这篇关于在Csharp中使用oledb conn在文本框中导入Excel数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！