本文实例为大家分享了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测试数据bine( "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测试数据mon
{
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;
}
}
}
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持服务器之家。
原文链接：https://blog.csdn.net/u012949335/article/details/81779215
dy("nrwz");
查看更多关于C#语言MVC框架Aspose.Cells控件导出Excel表数据的详细内容...