Question

如何使用C#将DataTable中的数据导入Excle，求大神指导！！！

使用C#将DataTable中的数据导入Excle时，Excle.不能带出Applicationg,如上图，请教各位大神。来个完整的程序最好，一定要带上前面的using部分。

Answer 1

public bool CreateExcelFileForDataTable(System.Data.DataTable table, string strFullFilePath, string title)
{
FileInfo file = new FileInfo(strFullFilePath);
if (file.Exists)
{
file.Delete();
}
int rowIndex = 3; //开始写入数据的单元格行
int colIndex = 0; //开始写入数据的单元格列
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.ApplicationClass mExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
mExcel.Visible = false;
Microsoft.Office.Interop.Excel.Workbooks mBooks = (Microsoft.Office.Interop.Excel.Workbooks)mExcel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook mBook = (Microsoft.Office.Interop.Excel.Workbook)(mBooks.Add(miss));
Microsoft.Office.Interop.Excel.Worksheet mSheet = (Microsoft.Office.Interop.Excel.Worksheet)mBook.ActiveSheet;
Microsoft.Office.Interop.Excel.Range er = mSheet.get_Range((object)"A1", System.Reflection.Missing.Value); //向Excel文件中写入标题文本
er.Value2 = title;
try
{
foreach (DataColumn col in table.Columns) //将所得到的表的列名,赋值给单元格
{
colIndex++;
mSheet.Cells[3, colIndex] = col.ColumnName;
}
foreach (DataRow row in table.Rows) //同样方法处理数据
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in table.Columns)
{
colIndex++;
if (colIndex == 2)
{
mSheet.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();//第二行数据为银行帐号信息转换为字符防止首位0丢失
}
else
{
mSheet.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
}
//保存工作已写入数据的工作表
mBook.SaveAs(strFullFilePath, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
return true;
}
catch (Exception ee)
{
throw new Exception(ee.Message);
}
finally //finally中的代码主要用来释放内存和中止进程()
{
mBook.Close(false, miss, miss);
mBooks.Close();
mExcel.Quit();
GC.Collect();
}
}
引用里要添加：
Microsoft.Office.Interop.Excel；

追问

引用里要添加：
Microsoft.Office.Interop.Excel；就是这个东西，我的引用里带不出来，我难搞啊！

Answer 2

public static void ToExcel(DataTable dt, string Filter, string FileName, string SheetName)
{
if (string.IsNullOrEmpty(FileName))
{
return;
}

//要保证文件存在
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"" + FileName + "\";Extended Properties=\"Excel 8.0;HDR=YES\"";
OleDbConnection oleConnection = new OleDbConnection(connStr);
oleConnection.Open();
OleDbCommand oleCmd = new OleDbCommand();
//删除同名的表
try
{
oleCmd.Connection = oleConnection;
oleCmd.CommandText = string.Format("drop table [{0}]", SheetName);
oleCmd.CommandType = CommandType.Text;
oleCmd.ExecuteNonQuery();
}
catch
{

}
//创建表（相同结构）,表不能重复
StringBuilder strsql = new StringBuilder();
strsql.AppendFormat("Create table [{0}]", SheetName);
strsql.Append(" (");
foreach (DataColumn col in dt.Columns)
{
strsql.AppendFormat("[{0}] {1},", col.ColumnName, GetDataType(col.DataType));
}
strsql.Remove(strsql.Length - 1, 1);
strsql.Append(" ) ");

oleCmd.Connection = oleConnection;
oleCmd.CommandType = CommandType.Text;

oleCmd.CommandText = strsql.ToString();
oleCmd.ExecuteNonQuery();

oleConnection.Close();
//将dt中的数据填充到当前的excle的表中
OleDbDataAdapter oleAdpater = new OleDbDataAdapter("select * from [" + SheetName + "]", oleConnection);

DataTable dt_excel = new DataTable();
oleAdpater.Fill(dt_excel);

oleAdpater.InsertCommand = SqlInsert(SheetName, dt, oleConnection);
DataRow dr_excel;
if (Filter == null) Filter = "";
foreach (DataRow dr in dt.Select(Filter))
{
dr_excel = dt_excel.NewRow();
foreach (DataColumn col in dt.Columns)
{
dr_excel[col.ColumnName] = dr[col.ColumnName];
}
dt_excel.Rows.Add(dr_excel);
}
oleAdpater.Update(dt_excel);
}
不需要任何插件，只需要引用using System.Data.OleDb;直接可用
给你源码，自己研究去吧