Загрузите excel в gridview
Я хочу загрузить excel в gridview, и мне удалось сделать это с помощью файла excel, который имел sheet1 в качестве первого листа, но я хочу загрузить любой лист в рабочей книге.
Мне нужно, чтобы заполнить выпадающий список с названиями листов книги, и когда лист установлен, мне нужно, чтобы заполнить в сетке.
Что я уже пробовал:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace Interfaz2
{
public partial class _Default : Page
{
//public static string s1 = "";
protected void Page_Load(object sender, EventArgs e)
{
}
protected void imp_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
GetExcelSheetNames(FileUpload1.PostedFile.FileName.ToString());
}
}
private String[] GetExcelSheetNames(string excelFile)
{
OleDbConnection objConn = null;
System.Data.DataTable dt = null;
try
{
// Connection String. Change the excel file to the file you
// will search.
String connString = "Provider=Microsoft.Jet.OLEDB.12.0;" +
"Data Source=" + excelFile + ";Extended Properties=Excel 12.0;";
// Create connection object by using the preceding connection string.
objConn = new OleDbConnection(connString);
// Open connection with the database.
objConn.Open();
// Get the data table containg the schema guid.
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return null;
}
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;
// Add the sheet name to the string array.
foreach (DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
sheet.Items.Add(excelSheets[i]);
i++;
}
// Loop through all of the sheets if you want too...
for (int j = 0; j < excelSheets.Length; j++)
{
// Query each excel sheet.
}
return excelSheets;
}
catch (Exception ex)
{
return null;
}
finally
{
// Clean up.
if (objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
}
}
protected void FileUpload1_Load(object sender, EventArgs e)
{
}
protected void sheet_SelectedIndexChanged(object sender, EventArgs e)
{
//s1 = sheet.SelectedValue.ToString();
string connString = "";
string strFileType = Path.GetExtension(FileUpload1.FileName).ToLower();
string path = FileUpload1.PostedFile.FileName;
string fileBasePath = Server.MapPath("~/Files/");
string fileName = Path.GetFileName(this.FileUpload1.FileName);
string fullFilePath = fileBasePath + fileName;
//Connection String to Excel Workbook
if (strFileType.Trim() == ".xls")
{
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fullFilePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
}
else if (strFileType.Trim() == ".xlsx")
{
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fullFilePath + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";
}
if (FileUpload1.HasFile)
{
string query = "SELECT [UserName],[Education],[Location] FROM [" + sheet.SelectedValue.ToString() + "]";
OleDbConnection conn = new OleDbConnection(connString);
if (conn.State == ConnectionState.Closed)
conn.Open();
OleDbCommand cmd = new OleDbCommand(query, conn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
grid.DataSource = ds.Tables[0];
grid.DataBind();
da.Dispose();
conn.Close();
conn.Dispose();
}
}
protected void FileUpload1_DataBinding(object sender, EventArgs e)
{
GetExcelSheetNames(FileUpload1.FileName.ToString());
}
}
CHill60
Каков ваш реальный вопрос? Что не так с кодом, который у вас есть? (Кроме очень плохой обработки ошибок)
Member 10850253
Я загрузил все листы в combobox, но теперь я хочу загрузить файл excel в gridview, в зависимости от выбора combobox.
using System;
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 System.Data;
using System.Data.OleDb;
using System.IO;
using System.Configuration;
public partial class _Default : System.Web.UI.Page
{
public static OleDbConnection connExcel;
public static OleDbCommand cmdExcel;
public static OleDbDataAdapter oda;
public static DataTable dt;
public static string FilePath;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnUpload_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
FilePath = Server.MapPath(FolderPath + FileName);
FileUpload1.SaveAs(FilePath);
Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);
}
}
private void Import_To_Grid(string FilePath, string Extension, string isHDR)
{
string conStr="";
switch (Extension)
{
case ".xls": //Excel 97-03
conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07
conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
conStr = String.Format(conStr, FilePath, isHDR);
connExcel = new OleDbConnection(conStr);
cmdExcel = new OleDbCommand();
oda = new OleDbDataAdapter();
dt = new DataTable();
cmdExcel.Connection = connExcel;
//Get the name of First Sheet
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
for (int i = 0; i < dtExcelSchema.Rows.Count; i++)
{
String sheetName = dtExcelSchema.Rows[i]["TABLE_NAME"].ToString();
sheetName = sheetName.Substring(0, sheetName.Length - 1);
sheet.Items.Add(sheetName);
}
//string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
}
protected void PageIndexChanging(object sender, GridViewPageEventArgs e)
{
string FolderPath = ConfigurationManager.AppSettings["FolderPath"] ;
string FileName = GridView1.Caption;
string Extension = Path.GetExtension(FileName);
string FilePath = Server.MapPath(FolderPath + FileName);
Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);
GridView1.PageIndex = e.NewPageIndex;
GridView1.DataBind();
}
protected void sheet_SelectedIndexChanged(object sender, EventArgs e)
{
//Read Data from First Sheet
connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + sheet.Text.ToString() + "]";
oda.SelectCommand = cmdExcel;
oda.Fill(dt);
connExcel.Close();
//Bind Data to GridView
GridView1.Caption = Path.GetFileName(FilePath);
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
Member 10850253
Пожалуйста, дайте мне знать, как я должен отображать лист в gridview, основываясь на выборе combobox.
Member 10850253
Как загрузить любой лист книги excel в asp.net с#?
Karthik_Mahalingam
использовать Ответить кнопка для отправки комментариев/запросов заинтересованному пользователю, чтобы пользователь получил уведомление и ответил на ваш текст.
Member 10850253
Я загрузил все листы в combobox, но теперь я хочу загрузить файл excel в gridview, в зависимости от выбора combobox.
using System;
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 System.Data;
using System.Data.OleDb;
using System.IO;
using System.Configuration;
public partial class _Default : System.Web.UI.Page
{
public static OleDbConnection connExcel;
public static OleDbCommand cmdExcel;
public static OleDbDataAdapter oda;
public static DataTable dt;
public static string FilePath;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnUpload_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
FilePath = Server.MapPath(FolderPath + FileName);
FileUpload1.SaveAs(FilePath);
Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);
}
}
private void Import_To_Grid(string FilePath, string Extension, string isHDR)
{
string conStr="";
switch (Extension)
{
case ".xls": //Excel 97-03
conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07
conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
conStr = String.Format(conStr, FilePath, isHDR);
connExcel = new OleDbConnection(conStr);
cmdExcel = new OleDbCommand();
oda = new OleDbDataAdapter();
dt = new DataTable();
cmdExcel.Connection = connExcel;
//Get the name of First Sheet
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
for (int i = 0; i < dtExcelSchema.Rows.Count; i++)
{
String sheetName = dtExcelSchema.Rows[i]["TABLE_NAME"].ToString();
sheetName = sheetName.Substring(0, sheetName.Length - 1);
sheet.Items.Add(sheetName);
}
//string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
}
protected void PageIndexChanging(object sender, GridViewPageEventArgs e)
{
string FolderPath = ConfigurationManager.AppSettings["FolderPath"] ;
string FileName = GridView1.Caption;
string Extension = Path.GetExtension(FileName);
string FilePath = Server.MapPath(FolderPath + FileName);
Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);
GridView1.PageIndex = e.NewPageIndex;
GridView1.DataBind();
}
protected void sheet_SelectedIndexChanged(object sender, EventArgs e)
{
//Read Data from First Sheet
connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + sheet.Text.ToString() + "]";
oda.SelectCommand = cmdExcel;
oda.Fill(dt);
connExcel.Close();
//Bind Data to GridView
GridView1.Caption = Path.GetFileName(FilePath);
GridView1.DataSource = dt;
GridView1.DataBind();
}
}