How to Use Npoi to Operate excel

Npoi Introduction
--------------------------------------------------------------------------------

1. The entire Excel table is called a worksheet: WorkBook (working thin), including a page (worksheet): Sheet; Row: Row; Cell.

2. Npoi: http://npoi.codeplex.com/releases/view/38113

4. I forgot to tell you What npoi is doing. npoi can read and write almost all Office 97-2003 file formats. At least it can support Word, PowerPoint, Excel, and Visio formats.

--------------------------------------------------------------------------------

Use Npoi to create a simple xls file

--------------------------------------------------------------------------------

Copy codeThe Code is as follows: // create an xls file
Private void button#click (object sender, EventArgs e)
{
// Create a workbook
HSSFWorkbook wk = new HSSFWorkbook ();
// Create a table named mySheet
ISheet tb = wk. CreateSheet ("mySheet ");
// Create a row. The second row is used.
IRow row = tb. CreateRow (1 );
For (int I = 0; I <20; I ++)
{
ICell cell = row. CreateCell (I); // create a cell in the second row
Cell. SetCellValue (I); // cyclically add data to the cell in the second row
}
Using (FileStream fs = File. OpenWrite (@ "c:/myxls.xls") // open an xlsfile. If no File exists, create it on your own. If the myxls.xls File exists, do not open it during creation!
{
Wk. Write (fs); // Write the mySheet table to the opened xls file and save it.
MessageBox. Show ("prompt: Creation successful! ");
}
}

Use Npoi to read a simple xls file

--------------------------------------------------------------------------------Copy codeThe Code is as follows: // read the xls file
Private void button2_Click (object sender, EventArgs e)
{StringBuilder sbr = new StringBuilder ();
Using (FileStream fs = File. OpenRead (@ "c:/myxls.xls") // open the myxls.xls File
{
HSSFWorkbook wk = new HSSFWorkbook (fs); // write data in the xls file to wk
For (int I = 0; I <wk. NumberOfSheets; I ++) // numberofsheetsis the total number of tables in myxls.xls.
{
ISheet sheet = wk. GetSheetAt (I); // read data from the current table
For (int j = 0; j <= sheet. LastRowNum; j ++) // LastRowNum indicates the total number of rows in the current table.
{
IRow row = sheet. GetRow (j); // read data from the current row
If (row! = Null)
{
Sbr. Append ("------------------------------------- \ r \ n"); // the boundary between reading rows and rows
For (int k = 0; k <= row. LastCellNum; k ++) // LastCellNum indicates the total number of columns in the current row.
{
ICell cell = row. GetCell (k); // current table
If (cell! = Null)
{
Sbr. Append (cell. ToString (); // obtain the data in the table and convert it to the string type.
}
}
}
}
}
}
Sbr. ToString ();
Using (StreamWriter wr = new StreamWriter (new FileStream (@ "c:/myText.txt", FileMode. Append) // write the data read from the xlsfile to the myText.txt File
{
Wr. Write (sbr. ToString ());
Wr. Flush ();
}

}

--------------------------------------------------------------------------------

Use Npoi to create a common xls file

--------------------------------------------------------------------------------

Copy codeThe Code is as follows: // create a common xls file
Private void button3_Click (object sender, EventArgs e)
{
IWorkbook wb = new HSSFWorkbook ();
// Create a table
ISheet sh = wb. CreateSheet ("zhiyuan ");
// Set the unit width
Sh. SetColumnWidth (0, 15*256 );
Sh. SetColumnWidth (1, 35*256 );
Sh. SetColumnWidth (2, 15*256 );
Sh. SetColumnWidth (3, 10*256 );
Int I = 0;
# Region exercise merge Cells
Sh. AddMergedRegion (new NPOI. SS. Util. CellRangeAddress (0, 0, 0, 3 ));

// CellRangeAddress () the Parameter order of this method is start row number, end row number, start column number, and end column number.

IRow row0 = sh. CreateRow (0 );
Row0.Height = 20*20;
ICell icell1top0 = row0.CreateCell (0 );
Icell1top0. CellStyle = Getcellstyle (wb, stylexls. header );
Icell1top0. SetCellValue ("title merging unit ");
# Endregion
I ++;
# Region set the header
IRow row1 = sh. CreateRow (1 );
Row1.Height = 20*20;

ICell icell1top = row1.CreateCell (0 );
Icell1top. CellStyle = Getcellstyle (wb, stylexls. header );
Icell1top. SetCellValue ("website name ");

ICell icell2top = row1.CreateCell (1 );
Icell2top. CellStyle = Getcellstyle (wb, stylexls. header );
Icell2top. SetCellValue ("url ");

ICell icell3top = row1.CreateCell (2 );
Icell3top. CellStyle = Getcellstyle (wb, stylexls. header );
Icell3top. SetCellValue ("Baidu snapshot ");

ICell icell4top = row1.CreateCell (3 );
Icell4top. CellStyle = Getcellstyle (wb, stylexls. header );
Icell4top. SetCellValue ("Baidu ");
# Endregion

Using (FileStream stm = File. OpenWrite (@ "c:/myMergeCell.xls "))
{
Wb. Write (stm );
MessageBox. Show ("prompt: Creation successful! ");
}
}

# Region defines the enumeration of cells from common to styles
Public enum stylexls
{
Header,
Url,
Time,
Number,
Money,
Percentage,
Uppercase Chinese,
Scientific notation,
Default
}
# Endregion

# Region defines commonly used cells to styles
Static ICellStyle Getcellstyle (IWorkbook wb, stylexls str)
{
ICellStyle cellStyle = wb. CreateCellStyle ();

// Define several Fonts
// You can also use a font to write some public attributes, and then add special
IFont font12 = wb. CreateFont ();
Font12.FontHeightInPoints = 10;
Font12.FontName = "";

IFont font = wb. CreateFont ();
Font. FontName = "";
// Font. Underline = 1; Underline

IFont fontcolorblue = wb. CreateFont ();
Fontcolorblue. Color = HSSFColor. OLIVE_GREEN.BLUE.index;
Fontcolorblue. IsItalic = true; // underline
Fontcolorblue. FontName = "";

// Border
CellStyle. BorderBottom = NPOI. SS. UserModel. BorderStyle. DOTTED;
CellStyle. BorderLeft = NPOI. SS. UserModel. BorderStyle. HAIR;
CellStyle. BorderRight = NPOI. SS. UserModel. BorderStyle. HAIR;
CellStyle. BorderTop = NPOI. SS. UserModel. BorderStyle. DOTTED;
// Border color
CellStyle. BottomBorderColor = HSSFColor. OLIVE_GREEN.BLUE.index;
CellStyle. TopBorderColor = HSSFColor. OLIVE_GREEN.BLUE.index;

// Background image, which I have never used. It feels ugly.
// CellStyle. FillBackgroundColor = HSSFColor. OLIVE_GREEN.BLUE.index;
// CellStyle. FillForegroundColor = HSSFColor. OLIVE_GREEN.BLUE.index;
CellStyle. FillForegroundColor = HSSFColor. WHITE. index;
// CellStyle. FillPattern = FillPatternType. NO_FILL;
CellStyle. FillBackgroundColor = HSSFColor. BLUE. index;

// Horizontal Alignment
CellStyle. Alignment = NPOI. SS. UserModel. HorizontalAlignment. LEFT;

// Vertical Alignment
CellStyle. VerticalAlignment = verticalignment. CENTER;

// Automatic line feed
CellStyle. WrapText = true;

// Indent; when set to 1, the front blank is too large. Improvements on the xiwang official website. Or the setting is incorrect.
CellStyle. Indention = 0;

// The above is basically a common public setting.
// Common field types are listed below
Switch (str)
{
Case stylexls. header:
// CellStyle. FillPattern = FillPatternType. LEAST_DOTS;
CellStyle. SetFont (font12 );
Break;
Case stylexls. Time:
IDataFormat datastyle = wb. CreateDataFormat ();

CellStyle. DataFormat = datastyle. GetFormat ("yyyy/mm/dd ");
CellStyle. SetFont (font );
Break;
Case stylexls. Number:
CellStyle. DataFormat = HSSFDataFormat. GetBuiltinFormat ("0.00 ");
CellStyle. SetFont (font );
Break;
Case stylexls. Money:
IDataFormat format = wb. CreateDataFormat ();
CellStyle. DataFormat = format. GetFormat ("￥ #,## 0 ");
CellStyle. SetFont (font );
Break;
Case stylexls. url:
Fontcolorblue. Underline = 1;
CellStyle. SetFont (fontcolorblue );
Break;
Case stylexls. Percentage:
CellStyle. DataFormat = HSSFDataFormat. GetBuiltinFormat ("0.00% ");
CellStyle. SetFont (font );
Break;
Case stylexls. Chinese uppercase:
IDataFormat format1 = wb. CreateDataFormat ();
CellStyle. DataFormat = format1.GetFormat ("[DbNum2] [$-804] 0 ");
CellStyle. SetFont (font );
Break;
Case stylexls. Scientific Notation:
CellStyle. DataFormat = HSSFDataFormat. GetBuiltinFormat ("0.00E + 00 ");
CellStyle. SetFont (font );
Break;
Case stylexls. Default:
CellStyle. SetFont (font );
Break;
}
Return cellStyle;

}
# Endregion

--------------------------------------------------------------------------------
Tip: 1. The above npoi version is 1.2.5. The current version is the highest version, which is somewhat different from the previous version.

2. To use the above Code, you need to add two npoi dll files. Ionic. Zip. dll, NPOI. dll