C
#读
写Excel
.
转载:
首先Add References Excel.dll,注意在MS office 2003的版本中,可能找不到Excel.dll,这时需要从Excel.exe中扣出来,怎么扣?网上有很多方法,扣出来的同时也把Excel.dll 变成了受托管的。
转载:
1.读取Excel File。
其实读取Excel和读取数据库的方式差不多:
#region
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
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行,我还不知道是什么原因,哪位知道告诉我谢谢。那么这个时候可以用 下面的方法判断真实的行数:
1
int
b
=
0
;
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 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 }
2.生成Excel并插入数据。
有时我们需要把检索出来的数据导出到Excel。下面说说如何生成,插入数据到Excel. 首先Add References Excel.dll,引用using Excel;
下面的代码是将ListView中的数据导入到Excel:
1
System.Reflection.Missing miss
=
System.Reflection.Missing.Value;
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操作一般都要考虑到异常,自己添加。在网上也找了一些例子,觉得上面的资源释放机制是相对比较好的。
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();