读取Excel C# 及 java

1.C# oledb方式 此种方式需要测试环境与生产环境都安装注册有access数据库

  /// <summary>
        /// OLEDB方式读取Excel 
        /// </summary>
        /// <param name="pathName">Excel路径</param>
        /// <param name="sheetName">工作表名</param>
        /// <returns></returns>
        public DataTable ExcelToDataTable(string fileUrl)
        {
            //支持.xls和.xlsx，即包括office2010等版本的   HDR=Yes代表第一行是标题，不是数据；
            const string cmdText = "Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
            DataTable dt = null;
            //建立连接
            OleDbConnection conn = new OleDbConnection(string.Format(cmdText, fileUrl));
            try
            {
                //打开连接
                if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                //获取Excel的第一个Sheet名称
                string sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim();
                //查询sheet中的数据
                //string strSql = "select * from [" + sheetName + "]" +"where idx not in (select top "+"2" + "from [" + sheetName + "])";
                string strSql = "select * from [" + sheetName + "] ";
                OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dt = ds.Tables[0];
                //for (int i = 0; i < dt.Columns.Count; i++)
                //{
                //     dt.Columns[i].ColumnName = dt.Rows[0][i].ToString();
                //}
                //dt.Rows.RemoveAt(0);
                return dt;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }

2. C# NPOI方式读取excel 返回datatable

SaveFileAddress 为服务器绝对路径

 public DataTable ReadExcel(string SaveFileAddress,string FileName) {
            //创建文件流
            FileStream fileStream = new FileStream(SaveFileAddress, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
            try
            {
                string filetype = FileName.Split('.')[1];
                ISheet sheet = null;
                if (filetype == "xls")
                {
                    HSSFWorkbook HSSF = new HSSFWorkbook(fileStream);
                    sheet = HSSF.GetSheetAt(0);
                }
                else
                {
                    XSSFWorkbook XSSF = new XSSFWorkbook(fileStream);
                    sheet = XSSF.GetSheetAt(0);
                }
                //创建容器
                DataTable dt = new DataTable();
                //动态创建列
                for (int i = 0; i < sheet.GetRow(1).LastCellNum; i++)
                {
                    DataColumn dc = new DataColumn("T" + i, Type.GetType("System.String"));
                    dt.Columns.Add(dc);
                }
                //动态添加数据
                for (int i = 1; i < sheet.LastRowNum + 1; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dr = dt.NewRow();
                    int count = 0;
                    for (int j = 0; j < row.LastCellNum; j++)
                    {
                        string a = row.GetCell(j)==null?"": row.GetCell(j).ToString();
                        if (string.IsNullOrEmpty(a))
                        {
                            count++;
                        }
                        dr[j] = a;
                    }
                    if(count!= row.LastCellNum)
                    dt.Rows.Add(dr);
                }
                return dt;
            }
            catch (Exception ex)
            {
                DataTable dt = new DataTable();
                return dt;
            }
            finally {
                fileStream.Close();
            }
        }

3.java即将更新