导出excel,导出文件流并上传系统，导出合并行，合并列列excel

1. 导出直接下载

 

 const elt = document.createElement('a')
      // const token = getToken()
      elt.setAttribute(
        'href',
        `/Customer/productList/GetListExcelFile?id=${this.model.ID}&TOKEN=${this.token}`
      )
      elt.setAttribute('download', '供货一览表.xlsx')
      elt.style.display = 'none'
      document.body.appendChild(elt)
      elt.click()
      document.body.removeChild(elt)

2.导出流

 

• 前段

• axios({
          method: 'get',
          url:
             webConfig.webUrl + '/Customer/ProductList/GetListExcelFile?id=' + this.model.ID,
          responseType: 'blob', // 设置接受的流格式
          headers: {
            TOKEN: getToken(),
            'Content-Type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;chartset=UTF-8'
          }
        })
          .then((res) => {
            // console.log('GetListExcelFile', res)
            this.saveFile(res.data)
          })

  后端

   [HttpGet]
          public FileContentResult GetListExcelFile(string id)
          {
              var excel = impl.GetListExcelFile(id);
              System.IO.MemoryStream ms = new System.IO.MemoryStream();
              excel.Write(ms);
               var result = new FileContentResult(ms.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;chartset=UTF-8");
               return result;
          }

  3.导出合并行，合并列列excel

  组织列头和内容

•    public XSSFWorkbook GetListExcelFile(string id)
          {
              List<List<string>> columnList = new List<List<string>>();
              List<List<string>> rowList = new List<List<string>>();
  
  
              GetSummaryData(id, ref columnList, ref rowList);
  
  
              var excel = DownloadExcel.DownLoadProductListExcel("一览表", columnList, rowList);
              return excel;
          }

  根据行内容，列内容合并

•  public static XSSFWorkbook DownLoadProductListExcel(string sheetName, List<List<string>> colunms, List<List<string>> rows)
          {
              NPOI.XSSF.UserModel.XSSFWorkbook book = new NPOI.XSSF.UserModel.XSSFWorkbook();
              NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetName);
  
              //正式数据样式
              ICellStyle styleCommonLeft = book.CreateCellStyle();
              styleCommonLeft.Alignment = HorizontalAlignment.Left;
              styleCommonLeft.VerticalAlignment = VerticalAlignment.Center;
              styleCommonLeft.WrapText = true;
              IFont fontLeft = book.CreateFont();
              fontLeft.FontHeightInPoints = 12;
              fontLeft.FontName = "宋体";
              styleCommonLeft.SetFont(fontLeft);
  
              ICellStyle styleCommonCenter = book.CreateCellStyle();
              styleCommonCenter.Alignment = HorizontalAlignment.Center;
              styleCommonCenter.VerticalAlignment = VerticalAlignment.Center;
              styleCommonCenter.WrapText = true;
              IFont fontCenter = book.CreateFont();
              fontCenter.FontHeightInPoints = 12;
              fontCenter.IsBold = true;
              fontCenter.FontName = "宋体";
              styleCommonCenter.SetFont(fontCenter);
  
  
              // 列头 第1/2行
              var rangeList = new List<int>();
              for (int i = 0; i < colunms.Count; i++)
              {
                  IRow row = sheet.CreateRow(i);
                  for (int j = 0; j < colunms[i].Count; j++)
                  {
                      row.CreateCell(j).SetCellValue(colunms[i][j]);
                      row.GetCell(j).CellStyle = styleCommonCenter;
  
                      //sheet.SetColumnWidth(i, 5);
                      sheet.SetDefaultColumnStyle(i, styleCommonCenter);
  
                      if (colunms.Count > 1 && i == 0 && string.IsNullOrWhiteSpace(colunms[0][j]) == false)
                      {
                          rangeList.Add(j);
                      }
                  }
              }
              //自定义合并列
              sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0));
              sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 1, 24));
              sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 25, 32));
              sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 33, 44));
              //if (rangeList.Count > 0)
              //{
              //    if (rangeList.Count == 1)
              //    {
              //        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, rangeList[0], colunms[0].Count - 1));
              //    }
              //    else
              //    {
              //        for (int i = 0; i < rangeList.Count - 1; i++)
              //        {
              //            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, rangeList[i], rangeList[i + 1] - 1));
              //        }
              //        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, rangeList[rangeList.Count - 1], colunms[0].Count - 1));
              //    }
              //}
  
              var rowRangeLength = 0;
              var rowRangeStart = 0;
              var lastMcode = string.Empty;
              var columnConnt = colunms.Count;
  
              for (int j = 0; j < rows.Count; j++)
              {
                  NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(j + columnConnt);
                  List<string> itemrow = rows[j];
                  for (int g = 0; g < itemrow.Count; g++)
                  {
                      row2.CreateCell(g).SetCellValue(itemrow[g]);
                      row2.GetCell(g).CellStyle = styleCommonLeft;
                  }
                  if (j == 0)
                  {
                      lastMcode = itemrow[1];
                  }
  
                  if (j == rows.Count - 1)
                  {
                      rowRangeLength++;
                      if (rowRangeLength > 1)
                      {
                          for (int c = 0; c < itemrow.Count - 2; c++)
                          {
                              sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowRangeStart + 2 , rowRangeStart + 1 + rowRangeLength, c, c));
                          }
  
                      }
                  }
                  else if (lastMcode != itemrow[1])
                  {
                      for (int c = 0; c < itemrow.Count - 2; c++)
                      {
                          sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowRangeStart + 2 , rowRangeStart + 1 + rowRangeLength, c, c));
                      }
                      lastMcode = itemrow[1];
                      rowRangeStart = j;
                      rowRangeLength = 0;
                  }
                  rowRangeLength++;
  
              }
  
  
              return book;
          }