c#操作excel表格

     c#操作excel表格有很多方法，本文介绍的是基于Interop.Excel方式。该方式并不是winform操作excel的最好方法，本文介绍该方法主要是为一些仍有需求的小伙伴。建议有兴趣的小伙伴可以看一下miniexcel，该方法更简洁高效。

一、首先需要下载interop.excel库，在“管理NuGET程序包中下载

二、包含头文件

using Excel = Microsoft.Office.Interop.Excel;

三、操作excel

本文把相关读写excel封装成函数，具体如下：

1、往某个单元格写入数据

public bool WriteToCell(string openAddr,string sheet,int row,int column,string value)
        {
            try
            {
                Excel.Application excelAppWrite = null;
                Excel.Workbook workbookWrite = null;
                Excel.Worksheet worksheetWrite = null;

                excelAppWrite = new Excel.Application();
                workbookWrite = excelAppWrite.Workbooks.Open(openAddr);
                worksheetWrite = workbookWrite.Sheets[sheet];
                Excel.Range xlRange = worksheetWrite.UsedRange;

                xlRange.Cells[row, column] = value;

                // 关闭工作簿并退出Excel应用程序
                workbookWrite.Close(true);
                excelAppWrite.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheetWrite);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbookWrite);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelAppWrite);
                worksheetWrite = null;
                workbookWrite = null;
                excelAppWrite = null;
                GC.Collect();

                return true;
            }
            catch (Exception ex)
            {            
                return false;
            }
        }

2、往excel表最后一行写入数据

public bool WriteToRow(string openAddr, string sheet,string[] valueArray)
        {
            try
            {
                Excel.Application excelAppWrite = null;
                Excel.Workbook workbookWrite = null;
                Excel.Worksheet worksheetWrite = null;

                excelAppWrite = new Excel.Application();
                workbookWrite = excelAppWrite.Workbooks.Open(openAddr);
                worksheetWrite = workbookWrite.Sheets[sheet];
                Excel.Range xlRange = worksheetWrite.UsedRange;

                int numberOfRows = xlRange.Rows.Count;
                for (int i = 0; i < valueArray.Length; i++)
                {
                    xlRange.Cells[numberOfRows + 1, i + 1] = valueArray[i];
                }
                
                // 关闭工作簿并退出Excel应用程序
                workbookWrite.Close(true);
                excelAppWrite.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheetWrite);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbookWrite);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelAppWrite);
                worksheetWrite = null;
                workbookWrite = null;
                excelAppWrite = null;
                GC.Collect();

                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

3、把数据库数据写入excel

 public bool WriteToExcel(string openAddr, string sheet, DataTable dataTable)
        {
            try
            {
                Excel.Application excelAppWrite = null;
                Excel.Workbook workbookWrite = null;
                Excel.Worksheet worksheetWrite = null;

                excelAppWrite = new Excel.Application();
                workbookWrite = excelAppWrite.Workbooks.Open(openAddr);
                worksheetWrite = workbookWrite.Sheets[sheet];
                Excel.Range xlRange = worksheetWrite.UsedRange;

                int numberOfRows = xlRange.Rows.Count;
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    DataRow dataRow = dataTable.Rows[i];
                    for (int j = 0; j < dataTable .Columns.Count; j++)
                    {
                        xlRange.Cells[numberOfRows + i + 1, j + 1] = dataRow[j];
                    }
                }
            
                // 关闭工作簿并退出Excel应用程序
                workbookWrite.Close(true);
                excelAppWrite.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheetWrite);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbookWrite);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelAppWrite);
                worksheetWrite = null;
                workbookWrite = null;
                excelAppWrite = null;
                GC.Collect();

                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

4、读取某一个单元格数据

 public string ReadCell(string openAddr, string sheet, int row, int column)
        {
            try
            {
                Excel.Application excelAppWrite = null;
                Excel.Workbook workbookWrite = null;
                Excel.Worksheet worksheetWrite = null;

                excelAppWrite = new Excel.Application();
                workbookWrite = excelAppWrite.Workbooks.Open(openAddr);
                worksheetWrite = workbookWrite.Sheets[sheet];
                Excel.Range xlRange = worksheetWrite.UsedRange;

                string cellValue = xlRange.Cells[row, column].Text.ToString();

                // 关闭工作簿并退出Excel应用程序
                workbookWrite.Close(true);
                excelAppWrite.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheetWrite);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbookWrite);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelAppWrite);
                worksheetWrite = null;
                workbookWrite = null;
                excelAppWrite = null;
                GC.Collect();

                return cellValue;
            }
            catch (Exception ex)
            {
                return "";
            }
        }

5、读取最后一行数据

public List<string> ReadToRow(string openAddr, string sheet)
        {
            List<string> valueArray = new List<string>();
            try
            {
                Excel.Application excelAppWrite = null;
                Excel.Workbook workbookWrite = null;
                Excel.Worksheet worksheetWrite = null;

                excelAppWrite = new Excel.Application();
                workbookWrite = excelAppWrite.Workbooks.Open(openAddr);
                worksheetWrite = workbookWrite.Sheets[sheet];
                Excel.Range xlRange = worksheetWrite.UsedRange;

                int numberOfRows = xlRange.Rows.Count;
                int numberOfColumn = xlRange.Columns.Count;
                for(int i = 0; i < numberOfColumn; i++)
                {
                    valueArray.Add(xlRange.Cells[numberOfRows, i + 1].Text.ToString());
                }

                // 关闭工作簿并退出Excel应用程序
                workbookWrite.Close(true);
                excelAppWrite.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheetWrite);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbookWrite);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelAppWrite);
                worksheetWrite = null;
                workbookWrite = null;
                excelAppWrite = null;
                GC.Collect();

                return valueArray;
            }
            catch (Exception ex)
            {
                return valueArray;
            }
        }

