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
NPOI建立並匯出word
2018-08-03
kxcel, 方便匯入和匯出 Excel
2019-09-05
Excel
如何使用 JavaScript 匯入和匯出 Excel
2024-03-26
JavaScriptExcel
Vue框架下實現匯入匯出Excel、匯出PDF
2021-09-09
Vue框架Excel
前端實現Excel匯入和匯出功能
2019-02-16
前端Excel
.Net Core Excel匯入匯出神器Npoi.Mapper
2020-12-13
ExcelAPP
如何使用JavaScript匯入和匯出Excel檔案
2019-07-05
JavaScriptExcel
分享：一個基於NPOI的excel匯入匯出元件（強型別）
2018-11-13
Excel元件型別
C# 實現NPOI的Excel匯出
2022-01-19
C#Excel
基於NPOI封裝匯出Excel方法
2023-02-21
封裝Excel
vue excel匯入匯出
2019-03-07
VueExcel
原生PHP網頁匯出和匯入excel檔案例項
2019-11-26
PHP網頁Excel
Angular Excel 匯入與匯出
2019-04-17
AngularExcel
php讀取excel檔案資料的匯入和匯出
2018-06-09
PHPExcel
NPOI 在指定單元格匯入匯出圖片
2021-03-02
EasyOffice-.NetCore一行程式碼匯入匯出Excel,生成Word
2019-05-19
NetCore行程Excel
vue + element + 匯入、匯出excel表格
2018-09-10
VueExcel
基於Vue + axios + WebApi + NPOI匯出Excel檔案
2019-06-04
VueiOSWebAPIExcel
Oracle資料庫匯入匯出。imp匯入命令和exp匯出命令
2022-03-01
Oracle資料庫
DB2 匯入和匯出命令列和客戶端匯出
2021-09-02
DB2命令列客戶端
10分鐘做好 Bootstrap Blazor 的表格元件匯出 Excel/Word/Html/Pdf
2023-01-11
bootBlazor元件ExcelHTML
MySQL入門--匯出和匯入資料
2019-06-04
MySql
EasyPoi, Excel資料的匯入匯出
2020-10-01
Excel
Vue + Element 實現匯入匯出Excel
2020-04-13
VueExcel
MATLAB匯入txt和excel檔案技巧彙總：批量匯入、單個匯入
2021-07-04
MatlabExcel
IDEA 匯出和匯入jar包教程
2018-09-29
IdeaJAR
匯入和匯出AWR的資料
2018-06-10
前端必讀：如何在 JavaScript 中使用 SpreadJS 匯入和匯出 Excel 檔案
2022-09-22
前端JavaScriptJSExcel
Java之POI操作Excel表-匯入匯出
2020-09-23
JavaExcel
基於 PhpSpreadsheet 簡單 Excel 匯入匯出
2019-04-23
PHPExcel
關於java中Excel的匯入匯出
2024-08-27
JavaExcel
EasyExcel完成excel檔案的匯入匯出
2022-03-27
Excel
excel的匯入與匯出---通用版
2021-08-16
Excel
SQL資料庫的匯入和匯出
2020-10-29
SQL資料庫
Navicat如何匯入和匯出sql檔案
2022-03-01
SQL
Oracle資料泵的匯入和匯出
2020-11-24
Oracle
ClickHouse 資料表匯出和匯入（qbit）
2022-06-01

NPOI匯出和匯入Excel,Word和PDF

Models資料夾

Controllers資料夾

Views資料夾

執行結果如圖:

相關文章