Question

着急，c#操作excel 问题，求解啊！！！

Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass(); 这句没错（开始都出错了，后来我网上找方法把互操作类型改成false就好了）
关键的地方到了： excel.Application.Workbooks.Add(true); 这个excel报错了啊，说 excel is a field but use like a type~ 我擦，咋回事啊
Microsoft.Office.Interop.Excel 这个组件我已经引进来了，可是依然无法用using Excel ，这我也就不在乎了，网上说直接用Microsoft.Office.Interop.Excel这个就行，但是excel咋还错了呢
Vivian陈薇
我那边的代码也试了，哪次都是到
ApplicationClass myExcel = new ApplicationClass();
Worksheet worksheet = null;
object missing = System.Reflection.Missing.Value;
myExcel.Workbooks.Add(missing); 这里 又说myExcel is a field but used like a type ，我自己觉得应该是包没引对，我解决不了了， “ is a field but used like a type”这句话快恶心死我了。。。 我引的包是Microsoft Excel 14.0 Object Library 可能是因为我Excel 版本高吧（win8 默认的）

其他大神也帮我看一下呗

Answer 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);
}
}
}

Answer 2

参考一下这个http://zhidao.baidu.com/question/1574061821047183860.html?oldq=1

追问

我那边的代码也试了，哪次都是到 myExcel.Workbooks.Add(missing); 这里 又说myExcel is a field but used like a type ，我自己觉得应该是包没引对，我解决不了了

追答

需要添加COM组件 Interop.Excel.dll,Interop.Microsoft.Office.Core.dll，你添加了吗

追问

Microsoft.Office.Interop.Excel 引了

Answer 3

为什么用这个技术啊，这不是要启动excel进程吗，不复杂的话用NOPI足够用了。

追问

您能说的再具体一点吗？ NOPI怎么引，基本操作之类的~~~

追答

nopi是一个组件。dll格式的，你把它挎到你项目目录下，随便指个目录都可以。然后，添加引用，选择所有的dll就可以了，低版本的nopi的dll有好几个，都要引入，高版本好象就一个。具体引用以后，使用的方法，我给你个地址：http://user点qzone点qq点com/315426452/blog/1385110354。这个里面有它的使用方法，把地址里的点换成点符号，你明白的，要不百度会过滤的。至于NOPI，你到网上搜索一下。或是用这个http://pan.baidu.com/s/1ntz5E8T，呵，祝你成功