//private void bindemployeetype()
//{
// DBManager SalesDB = new DBManager();
// cmd = new SqlCommand("SELECT employee_type FROM employedetails where (employee_type<>'') GROUP BY employee_type");
// DataTable dttrips = vdm.SelectQuery(cmd).Tables[0];
// ddlemployee.DataSource = dttrips;
// ddlemployee.DataTextField = "employee_type";
// ddlemployee.DataValueField = "employee_type";
// ddlemployee.DataBind();
// ddlemployee.ClearSelection();
// ddlemployee.Items.Insert(0, new ListItem { Value = "ALL", Text = "ALL", Selected = true });
// ddlemployee.SelectedValue = "ALL";
//}
protected void btnImport_Click(object sender, EventArgs e)
{
try
{
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(FileUploadToServer.FileName).ToLower();
//getting the path of the file
string path = Server.MapPath("~/Userfiles/" + FileUploadToServer.FileName);
//saving the file inside the MyFolder of the server
FileUploadToServer.SaveAs(path);
lblmsg.Text = FileUploadToServer.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;";
}
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
OleDbConnection con = null;
con = new OleDbConnection(ConStr);
con.Close(); con.Open();
DataTable dtquery = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//Get first sheet name
string getExcelSheetName = dtquery.Rows[0]["Table_Name"].ToString();
//string query = "SELECT * FROM [Total Deduction$]";
//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(@"SELECT * FROM [" + getExcelSheetName + @"]", conn);
// create a data adapter and get the data into dataadapter
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
//DataSet ds = new DataSet();
DataTable dt = new DataTable();
//fill the Excel data to data set
da.Fill(dt);
//set data source of the grid view
for (int i = dt.Rows.Count - 1; i >= 0; i--)
{
if (dt.Rows[i][1] == DBNull.Value)
{
dt.Rows[i].Delete();
}
}
dt.AcceptChanges();
grvExcelData.DataSource = dt;
//binding the gridview
grvExcelData.DataBind();
Session["dtImport"] = dt;
//close the connection
conn.Close();
}
catch (Exception ex)
{
lblmsg.Text = ex.Message.ToString();
}
}
protected void btnUpload_Click(object sender, EventArgs e)
{
String SavePath = "D:\\brunohuang\\FileUpload_Folder\\";
string sheet_name = "";
int sheet_num;
int dLastNum;
int cate_items = 0;
String Cate_Username = "******";
String Cate_Status = "Y";
/*List<string> Cate_Iiitems = new List<string>();
* List<string> Cate_SpeChar = new List<string>();
* List<string> Cate_Md = new List<string>();
* List<string> Cate_Cate = new List<string>();
* List<string> Cate_KP = new List<string>();
*/
List <int> success_insert = new List <int>();
List <int> fail_insert = new List <int>();
int success_count = 0;
int fail_count = 0;
List <string> history_cate = new List <string>();
string fileName = "";
clsMySQL db = new clsMySQL();
try {
fileName = Path.GetFileName(FileUploadToServer.PostedFile.FileName);
string filePath = Server.MapPath("~\\FileUpload\\") + Path.GetFileName(FileUploadToServer.PostedFile.FileName);
FileUploadToServer.SaveAs(filePath);
lblMsg.Text = "上傳成功!!" + SavePath;
if (CheckExcelFile(fileName))
{
/*
* if (FileUploadToServer.HasFile)
* {
*
* }
*/
XSSFWorkbook wk = new XSSFWorkbook(FileUploadToServer.FileContent);
XSSFSheet hst;
XSSFRow hr;
DataTable myDT = new DataTable();
sheet_num = wk.NumberOfSheets;
for (int k = 0; k < sheet_num; k++)
{
hst = (XSSFSheet)wk.GetSheetAt(k);
cate_items = hst.LastRowNum; //每一張工作表有幾筆資料
sheet_name = hst.SheetName;
hr = (XSSFRow)hst.GetRow(0);
dLastNum = hr.LastCellNum; //每一列的欄位數
for (int i = hr.FirstCellNum; i < hr.LastCellNum; i++)
{
if (hr.GetCell(i) != null)
{
DataColumn myColumn = new DataColumn(hr.GetCell(i).StringCellValue);
myDT.Columns.Add(myColumn);
}
}
for (int j = 1; j <= cate_items; j++)
{
hr = (XSSFRow)hst.GetRow(j);
XSSFRow row = (XSSFRow)mySheet.GetRow(i);
DataRow myrow = myDT.NewRow();
for (int i = 1; i < dLastNum; i++)
{
//string strcell = hr.GetCell(i) == null ? "0" : hr.GetCell(i).ToString();
myrow =
}
String insert_cate = string.Format("insert into npi_ep_category" +
"(npi_EP_Cate_Username,npi_EP_Cate_UpdateTime,npi_EP_Cate_Status," +
"EP_Cate_Stage,EP_Cate_Iiitems,EP_Cate_SpeChar," +
"EP_Cate_Md,EP_Cate_Cate,EP_Cate_KP)values" +
"('{0}',NOW(),'{1}'," +
"'{2}','{3}','{4}','{5}','{6}','{7}')"
, Cate_Username, Cate_Status, sheet_name, hr.GetCell(0), hr.GetCell(1), hr.GetCell(2), hr.GetCell(3), hr.GetCell(4));
if (db.QueryExecuteNonQuery(insert_cate) == true)
{
success_count++;
}
else
{
fail_count++;
history_cate.Add(hr.GetCell(0) + "|" + hr.GetCell(1) + "|" + hr.GetCell(2) + "|" + hr.GetCell(3) + "|" + hr.GetCell(4));
}
}
}
}
else
{
lblMsg.ForeColor = System.Drawing.Color.Red;
lblMsg.Text = "您選擇的[" + fileName + "]無法匯入,請重新選擇Excel檔案或檢查Excel檔案內容!!";
}
}
catch (Exception exfile)
{
lblMsg.ForeColor = System.Drawing.Color.Red;
if (fileName == "")
{
lblMsg.Text = "[Import Error Message] 請選擇要匯入的Excel檔案!!";
}
else
{
lblMsg.Text = "[Import Error Message]您選擇的[" + fileName + "]無法匯入,請重新選擇Excel檔案或檢查Excel檔案內容!!";
}
}
}
protected void btnImport_Click(object sender, EventArgs e)
{
try
{
string FilePath = ConfigurationManager.AppSettings["FilePath"].ToString();
string filename = string.Empty;
//To check whether file is selected or not to uplaod
if (FileUploadToServer.HasFile)
{
try
{
string[] allowdFile = { ".xls", ".xlsx" };
//Here we are allowing only excel file so verifying selected file pdf or not
string FileExt = System.IO.Path.GetExtension(FileUploadToServer.PostedFile.FileName);
//Check whether selected file is valid extension or not
bool isValidFile = allowdFile.Contains(FileExt);
if (!isValidFile)
{
lblMessage.ForeColor = System.Drawing.Color.Red;
lblMessage.Text = "Please upload only Excel";
}
else
{
// Get size of uploaded file, here restricting size of file
int FileSize = FileUploadToServer.PostedFile.ContentLength;
if (FileSize <= 1048576)//1048576 byte = 1MB
{
//Get file name of selected file
filename = Path.GetFileName(Server.MapPath(FileUploadToServer.FileName));
//Save selected file into server location
FileUploadToServer.SaveAs(Server.MapPath(FilePath) + filename);
//Get file path
string filePath = Server.MapPath(FilePath) + filename;
//Open the connection with excel file based on excel version
OleDbConnection con = null;
if (FileExt == ".xls")
{
con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;");
}
else if (FileExt == ".xlsx")
{
con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;");
}
con.Close(); con.Open();
//Get the list of sheet available in excel sheet
DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//Get first sheet name
string getExcelSheetName = dt.Rows[0]["Table_Name"].ToString();
//Select rows from first sheet in excel sheet and fill into dataset "SELECT * FROM [Sheet1$]";
OleDbCommand ExcelCommand = new OleDbCommand(@"SELECT * FROM [" + getExcelSheetName + @"]", con);
OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand);
DataSet ExcelDataSet = new DataSet();
ExcelAdapter.Fill(ExcelDataSet);
//Bind the dataset into gridview to display excel contents
grvExcelData.DataSource = ExcelDataSet;
grvExcelData.DataBind();
Session["dtImport"] = ExcelDataSet.Tables[0];
btnsave.Visible = true;
}
else
{
lblMessage.Text = "Attachment file size should not be greater then 1 MB!";
}
}
}
catch (Exception ex)
{
lblMessage.Text = "Error occurred while uploading a file: " + ex.Message;
}
}
else
{
lblMessage.Text = "Please select a file to upload.";
}
}
catch (Exception ex)
{
lblMessage.Text = ex.ToString();
lblMessage.Visible = true;
}
}
protected void btnUpload_Click(object sender, EventArgs e)
{
String SavePath = "D:\\brunohuang\\FileUpload_Folder\\";
string sheet_name = "";
int sheet_num;
int dLastNum;
int cate_items = 0;
String Cate_Username = "******";
String Cate_Status = "Y";
DateTime dt = DateTime.Now;
string inf_ID = "";
List <string> List_Inf_ID = new List <string>();
Boolean sign_count = false;
/*List<string> Cate_Iiitems = new List<string>();
* List<string> Cate_SpeChar = new List<string>();
* List<string> Cate_Md = new List<string>();
* List<string> Cate_Cate = new List<string>();
* List<string> Cate_KP = new List<string>();
*/
List <int> success_insert = new List <int>();
List <int> fail_insert = new List <int>();
int success_count = 0;
int fail_count = 0;
List <string> history_cate = new List <string>();
string fileName = "";
string time = "";
string count_sheet_name = "";
int debug_count = 0;
Boolean sign = false;
clsMySQL db = new clsMySQL();
try
{
fileName = Path.GetFileName(FileUploadToServer.PostedFile.FileName);
//string filePath = Server.MapPath("~\\bruno\\FileUpload_Folder\\") + Path.GetFileName(FileUploadToServer.PostedFile.FileName);
string filePath = Server.MapPath("FileUpload_Folder\\") + Path.GetFileName(FileUploadToServer.PostedFile.FileName);
FileUploadToServer.SaveAs(filePath);
if (CheckExcelFile(fileName))
{
/*
* if (FileUploadToServer.HasFile)
* {
*
* }
*/
XSSFWorkbook wk = new XSSFWorkbook(FileUploadToServer.FileContent);
XSSFSheet hst;
XSSFRow hr;
DataTable myDT = new DataTable();
sheet_num = wk.NumberOfSheets;
for (int k = 0; k < sheet_num; k++) //從每張工作表開始做
{
hst = (XSSFSheet)wk.GetSheetAt(k);
cate_items = hst.LastRowNum; //每一張工作表有幾筆資料
sheet_name = hst.SheetName;
if (k != sheet_num - 1) //顯示匯入的Stage有哪些
{
count_sheet_name += sheet_name + ",";
}
else
{
count_sheet_name += sheet_name;
}
hr = (XSSFRow)hst.GetRow(0);
dLastNum = hr.LastCellNum; //每一列的欄位數
/* for (int i = hr.FirstCellNum; i < hr.LastCellNum; i++)
* {
* if (hr.GetCell(i) != null)
* {
* DataColumn myColumn = new DataColumn(hr.GetCell(i).StringCellValue);
* myDT.Columns.Add(myColumn);
*
* }
*
* }*/
for (int j = 1; j <= cate_items; j++)
{
hr = (XSSFRow)hst.GetRow(j);
//XSSFRow row = (XSSFRow) mySheet.GetRow(i);
DataRow myrow = myDT.NewRow();
/*for(int i=1;i<dLastNum;i++)
* {
* //string strcell = hr.GetCell(i) == null ? "0" : hr.GetCell(i).ToString();
* myrow =
*
* }*/
String insert_cate = string.Format("insert into npi_ep_category" +
"(npi_EP_Cate_Username,npi_EP_Cate_UpdateTime,npi_EP_Cate_Status," +
"EP_Cate_Stage,EP_Cate_Iiitems,EP_Cate_SpeChar," +
"EP_Cate_Md,EP_Cate_Cate,EP_Cate_KP)values" +
"('{0}',NOW(),'{1}'," +
"'{2}','{3}','{4}','{5}','{6}','{7}')"
, Cate_Username, Cate_Status, sheet_name, hr.GetCell(0), hr.GetCell(1), hr.GetCell(2), hr.GetCell(3), hr.GetCell(4));
string excel_sql = sheet_name + "|" + hr.GetCell(0) + "|" + hr.GetCell(1) + "|" + hr.GetCell(2) + "|" + hr.GetCell(3) + "|" + hr.GetCell(4);
Label1.Text = dt.ToString("yyyy/MM/dd hh:mm:ss");
time = Label1.Text;
if (jude_npiepcategory_data(excel_sql) == true)
{
if (db.QueryExecuteNonQuery(insert_cate) == true)
{
success_count++;
sign = true;
}
else
{
fail_count++;
history_cate.Add(hr.GetCell(0) + "|" + hr.GetCell(1) + "|" + hr.GetCell(2) + "|" + hr.GetCell(3) + "|" + hr.GetCell(4));
}
debug_count++;
}
else
{
List_Inf_ID.Add(Session["value_ID"].ToString() + "|");
sign_count = true;
}
Session.Clear();
}
}
wk = null;
hst = null;
hr = null;
}
else
{
lblMsg.ForeColor = System.Drawing.Color.Red;
lblMsg.Text = "您選擇的[" + fileName + "]無法匯入,請重新選擇Excel檔案或檢查Excel檔案內容!!";
}
/*string strScript2 = string.Format("<script language='javascript'>alert('完成匯入\\n成功匯入筆數:" + success_count + "\\n匯入的Stage:" + count_sheet_name + "');</script>");
* Page.ClientScript.RegisterStartupScript(this.GetType(), "onload", strScript2);*/
Lab_item.Text = success_count.ToString();
Lab_Stage.Text = count_sheet_name;
lblMsg.Text = fileName + "上傳成功!!";
if (sign_count == true)
{
for (int i = 0; i < List_Inf_ID.Count; i++)
{
inf_ID += List_Inf_ID[i];
}
string strScript2 = string.Format("<script language='javascript'>alert('資料重複編號:" + inf_ID + "');</script>");
Page.ClientScript.RegisterStartupScript(this.GetType(), "onload", strScript2);
}
}
catch (Exception exfile)
{
lblMsg.ForeColor = System.Drawing.Color.Red;
if (fileName == "")
{
lblMsg.Text = "[Import Error Message] 請選擇要匯入的Excel檔案!!";
}
else
{
lblMsg.Text = "[Import Error Message]您選擇的[" + fileName + "]無法匯入,請重新選擇Excel檔案或檢查Excel檔案內容!!";
}
}
finally
{
/*string strScript = string.Format("<script language='javascript'>alert('您沒有輸入Packge_Name!');</script>");
* Page.ClientScript.RegisterStartupScript(this.GetType(), "onload", strScript);*/
}
string sql_cate_datetime = "select * from npi_ep_category where npi_ep_category.npi_EP_Cate_UpdateTime >= DATE_FORMAT('" + time + "', '%Y/%m/%d %H:%i:%s')";
//DATE_FORMAT('2016/04/28 11:26:00', '%Y/%m/%d %H:%i:%s')
if (sign == true)
{
clsMySQL ds = new clsMySQL();
clsMySQL.DBReply dr = ds.QueryDS(sql_cate_datetime);
gvRecord.DataSource = dr.dsDataSet.Tables[0].DefaultView;
gvRecord.DataBind();
ds.Close();
}
}
public void ImportToDataTable()
{
string fileName = "";
lblMsg.Text = "";
try
{
fileName = Path.GetFileName(FileUploadToServer.PostedFile.FileName);
string filePath = Server.MapPath("~\\FileUpload\\") + Path.GetFileName(FileUploadToServer.PostedFile.FileName);
FileUploadToServer.SaveAs(filePath);
DataTable dt = new DataTable();
//---------------取出file type : DIF/D_R/DRC,取檔名的前三字元
if (CheckExcelFile(fileName)) //判斷File是否為xlsx
{
string strType = fileName.Substring(0, 3);
string strSQL = "";
switch (strType)
{
case "DIF":
strSQL = "select Mtype,Mname,Mpos,MRow,MCol from npimap where mtype = 'DIF' order by mid";
break;
case "Q_R":
strSQL = "select Mtype,Mname,Mpos,MRow,MCol from npimap where mtype = 'Q_R' order by mid";
break;
case "DRC":
strSQL = "select Mtype,Mname,Mpos,MRow,MCol from npimap where mtype = 'DRC' order by mid";
break;
default:
lblMsg.ForeColor = System.Drawing.Color.Red;
lblMsg.Text = "您選擇的檔案:[" + fileName + "]無法匯入,請重新選擇正確的Excel檔案!!!";
strSQL = "";
break;
}
//--------------------------------------------------------------------------------
var existingFile = new FileInfo(filePath);
using (var package = new ExcelPackage(existingFile))
{
ExcelWorkbook workBook = package.Workbook;
if (workBook != null && strSQL != "")
{
if (workBook.Worksheets.Count > 0)
{
ExcelWorksheet worksheet = workBook.Worksheets.First();//Excel File的第一個Sheet1
DataTable dt_Pos = new DataTable();
clsMySQL db = new clsMySQL();
ArrayList arSQL = new ArrayList();
db.dbConn();
dt_Pos = db.dbQueryDT(strSQL);//db.QueryDataTable(strSQL);
db.dbClose();
dt.Columns.Add("檔案類型");
dt.Columns.Add("欄位名稱");
dt.Columns.Add("欄位位置");
//dt.Columns.Add("Rows");
//dt.Columns.Add("Cols");
dt.Columns.Add("欄位內容");
//Get Customer and Device value----------------------------------------------------------------
string tmpCustomer = "";
string tmpDevice = "";
for (int t = 0; t <= 2; t++)
{
if (dt_Pos.Rows[t][1].ToString() == "Customer")
{
if (strType == "DRC")
{
string[] str2 = worksheet.Cells[Convert.ToInt32(dt_Pos.Rows[t][3].ToString()), Convert.ToInt32(dt_Pos.Rows[t][4].ToString())].Value.ToString().Split(':');
tmpCustomer = str2[1];
}
else
{
tmpCustomer = worksheet.Cells[Convert.ToInt32(dt_Pos.Rows[t][3].ToString()), Convert.ToInt32(dt_Pos.Rows[t][4].ToString())].Value.ToString();
}
}
else
{
if (dt_Pos.Rows[t][1].ToString() == "Device")
{
if (strType == "DRC")
{
string[] str2 = worksheet.Cells[Convert.ToInt32(dt_Pos.Rows[t][3].ToString()), Convert.ToInt32(dt_Pos.Rows[t][4].ToString())].Value.ToString().Split(':');
tmpDevice = str2[1];
}
else
{
tmpDevice = worksheet.Cells[Convert.ToInt32(dt_Pos.Rows[t][3].ToString()), Convert.ToInt32(dt_Pos.Rows[t][4].ToString())].Value.ToString();
}
}
}
}
//---------------------------------------------------------------------------------------------------------
for (int i = 0; i < dt_Pos.Rows.Count; i++)
{
DataRow dr = dt.NewRow();
string tmpValue = "";
int x = 0;
db.dbConn();
dr[x++] = dt_Pos.Rows[i][0].ToString();
dr[x++] = dt_Pos.Rows[i][1].ToString();
dr[x++] = dt_Pos.Rows[i][2].ToString();
//dr[x++] = dt_Pos.Rows[i][3].ToString();
//dr[x++] = dt_Pos.Rows[i][4].ToString();
if (strType == "DRC" && (dt_Pos.Rows[i][2].ToString() == "B46" || dt_Pos.Rows[i][2].ToString() == "B47" || dt_Pos.Rows[i][2].ToString() == "B48"))
{
string[] str1 = worksheet.Cells[Convert.ToInt32(dt_Pos.Rows[i][3].ToString()), Convert.ToInt32(dt_Pos.Rows[i][4].ToString())].Value.ToString().Split(':');
dr[x++] = str1[1];
tmpValue = str1[1];
}
else
{
dr[x++] = worksheet.Cells[Convert.ToInt32(dt_Pos.Rows[i][3].ToString()), Convert.ToInt32(dt_Pos.Rows[i][4].ToString())].Value;
if (worksheet.Cells[Convert.ToInt32(dt_Pos.Rows[i][3].ToString()), Convert.ToInt32(dt_Pos.Rows[i][4].ToString())].Value == null)
{
tmpValue = "";
}
else
{
tmpValue = worksheet.Cells[Convert.ToInt32(dt_Pos.Rows[i][3].ToString()), Convert.ToInt32(dt_Pos.Rows[i][4].ToString())].Value.ToString();
}
}
dt.Rows.Add(dr);
gvRecord.DataSource = dt;
gvRecord.DataBind();
//-----------------------------------insert MySql Database: npiImportData
string strSQL_Query = string.Format("select * from npiImportData where New_Customer = '{0}' and New_Device ='{1}' and Stype='{2}' and Im_Pos='{3}'", tmpCustomer.Trim(), tmpDevice.Trim(), strType.Trim(), dt_Pos.Rows[i][2].ToString().Trim());
string strSQL_Del = string.Format("Delete from npiImportData where New_Customer='{0}' and New_Device='{1}' and Stype='{2}' and Im_Pos='{3}'", tmpCustomer.Trim(), tmpDevice.Trim(), strType.Trim(), dt_Pos.Rows[i][2].ToString().Trim());
arSQL.Add(strSQL_Del);
string strSQL_Insert = string.Format("Insert into npiImportData (New_Customer,New_Device,Stype,UpdateTime,npiUser,Im_Name,Im_Pos,Im_Value) values (" +
"'{0}','{1}','{2}',NOW(),'','{3}','{4}','{5}')", tmpCustomer.Trim(), tmpDevice.Trim(), strType.Trim(), dt_Pos.Rows[i][1].ToString().Trim(), dt_Pos.Rows[i][2].ToString().Trim(), tmpValue.Trim());
arSQL.Add(strSQL_Insert);
lblMsg.ForeColor = System.Drawing.Color.Red;
//if (db.QueryDataReader(strSQL_Query).HasRows)
if (db.dbQueryDR(strSQL_Query).HasRows)
{
if (!db.myBatchNonQuery(arSQL))
{
lblMsg.Text = "[Import Error Message] Delete/Insert Fail!!<br/>";
}
}
else
{
if (!db.QueryExecuteNonQuery(strSQL_Insert))
{
lblMsg.Text = "[Import Error Message] Insert Fail!! <br/>";
}
}
arSQL.Clear();
lblMsg.Text = strSQL_Insert + "<br/>" + lblMsg.Text;
//---------------------------------------
db.dbClose();
}
lblMsg.ForeColor = System.Drawing.Color.Green;
lblMsg.Text = "[" + fileName + "],完成資料匯入!!";
}
else
{
lblMsg.ForeColor = System.Drawing.Color.Red;
lblMsg.Text = "您選擇的[" + fileName + "]無法匯入,請重新選擇Excel檔案或檢查Excel檔案內容!!";
}
}
else
{
gvRecord.DataSource = dt;
gvRecord.DataBind();
}
}
}
File.Delete(filePath); //將IIS上的excel file刪除,C:\inetpub\wwwroot\BU3Web\FileUpload
}
catch (Exception exfile)
{
lblMsg.ForeColor = System.Drawing.Color.Red;
if (fileName == "")
{
lblMsg.Text = "[Import Error Message] 請選擇要匯入的Excel檔案!!";
}
else
{
lblMsg.Text = "[Import Error Message]您選擇的[" + fileName + "]無法匯入,請重新選擇Excel檔案或檢查Excel檔案內容!!";
}
}
}
protected void btnUpload_Click(object sender, EventArgs e)
{
if (Convert.ToInt64(ddcategory.SelectedItem.Value) == -1)
{
lblMsg.ForeColor = System.Drawing.Color.Red;
lblMsg.Text = "Please select Group and the Category first";
return;
}
if (btnUpload.Text == "Upload")
{
//Get path from web.config file to upload
string FilePath = "~//excell//";
string filename = string.Empty;
//To check whether file is selected or not to uplaod
if (FileUploadToServer.HasFile)
{
bool isvalidfile = checkextension();
string FileExt = System.IO.Path.GetExtension(FileUploadToServer.PostedFile.FileName);
if (!isvalidfile)
{
lblMsg.ForeColor = System.Drawing.Color.Red;
lblMsg.Text = "Please upload only Excel";
}
else
{
int FileSize = FileUploadToServer.PostedFile.ContentLength;
if (FileSize <= 10485760) //1048576 byte = 1MB
{
//Get file name of selected file
filename = Path.GetFileName(Server.MapPath(FileUploadToServer.FileName));
OleDbConnection con = null;
//Save selected file into server location
FileUploadToServer.SaveAs(Server.MapPath(FilePath) + "Member" + FileExt);
//Get file path
try
{
string filePath = Server.MapPath(FilePath) + "Member" + FileExt;
//Open the connection with excel file based on excel version
if (FileExt == ".xls")
{
con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;");
}
else if (FileExt == ".xlsx")
{
con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;");
}
}
catch (IOException)
{
}
try
{
con.Open();
}
catch (System.InvalidOperationException)
{
lblMsg.Text = "Please save your Excel 2007 File AS Excel 2003 and then try uploading again";
return;
}
//Get the list of sheet available in excel sheet
DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//Get first sheet name
string getExcelSheetName = dt.Rows[0]["Table_Name"].ToString();
//Select rows from first sheet in excel sheet and fill into dataset
OleDbCommand ExcelCommand = new OleDbCommand(@"SELECT Member_Name,Member_Mobile,Member_Email,Member_Address FROM [" + getExcelSheetName + @"]", con);
OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand);
con.Close();
try
{
DataTable dtbl = new DataTable();
ExcelAdapter.Fill(dtbl);
ViewState["maintable"] = dtbl;
// validatecategoryavailability();
//int check= fillerrorcategorygridview();
//if (check == 0)
//{
// ViewState["maintable"] = dtbl;
int validate = validatemembermobile();
if (validate == 1)
{
fillerrormobilegridview();
}
else
{
ViewState["maintable"] = dtbl;
try
{
insertmembers();
}
catch (InvalidCastException)
{
}
showinsertedmembers();
}
// }
}
catch (OleDbException)
{
lblMsg1.ForeColor = System.Drawing.Color.Red;
lblMsg1.Visible = true;
lblMsg1.Text = "Error uploading file: Uploaded file is not in correct format.<br>It must consist of columns named :<i>Member_Name,Member_Mobile,Member_Email,Member_Address</i><br>Correct your file and try uploading it again";
}
}
}
}
else
{
lblMsg.Text = "Please select a file to upload.";
}
}
else
{
btnUpload.Text = "Upload";
FileUploadToServer.Visible = true;
}
}
protected void btnUpload_Click(object sender, EventArgs e)
{
if (Convert.ToInt64(ddcategory.SelectedItem.Value) == -1)
{
lblMsg.ForeColor = System.Drawing.Color.Red;
lblMsg.Text = "Please select Group and the Category first";
return;
}
if (btnUpload.Text == "Upload")
{
refresh.Visible = true;
btnUpload.Visible = false;
//Get path from web.config file to upload
string FilePath = "~//excell//";
string filename = string.Empty;
//To check whether file is selected or not to uplaod
if (FileUploadToServer.HasFile)
{
bool isvalidfile = checkextension();
string FileExt = System.IO.Path.GetExtension(FileUploadToServer.PostedFile.FileName);
if (!isvalidfile)
{
lblMsg.ForeColor = System.Drawing.Color.Red;
lblMsg.Text = "Please upload only Excel";
}
else
{
int FileSize = FileUploadToServer.PostedFile.ContentLength;
if (FileSize <= 10485760)//1048576 byte = 1MB
{
//Get file name of selected file
filename = Path.GetFileName(Server.MapPath(FileUploadToServer.FileName));
OleDbConnection con = null;
//Save selected file into server location
FileUploadToServer.SaveAs(Server.MapPath(FilePath) + "ExcelSend" + FileExt); //Get file path
try
{
string filePath = Server.MapPath(FilePath) + "ExcelSend" + FileExt;
//Open the connection with excel file based on excel version
if (FileExt == ".xls")
{
con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;");
}
else if (FileExt == ".xlsx")
{
con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;");
}
}
catch (IOException)
{
}
try
{
con.Open();
}
catch (System.InvalidOperationException)
{
lblMsg.Text = "Please save your Excel 2007 File AS Excel 2003 and then try uploading again";
return;
}
//Get the list of sheet available in excel sheet
DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//Get first sheet name
string getExcelSheetName = dt.Rows[0]["Table_Name"].ToString();
//Select rows from first sheet in excel sheet and fill into dataset
OleDbCommand ExcelCommand = new OleDbCommand(@"SELECT [Party Name],Items,Place,[Delivery Terms],[Qty(bags)],[Rate/Qtl],Pack FROM [" + getExcelSheetName + @"]", con);
OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand);
try
{
DataTable dtbl = new DataTable();
ExcelAdapter.Fill(dtbl);
ViewState["maintable"] = dtbl;
sendmessage();
con.Close();
con.Dispose();
}
catch (Exception)
{
lblMsg1.ForeColor = System.Drawing.Color.Red;
lblMsg1.Visible = true;
lblMsg1.Text = "Error uploading file: Uploaded file is not in correct format.<br>It must atleast consist of columns named :<i>Party Name,Items,Place,Delivery Terms,Qty(bags),Rate/Qtl,Pack</i><br>Correct your file and try uploading it again";
}
}
}
}
else
{
Response.Redirect("run_excelsend.aspx");
}
}
}
public void BindGridView()
{
try
{
string FilePath = ResolveUrl("~/FILES/"); // Give Upload File Path
string filename = string.Empty;
if (FileUploadToServer.HasFile) // Check FileControl has any file or not
{
try
{
string[] allowdFile = { ".xls", ".xlsx" };
string FileExt = System.IO.Path.GetExtension(FileUploadToServer.PostedFile.FileName).ToLower();// get extensions
bool isValidFile = allowdFile.Contains(FileExt);
// check if file is valid or not
if (!isValidFile)
{
}
else
{
int FileSize = FileUploadToServer.PostedFile.ContentLength; // get filesize
if (FileSize <= 1048576) //1048576 byte = 1MB
{
filename = Path.GetFileName(Server.MapPath(FileUploadToServer.FileName)); // get file name
FileUploadToServer.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);
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
{
GridView1.DataSource = dt;
GridView1.DataBind();
ViewState["sdtt"] = dt;
}
else
{
}
FilePath = ResolveUrl("~/FILES/");
string fileName = Server.MapPath(FilePath) + filename;
FileInfo f = new FileInfo(fileName);
if (f.Exists)
{
f.IsReadOnly = false;
f.Delete();
}
}
else
{
}
}
}
catch (Exception ex)
{
}
}
else
{
}
}
catch
{
}
}
protected void btnUpload_Click(object sender, EventArgs e)
{
string Schoolid = Session["SchoolId"].ToString();
string directoryPath = Server.MapPath(string.Format("~/ExcelFile/{0}/", Schoolid.Trim()));
if (!Directory.Exists(directoryPath))
{
Directory.CreateDirectory(directoryPath);
}
//Get path from web.config file to upload
string FilePath = string.Format("~/ExcelFile/{0}/", Schoolid.Trim());//ConfigurationManager.AppSettings["FilePath"].ToString();
string filename = string.Empty;
//To check whether file is selected or not to uplaod
if (FileUploadToServer.HasFile)
{
try
{
string[] allowdFile = { ".xls" };//, ".xlsx" };
//Here we are allowing only excel file so verifying selected file pdf or not
string FileExt = System.IO.Path.GetExtension(FileUploadToServer.PostedFile.FileName);
//Check whether selected file is valid extension or not
bool isValidFile = allowdFile.Contains(FileExt);
if (!isValidFile)
{
divsucrss.Style.Add("display", "none");
divUnsucrss.Style.Remove("display");
ltrNotSucess.Text = "Please upload only Excel";
}
else
{
// Get size of uploaded file, here restricting size of file
int FileSize = FileUploadToServer.PostedFile.ContentLength;
if (FileSize <= 3048576)//1048576 byte = 1MB
{
//Get file name of selected file
filename = Path.GetFileName(Server.MapPath(FileUploadToServer.FileName));
//Save selected file into server location
FileUploadToServer.SaveAs(Server.MapPath(FilePath) + filename);
//Get file path
string filePath = Server.MapPath(FilePath) + filename;
//Open the connection with excel file based on excel version
OleDbConnection con = null;
if (FileExt == ".xls")
{
con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;");
}
else if (FileExt == ".xlsx")
{
// con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;");
}
con.Open();
//Get the list of sheet available in excel sheet
DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//Get first sheet name
string getExcelSheetName = dt.Rows[0]["Table_Name"].ToString();
//Select rows from first sheet in excel sheet and fill into dataset
OleDbCommand ExcelCommand = new OleDbCommand(@"SELECT * FROM [" + getExcelSheetName + @"]", con);
OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand);
DataSet ExcelDataSet = new DataSet();
ExcelAdapter.Fill(ExcelDataSet);
con.Close();
// var aa=ExcelDataSet.Tables[0].Rows.
try
{
string Filds = clsStdDetail.GetSchoolFild(Schoolid).Select(x => x.MyIdFild).FirstOrDefault().ToString();
var lstFild = Filds;
//if (lstFild.Contains("[StudentPhoto]"))
// dtxtStudentPhoto.Style.Remove("display");
//if (lstFild.Contains("[Status]"))
// dtxtStatus.Style.Remove("display");
for (int i = 0; i < ExcelDataSet.Tables[0].Rows.Count; i++)
{
objStd.SchoolUID = Schoolid;
if (lstFild.Contains("[ScholarNo]"))
{
objStd.ScholarNo = ExcelDataSet.Tables[0].Rows[i]["ScholarNo"].ToString();
}
else
{
objStd.ScholarNo = "";
}
if (lstFild.Contains("[StudentName]"))
{
objStd.StudentName = ExcelDataSet.Tables[0].Rows[i]["StudentName"].ToString();
}
else
{
objStd.StudentName = "";
}
if (lstFild.Contains("[FatherName]"))
{
objStd.FatherName = ExcelDataSet.Tables[0].Rows[i]["FatherName"].ToString();
}
else
{
objStd.FatherName = "";
}
if (lstFild.Contains("[MotherName]"))
{
objStd.MotherName = ExcelDataSet.Tables[0].Rows[i]["MotherName"].ToString();
}
else
{
objStd.MotherName = "";
}
if (lstFild.Contains("[BloodGroup]"))
{
objStd.BloodGroup = ExcelDataSet.Tables[0].Rows[i]["BloodGroup"].ToString();
}
else
{
objStd.BloodGroup = "";
}
if (lstFild.Contains("[PhoneNumber]"))
{
objStd.PhoneNumber = ExcelDataSet.Tables[0].Rows[i]["PhoneNumber"].ToString();
}
else
{
objStd.PhoneNumber = "";
}
if (lstFild.Contains("[DateofBarth]"))
{
try {
objStd.DateofBarth = Convert.ToDateTime(ExcelDataSet.Tables[0].Rows[i]["DateofBirth"]).ToString("dd/MM/yyyy");
}
catch { objStd.DateofBarth = "";
ltrupdaterecorddetail.Text += " <spain style='color:red'>" + ExcelDataSet.Tables[0].Rows[i]["ScholarNo"].ToString() + "</spain>"; }
}
else
{
objStd.DateofBarth = "";
}
if (lstFild.Contains("[Class]"))
{
objStd.Class = ExcelDataSet.Tables[0].Rows[i]["Class"].ToString();
}
else
{
objStd.Class = "";
}
if (lstFild.Contains("[EmailId]"))
{
objStd.EmailId = ExcelDataSet.Tables[0].Rows[i]["EmailId"].ToString();
}
else
{
objStd.EmailId = "";
}
if (lstFild.Contains("[Addres]"))
{
objStd.Addres = ExcelDataSet.Tables[0].Rows[i]["ADDRESS"].ToString();
}
else
{
objStd.Addres = "";
}
if (lstFild.Contains("[F2]"))
{
objStd.F2 = ExcelDataSet.Tables[0].Rows[i]["OTHER1"].ToString();
}
else
{
objStd.F2 = "";
}
if (lstFild.Contains("[F3]"))
{
objStd.F3 = ExcelDataSet.Tables[0].Rows[i]["OTHER2"].ToString();
}
else
{
objStd.F3 = "";
}
if (lstFild.Contains("[F4]"))
{
objStd.F4 = ExcelDataSet.Tables[0].Rows[i]["OTHER3"].ToString();
}
else
{
objStd.F4 = "";
}
if (lstFild.Contains("[StudentPhoto]"))
{
string pto = ExcelDataSet.Tables[0].Rows[i]["StudentPhoto"].ToString();
objStd.StudentPhoto = "StdPhoto/" + Schoolid + "/" + (pto.Contains(".") ? pto : pto + ".jpg");
}
else
{
objStd.StudentPhoto = "";
}
objStd.F1 = ExcelDataSet.Tables[0].Rows[i]["TITLE"].ToString();
objStd.LastUpdated = DateTime.Now;
objStd.IsActive = 1;
objStd.Status = 0;
//objStd.StudentPhoto= //img
int isExist = clsStdDetail.GetStudentDetail(Schoolid, objStd.ScholarNo).Count;
if (isExist > 0)
{
objStd.ScholarNo_Old = ExcelDataSet.Tables[0].Rows[i]["ScholarNo"].ToString();
string retCode = clsStdDetail.UpdateStudetDetail(objStd);
if (retCode != "1")
{
divsucrss.Style.Add("display", "none");
divUnsucrss.Style.Remove("display");
ltrNotSucess.Text = retCode + "There are some error contact to admin !!";
}
else
{
divUnsucrss.Style.Add("display", "none");
divsucrss.Style.Remove("display");
ltrSucess.Text = "Data Inserted Sucess ";
divupdatedrecord.Style.Remove("display");
ltrupdaterecorddetail.Text += objStd.ScholarNo_Old + " ";
}
}
else
{
string retCode = clsStdDetail.InsertStudetDetail(objStd);
if (retCode != "1")
{
divsucrss.Style.Add("display", "none");
divUnsucrss.Style.Remove("display");
ltrNotSucess.Text = retCode + "There are some error contact to admin !!";
}
else
{
divUnsucrss.Style.Add("display", "none");
divsucrss.Style.Remove("display");
ltrSucess.Text = "Data Inserted Sucess ";
}
}
}
GridView1.DataSource = ExcelDataSet;
GridView1.DataBind();
}
catch (Exception s)
{
divsucrss.Style.Add("display", "none");
divUnsucrss.Style.Remove("display");
ltrNotSucess.Text = s.Message + "There are some error contact to admin !!";
}
}
else
{
divsucrss.Style.Add("display", "none");
divUnsucrss.Style.Remove("display");
ltrNotSucess.Text = "Attachment file size should not be greater then 1 MB !";
}
}
}
catch (Exception ex)
{
divsucrss.Style.Add("display", "none");
divUnsucrss.Style.Remove("display");
ltrNotSucess.Text = "Error occurred while uploading a file: " + ex.Message;
}
}
else
{
divsucrss.Style.Add("display", "none");
divUnsucrss.Style.Remove("display");
ltrNotSucess.Text = "Please select a file to upload.";
}
}
protected void btnUpload_Click(object sender, EventArgs e)
{
string FilePath = ConfigurationManager.AppSettings["FilePath"].ToString();
string filename = string.Empty;
if (FileUploadToServer.HasFile)
{
try
{
string[] allowedFile = { ".xls", ".xlsx" };
string FileExt = System.IO.Path.GetExtension(FileUploadToServer.PostedFile.FileName);
bool isValidFile = allowedFile.Contains(FileExt);
if (!isValidFile)
{
lblMsg.ForeColor = System.Drawing.Color.Red;
lblMsg.Text = "Please upload only Excel format file.";
}
else
{
int FileSize = FileUploadToServer.PostedFile.ContentLength;
if (FileSize <= 5242880) //5242880 bytes = 5MB
{
filename = Path.GetFileName(Server.MapPath(FileUploadToServer.FileName));
FileUploadToServer.SaveAs(Server.MapPath(FilePath) + filename);
string filePath = Server.MapPath(FilePath) + filename;
OleDbConnection con = null;
if (FileExt == ".xls")
{
con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;");
}
else if (FileExt == ".xlsx")
{
con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;");
}
con.Open();
DataTable dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string getExcelSheetName = dt.Rows[0]["Table_Name"].ToString();
OleDbCommand ExcelCommand = new OleDbCommand(@"SELECT F1,F2,F3 FROM [" + getExcelSheetName + @"]", con);
OleDbDataAdapter ExcelAdapter = new OleDbDataAdapter(ExcelCommand);
DataSet ExcelDataSet = new DataSet();
ExcelAdapter.Fill(ExcelDataSet);
con.Close();
GridView1.DataSource = ExcelDataSet;
GridView1.DataBind();
}
else
{
lblMsg.Text = "Attached file size should not be greater than 5 MB!";
}
}
}
catch (Exception ex)
{
lblMsg.Text = "Error occured while uploading: " + ex.Message;
}
}
else
{
lblMsg.Text = "Please select a file to upload.";
}
}