Three ways to export Excel through NPOI in Winform (HSSFWorkbook, XSSFWorkbook, SXSSFWorkbook) with code download

Scenes

Difference between HSSFworkbook,XSSFworkbook,SXSSFworkbook

HSSFWorkbook:

It is the version before operating Excel 2003 (including 2003), and the extension is .xls; the most common way to export excel; but the limitation of this method is that the number of exported lines is at most 65535 lines, and the system will report an error when more than 65536 lines are exceeded.

XSSFWorkbook:

It is the version after operating Excel2007, the extension is .xlsx; in order to break through the 65535 line limit of HSSFWorkbook. It corresponds to excel2007(1048576 rows, 16384 columns) with the extension ".xlsx", which can export up to 1.04 million rows, but this is accompanied by a problem---OOM memory overflow, the reason is the book sheet row you created At this time, cell s, etc. exist in memory and are not persistent.

SXSSFWorkbook:

It is also the version after operating Excel2007, the extension is .xlsx; SXSSFWorkbook is the streaming version of XSSFWorkbook, it only saves the latest excel rows in memory for viewing, and the previous excel rows will be written to the hard disk (Windows computer). If it is, it is written to the temp folder in the root directory of the C drive). rows that are written to disk are invisible/inaccessible. Only those that are still in memory can be accessed.

Therefore, some temporary files will be generated in the default C drive directory. The default path is:

C:\Users\HAOHAO\AppData\Local\Temp\poifiles

If you forget the location to clean up, you can use junk cleaning software

Note:

Blog homepage:
https://blog.csdn.net/badao_liumang_qizhi
Pay attention to the public account
domineering programmer
Get programming-related eBooks, tutorial pushes, and free downloads.

accomplish

The above three methods are the same as when creating a new sheet, Row and Cell when exporting Eecel, except that when the wookbook object is created at the beginning and the file extension is different.

Before exporting Excel, the dll of npoi must be referenced first. The dll that NPOI needs to refer to is as follows:

You can see that in addition to the reference at the beginning of NPOI, there are two other references. Why are there two references? We can download the source code through its github

https://github.com/svn2github/npoi

Then you can see that these two dlls are referenced in its source code, so these two dlls also need to be referenced here

dll download link:

https://download.csdn.net/download/BADAO_LIUMANG_QIZHI/12473431

HSSFWorkbook Export to Excel

Create a new form page and then enter its code. For example, to construct the exported data, first create an object class DataItem

public class DataItem
    {

        public int Age { get; set; }


        public string Name { get; set; }


        public string Address { get; set; }

        public int Sex { get; set; }

    }

Then enter the code of this form, first build the exported data

//data
        List<DataItem> ItemList = new List<DataItem>() 
        { 
            new DataItem() {Name = "overbearing",Age = 24,Address = "China",Sex = 1},
            new DataItem() {Name = "rogue",Age = 25,Address = "Beijing",Sex = 0},
            new DataItem() {Name = "temperament",Age = 26,Address = "Shanghai",Sex = 0},
            new DataItem() {Name = "programmer",Age = 27,Address = "Qingdao",Sex = 1},
        };

Drag a button, then in its click event

