C# 轉EXCEL

  • 100
  • 0
  • C#
  • 2020-02-20

 using ClosedXML.Excel;

 

 

# Simple Sample

https://github.com/ClosedXML/ClosedXML/wiki/Adding-DataTable-as-Worksheet

 

https://dotblogs.com.tw/toreoo123456_note/2018/01/17/111405

EXCEL WEB

https://qawithexperts.com/article/asp-net/export-datatable-to-excel-in-c-aspnet-web-form-example/206

EXCEL AP

https://einboch.pixnet.net/blog/post/274497938-%E4%BD%BF%E7%94%A8npoi%E7%94%A2%E7%94%9Fexcel%E6%AA%94%E6%A1%88

 

ajax 沒辦法下載 ( 應該是說 jquery .ajax 這方式不能直接下載)

要form submit 或者

 <asp:Button ID="btn_excel" class="btn btn-outline-info btn-lg ml-2" runat="server" Text="下載EXCEL" OnClick="btn_excel_Click" />

去觸發

    protected void btn_excel_Click(object sender, EventArgs e)
    {
        MyAPI w = new MyAPI();
        DataTable dt = w.GetDT();
        ToExcel(dt);
    }

    public void ToExcel(DataTable dt) {
        try
        {
            using (XLWorkbook wb = new XLWorkbook())
            {

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

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

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

                //副標題
                ws.Cell(2, 1).Value = "我是副標題"

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

 

https://dotblogs.com.tw/toreoo123456_note/2018/01/17/111405

using System;
using System.Data;

class Program
{
    static void Main()
    {
        // Get the DataTable.
        DataTable table = GetTable();
        // ... Use the DataTable here with SQL.
    }

    /// <summary>
    /// This example method generates a DataTable.
    /// </summary>
    static DataTable GetTable()
    {
        // Here we create a DataTable with four columns.
        DataTable table = new DataTable();
        table.Columns.Add("Dosage", typeof(int));
        table.Columns.Add("Drug", typeof(string));
        table.Columns.Add("Patient", typeof(string));
        table.Columns.Add("Date", typeof(DateTime));

        // Here we add five DataRows.
        table.Rows.Add(25, "Indocin", "David", DateTime.Now);
        table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now);
        table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now);
        table.Rows.Add(21, "Combivent", "Janet", DateTime.Now);
        table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now);
        return table;
    }
}