NPOI导出百万数据

1、引用最新版的NPOI（2.4.1）

2、调用本人NPOIHelper

3、调用下面的导出helper

#region 药物库存表导出1048576
        [HttpPost]
        public async Task<ApiRes<ExportModelRespon>> ExpDrugListStockExecl([FromBody]ExpDrugListStockRequest modelRequest)
        {
            
            string serverPath = HttpContext.Current.Server.MapPath("/RandResult/");
            Task.Factory.StartNew(() => getDrugListStock(modelRequest, serverPath));
            var baseModel = new ApiRes<ExportModelRespon>()
            {
                Body = new ExportModelRespon(),
                Message = "成功"
            };
            baseModel.Body.model = "";
            return baseModel;
        }

        private async void getDrugListStock(ExpDrugListStockRequest modelRequest, string serverPath)
        {
            Log4net.Error("药物库存表导出开始" + System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));

            var fileInfoModel = _iSystemExportInfoServices.GetSystemExportInfoModel(projectInfoId, userId, (int)ReportTypeEnum.DrugListStockReport);
            if (fileInfoModel != null)
            {
                fileInfoModel.IsSuccess = false;
                if (1 != _iSystemExportInfoServices.UpSystemExportInfo(fileInfoModel))
                {
                    throw new SeeException(5001, "", "");
                }
            }
            else
            {
                fileInfoModel = new TM_SystemExportInfo()
                {
                    Id = Guid.NewGuid().ToString(),
                    CompanyId = companyId,
                    ProjectId = projectId,
                    ProjectInfoId = projectInfoId,
                    FilePath = "",
                    IsSuccess = false,
                    ReportType = (int)ReportTypeEnum.DrugListStockReport,
                    UserId = userId,
                    CreateTime = DateTime.Now,
                };
                if (1 != _iSystemExportInfoServices.AddSystemExportInfo(fileInfoModel))
                {
                    throw new SeeException(5001, "", "");
                }
            }
            #region  获取药物库存表
            string hospitalId = modelRequest.HospitalId;
            string language = modelRequest.Language;
            if (string.IsNullOrEmpty(hospitalId))
            {
                hospitalId = _iDrugListServices.GetHospitalIds(userId, projectInfoId);
            }
            var model = _iDrugListServices.GetDrugListStockAll(projectInfoId, hospitalId, language);
            if (!_iUserGroupServices.IsBlindStatus(userId, projectInfoId))
            {
                var drugListStockModels = model.DrugListStockModels;
                var drugListStockDetailModels = model.DrugListStockDetailModels;
                string baseDrug = LanguageTool.LanguageHelper.GetValue(language, "Code129");//不适用
                foreach (var item in drugListStockModels)
                {
                    item.DrugType = baseDrug;
                    item.DrugBatch = baseDrug;
                }
                foreach (var item in drugListStockDetailModels)
                {
                    item.DrugType = baseDrug;
                    item.BatchNumer = baseDrug;
                    item.ExpiryDate = baseDrug;
                }

            }
            string planNum = string.Empty;
            string projectName = string.Empty;
            string applicant = string.Empty;
            var ProjectMainModels = _iProjectServices.LoadEntities(p => p.ProjectId == projectId).FirstOrDefault();
            if (ProjectMainModels != null)
            {
                planNum = ProjectMainModels.PlanNum;
                projectName = ProjectMainModels.ProjectName;
                applicant = ProjectMainModels.Applicant;
            }
            var projectTitalModel = new ProjectTitleModel
            {
                PlanNum = planNum,
                ProjectName = projectName,
                Applicant = applicant
            };
            model.ProjectTitleModel = projectTitalModel;
            #endregion

            var hospitalName = LanguageTool.LanguageHelper.GetValue(language, "Code543");//库房|中心
            var drugType = LanguageTool.LanguageHelper.GetValue(language, "Code261");//药物治疗类型
            var drugBatch = LanguageTool.LanguageHelper.GetValue(language, "Code262");//批次
            var unpassNum = LanguageTool.LanguageHelper.GetValue(language, "Code252");//未放行
            var useNum = LanguageTool.LanguageHelper.GetValue(language, "Code253");//可用
            var retainNum = LanguageTool.LanguageHelper.GetValue(language, "Code557");//转运保留
            //var val7 = LanguageTool.LanguageHelper.GetValue(language, "Code558");//返还保留
            var sendNum = LanguageTool.LanguageHelper.GetValue(language, "Code259");//运送中
            //var val9 = LanguageTool.LanguageHelper.GetValue(language, "Code540");//返还中
            var freezeNum = LanguageTool.LanguageHelper.GetValue(language, "Code652");//冻结
            var quarantineNum = LanguageTool.LanguageHelper.GetValue(language, "Code256");//隔离
            var issueNum = LanguageTool.LanguageHelper.GetValue(language, "Code254");//已发放
            var backNum = LanguageTool.LanguageHelper.GetValue(language, "Code515");//已回收
            var destroyNum = LanguageTool.LanguageHelper.GetValue(language, "Code541");//已销毁
            var loseNum = LanguageTool.LanguageHelper.GetValue(language, "Code398");//丢失
            var invalidNum = LanguageTool.LanguageHelper.GetValue(language, "Code667");//作废废弃
            var stainedNum = LanguageTool.LanguageHelper.GetValue(language, "Code542");//污损损坏
            var dueNum = LanguageTool.LanguageHelper.GetValue(language, "Code257");//已过期
            Dictionary<string, string> dictionary = new Dictionary<string, string>()
            {
                { hospitalName,"HospitalName"},
                { drugType,"DrugType"},
                { drugBatch,"DrugBatch"},
                { unpassNum,"UnpassNum"},
                { useNum,"UseNum"},
                { retainNum,"RetainNum"},
                { sendNum,"SendNum"},
                { freezeNum,"FreezeNum"},
                { quarantineNum,"QuarantineNum"},
                { issueNum,"IssueNum"},
                { backNum,"BackNum"},
                { destroyNum,"DestroyNum"},
                { loseNum,"LoseNum"},
                { invalidNum,"InvalidNum"},
                { stainedNum,"StainedNum"},
                { dueNum,"DueNum"},
            };
            var drugNum = LanguageTool.LanguageHelper.GetValue(language, "Code497");//药物编号
            var drugTypes = LanguageTool.LanguageHelper.GetValue(language, "Code261");//药物治疗类型
            var batchNumer = LanguageTool.LanguageHelper.GetValue(language, "Code262");//批次
            var expiryDate = LanguageTool.LanguageHelper.GetValue(language, "Code247");//效期
            var hospitalNames = LanguageTool.LanguageHelper.GetValue(language, "Code543");//库房|中心
            var status = LanguageTool.LanguageHelper.GetValue(language, "Code498");//状态
            Dictionary<string, string> dictionarys = new Dictionary<string, string>()
            {
                { drugNum,"DrugNum"},
                { drugTypes,"DrugType"},
                { batchNumer,"BatchNumer"},
                { expiryDate,"ExpiryDate"},
                { hospitalNames,"HospitalName"},
                { status,"Status"},
            };
            List<ExcelNewModel> excelModels = new List<ExcelNewModel>();
            int maxCount = 1048576 - 5; //1048576是EXCEL2007的最大行数，-1是要去掉标题行

            var planNumCell = LanguageTool.LanguageHelper.GetValue(language, "Code121");//方案号
            var projectNameCell = LanguageTool.LanguageHelper.GetValue(language, "Code536");//项目名称
            var applicantCell = LanguageTool.LanguageHelper.GetValue(language, "Code537");//申办方
            var projectTitle = model.ProjectTitleModel;
            var cellDictionarys = new Dictionary<string, string>();
            if (projectTitle != null)
            {
                cellDictionarys.Add(planNumCell, projectTitle.PlanNum);
                cellDictionarys.Add(projectNameCell, projectTitle.ProjectName);
                cellDictionarys.Add(applicantCell, projectTitle.Applicant);
            }
            for (int i = 0; i < 15; i++)
            {
                model.DrugListStockModels.AddRange(model.DrugListStockModels);
            }
            #region 药物库存表
            if (model.DrugListStockModels.Count > maxCount)
            {
                int sheetCount = 0;
                for (int i = 0; i < model.DrugListStockModels.Count; i = i + maxCount)
                {
                    if (i!=0)
                    {
                        cellDictionarys = null;
                    }
                    var thisHospitalListSheet = model.DrugListStockModels.Skip(sheetCount * maxCount).Take(maxCount).ToList();
                    if (i != 0)
                    {
                        maxCount = 1048575;
                        //加1048571（第一张sheet的数据数和其他表的不同）
                        thisHospitalListSheet = model.DrugListStockModels.Skip((sheetCount-1) * maxCount+ 1048571).Take(maxCount).ToList();
                    }
                    var dtHospitalSheet = ToDataTable(thisHospitalListSheet);
                    sheetCount = sheetCount + 1;
                    var excelNewModelSheet = new ExcelNewModel
                    {
                        CellDictionarys= cellDictionarys,
                        FieldDictionarys = dictionary,
                        ResultDataTable = dtHospitalSheet,
                        Title = "DrugListStock-" + sheetCount.ToString(),
                        SheetType = 2
                    };
                    excelModels.Add(excelNewModelSheet);
                }
            }
            else
            {
                var dt = ToDataTable(model.DrugListStockModels);
                var excelNewModel = new ExcelNewModel
                {
                    CellDictionarys = cellDictionarys,
                    FieldDictionarys = dictionary,
                    ResultDataTable = dt,
                    Title = "DrugListStock",
                    SheetType = 2
                };
                excelModels.Add(excelNewModel);
            }
            #endregion



            int maxCounts= 1048576-1;
            for (int i = 0; i < 10; i++)
            {
                model.DrugListStockDetailModels.AddRange(model.DrugListStockDetailModels);
            }
            #region 药物清单
            if (model.DrugListStockDetailModels.Count > maxCounts)
            {
                int sheetCount = 0;
                for (int i = 0; i < model.DrugListStockDetailModels.Count; i = i + maxCounts)
                {
                    var thisHospitalListSheet = model.DrugListStockDetailModels.Skip(sheetCount * maxCounts).Take(maxCounts).ToList();
                    var dtHospitalSheet = ToDataTable(thisHospitalListSheet);
                    dtHospitalSheet.Columns.Remove("DrugOrder");
                    sheetCount = sheetCount + 1;
                    var excelNewModelSheet = new ExcelNewModel
                    {
                        FieldDictionarys = dictionarys,
                        ResultDataTable = dtHospitalSheet,
                        Title = "DrugListStockDetail-" + sheetCount.ToString(),
                        SheetType = 1
                    };
                    excelModels.Add(excelNewModelSheet);
                }
            }
            else
            {
                var dt = ToDataTable(model.DrugListStockDetailModels);
                dt.Columns.Remove("DrugOrder");
                var excelNewModel = new ExcelNewModel
                {
                    FieldDictionarys = dictionarys,
                    ResultDataTable = dt,
                    Title = "DrugListStockDetail",
                    SheetType = 1
                };
                excelModels.Add(excelNewModel);
            }
            #endregion

            string TableName = LanguageTool.LanguageHelper.GetValue(language, "Code567");
            string partUrl = NPOIHelper.GetDrugListStockBig(excelModels, "DrugListStock", serverPath);
            var baseModel = new ApiRes<ExportModelRespon>()
            {
                Body = new ExportModelRespon(),
                Message = "成功"

            };
            baseModel.Body.model = partUrl;
            string strJson = JsonConvert.SerializeObject(baseModel);
            fileInfoModel.IsSuccess = true;
            fileInfoModel.FilePath = partUrl;
            if (1 != _iSystemExportInfoServices.UpSystemExportInfo(fileInfoModel))
            {
                throw new SeeException(5001, "", "");
            }
            Log4net.Error("药物库存表导出结束" + System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + "" + strJson);
        }
        #endregion
        #region 中心药物明细导出1048576


        [HttpPost]
        public async Task<ApiRes<ExportModelRespon>> GetDrugUrl([FromBody]DrugExportRequest modelRequest)
        {

            #region 药物信息Old
            //List<string> rowName = new List<string>();
            //rowName.Add(LanguageTool.LanguageHelper.GetValue(language, "Code497"));  //药物编号
            //rowName.Add(LanguageTool.LanguageHelper.GetValue(language, "Code261")); //药物治疗类型
            //rowName.Add(LanguageTool.LanguageHelper.GetValue(language, "Code262"));//批次
            //rowName.Add(LanguageTool.LanguageHelper.GetValue(language, "Code496")); //有效期
            //rowName.Add(LanguageTool.LanguageHelper.GetValue(language, "Code498")); //状态
            //rowName.Add(LanguageTool.LanguageHelper.GetValue(language, "Code123"));//研究中心/仓库
            //rowName.Add(LanguageTool.LanguageHelper.GetValue(language, "Code563")); //转运日期
            //rowName.Add(LanguageTool.LanguageHelper.GetValue(language, "Code564")); //接收日期
            ////rowName.Add(LanguageTool.LanguageHelper.GetValue(language, "Code566"));//物流单号
            //var valPatientNum = LanguageTool.LanguageHelper.GetValue(language, "Code127");
            //if (IsPatientScreenNoProject(projectInfoId))
            //{
            //    //受试者筛选号
            //    valPatientNum = LanguageTool.LanguageHelper.GetValue(language, "Code659");
            //}
            //rowName.Add(valPatientNum);//研究者
            //rowName.Add(LanguageTool.LanguageHelper.GetValue(language, "Code462"));//随机号
            //rowName.Add(LanguageTool.LanguageHelper.GetValue(language, "Code136")); //访视
            //rowName.Add(LanguageTool.LanguageHelper.GetValue(language, "Code532"));//访视日期
            #endregion
            string serverPath = HttpContext.Current.Server.MapPath("/RandResult/");
            Task.Factory.StartNew(() => getDrugInfos(modelRequest, serverPath));
            var baseModel = new ApiRes<ExportModelRespon>()
            {
                Body = new ExportModelRespon(),
                Message = "成功"
            };
            baseModel.Body.model = "";
            return baseModel;
        }

        private async void getDrugInfos(DrugExportRequest modelRequest, string serverPath)
        {
            Log4net.Error("中心药物明细导出开始" + System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));

            var fileInfoModel = _iSystemExportInfoServices.GetSystemExportInfoModel(projectInfoId, userId, (int)ReportTypeEnum.DrugInfoReport);
            if (fileInfoModel != null)
            {
                fileInfoModel.IsSuccess = false;
                if (1 != _iSystemExportInfoServices.UpSystemExportInfo(fileInfoModel))
                {
                    throw new SeeException(5001, "", "");
                }
            }
            else
            {
                fileInfoModel = new TM_SystemExportInfo()
                {
                    Id = Guid.NewGuid().ToString(),
                    CompanyId = companyId,
                    ProjectId = projectId,
                    ProjectInfoId = projectInfoId,
                    FilePath = "",
                    IsSuccess = false,
                    ReportType = (int)ReportTypeEnum.DrugInfoReport,
                    UserId = userId,
                    CreateTime = DateTime.Now,
                };
                if (1 != _iSystemExportInfoServices.AddSystemExportInfo(fileInfoModel))
                {
                    throw new SeeException(5001, "", "");
                }
            }

            List<DrugAllModel> models = _iDrugServices.GetModelListByAllExport(modelRequest.HospitalId,
               modelRequest.DrugType, modelRequest.DrugBatch, modelRequest.Status, modelRequest.DrugNumList, userId, projectInfoId);
            string language = modelRequest.Language;
            if (!_iUserGroupServices.IsBlindStatus(userId, projectInfoId))
            {
                foreach (var item in models)
                {
                    item.DrugType = LanguageTool.LanguageHelper.GetValue(language, "Code129"); // "不适用"
                    item.DrugBatchNumber = LanguageTool.LanguageHelper.GetValue(language, "Code129"); // "不适用"
                    item.ExpiyDateTime = LanguageTool.LanguageHelper.GetValue(language, "Code129");// "不适用"
                }
            }
            List<DrugAllExportModel> list = new List<DrugAllExportModel>();
            foreach (var model in models)
            {
                DrugAllExportModel m = new DrugAllExportModel()
                {
                    DrugNum = model.DrugNum,
                    DrugType = model.DrugType,
                    DrugBatchNumber = model.DrugBatchNumber,
                    ExpiyDateTime = model.ExpiyDateTime,
                    Status = LanguageTool.LanguageHelper.GetEnglishValueByFiledName(language, model.Status),
                    HospitalName = model.HospitalName,
                    TransportDate = model.TransportDate,
                    ReceivedDate = model.ReceivedDate,
                    Researcher = model.PatNum,
                    RandomNum = model.RandomNum,
                    ViewName = model.ViewName,
                    SendDrugTime = model.SendDrugTime
                };
                list.Add(m);
            }
            //DataTable dt = ToDataTable<DrugAllExportModel>(list);

            string drugNo= LanguageTool.LanguageHelper.GetValue(language, "Code497");  //药物编号1
            string drugType= LanguageTool.LanguageHelper.GetValue(language, "Code261"); //药物治疗类型2
            string lot= LanguageTool.LanguageHelper.GetValue(language, "Code262");//批次3
            string expiryData= LanguageTool.LanguageHelper.GetValue(language, "Code496"); //有效期4
            string status= LanguageTool.LanguageHelper.GetValue(language, "Code498"); //状态5
            string hospitalName = LanguageTool.LanguageHelper.GetValue(language, "Code123");//研究中心/仓库6
            string deliveryDate= LanguageTool.LanguageHelper.GetValue(language, "Code563"); //转运日期7
            string receivingDate= LanguageTool.LanguageHelper.GetValue(language, "Code564"); //接收日期8
            //rowName.Add(LanguageTool.LanguageHelper.GetValue(language, "Code566"));//物流单号
            string valPatientNum = LanguageTool.LanguageHelper.GetValue(language, "Code127");//9
            if (IsPatientScreenNoProject(projectInfoId))
            {
                //受试者筛选号
                valPatientNum = LanguageTool.LanguageHelper.GetValue(language, "Code659");
            }
            string randomNum= LanguageTool.LanguageHelper.GetValue(language, "Code462");//随机号10
            string visit= LanguageTool.LanguageHelper.GetValue(language, "Code136"); //访视11
            string dispensationTime= LanguageTool.LanguageHelper.GetValue(language, "Code532");//访视日期12
            Dictionary<string, string> dictionary = new Dictionary<string, string>()
            {
                { drugNo,"DrugNum"},
                { drugType,"DrugType"},
                { lot,"DrugBatchNumber"},
                { expiryData,"ExpiyDateTime"},
                { status,"Status"},
                { hospitalName,"HospitalName"},
                { deliveryDate,"TransportDate"},
                { receivingDate,"ReceivedDate"},
                { valPatientNum,"Researcher"},
                { randomNum,"RandomNum"},
                { visit,"ViewName"},
                { dispensationTime,"SendDrugTime"}
            };
            List<ExcelNewModel> excelModels = new List<ExcelNewModel>();
            int maxCount = 1048576 - 5; //1048576是EXCEL2007的最大行数，-5是要去掉标题行
            for (int i = 0; i < 13; i++)
            {
                list.AddRange(list);
            }
            if (list.Count > maxCount)
            {
                int sheetCount = 0;
                for (int i = 0; i < list.Count; i = i + maxCount)
                {
                    var thisHospitalListSheet = list.Skip(sheetCount * maxCount).Take(maxCount).ToList();
                    if (i!=0)
                    {
                        maxCount = 1048575;
                        //加1048571（第一张sheet的数据数和其他表的不同）
                        thisHospitalListSheet = list.Skip((sheetCount - 1) * maxCount+ 1048571).Take(maxCount).ToList();
                    }  
                    var dtHospitalSheet = ToDataTable(thisHospitalListSheet);
                    sheetCount = sheetCount + 1;
                    var excelNewModelSheet = new ExcelNewModel
                    {
                        FieldDictionarys = dictionary,
                        ResultDataTable = dtHospitalSheet,
                        Title = "DrugInfo-" + sheetCount.ToString(),
                    };
                    excelModels.Add(excelNewModelSheet);
                }
            }
            else
            {
                var dt = ToDataTable(list);
                var excelNewModel = new ExcelNewModel
                {
                    FieldDictionarys = dictionary,
                    ResultDataTable = dt,
                    Title = "DrugInfo"
                };
                excelModels.Add(excelNewModel);
            }
            string TableName = LanguageTool.LanguageHelper.GetValue(language, "Code567");
            #region 获取项目信息
            var ProjectMainModels = new TMProjectModel();
            if (string.IsNullOrEmpty(companyToken))
            {
                TM_Project project = _iProjectServices.GetByTopOne();
                ProjectMainModels = _iProjectServices.GetProjectById(project == null ? "" : project.ProjectId);
                Log4net.Error("company:None");
            }
            else
            {
                ProjectMainModels = _iProjectServices.GetProjectById(projectId);
            }
            #endregion 

            //string partUrl = NPOIHelper.getUrlForDrugDetail(dt, rowName, TableName, ProjectMainModels.ProjectName, LanguageTool.LanguageHelper.GetValue(language, "Code536"), LanguageTool.LanguageHelper.GetValue(language, "Code568"));
            string partUrl = NPOIHelper.GetDrugInfoUrlBig(excelModels, TableName, serverPath, ProjectMainModels.ProjectName, LanguageTool.LanguageHelper.GetValue(language, "Code536"), LanguageTool.LanguageHelper.GetValue(language, "Code568"));
            var baseModel = new ApiRes<ExportModelRespon>()
            {
                Body = new ExportModelRespon(),
                Message = "成功"

            };
            baseModel.Body.model = partUrl;
            string strJson = JsonConvert.SerializeObject(baseModel);
            fileInfoModel.IsSuccess = true;
            fileInfoModel.FilePath = partUrl;
            if (1 != _iSystemExportInfoServices.UpSystemExportInfo(fileInfoModel))
            {
                throw new SeeException(5001, "", "");
            }
            Log4net.Error("中心药物明细导出结束" + System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff") + "" + strJson);
        }
        #endregion

 

