Npoi, table String to Excel

Because there are 4,000 style restrictions. So add a class, Save the style ... The repeating style is new, and only the new style is a style. This basically does not reach the 4,000 limit.

Using system;using system.collections.generic;using system.linq;using system.text;using System.Threading.Tasks; Using Npoi. HSSF. Usermodel;using Npoi. Ss.            Usermodel;namespace testnpoi{public class CellStyle {public CellStyle (Hssfworkbook workbook) {            Wk = Workbook;            _cstyle = Wk.createcellstyle ();        _font = Wk.createfont ();        public static Hssfworkbook Wk {get; set;}        Public short Fillforegroundcolor {get; set;}        Public short FontColor {get; set;}        Left align by default.        Public HorizontalAlignment Alignment {get; set;}        public string Type {get; set;}        public string Isfontbold {get; set;}        public string HorizontalAlign {get; set;}        public string underscore {get; set;}        Private Icellstyle _cstyle;        Private ReadOnly IFont _font; Public Icellstyle Cstyle {get {_cstyle.borderbottom = Npoi. Ss. UsErModel.BorderStyle.THIN; _cstyle.borderright = Npoi. Ss.                UserModel.BorderStyle.THIN; _cstyle.bordertop = Npoi. Ss.                UserModel.BorderStyle.THIN; _cstyle.borderleft = Npoi. Ss.                UserModel.BorderStyle.THIN; _cstyle.fillforegroundcolor = Npoi. HSSF.                Util.HSSFColor.WHITE.index;                _cstyle.fillpattern = Fillpatterntype.solid_foreground; _cstyle.fillforegroundcolor = this.                Fillforegroundcolor; if (Type = = "header") {_font.                    Boldweight = (short) fontboldweight.bold;                _cstyle.wraptext = false; } else {_font.                Boldweight = (short) fontboldweight.none; } _font.                    FontName = "Verdana";                _cstyle.wraptext = true; _font. Color = this.                FontColor;                 _cstyle.setfont (_font); _cstyle.alignment = ThiS.alignment;            return _cstyle;        } set {This._cstyle = value;} }    }}

The following is the HTML string output to Ezcel, this is for my table, the others slowly change it ....

Using system;using system.collections;using system.collections.generic;using system.drawing;using System.IO;using System.linq;using system.text.regularexpressions;using system.xml.linq;using chilkat;using NPOI. HSSF. Record.cf;using Npoi. HSSF. Usermodel;using Npoi. HSSF. Util;using Npoi. Ss. Usermodel;namespace testnpoi{public class Npoihelper {private ReadOnly list<cellstyle> _userstylelis        t = new list<cellstyle> ();        <summary>//write HTML XML string to Excel using Npoi. </summary>//<param name= "sHtml" ></param> public void Writehtmltoexcel (string sHtml)            Byte[] Writehtmltoexcel (string sHtml) {//The most important thing are to get a XML string ... 1. Remove tags that is not a closed (maybe HTML, but not XML friendly)//2.            Remove Special string ... list<string> multiplepatterns = new List<string> {"title=\". *?\" "," onmouseover=\ ". *?\" "," ondblclick=\ ". *?\" "," <br> ",                "<BR>", " "," <input .*?> ",//" &LT;TD >            . *?<input name=\ ".*?>.*?</td>"}; sHtml = Regex.Replace (sHtml, String. Join ("|", Multiplepatterns), String.            Empty);            var doc = Xdocument.parse (sHtml);            Hssfworkbook wk = new Hssfworkbook (); Isheet sheet = wk.            Createsheet ("Report1"); Icellstyle style = wk.            Createcellstyle ();            Initialize Workbook color Pallette. Style.            Fillforegroundcolor = Htmltocolorindex (wk, "#CCCC99"); String[] Styleall = {"Background", "Align", "Color"}; "Text-dec", "wrap"//Get header tr var queryheader = doc. Descendants ("tr").            Take (1); Get header th int count = Queryheader. Descendants ().            Count (); IRow row = sheet.            CreateRow (0); for (int i = 0; i < count; i++) {Icell cell = row.                Createcell (i); XElement XE = Queryheader. Descendants ().                Elementatordefault (i);                foreach (string s in Styleall) {Getstyleall (Xe, WK, ref style, s);                } int index = Checkduplicateuserstyle (style, wk, "header"); Cell. Setcellvalue (XE.                Value.trim ()); Cell. CellStyle = _userstylelist[index].                                Cstyle; Sheet.            Autosizecolumn (i); } for (int i = 0; i < Doc. Descendants ("tr"). Count ()-1; i++) {//Get data style var Dataleft = doc. Descendants ("tr"). Skip (i + 1).                Take (1); IRow NewRow = sheet.                CreateRow (i + 1); var DataRow = Dataleft. DescEndants ("TD"); for (int j = 0; J < DataRow. Count (); J + +) {XElement dataxe = DataRow.                    Elementatordefault (j);                                        Icell Newcell = Newrow.createcell (j);                    foreach (string s in Styleall) {Getstyleall (dataxe, WK, ref style, s);                    } int index = Checkduplicateuserstyle (style, WK, "data"); Newcell.setcellvalue (Dataxe.                    Value.trim ()); Newcell.cellstyle = _userstylelist[index].                    Cstyle; Sheet.                Autosizecolumn (i);                }}//download scripts using (MemoryStream ms = new MemoryStream ()) { Wk.                Write (MS);                From the web.                HttpContext.Current.Response.ContentType = "Application/octet-stream"; HttpContext.Current.Response.AddHeader ("Content-disposition", "atTachment;                Filename=report.xls "); HttpContext.Current.Response.BinaryWrite (Ms.                ToArray ());                From console, write to file.                String dt = DateTime.Now.ToString ("Yyyy-mm-dd-hh-mm-ss"); using (FileStream fs = new FileStream (@ "C:\Users\Richard\Documents\tools\TestNPOI\TestNPOI\bin\" + dt + ". xls", FileMode . Create)) {Ms.                WriteTo (FS);                }////from Web/console, attached to email. Return Ms.            ToArray ();        }} public void Convertcellstyletouserstyle (Icellstyle cellstyle, CellStyle usercellstyle, Hssfworkbook wk)            {usercellstyle.fillforegroundcolor = Cellstyle.fillbackgroundcolor; Usercellstyle.fontcolor = Cellstyle.getfont (wk).            Color;        Usercellstyle.alignment = cellstyle.alignment;  } public int Checkduplicateuserstyle (Icellstyle cellstyle, Hssfworkbook wk, String type) {          int index = _userstylelist.findindex (t = = T.fontcolor = = Cellstyle.getfont (wk). Color && T.fillforegroundcolor = = Cellstyle.fillforegroundcolor && t.alignment = = cellstyle.alignment)            ;            if (index >= 0) return index; CellStyle user = new CellStyle (wk) {fontcolor = Cellstyle.getfont (wk).                 Color, Fillforegroundcolor = cellstyle.fillforegroundcolor, Alignment = Cellstyle.alignment,            type = type};            _userstylelist.add (user);        return _userstylelist.count-1;            } public void Getstyleall (XElement xe, Hssfworkbook wk, ref icellstyle CellStyle, string itemstyle) {            int pos_start =-1;            int pos_len =-1;                    Switch (itemstyle) {case ' background '://Background color <-> foreground color in Excel                    Pos_start = 17;         Pos_len = 7;           Break                    Case "Text-dec"://underline ... actually, no need to does this ... can use regex ... but no time to change the code ....                    Pos_start = 16;                    Pos_len = 9;                Break                    Case "COLOR"://Font Color Pos_start = 6; Pos_len = 7;                Changeable break;                    Case "Align"://Alignment pos_start = 6; Pos_len = 4;                Changeable break;                Case "wrap"://break;                    Default:pos_start = 0;                    Pos_len = 0;            Break } if (ItemStyle = = "Align") {if (XE. Attribute ("align") = null) {string attr = Xe. Attribute ("Align").                    Value; if (attr = = "Left") {Cellstyle.aLignment = HorizontalAlignment.Left; } else if (attr = = "Right") {cellstyle.alignment = Horizonta                    Lalignment.right;                    } else {cellstyle.alignment = HorizontalAlignment.Center; }} else Cellstyle.alignment = HorizontalAlignment.Left            ; }//else if (ItemStyle = = "wrap")//{//if (XE. Attribute ("wrap") = null)//{//String attr = Xe. Attribute ("White-space").            Value;            Cellstyle.wraptext = attr = = "nowrap;";            }//else//{//Cellstyle.wraptext = false; }//} else if (ItemStyle = = "Text-dec") {if (XE. Attribute ("style")! = null && XE. AttrIbute ("style"). Value.contains (ItemStyle)) {GetBackColor (XE). Attribute ("style").                Value, Wk,ref CellStyle, Pos_start, Pos_len, ItemStyle); }} else if (ItemStyle = = "Color" | | itemstyle = = "Background") {if (xe.a Ttribute ("style")! = null && XE. Attribute ("style"). Value.contains (ItemStyle)) {GetBackColor (XE). Attribute ("style").                Value, Wk,ref CellStyle, Pos_start, Pos_len, ItemStyle); } else {if (XE. Parent.attribute ("style")! = null && XE. Parent.attribute ("style"). Value.contains (ItemStyle)) {GetBackColor (XE). Parent.attribute ("style").                    Value, Wk,ref CellStyle, Pos_start, Pos_len, ItemStyle);                            } else {if (ItemStyle = = "Background") CellstylE.fillforegroundcolor = Npoi. HSSF.                    Util.HSSFColor.WHITE.index; }}}}///<summary>//need to re-write if has time ....//        /</summary>//<param name= "attr" ></param>//<param name= "wk" ></param> <param name= "CellStyle" ></param>//<param name= "Posstart" ></param>//&lt ;p Aram Name= "Poslen" ></param>///<param name= "type" ></param> public void GetBackColor ( String attr, Hssfworkbook wk, ref icellstyle cellstyle, int posstart, int poslen, String type) {int pos            Des = 0;            Short Npoicolor = 0; Posdes = attr.            IndexOf (type, stringcomparison.ordinal); Switch (type) {case "background"://regex Regbackcol = new Regex ("background                    -color:[\\w]+; "); String mybackstring = String. EmpTy Regex regbackcol = new Regex ("background-color:*" ([^;]                    +)"); Match Matchbackcol = Regbackcol.                    Match (attr); if (Matchbackcol. Success) {string colstr = Matchbackcol.                        Value; if (colstr. Contains ("#")) {mybackstring = Colstr.                        Substring (Posstart, Poslen); } else if (colstr. Contains ("RGB")) {string col = colstr. Substring (Posstart, Colstr. Length-posstart).                            Trim (); String Colrgb = Col. Substring (4, Col.                            LENGTH-4); int COLR = Int. Parse (Colrgb.                            Split (', ') [0]); int colg = Int. Parse (Colrgb.                            Split (', ') [1]); int colb = Int. Parse (Colrgb.                            Split (', ') [2]); mybackstring = String.Format ("#{0:x2}{1:x2}{2:x2}", COLR, Colg, colb); } else {string col = colstr. Substring (Posstart, Colstr.                            Length-posstart);                            Color color = color.fromname (col); mybackstring = String.Format ("#{0:x2}{1:x2}{2:x2}", color. R, color. G, color.                        B);                        } Npoicolor = Htmltocolorindex (wk, mybackstring);                        if (Npoicolor! = 0) {Cellstyle.fillforegroundcolor = Npoicolor; }} else {Cellstyle.fill Foregroundcolor = Npoi. HSSF.                    Util.HSSFColor.WHITE.index;                } break;                Case "align": break; Case "Text-dec": IFont fontdec = wk.                    Getfontat (0); Fontdec. Underline= Fontformatting.u_single;                    Cellstyle.setfont (FONTDEC);                Break Case "COLOR": attr = attr.                    Replace ("-color", "" ");                    int poscol =-1; Poscol = attr.                    IndexOf (type); if (poscol >= 0) {if (attr. Contains ("#")) {string col = attr.                            Substring (Posdes + Posstart, Poslen);                            Short index = Htmltocolorindex (wk, col); IFont font = wk. Getfontat (0); Font.                            Color = index; Font.                            FontName = "Verdana";                        Cellstyle.setfont (font);                            } else {Regex reg = new Regex ("color:[\\w]+"); Match match = Reg.                    Match (attr);        if (match. Success) {string colstr = match.                                Value; String col = Colstr. Substring (Posstart, Colstr.                                Length-posstart);                                Color color = color.fromname (col); String myhexstring = String.Format ("#{0:x2}{1:x2}{2:x2}", color. R, color. G, color.                                B);                                Short index = Htmltocolorindex (wk, myhexstring); IFont font = wk. Getfontat (0);///////////////font.                                Color = index; Font.                                FontName = "Verdana";                            Cellstyle.setfont (font);            }}} break;            }} public short Htmltocolorindex (Hssfworkbook book, string htmlcolor) {Short T = 0; System.Drawing.Color Color = System.drawiNg.            Colortranslator.fromhtml (Htmlcolor); Hssfpalette palette = Book.            Getcustompalette (); Hssfcolor Hssfcolor = palette. Findcolor (color. R, color. G, color.            B); if (Hssfcolor = = null) {if (Npoi. HSSF. Record.PaletteRecord.STANDARD_PALETTE_SIZE < 255) {if (Npoi. HSSF. Record.PaletteRecord.STANDARD_PALETTE_SIZE <) {Npoi. HSSF.                        Record.PaletteRecord.STANDARD_PALETTE_SIZE = 64; Npoi. HSSF.                        Record.PaletteRecord.STANDARD_PALETTE_SIZE + = 1; Hssfcolor = palette. Addcolor (color. R, color. G, color.                        B); Hssfcolor = palette. Findcolor (color. R, color. G, color.                    B); } else {Hssfcolor = palette. Findsimilarcolor (color. R, color. G, color.                    B);                } t = Hssfcolor.getindex ();          }  } else {t = Hssfcolor.getindex ();        } return t; } public void Attachexceltoemail (byte[] buff, email email, string fileName) {email.        Adddataattachment (fileName, Buff); }    }}

　　

Npoi, table String to Excel