如何在C#中操作excle?

Question

public int ExcelDataCheck(Excel.Application excel)
{
int errorNumber = 0; Excel.Workbooks xBks = excel.Workbooks;
Excel.Workbook xBk = xBks.get_Item(1); try
{
DataService service = new DataService();
DataSet structDS = new DataSet();
string unableSheetNames = ""; for(int i=0;i<xBk.Worksheets.Count;i++)
{
//sheet和cell都是从1开始索引
((Excel._Worksheet)xBk.Worksheets.get_Item(i+1)).Activate();
string sheetName = ((Excel._Worksheet)xBk.Worksheets.get_Item(i+1)).Name; string strSql = "select * from datadictionary where tb_name_c ='" + sheetName + "'";
structDS = service.oDataSet(strSql,sheetName);
if(structDS.Tables.Count ==0 || structDS.Tables[sheetName].Rows.Count ==0)
{
unableSheetNames += ((Excel._Worksheet)xBk.Worksheets.get_Item(i+1)).Name +"\r\n";
}
else
{
//该sheet有多少行,计算原则是EID不为空则加一行
int rowCount=2;
bool myNull = false;
while(!myNull)
{
Excel.Range range = excel.get_Range(excel.Cells[rowCount,1],excel.Cells[rowCount,1]);
rowCount++; if(range.Value2 == null || range.Value2.ToString() == "")
myNull = true;
}
rowCount -= 2; for(int j=0;j<structDS.Tables[sheetName].Rows.Count;j++)
{
for(int k=0;k<rowCount;k++)
{
string fdname = structDS.Tables[sheetName].Rows[j]["FD_Name_C"].ToString(); string strType = structDS.Tables[sheetName].Rows[j]["FD_Type"].ToString(); int iSize = Convert.ToInt32(structDS.Tables[sheetName].Rows[j]["FD_Size"]);
int iDigit = Convert.ToInt32(structDS.Tables[sheetName].Rows[j]["FD_Digit"]); Excel.Range range1 = excel.get_Range(excel.Cells[k+2,j+1],excel.Cells[k+2,j+1]);
string strValue = "";
if(range1.Value2 != null)
{
if(strType == "datetime")
{
range1.Columns.AutoFit();
strValue = range1.Text.ToString();
}
else
strValue = range1.Value2.ToString();
} if(strValue != "" && !service.verifyValue(strValue,iSize,iDigit,strType))
{
excel.get_Range(excel.Cells[k+2,j+1],excel.Cells[k+2,j+1]).Interior.ColorIndex = 19;
errorNumber++;
}

}
}
} }
if(unableSheetNames != "")
{
MessageBox.Show("无效的页名:\r\n" + unableSheetNames);
} return errorNumber; }
catch(Exception ex)
{
MessageBox.Show(ex.Message);
return 9999;
}
finally
{
//结束进程 }

Answer 1

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.IO;
using System.Reflection;
namespace MyExcel
{
/// <summary>
/// Form1 の概要の説明です。
/// </summary>
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.Button btnNormal;
private System.Windows.Forms.Button btnAdvance;
private string [,] myData= 
 {
{"車牌号","類 型","品 牌","型 号","顔 色","附加標記号","車架号"}, {"浙KA3676","危險品","品 牌1","解放SZG9220YY","白","1110708900","022836"}, {"浙KA4109","危險品","品 牌2","解放CA4110P1K2","白","223132","010898"}, {"浙KA0001A","危險品","品 牌3","南明LSY9190WS","白","1110205458","0474636"}, {"浙KA0493","普通","品 牌4","解放LSY9190WS","白","1110255971","0094327"}, {"浙KA1045","普通","品 牌5","解放LSY9171WCD","黑","1110391226","0516003"}, {"浙KA1313","普通","品 牌6","解放9190WCD","黑","1110315027","0538701"}, {"浙KA1322","普通","品 牌7","解放LSY9190WS","黑","24323332","0538716"}, {"浙KA1575","普通","品 牌8","解放LSY9181WCD","黑","1110314149","0113018"}, {"浙KA1925","普通","品 牌9","解放LSY9220WCD","黑","1110390626","00268729"}, {"浙KA2258","普通","品 牌10","解放LSY9220WSP","黑","111048152","00320"}  }; /// <summary>
/// 必要なデザイナ変数です。
/// </summary>
private System.ComponentModel.Container components = null; public Form1()
{
//
// Windows フォーム デザイナ サポートに必要です。
//
InitializeComponent(); //
// TODO: InitializeComponent 呼び出しの後に、コンストラクタ コードを追加してください。
//
} /// <summary>
/// 使用されているリソースに後処理を実行します。
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null) 
{
components.Dispose();
}
}
base.Dispose( disposing );
} #region Windows フォーム デザイナで生成されたコード 
/// <summary>
/// デザイナ サポートに必要なメソッドです。このメソッドの内容を
/// コード エディタで変更しないでください。
/// </summary>
private void InitializeComponent()
{
this.btnNormal = new System.Windows.Forms.Button();
this.btnAdvance = new System.Windows.Forms.Button();
this.SuspendLayout();
// 
// btnNormal
// 
this.btnNormal.Location = new System.Drawing.Point(40, 32);
this.btnNormal.Name = "btnNormal";
this.btnNormal.TabIndex = 0;
this.btnNormal.Text = "button1";
this.btnNormal.Click += new System.EventHandler(this.btnNormal_Click);
// 
// btnAdvance
// 
this.btnAdvance.Location = new System.Drawing.Point(136, 32);
this.btnAdvance.Name = "btnAdvance";
this.btnAdvance.TabIndex = 1;
this.btnAdvance.Text = "button2";
this.btnAdvance.Click += new System.EventHandler(this.btnAdvance_Click);
// 
// Form1
// 
this.AutoScaleBaseSize = new System.Drawing.Size(5, 12);
this.ClientSize = new System.Drawing.Size(292, 273);
this.Controls.Add(this.btnAdvance);
this.Controls.Add(this.btnNormal);
this.Name = "Form1";
this.Text = "Form1";
this.ResumeLayout(false); }
#endregion /// <summary>
/// アプリケーションのメイン エントリ ポイントです。
/// </summary>
[STAThread]
static void Main() 
{
Application.Run(new Form1());
} private void btnNormal_Click(object sender, System.EventArgs e)
{

//?建一个Excel文件 Excel.Application myExcel = new Excel.Application ( ) ; myExcel.Application.Workbooks.Add ( true ) ; //?Excel文件可? myExcel.Visible=true; //第一行??表名称 myExcel.Cells[1,4]="普通報表"; //逐行写入数据， for(int i=0;i<11;i++)
{
for(int j=0;j<7;j++)
{
//以?引号??，表示??元格??文本
myExcel.Cells[2+i,1+j]="'"+myData[i,j];
}
} } private void btnAdvance_Click(object sender, System.EventArgs e)
{
string filename="";
SaveFileDialog mySave=new SaveFileDialog(); mySave.Filter="Excel文件(*.XLS)|*.xls|所有文件(*.*)|*.*";
if(mySave.ShowDialog()!=DialogResult.OK)
{
return;
} else
{
filename=mySave.FileName;
                
string filenameold=mySave.FileName; //打開Execl的一個模版  此模版中包含下面要用到的宏
FileInfo mode=new FileInfo("d:\\test\\normal.xls"); try
{ mode.CopyTo(filename,true); }
catch(Exception ee)
{
MessageBox.Show(ee.Message); return;
}
} object missing=Missing.Value;
Excel.Application myExcel=new Excel.Application ( ); myExcel.Application.Workbooks.Open(filename,missing,missing,missing,missing,
missing,missing,missing,missing,missing,missing, missing,missing); 

myExcel.Visible=true;

myExcel.Cells[2,4]="高級報表"; for(int i=0;i<11;i++)
{
for(int j=0;j<7;j++)
{
myExcel.Cells[4+i,1+j]="'"+myData[i,j];
}
}

Excel.Workbook myBook=myExcel.Workbooks[1];
Excel.Worksheet mySheet=(Excel.Worksheet)myBook.Worksheets[1];
Excel.Range r1=mySheet.get_Range(mySheet.Cells[2,1],mySheet.Cells[3,7]);
r1.Select(); //調用Excel中的宏格式樣式
myExcel.Run("Macro1",missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing, missing,missing,missing,missing,missing,missing,missing); 

r1 = mySheet.get_Range(mySheet.Cells[4,1],mySheet.Cells[4,7]);
r1.Select();
//調用Excel中的宏格式樣式
myExcel.Run("Macro2",missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing, missing,missing,missing,missing,missing,missing,missing); 


r1 = mySheet.get_Range(mySheet.Cells[4,1],mySheet.Cells[14,7]);
r1.Select();
//調用Excel中的宏格式樣式
myExcel.Run("Macro3",missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing,missing, missing,missing,missing,missing,missing,missing,missing); 


myBook.Save();
}
}
}

