C # simple encapsulation of reading and writing Excel

Easy to use, similar to ado.net

Exceltools. open ()

// Read/write

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 open a page of an Excel file // <Param name = "str_path"> Excel file path </param> /// <Param name = "str_sheet"> to operate </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); // Excel worksheet = (worksheet) workbook. worksheets [str_sheet];} # endregion # obtain the row number in the Excel worksheet from the region filtering date column // <Param name = "col_name"> list to be filtered </param> /// <Param name =" str_date_value "> value to be filtered </param> // <Param name =" ishastitle "> whether the header exists </param> // <returns> returns the row number, error returned-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 writes the value to a column cell in a row of a page in the Excel file that is currently open. // <Param name = "row"> write to a row that is currently open </param> /// <Param name = "col"> write a column </param> /// <Param name = "str_value"> value to be written </param> /// <returns> 0 is returned successfully, error returned-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 gets the largest used row number on the current available page. // <returns> 0 is returned successfully, error returned-1 </returns> Public static int getcursheetusedrangerowscount () {If (xlsapp = NULL) Return-1; int used_rng_rows = worksheet. usedrange. rows. count; return used_rng_rows;} # endregion # Save and close 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 # simple encapsulation of reading and writing Excel