using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.IO;
using System.Windows.Forms;
namespace ExportTest
{
/// <summary>
/// 将数据表格导出到excel中，可以同时添加图片
/// </summary>
public class ExportExcel
{
/// <summary>
/// 导出所需时间（秒）
/// </summary>
public static string Second = "0";
/// <summary>
/// 将表格列表中的各表格导入到指定文件的excel中
/// </summary>
/// <param name="ldt">表格列表</param>
/// <param name="fileName">导出到的excel文件名</param>
/// <param name="picName">要导出的图片文件全名</param>
public static void DataTableToExcel(List<DataTable> ldt, string fileName, string picName)
{
#region 导出表格数据
if (File.Exists(fileName))
{
File.Delete(fileName);
}
DateTime dt1 = System.DateTime.Now;
Microsoft.Office.Interop.Excel.Application appd = new Microsoft.Office.Interop.Excel.ApplicationClass();
if (appd == null)
{
throw new Exception("请先安装Excel！");
}
else
{
appd.Quit();
ReleaseObj(appd);
}
string connectionS = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(connectionS);
OleDbCommand oleCommand = conn.CreateCommand();
try
{
conn.Open();
for (int lcount = 0; lcount < ldt.Count; lcount++)
{
DataTable dt = ldt[lcount];
string dtName = dt.TableName;
if (dt.TableName == "")
{
dtName = "Table1";
}
//添加表格模板sql语句
string createTableSql = "CREATE TABLE ";
createTableSql += dtName;
createTableSql += "(";
//添加数据
string insertSql = "INSERT INTO ";
insertSql += dtName;
insertSql += " values(";
if (dt.Columns.Count > 0)
{
for (int c = 0; c < dt.Columns.Count; c++)
{
createTableSql += dt.Columns[c].ColumnName + " varchar,";
insertSql += "@" + dt.Columns[c].ColumnName + ",";
}
createTableSql = createTableSql.Trim(',');
createTableSql += ")";
insertSql = insertSql.Trim(',');
insertSql += ")";
oleCommand.CommandText = createTableSql;
oleCommand.ExecuteNonQuery();
}
//插入语句
oleCommand.CommandText = insertSql;
//数据导入表格模板
if (dt != null && dt.Rows.Count > 0)
{
int count = dt.Rows.Count;
for (int i = 0; i < count; i++)
{
oleCommand.Parameters.Clear();
for (int c = 0; c < dt.Columns.Count; c++)
{
oleCommand.Parameters.Add(new OleDbParameter("@" + dt.Columns[c].ColumnName, dt.Rows[i][c].ToString()));
}
oleCommand.ExecuteNonQuery();
}
}
}
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
#endregion
DateTime dt2 = System.DateTime.Now;
Comp(dt1, dt2);
object o = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
//打开excel文件
Microsoft.Office.Interop.Excel.Workbook newWorkbook = app.Workbooks.Open(fileName, o, o, o, o, o, o, o, o, o, o, o, o, o, o);
//打开工作薄
Excel.Worksheet newWorksheet;
newWorksheet = (Excel.Worksheet)newWorkbook.Worksheets[1];
//.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
//添加图片
newWorksheet.Shapes.AddPicture(picName, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoCTrue, 100, 100, 100, 100);
//释放资源
ReleaseObj(newWorksheet);
//关闭工作薄
newWorkbook.Close(true, o, o);
//推出excel
app.Quit();
//释放资源
ReleaseObj(newWorkbook);
ReleaseObj(app);
//释放资源
Dispose();
}
/// <summary>
/// 垃圾回收
/// </summary>
public static void Dispose()
{
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
}
/// <summary>
/// 释放对象
/// </summary>
/// <param name="o"></param>
private static void ReleaseObj(object o)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
}
catch { }
finally { o = null; }
}
/// <summary>
/// 比较时间之间的秒数
/// </summary>
/// <param name="dt1">开始时间</param>
/// <param name="dt2">结束时间</param>
public static void Comp(DateTime dt1, DateTime dt2)
{
Second = (dt2-dt1).TotalSeconds.ToString();
}
}
}
