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

Source: Internet
Author: User
Tags table definition

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?

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.