在此之前,要添加using Microsoft.Office.Interop.Excel;引用
System.Data.DataTable dt = new BLL.doctorBLL().select();
string filename = "导出excel";
DataTableToExcel(dt, filename);
}
/// <summary>
///
/// </summary>
/// <param name="dt">需要导出到excel的数据表dt</param>
/// <param name="saveFileName">存储文件名</param>
/// <returns></returns>
public static void DataTableToExcel(System.Data.DataTable dt, string saveFileName)
{
if (dt == null) return;
//-***************获取excel对象***************
// string saveFileName = "";
TimeSpan dateBegin = new TimeSpan(DateTime.Now.Ticks);
bool fileSaved = false;
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = "导入记录查询结果 " + DateTime.Today.ToString("yyyy-MM-dd");
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0) return; //被点了取消
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法启动Excel,可能您未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbook workbook = xlApp.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range;
// string oldCaption = dt.CaptionText;
// 列索引,行索引,总列数,总行数
int colIndex = 0;
int RowIndex = 0;
int colCount = dt.Columns.Count;
int RowCount = dt.Rows.Count;
// *****************获取数据*********************
//dataGrid1.CaptionVisible = true;
//dataGrid1.CaptionText = "正在导出数据";
// 创建缓存数据
object[,] objData = new object[RowCount + 1, colCount];
// 获取列标题
for (int i = 0; i < dt.Columns.Count; i++)
{
objData[RowIndex, colIndex++] = dt.Columns[i].Caption; // dgv.Columns[i].HeaderText;
}
// 获取具体数据
for (RowIndex = 1; RowIndex <= RowCount; RowIndex++)
{
for (colIndex = 0; colIndex <colCount; colIndex++)
{
objData[RowIndex, colIndex] = dt.Rows[RowIndex - 1][colIndex];
}
}
//********************* 写入Excel*******************
range = worksheet.get_Range(xlApp.Cells[2,1],xlApp.Cells[RowCount+2, colCount]);
range.Value2 = objData;
System.Windows.Forms.Application.DoEvents();
//*******************设置输出格式******************************
//设置顶部説明 合并的单元格
range = worksheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colCount]);
range.MergeCells = true;
range.RowHeight = 38;
range.Font.Bold = true;
range.Font.Size = 14;
range.Font.ColorIndex = 10;//字体颜色
xlApp.ActiveCell.FormulaR1C1 = "导入记录查询结果";
//特殊数字格式
range = worksheet.get_Range(xlApp.Cells[2, colCount], xlApp.Cells[RowCount, colCount]);
// range.NumberFormat = "yyyy-MM-dd hh:mm:ss";
xlApp.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
range = worksheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colCount]);
// range.Interior.ColorIndex = 10;//背景色
range.Font.Bold = true;
range.RowHeight = 20;
//((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, 1]).ColumnWidth = 25;
//((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, 2]).ColumnWidth = 13;
//((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, 3]).ColumnWidth = 18;
//((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, 4]).ColumnWidth = 15;
//((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, 5]).ColumnWidth = 22;
//***************************保存**********************
//dataGrid1.CaptionVisible = false;
//dataGrid1.CaptionText = oldCaption;
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();//强行销毁
TimeSpan dateEnd = new TimeSpan(DateTime.Now.Ticks);
TimeSpan tspan = dateBegin.Subtract(dateEnd).Duration();
MessageBox.Show("导出成功,用时" + tspan.ToString() + "秒");
if (fileSaved && System.IO.File.Exists(saveFileName))
System.Diagnostics.Process.Start(saveFileName); //保存成功后打开此文件
}