|
<appSettings>
<add key="FilePath" value="C:\Users\*****\Desktop\Plans_First.xlsx"/>
<add key="SheetName" value="LAVVAGGIO|MANUTENZIONE|Map"/>
<add key="SortColumnName" value="LAVVAGGI_VALUE|MANUTENZIONI_VALUE"/>
<add key="MapColumn" value="NAME|LAVVAGGI_RANK|MANUTENZIONI_RANK"/> <!--ID Column should be present by default-->
<add key="RankColumn" value="LAVVAGGI_RANK|MANUTENZIONI_RANK"/>
</appSettings>
private DataTable SortSheet(string FilePath, string SheetName, string SortColumnName,string RankColumn)
{
try
{
var fileName = FilePath;
if (!File.Exists(FilePath))
{
txtLogger.Text += FilePath + " File Not Found!!!" + Environment.NewLine;
MessageBox.Show(" File Not Found!!!");
new DataTable();
}
#region Reading excel file
var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;IMEX=0;HDR=YES;TypeGuessRows=0;ImportMixedTypes=Text\""; ;
DataSet ds = new DataSet();
using (var conn = new OleDbConnection(connectionString))
{
try
{
try
{
conn.Open();
}
catch (Exception ex1)
{
txtLogger.Text += " exception : " + ex1.StackTrace + Environment.NewLine;
MessageBox.Show("File is Open, Please close to proceed!!!");
new DataTable();
}
var sheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
DataRow[] dr = sheets.Select("[Table_name]= '" + SheetName.ToString() + "$'");
if (dr.Length == 0)
{
MessageBox.Show("SheetName Not Found!!!");
txtLogger.Text += SheetName.ToString() + " : SheetName Not Found!!!" + Environment.NewLine;
new DataTable();
}
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM [" + SheetName + "$] WHERE [ID] IS NOT NULL ";
var adapter = new OleDbDataAdapter(cmd);
adapter.Fill(ds);
}
}
catch (Exception ex)
{
txtLogger.Text += " exception : " + ex.StackTrace + Environment.NewLine;
MessageBox.Show("Error Occured ,Please check the log!!!");
new DataTable();
}
finally
{
conn.Close();
conn.Dispose();
}
}
#endregion
#region Sorting based on SortColumnName
DataTable dt = ds.Tables[0];
DataView view = dt.DefaultView;
view.Sort = SortColumnName + " ASC";
DataTable sortedData = view.ToTable();
if(sortedData.Columns.Contains(RankColumn))
{
int rank = 1;
foreach (DataRow row in sortedData.Rows)
{
row[RankColumn] = rank;
rank++;
}
}
txtLogger.Text += " Sort Completed : Sheet " + SheetName + Environment.NewLine;
return sortedData;
#endregion
}
catch (Exception)
{
throw;
}
}
private void InsertSheet(string FilePath, DataTable MapDatatable, string MapSheetName, List<string> updatedColumns)
{
try
{
var fileName = FilePath;
if (!File.Exists(FilePath))
{
txtLogger.Text += FilePath + " File Not Found!!!" + Environment.NewLine;
MessageBox.Show(" File Not Found!!!");
return;
}
var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;IMEX=0;HDR=YES;TypeGuessRows=0;ImportMixedTypes=Text\""; ;
#region Updating Excel
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
try
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.Connection = conn;
var sheets = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
int colcount = 0;
System.Type type;
foreach (DataRow row in MapDatatable.Rows)
{
colcount = 0;
StringBuilder commandString = new StringBuilder();
commandString.Append("UPDATE [" + MapSheetName.ToString() + "$"+"] SET ");
foreach (DataColumn c in MapDatatable.Columns)
{
if (updatedColumns.Contains(c.ColumnName))
{
commandString.Append(c.ColumnName);
type = row.ItemArray[colcount].GetType();
if ((type == typeof(string)) || (type == typeof(DateTime)))
{
commandString.Append(" = '").Append(row.ItemArray[colcount]).Append("' ,");
}
else if (row.ItemArray[colcount].ToString().Trim() == string.Empty)
{
commandString.Append(" = ' ").Append(row.ItemArray[colcount]).Append("' ,");
}
else
{
commandString.Append(" = ").Append(row.ItemArray[colcount]).Append(" ,");
}
}
colcount++;
}
commandString.Remove(commandString.Length - 1, 1);
commandString.Append(" where ID = " + row["ID"].ToString() + ";");
cmd.CommandText = commandString.ToString();
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
txtLogger.Text += " Update exception on Line : " + ex.StackTrace + ": Sheet " + MapSheetName+ Environment.NewLine;
MessageBox.Show("Error Occured ,Please check the log!!!");
return;
}
finally
{
conn.Close();
conn.Dispose();
}
}
#endregion
}
catch (Exception)
{
throw;
}
}
<pre>private void button1_Click(object sender, EventArgs e)
{
#region Full Event
try
{
txtLogger.Text = string.Empty;
string FilePath = System.Configuration.ConfigurationManager.AppSettings["FilePath"];
string SheetName = System.Configuration.ConfigurationManager.AppSettings["SheetName"];
string SortColumnName = System.Configuration.ConfigurationManager.AppSettings["SortColumnName"];
string MapColumn = System.Configuration.ConfigurationManager.AppSettings["MapColumn"];
string RankColumn = System.Configuration.ConfigurationManager.AppSettings["RankColumn"];
string[] Sheets = SheetName.Split('|');
string[] sortColumnName = SortColumnName.Split('|');
string[] mapColumn = MapColumn.Split('|');
string[] rankColumn = RankColumn.Split('|');
int colcount = 0;
List<string> updatedColumns = new List<string>();
foreach (var item in mapColumn)
{
updatedColumns.Add(mapColumn[colcount].ToString());
colcount++;
}
if(DateTime.Now.Year==2023)
{
MessageBox.Show("error");
return;
}
DataTable Sheet1, Sheet2 , Sheet3;
Sheet1 = SortSheet(FilePath, Sheets[0].ToString(), sortColumnName[0].ToString(),rankColumn[0].ToString());
Sheet2 = SortSheet(FilePath, Sheets[1].ToString(), sortColumnName[1].ToString(),rankColumn[1].ToString());
Sheet3 = SortSheet(FilePath, Sheets[2].ToString(), "ID", rankColumn[1].ToString());
if ((Sheet1.Rows.Count>0)&&(Sheet2.Rows.Count > 0))
{
if(!Sheet1.Columns.Contains(mapColumn[0].ToString()))
{
Sheet1.Columns.Add(mapColumn[0].ToString(), typeof(System.String));
foreach (DataRow row in Sheet1.Rows)
{
row[mapColumn[1].ToString()] = string.Empty;
}
}
if (!Sheet1.Columns.Contains(mapColumn[1].ToString()))
{
Sheet1.Columns.Add(mapColumn[1].ToString(), typeof(System.Int32));
foreach (DataRow row in Sheet1.Rows)
{
row[mapColumn[1].ToString()] = 0;
}
}
if (!Sheet1.Columns.Contains(mapColumn[2].ToString()))
{
Sheet1.Columns.Add(mapColumn[2].ToString(), typeof(System.Int32));
foreach (DataRow row in Sheet1.Rows)
{
row[mapColumn[2].ToString()] = 0;
}
}
}
Sheet1.AsEnumerable()
.Join(Sheet2.AsEnumerable(),
dt1_Row => dt1_Row.ItemArray[0],
dt2_Row => dt2_Row.ItemArray[0],
(dt1_Row, dt2_Row) => new { dt1_Row, dt2_Row })
.ToList()
.ForEach(o =>
o.dt1_Row.SetField(7, o.dt2_Row.ItemArray[6]));
InsertSheet(FilePath, Sheet1, Sheets[2].ToString(),updatedColumns);
MessageBox.Show(" Reschedule Completed!!!");
return;
}
catch (Exception ex)
{
txtLogger.Text += " exception : " + ex.StackTrace + Environment.NewLine;
MessageBox.Show("Error Occured ,Please check the log!!!");
return;
}
#endregion
}
modified 4-Oct-22 17:41pm.
|
|
|
|
|
A massive wall of unexplained code is not a question.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
So have I have a modal with a updatepanel and a repeater inside it. I have an open another modal on linkbutton click, I would like to update the 2nd modal with a checkboxlist, but the linkbutton click doesn't fire for me to update the 2nd modal data.
|
|
|
|
|
There's a secret error somewhere in your secret code. You should fix that.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
bonjour
avec le code ci-dessous je trouve que quelque champ de ma demande, n
le graphique n'affiche que les description de (AA,CC,SS,GG,...) pas de (BB,DD,FF,...)
code source
Me.Chart1.Series("Statistiques").Points.AddXY("AA", 95)
Me.Chart1.Series("Statistiques").Points.AddXY("BB", 90)
Me.Chart1.Series("Statistiques").Points.AddXY("CC", 85)
Me.Chart1.Series("Statistiques").Points.AddXY("DD", 95)
Me.Chart1.Series("Statistiques").Points.AddXY("SS", 93)
Me.Chart1.Series("Statistiques").Points.AddXY("FF", 90)
Me.Chart1.Series("Statistiques").Points.AddXY("GG", 95)
Me.Chart1.Series("Statistiques").Points.AddXY("UU", 95)
Me.Chart1.Series("Statistiques").Points.AddXY("II", 95)
Me.Chart1.Series("Statistiques").Points.AddXY("QQ", 95)
comment je peux afficher la description de reste des champs (BB, DD, ....) et merci d'avance
|
|
|
|
|
document.activeElement.id is not working in safari
|
|
|
|
|
If you wish for help with your problem then you need to edit your question and provide full details. Saying, "not working", tells us nothing.
|
|
|
|
|
Hence, you can change your event handler from:
$('#modalLogout').on('hide.bs.modal', function () {
var activeElement = $(document.activeElement);
to the delegated one:
$('#modalLogout').on('click', '#LogoutOk', function (e) {
var activeElement = $(e.target);
|
|
|
|
|
can anyone provide pointers on how to develop Auto reply chatbot in asp.net site?
=====================================================
The grass is always greener on the other side of the fence
|
|
|
|
|
|
I am developing online learning portal in ASP.NET MVC web-application and I want develop video conference feature inside web-app.
Please help me in creating this application, because I am new in web-development.
|
|
|
|
|
You'll probably want to start with the Media Capture and Streams API[^]. But that's an extremely ambitious project, especially for a beginner.
NB: Asking for help writing something without explaining precisely what help you need, what you have tried, or where you are stuck, will lead people to assume that you want someone to do all the work for you. That's not going to happen.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
maybe you can integrate "BigBlueButton" in your project
=====================================================
The grass is always greener on the other side of the fence
|
|
|
|
|
Can you please help on the best approach to build an API in JAVA/Dotnet which has mainframe as database.
Basically we need to expose product data information so that consumer can use them for their business analysis.
|
|
|
|
|
Exposing production data for analysis is fraught with nightmares. Business analysis is a monster and should only be done against a data wharehouse so the first thing you need to look into is wharehousing strategies. They will depend on your data size, replication speed requirement and budget.
Once you have sorted that then connection to the wharehouse is trivial. Designing the wharehouse on the other hand is an entire career.
Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP
|
|
|
|
|
suppose i am developing a site with ASP.Net MVC core. i have created a empty project where i add two controller.
one is Home and login controller. Home controller's index action is not protected by Authorized attribute but Home controller's product action is protected.
when user try to access product action then user should be redirected to login page if not signed in. so tell me how to setup project in classic mvc or mvc core where i will mention that user should be redirected to login page if user is not signed in.
i will not use identity rather i will check user credentials from db using ado.net.
please guide me step wise that what i need to follow. Thanks
|
|
|
|
|
|
Sir, my question was bit different. i am trying to know how asp.net mvc engine understand now user need to redirect to login page? if login controller name would be different then how asp.net mvc understand it ? where this binding done in default project when we create a default project from IDE?
i believe there must be a place where we mention that login page should come if user is not signed in when access any protected resource.
please share knowledge if you know. thanks
|
|
|
|
|
|
Please see the code.
using (Html.BeginForm("Login", "Default", new { ReturnUrl = ViewBag.ReturnUrl, data = Request.QueryString["data"] }, FormMethod.Post,
new
{
id = "idtest1",
onsubmit = "this.querySelectorAll('button').forEach(i => i.disabled = false)",
@class = "form-signin",
role = "form"
}))
{
}
specially see this line which is not clear to me. onsubmit = "this.querySelectorAll('button').forEach(i => i.disabled = false)",
in the above code BeginForm html helper has been used to render form at runtime. what will happen when form will be submitted ?
what this line will do when form submit ? onsubmit = "this.querySelectorAll('button').forEach(i => i.disabled = false)",
please help me to understand this JavaScript code objective when form submit. Thanks
|
|
|
|
|
That line sets the onsubmit attribute on the <form> element to the specified Javascript string. It's an old way of attaching an event handler to an element.
When the form is submitted, all buttons on that form will have their disabled property set to false . But that won't actually accomplish anything; a button's name and value are only submitted with the form if you click on that button, and you can't click on a disabled button.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Sir, thank you so much for reply.
|
|
|
|
|
Hello,
Quick intro, My name is Jason and recently a work colleague retired and had developed several ASPX websites of which I have been tasked to support.
My previous background of programming is around Delphi, Intersystems Cache and more recently C# but these websites that I'm now supporting are all written in VB.net, asp.net, aspx or whatever language it is called, as you have probably guessed by now I have never used Visual Studio or produced code that created aspx files.
Anyway as a newbie to this language I have run into a problem that you experts will have learnt in school
I have a webpage (student.aspx) that contains this snippet of code :
<asp:DropDownList runat="server" ID="ddlCourse" DataTextField="Course"
DataValueField="Course" DataSourceID="SDSCourse"
Width="260px" onChange="CheckNerveCenter"/>
I have added the "onChange" bit
and this bit of code is also existing
<asp:CheckBox ID="chkNerveCentre" runat="server" Text="Nerve Centre" TextAlign="Left" />
So basically the page contains several combo boxes and several check boxes
I need to do some logic for 2 of them, code above.
If the combo box contains text (from database) with the word Nursing, Nurse, Nurses etc. lets say "*Nurs* then teh tick box needs to be disabled so it cannot be ticked
This is the code I have done in the code behind file student.aspx.vb, it just contains a comment now
Function CheckNerveCenter() As Object
'If ID="ddlCourse" contains any word like "*Nurs*" then
'uncheck ID="chkNerveCentre" and then disable ID="chkNerveCentre"
End Function
At first I tried to get it to display a message to show that it was calling the function from the "onChange" but it doesn't seem to work.
As I'm new to this language would you please just help a newbie in the right direction
I did try and use the OnChangeText in visual studio but it just kept showing me this error
"Property Value not valid","Error HRESULT E_FAIL has been returned from a call to a COM component." I have no idea what this means
That's why I decided to try and use a function of my own but now stuck
Any ideas please?
Jason
modified 7-Jul-22 7:31am.
|
|
|
|
|
The OnChange event will run on the server, when the page posts back to the server. That normally only happens when you click a button; if you want it to happen as soon as your list changes, you'll need to set the AutoPostBack property of the list to True .
ListControl.AutoPostBack Property (System.Web.UI.WebControls) | Microsoft Docs[^]
Your event handler method will need to match the expected signature:
Protected Sub CheckNerveCenter(ByVal sender As Object, ByVal e As EventArgs)
...
End Sub
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
if you know than described the code
|
|
|
|
|