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