Reading Excel (.xls) Files with add ...
WITH A Little Preparation On The Excel Side and Very Little Work On The ADO.NET Side You CAN Connect To And Read Excel Workbooks Just As Though They Were Database Tables.
By: John Kilgo Date: May 28, 2003 Download The Code.
Printer Friendly Version
Since Excel has an OleDB provider, we can use ADO.NET's OleDb functionality to connect to Excel. It does take a little preparation on the Excel side however. You might think that since Excel has rows and columns we might be able to use that and Refer to A1 and B3, ETC. We can't (As Far As I Know). We Must Resort To Another Method. Excel Does Provide for Something Called A "Named Range" Which We can make use of. this name equivalent of a table name if we were dealing with a real database We must also use Excel's first row as column headings In my example spreasheet that can be downloaded, the first row contains LastName |.. FirstName | Address | City |. State Additional rows Contain the actual data.
To create the Named Range select (highlight) the cells you want in your "table" Then, from the menu, choose Insert |. Name |. Define In the upper left corner of the resulting dialog box, type a name for your range. This will become the "table name" you will use in your SQL SELECT statement. The name (full path) of the spreadsheet will be the Data Source in your connection string as you will see in the code to follow. If you download the files And Open Up The Spreadsheet We Have Included, The Select All of the Cells with Data I Gave My Named Range.
You must do the two things above (Named Range and a first row of column names) for this method to work. The rest is easy. Let's look at the code. First the .aspx page (ExcelSelect.aspx). There is nothing fancy about it. Just two label controls and two DataGrids. The two Labels are there to display the SQL SELECT statements for each DataGrid. I used two SELECT statements and two grids just to show that we can deal with the (properly setup) spreadsheet just as We would a database table.
<% @ Page language = "vb" autoeventwireup = "false" src = "inherits =" excelselect "%>
Excelselect </ title> <meta name = "generator" content = "Microsoft Visual Studio.Net 7.0> <meta name =" code_language "content =" Visual Basic 7.0 "> <meta name = vs_defaultClientScript content = "JavaScript"> <meta name = vs_targetSchema content = "http://schemas.microsoft.com/intellisense/ie5"> </ head> <body MS_POSITIONING = "GridLayout"> <form id = "Form1 "Method =" post "runat =" server "> <asp: label id =" lblsql1 "runat =" server "/> <ask: dataGrid id =" dTGADDRESS1 "runat =" server "Headersty-BackColor =" Indianred "Headerstyle -ForeColor = "White" HeaderStyle-Font-Name = "Verdana" HeaderStyle-Font-Size = "10" ItemStyle-BackColor = "Gainsboro" ItemStyle-Font-Name = "Verdana" ItemStyle-Font-Size = "10" CellPadding = "4" GridLines = "Both" /> <p> </ p> < asp: Label ID = "lblSql2" Runat = "server" /> <asp: DataGrid ID = "dtgAddresses2" Runat = "server" HeaderStyle-BackColor = "IndianRed" HeaderStyle-ForeColor = "White" HeaderStyle-Font-Name = "</p>
<p>Verdana "Headersty-font-size =" 10 "itemStyle-backcolor =" gainsboro "itemStyle-font-name =" verdana "itemStyle-font-size =" 10 "cellpadding =" 4 "gridlines =" Both "/> </ Form> </ body> </ html> Now for the code-behind page Excelselect.aspx.vb. as you can see we are useing the OLEDB JET Provider. It is important to note what in the connection string the last parameter is Extended Properties = Excel 8.0; ".. You MUST include this parameter Really, the connection string is the only" tricky "part of this program Once that is defined properly the rest is just executing a reader and binding the grid in the usual fashion. . You wouldn't be itself.</p>
<p>Imports SystemImports System.DataImports System.Data.OleDbPublic Class ExcelSelect Inherits System.Web.UI.Page Protected dtgAddresses1 As System.Web.UI.WebControls.DataGrid Protected dtgAddresses2 As System.Web.UI.WebControls.DataGrid Protected lblSql1 As System.Web .UI.WebControls.Label Protected lblSql2 As System.Web.UI.WebControls.Label Private Sub Page_Load (ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim strConn As String = "Provider = Microsoft. Jet.OleDb.4.0; "_ &" data source = f: /inetpub/wwwroot/dotnetjohn/NameAndAddress.xls; "_ &" Extended Properties = Excel 8.0; " 'First DataGrid Dim objConn As New OleDbConnection (strConn) Dim strSql As String = "Select LastName, FirstName, Address, City, State From Addresses" lblSql1.Text = strSql Dim objCmd As New OleDbCommand (strSql, objConn) Try objConn.Open () dtgAddresses1.DataSource = objCmd.ExecuteReader () dtgAddresses 1.DataBind () Catch exc As Exception Response.Write (exc.ToString ()) Finally objConn.Dispose () End Try 'Second DataGrid objConn = New OleDbConnection (strConn) strSql = "Select * From Addresses Where State =' CA ' "lblSql2.Text = strSql objCmd = New OleDbCommand (strSql, objConn) Try objConn.Open () dtgAddresses2.DataSource = objCmd.ExecuteReader () dtgAddresses2.DataBind () Catch exc As Exception Response.Write (exc.ToString ()) Finally Objconn.dispose () End Try End Subend Class</p>
<p>Well there you have it. You can read from Excel in .Net. There are some practical problems obviously. Having to set the Named Ranges is an issue as well as having the first row of column headers. Also, you will receive an error if a user has the workbook open in write mode. But if you are desperate and really need to read Excel spreadsheets, this is a way to do it and have it look professionally done.You may run the program here.You may download the code here Your Machine.</p></div><div class="text-center mt-3 text-grey">
转载请注明原文地址:https://www.9cbs.com/read-110071.html</div><div class="plugin d-flex justify-content-center mt-3"></div><hr><div class="row"><div class="col-lg-12 text-muted mt-2"><i class="icon-tags mr-2"></i><span class="badge border border-secondary mr-2"><h2 class="h6 mb-0 small"><a class="text-secondary" href="tag-2.html">9cbs</a></h2></span></div></div></div></div><div class="card card-postlist border-white shadow"><div class="card-body"><div class="card-title"><div class="d-flex justify-content-between"><div><b>New Post</b>(<span class="posts">0</span>)
</div><div></div></div></div><ul class="postlist list-unstyled">
</ul></div></div><div class="d-none threadlist"><input type="checkbox" name="modtid" value="110071" checked /></div></div></div></div></div><footer class="text-muted small bg-dark py-4 mt-3" id="footer"><div class="container"><div class="row"><div class="col">CopyRight © 2020 All Rights Reserved
</div><div class="col text-right">Processed:
<b>0.039</b>, SQL:
<b>9</b></div></div></div></footer><script src="./lang/en-us/lang.js?2.2.0"></script><script src="view/js/jquery.min.js?2.2.0"></script><script src="view/js/popper.min.js?2.2.0"></script><script src="view/js/bootstrap.min.js?2.2.0"></script><script src="view/js/xiuno.js?2.2.0"></script><script src="view/js/bootstrap-plugin.js?2.2.0"></script><script src="view/js/async.min.js?2.2.0"></script><script src="view/js/form.js?2.2.0"></script><script>
var debug = DEBUG = 0;
var url_rewrite_on = 1;
var url_path = './';
var forumarr = {"1":"Tech"};
var fid = 1;
var uid = 0;
var gid = 0;
xn.options.water_image_url = 'view/img/water-small.png';
</script><script src="view/js/wellcms.js?2.2.0"></script><a class="scroll-to-top rounded" href="javascript:void(0);"><i class="icon-angle-up"></i></a><a class="scroll-to-bottom rounded" href="javascript:void(0);" style="display: inline;"><i class="icon-angle-down"></i></a></body></html><script>
var forum_url = 'list-1.html';
var safe_token = 'ZAJAKsBtbele_2FlaB0v53rEe1PUnGrHR6_2BGY02ZUIqTbvTvHMfKOGSZiE_2FX9jF_2Bii42ZHyeHbYzy8pC8q';
var body = $('body');
body.on('submit', '#form', function() {
var jthis = $(this);
var jsubmit = jthis.find('#submit');
jthis.reset();
jsubmit.button('loading');
var postdata = jthis.serializeObject();
$.xpost(jthis.attr('action'), postdata, function(code, message) {
if(code == 0) {
location.reload();
} else {
$.alert(message);
jsubmit.button('reset');
}
});
return false;
});
function resize_image() {
var jmessagelist = $('div.message');
var first_width = jmessagelist.width();
jmessagelist.each(function() {
var jdiv = $(this);
var maxwidth = jdiv.attr('isfirst') ? first_width : jdiv.width();
var jmessage_width = Math.min(jdiv.width(), maxwidth);
jdiv.find('img, embed, iframe, video').each(function() {
var jimg = $(this);
var img_width = this.org_width;
var img_height = this.org_height;
if(!img_width) {
var img_width = jimg.attr('width');
var img_height = jimg.attr('height');
this.org_width = img_width;
this.org_height = img_height;
}
if(img_width > jmessage_width) {
if(this.tagName == 'IMG') {
jimg.width(jmessage_width);
jimg.css('height', 'auto');
jimg.css('cursor', 'pointer');
jimg.on('click', function() {
});
} else {
jimg.width(jmessage_width);
var height = (img_height / img_width) * jimg.width();
jimg.height(height);
}
}
});
});
}
function resize_table() {
$('div.message').each(function() {
var jdiv = $(this);
jdiv.find('table').addClass('table').wrap('<div class="table-responsive"></div>');
});
}
$(function() {
resize_image();
resize_table();
$(window).on('resize', resize_image);
});
var jmessage = $('#message');
jmessage.on('focus', function() {if(jmessage.t) { clearTimeout(jmessage.t); jmessage.t = null; } jmessage.css('height', '6rem'); });
jmessage.on('blur', function() {jmessage.t = setTimeout(function() { jmessage.css('height', '2.5rem');}, 1000); });
$('#nav li[data-active="fid-1"]').addClass('active');
</script>
