C#使用模板文件批量导出word文档

需求背景

因为疫情爆发，进入一级响应状态，公安部门进行了严格出入境管理，需要对每个出入境的人进行状态跟踪。

疫情专班会将出入境的每个人员汇总在一张Excel表中，如下图所示：

每一行对应一个人员信息，一个人员信息需要生成一个协查函，需要将人员信息填入到固定格式的协查函中，协查函的格式如下图所示：

功能实现

功能实现分成两个部分，一是从Excel读取数据，二是将读取的数据批量输出到Word文档。

从Excel读取数据使用NPOI，输出到word文档使用Microsoft Word Object Library.

NPOI可以在NuGet程序包中搜索到：

Microsoft Word Object Library.可以在类库中引用到。

考虑到速度，功能用winform程序实现。

实现效果

启动程序时：

选择Excel：

导入Excel：

导出到Word：

打开Word看看：

在开发之前需要对创建一个Word(dot格式)模板文件，内容格式很协查函一样，然后再需要插入数据的地方设置书签即可。

程序代码

ExcelHelp类用于将选中的Excel数据导入到datatable中。

using NPOI.HSSF.UserModel;
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;
using System.Windows.Forms;

namespace WindowsFormsApp1
{
    public class ExcelHelp
    {

        /// 
        /// 根据指定流文件将Excel导入到datatable中
        /// 
        public virtual DataTable ExcelExportDataTable()
        {
            DataTable dt = new DataTable();
            OpenFileDialog fileDialog = new OpenFileDialog();
            fileDialog.Filter = "Excel文件|*.xls;*.xlsx";
            fileDialog.InitialDirectory = "E:\";//设置默认打开路径
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                string fileName = fileDialog.FileName;//得到文件所在位置
                FileStream fs = new FileStream(fileDialog.FileName, FileMode.Open, FileAccess.Read);
                dt = ExcelToDataTable(fs, 0, 2);
            }
            return dt;
        }


        /// 
        /// 将excel数据流中的数据转化为datatable
        /// 
        /// 指定流文件
        /// 导入sheet页页号
        /// 行标题行号
        /// 
        private DataTable ExcelToDataTable(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex)
        {
            //HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
            //IWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
            IWorkbook workbook = new XSSFWorkbook(ExcelFileStream);
            ISheet sheet = workbook.GetSheetAt(SheetIndex);
            DataTable table = new DataTable();


            
            IRow headerRow = sheet.GetRow(HeaderRowIndex);
            int cellCount = headerRow.LastCellNum;

            DataColumn columnNo = new DataColumn("No");
            table.Columns.Add(columnNo);
            DataColumn columnName = new DataColumn("Name");//姓名
            table.Columns.Add(columnName);
            DataColumn columnID = new DataColumn("ID");//身份证
            table.Columns.Add(columnID);
            DataColumn columnPhone = new DataColumn("Phone");//电话
            table.Columns.Add(columnPhone);
            DataColumn columnStreet = new DataColumn("Street");//电话
            table.Columns.Add(columnStreet);
            DataColumn columnXVillage = new DataColumn("XVillage");//行政村
            table.Columns.Add(columnXVillage);
            DataColumn columnZVillage = new DataColumn("ZVillage");//自然村
            table.Columns.Add(columnZVillage);
            DataColumn columnAddress = new DataColumn("Address");//具体地址
            table.Columns.Add(columnAddress);
            DataColumn columnDutyName = new DataColumn("DutyName");//责任人姓名
            table.Columns.Add(columnDutyName);
            DataColumn columnDutyPhone = new DataColumn("DutyPhone");//责任人电话
            table.Columns.Add(columnDutyPhone);
            DataColumn columnStatus = new DataColumn("Status");//管控状态
            table.Columns.Add(columnStatus);
            DataColumn columnBackTime = new DataColumn("BackTime");//返回封锁区时间
            table.Columns.Add(columnBackTime);
            DataColumn columnProvince = new DataColumn("Province");//省
            table.Columns.Add(columnProvince);
            DataColumn columnCity = new DataColumn("City");//市
            table.Columns.Add(columnCity);
            DataColumn columnCountry = new DataColumn("Country");//村
            table.Columns.Add(columnCountry);
            DataColumn columnAddress2 = new DataColumn("Address2");//具体地址
            table.Columns.Add(columnAddress2);

            
            int firstRowNum = 3;
            //int rowCount = sheet.LastRowNum;
            for (int i = firstRowNum; i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dataRow = table.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                    {
                        dataRow[j] = row.GetCell(j).ToString();
                    }
                }
                table.Rows.Add(dataRow);
            }
            ExcelFileStream.Close();
            workbook = null;
            sheet = null;
            return table;
        }
    }
}

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Microsoft.Office.Interop.Word;


