C#读写EXCEL的简单封装

来源：互联网发布：webservice和json区别编辑：程序博客网时间：2024/06/06 01:35
用法简单，类似于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    }}
0 0