How to operate Excel with NPOI in C #

Most people do not understand the knowledge points of this article "how to operate Excel with NPOI in C#", so the editor summarizes the following content, detailed content, clear steps, and has a certain reference value. I hope you can get something after reading this article. Let's take a look at this "how to use NPOI in C# to operate Excel" article.

one。 Merge cells

NOPI supports cell merging and cell formatting!

Note:

Cells must be created first when merging cells

1. Merge cell statements:

Sheet.AddMergedRegion (new CellRangeAddress (0,0,0,3)); / / start row, end row, start column, end column

two。 Format the cell:

ICellStyle cellStyle = wk.CreateCellStyle (); / / first create the cell format / / set the upper and lower left and right borders of the cell cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Hair; / / dashed cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thick;// thick line cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Double;// double line cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin / / thin line / / text horizontal and vertical alignment cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Top; / / whether to wrap / / cellStyle.WrapText = true; / / if the string is too large, fill in the cell / / reduce the font to fill in cellStyle.ShrinkToFit = true / / if the string is too large or small, fill in the cell / / create a new font style object IFont font = wk.CreateFont (); / / set the font bold style font.Boldweight = short.MaxValue;ICell MyCell = sheet.CreateRow (1) .CreateCell (1); / / create the cell MyCell.CellStyle = cellStyle;// to assign to the cell "the cell format you just created"

Source code:

Using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.IO;using NPOI;using NPOI.XSSF.UserModel;using NPOI.SS.UserModel;using NPOI.HSSF.UserModel;using NPOI.HSSF.Util;using NPOI.SS.Util;namespace Excel5 {class Program {static void Main (string [] args) {XSSFWorkbook wk = new XSSFWorkbook (); / * ISheet sheet = wk.CreateSheet ("example") ICellStyle cellStyle = wk.CreateCellStyle (); / / set the upper and lower left and right borders of the cell cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Hair; / / dashed cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thick;// thick line cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Double;// double line cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin / / thin line / / text horizontal and vertical alignment cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Top; / / whether to wrap / / cellStyle.WrapText = true; / / if the string is too large, fill in the cell / / reduce the font to fill in cellStyle.ShrinkToFit = true / / if the string is too large or small, fill in the cell / / create a new font style object IFont font = wk.CreateFont (); / / set the font bold style font.Boldweight = short.MaxValue;ICell MyCell = sheet.CreateRow (1) .CreateCell (1); ICell MyCell2 = sheet.CreateRow (0) .CreateCell (1); MyCell.CellStyle = cellStyle;//MyCell.SetCellValue ("Test format effect") Using (FileStream fileStream = File.Open ("d:\\ pratice3.xlsx", FileMode.OpenOrCreate, FileAccess.ReadWrite)) {wk.Write (fileStream); fileStream.Close ();} * / create a Sheet ISheet sheet = wk.CreateSheet ("example") / / create a row IRow row = sheet.CreateRow (0) in the first row; / / create a cell for in the first column of the first row (ICell cell I = 0; I < 10; iSum +) {ICell cell = row.CreateCell (I) If ((I) 4room0) row.CreateCell (I) .SetCellValue ("Test");} sheet.AddMergedRegion (new CellRangeAddress (0,0,0,3)) / / start line, end row, start column, stop column / / row.CreateCell (0). SetCellValue ("merge cells"); using (FileStream fs = File.OpenWrite ("d:\\ pratice1.xlsx")) {wk.Write (fs); / / write and save to the open xls file. } / / the file generated in the previous tutorial string Address = "d:\\ pratice1.xlsx"; / / indicates the path XSSFWorkbook wk2 = null Using (FileStream fs = File.Open (Address, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) {/ / read the xlsx file into the workbook variable, then you can close wk2 = new XSSFWorkbook (fs); fs.Close () } using (FileStream fileStream = File.Open ("d:\\ pratice1.xlsx", FileMode.OpenOrCreate, FileAccess.ReadWrite)) {wk2.Write (fileStream); fileStream.Close ();} Console.WriteLine ("OK"); Console.ReadKey ();}

This is two parts of the code, separated by comments, and then make your own adjustments when testing!

Note:

When cells are merged, the value of the upper-left corner is saved by default!

The above is about the content of this article on "how to operate Excel with NPOI in C#". I believe we all have some understanding. I hope the content shared by the editor will be helpful to you. If you want to know more about the relevant knowledge, please pay attention to the industry information channel.