.NET对Excel的读写
来源:互联网 发布:部门考核数据流程图 编辑:程序博客网 时间:2024/05/16 00:49
在.net中读Excel可以通过两种方式读取
1.以数据库的方式,这种方式简单明了,推荐。
参考代码如下:
注意修改两个地方Data Source=C:/Book.xls指Excel文件的位置,string.Format("select * from [{0}$]", p_tagName)中p_tagName指Sheet标签页的名称
Code
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->//构建连接字符串
string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Book.xls;Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
OleDbConnection Conn = new OleDbConnection(ConnStr);
Conn.Open();
//填充数据
string sql = string.Format("select * from [{0}$]", p_tagName);
OleDbDataAdapter da = new OleDbDataAdapter(sql, ConnStr);
da.Fill(ds);
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->//构建连接字符串
string ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Book.xls;Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
OleDbConnection Conn = new OleDbConnection(ConnStr);
Conn.Open();
//填充数据
string sql = string.Format("select * from [{0}$]", p_tagName);
OleDbDataAdapter da = new OleDbDataAdapter(sql, ConnStr);
da.Fill(ds);
2.以OLE方式读取
Code
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->//读取excel
int indexSheet = 1;
Excel.ApplicationClass Myexcel = new Excel.ApplicationClass();
if (Myexcel == null)
{
MessageBox.Show("EXCEL无法启动!", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
Myexcel.Visible = true;
Myexcel.UserControl = true;
Excel.Workbooks Myworkbooks = Myexcel.Workbooks;
Excel.Workbook Myworkbook = Myworkbooks.Open(@"C:\Book.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Worksheet Myworksheet = (Excel.Worksheet)Myworkbook.Worksheets[indexSheet];
Excel.Range rng3 = Myworksheet.get_Range("E1",Missing.Value);
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->//读取excel
int indexSheet = 1;
Excel.ApplicationClass Myexcel = new Excel.ApplicationClass();
if (Myexcel == null)
{
MessageBox.Show("EXCEL无法启动!", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
Myexcel.Visible = true;
Myexcel.UserControl = true;
Excel.Workbooks Myworkbooks = Myexcel.Workbooks;
Excel.Workbook Myworkbook = Myworkbooks.Open(@"C:\Book.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Worksheet Myworksheet = (Excel.Worksheet)Myworkbook.Worksheets[indexSheet];
Excel.Range rng3 = Myworksheet.get_Range("E1",Missing.Value);
最后通过rng3的属性Value2可以获取到指定Cell的值
在.net中写Excel可以通过两种方式读取
1.通过数据库方式批量更新
2.通过OLE方式更新
从以下片段可以看出和读取没有什么区别
Code
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->if (sBhName.IndexOf(infoArr[0]) >= 0 || sDmisName.IndexOf(infoArr[0]) >= 0)
{
Excel.Range rng3 = null;
bEmsByqId = GetNumberInt(infoArr[3]);
if (bEmsByqId == bDmisByqId)
{
rng3 = Myworksheet.get_Range(string.Format("E{0}", Convert.ToString(i + 1)), Missing.Value);
rng3.Value2 = infoArr[1];
rng3.Interior.ColorIndex = 4; //设置Range的背景色
rng3 = Myworksheet.get_Range(string.Format("F{0}", Convert.ToString(i + 1)), Missing.Value);
rng3.Value2 = infoArr[0];
rng3.Interior.ColorIndex = 6; //设置Range的背景色
rng3 = Myworksheet.get_Range(string.Format("G{0}", Convert.ToString(i + 1)), Missing.Value);
rng3.Value2 = infoArr[3];
rng3.Interior.ColorIndex = 8; //设置Range的背景色
}
}
Myworksheet.SaveAs(@"C:\date.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value);
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
-->if (sBhName.IndexOf(infoArr[0]) >= 0 || sDmisName.IndexOf(infoArr[0]) >= 0)
{
Excel.Range rng3 = null;
bEmsByqId = GetNumberInt(infoArr[3]);
if (bEmsByqId == bDmisByqId)
{
rng3 = Myworksheet.get_Range(string.Format("E{0}", Convert.ToString(i + 1)), Missing.Value);
rng3.Value2 = infoArr[1];
rng3.Interior.ColorIndex = 4; //设置Range的背景色
rng3 = Myworksheet.get_Range(string.Format("F{0}", Convert.ToString(i + 1)), Missing.Value);
rng3.Value2 = infoArr[0];
rng3.Interior.ColorIndex = 6; //设置Range的背景色
rng3 = Myworksheet.get_Range(string.Format("G{0}", Convert.ToString(i + 1)), Missing.Value);
rng3.Value2 = infoArr[3];
rng3.Interior.ColorIndex = 8; //设置Range的背景色
}
}
Myworksheet.SaveAs(@"C:\date.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value);
最后不要忘记保存哦,呵呵
- .NET对Excel的读写
- .NET对Excel的读写
- .net对Excel表数据读写操作
- java对excel的读写
- POI对Excel的读写
- C#.net实现windows窗体程序对Excel文件的读写操作
- C#对EXCEL的读写操作
- java对excel文件的读写操作
- C#对EXCEL的读写操作
- QTP-实现对Excel文件的读写
- C#对Excel的读写操作
- java对Excel的读写操作
- JAVA对excel表的读写操作
- c++ 对 EXCEL 读写的封装
- poi对excel的基本读写操作
- Poi对Excel的基本读写操作
- poi对excel的基本读写操作
- python 对EXCEL的读写操作
- Jboss Weblogic Websphere 等服务器的区别
- symbian E32USER,ALLOC,KERN-EXEC,USER,CONE,WSERV错误解析
- megaminx之旅四:班级(Classes)的UI设计
- 利用SqlBulkCopy快速大批量导入数据(SqlBulkCopy强大)
- ASP.NET MVC3的验证(Validation)框架
- .NET对Excel的读写
- MS.net 开发点滴
- Centos5.6-xen4.0.3内核编译
- D-Bus入门(序)——d-bus官方介绍
- SHL测试题
- 你要做那种人
- Excel读取与创建方法三: MyXLS
- Mcafee 导致lotus notes NSD等错误
- 源代码下载网站(笔记!)