總結(jié)一下工作中遇到的NPOI以及在ASP.NETMVC中的使用

2019-11-14 15:51:18

字體：大中小

供稿：網(wǎng)友

1.前言

相信大家在工作中經(jīng)常要遇到一些導(dǎo)入導(dǎo)出Execl操作。學(xué)習(xí)貴在分享，分享使人快樂，園子里的前輩已經(jīng)有很多好的文章，鄙人也是能力有限，在這里把這些好的文章總結(jié)，方便以后再工作中使用。

NPOI：是 POI 項(xiàng)目的 .NET 版本。POI是一個(gè)開源的java讀寫Excel、Word等微軟OLE2組件文檔的項(xiàng)目。

NPOI是構(gòu)建在POI 3.x版本之上的，它可以在沒有安裝Office的情況下對(duì)Word/Excel文檔進(jìn)行讀寫操作。

它不使用Office COM組件(Microsoft.Office.Interop.XXX.dll)，不需要安裝Microsoft Office，支持對(duì)Office 97-2003的文件格式，功能比較強(qiáng)大。

能夠讀寫幾乎所有的Office 97-2003文件格式，至少能夠支持Word, Powerpoint, Excel, Visio的格式。

NPOI官方網(wǎng)站【點(diǎn)擊這里】

1、整個(gè)Excel表格叫做工作表：WorkBook（工作薄），包含的叫頁（工作表）：Sheet；行：Row；單元格Cell。

2、NPOI是POI的C#版本，NPOI的行和列的index都是從0開始

3、POI讀取Excel有兩種格式一個(gè)是HSSF，另一個(gè)是XSSF。

HSSF和XSSF的區(qū)別如下：

HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，擴(kuò)展名是.xls

XSSFWorkbook:是操作Excel2007的版本，擴(kuò)展名是.xlsx

即：HSSF適用2007以前的版本,XSSF適用2007版本及其以上的。

HSSFWorkbook對(duì)應(yīng)的就是Excel文件工作簿，

HSSFSheet對(duì)應(yīng)的就是Excel中sheet 工作表，

HSSFCell對(duì)應(yīng)的就是Excel的單元格，

HSSFRow對(duì)應(yīng)的就是Excel的行

.NET調(diào)用NPOI組件導(dǎo)入導(dǎo)出Excel的操作類

　　此NPOI操作類的優(yōu)點(diǎn)如下：
（1）支持web及winform從DataTable導(dǎo)出到Excel；
（2）生成速度很快；
（3）準(zhǔn)確判斷數(shù)據(jù)類型，不會(huì)出現(xiàn)身份證轉(zhuǎn)數(shù)值等問題；
（4）如果單頁條數(shù)大于65535時(shí)會(huì)新建工作表；
（5）列寬自適應(yīng)；

2.簡(jiǎn)單用法

