邮件报表推送的个人建议方案（.net）
司寇阳曦
报表邮件推送方案：
1.Sqlserver报表=》直接将数据库的数据进行整理后进行发出，样式较为固定且高亮显示重要数据时，相对比较麻烦。优点是显示图表相对比较方便；
2.C#直接编写后台代码=》从数据库获取数据后，利用stringbuilder将html代码拼接起来，可自定义显示样式。但显示统计图时，只能迂回方案来实现。
最近在搞项目的时候，发现采用下述邮件推送的方式，可以既能生成邮件图表数据，又能生成Excel文件，通过保存每日推送的Excel文件，最终来实现既可以看到实时数据，又能看到历史Excel图表。当然我们也可以采用单独的网页报表来实现历史查询，这里只是给大家一个建议方案！
方案简述：
1.利用NPOI或Spire.xls生成Excel文档；
2.利用Spire.xls将Excel转换到Html文档；(Spire.xls开源版只能转换三页）
3.利用PreMailer.Net将Html文档转到为内联样式，否则Outlook无法正常显示；
通过上述方案，我们就可以先生成Excel，将其保存在文件夹里面，供未来历史查询；
然后利用Excel，生成Html文档并转换到内联样式，这样子邮件可以直接显示对应样式；
具体过程
DateTime attend_date = DateTime.Now.Date.AddDays(-1);
var report_list = wh_dal.GetDailyReport(attend_date);
MemoryStream ms = new MemoryStream();
// 生成日报Excel
WHExcelHelper excel_helper = new WHExcelHelper();
excel_helper.ExportDailyExcel(ms, report_list, attend_date);
ms.Seek(0, SeekOrigin.Begin);
string daily_relative_path = AppSettings.MTDailyExcelFile + "/" + attend_date.ToString("yyyy-MM");
string daily_path = AppSettings.DeployDir + "/" + daily_relative_path;
if (!Directory.Exists(daily_path))
{
Directory.CreateDirectory(daily_path);
}
string file_xls = "工时日报表（" + attend_date.ToString("MM-dd") + "）.xls";
string file_html = "工时日报表（" + attend_date.ToString("MM-dd") + "）.html";
string daily_xls_path = daily_path + "/" + file_xls;
string daily_html_path = daily_path + "/" + file_html;
if (!File.Exists(daily_xls_path))
{
File.Delete(daily_xls_path);
}
// 当前地址
var current_url = AppSettings.MT_BWUrl + daily_relative_path + "/" + file_xls;
// 历史地址
var history_url = AppSettings.MT_BWUrl + AppSettings.MTDailyExcelFile;
// 生成日报邮件
string html_info = GetEmailHtmlInfo(ms, daily_xls_path, daily_html_path);
StringBuilder strHtml = new StringBuilder();
strHtml.Append("<br />");
strHtml.Append("<p style=\"margin - left: 65px\">");
strHtml.Append("<span style=\"font - family: 宋体, Arial, Helvetica, sans - serif; font - size: 18px; color: #000000; font-weight: bold;\">" +"当前文件：<a href=\"" + current_url + "\">" + current_url + "</a></span>");
strHtml.Append("<br />");
strHtml.Append("<span style=\"font - family: 宋体, Arial, Helvetica, sans - serif; font - size: 18px; color: #000000; font-weight: bold;\">" +"历史查看：<a href=\""+ history_url + "\">"+ history_url + "</a></span>");
strHtml.Append("</p>");
html_info += strHtml;excel_helper.ExportDailyExcel(ms, report_list, attend_date); 这里面我是采用NPOI生成具体内容
public class WHExcelHelper
{
//创建一个Excel表
HSSFWorkbook workbook = new HSSFWorkbook();
// 样式表
private ICellStyle td_green = null;
private ICellStyle td_blue = null;
private ICellStyle td_brown = null;
private ICellStyle td_grey = null;
private ICellStyle td_normal = null;
private ICellStyle td_decimal = null;
private ICellStyle th_lime_title = null;
private ICellStyle th_grey25_title = null;
private ICellStyle th_grey40_title = null;
private ICellStyle th_tan_title = null;
private ICellStyle th_paleblue_title = null;
private ICellStyle th_skyblue_title = null;
private ICellStyle th_normal_title = null;
private ICellStyle th_head_title = null;
private ICellStyle td_percent = null;
/// <summary>
/// 构造函数
/// </summary>
public WHExcelHelper()
{
// 设置字体
IFont font_white = workbook.CreateFont();
font_white.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
IFont font_black = workbook.CreateFont();
font_black.IsBold = true;
font_black.Color = NPOI.HSSF.Util.HSSFColor.Black.Index;
IFont font_head = workbook.CreateFont();
font_head.IsBold = true;
font_head.Color = NPOI.HSSF.Util.HSSFColor.Black.Index;
font_head.FontHeightInPoints = 28;
// 初始化样式
td_green = workbook.CreateCellStyle() as HSSFCellStyle;
// 设置字体颜色
td_green.SetFont(font_white);
//设置垂直居中
td_green.VerticalAlignment = VerticalAlignment.Center;
//设置水平居中
td_green.Alignment = HorizontalAlignment.Center;
// 设置背景为绿色
td_green.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Green.Index;
// 背景模式
td_green.FillPattern = FillPattern.SolidForeground;
// 可回车
td_green.WrapText = true;
// 边框设置
td_green.BorderBottom = BorderStyle.Thin;
td_green.BorderLeft = BorderStyle.Thin;
td_green.BorderRight = BorderStyle.Thin;
td_green.BorderTop = BorderStyle.Thin;
td_blue = workbook.CreateCellStyle() as HSSFCellStyle;
//设置垂直居中
td_blue.VerticalAlignment = VerticalAlignment.Center;
//设置水平居中
td_blue.Alignment = HorizontalAlignment.Center;
// 设置背景为黄色
td_blue.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Blue.Index;
// 背景模式
td_blue.FillPattern = FillPattern.SolidForeground;
// 可回车
td_blue.WrapText = true;
// 边框设置
td_blue.BorderBottom = BorderStyle.Thin;
td_blue.BorderLeft = BorderStyle.Thin;
td_blue.BorderRight = BorderStyle.Thin;
td_blue.BorderTop = BorderStyle.Thin;
td_brown = workbook.CreateCellStyle() as HSSFCellStyle;
// 设置字体颜色
td_brown.SetFont(font_white);
//设置垂直居中
td_brown.VerticalAlignment = VerticalAlignment.Center;
//设置水平居中
td_brown.Alignment = HorizontalAlignment.Center;
// 设置背景为黄色
td_brown.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Brown.Index;
// 背景模式
td_brown.FillPattern = FillPattern.SolidForeground;
// 可回车
td_brown.WrapText = true;
// 边框设置
td_brown.BorderBottom = BorderStyle.Thin;
td_brown.BorderLeft = BorderStyle.Thin;
td_brown.BorderRight = BorderStyle.Thin;
td_brown.BorderTop = BorderStyle.Thin;
td_grey = workbook.CreateCellStyle() as HSSFCellStyle;
//设置垂直居中
td_grey.VerticalAlignment = VerticalAlignment.Center;
//设置水平居中
td_grey.Alignment = HorizontalAlignment.Center;
// 设置背景为黄色
td_grey.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey50Percent.Index;
// 背景模式
td_grey.FillPattern = FillPattern.SolidForeground;
// 可回车
td_grey.WrapText = true;
// 边框设置
td_grey.BorderBottom = BorderStyle.Thin;
td_grey.BorderLeft = BorderStyle.Thin;
td_grey.BorderRight = BorderStyle.Thin;
td_grey.BorderTop = BorderStyle.Thin;
td_normal = workbook.CreateCellStyle() as HSSFCellStyle;
//设置垂直居中
td_normal.VerticalAlignment = VerticalAlignment.Center;
//设置水平居中
td_normal.Alignment = HorizontalAlignment.Center;
// 可回车
td_normal.WrapText = true;
// 边框设置
td_normal.BorderBottom = BorderStyle.Thin;
td_normal.BorderLeft = BorderStyle.Thin;
td_normal.BorderRight = BorderStyle.Thin;
td_normal.BorderTop = BorderStyle.Thin;
td_decimal = workbook.CreateCellStyle() as HSSFCellStyle;
//设置垂直居中
td_decimal.VerticalAlignment = VerticalAlignment.Center;
//设置水平居中
td_decimal.Alignment = HorizontalAlignment.Center;
// 可回车
td_decimal.WrapText = true;
// 边框设置
td_decimal.BorderBottom = BorderStyle.Thin;
td_decimal.BorderLeft = BorderStyle.Thin;
td_decimal.BorderRight = BorderStyle.Thin;
td_decimal.BorderTop = BorderStyle.Thin;
td_decimal.DataFormat = workbook.CreateDataFormat().GetFormat("0.0");
th_lime_title = workbook.CreateCellStyle() as HSSFCellStyle;
// 设置加粗字体
th_lime_title.SetFont(font_black);
//设置垂直居中
th_lime_title.VerticalAlignment = VerticalAlignment.Center;
//设置水平居中
th_lime_title.Alignment = HorizontalAlignment.Center;
// 设置背景为天蓝色
th_lime_title.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Lime.Index;
th_lime_title.FillPattern = FillPattern.SolidForeground;
// 可回车
th_lime_title.WrapText = true;
// 边框设置
th_lime_title.BorderBottom = BorderStyle.Thin;
th_lime_title.BorderLeft = BorderStyle.Thin;
th_lime_title.BorderRight = BorderStyle.Thin;
th_lime_title.BorderTop = BorderStyle.Thin;
th_grey25_title = workbook.CreateCellStyle() as HSSFCellStyle;
// 设置加粗字体
th_grey25_title.SetFont(font_black);
//设置垂直居中
th_grey25_title.VerticalAlignment = VerticalAlignment.Center;
//设置水平居中
th_grey25_title.Alignment = HorizontalAlignment.Center;
// 设置背景为天蓝色
th_grey25_title.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
th_grey25_title.FillPattern = FillPattern.SolidForeground;
// 可回车
th_grey25_title.WrapText = true;
// 边框设置
th_grey25_title.BorderBottom = BorderStyle.Thin;
th_grey25_title.BorderLeft = BorderStyle.Thin;
th_grey25_title.BorderRight = BorderStyle.Thin;
th_grey25_title.BorderTop = BorderStyle.Thin;
th_grey40_title = workbook.CreateCellStyle() as HSSFCellStyle;
// 设置加粗字体
th_grey40_title.SetFont(font_black);
//设置垂直居中
th_grey40_title.VerticalAlignment = VerticalAlignment.Center;
//设置水平居中
th_grey40_title.Alignment = HorizontalAlignment.Center;
// 设置背景为天蓝色
th_grey40_title.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey40Percent.Index;
th_grey40_title.FillPattern = FillPattern.SolidForeground;
// 可回车
th_grey40_title.WrapText = true;
// 边框设置
th_grey40_title.BorderBottom = BorderStyle.Thin;
th_grey40_title.BorderLeft = BorderStyle.Thin;
th_grey40_title.BorderRight = BorderStyle.Thin;
th_grey40_title.BorderTop = BorderStyle.Thin;
th_tan_title = workbook.CreateCellStyle() as HSSFCellStyle;
// 设置加粗字体
th_tan_title.SetFont(font_black);
//设置垂直居中
th_tan_title.VerticalAlignment = VerticalAlignment.Center;
//设置水平居中
th_tan_title.Alignment = HorizontalAlignment.Center;
// 设置背景为天蓝色
th_tan_title.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Tan.Index;
th_tan_title.FillPattern = FillPattern.SolidForeground;
// 可回车
th_tan_title.WrapText = true;
// 边框设置
th_tan_title.BorderBottom = BorderStyle.Thin;
th_tan_title.BorderLeft = BorderStyle.Thin;
th_tan_title.BorderRight = BorderStyle.Thin;
th_tan_title.BorderTop = BorderStyle.Thin;
th_paleblue_title = workbook.CreateCellStyle() as HSSFCellStyle;
// 设置加粗字体
th_paleblue_title.SetFont(font_black);
//设置垂直居中
th_paleblue_title.VerticalAlignment = VerticalAlignment.Center;
//设置水平居中
th_paleblue_title.Alignment = HorizontalAlignment.Center;
// 设置背景为天蓝色
th_paleblue_title.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.PaleBlue.Index;
th_paleblue_title.FillPattern = FillPattern.SolidForeground;
// 可回车
th_paleblue_title.WrapText = true;
// 边框设置
th_paleblue_title.BorderBottom = BorderStyle.Thin;
th_paleblue_title.BorderLeft = BorderStyle.Thin;
th_paleblue_title.BorderRight = BorderStyle.Thin;
th_paleblue_title.BorderTop = BorderStyle.Thin;
th_skyblue_title = workbook.CreateCellStyle() as HSSFCellStyle;
// 设置加粗字体
th_skyblue_title.SetFont(font_black);
//设置垂直居中
th_skyblue_title.VerticalAlignment = VerticalAlignment.Center;
//设置水平居中
th_skyblue_title.Alignment = HorizontalAlignment.Center;
// 设置背景为天蓝色
th_skyblue_title.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightCornflowerBlue.Index;
th_skyblue_title.FillPattern = FillPattern.SolidForeground;
// 可回车
th_skyblue_title.WrapText = true;
// 边框设置
th_skyblue_title.BorderBottom = BorderStyle.Thin;
th_skyblue_title.BorderLeft = BorderStyle.Thin;
th_skyblue_title.BorderRight = BorderStyle.Thin;
th_skyblue_title.BorderTop = BorderStyle.Thin;
th_normal_title = workbook.CreateCellStyle() as HSSFCellStyle;
// 设置加粗字体
th_normal_title.SetFont(font_black);
//设置垂直居中
th_normal_title.VerticalAlignment = VerticalAlignment.Center;
//设置水平居中
th_normal_title.Alignment = HorizontalAlignment.Center;
// 设置背景为天蓝色
th_normal_title.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.White.Index;
th_normal_title.FillPattern = FillPattern.SolidForeground;
// 可回车
th_normal_title.WrapText = true;
// 边框设置
th_normal_title.BorderBottom = BorderStyle.Thin;
th_normal_title.BorderLeft = BorderStyle.Thin;
th_normal_title.BorderRight = BorderStyle.Thin;
th_normal_title.BorderTop = BorderStyle.Thin;
th_head_title = workbook.CreateCellStyle() as HSSFCellStyle;
// 设置加粗字体
th_head_title.SetFont(font_head);
//设置垂直居中
th_head_title.VerticalAlignment = VerticalAlignment.Center;
//设置水平居中
th_head_title.Alignment = HorizontalAlignment.Center;
// 边框设置
th_head_title.BorderBottom = BorderStyle.Thin;
th_head_title.BorderLeft = BorderStyle.Thin;
th_head_title.BorderRight = BorderStyle.Thin;
th_head_title.BorderTop = BorderStyle.Thin;
td_percent = workbook.CreateCellStyle() as HSSFCellStyle;
//设置垂直居中
td_percent.VerticalAlignment = VerticalAlignment.Center;
//设置水平居中
td_percent.Alignment = HorizontalAlignment.Center;
// 可回车
td_percent.WrapText = true;
// 边框设置
td_percent.BorderBottom = BorderStyle.Thin;
td_percent.BorderLeft = BorderStyle.Thin;
td_percent.BorderRight = BorderStyle.Thin;
td_percent.BorderTop = BorderStyle.Thin;
td_percent.DataFormat = workbook.CreateDataFormat().GetFormat("0.0%");
}
/// <summary>
/// 导出日报表
/// </summary>
/// <param name="ms"></param>
/// <param name="report_list"></param>
/// <param name="dt_attend"></param>
public void ExportDailyExcel(MemoryStream ms, List<MT_WH_DailyReport> report_list, DateTime dt_attend)
{
// 生成全部信息的表头
HSSFSheet sheet = workbook.CreateSheet("日报表") as HSSFSheet;
string title = "金条BU【蓝领工时利用率】【" + dt_attend.ToString("yyyy-MM-dd") + "】";
CreateHead(workbook, sheet, title);
int row_index = 3;
for (int i = 0; i < report_list.Count; i++)
{
IRow row = sheet.CreateRow(row_index) as HSSFRow;
row.HeightInPoints = 30;
var current = report_list[i];
int NO = i + 1;
// NO
row.CreateCell(0).SetCellValue(NO);
row.GetCell(0).CellStyle = td_normal;
// 工厂代码
row.CreateCell(1).SetCellValue(current.BUNO);
row.GetCell(1).CellStyle = td_normal;
// 工厂名称
row.CreateCell(2).SetCellValue(current.Factory);
row.GetCell(2).CellStyle = td_normal;
// 目标
row.CreateCell(3).SetCellValue(current.TargetRate.HasValue ? (double)current.TargetRate.Value : 0);
row.GetCell(3).CellStyle = td_percent;
// 实绩
row.CreateCell(4).SetCellValue(current.RealRate.HasValue ? (double)current.RealRate.Value : 0);
row.GetCell(4).CellStyle = td_percent;
// 达成率
row.CreateCell(5).SetCellValue(current.ArrivalRate.HasValue ? (double)current.ArrivalRate.Value : 0);
row.GetCell(5).CellStyle = td_percent;
// 蓝领出勤工时
row.CreateCell(6).SetCellValue(current.BlueHourTotal.HasValue ? (double)current.BlueHourTotal.Value : 0);
row.GetCell(6).CellStyle = td_normal;
// 生产有效工时
row.CreateCell(7).SetCellValue(current.ValidHourTotal.HasValue ? (double)current.ValidHourTotal.Value : 0);
row.GetCell(7).CellStyle = td_decimal;
// 蓝领人数
double total = (current.DBlueCount.HasValue ? current.DBlueCount.Value : 0) + (current.IBlueCount.HasValue ? current.IBlueCount.Value : 0);
row.CreateCell(8).SetCellValue(total);
row.GetCell(8).CellStyle = td_normal;
// 直接蓝领人数
row.CreateCell(9).SetCellValue(current.DBlueCount??0);
row.GetCell(9).CellStyle = td_normal;
// 间接蓝领人数
row.CreateCell(10).SetCellValue(current.IBlueCount ?? 0);
row.GetCell(10).CellStyle = td_normal;
// 间接蓝领人数占比
row.CreateCell(11).SetCellValue(current.BlueRate.HasValue ? (double)current.BlueRate.Value : 0);
row.GetCell(11).CellStyle = td_percent;
// 直接蓝领出勤工时
row.CreateCell(12).SetCellValue(current.DBlueHourTotal.HasValue ? (double)current.DBlueHourTotal.Value : 0);
row.GetCell(12).CellStyle = td_normal;
// 间接蓝领出勤工时
row.CreateCell(13).SetCellValue(current.IBlueHourTotal.HasValue ? (double)current.IBlueHourTotal.Value : 0);
row.GetCell(13).CellStyle = td_normal;
// 间接蓝领出勤占比
row.CreateCell(14).SetCellValue(current.BlueHourRate.HasValue ? (double)current.BlueHourRate.Value : 0);
row.GetCell(14).CellStyle = td_percent;
// 异常停机工时
row.CreateCell(15).SetCellValue(current.FaultHour.HasValue ? (double)current.FaultHour.Value : 0);
row.GetCell(15).CellStyle = td_decimal;
// 占比
row.CreateCell(16).SetCellValue(current.FaultHourRate.HasValue ? (double)current.FaultHourRate.Value : 0);
row.GetCell(16).CellStyle = td_percent;
// 换模准备工时
row.CreateCell(17).SetCellValue(current.MouldReadyHour.HasValue ? (double)current.MouldReadyHour.Value : 0);
row.GetCell(17).CellStyle = td_decimal;
// 占比
row.CreateCell(18).SetCellValue(current.MouldReadyHourRate.HasValue ? (double)current.MouldReadyHourRate.Value : 0);
row.GetCell(18).CellStyle = td_percent;
// 废品工时
row.CreateCell(19).SetCellValue(current.ScrapHour.HasValue ? (double)current.ScrapHour.Value : 0);
row.GetCell(19).CellStyle = td_decimal;
// 占比
row.CreateCell(20).SetCellValue(current.ScrapHourRate.HasValue ? (double)current.ScrapHourRate.Value : 0);
row.GetCell(20).CellStyle = td_percent;
// 其他无效工时
row.CreateCell(21).SetCellValue(current.OtherHour.HasValue ? (double)current.OtherHour.Value : 0);
row.GetCell(21).CellStyle = td_decimal;
// 占比
row.CreateCell(22).SetCellValue(current.OtherHourRate.HasValue ? (double)current.OtherHourRate.Value : 0);
row.GetCell(22).CellStyle = td_percent;
// 直接蓝领工时利用率
row.CreateCell(23).SetCellValue(current.BlueUseRate.HasValue ? (double)current.BlueUseRate.Value : 0);
row.GetCell(23).CellStyle = td_percent;
row_index++;
}
// 设置第三列宽度
sheet.SetColumnWidth(2, 20 * 256);
// 冻结前三行与前三列
sheet.CreateFreezePane(3, 3, 3, 3);
workbook.Write(ms);
}
/// <summary>
/// 导出周报表
/// </summary>
/// <param name="ms"></param>
/// <param name="report_list"></param>
/// <param name="dt_start"></param>
/// <param name="dt_end"></param>
/// <param name="week_index"></param>
public void ExportWeeklyExcel(MemoryStream ms, List<MT_WH_WeeklyReport> report_list, DateTime dt_start, DateTime dt_end, int week_index)
{
// 生成全部信息的表头
HSSFSheet sheet = workbook.CreateSheet("周报表") as HSSFSheet;
string title = "金条BU【蓝领工时利用率第" + week_index + "周】【" + dt_start.ToString("MM-dd") + "~" + dt_end.ToString("MM-dd") + "】";
CreateHead(workbook, sheet, title);
int row_index = 3;
for (int i = 0; i < report_list.Count; i++)
{
IRow row = sheet.CreateRow(row_index) as HSSFRow;
row.HeightInPoints = 30;
var current = report_list[i];
int NO = i + 1;
// NO
row.CreateCell(0).SetCellValue(NO);
row.GetCell(0).CellStyle = td_normal;
// 工厂代码
row.CreateCell(1).SetCellValue(current.BUNO);
row.GetCell(1).CellStyle = td_normal;
// 工厂名称
row.CreateCell(2).SetCellValue(current.Factory);
row.GetCell(2).CellStyle = td_normal;
// 目标
row.CreateCell(3).SetCellValue(current.TargetRate.HasValue ? (double)current.TargetRate.Value : 0);
row.GetCell(3).CellStyle = td_percent;
// 实绩
row.CreateCell(4).SetCellValue(current.RealRate.HasValue ? (double)current.RealRate.Value : 0);
row.GetCell(4).CellStyle = td_percent;
// 达成率
row.CreateCell(5).SetCellValue(current.ArrivalRate.HasValue ? (double)current.ArrivalRate.Value : 0);
row.GetCell(5).CellStyle = td_percent;
// 蓝领出勤工时
row.CreateCell(6).SetCellValue(current.BlueHourTotal.HasValue ? (double)current.BlueHourTotal.Value : 0);
row.GetCell(6).CellStyle = td_normal;
// 生产有效工时
row.CreateCell(7).SetCellValue(current.ValidHourTotal.HasValue ? (double)current.ValidHourTotal.Value : 0);
row.GetCell(7).CellStyle = td_decimal;
// 蓝领人数
double total = (current.DBlueCount.HasValue ? current.DBlueCount.Value : 0) + (current.IBlueCount.HasValue ? current.IBlueCount.Value : 0);
row.CreateCell(8).SetCellValue(total);
row.GetCell(8).CellStyle = td_normal;
// 直接蓝领人数
row.CreateCell(9).SetCellValue(current.DBlueCount ?? 0);
row.GetCell(9).CellStyle = td_normal;
// 间接蓝领人数
row.CreateCell(10).SetCellValue(current.IBlueCount ?? 0);
row.GetCell(10).CellStyle = td_normal;
// 间接蓝领人数占比
row.CreateCell(11).SetCellValue(current.BlueRate.HasValue ? (double)current.BlueRate.Value : 0);
row.GetCell(11).CellStyle = td_percent;
// 直接蓝领出勤工时
row.CreateCell(12).SetCellValue(current.DBlueHourTotal.HasValue ? (double)current.DBlueHourTotal.Value : 0);
row.GetCell(12).CellStyle = td_normal;
// 间接蓝领出勤工时
row.CreateCell(13).SetCellValue(current.IBlueHourTotal.HasValue ? (double)current.IBlueHourTotal.Value : 0);
row.GetCell(13).CellStyle = td_normal;
// 间接蓝领出勤占比
row.CreateCell(14).SetCellValue(current.BlueHourRate.HasValue ? (double)current.BlueHourRate.Value : 0);
row.GetCell(14).CellStyle = td_percent;
// 异常停机工时
row.CreateCell(15).SetCellValue(current.FaultHour.HasValue ? (double)current.FaultHour.Value : 0);
row.GetCell(15).CellStyle = td_decimal;
// 占比
row.CreateCell(16).SetCellValue(current.FaultHourRate.HasValue ? (double)current.FaultHourRate.Value : 0);
row.GetCell(16).CellStyle = td_percent;
// 换模准备工时
row.CreateCell(17).SetCellValue(current.MouldReadyHour.HasValue ? (double)current.MouldReadyHour.Value : 0);
row.GetCell(17).CellStyle = td_decimal;
// 占比
row.CreateCell(18).SetCellValue(current.MouldReadyHourRate.HasValue ? (double)current.MouldReadyHourRate.Value : 0);
row.GetCell(18).CellStyle = td_percent;
// 废品工时
row.CreateCell(19).SetCellValue(current.ScrapHour.HasValue ? (double)current.ScrapHour.Value : 0);
row.GetCell(19).CellStyle = td_decimal;
// 占比
row.CreateCell(20).SetCellValue(current.ScrapHourRate.HasValue ? (double)current.ScrapHourRate.Value : 0);
row.GetCell(20).CellStyle = td_percent;
// 其他无效工时
row.CreateCell(21).SetCellValue(current.OtherHour.HasValue ? (double)current.OtherHour.Value : 0);
row.GetCell(21).CellStyle = td_decimal;
// 占比
row.CreateCell(22).SetCellValue(current.OtherHourRate.HasValue ? (double)current.OtherHourRate.Value : 0);
row.GetCell(22).CellStyle = td_percent;
// 直接蓝领工时利用率
row.CreateCell(23).SetCellValue(current.BlueUseRate.HasValue ? (double)current.BlueUseRate.Value : 0);
row.GetCell(23).CellStyle = td_percent;
row_index++;
}
// 设置第三列宽度
sheet.SetColumnWidth(2, 20 * 256);
// 冻结前三行与前三列
sheet.CreateFreezePane(3, 3, 3, 3);
workbook.Write(ms);
}
/// <summary>
/// 生成Excel表格头
/// </summary>
/// <param name="workbook"></param>
/// <param name="sheet"></param>
/// <param name="title"></param>
private void CreateHead(HSSFWorkbook workbook, HSSFSheet sheet, string title)
{
// 创建标题行
HSSFRow row_0 = sheet.CreateRow(0) as HSSFRow;
row_0.HeightInPoints = 50;
row_0.CreateCell(0).SetCellValue(title);
row_0.GetCell(0).CellStyle = th_head_title;
CellRangeAddress region_title = new CellRangeAddress(0, 0, 0, 23);
sheet.AddMergedRegion(region_title);
sheet.SetEnclosedBorderOfRegion(region_title, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
// 创建第二行
HSSFRow row_1 = sheet.CreateRow(1) as HSSFRow;
row_1.HeightInPoints = 50;
// 工厂基本信息
row_1.CreateCell(0).SetCellValue("工厂基本信息");
row_1.GetCell(0).CellStyle = th_grey25_title;
CellRangeAddress row1_1 = new CellRangeAddress(1, 1, 0, 2);
sheet.AddMergedRegion(row1_1);
sheet.SetEnclosedBorderOfRegion(row1_1, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
// 指标达成情况
row_1.CreateCell(3).SetCellValue("指标达成情况");
row_1.GetCell(3).CellStyle = th_skyblue_title;
CellRangeAddress row1_2 = new CellRangeAddress(1, 1, 3, 5);
sheet.AddMergedRegion(row1_2);
sheet.SetEnclosedBorderOfRegion(row1_2, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
// 指标源数据
row_1.CreateCell(6).SetCellValue("指标源数据");
row_1.GetCell(6).CellStyle = th_lime_title;
CellRangeAddress row1_3 = new CellRangeAddress(1, 1, 6, 7);
sheet.AddMergedRegion(row1_3);
sheet.SetEnclosedBorderOfRegion(row1_3, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
// 蓝领人员结构
row_1.CreateCell(8).SetCellValue("蓝领人员结构");
row_1.GetCell(8).CellStyle = th_grey40_title;
CellRangeAddress row1_4 = new CellRangeAddress(1, 1, 8, 11);
sheet.AddMergedRegion(row1_4);
sheet.SetEnclosedBorderOfRegion(row1_4, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
// 蓝领出勤工时结构
row_1.CreateCell(12).SetCellValue("蓝领出勤工时结构");
row_1.GetCell(12).CellStyle = th_paleblue_title;
CellRangeAddress row1_5 = new CellRangeAddress(1, 1, 12, 14);
sheet.AddMergedRegion(row1_5);
sheet.SetEnclosedBorderOfRegion(row1_5, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
// 直接蓝领无效工时分析
row_1.CreateCell(15).SetCellValue("直接蓝领无效工时分析");
row_1.GetCell(15).CellStyle = th_tan_title;
CellRangeAddress row1_6 = new CellRangeAddress(1, 1, 15, 22);
sheet.AddMergedRegion(row1_6);
sheet.SetEnclosedBorderOfRegion(row1_6, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
// 直接蓝领工时利用率
row_1.CreateCell(23).SetCellValue("直接蓝领工时利用率");
row_1.GetCell(23).CellStyle = th_normal_title;
CellRangeAddress row1_7 = new CellRangeAddress(1, 2, 23, 23);
sheet.AddMergedRegion(row1_7);
sheet.SetEnclosedBorderOfRegion(row1_7, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
// 创建第二行
HSSFRow row_2 = sheet.CreateRow(2) as HSSFRow;
row_2.HeightInPoints = 50;
// NO
row_2.CreateCell(0).SetCellValue("NO");
row_2.GetCell(0).CellStyle = th_grey25_title;
// 工厂代码
row_2.CreateCell(1).SetCellValue("工厂代码");
row_2.GetCell(1).CellStyle = th_grey25_title;
// 工厂名称
row_2.CreateCell(2).SetCellValue("工厂名称");
row_2.GetCell(2).CellStyle = th_grey25_title;
// 目标
row_2.CreateCell(3).SetCellValue("目标");
row_2.GetCell(3).CellStyle = th_skyblue_title;
// 实绩
row_2.CreateCell(4).SetCellValue("实绩");
row_2.GetCell(4).CellStyle = th_skyblue_title;
// 达成率
row_2.CreateCell(5).SetCellValue("达成率");
row_2.GetCell(5).CellStyle = th_skyblue_title;
// 蓝领出勤工时
row_2.CreateCell(6).SetCellValue("蓝领出勤工时");
row_2.GetCell(6).CellStyle = th_lime_title;
// 生产有效工时
row_2.CreateCell(7).SetCellValue("生产有效工时");
row_2.GetCell(7).CellStyle = th_lime_title;
// 蓝领人数
row_2.CreateCell(8).SetCellValue("蓝领人数");
row_2.GetCell(8).CellStyle = th_grey40_title;
// 直接蓝领人数
row_2.CreateCell(9).SetCellValue("直接蓝领人数");
row_2.GetCell(9).CellStyle = th_grey40_title;
// 间接蓝领人数
row_2.CreateCell(10).SetCellValue("间接蓝领人数");
row_2.GetCell(10).CellStyle = th_grey40_title;
// 间接蓝领人数占比
row_2.CreateCell(11).SetCellValue("间接蓝领人数占比");
row_2.GetCell(11).CellStyle = th_grey40_title;
// 直接蓝领出勤工时
row_2.CreateCell(12).SetCellValue("直接蓝领出勤工时");
row_2.GetCell(12).CellStyle = th_paleblue_title;
// 间接蓝领出勤工时
row_2.CreateCell(13).SetCellValue("间接蓝领出勤工时");
row_2.GetCell(13).CellStyle = th_paleblue_title;
// 间接蓝领出勤占比
row_2.CreateCell(14).SetCellValue("间接蓝领出勤占比");
row_2.GetCell(14).CellStyle = th_paleblue_title;
// 异常停机工时
row_2.CreateCell(15).SetCellValue("异常停机工时");
row_2.GetCell(15).CellStyle = th_tan_title;
// 占比
row_2.CreateCell(16).SetCellValue("占比");
row_2.GetCell(16).CellStyle = th_tan_title;
// 换模准备工时
row_2.CreateCell(17).SetCellValue("换模准备工时");
row_2.GetCell(17).CellStyle = th_tan_title;
// 占比
row_2.CreateCell(18).SetCellValue("占比");
row_2.GetCell(18).CellStyle = th_tan_title;
// 废品工时
row_2.CreateCell(19).SetCellValue("废品工时");
row_2.GetCell(19).CellStyle = th_tan_title;
// 占比
row_2.CreateCell(20).SetCellValue("占比");
row_2.GetCell(20).CellStyle = th_tan_title;
// 其他无效工时
row_2.CreateCell(21).SetCellValue("其他无效工时");
row_2.GetCell(21).CellStyle = th_tan_title;
// 占比
row_2.CreateCell(22).SetCellValue("占比");
row_2.GetCell(22).CellStyle = th_tan_title;
// 占比
row_2.CreateCell(23).SetCellValue("直接蓝领工时利用率");
row_2.GetCell(23).CellStyle = th_normal_title;
}
}以下为如何进行转换操作：
private string GetEmailHtmlInfo(MemoryStream ms, string daily_xls_path, string daily_html_path)
{
// 将Html进行保存
using (var fs_xls = new FileStream(daily_xls_path, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
ms.Position = 0;
ms.CopyTo(fs_xls);
ExcelConvertHelper convert_helper = new ExcelConvertHelper();
convert_helper.ConvertToHtml(daily_xls_path, daily_html_path);
}
string html_info = File.ReadAllText(daily_html_path);
using (var pm = new PreMailer.Net.PreMailer(html_info))
{
var document = pm.Document;
var result = pm.MoveCssInline();
return result.Html;
}
}
类似资料：