c#与excel之间的导入与导出

1、c# 从Excel导入到dataGridView作者：admin | 点击数：18336 | 更新时间：2010/6/27using System;using System.Data;using System.Configuration;using System.Windows.Forms;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.U

2、I.HtmlControls;using Microsoft.Office.Interop;using Microsoft.Office.Core;using Excel=Microsoft.Office.Interop.Excel;namespace ExcelEdit public class ExcelEdit / 点击按钮导入数据 private void button1_Click(object sender, EventArgs e) /打开一个文件选择框 OpenFileDialog ofd = new OpenFileDialog(); ofd.Title = "Ex

3、cel文件" ofd.FileName = "" ofd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);/为了获取特定的系统文件夹，可以使用System.Environment类的静态方法GetFolderPath()。该方法接受一个Environment.SpecialFolder枚举，其中可以定义要返回路径的哪个系统目录 ofd.Filter = "Excel文件(*.xls)|*.xls" ofd.ValidateNa

4、mes = true; /文件有效性验证ValidateNames，验证用户输入是否是一个有效的Windows文件名 ofd.CheckFileExists = true; /验证路径有效性 ofd.CheckPathExists = true; /验证文件有效性 string strName = string.Empty; if (ofd.ShowDialog() = DialogResult.OK) strName = ofd.FileName; if (strName = "") MessageBox.Show("没有选择Excel文件！无法进行数据导入&q

5、uot;); return; /调用导入数据方法 EcxelToDataGridView(strName, this.hGridView1); / Excel数据导入方法 public void EcxelToDataGridView(string filePath,DataGridView dgv) /根据路径打开一个Excel文件并将数据填充到DataSet中 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + filePath + "Extended Properties

6、='Excel 8.0;HDR=NO;IMEX=1'"/HDR=YES 有两个值:YES/NO,表示第一行是否字段名,默认是YES,第一行是字段名 OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); string strExcel = "" OleDbDataAdapter myCommand = null; DataSet ds = null; strExcel = "select * from sheet1$" myCommand = new O

