如何在NPOI中设置数值类型的空单元格-不崩溃
单元格未设置值,数值类型未复制,造成excel打不开,wps需要收费才能恢复
关键语句 cell.SetCellType(CellType.Numeric);
public string Export(List<ChallengeHis> inputs)
{
var workbook = new XSSFWorkbook();
//创建表
var table = workbook.CreateSheet($"{DateTime.Now.ToString("yyyy-MM-dd")}");
int i = 0;
int j = 0;
var row = table.CreateRow(i++);
var cell = row.CreateCell(j++);
cell.SetCellValue("学号");
cell = row.CreateCell(j++);
cell.SetCellValue("姓名");
cell = row.CreateCell(j++);
cell.SetCellType(CellType.Numeric);
cell.SetCellValue("最高纪录");
cell = row.CreateCell(j++);
cell.SetCellType(CellType.Numeric);
cell.SetCellValue("轮次");
cell = row.CreateCell(j++);
cell.SetCellValue("开始时间");
cell = row.CreateCell(j++);
cell.SetCellValue("结束时间");
cell = row.CreateCell(j++);
cell.SetCellValue("挑战时长");
cell = row.CreateCell(j++);
cell.SetCellValue("休息时长");
cell = row.CreateCell(j++);
cell.SetCellType(CellType.Numeric);
cell.SetCellValue("积分");
cell = row.CreateCell(j++);
cell.SetCellValue("结果");
cell = row.CreateCell(j++);
cell.SetCellValue("平破");
cell = row.CreateCell(j++);
cell.SetCellType(CellType.Numeric);
cell.SetCellValue("军功章");
cell = row.CreateCell(j++);
cell.SetCellType(CellType.Numeric);
cell.SetCellValue("分享积分");
cell = row.CreateCell(j++);
cell.SetCellType(CellType.Numeric);
cell.SetCellValue("小组积分");
cell.SetCellType(CellType.Numeric);
cell = row.CreateCell(j++);
cell.SetCellType(CellType.Numeric);
cell.SetCellValue("助理教官");
int n = 1;
string currentStudentNo = "";
foreach (var item in inputs.OrderBy(p => p.StudentNo).ThenBy(p => p.StartTime))
{
row = table.CreateRow(i++);
j = 0;
if (currentStudentNo != item.StudentNo)
{
currentStudentNo = item.StudentNo;
n = 1;
}
cell = row.CreateCell(j++);
cell.SetCellValue(item.StudentNo);
cell = row.CreateCell(j++);
cell.SetCellValue(item.StudentName);
cell = row.CreateCell(j++);
cell.SetCellValue(0);
cell = row.CreateCell(j++);
cell.SetCellValue(n++);
cell = row.CreateCell(j++);
cell.SetCellValue(item.StartTime.ToString("HH:mm"));
cell = row.CreateCell(j++);
cell.SetCellValue(item.EndTime.ToString("HH:mm"));
cell = row.CreateCell(j++);
cell.SetCellValue(item.Duration);
cell = row.CreateCell(j++);
cell.SetCellValue(item.RestDuration);
cell = row.CreateCell(j++);
cell.SetCellValue(item.Points);
cell = row.CreateCell(j++);
cell.SetCellValue(item.Result);
cell = row.CreateCell(j++);
cell.SetCellValue(item.Surpasses);
//cell = row.CreateCell(j++);
//cell.SetCellValue("军功章");
//cell = row.CreateCell(j++);
//cell.SetCellValue("分享积分");
//cell = row.CreateCell(j++);
//cell.SetCellValue("小组积分");
//cell = row.CreateCell(j++);
//cell.SetCellValue("助理教官");
//只在第一行汇总
if (n == 2)
{
cell = row.CreateCell(j++);
cell.SetCellValue(AccompanyManager.SumByStudentIdAndTime(item.StudentId, item.StartTime));
cell = row.CreateCell(j++);
cell.SetCellValue(DirectAwardManager.SumPointsByStudentIdAndTime(item.StudentId, item.StartTime, SysConsts.ChallengeTypeSharedName));
cell = row.CreateCell(j++);
cell.SetCellValue(DirectAwardManager.SumPointsByStudentIdAndTime(item.StudentId, item.StartTime, SysConsts.ChallengeTypeGroupName));
cell = row.CreateCell(j++);
cell.SetCellValue(DirectAwardManager.SumPointsByStudentIdAndTime(item.StudentId, item.StartTime, SysConsts.ChallengeTypeassistantName));
}
else
{
cell = row.CreateCell(j++);
//cell.SetCellValue(0);
cell = row.CreateCell(j++);
//cell.SetCellValue(0);
cell = row.CreateCell(j++);
//cell.SetCellValue(0);
cell = row.CreateCell(j++);
//cell.SetCellValue(0);
}
}
var fileName = $"{SysConsts.TempDirName}\\挑战记录{DateTime.Now.ToString("yyyy-MM-dd")}.xlsx";
//打开xls文件,如没有则创建,如存在则在创建是不要打开该文件
using (var fs = File.OpenWrite(fileName))
{
workbook.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。
}
return fileName;
}