设计VS代码-Excel导出

NPOI作为操作Excel的强大动态库。可以让我们没有Office环境操作Excel文件。

对平常的需要导出Excel的业务场景我们可能直接用NPOI动态库的方法结合业务逻辑就写导出代码了。
例如以下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Data;
using System.IO;
using NPOI.HSSF.UserModel;

namespace LIS.Core.Util
{
    ///<summary  NoteObject="Class">
    /// [功能描述: 导出工具类，提供数据导出服务]<br></br>
    /// [创建者:   张联珠]<br></br>
    /// [创建时间: 2014-9-15]<br></br>
    /// <说明>
    ///    
    /// </说明>
    /// <修改记录>
    ///     <修改时间></修改时间>
    ///     <修改内容>
    ///            
    ///     </修改内容>
    /// </修改记录>
    /// </summary>
    public static class ExportUtil
    {
        /// <summary>
        /// 把集合导出excel
        /// </summary>
        /// <typeparam name="T">集合实体类型</typeparam>
        /// <param name="txtName">导出文件名</param>
        /// <param name="datas">导出的数据</param>
        /// <param name="titleStr">标题集合</param>
        /// <param name="response">response对象</param>
        public static void ListExportExcel<T>(string txtName, List<T> datas, List<string> titleStr, HttpResponse response)
        {
            try
            {
                HSSFWorkbook hssfworkbook = new HSSFWorkbook();
                NPOI.SS.UserModel.ISheet sheet = hssfworkbook.CreateSheet("Sheet1");
                NPOI.SS.UserModel.IRow row = null; 
                if (titleStr != null && titleStr.Count > 0)
                {
                    row = sheet.CreateRow(0);
                    //自动适应宽度
                    sheet.AutoSizeColumn(0);
                    //遍历组装串
                    for (var i = 0; i < titleStr.Count; i++)
                    {
                        row.CreateCell(i).SetCellValue(titleStr[i]);
                    }
                }
                if (datas != null && datas.Count > 0)
                {
                    //获取实体的类型
                    Type type = typeof(T);
                    //获取实体的属性集合
                    System.Reflection.PropertyInfo[] propertys = type.GetProperties();
                    //遍历组装串
                    for (var i = 0; i < datas.Count; i++)
                    {
                        row = sheet.CreateRow(i+1);
                        //自动适应宽度
                        sheet.AutoSizeColumn(i+1);
                        int index = 0;
                        //遍历属性的集合，创建DataTable
                        foreach (var p in propertys)
                        {
                            row.CreateCell(index).SetCellValue(ObjToString(p.GetValue(datas[i], null)));
                            index++;
                        }
                    }

                }
                FileStream file = null;
                try
                {
                    file = new FileStream(txtName, FileMode.Create);
                    hssfworkbook.Write(file);
                }
                finally
                {
                    if (file != null)
                    {
                        file.Close();
                        file.Dispose();
                    }
                }
                DownloadFile(response, txtName);
            }
            catch (Exception ex)
            {
                LIS.Core.Util.LogUtils.WriteExceptionLog("导出Excel出错", ex);
            }
        }

        /// <summary>
        /// 把数据表导出excel
        /// </summary>
        /// <param name="txtName">导出文件名</param>
        /// <param name="datas">导出的数据</param>
        /// <param name="titleStr">标题集合</param>
        /// <param name="response">response对象</param>
        public static void DataTableExportExcel(string txtName, DataTable datas, List<string> titleStr, HttpResponse response)
        {
            try
            {
                HSSFWorkbook hssfworkbook = new HSSFWorkbook();
                NPOI.SS.UserModel.ISheet sheet = hssfworkbook.CreateSheet("Sheet1");
                NPOI.SS.UserModel.IRow row = null;
                if (titleStr != null && titleStr.Count > 0)
                {
                    row = sheet.CreateRow(0);
                    //自动适应宽度
                    sheet.AutoSizeColumn(0);
                    //遍历组装串
                    for (var i = 0; i < titleStr.Count; i++)
                    {
                        row.CreateCell(i).SetCellValue(titleStr[i]);
                    }
                }
                if (datas != null && datas.Rows.Count > 0)
                {
                    //遍历组装串
                    for (var i = 0; i < datas.Rows.Count; i++)
                    {
                        row = sheet.CreateRow(i + 1);
                        //自动适应宽度
                        sheet.AutoSizeColumn(i+1);
                        int index = 0;
                        //遍历属性的集合，创建DataTable
                        foreach (DataColumn cloumn in datas.Columns)
                        {
                            row.CreateCell(index).SetCellValue(ObjToString(datas.Rows[i][cloumn.ColumnName]));
                            index++;
                        }
                    }

                }
                FileStream file = null;
                try
                {
                    file = new FileStream(txtName, FileMode.Create);
                    hssfworkbook.Write(file);
                }
                finally
                {
                    if (file != null)
                    {
                        file.Close();
                        file.Dispose();
                    }
                }
                DownloadFile(response, txtName);
            }
            catch (Exception ex)
            {
                LIS.Core.Util.LogUtils.WriteExceptionLog("导出Excel出错", ex);
            }
        }

        /// <summary>
        /// 把字符串导出excel
        /// </summary>
        /// <param name="txtName">导出文件名</param>
        /// <param name="str">串</param>
        /// <param name="response">response对象</param>
        public static void StringExportExcel(string txtName, string str, HttpResponse response)
        {
            try
            {
                WriteTxt(txtName, str, true);
                DownloadFile(response, txtName);
            }
            catch (Exception ex)
            {
                LIS.Core.Util.LogUtils.WriteExceptionLog("导出Excel出错", ex);
            }

        }

