c# 从Excel导入到dataGridView |
作者：admin | 点击数：18336 | 更新时间：2010/6/27 |
using 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.UI.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 = "Excel文件"; ofd.FileName = ""; ofd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);//为了获取特定的系统文件夹，可以使用System.Environment类的静态方法GetFolderPath()。该方法接受一个Environment.SpecialFolder枚举，其中可以定义要返回路径的哪个系统目录 ofd.Filter = "Excel文件(*.xls)|*.xls"; ofd.ValidateNames = 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文件！无法进行数据导入"); 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 ='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 OleDbDataAdapter(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 dc = new DataColumn(); dc.ColumnName = dgvc.DataPropertyName; //dc.DataType = dgvc.ValueType;//若需要限制导入时的数据类型则取消注释，前提是DataGridView必须先绑定一个数据源那怕是空的DataTable tb.Columns.Add(dc); } } //根据Excel的行逐一对上面构造的DataTable的列进行赋值 foreach (DataRow excelRow in ds.Tables[0].Rows) { int i = 0; DataRow dr = tb.NewRow(); foreach (DataColumn dc in tb.Columns) { dr[dc] = excelRow[i]; i++; } tb.Rows.Add(dr); } //在DataGridView中显示导入的数据 dgv.DataSource = tb; } } } |
C# datagridview 导出Excel并打开 |
作者：admin | 点击数：6073 | 更新时间：2010/6/27 |
1.引入命名空间 using Excel = Microsoft.Office.Interop.Excel; 在解决方案里添加COM引用 2.在类中声明两个变量： Excel.Application excel; Excel._Workbook objBook; 3.在导出按钮的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;// 接口 sheets 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)objSheets[1]; try { if (dataGridView1.Rows.Count == 0) //没有数据的话就不往下执行 return; excel.Visible = false; //让后台执行设置为不可见，为true的话会看到打开一个Excel，然后数据在往里写 for (int i = 0; i < dataGridView1.Columns.Count; i++) //生成Excel中列头名称 { objSheet.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText; } for (int i = 0; i < dataGridView1.Rows.Count ; i++) //把DataGridView当前页的数据保存在Excel中 { for (int j = 0; j < dataGridView1.Columns.Count; j++) { if (dataGridView1[j, i].ValueType == typeof(string)) { objSheet.Cells[i + 2, j + 1] = "'" + dataGridView1[j, i].Value.ToString(); } else { objSheet.Cells[i + 2, j + 1] = dataGridView1[j, i].Value.ToString(); } } } objBook.SaveCopyAs(filepath); //设置禁止弹出保存和覆盖的询问提示框 excel.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)) System.Diagnostics.Process.Start(filepath); //保存成功后打开此文件 } catch (Exception ex) { MessageBox.Show(ex.Message, "错误提示"); } } |
c# 把listView里面的数据保存成Excle |
作者：admin | 点击数：5500 | 更新时间：2010/6/27 |
把listView里面的数据保存成Excle 第一种方法：drop table if exists admin using System.IO; using Microsoft.Office.Interop.Excel; public static void ExportToExcel(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"); saveDialog.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.Application(); 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.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; xlApp.Visible = false; //填充列 for (int i = 0; i < pListView.Columns.Count; i++) { worksheet.Cells[1, i + 1] = pListView.Columns[i].Text.ToString(); ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]).Font.Bold = true; } //填充数据（这里分了两种情况，1：lv带CheckedBox，2：不带CheckedBox） //带CheckedBoxes if (pListView.CheckBoxes == true) { int tmpCnt = 0; for (int i = 0; i < pListView.Items.Count; i++) { if (pListView.Items[i].Checked == true) { for (int j = 0; j < pListView.Columns.Count; j++) { if (j == 0) { worksheet.Cells[2 + tmpCnt, j + 1] = pListView.Items[i].Text.ToString(); ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2 + tmpCnt, j + 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; } else { worksheet.Cells[2 + tmpCnt, j + 1] = pListView.Items[i].SubItems[j].Text.ToString(); ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2 + tmpCnt, j + 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.Cells[2 + i, j + 1] = pListView.Items[i].Text.ToString(); ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2 + i, j + 1]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; } else { worksheet.Cells[2 + i, j + 1] = pListView.Items[i].SubItems[j].Text.ToString(); ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2 + i, j + 1]).HorizontalAlignment = Microsoft.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.XlSaveAsAccessMode.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 = Microsoft.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; if (MyExcel == null) { return; } Workbooks MyWorkBooks = (Workbooks)MyExcel.Workbooks; Workbook MyWorkBook = (Workbook)MyWorkBooks.Add(Missing.Value); Worksheet MyWorkSheet = (Worksheet)MyWorkBook.Worksheets[1]; Range MyRange = _Range("A1", "H1"); MyRange = _Resize(1, listView1.Columns.Count); object[] MyHeader = new object[listView1.Columns.Count]; for (int i = 0; i < listView1.Columns.Count; i++) { MyHeader.SetValue(listView1.Columns[i].Text, i); } MyRange.Value2 = MyHeader; MyWorkSheet.Name = Sheetname; if (listView1.Items.Count > 0) { MyRange = _Range("A2", Missing.Value); object[,] MyData = new Object[listView1.Items.Count, listView1.Columns.Count]; for (int j = 0; j < listView1.Items.Count; j++) { ListViewItem lvi = listView.Items[j]; for (int k = 0; k < listView1.Columns.Count; k++) { MyData[j, k] = lvi.SubItems[k].Text; } } MyRange = _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.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 的摘要说明
/// </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.Application();
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, 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.Worksheet GetSheet(string SheetName)
//获取一个工作表
{
Excel.Worksheet s = (Excel.Worksheet)wb.Worksheets[SheetName];
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论