Sunday, December 19, 2010

export DataTable To Excel

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.

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>")
%>

http://stackoverflow.com

http://stackoverflow.com

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

SQL (Structured Query Language) in one page

 
Contents:
    Review: FAQ;
    Table of contents:
Database Manipulation (CREATE, DROP DATABASE),
Table Manipulation (CREATE, ALTER, DROP TABLE, Data Types),
Index Manipulation (CREATE, DROP INDEX),
Data Manipulation (INSERT, UPDATE, DELETE, TRUNCATE TABLE),
Select (SELECT, FROM, WHERE, ORDER BY, GROUP BY, HAVING, Operators,
Aggregate functions)
, AliasJoinUNIONSELECT INTO/INCREATE VIEW;
    Similar Sites:
MANUALCheat sheetsHTMLCSSXMLDTDJavaScriptW3C DOMSQL,
SSITell a friendFree IconsItlibitum, Corp..

 

All Javascript in one page


Contents:
    Review: 
ReviewAllocationSimple 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..