NPOI生成Excel

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

 

解决方法

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

 

界面预览

     

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

 

代码

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132

/// <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;        }