相关文章推荐
性感的椅子  ·  如何查询json字段类型postgresql ...·  1 年前    · 
发财的西红柿  ·  javascript - How to ...·  2 年前    · 
爱健身的跑步鞋  ·  花钱续命正在成为可能 | Foodaily每日食品·  2 年前    · 
豪爽的金针菇  ·  Django Template语法中 ...·  2 年前    · 
刀枪不入的乌冬面  ·  SQL查询最近12个月的数据量 没有补0 ...·  2 年前    · 
Code  ›  NPOI导出和导入Excel,Word和PDF_npoi pdf_风神修罗使的博客
string form npoi
https://blog.csdn.net/WuLex/article/details/81145477
求醉的大白菜
2 年前
using System; using System.Data.Entity; using System.ComponentModel.DataAnnotations.Schema; using System.Linq; public partial class NPOIModel : DbContext public NPOIModel() : base( "name=NPOIModel" ) Database.SetInitializer( new InitDatabase()); public virtual DbSet<Account> Account { get ; set ; } protected override void OnModelCreating(DbModelBuilder modelBuilder)

InitDatabase.cs 

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;
namespace NPOItest.Models
    public class InitDatabase : DropCreateDatabaseAlways<NPOIModel>
        protected override void Seed(NPOItest.Models.NPOIModel context)
            Account acc1 = new Account { Username = "admin", Password = "admin", Name = "Kevin", Sex = "Man", Email = "admin@NPOT.com", Company = "XXX_NO.1", Position = "R&D", Phone = "0000-0000" };
            Account acc2 = new Account { Username = "user", Password = "user", Name = "Durant", Sex = "Female", Email = "user@NPOT.com", Company = "AAA_NO.2", Position = "CEO", Phone = "0000-1111" };
            context.Account.Add(acc1);
            context.Account.Add(acc2);
            context.SaveChanges();

