C#Excel相关

1.引用dll文件

解决方案资源管理器→右键“引用”→“添加引用”→“程序集”→“扩展”→“Microsoft.Office.Interop.Excel”。

添加using

using Excel = Microsoft.Office.Interop.Excel;

2.相关变量

object missing = Missing.Value;
        Excel.Application xlApp;
        Excel.Workbooks workbooks;
        Excel.Workbook workbook;
        Excel.Worksheet worksheet;
        Excel.Range range;

xlApp = new Excel.Application();
                workbooks = xlApp.Workbooks;
                workbook = workbooks.Open(strPath, missing, false, missing, missing, missing, missing, missing, missing,
                    true, missing, missing, missing, missing, missing);//strPath为Excel路径
                worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1

3.判断Excel中是否有空值

返回true有空值，返回false无空值。

private bool IsExcelExistNull()
        {
            if (xlApp == null)
            {
                xlApp = new Excel.Application();
                workbooks = xlApp.Workbooks;
                workbook = workbooks.Open(strPath, missing, false, missing, missing, missing, missing, missing, missing,
                    true, missing, missing, missing, missing, missing);
                worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 
            }

            rowCount = worksheet.Cells.Find("*", missing, missing, missing, Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlPrevious, false, missing, missing).Row;//行数
            colCount = worksheet.Cells.Find("*", missing, missing, missing, Excel.XlSearchOrder.xlByColumns, Excel.XlSearchDirection.xlPrevious, false, missing, missing).Column;//列数

            for (int i = 1;i <= rowCount;i++)
            {
                for (int j = 1;j <= colCount;j++)
                {
                    if (worksheet.Cells[i, j].Value == null)
                    {
                        return true;
                    }
                }
            }
            return false;
        }

4.查看Excel是否被占用

返回true被占用，返回false没有占用。

private bool IsFileLocked(string strPath)
        {
            try
            {
                if (!File.Exists(strPath))
                    return false;
                using (var fs = new FileStream(strPath, FileMode.Open, FileAccess.Read, FileShare.None))
                {
                    fs.Close();
                }
            }
            catch
            {
                return true;
            }
            return false;
        }

5.释放Excel占用

if (xlApp != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject((object)worksheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject((object)workbook);
                xlApp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject((object)xlApp);
                GC.Collect();//强行销毁
            }

6.删除Excel某行

range = worksheet.Rows[i];
range.Delete(Excel.XlDirection.xlDown);

7.新增工作簿

worksheet = workbook.Worksheets.Add(After: worksheet);//插入到现有工作簿的最后面

依次插入多个工作簿：

for (int i = 1; i < sheets; i++)  //sheets为想要拥有的工作簿总个数
            {
                worksheet = workbook.Worksheets.Add(After: worksheet);
            }

8.单元格格式

range = worksheet.Cells;
range.ColumnWidth = 30;//列宽
range.NumberFormatLocal = "0.0000000000";//保留十位小数
range.HorizontalAlignment = Excel.Constants.xlCenter;//水平居中
range.VerticalAlignment = Excel.Constants.xlCenter;//垂直居中
range = worksheet.Columns[1]; range.Font.Color = Color.Red;//第1列字体为红色
range = worksheet.Rows[1]; range.Font.Color = Color.Red;//第1行字体为红色

9.引入Excel公式

worksheet.Cells[2, 8] = "=AVERAGE(A:A)";//A列平均值

10.保存修改

workbook.Save();