请教：excel读入sql,要求从第六行开始,不要1-5行的复杂表头。应该如何处理？谢谢！

[yyy39]

请教：excel读入sql,要求从第六行开始,不要1-5行的复杂表头。应该如何处理？谢谢！

[JGood]

一般情况下，第一行作为字段名称，是不会读进去的。

我一般用OleDbDataReader来读Excel文件的
string excelConnectionString = "Provider = Microsoft.Jet.OleDb.4.0; Data Source = " + filePath + "; Extended Properties=Excel 8.0;";
OleDbConnection connExcel = new OleDbConnection(excelConnectionString);
connExcel.Open();
OleDbCommand cmdExcel = new OleDbCommand("SELECT * FROM [" + tableName + "$]", connExc
OleDbDataReader dr = cmdExcel.ExecuteReader();

//不要第1-5行，可以试试
//循环四次就可以了，第一行是字段名称
for (int i = 0; i < 4; i++)
{
dr.Read();
}

//...操作...

[yyy39]

将excel导入sql，表比较多，如果在excel中删除1-5行，工作量太大。

[yyy39]

在sql存储过程中如何实现？

[Lixin19821010]

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.OleDb;

using Excel;

namespace read_excel
{
public partial class Form1 : Form
{
private System.Data.DataSet myDataSet;
public Form1()
{
InitializeComponent();
GetConnetion();
}
private void GetConnetion()
{
string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = C:\\test.xls;Extended Properties=Excel 8.0";
OleDbConnection myConn = new OleDbConnection(strCon);
//string strCom = " SELECT * FROM [Sheet1$A1:E4] ";//Command1
string strCom = "select FirstName,LastName,Height,Weight,ID from [Sheet1$]";//Command2
//string strCom = "Insert into [Sheet1$] values('10','10','10','10','10')";
//string strCom = "delete from [Sheet1$] where ID = 1";
myConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
myDataSet = new DataSet();
myCommand.Fill(myDataSet, "[Sheet1$]");
myConn.Close();

}

private void button1_Click(object sender, EventArgs e)
{
DataGrid1.DataSource = myDataSet;
DataGrid1.DataMember = "[Sheet1$]";
dataGrid2.DataSource = myDataSet;
dataGrid2.DataMember = "[Sheet1$]";
}

private void button2_Click(object sender, EventArgs e)
{
Excel.Application myExcel = new Excel.Application();
myExcel.Application.Workbooks.Add(true);
//This part used for only write values which values come from here
myExcel.Cells[1, 1] = "Li";
myExcel.Cells[1, 2] = "Xin";
myExcel.Cells[2, 1] = "Love";
myExcel.Cells[3, 1] = "Liao";
myExcel.Cells[3, 2] = "Sheng";
myExcel.Cells[3, 3] = "Hui";

//This part is used for write values which values come from datagrid
//for (int i = 1; i < 9; i++)
//{
// for (int j = 1; j < 5; j++)
// {
// myExcel.Cells[i, j] = Convert.ToInt32(this.DataGrid1[i, j].ToString()) + 1000;
// }
//}
//myExcel.Save(@"myExcel.xls");
myExcel.Save(@"C:\FuckYou.xls");
myExcel.Visible = true;
//myExcel.Quit();
}
}
}

这里有一些可以做参考,有读的,有写的,都有注释.虽然整个程序很乱,但是功能模块都还是不难读懂的.至于从第6行开始读,那很简单,看明白上面的你就肯定明白了.自己的一点想法，希望能起到抛砖引玉的作用.

[JGood]

用SQL存储过程，使用OpenDataSource语句应该可以实现

[Lixin19821010]

虽然有一些都注释掉了,但其实是有用的,你可以试一试,对你玩转EXCEL会有一个小小的作用:)

[netusemaster]

