C#语言MVC框架Aspose.Cells控件导出Excel表数据

@{
 ViewBag.Title = "xx";
}


 
 汇总

public ActionResult dcExcel(stream sear)
 {
  DataTable dt = dal.GetKslyTj(" kd='"+sear+"'");
  var stream=PrintExcelApose.PrintExcel(dt, Server.MapPath(Path.Combine("../Ex/dd/", "ddd.xls")), "xx信息");
  return File(stream, "application/octet-stream", "xx信息.xls");
 
 }

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using Aspose.Cells;
using System.IO;
 
namespace YidiTutor.Common
{
 public class PrintExcelApose
 {
 public PrintExcelApose()
 {
 
 }
 
 
 /// 

 /// 打印Excel模板
 /// 
 /// 数据源Datatable
 /// Excel路径
 /// 导出的文件名称
 public static byte[] PrintExcel(DataTable dt, string path, string filename)
 {
  
  //(&=[yddt].xh)
  filename = filename + ".xls";
  Aspose.Cells.WorkbookDesigner designer = new Aspose.Cells.WorkbookDesigner();
  dt.TableName = "yddt";
  designer.Open(path); //打开Excel模板
  designer.SetDataSource(dt);//设置数据源
  designer.Process();//自动赋值
  if (System.IO.File.Exists(filename))
  System.IO.File.Delete(filename);
  // designer.Save(System.Web.HttpUtility.UrlDecode(System.Text.Encoding.UTF8.GetBytes(filename),System.Text.Encoding.UTF8), Aspose.Cells.SaveType.OpenInExcel, Aspose.Cells.FileFormatType.Excel2003, HttpContext.Current.Response);
 
  return designer.Workbook.SaveToStream().ToArray();
  //HttpContext.Current.Response.End();
 
 }
 
 /// 

 /// 打印Excel模板
 /// 
 /// datatable数据
 /// datatable数据源
 /// Dictionary数据
 /// Excel地址
 /// 导出文件名称
 /// 是否可编辑,true不可修改,false可修改
 public static byte[] PrintExcel(DataTable dtinfo, DataTable dtlist, Dictionary dict, string path, string filename, bool protect = false)
 {
  filename = filename + ".xls";
  Aspose.Cells.WorkbookDesigner designer = new Aspose.Cells.WorkbookDesigner();
  dtlist.TableName = "yddt";
  designer.Open(path); //打开Excel模板
  Workbook workbook = designer.Workbook; //工作簿 
  if (dtinfo != null && dtinfo.Rows.Count > 0)
  {
  for (int i = 0; i < dtinfo.Rows.Count; i++)
  {
   for (int k = 0; k < dtinfo.Columns.Count; k++)
   {
   string column = dtinfo.Columns[k].ColumnName;
   workbook.Worksheets[0].Replace("$" + column.ToLower() + "$", dtinfo.Rows[i][column].ToString());
   }
  }
  }
  if (dict != null)
  {
  foreach (string j in dict.Keys)
  {
   workbook.Worksheets[0].Replace("$" + j.ToLower() + "$", dict[j].ToString());
  }
  }
  if (protect)
  workbook.Worksheets[0].Protect(ProtectionType.All, "xakj..123", "");
  designer.SetDataSource(dtlist);//设置数据源
  designer.Process();//自动赋值
  if (System.IO.File.Exists(filename))
  System.IO.File.Delete(filename);
  // designer.Save(System.Web.HttpUtility.UrlEncode(System.Text.Encoding.UTF8.GetBytes(filename)), Aspose.Cells.SaveType.OpenInExcel, Aspose.Cells.FileFormatType.Excel2003, HttpContext.Current.Response);
  //designer.Save(System.Web.HttpUtility.UrlDecode(System.Text.Encoding.UTF8.GetBytes(filename),System.Text.Encoding.UTF8), Aspose.Cells.SaveType.OpenInExcel, Aspose.Cells.FileFormatType.Excel2003, HttpContext.Current.Response);
  //HttpContext.Current.Response.End();
  return designer.Workbook.SaveToStream().ToArray();
 
 
 }
 
 /// 

