[NPOI] C #. NET import the array to the Excel source code

The last time I published a code to import data to Excel using the Excel component, I felt bad, especially when using it. I saw an open-source NPOI Excel read and write component in the blog Park, which is very good and developed by Chinese people. I would like to thank the NPOI Development Team for providing such good things to everyone. Well, I will also dedicate my own code to the promotion. The advantage of using this item is that it is easy to use and fast. The NPOI usage tutorial is very detailed. The Code is as follows:

Using NPOI. Util;
Using NPOI. HSSF. Model;
Using NPOI. HSSF. UserModel;
Using NPOI. HSSF;

......

Code // convert a dataset to Excel: ConvertDataTableToExcel ConvertDataGridViewToExcel // currently supported data types include: DataTable, two-dimensional array, two-dimensional staggered array, DataGridView, ArrayList // 2010.01.03 using the NPOI class library, improve operation speed, easy to expand /// <summary> /// export the dataset to an Excel file /// </summary> /// <param name = "data"> one-dimensional array </param> /// <param name = "xlsSaveFileName"> Excel file name </param> /// <param name = "sheetName"> workbook name </param> /// <returns> whether the conversion is successful </returns> public static bool ConvertToExcel <T> (T [] data, string xlsSaveFileName, string sheetName) {FileStream fs = new FileStream (xlsSaveFileName, FileMode. create); try {HSSFWorkbook newBook = new HSSFWorkbook (); HSSFSheet newSheet = (HSSFSheet) newBook. createSheet (sheetName); // create a workbook HSSFRow newRow = (HSSFRow) newSheet. createRow (0); // create a row for (int I = 0; I <data. length; I ++) {newSheet. getRow (0 ). createCell (I ). setCellValue (Convert. toDouble (data [I]. toString (); // write data} newBook. write (fs); return true;} catch (Exception err) {throw new Exception ("failed to convert data to Excel:" + err. message);} finally {fs. close ();}} /// <summary> /// export the dataset to an Excel file /// </summary> /// <param name = "data"> two-dimensional array </param>/ // <param name = "xlsSaveFileName"> Excel file name </param> /// <param name = "sheetName"> workbook name </param> /// <returns> whether the conversion is successful </returns> public static bool ConvertToExcel <T> (T [,] data, string xlsSaveFileName, string sheetName) {FileStream fs = new FileStream (xlsSaveFileName, FileMode. create); try {HSSFWorkbook newBook = new HSSFWorkbook (); HSSFSheet newSheet = (HSSFSheet) newBook. createSheet (sheetName); // create a workbook for (int I = 0; I <data. getLength (0); I ++) {HSSFRow newRow = (HSSFRow) newSheet. createRow (I); // create a row for (int j = 0; j <data. getLength (1); j ++) {newSheet. getRow (I ). createCell (j ). setCellValue (Convert. toDouble (data [I, j]. toString (); // write data} newBook. write (fs); return true;} catch (Exception err) {throw new Exception ("failed to convert data to Excel:" + err. message);} finally {fs. close ();}} /// <summary> ///// <summary> /// export the dataset to an Excel file /// </summary> /// <param name = "data "> staggered array </param> /// <param name =" xlsSaveFileName "> Excel file name </param> /// <param name =" sheetName "> workbook name </param> /// <returns> whether the conversion is successful </returns> /// </summary> public static bool ConvertToExcel <T> (T [] [] data, string xlsSaveFileName, string sheetName) {FileStream fs = new FileStream (xlsSaveFileName, FileMode. create); try {HSSFWorkbook newBook = new HSSFWorkbook (); HSSFSheet newSheet = (HSSFSheet) newBook. createSheet (sheetName); // create a workbook for (int I = 0; I <data. getLength (0); I ++) {HSSFRow newRow = (HSSFRow) newSheet. createRow (I); // create a row for (int j = 0; j <data [I]. length; j ++) {newSheet. getRow (I ). createCell (j ). setCellValue (Convert. toDouble (data [I] [j]. toString (); // write data} newBook. write (fs); return true;} catch (Exception err) {throw new Exception ("failed to convert data to Excel:" + err. message);} finally {fs. close ();}} /// <summary> /// export the dataset to an Excel file /// </summary> /// <param name = "dt"> DataTable object </param>/ // <param name = "xlsSaveFileName"> Excel file name </param> /// <param name = "sheetName"> workbook name </param> /// <returns> whether the conversion is successful </returns> public static bool ConvertToExcel (System. data. dataTable dt, string xlsSaveFileName, string sheetName) {FileStream fs = new FileStream (xlsSaveFileName, FileMode. create); try {HSSFWorkbook newBook = new HSSFWorkbook (); HSSFSheet newSheet = (HSSFSheet) newBook. createSheet (sheetName); // create a workbook for (int I = 0; I <dt. rows. count; I ++) {HSSFRow newRow = (HSSFRow) newSheet. createRow (I); // create a row for (int j = 0; j <dt. columns. count; j ++) {newSheet. getRow (I ). createCell (j ). setCellValue (dt. rows [I] [j]. toString (); // write data} newBook. write (fs); return true;} catch (Exception err) {throw new Exception ("failed to convert data to Excel:" + err. message);} finally {fs. close ();}} /// <summary> /// export the dataset to an Excel file /// </summary> /// <param name = "dt"> DataGridView object </param>/ // <param name = "xlsSaveFileName"> Excel file name </param> /// <param name = "sheetName"> workbook name </param> /// <returns> whether the conversion is successful </returns> public static bool ConvertToExcel (System. windows. forms. dataGridView dgv, string xlsSaveFileName, string sheetName) {return ConvertToExcel (System. data. dataTable) dgv. dataSource, xlsSaveFileName, sheetName );}

