MVC uses NPOI to import and export Excel sample code

What is NPOI

The project is located/.NET version of POI Java project. POI is an open source project that helps you read/write xls, doc, ppt files. It has a wide range of applications. This article introduces to you the problem of MVC importing and exporting Excel using NPOI.

I'll record it because of recent projects:

First export Excel:

First reference the NPOI package

(Action must be done with FileResult)

/// &lt;summary&gt;
        /// Batch export of the first batch of assigned students in our school        /// &lt;/summary&gt;
        /// &lt;returns&gt;&lt;/returns&gt;
        public FileResult ExportStu2()
        {
            string schoolname = "401";
            //Create an Excel file object             book = new ();
            //Add a sheet             sheet1 = ("Sheet1");
            //Get list data            List&lt;TB_STUDENTINFOModel&gt; listRainInfo = m_BLL.GetSchoolListAATQ(schoolname);
            //Add the header of the first line to sheet1             row1 = (0);
            (0).SetCellValue("Computer Number");
            (1).SetCellValue("Name");
            //Steply write data to each row of sheet1            for (int i = 0; i &lt; ; i++)
            {
                 rowtemp = (i + 1);
                (0).SetCellValue(listRainInfo[i].ST_CODE.ToString());
                (1).SetCellValue(listRainInfo[i].ST_NAME.ToString());
            }
            // Write to the client             ms = new ();
            (ms);
            (0, );
            return File(ms, "application/-excel", "The first batch of computer allotment rosters.xls");
        }

You can do it by writing directly in the front desk:

@("Click to export the roster", "ExportStu2")

Let's talk about importing below:

First of all, let’s talk about the front desk. Please note that you must add new { enctype = "multipart/form-data" } when uploading mvc:

&lt;td&gt;
2、@using(@("ImportStu", "ProSchool", , new { enctype = "multipart/form-data" }))
{
 &lt;text&gt;Select Upload File：（The worksheet name is“Sheet1”，“Computer number”existA1Cell。）&lt;/text&gt;
&lt;input name="file" type="file"  /&gt;
&lt;input type="submit" name="Upload" value="Batch import of the first batch of computer allocation list" /&gt;
 }
 &lt;/td&gt;

Background implementation: Pass only the path to get DataTable:

/// &lt;summary&gt;
        /// Excel import        /// &lt;/summary&gt;
        /// &lt;param name="filePath"&gt;&lt;/param&gt;
        /// &lt;returns&gt;&lt;/returns&gt;
        public DataTable ImportExcelFile(string filePath)
        {
            HSSFWorkbook hssfworkbook;
            #region//Initialization information            try
            {
                using (FileStream file = new FileStream(filePath, , ))
                {
                    hssfworkbook = new HSSFWorkbook(file);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            #endregion
            using ( sheet = (0))
            {
                DataTable table = new DataTable();
                IRow headerRow = (0);//The first line of the title line                int cellCount = ;//LastCellNum = PhysicalNumberOfCells
                int rowCount = ;//LastRowNum = PhysicalNumberOfRows - 1
                //handling header.
                for (int i = ; i &lt; cellCount; i++)
                {
                    DataColumn column = new DataColumn((i).StringCellValue);
                    (column);
                }
                for (int i = ( + 1); i &lt;= rowCount; i++)
                {
                    IRow row = (i);
                    DataRow dataRow = ();
                    if (row != null)
                    {
                        for (int j = ; j &lt; cellCount; j++)
                        {
                            if ((j) != null)
                                dataRow[j] = GetCellValue((j));
                        }
                    }
                    (dataRow);
                }
                return table;
            }
            
        }

Add a class

/// &lt;summary&gt;
        /// Get the column value according to Excel column type        /// &lt;/summary&gt;
        /// <param name="cell">Excel column</param>        /// &lt;returns&gt;&lt;/returns&gt;
        private static string GetCellValue(ICell cell)
        {
            if (cell == null)
                return ;
            switch ()
            {
                case :
                    return ;
                case :
                    return ();
                case :
                    return ();
                case :
                case :
                default:
                    return ();//This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number
                case :
                    return ;
                case :
                    try
                    {
                        HSSFFormulaEvaluator e = new HSSFFormulaEvaluator();
                        (cell);
                        return ();
                    }
                    catch
                    {
                        return ();
                    }
            }
        }

After getting the DataTable, you can do whatever you want.

This is the article about MVC import and export Excel using NPOI. This is all about this article. For more related MVC import and export Excel content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!