62,074
社区成员
发帖
与我相关
我的任务
分享
public void ExcelToDataSet()
{
string strFilePath = Server.MapPath("../upload/excel/" + SaveClientFile());
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable sheetNames = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });//获得Excel中的所有sheetname
//OleDbDataAdapter odda;
foreach (DataRow dr in sheetNames.Rows)//循环所有的表
{
DataTable dtAll = dbexcelHelper.GetTable("select *from [" + dr[2] + "]", conn, "tbTable");
if (conn != null && conn.State == ConnectionState.Open)
{
conn.Close();
}
for (int i = 0; i < dtAll.Rows.Count; i++)
{
//相关操作
}
}
}
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
namespace Test
{
public partial class TestExcel : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
Missing miss = Missing.Value;
Excel.ApplicationClass excelApp = new Excel.ApplicationClass();
excelApp.Visible = false;
Excel.Workbooks excelBooks = excelApp.Workbooks as Excel.Workbooks;
Excel.Workbook excelBook = excelBooks.Open("F:\\Test.xls", miss, miss, miss, miss,
miss, miss, miss, miss, miss, miss, miss, miss, miss, miss);
Excel.Worksheet excelSheet = excelBook.ActiveSheet as Excel.Worksheet;
for (int i = 2; i < excelSheet.Rows.Count; i++)
{
if (((Excel.Range)excelSheet.Cells[i, 1]).Value2 != null)
{
for (int ii = 1; ii < excelSheet.Columns.Count; ii++)
{
if (((Excel.Range)excelSheet.Cells[i, ii]).Value2 != null)
{
Response.Write(((Excel.Range)excelSheet.Cells[i, ii]).Value2.ToString());
}
else { break; }
}
Response.Write("<br />");
}else { break; }
}
excelBook.Save();
excelBook.Close(true, miss, miss);
excelBooks.Close();
excelApp.Quit();
}
}
}