C#操作Excel的方式多種多樣，以下為個(gè)人實(shí)踐中的一些總結(jié)，留個(gè)筆記方便以后查看，陸續(xù)更新中。。。

進(jìn)入正題：

一：將Excel的數(shù)據(jù)直接導(dǎo)入到SQL數(shù)據(jù)庫中

這個(gè)需要Excel文件與數(shù)據(jù)庫中的表的字段一致，或者指定每一個(gè)字段也行；
首先讀取Excel里面的內(nèi)容：

string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; " + "Data Source =" + this.txtPath.Text + ";Extended Properties=Excel 8.0";OleDbConnection myConn = new OleDbConnection(strCon);string sql = "SELECT * FROM [Sheet1$]";try{myConn.Open();OleDbDataAdapter oda = new OleDbDataAdapter(sql, myConn);oda.Fill(ds, "[Sheet1$]");myConn.Close();this.dataGridView1.DataSource = ds.Tables[0];}catch (Exception ex){MessageBox.Show(ex.Message);}

然后循環(huán)將每一條數(shù)據(jù)Insert到數(shù)據(jù)庫中：

string connectionString = @"server=SQL2008; database=ExcelDB; uid=sa;pwd=123123";SqlConnection con = new SqlConnection(connectionString);DataGrid mygrid = new DataGrid();mygrid.SetDataBinding(ds, "[Sheet1$]");int num = ds.Tables[0].Rows.Count;for (int i = 0; i < num; i++){string value1 = mygrid[i, 0].ToString();string value2 = mygrid[i, 1].ToString();string value3 = mygrid[i, 2].ToString();string value4 = mygrid[i, 3].ToString();string value5 = mygrid[i, 4].ToString();string strsql = "INSERT INTO tb_01 VALUES('" + value1 + "','" + value2 + "','" + value3 + "','" + value4 + "','" + value5 + "')";con.Open();SqlCommand cmd = new SqlCommand(strsql, con);cmd.ExecuteNonQuery();con.Close();}

?

二：將SQL數(shù)據(jù)庫中的數(shù)據(jù)寫入到Excel
通常有很多報(bào)表會(huì)要求將后臺(tái)的數(shù)據(jù)寫入到一個(gè)固定的模板文件中，那么可以這樣操作：

