C#把EXCEL数据转换成DataTable

C#实现EXCEL表格转DataTable

C#代码实现把Excel文件转化为DataTable,根据Excel的文件后缀名不同,用不同的方法来进行实现,下面通过根据Excel文件的两种后缀名(*.xlsx和*.xls)分别来实现。获取文件后缀名的方法是:Path.GetExtension(fileName)方法,通过引用:using System.IO;实现代码如下:(其中以下代码中出现的filename都是带盘符的绝对路径)

根据Excel文件的后缀名不同调用的主方法

 	private DataTable FileToDataTable(string fileName)
 	     {
 	         DataTable dt = new DataTable();
 	         string extendName = Path.GetExtension(fileName);//获取文件的后缀名
 	         switch (extendName.ToLower())
 	         {
 	             case ".xls":
 	                 dt = XlsToDataTable(fileName);
 	                 break;
 	             case ".xlsx":
 	                 dt = XlsxToDataTable(fileName);
 	                 break;
 	             default:
 	                 break;
 	         }
 	         return dt;
 	     }

XlsToDataTable()

 	private DataTable XlsToDataTable(string fileName)
 	     {
 	         DataTable dataTable = new DataTable();
 	         Stream stream = null;
 	         try
 	         {
 	             stream = File.OpenRead(fileName);
 	             HSSFWorkbook hssfworkbook = new HSSFWorkbook(stream);
 	             HSSFSheet hssfsheet = (HSSFSheet)hssfworkbook.GetSheetAt(hssfworkbook.ActiveSheetIndex);
 	             HSSFRow hssfrow = (HSSFRow)hssfsheet.GetRow(0);
 	             int lastCellNum = (int)hssfrow.LastCellNum;
 	             for (int i = (int)hssfrow.FirstCellNum; i < lastCellNum; i++)
 	             {
 	                 DataColumn column = new DataColumn(hssfrow.GetCell(i).StringCellValue);
 	                 dataTable.Columns.Add(column);
 	             }
 	             dataTable.TableName = hssfsheet.SheetName;
 	             int lastRowNum = hssfsheet.LastRowNum;
 	             //列名后,从TABLE第二行开始进行填充数据
 	             for (int i = hssfsheet.FirstRowNum + 1; i < hssfsheet.LastRowNum; i++)//
 	             {
 	                 HSSFRow hssfrow2 = (HSSFRow)hssfsheet.GetRow(i);
 	                 DataRow dataRow = dataTable.NewRow();
 	                 for (int j = (int)hssfrow2.FirstCellNum; j < lastCellNum; j++)//
 	                 {
 	                     dataRow[j] = hssfrow2.GetCell(j);//
 	                 }
 	                 dataTable.Rows.Add(dataRow);
 	             }
 	             stream.Close();
 	         }
 	         catch (Exception ex)
 	         {
 	             ScriptManager.RegisterStartupScript(Page, GetType(), "alertForm", "alert(' Xls to DataTable: " + ex.Message + "');", true);
 	         }
 	         finally
 	         {
 	             if (stream != null)
 	             {
 	             stream.Close();
 	             }
 	         }
 	         return dataTable;
 	     }

XlsxToDataTable()

 	public DataTable XlsxToDataTable(string vFilePath)
 	     {
 	         DataTable dataTable = new DataTable();
 	         try
 	         {
 	             SLDocument sldocument = new SLDocument(vFilePath);
 	             dataTable.TableName = sldocument.GetSheetNames()[0];
 	             SLWorksheetStatistics worksheetStatistics = sldocument.GetWorksheetStatistics();
 	             int startColumnIndex = worksheetStatistics.StartColumnIndex;
 	             int endColumnIndex = worksheetStatistics.EndColumnIndex;
 	             int startRowIndex = worksheetStatistics.StartRowIndex;
 	             int endRowIndex = worksheetStatistics.EndRowIndex;
 	             for (int i = startColumnIndex; i <= endColumnIndex; i++)
 	             {
 	                 SLRstType cellValueAsRstType = sldocument.GetCellValueAsRstType(1, i);
 	                 dataTable.Columns.Add(new DataColumn(cellValueAsRstType.GetText(), typeof(string)));
 	             }
 	             for (int j = startRowIndex + 1; j <= endRowIndex; j++)
 	             {
 	                 DataRow dataRow = dataTable.NewRow();
 	                 for (int i = startColumnIndex; i <= endColumnIndex; i++)
 	                 {
 	                     dataRow[i - 1] = sldocument.GetCellValueAsString(j, i);
 	                 }
 	                 dataTable.Rows.Add(dataRow);
 	             }
 	         }
 	         catch (Exception ex)
 	         {
 	             throw new Exception("Xlsx to DataTable: \n" + ex.Message);
 	         }
 	         return dataTable;
 	     }

到此这篇关于C#把EXCEL数据转换成DataTable的文章就介绍到这了。希望对大家的学习有所帮助,也希望大家多多支持我们。

标签

发表评论