重庆忠县网站建设公司推荐,园区网络建设方案,购物网站 购物车界面如何做,在线定制家具需求背景 因为疫情爆发#xff0c;进入一级响应状态#xff0c;公安部门进行了严格出入境管理#xff0c;需要对每个出入境的人进行状态跟踪。 疫情专班会将出入境的每个人员汇总在一张Excel表中#xff0c;如下图所示#xff1a; 每一行对应一个人员信息#xff0c;一个…需求背景 因为疫情爆发进入一级响应状态公安部门进行了严格出入境管理需要对每个出入境的人进行状态跟踪。 疫情专班会将出入境的每个人员汇总在一张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{/// summary/// 根据指定流文件将Excel导入到datatable中/// /summarypublic 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;}/// summary/// 将excel数据流中的数据转化为datatable/// /summary/// param nameExcelFileStream指定流文件/param/// param nameSheetIndex导入sheet页页号/param/// param nameHeaderRowIndex行标题行号/param/// returns/returnsprivate 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);/*构建datatable表体*/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中无数据!);}}}
}
