C # Importing and exporting Excel with Infragistics (i)

Recent projects have data import to export the needs of Excel, here do simple collation.

The company uses Infragistics products, pay for it, and don't need to install Office locally.

Need friends can download infragistics.2013.2.2098, extract password: 5u17

This article full code download Demo.Excel.zip

Of course, I know there are other open-source class libraries to operate Excel, I hope the resources of Bo friends can share.

Infragistics Installation and use

After you install Infragistics_winforms_20132.msi directly, the references in the project reference are available. To manipulate Excel, quote Infragistics4.documents.excel.v13.2.dll is sufficient.

Export formats supported by Excel

• excel97to2003
• Excel97to2003template
• Excel2007
• Excel2007macroenabled
• Excel2007macroenabledtemplate
• Excel2007template
• Strictopenxml

If you use Justcompile to view the source code, in the case of no formatting, the default is saved to Excel 97-2003 Workbook (*.xls) format, so you want to export other formats of Excel, you need to call Setcurrentformat (Workbookformat format) Method.

Using SaveFileDialog, set filter to map format according to the file's suffix name.

Public Format GetFormat (string sextension) {    switch (sextension)    {case        ". xls":            return format.excel97to2003;        Case ". XLT":            return format.excel97to2003template;        Case ". xlsx":            return format.excel2007;        Case ". Xltx":            return format.excel2007template;        Case ". xlsm":            return format.excel2007macroenabled;        Case ". Xltm":            return format.excel2007macroenabledtemplate;        Default:            return format.excel97to2003;    

create Worksheet

Defining data types

Define Attribute

• Displaynameattribute: Displays the header of Excel
• Worksheetheaderattribute: Defines the background and foreground color of the header

To populate worksheet with generics

Reflection Gets the property value

public bool Createsheet<t> (string ssheetname, list<t> lstrowdata, bool bcreateheader) {excelprocessevent (" Createsheet Start, Ssheetname-"+ Ssheetname +", Lstrowdata.count-"+ Lstrowdata.count +", Bcreateheader-"+ BCrea    Teheader);        try {Worksheet Aworksheet = _workbook.worksheets.add (ssheetname);        Type atype = typeof (T);        if (Bcreateheader) Setsheetheader (Aworksheet, atype); int rowIndex = Bcreateheader?        1:0; foreach (Var rowdata in Lstrowdata) {for (int i = 0; i < atype.getproperties (). Length;                i++) {var prop = atype.getproperties () [i]; Aworksheet.rows[rowindex]. Cells[i]. Value = Prop.                GetValue (RowData); Excelprocessevent ("Createsheet InProgress," + RowIndex + "-" + Prop. Name + ":" + Prop. GetValue (RowData).            ToString ());        } rowindex++;        } excelprocessevent ("Createsheet End, Success");    return true; } CATCH (Exception ex) {excelerrorevent ("Createsheet Failed, Error-" + ex.        Message);    return false;    }}private void Setsheetheader (Worksheet oworksheet, Type otype) {excelprocessevent ("Setsheetheader Start"); for (int i = 0; i < otype.getproperties (). Length;        i++) {var prop = otype.getproperties () [i]; String displayName = Prop.        Name; try {var customattr = Prop.            Getcustomattribute<displaynameattribute> ();                            DisplayName = Customattr.displayname;        } catch {} excelprocessevent ("Setsheetheader InProgress, DisplayName-" + displayName);        Color backgroundcolor = Color.White;        Color ForeColor = Color.Black; try {var customattr = Prop.            Getcustomattribute<worksheetheaderattribute> ();            BackgroundColor = colortranslator.fromhtml (Customattr.backgroundcolor); ForeColor = colortranslator.fromhtmL (Customattr.forecolor); } catch {} excelprocessevent ("Setsheetheader InProgress, BackgroundColor-" + Backgroundcolo        R + ", ForeColor-" + ForeColor); Oworksheet.rows[0]. Cells[i].        Value = DisplayName; Oworksheet.rows[0]. Cells[i].        Cellformat.fill = Cellfill.createsolidfill (backgroundcolor); Oworksheet.rows[0]. Cells[i].    CellFormat.Font.ColorInfo = new Workbookcolorinfo (ForeColor); } excelprocessevent ("Setsheetheader End");}

Save Workbook

Save a cost ground file

Save into Byte stream (want to make a service, provide Excel download)

public bool Save (string sfilename) {excelprocessevent ("Save Start, sFileName-" + sfilename); Workbookformat?        Format = Workbook.getworkbookformat (sFileName); if (!format.            HasValue) {excelerrorevent ("Save Failed, Error-no matched Workbook format found");        return false; } try {_workbook.setcurrentformat (format.            Value);            if (_workbook.worksheets.count <= 0) _workbook.worksheets.add ("Sheet1");            _workbook.save (sFileName);            Excelprocessevent ("Save End, Success");        return true; } catch (Exception ex) {excelerrorevent ("Save Failed, Error-" + ex.            Message);        return false; }} public bool Save (out byte[] filebytes, Format eformat = format.excel97to2003) {excelprocessevent ("Sav        e Start ");        Filebytes = new Byte[0]; Workbookformat?        Format = FormatMap (Eformat); if (!format.    HasValue)    {excelerrorevent ("Save Failed, Error-no matched Workbook format found");        return false; } try {using (MemoryStream ms = new MemoryStream ()) {_workbook.setcur Rentformat (format.                Value);                if (_workbook.worksheets.count <= 0) _workbook.worksheets.add ("Sheet1");                _workbook.save (MS); Ms.                Seek (0, Seekorigin.begin); Filebytes = new byte[(int) Ms.                Length]; Ms.                Read (filebytes, 0, filebytes.length);                Excelprocessevent ("Save End, Success");            return true; }} catch (Exception ex) {excelerrorevent ("Save Failed, Error-" + ex.            Message);        return false; }    }

Import Excel Load File

    public bool Load (string sfilename)    {        excelprocessevent ("Load Start, sFileName-" + sfilename);        Try        {            _workbook = workbook.load (sfilename);            Excelprocessevent ("Load End, Success");            return true;        }        catch (Exception ex)        {            excelerrorevent ("Load Failed, Error-" + ex. Message);            return false;        }    }

parsing worksheet

Generics are more general-purpose

Reflection dynamically creates class instances

Public list<t> readsheet<t> (string ssheetname) {excelprocessevent ("Readsheet Start, Ssheetname-" + sSheet    Name);    list<t> lst = new list<t> ();    Worksheet aworksheet = null;        try {aworksheet = _workbook.worksheets[ssheetname];            if (Aworksheet = = null) {excelprocessevent ("Readsheet Failed, error-no Worksheet found");        return LST; } var lstheaders = Aworksheet.rows[0]. Cells.select (o = o.value.tostring ()).        ToList ();        Type Rowdatatype = typeof (T);        list<keyvaluepair<string, int>> lstheadersorder = new list<keyvaluepair<string, int>> ();            for (int i = 1; i < AWorksheet.Rows.Count (); i++) {var row = Aworksheet.rows[i];            T obj = (t) activator.createinstance (Rowdatatype); foreach (Var prop in rowdatatype.getproperties ()) {var displaynameattr = Prop. Getcustomattribute<displaynameattributE> ();                string displayName = Displaynameattr.displayname;                int cellindex = Lstheaders.indexof (displayName); Prop. SetValue (obj, row. Cells[cellindex].            Value); } lst.        ADD (obj);    } return LST; } catch (Exception ex) {excelerrorevent ("Readsheet Failed, Error-" + ex.    Message); } return LST;}

Client Calls

Using system;using system.collections.generic;using system.linq;using system.text;using System.Threading.Tasks; Using system.componentmodel;using system.windows.media;using service.excel;namespace Demo.Excel.Client{public class Book {[DisplayName (' Book number ')] [Worksheetheader ("#006699", "#ffffff")] public string Id {get ; Set         } [DisplayName ("Book Name")] [Worksheetheader ("#006699", "#ffffff")] public string Name {get; set;}        [DisplayName ("Price")]        [Worksheetheader ("#006699", "#ffffff")] public string price {get; set;}        [DisplayName ("Author Name")]        [Worksheetheader ("#006699", "#ffffff")] public string Author {get; set;}        [DisplayName ("Book Description")]    [Worksheetheader ("#006699", "#ffffff")] public string Description {get; set;} }}

private void Test_saveas () {Excelhelper oexcelhelper = new Excelhelper ();        SaveFileDialog SaveFileDialog = new SaveFileDialog (); Savefiledialog.filter = "Excel workbook|*.xlsx| Excel macro-enabled workbook|*.xlsm| Excel 97-2003 workbook|*.xls| Excel template|*.xltx| Excel macro-enabled template|*.xltm| Excel 97-2003 template|*.xlt|        Static Open XML spreadsheet|*.xlsx ";        Savefiledialog.filterindex = 1;        Savefiledialog.addextension = true;        Savefiledialog.filename = "Testexcel." + DateTime.Now.ToString ("Yyyymmddhhmmss");            if (savefiledialog.showdialog () = = True) {list<book> Lstbook = new list<book> ();                for (int i = 0; i <, i++) {Book Abook = new book ();                Abook.id = i.ToString ();                Abook.name = "book-" + i.ToString ();                Abook.price = i.ToString ();                Abook.author = "Cad-capture"; Abook.description = "This is a famous Book around the World ";            Lstbook.add (Abook);            } oexcelhelper.createsheet<book> ("book", Lstbook, True);            byte[] filebytes = new Byte[0]; if (Oexcelhelper.save (Savefiledialog.filename)) {}}} private void Test_saveasbinary (        ) {Excelhelper oexcelhelper = new Excelhelper ();        SaveFileDialog SaveFileDialog = new SaveFileDialog (); Savefiledialog.filter = "Excel workbook|*.xlsx| Excel macro-enabled workbook|*.xlsm| Excel 97-2003 workbook|*.xls| Excel template|*.xltx| Excel macro-enabled template|*.xltm| Excel 97-2003 template|*.xlt|        Static Open XML spreadsheet|*.xlsx ";        Savefiledialog.filterindex = 1;        Savefiledialog.addextension = true;        Savefiledialog.filename = "Testexcel." + DateTime.Now.ToString ("Yyyymmddhhmmss");            if (savefiledialog.showdialog () = = True) {byte[] filebytes = new Byte[0]; if (Oexcelhelper.save (out FilebytES, Oexcelhelper.getformat (System.IO.Path.GetExtension (savefiledialog.safefilename))) {Try                    {using (FileStream FileStream = File.openwrite (savefiledialog.filename))                    {filestream.write (filebytes, 0, filebytes.length);            }} catch (Exception ex) {}         }}} private void Test_load () {Excelhelper oexcelhelper = new Excelhelper ();        OpenFileDialog OpenFileDialog = new OpenFileDialog (); Openfiledialog.filter = "Excel workbook|*.xlsx| Excel macro-enabled workbook|*.xlsm| Excel 97-2003 workbook|*.xls| Excel template|*.xltx| Excel macro-enabled template|*.xltm| Excel 97-2003 template|*.xlt|        Static Open XML spreadsheet|*.xlsx ";        Openfiledialog.filterindex = 1;        Openfiledialog.addextension = true; if (Openfiledialog.shoWdialog () = = True) {list<book> Lstbook = new list<book> ();            if (Oexcelhelper.load (Openfiledialog.filename)) {Lstbook = oexcelhelper.readsheet<book> ("book");        } int count = Lstbook.count; }                }

Summary

This article is just a simple data encapsulation and then import and export, using the following generics, reflection, data flow, custom features, the next article will be a simple Excel style, Excel download.

In addition how to let the Excelhelper class more once and for all, you bo friends have what better idea, welcome to share.

C # Importing and exporting Excel with Infragistics (i)