.net 操作 EXCEL (using c# to control and access the excel)
来源:互联网 发布:java工程师高级培训 编辑:程序博客网 时间:2024/06/04 19:24
http://tech.sina.com.cn/s/s/2008-06-14/0748693243.shtml
背景:
在ATBS项目中,需要操作EXCEL 文档,本以为是OBA的应用,但其实不然。OBA是在EXCEL中嵌入.net应用插件,而我们则是需要在SCSF中操作EXCEL。
我大致调查了一下,主要发现3种方式。
1:使用Microsoft.Office.Interop.Excel,调用EXCEL COM组件,操作EXCEL文件
2:使用OleDb 操作EXCEL数据源,进而利用ADO.net。
3:使用OPEN XML,访问EXCEL zip文件并使用DOM。
实现:
1:使用.net 调用 com
////////////////////////private static Microsoft.Office.Interop.Excel.Application xApp;..............if (xApp == null)xApp = new Microsoft.Office.Interop.Excel.ApplicationClass();Microsoft.Office.Interop.Excel.Workbook xBook = null;xApp.Visible = false;try{xBook = xApp.Workbooks._Open(@"c:/待发工资.xlsx", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);Microsoft.Office.Interop.Excel.Worksheet xSheet = (Microsoft.Office.Interop.Excel.Worksheet)xBook.Sheets[1];//Excel.Worksheet xSheet=(Excel.Worksheet)xApp.ActiveSheet;//Microsoft.Office.Interop.Excel.Range rng1 = xSheet.get_Range("A1", Type.Missing); Microsoft.Office.Interop.Excel.Range cell = (Microsoft.Office.Interop.Excel.Range)xSheet.Cells[2, 1];string str = "";for (int i = 2; cell.Value2 != null; i++){str += cell.Value2.ToString() + ".";cell = (Microsoft.Office.Interop.Excel.Range)xSheet.Cells[2, i];}MessageBox.Show(str);xBook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlDoNotSaveChanges, @"c:/待发工资.xlsx", Missing.Value);xApp.Quit();}catch (Exception ex){Console.WriteLine(ex.Message.ToString());}///////////////////////
2: 使用oleDB(VSTS 2008 + EXCEL 2007)
//////////////////////OleDbConnection objConn = null;DataSet data = new DataSet();try{string strConn = @"Provider=Microsoft.ACE.OleDb.12.0;Data Source=c:/待发工资.xlsx;Extended Properties='Excel 12.0;HDR=YES'";//IMEX=1为只读//"Provider=Microsoft.Jet.OleDb.4.0;data source=c:/待发工资.xlsx;Extended Properties=""Excel 12.0;HDR=YES;IMEX=1"""; objConn = new OleDbConnection(strConn);objConn.Open();//System .Data . DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);//int SheetIndex = 0;//string tableName = schemaTable.Rows[SheetIndex][2].ToString().Trim();string strSql = "Select * From [Sheet1$]";OleDbCommand objCmd = new OleDbCommand(strSql, objConn);OleDbDataAdapter sqlada = new OleDbDataAdapter();sqlada.SelectCommand = objCmd;sqlada.Fill(data);string str = "";for (int i = 0; i < data.Tables[0].Rows[0].ItemArray.Length; i++){str += data.Tables[0].Rows[0].ItemArray[i].ToString() + ".";}MessageBox.Show(str);objConn.Close();}catch (Exception ex){objConn.Close();Console.WriteLine(ex.Message.ToString());}//////////////////////
3:使用OPEN XML (比较复杂阿)
//////////////////////private const string documentRelationshipType ="http://schemas.openxmlformats.org/officeDocument/" +"2006/relationships/officeDocument";private const string worksheetSchema ="http://schemas.openxmlformats.org/spreadsheetml/2006/main";private const string sharedStringsRelationshipType ="http://schemas.openxmlformats.org/officeDocument/" +"2006/relationships/sharedStrings";private const string sharedStringSchema ="http://schemas.openxmlformats.org/spreadsheetml/2006/main";public static Package xlPackage;private static Package OpenReadExcel(string fileName){if (xlPackage == null)xlPackage = Package.Open(fileName, FileMode.Open, FileAccess.Read);return xlPackage;}private static Package OpenWriteExcel(string fileName){if (xlPackage == null)xlPackage = Package.Open(fileName, FileMode.Open, FileAccess.ReadWrite);return xlPackage;}//////////////////////public static string XLGetCellValue(string fileName, string sheetName, string addressName){// Return the value of the specified cell. string cellValue = null;// Retrieve the stream containing the requested// worksheet's info: using (xlPackage = OpenReadExcel(fileName)){PackagePart documentPart = null;Uri documentUri = null;// Get the main document part (workbook.xml). foreach (System.IO.Packaging.PackageRelationship relationship inxlPackage.GetRelationshipsByType(documentRelationshipType)){// There should only be one document part in the package. documentUri = PackUriHelper.ResolvePartUri(new Uri("/", UriKind.Relative), relationship.TargetUri);documentPart = xlPackage.GetPart(documentUri);// There should only be one instance,// but get out no matter what. break;}if (documentPart != null){// Load the contents of the workbook. XmlDocument doc = new XmlDocument();doc.Load(documentPart.GetStream());// Create a namespace manager, so you can search.// Add a prefix (d) for the default namespace. NameTable nt = new NameTable();XmlNamespaceManager nsManager = new XmlNamespaceManager(nt);nsManager.AddNamespace("d", worksheetSchema);nsManager.AddNamespace("s", sharedStringSchema);string searchString = string.Format("//d:sheet[@name='{0}']", sheetName);XmlNode sheetNode = doc.SelectSingleNode(searchString, nsManager);if (sheetNode != null){// Get the relId attribute: XmlAttribute relationAttribute =sheetNode.Attributes["r:id"];if (relationAttribute != null){string relId = relationAttribute.Value;// First, get the relation between the// document and the sheet. PackageRelationship sheetRelation =documentPart.GetRelationship(relId);Uri sheetUri = PackUriHelper.ResolvePartUri(documentUri, sheetRelation.TargetUri);PackagePart sheetPart = xlPackage.GetPart(sheetUri);// Load the contents of the workbook. XmlDocument sheetDoc = new XmlDocument(nt);sheetDoc.Load(sheetPart.GetStream());// Next code block goes here. XmlNode cellNode = sheetDoc.SelectSingleNode(string.Format("//d:sheetData/d:row/d:c[@r='{0}']", addressName), nsManager);if (cellNode != null){// Retrieve the value. The value may be stored within// this element. If the "t" attribute contains "s", then// the cell contains a shared string, and you must look// up the value individually. XmlAttribute typeAttr = cellNode.Attributes["t"];string cellType = string.Empty;if (typeAttr != null){cellType = typeAttr.Value;}XmlNode valueNode = cellNode.SelectSingleNode("d:v", nsManager);if (valueNode != null){cellValue = valueNode.InnerText;}// Check the cell type. At this point, this code only checks// for booleans and strings individually. if (cellType == "b"){if (cellValue == "1"){cellValue = "TRUE";}else{cellValue = "FALSE";}}else if (cellType == "s"){// Go retrieve the actual string from the associated string file. foreach (System.IO.Packaging.PackageRelationshipstringRelationship indocumentPart.GetRelationshipsByType(sharedStringsRelationshipType)){// There should only be one shared string reference,// so you exit this loop immediately. Uri sharedStringsUri = PackUriHelper.ResolvePartUri(documentUri, stringRelationship.TargetUri);PackagePart stringPart = xlPackage.GetPart(sharedStringsUri);if (stringPart != null){// Load the contents of the shared strings. XmlDocument stringDoc = new XmlDocument(nt);stringDoc.Load(stringPart.GetStream());// Add the string schema to the namespace manager: nsManager.AddNamespace("s", sharedStringSchema);int requestedString = Convert.ToInt32(cellValue);string strSearch = string.Format("//s:sst/s:si[{0}]", requestedString + 1);XmlNode stringNode =stringDoc.SelectSingleNode(strSearch, nsManager);if (stringNode != null){cellValue = stringNode.InnerText;}}}}}}}return cellValue;}}return "";}/////////////////////public static bool XLInsertNumberIntoCell(string fileName, string sheetName, string addressName, string value){// Retrieve the stream containing the requested// worksheet's info: PackagePart documentPart = null;Uri documentUri = null;bool returnValue = false;XmlDocument xDoc = null;XmlDocument doc = null;PackagePart sheetPart = null;using (xlPackage = OpenWriteExcel(fileName)){// Get the main document part (workbook.xml). foreach (System.IO.Packaging.PackageRelationship relationshipin xlPackage.GetRelationshipsByType(documentRelationshipType)){// There should only be one document part in the package. documentUri = PackUriHelper.ResolvePartUri(new Uri("/",UriKind.Relative), relationship.TargetUri);documentPart = xlPackage.GetPart(documentUri);// There should only be one instance,// but get out no matter what. break;}// Code removed here. if (documentPart != null){// Load the contents of the workbook. doc = new XmlDocument();doc.Load(documentPart.GetStream());// Create a NamespaceManager to handle the default namespace,// and create a prefix for the default namespace: XmlNamespaceManager nsManager =new XmlNamespaceManager(doc.NameTable);nsManager.AddNamespace("d", doc.DocumentElement.NamespaceURI);// Code removed here string searchString =string.Format("//d:sheet[@name='{0}']", sheetName);XmlNode sheetNode = doc.SelectSingleNode(searchString, nsManager);if (sheetNode != null){// Get the relId attribute: XmlAttribute relationAttribute = sheetNode.Attributes["r:id"];if (relationAttribute != null){string relId = relationAttribute.Value;// First, get the relation between the document and the sheet. PackageRelationship sheetRelation =documentPart.GetRelationship(relId);Uri sheetUri = PackUriHelper.ResolvePartUri(documentUri, sheetRelation.TargetUri);sheetPart = xlPackage.GetPart(sheetUri);// Load the contents of the sheet into an XML document. xDoc = new XmlDocument();xDoc.Load(sheetPart.GetStream());// Code removed here.// Use regular expressions to get the row number.// If the parameter wasn't well formed, this code// will fail: System.Text.RegularExpressions.Regex r =new System.Text.RegularExpressions.Regex(@"^(?<col>/D+)(?<row>/d+)");string rowNumber = r.Match(addressName).Result("${row}");// Search for the existing cell: XmlNode cellnode = xDoc.SelectSingleNode(string.Format("//d:sheetData/d:row/d:c[@r='{0}']", addressName),nsManager);if (cellnode == null){// Code removed here. XmlElement cellElement = xDoc.CreateElement("c", worksheetSchema);cellElement.Attributes.Append(xDoc.CreateAttribute("r"));cellElement.Attributes["r"].Value = addressName;XmlElement valueElement = xDoc.CreateElement("v", worksheetSchema);valueElement.InnerText = value;cellElement.AppendChild(valueElement);// Default style is "0" cellElement.Attributes.Append(xDoc.CreateAttribute("s"));cellElement.Attributes["s"].Value = "0";XmlNode rowNode = xDoc.SelectSingleNode(string.Format("//d:sheetData/d:row[@r='{0}']", rowNumber), nsManager);if (rowNode == null){// Code removed here…// Didn't find the row, either. Just add a new row element: XmlNode sheetDataNode = xDoc.SelectSingleNode("//d:sheetData", nsManager);if (sheetDataNode != null){XmlElement rowElement = xDoc.CreateElement("row", worksheetSchema);rowElement.Attributes.Append(xDoc.CreateAttribute("r"));rowElement.Attributes["r"].Value = rowNumber;rowElement.AppendChild(cellElement);sheetDataNode.AppendChild(rowElement);returnValue = true;}}else{// Code removed here… XmlAttribute styleAttr =((XmlAttribute)(rowNode.Attributes.GetNamedItem("s")));if (styleAttr != null){// You know cellElement has an "s" attribute -- you// added it yourself. cellElement.Attributes["s"].Value = styleAttr.Value;}// You must insert the new cell at the correct location.// Loop through the children, looking for the first cell that is// beyond the cell you're trying to insert. Insert before that cell. XmlNode biggerNode = null;XmlNodeList cellNodes = rowNode.SelectNodes("./d:c", nsManager);if (cellNodes != null){foreach (XmlNode node in cellNodes){if (String.Compare(node.Attributes["r"].Value, addressName) > 0){biggerNode = node;break;}}}if (biggerNode == null){rowNode.AppendChild(cellElement);}else{rowNode.InsertBefore(cellElement, biggerNode);}returnValue = true;}}else{// Code removed here. cellnode.Attributes.RemoveNamedItem("t");XmlNode valueNode = cellnode.SelectSingleNode("d:v", nsManager);if (valueNode == null){// Cell with deleted value. Add a value element now. valueNode = xDoc.CreateElement("v", worksheetSchema);cellnode.AppendChild(valueNode);}valueNode.InnerText = value.ToString();returnValue = true;}}}// Save the XML back to its part. xDoc.Save(sheetPart.GetStream(FileMode.Create, FileAccess.Write));}}return returnValue;}/////////////////////
- .net 操作 EXCEL (using c# to control and access the excel)
- .net 操作 EXCEL (using c# to control and acce the excel)
- Using .NET and Excel 2003 To Validate E-Mails
- C#操作Excel/Access参考
- .Net C# 操作Excel
- Writing Data to Excel using C#
- Summarize C# Control Excel Skills(C#操作Excel技巧总结)
- How To Transfer XML Data to Microsoft Excel 2002 by Using Visual C# .NET
- Integrating Excel and Access
- How to transfer data to an Excel workbook by using Visual C# 2005 or Visual C# .NET
- Using CoCreateObjectDotNet to Access and Use .NET Assemblies in InstallScript
- How to automate Excel using MFC and worksheet functions
- How to authenticate against the Active Directory by using Forms authentication and Visual C# .NET
- Export to Excel user Control
- Export large data from GridView to Excel file using C#
- C# 连接sql数据库对 access,excel导入导出操作
- Excel To Access by asp
- C#操作Excel(一)
- CAD解决学生版的问题
- java 初始化的顺序和步骤
- 搜索利器Everything
- redboot的使用
- jQuery 1.3 API 参考文档中文版
- .net 操作 EXCEL (using c# to control and access the excel)
- 在netbeans上配置Petstore
- AutoCAD2006激活方法
- 不可重现的BUG的应对策略
- 【转】C++箴言:避免构造或析构函数中调用虚函数
- AJAX的js代码
- .INF文件是什么
- 人生之境界
- 人间正道