4、

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Dynamic;
using System.IO;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Text;
using System.Web;
using NPOI.HSSF.Util;
using NPOI.SS.Util;
using TM.Medical.ToolUtility.Tools;
using NPOI.XSSF.Streaming;

namespace UtilityNPOI
{
    public class NPOIHelper
    {
        private HSSFWorkbook hssfworkbook;
        private ISheet sheet1;

        public void BuildExcel()
        {
            hssfworkbook = new HSSFWorkbook();
            // 新建一个Excel页签
            sheet1 = hssfworkbook.CreateSheet("Sheet1");

            // 创建新增行
            for (var i = 0; i < 10; i++)
            {
                IRow row1 = sheet1.CreateRow(i);
                for (var j = 0; j < 10; j++)
                {
                    //新建单元格
                    ICell cell = row1.CreateCell(j);
                    // 单元格赋值
                    cell.SetCellValue(string.Format("{0}{1}", "单元格", j.ToString()));
                }
            }
            // 设置行宽度
            sheet1.SetColumnWidth(2, 10*256);
            // 获取单元格 并设置样式
            ICellStyle styleCell = hssfworkbook.CreateCellStyle();
            //居中
            styleCell.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            //垂直居中
            styleCell.VerticalAlignment = VerticalAlignment.Top;
            ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
            //设置字体
            IFont fontColorRed = hssfworkbook.CreateFont();
            fontColorRed.Color = HSSFColor.OliveGreen.Red.Index;
            styleCell.SetFont(fontColorRed);
            sheet1.GetRow(2).GetCell(2).CellStyle = styleCell;
            // 合并单元格
            sheet1.AddMergedRegion(new CellRangeAddress(2, 4, 2, 5));
            // 输出Excel
            string filename = "patient" + DateTime.Now.ToString("yy-MM-dd hh:mm:ss") + ".xls";
            var context = HttpContext.Current;
            context.Response.ContentType = "application/vnd.ms-excel";
            context.Response.AddHeader("Content-Disposition",
                string.Format("attachment;filename={0}", context.Server.UrlEncode(filename)));
            context.Response.Clear();

            MemoryStream file = new MemoryStream();
            hssfworkbook.Write(file);
            context.Response.BinaryWrite(file.GetBuffer());
            context.Response.End();
        }

