C#实现excel 文件导出

最新推荐文章于 2024-02-19 23:04:21 发布

code狗

最新推荐文章于 2024-02-19 23:04:21 发布

阅读量1.8k

点赞数

文章标签： c# excel 开发语言

本文链接：https://blog.csdn.net/jjy17337774210/article/details/128557585

版权

实现方式：可以采用模板文件导出也可以采用代码生成文件，将文件转化为字节流，然后用File（）方法输出即可。

Controller

public ActionResult DownloadRiskTipExcel(string nodeId, int level, string riskSourceName, string riskStatusValue)
		{

			var data = riskTipsService.DownloadRiskTipExcel(nodeId, level, riskSourceName, riskStatusValue);
			var name = $"风险提示-导出-" + DateTime.Now.ToString("yyyyMMddhhmm") + ".xlsx";
			return File(data, "application/octet-stream", name);
		}

IService

	byte[] DownloadRiskTipExcel(string nodeId, int level, string riskSourceName, string riskStatusValue);

Service

public byte[] DownloadRiskTipExcel(string nodeId, int level, string riskSourceName, string riskStatusValue)
		{
			var dataList = GetRiskPointPageList(nodeId, level, riskSourceName, riskStatusValue);
			var host = GetService<IHostingEnvironment>();

			XSSFWorkbook workbook;
			string templateFilePath = templateFilePath = Path.GetFullPath(host.WebRootPath + "/Template/RiskTipTemplate.xlsx");

			byte[] datas;
	
			using (var fs = System.IO.File.OpenRead(templateFilePath))
			{

				workbook = new XSSFWorkbook(fs);
				int sheetIndex = 0;

				ICellStyle cellStyle = workbook.CreateCellStyle();
				cellStyle.WrapText = true;
				cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;//粗线
				cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;//粗线
				cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;//粗线
				cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;//粗线
				cellStyle.Alignment = HorizontalAlignment.Center;
				cellStyle.VerticalAlignment = VerticalAlignment.Center;

				ICellStyle borderCellStyle = workbook.CreateCellStyle();
				borderCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;//粗线
				borderCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;//粗线
				borderCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;//粗线
				borderCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;//粗线

				IFont font = workbook.CreateFont();
				font.Boldweight = (short)FontBoldWeight.Bold;
				borderCellStyle.SetFont(font);
				borderCellStyle.Alignment = HorizontalAlignment.Center;
				borderCellStyle.VerticalAlignment = VerticalAlignment.Center;

				XSSFSheet sheet = workbook.GetSheetAt(0) as XSSFSheet;
				int rowIndex =1;
				foreach (var item in dataList)
				{
					IRow dataRow = sheet.GetRow(rowIndex);
					if (dataRow == null)
					{
						dataRow = sheet.CreateRow(rowIndex);
					}
					cellStyle.WrapText = false;
					SetCellValue(workbook, dataRow, cellStyle, 0, item.SegName);
					SetCellValue(workbook, dataRow, cellStyle, 1, item.SiteName);
					SetCellValue(workbook, dataRow, cellStyle, 2, item.RiskPointName);
					SetCellValue(workbook, dataRow, cellStyle, 3, item.RiskDesignLevelDesc);
					SetCellValue(workbook, dataRow, cellStyle, 4, item.ConstructionLevelDesc);
					SetCellValue(workbook, dataRow, cellStyle, 5, item.RiskTypeName);
					SetCellValue(workbook, dataRow, cellStyle, 6, item.RiskName);
					SetCellValue(workbook, dataRow, cellStyle, 7, item.RiskLevelDesc);
					SetCellValue(workbook, dataRow, cellStyle, 8, item.ProcessingStatusDesc);
					rowIndex++;
				}


				workbook.SetActiveSheet(0);
			}
			using (var ms = new MemoryStream())
			{
				workbook.Write(ms);
				datas = ms.ToArray();
			}
			return datas;
		}

	/// <summary>
		/// 单元格赋值
		/// </summary>
		private void SetCellValue(XSSFWorkbook workbook, IRow row, ICellStyle cellStyle, int columIndex, string value)
		{
			var cell = row.CreateCell(columIndex);

			cell.CellStyle = cellStyle;

			cell.SetCellValue(value);
		}