7、leDbDataAdapter(strExcel, strConn); ds = new DataSet(); myCommand.Fill(ds, "table1"); /根据DataGridView的列构造一个新的DataTable DataTable tb = new DataTable(); foreach (DataGridViewColumn dgvc in dgv.Columns) if (dgvc.Visible && dgvc.CellType != typeof(DataGridViewCheckBoxCell) DataColumn d

8、c = new DataColumn(); dc.ColumnName = dgvc.DataPropertyName; /dc.DataType = dgvc.ValueType;/若需要限制导入时的数据类型则取消注释，前提是DataGridView必须先绑定一个数据源那怕是空的DataTable tb.Columns.Add(dc); /根据Excel的行逐一对上面构造的DataTable的列进行赋值 foreach (DataRow excelRow in ds.Tables0.Rows) int i = 0; DataRow dr = tb.NewRow(); foreach (Dat

9、aColumn dc in tb.Columns) drdc = excelRowi; i+; tb.Rows.Add(dr); /在DataGridView中显示导入的数据 dgv.DataSource = tb; C# datagridview 导出Excel并打开作者：admin | 点击数：6073 | 更新时间：2010/6/271.引入命名空间using Excel = Microsoft.Office.Interop.Excel;在解决方案里添加COM引用2.在类中声明两个变量：Excel.Application excel;Excel._Workbook objBook;3.在

10、导出按钮的Click事件中添加如下程序： SaveFileDialog dg = new SaveFileDialog();/保存文件对话框，选择导出文件的存放位置 dg.Filter = "xls files(*.xls)|*.xls"/保存为xls格式 if (dg.ShowDialog() = DialogResult.OK) string filepath = dg.FileName.ToString();/保存文件的路径 Excel.Workbooks objBooks;/接口 workbooks Excel.Sheets objSheets;/ 接口 sheet

11、s Excel._Worksheet objSheet;/接口 worksheet excel = new Excel.Application(); objBooks = excel.Workbooks; Object miss = System.Reflection.Missing.Value; objBook = objBooks.Add(miss); objSheets = objBook.Sheets; objSheet = (Excel._Worksheet)objSheets1; try if (dataGridView1.Rows.Count = 0) /没有数据的话就不往下执行

12、 return; excel.Visible = false; /让后台执行设置为不可见，为true的话会看到打开一个Excel，然后数据在往里写 for (int i = 0; i < dataGridView1.Columns.Count; i+) /生成Excel中列头名称 objSheet.Cells1, i + 1 = dataGridView1.Columnsi.HeaderText; for (int i = 0; i < dataGridView1.Rows.Count ; i+) /把DataGridView当前页的数据保存在Excel中 for (int j =

13、 0; j < dataGridView1.Columns.Count; j+) if (dataGridView1j, i.ValueType = typeof(string) objSheet.Cellsi + 2, j + 1 = "'" + dataGridView1j, i.Value.ToString(); else objSheet.Cellsi + 2, j + 1 = dataGridView1j, i.Value.ToString(); objBook.SaveCopyAs(filepath); /设置禁止弹出保存和覆盖的询问提示框 exc

14、el.DisplayAlerts = false; excel.AlertBeforeOverwriting = false; /确保Excel进程关闭 objBooks.Close(); excel.Workbooks.Close(); excel.Quit(); excel = null; GC.Collect(); MessageBox.Show("数据导出完成!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); if (System.IO.File.Exists(filepath

15、) System.Diagnostics.Process.Start(filepath); /保存成功后打开此文件 catch (Exception ex) MessageBox.Show(ex.Message, "错误提示"); c# 把listView里面的数据保存成Excle作者：admin | 点击数：5500 | 更新时间：2010/6/27把listView里面的数据保存成Excle第一种方法：using System.IO;using Microsoft.Office.Interop.Excel; public static void ExportToExce

16、l(ListView pListView) if (pListView.Items = null) return; string saveFileName = "" SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.DefaultExt = "xls" saveDialog.Filter = "Excel文件|*.xls" saveDialog.FileName = DateTime.Now.ToString("yyyy-MM-dd"); sa

17、veDialog.ShowDialog(); saveFileName = saveDialog.FileName; if (saveFileName.IndexOf(":") < 0) return; /这里直接删除，因为saveDialog已经做了文件是否存在的判断 if (File.Exists(saveFileName) File.Delete(saveFileName); Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Applicat

18、ion(); if (xlApp = null) MessageBox.Show("无法创建Excel对象，可能您的机器未安装Excel"); return; Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks; Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(true); Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Offi

19、ce.Interop.Excel.Worksheet)workbook.Worksheets1; xlApp.Visible = false; /填充列 for (int i = 0; i < pListView.Columns.Count; i+) worksheet.Cells1, i + 1 = pListView.Columnsi.Text.ToString(); (Microsoft.Office.Interop.Excel.Range)worksheet.Cells1, i + 1).Font.Bold = true; /填充数据（这里分了两种情况，1：lv带CheckedB

20、ox，2：不带CheckedBox） /带CheckedBoxes if (pListView.CheckBoxes = true) int tmpCnt = 0; for (int i = 0; i < pListView.Items.Count; i+) if (pListView.Itemsi.Checked = true) for (int j = 0; j < pListView.Columns.Count; j+) if (j = 0) worksheet.Cells2 + tmpCnt, j + 1 = pListView.Itemsi.Text.ToString()

21、; (Microsoft.Office.Interop.Excel.Range)worksheet.Cells2 + tmpCnt, j + 1).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; else worksheet.Cells2 + tmpCnt, j + 1 = pListView.Itemsi.SubItemsj.Text.ToString(); (Microsoft.Office.Interop.Excel.Range)worksheet.Cells2 + tmpCnt, j

22、 + 1).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; tmpCnt+; else /不带Checkedboxe for (int i = 0; i < pListView.Items.Count; i+) for (int j = 0; j < pListView.Columns.Count; j+) if (j = 0) worksheet.Cells2 + i, j + 1 = pListView.Itemsi.Text.ToString(); (Microsoft.O

23、ffice.Interop.Excel.Range)worksheet.Cells2 + i, j + 1).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; else worksheet.Cells2 + i, j + 1 = pListView.Itemsi.SubItemsj.Text.ToString(); (Microsoft.Office.Interop.Excel.Range)worksheet.Cells2 + i, j + 1).HorizontalAlignment = M

24、icrosoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; object missing = System.Reflection.Missing.Value; try workbook.Saved = true; workbook.SaveAs(saveFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlXMLSpreadsheet, missing, missing, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessM

25、ode.xlNoChange, missing, missing, missing, missing, missing); catch (Exception e1) MessageBox.Show("导出文件时出错,文件可能正被打开！n" + e1.Message); finally xlApp.Quit(); System.GC.Collect(); MessageBox.Show("导出Excle成功！"); 第二种方法：using Microsoft.Office.Interop.Excel;using ExcelApplication = Mic

26、rosoft.Office.Interop.Excel.Application;using System.Reflection; public void TurnToExcel(ListView listView, string stname) string Sheetname = stname; ListView listView1 = listView; if (listView1.Items.Count < 1) return; try ExcelApplication MyExcel = new ExcelApplication(); MyExcel.Visible = true

27、; if (MyExcel = null) return; Workbooks MyWorkBooks = (Workbooks)MyExcel.Workbooks; Workbook MyWorkBook = (Workbook)MyWorkBooks.Add(Missing.Value); Worksheet MyWorkSheet = (Worksheet)MyWorkBook.Worksheets1; Range MyRange = MyWorkSheet.get_Range("A1", "H1"); MyRange = MyRange.get_

