最近在做一个发邮件的功能，客户要求需要导出一个Excel附件，并给了附件的格式，
eg:
| Last Name 姓 | First Name 名 |
Chinese Characters |
实现方式有两种：
一、使用Microsoft.Office.Interop.Excel组件的方式
二、使用NPOI的方式
下面讲一下这两种方式的具体实现：
一、使用Microsoft.Office.Interop.Excel组件的方式
该方式需要引入Microsoft.Office.Interop.Excel；System.Reflection
实现代码：
1 /// <summary> 2 /// 生成附件（使用Microsoft.Office.Interop.Excel组件的方式） 3 /// </summary> 4 /// <param name="DT"></param> 5 /// <returns></returns> 6 public static void GenerateAttachment(DataTable DT) 7 { 8 try 9 { 10 //需要添加 Microsoft.Office.Interop.Excel引用 11 Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application(); 12 if (app == null)//服务器上缺少Excel组件，需要安装Office软件 13 { 14 return; 15 } 16 app.Visible = false; 17 app.UserControl = true; 18 string strTempPath = Application.StartupPath + "\EmailTemplate\TE Enrollment Form.xls"; 19 Microsoft.Office.Interop.Excel.Workbooks workbooks = app.Workbooks; 20 Microsoft.Office.Interop.Excel._Workbook workbook = workbooks.Add(strTempPath); //加载模板 21 Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Sheets; 22 Microsoft.Office.Interop.Excel._Worksheet worksheet = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1); //第一个工作薄。 23 if (worksheet == null)//工作薄中没有工作表 24 { 25 return; 26 } 27 28 //1、获取数据 29 int rowCount = DT.Rows.Count; 30 if (rowCount < 1)//没有取到数据 31 { 32 return; 33 } 34 35 //2、写入数据，Excel索引从1开始 36 for (int i = 1; i <= rowCount; i++) 37 { 38 int row_ = 2 + i; //Excel模板上表头占了1行 39 int dt_row = i - 1; //dataTable的行是从0开始的 40 worksheet.Cells[row_, 1] = DT.Rows[dt_row]["Lastname_EN"].ToString(); 41 worksheet.Cells[row_, 2] = DT.Rows[dt_row]["Firstname_EN"].ToString(); 42 worksheet.Cells[row_, 3] = DT.Rows[dt_row]["namechinese"].ToString(); 43 } 44 //调整Excel的样式。 45 Microsoft.Office.Interop.Excel.Range rg = worksheet.Cells.get_Range("A3", worksheet.Cells[rowCount + 2, 32]); 46 rg.Borders.LineStyle = 1; //单元格加边框 47 worksheet.Columns.AutoFit(); //自动调整列宽 48 49 //隐藏某一行 50 //选中部分单元格，把选中的单元格所在的行的Hidden属性设为true 51 //worksheet.get_Range(app.Cells[2, 1], app.Cells[2, 32]).EntireRow.Hidden = true; 52 53 //删除某一行 54 worksheet.get_Range(app.Cells[2, 1], app.Cells[2, 32]).EntireRow.Delete(Microsoft.Office.Interop.Excel.XlDirection.xlUp); 55 56 57 //3、保存生成的Excel文件 58 //Missing在System.Reflection命名空间下 59 string savePath = Application.StartupPath + "\Temp\TEEnrollmentForm\TE Enrollment Form.xls"; 60 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); 61 62 //4、按顺序释放资源 63 NAR(worksheet); 64 NAR(sheets); 65 NAR(workbook); 66 NAR(workbooks); 67 app.Quit(); 68 NAR(app); 69 } 70 catch (Exception ex) 71 { 72 WriteLog(ex.ToString()); 73 } 74 } 75 /// <summary> 76 /// 释放资源 77 /// </summary> 78 /// <param name="o"></param> 79 public static void NAR(object o) 80 { 81 try 82 { 83 System.Runtime.InteropServices.Marshal.ReleaseComObject(o); 84 } 85 catch (Exception ex) 86 { 87 WriteLog(ex.ToString()); 88 } 89 finally 90 { 91 o = null; 92 } 93 }
二、使用NPOI的方式
该方式需要引用NPOI.dll
实现代码：
1 /// <summary> 2 /// ExportExcel（使用NPOI的方式） 3 /// </summary> 4 /// <param name="DT"></param> 5 public static void ExportExcel(DataTable DT) 6 { 7 try 8 { 9 HSSFWorkbook hssfworkbookDown; 10 string modelExlPath = Application.StartupPath + "\EmailTemplate\TE Enrollment Form.xls"; 11 if (File.Exists(modelExlPath) == false)//模板不存在 12 { 13 return; 14 } 15 using (FileStream file = new FileStream(modelExlPath, FileMode.Open, FileAccess.Read)) 16 { 17 hssfworkbookDown = new HSSFWorkbook(file); 18 file.Close(); 19 } 20 if (DT.Rows.Count > 0) 21 { 22 WriterExcel(hssfworkbookDown, 0, DT); 23 24 string filename = "TE Enrollment Form.xls"; 25 string strFilePath = Application.StartupPath + "\Temp\TEEnrollmentForm"; 26 if (Directory.Exists(strFilePath) == false) 27 { 28 Directory.CreateDirectory(strFilePath); 29 } 30 strFilePath = strFilePath + "\" + filename; 31 FileStream files = new FileStream(strFilePath, FileMode.Create); 32 hssfworkbookDown.Write(files); 33 files.Close(); 34 if (File.Exists(strFilePath) == false)//附件生成失败 35 { 36 return; 37 } 38 } 39 } 40 catch (Exception ex) 41 { 42 WriteLog(ex.ToString()); 43 } 44 } 45 /// <summary> 46 /// WriterExcel 47 /// </summary> 48 /// <param name="hssfworkbookDown"></param> 49 /// <param name="sheetIndex"></param> 50 /// <param name="DT"></param> 51 public static void WriterExcel(HSSFWorkbook hssfworkbookDown, int sheetIndex, DataTable DT) 52 { 53 try 54 { 55 #region 设置单元格样式 56 //字体 57 HSSFFont fontS9 = (HSSFFont)hssfworkbookDown.CreateFont(); 58 fontS9.FontName = "Arial"; 59 fontS9.FontHeightInPoints = 10; 60 fontS9.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.NORMAL; 61 //表格 62 ICellStyle TableS9 = (ICellStyle)hssfworkbookDown.CreateCellStyle(); 63 TableS9.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; 64 TableS9.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; 65 TableS9.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; 66 TableS9.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; 67 TableS9.WrapText = true; 68 TableS9.SetFont(fontS9); 69 #endregion 70 71 HSSFSheet sheet = (HSSFSheet)hssfworkbookDown.GetSheetAt(sheetIndex); 72 hssfworkbookDown.SetSheetHidden(sheetIndex, false); 73 hssfworkbookDown.SetActiveSheet(sheetIndex); 74 75 int n = 1;//因为模板有表头，所以从第2行开始写 76 for (int j = 0; j < DT.Rows.Count; j++) 77 { 78 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(j + n); 79 string strDepID = DT.Rows[j]["relationship"].ToString().Trim(); 80 dataRow.CreateCell(0); 81 dataRow.Cells[0].SetCellValue(strDepID == "" ? DT.Rows[j]["Lastname_EN"].ToString() : ""); 82 dataRow.CreateCell(1); 83 dataRow.Cells[1].SetCellValue(strDepID == "" ? DT.Rows[j]["Firstname_EN"].ToString() : ""); 84 dataRow.CreateCell(2); 85 dataRow.Cells[2].SetCellValue(strDepID == "" ? DT.Rows[j]["namechinese"].ToString() : ""); 86 87 for (int i = 0; i <= 2; i++)//循环列，添加样式 88 { 89 dataRow.Cells[i].CellStyle = TableS9; 90 } 91 } 92 //设定第一行，第一列的单元格选中 93 sheet.SetActiveCell(0, 0); 94 } 95 catch (Exception ex) 96 { 97 WriteLog(ex.ToString()); 98 } 99 }
最终效果展示：
