1 /// <summary>
2 ///
3 /// </summary>
4 /// <param name="filePath"></param>
5 /// <param name="newFilePath"></param>
6 /// <returns></returns>
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 }
转载于:https://www.cnblogs.com/xinchen/archive/2012/02/13/2348713.html
535
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
