export raw data in MS excel from sql database
input parameter
1. date period (from date ~ todate and today,yesterday,month )
2. search ID
- public partial class WebForm1 : System.Web.UI.Page
- {
-
- protected void Page_Load(object sender, EventArgs e)
- {
-
- }
-
- protected void ExportExcel(object sender, EventArgs e)
- {
- string storeNo = StoreNo.Text;
-
- using (SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MakaseteNet_V1ConnectionString"].ConnectionString))
- {
- using (SqlCommand cmd = new SqlCommand("SELECT * FROM POS_TtlTran where StoreNo = " + storeNo))
- {
-
- using (SqlDataAdapter sda = new SqlDataAdapter())
- {
- cmd.Connection = con;
- sda.SelectCommand = cmd;
- using (DataTable dt = new DataTable())
- {
- sda.Fill(dt);
- using (XLWorkbook wb = new XLWorkbook())
- {
- wb.Worksheets.Add(dt, "StoreNo");
-
- Response.Clear();
- Response.Buffer = true;
- Response.Charset = "";
- Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
- Response.AddHeader("content-disposition", "attachment;filename=POS_TtlTran.xlsx");
- using (MemoryStream MyMemoryStream = new MemoryStream())
- {
- wb.SaveAs(MyMemoryStream);
- MyMemoryStream.WriteTo(Response.OutputStream);
- Response.Flush();
- Response.End();
- }
- }
- }
- }
- }
- }
- }
-
- }
- }
- <body>
- <form id="form1" runat="server">
-
- <div>
- <td>
- <tr>
- StoreNO <asp:TextBox ID="StoreNo" runat="server" ></asp:TextBox>
- </tr>
- </td>
- <br>
- <br>
- <td class="auto-style11" colspan="2">
- <asp:Label ID="Date" runat="server" Text="Date" meta:resourcekey="lbFromMonthResource1"></asp:Label>
- <asp:TextBox ID="FromMonth" runat="server" Width="90px" class="date-picker" meta:resourcekey="tbxFromMonthResource"></asp:TextBox>
- </td>
- <td id="td1">~<asp:TextBox ID="ToMonth" runat="server" Width="90px" class="date-picker" meta:resourcekey="tbxToMonthResource"></asp:TextBox>
- </td>
- </br><br>
- <input id="btnToday" type="button" value="Today" class="btn btn-default" />
- <input id="btnYesterday" type="button" value="Yesterday" class="btn btn-default" />
- <input id="btnThisMonth" type="button" value="ThisMonth" class="btn btn-default" />
- </br>
- <br>
- <asp:Button Text="Export to excel" OnClick="ExportExcel" runat="server" />
- </br>
- </div>
- </form>
- </body>
- </html>
Can anyone help how to do it !