[ValidateAntiForgeryToken]
public IActionResult ExportToExcel()
string uploadBatchID = StoreTempValue.UploadBatchIDAPI;
if (string.IsNullOrEmpty(uploadBatchID)) return null;
DataTable dt = new DataTable("MappingResults");
using (SqlConnection con = new SqlConnection(ShareConnectionString.Value))
SqlCommand cmd = new SqlCommand("[dbo].[TPS_sp_GetMappingResults]", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@uploadbatch_id", uploadBatchID);
SqlDataAdapter da = new SqlDataAdapter(cmd);
string tmpPath = Path.GetTempPath();
string fileName = "MappingResults_" + DateTime.Now.ToString("ddMMyyyyHHmmss") + ".xlsx";
tmpPath = Path.Combine(tmpPath, fileName);
using (SpreadsheetDocument document = SpreadsheetDocument.Create(tmpPath, SpreadsheetDocumentType.Workbook))
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet();
SheetData sheetData = new SheetData();
WorkbookStylesPart stylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
stylesPart.Stylesheet = GenerateStyleSheet();
stylesPart.Stylesheet.Save();
Columns columns = new Columns();
columns.Append(CreateColumnData(1, (UInt32)dt.Columns.Count, 25));
worksheet.Append(columns);
Row headerRow = new Row();
List<string> columnNames = new List<string>();
foreach (DataColumn column in dt.Columns)
columnNames.Add(column.ColumnName);
Cell cell = new Cell() { DataType = CellValues.String, CellValue = new CellValue(column.ColumnName), StyleIndex = 1 }; // Header cell style
sheetData.Append(headerRow);
foreach (DataRow row in dt.Rows)
foreach (string columnName in columnNames)
Cell cell = new Cell() { DataType = CellValues.String, CellValue = new CellValue(row[columnName].ToString()), StyleIndex = 0 };
sheetData.Append(newRow);
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
Sheets sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets());
sheets.Append(new Sheet()
Id = document.WorkbookPart.GetIdOfPart(worksheetPart),
workbookPart.Workbook.Save();
byte[] fileBytes = System.IO.File.ReadAllBytes(tmpPath);
return File(fileBytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
return null; // Handle empty data case appropriately