Announcement

Friday, 21 February 2014

How to compare only month part of DateTime field using LINQ

Getting all values of a particular column by comparing Month part of DateTime field in SQL using Linq.

Fetch the month of date time field using the Value property in Linq. Then get the Month of it as: f.ForDate.Value.Month

e.g., the following query will get all start time as an array of String where the Month of ForDate field in Table FPCTimeMaster matches the Month of DateTime field timeMasterDate.

string[] startTimes = context.FPCTimeMasters.Where(f =>f.ForDate.Value.Month == timeMasterDate.Month && f.ChannelId == channelID ).Select(s => s.StartTime).ToArray();

Wednesday, 12 February 2014

Export DataTable to Excel without dll

You can simply export DataTable to Excel file without using dll provided that Microsoft Office is installed.

Here is the code:-

Function ExportToExcel() expects a parameter of type DataTable which contains required information
that is to be converted into excel.


private void ExportToExcel(DataTable table)
        {
            HttpContext.Response.Clear();
            HttpContext.Response.ClearContent();
            HttpContext.Response.ClearHeaders();
            HttpContext.Response.Buffer = true;
            HttpContext.Response.ContentType = "application/ms-excel";
            HttpContext.Response.Write(@"<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">");
            //Set the name of report as per query fired by the user.
            HttpContext.Response.AddHeader("Content-Disposition", "attachment;filename=Reports.xls");

            HttpContext.Response.Charset = "utf-8";
            HttpContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250");
            //sets font
            HttpContext.Response.Write("<font style='font-size:10.0pt; font-family:Calibri;'>");
            HttpContext.Response.Write("<BR><BR><BR>");
            //sets the table border, cell spacing, border color, font of the text, background, foreground, font height
            HttpContext.Response.Write("<Table border='1' bgColor='#ffffff' " +
              "borderColor='#000000' cellSpacing='0' cellPadding='0' " +
              "style='font-size:10.0pt; font-family:Calibri; background:white;'> <TR>");
            //am getting my grid's column headers
            //int columnscount = GridView_Result.Columns.Count;
            int columnscount = table.Columns.Count;
            for (int j = 0; j < columnscount; j++)
            {   //write in new column
                HttpContext.Response.Write("<Td>");
                //Get column headers  and make it as bold in excel columns
                HttpContext.Response.Write("<B>");
                //HttpContext.Response.Write(GridView_Result.Columns[j].HeaderText.ToString());
                HttpContext.Response.Write(table.Columns[j].ToString());
                HttpContext.Response.Write("</B>");
                HttpContext.Response.Write("</Td>");
            }
            HttpContext.Response.Write("</TR>");
            foreach (DataRow row in table.Rows)
            {   //write in new row
                HttpContext.Response.Write("<TR>");
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    HttpContext.Response.Write("<Td>");
                    HttpContext.Response.Write(row[i].ToString());
                    HttpContext.Response.Write("</Td>");
                }
                HttpContext.Response.Write("</TR>");
            }
            HttpContext.Response.Write("</Table>");
            HttpContext.Response.Write("</font>");
            HttpContext.Response.Flush();
            HttpContext.Response.End();
        }

Sunday, 2 February 2014

Export table data to excel using jquery

Export table data to excel using jquery

Export DataTable to Excel ©techiners
Export DataTable to Excel


To export data table to excel using jquery just add following code:




$('#btnExcel').click(function (e) {

window.open('data:application/vnd.ms-excel,'+$('#dvData').html());
e.preventDefault();


Explanation: #btnExcel is the id of the button on the click of which you need to export the data to the excel file. #dvData is the id of the DIV element in which data is present or rendered on the view that needs to be exported to excel file.