public DataSet readexcel(string fileaddrstr)
{
Application app = new Application();
Workbook wbook = app.Workbooks.Open(@"C:\Inetpub\wwwroot\Sqb.root\Sqb\WebSites\Sqb\uploadfile\" + filename + ".xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Worksheet worksheet = (Worksheet)wbook.Worksheets[1];
DataSet ds = new DataSet();
System.Data.DataTable dt = new System.Data.DataTable();
DataRow[] dr = new DataRow[65535]; for (int i = 1; i < worksheet.Rows.Count - 1; i++)
{
for (int j = 2; j <= worksheet.Columns.Count - 1; j++)
{
if (((Range)worksheet.Cells[i, j]).Text.ToString() != "0" && ((Range)worksheet.Cells[i, j]).Text.ToString() != "")
{ string sss = ((Range)worksheet.Cells[i, j]).Text.ToString();
dr[i-1] = dt.NewRow();
dr[i-1][j-2] = sss; }
}
}
foreach (DataRow row in dr)
{
dt.Rows.Add(dr);
}
dt = ds.Tables[0];
return ds;
}
我在DR副值的时候报找不到那一列的错误是为什么``希望大侠们能给指点下
{
Application app = new Application();
Workbook wbook = app.Workbooks.Open(@"C:\Inetpub\wwwroot\Sqb.root\Sqb\WebSites\Sqb\uploadfile\" + filename + ".xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Worksheet worksheet = (Worksheet)wbook.Worksheets[1];
DataSet ds = new DataSet();
System.Data.DataTable dt = new System.Data.DataTable();
DataRow[] dr = new DataRow[65535]; for (int i = 1; i < worksheet.Rows.Count - 1; i++)
{
for (int j = 2; j <= worksheet.Columns.Count - 1; j++)
{
if (((Range)worksheet.Cells[i, j]).Text.ToString() != "0" && ((Range)worksheet.Cells[i, j]).Text.ToString() != "")
{ string sss = ((Range)worksheet.Cells[i, j]).Text.ToString();
dr[i-1] = dt.NewRow();
dr[i-1][j-2] = sss; }
}
}
foreach (DataRow row in dr)
{
dt.Rows.Add(dr);
}
dt = ds.Tables[0];
return ds;
}
我在DR副值的时候报找不到那一列的错误是为什么``希望大侠们能给指点下
只是定义了一个DataTable对象,但是并没有给table加入列啊你写的真够乱的
把下面这个例子好好看看
private void MakeDataTableAndDisplay()
{
// Create new DataTable and DataSource objects.
DataTable table = new DataTable(); // Declare DataColumn and DataRow variables.
DataColumn column;
DataRow row; // Create new DataColumn, set DataType, ColumnName and add to DataTable.
column = new DataColumn();
column.DataType = System.Type.GetType("System.Int32");
column.ColumnName = "id";
table.Columns.Add(column); // Create second column.
column = new DataColumn();
column.DataType = Type.GetType("System.String");
column.ColumnName = "item";
table.Columns.Add(column); // Create new DataRow objects and add to DataTable.
for (int i = 0; i < 10; i++)
{
row = table.NewRow();
row["id"] = i;
row["item"] = "item " + i.ToString();
table.Rows.Add(row);
} DataSet ds = new DataSet();
ds.Tables.Add(table); }
dt.Columns.Add(new DataColumn("id",typeof(int)));
{
dt.Columns.Add(((Range)worksheet.Cells[0, j]).Text.ToString() )//不知道你Excel中的列是放在哪一行的,这边假设是放在第一行的
}
((Range)worksheet.Cells[0, j]).Text.ToString() 改成worksheet.Celles[0,j].Value.ToString()试试
((Range)worksheet.Cells[0, j]).Text.ToString() 改成worksheet.Celles[0,j].Value.ToString()试试
试了还是不行- -``语法通不过``
/// 打開Excel表,并返回第一個工作表
/// </summary>
/// <param name="filename">Excel文件名</param>
/// <returns>第一個工作表</returns>
private DataTable OpenExcelFile(string filename,string tablename)
{
// 打開Execl表
string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";" +
"Extended Properties='Excel 8.0;HDR=1;IMEX=1;'";
System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(conn);
cn.Open(); // 讀取數據
string query = "SELECT * FROM ["+tablename+"$]";
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(query, cn);
DataSet ds = new DataSet();
da.Fill(ds); // 關閉Excel表
cn.Close(); return ds.Tables[0];
}
这里不是可以知道有多少列么。。
按着个建好DATATable的列,
然后ROW=DATATABLE.NEWROW
一行行加进去