Need to introduce NPOI, download from Nuget
General method, pro-test available
public class NPOIHelper<T> where T : class
{
public static async Task<IActionResult> ExcelDataExrot(string tablename, List<T> list, List<string> ExcelTitle)
{
try
{
//Create Excel file object
var workbook = new HSSFWorkbook();
//Create worksheet sheet0
var sheet = workbook. CreateSheet(tablename);
//Set the top title style
var TitleCellStyleFont = NpoiExcelExportHelper._.CreateStyle(workbook, HorizontalAlignment.Center,
VerticalAlignment.Center, 20, true, 700, "Italian", true, false, false, true, FillPattern.SolidForeground,
HSSFColor.Coral.Index, HSSFColor.White.Index,
FontUnderlineType.None, FontSuperScript.None, false);
//first row form
var row = NpoiExcelExportHelper._.CreateRow(sheet, 0, 28);
var cell = row. CreateCell(0);
//Merge cells Example: The rectangular area enclosed by the 1st row to the 2nd row, the 3rd column to the 4th column
//TODO: About Excel row and column cell merging
//The first parameter: which row to start merging the second parameter: to which row to end the merging third parameter: to which column to start merging the fourth parameter: to which column to end the merging
CellRangeAddress region = new CellRangeAddress(0, 0, 0, ExcelTitle. Count() - 1);
sheet. AddMergedRegion(region);
cell.SetCellValue(tablename); // After merging the cells, just assign a value to the first position (TODO: top title)
cell.CellStyle = TitleCellStyleFont;
//Second level title column style setting
var headTopStyle = NpoiExcelExportHelper._.CreateStyle(workbook, HorizontalAlignment.Center,
VerticalAlignment.Center, 15, true, 700, "Italian", true, false, false, true, FillPattern.SolidForeground,
HSSFColor.Grey25Percent.Index, HSSFColor.Black.Index,
FontUnderlineType.None, FontSuperScript.None, false);
//header name
var headerName = list[0];
row = NpoiExcelExportHelper._.CreateRow(sheet, 1, 24); // second row
for (int i = 0; i < ExcelTitle. Count; i ++ )
{
cell = NpoiExcelExportHelper._.CreateCells(row, headTopStyle, i, ExcelTitle[i]);
//set cell width
sheet. SetColumnWidth(i, 7000);
}
//#region cell content information
// cell border style
var cellStyle = NpoiExcelExportHelper._.CreateStyle(workbook, HorizontalAlignment.Center,
VerticalAlignment. Center, 10, true, 400);
//Merge cells in the left column begin
//TODO: Regarding the merging of Excel row and column cells (after merging cells, you only need to assign a value to the first position)
// The first parameter: from which row to start merging the second parameter: to which row to end the merging third parameter: to which column to start merging the fourth parameter: to which column to end the merging
for (var i = 0; i < list. Count(); i ++ )
{
row = NpoiExcelExportHelper._.CreateRow(sheet, i + 2, 20); //sheet.CreateRow(i + 2);//Create a row based on the header above
int y = 0;
foreach (System. Reflection. PropertyInfo p in list[i]. GetType(). GetProperties())
{
cell = NpoiExcelExportHelper._.CreateCells(row, cellStyle, y,
p.GetValue(list[i]) != null ? p.GetValue(list[i]).ToString() : null);
y + + ;
}
}
// #endregion
//excel save file name
string excelFileName = tablename + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
//Convert the Excel table into a stream and output
MemoryStream bookStream = new MemoryStream(); //Create a file stream
workbook.Write(bookStream); //File write stream (write byte sequence to stream)
bookStream.Seek(0, SeekOrigin.Begin); //Call Seek before output, specifying position 0 as the starting position
return await Task. FromResult(
new FileStreamResult(bookStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
FileDownloadName = excelFileName
});
}
catch (Exception e)
{
Console. WriteLine(e. Message);
return null;
}
}
}
public class NpoiExcelExportHelper
{
private static NpoiExcelExportHelper _exportHelper;
public static NpoiExcelExportHelper_
{
get => _exportHelper (_exportHelper = new NpoiExcelExportHelper());
set => _exportHelper = value;
}
/// <summary>
/// TODO: Create the row first, then create the corresponding column
/// Create the row specified in Excel
/// </summary>
/// <param name="sheet">Excel worksheet object</param>
/// <param name="rowNum">Create the first row (starting from 0)</param>
/// <param name="rowHeight">row height</param>
public HSSFRow CreateRow(ISheet sheet, int rowNum, float rowHeight)
{
HSSFRow row = (HSSFRow) sheet.CreateRow(rowNum); //Create row
row.HeightInPoints = rowHeight; //Set column header row height
return row;
}
/// <summary>
/// Create the specified cell in the row
/// </summary>
/// <param name="row">The row that needs to create cells</param>
/// <param name="cellStyle">cell style</param>
/// <param name="cellNum">Create the first few cells (starting from 0)</param>
/// <param name="cellValue">Assign value to cell</param>
/// <returns></returns>
public HSSFCell CreateCells(HSSFRow row, HSSFCellStyle cellStyle, int cellNum, string cellValue)
{
HSSFCell cell = (HSSFCell) row.CreateCell(cellNum); //Create a cell
cell.CellStyle = cellStyle; //bind the style to the cell
if (!string.IsNullOrWhiteSpace(cellValue))
{
// cell assignment
cell. SetCellValue(cellValue);
}
return cell;
}
/// <summary>
/// Common style settings for inline cells
/// </summary>
/// <param name="workbook">Excel file object</param>
/// <param name="hAlignment">horizontal layout</param>
/// <param name="vAlignment">vertical layout</param>
/// <param name="fontHeightInPoints">font size</param>
/// <param name="isAddBorder">Whether a border is required</param>
/// <param name="boldWeight">Bold font (None = 0, Normal = 400, Bold = 700</param>
/// <param name="fontName">Font (imitation Song, Kai, Song, Microsoft Yahei...corresponding to Excel theme font)</param>
/// <param name="isAddBorderColor">Whether to add border color</param>
/// <param name="isItalic">Whether to make the text italic</param>
/// <param name="isLineFeed">Whether to wrap automatically</param>
/// <param name="isAddCellBackground">Whether to add cell background color</param>
/// <param name="fillPattern">Fill pattern style (FineDots, SolidForeground, exists when isAddFillPattern=true)</param>
/// <param name="cellBackgroundColor">Cell background color (exist when isAddCellBackground=true)</param>
/// <param name="fontColor">font color</param>
/// <param name="underlineStyle">Underline style (no underline [None], single underline [Single], double underline [Double], single underline [SingleAccounting] for accounting, double underline [DoubleAccounting] for accounting)</ param>
/// <param name="typeOffset">Font superscript and subscript (normal default value [None], superscript [Sub], subscript [Super]), that is, the upper and lower offsets of the font in the cell</ param>
/// <param name="isStrikeout">Whether to display strikeout</param>
/// <returns></returns>
public HSSFCellStyle CreateStyle(HSSFWorkbook workbook, HorizontalAlignment hAlignment,
VerticalAlignment vAlignment, short fontHeightInPoints, bool isAddBorder, short boldWeight,
string fontName = "宋体", bool isAddBorderColor = true, bool isItalic = false, bool isLineFeed = false,
bool isAddCellBackground = false, FillPattern fillPattern = FillPattern.NoFill,
short cellBackgroundColor = HSSFColor.Yellow.Index, short fontColor = HSSFColor.Black.Index,
FontUnderlineType underlineStyle =
FontUnderlineType.None, FontSuperScript typeOffset = FontSuperScript.None, bool isStrikeout = false)
{
HSSFCellStyle cellStyle = (HSSFCellStyle) workbook.CreateCellStyle(); //Create a column header cell instance style
cellStyle.Alignment = hAlignment; // horizontal center
cellStyle.VerticalAlignment = vAlignment; //vertically centered
cellStyle.WrapText = isLineFeed; //Automatic line feed
//The background color, border color, and font color all use the corresponding palette index in the HSSFColor attribute. For the HSSFColor color index comparison table, refer to: https://www.cnblogs.com/Brainpan/p/5804167.html
//TODO: After referencing NPOI, the background color setting of the Excel cell can be realized through the FillForegroundColor property of the ICellStyle interface, and FillPattern is the fill pattern of the cell background color
//TODO: Be very careful, to set the background color of the cell must be two properties FillForegroundColor and FillPattern set at the same time, otherwise the background color will not be displayed
if (isAddCellBackground)
{
cellStyle.FillForegroundColor = cellBackgroundColor; //cell background color
cellStyle.FillPattern = fillPattern; //fill pattern style (FineDots fine point, SolidForeground three-dimensional foreground)
}
//Whether to increase the border
if (isAddBorder)
{
// Commonly used border styles None (no), Thin (thin border, thin), Medium (medium), Dashed (dotted line), Dotted (dotted), Thick (thick), Double (double), Hair ( hair) [top right bottom left order setting]
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle. BorderTop = BorderStyle. Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
}
// Whether to set the border color
if (isAddBorderColor)
{
//Border color [top right bottom left order setting]
cellStyle.TopBorderColor = HSSFColor.DarkGreen.Index; //DarkGreen (black green)
cellStyle.RightBorderColor = HSSFColor.DarkGreen.Index;
cellStyle.BottomBorderColor = HSSFColor.DarkGreen.Index;
cellStyle.LeftBorderColor = HSSFColor.DarkGreen.Index;
}
//Set the relevant font style
var cellStyleFont = (HSSFFont) workbook.CreateFont(); //create font
//If the font size only needs to be bold, then use the following attribute directly
//cellStyleFont.IsBold = true;
// cellStyleFont.Boldweight = boldWeight; // font bold
cellStyleFont.FontHeightInPoints = fontHeightInPoints; //font size
cellStyleFont.FontName = fontName; //Font (imitation Song, Kai, Song)
cellStyleFont.Color = fontColor; //Set font color
cellStyleFont.IsItalic = isItalic; //Whether to change the text into italic
cellStyleFont.Underline = underlineStyle; // font underline
cellStyleFont.TypeOffset = typeOffset; // font superscript subscript
cellStyleFont.IsStrikeout = isStrikeout; //Whether there is a strikethrough
cellStyle.SetFont(cellStyleFont); //Bind the font to the style
return cellStyle;
}
}