NPOIServices.cs
 

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XWPF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
namespace NPOItest.Models.Sevices
    public class NPOIServices
        private NPOIModel db = new NPOIModel();
        public HSSFWorkbook AccountEmpty_E(FileStream fs)
            HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs);
            HSSFSheet ws = (HSSFSheet)templateWorkbook.GetSheetAt(0);
            ws.GetRow(1).GetCell(2).SetCellValue(DateTime.Now.ToString("yyyy/MM/dd"));
            return templateWorkbook;
        public HSSFWorkbook AccountData_E(FileStream fs)
            List<Account> data = db.Account.ToList();
            HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs);
            HSSFSheet ws = (HSSFSheet)templateWorkbook.GetSheetAt(0);
            ws.GetRow(1).GetCell(2).SetCellValue(DateTime.Now.ToString("yyyy/MM/dd"));
            int startRow = 3;
            int i = 1;
            foreach (Account item in data)
                ws.GetRow(startRow).GetCell(0).SetCellValue(i.ToString());
                ws.GetRow(startRow).GetCell(1).SetCellValue(item.Username);
                ws.GetRow(startRow).GetCell(2).SetCellValue(item.Name);
                ws.GetRow(startRow).GetCell(3).SetCellValue(item.Email);
                ws.GetRow(startRow).GetCell(4).SetCellValue(item.Sex);
                ws.GetRow(startRow).GetCell(5).SetCellValue(item.Company);
                ws.GetRow(startRow).GetCell(6).SetCellValue(item.Position);
                ws.GetRow(startRow).GetCell(7).SetCellValue(item.Phone);
                startRow++;
                i++;
            ws.ShiftRows(ws.LastRowNum + 1, ws.LastRowNum + (ws.LastRowNum - startRow + 1), -(ws.LastRowNum - startRow + 1));
            return templateWorkbook;
        public string InsertData_E(HSSFWorkbook excel)
            HSSFSheet ws = (HSSFSheet)excel.GetSheetAt(0);
            List<Account> newAccounts = new List<Account>();
            int startRow = 3;
            for (int i = startRow; i <= ws.LastRowNum; i++)
                newAccounts.Add(new Account
                    Username = ws.GetRow(startRow).GetCell(1).StringCellValue,
                    Password = "520520",
                    Name = ws.GetRow(startRow).GetCell(2).StringCellValue,
                    Email = ws.GetRow(startRow).GetCell(3).StringCellValue,
                    Sex = ws.GetRow(startRow).GetCell(4).StringCellValue,
                    Company = ws.GetRow(startRow).GetCell(5).StringCellValue,
                    Position = ws.GetRow(startRow).GetCell(6).StringCellValue,
                    Phone = ws.GetRow(startRow).GetCell(7).StringCellValue
                startRow++;
            db.Account.AddRange(newAccounts);
            db.SaveChanges();
            return "Success !";
        public XWPFDocument AccountEmpty_W(FileStream fs)
            XWPFDocument templateWorkbook = new XWPFDocument(fs);
            XWPFTable tb = templateWorkbook.Tables[0];
            tb.GetRow(0).GetCell(1).SetText(DateTime.Now.ToString("yyyy/MM/dd"));
            return templateWorkbook;
        public XWPFDocument AccountData_W(FileStream fs)
            List<Account> data = db.Account.ToList();
            XWPFDocument templateWorkbook = new XWPFDocument(fs);
            XWPFTable tb = templateWorkbook.Tables[0];
            tb.GetRow(0).GetCell(1).SetText(DateTime.Now.ToString("yyyy/MM/dd"));
            int startRow = 3;
            int i = 1;
            foreach (Account item in data)
                tb.CreateRow().CreateCell();
                tb.GetRow(startRow).GetCell(0).SetText(i.ToString());
                tb.GetRow(startRow).GetCell(1).SetText(item.Username);
                tb.GetRow(startRow).GetCell(2).SetText(item.Name);
                tb.GetRow(startRow).CreateCell();
                tb.GetRow(startRow).GetCell(3).SetText(item.Email);
                tb.GetRow(startRow).CreateCell();
                tb.GetRow(startRow).GetCell(4).SetText(item.Sex);
                tb.GetRow(startRow).CreateCell();
                tb.GetRow(startRow).GetCell(5).SetText(item.Company);
                tb.GetRow(startRow).CreateCell();
                tb.GetRow(startRow).GetCell(6).SetText(item.Position);
                tb.GetRow(startRow).CreateCell();
                tb.GetRow(startRow).GetCell(7).SetText(item.Phone);
                startRow++;
                i++;
            return templateWorkbook;
        public string InsertData_W(XWPFDocument word)
            XWPFTable tb = word.Tables[0];
            List<Account> newAccounts = new List<Account>();
            int startRow = 3;
            for (int i = startRow; i <= 5; i++)
                newAccounts.Add(new Account
                    Username = tb.GetRow(startRow).GetCell(1).GetText(),
                    Password = "520520",
                    Name = tb.GetRow(startRow).GetCell(2).GetText(),
                    Email = tb.GetRow(startRow).GetCell(3).GetText(),
                    Sex = tb.GetRow(startRow).GetCell(4




    
).GetText(),
                    Company = tb.GetRow(startRow).GetCell(5).GetText(),
                    Position = tb.GetRow(startRow).GetCell(6).GetText(),
                    Phone = tb.GetRow(startRow).GetCell(7).GetText()
                startRow++;
            db.Account.AddRange(newAccounts);
            db.SaveChanges();
            return "Success !";

ConvertPDFHelper.cs
 

using System;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;
namespace NPOItest.Models.Sevices
    public class ConvertPDFHelper
        public string ConvertExcelToPdf(string inputFile, string pdfPath)
            Application excelApp = new Application();
            excelApp.Visible = false;
            Workbook workbook = null;
            Workbooks workbooks = null;
                workbooks = excelApp.Workbooks;
                workbook = workbooks.Open(inputFile);
                workbook.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF,
                                             pdfPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                             Type.Missing, Type.Missing);
            finally
                if (workbook != null)
                    workbook.Close(XlSaveAction.xlDoNotSaveChanges);
                    while (Marshal.FinalReleaseComObject(workbook) != 0) { };
                    workbook = null;
                if (workbooks != null)
                    workbooks.Close();
                    while (Marshal.FinalReleaseComObject(workbooks) != 0) { };
                    workbooks = null;
                if (excelApp != null)
                    excelApp.Quit();
                    excelApp.Application.Quit();
                    while (Marshal.FinalReleaseComObject(excelApp) != 0) { };
                    excelApp = null;
                GC.Collect();
                GC.WaitForPendingFinalizers();
            return pdfPath;          

Controllers文件夹
 

NPOIController.cs
 

using NPOI.HSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using NPOItest.Models.Sevices;
using NPOI.XWPF.UserModel;
namespace NPOItest.Controllers
    public class NPOIController : Controller
        private string fileSavedPath = "~/Content/";
        private NPOIServices NPServices = new NPOIServices();
        // GET: NPOI Excel
        public void EmptyExport_E()
            FileStream fs = new FileStream(string.Concat(Server.MapPath(fileSavedPath), "/Excels/temp.xls"), FileMode.Open, FileAccess.ReadWrite);
            HSSFWorkbook templateWorkbook = NPServices.AccountEmpty_E(fs);
            Response.Clear();
            Response.ContentType = "application/vnd.openxmlformats";
            Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlPathEncode("AccountEmpty.xls"));
            MemoryStream ms = new MemoryStream();
            templateWorkbook.Write(ms);
            ms.WriteTo(Response.OutputStream);
            Response.End();
        // GET: NPOI Excel
        public void DataExport_E()
            FileStream fs = new FileStream(string.Concat(Server.MapPath(fileSavedPath), "/Excels/temp.xls"), FileMode.Open, FileAccess.ReadWrite);
            HSSFWorkbook templateWorkbook = NPServices.AccountData_E(fs);
            Response.Clear();
            Response.ContentType = "application/vnd.openxmlformats";
            Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlPathEncode("AccountData.xls"));
            MemoryStream ms = new MemoryStream();
            templateWorkbook.Write(ms);
            ms.WriteTo(Response.OutputStream);
            Response.End();
        // GET: NPOI EXCEL to PDF
        public void PDFExport_E()
            FileStream fs = new FileStream(string.Concat(Server.MapPath(fileSavedPath), "/Excels/temp.xls"), FileMode.Open, FileAccess.ReadWrite);
            HSSFWorkbook templateWorkbook = NPServices.AccountData_E(fs);
            MemoryStream ms = new MemoryStream();
            templateWorkbook.Write(ms);
            string target = string.Concat(Server.MapPath(fileSavedPath), "/temp/" + System.Guid.NewGuid().ToString() + "EXCEL.xls");//??
            using (var fileStream = new FileStream(target, FileMode.CreateNew, FileAccess.ReadWrite))
                ms.Position = 0;
                ms.CopyTo(fileStream); // fileStream is not populated
            ConvertPDFHelper Convert = new ConvertPDFHelper();
            string pdfPath = string.Concat(Server.MapPath(fileSavedPath), "/Excels/temp/" + System.Guid.NewGuid().ToString() + ".pdf");
            string PDFfile = Convert.ConvertExcelToPdf(target, pdfPath);
            Stream iStream = new FileStream(PDFfile, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite);
            MemoryStream memoryStream = new MemoryStream();
            iStream.CopyTo(memoryStream);
            iStream.Dispose();
            Response.Clear();
            Response.ContentType = "application/vnd.openxmlformats";
            Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlPathEncode("AccountPDF.pdf"));
            memoryStream.WriteTo(Response.OutputStream);
            Response.End();
        // POST: NPOI Excel
        [HttpPost]
        public ActionResult Import_E(HttpPostedFileBase file)
            string message;
            if (file != null && file.ContentLength > 0 && file.ContentLength < (10 * 1024 * 1024))
                string filetype = file.FileName.Split('.').Last();
                string fileName = Path.GetFileName(file.FileName);
                string path = Path.Combine(Server.MapPath("~/Content/Imports"), fileName);
                if (filetype == "xls")
                    file.SaveAs(path);
                    FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read);
                    HSSFWorkbook excel = new HSSFWorkbook(fs);
                    message = NPServices.InsertData_E(excel);
                    message = "File format error !";
                message = "Please select file import !";
            ViewBag.Message = message;
            return View();
        // GET: NPOI Word
        public void EmptyExport_W()
            FileStream fs = new FileStream(string.Concat(Server.MapPath(fileSavedPath), "/Excels/temp.docx"), FileMode.Open, FileAccess.ReadWrite);
            XWPFDocument templateWorkbook = NPServices.AccountEmpty_W(fs);
            Response.Clear();
            Response.ContentType = "application/vnd.openxmlformats";
            Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlPathEncode("AccountEmpty.docx"));
            MemoryStream ms = new MemoryStream();
            templateWorkbook.Write(ms);
            ms.WriteTo(Response.OutputStream);
            Response.End();
        // GET: NPOI Word
        public void DataExport_W()
            FileStream fs = new FileStream(string.Concat(Server.MapPath(fileSavedPath), "/Excels/temp.docx"), FileMode.Open, FileAccess.ReadWrite);
            XWPFDocument templateWorkbook = NPServices.AccountData_W(fs);
            Response.Clear();
            Response.ContentType = "application/vnd.openxmlformats";
            Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlPathEncode("AccountData.docx"));
            MemoryStream ms = new MemoryStream();
            templateWorkbook.Write(ms);
            ms.WriteTo(Response.OutputStream);
            Response.End();
        // GET: NPOI Word TO PDF 
        public void PDFExport_W()
            FileStream fs = new FileStream(string.Concat(Server.MapPath(fileSavedPath), "/Excels/temp.docx"), FileMode.Open, FileAccess.ReadWrite);
            XWPFDocument templateWorkbook = NPServices.AccountData_W(fs);
            MemoryStream ms = new MemoryStream();
            templateWorkbook.Write(ms);
            string target = string.Concat(Server.MapPath(fileSavedPath), "/temp/" + System.Guid.NewGuid().ToString() + "Word.docx");//??
            using (var fileStream = new FileStream(target, FileMode.CreateNew, FileAccess.ReadWrite))
                ms.Position = 0;
                ms.CopyTo(fileStream); // fileStream is not populated
            ConvertPDFHelper Convert = new ConvertPDFHelper();
            string pdfPath = string.Concat(Server.MapPath(fileSavedPath), "/Excels/temp/" + System.Guid.NewGuid().ToString() + ".pdf");
            string PDFfile = Convert.ConvertExcelToPdf(target, pdfPath);
            Stream iStream = new FileStream(PDFfile, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite);
            MemoryStream memoryStream = new MemoryStream();
            iStream.CopyTo(memoryStream);
            iStream.Dispose();
            Response.Clear();
            Response.ContentType = "application/vnd.openxmlformats";
            Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlPathEncode("AccountPDF.pdf"));
            memoryStream.WriteTo(Response.OutputStream);
            Response.End();
        // POST: NPOI Word
        [HttpPost]
        public ActionResult Import_W(HttpPostedFileBase file)
            string message;
            if (file != null && file.ContentLength > 0 && file.ContentLength < (10 * 1024 * 1024))
                string filetype = file.FileName.Split('.').Last();
                string fileName = Path.GetFileName(file.FileName);
                string path = Path.Combine(Server.MapPath("~/Content/Imports"), fileName);
                if (filetype == "docx")
                    file.SaveAs(path);
                    FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read);
                    XWPFDocument word = new XWPFDocument(fs);
                    message = NPServices.InsertData_W(word);
                    message = "File format error !";
                message = "Please select file import !";
            ViewBag.Message = message;
            return View();

