第一步,引用 com组件 excel.dll
如何生成excel.dll
2.把[C:\Program Files\Microsoft Office\Office:默认安装路径]下的EXCEL9.OLB文件拷贝到[C:\Visual Studio.Net\SDK\v1.1\Bin:VS.Net安装路径]路径下。
3.打开Visual Studio .Net2003命令提示,运行TlbImp Excel9.olb Excel.dll ,就会在[C:\Visual Studio.Net\SDK\v1.1\Bin]下生成Excel.dll组件。
4.在项目中添加Excel.dll引用就OK了。
Excel2003生成Excel.dll的方法
然后运行VS2005命令提示输入TlbImp EXCEL.EXE Excel.dll
然后会在C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0下找到Excel.dll Office .dll 和VBIDE.dll
第二步,调用此方法即可将dataSet的数据导出到本地
public static void ExporToExcel(DataSet ds)
{
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.ApplicationClass();
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
Excel.Range range;
// string oldcaption=this.captiontext;
long totalcount=ds.Tables[0].Rows.Count;
long rowread=0;
float percent=0;
// worksheet.Cells[1,1]=this.captiontext;
//写入字段
for(int i=0;i<ds.Tables[0].Columns.Count;i++)
{
worksheet.Cells[2,i+1]=ds.Tables[0].Columns[i].ColumnName;
range=(Excel.Range)worksheet.Cells[2,i+1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
}
//写入数值
// this.captionvisible = true;
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+3,i+1]=ds.Tables[0].Rows[r][i];
}
rowread++;
percent=((float)(100*rowread))/totalcount;
// this.captiontext = "正在导出数据["+ percent.tostring("0.00") +"%]";
System.Windows.Forms.Application.DoEvents();
}
// this.captionvisible = false;
// this.captiontext = oldcaption;
range=worksheet.get_Range(worksheet.Cells[2,1],worksheet.Cells[ds.Tables[0].Rows.Count+2,ds.Tables[0].Columns.Count]);
range.BorderAround(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlThin,Excel.XlColorIndex.xlColorIndexAutomatic,null);
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle =Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight =Excel.XlBorderWeight.xlThin;
if(ds.Tables[0].Columns.Count>1)
{
range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex =Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
}
if(savefilename!="")
{
try
{
workbook.Saved =true;
workbook.SaveCopyAs(savefilename);
filesaved=true;
MessageBox.Show("文件已经成功导出...");
}
catch(Exception ex)
{
filesaved=false;
MessageBox.Show("导出文件时出错,文件可能正被打开!\n"+ex.Message);
}
}
else
{
filesaved=false;
}
xlapp.Quit();
GC.Collect();//强行销毁
}