NPOI的操作

  public async Task<MemoryStream> ExportExcel(IList<fuquestionbank> _list, string pId, string pfid, string fugid)
        {
            #region 绘制表头
            string[] arr = { "序号", "姓名", "性别", "年龄", "联系电话", "随访医生", "最近一次随访", "计划次数", "共随访次数", "病历数", "咨询数", "下次随访时间", "状态" };
            HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            //添加一个sheet1  
            ISheet sheet1 = book.CreateSheet("Sheet1");
            CellRangeAddress m_region = new CellRangeAddress(0, 1, 0, arr.Length - 1);  //合并0列的n--n+2行
            sheet1.AddMergedRegion(m_region);
            IRow row = sheet1.CreateRow(0);
            ICell cell = row.CreateCell(0);
            ICellStyle cellstyle = book.CreateCellStyle();//设置垂直居中格式
            cellstyle.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中
            cellstyle.Alignment = HorizontalAlignment.CENTER;//水平居中

            cell.CellStyle = cellstyle;
            cell.SetCellValue("基本信息");
            IRow row2 = sheet1.CreateRow(2);
            for (int i = 0; i < arr.Length; i++)
            {
                row2.CreateCell(i).SetCellValue(arr[i]);
            }

            var title = _list.GroupBy(x => x.FllowPlan_Name);
            List<string> planName = new List<string>();//随访计划名称
            List<string> fllowName = new List<string>();//问卷名称
            Dictionary<string, string> timu = new Dictionary<string, string>();//当前问卷下的问卷题目
            List<string> timuList = new List<string>();
            Dictionary<string, int> timuResult = new Dictionary<string, int>();//当前问卷下的题目的个数
            //获取随访计划和问卷信息
            foreach (var item in title)
            {
                planName.Add(item.Key.ToString());
                foreach (var name in item)
                {
                    if (!fllowName.Contains(name.FollowInfo_Name))
                    {
                        fllowName.Add(name.FollowInfo_Name);
                    }
                    if (!timu.ContainsKey(name.Question_Name))
                    {
                        timu.Add(name.Question_Name, name.FollowInfo_Name);
                    }
                }
            }
            //获取当前问卷下的题目的个数
            var tGroup = timu.GroupBy(x => x.Value);
            foreach (var item in tGroup)
            {
                foreach (var count in item)
                {
                    if (!timuResult.ContainsKey(count.Value))
                    {
                        timuResult.Add(count.Value, item.Count());
                    }
                }
            }
            //获取题目集合
            foreach (KeyValuePair<string, string> item in timu)
            {
                timuList.Add(item.Key);
            }
            //绘制随访计划表头
            //IRow row3 = sheet1.CreateRow(0);

            int rowOne = arr.Length;
            for (int i = 0; i < planName.Count; i++)
            {
                sheet1.AddMergedRegion(new CellRangeAddress(0, 0, rowOne, rowOne + timu.Count - 2));
                ICell cellPlan = row.CreateCell(rowOne);
                cellPlan.CellStyle = cellstyle;
                cellPlan.SetCellValue(planName[i]);
                // row.CreateCell(rowOne).SetCellValue(planName[i]);
                rowOne += timu.Count;
            }
            //绘制随访问卷表头
            IRow row4 = sheet1.CreateRow(1);
            int rowTwo = arr.Length;
            int index = 0;
            for (int i = 0; i < planName.Count * fllowName.Count; i++)
            {

                //获取当前问卷下的题目个数
                int r = timuResult[fllowName[index]];
                sheet1.AddMergedRegion(new CellRangeAddress(1, 1, rowTwo, rowTwo + r - 1));
                ICell cellFllow = row4.CreateCell(rowTwo);
                cellFllow.CellStyle = cellstyle;
                cellFllow.SetCellValue(fllowName[index]);
                //row4.CreateCell(rowTwo).SetCellValue(fllowName[index]);
                rowTwo += r;
                index += 1;
                if (index > fllowName.Count - 1)
                {
                    index = 0;
                }
            }
            //绘制问卷题目表头
            //IRow row5 = sheet1.CreateRow(2);
            int index1 = 0;
            for (int i = arr.Length; i < (timuList.Count * planName.Count) + arr.Length; i++)
            {
                ICell cellTimu = row2.CreateCell(i);
                cellTimu.CellStyle = cellstyle;
                cellTimu.SetCellValue(timuList[index1].ToString());

                //  row2.CreateCell(i).SetCellValue(timuList[index1].ToString());
                index1 += 1;
                if (index1 >= timuList.Count - 1)
                {
                    index1 = 0;
                }
            }

 

            }               
            // 写入到客户端   
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            return ms;
        }

 

            //表头数据
            var tablleTitle = await patientBLL.getFllowInfoList(fugid);
            IList<fuquestionbank> tableTil = tablleTitle.OrderBy(x => x.FllowPlan_id).ThenBy(x => x.FollowInfo_Name).ToList();
            //导出
            excelBll excelBll = new BLL.excelBll();
            MemoryStream ms = await excelBll.ExportExcel(tableTil, pId, pfid, fugid);
            string SavaName = DateTime.Now.ToString("yyyyMMddhhmmss");
            return File(ms, "application/vnd.ms-excel", "患者管理" + SavaName + ".xls");

 

//list集合转datatable
   public DataTable IListOut(IList<excelModel> _list)
        {
            DataTable dtReturn = new DataTable();
            PropertyInfo[] oProps = null;
            foreach (excelModel rec in _list)
            {
                if (oProps == null)
                {
                    oProps = ((Type)rec.GetType()).GetProperties();
                    foreach (PropertyInfo pi in oProps)
                    {
                        Type colType = pi.PropertyType; if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                        {
                            colType = colType.GetGenericArguments()[0];
                        }
                        dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
                    }
                }
                DataRow dr = dtReturn.NewRow(); foreach (PropertyInfo pi in oProps)
                {
                    dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue(rec, null);
                }
                dtReturn.Rows.Add(dr);
            }
            return (dtReturn);
        }