Views文件夹
 

Index.cshtml
 
    ViewBag.Title = "Home Page";
<div class="jumbotron">
    <h1>NPOI Excel</h1>
        <button class="btn btn-primary btn-lg" onclick="location.href='@Url.Action("EmptyExport_E", "NPOI")'">
            帐户空数据库导出&raquo;
        </button>
        <button class="btn btn-primary btn-lg" onclick="location.href='@Url.Action("DataExport_E", "NPOI")'">
            帐户数据导出 &raquo;
        </button>
    <button class="btn btn-primary btn-lg" onclick="location.href='@Url.Action("PDFExport_E", "NPOI")'">
        PDF导出 &raquo;
    </button>
    <form class="form-horizontal form-group" id="UploadForm" action="@Url.Action("Import_E", "NPOI")" method="post" enctype="multipart/form-data">
        <label for="file" style="font-size:17px">导入Excel&nbsp;:</label>&nbsp;
        <input class="form-control btn btn-default btn-lg" type="file" name="file" id="file" />
        <input type="submit" value="Import Excel" class="btn btn-primary btn-lg" />
    </form>
</div>
<div class="jumbotron">
    <h1>NPOI Word</h1>
        <button class="btn btn-primary btn-lg" onclick="location.href='@Url.Action("EmptyExport_W", "NPOI")'">
            帐户空数据库导出 &raquo;
        </button>
        <button class="btn btn-primary btn-lg" onclick="location.href='@Url.Action("DataExport_W", "NPOI")'">
            帐户数据库导出 &raquo;
        </button>
    <button class="btn btn-primary btn-lg" onclick="location.href='@Url.Action("PDFExport_W", "NPOI")'">
        PDF导出 &raquo;
    </button>
    <form class="form-horizontal form-group" id="UploadForm" action="@Url.Action("Import_W", "NPOI")" method="post" enctype="multipart/form-data">
        <label for="file" style="font-size:17px">导入Word&nbsp;:</label>&nbsp;
        <input class="form-control btn btn-default btn-lg" type="file" name="file" id="file" />
        <input type="submit" value="Import Word" class="btn btn-primary btn-lg" />
    </form>
