//打开一个空的Excel
//新建一个Excel并保存到指定的路径
//行数
string count = ws.UsedRange.Rows.Count.ToString();
Microsoft.Office.Interop.Excel.Application app =
new Microsoft.Office.Interop.Excel.Application();
app.Workbooks.Add( true);
app.Visible = true;
app.Workbooks.Add( true);
app.Visible = true;
//打开一个已经存在的Excel文件
Microsoft.Office.Interop.Excel.Application app =
new Microsoft.Office.Interop.Excel.Application();
app.Workbooks.Add(路径和文件名);
app.Visible = true;
app.Workbooks.Add(路径和文件名);
app.Visible = true;
//读取Excel2003
conExcel.ConnectionString =
"
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=
" + filepath +
"
;Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"
";
//读取Excel2007
conExcel.ConnectionString =
"
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=
" +filepath +
"
;Extended Properties=\"Excel 12.0;HDR=YES\"
";
//设置禁止弹出保存和覆盖的询问提示框
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
app.AlertBeforeOverwriting = false;
wb.SaveCopyAs(@"D:\111.xlsx");
//新建一个Excel并保存到指定的路径
app =
new Microsoft.Office.Interop.Excel.Application();
wb = app.Workbooks.Add( true);
app.Cells[ 1, 1] = " test ";
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
wb.SaveCopyAs( @" C:\111.xlsx ");
Close();
wb = app.Workbooks.Add( true);
app.Cells[ 1, 1] = " test ";
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
wb.SaveCopyAs( @" C:\111.xlsx ");
Close();
DataTable myT=ExcelToDataTable(
"
D:/文件/新武昌站点资料.xls
",
"
sheet1
");
String mystr=myT.Rows[ 0][ 0].ToString();
this.textBox1.Text=mystr;
String mystr=myT.Rows[ 0][ 0].ToString();
this.textBox1.Text=mystr;
string strPathResult =
"";
string strVersionResult = "";
string strKeyName = " Path ";
object objResult = null;
Microsoft.Win32.RegistryValueKind regValueKind;
Microsoft.Win32.RegistryKey regKey = null;
Microsoft.Win32.RegistryKey regSubKey = null;
try
{
regKey = Microsoft.Win32.Registry.LocalMachine;
if (regSubKey == null)
{ // office97
regSubKey = regKey.OpenSubKey( @" SOFTWARE\Microsoft\Office\8.0\Common\InstallRoot ", false);
strVersionResult = " office97 ";
strKeyName = " OfficeBin ";
}
if (regSubKey == null)
{ // Office2000
regSubKey = regKey.OpenSubKey( @" SOFTWARE\Microsoft\Office\9.0\Common\InstallRoot ", false);
strVersionResult = " office2000 ";
strKeyName = " Path ";
}
if (regSubKey == null)
{ // officeXp
regSubKey = regKey.OpenSubKey( @" SOFTWARE\Microsoft\Office\10.0\Common\InstallRoot ", false);
strVersionResult = " officeXP ";
strKeyName = " Path ";
}
if (regSubKey == null)
{ // Office2003
regSubKey = regKey.OpenSubKey( @" SOFTWARE\Microsoft\Office\11.0\Common\InstallRoot ", false);
strVersionResult = " office2003 ";
strKeyName = " Path ";
}
if (regSubKey == null)
{ // office2007
regSubKey = regKey.OpenSubKey( @" SOFTWARE\Microsoft\Office\12.0\Common\InstallRoot ", false);
strVersionResult = " office2007 ";
strKeyName = " Path ";
}
objResult = regSubKey.GetValue(strKeyName);
regValueKind = regSubKey.GetValueKind(strKeyName);
if (regValueKind == Microsoft.Win32.RegistryValueKind.String)
{
strPathResult = objResult.ToString();
}
}
catch (System.Security.SecurityException ex)
{
throw new System.Security.SecurityException( " 您没有读取注册表的权限 ", ex);
}
catch (Exception ex)
{
throw new Exception( " 读取注册表出错! ", ex);
}
finally
{
if (regKey != null)
{
regKey.Close();
regKey = null;
}
if (regSubKey != null)
{
regSubKey.Close();
regSubKey = null;
}
}
MessageBox.Show(strVersionResult);
string strVersionResult = "";
string strKeyName = " Path ";
object objResult = null;
Microsoft.Win32.RegistryValueKind regValueKind;
Microsoft.Win32.RegistryKey regKey = null;
Microsoft.Win32.RegistryKey regSubKey = null;
try
{
regKey = Microsoft.Win32.Registry.LocalMachine;
if (regSubKey == null)
{ // office97
regSubKey = regKey.OpenSubKey( @" SOFTWARE\Microsoft\Office\8.0\Common\InstallRoot ", false);
strVersionResult = " office97 ";
strKeyName = " OfficeBin ";
}
if (regSubKey == null)
{ // Office2000
regSubKey = regKey.OpenSubKey( @" SOFTWARE\Microsoft\Office\9.0\Common\InstallRoot ", false);
strVersionResult = " office2000 ";
strKeyName = " Path ";
}
if (regSubKey == null)
{ // officeXp
regSubKey = regKey.OpenSubKey( @" SOFTWARE\Microsoft\Office\10.0\Common\InstallRoot ", false);
strVersionResult = " officeXP ";
strKeyName = " Path ";
}
if (regSubKey == null)
{ // Office2003
regSubKey = regKey.OpenSubKey( @" SOFTWARE\Microsoft\Office\11.0\Common\InstallRoot ", false);
strVersionResult = " office2003 ";
strKeyName = " Path ";
}
if (regSubKey == null)
{ // office2007
regSubKey = regKey.OpenSubKey( @" SOFTWARE\Microsoft\Office\12.0\Common\InstallRoot ", false);
strVersionResult = " office2007 ";
strKeyName = " Path ";
}
objResult = regSubKey.GetValue(strKeyName);
regValueKind = regSubKey.GetValueKind(strKeyName);
if (regValueKind == Microsoft.Win32.RegistryValueKind.String)
{
strPathResult = objResult.ToString();
}
}
catch (System.Security.SecurityException ex)
{
throw new System.Security.SecurityException( " 您没有读取注册表的权限 ", ex);
}
catch (Exception ex)
{
throw new Exception( " 读取注册表出错! ", ex);
}
finally
{
if (regKey != null)
{
regKey.Close();
regKey = null;
}
if (regSubKey != null)
{
regSubKey.Close();
regSubKey = null;
}
}
MessageBox.Show(strVersionResult);
app =
new Microsoft.Office.Interop.Excel.Application();
wb = app.Workbooks.Open(path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[ 1];
wb = app.Workbooks.Open(path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value
, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
ws = (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[ 1];
//行数
string count = ws.UsedRange.Rows.Count.ToString();
Range r = (Range)ws.Cells[1, 1];
MessageBox.Show(r.Value2.ToString());
MessageBox.Show(r.Value2.ToString());
public
void DelRow(
int row,
string fPath)
{
OpenApp(fPath);
Range range = ws.UsedRange;
int columns = range.Columns.Count;
for ( int i = 0; i < columns; i++)
{
((Range)ws.Cells[row, i+ 1]).Delete(Missing.Value);
}
wb.Save();
Close();
}
{
OpenApp(fPath);
Range range = ws.UsedRange;
int columns = range.Columns.Count;
for ( int i = 0; i < columns; i++)
{
((Range)ws.Cells[row, i+ 1]).Delete(Missing.Value);
}
wb.Save();
Close();
}
//删除一列
public
void DelCol(
int col,
string fPath)
{
OpenApp(fPath);
Range range = ws.UsedRange;
int rows = range.Rows.Count;
for ( int i = 0; i < rows; i++)
{
((Range)ws.Cells[ 1, col]).Delete(Missing.Value);
}
wb.Save();
Close();
}
{
OpenApp(fPath);
Range range = ws.UsedRange;
int rows = range.Rows.Count;
for ( int i = 0; i < rows; i++)
{
((Range)ws.Cells[ 1, col]).Delete(Missing.Value);
}
wb.Save();
Close();
}
public
void DelCell(
int row,
int col,
string fPath)
{
OpenApp(fPath);
Range range = (Range)ws.Cells[row, col];
range.Delete(Missing.Value);
wb.Save();
Close();
}
{
OpenApp(fPath);
Range range = (Range)ws.Cells[row, col];
range.Delete(Missing.Value);
wb.Save();
Close();
}
在这里需要注意一下,在Excel表中,删除和清除是不一样的,清除(Clear)只是把内容清空,而单元格还在,删除(Del)是把一个单元格删除,下面的数据就会 自动的对其,就会造成排版问题,这是需要注意的
//释放资源
因为引用的是COM组件,是非托管的代码,所以释放资源的代码就需要手动的编写,在网上查找了很多的代码都不好用,下面这个是比较好的。
public
void Close()
{
if (app == null) return;
if (wb != null)
{
wb.Close( false, Missing.Value, Missing.Value);
ReleaseCom(wb);
wb = null;
}
this.app.Quit();
ReleaseCom(app);
app = null;
GC.Collect();
}
static private void ReleaseCom( object o)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(o); // 强制释放一个对象
}
catch { }
finally
{
o = null;
}
}
{
if (app == null) return;
if (wb != null)
{
wb.Close( false, Missing.Value, Missing.Value);
ReleaseCom(wb);
wb = null;
}
this.app.Quit();
ReleaseCom(app);
app = null;
GC.Collect();
}
static private void ReleaseCom( object o)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(o); // 强制释放一个对象
}
catch { }
finally
{
o = null;
}
}
{
ws.get_Range(ws.Cells[x1, y1], ws.Cells[x2, y2]).Merge(Type.Missing);
}
ws.get_Range(ws.Cells[x1, y1], ws.Cells[x2, y2]).Merge(Type.Missing);
}
public void UniteCells(string ws, int x1, int y1, int x2, int y2)
//合并单元格
{
GetSheet(ws).get_Range(GetSheet(ws).Cells[x1, y1], GetSheet(ws).Cells[x2, y2]).Merge(Type.Missing);
}
//合并单元格
{
GetSheet(ws).get_Range(GetSheet(ws).Cells[x1, y1], GetSheet(ws).Cells[x2, y2]).Merge(Type.Missing);
}
//Range可以获取一个单元格,也可以是多个单元格
获取一个
Range ran=(Range)ws.Cells[Row,Col];
获取多行
Range ran=ws.Get_Range(左上角单元格,右下角单元格);
Range ran=ws.Get_Range(ws.Cells[row,col],ws.Cells[row,Col]);
Range ran=ws.Get_Range(ws.Cells[row,col],ws.Cells[row,Col]);
Range ra = (Range)ws.Cells[row + row + 4, col];
ra.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
ra.NumberFormatLocal = "#,##0.00";
ra.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
ra.NumberFormatLocal = "#,##0.00";
Range ran = ws.get_Range(ws.Cells[3,1],ws.Cells[4,9]);
Range ra = ws.get_Range(ws.Cells[5,1],ws.Cells[6,9]);
ran.Copy(ra);
Range ra = ws.get_Range(ws.Cells[5,1],ws.Cells[6,9]);
ran.Copy(ra);
本文转自钢钢博客园博客,原文链接:http://www.cnblogs.com/xugang/archive/2012/02/08/2342129.html,如需转载请自行联系原作者