NPOI操作excel(通过获取批注信息给excel动态赋值)
private string fileName = null; //文件名
private IWorkbook workbook = null;
private FileStream fs = null;
private IFormulaEvaluator evaluator = null;
if (fileName == "") return "";
int CurrentRow = 0;
ISheet sheet = null;
//把文件内容导入到工作薄当中，然后关闭文件
fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
if (fileName.IndexOf(".xlsx", StringComparison.Ordinal) > 0)
{ // 2007版本
workbook = new XSSFWorkbook(fs);
}
else if (fileName.IndexOf(".xls", StringComparison.Ordinal) > 0) // 2003版本
{
workbook = new HSSFWorkbook(fs);
}
fs.Close();//关闭文件
if (sheetName != null)
{
sheet = workbook.GetSheet(sheetName) ?? workbook.GetSheetAt(0);
}
else
{
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
//获取建设期
var cp =2
var rowCount = sheet.LastRowNum; //竖着数最后一行的标号
for (var i = sheet.FirstRowNum; i <= rowCount; i++)
{
#region 获取当前行的启起值和结束值(模块)
var row = sheet.GetRow(i);//获取当前行的启起值和结束值
if (row == null) continue;
#region 获取一行的启起值和一行的结束值作为循环条件(模块)
//获取一行的启起值和一行的结束值作为循环条件//int cellCount = row.LastCellNum; //一行最后一个cell的编号 即总的列数
for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
{
#region 判断当前单元格数据不等空(模块)
if (row.GetCell(j) == null || row.GetCell(j).CellComment == null) continue;
var Par = new List<SqlParameter>(); //sql参数化
var sd = row.GetCell(j).ToString();
#region 判断当前单元格批注不等空(模块)
var annotation = ((row.GetCell(j).CellComment.String.ToString().Replace("\n", "")).Replace("\r", "")).Trim();//获取当前单元格的批注
#region 获取excel起启行结束行
//如果相等记录当前行
if (annotation.Equals("open", StringComparison.OrdinalIgnoreCase))
{
CurrentRow = i; //记录当前excel行
}
else
{
//Convert.ToInt32(cp.Constant); //建设期
//解析单元格批注内容
string[] dataArr = annotation.Split('_');
string symbol = "";
if (dataArr.Length < 1) continue;
Par.Add(new SqlParameter("@SID", R_ID));
var arr = dataArr[1].Split('|');//获取值判断是否排除建设期
if (arr.Length > 1)
{
Par.Add(new SqlParameter("@gid", arr[0]));
}
else
{
var ffda = dataArr[1].Split('~');//判断是否有符号
if (ffda.Length > 1)
{
symbol = ffda[1];
Par.Add(new SqlParameter("@gid", ffda[0]));
}
else
{
Par.Add(new SqlParameter("@gid", dataArr[1]));
}
}
#region 执行存储过程过程获取数据
//执行存储过程
var dataTable = DbHelperSQL.RunStoredProcedure(dataArr[0].ToString(), Par.ToArray());//
if (dataTable.Rows.Count > 0)
{
var data = CommFunHelper.GetDataTableByKeyValue(dataTable);
if (dataArr[0].ToString().Equals("InternalFunds", StringComparison.OrdinalIgnoreCase) || dataArr[0].ToString().Equals("ProjectInternalRemuneration", StringComparison.OrdinalIgnoreCase))
{
#region keyValues取值方式
var value = "";
for (var k = 1; k <= sheet.GetRow(CurrentRow).LastCellNum; k++)
{
var va = sheet.GetRow(CurrentRow).GetCell(j + k);
if (va == null) continue;
var rowArr = va.ToString().Split('-');
if (rowArr.Length > 1)
{
if (data.ContainsKey(rowArr[1]))
{
value = data[rowArr[1]] + symbol;
}
else
{
value = null;
}
}
else
{
if (data.ContainsKey(va.ToString()))
{
value = data[va.ToString()] + symbol;
}
else
{
value = null;
}
}
if (arr.Length <= 1)
{
if (k <= Convert.ToInt32(cp.Constant))
{
value = "";
}
}
if (value == null) continue;
//先获取单元格的值再设置单元格的边框线再赋值不然.......自己看着办
row.CreateCell(j + k).CellStyle.BorderLeft =
BorderStyle.Thin; //设置左边框线
row.CreateCell(j + k).CellStyle.BorderBottom =
BorderStyle.Thin; //设置下边框线
row.CreateCell(j + k).CellStyle.BorderTop =
BorderStyle.Thin; //设置上边框线
row.CreateCell(j + k).CellStyle.BorderRight =
BorderStyle.Thin; //设置右边框线
row.CreateCell(j + k).SetCellValue(value);
//row.CreateCell(j + k).SetCellFormula("sum(A1,B1)");使用计算公式
}
#endregion
}
else
{
var value = dataTable.Rows[0][0] == DBNull.Value ? "" : dataTable.Rows[0][0].ToString();
//先获取单元格的值再设置单元格的边框线再赋值不然.......自己看着办
row.CreateCell(j + 1).CellStyle.BorderBottom =
BorderStyle.Thin; //设置下边框线
row.CreateCell(j + 1).CellStyle.BorderRight =
BorderStyle.Thin; //设置下边框线
row.CreateCell(j + 1).SetCellValue(value + symbol);
}
}
#endregion
}
#endregion
row.GetCell(j).RemoveCellComment();//删除当前单元格批注信息
#endregion
#endregion
}
#endregion
#endregion
}
#region 保存文件
string filePath = ConfigurationSettings.AppSettings["ExSave"];
string FileName = R_ID + "_" + az + "_" + ".xlsx";
var strFullName = Server.MapPath(filePath) + "\\" + FileName;
using (FileStream fileStream = System.IO.File.Create(strFullName))
{
workbook.Write(fileStream);
fileStream.Close();
}
//XlsToImg(strFullName, Path.ChangeExtension(strFullName, ".png"));
#endregion
return filePath + "/" + Path.ChangeExtension(FileName, ".png");
}
