?? excel.cs
字號:
#region 代碼1
//快速導入Excel
private void button3_Click(object sender, EventArgs e)
{
if (this.datagridview1.RowCount < 1)
{
MessageBox.Show("沒有可以導出的數據！");
return;
}
Microsoft.Office.Interop.Excel.Application appExcel;
appExcel = new Microsoft.Office.Interop.Excel.Application();
Workbook workbookData;
Worksheet worksheetData;
Range rangedata;
appExcel.Visible = true;
// set culture to US
System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
workbookData = appExcel.Workbooks.Add(Missing.Value);
worksheetData = (Worksheet)workbookData.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
worksheetData.Name = "Saved";
for (int i = 0; i < this.datagridview1.Columns.Count; i++)
{
worksheetData.Cells[1, i + 1] = this.datagridview1.Columns[i].HeaderText;
}
rangedata = worksheetData.get_Range("A2", Missing.Value);
Range xlRang = null;
int iRowCount = this.datagridview1.RowCount - 1;
int iParstedRow = 0, iCurrSize = 0;
int iEachSize = 1000; // each time you
int iColumnAccount = this.datagridview1.Columns.Count;
object[,] objVal = new object[iEachSize, iColumnAccount];
try
{
iCurrSize = iEachSize;
while (iParstedRow < iRowCount)
{
if ((iRowCount - iParstedRow) < iEachSize)
iCurrSize = iRowCount - iParstedRow;
for (int i = 0; i < iCurrSize; i++)
{
for (int j = 0; j < iColumnAccount; j++)
objVal[i, j] = this.datagridview1.Rows[i].Cells[j].Value.ToString();
}
// Get Save Range from Excel WorkSheet
// such as A1 H10, means From A to H Columns, and 1 to 10 rows
xlRang = worksheetData.get_Range("A" + ((int)(iParstedRow + 2)).ToString(), ((char)('A' + iColumnAccount - 1)).ToString() + ((int)(iParstedRow + iCurrSize + 1)).ToString());
xlRang.Value2 = objVal;
iParstedRow = iParstedRow + iCurrSize;
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRang);
xlRang = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
// return to previous culture
System.Threading.Thread.CurrentThread.CurrentCulture = CurrentCI;
}
#endregion
#region 代碼2
using Excel;
/// <summary>
/// 將DataSet里所有數據導入Excel.
/// 需要添加COM: Microsoft Excel Object Library
///引用Excel
/// </summary>
/// <param name="filePath"></param>
/// <param name="ds"></param>
public static void ExportToExcel(string filePath, DataSet ds)
{
object oMissing = System.Reflection.Missing.Value;
Excel.ApplicationClass xlApp = new Excel.ApplicationClass();
try
{
// 打開Excel文件。以下為Office 2000.
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing);
Excel.Worksheet xlWorksheet;
// 循環所有DataTable
for( int i=0; i<ds.Tables.Count; i++ )
{
// 添加入一個新的Sheet頁。
xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.Add(oMissing,oMissing,1,oMissing);
// 以TableName作為新加的Sheet頁名。
xlWorksheet.Name = ds.Tables[i].TableName;
// 取出這個DataTable中的所有值,暫存于stringBuffer中。
string stringBuffer = "";
for( int j=0; j<ds.Tables[i].Rows.Count; j++ )
{
for( int k=0; k<ds.Tables[i].Columns.Count; k++ )
{
stringBuffer += ds.Tables[i].Rows[j][k].ToString();
if( k < ds.Tables[i].Columns.Count - 1 )
stringBuffer += "\t";
}
stringBuffer += "\n";
}
// 利用系統剪切板
System.Windows.Forms.Clipboard.SetDataObject("");
// 將stringBuffer放入剪切板。
System.Windows.Forms.Clipboard.SetDataObject(stringBuffer);
// 選中這個sheet頁中的第一個單元格
((Excel.Range)xlWorksheet.Cells[1,1]).Select();
// 粘貼！
xlWorksheet.Paste(oMissing,oMissing);
// 清空系統剪切板。
System.Windows.Forms.Clipboard.SetDataObject("");
}
// 保存并關閉這個工作簿。
xlWorkbook.Close( Excel.XlSaveAction.xlSaveChanges, oMissing, oMissing );
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);
xlWorkbook = null;
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
// 釋放...
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
GC.Collect();
}
}
#endregion
#region 代碼3
public class ImportExportToExcel
2 {
3 private string strConn ;
4
5 private System.Windows.Forms.OpenFileDialog openFileDlg=new System.Windows.Forms.OpenFileDialog();
6 private System.Windows.Forms.SaveFileDialog saveFileDlg=new System.Windows.Forms.SaveFileDialog();
7
8 public ImportExportToExcel()
9 {
10 //
11 // TODO: 在此處添加構造函數邏輯
12 //
13 this.openFileDlg.DefaultExt = "xls";
14 this.openFileDlg.Filter = "Excel文件 (*.xls)|*.xls";
15
16 this.saveFileDlg.DefaultExt="xls";
17 this.saveFileDlg.Filter= "Excel文件 (*.xls)|*.xls";
18
19 }
20
21 從Excel文件導入到DataSet#region 從Excel文件導入到DataSet
22 // /// <summary>
23 // /// 從Excel導入文件
24 // /// </summary>
25 // /// <param name="strExcelFileName">Excel文件名</param>
26 // /// <returns>返回DataSet</returns>
27 // public DataSet ImportFromExcel(string strExcelFileName)
28 // {
29 // return doImport(strExcelFileName);
30 // }
31 /**//// <summary>
32 /// 從選擇的Excel文件導入
33 /// </summary>
34 /// <returns>DataSet</returns>
35 public DataSet ImportFromExcel()
36 {
37 DataSet ds=new DataSet();
38 if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
39 ds=doImport(openFileDlg.FileName);
40 return ds;
41 }
42 /**//// <summary>
43 /// 從指定的Excel文件導入
44 /// </summary>
45 /// <param name="strFileName">Excel文件名</param>
46 /// <returns></returns>
47 public DataSet ImportFromExcel(string strFileName)
48 {
49 DataSet ds=new DataSet();
50 ds=doImport(strFileName);
51 return ds;
52 }
53 /**//// <summary>
54 /// 執行導入
55 /// </summary>
56 /// <param name="strFileName">文件名</param>
57 /// <returns>DataSet</returns>
58 private DataSet doImport(string strFileName)
59 {
60 if (strFileName=="") return null;
61
62 strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
63 "Data Source=" + strFileName + ";" +
64 "Extended Properties=Excel 8.0;";
65 OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
66
67 DataSet ExcelDs = new DataSet();
68 try
69 {
70 ExcelDA.Fill(ExcelDs, "ExcelInfo");
71
72 }
73 catch(Exception err)
74 {
75 System.Console.WriteLine( err.ToString() );
76 }
77 return ExcelDs;
78
79
80
81 }
82 #endregion
83
84 從DataSet到出到Excel#region 從DataSet到出到Excel
85 /**//// <summary>
86 /// 導出指定的Excel文件
87 /// </summary>
88 /// <param name="ds">要導出的DataSet</param>
89 /// <param name="strExcelFileName">要導出的Excel文件名</param>
90 public void ExportToExcel(DataSet ds,string strExcelFileName)
91 {
92 if (ds.Tables.Count==0 || strExcelFileName=="") return;
93 doExport(ds,strExcelFileName);
94
95
96 }
97 /**//// <summary>
98 /// 導出用戶選擇的Excel文件
99 /// </summary>
100 /// <param name="ds">DataSet</param>
101 public void ExportToExcel(DataSet ds)
102 {
103 if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
104 doExport(ds,saveFileDlg.FileName);
105
106 }
107 /**//// <summary>
108 /// 執行導出
109 /// </summary>
110 /// <param name="ds">要導出的DataSet</param>
111 /// <param name="strExcelFileName">要導出的文件名</param>
112 private void doExport(DataSet ds,string strExcelFileName)
113 {
114
115 Excel.Application excel= new Excel.Application();
116
117 // Excel.Workbook obj=new Excel.WorkbookClass();
118 // obj.SaveAs("c:\zn.xls",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null);
119
120 int rowIndex=1;
121 int colIndex=0;
122
123 excel.Application.Workbooks.Add(true);
124
125
126 System.Data.DataTable table=ds.Tables[0] ;
127 foreach(DataColumn col in table.Columns)
128 {
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -