求C#读取excel内容，然后保存在access

Question

如题

Answer 1

晕！
那还不是读数据写数据啊！？
读excel数据
 “select * from [Sheet1$]", DBOperate.DriverType.OleDb, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + _filepathName + "';Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'”写access也是连接数据库！insert语句！

Answer 2

        //定义ReadExcel函数，用该函数从Excel表中读取数据到datable中。
        public DataSet ReadExcel(string strFileName, string sheetName)
        {            string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + strFileName + ";Extended Properties = Excel 8.0";
            OleDbConnection oleConnection = new OleDbConnection(strConnection);
            try
            {
                oleConnection.Open();
                DataSet dsRead = new DataSet();
                OleDbDataAdapter oleAdper = new OleDbDataAdapter(" SELECT * FROM [" + sheetName + "]", oleConnection);
                oleAdper.Fill(dsRead, sheetName);
                return dsRead;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return null;
            }
            finally
            {
                oleConnection.Close();
            }
        }

Answer 3

晕！ 
那还不是读数据写数据啊！？ 
读excel数据 
C# code “select * from [Sheet1$]", DBOperate.DriverType.OleDb, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + _filepathName + "';Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'”

Answer 4

oledb就可以了通用的别的和操作 sql server差不多

Answer 5

 /// <summary>
    /// 导入EXCEL表
    /// </summary>
    /// <param name="strFileName"></param>
    /// <returns></returns>
    public DataSet CreateDataSource(string strFileName, string Sqlstr)
    {
        string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFileName + "; Extended Properties=Excel 8.0;";
        OleDbConnection olecon = new OleDbConnection(strCon);
        OleDbDataAdapter myda = new OleDbDataAdapter(Sqlstr, olecon);
        DataSet myds = new DataSet();
        myda.Fill(myds);
        return myds;
    }

Answer 6

卖个广告  自己写的源代码~http://download.csdn.net/source/2030607
Excel与access的互导  还能指定行列和加密

Answer 7

http://blog.csdn.net/glumtree/archive/2007/12/06/1921189.aspx

Answer 8

把excel当做数据源
然后读取内容保存在dataset中
然后遍历dataset 插入access
ok 结束

