Intersoft Community - WebGrid Enterprise - format export

format export

Hans — Wed, 28 Mar 2012 22:45:57 GMT

Hello John,

I’m glad to hear that it works on you. Please don’t hesitate to contact us, if you have another problem.

Thank you to for asking.

Regards,

Hans

format export

John.Bonin@dot.gov — Wed, 28 Mar 2012 09:46:17 GMT

Thanks Hans for your help and samples. I also tried the DataTable at one time and there wasn't much difference and was not worth it. Using a hidden column, that is already in the database works the best.

Thanks

format export

Hans — Wed, 28 Mar 2012 03:27:34 GMT

Hello,

You could use DataTable in your scenario if you want to.

I made a new sample. I did like before, I use nortwind.mdb database and shippers table then I added a new row that have a null/blank text at Phone field.

In my sample, I want to change the null value in Phone field with “No Phone Number” value.

I made two DataTable. The first DataTable, I get the data from database. The second DataTable, I created/added the columns and the rows programmatically.

I copied row data from the first DataTable to the second DataTable. But while copying data row, I checked if the value in Phone field is null, I changed it to “No Phone Number” value.

I put the script in InitializeDataSource() server side event. Here’s the script below.

protected void WebGrid1_InitializeDataSource(object sender, ISNet.WebUI.WebGrid.DataSourceEventArgs e)
{
        OleDbConnection oConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Northwind.mdb;Persist Security Info=True");
        oConn.Open();
        string t = "SELECT * FROM Shippers";
        OleDbDataAdapter da = new OleDbDataAdapter(t, oConn);
        DataTable table = new DataTable();
        da.Fill(table);
        // Create new DataTable
        DataTable newTable = new DataTable();
        // Create new column
        DataColumn col1 = new DataColumn("ShipperID");
        col1.DataType = System.Type.GetType("System.Int32"); 
        DataColumn col2 = new DataColumn("CompanyName");
        col2.DataType = System.Type.GetType("System.String");
        DataColumn col3 = new DataColumn("Phone");
        col3.DataType = System.Type.GetType("System.String");
        // Add the columns to newTable
        newTable.Columns.Add(col1);
        newTable.Columns.Add(col2);
        newTable.Columns.Add(col3);
        // Get the number of data in Shippers table
        DataRowCollection rows= table.Rows;
        // It's looping as much as number of data in Shippers table
        for(int i=0;i<rows.Count;i++)
        {
            DataRow row = newTable.NewRow();
            
            row[col1] = rows[i].ItemArray[0];
            row[col2] = rows[i].ItemArray[1];
            // If the phone field is null
            if (rows[i].ItemArray[2].ToString() == "")
            {
                row[col3] = "No Phone Number";
            }
            // If the phone field isn't null
            else
            {
                row[col3] = rows[i].ItemArray[2];
            }
            // Add rows to newTable
            newTable.Rows.Add(row);       
        }
        
        e.DataSource = newTable;
    }

I also attached my sample, in order you can more easily to understand my sample.

But in my opinion, there aren’t any different in export process if you are using DataTable or not.

Hope this help. Thank you.

Regards,

Hans

format export

John.Bonin@dot.gov — Tue, 27 Mar 2012 09:28:43 GMT

Thanks, that's what I ended up doing, creating a new column and formatting the new column based upon the original column. Would be better and I believe performance would be better if the export was done from the cache data as opposed to going back to the database and getting the values again, but I can see why they did that.

Again thanks for all you samples...

John

format export

Hans — Tue, 27 Mar 2012 03:09:55 GMT

Hello,

I’m sorry for this misunderstanding. I also can replicate your issue. This issue occurs because the new value [“**”] have a different type (string) with the column field type (integer).

If you still want to use this scenario, I suggest to create/add a new column with string type. Then copy the (integer) column’s value to the new (string) column.

I made a simple sample that maybe have the same with your scenario. In my sample, I’m using Nortwind.mdb and Order Details table.

In WebGrid, I add new column (string type), named Qty_Extended. I also set the Visible property in Quantity column to false.

In InitializeRow() event, if the Quantity have value less than 10, I set the value to “**” and then I add the value in Quantity column to Qty_Extended column.