 /// 打印Excel模板
 /// 
 /// 填充Excel数据类
 public static byte[] PrintExcel(PrintExcel printexcel)
 {
  printexcel.filename = printexcel.filename + ".xls";
  Aspose.Cells.WorkbookDesigner designer = new Aspose.Cells.WorkbookDesigner();
  printexcel.dtlist.TableName = "yddt";
  designer.Open(printexcel.path); //打开Excel模板
  MergeExcel merge = printexcel.merge;//合并单元格
  Workbook workbook = designer.Workbook; //工作簿 
  if (printexcel.dtinfo != null && printexcel.dtinfo.Rows.Count > 0)
  {
  for (int i = 0; i < printexcel.dtinfo.Rows.Count; i++)
  {
   for (int k = 0; k < printexcel.dtinfo.Columns.Count; k++)
   {
   string column = printexcel.dtinfo.Columns[k].ColumnName;
   workbook.Worksheets[0].Replace("$" + column.ToLower() + "$", printexcel.dtinfo.Rows[i][column].ToString());
   }
  }
  }
  if (printexcel.dict != null)
  {
  foreach (string j in printexcel.dict.Keys)
  {
   workbook.Worksheets[0].Replace("$" + j.ToLower() + "$", printexcel.dict[j].ToString());
  }
  }
  if (printexcel.protect)
  workbook.Worksheets[0].Protect(ProtectionType.All, "xakj..123", "");
  designer.SetDataSource(printexcel.dtlist);//设置数据源
  designer.Process();//自动赋值
 
  //合格单元格
  if (printexcel.dtlist != null && printexcel.dtlist.Rows.Count > 0)
  {
  Aspose.Cells.Worksheet sheet = designer.Workbook.Worksheets[0];
  Cells cel = sheet.Cells;
  List column = printexcel.merge.firstColumn;
  int j = 1;
  for (int i = 0; i < printexcel.dtlist.Rows.Count; i++)
  {
   for (int kk = 0; kk < column.Count; kk++)
   {
   cel.Merge(merge.firstRow, column[kk], Convert.ToInt32(printexcel.dtlist.Rows[i][merge.columnname].ToString().Trim()), merge.columnNumber);
   cel[merge.firstRow, column[kk]].PutValue(j);
   i = i + Convert.ToInt32(printexcel.dtlist.Rows[i][merge.columnname].ToString().Trim()) - 1;
   j++;
   }
  }
  }
 
  if (System.IO.File.Exists(printexcel.filename))
  System.IO.File.Delete(printexcel.filename);
  //designer.Save(System.Web.HttpUtility.UrlEncode(System.Text.Encoding.UTF8.GetBytes(printexcel.filename)), Aspose.Cells.SaveType.OpenInExcel, Aspose.Cells.FileFormatType.Excel2003, HttpContext.Current.Response);
  //HttpContext.Current.Response.End();
  return designer.Workbook.SaveToStream().ToArray();  
 }
 }
 
 
 /// 

 /// 填充Excel数据
 /// 
 public class PrintExcel
 {
 /// 

 /// 数据
 /// 
 public DataTable dtinfo;
 /// 

 /// 数据源
 /// 
 public DataTable dtlist;
 /// 

 /// 数据
 /// 
 public Dictionary dict;
 /// 

 /// Excel地址
 /// 
 public string path;
 /// 

 /// 导出Excel名称
 /// 
 public string filename;
 /// 

 /// 是否可编辑
 /// 
 public bool protect = false;
 /// 

 /// 合并单元格
 /// 
 public MergeExcel merge;
 }
 /// 

 /// 合格单元格
 /// 
 public class MergeExcel
 {
 /// 

 /// 第几行
 /// 
 public int firstRow;
 /// 

 /// 合并的第几列
 /// 
 public List firstColumn;
 /// 

 /// 合并行数
 /// 
 public int rowNumber;
 /// 

 /// 合并的列数
 /// 
 public int columnNumber;
 /// 

 /// datatable合并行数的列名称字段
 /// 
 public string columnname;
 }
 
 
 public class AsposeExcell
 {
 public static DataTable ExportToDataTableAsString(string excelFilePath, bool showTitle = true)
 {
  Workbook workbook = new Workbook();
  workbook.Open(excelFilePath);
  Cells cells = workbook.Worksheets[0].Cells;
  System.Data.DataTable dataTable2 = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxColumn + 1, showTitle);//showTitle
  return dataTable2;
 }
 public static DataTable ExportToDataTableAsString(Stream stream, bool showTitle = true)
 {
  Workbook workbook = new Workbook();
  workbook.Open(stream);
  Cells cells = workbook.Worksheets[0].Cells;
  System.Data.DataTable dataTable2 = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxColumn + 1, showTitle);//showTitle
  return dataTable2;
 }
 public static Stream FileToStream(string fileName)
 {
  // 打开文件
  FileStream fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.Read);
  // 读取文件的 byte[]
  byte[] bytes = new byte[fileStream.Length];
  fileStream.Read(bytes, 0, bytes.Length);
  fileStream.Close();
  // 把 byte[] 转换成 Stream
  Stream stream = new MemoryStream(bytes);
  return stream;
 }
 }
}