namespace 導(dǎo)入導(dǎo)出{    public partial class Form1 : Form    {        public Form1()        {            InitializeComponent();        }        PRivate void btnInput_Click(object sender, EventArgs e)        {            #region 導(dǎo)入到DataTable            using (FileStream stream = File.OpenRead("huangjinfeng.xls"))            {                IWorkbook workbook = new HSSFWorkbook(stream);                ISheet sheet = workbook.GetSheet("員工捐款信息表");                DataTable table = new DataTable();                IRow headerRow = sheet.GetRow(0);                int cellCount = headerRow.LastCellNum;                int rowCount = sheet.LastRowNum;                for (int i = headerRow.FirstCellNum; i < cellCount; i++)                {                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);                    table.Columns.Add(column);                }                for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)                {                    IRow row = sheet.GetRow(i);                    DataRow dataRow = table.NewRow();                    if (row != null)                    {                        for (int j = row.FirstCellNum; j < cellCount; j++)                        {                            if (row.GetCell(j) != null)                                dataRow[j] = row.GetCell(j);                        }                    }                    table.Rows.Add(dataRow);                }                this.dataGridView1.DataSource = table;                MessageBox.Show("導(dǎo)入數(shù)據(jù)成功");            }            #endregion            #region 導(dǎo)入到數(shù)據(jù)庫(kù)            //using (FileStream stream = File.OpenRead("huangjinfeng.xls"))            //{            //    string sql="INSERT INTO [dbo].[DonationDetail]([dUserName],[dcID],[dAmount],[dDate],[disdelete],[dCreateTime])";            //    RenderToDb(stream,sql);            //    MessageBox.Show("SQL");            //}            #endregion        }        private void btnOut_Click(object sender, EventArgs e)        {            DonationEntities2 db = new DonationEntities2();            var lists = db.CreateObjectSet<DonationDetail>().Where(c => c.disdelete == 0).ToList();            #region 自由導(dǎo)出            HSSFWorkbook workbook = new HSSFWorkbook();            //2.創(chuàng)建工作表            ISheet sheet = workbook.CreateSheet("員工捐款信息表");            IRow rowHeader = sheet.CreateRow(0);            rowHeader.CreateCell(0, CellType.String).SetCellValue("DId");            rowHeader.CreateCell(1, CellType.String).SetCellValue("員工姓名");            rowHeader.CreateCell(2, CellType.String).SetCellValue("DcId");            rowHeader.CreateCell(3, CellType.String).SetCellValue("捐款金額");            rowHeader.CreateCell(4, CellType.String).SetCellValue("捐款日期");            rowHeader.CreateCell(5, CellType.String).SetCellValue("是否刪除");            rowHeader.CreateCell(6, CellType.String).SetCellValue("創(chuàng)建日期");            for (int i = 0; i < lists.Count; i++)            {                IRow row = sheet.CreateRow(i + 1);                //為指定的行添加列                row.CreateCell(0, CellType.String).SetCellValue(lists[i].dId);                row.CreateCell(1, CellType.String).SetCellValue(lists[i].dUserName);                row.CreateCell(2, CellType.String).SetCellValue(lists[i].dcID);                row.CreateCell(3, CellType.String).SetCellValue(lists[i].dAmount.ToString());                row.CreateCell(4, CellType.String).SetCellValue(Convert.ToDateTime(lists[i].dDate.ToString()));                row.CreateCell(5, CellType.String).SetCellValue(lists[i].disdelete);                row.CreateCell(6, CellType.String).SetCellValue(Convert.ToDateTime(lists[i].dCreateTime.ToString()));            }            //使用文件流做數(shù)據(jù)的寫入            using (FileStream fss = new FileStream("huangjinfeng.xls", FileMode.Create))            {                workbook.Write(fss);            }            MessageBox.Show("導(dǎo)出數(shù)據(jù)成功");            #endregion        }

        #region 導(dǎo)入到數(shù)據(jù)庫(kù)        public static void RenderToDb(Stream excelFileStream, string insertSql)        {            using (excelFileStream)            {                IWorkbook workbook = new HSSFWorkbook(excelFileStream);                ISheet sheet = workbook.GetSheetAt(0);//取第一個(gè)工作表                StringBuilder builder = new StringBuilder();                IRow headerRow = sheet.GetRow(0);//第一行為標(biāo)題行                int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells                int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1                for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)                {                    IRow row = sheet.GetRow(i);                    if (row != null)                    {                        builder.Append(insertSql);                        builder.Append(" values (");                        for (int j = row.FirstCellNum; j < cellCount; j++)                        {                            builder.AppendFormat("'{0}',", row.GetCell(j)).Replace("'", "''");                        }                        builder.Length = builder.Length - 1;                        builder.Append(");");                    }                    if ((i % 50 == 0 || i == rowCount) && builder.Length > 0)                    {                        //每50條記錄一次批量插入到數(shù)據(jù)庫(kù)                        //rowAffected += dbAction(builder.ToString());                        SqlHelper.ExecuteNonQuery(builder.ToString());                        builder.Length = 0;                    }                }            }        }        #endregion

RenderToDb(Stream excelFileStream, string insertSql)

        #region 是否有數(shù)據(jù)        /// <summary>        /// 是否有數(shù)據(jù)        /// </summary>        /// <param name="excelFileStream"></param>        /// <returns></returns>        public static bool HasData(Stream excelFileStream)        {            using (excelFileStream)            {                IWorkbook workbook = new HSSFWorkbook(excelFileStream);                if (workbook.NumberOfSheets > 0)                {                    ISheet sheet = workbook.GetSheetAt(0);                    return sheet.PhysicalNumberOfRows > 0;                }            }            return false;        }         #endregion

HasData(Stream excelFileStream)

} }

3.項(xiàng)目中的Execl導(dǎo)入導(dǎo)出

工作中我一般主要是用到的MVC，在這里就說說我們項(xiàng)目中一般的處理過程。先看看我自己寫的一個(gè)NPOIBase父類。

