Import and export of C#excel

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

2, Npoi is the C # version of POI, Npoi row and column index are starting from 0

3, POI read Excel has two formats one is HSSF, the other is XSSF. The differences between HSSF and XSSF are as follows:
HSSF is the POI Project's pure Java implementation of the Excel ' ( -2007) file format.
XSSF is the POI Project's pure Java implementation of the Excel OOXML (. xlsx) file format.
That is: HSSF applies to versions prior to 2007, XSSF is applicable to version 2007 and above.

Here is an example of using Npoi to read and write Excel: The function of the Excelhelper package is to write data in the DataTable to Excel, or to read data from Excel into a DataTable.

Excelhelper class:

Using system;using system.collections.generic;using system.linq;using system.text;using NPOI. Ss. Usermodel;using Npoi. XSSF. Usermodel;using Npoi. HSSF.        Usermodel;using system.io;using system.data;namespace netutilitylib{public class Excelhelper:idisposable { private string fileName = null;        File name private Iworkbook workbook = null;        Private FileStream fs = null;        private bool disposed;            Public Excelhelper (String fileName) {this.filename = FileName;        disposed = false; }///<summary>//Import DataTable data into Excel///</summary>//<param name= "Data" > Data to import </param>//<param name= "Iscolumnwritten" >datatable column names are imported </param>//<par Am Name= "SheetName" > Name of the sheet of Excel to import </param>//<returns> Import data rows (contains column names that row) </returns> p ublic int Datatabletoexcel (DataTable data, string sheetname, bool Iscolumnwritten) {            int i = 0;            int j = 0;            int count = 0;            Isheet sheet = null;            FS = new FileStream (FileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);            if (Filename.indexof (". xlsx") > 0)//2007 Version workbook = new Xssfworkbook ();            else if (Filename.indexof (". xls") > 0)//2003 Version workbook = new Hssfworkbook (); try {if (workbook! = null) {sheet = workbook.                Createsheet (SheetName);                } else {return-1; } if (Iscolumnwritten = = true)//write DataTable column name {IRow row = sheet.                    CreateRow (0); for (j = 0; j < data. Columns.count; ++J) {row. Createcell (j). Setcellvalue (data. COLUMNS[J].                    ColumnName);        } count = 1;        } else {count = 0; } for (i = 0; i < data. Rows.Count; ++i) {IRow row = sheet.                    CreateRow (count); for (j = 0; j < data. Columns.count; ++J) {row. Createcell (j). Setcellvalue (data. ROWS[I][J].                    ToString ());                } ++count; } workbook. Write (FS);            Write to Excel return count; } catch (Exception ex) {Console.WriteLine ("Exception:" + ex.                Message);            return-1; }}////<summary> import data from Excel into a DataTable////</summary>//<param Name= name of "sheetname" >excel Workbook sheet </param>///<param Name= "Isfirstrowcolumn" > whether the first row is a DataTable column name      </param>//<returns> return datatable</returns>  Public DataTable exceltodatatable (string sheetname, bool isfirstrowcolumn) {Isheet sheet = null;            DataTable data = new DataTable ();            int startrow = 0;                try {fs = new FileStream (FileName, FileMode.Open, FileAccess.Read);                if (Filename.indexof (". xlsx") > 0)//2007 Version workbook = new Xssfworkbook (FS);                else if (Filename.indexof (". xls") > 0)//2003 Version workbook = new Hssfworkbook (FS); if (sheetname! = null) {sheet = workbook.                    Getsheet (SheetName); if (sheet = = null)//If the specified sheetname corresponding sheet is not found, try to get the first sheet {sheet = workbook .                    Getsheetat (0); }} else {sheet = workbook.                Getsheetat (0);      } if (sheet! = null) {              IRow firstrow = sheet.                    GetRow (0); int cellcount = Firstrow.lastcellnum; The number of the last cell in a row is the total number of columns if (Isfirstrowcolumn) {for (int i = fi Rstrow.firstcellnum; i < Cellcount;                            ++i) {Icell cell = Firstrow.getcell (i); if (cell! = null) {string cellvalue = cell.                                Stringcellvalue; if (cellvalue! = null) {DataColumn column = new Datacol                                    Umn (Cellvalue); Data.                                Columns.Add (column); }}} StartRow = Sheet.                    Firstrownum + 1; } else {StartRow = sheet.      Firstrownum;              }//The label of the last column int rowCount = sheet.                    Lastrownum; for (int i = startrow; I <= rowCount; ++i) {IRow row = sheet.                        GetRow (i); if (row = = null) continue; Rows with no data default is a null DataRow datarow = data.                        NewRow (); for (int j = row. Firstcellnum; J < Cellcount; ++J) {if (row. Getcell (j)! = NULL)///Similarly, cells with no data default to null Datarow[j] = row. Getcell (j).                        ToString (); } data.                    Rows.Add (DataRow);            }} return data; } catch (Exception ex) {Console.WriteLine ("Exception:" + ex.                Message);            return null; }} public void Dispose () {DisposE (true); Gc.        SuppressFinalize (this);                } protected virtual void Dispose (bool disposing) {if (!this.disposed) { if (disposing) {if (fs! = NULL) fs.                Close ();                } fs = null;            disposed = true; }        }    }}

Test code:

Using system;using system.collections.generic;using system.linq;using system.text;using System.Data;namespace Npoiexcelexample{class Program {static DataTable Generatedata () {DataTable data = new Da            Tatable (); for (int i = 0; i < 5; ++i) {data.            Columns.Add ("Columns_" + i.tostring (), typeof (String)); } for (int i = 0; i < ++i) {DataRow row = data.                NewRow ();                row["columns_0"] = "item0_" + i.tostring ();                row["columns_1"] = "item1_" + i.tostring ();                row["columns_2"] = "item2_" + i.tostring ();                row["Columns_3"] = "item3_" + i.tostring ();                row["Columns_4"] = "item4_" + i.tostring (); Data.            Rows.Add (row);        } return data;            } static void Printdata (DataTable data) {if (data = = null) return; for (int i = 0; i < data. Rows.coUnt ++i) {for (int j = 0; j < data. Columns.count; ++J) Console.Write ("{0}", data.                ROWS[I][J]);            Console.Write ("\ n"); }} static void Testexcelwrite (string file) {try {using (Excel                    Helper excelhelper = new Excelhelper (file)) {DataTable data = Generatedata ();                    int count = excelhelper.datatabletoexcel (data, "MySheet", true);                if (Count > 0) Console.WriteLine ("Number of imported data is {0}", count); }} catch (Exception ex) {Console.WriteLine ("Exception:" + ex.            Message); }} static void Testexcelread (string file) {try {using (excelh Elper excelhelper = new Excelhelper (file)) {DataTable dt = ExceLhelper.exceltodatatable ("MySheet", true);                Printdata (DT); }} catch (Exception ex) {Console.WriteLine ("Exception:" + ex.            Message); }} static void Main (string[] args) {string file = ": \\..            \\myTest.xlsx ";            Testexcelwrite (file);        Testexcelread (file); }    }}

Check this article for a higher volume of reading, update the other version of Aspose.cells that I use:

Ps:aspose is chargeable.

Using system;using system.collections.generic;using system.data;using system.io;using System.Linq;using System.Text; Using Aspose.cells;namespace netutilitylib{public static class Excelhelper {public static int Datatabletoex            CEL (DataTable data, String fileName, String sheetname, bool iscolumnnamewritten) {int num =-1;                try {Workbook Workbook;                Worksheet Worksheet = null;                if (file.exists (filename)) WorkBook = new WorkBook (filename);                else WorkBook = new WorkBook ();                        if (SheetName = = null) {if (WorkBook.Worksheets.Count > 0) {                    worksheet = Workbook.worksheets[0];                        } else {sheetname = "Sheet1";              WorkBook.Worksheets.RemoveAt (SheetName);          Worksheet = WORKBOOK.WORKSHEETS.ADD (sheetname); }} if (worksheet! = null) {worksheet.                    Cells.clear (); num = worksheet.                    Cells.importdatatable (data, Iscolumnnamewritten, 0, 0, false);                Workbook.save (FileName); }} catch (Exception ex) {Console.WriteLine (ex).            Message);        } return num;            public static void Addonerowtoexcel (DataRow datarow, String fileName, String sheetname) {try                {Workbook Workbook;                if (file.exists (filename)) WorkBook = new WorkBook (filename);                else WorkBook = new WorkBook ();                Worksheet Worksheet=null;                if (SheetName = = null) {worksheet = workbook.worksheets[0];  }              else {worksheet = Workbook.worksheets[sheetname]; } if (worksheet! = null) {worksheet. Cells.importdatarow (dataRow, worksheet.                    Cells.maxdatarow + 1,0); Worksheet. Cells.importarray (dataarray, worksheet.                    Cells.maxdatarow+1, 0, false);                Workbook.save (FileName); }} catch (Exception ex) {Console.WriteLine (ex).            Message);        }} public static DataTable exceltodatatable (String fileName, String sheetname, bool isfirstrowcolumnname)            {DataTable data = new DataTable ();                try {Workbook Workbook = null;                FileInfo FileInfo = new FileInfo (fileName); if (FileInfo.Extension.ToLower ().    Equals (". xlsx")) Workbook = new Workbook (fileName, new Loadoptions (LOADFORMAT.XLSX));            else if (FileInfo.Extension.ToLower ().                Equals (". xls")) Workbook = new Workbook (fileName, new Loadoptions (loadformat.excel97to2003));                    if (workbook! = null) {Worksheet Worksheet = null; if (sheetname! = null) {worksheet = workbook.                    Worksheets[sheetname]; } else {worksheet = workbook.                    Worksheets[0]; if (worksheet! = null) {data = worksheet. Cells.exportdatatable (0, 0, worksheet. cells.maxrow+1, worksheet.                        Cells.maxcolumn+1, Isfirstrowcolumnname);                    return data;                }} else {return data;       }} catch (Exception ex) {         Console.WriteLine (ex.            Message);        } return data; }    }}
Excel-related DLLs download: Npoi-lib.rar

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

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

Import and export of C#excel