asp.net mvc导入导出excel 使用Aspose.Cells好用到停不下来

1、创建asp.net mvc 项目

2、选择引用，右键选择管理NuGet程序包

选择管理NuGet程序包

3、输入Aspose.Cells搜索，选择红框的这个添加

选择Aspose.Cells

4、添加引用 using Aspose.Cells; 和using System.Data;

5、 导入excel到数据库

数据库对应的字段：

前端页面

view的页面

Improve_ExcelController 控制器

6、将数据显示和筛选出来

这里用到了PageList 分页，步骤是在引用右键，选择NuGet ，搜索PagedList.Mvc，然后引入using PagedList;

控制器 DataRefshController

//条件筛选查询显示

这里用到了将datatble 转换为list

view 前端页面

查询之后，单击导出excel

//导出到excel

这里创建了一个AsposeCellsHelper类

导入比使用office 的插件简单易懂，只需要设置数据库对应的表和连接数据库即可，

导出的时候也很简单，封装了一个类

public class AsposeCellsHelper

{

public Workbook workBook;

public Worksheet worksheet;

Style style;

/// <summary>

/// 构造函数，不设置工作表名称

/// </summary>

public AsposeCellsHelper()

{

workBook = new Workbook();

worksheet = workBook.Worksheets[0];

}

/// <summary>

/// 构造函数，设置工作表名称

/// </summary>

public AsposeCellsHelper(string name)

{

workBook = new Workbook();

if (string.IsNullOrEmpty(name))

{

name = DateTime.Now.ToString("yyyyMMddHHmmssffff");

}

if (workBook.Worksheets.Count > 0)

{

worksheet = workBook.Worksheets[0];

worksheet.Name = name;

}

else

{

worksheet = workBook.Worksheets.Add(name);

}

}

/// <summary>

/// 创建工作表，并将对象切换为当前创建的工作表

/// </summary>

/// <param name="name"></param>

public void CreateSheet(string name)

{

if (string.IsNullOrEmpty(name))

{

name = DateTime.Now.ToString("yyyyMMddHHmmssffff");

}

worksheet = workBook.Worksheets.Add(name);

}

//设置单元格样式

public void SetCellStyle(int fontSize, bool IsBold)

{

if (style == null)

{

style = workBook.DefaultStyle;

}

//style.BackgroundColor = backgroundColor;

style.Font.Name = "宋体";//文字字体

style.Font.Size = fontSize;//文字大小

//前景色

style.ForegroundColor = Color.White;

style.Font.IsBold = IsBold;//粗体

style.HorizontalAlignment = TextAlignmentType.Center;//文字居中

//线条样式

style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;

//线条颜色

style.Borders[BorderType.TopBorder].Color = Color.Black;

style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;

style.Borders[BorderType.BottomBorder].Color = Color.Black;

style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;

style.Borders[BorderType.LeftBorder].Color = Color.Black;

style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;

style.Borders[BorderType.RightBorder].Color = Color.Black;

}

//设置值

public void SetValue(int x, int y, string value, int rowHeight = 20)

{

//设置值

worksheet.Cells[x, y].PutValue(value, false);

//设置样式

worksheet.Cells[x, y].SetStyle(style);

//设置行高

worksheet.Cells.SetRowHeight(x, rowHeight);

}

/// <summary>

/// 填充数据到excel，数据填充可自行添加或继承后扩展方法

/// </summary>

/// <param name="dt"></param>

public void SetDataByTable(DataTable dt, string title)

{

DataColumnCollection columns = dt.Columns;

int addIndex = 0;

//表格标题

if (!string.IsNullOrEmpty(title))

{

addIndex = 1;

SetCellStyle(16, true);

worksheet.Cells.Merge(0, 0, 1, columns.Count);

SetValue(0, 0, title, 30);

}

//表头

if (columns.Count > 0)

{

int columnIndex = 0;

foreach (DataColumn dc in columns)

{

SetCellStyle(14, true);

//将表头是表字段更改成我们想要的汉字

switch (dc.ColumnName)

{

case "ids":

dc.ColumnName = "ID";

break;

case "users":

dc.ColumnName = "用户";

break;

case "area":

dc.ColumnName = "区域";

break;

case "school":

dc.ColumnName = "学校";

break;

case "classes":

dc.ColumnName = "班级";

break;

case "name":

dc.ColumnName = "姓名";

break;

case "phone":

dc.ColumnName = "电话";

break;

case "integration":

dc.ColumnName = "积分";

break;

case "states":

dc.ColumnName = "状态";

break;

case "createDate":

dc.ColumnName = "创建时间";

break;

case "refreshDate":

dc.ColumnName = "更新时间";

break;

}

SetValue(0 + addIndex, columnIndex, dc.ColumnName, 25);

columnIndex += 1;

}

}

//数据

if (dt.Rows.Count > 0)

{

int rowIndex = 1 + addIndex;

foreach (DataRow dr in dt.Rows)

{

for (int i = 0; i < columns.Count; i++)

{

SetCellStyle(12, false);

SetValue(rowIndex, i, dr[i].ToString());

}

rowIndex += 1;

}

}

worksheet.AutoFitColumns();

}

/// <summary>

/// 导出excel

/// </summary>

/// <param name="name"></param>

public void ExportExcel(string name)

{

try

{

HttpResponse Response = System.Web.HttpContext.Current.Response;

if (Response != null)

{

if (string.IsNullOrEmpty(name))

{

name = "学员信息"+DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx";

}

// Save in Xlsx format

workBook.Save(Response, name, Aspose.Cells.ContentDisposition.Attachment, new OoxmlSaveOptions());

Response.End();

}

}

catch (Exception ex)

{

throw ex;

}

finally

{

//可以不写dispose

worksheet.Dispose();

workBook.Dispose();

}

}

}

ok！需要全部代码的，需要源码的找我哦！

谁用谁知道，比office的插件好用多了，记得收藏和点赞哦！