Click here to Skip to main content
15,512,456 members
Home / Discussions / ASP.NET
   

ASP.NET

 
PinnedHOW TO ANSWER A QUESTION PinPopular
Chris Maunder12-Jul-09 23:39
cofounderChris Maunder12-Jul-09 23:39 
QuestionStore Rich Text in DATA Base Pin
CARBER25-Nov-22 23:05
CARBER25-Nov-22 23:05 
AnswerRe: Store Rich Text in DATA Base Pin
RedDk26-Nov-22 10:08
RedDk26-Nov-22 10:08 
GeneralRe: Store Rich Text in DATA Base Pin
CARBER26-Nov-22 12:03
CARBER26-Nov-22 12:03 
AnswerRe: Store Rich Text in DATA Base Pin
Sam Hobbs26-Nov-22 13:22
Sam Hobbs26-Nov-22 13:22 
QuestionSuggestion on searching thru jobs and resumes Pin
Michael Clinton21-Oct-22 10:44
Michael Clinton21-Oct-22 10:44 
AnswerRe: Suggestion on searching thru jobs and resumes Pin
Richard MacCutchan21-Oct-22 22:43
mveRichard MacCutchan21-Oct-22 22:43 
AnswerRe: Suggestion on searching thru jobs and resumes Pin
rareprob solutions31-Oct-22 23:54
rareprob solutions31-Oct-22 23:54 
AnswerRe: Suggestion on searching thru jobs and resumes Pin
jsc4226-Nov-22 5:59
professionaljsc4226-Nov-22 5:59 
Question(SOLVED) Error: The string was not recognized as a valid DateTime. There is an unknown word starting at index 0. Pin
samflex19-Oct-22 9:11
samflex19-Oct-22 9:11 
AnswerRe: Error: The string was not recognized as a valid DateTime. There is an unknown word starting at index 0. Pin
Dave Kreskowiak19-Oct-22 13:26
mveDave Kreskowiak19-Oct-22 13:26 
GeneralRe: Error: The string was not recognized as a valid DateTime. There is an unknown word starting at index 0. Pin
samflex20-Oct-22 8:03
samflex20-Oct-22 8:03 
AnswerRe: (SOLVED) Error: The string was not recognized as a valid DateTime. There is an unknown word starting at index 0. Pin
Eddy Vluggen20-Oct-22 22:56
professionalEddy Vluggen20-Oct-22 22:56 
QuestionHow to Add Add Microsoft Extensions Hosting Manually in .Net Project Pin
Member 1279932118-Oct-22 9:43
Member 1279932118-Oct-22 9:43 
SuggestionRe: How to Add Add Microsoft Extensions Hosting Manually in .Net Project Pin
Richard Deeming19-Oct-22 1:15
mveRichard Deeming19-Oct-22 1:15 
Question"The entry '*' has already been added." error in WebClient UploadString call Pin
Edwin Hon13-Oct-22 17:15
Edwin Hon13-Oct-22 17:15 
AnswerRe: "The entry '*' has already been added." error in WebClient UploadString call Pin
Richard Deeming14-Oct-22 3:55
mveRichard Deeming14-Oct-22 3:55 
QuestionBlazor, styling dynamic data Pin
Wiep Corbier7-Oct-22 18:39
Wiep Corbier7-Oct-22 18:39 
AnswerRe: Blazor, styling dynamic data Pin
Richard Deeming10-Oct-22 22:47
mveRichard Deeming10-Oct-22 22:47 
GeneralRe: Blazor, styling dynamic data Pin
Wiep Corbier10-Oct-22 23:40
Wiep Corbier10-Oct-22 23:40 
SuggestionSort 2 Sheets and update on another Sheet with ole DB Pin
binumon kb4-Oct-22 12:30
binumon kb4-Oct-22 12:30 
	<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=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\""; ;
                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)
                    {
                        //exception here
                        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

                /*
                #region Updating Excel
                int rank = 1;
                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" });
                            rank = 1;
                            int colcount = 0;
                            List<string> updatedColumns = new List<string>();
                            updatedColumns.Add(RankColumn);
                            System.Type type;
                            foreach (DataRow row in sortedDate.Rows)
                            {
                                colcount = 0;
                                StringBuilder commandString = new StringBuilder();
                                commandString.Append("UPDATE [" + SheetName.ToString()  + "$"+"] SET ");
                                foreach (var item in row.ItemArray)
                                {
                                    foreach (DataColumn c in sortedDate.Columns)  //loop through the columns. 
                                    {                                        
                                        if (!updatedColumns.Contains(c.ColumnName))
                                        {
                                            commandString.Append(c.ColumnName);
                                            type = item.GetType();
                                            if ((type == typeof(string)) || (type == typeof(DateTime)))
                                            {
                                                commandString.Append(" = '").Append(item).Append("' ,");
                                                updatedColumns.Add(c.ColumnName);
                                                break;
                                            }
                                            else if (item.ToString().Trim() == string.Empty)
                                            {
                                                commandString.Append(" = '  ").Append(item).Append("' ,");
                                                updatedColumns.Add(c.ColumnName);
                                                break;
                                            }
                                            else
                                            {
                                                commandString.Append(" = ").Append(item).Append(" ,");
                                                updatedColumns.Add(c.ColumnName);
                                                break;
                                            }
                                        }

                                      
                                    }
                                    colcount++;
                                }
                                //cmd.CommandText = "UPDATE [" + SheetName.ToString() + "$" + "] SET F1 = " + row[0].ToString() + ",F2 = '" + row[1].ToString() + "',F3 = '" + row[2].ToString() + "',F4 = " + row[3].ToString() + ",F5 = '" + row[4].ToString() + "',F6 = " + row[5].ToString() + " where F7 = " + rank + ";";
                                commandString.Remove(commandString.Length - 1, 1);
                                commandString.Append(" where " + RankColumn + " = " + rank + ";");
                                cmd.CommandText = commandString.ToString();
                                cmd.ExecuteNonQuery();
                                updatedColumns = new List<string>();
                                updatedColumns.Add(RankColumn);
                                rank++;
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        txtLogger.Text += " Update exception on Line "+ rank +" : " + ex.StackTrace + ": Sheet " + SheetName+ Environment.NewLine;
                        MessageBox.Show("Error Occured ,Please check the log!!!");
                        return;
                    }
                    finally
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                }
                #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=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\""; ;
                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)  //loop through the 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.

GeneralRe: Sort 2 Sheets and update on another Sheet with ole DB Pin
Richard Deeming4-Oct-22 22:23
mveRichard Deeming4-Oct-22 22:23 
Questionwebforms, modal, updatepanel, repeater and linkbutton and opens another modal Pin
Michael Clinton28-Sep-22 16:48
Michael Clinton28-Sep-22 16:48 
AnswerRe: webforms, modal, updatepanel, repeater and linkbutton and opens another modal Pin
Richard Deeming28-Sep-22 23:09
mveRichard Deeming28-Sep-22 23:09 
QuestionChart1.Series Pin
ESTHETIQUE TUNISIE21-Sep-22 4:59
ESTHETIQUE TUNISIE21-Sep-22 4:59 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.