Wednesday 6 June 2012

OpenFileDialog

   private static void raiseOpenFileDialog()
        {
            try
            {
                Stream myStream = null;
                OpenFileDialog openFileDialog1 = new OpenFileDialog();

                openFileDialog1.InitialDirectory = "c:\\";

                openFileDialog1.Filter = "Picture (*.jpg,*.bmp,*.gif,*.png)|*.jpg;*.bmp;*.gif;*.png";
                openFileDialog1.FilterIndex = 4;
                openFileDialog1.Title = "Choose a picture";
                openFileDialog1.RestoreDirectory = true;

                if (openFileDialog1.ShowDialog() == DialogResult.OK)
                {
                    try
                    {
                        if ((myStream = openFileDialog1.OpenFile()) != null)
                        {
                            using (myStream)
                            {
                                // Insert code to read the stream here.
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Error: Could not read file from disk. Original error: " + ex.Message);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

Thursday 31 May 2012

Upload Excel File, then Display Record(s) in Gridview

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ExcelToGrid.aspx.cs" Inherits="ExcelToGridview.ExcelToGrid" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <div>
            <asp:Label ID="label1" runat="server" Text="File"></asp:Label>
            &nbsp;
            <asp:FileUpload ID="xlsUpload" runat="server" Font-Size="Small" />
        </div>
        <div>
            <table width="100%">
                <tr align="left">
                    <td>
                        <asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />
                    </td>
                </tr>
            </table>
        </div>
        <div>
            <table width="100%">
                <tr align="left">
                    <td>
                        <asp:Label ID="lblMessage" runat="server" Text="" ForeColor="Red"></asp:Label>
                    </td>
                </tr>
            </table>
        </div>
        <div style="margin-top: 20px;">
            <table>
                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4"
                    ForeColor="#333333">
                    <PagerSettings FirstPageText="" LastPageText="" NextPageText="" />
                    <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
                    <Columns>
                        <asp:BoundField DataField="CardNo" HeaderText="Card No" />
                        <asp:BoundField DataField="MemberName" HeaderText="Member Name" />
                    </Columns>
                    <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
                    <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
                    <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
                    <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
                    <AlternatingRowStyle BackColor="White" />
                </asp:GridView>
            </table>
        </div>
    </div>
    </form>
</body>
</html>
===============================================
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.IO;
using System.Data.OleDb;

///
/// Author: Algem G. Mojedo
///


namespace ExcelToGridview
{
    public partial class ExcelToGrid : System.Web.UI.Page
    {
        DataTable dt = null;

        public System.Data.DataTable xlsInsert(string pth)
        {
            string strcon = string.Empty;
            if (Path.GetExtension(pth).ToLower().Equals(".xls") || Path.GetExtension(pth).ToLower().Equals(".xlsx"))
            {
                strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
                                + pth +
                                ";Extended Properties=\"Excel 8.0;HDR=YES;\"";
            }
            else
            {
                strcon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
                              + pth +
                              ";Extended Properties=\"Excel 12.0;HDR=YES;\"";
            }
            string strselect = "Select * from [Sheet1$]";
            DataTable exDT = new DataTable();
            using (OleDbConnection excelCon = new OleDbConnection(strcon))
            {
                try
                {
                    excelCon.Open();
                    using (OleDbDataAdapter exDA = new OleDbDataAdapter(strselect, excelCon))
                    {
                        exDA.Fill(exDT);
                    }
                }
                catch (OleDbException oledb)
                {
                    throw new Exception(oledb.Message.ToString());
                }
                finally
                {
                    excelCon.Close();
                }
                for (int i = 0; i < exDT.Rows.Count; i++)
                {
                    // Check if first column is empty
                    // If empty then delete such record
                    if (exDT.Rows[i]["CardNo"].ToString() == string.Empty)
                    {
                        exDT.Rows[i].Delete();
                    }
                }
                exDT.AcceptChanges();  // refresh rows changes
                if (exDT.Rows.Count == 0)
                {
                    throw new Exception("File uploaded has no record found.");
                }
                return exDT;
            }
        }

        protected void btnUpload_Click(object sender, EventArgs e)
        {
            if (xlsUpload.HasFile)
            {
                bool uplod = true;
                string fleUpload = Path.GetExtension(xlsUpload.FileName.ToString());
                if (fleUpload.Trim().ToLower() == ".xls" | fleUpload.Trim().ToLower() == ".xlsx")
                {
                    // Save excel file into Server sub dir 
                    // to catch excel file downloading permission   
                    xlsUpload.SaveAs(Server.MapPath("~/XlsUploadFile/" + xlsUpload.FileName.ToString()));
                    string uploadedFile = (Server.MapPath("~/XlsUploadFile/" + xlsUpload.FileName.ToString()));
                    try
                    {
                        dt = xlsInsert(uploadedFile);
                        GridView1.DataSource = dt;
                        GridView1.DataBind();
                    }
                    catch (Exception)
                    {
                        uplod = false;
                        this.lblMessage.Text = "System uploading Error";
                    }
                    File.Delete(uploadedFile); // Delete upload exel  file in sub dir  'lsUploadFile' no need to keep...
                }
                if (uplod)
                {
                    string mess1 = "File has successfully uploaded";
                    this.lblMessage.Text = mess1;
                }
            }
            else
            {
                this.lblMessage.Text = "Please select file to upload.";
            }

        }
        protected void Page_Load(object sender, EventArgs e)
        {

        }
    }
}
============================================




For Reference