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

hanufo 2010-01-28 04:56:20
如题
...全文
263 15 打赏 收藏 转发到动态 举报
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
vip__888 2010-01-29
  • 打赏
  • 举报
 回复
把excel当做数据源
然后读取内容保存在dataset中
然后遍历dataset 插入access
ok 结束
chxg99 2010-01-29
  • 打赏
  • 举报
 回复
学习+帮顶
lertulo 2010-01-29
  • 打赏
  • 举报
 回复 


        /// <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);

            }



        }


jack15850798154 2010-01-28
  • 打赏
  • 举报
 回复
http://blog.csdn.net/glumtree/archive/2007/12/06/1921189.aspx
tnt278301967 2010-01-28
  • 打赏
  • 举报
 回复
卖个广告 自己写的源代码~http://download.csdn.net/source/2030607
Excel与access的互导 还能指定行列和加密
mxc1225 2010-01-28
  • 打赏
  • 举报
 回复
[Quote=引用 8 楼 hanufo 的回复:]
是不是需要添加什么引用啊
[/Quote]
不需要！oledb很方便的！？
hanufo 2010-01-28
  • 打赏
  • 举报
 回复
是不是需要添加什么引用啊
jack15850798154 2010-01-28
  • 打赏
  • 举报
 回复
/// <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;
}
jack15850798154 2010-01-28
  • 打赏
  • 举报
 回复
oledb就可以了。。。通用的。。。别的和操作 sql server差不多。。。
abaochan 2010-01-28
  • 打赏
  • 举报
 回复
晕！
那还不是读数据写数据啊！？
读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;'”

AnfyChan 2010-01-28
  • 打赏
  • 举报
 回复

//定义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();
}
}
hanufo 2010-01-28
  • 打赏
  • 举报
 回复
嗯，有没有完整的代码
xiaofeiVSmugua 2010-01-28
  • 打赏
  • 举报
 回复
