Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
I am having a rpt file and the excel sheet which contains many worksheet in it, I have to fetch the data of particular worksheet selected and the row data based on the name of that worksheet selected using the vb code and create a separate pdf document for all the rows.

can any one please help me out
Thanks in advance
Posted 17-Jan-13 3:53am
Edited 17-Jan-13 3:55am
v2
Comments
Sandeep Mewara at 17-Jan-13 12:54pm
   
And, what have you tried so far? Where are you stuck, be specific.
sagarjainhr at 18-Jan-13 0:36am
   
I am new to vb so i am not getting how to query the excel sheet and store it in dataset, so that I can access it and create pdf
sagarjainhr at 18-Jan-13 4:01am
   
Dim connectionString As String = [String].Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", filename)
Dim query As String = "select * from [" & WorkSheet & "$A" & i & ":Y" & i & "]"
Dim dataAdapter As New OleDbDataAdapter(query, connectionString)
Dim table As New DataTable("ExcelData")
dataAdapter.Fill(table)
 
I have done until this but i am not able to get to fetch it to the crystal report and also i am getting just the row but not the header of it also i am storing it in the data table. What should i do next

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Hi,
 
I don't no VB.net, i send C# code for you please convert this into Whatever you want. Try the following code
 

 <table class="TextVerdanaNavy8" width="75%" align="center">                  
         
         <tr valign="top">
            <td align="center" colspan="3">
                <asp:label id="Label1" runat="server" text="Drawing Schedule" cssclass="PageHead" xmlns:asp="#unknown"></asp:label>
            </td>
        </tr>
         <tr>
 
            <td align="right">
               &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Excel File :
            </td>
            
            <td align="left">            
                <asp:fileupload id="FileUpload1" width="50%" runat="server" xmlns:asp="#unknown" />  
                <asp:label id="lblmsg" runat="server" text="" xmlns:asp="#unknown"></asp:label> 
                  
                <asp:button id="btnGetExcelSheetList" runat="server" onclick="btnGetExcelSheetList_Click" xmlns:asp="#unknown">
                        Text="Get Excel Sheet List" />
 
        private void Get_Sheets()
        {
            OleDbConnection oconn = null;
            DataTable dt = null;
            try
            {
                string FilePath = string.Empty;
                string FileName = string.Empty;
                if (FileUpload1.HasFile)
                {
                    FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
                    string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
                    string FolderPath = ConfigurationManager.AppSettings["PRPOFolder"];
                    FilePath = Server.MapPath(FolderPath + FileName);
                    ViewState["FilePath"] = FilePath;
                    ViewState["FileName"] = FileName;
                    FileUpload1.SaveAs(FilePath);
                }
                //Microsoft Office 12.0 Access Database Engine OLE DB Provider
                oconn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=Excel 8.0");
 
                oconn.Open();
                dt = null;
                dt = oconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (dt == null)
                {
 
                }
                String[] sheet = new String[dt.Rows.Count];
                int i = 0;
                foreach (DataRow dr in dt.Rows)
                {
                    sheet[i] = dr["TABLE_NAME"].ToString();
                    i++;
                }
                string[] a = sheet;
                int j = 0;
                if (a != null && a.Length > 0)
                {
                    ddlSheet.Visible = true;
                    lblsheet.Visible = true;
                    for (j = 0; j < a.Length; j++)
                    {
                        ddlSheet.Items.Add(a[j]);
                    }
                    ddlSheet.Items.Insert(0, "<--- Select Excel Sheet --->");
                }
                else
                {
                    ddlSheet.Visible = false;
                    lblsheet.Visible = false;
                }
            }
            catch (Exception ex)
            {
            }
            finally
            {
                if (oconn != null)
                {
                    oconn.Close();
                    oconn.Dispose();
                }
                if (dt != null)
                {
                    dt.Dispose();
                }
            }
        }
 
        protected void btnGetExcelSheetList_Click(object sender, EventArgs e)
        {
            ddlSheet.Items.Clear();
            Get_Sheets();
        }
 
        protected void btnShow_Click(object sender, EventArgs e)
        {
            gdDrawing.Visible = false;
            Display();
        }
 
        private void Display()
        {
            OleDbConnection oconn = null;
            DataTable dt1 = new DataTable();
            try
            {
                string FileName = ViewState["FileName"] as string;
                string FilePath = ViewState["FilePath"] as string;
 
                oconn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=Excel 8.0");
 
                OleDbCommand ocmd = new OleDbCommand("select * from [" + ddlSheet.SelectedItem.ToString() + "]", oconn);
 
                oconn.Open();
 

                OleDbDataReader odr = ocmd.ExecuteReader();
 
                DesignService.Design ODesign = new DesignService.Design();
 
                string Title = string.Empty;
                string Size = string.Empty;        
                
                dt1.TableName = FileName;
                dt1.Columns.Add("Title");
                dt1.Columns.Add("Size");
               
                while (odr.Read())
                {
 
                    Title = odr["Title"].ToString();//odr[0].ToString();//
                    Size = odr["Size"].ToString(); //odr[1].ToString();//                  
                   
                    //Assign the values to DataRow
                    DataRow dr = dt1.NewRow();
                    dr["Title"] = Title;
                    dr["Size"] = Size;
                   
                    //Add Records to DataTable
                    dt1.Rows.Add(dr);
                }
 
                if (dt1.Rows.Count > 0)
                {
                    btninsert.Visible = true;
                    Session["Table"] = dt1;
 
                    gvExcelData.Visible = true;
                    gvExcelData.DataSource = dt1;
                    gvExcelData.DataBind();
                }
                else
                {
                    btninsert.Visible = false;
                }
            }
            catch (DataException ex)
            {
            }
            finally
            {
                if (oconn != null)
                {
                    oconn.Close();
                    oconn.Dispose();
                }
                if (dt1 != null)
                {
                    dt1.Dispose();
                }
            }
        }
 

 
upto here data will be displayed in GridView , now you export gridview data to pdf
refer this link for exporting gridview to pdf
http://forums.asp.net/p/1425195/3174526.aspx[^]
 

I think this might be helpfull to you...
  Permalink  
v2
Comments
sagarjainhr at 28-Jan-13 7:01am
   
Thanks alot

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web01 | 2.8.1411022.1 | Last Updated 28 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100