// 以DataSet- 导出Excel文件
public void ExportExcel()
{
DataSet ds=this.dataset_zhao_sheng1;//取得dataGrid绑定的DataSet
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
Excel.Range range;
long totalCount=ds.Tables[0].Rows.Count;
long rowRead=0;
float percent=0;
worksheet.Cells[1,1]="学生名单";
//写入字段
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") +"%]...";
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;
}
catch(Exception ex)
{
fileSaved=false;
MessageBox.Show("导出文件时出错,文件可能正被打开!/n"+ex.Message);
}
}
else
{
fileSaved=false;
}
xlApp.Quit();
GC.Collect();//强行销毁
if(fileSaved && File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName);
}
// DataGrid - 导出Excel文件
public static void ExportDataGridToExcel(DataGrid grid,string ReportTitle)
{
DataTable myTable = ((DataSet)grid.DataSource).Tables[0];
try
{
Excel.Application xlApp = new Excel.ApplicationClass();
int rowIndex;
int colIndex;
rowIndex = 2;
colIndex = 0;
Excel.Workbook xlBook =xlApp.Workbooks.Add(true);
if (grid.TableStyles.Count >0 )
{
Excel.Range range = xlApp.get_Range(xlApp.Cells[1,1],xlApp.Cells[1,grid.TableStyles[0].GridColumnStyles.Count]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = ReportTitle;
xlApp.ActiveCell.Font.Size = 18;
xlApp.ActiveCell.Font.Bold = true;
foreach(DataGridColumnStyle colu in grid.TableStyles[0].GridColumnStyles)
{
colIndex=colIndex +1;
xlApp.Cells[2,colIndex] = colu.HeaderText ;
}
//得到的表所有行,赋值给单元格
for (int row = 0;row < myTable.Rows.Count;row++)
{
rowIndex = rowIndex + 1;
colIndex = 0;
for (int col=0;col<grid.TableStyles[0].GridColumnStyles.Count;col++)
{
colIndex = colIndex + 1;
xlApp.Cells[rowIndex, colIndex] = grid[row,col].ToString();
}
}
}
else
{
Excel.Range range = xlApp.get_Range(xlApp.Cells[1,1],xlApp.Cells[1,myTable.Columns.Count]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = ReportTitle;
xlApp.ActiveCell.Font.Size = 18;
xlApp.ActiveCell.Font.Bold = true;
//将表中的栏位名称填到Excel的第一行
foreach(DataColumn Col in myTable.Columns)
{
colIndex = colIndex + 1;
xlApp.Cells[2, colIndex] = Col.ColumnName;
}
//得到的表所有行,赋值给单元格
for (int row = 0;row < myTable.Rows.Count;row++)
{
rowIndex = rowIndex + 1;
colIndex = 0;
for (int col=0;col<myTable.Columns.Count;col++)
{
colIndex = colIndex + 1;
xlApp.Cells[rowIndex, colIndex] = grid[row,col].ToString();
}
}
}
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colIndex]).Font.Bold = true;
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[rowIndex, colIndex]).Borders.LineStyle = 1;
xlApp.Cells.EntireColumn.AutoFit();
xlApp.Cells.VerticalAlignment = Excel.Constants.xlCenter ;
xlApp.Cells.HorizontalAlignment = Excel.Constants.xlCenter ;
xlApp.Visible = true;
}
catch(Exception e)
{
throw e;
}
}