Helped company personnel mm with a payroll splitting Tool

Introduction

Occasionally, at lunch time, I said that I had to work overtime for more than 8 o'clock. I copied and pasted the Excel content manually. The company has about 150 million people. I had to do this manually every time I paid my salary, split an Excel file into more than 150 Excel files and paste the content in the Excel file. So I wrote a small program to help the personnel mm solve the problem.

 

Solution

It mainly uses npoi to generate an Excel file, create an Excel file based on each record, read the employee name as the file name, and set EXCEL to read-only.

 

Interface Preview

A prompt will be displayed for import and split in the status bar.

 

Code

/// <Summary> /// read Excel /// </Summary> /// <Param name = "filepath"> </param> /// <returns> </returns> Public dataset todatatable (string filepath, string filename) {string connstr = ""; string filetype = system. io. path. getextension (filename); If (string. isnullorempty (filetype) return NULL; If (filetype = ". xls ") {connstr =" provider = Microsoft. jet. oledb.4.0; "+" Data Source = "+ filepath +"; "+"; e Xtended properties = \ "Excel 8.0; HDR = yes; IMEX = 1 \" ";}else {connstr =" provider = Microsoft. ace. oledb.12.0; "+" Data Source = "+ filepath +"; "+"; extended properties = \ "Excel 12.0; HDR = yes; IMEX = 1 \"";} string SQL _f = "select * from [{0}]"; oledbconnection conn = NULL; oledbdataadapter da = NULL; datatable dtsheetname = NULL; dataset DS = new dataset (); try {// initialize the connection and enable conn = new oledbconnection (con NSTR); Conn. open (); // obtain the table definition metadata of the data source string sheetname = ""; dtsheetname = Conn. getoledbschematable (oledbschemaguid. tables, new object [] {null, "table"}); // initialize the adapter da = new oledbdataadapter (); For (INT I = 0; I <dtsheetname. rows. count; I ++) {sheetname = (string) dtsheetname. rows [I] ["table_name"]; If (sheetname. contains ("$ ")&&! Sheetname. replace ("'",""). endswith ("$") {continue;} da. selectcommand = new oledbcommand (string. format (SQL _f, sheetname), Conn); dataset dsitem = new dataset (); DA. fill (dsitem, "mytable"); DS. tables. add (dsitem. tables [0]. copy () ;}} catch (exception ex) {} finally {// close the connection if (Conn. state = connectionstate. open) {Conn. close (); DA. dispose (); Conn. dispose () ;}} return Ds;} public void excelsp Lit able exceltable {// create a worksheet hssfworkbook workbook = new hssfworkbook (); isheet sheet = workbook. createsheet ("sheet1"); sheet. protectsheet ("123"); // encrypted EXCEL to achieve read-only // create the header irow headerrow = sheet. createrow (0); For (INT I = 0; I <exceltable. columns. count; I ++) {headerrow. createcell (I ). setcellvalue (exceltable. columns [I]. columnname);} int Index = 0; // Number of splits // create content irow datarow = sheet. createro W (1); filestream stream = NULL; If (! Directory. exists (@ "D:/myxls") {directory. createdirectory (@ "D:/myxls");} For (INT I = 0; I <exceltable. rows. count; I ++) {for (Int J = 0; j <exceltable. columns. count; j ++) {icell cell = datarow. createcell (j); cell. setcellvalue (exceltable. rows [I] [J]. tostring ();} string excelname = exceltable. rows [I] ["name"]. tostring () + "_" + datetime. now. tostring ("yyyy-mm") + ". xls "; stream = new filestream (@" D:/myxls/"+ excelname, filemode. create); workbook. write (Stream); index ++;} stream. close (); this. toolstripstatuslabel1.text = "split payroll:" + index + ""; this. cursor = cursors. default ;}