Syncfusion excel asp net core

Pustaka Syncfusion Excel untuk ASP. Platform .NET Core dapat digunakan untuk membuat, membaca, mengedit file Excel. Ini juga mengonversi file Excel ke PDF

Buat laporan Excel sederhana

1. Instal Syncfusion. XlsIO. Bersih. Paket Core NuGet sebagai referensi untuk Anda. Aplikasi .NET Standard dari NuGet. org

Pengontrol default dengan nama HomeController. cs ditambahkan pada pembuatan ASP. proyek inti .NET. Sertakan ruang nama berikut di HomeController. file cs

using Syncfusion.XlsIO;
using System.IO;
using Syncfusion.Drawing;

1. Metode tindakan default bernama Index akan hadir di HomeController. cs. Klik kanan pada metode Indeks dan pilih Go To View di mana Anda akan diarahkan ke halaman tampilan terkait Indeks. cshtml

2. Tambahkan tombol baru di Indeks. cshtml seperti gambar di bawah ini

3. Tambahkan metode tindakan baru CreateDocument di HomeController. cs dan sertakan potongan kode di bawah ini untuk membuat file Excel dan mengunduhnya

//Create an instance of ExcelEngine
using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
   
	application.DefaultVersion = ExcelVersion.Xlsx;
   
	//Create a workbook
	IWorkbook workbook = application.Workbooks.Create(1);
	IWorksheet worksheet = workbook.Worksheets[0];
   
	//Adding a picture
	FileStream imageStream = new FileStream("AdventureCycles-Logo.png", FileMode.Open, FileAccess.Read);
	IPictureShape shape = worksheet.Pictures.AddPicture(1, 1, imageStream, 20, 20);
   
	//Disable gridlines in the worksheet
	worksheet.IsGridLinesVisible = false;
   
	//Enter values to the cells from A3 to A5
	worksheet.Range["A3"].Text = "46036 Michigan Ave";
	worksheet.Range["A4"].Text = "Canton, USA";
	worksheet.Range["A5"].Text = "Phone: +1 231-231-2310";
   
	//Make the text bold
	worksheet.Range["A3:A5"].CellStyle.Font.Bold = true;
   
	//Merge cells
	worksheet.Range["D1:E1"].Merge();
   
	//Enter text to the cell D1 and apply formatting.
	worksheet.Range["D1"].Text = "INVOICE";
	worksheet.Range["D1"].CellStyle.Font.Bold = true;
	worksheet.Range["D1"].CellStyle.Font.RGBColor = Color.FromArgb(42, 118, 189);
	worksheet.Range["D1"].CellStyle.Font.Size = 35;
   
	//Apply alignment in the cell D1
	worksheet.Range["D1"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
	worksheet.Range["D1"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignTop;
   
	//Enter values to the cells from D5 to E8
	worksheet.Range["D5"].Text = "INVOICE#";
	worksheet.Range["E5"].Text = "DATE";
	worksheet.Range["D6"].Number = 1028;
	worksheet.Range["E6"].Value = "12/31/2018";
	worksheet.Range["D7"].Text = "CUSTOMER ID";
	worksheet.Range["E7"].Text = "TERMS";
	worksheet.Range["D8"].Number = 564;
	worksheet.Range["E8"].Text = "Due Upon Receipt";
   
	//Apply RGB backcolor to the cells from D5 to E8
	worksheet.Range["D5:E5"].CellStyle.Color = Color.FromArgb(42, 118, 189);
	worksheet.Range["D7:E7"].CellStyle.Color = Color.FromArgb(42, 118, 189);
   
	//Apply known colors to the text in cells D5 to E8
	worksheet.Range["D5:E5"].CellStyle.Font.Color = ExcelKnownColors.White;
	worksheet.Range["D7:E7"].CellStyle.Font.Color = ExcelKnownColors.White;
   
	//Make the text as bold from D5 to E8
	worksheet.Range["D5:E8"].CellStyle.Font.Bold = true;
   
	//Apply alignment to the cells from D5 to E8
	worksheet.Range["D5:E8"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
	worksheet.Range["D5:E5"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
	worksheet.Range["D7:E7"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
	worksheet.Range["D6:E6"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignTop;
   
	//Enter value and applying formatting in the cell A7
	worksheet.Range["A7"].Text = "  BILL TO";
	worksheet.Range["A7"].CellStyle.Color = Color.FromArgb(42, 118, 189);
	worksheet.Range["A7"].CellStyle.Font.Bold = true;
	worksheet.Range["A7"].CellStyle.Font.Color = ExcelKnownColors.White;
   
	//Apply alignment
	worksheet.Range["A7"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft;
	worksheet.Range["A7"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
   
	//Enter values in the cells A8 to A12
	worksheet.Range["A8"].Text = "Steyn";
	worksheet.Range["A9"].Text = "Great Lakes Food Market";
	worksheet.Range["A10"].Text = "20 Whitehall Rd";
	worksheet.Range["A11"].Text = "North Muskegon,USA";
	worksheet.Range["A12"].Text = "+1 231-654-0000";
   
	//Create a Hyperlink for e-mail in the cell A13
	IHyperLink hyperlink = worksheet.HyperLinks.Add(worksheet.Range["A13"]);
	hyperlink.Type = ExcelHyperLinkType.Url;
	hyperlink.Address = "[email protected]";
	hyperlink.ScreenTip = "Send Mail";
   
	//Merge column A and B from row 15 to 22
	worksheet.Range["A15:B15"].Merge();
	worksheet.Range["A16:B16"].Merge();
	worksheet.Range["A17:B17"].Merge();
	worksheet.Range["A18:B18"].Merge();
	worksheet.Range["A19:B19"].Merge();
	worksheet.Range["A20:B20"].Merge();
	worksheet.Range["A21:B21"].Merge();
	worksheet.Range["A22:B22"].Merge();
   
	//Enter details of products and prices
	worksheet.Range["A15"].Text = "  DESCRIPTION";
	worksheet.Range["C15"].Text = "QTY";
	worksheet.Range["D15"].Text = "UNIT PRICE";
	worksheet.Range["E15"].Text = "AMOUNT";
	worksheet.Range["A16"].Text = "Cabrales Cheese";
	worksheet.Range["A17"].Text = "Chocos";
	worksheet.Range["A18"].Text = "Pasta";
	worksheet.Range["A19"].Text = "Cereals";
	worksheet.Range["A20"].Text = "Ice Cream";
	worksheet.Range["C16"].Number = 3;
	worksheet.Range["C17"].Number = 2;
	worksheet.Range["C18"].Number = 1;
	worksheet.Range["C19"].Number = 4;
	worksheet.Range["C20"].Number = 3;
	worksheet.Range["D16"].Number = 21;
	worksheet.Range["D17"].Number = 54;
	worksheet.Range["D18"].Number = 10;
	worksheet.Range["D19"].Number = 20;
	worksheet.Range["D20"].Number = 30;
	worksheet.Range["D23"].Text = "Total";
   
	//Apply number format
	worksheet.Range["D16:E22"].NumberFormat = "$.00";
	worksheet.Range["E23"].NumberFormat = "$.00";
   
	//Apply incremental formula for column Amount by multiplying Qty and UnitPrice
	application.EnableIncrementalFormula = true;
	worksheet.Range["E16:E20"].Formula = "=C16*D16";
   
	//Formula for Sum the total
	worksheet.Range["E23"].Formula = "=SUM(E16:E22)";
   
	//Apply borders
	worksheet.Range["A16:E22"].CellStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
	worksheet.Range["A16:E22"].CellStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
	worksheet.Range["A16:E22"].CellStyle.Borders[ExcelBordersIndex.EdgeTop].Color = ExcelKnownColors.Grey_25_percent;
	worksheet.Range["A16:E22"].CellStyle.Borders[ExcelBordersIndex.EdgeBottom].Color = ExcelKnownColors.Grey_25_percent;
	worksheet.Range["A23:E23"].CellStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
	worksheet.Range["A23:E23"].CellStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
	worksheet.Range["A23:E23"].CellStyle.Borders[ExcelBordersIndex.EdgeTop].Color = ExcelKnownColors.Black;
	worksheet.Range["A23:E23"].CellStyle.Borders[ExcelBordersIndex.EdgeBottom].Color = ExcelKnownColors.Black;
   
	//Apply font setting for cells with product details
	worksheet.Range["A3:E23"].CellStyle.Font.FontName = "Arial";
	worksheet.Range["A3:E23"].CellStyle.Font.Size = 10;
	worksheet.Range["A15:E15"].CellStyle.Font.Color = ExcelKnownColors.White;
	worksheet.Range["A15:E15"].CellStyle.Font.Bold = true;
	worksheet.Range["D23:E23"].CellStyle.Font.Bold = true;
   
	//Apply cell color
	worksheet.Range["A15:E15"].CellStyle.Color = Color.FromArgb(42, 118, 189);
   
	//Apply alignment to cells with product details
	worksheet.Range["A15"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft;
	worksheet.Range["C15:C22"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
	worksheet.Range["D15:E15"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
   
	//Apply row height and column width to look good
	worksheet.Range["A1"].ColumnWidth = 36;
	worksheet.Range["B1"].ColumnWidth = 11;
	worksheet.Range["C1"].ColumnWidth = 8;
	worksheet.Range["D1:E1"].ColumnWidth = 18;
	worksheet.Range["A1"].RowHeight = 47;
	worksheet.Range["A2"].RowHeight = 15;
	worksheet.Range["A3:A4"].RowHeight = 15;
	worksheet.Range["A5"].RowHeight = 18;
	worksheet.Range["A6"].RowHeight = 29;
	worksheet.Range["A7"].RowHeight = 18;
	worksheet.Range["A8"].RowHeight = 15;
	worksheet.Range["A9:A14"].RowHeight = 15;
	worksheet.Range["A15:A23"].RowHeight = 18;
   	
   	
	//Saving the Excel to the MemoryStream 
	MemoryStream stream = new MemoryStream();
   
	workbook.SaveAs(stream);
   
	//Set the position as '0'.
	stream.Position = 0;
   
	//Download the Excel file in the browser
	FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel");
   
	fileStreamResult.FileDownloadName = "Output.xlsx";
   
	return fileStreamResult;
}

Contoh kerja lengkap tentang cara membuat file Excel di ASP. NET Core di C# hadir di halaman GitHub ini

Dengan menjalankan program, Anda akan mendapatkan file Excel seperti di bawah ini

Baca dan Edit file Excel

Cuplikan kode di bawah ini mengilustrasikan cara membaca dan mengedit file Excel di ASP. Inti BERSIH

//New instance of ExcelEngine is created 
//Equivalent to launching Microsoft Excel with no workbooks open
//Instantiate the spreadsheet creation engine
ExcelEngine excelEngine = new ExcelEngine();

//Instantiate the Excel application object
IApplication application = excelEngine.Excel;

//Assigns default application version
application.DefaultVersion = ExcelVersion.Xlsx;

//A existing workbook is opened.             
FileStream sampleFile = new FileStream("Sample.xlsx", FileMode.Open);
IWorkbook workbook = application.Workbooks.Open(sampleFile);

//Access first worksheet from the workbook.
IWorksheet worksheet = workbook.Worksheets[0];

//Set Text in cell A3.
worksheet.Range["A3"].Text ="Hello World";

//Access a cell value from Excel
var value = worksheet.Range["A1"].Value;

//Defining the ContentType for excel file.
string ContentType = "Application/msexcel";

//Define the file name.
string fileName = "Output.xlsx";

//Creating stream object.
MemoryStream stream = new MemoryStream();

//Saving the workbook to stream in XLSX format
workbook.SaveAs(stream);

stream.Position = 0;

//Closing the workbook.

workbook.Close();

//Dispose the Excel engine
excelEngine.Dispose();

//Creates a FileContentResult object by using the file contents, content type, and file name.
return File(stream, ContentType, fileName);

Contoh kerja lengkap tentang cara membaca dan mengedit file Excel di ASP. NET Core di C# hadir di halaman GitHub ini

CATATAN

_hostingEnvironment adalah jalur dasar untuk file input bertipe IHostingEnvironment

CATATAN

Dimulai dengan v16. 2. 0. x, jika Anda mereferensikan rakitan Syncfusion dari penyiapan uji coba atau dari umpan NuGet, Anda juga harus menambahkan “Syncfusion. Melisensikan” referensi rakitan dan sertakan kunci lisensi dalam proyek Anda. Lihat link ini untuk mengetahui cara mendaftarkan kunci lisensi Syncfusion di aplikasi Anda untuk menggunakan komponen kami. Anda juga dapat menjelajahi kami yang menunjukkan cara membuat dan memodifikasi file Excel dari C# hanya dengan lima baris kode

参考：https. //Tolong. syncfusion. com/file-formats/xlsio/create-read-edit-excel-files-in-asp-net-core-c-sharp#create-a-simple-excel-report

Cara membaca data dari file Excel di ASP. inti bersih?

Cara membuat file Excel di ASP. inti bersih?

Cara membaca dan menulis file Excel di C#. inti bersih?

Apa cara terbaik untuk membaca file Excel di C#?