[NPOI版]C#.NET将数组导入Excel源代码

本博客所有文章分类的总目录：http://www.cnblogs.com/asxinyu/p/4288836.html

本博客其他.NET开源项目文章目录：http://www.cnblogs.com/asxinyu/p/4329755.html

上次发布了一个用Excel组件将数据导入到Excel的代码，用的过程中，感觉不好，特别是速度很慢。在博客园看到了一个NPOI Excel读写的开源组件，很不错，还是国人开发的。赞一个，谢谢NPOI开发团队，有这么好的东西奉献给大家啊。呵呵，我也把自己使用的代码奉献一下，也算推广一下吧。这个东西使用的好处是：使用简单，速度特快。NPOI的使用教程很详细，下面直接上代码：

using NPOI.Util;
using NPOI.HSSF.Model ;
using NPOI.HSSF.UserModel ;
using NPOI.HSSF ;

......

Code// 将数据集转换到Excel： ConvertDataTableToExcel   ConvertDataGridViewToExcel
		// 目前支持的数据类型有:DataTable,二维数组,二维交错数组,DataGridView,ArrayList
		// 2010.01.03 采用NPOI类库，改善操作速度,便于扩展

		/// <summary>
		/// 将数据集导出到Excel文件
		/// </summary>
		/// <param name="data">一维数组</param>
		/// <param name="xlsSaveFileName">Excel文件名称</param>
		/// <param name="sheetName">工作簿名称</param>
		/// <returns>是否转换成功</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 ) ;//新建工作簿
				HSSFRow      newRow = (HSSFRow )newSheet.CreateRow(0) ;//创建行
				for (int i = 0 ; i <data.Length ; i ++ )
				{
					newSheet.GetRow (0).CreateCell (i ).SetCellValue (Convert.ToDouble (data [i ].ToString ())) ;//写入数据
				}
				newBook .Write (fs ) ;
				return true ;
			}
			catch (Exception err)
			{
				throw new Exception ("转换数据到Excel失败："+err.Message ) ;
			}
			finally
			{
				fs.Close () ;
			}
		}
		
		/// <summary>
		/// 将数据集导出到Excel文件
		/// </summary>
		/// <param name="data">二维数组</param>
		/// <param name="xlsSaveFileName">Excel文件名称</param>
		/// <param name="sheetName">工作簿名称</param>
		/// <returns>是否转换成功</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 ) ;//新建工作簿
				for (int i = 0 ; i <data.GetLength (0) ; i ++ )
				{
					HSSFRow  newRow = (HSSFRow )newSheet.CreateRow(i ) ;//创建行
					for (int j = 0 ; j <data.GetLength (1) ; j ++ )
					{
						newSheet.GetRow (i ).CreateCell (j ).SetCellValue (Convert.ToDouble (data [i,j ].ToString ())) ;//写入数据
					}
				}
				newBook .Write (fs ) ;
				return true ;
			}
			catch (Exception err)
			{
				throw new Exception ("转换数据到Excel失败："+err.Message ) ;
			}
			finally
			{
				fs.Close () ;
			}
		}
		
		/// <summary>
		/// 		/// <summary>
		/// 将数据集导出到Excel文件
		/// </summary>
		/// <param name="data">交错数组</param>
		/// <param name="xlsSaveFileName">Excel文件名称</param>
		/// <param name="sheetName">工作簿名称</param>
		/// <returns>是否转换成功</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 ) ;//新建工作簿
				for (int i = 0 ; i <data.GetLength (0) ; i ++ )
				{
					HSSFRow  newRow = (HSSFRow )newSheet.CreateRow(i ) ;//创建行
					for (int j = 0 ; j <data[i ].Length ; j ++ )
					{
						newSheet.GetRow (i ).CreateCell (j ).SetCellValue (Convert.ToDouble (data [i][j ].ToString ())) ;//写入数据
					}
				}
				newBook .Write (fs ) ;
				return true ;
			}
			catch (Exception err)
			{
				throw new Exception ("转换数据到Excel失败："+err.Message ) ;
			}
			finally
			{
				fs.Close () ;
			}
		}
		
		/// <summary>
		/// 将数据集导出到Excel文件
		/// </summary>
		/// <param name="dt">DataTable对象</param>
		/// <param name="xlsSaveFileName">Excel文件名称</param>
		/// <param name="sheetName">工作簿名称</param>
		/// <returns>是否转换成功</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 ) ;//新建工作簿
				for (int i = 0 ; i <dt.Rows.Count  ; i ++ )
				{
					HSSFRow  newRow = (HSSFRow )newSheet.CreateRow(i ) ;//创建行
					for (int j = 0 ; j <dt.Columns.Count ; j ++ )
					{
						newSheet.GetRow (i ).CreateCell (j ).SetCellValue (dt.Rows [i ][j].ToString ()) ;//写入数据
					}
				}
				newBook .Write (fs ) ;
				return true ;
			}
			catch (Exception err)
			{
				throw new Exception ("转换数据到Excel失败："+err.Message ) ;
			}
			finally
			{
				fs.Close () ;
			}
		}
		
		/// <summary>
		/// 将数据集导出到Excel文件
		/// </summary>
		/// <param name="dt">DataGridView对象</param>
		/// <param name="xlsSaveFileName">Excel文件名称</param>
		/// <param name="sheetName">工作簿名称</param>
		/// <returns>是否转换成功</returns>
		public static bool ConvertToExcel(System.Windows.Forms.DataGridView dgv, string xlsSaveFileName,string sheetName)
		{
			return ConvertToExcel((System.Data.DataTable )dgv.DataSource  ,xlsSaveFileName,sheetName )  ;
		}

