using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web;
namespace Able.Utility.Common
{
public class NOPIHelper
{
public enum NOPIDataType
{
String = 1,
DateTime = 2,
Bool = 3,
Number = 4,
Double = 5,
Default = 6
}
//列自適應
public static void SetAutoSizeColumn(int list, ISheet sheet)
{
for (int i = 0; i <= list; i++)
{
sheet.AutoSizeColumn(i, true);
}
}
//列居中
public static ICellStyle GetCenterStyle(HSSFWorkbook workbook)
{
var centerStyle = workbook.CreateCellStyle();
centerStyle.Alignment = HorizontalAlignment.Center;// 左右居中
centerStyle.VerticalAlignment = VerticalAlignment.Center;// 上下居中
return centerStyle;
}
//列向左
public static ICellStyle GetLeftStyle(HSSFWorkbook workbook)
{
var leftStyle = workbook.CreateCellStyle();
leftStyle.Alignment = HorizontalAlignment.Left;//向左
return leftStyle;
}
public static ICellStyle GetDateStyle(HSSFWorkbook workbook)
{
var dateStyle = workbook.CreateCellStyle();
dateStyle.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy-MM-dd HH:mm:ss");
return dateStyle;
}
//設置字體
private static IFont GetFont(HSSFWorkbook workbook, double FontHeightInPoints, short Boldweight)
{
var titleFont = workbook.CreateFont();
titleFont.FontHeightInPoints = FontHeightInPoints;
titleFont.Boldweight = Boldweight;
return titleFont;
}
public static void SetCellValue(HSSFWorkbook workbook, ICell newCell, dynamic strValue, NOPIDataType dataType = NOPIDataType.Default, HorizontalAlignment? alignment = null)
{
if (strValue == null)
{
newCell.SetCellValue("");
return;
}
var cellStyle = workbook.CreateCellStyle();
if (alignment != null)
{
cellStyle.Alignment = (HorizontalAlignment)alignment;
}
switch (dataType)
{
case NOPIDataType.String://字符串类型
newCell.SetCellValue(Convert.ToString(strValue));
if (alignment == null)
{
cellStyle.Alignment = HorizontalAlignment.Left;
}
break;
case NOPIDataType.Bool://布尔型
newCell.SetCellValue(strValue);
if (alignment == null)
{
cellStyle.Alignment = HorizontalAlignment.Center;
}
break;
case NOPIDataType.Number://整型:Int16,Int32,Int64,Byte
newCell.SetCellValue(Convert.ToString(strValue));
if (alignment == null)
{
cellStyle.Alignment = HorizontalAlignment.Right;
}
break;
case NOPIDataType.Double://浮点型
newCell.SetCellValue(strValue);
if (alignment == null)
{
cellStyle.Alignment = HorizontalAlignment.Right;
}
break;
case NOPIDataType.DateTime://日期类型
newCell.SetCellValue(strValue);
if (alignment == null)
{
cellStyle.Alignment = HorizontalAlignment.Center;
}
cellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("yyyy-MM-dd HH:mm:ss");//格式化显示
break;
default:
newCell.SetCellValue(Convert.ToString(strValue));
if (alignment == null)
{
cellStyle.Alignment = HorizontalAlignment.Left;
}
break;
}
newCell.CellStyle = cellStyle;
}
public static void ExportExcel(string fileName, out int rowIndex, out HSSFWorkbook workbook, out ISheet sheet, Dictionary<string, string> arrQuery = null, List<string> arrTitle = null)
{
rowIndex = 0;
workbook = new HSSFWorkbook();
sheet = workbook.CreateSheet(fileName);
#region 設置標題
var titleCellIndex = 0;
var titleRowIndex = rowIndex++;
var titleRow = sheet.CreateRow(titleRowIndex);
titleRow.CreateCell(titleCellIndex).SetCellValue(fileName);//創建標題列
var titleStyle = GetCenterStyle(workbook);//標題樣式
titleStyle.SetFont(GetFont(workbook, 20, 400));//設置字體樣式
titleRow.GetCell(titleCellIndex).CellStyle = titleStyle;//設置樣式
if (arrTitle != null && arrTitle.Count() > 1)
{
sheet.AddMergedRegion(new CellRangeAddress(titleRowIndex, titleRowIndex, titleCellIndex, arrTitle.Count() - 1));//合併單元格
}
#endregion
#region 查詢條件
if (arrQuery != null)
{
var queryStyle = GetLeftStyle(workbook);
queryStyle.SetFont(GetFont(workbook, 15, 400));
foreach (var item in arrQuery)
{
var row = sheet.CreateRow(rowIndex++);
row.CreateCell(0).SetCellValue(item.Key);//查詢條件
row.GetCell(0).CellStyle = queryStyle;
row.CreateCell(1).SetCellValue(item.Value);//查詢內容
row.GetCell(1).CellStyle.Alignment = HorizontalAlignment.Left;
}
}
#endregion
#region 设置列头
if (arrTitle != null)
{
var headStyle = GetCenterStyle(workbook);
headStyle.SetFont(GetFont(workbook, 15, 400));
var headerRow = sheet.CreateRow(rowIndex++);
for (int i = 0; i < arrTitle.Count; i++)
{
headerRow.CreateCell(i).SetCellValue(arrTitle[i]);
headerRow.GetCell(i).CellStyle = headStyle;
}
}
#endregion
}
}
}
1：調用
int rowIndex;
ISheet sheet;
HSSFWorkbook workbook;
var fileName = base.L("SubscriptionList");
var arrTitle = JsonConvert.DeserializeObject<List<string>>(input.arrTitle);//設置標題
NOPIHelper.ExportExcel(fileName, out rowIndex, out workbook, out sheet, null, arrTitle);
foreach (var item in resultPagedDto.Items)
{
int cellIndex = 0;
var row = sheet.CreateRow(rowIndex++);
NOPIHelper.SetCellValue(workbook, row.CreateCell(cellIndex++), item.EmailAddress);
NOPIHelper.SetCellValue(workbook, row.CreateCell(cellIndex++), item.MobileNumber);
NOPIHelper.SetCellValue(workbook, row.CreateCell(cellIndex++), item.Barcode);//Barcode
NOPIHelper.SetCellValue(workbook, row.CreateCell(cellIndex++), item.ProductName);//ProductName
NOPIHelper.SetCellValue(workbook, row.CreateCell(cellIndex++), item.Variant);//Variant
NOPIHelper.SetCellValue(workbook, row.CreateCell(cellIndex++), item.Vendor);//Vendor
NOPIHelper.SetCellValue(workbook, row.CreateCell(cellIndex++), item.StatusName);//StatusName
NOPIHelper.SetCellValue(workbook, row.CreateCell(cellIndex++), item.NotificationTime, NOPIDataType.DateTime);//NotificationTime
NOPIHelper.SetCellValue(workbook, row.CreateCell(cellIndex++), item.CreationTime, NOPIDataType.DateTime);//CreationTime
}
return base.ExportExcelFile(sheet, workbook, fileName, arrTitle.Count);
2：導出
public FileResult ExportExcelFile(ISheet sheet, HSSFWorkbook workbook, string fileName, int totalColumn)
{
NOPIHelper.SetAutoSizeColumn(totalColumn, sheet);
using (MemoryStream memoryStream = new MemoryStream())
{
workbook.Write(memoryStream);
//application/vnd.ms-excel
return File(memoryStream.ToArray(), "application/ms-excel", $"{fileName}.xls");
}
}
原文：https://www.cnblogs.com/rgsn/p/14311866.html