1    /// 
   2 ///   3 /// 
   4 ///    5 ///    6 /// 
        7         public string CreateSignExcel(string filePath, string newFilePath, DataTable dt)   8         {
     9             if (File.Exists(newFilePath))  10                 File.Delete(newFilePath);  11             FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read);  12             HSSFWorkbook hssfworkbook;  13             try  14             {
    15                 hssfworkbook = new HSSFWorkbook(file);  16             }  17             catch (NPOI.EncryptedDocumentException exe)//工作簿写保护报错  18             {
    19                 return "";  20             }  21             int FileSheet = 0;  22             FileSheet = hssfworkbook.NumberOfSheets;  23             //npoi获取列数  24             int firstrownum = 0, maxRowNum = 0, maxColNum = 0;  25             ICellStyle cellStyle = hssfworkbook.CreateCellStyle();  26             cellStyle.WrapText = true;  27             //纵  28             cellStyle.VerticalAlignment = VerticalAlignment.CENTER;  29             //横  30             cellStyle.Alignment = HorizontalAlignment.CENTER;  31             try  32             {
    33                 for (int i = 0; i < FileSheet; i++)  34                 {
    35                     HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(i);  36                     HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();  37                     int hidecolemns = 0;//判断最大隐藏行  38                     sheet.IsActive = true;  39                     maxRowNum = sheet.LastRowNum; //最大行数  40                     firstrownum = sheet.FirstRowNum;//开始行数  41                     System.Collections.IEnumerator rows = sheet.GetRowEnumerator();  42                     while (rows.MoveNext())  43                     {
    44                         IRow row = (HSSFRow)rows.Current;  45                         if (maxColNum < row.LastCellNum)  46                             maxColNum = row.LastCellNum;  47                     }  48                     if (maxRowNum < 2)  49                     {
    50                         //锁定单元格  51 //sheet.Protect = true;//如何动态去到密码保护，另外如何设置密码  52                         sheet.ProtectSheet("1");  53                         continue;  54                     }  55                     maxRowNum += 1;  56                     int a = 0;  57                     #region 签字  58                     for (int k = 0; k < dt.Rows.Count; k++)  59                     {
    60                         if (a == 0)  61                         {
    62                             for (int l = 0; l < 4; l++)  63                             {
    64                                 IRow row1;  65                                 row1 = sheet.GetRow(maxRowNum + 1 + l);  66                                 if (row1 == null)  67                                 {
    68                                     row1 = sheet.CreateRow(maxRowNum + 1 + l);  69                                 }  70                                 for (int m = 0; m < 3; m++)  71                                 {
    72                                     ICell cell1;  73                                     cell1 = row1.GetCell(m);  74                                     if (cell1 == null)  75                                     {
    76                                         cell1 = row1.CreateCell(m);  77                                         cell1.CellStyle = cellStyle;  78                                     }  79                                 }  80                                 row1.GetCell(0).SetCellValue(dt.Rows[k]["UserDscp"].ToString());  81                             }  82                             CellRangeAddress region = new CellRangeAddress(maxRowNum + 1, maxRowNum + 4, 0, 0);  83                             sheet.AddMergedRegion(region);  84                         }  85                         else  86                         {
    87                             for (int l = 0; l < 4; l++)  88                             {
    89                                 IRow row1;  90                                 row1 = sheet.GetRow(maxRowNum + 1 + l);  91                                 if (row1 == null)  92                                 {
    93                                     row1 = sheet.CreateRow(maxRowNum + 1 + l);  94                                 }  95                                 for (int m = 3; m < 6; m++)  96                                 {
    97                                     ICell cell1;  98                                     cell1 = row1.GetCell(m);  99                                     if (cell1 == null) 100                                     {
   101                                         cell1 = row1.CreateCell(m); 102                                         cell1.CellStyle = cellStyle; 103                                     } 104                                 } 105                                 row1.GetCell(3).SetCellValue(dt.Rows[k]["UserDscp"].ToString()); 106                             } 107                             CellRangeAddress region = new CellRangeAddress(maxRowNum + 1, maxRowNum + 4, 3, 3); 108                             sheet.AddMergedRegion(region); 109                         } 110                         string appresulte = dt.Rows[k]["AppResult"].ToString(); 111                         if (appresulte == string.Empty || appresulte == "NULL")//此时未审核 112                         {
   113                             IRow row1 = sheet.GetRow(maxRowNum + 1); 114                             IRow row2 = sheet.GetRow(maxRowNum + 2); 115                             IRow row3 = sheet.GetRow(maxRowNum + 3); 116                             IRow row4 = sheet.GetRow(maxRowNum + 4); 117                             if (a == 0) 118                             {
   119                                 row1.GetCell(1).SetCellValue("未审核"); 120                                 row1.GetCell(2).SetCellValue("未审核"); 121                                 row2.GetCell(1).SetCellValue("未审核"); 122                                 row2.GetCell(2).SetCellValue("未审核"); 123                                 row3.GetCell(1).SetCellValue("未审核"); 124                                 row3.GetCell(2).SetCellValue("未审核"); 125                                 row4.GetCell(1).SetCellValue("未审核"); 126                                 row4.GetCell(2).SetCellValue("未审核"); 127                             } 128                             else 129                             {
   130                                 row1.GetCell(4).SetCellValue("未审核"); 131                                 row1.GetCell(5).SetCellValue("未审核"); 132                                 row2.GetCell(4).SetCellValue("未审核"); 133                                 row2.GetCell(5).SetCellValue("未审核"); 134                                 row3.GetCell(4).SetCellValue("未审核"); 135                                 row3.GetCell(5).SetCellValue("未审核"); 136                                 row4.GetCell(4).SetCellValue("未审核"); 137                                 row4.GetCell(5).SetCellValue("未审核"); 138                             } 139                         } 140                         else 141                         {
   142                             if (appresulte == "2")//拒绝 143                             {
   144                                 string apprue = dt.Rows[k]["approvername"].ToString() + "拒绝(" + dt.Rows[k]["appcause"].ToString() + ")"; 145                                 IRow row1 = sheet.GetRow(maxRowNum + 1); 146                                 IRow row2 = sheet.GetRow(maxRowNum + 2); 147                                 IRow row3 = sheet.GetRow(maxRowNum + 3); 148                                 IRow row4 = sheet.GetRow(maxRowNum + 4); 149                                 if (a == 0) 150                                 {
   151                                     row1.GetCell(1).SetCellValue(apprue); 152                                     row1.GetCell(2).SetCellValue(apprue); 153                                     row2.GetCell(1).SetCellValue(apprue); 154                                     row2.GetCell(2).SetCellValue(apprue); 155                                     row3.GetCell(1).SetCellValue(apprue); 156                                     row3.GetCell(2).SetCellValue(apprue); 157                                     row4.GetCell(1).SetCellValue(apprue); 158                                     row4.GetCell(2).SetCellValue(apprue); 159                                 } 160                                 else 161                                 {
   162                                     row1.GetCell(4).SetCellValue(apprue); 163                                     row1.GetCell(5).SetCellValue(apprue); 164                                     row2.GetCell(4).SetCellValue(apprue); 165                                     row2.GetCell(5).SetCellValue(apprue); 166                                     row3.GetCell(4).SetCellValue(apprue); 167                                     row3.GetCell(5).SetCellValue(apprue); 168                                     row4.GetCell(4).SetCellValue(apprue); 169                                     row4.GetCell(5).SetCellValue(apprue); 170                                 } 171                             } 172                             else 173                             {
   174                                 int startcol = 1;// endcol = 2; 175                                 if (a == 1) 176                                 {
   177                                     startcol = 4; 178                                     //endcol = 4; 179                                 } 180                                 if (dt.Rows[k]["qz"].ToString().Trim() != "" && dt.Rows[k]["qz"].ToString().Trim() != "NULL")//签字同意且存在用户图片 181                                 {
   182                                     byte[] bytes; 183                                     bytes = (byte[])dt.Rows[k]["qz"]; 184 185                                     if (a == 0) 186                                     {
   187                                         int pictureIdx = hssfworkbook.AddPicture(bytes, PictureType.JPEG); 188                                         HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, startcol, maxRowNum + 1, 3, maxRowNum + 4);//后四位 第几列开始第几行开始，共几列到第几行 189                                         HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); 190                                     } 191                                     else 192                                     {
   193                                         int pictureIdx1 = hssfworkbook.AddPicture(bytes, PictureType.JPEG); 194                                         HSSFClientAnchor anchor1 = new HSSFClientAnchor(0, 0, 0, 0, 4, maxRowNum + 1, 6, maxRowNum + 4);//后四位 第几列开始第几行开始，共几列到第几行 195                                         HSSFPicture pict1 = (HSSFPicture)patriarch.CreatePicture(anchor1, pictureIdx1); 196                                     } 197                                 } 198                                 else 199                                 {
   200                                     string apprue = dt.Rows[k]["approvername"].ToString() + "同意(" + dt.Rows[k]["appcause"].ToString() + ")"; 201                                     IRow row1 = sheet.GetRow(maxRowNum + 1); 202                                     IRow row2 = sheet.GetRow(maxRowNum + 2); 203                                     IRow row3 = sheet.GetRow(maxRowNum + 3); 204                                     IRow row4 = sheet.GetRow(maxRowNum + 4); 205                                     if (a == 0) 206                                     {
   207                                         row1.GetCell(1).SetCellValue(apprue); 208                                         row1.GetCell(2).SetCellValue(apprue); 209                                         row2.GetCell(1).SetCellValue(apprue); 210                                         row2.GetCell(2).SetCellValue(apprue); 211                                         row3.GetCell(1).SetCellValue(apprue); 212                                         row3.GetCell(2).SetCellValue(apprue); 213                                         row4.GetCell(1).SetCellValue(apprue); 214                                         row4.GetCell(2).SetCellValue(apprue); 215                                     } 216                                     else 217                                     {
   218                                         row1.GetCell(4).SetCellValue(apprue); 219                                         row1.GetCell(5).SetCellValue(apprue); 220                                         row2.GetCell(4).SetCellValue(apprue); 221                                         row2.GetCell(5).SetCellValue(apprue); 222                                         row3.GetCell(4).SetCellValue(apprue); 223                                         row3.GetCell(5).SetCellValue(apprue); 224                                         row4.GetCell(4).SetCellValue(apprue); 225                                         row4.GetCell(5).SetCellValue(apprue); 226                                     } 227                                 } 228                             } 229                         } 230                         if (a == 0) 231                         {
   232                             a = 1; 233                         } 234                         else 235                         {
   236                             a = 0; 237                             maxRowNum += 4; 238                         } 239                     } 240                     #endregion 241                     sheet.ProtectSheet("1"); 242                 } 243             } 244             catch (Exception exp) 245             {
   246                 hssfworkbook.Dispose(); 247                 file.Close(); 248                 file.Dispose(); 249                 return ""; 250             } 251 252 　　　　//这里遗憾的是缺少保护工作簿(保护结构和窗口)，实现excel在线审批签字功能，如果不能进行保护工作簿，导致用户可以新建工作表删除原有工作表。 253 254             FileStream file2 = new FileStream(newFilePath, FileMode.Create); 255 256             hssfworkbook.Write(file2); 257             file.Close(); 258             file.Dispose(); 259             file2.Close(); 260             file2.Dispose(); 261             hssfworkbook.Dispose(); 262             return newFilePath; 263         }