c# - Excel 下载后无法读取数据
问题描述
我正在尝试下载我为其提供正确路径的 excel 文件。但是在我尝试打开时下载它后,我收到错误消息
excel 无法打开文件,因为文件格式或文件扩展名无效。确认文件已损坏...
在这个分开的类中,我创建了导出函数来创建数据并将其写入 excel 文件
public static ExcelPackage ExportExcel(string fileName, decimal projectId, ModelContext context)
{
ExcelPackage pck = new ExcelPackage();
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Project");
var surveys = context.Survey.Where(x => x.ProjectId == projectId)
.Include(s=> s.Question);
#region datatable
ws.Cells["A1"].Value = "SURVEY_ID";
ws.Cells["B1"].Value = "PAGE";
ws.Cells["C1"].Value = "RANKING";
ws.Cells["D1"].Value = "PARENT_ID";
ws.Cells["E1"].Value = "ID";
ws.Cells["F1"].Value = "ITEMTYPE";
ws.Cells["G1"].Value = "CONTROLTYPE";
ws.Cells["H1"].Value = "DISPLAYTYPE";
ws.Cells["J1"].Value = "VARIABLE_DESCRIPTION";
ws.Cells["K1"].Value = "VARIABLE_NAME";
ws.Cells["L1"].Value = "VARIABLE_LABEL";
ws.Cells["M1"].Value = "VALUE_LABEL_ID";
ws.Cells["N1"].Value = "MISSING_VALUE_ID";
ws.Cells["O1"].Value = "IS_REQUIRED";
ws.Cells["P1"].Value = "DATA_TYPE";
ws.Cells["Q1"].Value = "VARIABLE_LENGTH";
ws.Cells["R1"].Value = "NR_OF_DECIMALS";
ws.Cells["S1"].Value = "COMMENT";
ws.Cells["T1"].Value = "COMMENT_TYPE";
ws.Cells["U1"].Value = "SHOW_ALL_FOR_STUDY";
ws.Cells["V1"].Value = "ANSWER_FONT";
ws.Cells["W1"].Value = "IS_DELETE_QUESTION";
ws.Cells["X1"].Value = "IS_STOP_QUESTION";
ws.Cells["Y1"].Value = "ROUTING_TYPE";
ws.Cells["Z1"].Value = "ROUTING_VALUE";
ws.Cells["AA1"].Value = "SHOW";
int rowStart = 2;
foreach (var survey in surveys)
{
foreach (var item in survey.Question)
{
ws.Cells[String.Format("A{0}", rowStart)].Value = survey.IdSurvey;
ws.Cells[String.Format("B{0}", rowStart)].Value = item.Page;
ws.Cells[String.Format("C{0}", rowStart)].Value = item.Ranking;
ws.Cells[String.Format("D{0}", rowStart)].Value = item.ParentId;
ws.Cells[String.Format("E{0}", rowStart)].Value = item.IdQuestion;
ws.Cells[String.Format("F{0}", rowStart)].Value = item.Itemtype;
ws.Cells[String.Format("G{0}", rowStart)].Value = item.Controltype;
ws.Cells[String.Format("H{0}", rowStart)].Value = item.Displaytype;
ws.Cells[String.Format("J{0}", rowStart)].Value = item.VariableDescription;
ws.Cells[String.Format("K{0}", rowStart)].Value = item.VariableName;
ws.Cells[String.Format("L{0}", rowStart)].Value = item.VariableLabel;
ws.Cells[String.Format("M{0}", rowStart)].Value = item.ValueLabelId;
ws.Cells[String.Format("N{0}", rowStart)].Value = item.MissingValueId;
ws.Cells[String.Format("O{0}", rowStart)].Value = item.IsRequired;
ws.Cells[String.Format("P{0}", rowStart)].Value = item.DataType;
ws.Cells[String.Format("Q{0}", rowStart)].Value = item.VariableLength;
ws.Cells[String.Format("R{0}", rowStart)].Value = item.NrOfDecimals;
ws.Cells[String.Format("S{0}", rowStart)].Value = item.Comment;
ws.Cells[String.Format("T{0}", rowStart)].Value = item.CommentType;
ws.Cells[String.Format("U{0}", rowStart)].Value = item.ShowAllForStudy;
ws.Cells[String.Format("V{0}", rowStart)].Value = item.AnswerFont;
ws.Cells[String.Format("W{0}", rowStart)].Value = item.IsDeleteQuestion;
ws.Cells[String.Format("X{0}", rowStart)].Value = item.IsStopQuestion;
ws.Cells[String.Format("Y{0}", rowStart)].Value = item.RoutingType;
ws.Cells[String.Format("Z{0}", rowStart)].Value = item.RoutingValue;
ws.Cells[String.Format("AA{0}", rowStart)].Value = item.Show;
rowStart++;
}
ws.Cells["A:AZ"].AutoFitColumns();
}
#endregion
return pck;
}
}
然后我从控制器调用这个函数:
[HttpGet("{Id}")]
public IActionResult Export(IFormCollection form, decimal? id)
{
var exportId = _context.Project.First(x => x.ProjectId == id).ProjectId;
var fileName = form["Codebook.xlsx"];
//var pck = Models.Export.ExportExcel(fileName, exportId, _context);
return File(Models.Export.ExportExcel(fileName, exportId, _context).Stream
, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Codebook.xlsx");
解决方案
该文件应首先保存在 webroot 中,然后将其作为字节数组返回,以便在控制器添加 IwebHostEnviroment 然后更新操作方法
public IActionResult Export(IFormCollection form, decimal? id)
{
var project = _context.Project.First(x => x.ProjectId == id).ProjectId;
var fileName = form["Codeboek"];
var pck = Models.Export.ExportExcel(fileName, project, _context);
var contentRootPath = _env.ContentRootPath;
fileName = "exportFile.xlsx";
var filePath = contentRootPath + "/" + fileName;
pck.SaveAs(new FileInfo(filePath));
byte[] fileBytes = System.IO.File.ReadAllBytes(filePath);
return File(fileBytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
}
推荐阅读
- php - 如何在 MySQL 的单行中获取以逗号分隔格式存储的图像?
- java - Postgres 中的生成值
- wpf - 如果我使用 DataTemplate 将 VM 绑定到视图,WPF combobox.SelectedItem 不会更新
- ajax - Ajax POST 不适用于 spring security _csrf 但 GET 方法有效
- encryption - 在soap ui中没有进行解密
- c# - C#“静态抽象”方法替代实例化子类
- javascript - 使用 await async 获取数据而不使用 try catch
- python - 如何使用 python 客户端获取弹性搜索索引下的所有文档?
- c++ - 将 ISO 日期时间转换为本地日期时间并在 C++ 中提取时间
- java - 等待页面加载 HtmlUnitDriver