C# 读取EXCEL的数据批量插入单个PDF里的多个位置

发布于:2024-12-20 ⋅ 阅读:(81) ⋅ 点赞:(0)

C# 读取EXCEL的数据批量插入单个PDF里的多个位置

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using iTextSharp.text.pdf;
using iTextSharp.text;
using Font = iTextSharp.text.Font;
using Microsoft.Office.Interop.Excel;
using Application = Microsoft.Office.Interop.Excel.Application;
using Excel = Microsoft.Office.Interop.Excel;
using Rectangle = iTextSharp.text.Rectangle;


namespace EXCEL批量插入PDF指定位置
{
    public partial class Form1 : Form
    {
        InsertPdf insertPdf = new InsertPdf();
        List<InsertPdf> pudfPathList = new List<InsertPdf>();
        public Form1()
        {
            InitializeComponent();
        }

        private void btn_chose_Click(object sender, EventArgs e)
        {
            string OpenFilePath;//存储选择到的文件的完整路径
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Title = "选择Excel文件";//设置对话框标题栏的内容
            ofd.Filter = "Excel文件(*.xls*)|*.xls*;";  //这是设置对话框内显示的指定后缀类型文件(可设置多个)
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                OpenFilePath = ofd.FileName;
                input.Text = OpenFilePath;
            }
            else
            {
                return;
            }
            OPENEXCEL();
        }
        /// <summary>
        /// 打开Excel文件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void OPENEXCEL()
        {
            if (input.Text!="")
            {


                   string excelPath = input.Text;
                   object missing=Missing.Value;
                
                Excel.Application excel = new Excel.Application();//创建Excel对象


                try
                {
                    if (excel == null)
                    {
                        MessageBox.Show("Excel对象为空,请检查是否已安装Excel");
                        return;
                    }
                    else
                    {
                        excel.Visible = false;//显示Excel
                        excel.DisplayAlerts = false;//不显示警告信息
                        excel.UserControl = false;//不显示用户界面
                        excel.ScreenUpdating = false;//屏幕刷新
                        Excel.Workbook wb = excel.Application.Workbooks.Open(excelPath, missing, true, missing, missing, missing, missing, missing, missing, true, missing, missing, missing, missing, missing);//打开Excel文件
                        Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];//获取当前工作表
                        int rowCount = ws.UsedRange.Rows.Count;//获取当前工作表的行数
                        int colCount = ws.UsedRange.Columns.Count;//获取当前工作表的列数
                        dataGridView1.Rows.Clear();
                        dataGridView1.Columns.Clear();
                        for (int i = 1; i <= colCount; i++)
                        {
                            if (ws.Cells[1, i].Value2 == null)
                            {

                                break;
                            }
                            string colName = ws.Cells[1, i].Value2.ToString().Trim();
                            dataGridView1.Columns.Add("column" + i, colName);

                        }

                        for (int i = 2; i <= rowCount; i++)
                        {
                            int index = dataGridView1.Rows.Add();
                            if (ws.Cells[i, 1].Value2 != null)
                            {
                                for (int j = 1; j <= colCount; j++)
                                {
                                    if (ws.Cells[i, j].Value2 == null)
                                    { continue; }
                                    dataGridView1.Rows[index].Cells[j - 1].Value = ws.Cells[i, j].Value2.ToString().Trim();

                                }


                            }

                        }


                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("打开Excel文件失败!" + ex.Message);

                    //throw;
                }
                finally { 
                    CloseExcel(excel);
                    MessageBox.Show("加载成功!");

                }
            }
            
            

        }

