Helped the company's personnel MM to make a payroll split tool, mm split

Helped the company's personnel MM to make a payroll split tool, mm split
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 ;}

 

How to split a payroll Into a payroll format

Is to cut it into strips.
 
Let's talk about how to crop the salary from the personnel department ?? Is this reasonable ??

Our company's payroll has always been cut by our personnel department, and several companies in the industrial park have also been cut by the Administration Department. However, others always think that we are idle in personnel work, ** dixiong: Why didn't they see it when we were so busy as head bears?