下面是导入数据到DataTable:

Code/// <summary>
		/// 将数据导出到DataTable中
		/// </summary>
		/// <param name="data">二维数组数据</param>
		/// <param name="columnsName">列名</param>
		/// <returns>DataTable对象</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 ("列名长度不足");
			}
			try
			{
				//先添加列名
				for (int i = 0 ; i <data.GetLength (1) ; i ++)
				{
					DataColumn dc = new DataColumn (columnsName [i ],typeof (System.String ) ) ;
					dt.Columns.Add (dc ) ;
				}
				//添加数据
				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 ("转换数据到DataTable失败："+err.Message ) ;
			}
		}
		
		/// <summary>
		/// 将数据导出到DataTable中
		/// </summary>
		/// <param name="data">二维数组数据</param>
		/// <returns>DataTable对象</returns>
		public static System.Data.DataTable ConvertToDataTable<T>(T[,] data )
		{
			System.Data.DataTable dt = new System.Data.DataTable () ;
			try
			{
				//先添加列名
				for (int i = 0 ; i <data.GetLength (1) ; i ++)
				{
					DataColumn dc = new DataColumn() ;
					dt.Columns.Add (dc ) ;
				}
				//添加数据
				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 ("转换数据到DataTable失败："+err.Message ) ;
			}
		}
		/// <summary>
		/// 将数据导出到DataTable中
		/// </summary>
		/// <param name="data">交错数组数据</param>
		/// <param name="columnsName">列名</param>
		/// <returns>DataTable对象</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 ("列名长度不足");
			}
			try
			{
				//先添加列名
				for (int i = 0 ; i <data[0].Length  ; i ++)
				{
					DataColumn dc = new DataColumn (columnsName [i ],typeof (System.String ) ) ;
					dt.Columns.Add (dc ) ;
				}
				//添加数据
				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 ("转换数据到DataTable失败："+err.Message ) ;
			}
		}
		
		/// <summary>
		/// 将数据导出到DataTable中
		/// </summary>
		/// <param name="data">交错数组数据</param>
		/// <returns>DataTable对象</returns>
		public static System.Data.DataTable ConvertToDataTable<T>(T[][] data)
		{
			System.Data.DataTable dt = new System.Data.DataTable () ;
			try
			{
				//先添加列名
				for (int i = 0 ; i <data[0].Length  ; i ++)
				{
					DataColumn dc = new DataColumn ( ) ;
					dt.Columns.Add (dc ) ;
				}
				//添加数据
				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 ("转换数据到DataTable失败："+err.Message ) ;
			}
		}