using Aspose.Cells;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
namespace Sheets.common
{
public class OperateExcel
{
/// <summary>
/// 导出的文件保存到这里
/// </summary>
private static string ExportFilesPath = System.Configuration.ConfigurationManager.AppSettings[
"exportFilesPath"].ToString();
/// <summary>
/// 将DataTable生成Excel
/// </summary>
/// <param name="dtList">DataTable</param>
/// <param name="fileName">文件名</param>
/// <returns>返回文件路径名</returns>
#region DataTable生成Excel
public static string ExportToExcel(DataTable dtList,
string fileName)
{
//这里是利用Aspose.Cells.dll 生成excel文件的
string pathToFiles =
System.Web.HttpContext.Current.Server.MapPath(ExportFilesPath);
string etsName =
".xls";
//获取保存路径
string path = pathToFiles + fileName +
etsName;
Workbook wb =
new Workbook();
Worksheet ws = wb.Worksheets[
0];
Cells cell =
ws.Cells;
//设置行高
//cell.SetRowHeight(0, 20);
//表头样式
Style stHeadLeft =
wb.Styles[wb.Styles.Add()];
stHeadLeft.HorizontalAlignment = TextAlignmentType.Left;
//文字居中
stHeadLeft.Font.Name =
"宋体";
stHeadLeft.Font.IsBold =
true;
//设置粗体
stHeadLeft.Font.Size =
14;
//设置字体大小
Style stHeadRight =
wb.Styles[wb.Styles.Add()];
stHeadRight.HorizontalAlignment = TextAlignmentType.Right;
//文字居中
stHeadRight.Font.Name =
"宋体";
stHeadRight.Font.IsBold =
true;
//设置粗体
stHeadRight.Font.Size =
14;
//设置字体大小
//内容样式
Style stContentLeft =
wb.Styles[wb.Styles.Add()];
stContentLeft.HorizontalAlignment =
TextAlignmentType.Left;
stContentLeft.Font.Size =
10;
Style stContentRight =
wb.Styles[wb.Styles.Add()];
stContentRight.HorizontalAlignment =
TextAlignmentType.Right;
stContentRight.Font.Size =
10;
//赋值给Excel内容
for (
int col =
0; col < dtList.Columns.Count; col++
)
{
//Style stHead = null;
////Style stContent = null;
////设置表头
//string columnType = dtList.Columns[col].DataType.ToString();
//switch (columnType.ToLower())
//{
// //如果类型是string，则靠左对齐(对齐方式看项目需求修改)
// case "system.string":
// stHead = stHeadLeft;
// //stContent = stContentLeft;
// break;
// default:
// stHead = stHeadRight;
// //stContent = stContentRight;
// break;
//}
putValue(cell, dtList.Columns[col].ColumnName,
0, col);
for (
int row =
0; row < dtList.Rows.Count; row++
)
{
putValue(cell, dtList.Rows[row][col], row +
1, col);
}
}
wb.Save(path);
return ExportFilesPath + fileName +
etsName;
}
#endregion
private static void putValue(Cells cell,
object value,
int row,
int column)
{
//填充数据到excel中
cell[row, column].PutValue(value);
// cell[row, column].SetStyle(st);
}
}
}
转载于:https://www.cnblogs.com/siyunianhua/p/7248573.html
相关资源：Aspose无需模板导出Excel带Chart
