问题
最近在做一个招聘系统, 里面一个导出excel的功能, 要求导出简历,第一个Sheet页是列表页,其他Sheet页面是简历详情,列表页的第一列是一个超链接, 可以跳到简历的详情页。如下图:
在这里我主要讲HSSFHyperlink
函数,HSSFHyperlink
函数的用法如下:
HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Document);
link.Address = "#" + sheet页的名字 + "!A1";//设置超链接点击跳转的地址
代码:
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Demo
{
class Program
{
static void Main(string[] args)
{
//建立空白工作簿
HSSFWorkbook book = new HSSFWorkbook();
//在工作簿中:建立空白工作表
ISheet sheet = book.CreateSheet("简历信息列表");
#region Excel样式
IFont font = book.CreateFont();
font.Boldweight = short.MaxValue;
font.FontHeightInPoints = 11;
font.FontName = "宋体";
IFont font2 = book.CreateFont();
font2.FontName = "宋体";
IFont fontLink = book.CreateFont();
fontLink.Color = HSSFColor.Blue.Index;
fontLink.FontName = "宋体";
//设置链接的样式:水平垂直对齐居中
ICellStyle cellLinkStyle = book.CreateCellStyle();
cellLinkStyle.Alignment = HorizontalAlignment.Center;
cellLinkStyle.VerticalAlignment = VerticalAlignment.Center;
//cellLinkStyle.BorderBottom = CellBorderType.THIN;
//cellLinkStyle.BorderLeft = CellBorderType.THIN;
//cellLinkStyle.BorderRight = CellBorderType.THIN;
//cellLinkStyle.BorderTop = CellBorderType.THIN;
cellLinkStyle.BottomBorderColor = HSSFColor.Black.Index;
cellLinkStyle.LeftBorderColor = HSSFColor.Black.Index;
cellLinkStyle.RightBorderColor = HSSFColor.Black.Index;
cellLinkStyle.TopBorderColor = HSSFColor.Black.Index;
cellLinkStyle.SetFont(fontLink);
//设置表头的样式:水平垂直对齐居中,加粗
ICellStyle titleCellStyle = book.CreateCellStyle();
titleCellStyle.Alignment = HorizontalAlignment.Center;
titleCellStyle.VerticalAlignment = VerticalAlignment.Center;
titleCellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index; //图案颜色
//titleCellStyle.FillPattern = FillPatternType.SPARSE_DOTS; //图案样式
titleCellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index; //背景颜色
#endregion
//创建表头
IRow row = sheet.CreateRow(0);
row.HeightInPoints = 25;
row.CreateCell(0).SetCellValue("查看简历");
row.CreateCell(1).SetCellValue("应聘职位");
row.CreateCell(2).SetCellValue("状态");
row.CreateCell(3).SetCellValue("姓名");
row.CreateCell(4).SetCellValue("年龄");
row.CreateCell(5).SetCellValue("邮箱");
row.CreateCell(6).SetCellValue("地址");
//获取测试数据
DataTable dt = new Program().GetData();
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
row.HeightInPoints = 18; //设置字体大小
HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Document);
link.Address = "#" + dt.Rows[i]["name"].ToString() + (i + 1) + "!A1";//设置超链接点击跳转的地址
row1.CreateCell(0).Hyperlink = link;
row1.GetCell(0).SetCellValue("查看简历详细");
row1.CreateCell(1).SetCellValue(dt.Rows[i]["postion"].ToString());
row1.CreateCell(2).SetCellValue(dt.Rows[i]["state"].ToString());
row1.CreateCell(3).SetCellValue(dt.Rows[i]["name"].ToString());
row1.CreateCell(4).SetCellValue(dt.Rows[i]["age"].ToString());
row1.CreateCell(5).SetCellValue(dt.Rows[i]["email"].ToString());
row1.CreateCell(6).SetCellValue(dt.Rows[i]["address"].ToString());
//设置超链接的颜色
row1.GetCell(0).CellStyle = cellLinkStyle;
new Program().CreateDetailPage(book, dt.Rows[i], Convert.ToInt32(i) + 1);
}
//导出
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
FileStream file = new FileStream("Demo.xls", FileMode.OpenOrCreate);
book.Write(file);
file.Flush();
file.Close();
book = null;
}
public void CreateDetailPage(HSSFWorkbook book, DataRow row, int number)
{
ISheet sheet = book.CreateSheet(row["name"].ToString() + number);
IRow row1 = sheet.CreateRow(0);
row1.CreateCell(0).SetCellValue("简历详细信息");
}
/// <summary>
/// 取数据, 方便测试, 数据写死
/// </summary>
/// <returns></returns>
public DataTable GetData()
{
DataTable dt = new DataTable();
//创建列(第一个参数是列名称,第二个参数是列的类型)
dt.Columns.Add("postion", Type.GetType("System.String"));//职位
dt.Columns.Add("state", Type.GetType("System.String")); //状态
dt.Columns.Add("name", Type.GetType("System.String")); //姓名
dt.Columns.Add("age", Type.GetType("System.String")); //年龄
dt.Columns.Add("email", Type.GetType("System.String")); //邮箱
dt.Columns.Add("address", Type.GetType("System.String")); //地址
//填充数据
//创建一行数据
for (int i = 0; i < 5; i++)
{
DataRow dr = dt.NewRow();
dr["postion"] = "店铺兼职员工(全国范围)";
dr["state"] = "二面";
dr["name"] = "张三" + i;
dr["age"] = 22 + i;
dr["email"] = "123456@qq.com";
dr["address"] = "上海市 普陀区 111号";
//数据填充到表格
dt.Rows.Add(dr);
}
return dt;
}
}
}