Tuesday, November 6, 2012

New blog on Ext.Net

I have started writing on Ext.Net in a new blog(http://practicalextnet.blogspot.com/) that will be dedicated to Ext.Net technologies. Now a day I am working on Ext.Net and developing all my web application with highly used Ext.Net. I have faced many problems while started working. So I would like to share my knowledge and finding with ext.net developers on the community so that they get help in their work. Hope this will be helpful.

Thanks

Saturday, November 3, 2012

Display Gridpanel cell tooltip in Ext.Net



You often want to display tooltip using cell data when data length overflows column width in your gridpanel. You will get an example to show tooltip with gridpanel cell data here: http://examples1.ext.net/#/Miscellaneous/ToolTips/GridPanel_Cell_Tooltip/
But this is not working properly when it is included in some project especially if you have master page based design. I have tried this in my project and it worked properly with little bit changes. Here I want to share the codes with you guys:
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="Server">
    <link href="../Style/examples.css" rel="stylesheet" type="text/css" />
    <ext:XScript ID="XScript1" runat="server">
        <script type="text/javascript">
            var showTip = function () {
                var rowIndex = #{gpList}.view.findRowIndex(this.triggerElement),
                cellIndex = #{gpList}.view.findCellIndex(this.triggerElement),
                record = #{gpListStore}.getAt(rowIndex),
                fieldName = #{gpList}.getColumnModel().getDataIndex(cellIndex),
               
                data = record.get(fieldName);
                if (data ==null){
                this.body.dom.innerHTML = "";
                }
                else{
                this.body.dom.innerHTML = data;
                };
            };
        </script>
    </ext:XScript>
</asp:Content>

<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server">
    <ext:ResourceManager ID="rcm" runat="server">
    </ext:ResourceManager>
    <ext:Hidden ID="hfPK" runat="server">
    </ext:Hidden>
    <ext:TabPanel ID="tPnlMain" runat="server" ActiveTabIndex="0" AnchorHorizontal="100%"
        Height="500" Plain="true" Visible="true" Width="1024">
        <Items>
<ext:Panel ID="Tab2" runat="server" Title="Employee Search" Padding="1" AutoScroll="true">
                <Items>
                    <ext:RowLayout runat="server">
                        <Rows>
                            <ext:LayoutRow RowHeight=".25">
                                <ext:Panel ID="Panel1" runat="server" Header="false" Padding="1" AutoScroll="true">
                                    <Items>
                                    </Items>
                                </ext:Panel>
                            </ext:LayoutRow>
                            <ext:LayoutRow RowHeight=".75">
                                <ext:Panel ID="Panel3" runat="server" Title="Employee List" Padding="1" AutoScroll="true">
                                    <Items>
                                        <ext:GridPanel ID="gpList" runat="server" StripeRows="true" AutoExpandColumn="EmployeeName"
                                            Collapsible="true" AnchorHorizontal="100%" Height="322" TrackMouseOver="true">
                                            <Store>
                                                <ext:Store ID="gpListStore" runat="server">
                                                    <Reader>
                                                        <ext:JsonReader IDProperty="EmployeeId">
                                                            <Fields>
                                                                <ext:RecordField Name="EmployeeId" />
                                                                <ext:RecordField Name="EmployeeName" />
                                                                <ext:RecordField Name="EnrollId" />
                                                                <ext:RecordField Name="FirstName" />
                                                                <ext:RecordField Name="MiddleName" />
                                                                <ext:RecordField Name="LastName" />
                                                                <ext:RecordField Name="AddressLine1" />
                                                                <ext:RecordField Name="DepartmentId" />
                                                                <ext:RecordField Name="DesignationId" />
                                                                <ext:RecordField Name="PolicyId" />
                                                                <ext:RecordField Name="CardNumber" />
                                                                <ext:RecordField Name="DesignationName" />
                                                                <ext:RecordField Name="DeptName" />
                                                                <ext:RecordField Name="SbuName" />
                                                            </Fields>
                                                        </ext:JsonReader>
                                                    </Reader>
                                                </ext:Store>
                                            </Store>
                                            <ColumnModel ID="ColumnModel1" runat="server">
                                                <Columns>
                                                    <ext:RowNumbererColumn />
                                                    <ext:CommandColumn Header="Action" Width="65">
                                                        <Commands>
                                                            <ext:GridCommand Icon="ApplicationEdit" CommandName="Edit">
                                                                <ToolTip Text="Edit" />
                                                            </ext:GridCommand>
                                                        </Commands>
                                                    </ext:CommandColumn>
                                                    <ext:Column ColumnID="cEnrollId" Header="Enroll Id" DataIndex="EnrollId" Width="50" />
                                                    <ext:Column ColumnID="cStaffCode" Header="Employee Code" DataIndex="StaffCode" Width="70" />
                                                    <ext:Column ColumnID="cEmployeeName" Header="EmployeeName" DataIndex="EmployeeName"
                                                        Width="130" />
                                                    <ext:Column ColumnID="cDesignationName" Header="Designation" DataIndex="DesignationName"
                                                        Width="150" />
                                                    <ext:Column ColumnID="cDepartmentName" Header="Department" DataIndex="DeptName" Width="100" />
                                                    <ext:Column ColumnID="cSbuId" Header="Branch" DataIndex="SbuName" />
                                                    <ext:Column ColumnID="cAddressLine1" Header="Address" DataIndex="AddressLine1" Width="180" />
                                                </Columns>
                                            </ColumnModel>
                                            <SelectionModel>
                                                <ext:RowSelectionModel ID="RowSelectionModel1" runat="server" SingleSelect="true" />
                                            </SelectionModel>
                                            <BottomBar>
                                                <ext:PagingToolbar ID="gpListPager" runat="server" PageSize="20" />
                                            </BottomBar>
                                            <Listeners>
                                                <Command Handler="Ext.net.DirectMethods.ExecuteActionCommand(command, record.data.EmployeeId, record.data.EnrollId);" />
                                            </Listeners>
                                            <ToolTips>
                                                <ext:ToolTip ID="RowTip" runat="server" Target="#{gpList}.getView().mainBody" Delegate=".x-grid3-cell" ShowDelay="0"
                                                    TrackMouse="true">
                                                    <Listeners>
                                                        <Show Fn="showTip" />
                                                    </Listeners>
                                                </ext:ToolTip>
                                            </ToolTips>
                                        </ext:GridPanel>
                                    </Items>
                                </ext:Panel>
                            </ext:LayoutRow>
                        </Rows>
                    </ext:RowLayout>
                </Items>
            </ext:Panel>
        </Items>
    </ext:TabPanel>
</asp:Content>

Hope this will help my friends.







Friday, November 2, 2012

How to export data to excel?

Recently, I have worked on export data to excel and import data from excel. I have face many problems while working on this. So I have searched for different ways for doing export and import from excel. I would like to share with you guys different export methods so that you can easily try it.

Way 1: using Open XML when working with Office 2007 or later version


protected void ExportToExcel(DataTable dt, List<string> columnNames, string fileName)
    {
        MemoryStream stream = DocumentFormat.OpenXml.Extensions.SpreadsheetReader.Create();
        DocumentFormat.OpenXml.Packaging.SpreadsheetDocument doc = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(stream, true);
        DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart = DocumentFormat.OpenXml.Extensions.SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
        DocumentFormat.OpenXml.Extensions.WorksheetWriter writer = new DocumentFormat.OpenXml.Extensions.WorksheetWriter(doc, worksheetPart);

        //writer.PasteText("B2", "Hello World");
        DataTable dtc = new DataTable();
        for (int i = 0; i < columnNames.Count; i++)
        {
            dtc.Columns.Add(new DataColumn(columnNames[i]));
        }
        DataRow dr = dt.NewRow();
        for (int i = 0; i < columnNames.Count; i++)
        {
            dr[i] = columnNames[i];
        }
        dt.Rows.InsertAt(dr, 0);
        writer.InsertDataTable(dt, "A1", columnNames);
        //writer.PasteDataTable(dtc, "A1");
        //Save to the memory stream
        DocumentFormat.OpenXml.Extensions.SpreadsheetWriter.Save(doc);

        //Write to response stream
        this.Response.Clear();
        this.Response.AddHeader("content-disposition", String.Format("attachment;filename={0}", fileName));
        this.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

        stream.WriteTo(this.Response.OutputStream);
        this.Response.End();
    }


Way 2: working with office automation


protected void Exporttoexcel2(DataTable dt, string fileName)
    {
        off.Application excel = new off.Application();
        off.Workbook workbook = excel.Application.Workbooks.Add(true);
        int iCol = 0;
        foreach (DataColumn c in dt.Columns)
        {
            iCol++;
            excel.Cells[1, iCol] = c.ColumnName;
        }

        int iRow = 0;
        foreach (DataRow r in dt.Rows)
        {
            iRow++;
            iCol = 0;
            foreach (DataColumn c in dt.Columns)
            {
                iCol++;
                excel.Cells[iRow + 1, iCol] = r[c.ColumnName];
            }
        }

        string path = string.Empty;
        path = Server.MapPath("~/TempFiles/");
        path = path + fileName;

        object missing = System.Reflection.Missing.Value;
        workbook.SaveAs(path, off.XlFileFormat.xlXMLSpreadsheet, missing, missing, false, false, off.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
        excel.Visible = true;
        off.Worksheet worksheet = (off.Worksheet)excel.ActiveSheet;
        ((off._Worksheet)worksheet).Activate();
        ((off._Application)excel).Quit();

        this.Response.ContentType = "application/vnd.ms-excel";
        this.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + "");
        this.Response.TransmitFile(path);
        this.Response.End();
    }

Way 3: working with ExcelPackage library


protected void Exporttoexcel3(DataTable dt, string fileName)
    {
        FileInfo newFile = new FileInfo(fileName);
        using (ExcelPackage xlPackage = new ExcelPackage(newFile))
        {
            xlPackage.DebugMode = true;
            ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add(fileName);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                worksheet.Cell(1, i + 1).Value = dt.Columns[i].ColumnName;
            }

            for (int r = 0; r < dt.Rows.Count; r++)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {

                    worksheet.Cell(r + 2, i + 1).Value = Convert.ToString(dt.Rows[r][i]);
                }
            }
            xlPackage.Save();
        }
    }