C# DataTable 读取/汇出Excel (使用EPPlus)

1.使用EPPlus读取.xlsx档,需安装套件 using OfficeOpenXml;

public static DataTable ExcelImport(){    string path="你的excel路径"    //开启已存在的Excel档案    FileInfo existingFile = new FileInfo(path);    //new一个包存取.xlsx档    ExcelPackage package = new ExcelPackage(existingFile);    //因为EPPlus 升版和授权,需要加这行    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;    //选取第1个sheet    ExcelWorksheet worksheet = package.Workbook.Worksheets[0];    //取得所选sheet的列数和行数    int rows = worksheet.Dimension.End.Row;    int cols = worksheet.Dimension.End.Column;    //读取资料放入DataTable    DataTable dt = new DataTable(worksheet.Name);    DataRow dr = null;    //ExcelWorksheet在.net framwork 的index从1开始;在.net从0开始    for (int i = 1; i <= rows; i++)    {        if (i > 1)            dr = dt.Rows.Add();        for (int j = 1; j <= cols; j++)        {            //把第一列设为DataTable标头            if (i == 1)                dt.Columns.Add(worksheet.Cells[i, j].Value.ToString());            //剩下的写入DataTable,不要加ToString(),因为若储存格空白会导致NullReference            else                dr[j - 1] = worksheet.Cells[i, j].Value;        }    }    return dt;}

2.使用EPPlus汇出Excel(以WinForm DataGridView为例)

//建立datatableDataTable dt = new DataTable();//加入 Columnsdt.Columns.Add(new DataColumn(dataGridView.Columns[0].HeaderText, typeof(int)));dt.Columns.Add(new DataColumn(dataGridView.Columns[1].HeaderText, typeof(string)));dt.Columns.Add(new DataColumn(dataGridView.Columns[2].HeaderText, typeof(string)));dt.Columns.Add(new DataColumn(dataGridView.Columns[3].HeaderText, typeof(string)));//加入 Rows,Count-1是dataGridView有预设空白列for (int i = 0; i < dataGridView.Rows.Count - 1; i++){    DataRow dr = dt.NewRow();    for (int j = 0; j < dataGridView.Rows[i].Cells.Count; j++)        dr[j] = dataGridView.Rows[i].Cells[j].Value;    dt.Rows.Add(dr);}using (ExcelPackage package = new ExcelPackage()){    string path;    SaveFileDialog save = new SaveFileDialog();    save.FileName = $"{comboboxAward.Text}_{labelAwardQuota.Text}_{DateTime.Now:yyyyMMdd}.xlsx";    save.Filter = "(*.xlsx)|*.xlsx";    save.Title = "另存新档";    if (save.ShowDialog() == DialogResult.OK)    {        //取得使用者储存Excel的路径        path = Path.GetFullPath(save.FileName);        // 新增worksheet        ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("工作表2");        //新增DataTable到sheet        worksheet.Cells["A1"].LoadFromDataTable(dt, true);        package.SaveAs(path);    }}

重点:
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("工作表2");
worksheet.Cells["A1"].LoadFromDataTable(dt, true);
package.SaveAs(path);