数据集导出到excel 并添加背景类

 前提需要两个 dll 文件  Aspose.Cells.dll   5.几的版本可用    和System.Drawing.dll

using Aspose.Cells;
using System.Drawing;

   public class excel
    {
        public void FillDataTableTemp(DataTable Table, string ColNames, int BeginCol, int iBeginRow) // 数据集，列名，开始行，开始列
        {
            Aspose.Cells.Workbook workbook = new Workbook();
            workbook.Open(Server.MapPath("~") + "\\OfficeReportTemplate\\MYJD_kty.xls");   // 后面那个参数是excel模版 的名字
            Aspose.Cells.Worksheet sheet = (Worksheet)workbook.Worksheets[0];
         
            string[] ss = ColNames.Split(',');
            int TempCol = BeginCol;
            int TotalRowIndex = -1;
            int LastRowIndex = -1;

         // 合并列。根据返回的值重写第一列  例如统计
            foreach (DataRow row in Table.Rows)
            {
                TempCol = BeginCol;
                foreach (string strColName in ss)
                {
                    if (TempCol == BeginCol)
                    {
                        switch (row[strColName].ToString())  //
                        {
                           case "返回值1;":
                                sheet.Cells.Merge(iBeginRow, TempCol, 1, 2); // 合并列，详细参数  写括号可看出
                                sheet.Cells[iBeginRow, TempCol].PutValue("合计");
                                sheet.Cells[iBeginRow, TempCol].SetStyle(this.BorderStyle);
                                //设置颜色
                                TotalRowIndex = iBeginRow;  // 记录要加背景的行
                                
                                TempCol++;
                                continue;
                            case "返回值2":
                                sheet.Cells[iBeginRow, TempCol].PutValue("总量");
                                sheet.Cells.Merge(iBeginRow, TempCol, 1, 3);  //
                                sheet.Cells[iBeginRow, TempCol].SetStyle(this.BorderStyle);
                                TempCol++;
                                continue;
                                }
                    }
                    sheet.Cells[iBeginRow, TempCol].PutValue(row[strColName].ToString());
                    sheet.Cells[iBeginRow, TempCol].SetStyle(this.BorderStyle);
                    TempCol++;
                }
                sheet.Cells.SetRowHeight(iBeginRow, 25);       
                // 根据设置的需要填充颜色的行，设置背景色，并设置边框
                if (iBeginRow == TotalRowIndex || iBeginRow==LastRowIndex)
                {                  
                    for (int i = 0; i < 5; i++)//      5是表格的列数
                    {
                        sheet.Cells[iBeginRow, i].SetStyle(BorderColorStyle);
                    }
                }
                iBeginRow += 1;
            }
            String filename = string.Format("{0}.xls", DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString());
            Response.ContentType = "application/ms-excel;charset=utf-8";
            Response.AddHeader("content-disposition", "attachment; filename=" + filename);
            System.IO.MemoryStream memStream = workbook.SaveToStream();
            Response.BinaryWrite(memStream.ToArray());
            Response.End(); 
        }
        private Aspose.Cells.Style _BorderColorStyle;
        /// <summary>
        /// 边框并带颜色的样式
        /// </summary>
        private Aspose.Cells.Style BorderColorStyle
        {
            get
            {
                if (_BorderColorStyle == null)
                {
                    Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
                    Aspose.Cells.Style style = wb.Styles[wb.Styles.Add()];
                    style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;
                    style.ForegroundColor = System.Drawing.Color.WhiteSmoke;
                    style.Pattern = BackgroundType.Solid;
                    style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
                    style.Borders[BorderType.TopBorder].Color = Color.Black;
                    style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; ;
                    style.Borders[BorderType.BottomBorder].Color = Color.Black;
                    style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin; ;
                    style.Borders[BorderType.LeftBorder].Color = Color.Black;
                    style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
                    style.Borders[BorderType.RightBorder].Color = Color.Black;
                    this._BorderColorStyle = style;
                    return this._BorderColorStyle;
                }
                else
                {
                    return _BorderColorStyle;
                }
            }
        }
        private Aspose.Cells.Style _BorderStyle;
        /// <summary>
        /// 边框并带颜色的样式
        /// </summary>
        private Aspose.Cells.Style BorderStyle
        {
            get
            {
                if (_BorderStyle == null)
                {
                    Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook();
                    Aspose.Cells.Style style = wb.Styles[wb.Styles.Add()];
                    style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
                    style.Borders[BorderType.TopBorder].Color = Color.Black;
                    style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
                    style.Borders[BorderType.BottomBorder].Color = Color.Black;
                    style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
                    style.Borders[BorderType.LeftBorder].Color = Color.Black;
                    style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;
                    style.Borders[BorderType.RightBorder].Color = Color.Black;
                    this._BorderStyle = style;
                    return this._BorderStyle;
                }
                else
                {
                    return _BorderStyle;
                }
            }
        }

}