.net 操作 EXCEL (using c# to control and access the excel)

简介:

转自: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 in
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;
}
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.PackageRelationship
stringRelationship in
documentPart.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 relationship
in 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;
}
/////////////////////
欢迎加群互相学习,共同进步。QQ群:iOS: 58099570 | Android: 572064792 | Nodejs:329118122 做人要厚道,转载请注明出处!
















本文转自张昺华-sky博客园博客,原文链接:http://www.cnblogs.com/sunshine-anycall/archive/2008/11/24/1339716.html ,如需转载请自行联系原作者



相关文章
|
11月前
|
Java 物联网 C#
C#/.NET/.NET Core学习路线集合,学习不迷路!
C#/.NET/.NET Core学习路线集合,学习不迷路!
412 0
|
6月前
|
SQL 小程序 API
如何运用C#.NET技术快速开发一套掌上医院系统?
本方案基于C#.NET技术快速构建掌上医院系统,结合模块化开发理念与医院信息化需求。核心功能涵盖用户端的预约挂号、在线问诊、报告查询等,以及管理端的排班管理和数据统计。采用.NET Core Web API与uni-app实现前后端分离,支持跨平台小程序开发。数据库选用SQL Server 2012,并通过读写分离与索引优化提升性能。部署方案包括Windows Server与负载均衡设计,确保高可用性。同时针对API差异、数据库老化及高并发等问题制定应对措施,保障系统稳定运行。推荐使用Postman、Redgate等工具辅助开发,提升效率与质量。
198 0
|
10月前
|
开发框架 搜索推荐 算法
一个包含了 50+ C#/.NET编程技巧实战练习教程
一个包含了 50+ C#/.NET编程技巧实战练习教程
265 18
|
10月前
|
缓存 算法 安全
精选10款C#/.NET开发必备类库(含使用教程),工作效率提升利器!
精选10款C#/.NET开发必备类库(含使用教程),工作效率提升利器!
279 12
|
10月前
|
开发框架 人工智能 .NET
C#/.NET/.NET Core拾遗补漏合集(24年12月更新)
C#/.NET/.NET Core拾遗补漏合集(24年12月更新)
132 6
|
10月前
|
开发框架 算法 .NET
C#/.NET/.NET Core技术前沿周刊 | 第 15 期(2024年11.25-11.30)
C#/.NET/.NET Core技术前沿周刊 | 第 15 期(2024年11.25-11.30)
136 6
|
10月前
|
开发框架 Cloud Native .NET
C#/.NET/.NET Core技术前沿周刊 | 第 16 期(2024年12.01-12.08)
C#/.NET/.NET Core技术前沿周刊 | 第 16 期(2024年12.01-12.08)
130 6
|
10月前
|
开发框架 监控 .NET
C#进阶-ASP.NET WebForms调用ASMX的WebService接口
通过本文的介绍,希望您能深入理解并掌握ASP.NET WebForms中调用ASMX WebService接口的方法和技巧,并在实际项目中灵活运用这些技术,提高开发效率和应用性能。
549 5
|
10月前
|
算法 Java 测试技术
Benchmark.NET:让 C# 测试程序性能变得既酷又简单
Benchmark.NET是一款专为 .NET 平台设计的性能基准测试框架,它可以帮助你测量代码的执行时间、内存使用情况等性能指标。它就像是你代码的 "健身教练",帮助你找到瓶颈,优化性能,让你的应用跑得更快、更稳!希望这个小教程能让你在追求高性能的路上越走越远,享受编程带来的无限乐趣!
426 13
|
11月前
|
Java 物联网 编译器
C#一分钟浅谈:.NET Core 与 .NET 5 区别
本文对比了 .NET Core 和 .NET 5,从历史背景、主要区别、常见问题及易错点等方面进行了详细分析。.NET Core 侧重跨平台支持和高性能,而 .NET 5 在此基础上统一了 .NET 生态系统,增加了更多新特性和优化。开发者可根据具体需求选择合适的版本。
369 7