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的工作表
    //设置列宽
    sheet1.SetColumnWidth(0, 100 * 50);
    sheet1.SetColumnWidth(1, 100 * 50);
    sheet1.SetColumnWidth(2, 100 * 50);
    sheet1.SetColumnWidth(3, 100 * 50);
    sheet1.SetColumnWidth(4, 100 * 50);
    sheet1.SetColumnWidth(5, 100 * 50);
    sheet1.SetColumnWidth(6, 100 * 50);
    //创建样式
    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;