[Turn] more than Npoi Excel components-epplus

This article transferred from: http://blog.darkthread.net/post-2012-05-12-epplus.aspx

The pre-release "The Shield" will be the file structure into Excel files, from the network friend Xiang's message (thank you), recognized a I miss the good components-epplus!

Npoi is a source of poi, in many interfaces, with a bit of Java's perspective and style, although it can be done by the Excel operation, but the function interface and call step, always let. NET old birds feel the hand, like with chopsticks eat hand grilled chicken awkward twist. For example: to write text into a new cell, you must first CreateRow () and then Createcell (), rather than in Excel VBA through. Cells (RowIndex, Colindex) once in place.

Linqtoexcel to read Excel provides a convenient extra choice, you can use the familiar LINQ language method to inquire about the Excel content is a few laps fast thing, but unfortunately linqtoexcel only for reading, to produce Excel, or to return to Npoi.

Epplus is an open source project starting at the end of 2009, which targets the server-side production of office Open XML Excel files (the xlsx of Excel 2007/2010, which does not contain Excel 2003 XLS), providing a more direct feel than npoi , a simpler API interface! Use. Cells[rowindex, Colindex] to directly access the linked fields bit, even with. CELLS[R1, C1, R2, C2] can get a selection range, then change their style, and to specify the font color, use cells[...]. Style.Font.Color.SetColor (color.red) can be done, unlike Npoi need CreateFont (), Createcellstyle (), SetFont (), Setcellstyle () a long string operation. This is. NET guest Heart Ideal good element ~ ~

I can't help but wonder why few people have heard it before. Privately, conjecture May and epplus only support xlsx, can not be compatible with the XLS format of Excel 2003, in the need to take care of and the use of the group is not specific to the Excel version of the threshold considerations. (Although the software provides a free Excel inspection tool, but requires additional installation will still have deployment surface considerations) on the other hand, this component is granted by LGPL, which means that if it is to be included in the product, the product must open source(not 100% to open source , licensing restrictions can be seen below newbie's message), but also cause some software manufacturers to use the suspect. Moreover, the software itself provides the open XML SDK, has covered the Excel file operation, some developers have been directly using the SDK, did not expect to spend time to evaluate the more convenient components (I sighs in the previous article, it will really make people miss the other sharper choice), may also be one of the reasons.

In my opinion, as the time flies, the importance of the old version of Excel compatibility will decline year after time, and should be used in the Web site, as long as not sellers Web site program, do not have to worry about worries LGPL restrictions (can participate in the previous discussion). As compared to the Open XML SDK, the first look at the language, Epplus really, as the net friend said, with "only see the new people laugh, do not see the old man cry" charm! Really can tell Npoi bye bye, in the intuitive use of the open XML SDK has also been compared down. After a review, Epplus is a solution that can be used with ease.

Well, if you want to actually test the power, you can use the last file to get an Excel case. Instead, use Epplus to write the parts of Excel.

