c# 导出数据到Excel模板

最近在做一个发邮件的功能，客户要求需要导出一个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(1); //第一个工作薄。
        if (worksheet == null)//工作薄中没有工作表
        {
            return;
        }

        //1、获取数据
        int rowCount = DT.Rows.Count;
        if (rowCount < 1)//没有取到数据
        {
            return;
        }

        //2、写入数据，Excel索引从1开始
        for (int i = 1; i <= rowCount; i++)
        {
            int row_ = 2 + i;  //Excel模板上表头占了1行
            int dt_row = i - 1; //dataTable的行是从0开始的 
            worksheet.Cells[row_, 1] = DT.Rows[dt_row]["Lastname_EN"].ToString();
            worksheet.Cells[row_, 2] = DT.Rows[dt_row]["Firstname_EN"].ToString();
            worksheet.Cells[row_, 3] = DT.Rows[dt_row]["namechinese"].ToString();
        }
        //调整Excel的样式。
        Microsoft.Office.Interop.Excel.Range rg = worksheet.Cells.get_Range("A3", worksheet.Cells[rowCount + 2, 32]);
        rg.Borders.LineStyle = 1; //单元格加边框
        worksheet.Columns.AutoFit(); //自动调整列宽

        //隐藏某一行
        //选中部分单元格，把选中的单元格所在的行的Hidden属性设为true
        //worksheet.get_Range(app.Cells[2, 1], app.Cells[2, 32]).EntireRow.Hidden = true;

        //删除某一行
        worksheet.get_Range(app.Cells[2, 1], app.Cells[2, 32]).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;
    }
}

View Code

二、使用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 > 0)
        {
            WriterExcel(hssfworkbookDown, 0, 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 = 10;
        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 = 1;//因为模板有表头，所以从第2行开始写
        for (int j = 0; j < DT.Rows.Count; j++)
        {
            HSSFRow dataRow = (HSSFRow)sheet.CreateRow(j + n);
            string strDepID = DT.Rows[j]["relationship"].ToString().Trim();
            dataRow.CreateCell(0);
            dataRow.Cells[0].SetCellValue(strDepID == "" ? DT.Rows[j]["Lastname_EN"].ToString() : "");
            dataRow.CreateCell(1);
            dataRow.Cells[1].SetCellValue(strDepID == "" ? DT.Rows[j]["Firstname_EN"].ToString() : "");
            dataRow.CreateCell(2);
            dataRow.Cells[2].SetCellValue(strDepID == "" ? DT.Rows[j]["namechinese"].ToString() : "");

            for (int i = 0; i <= 2; i++)//循环列，添加样式
            {
                dataRow.Cells[i].CellStyle = TableS9;
            }
        }
        //设定第一行，第一列的单元格选中
        sheet.SetActiveCell(0, 0);
    }
    catch (Exception ex)
    {
        WriteLog(ex.ToString());
    }
}

View Code

 

最终效果展示：

 

如果您看了本篇博客,觉得对您有所收获，请点击右下角的 [推荐]

如果您想转载本博客，请注明出处

如果您对本文有意见或者建议，欢迎留言

感谢您的阅读，请关注我的后续博客