Pivot Viewer

I am new to Silverlight, but I tried the Pivot Viewer to display lists from Sharepoint. the task seems fine unless:

• make a dynamic list
• problem with images

the first one I got this article which is quite powerful . so it solved me Part one.

the second one I tried to understand why my images are not displayed, but when importing some images from the internet it works. I got an answer that these images that works are DZI (Deep Zoom Image). and there is a DZI Composer!! rocks... Hell no. the composer outputs a bunch of files to work for the concept of DZI.What I only need is one picture! Is this Hard :(

*DZI for simplicity, is making the image in layers so that it loads fast, and when zooming we can get the more detailed layers. 

I am trying to google around and there should be any luck.

Pivot Viewer now is good but it should be customeized. they will, but in next release.... 

nettiers dynamic search query

I am using nettiers 2.3.0 now in a project and then i faced a problem where i have to make a search function that takes a string and search in each entity of the string fields of it to get the results.

nettiers has a good functions like GetPaged that takes the where clause and search with it. but come on how to make this search with generic code and powerful!!!!

the solution is a magic word, "Reflection"!!! ta da .... you have to make your development framework ideal enough to do that. How?

As I said i am using nettiers this make me support the following: each entity has an interface describes each property. and a good naming convention that I can follow in naming the UI elements such as typed data sources, that nettiers generates, and so i wrote this code...

List<string> lookFor = new List<string>();

Type t = Type.GetType(string.Format("Entities.I{0}, Entities", EntityName));

foreach (PropertyInfo info in t.GetProperties())

{

if (info.PropertyType == Type.GetType("System.String"))

{

lookFor.Add(string.Format(" [{0}] Collate SQL_Latin1_General_CP1_CI_AS like '%{1}%' ", info.Name, txtName.Text));

}

}

string whereClause = string.Join(" OR ", lookFor.ToArray()) ;

DataSourceControl ds = WebFormUtil.GetControlByID(Page.Controls, EntityName + "DataSource") as DataSourceControl;

PropertyInfo filterProperty = ds.GetType().GetProperty("Parameters");

Object collection = filterProperty.GetValue(ds, null);

if (collection is ParameterCollection)

{

((CustomParameter)((ParameterCollection)collection)["WhereClause"]).Value = whereClause;

}

ds.DataBind();

with just the entity name as a variable, and the well formed structure of the tiers and the good structure of the any form that contains a list and a search box this code rocks. I am able to get the typed data source and attach a value to its attributes and finally get works done and generic.

Thanks to Reflection, it made it possible.

Excel Export

Image via WikipediaFaced many problems when loading or exporting to excel files! got many confused when loading data from excel file and it seems that the data you have is less than the data in the file. Needed to do many calculations and formatting!

the problem is we don't know how to deal with excel, and how excel deals with data connectors like ODBC.

I faced these issues and searched a lot to get a result and got these guidelines.

1- for loading an excel file make sure you format the excel file to the desired format you want. example: if you only will get the data in number format, so format the column with the suitable numeric format. so that all the data that you will have is the numbers, if you types a word in this column, excel will not transfer this value to the ODBC driver.

If you want it generic so you will format it as text. so whatever gets into the cell excel will transfer it.

here is a snippet of how loading from excel. [the code is in C#]

public DataTable LoadSheet(string sheetName)
{
DataTable dtSheet = new DataTable();

string excelConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\";";

string command = "select * from [" + sheetName + "$]";

new System.Data.OleDb.OleDbDataAdapter(command, excelConString).Fill(dtSheet);
return dtSheet;
}

this how to load a sheet of excel into DataTable which you can deal with in the rest of your code.

*Trick: when reading, excel considers the first formatted row as the table header [formatted means bordered] if you did not do that the headers will be F1, F2...etc

2- Writing to Excel. the problem begins.

1. first you have to export to a named sheet
2. the sheet should be in a table format, if you typed in this sheet before exporting the writing process will begin writing after your writing. example: you write in cell A2, when exporting the excel will begin writing from A3, B3,C3...etc.
3. any formats should be applied on the previous cell that you want to write in. example: you to begin writing in cell A4, and you want it to be formated as numeric (#.00). So, you have to apply this format on cell A3 only and not the column.
4. now you need the way to insert the rows.

OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filePath + "';Extended Properties=Excel 8.0;");
connection.Open();
OleDbCommand command;
// insert rows
foreach (object item in items)
{
// insert row
command = CreateInsertCommand(sheetName, fields, connection, item);
command.ExecuteNonQuery();
}
private OleDbCommand CreateInsertCommand(string sheetName, ExcelField[] fields, OleDbConnection connection, object item)
{
StringBuilder builder = new StringBuilder();
string fieldValue;
OleDbParameter parameter = null;
int count = fields.Length;

OleDbCommand command = connection.CreateCommand();

for (int i = 0; i < count; i++)
{
fieldValue = (item is DataRow) ? fields[i].GetDataRowValue(item) : fields[i].GetValue(item);
if (i == 0)
{
builder.AppendFormat("insert into [{0}$] values(?", sheetName);
}
else
builder.Append(", ?");

parameter = new OleDbParameter(string.Format("param{0}", i), GetOleDBType(fields[i].FieldType));
parameter.Value = fieldValue;

command.Parameters.Add(parameter);

if (i == count - 1)
builder.Append(")");
}

command.CommandText = builder.ToString();
return command;
}
and tada it did insert the rows.

*Tricks: make sure you cast the fields values to its proper type.

Now the nightmare part. the calculations and advanced formatting. this is an excel behavior. if you need to do this you have to create a separate sheet for viewing and other sheets to get the data and make a reference from the view sheet to the other sheets.

the calculations will be on the view sheet. your export function will export to those sheets. And you have to open the file to apply the formatting otherwise it will not affected!!!!

Enjooy the excel loading and exporting.

those tips for formatting Excel.

Related articles

• Master the Excel IF Function [Excel Tip] [via Zemanta]
• Hackers seize on Excel vulnerability [via Zemanta]
• Create an Excel Gantt Chart with Conditional Formatting [Excel Tip] [via Zemanta]
• Five easy Excel formatting tricks [via Zemanta]

Asp.net TreeView state

Don't you ever wanted to preserve the tree status over multiple pages? I searched for many times and found the solution.

1. use cookies
2. javascript override functions
3. TreeView javascript important functions

1. these script assign a value to cookie:

function getCookieVal (offset)
{
var endstr = document.cookie.indexOf (";", offset);
if (endstr == -1)
endstr = document.cookie.length;
return unescape(document.cookie.substring(offset, endstr));
}

function GetCookie (name)
{
var arg = name + "=";
var alen = arg.length;
var clen = document.cookie.length;
var i = 0;
while (i < style="color: rgb(51, 51, 255);">var j = i + alen;
if (document.cookie.substring(i, j) == arg)
return getCookieVal (j);
i = document.cookie.indexOf(" ", i) + 1;
if (i == 0) break;
}
return null;
}

function SetCookie (name, value)
{
var argv = SetCookie.arguments;
var argc = SetCookie.arguments.length;
var expires = (argc > 2) ? argv[2] : null;
var path = (argc > 3) ? argv[3] : null;
var domain = (argc > 4) ? argv[4] : null;
var secure = (argc > 5) ? argv[5] : false;
document.cookie = name + "=" + escape (value) +
((expires == null) ? "" : ("; expires=" + expires.toGMTString())) +
((path == null) ? "" : ("; path=" + path)) +
((domain == null) ? "" : ("; domain=" + domain)) +
((secure == true) ? "; secure" : "");
}

function DeleteCookie (name)
{
var exp = new Date();
exp.setTime (exp.getTime() - 1000000000); // This cookie is history (changed -1 to make it previous time)
var cval = GetCookie (name);
document.cookie = name + "=" + cval + "; expires=" + exp.toGMTString();
}


2-

To override the function in JavaScript, simply define a new variable to hold the old function, and redefine the old function variable as a new function. Here’s the syntax:

var base_TreeView_ToggleNode = TreeView_ToggleNode;

TreeView_ToggleNode = function(data, index, node, lineType, children){

base_TreeView_ToggleNode(data, index, node, lineType, children);

setProfileFolder(data, index,node,children);

return;

}
The setProfileFolder function is our addition to the base method.

see the original reference here.

3- Now you have to see the important functions and concepts about the treeview control

• TreeView_GetNodeText(node): it takes a node object and returns the node name

believe me it is good, if you want to try to get it by your self you will fail, the structure is so complicated.

• Facing the similar nodes in text but in different branches:

This is why the index thing exists. The tree view in javascript collect all items in javascript in an array. So, if you have:

Parent
+ child 01
--+child 02
-----leaf

it will be indexed like this

Parent [0]
+ child 01 [1]
--+child 02 [2]
-----leaf [3]

• you can navigate through the child nodes like this

var childNodes = children.getElementsByTagName("a");
var increment = 0;
for(;incrementvar child_node_name = TreeView_GetNodeText(childNodes[increment]) ;
}


In the server side parse the cookie back and preserve the collapse/expand behaviour.