最后给出详细代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data;

namespace Test
{
    class InteropExcelHelper
    {
        //往某个单元格写入数据
        public bool WriteToCell(string openAddr,string sheet,int row,int column,string value)
        {
            try
            {
                Excel.Application excelAppWrite = null;
                Excel.Workbook workbookWrite = null;
                Excel.Worksheet worksheetWrite = null;

                excelAppWrite = new Excel.Application();
                workbookWrite = excelAppWrite.Workbooks.Open(openAddr);
                worksheetWrite = workbookWrite.Sheets[sheet];
                Excel.Range xlRange = worksheetWrite.UsedRange;

                xlRange.Cells[row, column] = value;

                // 关闭工作簿并退出Excel应用程序
                workbookWrite.Close(true);
                excelAppWrite.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheetWrite);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbookWrite);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelAppWrite);
                worksheetWrite = null;
                workbookWrite = null;
                excelAppWrite = null;
                GC.Collect();

                return true;
            }
            catch (Exception ex)
            {            
                return false;
            }
        }

        //往最后一行写入数据
        public bool WriteToRow(string openAddr, string sheet,string[] valueArray)
        {
            try
            {
                Excel.Application excelAppWrite = null;
                Excel.Workbook workbookWrite = null;
                Excel.Worksheet worksheetWrite = null;

                excelAppWrite = new Excel.Application();
                workbookWrite = excelAppWrite.Workbooks.Open(openAddr);
                worksheetWrite = workbookWrite.Sheets[sheet];
                Excel.Range xlRange = worksheetWrite.UsedRange;

                int numberOfRows = xlRange.Rows.Count;
                for (int i = 0; i < valueArray.Length; i++)
                {
                    xlRange.Cells[numberOfRows + 1, i + 1] = valueArray[i];
                }
                
                // 关闭工作簿并退出Excel应用程序
                workbookWrite.Close(true);
                excelAppWrite.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheetWrite);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbookWrite);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelAppWrite);
                worksheetWrite = null;
                workbookWrite = null;
                excelAppWrite = null;
                GC.Collect();

                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        //把数据库数据写入工作表
        public bool WriteToExcel(string openAddr, string sheet, DataTable dataTable)
        {
            try
            {
                Excel.Application excelAppWrite = null;
                Excel.Workbook workbookWrite = null;
                Excel.Worksheet worksheetWrite = null;

                excelAppWrite = new Excel.Application();
                workbookWrite = excelAppWrite.Workbooks.Open(openAddr);
                worksheetWrite = workbookWrite.Sheets[sheet];
                Excel.Range xlRange = worksheetWrite.UsedRange;

                int numberOfRows = xlRange.Rows.Count;
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    DataRow dataRow = dataTable.Rows[i];
                    for (int j = 0; j < dataTable .Columns.Count; j++)
                    {
                        xlRange.Cells[numberOfRows + i + 1, j + 1] = dataRow[j];
                    }
                }
            
                // 关闭工作簿并退出Excel应用程序
                workbookWrite.Close(true);
                excelAppWrite.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheetWrite);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbookWrite);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelAppWrite);
                worksheetWrite = null;
                workbookWrite = null;
                excelAppWrite = null;
                GC.Collect();

                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        //读取某一个单元格数据
        public string ReadCell(string openAddr, string sheet, int row, int column)
        {
            try
            {
                Excel.Application excelAppWrite = null;
                Excel.Workbook workbookWrite = null;
                Excel.Worksheet worksheetWrite = null;

                excelAppWrite = new Excel.Application();
                workbookWrite = excelAppWrite.Workbooks.Open(openAddr);
                worksheetWrite = workbookWrite.Sheets[sheet];
                Excel.Range xlRange = worksheetWrite.UsedRange;

                string cellValue = xlRange.Cells[row, column].Text.ToString();

                // 关闭工作簿并退出Excel应用程序
                workbookWrite.Close(true);
                excelAppWrite.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheetWrite);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbookWrite);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelAppWrite);
                worksheetWrite = null;
                workbookWrite = null;
                excelAppWrite = null;
                GC.Collect();

                return cellValue;
            }
            catch (Exception ex)
            {
                return "";
            }
        }
        //读取最后一行数据
        public List<string> ReadToRow(string openAddr, string sheet)
        {
            List<string> valueArray = new List<string>();
            try
            {
                Excel.Application excelAppWrite = null;
                Excel.Workbook workbookWrite = null;
                Excel.Worksheet worksheetWrite = null;

                excelAppWrite = new Excel.Application();
                workbookWrite = excelAppWrite.Workbooks.Open(openAddr);
                worksheetWrite = workbookWrite.Sheets[sheet];
                Excel.Range xlRange = worksheetWrite.UsedRange;

                int numberOfRows = xlRange.Rows.Count;
                int numberOfColumn = xlRange.Columns.Count;
                for(int i = 0; i < numberOfColumn; i++)
                {
                    valueArray.Add(xlRange.Cells[numberOfRows, i + 1].Text.ToString());
                }

                // 关闭工作簿并退出Excel应用程序
                workbookWrite.Close(true);
                excelAppWrite.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheetWrite);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbookWrite);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelAppWrite);
                worksheetWrite = null;
                workbookWrite = null;
                excelAppWrite = null;
                GC.Collect();

                return valueArray;
            }
            catch (Exception ex)
            {
                return valueArray;
            }
        }
    }
}