Click here to Skip to main content
12,255,247 members (61,898 online)
Rate this:
 
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 2:53am
Edited 17-Jan-13 2:55am
v2
Comments
Sandeep Mewara 17-Jan-13 12:54pm
   
And, what have you tried so far? Where are you stuck, be specific.
sagarjainhr 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 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>

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 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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160426.1 | Last Updated 28 Jan 2013
Copyright © CodeProject, 1999-2016
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