private void button1_click(object sender, system.eventargs e) { if(openfiledialog1.showdialog() == dialogresult.ok) { oledbdataadapter ada = new oledbdataadapter("select * from [sheet1$]", "provider=microsoft.jet.oledb.4.0;data source=" + openfiledialog1.filename + ";extended properties=excel 8.0;"); datatable dt = new datatable(); try { ada.fill(dt); } catch(exception ex)
{ messagebox.show(ex.tostring()); } datagrid1.datasource = dt;
} }
sqlconnection conn = new sqlconnection("data source=(local);database=northwind;user id =sa;pwd=york"); string strsql = @"select * from openrowset(microsoft.jet.oledb.4.0 ,excel 5.0;hdr=yes;database=e:ook1.xls,a$)"; sqldataadapter da = new sqldataadapter(strsql, conn); dataset ds = new dataset(); da.fill(ds);
customergrid.datasource = ds.tables[0];
using System; using System.Reflection; // 引用这个才能使用Missing字段 using Excel;
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; } } }
//导出Excel的方法 private void ExportExcel() { DataSet ds=dtsSelect;//数据源 if(ds==null) return;
string saveFileName=""; bool fileSaved=false; SaveFileDialog saveDialog=new SaveFileDialog(); saveDialog.DefaultExt ="xls"; saveDialog.Filter="Excel文件|*.xls"; saveDialog.FileName ="Sheet1"; saveDialog.ShowDialog(); saveFileName=saveDialog.FileName; if(saveFileName.IndexOf(":")<0) return; //被点了取消
Excel.Application xlApp=new Excel.Application();
if(xlApp==null) { MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel"); return; }
Excel.Workbooks workbooks=xlApp.Workbooks;
Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//写入字段 for(int i=0;i<ds.Tables[0].Columns.Count;i++) {
worksheet.Cells[1,i+1]=ds.Tables[0].Columns[i].ColumnName; }
//写入数值 for(int r=0;r<ds.Tables[0].Rows.Count;r++) { for(int i=0;i<ds.Tables[0].Columns.Count;i++) { worksheet.Cells[r+2,i+1]=ds.Tables[0].Rows[r][i]; } System.Windows.Forms.Application.DoEvents(); } worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。 if(cmbxType.Text!="Notification") { Excel.Range rg=worksheet.get_Range(worksheet.Cells[2,2],worksheet.Cells[ds.Tables[0].Rows.Count+1,2]); rg.NumberFormat="00000000"; } if(saveFileName!="") {
try { workbook.Saved =true; workbook.SaveCopyAs(saveFileName); fileSaved=true; } catch(Exception ex) { fileSaved=false; MessageBox.Show("导出文件时出错,文件可能正被打开！
"+ex.Message); } } else { fileSaved=false; } xlApp.Quit(); GC.Collect();//强行销毁 if(fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打开EXCEL }
在项目中经常遇到读写EXCEL文件的需求。其实读取EXCEL很简单， 首先使用命名空间NExcel，然后加载文件到Workbook，循环读取就行了。
using NExcel;
Workbook wb = Workbook.getWorkbook(fileStream);
Sheet sheet = wb.Sheets[0];
int rowsCount = sheet.Rows;
for (int row = 2; row < rowsCount; row++)
{
string sheet.getCell(0, row).Contents;
decimal TotalPrice = Convert.ToDecimal(sheet.getCell(1, row).Value);
}
