C# 怎么在一个已经存在的EXCEL里写入数据？
C# 怎么在一个已经存在的EXCEL里写入数据？
[解决办法]
首先是如何创建、打开、读取、写入、保存的一般性代码：
using System;
using System.Reflection; // 引用这个才能使用Missing字段
namespace CExcel1
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
//创建Application对象
Excel.Application xApp=new Excel.ApplicationClass();
xApp.Visible=true;
//得到WorkBook对象, 可以用两种方式之一: 下面的是打开已有的文件
Excel.Workbook xBook=xApp.Workbooks._Open(@ "D:\Sample.xls ",
Missing.Value,Missing.Value,Missing.Value,Missing.Value
,Missing.Value,Missing.Value,Missing.Value,Missing.Value
,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
//xBook=xApp.Workbooks.Add(Missing.Value);//新建文件的代码
//指定要操作的Sheet，两种方式：
Excel.Worksheet xSheet=(Excel.Worksheet)xBook.Sheets[1];
//Excel.Worksheet xSheet=(Excel.Worksheet)xApp.ActiveSheet;
//读取数据，通过Range对象
Excel.Range rng1=xSheet.get_Range( "A1 ",Type.Missing);
Console.WriteLine(rng1.Value2);
//读取，通过Range对象，但使用不同的接口得到Range
Excel.Range rng2=(Excel.Range)xSheet.Cells[3,1];
Console.WriteLine(rng2.Value2);
//写入数据
Excel.Range rng3=xSheet.get_Range( "C6 ",Missing.Value);
rng3.Value2= "Hello ";
rng3.Interior.ColorIndex=6; //设置Range的背景色
//保存方式一：保存WorkBook
xBook.SaveAs(@ "D:\CData.xls ",
Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,
Excel.XlSaveAsAccessMode.xlNoChange,Missing.Value,Missing.Value,Missing.Value,
Missing.Value,Missing.Value);
//保存方式二：保存WorkSheet
xSheet.SaveAs(@ "D:\CData2.xls ",
Missing.Value,Missing.Value,Missing.Value,Missing.Value,
Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
//保存方式三
xBook.Save();
xSheet=null;
xBook=null;
xApp.Quit(); //这一句是非常重要的，否则Excel对象不能从内存中退出
xApp=null;
}
}
}
[解决办法]
下面是我以前写的一个测试,以xml方式扣作excel,很方便但缺点是只有0ffice 2003才能打开这样的xls文件.......
第一步先建一个excel模板文件(以xml格式另存为test.xml)
<?xml version= "1.0 "?>
<?mso-application progid= "Excel.Sheet "?>
<Workbook xmlns= "urn:schemas-microsoft-com:office:spreadsheet "
xmlns:o= "urn:schemas-microsoft-com:office:office "
xmlns:x= "urn:schemas-microsoft-com:office:excel "
xmlns:ss= "urn:schemas-microsoft-com:office:spreadsheet "
xmlns:html= "http://www.w3.org/TR/REC-html40 ">
<DocumentProperties xmlns= "urn:schemas-microsoft-com:office:office ">
<Created> 1996-12-17T01:32:42Z </Created>
<LastSaved> 2007-04-11T01:31:33Z </LastSaved>
<Version> 11.6568 </Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns= "urn:schemas-microsoft-com:office:office ">
<RemovePersonalInformation/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns= "urn:schemas-microsoft-com:office:excel ">
<WindowHeight> 4530 </WindowHeight>
<WindowWidth> 8505 </WindowWidth>
<WindowTopX> 480 </WindowTopX>
<WindowTopY> 120 </WindowTopY>
<AcceptLabelsInFormulas/>
<ProtectStructure> False </ProtectStructure>
<ProtectWindows> False </ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID= "Default " ss:Name= "Normal ">
<Alignment ss:Vertical= "Bottom "/>
<Borders/>
<Font ss:FontName= "宋体 " x:CharSet= "134 " ss:Size= "12 "/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID= "s22 ">
<Alignment ss:Horizontal= "Center " ss:Vertical= "Bottom "/>
<Font ss:FontName= "宋体 " x:CharSet= "134 " ss:Size= "16 " ss:Color= "#FF0000 "/>
</Style>
<Style ss:ID= "s24 ">
<Alignment ss:Vertical= "Center "/>
</Style>
<Style ss:ID= "s26 ">
<Alignment ss:Horizontal= "Center " ss:Vertical= "Center "/>
</Style>
</Styles>
<Worksheet ss:Name= "Sheet1 ">
<Table ss:ExpandedColumnCount= "7 " ss:ExpandedRowCount= "8 " x:FullColumns= "1 "
x:FullRows= "1 " ss:DefaultColumnWidth= "54 " ss:DefaultRowHeight= "14.25 ">
<Row ss:AutoFitHeight= "0 " ss:Span= "3 "/>
<Row ss:Index= "5 " ss:AutoFitHeight= "0 ">
<Cell ss:Index= "3 " ss:MergeAcross= "4 " ss:MergeDown= "1 " ss:StyleID= "s22 "> <Data
ss:Type= "String "> 标头 </Data> </Cell>
</Row>
<Row ss:AutoFitHeight= "0 "/>
<Row ss:AutoFitHeight= "0 ">
<Cell ss:Index= "3 " ss:MergeDown= "1 " ss:StyleID= "s24 "> <Data ss:Type= "String "> 序号 </Data> </Cell>
<Cell ss:MergeAcross= "3 " ss:StyleID= "s26 "> <Data ss:Type= "String "> 销售额 </Data> </Cell>
</Row>
<Row ss:AutoFitHeight= "0 ">
<Cell ss:Index= "4 "> <Data ss:Type= "String "> 1月 </Data> </Cell>
<Cell> <Data ss:Type= "String "> 2月 </Data> </Cell>
<Cell> <Data ss:Type= "String "> 3月 </Data> </Cell>
<Cell> <Data ss:Type= "String "> 4月 </Data> </Cell>
</Row>
</Table>
<WorksheetOptions xmlns= "urn:schemas-microsoft-com:office:excel ">
<Unsynced/>
<Print>
<ValidPrinterInfo/>
<PaperSizeIndex> 9 </PaperSizeIndex>
<HorizontalResolution> 300 </HorizontalResolution>
<VerticalResolution> 300 </VerticalResolution>
</Print>
<Selected/>
<TopRowVisible> 5 </TopRowVisible>
<Panes>
<Pane>
<Number> 3 </Number>
<ActiveRow> 8 </ActiveRow>
<RangeSelection> R9:R11 </RangeSelection>
</Pane>
</Panes>
<ProtectObjects> False </ProtectObjects>
<ProtectScenarios> False </ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>
------解决方案--------------------
然后是完全以xml方式操作它的示例
另存为一个xls就可以看到效果了
using System;
using System.Data;
using System.Text;
using System.IO;
using System.Collections.Generic;
using System.Xml;
public class MyClass
{
public static void Main()
{
//这是excel中要用到的两个命名空间
string ssNameSpaceUri = "urn:schemas-microsoft-com:office:spreadsheet ";
string EmptyNameSpaceUri = "urn:schemas-microsoft-com:office:spreadsheet ";
//生成测试用数据集
DataSet ds = CreateDataSet();
//取出模板
XmlDocument xd = new XmlDocument();
xd.Load( "..\\..\\test.xml ");
XmlElement xe = xd.DocumentElement;
//此xml文档中只有一个table
//也可以引入namespace,然后用SelectSingleNode方法
XmlNode table = xe.ChildNodes[4].ChildNodes[0];
//修改表格属性,加入新加的行数
XmlAttribute xaExpandedRowCount = table.Attributes[ "ss:ExpandedRowCount "];
Int32 rowCount = Int32.Parse(xaExpandedRowCount.Value) + ds.Tables[0].Rows.Count;
xaExpandedRowCount.Value = rowCount.ToString();
foreach (DataRow dr in ds.Tables[0].Rows) {
//生成每一行
XmlNode xnRow = xd.CreateElement( "Row ", EmptyNameSpaceUri);
//加入ss:AutoFitHeight属性
XmlAttribute xaAutoFitHeight = xd.CreateAttribute( "AutoFitHeight ", ssNameSpaceUri);
xaAutoFitHeight.Value = "0 ";
xnRow.Attributes.Append(xaAutoFitHeight);
for (int index = 0; index < ds.Tables[0].Columns.Count; index++) {
//为每个数据列生成新的xmlNode
XmlNode xnCell = xd.CreateElement( "Cell ", EmptyNameSpaceUri);
//如果是第一列,加入ss:index属性
if (index == 0)
{
XmlAttribute xaIndex = xd.CreateAttribute( "Index ", ssNameSpaceUri);
xaIndex.Value = "3 ";
xnCell.Attributes.Append(xaIndex);
}
//为每一列生成数据
XmlNode xnData = xd.CreateElement( "Data ", EmptyNameSpaceUri);
//这儿都是指定string(文本)
XmlAttribute xaType = xd.CreateAttribute( "Type ", ssNameSpaceUri);
xaType.Value = "String ";
xnData.Attributes.Append(xaType);
xnData.InnerText = dr[index].ToString();
xnCell.AppendChild(xnData);
//加入数据到列
xnRow.AppendChild(xnCell);
}
//新数据行加入完毕
table.AppendChild(xnRow);
}
//另存为xls
using (StreamWriter sw = new StreamWriter( "..\\..\\test.xls ", false, Encoding.Unicode))
{
sw.Write(xd.OuterXml);
sw.Flush();
sw.Close();
}
Console.Read();
}
static DataSet CreateDataSet()
{
DataTable master = new DataTable( "product ");
master.Columns.Add(new DataColumn( "Index ", typeof(int)));
master.Columns.Add(new DataColumn( "Jan ", typeof(int)));
master.Columns.Add(new DataColumn( "Feb ", typeof(int)));
master.Columns.Add(new DataColumn( "Mar ", typeof(int)));
master.Columns.Add(new DataColumn( "Apr ", typeof(int)));
for (int i = 0; i < 10; i++)
{
DataRow DRmaster = master.NewRow();
DRmaster[0] = i;
DRmaster[1] = i;
DRmaster[2] = i;
DRmaster[3] = i;
DRmaster[4] = i;
master.Rows.Add(DRmaster);
}
//返回数据集
DataSet ds = new DataSet();
ds.Tables.Add(master);
return ds;
}
}
[解决办法]
添加引用，com组件microsoft excel
[解决办法]
add reference==> com==> microsoft excel 11.0 object library