static DataTable GetTable()
{
DataTable table = new DataTable(); // New data table.
table.Columns.Add("Dosage", typeof(int)); // Add five columns.
table.Columns.Add("Drug", typeof(string));
table.Columns.Add("Patient", typeof(string));
table.Columns.Add("Date", typeof(DateTime));
table.Rows.Add(15, "Abilify", "xxx", DateTime.Now); // Add five data rows.
table.Rows.Add(40, "Accupril", "yyy", DateTime.Now);
table.Rows.Add(40, "Accutane", "zzz", DateTime.Now);
table.Rows.Add(20, "Aciphex", "zyy", DateTime.Now);
table.Rows.Add(45, "Actos", "xxxy", DateTime.Now);
return table; // Return reference.
}
private void exportDataTableToExcel(DataTable dt, string filePath)
{
// Excel file Path
string myFile = filePath;
//System.Data.DataRow dr = default(System.Data.DataRow);
int colIndex = 0;
int rowIndex = 0;
// Open the file and write the headers
StreamWriter fs = new StreamWriter(myFile, false);
fs.WriteLine("<? xml version=\"1.0\"?>");
fs.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>");
fs.WriteLine("<ss:Workbook xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">");
// Create the styles for the worksheet
fs.WriteLine(" <ss:Styles>");
// Style for the column headers
fs.WriteLine(" <ss:Style ss:ID=\"1\">");
fs.WriteLine(" <ss:Font ss:Bold=\"1\" ss:Color=\"#FFFFFF\"/>");
fs.WriteLine(" <ss:Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\" " + "ss:WrapText=\"1\"/>");
fs.WriteLine(" <ss:Interior ss:Color=\"#254117\" ss:Pattern=\"Solid\"/>");
fs.WriteLine(" </ss:Style>");
// Style for the column information
fs.WriteLine(" <ss:Style ss:ID=\"2\">");
fs.WriteLine(" <ss:Alignment ss:Vertical=\"Center\" ss:WrapText=\"1\"/>");
fs.WriteLine(" </ss:Style>");
// Style for the column headers
fs.WriteLine(" <ss:Style ss:ID=\"3\">");
fs.WriteLine(" <ss:Font ss:Bold=\"1\" ss:Color=\"#FFFFFF\"/>");
fs.WriteLine(" <ss:Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\" " + "ss:WrapText=\"1\"/>");
fs.WriteLine(" <ss:Interior ss:Color=\"#736AFF\" ss:Pattern=\"Solid\"/>");
fs.WriteLine(" </ss:Style>");
fs.WriteLine(" </ss:Styles>");
// Write the worksheet contents
fs.WriteLine("<ss:Worksheet ss:Name=\"Sheet1\">");
fs.WriteLine(" <ss:Table>");
fs.WriteLine(" <ss:Row>");
foreach (DataColumn dc in dt.Columns)
{
fs.WriteLine(string.Format(" <ss:Cell ss:StyleID=\"1\">" + "<ss:Data ss:Type=\"String\">{0}</ss:Data></ss:Cell>", dc.ColumnName ));
}
fs.WriteLine(" </ss:Row>");
object cellText = null;
// Write contents for each cell
foreach (DataRow dr in dt.Rows)
{
rowIndex = rowIndex + 1;
colIndex = 0;
fs.WriteLine(" <ss:Row>");
foreach (DataColumn dc in dt.Columns)
{
cellText = dr[dc];
// Check for null cell and change it to empty to avoid error
if (cellText == null ) cellText = "";
fs.WriteLine(string.Format(" <ss:Cell ss:StyleID=\"2\">" +
"<ss:Data ss:Type=\"String\">{0}</ss:Data></ss:Cell>", cellText));
colIndex = colIndex + 1;
}
fs.WriteLine(" </ss:Row>");
}
fs.WriteLine(" <ss:Row>");
fs.WriteLine(" </ss:Row>");
// Close up the document
fs.WriteLine(" </ss:Table>");
fs.WriteLine("</ss:Worksheet>");
fs.WriteLine("</ss:Workbook>");
fs.Close();
}
Example:
exportDataTableToExcel (GetTable(),"C:\\PatientDetails.xls");
will write the content of data table with Formatting Styles.
Sunday, December 19, 2010
Wednesday, December 08, 2010
AJAX Database Example
JavaScript:
function showCustomer(str)
{
if (str=="")
{
document.getElementById("txtHint").innerHTML="";
return;
}
if (window.XMLHttpRequest)
{// code for IE7+, Firefox, Chrome, Opera, Safari
xmlhttp=new XMLHttpRequest();
}
else
{// code for IE6, IE5
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
}
xmlhttp.onreadystatechange=function()
{
if (xmlhttp.readyState==4 && xmlhttp.status==200)
{
document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
}
}
xmlhttp.open("GET","getcustomer.asp?q="+str,true);
xmlhttp.send();
}
======================
ASP:
<%
response.expires=-1
sql="SELECT * FROM CUSTOMERS WHERE CUSTOMERID="
sql=sql & "'" & request.querystring("q") & "'"
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("/db/northwind.mdb"))
set rs=Server.CreateObject("ADODB.recordset")
rs.Open sql,conn
response.write("<table>")
do until rs.EOF
for each x in rs.Fields
response.write("<tr><td><b>" & x.name & "</b></td>")
response.write("<td>" & x.value & "</td></tr>")
next
rs.MoveNext
loop
response.write("</table>")
%>
function showCustomer(str)
{
if (str=="")
{
document.getElementById("txtHint").innerHTML="";
return;
}
if (window.XMLHttpRequest)
{// code for IE7+, Firefox, Chrome, Opera, Safari
xmlhttp=new XMLHttpRequest();
}
else
{// code for IE6, IE5
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
}
xmlhttp.onreadystatechange=function()
{
if (xmlhttp.readyState==4 && xmlhttp.status==200)
{
document.getElementById("txtHint").innerHTML=xmlhttp.responseText;
}
}
xmlhttp.open("GET","getcustomer.asp?q="+str,true);
xmlhttp.send();
}
======================
ASP:
<%
response.expires=-1
sql="SELECT * FROM CUSTOMERS WHERE CUSTOMERID="
sql=sql & "'" & request.querystring("q") & "'"
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("/db/northwind.mdb"))
set rs=Server.CreateObject("ADODB.recordset")
rs.Open sql,conn
response.write("<table>")
do until rs.EOF
for each x in rs.Fields
response.write("<tr><td><b>" & x.name & "</b></td>")
response.write("<td>" & x.value & "</td></tr>")
next
rs.MoveNext
loop
response.write("</table>")
%>
Hosted jQuery
head
script type="text/javascript" src="http://ajax.microsoft.com/ajax/jquery/jquery-1.4.2.min.js">
/script
/head
or
head
script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js">
/script
/head
script type="text/javascript" src="http://ajax.microsoft.com/ajax/jquery/jquery-1.4.2.min.js">
/script
/head
or
head
script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js">
/script
/head
SQL (Structured Query Language) in one page
All Javascript in one page
Contents: Review: Review, Allocation, Simple Examples; JavaScript Language: Values, Data type conversion, Variables, Literals, Expressions, Operators, Statements, Functions, Built-in Functions, Objects, Built-in Objects, Event; Built-in JavaScript Objects: Root (Root Properties, Root Methods), Array (Array Description, Array Properties, Array Methods), Boolean (Boolean Description), Data (Data Description,Data Methods), Function (Function Description), Image (Image Description,Image Properties), Math (Math Description, Math Properties, Math Methods), Number (Number Description,Number Properties), String (String Description,String Properties,String Methods); Other: Other; Similar Sites: MANUAL, Cheat sheets, HTML, CSS, XML, DTD, JavaScript, W3C DOM, SQL, SSI, Tell a friend, Free Icons, Itlibitum, Corp.. |
Subscribe to:
Posts (Atom)