public DataTable GetDataFromExcel(String _filename, String _sheetname) // 从Excel里拿到所有行数据
        {
            try
            {
                if (_filename == null)
                {
                    FileInfo f = new FileInfo(_filename);
                    if (!f.Exists)
                    {
                        throw new Exception(String.Format("{0} isn't Exist.", f.Name));
                    }
                }

                String tbName = String.Empty;

                string strConnection = " Provider=Microsoft.Jet.OLEDB.4.0; " +
               " Data Source=" + _filename + "; " +
               " Extended Properties=Excel 8.0; " +
               " Persist Security Info=False";
                System.Data.OleDb.OleDbConnection oleConn = new System.Data.OleDb.OleDbConnection(strConnection);

                if (oleConn.State == ConnectionState.Closed)
                    oleConn.Open();

                DataTable tables = oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new Object[] { null, null, null, "TABLE" });
                int cout = 0;
                foreach (DataRow dr in tables.Rows)
                {
                    String temp = dr["TABLE_NAME"].ToString();
                    temp = temp.Substring(0, temp.Length - 1);
                    if (_sheetname == String.Empty)
                    {
                        if (temp.ToLower() == "Sheet1".ToLower())
                        {
                            tbName = temp;
                            break;
                        }
                    }
                    else
                    {
                        if (temp.ToLower() == "Sheet1".ToLower())
                        {
                            tbName = temp;
                        }

                        if (temp.ToLower() == _sheetname.ToLower())
                        {
                            tbName = temp;
                            break;
                        }
                    }
                    
                    ++cout;
                    position = (100 / tables.Rows.Count) * cout;
                    ProgressEventArgs pe = new ProgressEventArgs(position);
                    OnProgressChange(pe);
                }

                if (tbName == String.Empty)
                {
                    throw new Exception("Format of the sheet's name is bad.");
                }

                String strSql = "SELECT * FROM [" + tbName + "$]";
                if (oleConn.State == ConnectionState.Closed)
                    oleConn.Open();
                System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(strSql, strConnection);
                DataSet ds = new DataSet();
                da.Fill(ds, "[" + tbName + "$]");

                m_dataTable = ds.Tables[0];

                oleConn.Close();

                ProgressEventArgs end = new ProgressEventArgs(100);
                OnProgressChange(end);

                return m_dataTable;
            }
            catch
            {
                return null;
            }
        }


public DataTable GetMyDatatle(DataTable old) //把拿到的DataTable old 拿从第6行起的数据
        {
            DataTable mytable = new DataTable();
            DataColumn dc;
            DataRow dr;

            for (int i = 0; i < old.Columns.Count; i++)
            {
                dc = new DataColumn();
                dc.ColumnName = old.Columns[i].ColumnName;
                mytable.Columns.Add(dc);
            }

            for (int j = 0; j < old.Rows.Count;j++ )
            {
                dr = mytable.NewRow();
                if (j > 4)
                {
                   
                    for (int k = 0; k < mytable.Columns.Count;k++ )
                    {
                        dr[k] = mytable.Rows[j][k].ToString();
                    }
                }
                mytable.Rows.Add(dr);          
            }
            return mytable;

        }

[yyy39]

原来我用以下sql语句:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 5.0;Database=D:\资料.xls',sheet1$')
看来还是没有类似简单的方法？

[飘_飘]

sql:Select *from table1 where id not in(select top 5 id from table1)

[yyy39]

没有id,只是不要excel前五行。
例如前五行：
第一行：工资表
第二行：一车间、二车间、三车间
第三行：一车间1组、一车间2组、二车间1组、三车间1组、三车间2组
、、、

[Lixin19821010]

在我刚才的回复中有这么一句话，虽然是被注释掉了://string strCom = " SELECT * FROM [Sheet1$A1:E4] ";//Command1
这个的意思就是选择A1到E4的所有CELLS的值.或许对您也有帮助

[Lixin19821010]

select * from [Sheet1 $A6:G10]
这个的意思就是选择从第６行第１列开始，第１０行第７列结束，这之间所以的值

[yyy39]

谢谢各位朋友！
特别谢谢Lixin19821010！你的方法很好。

[Lixin19821010]

:)

[whatwherewhyhow]

恭喜!

[qxbtd]

领教了，困扰我的问题也解决啦，哈哈！