    public class NPOIBase : ActionResult    {        public IWorkbook _workbook { get; set; }        public ISheet _sheet { get; set; }        public ICellStyle _titleStyle { get; set; }        public ICellStyle _leftStyle { get; set; }        public ICellStyle _centerStyle { get; set; }        public ICellStyle _rightStyle { get; set; }        public ICellStyle _headStyle { get; set; }        public ICellStyle _leftborderStyle { get; set; }        public ICellStyle _rightborderStyle { get; set; }        public ICellStyle _noneRightBorderStyle { get; set; }        public ICellStyle _noneLeftBorderStyle { get; set; }        public ICellStyle _noneLeftAndRightBorderStyle { get; set; }        public ICellStyle _borderStyle { get; set; }
        public override void ExecuteResult(ControllerContext context)        {        }
        public void IniNPOI(bool isHeadBorder = false, string sheetName = "")        {            _workbook = new HSSFWorkbook();            _sheet = string.IsNullOrWhiteSpace(sheetName) ? _workbook.CreateSheet() : _workbook.CreateSheet(sheetName);            IniStyle(isHeadBorder);        }

public void IniStyle(bool isHeadBorder = false)        {            IFont font12 = _workbook.CreateFont();            font12.FontHeightInPoints = 12;            font12.Boldweight = 700;            _titleStyle = _workbook.CreateCellStyle();            _titleStyle.Alignment = HorizontalAlignment.Center;            _titleStyle.VerticalAlignment = VerticalAlignment.Top;            _titleStyle.SetFont(font12);            _leftStyle = _workbook.CreateCellStyle();            _leftStyle.Alignment = HorizontalAlignment.Left;            _leftStyle.VerticalAlignment = VerticalAlignment.Top;            _centerStyle = _workbook.CreateCellStyle();            _centerStyle.Alignment = HorizontalAlignment.Center;            _centerStyle.VerticalAlignment = VerticalAlignment.Top;            _rightStyle = _workbook.CreateCellStyle();            _rightStyle.Alignment = HorizontalAlignment.Right;            _rightStyle.VerticalAlignment = VerticalAlignment.Top;            _headStyle = _workbook.CreateCellStyle();            _headStyle.Alignment = HorizontalAlignment.Center;            _headStyle.VerticalAlignment = VerticalAlignment.Top;            if (isHeadBorder)            {                _headStyle.BorderBottom = BorderStyle.Thin;                _headStyle.BorderLeft = BorderStyle.Thin;                _headStyle.BorderRight = BorderStyle.Thin;                _headStyle.BorderTop = BorderStyle.Thin;            }            _leftborderStyle = _workbook.CreateCellStyle();            _leftborderStyle.Alignment = HorizontalAlignment.Left;            _leftborderStyle.VerticalAlignment = VerticalAlignment.Top;            _leftborderStyle.BorderBottom = BorderStyle.Thin;            _leftborderStyle.BorderLeft = BorderStyle.Thin;            _leftborderStyle.BorderRight = BorderStyle.Thin;            _leftborderStyle.BorderTop = BorderStyle.Thin;            _rightborderStyle = _workbook.CreateCellStyle();            _rightborderStyle.Alignment = HorizontalAlignment.Right;            _rightborderStyle.VerticalAlignment = VerticalAlignment.Top;            _rightborderStyle.BorderBottom = BorderStyle.Thin;            _rightborderStyle.BorderLeft = BorderStyle.Thin;            _rightborderStyle.BorderRight = BorderStyle.Thin;            _rightborderStyle.BorderTop = BorderStyle.Thin;            _noneRightBorderStyle = _workbook.CreateCellStyle();            _noneRightBorderStyle.Alignment = HorizontalAlignment.Left;            _noneRightBorderStyle.VerticalAlignment = VerticalAlignment.Top;            _noneRightBorderStyle.BorderBottom = BorderStyle.Thin;            _noneRightBorderStyle.BorderLeft = BorderStyle.Thin;            _noneRightBorderStyle.BorderTop = BorderStyle.Thin;            _noneLeftBorderStyle = _workbook.CreateCellStyle();            _noneLeftBorderStyle.Alignment = HorizontalAlignment.Right;            _noneLeftBorderStyle.VerticalAlignment = VerticalAlignment.Top;            _noneLeftBorderStyle.BorderBottom = BorderStyle.Thin;            _noneLeftBorderStyle.BorderRight = BorderStyle.Thin;            _noneLeftBorderStyle.BorderTop = BorderStyle.Thin;            _noneLeftAndRightBorderStyle = _workbook.CreateCellStyle();            _noneLeftAndRightBorderStyle.Alignment = HorizontalAlignment.Center;            _noneLeftAndRightBorderStyle.VerticalAlignment = VerticalAlignment.Top;            _noneLeftAndRightBorderStyle.BorderBottom = BorderStyle.Thin;            _noneLeftAndRightBorderStyle.BorderTop = BorderStyle.Thin;            _borderStyle = _workbook.CreateCellStyle();            _borderStyle.Alignment = HorizontalAlignment.Center;            _borderStyle.VerticalAlignment = VerticalAlignment.Top;            _borderStyle.BorderBottom = BorderStyle.Thin;            _borderStyle.BorderLeft = BorderStyle.Thin;            _borderStyle.BorderRight = BorderStyle.Thin;            _borderStyle.BorderTop = BorderStyle.Thin;            IFont font = _workbook.CreateFont();            font.FontHeightInPoints = 10;            font.Boldweight = 700;            _headStyle.SetFont(font);        }

IniStyle

