C# DATATABLE 轉 EXCEL

  • 416
  • 0

ClosedXML.Excel SAMPLE

範例


    public void ToExcel(DataTable dt)
    {
        int ct = dt.Rows.Count;
        using (XLWorkbook wb = new XLWorkbook())
        {
            wb.Worksheets.Add(dt, "分頁");
            Response.Clear();
            Response.Buffer = true;
            Response.Charset = "";
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment;filename=檔名.xlsx");
            using (MemoryStream MyMemoryStream = new MemoryStream())
            {
                wb.SaveAs(MyMemoryStream);
                MyMemoryStream.WriteTo(Response.OutputStream);
                Response.Flush();
                Response.End();
            }
        }
    }

範例

   public void ToExcel(DataTable dt,string gameCode, string gameName, string date ,string tabName,string endDate,string gEd) {
        try
        {
            using (XLWorkbook wb = new XLWorkbook())
            {

                var ws = wb.Worksheets.Add(dt, tabName);
                //新增2行(標題與副標題)
                ws.Row(1).InsertRowsAbove(1);
                ws.Row(1).InsertRowsAbove(1);

                //合併表格
                ws.Range("A1:D1").Row(1).Merge();

   
                //標題
                ws.Cell(1, 1).Value = tabName;
                ws.Cell(1, 1).Style.Font.Bold = true;

                //副標題
                ws.Cell(2, 1).Value = gameName;//競賽名稱
                ws.Cell(2, 2).Value = "資料時間:"+ date;

              
                Response.Clear();
                Response.Buffer = true;
                Response.Charset = "";
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", "attachment;filename="+ tabName + ".xlsx");
                using (MemoryStream MyMemoryStream = new MemoryStream())
                {
                    wb.SaveAs(MyMemoryStream);
                    MyMemoryStream.WriteTo(Response.OutputStream);
                    Response.Flush();
                    Response.End();
                }
            }
        }
        catch (Exception ex)
        {
            logger.Error(ex.Message);
        }
    }