(精华)2020年6月26日 C#类库 Aspose组件的Office文件操作帮助类
【摘要】
using Aspose.Cells;
using System;
using System.Data;
using System.IO;
using System.Text;
namespace Co...
using Aspose.Cells;
using System;
using System.Data;
using System.IO;
using System.Text;
namespace Core.Util
{
/// <summary>
/// 使用Aspose组件的Office文件操作帮助类
/// </summary>
public class AsposeOfficeHelper
{
static AsposeOfficeHelper()
{
//注册EncodingProvider防止乱码
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
}
/// <summary>
/// 将DataTable输出为字节数组
/// </summary>
/// <param name="dt">表格数据</param>
/// <returns>Byte数组</returns>
public static byte[] DataTableToExcelBytes(DataTable dt)
{
Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
Cells cells = sheet.Cells;
int Colnum = dt.Columns.Count;//表格列数
int Rownum = dt.Rows.Count;//表格行数
//生成行 列名行
for (int i = 0; i < Colnum; i++)
{
cells[0, i].PutValue(dt.Columns[i].ColumnName);
}
//生成数据行
for (int i = 0; i < Rownum; i++)
{
for (int k = 0; k < Colnum; k++)
{
cells[1 + i, k].PutValue(dt.Rows[i][k].ToString());
}
}
//自动行高，列宽
sheet.AutoFitColumns();
sheet.AutoFitRows();
//将DataTable写入内存流
var ms = new MemoryStream();
book.Save(ms, SaveFormat.Excel97To2003);
return ms.ToArray();
}
/// <summary>
/// 通过模板导出Excel
/// </summary>
/// <param name="templateFile">模板</param>
/// <param name="dataSource">数据源</param>
/// <returns>文件Byte[]</returns>
public static byte[] ExportExcelByTemplate(string templateFile, params (string SourceName, object Data)[] dataSource)
{
if (templateFile.IsNullOrEmpty())
throw new Exception("模板不能为空");
if (dataSource.Length == 0)
throw new Exception("数据源不能为空");
WorkbookDesigner designer = new WorkbookDesigner
{
Workbook = new Workbook(templateFile)
};
var workBook = designer.Workbook;
dataSource.ForEach(aDataSource =>
{
designer.SetDataSource(aDataSource.SourceName, aDataSource.Data);
});
designer.Process();
using (MemoryStream stream = new MemoryStream())
{
workBook.Save(stream, SaveFormat.Excel97To2003);
var fileBytes = stream.ToArray();
return fileBytes;
}
}
/// <summary>
/// 从excel文件导入数据
/// 注：默认将第一行当作标题行，即不当作数据
/// </summary>
/// <param name="fileNmae">文件名</param>
/// <returns></returns>
public static DataTable ReadExcel(string fileNmae)
{
Workbook book = new Workbook(fileNmae);
Worksheet sheet = book.Worksheets[0];
Cells cells = sheet.Cells;
return cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
}
/// <summary>
/// 从excel文件导入数据
/// </summary>
/// <param name="fileNmae">文件名</param>
/// <param name="exportColumnName">是否将第一行当作标题行</param>
/// <returns></returns>
public static DataTable ReadExcel(string fileNmae,bool exportColumnName)
{
Workbook book = new Workbook(fileNmae);
Worksheet sheet = book.Worksheets[0];
Cells cells = sheet.Cells;
return cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, exportColumnName);
}
/// <summary>
/// 从excel文件字节源导入
/// 注：默认将第一行当作标题行，即不当作数据
/// </summary>
/// <param name="fileBytes">文件字节源</param>
/// <returns></returns>
public static DataTable ReadExcel(byte[] fileBytes)
{
return ReadExcel(fileBytes, true);
}
/// <summary>
/// 从excel文件字节源导入
/// </summary>
/// <param name="fileBytes">文件字节源</param>
/// <param name="exportColumnName">是否将第一行当作标题行</param>
/// <returns></returns>
public static DataTable ReadExcel(byte[] fileBytes,bool exportColumnName)
{
using (MemoryStream ms = new MemoryStream(fileBytes))
{
Workbook book = new Workbook(ms);
Worksheet sheet = book.Worksheets[0];
Cells cells = sheet.Cells;
return cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, exportColumnName);
}
}
}
}
文章来源: codeboy.blog.csdn.net，作者：愚公搬代码，版权归原作者所有，如需转载，请联系作者。
原文链接：codeboy.blog.csdn.net/article/details/106965913
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
作者其他文章
评论（0）