NPOI导出和导入Excel,Word和PDF

最新推荐文章于 2025-09-11 11:09:22 发布

转载最新推荐文章于 2025-09-11 11:09:22 发布 · 1.9w 阅读

NPOI.NET和EPPlus框架专栏收录该内容

28 篇文章

订阅专栏

本文介绍了一个使用NPOI库实现Excel和Word文件创建、读取、更新及转换为PDF的应用案例，包括数据库初始化、数据填充及从Excel和Word文件中导入数据到数据库等功能。

Models文件夹

NPOIModel.cs

namespace NPOItest.Models
{
    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;


            try
            {
                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);
                }
                else
                {
                    message = "File format error !";
                }
            }
            else
            {
                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);
                }
                else
                {
                    message = "File format error !";
                }
            }
            else
            {
                message = "Please select file import !";
            }
            ViewBag.Message = message;

            return View();
        }
    }
}

Views文件夹

Index.cshtml

@{
    ViewBag.Title = "Home Page";
}

<div class="jumbotron">
    <h1>NPOI Excel</h1>

    <p>
        <button class="btn btn-primary btn-lg" onclick="location.href='@Url.Action("EmptyExport_E", "NPOI")'">
            帐户空数据库导出&raquo;
        </button>
    </p>
    <br />
    <p>
        <button class="btn btn-primary btn-lg" onclick="location.href='@Url.Action("DataExport_E", "NPOI")'">
            帐户数据导出 &raquo;
        </button>
    </p>
    <br />
    <button class="btn btn-primary btn-lg" onclick="location.href='@Url.Action("PDFExport_E", "NPOI")'">
        PDF导出 &raquo;
    </button>
    <br />
    <br />
    <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>

    <p>
        <button class="btn btn-primary btn-lg" onclick="location.href='@Url.Action("EmptyExport_W", "NPOI")'">
            帐户空数据库导出 &raquo;
        </button>
    </p>
    <br />
    <p>
        <button class="btn btn-primary btn-lg" onclick="location.href='@Url.Action("DataExport_W", "NPOI")'">
            帐户数据库导出 &raquo;
        </button>
    </p>
    <br />
    <button class="btn btn-primary btn-lg" onclick="location.href='@Url.Action("PDFExport_W", "NPOI")'">
        PDF导出 &raquo;
    </button>
    <br />
    <br />
    <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>

运行结果如图:

这里写图片描述