先说本机环境：office2003。visval studio 2008 C#
一共两个问题：
1，新建windows项目，引入COM里的 microsoft excel 11.0 object library
2，代码里写 using Excel;
结果出错，说找不到Excel。
改用 using Excel=Microsoft.Office.Interop.Excel ;
才好。----为什么？使用 using Excel= Microsoft.Office.Interop.Excel ; 以后写下如下代码 Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
Excel.Range oRng; oXL = new Excel.Application();
oXL.Visible = false;
oWB = (Excel._Workbook)(oXL.Workbooks.Add(@"c:\1.xls"));
oSheet = (Excel._Worksheet)oWB.Sheets.get_Item (1);
string shtName = oSheet.Name.ToString();-------到这里，可以读出sheet表的表名 shtName =“测试sheet1”，说明已经找到了表。接下来：
for (int i = 2; i < 5; i++)
{
for (char j ='2'; j <'5'; j++)
{
string ss=oSheet.Cells[i, j].ToString ();
MessageBox.Show(ass);
}
发现这里读出的SS 全是 System._ComObject这是什么原因？
一共两个问题：
1，新建windows项目，引入COM里的 microsoft excel 11.0 object library
2，代码里写 using Excel;
结果出错，说找不到Excel。
改用 using Excel=Microsoft.Office.Interop.Excel ;
才好。----为什么？使用 using Excel= Microsoft.Office.Interop.Excel ; 以后写下如下代码 Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
Excel.Range oRng; oXL = new Excel.Application();
oXL.Visible = false;
oWB = (Excel._Workbook)(oXL.Workbooks.Add(@"c:\1.xls"));
oSheet = (Excel._Worksheet)oWB.Sheets.get_Item (1);
string shtName = oSheet.Name.ToString();-------到这里，可以读出sheet表的表名 shtName =“测试sheet1”，说明已经找到了表。接下来：
for (int i = 2; i < 5; i++)
{
for (char j ='2'; j <'5'; j++)
{
string ss=oSheet.Cells[i, j].ToString ();
MessageBox.Show(ass);
}
发现这里读出的SS 全是 System._ComObject这是什么原因？
还没见过
是不是因为这里的原因呢？
{
for (int j = 2; j < 5; j++)
{
MessageBox.Show(((Excel.Range)oSheet.Cells[i, j]).Text.ToString());
}
}
using System.Data;
using System.Collections.Generic;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data.OleDb;
using System.IO;
using System.Reflection;namespace ExcelImportAndExport
{ public class ExcelIO
{
private int _ReturnStatus;
private string _ReturnMessage; private const string CREATE_ERROR = "Can not create excel file, may be your computer has not installed excel!";
private const string IMPORT_ERROR = "Your excel file is open, please save and close it.";
private const string EXPORT_ERROR = "This is an error that the excel file may be open when it be exported. \n"; public int ReturnStatus
{
get { return _ReturnStatus; }
} public string ReturnMessage
{
get { return _ReturnMessage; }
} public ExcelIO()
{
} public DataSet ImportExcel(string fileName)
{
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
_ReturnStatus = -1;
_ReturnMessage = CREATE_ERROR;
return null;
} Excel.Workbook workbook;
try
{
workbook = xlApp.Workbooks.Open(fileName, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0);
}
catch
{
_ReturnStatus = -1;
_ReturnMessage = IMPORT_ERROR;
return null;
} int n = workbook.Worksheets.Count;
string[] SheetSet = new string[n];
System.Collections.ArrayList al = new System.Collections.ArrayList();
for (int i = 1; i <= n; i++)
{
SheetSet[i - 1] = ((Excel.Worksheet)workbook.Worksheets[i]).Name;
} workbook.Close(null, null, null);
xlApp.Quit();
if (workbook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
}
if (xlApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
GC.Collect(); DataSet ds = new DataSet();
string connStr = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + fileName + ";Extended Properties=Excel 8.0";
using (OleDbConnection conn = new OleDbConnection(connStr))
{
conn.Open();
OleDbDataAdapter da;
for (int i = 1; i <= n; i++)
{
string sql = "select * from [" + SheetSet[i - 1] + "$] ";
da = new OleDbDataAdapter(sql, conn);
da.Fill(ds, SheetSet[i - 1]);
da.Dispose();
}
conn.Close();
conn.Dispose();
}
return ds;
} public bool ExportExcel(DataSet ds, string saveFileName)
{
if (ds == null)
{
_ReturnStatus = -1;
_ReturnMessage = "The DataSet is null!";
return false;
} bool fileSaved = false;
Excel.Application xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
_ReturnStatus = -1;
_ReturnMessage = CREATE_ERROR;
return false;
} Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
for (int j = ds.Tables.Count - 1; j >= 0; j--)
{
DataTable dt = ds.Tables[j];
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
worksheet.Name = ds.Tables[j].TableName.ToString();
worksheet.Cells.Font.Size = 10;
Excel.Range range; long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0; for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
range = (Excel.Range)worksheet.Cells[1, i + 1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true; }
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
} range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 1, dt.Columns.Count]);
range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);
if (dt.Rows.Count > 0)
{
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
}
if (dt.Columns.Count > 1)
{
range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
} if (range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
if (worksheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
worksheet = null;
}
} ((Excel.Worksheet)workbook.Worksheets[ds.Tables.Count+1]).Delete();
if (saveFileName != "")
{
try
{
workbook.Saved = true;
System.Reflection.Missing miss = System.Reflection.Missing.Value;
workbook.SaveAs(saveFileName, Excel.XlFileFormat.xlExcel8, miss, miss, miss, miss,
Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
fileSaved = true;
}
catch (Exception ex)
{
fileSaved = false;
_ReturnStatus = -1;
_ReturnMessage = EXPORT_ERROR + ex.Message;
}
}
else
{
fileSaved = false;
}
if (workbook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
}
if (workbooks != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
workbooks = null;
}
xlApp.Application.Workbooks.Close();
xlApp.Quit();
if (xlApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
GC.Collect();
return fileSaved;
}
}
}
MessageBox.Show(((Excel.Range)oSheet.Cells[1, 1]).Value2.ToString()); // 33219
using Excel= Microsoft.Office.Interop.Excel;
是创建命名空间的别名
Excel是别名，使用时与完整的命名空间等效，使程序代码变的更简洁。
为什么呢？又下了另外一个程序，也是引用了自己带的excel.dll等，程序里写了using Excel，就可以用了。我新建了一个程序，把他们的excel.dll等拷贝过来，然后引用。结果无论写不写using Excel，均无法在程序里使用 Excel。必须写 using Microsoft.Office.Interop.Excel 为什么呢？是vs2008的版本原因吗？
(1) 将Office目录下的Excel.exe文件复制到C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin里
(2) 然后运行VS2005命令提示输入TlbImp EXCEL.EXE Excel.dll
(3) 生成结果： C:\Program Files\Microsoft Visual Studio 8\VC\Excel.dll