protected void WebGrid1_InitializeRow(object sender, ISNet.WebUI.WebGrid.RowEventArgs e)
{
        var qty = Convert.ToInt32(e.Row.Cells[3].Value);
        // If the value in Quantity column less than 10
        if (qty < 10)
        {
            // Set text in Quantity column to "**"
            e.Row.Cells[3].Text = "**"; // 
        }
        // Set text and value in Qty_Extended column
        e.Row.Cells[4].Text = e.Row.Cells[3].Text;
        e.Row.Cells[4].Value = e.Row.Cells[3].Text;
}

I attached my sample, in order you can more easily to understand my sample.

Hope this one can help you. Thank you.

Regards,

Hans

format export

John.Bonin@dot.gov — Thu, 22 Mar 2012 09:47:09 GMT

Hi,

Again thanks for the sample. I guess I was't clear, completely my fault, sorry. The problem arises when the field is a number. When displaying data in the grid you can modify the text property of cell, so if a cells value is null or let's say less than 10, you can change the text property of that cell to be a string like "***". But when you export to excel it goes back to the database to get all the values again and if a field is a number, the export expects a number so you can not change the text property, you can only change the value property.

Let's say you added a number field, like Qty, in the InitalizeRow event when displaying to the grid you can do:

if e.row.cells.getnameditem("qty").value < 10 then ' could use value or text here

e.row.cells.getnameditem("qty").text = "Order more"

end if

When going to the grid, for those cells where Qty is less than 10, you will see "Order more" but when you export you will see the actual numbers that is in the database. If you try to change the value to be "Order more" you will get an error that you can add a string to a number field.

So I guess what I would like to happen is to export what is displayed on the grid not what is in the database.

format export

Hans — Wed, 21 Mar 2012 23:08:35 GMT

Hello,

I’m sorry for this inconvenience. I already made a simple sample that maybe similar with your sample.

I’m using nortwind.mdb database and shippers table then I add a new row that have a null/blank text at Phone field.

I also set a FormatCondition in WebGrid and set NullText property (in Phone field) to “**”.

Like you said before the “**” text will appear in WebGrid but doesn’t appear in Excel.

Therefore, I’m using server side event InitializeRow to handle this null text. The result is the “**” text appears in WebGrid and Excel.

I sent you my sample. If you don’t mind please help me to modify the sample. Perhaps, I’m missing something when replicate your issue.

Thank you..

Regards,

Hans

format export

John.Bonin@dot.gov — Wed, 21 Mar 2012 09:35:42 GMT

Thank you for the information. I am using the FormatConditions within the webGrid definition not in code behind. Your method works if there is a value in the database but it appears not to work when the value is null. i use the NullText="**" attribute in the defintion but if I put the code in the InitializeRow event, I need to modify both the value and the text in order show the changes on the grid, but when exported an error occurs becuase my field is a single value (I need to maintain it this way for filtering) and a value of "**" is not a number so it fails.

format export

Hans — Wed, 21 Mar 2012 05:00:13 GMT

Hello,

Could you tell me how you set up Format Conditions?

I suggest you to set the customize value in WebGrid by using server side event InitializeRow. Your customize value will also be exported.

Let me show you how to use InitializeRow.

In my example, I’m using nortwind.mdb database and shippers table and I want to change the Phone field value if CompanyName field value is ‘Speedy Express’.

Here’s the code:

protected void WebGrid1_InitializeRow(object sender, RowEventArgs e)
{
   if (e.Row.Cells[1].Text == "Speedy Express") // If CompanyName is Speedy Express
   {
      e.Row.Cells[2].Text = "(021)4563219"; // Set Phone to (021)4563219
   }
}

I'll attach some screenshot so you can see the result in WebGrid in "WebGrid-Result.png" file and the result after I export that WebGrid to Excel in "Excel-Export-Result.png" file.

As you can see, in "Excel-Export-Result.png" file the new value are also exported.

For further information about this event, please check in WebGrid documentation (InitializeRow Event).

Hope this helps. Thank you.

Regards,

Hans K.

format export

John.Bonin@dot.gov — Mon, 19 Mar 2012 10:34:48 GMT

Is there a way to format the export to excel? On the webgrid, i can set up Format Conditions and NullText values but when I export to Excel these condtions do not export. Is there a way in the Export event to modify what is exported?