下载导出自定义格式Excel文件

///
/// 下载数据
///
/// 下载条件
///

    public FileContentResult GetsjslhzbExcel(print_sjslhzbModel model)
    { 
        print_sjslhzbProvider provider = new print_sjslhzbProvider();
        provider.CreateTable(model, hzwhere);

        DataSet ds = provider.GetListByHzb().data;
        // 文件标题
        string   fileName = "实际水量";
    
        fileName = fileName+"分管人员汇总表";

        Workbook workbook = new Workbook();

        Worksheet sheet = workbook.Worksheets[0];
        sheet.Protection.AllowFormattingColumn = true; // 允许修改列
        sheet.Protection.AllowFormattingRow = true;// 允许修改行

        // 设置所有列的宽度
        Cells cells = sheet.Cells;
       

        #region 样式

        Style styleTitle = workbook.Styles[workbook.Styles.Add()];
        styleTitle.IsLocked = true;
        styleTitle.Font.Name = "宋体";
        styleTitle.Font.Size = 16;
        styleTitle.Font.IsBold = true;
        styleTitle.IsTextWrapped = true;//单元格内容自动换行 
        styleTitle.HorizontalAlignment = TextAlignmentType.Center; // 文字对齐方式
        styleTitle.VerticalAlignment = TextAlignmentType.Center;

        Style styleUnit = workbook.Styles[workbook.Styles.Add()];
        styleUnit.IsLocked = true;
        styleUnit.Font.Name = "宋体";
        styleUnit.Font.Size = 10;
        styleUnit.Font.IsBold = true;
        styleUnit.IsTextWrapped = true;//单元格内容自动换行 
        styleUnit.HorizontalAlignment = TextAlignmentType.Center; // 文字对齐方式
        styleUnit.VerticalAlignment = TextAlignmentType.Center;
        styleUnit.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Dashed;
        styleUnit.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Dashed;
        styleUnit.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Dashed;
        styleUnit.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Dashed;

        Style styleCenter = workbook.Styles[workbook.Styles.Add()];
        styleCenter.IsLocked = true;
        styleCenter.Font.Name = "宋体";
        styleCenter.Font.Size = 10;
        styleCenter.IsTextWrapped = true;//单元格内容自动换行 
        styleCenter.HorizontalAlignment = TextAlignmentType.Center; // 文字对齐方式
        styleCenter.VerticalAlignment = TextAlignmentType.Center;
        styleCenter.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Dashed;
        styleCenter.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Dashed;
        styleCenter.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Dashed;
        styleCenter.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Dashed;



        #endregion

        var ysxzList = ds.Tables[0];
        if (ysxzList.Rows.Count > 0)
        {
            var rowIndex = 0;
            
            var totalcolumn = ysxzList.Rows.Count+3;

            #region 头部显示文字

            // 第一行        
           
            
            cells.Merge(0, 0, 1, totalcolumn);
            cells[0, 0].PutValue(fileName);
            cells[0, 0].SetStyle(styleTitle);
            cells.SetRowHeight(0,38);

            //第二行
            cells[1, 0].PutValue("分管人员");
            cells[1, 1].PutValue("单位户数");
            
            var a = 2;
            for (var i=0;i<ysxzList.Rows.Count;i++) 
            {
                cells[1, a].PutValue(ysxzList.Rows[i]["ysmc"]);
                cells.SetColumnWidth(a, 12);

                a++;
            }
            cells[1, a].PutValue("合计");
            cells.SetRowHeight(1, 24.75);

           

            #endregion
            // 3    
            rowIndex = 2;
            var data = ds.Tables[1];
            for (var i = 0; i < data.Rows.Count; i++)
            {
                cells[rowIndex, 0].PutValue(data.Rows[i]["glxm"]);
                cells[rowIndex, 1].PutValue(data.Rows[i]["hs"]);
                a = 2;
                for (var j = 0; j < ysxzList.Rows.Count; j++)
                {
                    cells[rowIndex, a].PutValue(data.Rows[i][ysxzList.Rows[j]["ysxz"].ToString()]);
                    a++;
                }
                cells[rowIndex, a].PutValue(data.Rows[i]["hj"]);
                cells.SetRowHeight(rowIndex, 19);

                rowIndex++;
            }
            cells.SetColumnWidth(0, 14);
            cells.SetColumnWidth(1, 12);
            cells.SetColumnWidth(a, 12);

            //设置样式

            Range cellRangeData1 = sheet.Cells.CreateRange(1, 0, 1, totalcolumn);
            cellRangeData1.SetStyle(styleUnit);

            Range cellRangeData2 = sheet.Cells.CreateRange(2, 0, data.Rows.Count, totalcolumn);
            cellRangeData2.SetStyle(styleCenter);

            //Range cellRangeData21 = sheet.Cells.CreateRange(5, 2, dt.Rows.Count + 2, totalcolumn - 2);
            //cellRangeData21.SetStyle(styleRight);

        }

        PageSetup page = sheet.PageSetup;
        page.PaperSize = PaperSizeType.PaperA4;
        page.Orientation = PageOrientationType.Landscape;

        page.CenterHorizontally = true;//页边距水平居中
        //page.CenterVertically = true;//页边距垂直居中

        System.IO.MemoryStream ms = workbook.SaveToStream();
        return File(ms.ToArray(), "application/vnd.ms-excel", fileName + ".xls");
    }