.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文件并使用ＤＯＭ。 
  实现： 
  １：使用．ｎｅｔ　调用　ｃｏｍ

////////////////////////
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: 使用ｏｌｅＤＢ（ＶＳＴＳ　２００８　＋　ＥＸＣＥＬ　２００７）

//////////////////////


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:使用ＯＰＥＮ　ＸＭＬ　（比较复杂阿）

//////////////////////

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;

}
/////////////////////