/// <summary>
/// 将Excel文件上传到服务器
/// </summary>
public string GetFied()
{
string Road = "";
try
{
string Name = this.fileUp.FileName.ToString();
string FileName = Name.Substring(Name.LastIndexOf(".") + 1);
if (FileName != "xls")
{
MessageBox.Show(this, "您上传的不是Excel文件!\\n");
return null;
}
//设置上传路径奖文件保存到服务器
string datetime = DateTime.Now.Date.ToString("yyyyMMdd");
string time = DateTime.Now.ToShortTimeString().Replace(":", "");
string NewFileName = "Excel" + datetime + time + DateTime.Now.Millisecond.ToString() + ".xls";//生成新文件名
Road = Server.MapPath("appFile") + "\\" + NewFileName;
this.fileUp.PostedFile.SaveAs(Road);
return Road;

}
catch (Exception)
{
MessageBox.Show(this,"数据导入错误,请重新导入!\\n");
}
return Road;
}
protected void btnUp_Click(object sender, EventArgs e)
{
GetFied();
string path = GetFied();
runwork(path);
}
public DataSet ExcelTables(string excelPath)
{
//创建一个数据链接
string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + excelPath + ";Extended Properties=\"Excel 8.0;IMEX=1\";Persist Security Info=False ";
OleDbConnection myConn = new OleDbConnection(strCon);
string strCom = " SELECT * FROM [Sheet1$] ";
myConn.Open();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
DataSet myDataSet = new DataSet();
myCommand.Fill(myDataSet, "[Sheet1$]");
myConn.Close();
return myDataSet;
}
public void runwork(string Road)
{
ihs.Model.GP_CUSUER userModel = new ihs.Model.GP_CUSUER();
try
{
if (Request.QueryString["sgc_id"] != null && Request.QueryString["AdminID"] != "")
{
int sgcid = Convert.ToInt32(Request.QueryString["sgc_id"]);
DataSet ds = ExcelTables(Road);
for (int i = 0; i < ds.Tables.Count; i++)
{
DataTable dtable = ds.Tables[i];
userModel.SGC_ID = sgcid;//项目编号
userModel.GPID = SGC_GROUPID;//集团编号
for (int j = 0; j < dtable.Rows.Count; j++)
{
if (dtable.Rows.Count < 0)
{
MessageBox.Show(this, "没有数据!\\n");
return;
}
else
{
userModel .SGC_NAME = dtable.Rows[j]["项目名称"].ToString();
userModel.CUSTNAME = dtable.Rows[j]["客户姓名"].ToString();
userModel.CUSTPHONE = dtable.Rows[j]["客户电话"].ToString();
userModel.CUSTINTER = dtable.Rows[j]["客户爱好"].ToString();
userModel.BIRTHDAY = dtable.Rows[j]["客户生日"].ToString();
userModel.CUSTDESC = dtable.Rows[j]["其他信息"].ToString();
userModel.DUTY = dtable.Rows[j]["客户职务"].ToString();
userModel.GPNAME = dtable.Rows[j]["集团名称"].ToString();
userModel.ARPU = Convert.ToInt32(dtable.Rows[j]["ARPU值"].ToString());
userModel.INCOME = Convert.ToInt32(dtable.Rows[j]["总收入"].ToString());
userModel.INTERINCOME = Convert.ToInt32(dtable.Rows[j]["上网总收入"].ToString());
userModel.TALKTIME = Convert.ToInt32(dtable.Rows[j]["通话时长"].ToString());
userModel.ARREARS = Convert.ToInt32(dtable.Rows[j]["欠费金额"].ToString());
if (dtable.Rows[j]["客户类型"].ToString() == "普通客户")
{
userModel.CUSTTYPE = 0;
}
else
{
userModel.CUSTTYPE = 1;
}
bool sgc = bll.GetUser(sgcid, userModel.CUSTNAME, userModel.GPNAME);
if (sgc == true)
{
bll.GetUpdateUser(userModel);
}
else
{
bll.Add(userModel);
DataSet dsUser = bll.GetUserNum(sgcid);
if (dsUser.Tables[0].Rows.Count > 0)
{
int useuNum = Convert.ToInt32(dsUser.Tables[0].Rows[0]["FACT_GROUPID"].ToString());
int arup = Convert.ToInt32(dsUser.Tables[0].Rows[0]["FACT_GROUPADDARPU"].ToString());
int factNum = useuNum + 1;
int factArup = arup +Convert.ToInt32( userModel.ARPU);
bll.GetUpdate(sgcid, factArup, factNum);
MessageBox.ShowAndRedirect(this, "导入成功!\\n", "GrowItmes.aspx");
}
}
}
}
}
}
}
catch (Exception)
{
MessageBox.Show(this,"导入数据失败,请重新导入!\\n");
}
}

这是我批量导入用户的代码。只是存的是oracle数据库.但是都差不多。
mxc1225 2010-01-28
  • 打赏
  • 举报
 回复
晕！
那还不是读数据写数据啊！？
读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语句！
C# 操作 Excel 表 和 Access数据库 的范例（用 VS2010 写的）
具体是：用 C# 操作 Excel 表 和 Access数据库，含批量导出、批量导入、批量合并excel、批量打印等功能，范例代码，绝对基础。 共 6 个基础的范例（绝对基础的代码）： ① 一键合并多个Excel表； ② 把excel表快速...
ExcelDataReader开源的读取Excel文件组件net35.7z
开源的读取Excel文件组件，轻量级解析DLL，net3.5 无需自己再编译，直接导入引用： FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read); //1. Reading from a binary Excel file ('97-2003...
asp获取微信公众号网页授权和用户信息（code、access_token、openid等）
讲解用户在微信客户端中访问第三方网页，公众号可以通过微信网页授权机制，来获取用户基本信息，进而实现业务逻辑。 获取code、access_token、openid、用户昵称、地区、性别、头像等 官方文档 ...
DotNet读取excel与access操作结合
用C#编写的winform，读取指定格式的excel文件，将文件内容存进access数据库，针对数据库的增删改查操作，针对汉字的首字母获取
C# 实现Excel 与Access 数据库之间的导入导出
文件为实现源码，内容详细，适合学习和解燃眉之急。
C#

110,561

社区成员

642,566

社区内容

发帖
与我相关
我的任务
社区描述
.NET技术 C#
社区管理员
  • C#
  • Web++
  • by_封爱
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告

让您成为最强悍的C#开发者

试试用AI创作助手写篇文章吧

+ 用AI写文章