1.(新建工程等步骤在这里都省略了)添加操作excel的COM组件:
2.主要的方法代码:
///
<summary>
/// 修改Excel
/// </summary>
/// <param name="Path"> 读取路径 </param>
/// <param name="newPath"> 另存路径 </param>
/// <param name="shop_name"> 店名 </param>
/// <param name="tablename"> 表名 </param>
public void AlterExcel( string Path, string newPath, string shop_name, string tablename)
{
try
{
Object miss = System.Reflection.Missing.Value;
Excel.Application ExcelApp = new Excel.ApplicationClass();
Excel.Workbooks wbooks = (Excel.Workbooks)ExcelApp.Workbooks;
Excel.Workbook wbook = wbooks.Open(Path, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss);
// 获得第一个工作薄
Excel.Worksheet wsheet = (Excel.Worksheet)wbook.Worksheets.get_Item( 1 );
ExcelApp.Visible = false ;
ExcelApp.DisplayAlerts = false ;
// 取得编号
ArrayList arr = new ArrayList();
for ( int i = 3 , j = wsheet.UsedRange.Rows.Count; i < j; i ++ )
{
Excel.Range rang = (Excel.Range)wsheet.Cells[i, 2 ];
if ( null != rang.Text && rang.Text.ToString().Length > 0 )
arr.Add(rang.Text);
else
break ;
}
// 添加新行 Cells[行,列]
((Excel.Range)wsheet.Columns.get_Item( 3 , miss)).Insert(XlInsertShiftDirection.xlShiftToRight, miss);
// 添加区域单元格
// wsheet.get_Range(wsheet.Cells[2, 3], wsheet.Cells[wsheet.UsedRange.Rows.Count - 3, 3]).Insert(XlInsertShiftDirection.xlShiftToRight, miss);
// 设置标头
wsheet.Cells[ 2 , 3 ] = " 地址 " ;
((Excel.Range)wsheet.Columns.get_Item( 3 , miss)).ColumnWidth = 25 ;
// 此处为获得该店的所有地址,这里替换成自己的方法就行
Hashtable hash = GetShopAddr(shop_name, tablename);
for ( int i = 3 , j = i + arr.Count; i < j; i ++ )
{
try
{
// 根据编号设置地址
wsheet.Cells[i, 3 ] = hash[Convert.ToInt32(arr[i - 3 ])];
}
catch (Exception)
{
continue ;
}
}
// 另存为
wbook.SaveAs(newPath, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
wbook.Close( false , miss, miss);
ExcelApp.Quit();
ReleaseObject(wsheet);
ReleaseObject(wbook);
ReleaseObject(wbooks);
ReleaseObject(ExcelApp);
GC.Collect();
GC.WaitForPendingFinalizers();
}
catch
{
// throw;
}
finally
{
// 结束进程
// KillExcelProcess();
}
}
/// <summary>
/// 释放对象
/// </summary>
/// <param name="obj"></param>
private void ReleaseObject( object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
}
catch {}
finally { obj = null ; }
}
/// <summary>
/// 结束Excel进程
/// </summary>
public void KillExcelProcess()
{
Process[] myProcesses = Process.GetProcessesByName( " Excel " );
foreach (Process myProcess in myProcesses)
myProcess.Kill();
}
注意:
/// 修改Excel
/// </summary>
/// <param name="Path"> 读取路径 </param>
/// <param name="newPath"> 另存路径 </param>
/// <param name="shop_name"> 店名 </param>
/// <param name="tablename"> 表名 </param>
public void AlterExcel( string Path, string newPath, string shop_name, string tablename)
{
try
{
Object miss = System.Reflection.Missing.Value;
Excel.Application ExcelApp = new Excel.ApplicationClass();
Excel.Workbooks wbooks = (Excel.Workbooks)ExcelApp.Workbooks;
Excel.Workbook wbook = wbooks.Open(Path, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss);
// 获得第一个工作薄
Excel.Worksheet wsheet = (Excel.Worksheet)wbook.Worksheets.get_Item( 1 );
ExcelApp.Visible = false ;
ExcelApp.DisplayAlerts = false ;
// 取得编号
ArrayList arr = new ArrayList();
for ( int i = 3 , j = wsheet.UsedRange.Rows.Count; i < j; i ++ )
{
Excel.Range rang = (Excel.Range)wsheet.Cells[i, 2 ];
if ( null != rang.Text && rang.Text.ToString().Length > 0 )
arr.Add(rang.Text);
else
break ;
}
// 添加新行 Cells[行,列]
((Excel.Range)wsheet.Columns.get_Item( 3 , miss)).Insert(XlInsertShiftDirection.xlShiftToRight, miss);
// 添加区域单元格
// wsheet.get_Range(wsheet.Cells[2, 3], wsheet.Cells[wsheet.UsedRange.Rows.Count - 3, 3]).Insert(XlInsertShiftDirection.xlShiftToRight, miss);
// 设置标头
wsheet.Cells[ 2 , 3 ] = " 地址 " ;
((Excel.Range)wsheet.Columns.get_Item( 3 , miss)).ColumnWidth = 25 ;
// 此处为获得该店的所有地址,这里替换成自己的方法就行
Hashtable hash = GetShopAddr(shop_name, tablename);
for ( int i = 3 , j = i + arr.Count; i < j; i ++ )
{
try
{
// 根据编号设置地址
wsheet.Cells[i, 3 ] = hash[Convert.ToInt32(arr[i - 3 ])];
}
catch (Exception)
{
continue ;
}
}
// 另存为
wbook.SaveAs(newPath, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
wbook.Close( false , miss, miss);
ExcelApp.Quit();
ReleaseObject(wsheet);
ReleaseObject(wbook);
ReleaseObject(wbooks);
ReleaseObject(ExcelApp);
GC.Collect();
GC.WaitForPendingFinalizers();
}
catch
{
// throw;
}
finally
{
// 结束进程
// KillExcelProcess();
}
}
/// <summary>
/// 释放对象
/// </summary>
/// <param name="obj"></param>
private void ReleaseObject( object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
}
catch {}
finally { obj = null ; }
}
/// <summary>
/// 结束Excel进程
/// </summary>
public void KillExcelProcess()
{
Process[] myProcesses = Process.GetProcessesByName( " Excel " );
foreach (Process myProcess in myProcesses)
myProcess.Kill();
}
a).需要添加命名空间 using Microsoft.Office.Interop.Excel;
b).添加新行处代码两个都行,一个是添加一列一个是添加一个范围的单元格,达到效果是一样的
最后推荐一个网站和一个论坛:
Excel终极伴侣 http://www.chinaobs.com/
MSDN http://msdn.microsoft.com/library/CHS/dv_wrcore/html/wrgrfexcelobjects.asp
本文转自博客园农民伯伯的博客,原文链接:C# 操作Excel2003,如需转载请自行联系原博主。