The quickest way to cite Epplus in a case is through NuGet: (and not with NuGet, do you ever feel like you're on the starting point?)

Refer to the article Zeeshan Umar, three or two to change the program:

typography shows pure text Copy

    Add an expansion method: Setquickstyle, specify foreground/background color/horizontal Align

    void Setquickstyle (thisexcelrange range,

        Color ForeColor,

        Default (Color),

        Excelhorizontalalignment halign = excelhorizontalalignment.left)

    {

        Range. Style.Font.Color.SetColor (ForeColor);

        Default (Color))

        {

            Range. Style.Fill.PatternType = Excelfillstyle.solid;

            Range. Style.Fill.BackgroundColor.SetColor (BgColor);

        }

        Range. Style.horizontalalignment = halign;

    }

    <summary>

    Make an Excel worksheet from the list of records in the catalogue

    </summary>

    <param name= "Dirpath" > The path </param>

    <param name= "Excelpath" > Outbound Excel Path </param>

    <param name= "filter" > Discard function, pass in path, pass it back to true. Table Exclusions </param>

    <returns></returns>

    void Webtreetoexcel (

        String Excelpath,

        func<null)

    {

        Organize the Narikiyo of the catalogue

        New List<webitem> ();

        Explore (list, Dirpath, 0);

        Build Excel

        New Excelpackage ())

        {

            Excelworksheet sheet = p.workbook.worksheets.add ("Site Tree");

            int colidx = 1;

            foreach ("Path; File;description ". Split ('; ')) 

            {

                Sheet. Cells[1, colidx++]. Value = colname;

            }

            Modify the title column style

            Sheet. Cells[1, 1, 1, 3]. Setquickstyle (Color.yellow, Color.green,

                    Excelhorizontalalignment.center);

            int rowidx = 2;

            In list)

            {

                If the bypass check returns True, the pen is skipped

                Null && filter (item. Path))

                    Continue

                Place path in first linked fields (hidden later)

                Sheet. CELLS[ROWIDX, 1]. Value = Item. Path;

                File name or catalog name

                    New String (' ', item. Layer * 4) + item. Name;

                if (item. Isfolder)

                {

                    Sheet. CELLS[ROWIDX, 2]. Setquickstyle (Color.Blue);

                }

                rowidx++;

            }

            The first linked fields hidden

            True

            Self-stretching linked fields width

            Sheet. Column (2). AutoFit ();

          Sheet. Column (2). Width + = 2;

          Sheet. Column (3). Width = 50;

            Write a file

            P.saveas (new FileInfo (Excelpath));

        }

Add an expansion method: Setquickstyle, specify foreground/background color/horizontal align private static void Setquickstyle (this excelrange range, color Foreco lor, Color BgColor = default (color), excelhorizontalalignment halign = excelhorizontalalignment.left) { Range. Style.Font.Color.SetColor (ForeColor); if (BgColor! = Default (Color)) {range. Style.Fill.PatternType = Excelfillstyle.solid; Range. Style.Fill.BackgroundColor.SetColor (BgColor); } range. Style.horizontalalignment = halign; } ///<summary>///////</summary>/////////////<param//////Dirpath > to be made an Excel worksheet/////Name= The recording path </param>///<param Name= "Excelpath" > Outbound Excel Path </param>//<param name= "filter" > Discard function, fax Enter the path to be sentenced, return true when the table excludes </param>///<returns></returns> public static void Webtreetoexcel (str ing Dirpath, string excelpath, func<string, bool> filter = null) {//To organize the catalogue Narikiyo list<webitem> list = new list<webitem> (); Explore (list, Dirpath, 0); Build Excel using (excelpackage p = new Excelpackage ()) {Excelworksheet sheet = P.workbook.workshe Ets. ADD ("Site Tree"); int colidx = 1; foreach (String colname in "Path; File;description ". Split (';')) {Sheet. Cells[1, colidx++]. Value = colname; }//Modify the Title column style sheet. Cells[1, 1, 1, 3]. Setquickstyle (Color.yellow, Color.green, excelhorizontalalignment.center);  int Rowidx = 2; foreach (var item in list) {//If bypass is passed back to true, it is skipped by the pen if (filter! = NULL &&amp ; Filter (item. Path)) continue; Place the path in the first linked fields (hidden) sheet. CELLS[ROWIDX, 1]. Value = Item. Path; Sheet The file name or the name of the catalogue. CELLS[ROWIDX, 2]. Value = new String (' ', item. Layer * 4) + item. Name; if (item. Isfolder) {sheet. CELLS[ROWIDX, 2]. Setquickstyle (Color.Blue); } rowidx++; }//First linked fields hidden sheet. Column (1). Hidden = true; Self-stretching linked fields-sheet. Column (2). AutoFit (); Sheet. Column (2). Width + = 2; Sheet. Column (3). Width = 50; Write the file P.saveas (new FileInfo (Excelpath)); }

One of the results of a model, and the last Npoi to write the method, is not a refreshing look at a lot of it? In the use of xlsx as the main web site to be used, we will use the bold!

[Turn] more than Npoi Excel components-epplus