NPOI的下载、引用

下载地址https://github.com/KezhanW/NPOI
然后我们打开解压好的文件，看到有dotnet2和dotnet4文件夹，这里我们打开dotnet4，引入全部的dll

接下来using

using NPOI;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.Util;

下面就可以开始我们的项目了

导出Excel表格

注：可以添加单元格样式、合并单元格等操作如无需删除即可

/// <summary>
/// 导出Excel
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public JsonResult ExportExcel(string ids)
{
    //创建工作簿对象
    IWorkbook workbook = new XSSFWorkbook(); //创建Workbook
    workbook.CreateSheet("sheet1"); //创建sheet
    ISheet sheet1 = workbook.GetSheet("sheet1"); //获取一个名为Sheet1的工作表
    //设置列宽
    //SetColumnWidth的第二个参数要乘以256，这是怎么回事呢？其实，这个参数的单位是1/256个字符宽度，也就是说，这里是把B列的宽度设置为了100个字符。
    sheet1.SetColumnWidth(0, 256 * 20);
    sheet1.SetColumnWidth(1, 256 * 20);
    sheet1.SetColumnWidth(2, 256 * 20);
    sheet1.SetColumnWidth(3, 256 * 20);
    sheet1.SetColumnWidth(4, 256 * 20);
    sheet1.SetColumnWidth(5, 256 * 20);
    sheet1.SetColumnWidth(6, 256 * 20);
    //创建样式
    ICellStyle style = workbook.CreateCellStyle();
    style.BorderBottom = BorderStyle.Thin;
    style.BorderTop = BorderStyle.Thin;
    style.BorderLeft = BorderStyle.Thin;
    style.BorderRight = BorderStyle.Thin;
    style.BottomBorderColor = HSSFColor.Black.Index;//下边框为细线边框
    style.LeftBorderColor = HSSFColor.Black.Index;//左边框
    style.RightBorderColor = HSSFColor.Black.Index;//上边框
    style.TopBorderColor = HSSFColor.Black.Index;//右边框
    style.Alignment = HorizontalAlignment.CenterSelection;//居中
    //查询数据
    KCMS_EF kef = new KCMS_EF();//实例化数据库操作上下文
    List<SysAdmin> userLogin = kef.SysAdmin.Where(p => p.IsLogin == 1).ToList();
    //创建数据
    IRow row = sheet1.CreateRow(0);
    row.HeightInPoints = 16; //行高
    row.CreateCell(0).SetCellValue("Excel的导出");
    row.GetCell(0).CellStyle = style;

    IRow row1 = sheet1.CreateRow(1);
    row1.HeightInPoints = 16; //行高
    row1.CreateCell(0).SetCellValue("账号");
    row1.GetCell(0).CellStyle = style;
    row1.CreateCell(1).SetCellValue("姓名");
    row1.GetCell(1).CellStyle = style;
    row1.CreateCell(2).SetCellValue("邮箱");
    row1.GetCell(2).CellStyle = style;
    var i = 2;
    foreach (var item in userLogin)
    {
        IRow row2 = sheet1.CreateRow(i);
        row2.HeightInPoints = 16; //行高
        row2.CreateCell(0).SetCellValue(item.LoginName);
        row2.GetCell(0).CellStyle = style;
        row2.CreateCell(1).SetCellValue(item.RealName);
        row2.GetCell(1).CellStyle = style;
        row2.CreateCell(2).SetCellValue(item.Email);
        row2.GetCell(2).CellStyle = style;
        i++;
    }
    //合并单元格
    //第一个参数：从第几行开始合并
    //第二个参数：到第几行结束合并
    //第三个参数：从第几列开始合并
    //第四个参数：到第几列结束合并
    CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2);
    sheet1.AddMergedRegion(region);
    //强制Excel重新计算表中所有的公式
    sheet1.ForceFormulaRecalculation = true;
    //生成表格
    string str = "Task List-" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
    DateTime now = DateTime.Now;
    string text = string.Concat(new string[]
    {
                "/UpFiles/ExcelFiles/",
                now.Year.ToString(),
                "/",
                now.Month.ToString(),
                "/",
                now.Day.ToString(),
                "/"
    });
    string text2 = Server.MapPath(text);
    if (!Directory.Exists(text2))
    {
        Directory.CreateDirectory(text2);
    }
    byte[] buffer;
    MemoryStream ms = new MemoryStream();
    workbook.Write(ms);
    buffer = ms.ToArray();
    System.IO.File.WriteAllBytes(text2 + str, buffer);
    return Json(new { code = 200, msg = text + str });
}

前台请求方法及下载

$.post("/practice/ExportExcel", {}, function (data) {
    var a = document.createElement('a');
    //需要下载的数据内容,我这里放的就是BLOB，如果你有下载链接就不需要了
    var filename = '分票数据导出.xlsx';
    a.href = data.msg;
    a.download = filename;
    a.click();
})

插入行

/**
* @param startRow the row to start shifting
            （从下标为startRow的行开始移动）
* @param endRow the row to end shifting
            （到下标为endRow的行结束移动）
* @param n the number of rows to shift
            （有多少行需要移动）
* @param copyRowHeight whether to copy the row height during the shift
* @param resetOriginalRowHeight whether to set the original row's height to the default
*/
void ShiftRows(int startRow, int endRow, int n, boolean copyRowHeight, boolean resetOriginalRowHeight);

例：sheet.ShiftRows(insertRow,sheet.LastRowNum,1,true,false);
    sheet.CreateRow(insertRow);

添加单元格背景色

ICellStyle style = workbook.CreateCellStyle();
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
style.FillPattern = FillPattern.SolidForeground;
 
ICell cell = workbook.CreateSheet().CreateRow(0).CreateCell(0);
cell.CellStyle = style;

修改字体

ICellStyle style = workbook.CreateCellStyle();
IFont font = workbook.CreateFont();//新建一个字体样式对象
font.IsBold = true;
font.FontName = "Microsoft YaHei";
font.Color = HSSFColor.LightOrange.Index;
style.SetFont(font);
 
ICell cell = workbook.CreateSheet().CreateRow(0).CreateCell(0);
cell.CellStyle = style;