Answer 2

1.项目->添加引用->浏览->选择你的excel.dll位置->确定private void ExcelFileToCSVFile(string ExcelFile, string CSVFile)
{
object missing = System.Reflection.Missing.Value;
Excel.Application excel = new Excel.Application();
excel.Visible = false;
Excel.Workbooks oBooks = excel.Workbooks;
oBooks.Open(ExcelFile, missing, missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing); Excel.Workbook oBook = excel.ActiveWorkbook;
oBook.SaveAs(CSVFile, XlFileFormat.xlCSV, missing, missing, missing, missing, 
Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing);
excel.DisplayAlerts = false;
oBook.Close(false, missing, false);
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook);
oBook = null; System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks);
oBooks = null; excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
System.GC.Collect();
}

Answer 3

2.http://expert.csdn.net/Expert/topic/2870/2870546.xml?temp=.4450495

Answer 4

/// <summary>
/// 
/// </summary>
/// <param name="dt"></param>
/// <param name="s_Path"></param>
/// <param name="p_Target"></param>
public void Export(System.Data.DataTable  dt,string s_Path,System.Web.UI.Page p_Target)
{
Excel.Application excel=new Excel.ApplicationClass();
Excel.Workbooks workbooks = excel.Workbooks; 
Excel._Workbook workbook1=excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet); 
Sheets sheets = workbook1.Worksheets; 
_Worksheet worksheet = (_Worksheet) sheets.get_Item(1);   int rowIndex=1; 
int colIndex=0;  //循环读取生成新表格 
excel.Application.Workbooks.Add(true);          //生成EXCEL的格式设定 
excel.Cells.Font.Bold = false; 
excel.Cells.FillLeft(); 
excel.Cells.Font.Size = "9"; 