        /// <summary>
        /// 写入数据到指定文件
        /// </summary>
        /// <param name="path">文件全路径</param>
        /// <param name="str">数据</param>
        /// <param name="isReplace">是否提换，默认为替换，否则为添加</param>
        /// <returns></returns>
        private static bool WriteTxt(string path, string str, bool isReplace = true)
        {
            FileStream fs = null;
            StreamWriter sw1 = null;
            try
            {
                //如果文件不存在，先创建一个
                if (!File.Exists(path))
                {
                    //创建写入文件  
                    fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.Write);
                    sw1 = new StreamWriter(fs, Encoding.GetEncoding("UTF-8"));
                    //开始写入值  
                    sw1.WriteLine(str);
                }
                else
                {
                    //如果是替换，先清除之前的内容
                    if (isReplace)
                    {
                        using (StreamWriter sw = new StreamWriter(path, false, Encoding.GetEncoding("UTF-8")))
                        {
                            sw.Write("");
                            sw.Close();
                        }
                    }
                    fs = new FileStream(path, FileMode.Append, FileAccess.Write);
                    sw1 = new StreamWriter(fs, Encoding.GetEncoding("UTF-8"));
                    sw1.WriteLine(str);
                }
                return true;
            }
            catch (Exception ex)
            {
                LIS.Core.Util.LogUtils.WriteExceptionLog("写文件出错", ex);
                return false;
            }
            finally
            {
                if (sw1 != null)
                {
                    sw1.Close();
                }
                if (fs != null)
                {
                    fs.Close();
                }
            }
        }

        /// <summary>
        /// 把DataReader的数据导出excel
        /// </summary>
        /// <param name="txtName">导出文件名</param>
        /// <param name="sdr">数据读取器</param>
        /// <param name="titleStr">标题集合</param>
        /// <param name="response">response对象</param>
        /// <param name="fields">fields输出的列明</param>
        public static void DataReaderExportExcel(string txtName, IDataReader sdr, List<string> titleStr, HttpResponse response, string fields)
        {
            try
            {
                //处理显示串
                if (fields != null && fields.Length > 0)
                {
                    fields = "," + fields + ",";
                }
                HSSFWorkbook hssfworkbook = new HSSFWorkbook();
                NPOI.SS.UserModel.ISheet sheet = hssfworkbook.CreateSheet("Sheet1");
                NPOI.SS.UserModel.IRow row = null; 
                if (titleStr != null && titleStr.Count > 0)
                {
                    row = sheet.CreateRow(0);
                    //自动适应宽度
                    sheet.AutoSizeColumn(0);
                    //遍历组装串
                    for (var i = 0; i < titleStr.Count; i++)
                    {

                        row.CreateCell(i).SetCellValue(titleStr[i]);
                    }
                }
                else
                {
                    row = sheet.CreateRow(0);
                    //自动适应宽度
                    sheet.AutoSizeColumn(0);
                    //遍历属性的集合，创建DataTable
                    for (int i = 0; i < sdr.FieldCount; i++)
                    {
                        //如果有显示字段参数传入,且当前名称不在传入列内，进行下一列
                        if (fields != null && fields != string.Empty)
                        {
                            if ((fields.IndexOf("," + sdr.GetName(i) + ",") == -1) && (sdr.GetName(i) != "RowID"))
                            {
                                continue;
                            }
                        }
                        row.CreateCell(i).SetCellValue(sdr.GetName(i));
                    }
                }
                if (sdr != null)
                {
                    //分割字段
                    string[] fildArr = null;
                    if (fields != null && fields != string.Empty)
                    {
                        fildArr = fields.Split(',');
                    }
                    int index = 1;
                    //遍历读取数据
                    while (sdr.Read())
                    {
                        row = sheet.CreateRow(index);
                        //自动适应宽度
                        sheet.AutoSizeColumn(index);
                        if (fields == null || fields == string.Empty)
                        {
                            //遍历属性的集合，创建DataTable
                            for (int i = 0; i < sdr.FieldCount; i++)
                            {
                                //如果有显示字段参数传入,且当前名称不在传入列内，进行下一列
                                if (fields != null && fields != string.Empty)
                                {
                                    string fielda = sdr.GetName(i);
                                    if ((fields.IndexOf("," + fielda + ",") == -1))
                                    {
                                        continue;
                                    }
                                }
                                row.CreateCell(i).SetCellValue(ObjToString(sdr[i]));

                            }
                        }
                        else
                        {
                            int j = 0;
                            foreach (string f in fildArr)
                            {
                                if (f != "")
                                {
                                    row.CreateCell(j).SetCellValue(ObjToString(sdr[f]));
                                    j++;
                                }
                            }
                        }
                        index++;
                    }

                }
                FileStream file = null;
                try
                {
                    file = new FileStream(txtName, FileMode.Create);
                    hssfworkbook.Write(file);
                }
                finally
                {
                    if (file != null)
                    {
                        file.Close();
                        file.Dispose();
                    }
                }
                DownloadFile(response, txtName);
            }
            catch (Exception ex)
            {
                LIS.Core.Util.LogUtils.WriteExceptionLog("导出Excel出错", ex);
            }
        }

        /// <summary>
        /// 对象转字符串
        /// </summary>
        /// <param name="obj">对着</param>
        /// <returns></returns>
        private static string ObjToString(object obj)
        {
            if (obj == null)
            {
                return "";
            }
            else
            {
                return obj.ToString();
            }
        }

