/// <summary>
/// DataTable直接导出Excel,此方法会把DataTable的数据用Excel打开,再自己手动去保存到确切的位置
/// </summary>
/// <param name="dt">要导出Excel的DataTable</param>
/// <returns></returns>
public static bool DoExport(System.Data.DataTable dt, string serverName)
{
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass();
if (app == null)
{
Log.AddLog(DateTime.Now.ToString() + ":Excel无法启动");
return(false);
}
app.Visible = false;
app.DisplayAlerts = false;
app.AlertBeforeOverwriting = false;
Workbooks wbs = app.Workbooks;
Workbook wb = wbs.Add(Missing.Value);
Worksheet ws = (Worksheet)wb.Worksheets[1];
int rowcnt = dt.Rows.Count;
int colcnt = dt.Columns.Count;
object[,] objData = new Object[rowcnt + 1, colcnt];
//获取列标题
for (int i = 0; i < colcnt; i++)
{
objData[0, i] = dt.Columns[i].ColumnName;
}
//获取具体数据
for (int i = 0; i < rowcnt; i++)
{
DataRow dr = dt.Rows[i];
for (int j = 0; j < colcnt; j++)
{
objData[i + 1, j] = dr[j];
}
}
//写入Excel
Range r = ws.get_Range(app.Cells[1, 1], app.Cells[rowcnt + 1, colcnt]);
r.NumberFormat = "@";
r.Value2 = objData;
r.EntireColumn.AutoFit();
try
{
#region GetSaveAsFilename
string fileName = "E:\\" + serverName + DateTime.Now.ToString("yyyyMMdd");
string fileFilter = string.Format("{0}(*.xlsx),*.xlsx,{1}(*.xls),*.xls,{2}(*.*),*.*", TableManager.GetGMTText(849), TableManager.GetGMTText(849), TableManager.GetGMTText(851));
string filePath = (string)app.GetSaveAsFilename(fileName, fileFilter, 1, TableManager.GetGMTText(848), Missing.Value);
wb.SaveAs(filePath);
#endregion
app.Workbooks.Close();
app.Quit();
app = null;
return(true);
}
catch (Exception e)
{
Log.AddLog(DateTime.Now.ToString() + "：" + e.Message);
return(false);
}
}