using Excel = Microsoft.Office.Interop.Excel;Excel.Application xApp = new Excel.ApplicationClass();Excel.Workbook xBook = xApp.Workbooks._Open(txtPath.Text.Trim(),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.Worksheet xSheet = (Excel.Worksheet)xBook.Sheets[1];Excel.Range rng1 = xSheet.get_Range("A1", Missing.Value);rng1.Value2 = this.txt01.Text.Trim();Excel.Range rng2 = xSheet.get_Range("B1", Missing.Value);rng2.Value2 = this.txt02.Text.Trim();Excel.Range rng3 = xSheet.get_Range("C1", Missing.Value);rng3.Value2 = this.txt03.Text.Trim();Excel.Range rng4 = xSheet.get_Range("D1", Missing.Value);rng4.Value2 = this.txt04.Text.Trim();xBook.Save();xApp.Quit();

A1,B1,C1,D1就是Excel里面的單元格坐標(biāo)，這個(gè)根據(jù)要求可以任意更改，
Excel操作完成之后務(wù)必要將其退出來，不然服務(wù)器上面Excel的進(jìn)程會(huì)
越來越多。

三.將WinForm里面DataGridView的數(shù)據(jù)導(dǎo)出到Excel里面。
該實(shí)例將所有的數(shù)據(jù)放到一個(gè)數(shù)組里面，然后再從數(shù)組里面導(dǎo)入到Excel，在速度上確實(shí)挺快的，
直接從DataGridView里面導(dǎo)入的話10000條數(shù)據(jù)大約要1分鐘左右，但是從數(shù)組里面導(dǎo)出去的話
只有2秒鐘不到的樣子。

if (this.dataGridView1.Rows.Count > 0){DateTime dt = DateTime.Now; //導(dǎo)出Excel表時(shí)間string title = "XXX報(bào)表 導(dǎo)出時(shí)間：" + dt.ToString(); //excel表頭Microsoft.Office.Interop.Excel.Application ep = new Microsoft.Office.Interop.Excel.ApplicationClass();ep.Application.Workbooks.Add(true);ep.get_Range("A1", "Z1").MergeCells = true; //合并第一行 ep.Cells[1, 1] = title; //Excel添加表頭到第一個(gè)單元格ep.get_Range("A1", "Z1").MergeCells = true; //合并第一行ep.get_Range("A1", "Z1").HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; //表頭居左ep.get_Range("A1", "Z1").Font.Name = "宋體"; //設(shè)置字體ep.get_Range("A1", "Z1").Font.Bold = true;ep.get_Range("A1", "Z1").Font.Size = 15; //文字大小ep.get_Range("A1", "Z1").RowHeight = 35; //設(shè)置標(biāo)題行高//列標(biāo)題設(shè)置（字段，Excel表的第二行，從dataGridView1字段中獲取）for (int i = 1; i <= 5; i++){ep.Cells[2, i] = dataGridView1.Columns[i - 1].HeaderText.ToString();}ep.get_Range("A2", "Z2").Font.Name = "宋體"; //設(shè)置字體ep.get_Range("A2", "Z2").Font.Bold = true; //字體加粗ep.get_Range("A2", "Z2").Font.Size = 10; //文字大小ep.get_Range("A2", "Z2").RowHeight = 20; //設(shè)置標(biāo)題行高int rowCount = 0;for (int i = 0; i < dataGridView1.Rows.Count; i++) //統(tǒng)計(jì)dataGridView1有效行 {if (dataGridView1.Rows[i].Cells[0].Value.ToString() == ""){break;}else{rowCount++;}}string[,] dataArray = new string[rowCount, 5];for (int i = 0; i < rowCount; i++){for (int j = 0; j < 5; j++){dataArray[i, j] = dataGridView1.Rows[i].Cells[j].Value.ToString();}}ep.get_Range("A3", ep.Cells[rowCount + 2, 5]).Value2 = dataArray; //填充數(shù)據(jù)ep.get_Range("A2", ep.Cells[rowCount + 2, 2]).WrapText = true; //設(shè)置文字自動(dòng)換行ep.get_Range("A2", ep.Cells[rowCount + 2, 5]).Font.Size = 10; //字體大小設(shè)置string strRange = "2:" + Convert.ToString(rowCount + 2);((Microsoft.Office.Interop.Excel.Range)ep.Rows[strRange, System.Type.Missing]).RowHeight = 40; //所有行高為40 ep.get_Range("A2", ep.Cells[rowCount + 2, 2 + 5]).EntireColumn.AutoFit();ep.get_Range("A2", ep.Cells[rowCount + 2, 5]).Borders.LineStyle = 1;SaveFileDialog saveFileDialog1 = new SaveFileDialog();saveFileDialog1.FileName = DateTime.Now.ToLocalTime().ToString("yyyyMMddHHmmss");saveFileDialog1.DefaultExt = "xls";saveFileDialog1.Filter = " Excel files(*.xls)|*.xls|All files(*.*)|*.*";saveFileDialog1.Title = "保存數(shù)據(jù)";saveFileDialog1.FilterIndex = 2;saveFileDialog1.RestoreDirectory = true;if (saveFileDialog1.ShowDialog() == DialogResult.OK){ep.Workbooks[1].SaveAs(saveFileDialog1.FileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);}ep.Workbooks.Close();ep.Quit();MessageBox.Show(DateTime.Now.ToLocalTime().ToString("yyyyMMddHHmmss") + "的資料保存成功", "提示", MessageBoxButtons.OK);}else{MessageBox.Show("沒有可導(dǎo)出數(shù)據(jù)");}

?

四.將后臺(tái)數(shù)據(jù)轉(zhuǎn)換為圖表格式

using Excel = Microsoft.Office.Interop.Excel;Excel.Application ThisApplication = null; Excel.Workbooks m_objBooks = null; Excel._Workbook ThisWorkbook = null; Excel.Worksheet xlSheet = null;string strCon = @"server=sql2008;database=exceldb;uid=sa;pwd=123123";private void FrmChart_Load(object sender, EventArgs e){SqlConnection con = new SqlConnection(strCon);DataSet ds = new DataSet();con.Open();SqlDataAdapter da = new SqlDataAdapter("select * from tb_02", con);da.Fill(ds, "tb_02");this.dataGridView1.DataSource = ds.Tables[0];con.Close();}private void CreateDatasheet(){xlSheet = (Excel.Worksheet)ThisWorkbook.Worksheets.Add(Type.Missing, ThisWorkbook.ActiveSheet,Type.Missing, Type.Missing);xlSheet.Name = "DataList";}private void CreateData(){ for (int i = 1; i <= 12; i++){xlSheet.Cells[i, 1] = dataGridView1.Rows[i - 1].Cells[1].Value.ToString();xlSheet.Cells[i, 2] = dataGridView1.Rows[i-1].Cells[4].Value.ToString();}}private void btnCreateChart_Click(object sender, EventArgs e){try{ThisApplication = new Excel.Application();m_objBooks = (Excel.Workbooks)ThisApplication.Workbooks;ThisWorkbook = (Excel._Workbook)(m_objBooks.Add(Type.Missing));ThisApplication.DisplayAlerts = false; this.CreateDatasheet();this.CreateData();CreateChart();ThisWorkbook.SaveAs(@"C:\TEST.xls", Type.Missing, Type.Missing,Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);}catch (Exception ex){MessageBox.Show(ex.Message);}finally{ThisWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);ThisApplication.Workbooks.Close();ThisApplication.Quit();System.Runtime.InteropServices.Marshal.ReleaseComObject(ThisWorkbook);System.Runtime.InteropServices.Marshal.ReleaseComObject(ThisApplication);ThisWorkbook = null;ThisApplication = null;GC.Collect();this.Close();}}private void CreateChart() { Excel.Chart xlChart = (Excel.Chart)ThisWorkbook.Charts. Add(Type.Missing, xlSheet, Type.Missing, Type.Missing);Excel.Range cellRange = (Excel.Range)xlSheet.Cells[1, 1]; xlChart.ChartWizard(cellRange.CurrentRegion, Excel.XlChartType.xl3DColumn, Type.Missing, Excel.XlRowCol.xlColumns, 1, 0, true, "Prod OutPut", "Month", "OutPut","");xlChart.Name = "Chart";Excel.ChartGroup grp = (Excel.ChartGroup)xlChart.ChartGroups(1); grp.GapWidth = 20; grp.VaryByCategories = true;Excel.Series s = (Excel.Series)grp.SeriesCollection(1); s.BarShape = XlBarShape.xlCylinder; s.HasDataLabels = true;xlChart.Legend.Position = XlLegendPosition.xlLegendPositionTop; xlChart.ChartTitle.Font.Size = 24; xlChart.ChartTitle.Shadow = true; xlChart.ChartTitle.Border.LineStyle = Excel.XlLineStyle.xlContinuous;Excel.Axis valueAxis = (Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlValue, XlAxisGroup.xlPrimary); valueAxis.AxisTitle.Orientation = -90;Excel.Axis categoryAxis = (Excel.Axis)xlChart.Axes(Excel.XlAxisType.xlCategory, XlAxisGroup.xlPrimary);}

有一些關(guān)鍵代碼來自網(wǎng)絡(luò)，自己加以整理后得出。大家有更好的經(jīng)驗(yàn)可以奉獻(xiàn)一下，大家互相學(xué)習(xí)！

完整的Demo：Excel Demo

總結(jié)

以上是生活随笔為你收集整理的C#对Excel的一些操作【一】的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。