28、Resize(1, listView1.Columns.Count); object MyHeader = new objectlistView1.Columns.Count; for (int i = 0; i < listView1.Columns.Count; i+) MyHeader.SetValue(listView1.Columnsi.Text, i); MyRange.Value2 = MyHeader; MyWorkSheet.Name = Sheetname; if (listView1.Items.Count > 0) MyRange = MyWorkSheet

29、.get_Range("A2", Missing.Value); object, MyData = new ObjectlistView1.Items.Count, listView1.Columns.Count; for (int j = 0; j < listView1.Items.Count; j+) ListViewItem lvi = listView.Itemsj; for (int k = 0; k < listView1.Columns.Count; k+) MyDataj, k = lvi.SubItemsk.Text; MyRange = M

30、yRange.get_Resize(listView1.Items.Count, listView1.Columns.Count); MyRange.Value2 = MyData; MyRange.EntireColumn.AutoFit(); MyExcel = null; catch (Exception Err) MessageBox.Show(Err.Message); 备注：添加Excel引用的方法添加引用：Microsoft.Office.Interop.Excel/引入Excel的COM组件using System;using System.Data;using System.

31、Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using Microsoft.Office.Interop;using Microsoft.Office.Core;namespace ExcelEdit/ <SUMMARY>/ ExcelEdit 的摘要说明/ <

32、;/SUMMARY> public class ExcelEdit public string mFilename; public Excel.Application app; public Excel.Workbooks wbs; public Excel.Workbook wb; public Excel.Worksheets wss; public Excel.Worksheet ws; public ExcelEdit() / / TODO: 在此处添加构造函数逻辑 / public void Create()/创建一个Excel对象 app = new Excel.Applic

33、ation(); wbs = app.Workbooks; wb = wbs.Add(true); public void Open(string FileName)/打开一个Excel文件 app = new Excel.Application(); wbs = app.Workbooks; wb = wbs.Add(FileName); /wb = wbs.Open(FileName, 0, true, 5,"", "", true, Excel.XlPlatform.xlWindows, "t", false, false, 0

34、, true,Type.Missing,Type.Missing); /wb = wbs.Open(FileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Excel.XlPlatform.xlWindows,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing); mFilename = FileName; public Excel.Workshee

35、t GetSheet(string SheetName) /获取一个工作表 Excel.Worksheet s = (Excel.Worksheet)wb.WorksheetsSheetName; return s; public Excel.Worksheet AddSheet(string SheetName) /添加一个工作表 Excel.Worksheet s = (Excel.Worksheet)wb.Worksheets.Add(Type.Missing,Type.Missing,Type.Missing,Type.Missing); s.Name = SheetName; ret

36、urn s; public void DelSheet(string SheetName)/删除一个工作表 (Excel.Worksheet)wb.WorksheetsSheetName).Delete(); public Excel.Worksheet ReNameSheet(string OldSheetName, string NewSheetName)/重命名一个工作表一 Excel.Worksheet s = (Excel.Worksheet)wb.WorksheetsOldSheetName; s.Name = NewSheetName; return s; public Exce

37、l.Worksheet ReNameSheet(Excel.Worksheet Sheet, string NewSheetName)/重命名一个工作表二 Sheet.Name = NewSheetName; return Sheet; public void SetCellValue(Excel.Worksheet ws, int x, int y, object value)/ws：要设值的工作表 X行Y列 value 值 ws.Cellsx, y = value; public void SetCellValue(string ws, int x, int y, object value

38、)/ws：要设值的工作表的名称 X行Y列 value 值 GetSheet(ws).Cellsx, y = value; public void SetCellProperty(Excel.Worksheet ws, int Startx, int Starty, int Endx, int Endy, int size, string name, Excel.Constants color, Excel.Constants HorizontalAlignment)/设置一个单元格的属性 字体， 大小，颜色 ，对齐方式 name = "宋体" size = 12; colo

39、r = Excel.Constants.xlAutomatic; HorizontalAlignment = Excel.Constants.xlRight; ws.get_Range(ws.CellsStartx, Starty, ws.CellsEndx, Endy).Font.Name = name; ws.get_Range(ws.CellsStartx, Starty, ws.CellsEndx, Endy).Font.Size = size; ws.get_Range(ws.CellsStartx, Starty, ws.CellsEndx, Endy).Font.Color =

40、color; ws.get_Range(ws.CellsStartx, Starty, ws.CellsEndx, Endy).HorizontalAlignment = HorizontalAlignment; public void SetCellProperty(string wsn, int Startx, int Starty, int Endx, int Endy, int size, string name, Excel.Constants color, Excel.Constants HorizontalAlignment) /name = "宋体" /size = 12; /color = Excel.Constants.xlAutomatic; /HorizontalAlignment = Excel.Constants.xlRight; Excel.Worksheet ws = GetSheet(wsn); ws.get_Range(ws.CellsStartx, Starty, ws.CellsEndx