datagridview导出excel

using System; using System.Collections.Generic; using System.Linq; using System.Text; namespace AlarmSystem.App_code { using System; using System.Collections.Generic; using System.Text; using System.Windows.Forms; using System.Reflection; using Excel = Microsoft.Office.Interop.Excel; /// <summary> /// DataGridView 数据源导出到Excel /// </summary> public class DataGridViewToExcel { private Form _OwnerForm; private DataGridView _DataGridView; private string _FileName = ""; /// <summary> /// 构造函数 /// </summary> /// <param name="ownerForm">拥用者窗体</param> /// <param name="dataGridView">DataGridView对象</param> public DataGridViewToExcel(Form ownerForm, DataGridView dataGridView) { _OwnerForm = ownerForm; _DataGridView = dataGridView; } /// <summary> /// 导出到Excel /// </summary> /// <param name="pfileName">文件名,不需要带扩展名</param> public void ExportToExcel(string pfileName) { if (_DataGridView.Rows.Count > 0) { _FileName = pfileName == null ? "未命名" : pfileName.Trim(); string fileName = ShowSaveFileDialog("Microsoft Excel Document", "Microsoft Excel|*.xls"); if (fileName != "") { try { ExportTo(fileName); OpenFile(fileName); } catch (System.Exception err) { MessageBox.Show(err.Message); } } } else { MessageBox.Show("没有可导出的数据!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } } /// <summary> /// 导出到 /// </summary> /// <param name="fileName">文件名</param> private void ExportTo(string fileName) { // //获取DataGridView中所有Visible = true的列 List<DataGridViewColumn> dataGridViewColumns = getDataGridViewColumns(); // //定义一个colCount用于记录当前DataGridView中Visible = true的列数 int colCount = dataGridViewColumns.Count; // //定义一个rowCount用于记录当前DataGridView中数据的总行数 long rowCount = _DataGridView.Rows.Count; // //声明一Excel Application 对象 Excel.Application xlApp = new Excel.ApplicationClass(); xlApp.DisplayAlerts = true; xlApp.SheetsInNewWorkbook = 1; Excel.Workbook xlBook = xlApp.Workbooks.Add(true); Excel.Worksheet ws = (Excel.Worksheet)xlBook.Worksheets[1]; // //定义一个Excel区域对象,用于保存选择的区域 Excel.Range r = ws.get_Range("A1", NumToExeclRowStr(colCount) + 1); // //row记录DataGridView中数据的行号,col记录DataGridView中数据的列号,startRow记录在Excel中记录的开始位置 int row = 0, col = 0, startRow = 0; // //定义一个object数组dgvHeadText，用于存放DataGridView列头文本 object[] objHeadText = new object[colCount]; // //循环将DataGridView中所有列的列头文本存入到object一维数组objHeadText int i = 0; foreach (DataGridViewColumn dgvc in dataGridViewColumns) { objHeadText[i++] = dgvc.HeaderText; } startRow++;//向Excel中每写一行表头startRow就加一 r = ws.get_Range("A" + 1, NumToExeclRowStr(colCount) + 1);//设置表头数据区域在Excel中的起始坐标范围 r = r.get_Resize(startRow, colCount);//设置表头数据区域在Excel中所占的行数和列数 r.Value2 = objHeadText;//将数组objHeadText中的所有数据整体写入所设置的表头数据区域 // //向Excel中写完表头后设置表头背景色和字体 r.Interior.ColorIndex = 16; r.Interior.Pattern = Excel.Constants.xlSolid; r.Font.ColorIndex = 2; r.HorizontalAlignment = Excel.Constants.xlCenter; r.VerticalAlignment = Excel.Constants.xlCenter; List<Type> lstType = new List<Type>(); foreach (DataGridViewColumn dgvc in dataGridViewColumns) { lstType.Add(dgvc.ValueType); } // //定义一个和DataGridView中行数和列数同样大小的object二维数组objRecord object[,] objRecord = new object[rowCount, colCount]; // //循环将DataGridView中的所有数据存入到object二维数组objRecord foreach (DataGridViewRow dgvr in _DataGridView.Rows) { col = 0; foreach (DataGridViewColumn dgvc in dataGridViewColumns) { if (lstType[col].Equals(typeof(string))) { objRecord[row, col] = "'" + dgvr.Cells[dgvc.Name].Value.ToString(); } else { objRecord[row, col] = dgvr.Cells[dgvc.Name].Value; } col++; } row++; } r = ws.get_Range("A" + (startRow + 1), NumToExeclRowStr(colCount) + (startRow + 1));//设置数据区域在Excel中的起始坐标范围 r = r.get_Resize(rowCount, colCount);//设置数据区域在Excel中所占的行数和列数 r.Value2 = objRecord;//将二维数组objRecord中的所有数据整体写入所设置的数据区域 r.EntireColumn.AutoFit();//设置Excel内的所有数据列自动调整 // //写入完成后将有数据的范围内设置其边框与内部线条 ws.UsedRange.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = 3; ws.UsedRange.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = 3; ws.UsedRange.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = 3; ws.UsedRange.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = 3; ws.UsedRange.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = 2; ws.UsedRange.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = 2; xlBook.Saved = true;//设置Wookbook为已保存状态 try { xlBook.SaveCopyAs(fileName);//将当前工作薄保存为指定的文件名 } catch (Exception ex) { MessageBox.Show(ex.Message); } xlApp.Quit();//退出Excel程序 } // //获取DataGridView中所有Visible = true的列 private List<DataGridViewColumn> getDataGridViewColumns() { List<DataGridViewColumn> list = new List<DataGridViewColumn>(); foreach (DataGridViewColumn dgvc in _DataGridView.Columns) { if (dgvc.Visible == true) { list.Add(dgvc); } } return list; } private string NumTochr(int num) { int n = 64 + num; return "" + (Char)n; } // //根据DataGridView行数和列数获取Excel的横纵坐标 private string NumToExeclRowStr(int num) { int x, y; if (num < 27) { return NumTochr(num); } x = num / 26; y = num - x * 26; return NumTochr(x) + NumTochr(y); } /// <summary> /// 打开文件 /// </summary> /// <param name="fileName">文件名</param> private void OpenFile(string fileName) { if (MessageBox.Show("您想打开此文件吗?", "打开", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) { try { System.Diagnostics.Process process = new System.Diagnostics.Process(); process.StartInfo.FileName = fileName; process.StartInfo.Verb = "Open"; process.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Normal; process.Start(); } catch { MessageBox.Show(_OwnerForm, "你的计算机中未安装Excel,不能打开该文档!", Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error); } } } /// <summary> /// 显示保存文件对话框,并返回选择的文件路径 /// </summary> /// <param name="title">对话框的标题</param> /// <param name="filter">过滤器</param> /// <returns>选择的文件路径</returns> private string ShowSaveFileDialog(string title, string filter) { SaveFileDialog dlg = new SaveFileDialog(); string name = _FileName; int n = name.LastIndexOf(".") + 1; if (n > 0) name = name.Substring(n, name.Length - n); dlg.Title = "导出到" + title; dlg.FileName = name; dlg.Filter = filter; if (dlg.ShowDialog() == DialogResult.OK) return dlg.FileName; return ""; } } }