一
首先Add References Excel.dll,注意在MS office 2003的版本中,可能找不到Excel.dll,这时需要从Excel.exe中扣出来,怎么扣?网上有很多方法,扣出来的同时也把Excel.dll变成了受托管的。
1.读取Excel File。
其实读取Excel和读取数据库的方式差不多:
1 string StrConn = " Provider=Microsoft.Jet.OLEDB.4.0; " + " Data Source= " + FilePath + " ;Extended Properties='Excel 8.0;HDR=NO' " ;
2 OleDbConnection MyConn = new OleDbConnection(StrConn);
3 MyConn.Open();
4 string StrCmd = " select * from [sheet1$] " ;
5 OleDbDataAdapter MyCommand = new OleDbDataAdapter(StrCmd,MyConn);
6 DataSet Ds = new DataSet();
7 MyCommand.Fill(Ds, " NameTB " );
8 DataTable dt = Ds.Tables[ " NameTB " ]
#endregion
如果Excel第一行是表头的话,可以把StrConn里面的HDR=NO改成,HDR=YES; 当然这样读出来的数据有时候是有问题的,比如本来只有3行的数据,却读来有>3行,我还不知道是什么原因,哪位知道告诉我谢谢。那么这个时候可以用下面的方法判断真实的行数:
2 if (Ds.Tables[ " NameTB " ].Rows[Ds.Tables[ " NameTB " ].Rows.Count - 1 ][ 0 ].ToString() != string .Empty)
3 b = Ds.Tables[ " NameTB " ].Rows.Count;
4 else
5 {
6 while (Ds.Tables[ " NameTB " ].Rows[b][ 0 ].ToString() != string .Empty)
7 {
8 b ++ ;
9 }
10 }
这里的Excel中的数据是一列多行。同时在从Table 中取出来用的时候要保证Ds.Tables["NameTB"].Rows[i][0].ToString() .Trim()!= string.Empty。
2.生成Excel并插入数据。
有时我们需要把检索出来的数据导出到Excel。下面说说如何生成,插入数据到Excel. 首先Add References Excel.dll,引用using Excel;
下面的代码是将ListView中的数据导入到Excel:
2 Excel.ApplicationClass m_objExcel = new Excel.ApplicationClass();
m_objExcel.Visible = false ;
3 Excel.Workbooks m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
4 Excel.Workbook m_objBook = (Excel.Workbook)(m_objBooks.Add(miss));
5 Excel.Worksheet m_objSheet = (Excel.Worksheet)m_objBook.ActiveSheet;
6 Excel.Range ER = null ;
16 ER = m_objSheet.get_Range(( object ) " A1 " , System.Reflection.Missing.Value);
17 ER.Value2 = " Last Name " ;
18 ER.ColumnWidth = 12 ;
19 ER = m_objSheet.get_Range(( object ) " B1 " , System.Reflection.Missing.Value);
20 ER.Value2 = " Frst Name " ;
21 ER.ColumnWidth = 15 ;
22 ER = m_objSheet.get_Range(( object ) " C1 " , System.Reflection.Missing.Value);
23 ER.Value2 = " Address " ;
24 ER.ColumnWidth = 35 ;
43 m_objSheet.get_Range( " A1 " , " D1 " ).Font.Bold = true ;
45 m_objSheet.get_Range( " A1 " , " D1 " ).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
47 for ( int i = 2 ; i < this .lvResult.Items.Count + 2 ; i ++ )
48 {
50 m_objExcel.Cells[i, 1 ] = this .lvResult.Items[i - 2 ].SubItems[ 1 ].Text.ToString().Trim();
51 m_objExcel.Cells[i, 2 ] = this .lvResult.Items[i - 2 ].SubItems[ 2 ].Text.ToString().Trim();
52 m_objExcel.Cells[i, 3 ] = this .lvResult.Items[i - 2 ].SubItems[ 3 ].Text.ToString().Trim();
59 }
//生成Excel.
61 m_objBook.SaveAs(SavePath, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
62 //释放资源。
63 m_objBook.Close( false , miss, miss);
64 m_objBooks.Close();
65 m_objExcel.Quit();
66
67 System.Runtime.InteropServices.Marshal.ReleaseComObject(ER);
68 System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objSheet);
69 System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook);
70 System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks);
71 System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
72 GC.Collect();
对IO操作一般都要考虑到异常,自己添加。在网上也找了一些例子,觉得上面的资源释放机制是相对比较好的。
二
C#读写EXCEL方法一
using System;
using System.Collections.Generic;
using System.Drawing;
using System.Reflection;
using System.IO;
namespace ExcelTest
{
class Program
{
static void Main(string[] args)
{
创建Application对象
Excel.Application xlsApp = new Excel.Application();
if (xlsApp == null)
{
return;
}
xlsApp.Visible = true;
//得到WorkBook对象, 可以用两种方式
//之一: 打开已有的文件
//Excel.Workbook xlsBook = xlsApp.Workbooks.Open(@"E:/Documents and Settings/daniel.chen/Desktop/test.xls",Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
//之二:新建一个文件
Excel.Workbook xlsBook = xlsApp.Workbooks.Add(Missing.Value);
//指定要操作的Sheet,两种方式
//之一:
Excel.Worksheet xlsSheet = (Excel.Worksheet)xlsBook.Sheets[1];
//之二:
//Excel.Worksheet xlsSheet = (Excel.Worksheet)xlsApp.ActiveSheet;
//指定单元格,读取数据,两种方法
//之一:
Excel.Range range1 = xlsSheet.get_Range("C2",Type.Missing);
Console.WriteLine(range1.Value2);
//之二:
Excel.Range range2 = (Excel.Range)xlsSheet.Cells[2, 3];
Console.WriteLine(range2.Value2);
//在单元格中写入数据
Excel.Range range3 = xlsSheet.get_Range("A1",Type.Missing);
range3.Value2 = "Hello World!";
range3.Borders.Color = Color.FromArgb(123, 231, 32).ToArgb();
range3.Font.Color = Color.Red.ToArgb();
range3.Font.Name = "Arial";
range3.Font.Size = 9;
//range3.Orientation = 90; //vertical
range3.Columns.HorizontalAlignment = Excel.Constants.xlCenter;
range3.VerticalAlignment = Excel.Constants.xlCenter;
range3.Interior.Color = Color.FromArgb(192,192,192).ToArgb();
range3.Columns.AutoFit();//adjust the column width automatically
//在某个区域写入数据数组
int matrixHeight = 20;
int matrixWidth = 20;
string[,] martix=new string[matrixHeight,matrixWidth];
for (int i = 0; i < matrixHeight; i++)
for (int j = 0; j < matrixWidth; j++)
{
martix[i, j] = String.Format("{0}_{1}", i+1, j+1);
}
string startColName=GetColumnNameByIndex(0);
string endColName=GetColumnNameByIndex(matrixWidth-1);
//取得某个区域,两种方法
//之一:
Excel.Range range4 = xlsSheet.get_Range("A1",Type.Missing);
range4 = range4.get_Resize(matrixHeight,matrixWidth);
//之二:
//Excel.Range range4 = xlsSheet.get_Range(String.Format("{0}{1}", startColName, 1), String.Format("{0}{1}", endColName, martixHeight));
range4.Value2 = martix;
range4.Font.Color = Color.Red.ToArgb();
range4.Font.Name="Arial";
range4.Font.Size = 9;
range4.Columns.HorizontalAlignment = Excel.Constants.xlCenter;
//设置column和row的宽度和颜色
int columnIndex=3;
int rowIndex=3;
string colName = GetColumnNameByIndex(columnIndex);
xlsSheet.get_Range(colName + rowIndex.ToString(), Type.Missing).Columns.ColumnWidth = 20;
xlsSheet.get_Range(colName + rowIndex.ToString(), Type.Missing).Rows.RowHeight = 40;
xlsSheet.get_Range(colName + rowIndex.ToString(), Type.Missing).Columns.Interior.Color = Color.Blue.ToArgb();//单格颜色
xlsSheet.get_Range(5 + ":" + 7, Type.Missing).Rows.Interior.Color = Color.Yellow.ToArgb();//第5行到第7行的颜色
//xlsSheet.get_Range("G : G", Type.Missing).Columns.Interior.Color=Color.Pink.ToArgb();//第n列的颜色如何设置??
//保存,关闭
if (File.Exists(@"E:/Documents and Settings/daniel.chen/Desktop/test1.xls"))
{
File.Delete(@"E:/Documents and Settings/daniel.chen/Desktop/test1.xls");
}
xlsBook.SaveAs(@"E:/Documents and Settings/daniel.chen/Desktop/test1.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
xlsBook.Close(false, Type.Missing, Type.Missing);
xlsApp.Quit();
GC.Collect();
Console.ReadKey();
}
//将column index转化为字母,至多两位
public static string GetColumnNameByIndex(int index)
{
string[] alphabet = new string[] {"","A", "B", "C", "D", "E", "F", "G", "H", "I", "J" ,"K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"};
string result = "";
int temp = index / 26;
int temp2 = index % 26 + 1;
if (temp > 0)
{
result += alphabet[temp];
}
result += alphabet[temp2];
return result;
}
}
}
C#读写EXCEL方法二
通过数据库方式
OleDbDataAdapter ada = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + openFileDialog1.FileName + ";Extended Properties=Excel 8.0;");
DataTable dt = new DataTable();
try
{
ada.Fill(dt);
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
dataGrid1.DataSource = dt;
}