Hi, people, The question I have is about Asp.net in c #.
First I will say that I have a code where I select an Excel file and it automatically generates a table where it puts all the data that the file contains (This with any Excel file that is placed, but I will only work with one same file and it always has the same format, the same columns, and the same fields). Up here well; Now, I have a "Users" table in my database and what I want to do with it, is to compare the "Curp" field with the other, the problem is that I do not know how to do the SQL query, or rather as I can do to compare the table I have on the page with the table in the database.
I'm still very new to Asp.Net and any help I could use would come in handy, thanks in advance.
This is the code I have to read the Excel file and the one that generates the table automatically.
<div class="scrollver pos-grid">
<div id="mostrar_buscar" style='display:none;'>
Cadena a buscar <input id="searchTerm" type="text" onkeyup="doSearch()" />
</div>
<table id="exceltable" border="1"></table>
<script>
function ExportToTable() {
var regex = /^([a-zA-Z0-9\s_\.\-:])+(.xlsx|.xls)$/;
/*Checks whether the file is a valid excel file*/
if (regex.test($("#excelfile").val().toLowerCase())) {
var xlsxflag = false; /*Flag for checking whether excel is .xls format or .xlsx format*/
if ($("#excelfile").val().toLowerCase().indexOf(".xlsx") > 0) {
xlsxflag = true;
}
/*Checks whether the browser supports HTML5*/
if (typeof (FileReader) != "undefined") {
var reader = new FileReader();
reader.onload = function (e) {
var data = e.target.result;
/*Converts the excel data in to object*/
if (xlsxflag) {
var workbook = XLSX.read(data, { type: 'binary' });
}
else {
var workbook = XLS.read(data, { type: 'binary' });
}
/*Gets all the sheetnames of excel in to a variable*/
var sheet_name_list = workbook.SheetNames;
var cnt = 0; /*This is used for restricting the script to consider only first sheet of excel*/
sheet_name_list.forEach(function (y) { /*Iterate through all sheets*/
/*Convert the cell value to Json*/
if (xlsxflag) {
var exceljson = XLSX.utils.sheet_to_json(workbook.Sheets[y]);
}
else {
var exceljson = XLS.utils.sheet_to_row_object_array(workbook.Sheets[y]);
}
if (exceljson.length > 0 && cnt == 0) {
BindTable(exceljson, '#exceltable');
cnt++;
}
});
$('#exceltable').show();
}
if (xlsxflag) {/*If excel file is .xlsx extension than creates a Array Buffer from excel*/
reader.readAsArrayBuffer($("#excelfile")[0].files[0]);
}
else {
reader.readAsBinaryString($("#excelfile")[0].files[0]);
}
}
else {
alert("Lo sentimos, tu navegador no soporta HTML5!");
}
}
else {
alert("Por favor sube un archivo Excel Valido!");
}
}
</script>
<script>
function BindTable(jsondata, tableid) {/*Function used to convert the JSON array to Html Table*/
var columns = BindTableHeader(jsondata, tableid); /*Gets all the column headings of Excel*/
for (var i = 0; i < jsondata.length; i++) {
var row$ = $('<tr/>');
for (var colIndex = 0; colIndex < columns.length; colIndex++) {
var cellValue = jsondata[i][columns[colIndex]];
if (cellValue == null)
cellValue = "";
row$.append($('<td/>').html(cellValue));
}
$(tableid).append(row$);
}
}
function BindTableHeader(jsondata, tableid) {/*Function used to get all column names from JSON and bind the html table header*/
var columnSet = [];
var headerTr$ = $('<tr/>');
for (var i = 0; i < jsondata.length; i++) {
var rowHash = jsondata[i];
for (var key in rowHash) {
if (rowHash.hasOwnProperty(key)) {
if ($.inArray(key, columnSet) == -1) {/*Adding each unique column names to a variable array*/
columnSet.push(key);
headerTr$.append($('<th/>').html(key));
}
}
}
}
$(tableid).append(headerTr$);
return columnSet;
}
function doSearch() {
var tableReg = document.getElementById('exceltable');
var searchText = document.getElementById('searchTerm').value.toLowerCase();
var cellsOfRow = "";
var found = false;
var compareWith = "";
// Recorremos todas las filas con contenido de la tabla
for (var i = 1; i < tableReg.rows.length; i++) {
cellsOfRow = tableReg.rows[i].getElementsByTagName('td');
found = false;
// Recorremos todas las celdas
for (var j = 0; j < cellsOfRow.length && !found; j++) {
compareWith = cellsOfRow[j].innerHTML.toLowerCase();
// Buscamos el texto en el contenido de la celda
if (searchText.length == 0 || (compareWith.indexOf(searchText) > -1)) {
found = true;
}
}
if (found) {
tableReg.rows[i].style.display = '';
} else {
// si no ha encontrado ninguna coincidencia, esconde la
// fila de la tabla
tableReg.rows[i].style.display = 'none';
}
}
}
</script>
/* Esto es para mostrar unas partes del HTML, no es importante en este caso, pero lo coloco por que va dentro del código que coloque. */
<script type="text/javascript">
function ocultar() {
document.getElementById('Ocultar').style.display = 'none';
}
function mostrar() {
document.getElementById('Pos-Boton2').style.display = 'block';
}
function mostrar_buscar() {
document.getElementById('mostrar_buscar').style.display = 'block';
}
</script>