ASP.Net 后台执行导出Excel list集合 有跨行合并

/// <summary>        /// 导出成交报告应收应付到EXCEL        /// </summary>        public void InExportToExcelAgreement(HtmlViewCondition htmlView, SearchAgreementViewModel search)        {            SaveFileDialog dialog = new SaveFileDialog();            dialog.Filter = "Excel(*.xls)|*.xls|All Files(*.*)|*.*";            dialog.FileName = "应收应付统计.xls";            BaseCommon.CheckInitHtmlViewCondition(ref htmlView);            var xlApp = new Microsoft.Office.Interop.Excel.Application();            object missing = System.Reflection.Missing.Value;            try            {                if (xlApp == null)                {                    MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");                    return;                }                Workbooks xlBooks = xlApp.Workbooks;                Workbook xlBook = xlBooks.Add(XlWBATemplate.xlWBATWorksheet);                Worksheet xlSheet = (Worksheet)xlBook.Worksheets[1];                //新增加一个工作簿，Workbook是直接保存，不会弹出保存对话框，加上Application会弹出保存对话框，值为false会报错                  //xlApp.Application.Workbooks.Add(true);                 Range range = null;                //****** 抬头 *********************************************************************************                range = xlSheet.Range["A1", "K1"];//                range.Merge(Missing.Value);         // 合并单元格                range.Columns.AutoFit();            // 设置列宽为自动适应                                  // 设置单元格左边框加粗                range.Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;                // 设置单元格右边框加粗                range.Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;// 设置单元格水平居中                range.Value2 = "应收应付清单";                range.Font.Size = 18;                        // 设置字体大小                range.Font.ColorIndex = 5;                  // 设置字体颜色                                    //range.Interior.ColorIndex = 6;  // 设置单元格背景色                range.RowHeight = 25;           // 设置行高                range.ColumnWidth = 20;         // 设置列宽                //给第二行赋值，即表格的标题                xlSheet.Cells[2, 1] = "合同编号";                xlSheet.Cells[2, 2] = "成交日期";                xlSheet.Cells[2, 3] = "类型";                xlSheet.Cells[2, 4] = "物业地址";                xlSheet.Cells[2, 5] = "签约人";                xlSheet.Cells[2, 6] = "业主/客户";                xlSheet.Cells[2, 7] = "收付日";                xlSheet.Cells[2, 8] = "款类";                xlSheet.Cells[2, 9] = "应收";                xlSheet.Cells[2, 10] = "实收";                xlSheet.Cells[2, 11] = "状态";                int rowIndex = 3;//这个用来标记数据有多少行位置，从第三行开始显示数据                int myrowIndex = 3;                int totalsize = 0;                var lvwList = AgreementLogic.GetAgreementFollow(htmlView, out totalsize, search);//取出数据信息                foreach (var model in lvwList)                {                    var count = AgreementLogic.GetAgreementIncomingOutgoing(model.Id).Count;//取单个成交报告收支表的个数                    myrowIndex = myrowIndex + count;                }                //标题栏                range = xlSheet.Range[xlSheet.Cells[2, 1], xlSheet.Cells[2, 11]];                range.Interior.ColorIndex = 45;//设置标题背景色为 浅橙色                range.Font.Bold = true;//标题字体加粗                foreach (var objItem in lvwList)                {                    var type = "";                    if (objItem.DeamndType == 0)                    {                        type = "出售";                    }                    if (objItem.DeamndType == 1)                    {                        type = "出租";                    }                    if (objItem.DeamndType == 2)                    {                        type = "办证";                    }                    var agreement = AgreementLogic.BrowseAgreementDetial(objItem.Id);                    xlSheet.Cells[rowIndex, 1] = objItem.SerialNumber;//合同编号                    xlSheet.Cells[rowIndex, 2] = Convert.ToDateTime(objItem.DealTime).ToString("yyyy-MM-dd");//成交日期                    xlSheet.Cells[rowIndex, 3] = string.Format("{0}{1}", type, HaoyoujuDict.Haoyoujudict.DeamndTypeDict[objItem.PropertyType]);//类型                    xlSheet.Cells[rowIndex, 4] = objItem.PropertyAddress;//物业地址                    xlSheet.Cells[rowIndex, 5] = objItem.Name;//签约人                    xlSheet.Cells[rowIndex, 6] = string.Format("{0}/{1}", agreement.ForSaleName, agreement.ToBuyName);//业主客户                    xlSheet.Cells[rowIndex, 11] = string.Format("{0}-{1}", HaoyoujuDict.Haoyoujudict.AgreementStatusDict[objItem.Status], objItem.IsLock ? "锁定" : "正常");//状态                    var list = AgreementLogic.GetAgreementIncomingOutgoing(objItem.Id);//去成交报告的收支信息                    int count = 0;                    if (list.Count == 0)//如果收支信息为空                    {                        xlSheet.Cells[rowIndex, 7] = null;//收付日                        xlSheet.Cells[rowIndex, 8] = null;//款类                        xlSheet.Cells[rowIndex, 9] = null;//应收                        xlSheet.Cells[rowIndex, 10] = null;//实收                        count = 1;                    }                    else                    {                        foreach (var m in list)//取出每个成交报告下的收支详细信息                        {                            xlSheet.Cells[rowIndex + count, 7] = Convert.ToDateTime(m.BillTime).ToString("yyyy-MM-dd");//收付日                            xlSheet.Cells[rowIndex + count, 8] = HaoyoujuDict.Haoyoujudict.AgreementIncomingTypeDict[m.Type];//款类                            xlSheet.Cells[rowIndex + count, 9] = string.Format("{0}元", m.MustAmount);//应收                            xlSheet.Cells[rowIndex + count, 10] = string.Format("{0}元", m.ActualAmount);//实收                            count += 1;                        }                    }                    #region 合并单元格                    //如果一个成交报告的收支信息的数量超过两条，则需要合并单元格                    if (list.Count >= 2)                    {                        range = xlSheet.Range[xlSheet.Cells[rowIndex, 1], xlSheet.Cells[rowIndex + count - 1, 1]];                        range.Merge(Missing.Value);                        range.Value2 = objItem.SerialNumber;//合同编号                        range = xlSheet.Range[xlSheet.Cells[rowIndex, 2], xlSheet.Cells[rowIndex + count - 1, 2]];                        range.Merge(Missing.Value);                        range.Value2 = Convert.ToDateTime(objItem.DealTime).ToString("yyyy-MM-dd");//成交日期                        range = xlSheet.Range[xlSheet.Cells[rowIndex, 3], xlSheet.Cells[rowIndex + count - 1, 3]];                        range.Merge(Missing.Value);                        range.Value2 = string.Format("{0}{1}", type, HaoyoujuDict.Haoyoujudict.DeamndTypeDict[objItem.PropertyType]);//类型                        range = xlSheet.Range[xlSheet.Cells[rowIndex, 4], xlSheet.Cells[rowIndex + count - 1, 4]];                        range.Merge(Missing.Value);                        range.Value2 = objItem.PropertyAddress;//物业地址                        range = xlSheet.Range[xlSheet.Cells[rowIndex, 5], xlSheet.Cells[rowIndex + count - 1, 5]];                        range.Merge(Missing.Value);                        range.Value2 = objItem.Name;//签约人                        range = xlSheet.Range[xlSheet.Cells[rowIndex, 6], xlSheet.Cells[rowIndex + count - 1, 6]];                        range.Merge(Missing.Value);                        range.Value2 = string.Format("{0}/{1}", agreement.ForSaleName, agreement.ToBuyName);//业主客户                        range = xlSheet.Range[xlSheet.Cells[rowIndex, 11], xlSheet.Cells[rowIndex + count - 1, 11]];                        range.Merge(Missing.Value);                        range.Value2 = string.Format("{0}-{1}", HaoyoujuDict.Haoyoujudict.AgreementStatusDict[objItem.Status], objItem.IsLock ? "锁定" : "正常");//状态                    }                    #endregion                    rowIndex = rowIndex + count;                }                //数据区域                range = xlSheet.Range[xlSheet.Cells[2, 1], xlSheet.Cells[rowIndex, 11]];                range.Borders.LineStyle = 1;                range.Font.Size = 11;                range.Columns.AutoFit();                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;// 设置单元格水平居中                range = xlSheet.Range[xlSheet.Cells[rowIndex, 1], xlSheet.Cells[rowIndex, 11]];                range.Merge(Missing.Value);         // 合并单元格                range.RowHeight = 20;                range.Value2 = "导出时间: " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");                range.HorizontalAlignment = XlHAlign.xlHAlignRight;// 设置单元格水平居中                  //***** 格式设定 ******************************************************************************                if (xlSheet != null)                {                    xlApp.Visible = false;//显示填充效果                    xlBook.SaveCopyAs("D:\\"+dialog.FileName);//保存到服务器地址                    xlBook.Close(false, null, null); //关闭excel                    xlApp.Quit();//关掉任务管理器的进程                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook);                    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet);                                        GC.Collect();//销毁                    string path =  "D:\\"+dialog.FileName;//找到服务器下载路径                    System.IO.FileInfo file = new System.IO.FileInfo(path);                    Response.Clear();//清除原有会话                    Response.Charset = "GB2312";                    Response.ContentEncoding = System.Text.Encoding.UTF8 ;                    // 添加头信息，为"文件下载/另存为"对话框指定默认文件名                     Response.AddHeader("content-disposition", "attachment;filename=" + file.Name);                    // 添加头信息，指定文件大小，让浏览器能够显示下载进度                     //Response.AddHeader("Content-Length", file.Length.ToString());                    // 指定返回的是一个不能被客户端读取的流，必须被下载                     Response.ContentType = "application/ms-excel";                    // 把文件流发送到客户端                     Response.WriteFile(file.FullName);                    // 停止页面的执行                     Response.End();                }            }            catch (Exception ex)            {                Logs.Context.Error(this, ex);                xlApp.Quit();                GC.Collect();//销毁            }        }

加上页面JS吧

$("#btn_toSubmit").click(function () {            var url = '@(Url.Action("InExportToExcelAgreement", "ExcelTonji"))?{0}'.format($("#form1").serialize());            $("#form1").attr("action", url);            $("#form1").submit();            $("#form1").attr("action", "");        });