首页 > 代码库 > 帮公司人事MM做了个工资条拆分工具

帮公司人事MM做了个工资条拆分工具

引言

      偶尔一次午饭时人事说加班加到8点多,纯手工复制粘贴Excel的内容,公司大概150多人吧,每次发工资时都需要这样手动处理,将一个Excel拆分成150多个Excel,再把里面的内容粘过去,如此循环。于是,我写了个小程序帮人事MM解决。

 

解决方法

     主要是用到了NPOI生成Excel,根据每条记录创建一个Excel,并读取员工姓名作为文件名,并设置Excel为只读。

 

界面预览

    

      导入和拆分在状态栏都会有相应提示

 

代码

 /// <summary>        /// 读取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 + ";" + ";Extended 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            {                // 初始化连接,并打开                conn = new OleDbConnection(connStr);                conn.Open();                // 获取数据源的表定义元数据                                        string SheetName = "";                dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });                // 初始化适配器                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            {                // 关闭连接                if (conn.State == ConnectionState.Open)                {                    conn.Close();                    da.Dispose();                    conn.Dispose();                }            }            return ds;        }        public void ExcelSplit(DataTable excelTable)        {            //创建工作表            HSSFWorkbook workbook = new HSSFWorkbook();            ISheet sheet = workbook.CreateSheet("Sheet1");            sheet.ProtectSheet("123"); //加密Excel,从而实现只读            //创建表头            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; //拆分个数            //创建内容            IRow datarow = sheet.CreateRow(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]["姓名"].ToString()+"_"+DateTime.Now.ToString("yyyy-MM")+ ".xls";                stream = new FileStream(@"d:/MyXls/" + excelname, FileMode.Create);                workbook.Write(stream);                               index++;            }            stream.Close();                        this.toolStripStatusLabel1.Text = "共拆分工资条:" + index + "条";            this.Cursor = Cursors.Default;        }