C#操作Excel与DataGridView之间的导入导出
- 格式：doc
- 大小：47.00 KB
- 文档页数：5
using System;using System.Collections.Generic;using ponentModel;using System.Data;using System.Drawing;using System.Text;using System.Windows.Forms;using System.Data.OleDb;using Microsoft.Office.Interop.Excel;using System.IO;namespace frmExcel{public partial class frm_Excel : Form{//全局变量DataSet ds = new DataSet();public frm_Excel(){InitializeComponent();}private void frm_Excel_Load(object sender, EventArgs e){}///<summary>///打开要导入的Excel文件///</summary>///<param name="sender"></param>///<param name="e"></param>private void btn_Open_Click(object sender, EventArgs e){if (openFileDialog1.ShowDialog(this) == DialogResult.OK) {txt_Path.Text = openFileDialog1.FileName;}}///<summary>///导入事件///</summary>///<param name="sender"></param>///<param name="e"></param>private void btn_In_Click(object sender, EventArgs e){if (!string.IsNullOrEmpty(txt_Path.Text)){string strCon = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + txt_Path.Text + ";Extended Properties=Excel 8.0";OleDbConnection strConn = new OleDbConnection(strCon);string strSql = "select * from [sheet1$]";strConn.Open();OleDbDataAdapter strCom = new OleDbDataAdapter(strSql, strConn);ds = new DataSet();strCom.Fill(ds, "[sheet1$]");strConn.Close();dataGridView1.DataMember = "[sheet1$]";dataGridView1.DataSource = ds;}else{MessageBox.Show("您要导入的文件错误！");}}///<summary>///导出事件///</summary>///<param name="sender"></param>///<param name="e"></param>private void btn_Out_Click(object sender, EventArgs e){if (dataGridView1.RowCount > 0){ExportToExcel("123", "12-12", dataGridView1);}else{MessageBox.Show("无可用数据导出！");}}///<summary>/// Excel导出///</summary>///<param name="caption">名称</param>///<param name="date">日期</param>///<param name="dgv">要导出的DataGridView数据控件</param>public void ExportToExcel(string caption, string date, DataGridView dgv){//DataGridView可见列数int visiblecolumncount = 0;for (int i = 0; i < dgv.Columns.Count; i++){if (dgv.Columns[i].Visible == true && (dgv.Columns[i] is DataGridViewTextBoxColumn)){visiblecolumncount++;}}try{//当前操作列的索引int currentcolumnindex = 1;//当前操作行的索引Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();excel.Application.Workbooks.Add(true);//excel.Cells.Font.Size = 10.5; //设置默认字体大小//设置标头excel.Caption = caption;//显示表头excel.Cells[1, 1] = caption;//显示时间excel.Cells[2, 1] = date;for (int i = 0; i < dgv.Columns.Count; i++){if (dgv.Columns[i].Visible == true && (dgv.Columns[i] is DataGridViewTextBoxColumn)) //如果显示{excel.Cells[3, currentcolumnindex] =dgv.Columns[i].HeaderText;excel.get_Range(excel.Cells[3, currentcolumnindex], excel.Cells[3, currentcolumnindex]).Cells.Borders.LineStyle = 1; //设置边框excel.get_Range(excel.Cells[3, currentcolumnindex], excel.Cells[3, currentcolumnindex]).ColumnWidth = dgv.Columns[i].Width / 8; currentcolumnindex++;}}excel.get_Range(excel.Cells[1, 1], excel.Cells[1, visiblecolumncount]).MergeCells = true; //合并单元格excel.get_Range(excel.Cells[1, 1], excel.Cells[1,1]).RowHeight = 30; //行高excel.get_Range(excel.Cells[1, 1], excel.Cells[1, visiblecolumncount]).HorizontalAlignment =Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //居中显示excel.get_Range(excel.Cells[2, 1], excel.Cells[2,2]).MergeCells = true; //合并excel.get_Range(excel.Cells[2, 1], excel.Cells[2,2]).HorizontalAlignment =Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; //左边显示object[,] dataArray = new object[dgv.Rows.Count, visiblecolumncount];//当前操作列的索引//int currentcolumnindex = 1;//当前操作行的索引for (int i = 0; i < dgv.Rows.Count; i++) //循环填充数据{currentcolumnindex = 1;for (int j = 0; j < dgv.Columns.Count; j++){if(dgv.Columns[j].Visible == true&& (dgv.Columns[j] is DataGridViewTextBoxColumn)){if (dgv[j, i].Value != null) //如果单元格内容不为空{dataArray[i, currentcolumnindex - 1] = dgv[j, i].Value.ToString();}currentcolumnindex++;}}}excel.get_Range(excel.Cells[4, 1],excel.Cells[dgv.Rows.Count + 3, visiblecolumncount]).Value2 = dataArray; //设置边框excel.get_Range(excel.Cells[4, 1],excel.Cells[dgv.Rows.Count + 3, visiblecolumncount]).Cells.Borders.LineStyle = 1; //设置边框excel.Visible = true;}catch{MessageBox.Show("信息导出失败！", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);}finally{}}}}。