        /// <summary>
        /// 把集合导出excel
        /// </summary>
        /// <typeparam name="T">集合实体类型</typeparam>
        /// <param name="txtName">导出文件名</param>
        /// <param name="datas">导出的数据</param>
        /// <param name="titleStr">标题集合</param>
        /// <param name="response">response对象</param>
        /// <param name="splitStr">分割符</param>
        public static void ListExportTxt<T>(string txtName, List<T> datas, List<string> titleStr, System.Web.HttpResponse response, string splitStr = "\t")
        {
            response.Clear();
            response.Buffer = true;
            response.Charset = "utf-8";
            response.ContentEncoding = System.Text.Encoding.UTF8;
            response.HeaderEncoding = System.Text.Encoding.UTF8;
            response.AppendHeader("content-disposition", "attachment;filename=\"" + HttpUtility.HtmlEncode(txtName ?? DateTime.Now.ToString("yyyyMMdd")) + ".txt\"");
            response.ContentType = "text/plain";
            StringBuilder sb = new StringBuilder();
            if (titleStr != null && titleStr.Count > 0)
            {
                //遍历组装串
                for (var i = 0; i < titleStr.Count; i++)
                {
                    sb.Append(titleStr[i] + splitStr);
                }
                sb.AppendLine();
            }
            if (datas != null && datas.Count > 0)
            {
                //获取实体的类型
                Type type = typeof(T);
                //获取实体的属性集合
                System.Reflection.PropertyInfo[] propertys = type.GetProperties();
                //遍历组装串
                for (var i = 0; i < datas.Count; i++)
                {
                    //遍历属性的集合，创建DataTable
                    foreach (var p in propertys)
                    {
                        sb.Append(p.GetValue(datas[i], null) + splitStr);
                    }
                    sb.AppendLine();
                }

            }
            response.Write(sb.ToString());
            response.Flush();
        }



        /// <summary>
        /// 把DataReader的数据导出excel
        /// </summary>
        /// <param name="txtName">导出文件名</param>
        /// <param name="sdr">数据读取器</param>
        /// <param name="titleStr">标题集合</param>
        /// <param name="response">response对象</param>
        /// <param name="splitStr">分割符</param>
        public static void DataReaderExportTxt(string txtName, IDataReader sdr, List<string> titleStr, System.Web.HttpResponse response, string fields, string splitStr = "\t")
        {
            //处理显示串
            if (fields != null && fields.Length > 0)
            {
                fields = "," + fields + ",";
            }
            response.Clear();
            response.Buffer = true;
            response.Charset = "utf-8";
            response.ContentEncoding = System.Text.Encoding.UTF8;
            response.HeaderEncoding = System.Text.Encoding.UTF8;
            response.AppendHeader("content-disposition", "attachment;filename=\"" + HttpUtility.HtmlEncode(txtName ?? DateTime.Now.ToString("yyyyMMdd")) + ".txt\"");
            response.ContentType = "text/plain";
            StringBuilder sb = new StringBuilder();
            if (titleStr != null && titleStr.Count > 0)
            {
                //遍历组装串
                for (var i = 0; i < titleStr.Count; i++)
                {
                    sb.Append(titleStr[i] + splitStr);
                }
                sb.AppendLine();
            }
            else
            {
                //遍历列的集合
                for (int i = 0; i < sdr.FieldCount; i++)
                {
                    //如果有显示字段参数传入,且当前名称不在传入列内，进行下一列
                    if (fields != null && fields != string.Empty)
                    {
                        if ((fields.IndexOf("," + sdr.GetName(i) + ",") == -1) && (sdr.GetName(i) != "RowID"))
                        {
                            continue;
                        }
                    }
                    sb.Append(sdr.GetName(i) + splitStr);
                }
                sb.AppendLine();
            }
            if (sdr != null)
            {
                //分割字段
                string[] fildArr = null;
                if (fields != null && fields != string.Empty)
                {
                    fildArr = fields.Split(',');
                }
                //遍历读取数据
                while (sdr.Read())
                {
                    if (fields == null || fields == string.Empty)
                    {
                        //遍历属性的集合，创建DataTable
                        for (int i = 0; i < sdr.FieldCount; i++)
                        {
                            //如果有显示字段参数传入,且当前名称不在传入列内，进行下一列
                            if (fields != null && fields != string.Empty)
                            {
                                if ((fields.IndexOf("," + sdr.GetName(i) + ",") == -1))
                                {
                                    continue;
                                }
                            }
                            sb.Append(sdr[i] + splitStr);
                        }
                    }
                    else
                    {
                        foreach (string f in fildArr)
                        {
                            if (f != "")
                            {
                                sb.Append(sdr[f] + splitStr);
                            }
                        }
                    }
                    sb.AppendLine();
                }

            }
            response.Write(sb.ToString());
            response.Flush();
        }

        /// <summary>
        /// response下载文件
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static void DownloadFile(System.Web.HttpResponse response, string filePath)
        {
            if (File.Exists(filePath))
            {
                FileInfo finfo = new FileInfo(filePath);
                response.Clear();
                response.ContentType = "application/x-dbf";
                response.ContentEncoding = System.Text.Encoding.UTF8;
                response.AddHeader("Content-Disposition", "attachment; filename=" + finfo.Name);
                FileStream fs = null;
                try
                {
                    fs = new FileStream(@filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                    int bufferLen = 2048;
                    byte[] buffer = new byte[bufferLen];
                    int byteCount = fs.Read(buffer, 0, bufferLen);
                    while (byteCount > 0)
                    {
                        response.OutputStream.Write(buffer, 0, byteCount);
                        //读取的大小
                        byteCount = fs.Read(buffer, 0, bufferLen);
                    }
                    response.Flush();
                }
                catch (Exception ex)
                {
                    LIS.Core.Util.LogUtils.WriteExceptionLog("下载导出文件发生错误", ex);
                }
                finally
                {
                    if (fs != null)
                    {
                        fs.Close();
                        fs.Dispose();
                        File.Delete(filePath);
                    }
                }
            }
        }
    }
}

