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

本文实例为大家分享了aspose.cells控件导出excel表数据的具体代码，供大家参考，具体内容如下

控件bin文件下载地址

@{
 viewbag.title = "xx";
}
<script type="text/javascript" language="javascript">
 function getparam() {
 var param = {};
 param.sear = $("#sear").textbox('getvalue');
 return param;
 }
 //这样写是为了传参数
 function print_dc()
 {
 var param = getparam();//参数
 var formobj=$("<form id='form1' action='dcexcel' method='post'></from>");
 //参数
 $("body").append(formobj);
 $.each(param,function(i,o){
  var input1=$("<input type='hidden' value='"+o+"' name='"+i+"'/>");
  input1.appendto(formobj);
 });
 
 formobj.submit();
 }
</script>
<input class="easyui-textbox" id="sear" data-options="width:80"/>
 <button type="button" class="but-primary" onclick="print_dc()">
 汇总</button>

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()
 {
 
 }
 
 
 /// <summary>
 /// 打印excel模板
 /// </summary>
 /// <param name="dt">数据源datatable</param>
 /// <param name="path">excel路径</param>
 /// <param name="filename">导出的文件名称</param>
 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();
 
 }
 
 /// <summary>
 /// 打印excel模板
 /// </summary>
 /// <param name="dtinfo">datatable数据</param>
 /// <param name="dtlist">datatable数据源</param>
 /// <param name="dict">dictionary数据</param>
 /// <param name="path">excel地址</param>
 /// <param name="filename">导出文件名称</param>
 /// <param name="protect">是否可编辑,true不可修改,false可修改</param>
 public static byte[] printexcel(datatable dtinfo, datatable dtlist, dictionary<string, string> 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();
 
 
 }
 
 /// <summary>
 /// 打印excel模板
 /// </summary>
 /// <param name="printexcel">填充excel数据类</param>
 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<int> 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();  
 }
 }
 
 
 /// <summary>
 /// 填充excel数据
 /// </summary>
 public class printexcel
 {
 /// <summary>
 /// 数据
 /// </summary>
 public datatable dtinfo;
 /// <summary>
 /// 数据源
 /// </summary>
 public datatable dtlist;
 /// <summary>
 /// 数据
 /// </summary>
 public dictionary<string, string> dict;
 /// <summary>
 /// excel地址
 /// </summary>
 public string path;
 /// <summary>
 /// 导出excel名称
 /// </summary>
 public string filename;
 /// <summary>
 /// 是否可编辑
 /// </summary>
 public bool protect = false;
 /// <summary>
 /// 合并单元格
 /// </summary>
 public mergeexcel merge;
 }
 /// <summary>
 /// 合格单元格
 /// </summary>
 public class mergeexcel
 {
 /// <summary>
 /// 第几行
 /// </summary>
 public int firstrow;
 /// <summary>
 /// 合并的第几列
 /// </summary>
 public list<int> firstcolumn;
 /// <summary>
 /// 合并行数
 /// </summary>
 public int rownumber;
 /// <summary>
 /// 合并的列数
 /// </summary>
 public int columnnumber;
 /// <summary>
 /// datatable合并行数的列名称字段
 /// </summary>
 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;
 }
 }
}

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。