        public static string getUrl(DataTable dtsource, List<string> rowName,string fileName)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("block");
            ICellStyle dateStyle = workbook.CreateCellStyle();
            IDataFormat format = workbook.CreateDataFormat();

            int rowCount = dtsource.Rows.Count + 1;
            int cellCount = dtsource.Columns.Count;
            Create(sheet, rowCount, cellCount);

            //for (int i = 0; i < dtsource.Columns.Count; i++)
            //{
            //    string cellValue = dtsource.Columns[i].ColumnName.ToString();
            //    sheet.GetRow(0).GetCell(i).SetCellValue(cellValue);
            //}
            for (int i = 0; i < rowName.Count; i++)
            {
                string cellValue = rowName[i].ToString();
                sheet.GetRow(0).GetCell(i).SetCellValue(cellValue);
            }

            for (int i = 0; i < dtsource.Rows.Count; i++)
            {
                int row = i + 1;
                for (int j = 0; j < dtsource.Columns.Count; j++)
                {
                    string cellValue = dtsource.Rows[i][j].ToString();
                    sheet.GetRow(row).GetCell(j).SetCellValue(cellValue);
                }
            }
           
            string filepath = "/export/";
            var patUrl = HttpContext.Current.Server.MapPath("/RandResult/");
            if (!FileHelperTools.IsExistDirectory(patUrl))
            {
                FileHelperTools.CreateDirectory(patUrl);
            }
            var partName = UniqueKeyGenerator.GetDtRng_RandomNo();
            using (FileStream fs = File.OpenWrite(@""+patUrl+fileName+"-"+ partName + ".xls"))
                //打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件！
            {
                workbook.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。
            }