从上面的模式看，代码简单直接，懂C#语法的看动态库文档就能写。如果用户的业务变复杂了，对导出有各种要求就得针对修改导出代码，导出代码随着修改可能有各种情况下没考虑到的bug导致异常。而且每个写业务的人都得了解操作Excel细节，很大的增加了维护和实现难度。（简单导出很完美）

为什么会有这样的问题呢？
上面模式缺乏设计，没有把业务逻辑和导出逻辑进行剥离。导致业务把导出逻辑牵着走，业务波动波及导出部分。
以往Excel填充列表数据为例，业务关心的是输出业务数据表格哪些列数据填入Excel的哪些对应列（比如DataTable）。往Excel填充表格标题这些非列表数据业务关心的输出数据的哪部分往Excel哪个表格填充。所以控制数据输出是业务性的。按约定填充数据是基础性的。

基础与业务的约定（以LIS用Query控制导出为例）
1.数据列约定
$ col用来描述和 query 输出列的对应关系，点后面跟 query 输出的列名。特殊$ col命令：$col. SeqNo:将序号打印在某一列
2.数据字段约定
$ s 用来描述 query 从 Sessions 以 Output 方式带出数据串的使用,第一个“.”后面描述分割符，第二个“.”后面描述取用第一个“.”描述分割符（推荐使用“^”）的第几位，从 0开始。
如果有第三个和第四个点分割的参数，使用模板指定的宽高合并单元格。
3.图片约定
$ p用来描述图形的起始位置，第一个“.”后面描述分割符，第二个“.”后面描述取用第一个“.”描述分割符（推荐使用“^”）的第几张图，从 0开始，图形默认占8*8个单元格。如果有第三个和第四个点分割的参数，优先使用模板指定的宽高忽略程序传入的宽高。

Excel模板示例

有了约定的话导出基础只要实现约定功能。保证对约定负责就行，业务按约定输出数据就可以构建一套稳定的导出体系

按参数下载Excel模板和调用查询获得业务输出的数据

