string strConnString = @"Server=localhost;UID=sa;PASSWORD=;database=mydatabase"; var objConn = new SqlConnection(strConnString); var dtAdapter = new SqlDataAdapter(); var dt = new DataTable(); objConn.Open(); string strSQL = "SELECT * FROM myTable"; dtAdapter = new SqlDataAdapter(strSQL, objConn); dtAdapter.Fill(dt); FileInfo template = new FileInfo(Server.MapPath(@"Xls/Template.xlsx")); using (var package = new ExcelPackage(template)) { var workbook = package.Workbook; //*** Sheet 1 var worksheet = workbook.Worksheets.First(); int startRows = 3; for (int i = 0; i < dt.Rows.Count; i++) { worksheet.Cells["A" + (i + startRows)].Value = dt.Rows[i]["Column1"].ToString(); worksheet.Cells["B" + (i + startRows)].Value = dt.Rows[i]["Column2"].ToString(); worksheet.Cells["C" + (i + startRows)].Value = dt.Rows[i]["Column3"].ToString(); worksheet.Cells["D" + (i + startRows)].Value = dt.Rows[i]["Column4"].ToString(); worksheet.Cells["E" + (i + startRows)].Value = dt.Rows[i]["Column5"].ToString(); } package.SaveAs(new FileInfo(Server.MapPath(@"Xls/myExcel.xlsx"))); } dtAdapter = null; objConn.Close();
ช่วยกันสนับสนุนรักษาเว็บไซต์ความรู้แห่งนี้ไว้ด้วยการสนับสนุน Source Code 2.0 ของทีมงานไทยครีเอท