C#語言MVC框架Aspose.Cells控件導出Excel表數(shù)據(jù)
更新時間：2018年12月15日 11:38:16 作者：筱筱脫脫
這篇文章主要為大家詳細介紹了C#語言MVC框架Aspose.Cells控件導出Excel表數(shù)據(jù)，具有一定的參考價值，感興趣的小伙伴們可以參考一下
本文實例為大家分享了Aspose.Cells控件導出Excel表數(shù)據(jù)的具體代碼，供大家參考，具體內(nèi)容如下
控件bin文件下載地址
@{
ViewBag.Title = "xx";
}
<script type="text/javascript" language="javascript">
function getparam() {
var param = {};
param.sear = $("#sear").textbox('getValue');
return param;
}
//這樣寫是為了傳參數(shù)
function print_dc()
{
var param = getparam();//參數(shù)
var formobj=$("<form id='form1' action='dcExcel' method='post'></from>");
//參數(shù)
$("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">數(shù)據(jù)源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);//設(shè)置數(shù)據(jù)源
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數(shù)據(jù)</param>
/// <param name="dtlist">datatable數(shù)據(jù)源</param>
/// <param name="dict">Dictionary數(shù)據(jù)</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);//設(shè)置數(shù)據(jù)源
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數(shù)據(jù)類</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);//設(shè)置數(shù)據(jù)源
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數(shù)據(jù)
/// </summary>
public class PrintExcel
{
/// <summary>
/// 數(shù)據(jù)
/// </summary>
public DataTable dtinfo;
/// <summary>
/// 數(shù)據(jù)源
/// </summary>
public DataTable dtlist;
/// <summary>
/// 數(shù)據(jù)
/// </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>
/// 合并行數(shù)
/// </summary>
public int rowNumber;
/// <summary>
/// 合并的列數(shù)
/// </summary>
public int columnNumber;
/// <summary>
/// datatable合并行數(shù)的列名稱字段
/// </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[] 轉(zhuǎn)換成 Stream
Stream stream = new MemoryStream(bytes);
return stream;
}
}
}
以上就是本文的全部內(nèi)容，希望對大家的學習有所幫助，也希望大家多多支持腳本之家。
您可能感興趣的文章:
- C# Aspose.Words 刪除word中的圖片操作
- C#使用Aspose.Cells導出excel
- C#使用Aspose.Cells創(chuàng)建和讀取Excel文件
- C#使用Aspose.Cells控件讀取Excel
- C# 利用Aspose.Words.dll將 Word 轉(zhuǎn)成PDF
- C# 調(diào)用exe傳參,并獲取打印值的實例
- 如何利用C#打印九九乘法表
- c# winform 解決PictureBox 無法打印全部圖片的問題
- C#操作Word打印的示例
- C# 打印網(wǎng)頁不顯示頁眉頁腳的實現(xiàn)方法
- C#實現(xiàn)掃描槍掃描二維碼并打印(實例代碼)
- C#利用PrintDocument定制打印單據(jù)的小例子
- c#使用Aspose打印文件的示例
相關(guān)文章
用C#編寫獲取遠程IP,MAC的方法...2007-04-04
可替代log4j日志的c#簡單日志類隊列實現(xiàn)類代碼分享
簡單日志類隊列實現(xiàn)。可按天周月年大小分割文件?？珊唵翁娲鷏og4j2013-12-12
多線程編程時，如果涉及同時讀寫共享數(shù)據(jù)，就要格外小心。如果共享數(shù)據(jù)是獨占資源，則要對共享數(shù)據(jù)的讀寫進行排它訪問，最簡單的方式就是加鎖。鎖也不能隨便用，否則可能會造成死鎖和活鎖。本文將通過示例詳細講解死鎖和活鎖是如何發(fā)生的以及如何避免它們。2020-11-11
C#根據(jù)反射和特性實現(xiàn)ORM映射實例分析
這篇文章主要介紹了C#根據(jù)反射和特性實現(xiàn)ORM映射的方法,實例分析了反射的原理、特性與ORM的實現(xiàn)技巧,具有一定參考借鑒價值,需要的朋友可以參考下2015-04-04
這篇文章主要為大家詳細介紹了Unity實現(xiàn)文本轉(zhuǎn)貼圖，文中示例代碼介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2021-05-05
C# CultureInfo之常用InvariantCulture案例詳解
這篇文章主要介紹了C# CultureInfo之常用InvariantCulture案例詳解,本篇文章通過簡要的案例,講解了該項技術(shù)的了解與使用,以下就是詳細內(nèi)容,需要的朋友可以參考下2021-08-08
在項目中經(jīng)常要獲取用戶的上網(wǎng)的IP地址，如何獲取用戶的IP地址，方法很多，現(xiàn)在介紹以下2種2014-01-01
C#?Winform消息通知之系統(tǒng)本地通知local?toast?notification
這篇文章主要為大家介紹了C#?Winform消息通知之系統(tǒng)本地通知local?toast?notification使用詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪2022-08-08
C#基于純數(shù)學方法遞歸實現(xiàn)貨幣數(shù)字轉(zhuǎn)換中文功能詳解
這篇文章主要介紹了C#基于純數(shù)學方法遞歸實現(xiàn)貨幣數(shù)字轉(zhuǎn)換中文功能,涉及C#針對字符串的遍歷、轉(zhuǎn)換與數(shù)學運算相關(guān)操作技巧,需要的朋友可以參考下2017-02-02
c# Selenium爬取數(shù)據(jù)時防止webdriver封爬蟲的方法
這篇文章主要介紹了c# Selenium爬取數(shù)據(jù)時防止webdriver封爬蟲的方法，幫助大家更好的理解和使用c#，感興趣的朋友可以了解下2021-01-01