/// <summary>
        /// 真的数据导出
        /// </summary>
        /// <param name="inputStrArr"></param>
        private static void RealExport(string[] inputStrArr)
        {
            string webServicAddress = inputStrArr[1].Replace("&", "&amp;");
            //获得配置的连接信息
            string connectStringOne = System.Configuration.ConfigurationSettings.AppSettings["WebServiceAddressOne"];
            //没有强制地址，使用单独地址
            if (connectStringOne != "")
            {
                webServicAddress = connectStringOne;
            }
            string className = inputStrArr[2];
            string functionName = inputStrArr[3];
            string paraJson = inputStrArr[4];
            string session = inputStrArr[5];
            string tempExcelPath = inputStrArr[6];
            //导出路径-1桌面  0用户选择  其他路径信息不带最后的\\
            string exportPath = inputStrArr[7];
            //取图片串
            string imgStr = "";
            if (inputStrArr.Length > 8)
            {
                imgStr=inputStrArr[8];
            }
            //得到参数
            Parameters para = null;
            try
            {
                para = JsonConvert.DeserializeObject<Parameters>(paraJson);
            }
            catch(Exception ex)
            {
                if (paraJson.Contains("\\\""))
                {
                    para = JsonConvert.DeserializeObject<Parameters>(paraJson.Replace("\\\"", "\""));
                }
                else
                {
                    MessageBox.Show(ex.Message,"提示");
                }
            }
            string err;
            //执行查询
            string xmlStr = DHCLabtrakReportPrint.WebManager.GetXML(webServicAddress, className, functionName, para, session, out err);
            WriteTxt(Application.StartupPath + "\\ExportLogs.txt", "地址:"+webServicAddress + "类名:" + className + "方法:" + functionName + "参数:" + paraJson + "会话:" + session +"数据:"+ xmlStr);
            if (xmlStr == "")
            {
                MessageBox.Show(err,"提示");
                return;
            }
            IWorkbook workbook = null;
            string fileName = "";
            string tmpFileName = "";
            if (tempExcelPath != "")
            {
                try
                {
                    HttpWebRequest request = null ;
                    if (tempExcelPath.StartsWith("https", StringComparison.OrdinalIgnoreCase))
                    {
                        request = WebRequest.Create(tempExcelPath) as HttpWebRequest;
                        ServicePointManager.ServerCertificateValidationCallback = new RemoteCertificateValidationCallback(CheckValidationResult);
                        request.ProtocolVersion = HttpVersion.Version11;
                        // 这里设置了协议类型。
                        ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls;
                        request.KeepAlive = false;
                        ServicePointManager.CheckCertificateRevocationList = true;
                        ServicePointManager.DefaultConnectionLimit = 100;
                        ServicePointManager.Expect100Continue = false;
                    }
                    else
                    {
                        request = WebRequest.Create(tempExcelPath) as HttpWebRequest;
                    }
                    HttpWebResponse response = request.GetResponse() as HttpWebResponse;
                    Stream responseStream = response.GetResponseStream();
                    string[] tmpNameArr = tempExcelPath.Split('/');
                    //检查创建目录
                    if (!Directory.Exists(@"C:\TRAK\TMP"))
                    {
                        Directory.CreateDirectory(@"C:\TRAK\TMP");
                    }
                    fileName = tmpNameArr[tmpNameArr.Length - 1];
                    //临时文件名
                    tmpFileName = @"C:\TRAK\TMP\" + DateTime.Now.ToString("yyyyMMddhhmmss") + Thread.CurrentThread.ManagedThreadId.ToString() + fileName;
                    StreamWriter sw = new StreamWriter(tmpFileName);
                    responseStream.CopyTo(sw.BaseStream);
                    sw.Flush();
                    sw.Close();
                    responseStream.Flush();
                    responseStream.Close();
                    // 2007版本
                    if (tmpFileName.IndexOf(".xlsx") > 0)
                    {
                        workbook = new XSSFWorkbook(tmpFileName);
                    }
                    // 2003版本
                    else if (tmpFileName.IndexOf(".xls") > 0)
                    {
                        Stream st = new FileStream(tmpFileName, FileMode.Open);
                        workbook = new HSSFWorkbook(st);
                    }
                }
                catch(Exception ex)
                {
                    if (ex.Message.Contains("远程服务器返回错误: (500) 内部服务器错误"))
                    {
                        MessageBox.Show("目前导出模板正在服务器被编辑，请稍后再试！", "温馨提示", MessageBoxButtons.OK);
                        return;
                    }
                    else
                    {
                        MessageBox.Show(ex.Message, "温馨提示", MessageBoxButtons.OK);
                    }
                }
            }
            try
            {
                //WriteTxt(@"C:\TRAK\logs.txt", xmlStr);
                DealXmlToExcel(xmlStr, workbook, fileName, exportPath,imgStr);
                if (tmpFileName!=""&&System.IO.File.Exists(tmpFileName))
                {
                    File.Delete(tmpFileName);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

按约定规则实现命令主要逻辑

/// <summary>
        /// 处理xml和excel模板的映射
        /// </summary>
        /// <param name="xmlStr"></param>
        /// <param name="workBook"></param>
        private static void DealXmlToExcel(string xmlStr, IWorkbook workBook, string fileName, string path, string imgStr)
        {
            DataSet ds = new DataSet();
            //创建一个xml文档
            XmlDocument xmlDoc = new XmlDocument();
            //为文档导入数据
            xmlDoc.LoadXml(xmlStr);
            //获得根节点
            XmlNode root = xmlDoc.SelectSingleNode("Response");
            //得到返回值节点
            XmlNode nodeReturn = root.SelectSingleNode("RetVal");
            //记录总数
            string RowCount = root.SelectSingleNode("RowCount").InnerText;
            //得到错误信息
            string errStr = root.SelectSingleNode("Error").InnerText;
            //分割图片串
            string[] imgstrArr = imgStr.Split('^');
            //如果错误信息不为空
            if (errStr != string.Empty)
            {
                MessageBox.Show(errStr, "提示", MessageBoxButtons.OK);
                return;
            }
            //返回信息
            string RetSession = "";
            if (root.SelectSingleNode("RetSession") != null)
            {
                RetSession = root.SelectSingleNode("RetSession").InnerText;
            }
            //给返回值赋值
            int retVal = Convert.ToInt32(nodeReturn.InnerText);
            
            //行号
            int rowSeq = 1;
            //获得存数据节点的名称
            string dataNodeName = root.SelectSingleNode("Node").InnerText;
            //返回值的类型：0数据集xml串，1json串
            string resType = root.SelectSingleNode("ResType").InnerText;
            if (resType == "1")
            {
                //json串
                throw new Exception("导出只支持query方法");
            }
            else
            {
                DataTable dt = null;
                Dictionary<int, IRow> moveRows = new Dictionary<int, IRow>();
                if (resType == "2")
                {
                    //json串
                    string retStr = root.SelectSingleNode(dataNodeName + "Result").InnerText;
                    dt = JsonConvert.DeserializeObject<DataTable>(retStr);
                }
                else
                {
                    //数据集xml串转JSON串
                    string dataSetXML = root.SelectSingleNode(dataNodeName + "Result").OuterXml;
                    using (StringReader xmlSR = new StringReader(dataSetXML))
                    {
                        //InferTypedSchema:忽视任何内联架构，从数据推断出强类型架构并加载数据。如果无法推断，则解释成字符串数据.数字前面的0自动去掉
                        //ReadSchema:按数据结构加载数据
                        ds.ReadXml(xmlSR, XmlReadMode.ReadSchema);
                        if (ds.Tables.Count > 0)
                        {
                            if (ds.Tables.Count > 0)
                            {
                                dt = ds.Tables[0];
                            }
                        }
                    }
                }
                if (dt!=null)
                {
                    //没模板采用普通导出
                    if (workBook == null)
                    {
                        ExportUtil.DataTableExportExcel(fileName, dt, null, path);
                    }
                    //模板和数据映射
                    else
                    {
                        //得到表单
                        ISheet sheet = workBook.GetSheetAt(0);
                        //最后一列的标号
                        int rowCount = sheet.LastRowNum;
                        //存下一行
                        IRow nextRow = null;
                        //记录当前输出的数据行数
                        int curDataIndex = -1;
                        //记录表格结束行位置
                        int gridEndRowIndex = -1;
                        int minMoveIndex = 999999999;
                        //偏移尾部
                        for (var i = 0; i <= rowCount; i++)
                        {
                            IRow row = sheet.GetRow(i);
                            if (i >= minMoveIndex)
                            {
                                continue;
                            }
                            //没有数据的行默认是null　　
                            if (row == null)
                            {
                                continue;
                            }
                            if (gridEndRowIndex > -1)
                            {
                                if (dt.Rows.Count > 1)
                                {
                                    if (minMoveIndex > (i + dt.Rows.Count - 1))
                                    {
                                        minMoveIndex = i + dt.Rows.Count - 1;
                                    }
                                    IRow perRow = row.CopyRowTo(i + dt.Rows.Count - 1);
                                    moveRows.Add(i + dt.Rows.Count - 1, perRow);
                                    int curCellCount = row.LastCellNum;
                                    //遍历单元格
                                    for (int z = 0; z < curCellCount; z++)
                                    {
                                        ICell cell = row.GetCell(z);
                                        if (cell != null)
                                        {
                                            cell.SetCellValue("");
                                        }
                                    }
                                }
                                continue;
                            }
                            int cellCount = row.LastCellNum;
                            //遍历单元格
                            for (int j = 0; j < cellCount; j++)
                            {
                                ICell cell = row.GetCell(j);
                                if (cell != null)
                                {
                                    object cellVal = null;
                                    if (cell.CellType == CellType.Numeric)
                                    {
                                        cellVal = cell.NumericCellValue;
                                    }
                                    else if (cell.CellType == CellType.String)
                                    {
                                        cellVal = cell.StringCellValue;
                                    }
                                    else if (cell.CellType == CellType.Boolean)
                                    {
                                        cellVal = cell.BooleanCellValue;
                                    }
                                    else
                                    {
                                        cellVal = cell.StringCellValue;
                                    }
                                    string cellStr = ExportUtil.ObjToString(cellVal);
                                    if (cellStr.Length > 5)
                                    {
                                        if (cellStr.Substring(0, 4) == "$col")
                                        {
                                            gridEndRowIndex = i;
                                        }
                                    }
                                }

                            }
                        }
                        rowCount = sheet.LastRowNum;
                        //遍历行
                        for (var i = 0; i < dt.Rows.Count + rowCount + 15; i++)
                        {
                            nextRow = null;
                            IRow row = sheet.GetRow(i);
                            //拷贝原来行数据
                            if (moveRows.ContainsKey(i))
                            {
                                row = sheet.CreateRow(i);
                                IRow perrow = moveRows[i];
                                int curCellCount = perrow.LastCellNum;
                                //遍历单元格
                                for (int z = 0; z < curCellCount; z++)
                                {
                                    ICell percell = perrow.GetCell(z);
                                    ICell curcell = row.CreateCell(z);
                                    if (percell != null)
                                    {
                                        if (percell.CellType == CellType.Numeric)
                                        {
                                            curcell.SetCellValue(percell.NumericCellValue);
                                        }
                                        else if (percell.CellType == CellType.String)
                                        {
                                            curcell.SetCellValue(percell.StringCellValue);
                                        }
                                        else if (percell.CellType == CellType.Boolean)
                                        {
                                            curcell.SetCellValue(percell.BooleanCellValue);
                                        }
                                        else
                                        {
                                            curcell.SetCellValue(percell.StringCellValue);
                                        }
                                    }
                                }

                            }
                            //没有数据的行默认是null　　
                            if (row == null)
                            {
                                row = sheet.CreateRow(i);
                            }
                            int cellCount = row.LastCellNum;
                            //遍历单元格
                            for (int j = 0; j < cellCount; j++)
                            {
                                ICell cell = row.GetCell(j);
                                if (cell != null)
                                {
                                    object cellVal = null;
                                    if (cell.CellType == CellType.Numeric)
                                    {
                                        cellVal = cell.NumericCellValue;
                                    }
                                    else if (cell.CellType == CellType.String)
                                    {
                                        cellVal = cell.StringCellValue;
                                    }
                                    else if (cell.CellType == CellType.Boolean)
                                    {
                                        cellVal = cell.BooleanCellValue;
                                    }
                                    else
                                    {
                                        cellVal = cell.StringCellValue;
                                    }
                                    string cellStr = ExportUtil.ObjToString(cellVal);
                                    //去除空格
                                    if (cellStr != null)
                                    {
                                        cellStr = cellStr.Trim();
                                    }
                                    //检查命令
                                    if (cellStr.Length > 5)
                                    {
                                        //设置单元格命令
                                        if (cellStr.Substring(0, 2) == "$s")
                                        {
                                            //解析命令
                                            string[] codeArr = cellStr.Split('.');
                                            //合格命令执行
                                            if (codeArr.Length >= 3)
                                            {
                                                char spStr = codeArr[1][0];
                                                int spIndex = Convert.ToInt32(codeArr[2]);
                                                string[] retStrs = RetSession.Split(spStr);
                                                string retStrValue = "";
                                                if (spIndex < retStrs.Length)
                                                {
                                                    retStrValue = retStrs[spIndex];
                                                }
                                                //后面接着两个参数指定宽高
                                                if (codeArr.Length >= 5)
                                                {
                                                    int cellWidth = Convert.ToInt32(codeArr[3]);
                                                    int cellHeight = Convert.ToInt32(codeArr[4]);
                                                    sheet.AddMergedRegion(new CellRangeAddress(i, i + cellHeight - 1, j, j + cellWidth - 1));
                                                }
                                                cell.SetCellValue(retStrValue);
                                            }
                                        }
                                        //设置单元格命令
                                        if (cellStr.Substring(0, 2) == "$p")
                                        {
                                            //解析命令
                                            string[] codeArr = cellStr.Split('.');
                                            //合格命令执行
                                            if (codeArr.Length >= 3)
                                            {
                                                char spStr = codeArr[1][0];
                                                int spIndex = Convert.ToInt32(codeArr[2]);
                                                string[] retStrs = RetSession.Split(spStr);
                                                string retStrValue = "";
                                                if (spIndex < imgstrArr.Length)
                                                {
                                                    retStrValue = imgstrArr[spIndex];
                                                }
                                                if (retStrValue != "")
                                                {
                                                    string[] imgResArr = retStrValue.Split('#');
                                                    int imgWidth = 8;
                                                    int imgHeight = 8;
                                                    if (imgResArr.Length >= 3)
                                                    {
                                                        imgWidth = Convert.ToInt32(imgResArr[1]);
                                                        imgHeight = Convert.ToInt32(imgResArr[2]);
                                                    }
                                                    //后面接着两个参数指定宽高
                                                    if (codeArr.Length >= 5)
                                                    {
                                                        imgWidth = Convert.ToInt32(codeArr[3]);
                                                        imgHeight = Convert.ToInt32(codeArr[4]);
                                                    }
                                                    if (imgResArr[0] != "")
                                                    {
                                                        byte[] arr = Convert.FromBase64String(imgResArr[0].Replace("data:image/png;base64,", ""));
                                                        int pictureIdx = workBook.AddPicture(arr, PictureType.JPEG);
                                                        //第三步：在sheet中创建画部  
                                                        IDrawing patriarch = sheet.CreateDrawingPatriarch();
                                                        //第四步：设置锚点 （在起始单元格的X坐标0-1023，Y的坐标0-255，在终止单元格的X坐标0-1023，Y的坐标0-255，起始单元格行数，列数，终止单元格行数，列数）  
                                                        IClientAnchor anchor = patriarch.CreateAnchor(0, 0, 0, 0, j, i, j + imgWidth, i + imgHeight);
                                                        //第五步：创建图片  
                                                        IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);
                                                    }
                                                }
                                                cell.SetCellValue("");
                                            }
                                        }
                                        //设置列命令
                                        if (cellStr.Substring(0, 4) == "$col")
                                        {
                                            ///画边格线
                                            //cell.CellStyle = Style;
                                            if (dt != null && curDataIndex <= dt.Rows.Count)
                                            {
                                                //解析命令
                                                string[] codeArr = cellStr.Split('.');
                                                //合格命令执行
                                                if (codeArr.Length >= 2)
                                                {
                                                    string colName = codeArr[1];
                                                    bool isNumber = false;
                                                    if (codeArr.Length >= 3)
                                                    {
                                                        if (codeArr[2].ToLower() == "number")
                                                        {
                                                            isNumber = true;
                                                        }
                                                    }
                                                    //创建下一行
                                                    if (nextRow == null && curDataIndex < dt.Rows.Count - 1)
                                                    {
                                                        nextRow = sheet.CreateRow(i + 1);
                                                        //自动适应宽度
                                                        //sheet.AutoSizeColumn(i + 1);
                                                        curDataIndex++;
                                                    }
                                                    //考虑列名不存在的情况
                                                    if (dt.Rows.Count > 0 && dt.Columns.Contains(colName))
                                                    {
                                                        string cellResStr = ExportUtil.ObjToString(dt.Rows[curDataIndex][colName]);
                                                        if (cellResStr != "" && isNumber == true && IsNumeric(cellResStr))
                                                        {
                                                            cell.CellStyle.DataFormat = (short)CellType.Numeric;
                                                            cell.SetCellValue(Convert.ToDouble(cellResStr));
                                                        }
                                                        else
                                                        {
                                                            if (cellResStr.Contains("^"))
                                                            {
                                                                string[] cellResArr = cellResStr.Split('^');
                                                                if (cellResArr.Length == 3)
                                                                {
                                                                    cellResStr = cellResArr[0];
                                                                    int cellWidth = Convert.ToInt32(cellResArr[1]);
                                                                    int cellHeight = Convert.ToInt32(cellResArr[2]);
                                                                    sheet.AddMergedRegion(new CellRangeAddress(i, i + cellHeight - 1, j, j + cellWidth - 1));
                                                                }
                                                            }
                                                            cell.SetCellValue(cellResStr);
                                                        }

                                                    }
                                                    else
                                                    {
                                                        cell.SetCellValue("");

                                                    }
                                                    //考虑某一列值的情况
                                                    if (colName.IndexOf("FIXFIELD") > 1)
                                                    {
                                                        cell.SetCellValue(colName.Replace("FIXFIELD", ""));
                                                    }
                                                    //考虑打列序号的情况
                                                    if (colName == "SeqNo")
                                                    {
                                                        cell.SetCellValue(rowSeq.ToString());
                                                        rowSeq = rowSeq + 1;
                                                    }
                                                    if (nextRow != null && nextRow != null && curDataIndex < dt.Rows.Count - 1)
                                                    {
                                                        ICell nextCell = nextRow.CreateCell(j);
                                                        nextCell.CellStyle = cell.CellStyle;
                                                        nextCell.SetCellValue(cellStr);
                                                    }
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                            nextRow = null;
                        }
                        moveRows.Clear();
                        ExportUtil.SaveFile(fileName, workBook, path);
                        //删除临时文件
                        if (File.Exists(@"C:\TRAK\TMP\" + fileName))
                        {
                            try
                            {
                                File.Delete(@"C:\TRAK\TMP\" + fileName);
                            }
                            catch (Exception ex)
                            {
                            }
                        }
                    }
                }
            }

        }

调用参数格式
导出调用参数格式
Export@http://127.0.0.1:57772/csp/lis/LIS.WS.DHCLISService.cls@LIS.WS.BLL.DHCQCWorkGroup@QryMachineByWG@{“P0”:“1”}@@http://127.0.0.1:14448/WebUI/download/exportExcelTMP/测试.xlsx@-1
@分割第一位固定，告诉程序需要导出数据
@分割第二位固定，检验webservice地址
@分割第三位固定，调用query类
@分割第四位固定，调用的query方法
@分割第五位固定，传给query的参数，P0-P13的json对象串
@分割第六位固定，传给query的会话信息
@分割第七位固定，模板地址，可为空（把模板名称调用lisprint.js的DealExportExcelTMPName方法把名称处理成全路径）
@分割第八位固定，导出地址,-1:桌面,-2:直接打印(不用office环境),0:用户选择,其他：可用地址路径,不带最后的\（F#开头强制指定文件全名）

业务查询代码

/// Creator： zlz
/// CreatDate： 20140606
/// Description:： 根据工作组查询仪器
/// Table： BTMI_MachineParameter,BT_WorkGroup
/// Input： WorkGroupDr:工作组代码
/// Output： 配置仪器标识结果集
/// Return： 
/// Others： 
Query QryMachineByWG(WorkGroupDr As %String, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10, P11, P12, P13, Output Sessions, Output RowCount As %String) As %Query(ROWSPEC = "Num,MachDr,MachCode,MachName")
{
}

/// Query的执行方法
/// d ##class(%ResultSet).RunQuery("LIS.WS.BLL.DHCQCWorkGroup","QryMachineByWG","","","","","","","","","","","","","","","","")
ClassMethod QryMachineByWGExecute(ByRef qHandle As %Binary, WorkGroupDr As %String, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10, P11, P12, P13, Output Sessions As %String, Output RowCount As %String) As %Status
{
	s WorkGroupDr=$g(WorkGroupDr)
 	Set repid=$I(^CacheTemp)
	If $Get(ind)="" Set ind=1
	s Sessions="张三^男^22^2016/11/30"
	s Num=1
	s workGroupCode=""
	i $l(WorkGroupDr) d
	.f  s workGroupCode=$o(^dbo.BTWorkGroupMachineI("IndexCode",WorkGroupDr,workGroupCode)) Quit:workGroupCode=""  Do
	..s workGroupRowId=""
	..f  s workGroupRowId=$o(^dbo.BTWorkGroupMachineI("IndexCode",WorkGroupDr,workGroupCode,workGroupRowId)) Quit:workGroupRowId=""  Do
	...s MachDr="" f  s MachDr=$o(^dbo.BTMIMachineParameterI("IndexWorkGroupMachine",workGroupRowId,MachDr)) q:MachDr=""  d
	....s StrData=$g(^dbo.BTMIMachineParameterD(MachDr))
	....s MachName=$lg(StrData,3)
	....s MachCode=$lg(StrData,2)
	....d OutputRow
	....s Num=Num+1
	e  d
	.f  s WorkGroupDr=$o(^dbo.BTWorkGroupMachineI("IndexCode",WorkGroupDr)) Quit:WorkGroupDr=""  Do
 	..f  s workGroupCode=$o(^dbo.BTWorkGroupMachineI("IndexCode",WorkGroupDr,workGroupCode)) Quit:workGroupCode=""  Do
	...s workGroupRowId=""
	...f  s workGroupRowId=$o(^dbo.BTWorkGroupMachineI("IndexCode",WorkGroupDr,workGroupCode,workGroupRowId)) Quit:workGroupRowId=""  Do
	....s MachDr="" f  s MachDr=$o(^dbo.BTMIMachineParameterI("IndexWorkGroupMachine",workGroupRowId,MachDr)) q:MachDr=""  d
	.....s StrData=$g(^dbo.BTMIMachineParameterD(MachDr))
	.....s MachName=$lg(StrData,3)
	.....s MachCode=$lg(StrData,2)
	.....d OutputRow
	.....s Num=Num+1
 	Set qHandle=$lb(0,repid,0)
	Quit $$$OK
	
	
OutputRow
    Set Data=$lb(Num,MachDr,MachCode,MachName)
    Set ColFields="Num,MachDr,MachCode,MachName"
 	Set ^CacheTemp(repid,ind)=##Class(LIS.Util.Common).TransListNull(Data,ColFields)
 	Set ind=ind+1
	quit
}

ClassMethod QryMachineByWGClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = QryMachineByWGExecute ]
{
	Set repid=$LIST(qHandle,2)
 	Kill ^CacheTemp(repid)
	Quit $$$OK
}

ClassMethod QryMachineByWGFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = QryMachineByWGExecute ]
{
 	Set AtEnd=$LIST(qHandle,1)
 	Set repid=$LIST(qHandle,2)
 	Set ind=$LIST(qHandle,3)
 	Set ind=$o(^CacheTemp(repid,ind))
 	If ind="" {				// if there are no more rows, finish fetching
 		Set AtEnd=1
 		Set Row=""
 	}
 	Else      {				// fetch row
 		Set Row=^CacheTemp(repid,ind)
 	}
 	// Save QHandle
 	s qHandle=$lb(AtEnd,repid,ind)
	Quit $$$OK
}

导出效果

前台触发示例

//绑定事件
     me.btnExportWorkList.click(function () { ExportWorkList(); });
     //导出工作列表
     function ExportWorkList() {
         var para = GetUrlPara(me.dgWorkList, me.SelectWorkListURL);
         var tempPath = DealExportExcelTMPName("微生物工作列表导出.xls");
         LISBasePrint('Export@' + WebServicAddress + '@LIS.WS.BLL.DHCRPMicNumberReport@ExportWorkList@' + JSON.stringify(para) + '@' + SessionStr + '@' + tempPath + '@0');

     }

这样的模式就只要写好业务输出逻辑，按格式驱动导出，按格式维护模板就行。对Excel操作无影无踪，导出也得到稳定性的提升。代码牛可以解决当前问题，设计好可以简化将来难度，所以不要干活时候太将就，设计从还没开始之前就该被重视，模块累的更多将来就能更轻易的累更大的模块。
20200411致我自己，保持初心，干活别糊弄