[Cyan tour] Using NPOI to import and export Excel files. cyannpoi

[Cyan tour] Using NPOI to import and export Excel files. cyannpoi

/// <Summary> /// Office file format /// </summary> public enum OfficeType {// <summary> /// 97-2003 format /// </summary> [Description ("Office2003")] office2003, /// <summary> // 2007 + format // </summary> [Description ("Office2007")] Office2007}

 

The "FormatFileName" method is defined to correct the file name generated:

/// <Summary> /// format the Excel file name and add a suffix to the Excel file based on the Excel type. /// </Summary> /// <param name = "fileName"> unformatted file name </param> /// <param name = "officeType"> Excel type </param> // <returns> the formatted Excel file name. </Returns> public static String FormatFileName (String fileName, OfficeType officeType) {if (String. isNullOrEmpty (fileName) throw new ArgumentNullException ("fileName"); var ext = officeType = OfficeType. office2007? ". Xlsx": ". xls"; var name = fileName; if (! FileName. EndsWith (ext, StringComparison. CurrentCultureIgnoreCase) {name + = ext;} return name ;}

 

How does one export data?

Data export is a regular task that took up May 2014 of my work time in 2/3. The problems encountered during this period are as follows:

To put it bluntly, there are two problems:

To solve these two problems, the blogger designs an interface "IExportColumn ":

/// <Summary> /// export column interface /// </summary> /// <typeparam name = "T"> data row type </typeparam> public interface IExportColumn <in T >{/// <summary> /// Column Title /// </summary> String Title {get ;} /// <summary> /// obtain the value of this column /// </summary> /// <param name = "row"> </param> // <param name = "index"> </param> // <returns> </returns> Object GetValue (T row, int32 index );}

The read-only Title attribute indicates the Title of the imported column. The GetValue method is used to pass in the index of the data item and item in the set. At the same time, the general column "ExportColumn <T>" is added ":

/// <Summary> /// export column /// </summary> /// <typeparam name = "T"> </typeparam> public class ExportColumn <T>: IExportColumn <T> {public ExportColumn (String title, Func <T, Int32, Object> funcGetValue) {if (String. isNullOrEmpty (title) throw new ArgumentNullException ("title"); if (funcGetValue = null) throw new ArgumentNullException ("funcGetValue"); this. title = title; this. _ funcGetValue = funcGetValue;} private readonly Func <T, Int32, Object> _ funcGetValue; public string Title {get; private set;} public object GetValue (T row, int index) {return this. _ funcGetValue (row, index );}}

You can also conveniently export the DataTable "DataRowExportColumn ":

    public class DataRowExportColumn : IExportColumn<DataRow>    {        public DataRowExportColumn(String name)            : this(name, String.Empty)        {        }        public DataRowExportColumn(String name, String title)            : this(name, title, null)        {        }        public DataRowExportColumn(String name, String title, Func<Object, Int32, Object> funcFormatValue)        {            if (String.IsNullOrEmpty(name)) throw new ArgumentNullException("name");            this.Name = name;            this._title = title;            this._funcFormatValue = funcFormatValue;        }        public String Name { get; private set; }        private readonly String _title;        private readonly Func<Object, Int32, Object> _funcFormatValue;        public string Title        {            get { return String.IsNullOrEmpty(this._title) ? this.Name : this._title; }        }        public object GetValue(DataRow row, int index)        {            var val = row[this.Name];            return this._funcFormatValue != null ? _funcFormatValue(val, index) : val;        }    }

Of course, we need an export method:

/// <Summary> // export Excel. If the Excel type is Office2003, the number of data rows cannot exceed 65535. If the Excel type is exceeded, it is split into multiple work zones. /// </Summary> /// <typeparam name = "T"> data type </typeparam> /// <param name = "dataSource"> data source </param> /// <param name = "excelType"> EXCEL format </param> /// <param name = "sheetName"> workspace name </param> /// <param name = "saveStream"> saved file stream </param> // <param name = "columns"> export column </param> public static void ExportExcel <T> (IList <t> dataSource, officeType excelType, String sheetName, Stream saveStream, IList <IExportColumn <T> columns)

The data export code looks like this:

Using (var fs = new FileStream (tmpFileName, FileMode. create) {ExcelHelper. exportExcel (list, OfficeType. office2003, "Insurance Card", fs, new IExportColumn <Entity. insuranceCard> [] {new ExportColumn <Entity. insuranceCard> ("no.", (o, I) => o. id), new ExportColumn <Entity. insuranceCard> ("card number", (o, I) => o. number), new ExportColumn <Entity. insuranceCard> ("type", (o, I) => o. insuranceCardTypeName), new ExportColumn <Ent Ity. insuranceCard> ("card printing time", (o, I) => o. createdTime), new ExportColumn <Entity. insuranceCard> ("Activate?", (o, I) => o. enabled? "Activated": "locked"), new ExportColumn <Entity. InsuranceCard> ("Activate?", (o, I) => o. Activated? "Activated": "Not activated"), new ExportColumn <Entity. InsuranceCard> ("Password", (o, I) => o. Password )});}

What do you mean to be compatible with DataTable and Dictionary <TKey, TValue>? Sao Nian, "dt. rows. cast <DataRow> (). toList () "doesn't understand," dic. select (I => new {I. key, I. value }). toList () "doesn't understand? What are you still using. NET 2.0? LINQBridge you deserve.

Data Import

The data sources for data export come from computers, while those for data import come from people. Once the "person" element is involved, a series of constraints must be added before the system can properly understand the operations that people want to express. After all, computers are not so intelligent.

If you want to use Excel to import data, we require that the first row of Excel must be the column title, and there cannot be multiple row titles or cross-row cross-column situations. If there are any non-conforming items, the import will fail. No way. Machines are machines. We chose DataSet as the data import return type, which is easy to process and versatile. Most importantly, you can directly view the DataSet content in Visual Studio for troubleshooting.

/// <Summary> /// import Excel /// </summary> /// <param name = "fileStream"> </param> /// <returns> </returns> // <exception cref = "ArgumentNullException"> </exception> public static DataSet ImportExcel (Stream fileStream)

Data Import automatically recognizes the Excel format, which is 2007-or +. Therefore, we only need to pass in the data stream of the Excel file.

Talk about those pitfalls

Finally, the source code is available: Cyan. Toolkit. Office