C#ClosedXML读写Excel文件

---

前言

关键词：C#；ClosedXML；Excel表格数据的读写

---

一、ClosedXML的介绍

ClosedXML是一个.NET第三方库，以OpenXML为基础，用于读取、操作和写入Excel 2007+ (.xlsx， .xlsm)文件。

二、使用步骤

1.文件格式转换

因为ClosedXML只能对.xlsx格式的文件进行转换，当文件格式为2007以前的.xls时，需要将.xls转换成.xlsx格式的文件。

方法：运用宏命令对文件格式进行转换

具体操作可参考：VBA宏命令xls转xlsx

代码如下（示例）：

'***********访问当前文件夹下所有子文件夹及文件,
Dim iFile(1 To 100000) As String
Dim count As Integer
Sub xls2xlsx()
 iPath = ThisWorkbook.Path
 On Error Resume Next
 count = 0
 zdir iPath
 For i = 1 To count
 If iFile(i) Like "*.xls" And iFile(i) <> ThisWorkbook.FullName Then
 MyFile = iFile(i)
 FilePath = Replace(MyFile, ".xls", ".xlsx")
 If Dir(FilePath, 16) = Empty Then
 Set WBookOther = Workbooks.Open(MyFile)
 Application.ScreenUpdating = False
 ActiveWorkbook.SaveAs Filename:=FilePath, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
 WBookOther.Close SaveChanges:=False '解决不能close 文件问题
 Application.ScreenUpdating = True
 End If
 End If
 Next
End Sub
Sub zdir(p) '访问当前文件夹下所有子文件夹及文件
 Set fs = CreateObject("scripting.filesystemobject")
 For Each f In fs.GetFolder(p).Files
 If f <> ThisWorkbook.FullName Then count = count + 1: iFile(count) = f
 Next
 For Each m In fs.GetFolder(p).SubFolders
 zdir m
 Next
End Sub

2.读.xlsx文件

1. 引入ClosedXML， 在项目的NuGet包管理器中搜索并安装ClosedXML
   

2. 添加using ClosedXML.Excel;

3. 构造读取.xlsx文件的函数，代码如下（示例）：

/// <summary>
/// 将EXCEL数据读取到DT
/// </summary>
/// <param name="fileFullName">文件的完整路径</param>
/// <returns>dt数据表</returns>
public static DataTable ImportExcelToDataTable(string fileFullName)
{
	//创建数据表用于存放读取到的数据
    DataTable dt = new DataTable();
    DataColumn 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);
    dataColumn = new DataColumn("columnName5");
    dt.Columns.Add(dataColumn);
    dataColumn = new DataColumn("columnName6");
    dt.Columns.Add(dataColumn);
    dataColumn = new DataColumn("columnName7");
    dt.Columns.Add(dataColumn);
    dataColumn = new DataColumn("columnName8");
    dt.Columns.Add(dataColumn);
    dataColumn = new DataColumn("columnName9");
    dt.Columns.Add(dataColumn);
    dataColumn = new DataColumn("columnName10");
    dt.Columns.Add(dataColumn);
    dataColumn = new DataColumn("columnName11");
    dt.Columns.Add(dataColumn);
    
    using ( XLWorkbook workBook = new XLWorkbook(fileFullName))
    {
        IXLWorksheet workSheet = workBook.Worksheet(1);//获得第一个Sheet
        //遍历所有可使用的行
        var rows = workSheet.RowsUsed();
        foreach (var row in rows)
        {
            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;
}

3.写.xlsx文件

/// <summary>
/// 将DT数据写入EXCEL
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="fileName">文件名</param>
public static void ExportToExcel(DataTable dt ,string fileName)
{
    //整体提取
    //XLWorkbook wb = new XLWorkbook();
    //wb.Worksheets.Add(dt, "WorksheetName");
    //wb.SaveAs("HelloWorld.xlsx");
    using (XLWorkbook workBook = new XLWorkbook())
    {
        if (dt.TableName == "")
        {
            dt.TableName = "sheet1";
        }
        var workSheet = workBook.Worksheets.Add(dt.TableName);
        for (int i = 0,j=0; i < dt.Rows.Count; i++)
        {
            if (dt.Rows[i][0].ToString() == "编号")
            {
                j++;
                workSheet.Cell(j, 1).Value = dt.Rows[i][1].ToString();
                workSheet.Cell(j, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                workSheet.Cell(j, 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
            }
           
        }
        for (int i = 0,j=0; i < dt.Rows.Count; i++)
        {
            if (dt.Rows[i][0].ToString() == "树干检测结论")
            {
                j++;
                workSheet.Cell(j, 2).Value = dt.Rows[i][1].ToString();
                workSheet.Cell(j, 2).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                workSheet.Cell(j, 2).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
            }

        }
        for (int i = 0, j = 0; i < dt.Rows.Count; i++)
        {
            if (dt.Rows[i][6].ToString() == "调查时间")
            {
                j++;
                string Schedule = DateTime.FromOADate(double.Parse(dt.Rows[i][7].ToString())).ToString("yyyy/MM/dd", System.Globalization.DateTimeFormatInfo.InvariantInfo);
                workSheet.Cell(j, 3).Value = Schedule;
                workSheet.Cell(j, 3).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                workSheet.Cell(j, 3).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
            }
        }
        workBook.SaveAs(@"C:\" + fileName+".xlsx");
    }
}

4.遍历文件夹中的所有xlsx文件

/// <summary>
/// 递归遍历文件夹中的所有EXCEL文件
/// </summary>
/// <param name="directory"></param>
/// <param name="pattern"></param>
/// <returns></returns>
public static List<string> GetFiles(string directory ,string pattern = "*.xlsx") { 
    List<string> files = new List<string>();
    foreach (var item in Directory.GetFiles(directory,pattern))
    {
        files.Add(item);
    }
    foreach (var item in Directory.GetDirectories(directory))
    {
        files.AddRange(GetFiles(item, pattern)) ;

    }
    return files;
}