正文
Aspose.Cells for .NET 8.5.0 工具类
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
基于 Aspose.Cells for .NET 8.5.0 工具类，
Aspose.Cells for .NET 8.5.0 这个自己去CSDN下载里面有破解的，没有破解的导出excel的时候会（Aspose.Cells）这样的logo
var workbook = new Workbook(fstream, loadOptions);
创建Workbook对象建议传入需要格式：new LoadOptions(LoadFormat.Xlsx)（可以指定自己需要的格式），
不然后面使用workbook解析Excel和导出Excel的时候会报格式化出错，官网里面建议传入需要格式的LoadOptions。老版本的Aspose.Cells 使用的是save和load的时候传入loadOptions，这些方法在高版本中已经过期了，
高版本是在创建对象的时候传入的。
response.ContentType = "application/ms-excel"; 自己设置需要导出的格式，
另外附上ContentType 对照表：http://tool.oschina.net/commons
/// <summary>
/// AsposeExcel 帮助类
/// </summary>
public class AsposeExcelUtil
{
private const int BufferSize = 0x1000;
/// <summary>
/// 读取Excel文件
/// </summary>
/// <param name="fullFilename">文件地址</param>
/// <returns></returns>
public static System.Data.DataTable ReadExcel(String fullFilename)
{
return ReadExcel(fullFilename, new LoadOptions(LoadFormat.Xlsx));
} /// <summary>
/// 读取Excel文件
/// </summary>
/// <param name="fullFilename">文件地址</param>
/// <param name="loadOptions">参数信息【如：读取包含密码的excel】</param>
/// <returns></returns>
public static System.Data.DataTable ReadExcel(String fullFilename, LoadOptions loadOptions)
{
var fstream = new System.IO.FileStream(fullFilename, FileMode.Open);
var workbook = new Workbook(fstream, loadOptions); Worksheet sheet = workbook.Worksheets[]; Cells cells = sheet.Cells;
return cells.ExportDataTableAsString(, , cells.MaxDataRow + , cells.MaxDataColumn + , true);
} private static void MakeHeader(Worksheet sheet, DataTable dt)
{
for (int col = ; col < dt.Columns.Count; col++)
{
Cell cell = sheet.Cells[, col];
cell.SetStyle(MakeHealStyle());
}
} /// <summary>
/// 创建默认的excel头样式
/// </summary>
/// <returns></returns>
private static Style MakeHealStyle()
{
var style = new Style();
style.Font.Name = "微软雅黑";//文字字体
style.Font.Size = ;//文字大小
style.IsLocked = false;//单元格解锁
style.Font.IsBold = true;//粗体
style.Font.IsBold = true;
style.Pattern = BackgroundType.Solid; //设置背景样式
style.IsTextWrapped = true;//单元格内容自动换行
style.SetBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
style.SetBorder(BorderType.BottomBorder, CellBorderType.Thin, Color.Black);
style.SetBorder(BorderType.RightBorder, CellBorderType.Thin, Color.Black);
style.HorizontalAlignment = TextAlignmentType.Center;//文字居中
style.IsTextWrapped = false;//setTextWrapped
return style;
} private static void MakeBody(Worksheet sheet, DataTable dt)
{
for (int r = ; r < dt.Rows.Count; r++)
{
for (int c = ; c < dt.Columns.Count; c++)
{
sheet.Cells[r + , c].PutValue(dt.Rows[r][c].ToString());
}
}
} /// <summary>
/// 把dt转换成excel的字节数组【不建议使用】
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static Byte[] DatatableToExcelBytes(DataTable dt)
{
try
{
return DatatableToExcelStream(dt).ToArray();
}
catch (Exception e)
{
return null;
}
} /// <summary>
/// 把dt转换成excel的流
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static MemoryStream DatatableToExcelStream(DataTable dt)
{
try
{
var workbook = new Workbook();
var sheet = workbook.Worksheets[];
sheet.Cells.ImportDataTable(dt, true, , );
MakeHeader(sheet, dt);
sheet.AutoFitColumns();
sheet.AutoFitRows();
var memory = new MemoryStream();
workbook.Save(memory, SaveFormat.Xlsx);
memory.Seek(, SeekOrigin.Begin);
return memory;
}
catch (Exception e)
{
return null;
}
} /// <summary>
/// 根据Dt生成 excel文件
/// </summary>
/// <param name="dt"></param>
/// <param name="exportFileName"></param>
/// <returns></returns>
public static Boolean DatatableToExcel(DataTable dt, string exportFileName)
{
try
{
var workbook = new Workbook();
var sheet = workbook.Worksheets[];
sheet.Cells.ImportDataTable(dt, true, , );
MakeHeader(sheet, dt);
sheet.AutoFitColumns();
sheet.AutoFitRows();
workbook.Save(exportFileName, SaveFormat.Xlsx);
return true;
}
catch (Exception e)
{
return false;
}
} /// <summary>
/// 将集合对象装换成Excel的字节数组
/// </summary>
/// <typeparam name="T">对象的类型</typeparam>
/// <param name="data">集合</param>
public static byte[] ConvertToExportByte<T>(IEnumerable<T> data)
{
return ConvertToExportStream<T>(data).ToArray();
} /// <summary>
/// 将对象转换成流
/// </summary>
/// <param name="data"></param>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public static MemoryStream ConvertToExportStream<T>(IEnumerable<T> data)
{
var workbook = new Workbook();
var sheet = workbook.Worksheets[];
PropertyInfo[] infos = typeof(T).GetProperties();
var colIndex = "A";
foreach (var p in infos)
{
Cell cell = sheet.Cells[colIndex + ];
Style style = MakeHealStyle();
cell.SetStyle(style);
cell.PutValue(p.Name);
int i = ;
foreach (var d in data)
{
sheet.Cells[colIndex + i].PutValue(p.GetValue(d, null));
i++;
}
colIndex = ((char)(colIndex[] + )).ToString(CultureInfo.InvariantCulture);
}
var memory = new MemoryStream();
workbook.Save(memory, SaveFormat.Xlsx);
memory.Seek(, SeekOrigin.Begin);
return memory;
} /// <summary>
/// 根据 datatable 导出文件
/// </summary>
/// <param name="data"></param>
/// <param name="response"></param>
/// <param name="exportFileName"></param>
public static void Export(DataTable data, HttpResponse response, String exportFileName)
{
response.Clear();
response.Buffer = true;
response.Charset = "UTF8";
response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(exportFileName, Encoding.UTF8).ToString(CultureInfo.InvariantCulture));
response.ContentEncoding = System.Text.Encoding.UTF8;
response.ContentType = "application/ms-excel";
Stream outputStream = DatatableToExcelStream(data);
var buffer = new byte[BufferSize];
int bytesRead = ;
while ((bytesRead = outputStream.Read(buffer, , BufferSize)) > )
{
response.OutputStream.Write(buffer, , bytesRead);
}
outputStream.Close();
response.Flush();
response.End();
} /// <summary>
/// 根据 datatable 导出文件
/// </summary>
/// <param name="data"></param>
/// <param name="response"></param>
/// <param name="exportFileName"></param>
public static void Export(DataTable data, HttpResponseBase response, String exportFileName)
{
response.Clear();
response.Buffer = true;
response.Charset = "UTF8";
response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(exportFileName, Encoding.UTF8).ToString(CultureInfo.InvariantCulture));
response.ContentEncoding = System.Text.Encoding.UTF8;
response.ContentType = "application/ms-excel";
Stream outputStream = DatatableToExcelStream(data);
var buffer = new byte[BufferSize];
int bytesRead = ;
while ((bytesRead = outputStream.Read(buffer, , BufferSize)) > )
{
response.OutputStream.Write(buffer, , bytesRead);
}
outputStream.Close();
response.Flush();
response.End();
} /// <summary>
/// 导出数据
/// </summary>
/// <typeparam name="T">对象类型</typeparam>
/// <param name="data">集合</param>
/// <param name="response">HttpResponse</param>
/// <param name="exportFileName">导出的文件名称</param>
public static void Export<T>(IEnumerable<T> data, HttpResponse response, String exportFileName)
{
response.Clear();
response.Buffer = true;
response.Charset = "UTF8";
response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(exportFileName, Encoding.UTF8).ToString(CultureInfo.InvariantCulture));
response.ContentEncoding = System.Text.Encoding.UTF8;
response.ContentType = "application/ms-excel";
Stream outputStream = ConvertToExportStream<T>(data);
var buffer = new byte[BufferSize];
int bytesRead = ;
while ((bytesRead = outputStream.Read(buffer, , BufferSize)) > )
{
response.OutputStream.Write(buffer, , bytesRead);
}
outputStream.Close();
response.Flush();
response.End();
} /// <summary>
/// 导出数据
/// </summary>
/// <typeparam name="T">对象类型</typeparam>
/// <param name="data">集合</param>
/// <param name="response">HttpResponse</param>
/// <param name="exportFileName">导出的文件名称</param>
public static void Export<T>(IEnumerable<T> data, HttpResponseBase response, String exportFileName)
{
response.Clear();
response.Buffer = true;
response.Charset = "UTF8";
response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(exportFileName, Encoding.UTF8).ToString(CultureInfo.InvariantCulture));
response.ContentEncoding = System.Text.Encoding.UTF8;
response.ContentType = "application/ms-excel";
Stream outputStream = ConvertToExportStream<T>(data);
var buffer = new byte[BufferSize];
int bytesRead = ;
while ((bytesRead = outputStream.Read(buffer, , BufferSize)) > )
{
response.OutputStream.Write(buffer, , bytesRead);
}
outputStream.Close();
response.Flush();
response.End();
}
}
