ASP.Net WebApi 操作Excel文件（NPOI实现）

目录

前提

导出

导入

---

前提

安装NPOI

导出

新建service方法，搭建基本框架，获取Excel数据，返回byte[]
 

public byte[] GetByte(){     byte[] bt = null;     try     {        List<SalesOrder> list = SalesOrderBll.Instance.GetList(); //获取数据     }     catch (Exception e)    {        bt = null;        Logger.Exception(e); //保存日志    }    return bt;}

导入模板（定义好的表头），新建NPOI相关字段
 

public byte[] GetByte(){     byte[] bt = null;     try     {        List<SalesOrder> list = SalesOrderBll.Instance.GetList(); //获取数据        //导出模板        string fileTemplate = HttpContext.Current.Server.MapPath("/ExportTemplate/导出模板.xlsx");        FileStream fs = new FileStream(fileTemplate, FileMode.Open, FileAccess.Read); //文件流        XSSFWorkbook workbook = new XSSFWorkbook(fs);        ISheet sheet = workbook.GetSheet("导出数据");  //工作表        //如果有样式需求可添加(在下方应用)        // 设置字体为黑体        IFont font = workbook.CreateFont();        font.FontName = "黑体";        font.Color = HSSFColor.Black.Index;        // 设置单元格外边框        ICellStyle cellStyle = workbook.CreateCellStyle();        cellStyle.BorderTop = BorderStyle.Thin;        cellStyle.BorderBottom = BorderStyle.Thin;        cellStyle.BorderLeft = BorderStyle.Thin;        cellStyle.BorderRight = BorderStyle.Thin;     }     catch (Exception e)    {        bt = null;        Logger.Exception(e); //保存日志    }    return bt;}

如果没有模板就自定义表头(每一列单独设置)
 

public byte[] GetByte(){     byte[] bt = null;     try     {        List<SalesOrder> list = SalesOrderBll.Instance.GetList(); //获取数据        FileStream fs = new FileStream(fileTemplate, FileMode.Open, FileAccess.Read); //文件流        XSSFWorkbook workbook = new XSSFWorkbook(fs);        ISheet sheet = workbook.GetSheet("导出数据");  //工作表        IRow row = sheet.CreateRow(0); //Excel的第一行，从0开始        ICell cell = row.CreateCell(0); //Excel的第一行第一列，从0开始        cell.SetCellValue("标题1");        ICell cell2 = row.CreateCell(1);        cell2.SetCellValue("标题2"); //以此类推。。     }    catch (Exception e)    {        bt = null;        Logger.Exception(e); //保存日志    }    return bt;}

新增私有辅助方法，用于设置单元格值
 

/// <summary>/// 辅助方法/// </summary>/// <param name="row">当前行</param>/// <param name="columnIndex">单元格列数</param>/// <param name="value">单元格值</param>/// <param name="cellStyle">单元格样式</param>/// <param name="font">字体样式</param>private void SetCellValue(IRow row, int columnIndex, object value, ICellStyle cellStyle = null, IFont font = null){    ICell cell = row.CreateCell(columnIndex);    if (cellStyle != null)    {        cell.CellStyle = cellStyle;        cell.CellStyle.SetFont(font);    }    cell.SetCellValue(value?.ToString());}

遍历数据，设置每一行每一列的值
 

public byte[] GetByte(){     byte[] bt = null;     try     {        List<SalesOrder> list = SalesOrderBll.Instance.GetList(); //获取数据        //导出模板        string fileTemplate = HttpContext.Current.Server.MapPath("/ExportTemplate/光学镜订单导出模板.xlsx");        FileStream fs = new FileStream(fileTemplate, FileMode.Open, FileAccess.Read); //文件流        XSSFWorkbook workbook = new XSSFWorkbook(fs);        ISheet sheet = workbook.GetSheet("导出数据");  //工作表        // 设置字体为黑体        IFont font = workbook.CreateFont();        font.FontName = "黑体";        font.Color = HSSFColor.Black.Index;        // 设置单元格外边框        ICellStyle cellStyle = workbook.CreateCellStyle();        cellStyle.BorderTop = BorderStyle.Thin;        cellStyle.BorderBottom = BorderStyle.Thin;        cellStyle.BorderLeft = BorderStyle.Thin;        cellStyle.BorderRight = BorderStyle.Thin;                int index = 2; //数据开始行数        foreach (SalesOrder order in list)        {            IRow row = sheet.CreateRow(index);            SetCellValue(row, 0, order.SalesOrderNo, cellStyle, font); //使用私有辅助方法赋值            SetCellValue(row, 1, order.ContactName, cellStyle, font);            SetCellValue(row, 2, order.Mobile, cellStyle, font);        }     }    catch (Exception e)    {        bt = null;        Logger.Exception(e); //保存日志    }    return bt;}/// <summary>/// 辅助方法/// </summary>/// <param name="row">当前行</param>/// <param name="columnIndex">单元格列数</param>/// <param name="value">单元格值</param>/// <param name="cellStyle">单元格样式</param>/// <param name="font">字体样式</param>private void SetCellValue(IRow row, int columnIndex, object value, ICellStyle cellStyle = null, IFont font = null){    ICell cell = row.CreateCell(columnIndex);    if (cellStyle != null)    {        cell.CellStyle = cellStyle;        cell.CellStyle.SetFont(font);    }    cell.SetCellValue(value?.ToString());}

合并单元格（有需要的使用）

新建私有赋值方法，合并单元格
 

/// <summary>/// 辅助方法/// </summary>/// <param name="sheet">工作表</param>/// <param name="startRow">合并开始行数</param>/// <param name="endRow">合并结束行数</param>/// <param name="startCol">合并开始列数</param>/// <param name="endCol">合并结束列数</param>private void SetMergedCellStyle(ISheet sheet, int startRow, int endRow, int startCol, int endCol){    CellRangeAddress mergedRegion = new NPOI.SS.Util.CellRangeAddress(startRow, endRow, startCol, endCol);    sheet.AddMergedRegion(mergedRegion);}

根据需求进行合并
 

public byte[] GetByte(){     byte[] bt = null;     try     {        List<SalesOrder> list = SalesOrderBll.Instance.GetList(); //获取数据        //导出模板        string fileTemplate = HttpContext.Current.Server.MapPath("/ExportTemplate/光学镜订单导出模板.xlsx");        FileStream fs = new FileStream(fileTemplate, FileMode.Open, FileAccess.Read); //文件流        XSSFWorkbook workbook = new XSSFWorkbook(fs);        ISheet sheet = workbook.GetSheet("导出数据");  //工作表        // 设置字体为黑体        IFont font = workbook.CreateFont();        font.FontName = "黑体";        font.Color = HSSFColor.Black.Index;        // 设置单元格外边框        ICellStyle cellStyle = workbook.CreateCellStyle();        cellStyle.BorderTop = BorderStyle.Thin;        cellStyle.BorderBottom = BorderStyle.Thin;        cellStyle.BorderLeft = BorderStyle.Thin;        cellStyle.BorderRight = BorderStyle.Thin;                int index = 2; //数据开始行数        foreach (SalesOrder order in list)        {            IRow row = sheet.CreateRow(index);            IRow row2 = sheet.CreateRow(index + 1); //设置row2是因为我想合并两行数据            SetCellValue(row, 0, order.SalesOrderNo, cellStyle, font);            SetMergedCellStyle(sheet, index, index + 1, 0, 0);  //合并excel中第i行到i+1行第一列的数据            SetCellValue(row, 1, order.ContactName, cellStyle, font);            SetMergedCellStyle(sheet, index, index + 1, 1, 1);            SetCellValue(row, 2, order.Mobile1);  //第三列不进行合并            SetCellValue(row2, 2, order.Mobile2);        }     }    catch (Exception e)    {        bt = null;        Logger.Exception(e); //保存日志    }    return bt;}/// <summary>/// 辅助方法/// </summary>/// <param name="row">当前行</param>/// <param name="columnIndex">单元格列数</param>/// <param name="value">单元格值</param>/// <param name="cellStyle">单元格样式</param>/// <param name="font">字体样式</param>private void SetCellValue(IRow row, int columnIndex, object value, ICellStyle cellStyle = null, IFont font = null){    ICell cell = row.CreateCell(columnIndex);    if (cellStyle != null)    {        cell.CellStyle = cellStyle;        cell.CellStyle.SetFont(font);    }    cell.SetCellValue(value?.ToString());}

效果如下，第1、2列进行合并，第三列不合并


 重写Npoi流方法（否则会自己关闭流从而报错）
 

/// <summary>/// 重写Npoi流方法/// </summary>internal class NpoiMemoryStream : MemoryStream{    public NpoiMemoryStream()    {        AllowClose = true;    }    public bool AllowClose { get; set; }    public override void Close()    {        if (AllowClose)            base.Close();    }}

返回byte[]
 

NpoiMemoryStream streamOut = new NpoiMemoryStream { AllowClose = false };workbook.Write(streamOut);streamOut.Seek(0, SeekOrigin.Begin);streamOut.AllowClose = true;bt = streamOut.ToArray();

完整代码
 

public byte[] GetByte(){     byte[] bt = null;     try     {        List<SalesOrder> list = SalesOrderBll.Instance.GetList(); //获取数据        //导出模板        string fileTemplate = HttpContext.Current.Server.MapPath("/ExportTemplate/光学镜订单导出模板.xlsx");        FileStream fs = new FileStream(fileTemplate, FileMode.Open, FileAccess.Read); //文件流        XSSFWorkbook workbook = new XSSFWorkbook(fs);        ISheet sheet = workbook.GetSheet("导出数据");  //工作表        // 设置字体为黑体        IFont font = workbook.CreateFont();        font.FontName = "黑体";        font.Color = HSSFColor.Black.Index;        // 设置单元格外边框        ICellStyle cellStyle = workbook.CreateCellStyle();        cellStyle.BorderTop = BorderStyle.Thin;        cellStyle.BorderBottom = BorderStyle.Thin;        cellStyle.BorderLeft = BorderStyle.Thin;        cellStyle.BorderRight = BorderStyle.Thin;                int index = 2; //数据开始行数        foreach (SalesOrder order in list)        {            IRow row = sheet.CreateRow(index);            IRow row2 = sheet.CreateRow(index + 1); //设置row2是因为我想合并两行数据            SetCellValue(row, 0, order.SalesOrderNo, cellStyle, font);            SetMergedCellStyle(sheet, index, index + 1, 0, 0);  //合并excel中第i行到i+1行第一列的数据            SetCellValue(row, 1, order.ContactName, cellStyle, font);            SetMergedCellStyle(sheet, index, index + 1, 1, 1);            SetCellValue(row, 2, order.Mobile1);  //第三列不进行合并            SetCellValue(row2, 2, order.Mobile2);        }        //返回byte[]        NpoiMemoryStream streamOut = new NpoiMemoryStream { AllowClose = false };        workbook.Write(streamOut);        streamOut.Seek(0, SeekOrigin.Begin);        streamOut.AllowClose = true;        bt = streamOut.ToArray();     }    catch (Exception e)    {        bt = null;        Logger.Exception(e); //保存日志    }    return bt;}/// <summary>/// 辅助方法/// </summary>/// <param name="row">当前行</param>/// <param name="columnIndex">单元格列数</param>/// <param name="value">单元格值</param>/// <param name="cellStyle">单元格样式</param>/// <param name="font">字体样式</param>private void SetCellValue(IRow row, int columnIndex, object value, ICellStyle cellStyle = null, IFont font = null){    ICell cell = row.CreateCell(columnIndex);    if (cellStyle != null)    {        cell.CellStyle = cellStyle;        cell.CellStyle.SetFont(font);    }    cell.SetCellValue(value?.ToString());}

WebApi返回文件
 

[HttpPost]public HttpResponseMessage Post(){    var response = Request.CreateResponse(HttpStatusCode.OK);    response.Content = new ByteArrayContent(OrderServices.Instance.GetByte());  //刚刚实现的方法    response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment") { FileName = "导出文件.xlsx" };    response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");    return response;}

导入

安装MultipartDataMediaFormatter用于控制器接收文件


 WebApi接收文件

我这里是自己生成了一个类VHttpFile可以添加别的参数，HttpFile用于接收文件，如果只有文件那么控制器那里只写HttpFile就可以了
 

public class VHttpFile{    public string FileName { get; set; }    public HttpFile File { get; set; }}

 

[HttpPost]public bool Import(VHttpFile file){    return ImportServices.Instance.Import(file);}

创建Import方法（基础框架，格式校验）
 

public bool Import(VHttpFile model){    bool result = false;    try    {        HttpFile file = model.File;        if (file == null || file.Buffer.Length == 0)            return result.Fail("上传文件为空");        if (Path.GetExtension(file.FileName).ToLower() != ".xlsx" && Path.GetExtension(file.FileName).ToLower() != ".xls")            return result.Fail("文件格式错误，请上传xlsx格式文件");            }    catch(Exception ex)    {        result = false;        Logger.Exception(e); //添加日志    }    return result;}

创建NPOI相关类（Excel操作相关类）

 

public bool Import(VHttpFile model){    bool result = false;    try    {        HttpFile file = model.File;        if (file == null || file.Buffer.Length == 0)            return result.Fail("上传文件为空");        if (Path.GetExtension(file.FileName).ToLower() != ".xlsx" && Path.GetExtension(file.FileName).ToLower() != ".xls")            return result.Fail("文件格式错误，请上传xlsx格式文件");                IWorkbook workbook = null;        StringBuilder errorMessage = new StringBuilder(); //用于存储有异常的数据        using (var stream = new MemoryStream(file.Buffer))        {            if (Path.GetExtension(file.FileName).ToLower() == ".xlsx")  //不同版本Excel生成不一样的类            {                workbook = new XSSFWorkbook(stream);            }            else if (Path.GetExtension(file.FileName).ToLower() == ".xls")            {                workbook = new HSSFWorkbook(stream);            }            ISheet sheet = workbook.GetSheetAt(0); //获取工作表            int rowCount = sheet.LastRowNum; //最后一行数据        }    }    catch(Exception ex)    {        result = false;        Logger.Exception(e); //添加日志    }    return result;}

遍历表数据，保存到数据库

为什么我从1开始，因为一般表格的第一行是标题，数据从第二行开始，在代码中索引从0开始，所以数据应该从索引1开始。
 

public bool Import(VHttpFile model){    bool result = false;    try    {        HttpFile file = model.File;        if (file == null || file.Buffer.Length == 0)            return result.Fail("上传文件为空");        if (Path.GetExtension(file.FileName).ToLower() != ".xlsx" && Path.GetExtension(file.FileName).ToLower() != ".xls")            return result.Fail("文件格式错误，请上传xlsx格式文件");                IWorkbook workbook = null;        StringBuilder errorMessage = new StringBuilder(); //用于存储有异常的数据        using (var stream = new MemoryStream(file.Buffer))        {            if (Path.GetExtension(file.FileName).ToLower() == ".xlsx")  //不同版本Excel生成不一样的类            {                workbook = new XSSFWorkbook(stream);            }            else if (Path.GetExtension(file.FileName).ToLower() == ".xls")            {                workbook = new HSSFWorkbook(stream);            }            ISheet sheet = workbook.GetSheetAt(0); //获取工作表            int rowCount = sheet.LastRowNum; //最后一行数据            List<SalesOrder> list = new List<SalesOrder>(); //存储需要新增的数据            for (int row = 1; row <= rowCount; row++)            {                IRow currentRow = sheet.GetRow(row); //当前行                string saleOrderNo = currentRow.GetCell(0).StringCellValue;  //订单号                if (string.IsNullOrEmpty(saleOrderNo))                {                    errorMessage.AppendLine($"第{row}行错误：订单号为空");                    continue;                }                decimal price = (decimal)currentRow.GetCell(1).NumericCellValue;  //订单金额                int amount = (int)currentRow.GetCell(2).NumericCellValue;  //订单数量                list.Add(new SalesOrder                {                    SaleOrderNo = saleOrderNo,                    Price = price,                    Amount = amount                });            }            result = DBHelper.Save(list); //保存到数据库            if (errorMessage.Length > 0)            {                Logger.Exception(errorMessage.ToString()); //添加日志            }        }    }    catch(Exception ex)    {        result = false;        Logger.Exception(e); //添加日志    }    return result;}

关于上面使用 StringCellValue、NumericCellValue 或者是其他，取决于单元格的格式，需要注意。如果单元格格式为货币但是你调用的是StringCellValue那么就会报错

 待补充（合并单元格读数据）
 

internal class ImportServices{    private static readonly Dictionary<string, ImportServices> DicData = new Dictionary<string, ImportServices>();    /// <summary>    /// 属性集合    /// </summary>    public Dictionary<int, Params> ParamDic = new Dictionary<int, Params>();    public int StartIndex {  get; set; }    public int EndIndex { get; set; }    public string CategoryCode { get; set; }    public static ImportServices GetInstance(ISheet sheet,string categoryCode,int startIndex,int endIndex)    {        string sheetName = sheet.SheetName;        if (!DicData.ContainsKey(sheetName))        {            DicData[sheetName] = new ImportServices(sheet, categoryCode, startIndex,endIndex);        }        return DicData[sheetName];    }    private static CellRangeAddress GetRangeAddress(ICell cell)    {        ISheet sheet = cell.Sheet;        for (int i = 0; i < sheet.NumMergedRegions; i++)        {            CellRangeAddress region = sheet.GetMergedRegion(i);            if (region.ContainsRow(cell.RowIndex) && region.ContainsColumn(cell.ColumnIndex))            {                return region;            }        }        return null;    }    public ImportServices(ISheet sheet,string categoryCode, int startIndex, int endIndex)    {        StartIndex = startIndex;        EndIndex = endIndex;        CategoryCode = categoryCode;        IRow row = sheet.GetRow(1);        IRow row2 = sheet.GetRow(2);        List <ParamGroups> paramsList = ParamGroupsBll.Instance.GetList(categoryCode);        for (int i = startIndex; i < endIndex;)        {            ICell cell = row.GetCell(i);            CellRangeAddress mergedRegion = GetRangeAddress(cell);            string title = cell.StringCellValue;            if(mergedRegion == null)            {                Params value = paramsList.FirstOrDefault(p => p.PGroupName == title)?.ParamsList.FirstOrDefault(p => p.ParamName == row2.GetCell(i).StringCellValue);                if (value != null)                {                    ParamDic.Add(i, value);                }                i++;            }            else            {                for (int j = mergedRegion.FirstColumn; j <= mergedRegion.LastColumn; j++)                {                    Params value = paramsList.FirstOrDefault(p => p.PGroupName == title)?.ParamsList.FirstOrDefault(p => p.ParamName == row2.GetCell(i).StringCellValue);                    if(value != null)                    {                        ParamDic.Add(j, value);                    }                }                i = mergedRegion.LastColumn + 1;            }        }    }    public List<Params> GetList(IRow row)    {        List<Params> list = new List<Params>();        for (int col = StartIndex; col <= EndIndex; col++)        {            ICell cell = row.GetCell(col);            string value = cell.StringCellValue;            if (!string.IsNullOrEmpty(value) && ParamDic.ContainsKey(col))            {                list.Add(ParamDic[col]);            }        }        return list;    }}