</div>
 

Import_E.cshtml
 
    ViewBag.Title = "Result";
<h2>Result</h2>
<h3>@ViewBag.Message</h3>
 

Import_W.cshtml
 
    ViewBag.Title = "Result";
<h2>Result</h2>
<h3>@ViewBag.Message</h3>
 

运行结果如图:
 
                                    通过NPOI将数据导出为Excel表格，添加新功能导出PDF，我已知的NPOI并没有直接导出为PDF的方法，如果有请告诉我，谢谢。
网上查询了将Excel转为PDF导出的方法，有的需要office组件，有的是Spire.XLS，这两个我都没接触过，为了避免坑多，我放弃了使用这两种方法导出PDF（其实就是我不会，没别的可说），之后我找到了不需要这个两种方法的另一种方式，引用iTextSharp，来导出PDF。
文章是这一篇：https://blog.csdn.net/weixin_43663915/ar.
                                    C# NPOI 是一个基于 .NET Framework 的 Excel 和 Word 操作库。它不仅可以读取和写入 Excel 和 Word 文件，还可以对 Excel 和 Word 文件进行格式化和样式编辑，支持多种常见的文件格式，如XLS，XLSX等。本篇文章将针对C# NPOI操作Excel进行举例详解。
                                    最近研究Excel表格转PDF的，网上用的各种第三方库的方法不少，比如用Spire.XLS、也有用开源的DinkToPdf的，发现都不好用。Spire.XLS是收费的，免费的导出后和水印，DinkToPdf的不好控制，不能做到精确的控制。
