C#讀寫EXCEL的簡單封裝

標籤：style   color   io   os   ar   for   檔案   sp   cti   

用法簡單，類似於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    }}

 

C#讀寫EXCEL的簡單封裝