MVC实现导出功能,并设置导出Execl表格的样式
效果图
JS代码
功能导出
function Export(i){
//标题
var Title ='@ ViewBag.Title'; //标题名称
var Function ='@ ViewBag.FunctionID'; //控制器名称
var HrefPath =“/”+ Function +“/ ExportExcelTo”; //路径+方法
$ .ajax({
url:HrefPath,
type:“ POST“,
data:{”eXcelName“:Title},
success:function(data){
if(data.code == 0){
// var Path =”/ Tem /职务管理.xls“;
var Path =”/ Tem /“+ Title +”。xls“;
window.location = Path;
//如需用的层需要添加引用<script src =“〜/ layui / layui.all.js”> </ script>
layer.open({
type:1,
id:2,
title:“信息”,
shadeClose :true,
area:['30%','25%'],
内容:“”+ data.msg +“”,
});
}
}
})
}
控制器里面的方法
public ActionResult ExportExcelTo(string eXcelName)
{
//实例化
Export _export = new Export();
//拼接存放路径
String mapPath = Server.MapPath(“/”); //找到发布项目的路径
string Path = mapPath +“Tem \\”+ eXcelName +“。xls”; // Tem是项目的文件夹
//获取类型
类型typSe = typeof(CW_AccountantSubjectList); //实体类类型
//实例化
MemoryStream ms = new MemoryStream(); //使用System.IO;
//调用方法
ReturnMsg rm = _export.ExportMs(ref ms,typSe,dTData,1,Path); // dtData是全局数据源
//返回字符串
返回Json(new {code = rm.code,msg = rm.msg,path = Path});
}
DAL里面的方法
using System.Data;
using Model.Export;
using System.IO;
using System;
using System.Reflection;
using System.ComponentModel.DataAnnotations;
using Common;
using NPOI.SS.Util;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HSSF.Util;
namespace DAL.ExportExcel
{
public class Export
{
public ReturnMsg ExportMs(ref MemoryStream ms, Type typse, DataTable dt, int type = 0, string path = "", string model = "")
{
if (dt == null || dt.Rows.Count <= 0)
{
return new ReturnMsg() { code = 1, msg = "导出数据为空!" };
}
else
{
ReturnMsg rm = ExportDataTable(ref ms, typse, dt, type, path);
return rm;
}
}
/// <summary>
/// 将DataTable导出Excel
/// </summary>
/// <returns></returns>
public ReturnMsg ExportDataTable(ref MemoryStream ms, Type typSe, DataTable dt, int type = 0, string path = "")
{
try
{
HSSFWorkbook book = new HSSFWorkbook(); //创建Excel文件的对象
ISheet sheet1 = book.CreateSheet("Sheet1"); //添加一个sheet
IRow row0 = sheet1.CreateRow(0); //给sheet1添加第一行的头部标题
//设置字体的样式
IFont font1 = book.CreateFont();
font1.FontName = "宋体";//字体
font1.FontHeightInPoints = 15;//字号
font1.Color = HSSFColor.BLUE.index;//颜色
font1.Boldweight = 400;//粗体
font1.IsItalic = false;//斜体
//设置字体的样式
IFont font2 = book.CreateFont();
font2.FontName = "宋体";//字体
font2.FontHeightInPoints = 10;//字号
font2.Color = HSSFColor.BLACK.index;//颜色
font2.Boldweight = 400;//粗体
font2.IsItalic = false;//斜体
//新增样式
ICellStyle styleMain = book.CreateCellStyle();
styleMain.Alignment =HorizontalAlignment.CENTER;//居中
styleMain.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.YELLOW.index; //设置背景颜色
styleMain.FillPattern = FillPatternType.SOLID_FOREGROUND;
styleMain.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.YELLOW.index; //设置背景颜色
styleMain.SetFont(font1);
//新增样式
ICellStyle styleCenter = book.CreateCellStyle();
//居中
styleCenter.Alignment = HorizontalAlignment.CENTER;
//新增样式
ICellStyle styleTitle = book.CreateCellStyle();
//居中
styleTitle.Alignment = HorizontalAlignment.CENTER;
//设置背景颜色
styleTitle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LIME.index;
styleTitle.FillPattern = FillPatternType.SOLID_FOREGROUND;
styleTitle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.LIME.index;
styleTitle.SetFont(font2);
//sheet1的内容
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(1);
int i = 0;
foreach (DataColumn column in dt.Columns)
{
if (column.ColumnName.ToUpper() != "DR")
{
string columnName = column.ColumnName.ToUpper(); //获取名称
int NameLength = columnName.Length; //获取字符串的长度
string TwoCharacter = columnName.Substring(0, 2).ToUpper(); //截取字符前两位
int AfterIndex = QConvert.ToInt32(NameLength) - QConvert.ToInt32(2); //计算后两位的开始索引NameLength
string AfterTwoCharacter = columnName.Substring(AfterIndex, 2).ToUpper(); //截取后两位的字符
string CharacterChar = TwoCharacter + AfterTwoCharacter; //拼接成一个字符串
//过滤特殊字段
if (CharacterChar!="INID" && (columnName!= "MODIFYUSER" && columnName != "RATIFYUSER" && columnName != "DOBILLUSER"))
{
var pName = typSe.GetProperty(column.ColumnName);
//获取到表的列名(两种方法)
//(第一种方法)
var DisplayName = pName.GetCustomAttribute<DisplayAttribute>().Name;
//(第二种方法)
//var DisplayName = pName.CustomAttributes.ToList()[0].NamedArguments[0].TypedValue.Value.ToString();
row1.CreateCell(i).SetCellValue(DisplayName); //设置单元格的内容
row1.Cells[i].CellStyle = styleTitle; //设置单元格的背景颜色
sheet1.SetColumnWidth(i, 30 * 200); //设置单元格的宽度
i++;
}
}
}
sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, i-1)); //合并单元格
row0.CreateCell(0).SetCellValue("文件名称"); //设置标题
row0.Cells[0].CellStyle = styleMain; //设置首行的第一个单元格进行文字居中
int j = 1;
foreach (DataRow dr in dt.Rows)
{
int Index = 0;
IRow rowtemp = sheet1.CreateRow(j + 1);//创建新行
i = 0;
for (; i < dt.Columns.Count; i++)
{
string CellName = dt.Columns[i].ToString().ToUpper(); //获取到列值
int CellNameLength = CellName.Length; //获取字符串的长度
string CellTwoCharacter = CellName.Substring(0, 2).ToUpper(); //截取字符前两位
int AfterIndex = QConvert.ToInt32(CellNameLength) - QConvert.ToInt32(2); //计算后两位的开始索引NameLength
string CellAfterTwoCharacter = CellName.Substring(AfterIndex, 2).ToUpper(); //截取后两位的字符
string CharacterChar = CellTwoCharacter + CellAfterTwoCharacter; //拼接成一个字符串
if (CharacterChar!="INID"&& (CellName != "MODIFYUSER" && CellName != "RATIFYUSER" && CellName != "DOBILLUSER" && CellName != "DR"))
{
rowtemp.CreateCell(Index).SetCellValue(dr[i].ToString()); //填充数据
rowtemp.Cells[Index].CellStyle = styleCenter; //设置单元格的内容进行居中
Index = Index+1;
}
}
j++;
}
if (type == 0)
{
MemoryStream ms1 = new MemoryStream();
book.Write(ms1);
ms1.Seek(0, SeekOrigin.Begin);
ms = ms1;
}
else
{
FileStream fs = new FileStream(path, FileMode.Create);
book.Write(fs);
fs.Close();
ms = null;
}
return new ReturnMsg() { code = 0, msg = "" };
}
catch (Exception ex)
{
ms = null;
return new ReturnMsg() { code = 1, msg = "导出异常:" + ex.Message };
}
}
}
}
创建一个信息提示的类
使用系统;
使用System.Collections.Generic;
使用System.Linq;
使用System.Text;
使用System.Threading.Tasks;
namespace Model.Export
{
public class ReturnMsg
{
public int code {get; 组; } // 0/1
public string msg {get; 组; } //信息
}
}