        public void FillHeadCell(IRow row, int colIndex, string value, ICellStyle cellStyle = null, 
                                  NPOI.SS.Util.CellRangeAddress mergedCellRangeAddress = null)        {            if (_sheet == null || row == null) return;            if (cellStyle == null) cellStyle = _headStyle;            FillCell(row, colIndex, value, cellStyle, mergedCellRangeAddress);            _sheet.SetColumnWidth(colIndex, (Encoding.Default.GetBytes(value.Trim()).Length + 4) * 256);        }
        public void FillCell(IRow row, int colIndex, string value, ICellStyle cellStyle = null, 
                             NPOI.SS.Util.CellRangeAddress mergedCellRangeAddress = null)        {            if (_sheet == null || row == null) return;            ICell titleSum = row.CreateCell(colIndex);            titleSum.SetCellValue(value);            if (cellStyle != null) titleSum.CellStyle = cellStyle;            else if (_centerStyle != null) titleSum.CellStyle = _centerStyle;            if (mergedCellRangeAddress != null) _sheet.AddMergedRegion(mergedCellRangeAddress);        }
        public void FillCell(IRow row, int colIndex, double value, ICellStyle cellStyle = null, 
                             NPOI.SS.Util.CellRangeAddress mergedCellRangeAddress = null)        {            if (_sheet == null || row == null) return;            ICell titleSum = row.CreateCell(colIndex);            titleSum.SetCellValue(value);            if (cellStyle != null) titleSum.CellStyle = cellStyle;            else if (_centerStyle != null) titleSum.CellStyle = _centerStyle;            if (mergedCellRangeAddress != null) _sheet.AddMergedRegion(mergedCellRangeAddress);        }
        public void ResponSEOutPutExcelStream(string fildName)        {            if (string.IsNullOrWhiteSpace(fildName)) fildName = DateTime.Now.ToString("yyyyMMddHHmmss.xls");            if (fildName.ToLower().IndexOf(".xls") == -1) fildName += ".xls";            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";            HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fildName));            HttpContext.Current.Response.Clear();            MemoryStream file = new MemoryStream();            _workbook.Write(file);            file.WriteTo(HttpContext.Current.Response.OutputStream);            HttpContext.Current.Response.End();        }
        public void SetPrint(bool isLandscape = false, bool isFitToPage = false, double topMargin = 0, double rightMargin = 0, 
                             double bottomMargin = 0.5, double leftMargin = 0, short scale = 100)        {            _sheet.PrintSetup.Landscape = isLandscape;            _sheet.SetMargin(MarginType.TopMargin, topMargin);            _sheet.SetMargin(MarginType.RightMargin, rightMargin);            _sheet.SetMargin(MarginType.LeftMargin, leftMargin);            _sheet.SetMargin(MarginType.BottomMargin, bottomMargin);            _sheet.PrintSetup.PaperSize = 9;            _sheet.PrintSetup.Scale = scale;            _sheet.FitToPage = isFitToPage;            if (isFitToPage)            {                _sheet.PrintSetup.FitWidth = 1;                _sheet.PrintSetup.FitHeight = 0;            }        }    }

這個(gè)由于父類是繼承的ActionResult，我們用起來就比較方便，在Action中，直接使用就可以了。代碼示例如下...

