NPOI實作excel 2003 線上審批簽字功能，缺陷不知如何進行工作簿保護?求解

 /// <summary>

///

/// </summary>

/// <param name="filePath"></param>

/// <param name="newFilePath"></param>

/// <returns></returns>

public string CreateSignExcel(string filePath, string newFilePath, DataTable dt)

{

if (File.Exists(newFilePath))

File.Delete(newFilePath);

FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read);

HSSFWorkbook hssfworkbook;

try

{

hssfworkbook = new HSSFWorkbook(file);

}

catch (NPOI.EncryptedDocumentException exe)//工作簿寫保護報錯

{

return "";

}

int FileSheet = 0;

FileSheet = hssfworkbook.NumberOfSheets;

//npoi擷取列數

int firstrownum = 0, maxRowNum = 0, maxColNum = 0;

ICellStyle cellStyle = hssfworkbook.CreateCellStyle();

cellStyle.WrapText = true;

//縱

cellStyle.VerticalAlignment = VerticalAlignment.CENTER;

//橫

cellStyle.Alignment = HorizontalAlignment.CENTER;

try

{

for (int i = 0; i < FileSheet; i++)

{

HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(i);

HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();

int hidecolemns = 0;//判斷最大隐藏行

sheet.IsActive = true;

maxRowNum = sheet.LastRowNum; //最大行數

firstrownum = sheet.FirstRowNum;//開始行數

System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

while (rows.MoveNext())

{

IRow row = (HSSFRow)rows.Current;

if (maxColNum < row.LastCellNum)

maxColNum = row.LastCellNum;

}

if (maxRowNum < 2)

{

//鎖定單元格

//sheet.Protect = true;//如何動态去到密碼保護，另外如何設定密碼

sheet.ProtectSheet("1");

continue;

}

maxRowNum += 1;

int a = 0;

#region 簽字

for (int k = 0; k < dt.Rows.Count; k++)

{

if (a == 0)

{

for (int l = 0; l < 4; l++)

{

IRow row1;

row1 = sheet.GetRow(maxRowNum + 1 + l);

if (row1 == null)

{

row1 = sheet.CreateRow(maxRowNum + 1 + l);

}

for (int m = 0; m < 3; m++)

{

ICell cell1;

cell1 = row1.GetCell(m);

if (cell1 == null)

{

cell1 = row1.CreateCell(m);

cell1.CellStyle = cellStyle;

}

}

row1.GetCell(0).SetCellValue(dt.Rows[k]["UserDscp"].ToString());

}

CellRangeAddress region = new CellRangeAddress(maxRowNum + 1, maxRowNum + 4, 0, 0);

sheet.AddMergedRegion(region);

}

else

{

for (int l = 0; l < 4; l++)

{

IRow row1;

row1 = sheet.GetRow(maxRowNum + 1 + l);

if (row1 == null)

{

row1 = sheet.CreateRow(maxRowNum + 1 + l);

}

for (int m = 3; m < 6; m++)

{

ICell cell1;

cell1 = row1.GetCell(m);

if (cell1 == null)

{

cell1 = row1.CreateCell(m);

cell1.CellStyle = cellStyle;

}

}

row1.GetCell(3).SetCellValue(dt.Rows[k]["UserDscp"].ToString());

}

CellRangeAddress region = new CellRangeAddress(maxRowNum + 1, maxRowNum + 4, 3, 3);

sheet.AddMergedRegion(region);

}

string appresulte = dt.Rows[k]["AppResult"].ToString();

if (appresulte == string.Empty || appresulte == "NULL")//此時未稽核

{

IRow row1 = sheet.GetRow(maxRowNum + 1);

IRow row2 = sheet.GetRow(maxRowNum + 2);

IRow row3 = sheet.GetRow(maxRowNum + 3);

IRow row4 = sheet.GetRow(maxRowNum + 4);

if (a == 0)

{

row1.GetCell(1).SetCellValue("未稽核");

row1.GetCell(2).SetCellValue("未稽核");

row2.GetCell(1).SetCellValue("未稽核");

row2.GetCell(2).SetCellValue("未稽核");

row3.GetCell(1).SetCellValue("未稽核");

row3.GetCell(2).SetCellValue("未稽核");

row4.GetCell(1).SetCellValue("未稽核");

row4.GetCell(2).SetCellValue("未稽核");

}

else

{

row1.GetCell(4).SetCellValue("未稽核");

row1.GetCell(5).SetCellValue("未稽核");

row2.GetCell(4).SetCellValue("未稽核");

row2.GetCell(5).SetCellValue("未稽核");

row3.GetCell(4).SetCellValue("未稽核");

row3.GetCell(5).SetCellValue("未稽核");

row4.GetCell(4).SetCellValue("未稽核");

row4.GetCell(5).SetCellValue("未稽核");

}

}

else

{

if (appresulte == "2")//拒絕

{

string apprue = dt.Rows[k]["approvername"].ToString() + "拒絕(" + dt.Rows[k]["appcause"].ToString() + ")";

IRow row1 = sheet.GetRow(maxRowNum + 1);

IRow row2 = sheet.GetRow(maxRowNum + 2);

IRow row3 = sheet.GetRow(maxRowNum + 3);

IRow row4 = sheet.GetRow(maxRowNum + 4);

if (a == 0)

{

row1.GetCell(1).SetCellValue(apprue);

row1.GetCell(2).SetCellValue(apprue);

row2.GetCell(1).SetCellValue(apprue);

row2.GetCell(2).SetCellValue(apprue);

row3.GetCell(1).SetCellValue(apprue);

row3.GetCell(2).SetCellValue(apprue);

row4.GetCell(1).SetCellValue(apprue);

row4.GetCell(2).SetCellValue(apprue);

}

else

{

row1.GetCell(4).SetCellValue(apprue);

row1.GetCell(5).SetCellValue(apprue);

row2.GetCell(4).SetCellValue(apprue);

row2.GetCell(5).SetCellValue(apprue);

row3.GetCell(4).SetCellValue(apprue);

row3.GetCell(5).SetCellValue(apprue);

row4.GetCell(4).SetCellValue(apprue);

row4.GetCell(5).SetCellValue(apprue);

}

}

else

{

int startcol = 1;// endcol = 2;

if (a == 1)

{

startcol = 4;

//endcol = 4;

}

if (dt.Rows[k]["qz"].ToString().Trim() != "" && dt.Rows[k]["qz"].ToString().Trim() != "NULL")//簽字同意且存在使用者圖檔

{

byte[] bytes;

bytes = (byte[])dt.Rows[k]["qz"];

if (a == 0)

{

int pictureIdx = hssfworkbook.AddPicture(bytes, PictureType.JPEG);

HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, startcol, maxRowNum + 1, 3, maxRowNum + 4);//後四位 第幾列開始第幾行開始，共幾列到第幾行

HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);

}

else

{

int pictureIdx1 = hssfworkbook.AddPicture(bytes, PictureType.JPEG);

HSSFClientAnchor anchor1 = new HSSFClientAnchor(0, 0, 0, 0, 4, maxRowNum + 1, 6, maxRowNum + 4);//後四位 第幾列開始第幾行開始，共幾列到第幾行

HSSFPicture pict1 = (HSSFPicture)patriarch.CreatePicture(anchor1, pictureIdx1);

}

}

else

{

string apprue = dt.Rows[k]["approvername"].ToString() + "同意(" + dt.Rows[k]["appcause"].ToString() + ")";

IRow row1 = sheet.GetRow(maxRowNum + 1);

IRow row2 = sheet.GetRow(maxRowNum + 2);

IRow row3 = sheet.GetRow(maxRowNum + 3);

IRow row4 = sheet.GetRow(maxRowNum + 4);

if (a == 0)

{

row1.GetCell(1).SetCellValue(apprue);

row1.GetCell(2).SetCellValue(apprue);

row2.GetCell(1).SetCellValue(apprue);

row2.GetCell(2).SetCellValue(apprue);

row3.GetCell(1).SetCellValue(apprue);

row3.GetCell(2).SetCellValue(apprue);

row4.GetCell(1).SetCellValue(apprue);

row4.GetCell(2).SetCellValue(apprue);

}

else

{

row1.GetCell(4).SetCellValue(apprue);

row1.GetCell(5).SetCellValue(apprue);

row2.GetCell(4).SetCellValue(apprue);

row2.GetCell(5).SetCellValue(apprue);

row3.GetCell(4).SetCellValue(apprue);

row3.GetCell(5).SetCellValue(apprue);

row4.GetCell(4).SetCellValue(apprue);

row4.GetCell(5).SetCellValue(apprue);

}

}

}

}

if (a == 0)

{

a = 1;

}

else

{

a = 0;

maxRowNum += 4;

}

}

#endregion

sheet.ProtectSheet("1");

}

}

catch (Exception exp)

{

hssfworkbook.Dispose();

file.Close();

file.Dispose();

return "";

}

　　　　//這裡遺憾的是缺少保護工作簿(保護結構和視窗)，實作excel線上審批簽字功能，如果不能進行保護工作簿，導緻使用者可以建立工作表删除原有工作表。

FileStream file2 = new FileStream(newFilePath, FileMode.Create);

hssfworkbook.Write(file2);

file.Close();

file.Dispose();

file2.Close();

file2.Dispose();

hssfworkbook.Dispose();

return newFilePath;

}