            return "/RandResult/"+fileName+"-"+partName + ".xls";
        }
        public static string getUrl(DataTable dtsource, List<string> rowName, string fileName, List<string> relations,
            List<string> relationNames)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("block");
            ICellStyle dateStyle = workbook.CreateCellStyle();
            IDataFormat format = workbook.CreateDataFormat();

            int rowCount = dtsource.Rows.Count + 6;
            int cellCount = dtsource.Columns.Count;
            Create(sheet, rowCount, cellCount);

            for (int i = 0; i < relationNames.Count(); i++)
            {
                int row = i;
                sheet.GetRow(row).GetCell(0).SetCellValue(relationNames[i].ToString());
            }
            for (int i = 0; i < relations.Count(); i++)
            {
                int row = i;
                sheet.GetRow(row).GetCell(1).SetCellValue(relations[i].ToString());
            }

            for (int i = 0; i < rowName.Count; i++)
            {
                string cellValue = rowName[i].ToString();
                sheet.GetRow(5).GetCell(i).SetCellValue(cellValue);
            }

            for (int i = 0; i < dtsource.Rows.Count; i++)
            {
                int row = i + 6;
                for (int j = 0; j < dtsource.Columns.Count; j++)
                {
                    string cellValue = dtsource.Rows[i][j].ToString();
                    sheet.GetRow(row).GetCell(j).SetCellValue(cellValue);
                }
            }

            string filepath = "/export/";
            var patUrl = HttpContext.Current.Server.MapPath("/RandResult/");
            if (!FileHelperTools.IsExistDirectory(patUrl))
            {
                FileHelperTools.CreateDirectory(patUrl);
            }
            var partName = UniqueKeyGenerator.GetDtRng_RandomNo();
            using (FileStream fs = File.OpenWrite(@"" + patUrl + fileName + "-" + partName + ".xls"))
            //打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件！
            {
                workbook.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。
            }

