/// <summary>
/// 把DataTable导出Excel表
/// </summary>
/// <param name="dataTable">DataTable</param>
/// <returns>true为成功，false为失败</returns>
private static bool _ExportExcel(DataTable dataTable, string fileName)
{
bool Flag = false;
//Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
try
{
//没有数据的话就不往下执行
if (dataTable.Rows.Count == 0)
{
return(false);
}
excel.Application.Workbooks.Add();
excel.Visible = false;//要不要显示Excel表
//判断电脑是否有Excel
if (excel == null)
{
return(false);
}
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet;//
// 生成字段名称
for (int i = 0; i < dataTable.Columns.Count; i++)
{
excel.Cells[1, i + 1] = dataTable.Columns[i].ColumnName;
((Microsoft.Office.Interop.Excel.Range)sheet.Cells[1, i + 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
}
//填充数据
for (int i = 0; i < dataTable.Rows.Count - 1; i++)
{
for (int j = 0; j < dataTable.Columns.Count; j++)
{
excel.Cells[i + 2, j + 1] = "" + dataTable.Rows[i][j].ToString();
((Microsoft.Office.Interop.Excel.Range)sheet.Cells[i + 2, j + 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
}
}
SetColumnFit(sheet);//设置自适应宽度
//保存Excel表
excel.Save(fileName);
Flag = true;
}
catch
{
throw;
}
finally
{
//设置禁止弹出保存和覆盖的询问提示框
excel.DisplayAlerts = true;
excel.AlertBeforeOverwriting = true;
excel.Workbooks.Close();
//确保Excel进程关闭
excel.Quit();
excel = null;
//垃圾回收
GC.Collect();
}
return(Flag);
}
/// <summary>
/// 将DataTable的数据导出显示为报表
/// </summary>
/// <param name="dt">要导出的数据</param>
/// <param name="strTitle">导出报表的标题</param>
/// <param name="FilePath">保存文件的路径</param>
/// <returns></returns>
public static void OutputExcel(System.Data.DataTable dt, string strTitle, string filePath)
{
var beforeTime = DateTime.Now;
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel._Workbook xBk;
Microsoft.Office.Interop.Excel._Worksheet xSt;
int rowIndex = 4;
int colIndex = 1;
excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
xBk = excel.Workbooks.Add(true);
xSt = (Microsoft.Office.Interop.Excel._Worksheet)xBk.ActiveSheet;
//取得列标题
foreach (DataColumn col in dt.Columns)
{
colIndex++;
excel.Cells[4, colIndex] = col.ColumnName;
//设置标题格式为居中对齐
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Font.Bold = true;
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Select();
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[4, colIndex]).Interior.ColorIndex = 15;//19;//设置为浅黄色，共计有56种
}
//取得表格中的数据
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 1;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
if (col.DataType == System.Type.GetType("System.DateTime"))
{
excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd");
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
}
else
if (col.DataType == System.Type.GetType("System.String"))
{
excel.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; //设置字符型的字段格式为居中对齐
}
else
{
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
}
//加载一个合计行
int rowSum = rowIndex + 1;
int colSum = 2;
excel.Cells[rowSum, 2] = "合计";
xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, 2]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
//设置选中的部分的颜色
xSt.get_Range(excel.Cells[rowSum, colSum], excel.Cells[rowSum, colIndex]).Select();
//xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex =Assistant.GetConfigInt("ColorIndex");// 1;//设置为浅黄色，共计有56种
//取得整个报表的标题
excel.Cells[2, 2] = strTitle;
//设置整个报表的标题格式
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Bold = true;
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, 2]).Font.Size = 22;
//设置报表表格为最适应宽度
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Select();
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Columns.AutoFit();
//设置整个报表的标题为跨列居中
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).Select();
xSt.get_Range(excel.Cells[2, 2], excel.Cells[2, colIndex]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenterAcrossSelection;
//绘制边框
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, colIndex]).Borders.LineStyle = 1;
xSt.get_Range(excel.Cells[4, 2], excel.Cells[rowSum, 2]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick; //设置左边线加粗
xSt.get_Range(excel.Cells[4, 2], excel.Cells[4, colIndex]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick; //设置上边线加粗
xSt.get_Range(excel.Cells[4, colIndex], excel.Cells[rowSum, colIndex]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick; //设置右边线加粗
xSt.get_Range(excel.Cells[rowSum, 2], excel.Cells[rowSum, colIndex]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick; //设置下边线加粗
var afterTime = DateTime.Now;
//显示效果
//excel.Visible = true;
//excel.Sheets[0] = "sss";
// ClearFile(FilePath);
//string filename = DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";
//excel.ActiveWorkbook.SaveAs(filePath, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
//wkbNew.SaveAs strBookName;
try
{
excel.Save();
}
catch (Exception)
{
return;
}
#region 结束Excel进程
//需要对Excel的DCOM对象进行配置:dcomcnfg
//excel.Quit();
//excel=null;
xBk.Close(null, null, null);
excel.Workbooks.Close();
excel.Quit();
//注意：这里用到的所有Excel对象都要执行这个操作，否则结束不了Excel进程
// if(rng != null)
// {
// System.Runtime.InteropServices.Marshal.ReleaseComObject(rng);
// rng = null;
// }
// if(tb != null)
// {
// System.Runtime.InteropServices.Marshal.ReleaseComObject(tb);
// tb = null;
// }
if (xSt != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xSt = null;
}
if (xBk != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
xBk = null;
}
if (excel != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
excel = null;
}
GC.Collect();//垃圾回收
#endregion
return;
}
