07.04.2022 akış

 Nuget paket yönetiminde datayı excele yazdıran veya excelden okuyan bir sürü alternatif yazılım kütüphanesi mevcut ancak bu çözümlerin çoğu paralı ve seri numarası ister. Ücretsiz bir excel kütüphanesi isterseniz size bu kütüphaneyi öneririm. 

Excel Helper

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147

public class ExcelHelper { public static ExcelReadDataDto GetExcelReadData(IFormFile file, string filePath) { ExcelReadDataDto excelRead = new ExcelReadDataDto(); if (!Directory.Exists(filePath)) { Directory.CreateDirectory(filePath); } if (file.Length > 0) { string sFileExtension = Path.GetExtension(file.FileName).ToLower(); ISheet sheet; string fullPath = Path.Combine(filePath, file.FileName); using var stream = new FileStream(fullPath, FileMode.Create); file.CopyTo(stream); stream.Position = 0; if (sFileExtension == ".xls") { HSSFWorkbook hssfwb = new HSSFWorkbook(stream); //This will read the Excel 97-2000 formats sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } else { XSSFWorkbook hssfwb = new XSSFWorkbook(stream); //This will read 2007 Excel format sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook } IRow headerRow = sheet.GetRow(0); //Get Header Row excelRead.HeaderData = new List<string>(); for (int j = 0; j < headerRow.LastCellNum; j++) { ICell cell = headerRow.GetCell(j); if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) continue; excelRead.HeaderData.Add(cell.ToString()); } excelRead.RowData = new List<ExcelRowData>(); for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File { excelRead.RowData.Add(new ExcelRowData()); excelRead.RowData[i - 1].CellData = new List<string>(); IRow row = sheet.GetRow(i); if (row == null) continue; if (row.Cells.All(d => d.CellType == CellType.Blank)) continue; for (int j = row.FirstCellNum; j < headerRow.LastCellNum; j++) { if (row.GetCell(j) != null) excelRead.RowData[i - 1].CellData.Add(row.GetCell(j).ToString()); } } } return excelRead; } public static MemoryStream GetExcelStream(ExcelReadDataDto result) { IWorkbook workbook = new XSSFWorkbook(); ISheet sheet1 = workbook.CreateSheet("Test"); //sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 10)); var rowIndex = 0; IRow row = sheet1.CreateRow(rowIndex); //row.Height = 30 * 80; int excelIndex = 0; foreach (var item in result.HeaderData) { var cell = row.CreateCell(excelIndex); cell.SetCellValue(item); sheet1.AutoSizeColumn(excelIndex); excelIndex++; } //var font = workbook.CreateFont(); //font.IsBold = true; //font.Color = HSSFColor.DarkBlue.Index2; //cell.CellStyle.SetFont(font); rowIndex = 1; foreach (var data in result.RowData) { sheet1.CreateRow(rowIndex); int colIndex = 0; foreach (var item in data.CellData) { sheet1.GetRow(rowIndex).CreateCell(colIndex); var currentRow = sheet1.GetRow(rowIndex); currentRow.Cells[colIndex].SetCellValue(item); colIndex++; } rowIndex++; } //rowIndex++; //// bir elektronik tablo daha ekleyin //var sheet2 = workbook.CreateSheet("My Sheet"); //// Hücre stilleri oluşturun. //var style1 = workbook.CreateCellStyle(); //style1.FillForegroundColor = HSSFColor.Blue.Index2; //style1.FillPattern = FillPattern.SolidForeground; //var style2 = workbook.CreateCellStyle(); //style2.FillForegroundColor = HSSFColor.Yellow.Index2; //style2.FillPattern = FillPattern.SolidForeground; //// Hücre stilini ve verilerini ayarlayın. //var cell2 = sheet2.CreateRow(0).CreateCell(0); //cell2.CellStyle = style1; //cell2.SetCellValue(0); //cell2 = sheet2.CreateRow(1).CreateCell(0); //cell2.CellStyle = style2; //cell2.SetCellValue(1); //cell2 = sheet2.CreateRow(2).CreateCell(0); //cell2.CellStyle = style1; //cell2.SetCellValue(2); //cell2 = sheet2.CreateRow(3).CreateCell(0); //cell2.CellStyle = style2; //cell2.SetCellValue(3); //cell2 = sheet2.CreateRow(4).CreateCell(0); //cell2.CellStyle = style1; //cell2.SetCellValue(4); using (var memoryStream = new MemoryStream()) { workbook.Write(memoryStream); return memoryStream; } } }

Alternatif kütüphane ExcelPackage nuget üzerinde aratabilirsiniz Onunda kullanımı aşağıdaki gibidir. Ücretsiz kullanmak için önce Lisans tipi ücretsiz olarak ayarlanır

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48

// As an example lets assume we've generated an EPPlus ExcelPackage ExcelPackage.LicenseContext = LicenseContext.NonCommercial; ExcelPackage workbook = new ExcelPackage(); // Do something to populate your workbook ExcelWorksheet worksheet = workbook.Workbook.Worksheets.Add("Leads"); int excelIndex = 1; foreach (var item in result.HeaderData) { worksheet.Cells[1, excelIndex].Value = item; excelIndex++; } if(result.RowData.Count > 0) { int rowIndex = 2; foreach (var data in result.RowData) { int colIndex = 1; foreach (var item in data.CellData) { worksheet.Cells[rowIndex, colIndex].Value = item; colIndex++; } rowIndex++; } } string handle = Guid.NewGuid().ToString(); using (MemoryStream memoryStream = new MemoryStream()) { workbook.SaveAs(memoryStream); memoryStream.Position = 0; TempData[handle] = memoryStream.ToArray(); } //Excel helper kullanarakta veriyi alabiliriz //TempData[handle] = ExcelHelper.GetExcelStream(result).ToArray(); var model = new TempDataModel(); model.Data = new TempDataItemModel() { FileGuid = handle, FileName = "Test.xlsx" }; return Json(model);

Gönderilen dosya memory'de durur ve indirme linki döndürülerek aşağıdaki actiona çağrıda bulunulur

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

[HttpGet] public IActionResult Download(string fileGuid, string fileName) { if (TempData[fileGuid] != null) { byte[] data = TempData[fileGuid] as byte[]; return File(data, "application/vnd.ms-excel", fileName); //return File(data , "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName); } else { return new EmptyResult(); } }