Using Npoi to manipulate Excel instance code in C # development

Using Npoi to manipulate Excel instance code in C # development source: CC finish Date: 2012/11/16 9:35:50 [Big Small] Comments: 0 | I want to express my opinion

What is Npoi?

1. The entire Excel table is called a worksheet: WorkBook (Workbook), containing the called page (sheet): Sheet; row: row; cell.

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

3.Npoi Learning Series Recommended: http://www.cnblogs.com/tonyqus/archive/2009/04/12/1434209.html

4. Forget to tell everyone what Npoi is doing, Npoi can read and write almost all of the Office 97-2003 file formats, at least to support the format of Word, PowerPoint, Excel, Visio.

use Npoi to create a simple XLS file

Create an XLS file
private void Button1_Click (object sender, EventArgs e)
{
Create a workbook
Hssfworkbook wk = new Hssfworkbook ();
Create a table named MySheet
Isheet TB = wk. Createsheet ("MySheet");
Creates a row, the second line of this behavior
IRow row = tb. CreateRow (1);
for (int i = 0; i <; i++)
{
Icell cell = row.  Createcell (i); Create a cell in the second row
Cell. Setcellvalue (i);//loop to add data to cells in the second row
}
using (FileStream fs = File.openwrite (@ "C:/myxls.xls"))//Open an XLS file, if not, create it yourself if there is a Myxls.xls file in the Create is do not open the file!
{
Wk.   Write (FS); Write the MySheet table to the open XLS file and save it.
MessageBox.Show ("Hint: Create success! ");
}
}

Use Npoi to read a simple XLS file

 Read xls file private void button2_click (object sender, EventArgs e) {StringBuilder sbr = new StringBuilder (            );  using (FileStream fs = File.openread (@ "C:/myxls.xls"))//Open Myxls.xls file {hssfworkbook wk = new   Hssfworkbook (FS); Writes the data in the XLS file to wk for (int i = 0; i < wk. Numberofsheets; i++)//numberofsheets is the total number of tables in Myxls.xls {isheet sheet = wk.   Getsheetat (i); Reads the current table data for (int j = 0; J <= sheet. Lastrownum; J + +)//lastrownum is the total number of rows in the current table {IRow row = sheet.  GetRow (j); Reads the current row data if (row! = null) {SBR. Append ("-------------------------------------\ r \ n"); Reads the hint bounds between rows and rows for (int k = 0; k <= Row. Lastcellnum; k++)//lastcellnum is the total number of columns for the current row {Icell cell = row.  Getcell (k); WhenPrevious table if (cell! = null) { Sbr. Append (cell.   ToString ());                    Get the data in the table and convert it to a string type}}} }}} sbr.            ToString (); using (StreamWriter WR = new StreamWriter (new FileStream (@ "C:/mytext.txt", filemode.append))// Writes the data that reads the XLS file to the MyText.txt file {wr. Write (SBR.                ToString ()); Url            Flush (); }                    }

Use Npoi to create a common XLS file

 Create a common xls file private void Button3_Click (object sender, EventArgs e) {Iworkbook WB =            New Hssfworkbook (); CREATE TABLE Isheet SH = wb.            Createsheet ("Zhiyuan"); Sets the width of the cell sh.            Setcolumnwidth (0, 15 * 256); Sh.            Setcolumnwidth (1, 35 * 256); Sh.            Setcolumnwidth (2, 15 * 256); Sh.            Setcolumnwidth (3, 10 * 256);            int i = 0; #region Practice Merging cell sh. Addmergedregion (New Npoi. Ss.              Util.cellrangeaddress (0, 0, 0, 3)); //cellrangeaddress () The parameter order of the method is: Start line number, end line number, start column number, end column number. IRow row0 = sh.            CreateRow (0); Row0.            Height = 20 * 20; Icell icell1top0 = row0.            Createcell (0); Icell1top0.            CellStyle = Getcellstyle (WB, Stylexls. Head); Icell1top0.            Setcellvalue ("title merge 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. Head); Icell1top.            Setcellvalue ("website name"); Icell icell2top = Row1.            Createcell (1); Icell2top.            CellStyle = Getcellstyle (WB, Stylexls. Head); Icell2top.            Setcellvalue ("url"); Icell icell3top = Row1.            Createcell (2); Icell3top.            CellStyle = Getcellstyle (WB, Stylexls. Head); Icell3top.            Setcellvalue ("Baidu snapshot"); Icell icell4top = Row1.            Createcell (3); Icell4top.            CellStyle = Getcellstyle (WB, Stylexls. Head); Icell4top.            Setcellvalue ("Baidu included"); #endregion using (FileStream stm=file.openwrite (@ "C:/mymergecell.xls")) {wb.                 Write (STM); MessageBox.Show ("Hint: Create success!            ");            }} #region to define the cell common to the Style enumeration public enum Stylexls {header, URL, time, Number, money, percent, Chinese capital, scientific notation, defaultrecognize} #endregion #region define cells commonly used to style static Icellstyle Getcellstyle (Iworkbook wb, Stylexls str) {Icellstyle CellStyle = wb.            Createcellstyle (); Define several fonts//can also be a font, write some public properties, and then add a special ifont font12 = WB when needed below.            CreateFont (); Font12.            Fontheightinpoints = 10; Font12.            FontName = "Microsoft Jas Black"; IFont font = WB.            CreateFont (); Font.            FontName = "Microsoft Jas Black"; Font. underline = 1; underline IFont Fontcolorblue = wb.            CreateFont (); Fontcolorblue. Color = Hssfcolor.olive_green.            Blue.index; Fontcolorblue. Isitalic = true;//underline fontcolorblue.            FontName = "Microsoft Jas Black"; 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 graphics, I have not 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 = verticalalignment.center;            Wrap Cellstyle.wraptext = true; Indent, when set to 1 o'clock, the blank space in front is too large. Shivang official website Improvement.            Or I set the wrong cellstyle.indention = 0; The above is basically set up common public settings//The following list of common field types switch (str) {CASe stylexls. Head://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%");                    Cellstyle.setfont (font);                Break Case Stylexls. Chinese capital: 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

Hint: The above use Npoi version is 1.2.5 version, the version currently belongs to the highest version, and the use of the previous version is somewhat different.

Using Npoi to manipulate Excel instance code in C # development