C#读写EXCEL的简单封装

用法简单，类似于ado.net

ExcelTools.Open()

//读写

ExcelTools.SaveAndClose()

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.IO;
using System.Windows.Forms;
using System.Reflection;

namespace AutoReportDeal
{
    class ExcelTools
    {
        public static Microsoft.Office.Interop.Excel.Application xlsApp = null;
        public static Microsoft.Office.Interop.Excel.Workbook workbook = null;
        public static Microsoft.Office.Interop.Excel.Worksheet worksheet = null;

        public static string str_this_path = null;

        #region 打开某EXCEL文件的某个页
        /// <param name="str_path">EXCEL文件路径</param>
        /// <param name="str_sheet">要操作的页</param>
        public static void Open(string str_path, string str_sheet)
        {
            str_this_path = str_path;
            //Excel Application
            xlsApp = new Microsoft.Office.Interop.Excel.Application();

            //Excel Workbook
            workbook = xlsApp.Workbooks.Open(str_path, 0, true, 5,
                                             System.Reflection.Missing.Value,
                                             System.Reflection.Missing.Value,
                                             false, System.Reflection.Missing.Value,
                                             System.Reflection.Missing.Value, true,
                                             false, System.Reflection.Missing.Value,
                                             false, false, false);

            //Excel Worksheet
            worksheet = (Worksheet)workbook.Worksheets[str_sheet];
        } 
        #endregion

        #region 筛选日期列获得在EXCEL中的行号
        /// <param name="col_name">要进行筛选的列明</param>
        /// <param name="str_date_value">要筛选的值</param>
        /// <param name="isHasTitle">是否存在表头</param>
        /// <returns>成功返回行号，出错返回-1</returns>
        public static int GetExcelRowsIndexByDate(string col_name, string str_date_value, bool isHasTitle)
        {
            if (str_date_value == null)
                return -1;

            int row = 2;
            if (isHasTitle == false)
                row = 1;

            DateTime cTime = Convert.ToDateTime(str_date_value);
            for (; row <= worksheet.UsedRange.Rows.Count; ++row)
            {
                Microsoft.Office.Interop.Excel.Range rng = worksheet.get_Range(col_name + row.ToString(), Missing.Value);
                DateTime dTime = Convert.ToDateTime(rng.Text.ToString().Trim());
                if (cTime.Year == dTime.Year && cTime.Month == dTime.Month && cTime.Day == dTime.Day)
                    return row;
            }

            return -1;
        } 
        #endregion

        #region 将值写入到当前打开的EXCEL文件中某页的某行某列单元格中
        /// <param name="row">写入当前打开的某行</param>
        /// <param name="col">写入某列</param>
        /// <param name="str_value">要写入的值</param>
        /// <returns>成功返回0，出错返回-1</returns>
        public static int WriteToExcel(int row, int col, string str_value)
        {
            if (row < 0 || col < 0 || str_value == null || xlsApp == null)
                return -1;

            worksheet.Cells[row, col] = str_value;
            return 0;
        } 
        #endregion

        #region 获取当前可用页中的已用的最大行号
        /// <returns>成功返回0，出错返回-1</returns>
        public static int GetCurSheetUsedRangeRowsCount()
        {
            if (xlsApp == null)
                return -1;

            int used_rng_rows = worksheet.UsedRange.Rows.Count;

            return used_rng_rows;
        }
        #endregion

        

        #region 保存并关闭
        public static void CloseAndSave()
        {
            xlsApp.DisplayAlerts = false;
            xlsApp.AlertBeforeOverwriting = false;
            if (File.Exists(str_this_path))
            {
                File.Delete(str_this_path);
            }
            xlsApp.ActiveWorkbook.SaveCopyAs(str_this_path);
            xlsApp.Quit();
            xlsApp = null;
            workbook = null;
            worksheet = null;
            str_this_path = null;
        }         
        #endregion
    }
}