隨手筆記

string conn = " Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source = " + location + ";Extended Properties='Excel 12.0;HDR=NO'";//連結字串中的HDR=YES，代表略過第一欄資料

OleDbConnection oledb_conn = new OleDbConnection(conn);//連線位址

oledb_conn.Open();//開啟連線

OleDbCommand oledb_com = new OleDbCommand("select * from [工作表1$]", oledb_conn);//搜尋資料

OleDbDataReader oledb_dr = oledb_com.ExecuteReader();//讀取
int i = 0;
while (oledb_dr.Read())//連線狀態
{

if (i > 1)
{
break;
}
Console.WriteLine(oledb_dr[0].ToString());
i++;
}

public DataTable RenderDataTableFromExcel(string fullPath)
    {
        string connStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";
        connStr = string.Format(connStr, fullPath);
        string sheetName = null;
        DataTable sheetNames = default(DataTable);
        OleDbConnection conn = default(OleDbConnection);

        conn = new OleDbConnection(connStr);
        conn.Open();
        sheetNames = conn.GetSchema("Tables");
        if (sheetNames.Rows.Count > 0)
            sheetName = sheetNames.Rows[0]["TABLE_NAME"].ToString();
        sheetNames.Clear();
        sheetNames.Dispose();

        OleDbCommand cmd = new OleDbCommand(string.Format("SELECT * FROM [{0}]", sheetName), conn);
        OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
        DataTable dt = new DataTable();
        adapter.Fill(dt);

        conn.Close();
        return dt;
    }

public DataTable GetDataTableFromExcelFile(string fileName)
    {
        FileStream fs = null;
        DataTable dt = new DataTable();
        try
        {
            IWorkbook wb = null;
            fs = File.Open(fileName, FileMode.Open, FileAccess.Read);
            switch (Path.GetExtension(fileName).ToUpper())
            {
                case ".XLS":
                    {
                        wb = new HSSFWorkbook(fs);
                    }
                    break;
                case ".XLSX":
                    {
                        wb = new XSSFWorkbook(fs);
                    }
                    break;
            }
            if (wb.NumberOfSheets > 0)
            {
                ISheet sheet = wb.GetSheetAt(0);
                IRow headerRow = sheet.GetRow(2);

                //處理標題列
                for (int i = headerRow.FirstCellNum ; i < headerRow.LastCellNum; i++)
                {
                    dt.Columns.Add(headerRow.GetCell(i).StringCellValue.Trim());
                }
                IRow row = null;
                DataRow dr = null;
                CellType ct = CellType.Blank;
                //標題列之後的資料

                

                for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    dr = dt.NewRow();
                    row = sheet.GetRow(i);
                    
                    if (row == null) continue;
                    for (int j = row.FirstCellNum; j < row.LastCellNum; j++)//row.LastCellNum
                    {
                        try
                        {
                            ct = row.GetCell(j).CellType;
                            //如果此欄位格式為公式 則去取得CachedFormulaResultType
                            if (ct == CellType.Formula)
                            {
                                ct = row.GetCell(j).CachedFormulaResultType;
                            }
                            if (ct == CellType.Numeric)
                            {
                                if (i == sheet.LastRowNum && j == 4)
                                {
                                    NumberFormatInfo nfi = new CultureInfo("en-US", false).NumberFormat;
                                    dr[j] = row.GetCell(j).NumericCellValue.ToString("P", nfi);
                                }
                                else
                                {
                                    dr[j] = row.GetCell(j).NumericCellValue;
                                }
                            }
                            else
                            {
                                dr[j] = row.GetCell(j).ToString().Replace("$", "");
                            }
                            
                        }
                        catch(Exception e)
                        {
                            
                        }
                    }
                    dt.Rows.Add(dr);
                }
            }
            fs.Close();
        }
        finally
        {
            if (fs != null) fs.Dispose();
        }
        return dt;
    }