Recent Post

Thursday, October 10, 2013

Upload dữ liệu excel lên server trong asp.net

by Unknown  |  at  5:40 PM



vừa nghịch xong cái này ai cần có thể tam khảo

B1: cài file patch cho excel trên server tên AccessDatabaseEngine các bạn có thể lên google seach lấy về bản cài phù hợp với server của các bạn http://codechicken.blogspot.com/

B2: viết code upload và hiển thị dữ liệu lên girdview khi cần thiết

--

Code html :


<body>

<form id="form1" runat="server">

<p>

<table style="width:100%;">

<tr>

<td class="style2">

&nbsp;</td>

<td class="style4">

<td>

</td>

</tr>

<tr>

<td class="style2">

Chọn file excel :</td>

<td class="style3">

<asp:FileUpload ID="FileUpload1" runat="server" />

</td>

<td>

&nbsp;</td>

</tr>

<tr>

<td class="style2">

&nbsp;</td>

<td class="style3">

<asp:Button ID="btnUpload" runat="server" Text="Tải lên"

OnClick="btnUpload_Click" CssClass="button" Width="65px" />

</td>

<td>

&nbsp;</td>

</tr>

<tr>

<td class="style2">

<asp:Label ID="Label1" runat="server" Visible="False"></asp:Label>

</td>

<td class="style3">

<asp:Label ID="Label2" runat="server" Visible="False"></asp:Label>

</td>

<td>

<asp:Label ID="Label3" runat="server" Visible="False"></asp:Label>

</td>

</tr>

<tr>

<td class="style1">

<asp:Label ID="lblIDKhachHang" runat="server" Visible="False"></asp:Label>

</td>

<td class="style3">

&nbsp;</td>

<td>

<asp:Button ID="btnghi" runat="server" Text="Ghi cập nhật" Width="172px"

onclick="btnghi_Click1" CssClass="button" />

</td>

</tr>

<tr>

<td class="style1">

Tổng đơn hàng là :</td>

<td class="style3">

<asp:Label ID="lblTongDonHang" runat="server"></asp:Label>

</td>

<td>

&nbsp;</td>

</tr>

</table>

</p>

<div>




<asp:GridView ID="GridView1" runat="server" Width="100%"

CssClass="grid"

HeaderStyle-CssClass="header"

FooterStyle-CssClass="footer"

RowStyle-CssClass="row"

AlternatingRowStyle-CssClass="alternating" ShowFooter="True"

onrowdatabound="GridView1_RowDataBound" AutoGenerateColumns="False"

>

<AlternatingRowStyle CssClass="alternating" />

<Columns>

<asp:BoundField DataField="STT" HeaderText="STT" />

<asp:BoundField DataField="SoDonHang" HeaderText="Số đơn hàng" />

<asp:BoundField DataField="SoHoaDon" HeaderText="Số hóa đơn" />

<asp:BoundField DataField="TrongLuong" HeaderText="Trọng lượng" />

<asp:BoundField DataField="SoKien" HeaderText="Số kiện" />

<asp:BoundField DataField="MaKhachHang" HeaderText="Mã khách hàng" />

<asp:BoundField DataField="TenKhachHang" HeaderText="Tên khách hàng" />

<asp:BoundField DataField="SoDienThoai" HeaderText="Số điện thoại" />

<asp:BoundField DataField="QuanHuyen" HeaderText="Quận huyện" />

<asp:BoundField DataField="DiaChi" HeaderText="Địa chỉ" />

</Columns>

<FooterStyle CssClass="footer" />

<HeaderStyle CssClass="header" />

<RowStyle CssClass="row" />

</asp:GridView>




</div>

</form>

</body>

</html>


////////////////////////code nut upload ở đây mình chưa kiểm tra tên và dung lượng file các bạn tự phát triển thêm



protected void btnUpload_Click(object sender, EventArgs e)

{

if (FileUpload1.HasFile)

{

string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);

Label1.Text = FileName.ToString();

string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);

Label2.Text = Extension.ToString();

string FolderPath = ConfigurationManager.AppSettings["FolderPath"];

string FilePath = Server.MapPath(FolderPath + FileName);

Label3.Text = FilePath.ToString();

FileUpload1.SaveAs(FilePath);

// GetExcelSheets(FilePath, Extension, "Yes");

Import_To_Grid(FilePath, Extension, "Yes");

}



}

///////////code hiển thị dữ liệu lên gridview khi upload thành công


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);

OleDbConnection connExcel = new OleDbConnection(conStr);

OleDbCommand cmdExcel = new OleDbCommand();

OleDbDataAdapter oda = new OleDbDataAdapter();

DataTable dt = new DataTable();

cmdExcel.Connection = connExcel;

//Get the name of First Sheet

connExcel.Open();

DataTable dtExcelSchema;

dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();

connExcel.Close();

//Read Data from First Sheet

connExcel.Open();

cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";

oda.SelectCommand = cmdExcel;

oda.Fill(dt);

connExcel.Close();

//Bind Data to GridView

GridView1.Caption = Path.GetFileName(FilePath);

GridView1.DataSource = dt;

GridView1.DataBind();

lblTongDonHang.Text = dt.Rows.Count.ToString();

}

//// lưu ý chuỗi kết nối trong web config


<?xml version="1.0"?>

<configuration>

<appSettings>

<add key="FolderPath" value="Files/"/>

<add key="FolderPathThongTinPhat" value="../FilesThongTinPhat/"/>

<add key="CrystalImageCleaner-AutoStart" value="true"/>

<add key="CrystalImageCleaner-Sleep" value="60000"/>

<add key="CrystalImageCleaner-Age" value="120000"/>

</appSettings>




<connectionStrings>

<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>

<add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"/>




<add name="conString" connectionString="server=servername;database=A;user id=chay;password=123" providerName="System.Data.SqlClient"/>

<add name="conString3" connectionString="server=sẻvername;database=B;user id=chay;password=123" providerName="System.Data.SqlClient"/>

</connectionStrings>

hình ảnh sau khi xong code:









0 comments:

Proudly Powered by Blogger.