NPOI匯出和匯入Excel,Word和PDF

風靈使發表於2018-07-23

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>

執行結果如圖:

這裡寫圖片描述

基於EPPlus和NPOI實現的Excel匯入匯出
2021-11-16
Excel
模板匯出word和 EXcel
2010-10-15
Excel
kxcel, 方便匯入和匯出 Excel
2019-09-05
Excel
Vue框架下實現匯入匯出Excel、匯出PDF
2021-09-09
Vue框架Excel
ASP.NET Excel匯入和匯出
2010-11-09
ASP.NETExcel
如何使用 JavaScript 匯入和匯出 Excel
2024-03-26
JavaScriptExcel
前端實現Excel匯入和匯出功能
2019-02-16
前端Excel
（轉）excel和sql server的匯入匯出
2008-03-04
ExcelSQLServer
如何使用JavaScript匯入和匯出Excel檔案
2019-07-05
JavaScriptExcel
SQL SERVER 和EXCEL的資料匯入匯出
2009-12-22
SQLServerExcel
.Net Core Excel匯入匯出神器Npoi.Mapper
2020-12-13
ExcelAPP
分享：一個基於NPOI的excel匯入匯出元件（強型別）
2018-11-13
Excel元件型別
C# word或者excel儲存或匯出為PDF
2016-11-07
C#Excel
C# 實現NPOI的Excel匯出
2022-01-19
C#Excel
基於NPOI封裝匯出Excel方法
2023-02-21
封裝Excel
匯出Excel或word文件
2012-07-06
Excel
ASP.NET EXCEL資料的匯出和匯入
2008-09-25
ASP.NETExcel
vue excel匯入匯出
2019-03-07
VueExcel
原生PHP網頁匯出和匯入excel檔案例項
2019-11-26
PHP網頁Excel
asp.net 利用NPOI匯出Excel通用類
2017-06-22
ASP.NETExcel
Angular Excel 匯入與匯出
2019-04-17
AngularExcel
php讀取excel檔案資料的匯入和匯出
2018-06-09
PHPExcel
vue + element + 匯入、匯出excel表格
2018-09-10
VueExcel
轉java操作excel匯入匯出
2016-04-12
JavaExcel
excel表格匯入word方法彙總
2016-06-27
Excel
匯出和匯入Gradle工程
2014-03-21
Gradle
postgresql 資料匯入和匯出
2013-07-16
SQL
在.NET環境下將報表匯出EXCEL和WORD (轉)
2007-12-14
Excel
10分鐘做好 Bootstrap Blazor 的表格元件匯出 Excel/Word/Html/Pdf
2023-01-11
bootBlazor元件ExcelHTML
Oracle資料庫匯入匯出。imp匯入命令和exp匯出命令
2022-03-01
Oracle資料庫
DB2 匯入和匯出命令列和客戶端匯出
2021-09-02
DB2命令列客戶端
MySQL入門--匯出和匯入資料
2019-06-04
MySql
基於Vue + axios + WebApi + NPOI匯出Excel檔案
2019-06-04
VueiOSWebAPIExcel
asp.net中利用NPOI匯出資料到excel中
2015-07-03
ASP.NETExcel
ASP.NET利用NPOI元件快速匯入匯出Execl資料
2014-09-20
ASP.NET元件
Vue + Element 實現匯入匯出Excel
2020-04-13
VueExcel
EasyPoi, Excel資料的匯入匯出
2020-10-01
Excel
java 匯入匯出Excel工具類ExcelUtil
2016-04-27
JavaExcel

NPOI匯出和匯入Excel,Word和PDF

Models資料夾

Controllers資料夾

Views資料夾

執行結果如圖:

相關文章