namespace WindowsFormsApp1
{
    public partial class Form1 : Form
    {
        public System.Data.DataTable table;
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            ExcelHelp eh = new ExcelHelp();
            table = eh.ExcelExportDataTable();
            dataGridView1.AllowUserToAddRows = false;
            dataGridView1.DataSource = table;
            if (table.Rows.Count == 0)
            {
                MessageBox.Show("Excel中无数据!");
                return;
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (table == null)
            {
                MessageBox.Show("请先选择Excel!");
                return;
            }
            int count = table.Rows.Count;
            //Microsoft.Office.Interop.Word._Application oWord;

            if (count > 0)
            {
                for (int i = 0; i < count; i++)
                {

                    //创建一个Word应用程序实例
                    Microsoft.Office.Interop.Word._Application oWord = new Microsoft.Office.Interop.Word.Application();
                    object oMissing = System.Reflection.Missing.Value;
                    //设置为不可见
                    oWord.Visible = false;
                    //模板文件地址，debug bin 目录下
                    //object oTemplate = "E://template.dot";
                    object oTemplate = System.Windows.Forms.Application.StartupPath + "\template.dot";
                    //以模板为基础生成文档
                    Microsoft.Office.Interop.Word._Document oDoc = oWord.Documents.Add(ref oTemplate, ref oMissing, ref oMissing, ref oMissing);
                    //声明书签数组
                    object[] oBookMark = new object[8];
                    //赋值书签名
                    oBookMark[0] = "Name";
                    oBookMark[1] = "ID";
                    oBookMark[2] = "Phone";
                    oBookMark[3] = "City";
                    oBookMark[4] = "Country";
                    oBookMark[5] = "Address2";
                    oBookMark[6] = "MM";
                    oBookMark[7] = "DD";
                    //赋值数据到书签的位置
                    string mm = DateTime.Now.Month.ToString();
                    string dd = DateTime.Now.Day.ToString();
                   
                    oDoc.Bookmarks.get_Item(ref oBookMark[0]).Range.Text = table.Rows[i]["Name"].ToString();
                    oDoc.Bookmarks.get_Item(ref oBookMark[1]).Range.Text = table.Rows[i]["ID"].ToString();
                    oDoc.Bookmarks.get_Item(ref oBookMark[2]).Range.Text = table.Rows[i]["Phone"].ToString();
                  
                    oDoc.Bookmarks.get_Item(ref oBookMark[3]).Range.Text = table.Rows[i]["City"].ToString();
                    oDoc.Bookmarks.get_Item(ref oBookMark[4]).Range.Text = table.Rows[i]["Country"].ToString();
                    oDoc.Bookmarks.get_Item(ref oBookMark[5]).Range.Text = table.Rows[i]["Address2"].ToString();
                    oDoc.Bookmarks.get_Item(ref oBookMark[6]).Range.Text = mm;
                    oDoc.Bookmarks.get_Item(ref oBookMark[7]).Range.Text = dd;
                    //导出的Word文件地址设置在 debug bin里的NewFile文件夹
                    object path = System.Windows.Forms.Application.StartupPath + "\NewFile\" + filename + ".doc";
                    oDoc.SaveAs(ref path, ref oMissing, ref oMissing, ref oMissing,
                    ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing,
                    ref oMissing, ref oMissing, ref oMissing, ref oMissing, ref oMissing,
                    ref oMissing, ref oMissing);
                    oDoc.Close(ref oMissing, ref oMissing, ref oMissing);

                    //关闭word模板
                    oWord.Quit(ref oMissing, ref oMissing, ref oMissing);
                }
                MessageBox.Show("导出成功,生成了" + count + "个文件!");
            }
            else
            {
                MessageBox.Show("Excel中无数据!");
            }
        }
    }
}

C#使用模板文件批量导出word文档

.Net相关栏目本月热门文章