最近在做一个发邮件的功能，客户要求需要导出一个Excel附件，并给了附件的格式，
eg:
| Last Name 姓 | First Name 名 |
Chinese Characters |
实现方式有两种：
一、使用Microsoft.Office.Interop.Excel组件的方式
二、使用NPOI的方式
下面讲一下这两种方式的具体实现：
一、使用Microsoft.Office.Interop.Excel组件的方式
该方式需要引入Microsoft.Office.Interop.Excel；System.Reflection
实现代码：
/// <summary>
/// 生成附件（使用Microsoft.Office.Interop.Excel组件的方式）
/// </summary>
/// <param name="DT"></param>
/// <returns></returns>
public static void GenerateAttachment(DataTable DT)
{
try
{
//需要添加 Microsoft.Office.Interop.Excel引用
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
if (app == null)//服务器上缺少Excel组件，需要安装Office软件
{
return;
}
app.Visible = false;
app.UserControl = true;
string strTempPath = Application.StartupPath + "\\EmailTemplate\\TE Enrollment Form.xls";
Microsoft.Office.Interop.Excel.Workbooks workbooks = app.Workbooks;
Microsoft.Office.Interop.Excel._Workbook workbook = workbooks.Add(strTempPath); //加载模板
Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Sheets;
Microsoft.Office.Interop.Excel._Worksheet worksheet = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(); //第一个工作薄。
if (worksheet == null)//工作薄中没有工作表
{
return;
} //1、获取数据
int rowCount = DT.Rows.Count;
if (rowCount < )//没有取到数据
{
return;
} //2、写入数据，Excel索引从1开始
for (int i = ; i <= rowCount; i++)
{
int row_ = + i; //Excel模板上表头占了1行
int dt_row = i - ; //dataTable的行是从0开始的
worksheet.Cells[row_, ] = DT.Rows[dt_row]["Lastname_EN"].ToString();
worksheet.Cells[row_, ] = DT.Rows[dt_row]["Firstname_EN"].ToString();
worksheet.Cells[row_, ] = DT.Rows[dt_row]["namechinese"].ToString();
}
//调整Excel的样式。
Microsoft.Office.Interop.Excel.Range rg = worksheet.Cells.get_Range("A3", worksheet.Cells[rowCount + , ]);
rg.Borders.LineStyle = ; //单元格加边框
worksheet.Columns.AutoFit(); //自动调整列宽 //隐藏某一行
//选中部分单元格，把选中的单元格所在的行的Hidden属性设为true
//worksheet.get_Range(app.Cells[2, 1], app.Cells[2, 32]).EntireRow.Hidden = true; //删除某一行
worksheet.get_Range(app.Cells[, ], app.Cells[, ]).EntireRow.Delete(Microsoft.Office.Interop.Excel.XlDirection.xlUp); //3、保存生成的Excel文件
//Missing在System.Reflection命名空间下
string savePath = Application.StartupPath + "\\Temp\\TEEnrollmentForm\\TE Enrollment Form.xls";
workbook.SaveAs(savePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); //4、按顺序释放资源
NAR(worksheet);
NAR(sheets);
NAR(workbook);
NAR(workbooks);
app.Quit();
NAR(app);
}
catch (Exception ex)
{
WriteLog(ex.ToString());
}
}
/// <summary>
/// 释放资源
/// </summary>
/// <param name="o"></param>
public static void NAR(object o)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
}
catch (Exception ex)
{
WriteLog(ex.ToString());
}
finally
{
o = null;
}
}
二、使用NPOI的方式
该方式需要引用NPOI.dll
实现代码：
/// <summary>
/// ExportExcel（使用NPOI的方式）
/// </summary>
/// <param name="DT"></param>
public static void ExportExcel(DataTable DT)
{
try
{
HSSFWorkbook hssfworkbookDown;
string modelExlPath = Application.StartupPath + "\\EmailTemplate\\TE Enrollment Form.xls";
if (File.Exists(modelExlPath) == false)//模板不存在
{
return;
}
using (FileStream file = new FileStream(modelExlPath, FileMode.Open, FileAccess.Read))
{
hssfworkbookDown = new HSSFWorkbook(file);
file.Close();
}
if (DT.Rows.Count > )
{
WriterExcel(hssfworkbookDown, , DT); string filename = "TE Enrollment Form.xls";
string strFilePath = Application.StartupPath + "\\Temp\\TEEnrollmentForm";
if (Directory.Exists(strFilePath) == false)
{
Directory.CreateDirectory(strFilePath);
}
strFilePath = strFilePath + "\\" + filename;
FileStream files = new FileStream(strFilePath, FileMode.Create);
hssfworkbookDown.Write(files);
files.Close();
if (File.Exists(strFilePath) == false)//附件生成失败
{
return;
}
}
}
catch (Exception ex)
{
WriteLog(ex.ToString());
}
}
/// <summary>
/// WriterExcel
/// </summary>
/// <param name="hssfworkbookDown"></param>
/// <param name="sheetIndex"></param>
/// <param name="DT"></param>
public static void WriterExcel(HSSFWorkbook hssfworkbookDown, int sheetIndex, DataTable DT)
{
try
{
#region 设置单元格样式
//字体
HSSFFont fontS9 = (HSSFFont)hssfworkbookDown.CreateFont();
fontS9.FontName = "Arial";
fontS9.FontHeightInPoints = ;
fontS9.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.NORMAL;
//表格
ICellStyle TableS9 = (ICellStyle)hssfworkbookDown.CreateCellStyle();
TableS9.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
TableS9.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
TableS9.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
TableS9.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
TableS9.WrapText = true;
TableS9.SetFont(fontS9);
#endregion HSSFSheet sheet = (HSSFSheet)hssfworkbookDown.GetSheetAt(sheetIndex);
hssfworkbookDown.SetSheetHidden(sheetIndex, false);
hssfworkbookDown.SetActiveSheet(sheetIndex); int n = ;//因为模板有表头，所以从第2行开始写
for (int j = ; j < DT.Rows.Count; j++)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(j + n);
string strDepID = DT.Rows[j]["relationship"].ToString().Trim();
dataRow.CreateCell();
dataRow.Cells[].SetCellValue(strDepID == "" ? DT.Rows[j]["Lastname_EN"].ToString() : "");
dataRow.CreateCell();
dataRow.Cells[].SetCellValue(strDepID == "" ? DT.Rows[j]["Firstname_EN"].ToString() : "");
dataRow.CreateCell();
dataRow.Cells[].SetCellValue(strDepID == "" ? DT.Rows[j]["namechinese"].ToString() : ""); for (int i = ; i <= ; i++)//循环列，添加样式
{
dataRow.Cells[i].CellStyle = TableS9;
}
}
//设定第一行，第一列的单元格选中
sheet.SetActiveCell(, );
}
catch (Exception ex)
{
WriteLog(ex.ToString());
}
}
最终效果展示：