将打开的办公室电子表格数据上传到gridview [英] Uploading open office spreadsheet data to gridview
问题描述
我需要开发一个网络应用程序来读取开放式办公室电子表格数据并在gridview中显示我尝试了很多但是无法做到这一点可以帮助我任何人
我尝试了什么:
i尝试此代码
导入Excel文件:
< asp:FileUpload ID =FileUpload1runat =server/>
< asp:按钮ID =btnUploadrunat =serverOnClick =btnUpload_Click Text =上传/>
< asp:Label ID =Label1runat =server>
< asp:GridView ID =gvExcelFilerunat =serverCellPadding =4ForeColor =#333333GridLines =None >
< alternatingrowstyle backcolor =Whiteforecolor =#284775>
< editrowstyle backcolor =#999999>
< footerstyle backcolor =#5D7B9Dfont-bold =Trueforecolor =White>
< HeaderStyle BackColor =#5D7B9DFont-Bold =TrueForeColor =White/>
< pagerstyle backcolor =#284775forecolor =Whitehorizontalalign =Center>
< rowstyle backcolor =#F7F6F3forecolor =#333333>
< SelectedRowStyle BackColor =#E2DED6Font-Bold =TrueForeColor =#333333/>
< sortedascendingcellstyle backcolor =#E9E7E2>
< sortedascendingheaderstyle backcolor =#506C8C>
< sorteddescendingcellstyle backcolor =#FFFDF8>
< sorteddescendingheaderstyle backcolor =#6F8DAE>
protected void btnUpload_Click(object sender,EventArgs e)
{
// Coneection String默认为空
string ConStr =;
//文件上传控件的溢出保存到ext,因为
//有两种类型的exation .xls和.xlsx的Excel
string ext = Path.GetExtension(FileUpload1.FileName).ToLower();
//获取文件的路径
string path = Server.MapPath(〜/ MyFolder /+ FileUpload1.FileName);
//将文件保存在服务器的MyFolder中
FileUpload1.SaveAs(path);
Label1.Text = FileUpload1.FileName +\的数据显示在GridView中;
//检查extantion是.xls还是.xlsx
if(ext.Trim()==。xls)
{
//该文件的连接字符串,其中包括.xls
ConStr =Provider = Microsoft.Jet.OLEDB.4.0; Data Source =+ path +;扩展属性= \Excel 8.0; HDR =是; IMEX = 2 \;
}
else if(ext.Trim()==。xlsx)
{
/ /该文件的/连接字符串,其中包含.xlsx
ConStr =Provider = Microsoft.ACE.OLEDB.12.0; Data Source =+ path +;扩展属性= \Excel 12.0; HDR =是; IMEX = 2\ ;
}
//进行查询
string query =SELECT * FROM [Sheet1 $];
//提供连接
OleDbConnection conn = new OleDbConnection(ConStr);
//检查连接状态是否关闭如果关闭
//打开连接
if(conn.State == ConnectionState 。已结束)
{
conn.Open();
}
//创建命令对象
OleDbCommand cmd = new OleDbCommand(query,conn);
//创建数据适配器并将数据输入dataadapter
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
//将Excel数据填充到数据集
da.Fill(ds);
//设置网格视图的数据源
gvExcelFile.DataSource = ds.Tables [0];
//绑定gridview
gvExcelFile.DataBind();
//关闭连接
conn.Close();
}
但它应该读取开放办公室电子表格数据而不是ms office excel表格数据我该怎么做
i need to develop a web application that reads the open office spread sheet data and display it in gridview i tried a lot but unable to do this can anyone help me out
What I have tried:
i tried this code
Import Excel File:
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnUpload" runat="server" OnClick="btnUpload_Click" Text="Upload" />
<asp:Label ID="Label1" runat="server">
<asp:GridView ID="gvExcelFile" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None">
<alternatingrowstyle backcolor="White" forecolor="#284775">
<editrowstyle backcolor="#999999">
<footerstyle backcolor="#5D7B9D" font-bold="True" forecolor="White">
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<pagerstyle backcolor="#284775" forecolor="White" horizontalalign="Center">
<rowstyle backcolor="#F7F6F3" forecolor="#333333">
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<sortedascendingcellstyle backcolor="#E9E7E2">
<sortedascendingheaderstyle backcolor="#506C8C">
<sorteddescendingcellstyle backcolor="#FFFDF8">
<sorteddescendingheaderstyle backcolor="#6F8DAE">
protected void btnUpload_Click(object sender, EventArgs e)
{
//Coneection String by default empty
string ConStr = "";
//Extantion of the file upload control saving into ext because
//there are two types of extation .xls and .xlsx of Excel
string ext = Path.GetExtension(FileUpload1.FileName).ToLower();
//getting the path of the file
string path = Server.MapPath("~/MyFolder/"+FileUpload1.FileName);
//saving the file inside the MyFolder of the server
FileUpload1.SaveAs(path);
Label1.Text = FileUpload1.FileName + "\'s Data showing into the GridView";
//checking that extantion is .xls or .xlsx
if (ext.Trim() == ".xls")
{
//connection string for that file which extantion is .xls
ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (ext.Trim() == ".xlsx")
{
//connection string for that file which extantion is .xlsx
ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
//making query
string query = "SELECT * FROM [Sheet1$]";
//Providing connection
OleDbConnection conn = new OleDbConnection(ConStr);
//checking that connection state is closed or not if closed the
//open the connection
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
//create command object
OleDbCommand cmd = new OleDbCommand(query, conn);
// create a data adapter and get the data into dataadapter
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
//fill the Excel data to data set
da.Fill(ds);
//set data source of the grid view
gvExcelFile.DataSource = ds.Tables[0];
//binding the gridview
gvExcelFile.DataBind();
//close the connection
conn.Close();
}
but it should read open office spread sheet data not ms office excel sheet data how can i do this
推荐答案
;
//提供连接
OleDbConnection conn = new OleDbConnection(ConStr);
//检查连接状态是否关闭如果关闭
//打开连接
if(conn.State == ConnectionState.Closed)
{
conn.Open();
}
//创建命令对象
OleDbCommand cmd = new OleDbCommand(query,conn);
//创建数据适配器并将数据导入dataadapter
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
//填充Excel数据到数据集
da.Fill(ds);
//设置网格视图的数据源
gvExcelFile.DataSource = ds.Tables [0];
//绑定gridview
gvExcelFile.DataBind();
//关闭连接
conn.Close();
}
但它应该读取开放办公室电子表格数据而不是ms office excel表格数据我该怎么做
";
//Providing connection
OleDbConnection conn = new OleDbConnection(ConStr);
//checking that connection state is closed or not if closed the
//open the connection
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
//create command object
OleDbCommand cmd = new OleDbCommand(query, conn);
// create a data adapter and get the data into dataadapter
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
//fill the Excel data to data set
da.Fill(ds);
//set data source of the grid view
gvExcelFile.DataSource = ds.Tables[0];
//binding the gridview
gvExcelFile.DataBind();
//close the connection
conn.Close();
}
but it should read open office spread sheet data not ms office excel sheet data how can i do this
这篇关于将打开的办公室电子表格数据上传到gridview的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!