请教:excel读入sql,要求从第六行开始,不要1-5行的复杂表头。应该如何处理?谢谢!
解决方案 »
- windows应用程序怎么做压力测试?
- C# 封装com的问题
- 求一个关于WINDOWS窗体传值的问题,拜托各位了!!!
- 关于一段字符串的截取,有点难度,希望高手路过指点一下,谢谢!
- TreeView1_SelectedNodeChanged失效
- 谁知道哪有微软的.net实例Duwamish7.0的源码下载?
- TreeView如何让父节点和子节点的图片大小不一样
- C# windows窗体 控件的问题,在线等,急!!
- 如何用C#做一个信息采集器,可用于采集例如京东,淘宝等购物网站里面特定商品的基本信息
- 【见面礼】放分100,前几位尽快。
- vs2005的winform设计器的工具箱,如何能出现解决方案添加的项目里的自定义控件?
- 能否在自己的Web应用程序中嵌入.net的网页设计器或其他网页设计器,怎么实现?
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();
}//...操作...
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行开始读,那很简单,看明白上面的你就肯定明白了.自己的一点想法,希望能起到抛砖引玉的作用.
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; }
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 5.0;Database=D:\资料.xls',sheet1$')
看来还是没有类似简单的方法?
例如前五行:
第一行:工资表
第二行:一车间、二车间、三车间
第三行:一车间1组、一车间2组、二车间1组、三车间1组、三车间2组
、、、
这个的意思就是选择A1到E4的所有CELLS的值.或许对您也有帮助
这个的意思就是选择从第6行第1列开始,第10行第7列结束,这之间所以的值
特别谢谢Lixin19821010!你的方法很好。