|
|
|
สอบถามปัญหาคำสั่ง ASP.NET เกี่ยวกับการ import excel ติด error คะ |
|
|
|
|
|
|
|
Code
ไฟล์ import.asps
<%@ Page Title="" Language="C#" MasterPageFile="~/MasterPage.Master" AutoEventWireup="true"
CodeBehind="Import.aspx.cs" Inherits="BacklogMonitoring.ImportData" %>
<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent1" runat="server">
<link href="/Styles/Style.css" rel="stylesheet" type="text/css" />
<link href="/CSS/smoothness/jquery-ui-1.10.0.custom.css" rel="stylesheet">
<script src="/js/jquery-1.9.0.js"></script>
<script src="/js/jquery-ui-1.10.0.custom.js"></script>
<style type="text/css">
.myButton
{}
</style>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent1" runat="server">
<center>
<img alt="" class="style1" src="images/Import.png" /><br />
<br />
<br />
<asp:FileUpload ID="FileUploadToServer1" runat="server" />
<br />
<br />
<br />
<asp:Button ID="btnUpload" runat="server"
Text="Import" OnClick="btnUpload_Click"
Width="100px" />
<br />
<br />
<asp:Label ID="lblMsg" runat="server" Visible="false"
style="color: #FF0000; font-weight: 700; font-size: small;"></asp:Label>
<br />
<br />
<asp:GridView ID="grvBatchUpload" runat="server" CellPadding="4"
GridLines="None" ForeColor="#333333">
<AlternatingRowStyle BackColor="White" />
<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
<RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
<SortedAscendingCellStyle BackColor="#FDF5AC" />
<SortedAscendingHeaderStyle BackColor="#4D0000" />
<SortedDescendingCellStyle BackColor="#FCF6C0" />
<SortedDescendingHeaderStyle BackColor="#820000" />
</asp:GridView>
</center>
</br>
</br>
</br>
</asp:Content>
ไฟล์ import.asps.cs
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
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;
using MySql.Data.MySqlClient;
namespace BacklogMonitoring
{
public partial class Import : System.Web.UI.Page
{
String strConnString = ConfigurationManager.ConnectionStrings["OFPConnectionString"].ConnectionString;
String strSQL;
MySqlConnection objConn;
MySqlCommand objCmd;
//object obj;
protected void Page_Load(object sender, EventArgs e)
{
}
public void BindGridView()
{
string FilePath = ResolveUrl("~/Uploads/"); // Give Upload File Path
string filename = string.Empty;
if (FileUploadToServer1.HasFile) // Check FileControl has any file or not
{
try
{
string[] allowdFile = { ".xls", ".xlsx" };
string FileExt = System.IO.Path.GetExtension(FileUploadToServer1.PostedFile.FileName).ToLower();// get extensions
bool isValidFile = allowdFile.Contains(FileExt);
// check if file is valid or not
if (!isValidFile)
{
lblMsg.Visible = true;
lblMsg.Style.Add("color", "red");
lblMsg.Text = "กรุณาเลือกไฟล์ Excel เท่านั้น !!!";
}
else
{
int FileSize = FileUploadToServer1.PostedFile.ContentLength; // get filesize
if (FileSize <= 1048576) //1048576 byte = 1MB
{
filename = Path.GetFileName(Server.MapPath(FileUploadToServer1.FileName));// get file name
FileUploadToServer1.SaveAs(Server.MapPath(FilePath) + filename); // save file to uploads folder
string filePath = Server.MapPath(FilePath) + filename;
string conStr = "";
if (FileExt == ".xls") // check for excel file type
{
conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
}
else if (FileExt == ".xlsx")
{
conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
}
conStr = String.Format(conStr, filePath, "Yes");
OleDbConnection con = new OleDbConnection(conStr);
OleDbCommand ExcelCommand = new OleDbCommand();
ExcelCommand.Connection = con;
con.Open();
DataTable ExcelDataSet = new DataTable();
ExcelDataSet = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
DataTable dt = new DataTable();
if (ExcelDataSet != null && ExcelDataSet.Rows.Count > 0)
{
string SheetName = ExcelDataSet.Rows[0]["TABLE_NAME"].ToString(); // get sheetname
ExcelCommand.CommandText = "SELECT * From [" + SheetName + "]";
OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand);
ExcelAdapter.SelectCommand = ExcelCommand;
ExcelAdapter.Fill(dt);
}
con.Close();
if (dt != null && dt.Rows.Count > 0) // Check if File is Blank or not
{
grvBatchUpload.DataSource = dt;
grvBatchUpload.DataBind();
//lblMsg.Visible = false;
//บันทึกลง Mysql
foreach (GridViewRow g1 in grvBatchUpload.Rows)
{
objConn = new MySqlConnection(strConnString);
strSQL = "insert into WBS (ASSET,SNO,CODEID,BP,HEADELECTRICITY,DEF,WBS_NO,STATUS1,STATUS2,TECHNICIAN,SECTION,RESP,MEMBER,YEAR,DESCRIPTION) values('" + g1.Cells[0].Text + "','" + g1.Cells[1].Text + "','" + g1.Cells[2].Text + "','" + g1.Cells[3].Text + "','" + g1.Cells[4].Text + "','" + g1.Cells[5].Text + "','" + g1.Cells[6].Text + "','" + g1.Cells[7].Text + "','" + g1.Cells[8].Text + "','" + g1.Cells[9].Text + "','" + g1.Cells[10].Text + "','" + g1.Cells[11].Text + "','" + g1.Cells[12].Text + "','" + g1.Cells[13].Text + "','" + g1.Cells[14].Text + "') ";
objCmd = new MySqlCommand(strSQL, objConn);
objConn.Open();
objCmd.ExecuteNonQuery();
objConn.Close();
}
lblMsg.Visible = true;
lblMsg.Style.Add("color", "blue");
lblMsg.Text = "Import ข้อมูลเสร็จเรียบร้อย";
//end mysql
}
else
{
lblMsg.Visible = true;
lblMsg.Style.Add("color", "red");
lblMsg.Text = "ไม่พบข้อมูลในไฟล์นี้ !!!";
}
FilePath = ResolveUrl("~/Uploads/");
string fileName = Server.MapPath(FilePath) + filename;
FileInfo f = new FileInfo(fileName);
if (f.Exists)
{
f.IsReadOnly = false;
f.Delete();
}
}
else
{
lblMsg.Visible = true;
lblMsg.Style.Add("color", "red");
lblMsg.Text = "ขนาดไฟล์ Excel ไม่เกิน 1 MB !!!";
}
}
}
catch (Exception ex)
{
lblMsg.Visible = true;
lblMsg.Style.Add("color", "red");
lblMsg.Text = "พบข้อผิดพลาดขณะทำการ Import ข้อมูล : " + ex.Message;
}
}
else
{
lblMsg.Visible = true;
lblMsg.Style.Add("color", "red");
lblMsg.Text = "กรุณาเลือกไฟล์ที่ต้องการ Import !!!";
}
}
protected void btnUpload_Click(object sender, EventArgs e)
{
//แสดงข้อมูลจาก Excel เข้า Gridview
BindGridView();
}
}
}
ผลลัพธ์คะ
Tag : ASP, Oracle, Web (ASP.NET), VB.NET, C#, Windows
|
ประวัติการแก้ไข 2015-11-17 11:27:23
|
|
|
|
|
Date :
2015-11-17 11:24:40 |
By :
aummyasia |
View :
962 |
Reply :
1 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
ลองบน Event แล้วสร้างใหม่ครับ เหมือนมันจะหาไม่เจอนะครับ btnUpload_Click
|
|
|
|
|
Date :
2015-11-18 09:38:19 |
By :
mr.win |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Load balance : Server 05
|