最后参考了2篇文章后，写了一个DEMO：
C#将Excel转为PDF，添加引用iTextSharp_花落下的日子的博客-CSDN博客
通过c#高效实现excel转pdf，无需依赖office组件（支持excel中带有图片的转换）_沉默的方三毛的博客-CSDN博..
                                    1.新建workbook 不同版本，个人建议07以上为准。
public static IWorkbook Create(ExcelVersion version = ExcelVersion.Excel07)
            IWorkbook workbook = null;
            switch (version)
                                    您可以使用 NPOI 库来将 Word 文档转换为 PDF。
以下是使用 NPOI 库进行 Word 转 PDF 转换的示例代码：
// 首先需要引用 NPOI 库和 Spire.Doc 库
using NPOI.XWPF.UserModel;
using Spire.Doc;
// 定义输入和输出文件的路径
string inputFilePath = @"C:\input.docx";
st...
                                    NPOI写Excel，Spire.XLS for.NET组件转换Excel为PDF
首先要引用NPOI动态库和Microsoft.Office.Interop.excel.dll （Microsoft.Office.Interop.excel.dll  最好使用使用NuGet下载 ，或者使用网上下载，网上下载以后解压文件，把Microsoft.Office.Interop.excel.dll拷贝...
 
推荐文章
性感的椅子  ·  如何查询json字段类型postgresql中的空值？-腾讯云开发者社区-腾讯云
1 年前
发财的西红柿  ·  javascript - How to send request to php file via Fetch API post method - Stack Overflow
2 年前
爱健身的跑步鞋  ·  花钱续命正在成为可能 | Foodaily每日食品
2 年前
豪爽的金针菇  ·  Django Template语法中 OneToOne、ForeignKey 外键查询 - 温斌 - 博客园
2 年前
刀枪不入的乌冬面  ·  SQL查询最近12个月的数据量 没有补0 - yingzi__block - 博客园
2 年前
今天看啥   ·   Py中国   ·   codingpro   ·   小百科   ·   link之家   ·   卧龙AI搜索
删除内容请联系邮箱 2879853325@qq.com
Code - 代码工具平台
© 2024 ~ 沪ICP备11025650号