            return "/RandResult/" + fileName + "-" + partName + ".xls";
        }

        public static string GetUrl(List<ExcelModel> excelModels, string fileName)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
            foreach (var excelModel in excelModels)
            {
                var dtSource = excelModel.ResultDataTable;
                var cellNames = excelModel.CellNames;
                var cellDictionarys = excelModel.CellDictionarys;
                var title = excelModel.Title;
                NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(title);
                ICellStyle dateStyle = workbook.CreateCellStyle();
                IDataFormat format = workbook.CreateDataFormat();
                int icount = 1;
                if (cellDictionarys.Count > 0)
                {
                    icount = 3;
                }
                int rowCount = dtSource.Rows.Count+ cellDictionarys.Count + icount;
                int cellCount = dtSource.Columns.Count;
                Create(sheet, rowCount, cellCount);
                int index = 0;
                foreach (var cellDictionary in cellDictionarys)
                {
                    var key = cellDictionary.Key;
                    var value = cellDictionary.Value;
                    sheet.GetRow(index).GetCell(0).SetCellValue(key);
                    sheet.GetRow(index).GetCell(1).SetCellValue(value);
                    index++;
                }
                if (cellDictionarys.Count > 0)
                {
                    index += 2;
                }

                for (int i = 0; i < cellNames.Count; i++)
                {
                    string cellValue = cellNames[i].ToString();
                    sheet.GetRow(index).GetCell(i).SetCellValue(cellValue);
                }

                for (int i = 0; i < dtSource.Rows.Count; i++)
                {
                    int row = index + i + 1;
                    for (int j = 0; j < dtSource.Columns.Count; j++)
                    {
                        string cellValue = dtSource.Rows[i][j].ToString();
                        sheet.GetRow(row).GetCell(j).SetCellValue(cellValue);
                    }
                }

            }
            var patUrl = HttpContext.Current.Server.MapPath("/RandResult/");
            if (!FileHelperTools.IsExistDirectory(patUrl))
            {
                FileHelperTools.CreateDirectory(patUrl);
            }
            var partName = UniqueKeyGenerator.GetDtRng_RandomNo();
            using (FileStream fs = File.OpenWrite(@"" + patUrl + fileName + "-" + partName + ".xls"))
            {
                workbook.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。
                workbook.Close();
            }

            return "/RandResult/" + fileName + "-" + partName + ".xls";
        }
        public static string GetUrl(List<ExcelNewModel> excelModels, string fileName)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
            foreach (var excelModel in excelModels)
            {
                var dtSource = excelModel.ResultDataTable;
                var fieldDictionarys = excelModel.FieldDictionarys;
                var title = excelModel.Title;
                NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(title);
                ICellStyle dateStyle = workbook.CreateCellStyle();
                IDataFormat format = workbook.CreateDataFormat();
                int rowCount = dtSource.Rows.Count + 1;
                int cellCount = fieldDictionarys.Count;
                Create(sheet, rowCount, cellCount);
                int index = 0;
                int k = 0;
                foreach (var fieldDictionary in fieldDictionarys)
                {
                    string cellValue = fieldDictionary.Key;
                    sheet.GetRow(index).GetCell(k).SetCellValue(cellValue);
                    k++;
                }

                for (int i = 0; i < dtSource.Rows.Count; i++)
                {
                    int row = index + i+1;
                    int j = 0;
                    foreach (var fieldDictionary in fieldDictionarys)
                    {
                        string fieldName = fieldDictionary.Value;
                        string cellValue = dtSource.Rows[i][fieldName].ToString();
                        sheet.GetRow(row).GetCell(j).SetCellValue(cellValue);
                        j++;
                    }
                }

            }
            var patUrl = HttpContext.Current.Server.MapPath("/RandResult/");
            if (!FileHelperTools.IsExistDirectory(patUrl))
            {
                FileHelperTools.CreateDirectory(patUrl);
            }
            var partName = UniqueKeyGenerator.GetDtRng_RandomNo();
            using (FileStream fs = File.OpenWrite(@"" + patUrl + fileName + "-" + partName + ".xls"))
            {
                workbook.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。
                workbook.Close();
            }

            return "/RandResult/" + fileName + "-" + partName + ".xls";
        }


        public static string getUrlNew(DataTable dtsource, List<string> rowName, string fileName, List<string> relations,
            List<string> relationNames,string title, DataTable dtsource2, List<string> rowName2,string title2)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(title);
            ICellStyle dateStyle = workbook.CreateCellStyle();
            IDataFormat format = workbook.CreateDataFormat();

            int rowCount = dtsource.Rows.Count + 6;
            int cellCount = dtsource.Columns.Count;
            Create(sheet, rowCount, cellCount);
            //sheet.GetRow(0).GetCell(dtsource.Columns.Count/2).SetCellValue(title);
            for (int i = 0; i < relationNames.Count(); i++)
            {
                int row = i;
                sheet.GetRow(row).GetCell(0).SetCellValue(relationNames[i].ToString());
            }
            for (int i = 0; i < relations.Count(); i++)
            {
                int row = i;
                sheet.GetRow(row).GetCell(1).SetCellValue(relations[i].ToString());
            }
            for (int i = 0; i < rowName.Count; i++)
            {
                string cellValue = rowName[i].ToString();
                sheet.GetRow(5).GetCell(i).SetCellValue(cellValue);
            }

            for (int i = 0; i < dtsource.Rows.Count; i++)
            {
                int row = i + 6;
                for (int j = 0; j < dtsource.Columns.Count; j++)
                {
                    string cellValue = dtsource.Rows[i][j].ToString();
                    sheet.GetRow(row).GetCell(j).SetCellValue(cellValue);
                }
            }
            int rowCount2 = dtsource2.Rows.Count+1;
            int cellCount2 = dtsource2.Columns.Count;
            NPOI.SS.UserModel.ISheet sheet2 = workbook.CreateSheet(title2);
            Create(sheet2, rowCount2, cellCount2);
            for (int i = 0; i < rowName2.Count; i++)
            {
                string cellValue = rowName2[i].ToString();
                sheet2.GetRow(0).GetCell(i).SetCellValue(cellValue);
            }

            for (int i = 0; i < dtsource2.Rows.Count; i++)
            {
                int row = i + 1;
                for (int j = 0; j < dtsource2.Columns.Count; j++)
                {
                    string cellValue = dtsource2.Rows[i][j].ToString();
                    sheet2.GetRow(row).GetCell(j).SetCellValue(cellValue);
                }
            }

            string filepath = "/export/";
            var patUrl = HttpContext.Current.Server.MapPath("/RandResult/");
            var partName = UniqueKeyGenerator.GetDtRng_RandomNo();
            using (FileStream fs = File.OpenWrite(@"" + patUrl + fileName + "-" + partName + ".xls"))
            //打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件！
            {
                workbook.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。
            }

            return "/RandResult/" + fileName + "-" + partName + ".xls";
        }
        /// <summary>
        /// 创建单元格
        /// </summary>
        /// <param name="sheet">表</param>
        /// <param name="rowcount">行数</param>
        /// <param name="cellcount">列数</param>
        /// <returns>返回表</returns>
        public static ISheet Create(ISheet sheet, int rowcount, int cellcount)
        {
            for (int i = 0; i < rowcount; i++)
            {
                NPOI.SS.UserModel.IRow targetRow = null;
                targetRow = sheet.CreateRow(i);
                for (int j = 0; j < cellcount; j++)
                {
                    NPOI.SS.UserModel.IRow row = sheet.GetRow(i);
                    NPOI.SS.UserModel.ICell cell = row.CreateCell(j);
                }
            }
            return sheet;
        }

        public static MemoryStream CommonBaseExcel(DataTable dtsource)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("test_01");
            ICellStyle dateStyle = workbook.CreateCellStyle();
            IDataFormat format = workbook.CreateDataFormat();

            int rowCount = dtsource.Rows.Count + 1;
            int cellCount = dtsource.Columns.Count;
            Create(sheet, rowCount, cellCount);

            for (int i = 0; i < dtsource.Columns.Count; i++)
            {
                string cellValue = dtsource.Columns[i].ColumnName.ToString();
                sheet.GetRow(0).GetCell(i).SetCellValue(cellValue);
            }
            for (int i = 0; i < dtsource.Rows.Count; i++)
            {
                int row = i + 1;
                for (int j = 0; j < dtsource.Columns.Count; j++)
                {
                    string cellValue = dtsource.Rows[i][j].ToString();
                    sheet.GetRow(row).GetCell(j).SetCellValue(cellValue);
                }

            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;

                workbook = null;
                //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
                return ms;
            }
        }

        public static DataSet GetImportExcel(string filePath)
        {
            string fileExt = Path.GetExtension(filePath);
            IWorkbook workbook;

            #region 初始化信息(兼容Excel2003、Excel2007)

            using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                if (fileExt == ".xls")
                {
                    workbook = new HSSFWorkbook(file);
                }
                else if (fileExt == ".xlsx")
                {
                    workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(file);
                }
                else
                {
                    return null;
                }
            }

            #endregion

            DataSet ds = new DataSet();


            int sheetLenth = workbook.NumberOfSheets;

            for (int m = 0; m < sheetLenth; m++)
            {
                ISheet sheet = workbook.GetSheetAt(m);
                string tableName = workbook.GetSheetName(m);
                int colCount = 0; //最大列数

                for (int i = 0; i < sheet.PhysicalNumberOfRows; i++)
                {
                    var rowObj = sheet.GetRow(i);
                    if (rowObj != null)
                        if (rowObj.LastCellNum > colCount)
                            colCount = rowObj.LastCellNum;
                }

                #region 将数据加载到内容表里

                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                DataTable dt = new DataTable();
                int index = 0;
                while (rows.MoveNext())
                {
                    IRow row;
                    if (fileExt == ".xls") //兼容Excel2003
                    {
                        row = (HSSFRow) rows.Current;
                    }
                    else if (fileExt == ".xlsx") //兼容Excel2007
                    {
                        row = (NPOI.XSSF.UserModel.XSSFRow) rows.Current;
                    }
                    else
                    {
                        row = null;
                    }
                    if (row != null)
                    {
                        index++;
                        if (index.Equals(1))
                        {
                            for (int j = 0; j < colCount; j++)
                            {
                                dt.Columns.Add(Convert.ToChar(((int) 'A') + j).ToString());
                            }
                        }

                    }

                    DataRow dr = dt.NewRow();

                    for (int i = 0; i < row.LastCellNum; i++)
                    {
                        ICell cell = row.GetCell(i);


                        if (cell == null)
                        {
                            dr[i] = null;
                        }
                        else
                        {
                            dr[i] = cell.ToString().Trim();
                        }
                    }
                    dt.Rows.Add(dr);
                }
                dt.TableName = tableName;
                ds.Tables.Add(dt);

                #endregion
            }
            return ds;
        } 
        public static DataSet GetImportExcelNew(Stream fs, string fileExt)
        {

            IWorkbook workbook;

            #region 初始化信息(兼容Excel2003、Excel2007)

            if (fileExt == ".xls")
            {
                workbook = new HSSFWorkbook(fs);
            }
            else if (fileExt == ".xlsx")
            {
                workbook = new XSSFWorkbook(fs);
            }
            else
            {
                return null;
            }

            #endregion

            DataSet ds = new DataSet();


            int sheetLenth = workbook.NumberOfSheets;

            for (int m = 0; m < sheetLenth; m++)
            {
                ISheet sheet = workbook.GetSheetAt(m);
                string tableName = workbook.GetSheetName(m);
                int colCount = 0; //最大列数

                for (int i = 0; i < sheet.PhysicalNumberOfRows; i++)
                {
                    var rowObj = sheet.GetRow(i);
                    if (rowObj != null)
                        if (rowObj.LastCellNum > colCount)
                            colCount = rowObj.LastCellNum;
                }

                #region 将数据加载到内容表里

                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                DataTable dt = new DataTable();
                int index = 0;
                while (rows.MoveNext())
                {
                    IRow row;
                    if (fileExt == ".xls") //兼容Excel2003
                    {
                        row = (HSSFRow) rows.Current;
                    }
                    else if (fileExt == ".xlsx") //兼容Excel2007
                    {
                        row = (NPOI.XSSF.UserModel.XSSFRow) rows.Current;
                    }
                    else
                    {
                        row = null;
                    }
                    if (row != null)
                    {
                        index++;
                        if (index.Equals(1))
                        {
                            for (int j = 0; j < colCount; j++)
                            {
                                dt.Columns.Add(Convert.ToChar(((int) 'A') + j).ToString());
                            }
                        }

                    }

                    DataRow dr = dt.NewRow();

                    for (int i = 0; i < row.LastCellNum; i++)
                    {
                        ICell cell = row.GetCell(i);


                        if (cell == null)
                        {
                            dr[i] = null;
                        }
                        else
                        {
                            dr[i] = cell.ToString().Trim();
                        }
                    }
                    dt.Rows.Add(dr);
                }
                dt.TableName = tableName;
                ds.Tables.Add(dt);

                #endregion
            }
            return ds;
        } 
        public static DataTable ReadHead(string path)
        {
            var patUrl2 = HttpContext.Current.Server.MapPath("");
            var patUrl = HttpContext.Current.Server.MapPath("/");
            var patUrl3 = HttpContext.Current.Server.MapPath("/upload/");
            string fileName =patUrl+path;  //路径
            FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate); //读取文件流
            HSSFWorkbook workbook = new HSSFWorkbook(fs);  //根据EXCEL文件流初始化工作簿
            var sheet1 = workbook.GetSheetAt(0); //获取第一个sheet
            DataTable table = new DataTable();//
            var row1 = sheet1.GetRow(0);//获取第一行即标头
            int cellCount = row1.LastCellNum; //第一行的列数
            //把第一行的数据添加到datatable的列名
            for (int i = row1.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new DataColumn(row1.GetCell(i).StringCellValue);
                if(!string.IsNullOrEmpty(row1.GetCell(i).StringCellValue))
                table.Columns.Add(column);
            }
            workbook = null; //清空工作簿--释放资源
            sheet1 = null;  //清空sheet</pre><br>
            return table;
        } 
        public static DataTable ReadExcelAll(string path)
        {
            //string fileName = @"e:\myxls.xls";  //路径
            var patUrl = HttpContext.Current.Server.MapPath("/");
            string fileName = patUrl + path;
            FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate); //读取文件流
            HSSFWorkbook workbook = new HSSFWorkbook(fs);  //根据EXCEL文件流初始化工作簿
            var sheet1 = workbook.GetSheetAt(0); //获取第一个sheet
            DataTable table = new DataTable();//
            var row1 = sheet1.GetRow(0);//获取第一行即标头
            int cellCount = row1.LastCellNum; //第一行的列数
            //把第一行的数据添加到datatable的列名
            for (int i = row1.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new DataColumn(row1.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }
            int rowCount = sheet1.LastRowNum; //总行数
            //把每行数据添加到datatable中
            for (int i = (sheet1.FirstRowNum + 1); i <= sheet1.LastRowNum; i++)
            {
                HSSFRow row = (HSSFRow)sheet1.GetRow(i);
                
                if (row.GetCell(0) != null)
                { 
                    DataRow dataRow = table.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        ICell cell = row.GetCell(j);
                        if (cell == null)
                        {
                            dataRow[j] = null;
                        }
                        else
                        {
                            if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell))
                            {
                                //cell.SetCellType(CellType.String);
                                //int sums = cell.ToString().Length;
                                if (cell.ToString().Length>7)
                                {
                                    dataRow[j] = cell.DateCellValue.ToString("yyyy/MM/dd");
                                }
                                else
                                {
                                    dataRow[j] = cell.DateCellValue.ToString("yyyy/MM");
                                }
                                
                            }
                            else
                            {
                                dataRow[j] = cell.ToString().Trim();
                            }
                        }
                        //if (row.GetCell(j) != null)
                        //    dataRow[j] = row.GetCell(j).ToString();
                    }

                    if (!string.IsNullOrEmpty(row.GetCell(0).ToString()))
                        table.Rows.Add(dataRow);
                }
            }
            //到这里 table 已经可以用来做数据源使用了
            workbook = null; //清空工作簿--释放资源
            sheet1 = null;  //清空sheet</pre><br>
            return table;
        }

        public static string getUrlForDrugDetail(DataTable dtsource, List<string> rowName, string fileName, string projectName,string projectTitle,string generatedTime )
        {
            NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("block");
            ICellStyle dateStyle = workbook.CreateCellStyle();
            IDataFormat format = workbook.CreateDataFormat();

            int rowCount = dtsource.Rows.Count + 1 + 4;
            int cellCount = dtsource.Columns.Count;

            //设置样式  居中 字体
            ICellStyle style0 = workbook.CreateCellStyle();
            style0.VerticalAlignment = VerticalAlignment.Center;
            style0.Alignment = HorizontalAlignment.Center;
            IFont font1 = workbook.CreateFont();
            font1.FontHeight = 15 * 15;
            font1.IsBold = true;
            style0.SetFont(font1);

            ICellStyle style = workbook.CreateCellStyle();
            style.VerticalAlignment = VerticalAlignment.Center;
            style.Alignment = HorizontalAlignment.Center;
            IFont font = workbook.CreateFont();
            style.SetFont(font);

            ICellStyle style2 = workbook.CreateCellStyle();
            IFont font2 = workbook.CreateFont();
            font2.FontHeight = 15 * 15;
            font1.IsBold = true;
            style2.SetFont(font2);

            Create(sheet, rowCount, cellCount);
            //建创行
            ICell cell0 = sheet.GetRow(0).GetCell(0);
            cell0.SetCellValue(fileName); //药物明细表
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, cellCount));
            cell0.CellStyle = style0;

            ICell cell1 = sheet.GetRow(1).GetCell(0);
            cell1.SetCellValue(projectTitle + "：" + projectName);//项目名称
            sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, cellCount));
            cell1.CellStyle = style2;

            ICell cell2 = sheet.GetRow(2).GetCell(0);
            cell2.SetCellValue(generatedTime+"：" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"));//生成时间
            sheet.AddMergedRegion(new CellRangeAddress(2, 2, 0, cellCount));
            cell2.CellStyle = style2;

            ICell cell3 = sheet.GetRow(3).GetCell(0);
            cell3.SetCellValue("");
            sheet.AddMergedRegion(new CellRangeAddress(3, 3, 0, cellCount));

            for (int i = 0; i < rowName.Count; i++)
            {
                string cellValue = rowName[i].ToString();
                ICell cell = sheet.GetRow(4).GetCell(i);
                cell.SetCellValue(cellValue);
                cell.CellStyle = style0;
            }

            for (int i = 0; i < dtsource.Rows.Count; i++)
            {
                int row = i + 1 + 4;
                for (int j = 0; j < dtsource.Columns.Count; j++)
                {
                    string cellValue = dtsource.Rows[i][j].ToString();
                    ICell cell = sheet.GetRow(row).GetCell(j);
                    cell.SetCellValue(cellValue);
                    cell.CellStyle = style;
                }
            }
            string filepath = "/export/";
            if (Directory.Exists(HttpContext.Current.Server.MapPath("/RandResult/")) == false)//如果不存在就创建file文件夹
            {
                Directory.CreateDirectory(HttpContext.Current.Server.MapPath("/RandResult/"));
            }
            var patUrl = HttpContext.Current.Server.MapPath("/RandResult/");
            var partName = UniqueKeyGenerator.GetDtRng_RandomNo();
            using (FileStream fs = File.OpenWrite(@"" + patUrl + fileName + "-" + partName + ".xls"))
            //打开一个xls文件，如果没有则自行创建，如果存在myxls.xls文件则在创建是不要打开该文件！
            {
                workbook.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。
            }

            return "/RandResult/" + fileName + "-" + partName + ".xls";
        }


        /// <summary>
        /// 导出中心药物明细
        /// </summary>
        /// <param name="excelModels"></param>
        /// <param name="fileName"></param>
        /// <param name="serverPath"></param>
        /// <returns></returns>
        public static string GetDrugInfoUrlBig(List<ExcelNewModel> excelModels, string fileName, string serverPath,string projectName, string projectTitle, string generatedTime)
        {
            SXSSFWorkbook sxssfBook = new SXSSFWorkbook();
            sxssfBook.RandomAccessWindowSize = 1000;
            int sheetIndex = 0;
            foreach (var excelModel in excelModels)
            {
                var dtSource = excelModel.ResultDataTable;
                var fieldDictionarys = excelModel.FieldDictionarys;
                var title = excelModel.Title;
                NPOI.SS.UserModel.ISheet sheet = sxssfBook.CreateSheet(title);
                ICellStyle dateStyle = sxssfBook.CreateCellStyle();
                IDataFormat format = sxssfBook.CreateDataFormat();

                if (sheetIndex==0)
                {
                    int rowCounts = dtSource.Rows.Count + 1 + 4;
                    int cellCounts = dtSource.Columns.Count;

                    //设置样式  居中 字体
                    ICellStyle style0 = sxssfBook.CreateCellStyle();
                    style0.VerticalAlignment = VerticalAlignment.Center;
                    style0.Alignment = HorizontalAlignment.Center;
                    IFont font1 = sxssfBook.CreateFont();
                    font1.FontHeight = 15 * 15;
                    font1.IsBold = true;
                    style0.SetFont(font1);

                    ICellStyle style = sxssfBook.CreateCellStyle();
                    style.VerticalAlignment = VerticalAlignment.Center;
                    style.Alignment = HorizontalAlignment.Center;
                    IFont font = sxssfBook.CreateFont();
                    style.SetFont(font);

                    ICellStyle style2 = sxssfBook.CreateCellStyle();
                    IFont font2 = sxssfBook.CreateFont();
                    font2.FontHeight = 15 * 15;
                    font1.IsBold = true;
                    style2.SetFont(font2);

                    Create(sheet, 5, cellCounts);
                    //建创行
                    ICell cell0 = sheet.GetRow(0).GetCell(0);
                    cell0.SetCellValue(fileName); //药物明细表
                    sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, cellCounts));
                    cell0.CellStyle = style0;

                    ICell cell1 = sheet.GetRow(1).GetCell(0);
                    cell1.SetCellValue(projectTitle + "：" + projectName);//项目名称
                    sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, cellCounts));
                    cell1.CellStyle = style2;

                    ICell cell2 = sheet.GetRow(2).GetCell(0);
                    cell2.SetCellValue(generatedTime + "：" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"));//生成时间
                    sheet.AddMergedRegion(new CellRangeAddress(2, 2, 0, cellCounts));
                    cell2.CellStyle = style2;

                    ICell cell3 = sheet.GetRow(3).GetCell(0);
                    cell3.SetCellValue("");
                    sheet.AddMergedRegion(new CellRangeAddress(3, 3, 0, cellCounts));

                }
                else
                {
                    int cellCounts = dtSource.Columns.Count;
                    Create(sheet, 1, cellCounts);
                }
                int rowCount = dtSource.Rows.Count + 1;
                int cellCount = fieldDictionarys.Count;
                //Create(sheet, 1, cellCount);
                int index = 0;
                if (sheetIndex==0)
                {
                    index = 4;
                }
                int k = 0;
                foreach (var fieldDictionary in fieldDictionarys)
                {
                    string cellValue = fieldDictionary.Key;
                    sheet.GetRow(index).GetCell(k).SetCellValue(cellValue);
                    k++;
                }

                for (int i = 0; i < dtSource.Rows.Count; i++)
                {
                    int row = index + i + 1;
                    int j = 0;
                    IRow currtRow = sheet.CreateRow(row);
                    foreach (var fieldDictionary in fieldDictionarys)
                    {
                        ICell cell = currtRow.CreateCell(j);
                        string fieldName = fieldDictionary.Value;
                        string cellValue = dtSource.Rows[i][fieldName].ToString();
                        sheet.GetRow(row).GetCell(j).SetCellValue(cellValue);
                        j++;
                    }
                }
                sheetIndex++;
            }
            var patUrl = serverPath;
            if (!FileHelperTools.IsExistDirectory(patUrl))
            {
                FileHelperTools.CreateDirectory(patUrl);
            }
            var partName = UniqueKeyGenerator.GetDtRng_RandomNo();
            using (FileStream fileStream = File.Open(@"" + patUrl + fileName + "-" + partName + ".xlsx", FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite))
            {
                sxssfBook.Write(fileStream);
                fileStream.Close();
            }
            return "/RandResult/" + fileName + "-" + partName + ".xlsx";
        }


        /// <summary>
        /// 导出药物库存表明细
        /// </summary>
        /// <param name="excelModels"></param>
        /// <param name="fileName"></param>
        /// <param name="serverPath"></param>
        /// <returns></returns>
        public static string GetDrugListStockBig(List<ExcelNewModel> excelModels, string fileName, string serverPath)
        {
            SXSSFWorkbook sxssfBook = new SXSSFWorkbook();
            sxssfBook.RandomAccessWindowSize = 1000;
            int sheetIndex = 0;
            foreach (var excelModel in excelModels)
            {
                var dtSource = excelModel.ResultDataTable;
                var fieldDictionarys = excelModel.FieldDictionarys;
                var title = excelModel.Title;
                NPOI.SS.UserModel.ISheet sheet = sxssfBook.CreateSheet(title);
                ICellStyle dateStyle = sxssfBook.CreateCellStyle();
                IDataFormat format = sxssfBook.CreateDataFormat();
                int rowCount=1;
                if (sheetIndex==0)
                {
                    rowCount=  5;
                }
                
                int cellCount = fieldDictionarys.Count;
                Create(sheet, rowCount, cellCount);
                int index = 0;
                //第一个sheet添加标题
                if (sheetIndex == 0)
                {
                    foreach (var cellDictionary in excelModel.CellDictionarys)
                    {
                        var key = cellDictionary.Key;
                        var value = cellDictionary.Value;
                        sheet.GetRow(index).GetCell(0).SetCellValue(key);
                        sheet.GetRow(index).GetCell(1).SetCellValue(value);
                        index++;
                    }
                }
                if (excelModel.CellDictionarys!=null)
                {
                    index += 1;
                }

                int k = 0;
                foreach (var fieldDictionary in fieldDictionarys)
                {
                    string cellValue = fieldDictionary.Key;
                    sheet.GetRow(index).GetCell(k).SetCellValue(cellValue);
                    k++;
                }

                for (int i = 0; i < dtSource.Rows.Count; i++)
                {
                    int row = index + i + 1;
                    int j = 0;
                    IRow currtRow = sheet.CreateRow(row);
                    foreach (var fieldDictionary in fieldDictionarys)
                    {
                        ICell cell = currtRow.CreateCell(j);
                        string fieldName = fieldDictionary.Value;
                        string cellValue = dtSource.Rows[i][fieldName].ToString();
                        sheet.GetRow(row).GetCell(j).SetCellValue(cellValue);
                        j++;
                    }
                }
                sheetIndex++;
            }
            var patUrl = serverPath;
            if (!FileHelperTools.IsExistDirectory(patUrl))
            {
                FileHelperTools.CreateDirectory(patUrl);
            }
            var partName = UniqueKeyGenerator.GetDtRng_RandomNo();
            using (FileStream fileStream = File.Open(@"" + patUrl + fileName + "-" + partName + ".xlsx", FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite))
            {
                sxssfBook.Write(fileStream);
                fileStream.Close();
            }
            return "/RandResult/" + fileName + "-" + partName + ".xlsx";
        }
    }
}