/// <summary> /// 打开Excel并将DataGridView控件中数据导出到Excel /// </summary> /// <param name="dgv">DataGridView对象 </param> /// <param name="isShowExcle">是否显示Excel界面 </param> /// <remarks> /// add com "Microsoft Excel 11.0 Object Library" /// using Excel=Microsoft.Office.Interop.Excel; /// </remarks> /// <returns> </returns> public bool DataGridviewShowToExcel(DevComponents.DotNetBar.SuperGrid.SuperGridControl dgv, bool isShowExcle) { if (dgv.PrimaryGrid.Rows.Count == 0) { return(false); } //建立Excel对象 Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); excel.Application.Workbooks.Add(true); excel.Visible = isShowExcle; //生成字段名称 for (int i = 0; i < dgv.PrimaryGrid.Columns.Count; i++) { excel.Cells[1, i + 1] = dgv.PrimaryGrid.Columns[i].HeaderText; } //填充数据 for (int i = 0; i < dgv.PrimaryGrid.Rows.Count - 1; i++) { for (int j = 0; j < dgv.PrimaryGrid.Columns.Count; j++) { if (dgv.PrimaryGrid.GetCell(i, j).GetType() == typeof(string)) { excel.Cells[i + 2, j + 1] = "'" + dgv.PrimaryGrid.GetCell(i, j).Value.ToString(); } else { excel.Cells[i + 2, j + 1] = dgv.PrimaryGrid.GetCell(i, j).Value.ToString(); } } } return(true); }
/// <summary> /// 常用方法,列之间加\t,一行一行输出,此文件其实是csv文件,不过默认可以当成Excel打开。 /// </summary> /// <remarks> /// using System.IO; /// </remarks> /// <param name="dgv"></param> public void DataGridViewToExcels(DevComponents.DotNetBar.SuperGrid.SuperGridControl dgv) { SaveFileDialog dlg = new SaveFileDialog(); dlg.Filter = "Execl files (*.xls)|*.xls"; dlg.FilterIndex = 0; dlg.RestoreDirectory = true; dlg.CreatePrompt = true; dlg.Title = "保存为Excel文件"; if (dlg.ShowDialog() == DialogResult.OK) { Stream myStream; myStream = dlg.OpenFile(); StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0)); string columnTitle = ""; try { //写入列标题 for (int i = 0; i < dgv.PrimaryGrid.Columns.Count; i++) { if (i > 0) { columnTitle += "\t"; } columnTitle += dgv.PrimaryGrid.Columns[i].HeaderText; } sw.WriteLine(columnTitle); //写入列内容 for (int j = 0; j < dgv.PrimaryGrid.Rows.Count; j++) { string columnValue = ""; for (int k = 0; k < dgv.PrimaryGrid.Columns.Count; k++) { if (k > 0) { columnValue += "\t"; } if (dgv.PrimaryGrid.GetCell(j, k).Value == null) { columnValue += ""; } else { columnValue += dgv.PrimaryGrid.GetCell(j, k).Value.ToString().Trim(); } } sw.WriteLine(columnValue); } sw.Close(); myStream.Close(); } catch (Exception e) { MessageBox.Show(e.ToString()); } finally { sw.Close(); myStream.Close(); } } }