Primitive Reconciliation

Question

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.

Answer 1

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

Answer 2

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>  

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.