Shulou(Shulou.com)06/01 Report--

Today, I would like to share with you the relevant knowledge points about how to convert EXCEL data into DataTable in C#. The content is detailed and the logic is clear. I believe most people still know too much about this knowledge, so share this article for your reference. I hope you can get something after reading this article.

Realizing the conversion of EXCEL form to DataTable by C #

The C # code implementation converts the Excel file into DataTable, which is implemented in different ways according to the suffix name of the Excel file. The following is achieved according to the two suffixes of the Excel file (* .xlsx and * .xls). The method to get the suffix name of a file is: Path.GetExtension (fileName) method, and the following code is implemented by referencing: using System.IO;: (the filename appearing in the following code is all absolute paths with drive characters)

The main method called according to the suffix name of the Excel file

Private DataTable FileToDataTable (string fileName) {DataTable dt = new DataTable (); string extendName = Path.GetExtension (fileName); / / get the suffix 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; iTunes +) {DataColumn column = new DataColumn (hssfrow.GetCell (I) .StringCellValue); dataTable.Columns.Add (column);} dataTable.TableName = hssfsheet.SheetName; int lastRowNum = hssfsheet.LastRowNum / / after the column name, populate the data for (int I = hssfsheet.FirstRowNum + 1; I < hssfsheet.LastRowNum; iTunes +) / {HSSFRow hssfrow2 = (HSSFRow) hssfsheet.GetRow (I); DataRow dataRow = dataTable.NewRow (); for (int j = (int) hssfrow2.FirstCellNum; j < lastCellNum) from the second row of the column Hssfrow2.GetCell +) / {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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.