Primitive Reconciliation

Cenk 981 Reputation points
2022-11-09T05:59:48.337+00:00

Hello,

There is this primitive reconciliation being made with Excel. In the beginning, it was not a problem because the data was scarce, and comparing GUIDs by eye in excel was not difficult. Now the data is too much for visual inspection. So is there a way that you can suggest to compare these GUIDs in excel with the database?

I really appreciate any help you can provide.

Thank you.

ASP.NET
ASP.NET
A set of technologies in the .NET Framework for building web applications and XML web services.
3,374 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,501 questions
ASP.NET API
ASP.NET API
ASP.NET: A set of technologies in the .NET Framework for building web applications and XML web services.API: A software intermediary that allows two applications to interact with each other.
312 questions
{count} votes

2 answers

Sort by: Most helpful
  1. SEO Expert 1 Reputation point
    2022-11-16T12:39:30.66+00:00

    Hi,

    The normal arrangement is bringing the succeed information into a data set table. When imported you have the overwhelming power of SQL to do anything correlations are required.

    Thanks

    0 comments No comments

  2. Lan Huang-MSFT 27,956 Reputation points Microsoft Vendor
    2022-11-17T02:20:44.523+00:00

    Hi @Cenk ,

    I am looking for a solution without importing excel data into the SQL database.

    I think you can try to upload Excel file, then read and import its data into DataSet or DataTable. This way you don't have to import the data into the database.
    You can first import the data into an Excel file and then display it in an ASP.Net GridView control.
    Then view the data through the button, and export the gridview as an excel file after the gridview is updated.
    You can check the example below, the data checking part can be changed according to your own needs.

     protected void Page_Load(object sender, EventArgs e)  
            {  
      
            }  
            protected void btnUpload_Click(object sender, EventArgs e)  
            {  
                if (FileUpload1.HasFile)  
                {  
                    string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);  
                    string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);  
                    string FolderPath = ConfigurationManager.AppSettings["FolderPath"];  
                    string FilePath = Server.MapPath(FolderPath + FileName);  
                    FileUpload1.SaveAs(FilePath);  
                    Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);  
                }  
            }  
            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();  
            }  
            protected void PageIndexChanging(object sender, GridViewPageEventArgs e)  
            {  
                string FolderPath = ConfigurationManager.AppSettings["FolderPath"];  
                string FileName = GridView1.Caption;  
                string Extension = Path.GetExtension(FileName);  
                string FilePath = Server.MapPath(FolderPath + FileName);  
                Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);  
                GridView1.PageIndex = e.NewPageIndex;  
                GridView1.DataBind();  
            }  
            protected void btnExportGridintoExcel_Click(object sender, EventArgs e)  
            {  
                ExportGridToExcel();  
            }  
      
            private void ExportGridToExcel()  
            {  
                Response.Clear();  
                Response.AddHeader("content-disposition", "attachment;filename=ExportGridData.xls");  
                Response.ContentType = "File/Data.xls";  
                StringWriter StringWriter = new System.IO.StringWriter();  
                HtmlTextWriter HtmlTextWriter = new HtmlTextWriter(StringWriter);  
                GridView1.RenderControl(HtmlTextWriter);  
                Response.Write(StringWriter.ToString());  
                Response.End();  
            }  
      
            public override void VerifyRenderingInServerForm(Control control)  
            {  
                // controller     
            }  
      
            protected void CHECK_Click(object sender, EventArgs e)  
            {  
      
                foreach (GridViewRow row in GridView1.Rows)  
                {  
                    int value =Convert.ToInt32(row.Cells[2].Text);  
                    if(value != 1)  
                    {  
                        row.Cells[2].Text = 1.ToString();  
                    }  
      
                }  
            }  
    

    <div>  
                <asp:FileUpload ID="FileUpload1" runat="server" /><br />  
                <asp:Button ID="btnUpload" runat="server" Text="Upload" OnC lick="btnUpload_Click" />  
                <br />  
                <asp:Label ID="Label1" runat="server" Text="Has Header ?" />  
                <asp:RadioButtonList ID="rbHDR" runat="server">  
                    <asp:ListItem Text = "Yes" Value = "Yes" Selected = "True" >  
                    </asp:ListItem>  
                    <asp:ListItem Text = "No" Value = "No"></asp:ListItem>  
                </asp:RadioButtonList>  
                <asp:GridView ID="GridView1" runat="server" OnPageIndexChanging="PageIndexChanging" AllowPaging = "true">  
                </asp:GridView>  
                <asp:Button ID="CHECK" runat="server" Text="CHECK" OnC lick="CHECK_Click" /><br/><br />  
                <asp:Button ID="btnExportGridintoExcel" runat="server" Text="btnExportGridintoExcel" OnC lick="btnExportGridintoExcel_Click" />           
            </div>  
    

    Web.config

    <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}'"/>  
    	</connectionStrings>  
    

    261221-1.gif
    Best regards,
    Lan Huang


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.