Answer 9

        /// <summary>
        /// 從Excel檔固定格式導入資料。
        /// </summary>
        /// <param name="FileName">Excel File</param>
        /// <param name="SheetName">Sheet Name</param>
        public void LoadFormSheet(String FileName, String SheetName)
        {
            if (FileName.Trim().Length == 0 || SheetName.Trim().Length == 0)
            {
                throw new Exception("無效的文件名或Sheet名稱！");
            }
            else
            {
                Excel.ApplicationClass xls = new Excel.ApplicationClass();
                xls.Workbooks.Open(FileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);                Excel.Worksheet wsheet = (Excel.Worksheet)xls.Worksheets[SheetName];                Array value = (Array)wsheet.UsedRange.Value2;                Excel.Range range = wsheet.UsedRange.Find("Sampling Size", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSearchDirection.xlNext, Missing.Value, Missing.Value, Missing.Value);                point p = new point();
                p.x = range.Row + 3 ;
                p.y = range.Column;                try
                {
                    SplitHeader(value);
                    SplitLines(value, p);                    //備用欗位1保存 SHEET名稱
                    this.Value0 = SheetName;                }
                catch (Exception Err)
                {
                    throw Err;
                }
                finally
                {
                    xls.Quit();
                }            }
        }        /// <summary>
        /// 將讀取到的 單頭 內容提取出來。
        /// </summary>
        /// <param name="al">Excel單獨一個Sheet的全部內容。</param>
        private void SplitHeader(Array al)
        {            this.RO = al.GetValue(4, 3).ToString();
            this.SO = al.GetValue(5, 3).ToString();            //供應商
            this.Customer1 = al.GetValue(6, 3).ToString();
            //制造商
            //this.Customer2               //原材料名稱
            this.Mtl_Type = al.GetValue(6, 5).ToString();            //收貨日期
            if (al.GetValue(7, 3).ToString().IndexOf('.') == -1)
                this.Receive_Date = DateTime.FromOADate(double.Parse(al.GetValue(7, 3).ToString())).ToString("yyyy/MM/dd");
            else
                this.Receive_Date = DateTime.Parse(al.GetValue(7, 3).ToString()).ToString("yyyy/MM/dd");            //this.Receive_Date =  DateTime.FromOADate(double.Parse(al.GetValue(7, 3).ToString())).ToString();
            //批次
            this.Lot = al.GetValue(8, 3).ToString();
            // Rank
            this.Rank = al.GetValue(8, 5).ToString();
            //檢驗日期
            
            if (al.GetValue(9, 3).ToString().IndexOf('.') == -1)
                Inspect_Date = DateTime.FromOADate(double.Parse(al.GetValue(9, 3).ToString())).ToString("yyyy/MM/dd");
            else
                Inspect_Date = DateTime.Parse(al.GetValue(9, 3).ToString()).ToString("yyyy/MM/dd");            //檢驗結果
            this.Result = al.GetValue(9, 5).ToString();            //數量
            this.Quantity = int.Parse(al.GetValue(10, 3).ToString());                        //包裝不良
            //this.Packing_NG
            //標簽與實物不符
            //this.Label_NG
            //出貨報告錯誤
            //this.Report_NG
            //內部ICP編號
            //this.IPC_Report_yageo
            //外部ICP編號
            //this.IPC_Report_Extra            this.Key = DateTime.Now.ToString("yyyyMMddHHmmssffff");        }
        /// <summary>
        /// 將讀取到的 單身 內容提取出來。
        /// </summary>
        /// <param name="al">Excel單獨一個Sheet的全部內容。</param>
        private void SplitLines(Array al,point p)
        {
            int column = p.y;
            //int len = 3;            for (int i = p.x + 1; i <= p.x + 20; ++i)
            {
                QCLine line = new QCLine();
                line.Index = i - p.x;                line.W = al.GetValue(i, column + 2) == null ? "" : al.GetValue(i, column + 2).ToString();
                line.L = al.GetValue(i, column + 3) == null ? "" : al.GetValue(i, column + 3).ToString();
                line.W1 = al.GetValue(i, column + 4) == null ? "" : al.GetValue(i, column + 4).ToString();
                line.W2 = al.GetValue(i, column + 5) == null ? "" : al.GetValue(i, column + 5).ToString();
                line.WP = al.GetValue(i, column + 6) == null ? "" : al.GetValue(i, column + 6).ToString();
                line.L1 = al.GetValue(i, column + 7) == null ? "" : al.GetValue(i, column + 7).ToString();
                line.L2 = al.GetValue(i, column + 8) == null ? "" : al.GetValue(i, column + 8).ToString();
                line.LP = al.GetValue(i, column + 9) == null ? "" : al.GetValue(i, column + 9).ToString();
                line.R = al.GetValue(i, column + 10) == null ? "" : al.GetValue(i, column + 10).ToString();
                line.Thickness = al.GetValue(i, column + 11) == null ? "" : al.GetValue(i, column + 11).ToString();
                line.T1 = al.GetValue(i, column + 12) == null ? "" : al.GetValue(i, column + 12).ToString();
                line.T2 = al.GetValue(i, column + 13) == null ? "" : al.GetValue(i, column + 13).ToString();
                line.RW = al.GetValue(i, column + 14) == null ? "" : al.GetValue(i, column + 14).ToString();
                line.RW1 = al.GetValue(i, column + 15) == null ? "" : al.GetValue(i, column + 15).ToString();
                line.RW2 = al.GetValue(i, column + 16) == null ? "" : al.GetValue(i, column + 16).ToString();
                line.RL = al.GetValue(i, column + 17) == null ? "" : al.GetValue(i, column + 17).ToString();
                line.RL1 = al.GetValue(i, column + 18) == null ? "" : al.GetValue(i, column + 18).ToString();
                line.RL2 = al.GetValue(i, column + 19) == null ? "" : al.GetValue(i, column + 19).ToString();
                line.WW1 = al.GetValue(i, column + 20) == null ? "" : al.GetValue(i, column + 20).ToString();
                line.WW2 = al.GetValue(i, column + 21) == null ? "" : al.GetValue(i, column + 21).ToString();
                line.WWW = al.GetValue(i, column + 22) == null ? "" : al.GetValue(i, column + 22).ToString();
                line.W = format(line.W);
                line.L = format(line.L);
                line.W1 = format(line.W1);
                line.W2 = format(line.W2);
                line.WP = format(line.WP);
                line.L1 = format(line.L1);
                line.L2 = format(line.L2);
                line.LP = format(line.LP);
                line.R = format(line.R);
                line.Thickness = format(line.Thickness);
                line.T1 = format(line.T1);
                line.T2 = format(line.T2);
                line.RW = format(line.RW);
                line.RW1 = format(line.RW1);
                line.RW2 = format(line.RW2);
                line.RL = format(line.RL);
                line.RL1 = format(line.RL1);
                line.RL2 = format(line.RL2);
                line.WW1 = format(line.WW1);
                line.WW2 = format(line.WW2);
                line.WWW = format(line.WWW);
                if (line.W == "0.000")
                    return;                //line.WP = decimal.Round(line.WP, len);
                //line.LP = decimal.Round(line.LP, len);
                //line.R = decimal.Round(line.R, len);
                //line.WWW = decimal.Round(line.WWW, len);
                                this.Lines.Add(line);
            }        }