如何从excel表中获取数据并使用vb中的crystal report生成pdf [英] how to fetch the data from excel sheet and generate the pdf using crystal report in vb

问题描述

我有一个rpt文件和包含许多工作表的excel表，我必须获取所选特定工作表的数据和基于使用vb代码选择的工作表名称的行数据并创建一个单独的所有行的pdf文件。



任何人都可以帮帮我

提前致谢

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

推荐答案

我不是没有VB.net，我为你发送C＃代码请把它转换成你想要的任何东西。请尝试以下代码

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">
                               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" />
                     
               <br />
           </asp:button></td>

       </tr>
        <tr>
           <td align="right" valign="top" colspan="" class="style1">
                   <asp:label id="lblsheet" runat="server" text="Sheet Name : " xmlns:asp="#unknown"></asp:label>
           </td>
           <td valign="top" colspan="" class="style6">
                   <asp:dropdownlist id="ddlSheet" runat="server" autopostback="True" visible="False" xmlns:asp="#unknown">
                    SkinID="DropDownListSkin"
                       OnSelectedIndexChanged="ddlSheet_SelectedIndexChanged">
                   </asp:dropdownlist>
                   <br />
                <br />
           </td>
        </tr>
        <tr>
          <td align="center" colspan="2">
               <table>
                   <tr>
                       <td align="right">
                           <asp:linkbutton id="btnShow" runat="server" onclick="btnShow_Click" visible="false" xmlns:asp="#unknown">
                           Show Data                  
                           </asp:linkbutton>
                       </td>


                                           </tr>
               </table>
           </td>
        </tr>
    </table>

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();
        }
    }
}

到此处数据将显示在GridView中，现在您将gridview数据导出为pdf

请参阅此链接以将gridview导出为pdf

http://forums.asp.net/p/1425195/3174526.aspx [ ^ ]





我认为这对你有帮助......

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

这篇关于如何从excel表中获取数据并使用vb中的crystal report生成pdf的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！