---

17/08/2021- duocnt    1102 Views    

---

Cài đặt thư viện.

• Microsoft.Office.Interop.Excel.

Source code.

https://github.com/ntduoc/Export-to-excel.git

Code C#.

• Sử dụng thư viện

using System.Data;
using System.IO;
using Microsoft.Office.Interop.Excel;

• Phương thức

public class ExportFromDataTable
    {
        public static void ExportToExcelFromDataTable(System.Data.DataTable dataTable, string exportPath,string filename)
        {
            DataSet dataSet = new DataSet();
            dataSet.Tables.Add(dataTable);
 
            Application excelApp = new Application();
            Workbook excelWorkBook = excelApp.Workbooks.Add();                  
            Worksheet excelWorkSheet = excelWorkBook.Sheets.Add();
            excelWorkSheet.Name = filename;
            // Make excel column from datattable columns
            for (int i = 1; i < dataTable.Columns.Count + 1; i++)
            {
                excelWorkSheet.Cells[1, i] = dataTable.Columns[i - 1].ColumnName;
            }
            // Make excel row from datatable row
            for (int j = 0; j < dataTable.Rows.Count; j++)
            {
                for (int k = 0; k < dataTable.Columns.Count; k++)
                {
                    excelWorkSheet.Cells[j + 2, k + 1] = dataTable.Rows[j].ItemArray[k].ToString();
                }
            }
            //Combine folder path and file name to save
            exportPath = Path.Combine(exportPath, filename);           
            excelWorkBook.SaveAs(exportPath);
            excelWorkBook.Close();
            excelApp.Quit();
        }
    }

• Gọi phương thức.

        static void Main(string[] args)
        {
 
            DataTable dataTable = new DataTable();
            string connectionString = "Data Source=duocnt;Initial Catalog=AdventureWorks2014;
                                       User ID=sa;Password=123456789;MultipleActiveResultSets=True";
            string SQL = "select * from [Purchasing].[Vendor]";
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand(SQL, sqlConnection);
            sqlConnection.Open();
 
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
 
            sqlDataAdapter.Fill(dataTable);
            sqlConnection.Close();
            sqlDataAdapter.Dispose();
 
            ExportFromDataTable.ExportToExcelFromDataTable(dataTable, @"E:\Temp\ExportExcel",
                                                                "Export from DataTable.xlsx");
 
            Console.WriteLine("Done");
            Console.ReadLine();
        }

• Kết quả