private void button2_Click(object sender, EventArgs e)
        {
            //Create HSSFWorkbook object
            HSSFWorkbook wb = new HSSFWorkbook();
            //Create a sheet and specify the name of the sheet
            ISheet sheet1 = wb.CreateSheet("detailed data");
            //Create a new style object and set style properties
            ICellStyle style1 = wb.CreateCellStyle();//style
            IFont font1 = wb.CreateFont();//font
            font1.FontName = "Song Dynasty";
            font1.FontHeightInPoints = 11;
            font1.Boldweight = (short)FontBoldWeight.Bold;
            style1.SetFont(font1);//The font in the style sets the specific font style

            //Create the first row
            IRow row0 = sheet1.CreateRow(0);
            //Create the first row and first column and set the value
            row0.CreateCell(0).SetCellValue("Name");
            //Get the first row and first column and set the style
            row0.GetCell(0).CellStyle = style1;
            row0.CreateCell(1).SetCellValue("age");
            row0.GetCell(1).CellStyle = style1;
            row0.CreateCell(2).SetCellValue("address");
            row0.GetCell(2).CellStyle = style1;
            row0.CreateCell(3).SetCellValue("gender");
            row0.GetCell(3).CellStyle = style1;

            //loop adding data
            foreach (DataItem item in ItemList)
            {
                int item_index = ItemList.IndexOf(item);
                //from the second line
                IRow rowi = sheet1.CreateRow(item_index+1);
                rowi.CreateCell(0).SetCellValue(item.Name);
                rowi.CreateCell(1).SetCellValue(item.Age);
                rowi.CreateCell(2).SetCellValue(item.Address);
                rowi.CreateCell(3).SetCellValue(item.Sex);
                
                //Set the column width, 256*number of characters, because the unit is 1/256 characters
                sheet1.SetColumnWidth(item_index, 256 * item.Address.Length *4);
            }
            try
            {
                //Write data in memory to disk
                using (FileStream filestream = new FileStream(System.IO.Path.Combine(@"D:\", "badao.xls"), FileMode.Create))
                {
                    wb.Write(filestream);
                    filestream.Close();
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex);
            }
            MessageBox.Show("export complete");
            
        }

Run the project and click the button, and then the excel of xls will be generated under the D drive

XSSFWorkbook export Excel

Then drag a button and set the button's click event

private void button3_Click(object sender, EventArgs e)
        {
           
            //Create XSSFWorkbook object
            XSSFWorkbook wb = new XSSFWorkbook();
            //Create a sheet and specify the name of the sheet g
            ISheet sheet1 = wb.CreateSheet("detailed data");
            //Create a new style object and set style properties
            ICellStyle style1 = wb.CreateCellStyle();//style
            IFont font1 = wb.CreateFont();//font
            font1.FontName = "Song Dynasty";
            font1.FontHeightInPoints = 11;
            font1.Boldweight = (short)FontBoldWeight.Bold;
            style1.SetFont(font1);//The font in the style sets the specific font style

            //Create the first row
            IRow row0 = sheet1.CreateRow(0);
            //Create the first row and first column and set the value
            row0.CreateCell(0).SetCellValue("Name");
            //Get the first row and first column and set the style
            row0.GetCell(0).CellStyle = style1;
            row0.CreateCell(1).SetCellValue("age");
            row0.GetCell(1).CellStyle = style1;
            row0.CreateCell(2).SetCellValue("address");
            row0.GetCell(2).CellStyle = style1;
            row0.CreateCell(3).SetCellValue("gender");
            row0.GetCell(3).CellStyle = style1;

            //loop adding data
            foreach (DataItem item in ItemList)
            {
                int item_index = ItemList.IndexOf(item);
                //from the second line
                IRow rowi = sheet1.CreateRow(item_index + 1);
                rowi.CreateCell(0).SetCellValue(item.Name);
                rowi.CreateCell(1).SetCellValue(item.Age);
                rowi.CreateCell(2).SetCellValue(item.Address);
                rowi.CreateCell(3).SetCellValue(item.Sex);

                //Set the column width, 256*number of characters, because the unit is 1/256 characters
                sheet1.SetColumnWidth(item_index, 256 * item.Address.Length * 4);
            }
            try
            {
                //Write data in memory to disk
                using (FileStream filestream = new FileStream(System.IO.Path.Combine(@"D:\", "liumang.xlsx"), FileMode.Create))
                {
                    wb.Write(filestream);
                    filestream.Close();
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex);
            }
            MessageBox.Show("export complete");
        }

Then run the project and click the button

SXSSFWorkbook export Excel

In the same way, drag and drop a button. In the click event of the button, the difference is that a large amount of data needs to be constructed in advance, and then wait for a period of time when exporting

private void button4_Click(object sender, EventArgs e)
        {
            
            //Create SXSSFWorkbook object
            SXSSFWorkbook wb = new SXSSFWorkbook();
            //Create a sheet and specify the name of the sheet
            ISheet sheet1 = wb.CreateSheet("detailed data");
            //Create a new style object and set style properties
            ICellStyle style1 = wb.CreateCellStyle();//style
            IFont font1 = wb.CreateFont();//font
            font1.FontName = "Song Dynasty";
            font1.FontHeightInPoints = 11;
            font1.Boldweight = (short)FontBoldWeight.Bold;
            style1.SetFont(font1);//The font in the style sets the specific font style

            //Create the first row
            IRow row0 = sheet1.CreateRow(0);
            //Create the first row and first column and set the value
            row0.CreateCell(0).SetCellValue("Name");
            //Get the first row and first column and set the style
            row0.GetCell(0).CellStyle = style1;
            row0.CreateCell(1).SetCellValue("age");
            row0.GetCell(1).CellStyle = style1;
            row0.CreateCell(2).SetCellValue("address");
            row0.GetCell(2).CellStyle = style1;
            row0.CreateCell(3).SetCellValue("gender");
            row0.GetCell(3).CellStyle = style1;

            //Build large amounts of data
            List<DataItem> bigData = new List<DataItem>();
            for (int i = 0; i < 50000;i++ )
            {
                DataItem data = new DataItem();
                data.Name = "overbearing" + i;
                data.Age = i;
                data.Address = "Qingdao" + i;
                data.Sex = i;
                bigData.Add(data);

            }
            //loop adding data
            foreach (DataItem item in bigData)
            {
                int item_index = bigData.IndexOf(item);
                //from the second line
                IRow rowi = sheet1.CreateRow(item_index + 1);
                rowi.CreateCell(0).SetCellValue(item.Name);
                rowi.CreateCell(1).SetCellValue(item.Age);
                rowi.CreateCell(2).SetCellValue(item.Address);
                rowi.CreateCell(3).SetCellValue(item.Sex);

                //Set the column width, 256*number of characters, because the unit is 1/256 characters
                sheet1.SetColumnWidth(item_index, 256 * item.Address.Length * 4);
            }
            try
            {
                //Write data in memory to disk
                using (FileStream filestream = new FileStream(System.IO.Path.Combine(@"D:\", "qizhi.xlsx"), FileMode.Create))
                {
                    wb.Write(filestream);
                    filestream.Close();
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex);
            }
            MessageBox.Show("export complete");
        }