The following shows how to import data to the DataTable:

Code /// <summary> /// export data to the able /// </summary> /// <param name = "data"> two-dimensional array data </param> /// <param name = "columnsName"> column name </param> /// <returns> DataTable object </returns> public static System. data. dataTable ConvertToDataTable <T> (T [,] data, string [] columnsName) {System. data. dataTable dt = new System. data. dataTable (); if (data. getLength (1)> columnsName. length) {throw new Exception ("the column name is not long enough");} try {// first add the column name for (int I = 0; I <data. getLength (1); I ++) {DataColumn dc = new DataColumn (columnsName [I], typeof (System. string); dt. columns. add (dc);} // Add data for (int I = 0; I <data. getLength (0); I ++) {DataRow dr = dt. rows. add (); for (int j = 0; j <data. getLength (1); j ++) {dr [j] = data [I, j]. toString ();} dt. rows. add (dr) ;}return dt ;}catch (Exception err) {throw new Exception ("failed to convert data to able:" + err. message );}} /// <summary> /// export data to the able /// </summary> /// <param name = "data"> two-dimensional array data </param> /// <returns> DataTable object </returns> public static System. data. dataTable ConvertToDataTable <T> (T [,] data) {System. data. dataTable dt = new System. data. dataTable (); try {// first add the column name for (int I = 0; I <data. getLength (1); I ++) {DataColumn dc = new DataColumn (); dt. columns. add (dc);} // Add data for (int I = 0; I <data. getLength (0); I ++) {DataRow dr = dt. rows. add (); for (int j = 0; j <data. getLength (1); j ++) {dr [j] = data [I, j]. toString ();} dt. rows. add (dr) ;}return dt ;}catch (Exception err) {throw new Exception ("failed to convert data to able:" + err. message );}} /// <summary> /// export data to the able /// </summary> /// <param name = "data"> staggered array data </param> /// <param name = "columnsName"> column name </param> /// <returns> DataTable object </returns> public static System. data. dataTable ConvertToDataTable <T> (T [] [] data, string [] columnsName) {System. data. dataTable dt = new System. data. dataTable (); if (data [0]. length> columnsName. length) {throw new Exception ("the column name is not long enough");} try {// first add the column name for (int I = 0; I <data [0]. length; I ++) {DataColumn dc = new DataColumn (columnsName [I], typeof (System. string); dt. columns. add (dc);} // Add data for (int I = 0; I <data. getLength (0); I ++) {DataRow dr = dt. newRow (); for (int j = 0; j <data [I]. length; j ++) {dr [j] = data [I] [j]. toString ();} dt. rows. add (dr) ;}return dt ;}catch (Exception err) {throw new Exception ("failed to convert data to able:" + err. message );}} /// <summary> /// export data to the able /// </summary> /// <param name = "data"> staggered array data </param> /// <returns> DataTable object </returns> public static System. data. dataTable ConvertToDataTable <T> (T [] [] data) {System. data. dataTable dt = new System. data. dataTable (); try {// first add the column name for (int I = 0; I <data [0]. length; I ++) {DataColumn dc = new DataColumn (); dt. columns. add (dc);} // Add data for (int I = 0; I <data. getLength (0); I ++) {DataRow dr = dt. newRow (); for (int j = 0; j <data [I]. length; j ++) {dr [j] = data [I] [j]. toString ();} dt. rows. add (dr) ;}return dt ;}catch (Exception err) {throw new Exception ("failed to convert data to able:" + err. message );}}

So far. This is really a good thing. I will try it again next time. There are many functions in it.