Click here to Skip to main content
15,880,469 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have a mysql database, and I can generate an excel file from it, but I also want to generate a link to that excel file, so that I can connect to it from powerbi. That way I wont give access to the entire database, and just give access to the content in the excel file that will be generated.

What I have tried:

String connString = System.Configuration.ConfigurationManager.ConnectionStrings["web"].ToString();
            using (conn = new MySqlConnection(connString))
            {
                using (MySqlCommand cmd = new MySqlCommand("SELECT * FROM avance_presupuestario where proyecto ='" + proy2.Text + "'"))
                {
                    using (MySqlDataAdapter sda = new MySqlDataAdapter())
                    {
                        cmd.Connection = conn;
                        sda.SelectCommand = cmd;
                        using (DataTable dt = new DataTable())
                        {
                            sda.Fill(dt);
                            using (XLWorkbook wb = new XLWorkbook())
                            {
                                wb.Worksheets.Add(dt, proy2.Text);

                                Response.Clear();
                                Response.Buffer = true;
                                Response.Charset = "";
                                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                                Response.AddHeader("content-disposition", "attachment;filename=Avance Presupuestario.xlsx");
                                using (MemoryStream MyMemoryStream = new MemoryStream())
                                {
                                    wb.SaveAs(MyMemoryStream);
                                    MyMemoryStream.WriteTo(Response.OutputStream);
                                    Response.Flush();
                                    Response.End();
                                }
                            }
                        }
                    }
                }

            }
Posted
Comments
Member 10850253 19-Dec-17 13:16pm    
I found this: https://www.youtube.com/watch?v=5m6dCbYO0Mk, but the thing is that I am using MYSQL instead of SQL and the SqlParameter doesn't work.
Can you please help?
Thanks.
CHill60 19-Dec-17 14:34pm    
SqlParameter won't work but I bet MysqlParameter will :-)
Member 10850253 19-Dec-17 14:52pm    
I can't find that statement.
I can only find Mysql.Data...
Member 10850253 19-Dec-17 16:26pm    
I just removed some characters from my statement, such as ', delete and drop.
That's the best I could do, because I tried the parameters and it gave me an error.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900