查看完整版本: .net导出excel通用方法

.net导出excel通用方法

publicvoidExportToExcel(stringfilename,System.Data.DataTabledt,stringexcelname)
{if(dt==null)return;stringsaveFileName="";
boolfileSaved=false;
SaveFileDialogsaveDialog=newSaveFileDialog();
saveDialog.DefaultExt="xls";
saveDialog.Filter="Excel文件│*.xls";
saveDialog.FileName=filename;
saveDialog.ShowDialog();
saveFileName=saveDialog.FileName;
if(saveFileName.IndexOf(":")%26lt;0)return;//被点了取消

Excel.ApplicationxlApp=newExcel.Application();if(xlApp==null)
{
MessageBox.Show("无法创建Excel[wiki]对象[/wiki]，可能您的机子未安装Excel");
return;
}Excel.Workbooksworkbooks=xlApp.Workbooks;
Excel.Workbookworkbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheetworksheet=(Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
Excel.Rangerange;
longtotalCount=dt.Rows.Count;
longrowRead=0;
floatpercent=0;worksheet.Cells[1,1]=excelname;
//写入字段
for(inti=0;i%26lt;dt.Columns.Count;i++)
{
worksheet.Cells[2,i+1]=dt.Columns.ColumnName;
range=(Excel.Range)worksheet.Cells[2,i+1];
range.Interior.ColorIndex=15;
range.Font.Bold=true;

}
//写入数值
//this.CaptionVisible=true;
for(intr=0;r%26lt;dt.Rows.Count;r++)
{
for(inti=0;i%26lt;dt.Columns.Count;i++)
{
worksheet.Cells[r+3,i+1]=dt.Rows[r];
}
rowRead++;
percent=((float)(100*rowRead))/totalCount;
//this.Capti+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[dt.Rows.Count+2,dt.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(dt.Columns.Count%26gt;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;
}
catch(Exceptionex)
{
fileSaved=false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n"+ex.Message);
}
}
else
{
fileSaved=false;
}
xlApp.Quit();
GC.Collect();//强行销毁
if(fileSaved%26%26File.Exists(saveFileName))System.Diagnostics.Process.Start(saveFileName);
}