        private void CloseExcel(Application excel)
        {
            excel.Quit();
            excel = null;
            Process[] excels = Process.GetProcessesByName("excel");
            foreach (Process p in excels)
            {
                p.Kill();//杀掉excel进程
            }
            GC.Collect();
        }
        /// <summary>
        /// 处理单个页面填写多个地方的PDF
        /// </summary>
        /// <param name="pdfPath">PDF文件路径</param>
        /// <param name="pdfsavePath">存PDF文件路径</param>
        /// <param name="xnum">填写字的坐标x</param>
        /// <param name="ynum">填写字的坐标y</param>
        /// <param name="fontnum">字体大小</param>
        /// <param name="textstr">要填入的文字</param>
        /// 
        private void PDFadd(string pdfPath, string pdfsavePath, InsertPdf insertPdf)
        {
            if (dataGridView1.Rows.Count > 0) 
            {
                for (int i = 0; i < dataGridView1.Rows.Count; i++)
                {
                    insertPdf.xnum = Convert.ToInt32(dataGridView1.Rows[i].Cells[0].Value);
                    insertPdf.ynum = Convert.ToInt32(dataGridView1.Rows[i].Cells[1].Value);
                    insertPdf.fontnum = Convert.ToInt32(dataGridView1.Rows[i].Cells[2].Value);
                    insertPdf.textstr = dataGridView1.Rows[i].Cells[3].Value.ToString();
                    pudfPathList.Add(insertPdf);
                }

            }

            //原文件地址
            string url = @pdfPath;
            //最后要保存的文件地址
            string urlNew= @pdfsavePath;
            //读取原pdf文件
            PdfReader reader = new PdfReader(url);
            PdfStamper stamper = new PdfStamper(reader, new FileStream(urlNew, FileMode.Create, FileAccess.Write, FileShare.None));
            setOnePage(pudfPathList, reader, stamper);
       

        }
        /// <summary>
        /// 处理单个页面多个填写的地方
        /// </summary>
        /// <param name="pdfPathList"></param>
        private void setOnePage(List<InsertPdf> pdfPathList, PdfReader reader, PdfStamper stamper) 
        {
            if (pdfPathList.Count == 1)
            {
                insertPdf = pdfPathList[1];
                //获取系统的字体
                BaseFont baseFont = BaseFont.CreateFont("C:\\Windows\\Fonts\\simhei.ttf", BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
                //字体属性及大小
                Font font = new Font(baseFont, insertPdf.fontnum);
                //需要添加的文字
                Phrase addName = new Phrase(insertPdf.textstr, font);
                //获取pdf总页数
                int pagesCount = reader.NumberOfPages;
                //获取pdf页面大小
                Rectangle pageSize = reader.GetPageSize(1);
                //设置pdf的宽
                float width = pageSize.Width;
                //设置pdf的高
                float height = pageSize.Height;
                //遍历所有页,从第一页开始
                for (int i = 1; i <= pagesCount; i++)//遍历所有页
                {

                    if (i == 1)
                    {//第一页
                     //设置当前页
                        PdfContentByte canvas = stamper.GetOverContent(i);
                        //将文本添加到每页pdf的右上角
                        ColumnText.ShowTextAligned(canvas, Element.ALIGN_RIGHT, addName, insertPdf.xnum, insertPdf.ynum, 0);//900, 435,x,y
                    }

                }
                //释放
                stamper.Close();
                MessageBox.Show("添加成功");
            }
            else if (pdfPathList.Count > 1) 
            {
                //获取系统的字体
                BaseFont baseFont = BaseFont.CreateFont("C:\\Windows\\Fonts\\simhei.ttf", BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
                //获取pdf总页数
                int pagesCount = reader.NumberOfPages;
                //获取pdf页面大小
                Rectangle pageSize = reader.GetPageSize(1);
                //设置pdf的宽
                float width = pageSize.Width;
                //设置pdf的高
                float height = pageSize.Height;
                PdfContentByte canvas = stamper.GetOverContent(pagesCount);
                for (int i = 0; i < pdfPathList.Count; i++)
                {
                    insertPdf = pdfPathList[i];

                    
                    //字体属性及大小
                    Font font = new Font(baseFont, insertPdf.fontnum);
                    //需要添加的文字
                    Phrase addName = new Phrase(insertPdf.textstr, font);
                    ColumnText.ShowTextAligned(canvas, Element.ALIGN_RIGHT, addName, insertPdf.xnum, insertPdf.ynum, 0);

                }



                stamper.Close();
                MessageBox.Show("添加成功");

            }  
        }
    }

   
}



网站公告

今日签到

点亮在社区的每一天
去签到