将Excel导入到数据中

常用的方式的有两种:

1. 通过 Microsoft.Jet.OLEDB.4.0 或  Microsoft.ACE.OLEDB.12.0

Microsoft.ACE.OLEDB.12.0 需要安装 AccessDatatbaseEngine

http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe

 

public DataTable ExcelToDataTable(string strExcelFileName, string strSheetName)
{
    string path = Server.MapPath(".")+"\\Common\\"+strExcelFileName;
    string conn1 = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + 
                    ";Extended Properties=Excel 8.0;";
    string conn2 = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + 
                   ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";
    string strExcel = string.Format("select * from [{0}$]", strSheetName);
    DataSet ds = new DataSet();
    using (OleDbConnection conn = new OleDbConnection(conn2))
    {
        conn.Open();
        OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, conn);
        adapter.Fill(ds, strSheetName);
        conn.Close();
        return ds.Tables[strSheetName];
    }
}
//调用方式: DataTable dt =   ExcelToDataTable("Report.xls","list");

 

 

2.通过  NPOI 2.1.1

public DataTable ExcelToDataTable(string strFileName, int sheetIndex = 0)
{
    string path = Server.MapPath(".") + "\\Common\\" + strFileName;
    DataTable dt = new DataTable();
    HSSFWorkbook hssfworkbook = null;
    XSSFWorkbook xssfworkbook = null;
    string fileExt = Path.GetExtension(path);
    using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
    {
        if (fileExt == ".xls")
            hssfworkbook = new HSSFWorkbook(file);
        else if (fileExt == ".xlsx")
            xssfworkbook = new XSSFWorkbook(file);
    }
    if (hssfworkbook != null)
    {
        HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheetIndex);
        if (sheet != null)
        {
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
            HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
            int cellCount = headerRow.LastCellNum;
            for (int j = 0; j < cellCount; j++)
            {
                HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
                dt.Columns.Add(cell.ToString());
            }
            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                HSSFRow row = (HSSFRow)sheet.GetRow(i);
                DataRow dataRow = dt.NewRow();
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                        dataRow[j] = row.GetCell(j).ToString();
                }
                dt.Rows.Add(dataRow);
            }
        }
    }
    else if (xssfworkbook != null)
    {
        XSSFSheet xSheet = (XSSFSheet)xssfworkbook.GetSheetAt(sheetIndex);
        if (xSheet != null)
        {
            System.Collections.IEnumerator rows = xSheet.GetRowEnumerator();
            XSSFRow headerRow = (XSSFRow)xSheet.GetRow(0);
            int cellCount = headerRow.LastCellNum;
            for (int j = 0; j < cellCount; j++)
            {
                XSSFCell cell = (XSSFCell)headerRow.GetCell(j);
                dt.Columns.Add(cell.ToString());
            }
            for (int i = (xSheet.FirstRowNum + 1); i <= xSheet.LastRowNum; i++)
            {
                XSSFRow row = (XSSFRow)xSheet.GetRow(i);
                DataRow dataRow = dt.NewRow();
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                        dataRow[j] = row.GetCell(j).ToString();
                }
                dt.Rows.Add(dataRow);
            }
        }
    }
    return dt;
}

//调用方式: DataTable dt = ExcelToDataTable("Report.xls");