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;
        }
posted @ 2020-01-03 08:14  码农阿亮  阅读(5340)  评论(3编辑  收藏  举报