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; }
本文来自博客园,作者:码农阿亮,转载请注明原文链接:https://www.cnblogs.com/wml-it/p/12133558.html
技术的发展日新月异,随着时间推移,无法保证本博客所有内容的正确性。如有误导,请大家见谅,欢迎评论区指正!
开源库地址,欢迎点亮:
GitHub:https://github.com/ITMingliang
Gitee: https://gitee.com/mingliang_it
GitLab: https://gitlab.com/ITMingliang
建群声明: 本着技术在于分享,方便大家交流学习的初心,特此建立【编程内功修炼交流群】,为大家答疑解惑。热烈欢迎各位爱交流学习的程序员进群,也希望进群的大佬能不吝分享自己遇到的技术问题和学习心得!进群方式:扫码关注公众号,后台回复【进群】。