C#导出EXCEL代码1

using System;
using System.IO;
using System.Text;
using System.Data;
using System.Reflection;
using System.Diagnostics;
using System.Collections;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data.Odbc;
using System.Collections.Generic;
using Microsoft.Office.Interop.Excel;
using System.Web;
using System.Data.OleDb;
//using cfg = System.Configuration;
namespace WX.Common
{
/// <summary>
/// Excel数据操作类
/// </summary>
public class ExcelHelper
{
//定义变量的缺省值
private static object mValue = System.Reflection.Missing.Value;

/// <summary>
/// 导出CVS文件数据
/// </summary>
/// <param name="fileName"></param>
/// <param name="path"></param>
/// <returns></returns>
public static DataSet ConnectCSVFile(string fileName, string path)
{
string strConn = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=";
strConn += path;
strConn += ";Extensions=asc,csv,tab,txt;HDR=Yes;Persist Security Info=False";
OdbcConnection objConn = new OdbcConnection(strConn);
DataSet ds = new DataSet();
try
{
string strSql = "select * from " + fileName; //fileName, For example: 1.csv
OdbcDataAdapter odbcCSVDataAdapter = new OdbcDataAdapter(strSql, objConn);
odbcCSVDataAdapter.Fill(ds);
}
catch (Exception ex)
{
throw ex;
}
finally
{
System.GC.Collect();
}

return ds;
}

/// <summary>
/// 导出Excel文件
/// </summary>
/// <param name="filepath"></param>
/// <param name="ExcelName"></param>
/// <returns></returns>
/// IMEX=1将强制混合数据转换为文本，HDR=NO将第一行作为内容，由于第一行Header都是文本，因此所有列的类型都将转换成文本。

public static DataSet ExcelToDataTable(string filepath, string sheetName)
{
string strConn = string.Empty;
string type = filepath.Substring(filepath.LastIndexOf('.') + 1, filepath.Length - filepath.LastIndexOf('.') - 1);
if (type == "xlsx")
{
strConn = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + filepath + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
}
else
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;IMEX=1;HDR=YES'";
}
System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strConn);
string strCom = "SELECT * FROM " + "[" + sheetName.Replace('#', '.').Replace("$", "").Replace("'","") + "$]";//读取Excel文件内容
Conn.Open();
System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn);
DataSet ds = new DataSet();
myCommand.Fill(ds, sheetName);
Conn.Close();
return ds;

}

/// <summary>
/// 导入Excel文件
/// </summary>
/// <param name="tmpDataTable"></param>
/// <param name="strFileName"></param>
public static void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName)
{
//检查进程 
List<Process> excelProcesses = GetExcelProcesses();
if (excelProcesses.Count > 0)
{
//杀死进程
KillTheExcel();
}

Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
try
{
if (tmpDataTable == null)
return;
int rowNum = tmpDataTable.Rows.Count;
int columnNum = tmpDataTable.Columns.Count;
int rowIndex = 1;//如果需要导出列名，设置为1，否则设置为0
int columnIndex = 0;

xlApp.DefaultFilePath = "";
xlApp.Visible = false;
xlApp.DisplayAlerts = false;
xlApp.SheetsInNewWorkbook = 1;

Excel.Worksheet ExcelSheet = (Worksheet)xlBook.Worksheets[1];
ExcelOperate operate = new ExcelOperate();
operate.SetBold(ExcelSheet, ExcelSheet.Cells[1, 1], ExcelSheet.Cells[1, columnNum]);
operate.SetHAlignCenter(ExcelSheet, ExcelSheet.Cells[1, 1], ExcelSheet.Cells[rowNum + 1, columnNum]);//居中 
ExcelSheet.Columns.EntireRow.AutoFit();
ExcelSheet.Rows.EntireColumn.AutoFit();
//将DataTable的列名导入Excel表第一行(如果需要可以加上)
foreach (DataColumn dc in tmpDataTable.Columns)
{
columnIndex++;
xlApp.Cells[rowIndex, columnIndex] = dc.ColumnName;
}

//将DataTable中的数据导入Excel中
for (int i = 0; i < rowNum; i++)
{
rowIndex++;
columnIndex = 0;
for (int j = 0; j < columnNum; j++)
{
columnIndex++;
xlApp.Cells[rowIndex, columnIndex] = tmpDataTable.Rows[i][j].ToString();
}
}

//保存
xlBook.SaveAs(strFileName, mValue, mValue, mValue, mValue, mValue, Excel.XlSaveAsAccessMode.xlShared
, mValue, mValue, mValue, mValue, mValue);
}
catch (Exception ex)
{
throw ex;
}

finally
{
xlApp.Workbooks.Close();
//关闭进程，自动保存
xlApp.Quit();
System.GC.Collect();
}
}

public static string ExcelSheetName(string filepath)
{
string al = string.Empty;
string strConn;
string type = filepath.Substring(filepath.LastIndexOf('.') + 1, filepath.Length - filepath.LastIndexOf('.') - 1);
if (type == "xlsx")
{
strConn = "Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + filepath + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
}
else
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;IMEX=1;HDR=YES'";
}
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
System.Data.DataTable sheetNames = conn.GetOleDbSchemaTable
(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
conn.Close();
if (sheetNames.Rows.Count > 0)
{
al = sheetNames.Rows[0][2].ToString();
if (!string.IsNullOrEmpty(al))
{
al = al.Substring(0, al.Length - 1);
}
}
//foreach (DataRow dr in sheetNames.Rows)
//{
// al = dr[2].ToString();
// if (!string.IsNullOrEmpty(al))
// {
// al = al.Substring(0, al.Length - 1);
// }
//}
return al;
}

public void ExportExcel(System.Data.DataTable dt, StreamWriter w)
{
try
{
for (int i = 0; i < dt.Columns.Count; i++)
{
w.Write(dt.Columns[i]);
w.Write(' ');
}
w.Write(" ");

object[] values = new object[dt.Columns.Count];
foreach (DataRow dr in dt.Rows)
{
values = dr.ItemArray;
for (int i = 0; i < dt.Columns.Count; i++)
{
w.Write(values[i]);
w.Write(' ');
}
w.Write(" ");
}
w.Flush();
w.Close();
}
catch
{
w.Close();
}
}

/// <summary>
/// 获得进程
/// </summary>
/// <returns></returns>
private static List<Process> GetExcelProcesses()
{
//获得当前计算机的进程列表
Process[] processes = Process.GetProcesses();
//保存Excel进程
List<Process> ListProcess = new List<Process>();

foreach (Process _pr in processes)
{
if (_pr.ProcessName.ToUpper().Equals("EXCEL"))
{
ListProcess.Add(_pr);
}
}
return ListProcess;
}

/// <summary>
/// 销毁所有Excel进程
/// </summary>
public static void KillTheExcel()
{
List<Process> listProcess = GetExcelProcesses();
foreach (Process _pr in listProcess)
{
_pr.Kill();
}
}

/// <summary>
/// 释放内存
/// </summary>
public void Dispose(Excel._Worksheet CurSheet, Excel._Workbook CurBook, Excel._Application CurExcel)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurSheet);
CurSheet = null;
CurBook.Close(false, Missing.Value, Missing.Value);
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurBook);
CurBook = null;

CurExcel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(CurExcel);
CurExcel = null;

GC.Collect();
GC.WaitForPendingFinalizers();
}
catch (System.Exception ex)
{
HttpContext.Current.Response.Write("在释放Excel内存空间时发生了一个错误:" + ex);
}
finally
{
foreach (System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel"))
pro.Kill();
}
System.GC.SuppressFinalize(this);
}
}
}