C#使用ClosedXML读写excel

MartinYangHJ

已于 2022-07-22 17:41:55 修改

阅读量2.3k

点赞数

分类专栏： C# 文章标签： c# 开发语言

于 2022-07-22 17:39:12 首次发布

本文链接：https://blog.csdn.net/yhj198927/article/details/125936736

版权

C# 专栏收录该内容

70 篇文章 2 订阅

订阅专栏

首先使用NuGet安装ClosedXML,然后在程序中添加引用ClosedXML.Excel;

1.导出数据到Excel

public void ExportToExcel(DataTable dt)
{
   using (var workbook = new XLWorkbook())
   {
        if (dt.TableName == "") dt.TableName = "sheet1";
        var worksheet = workbook.Worksheets.Add(dt.TableName);
        var header = worksheet.FirstRow();
        for (int i = 0; i < dt.Columns.Count; ++i)
        {
            worksheet.Cell(1, i + 1).Value = dt.Columns[i].ColumnName;
            worksheet.Cell(1, i + 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
            worksheet.Cell(1, i + 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        }
        for (int i = 0; i < dt.Rows.Count; ++i)
        {
            for (int j = 0; j < dt.Columns.Count; ++j)
            {
                worksheet.Cell(i + 2, j + 1).Value = dt.Rows[i][j].ToString();
                worksheet.Cell(i + 2, j + 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                worksheet.Cell(i + 2, j + 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
             }
         }
         workbook.SaveAs(sfd.FileName);
     }         
}

2.从Excel读取数据

public DataTable ImportExcelToDataTable(string filepath)
{
   DataTable dt = new DataTable();
   DataColumn dataColumn=new DataColumn("columnName1");
   dt.Columns.Add(dataColumn);
   dataColumn=new DataColumn("columnName2");
   dt.Columns.Add(dataColumn);
   dataColumn=new DataColumn("columnName3");
   dt.Columns.Add(dataColumn);
   dataColumn=new DataColumn("columnName4");
   dt.Columns.Add(dataColumn);
   using (XLWorkbook workBook = new XLWorkbook(filepath))
   {
       IXLWorksheet workSheet = workBook.Worksheet(1);                  
       bool isFirstRow = true;
       foreach (var row in workSheet.Rows())
       {
           if (isFirstRow)
           { 
                isFirstRow = false;
                continue;
           }
           else
           {
                dt.Rows.Add();
                int i = 0;
                foreach (IXLCell cell in row.Cells())
                {
                     if(i>dt.Columns.Count-1)
                     {
                        break;
                     }
                     dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                     i++;
                }
            }
         }
   }
   return dt;
}