//引用函数GetData中所取出的数据，得到数据源 
System.Data.DataTable  table =dt;
      
//将所得到的表的列名,赋值给单元格 
foreach(DataColumn col in table.Columns) 
{ 
colIndex++;  
excel.Cells[1,colIndex]=col.ColumnName; 
} 
//同样方法处理数据  foreach(DataRow row in table.Rows) 
{ 
rowIndex++; 
colIndex=0; 
//处理各列的数据 
foreach(DataColumn col in table.Columns) 
{ 
colIndex++; 
excel.Cells[rowIndex,colIndex]= "'" + row[col.ColumnName].ToString(); 
}
} 
//不可见,即后台处理 
excel.Visible=true;
//友好界面提示 

Excel.XlSaveAsAccessMode asm=Excel.XlSaveAsAccessMode.xlShared;  
object Nothing=System.Reflection.Missing.Value; 
string FileNameString =System.DateTime.Now.ToString("yyyyMMddhhmmss");
string s_FullPath=s_Path + FileNameString+".xls";
excel.ActiveWorkbook.SaveAs( s_FullPath,Nothing,Nothing,Nothing,Nothing,Nothing,asm,Nothing,Nothing,Nothing,Nothing,Nothing); 
excel.Workbooks.Close();
excel.Quit(); 
excel=null; 
FileStream fs_target = File.Open(s_FullPath,FileMode.Open,FileAccess.Read,FileShare.Read);
byte[] b_target = new byte[fs_target.Length];
fs_target.Read(b_target,0,(int)fs_target.Length);
fs_target.Close();
p_Target.Response.Clear();
p_Target.Response.ContentType  = "application/x-msdownload";
p_Target.Response.AddHeader("Content-Disposition", "attachment; filename=UserTable.xls");
p_Target.Response.BinaryWrite(b_target);
p_Target.Response.Flush();
p_Target.Response.End();
File.Delete(s_FullPath); }