    public class BaseMaterialsExcelResult : NPOIBase    {        string[] __headers = null;        IList<BaseMaterials> __BaseMaterialsList;        public BaseMaterialsExcelResult(IList<BaseMaterials> BaseMaterialsList)        {            __BaseMaterialsList = BaseMaterialsList;            __headers = new string[] {                "序號(hào)",                "材料",                "型號(hào)",                "推薦供應(yīng)商",                "出庫(kù)數(shù)量",                "入庫(kù)數(shù)量",                "結(jié)存"               };        }
        public override void ExecuteResult(ControllerContext context)        {            if (__BaseMaterialsList == null || __BaseMaterialsList.Count() == 0) return;            IniNPOI();            int rowIndex = 0;            foreach (var item in __BaseMaterialsList)            {                #region 新建表，填充列頭，樣式                int colIndex = 0;                if (rowIndex == 65535 || rowIndex == 0)                {                    if (rowIndex != 0)                        _sheet = _workbook.CreateSheet();                    IRow headerRow = _sheet.CreateRow(rowIndex);                    foreach (var head in __headers)                        FillHeadCell(headerRow, colIndex++, head);                    rowIndex = 1;                }                #endregion                #region 填充內(nèi)容                IRow dataRow = _sheet.CreateRow(rowIndex);                colIndex = 0;                FillCell(dataRow, colIndex++, rowIndex);                FillCell(dataRow, colIndex++, item.Name);                FillCell(dataRow, colIndex++, item.Type);                FillCell(dataRow, colIndex++, item.ProviderName);                FillCell(dataRow, colIndex++, item.OutStorageCount.ToString());                FillCell(dataRow, colIndex++, item.StorageCount.ToString());                FillCell(dataRow, colIndex++, item.StockCount.ToString());                #endregion                rowIndex++;            }            _sheet.CreateFreezePane(1, 1, 1, 1);            ResponseOutPutExcelStream("BaseMaterials.xls");        }    }

控制器中的代碼如下:

    public class HomeController : Controller    {        BluedonStockEntities  db = new BluedonStockEntities();        public List<BaseMaterials> GetList()        {            return db.CreateObjectSet<BaseMaterials>().Where(c => true).ToList();        }        public ActionResult Index()        {            var list = GetList();            return View(list);        }        public ActionResult Execl()        {            var list = GetList();             return new BaseMaterialsExcelResult(list);        }    }

4.問題總結(jié)。

在實(shí)例化了一個(gè)WorkBook之后，最好添加一個(gè)sheet，雖然在最新版的Npoi.net中自動(dòng)添加了，但是遇到遷移到原來版本就會(huì)出現(xiàn)問題。所以我建議還是最少添加一個(gè)sheet
在從單元格取值時(shí)要注意單元格的類型，一定要用對(duì)應(yīng)的類型的方法來取單元格中的對(duì)應(yīng)類型的值，如果不確定，那只能是強(qiáng)制轉(zhuǎn)換成為string類型，畢竟string類型是excel中其他類型都可以轉(zhuǎn)換過來的
在獲取sheet中的某一行或者某一行的某一個(gè)單元格的時(shí)候，還要注意就是一定要確保創(chuàng)建了該行，并且取單元格還要確保創(chuàng)建了單元格，否則會(huì)報(bào)Null reference not to object 這個(gè)我們經(jīng)常會(huì)看到的異常信息。在外層一定要加上try捕獲異常
合并單元格是sheet的工作，因此需要獲取對(duì)應(yīng)的sheet，然后調(diào)用其AddMergedRegion方法合并單元格，在合并單元格的時(shí)候，我們不需要確保該單元格已經(jīng)存在或創(chuàng)建。
在為單元格設(shè)置樣式的過程中，我們會(huì)發(fā)現(xiàn)所有和樣式相關(guān)的類的創(chuàng)建都是通過workBook.Create(Font)..這種方式來執(zhí)行的，我們不可以直接new一個(gè)類的實(shí)例。
如果前面的工作都已經(jīng)做好，需要把內(nèi)存中的excel表寫到硬盤上時(shí)，需要調(diào)用workBook.write()方法，傳入一個(gè)文件流進(jìn)行創(chuàng)建。在這里有可能會(huì)出現(xiàn)一個(gè)問題，就是你要?jiǎng)?chuàng)建的文件你已經(jīng)打開了，這時(shí)程序就會(huì)出現(xiàn)異常，因此我們?cè)谡{(diào)試程序的時(shí)候一定要記得打開了excel文件以后要關(guān)閉
最后需要注意的就是文件流，在我們把excel寫到硬盤上以后，要顯式的調(diào)用其close（）方法關(guān)閉文件流。因?yàn)槿绻魂P(guān)閉文件流的話，以后就會(huì)出現(xiàn)無法重新創(chuàng)建該文件的錯(cuò)誤，并且會(huì)提示某文件正由另一進(jìn)程使用，因此該進(jìn)程無法訪問此文件。

簡(jiǎn)單用法的源碼【點(diǎn)擊下載】

MVC版的源碼【點(diǎn)擊下載】

感謝你的支持，為我點(diǎn)個(gè)贊吧！

上一篇：面試習(xí)題之設(shè)計(jì)模式C#觀察者模式(貓叫老鼠驚走主人醒)

下一篇：一次性搞定Session