• 设为首页
  • 点击收藏
  • 手机版
    手机扫一扫访问
    迪恩网络手机版
  • 关注官方公众号
    微信扫一扫关注
    公众号

C#-VS2019DataGridView导出到Excel的三种方法

原作者: [db:作者] 来自: [db:来源] 收藏 邀请
 //原文出处:http://www.yongfa365.com/Item/DataGridViewToExcel.html
1
#region DataGridView数据显示到Excel 2 /// <summary> 3 /// 打开Excel并将DataGridView控件中数据导出到Excel 4 /// </summary> 5 /// <param name="dgv">DataGridView对象 </param> 6 /// <param name="isShowExcle">是否显示Excel界面 </param> 7 /// <remarks> 8 /// add com "Microsoft Excel 11.0 Object Library" 9 /// using Excel=Microsoft.Office.Interop.Excel; 10 /// </remarks> 11 /// <returns> </returns> 12 public bool DataGridviewShowToExcel(DataGridView dgv, bool isShowExcle) 13 { 14 if (dgv.Rows.Count == 0) 15 return false; 16 //建立Excel对象 17 Excel.Application excel = new Excel.Application(); 18 excel.Application.Workbooks.Add(true); 19 excel.Visible = isShowExcle; 20 //生成字段名称 21 for (int i = 0; i < dgv.ColumnCount; i++) 22 { 23 excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText; 24 } 25 //填充数据 26 for (int i = 0; i < dgv.RowCount - 1; i++) 27 { 28 for (int j = 0; j < dgv.ColumnCount; j++) 29 { 30 if (dgv[j, i].ValueType == typeof(string)) 31 { 32 excel.Cells[i + 2, j + 1] = "'" + dgv[j, i].Value.ToString(); 33 } 34 else 35 { 36 excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString(); 37 } 38 } 39 } 40 return true; 41 } 42 #endregion 43 44 #region DateGridView导出到csv格式的Excel 45 /// <summary> 46 /// 常用方法,列之间加\t,一行一行输出,此文件其实是csv文件,不过默认可以当成Excel打开。 47 /// </summary> 48 /// <remarks> 49 /// using System.IO; 50 /// </remarks> 51 /// <param name="dgv"></param> 52 private void DataGridViewToExcel(DataGridView dgv) 53 { 54 SaveFileDialog dlg = new SaveFileDialog(); 55 dlg.Filter = "Execl files (*.xls)|*.xls"; 56 dlg.FilterIndex = 0; 57 dlg.RestoreDirectory = true; 58 dlg.CreatePrompt = true; 59 dlg.Title = "保存为Excel文件"; 60 61 if (dlg.ShowDialog() == DialogResult.OK) 62 { 63 Stream myStream; 64 myStream = dlg.OpenFile(); 65 StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0)); 66 string columnTitle = ""; 67 try 68 { 69 //写入列标题 70 for (int i = 0; i < dgv.ColumnCount; i++) 71 { 72 if (i > 0) 73 { 74 columnTitle += "\t"; 75 } 76 columnTitle += dgv.Columns[i].HeaderText; 77 } 78 sw.WriteLine(columnTitle); 79 80 //写入列内容 81 for (int j = 0; j < dgv.Rows.Count; j++) 82 { 83 string columnValue = ""; 84 for (int k = 0; k < dgv.Columns.Count; k++) 85 { 86 if (k > 0) 87 { 88 columnValue += "\t"; 89 } 90 if (dgv.Rows[j].Cells[k].Value == null) 91 columnValue += ""; 92 else 93 columnValue += dgv.Rows[j].Cells[k].Value.ToString().Trim(); 94 } 95 sw.WriteLine(columnValue); 96 } 97 sw.Close(); 98 myStream.Close(); 99 } 100 catch (Exception e) 101 { 102 MessageBox.Show(e.ToString()); 103 } 104 finally 105 { 106 sw.Close(); 107 myStream.Close(); 108 } 109 } 110 } 111 #endregion 112 113 #region DataGridView导出到Excel,有一定的判断性 114 /// <summary> 115 ///方法,导出DataGridView中的数据到Excel文件 116 /// </summary> 117 /// <remarks> 118 /// add com "Microsoft Excel 11.0 Object Library" 119 /// using Excel=Microsoft.Office.Interop.Excel; 120 /// using System.Reflection; 121 /// </remarks> 122 /// <param name= "dgv"> DataGridView </param> 123 public static void DataGridViewToExcel(DataGridView dgv) 124 { 125 126 127 #region 验证可操作性 128 129 //申明保存对话框 130 SaveFileDialog dlg = new SaveFileDialog(); 131 //默然文件后缀 132 dlg.DefaultExt = "xls "; 133 //文件后缀列表 134 dlg.Filter = "EXCEL文件(*.XLS)|*.xls "; 135 //默然路径是系统当前路径 136 dlg.InitialDirectory = Directory.GetCurrentDirectory(); 137 //打开保存对话框 138 if (dlg.ShowDialog() == DialogResult.Cancel) return; 139 //返回文件路径 140 string fileNameString = dlg.FileName; 141 //验证strFileName是否为空或值无效 142 if (fileNameString.Trim() == " ") 143 { return; } 144 //定义表格内数据的行数和列数 145 int rowscount = dgv.Rows.Count; 146 int colscount = dgv.Columns.Count; 147 //行数必须大于0 148 if (rowscount <= 0) 149 { 150 MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); 151 return; 152 } 153 154 //列数必须大于0 155 if (colscount <= 0) 156 { 157 MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); 158 return; 159 } 160 161 //行数不可以大于65536 162 if (rowscount > 65536) 163 { 164 MessageBox.Show("数据记录数太多(最多不能超过65536条),不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); 165 return; 166 } 167 168 //列数不可以大于255 169 if (colscount > 255) 170 { 171 MessageBox.Show("数据记录行数太多,不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); 172 return; 173 } 174 175 //验证以fileNameString命名的文件是否存在,如果存在删除它 176 FileInfo file = new FileInfo(fileNameString); 177 if (file.Exists) 178 { 179 try 180 { 181 file.Delete(); 182 } 183 catch (Exception error) 184 { 185 MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning); 186 return; 187 } 188 } 189 #endregion 190 Excel.Application objExcel = null; 191 Excel.Workbook objWorkbook = null; 192 Excel.Worksheet objsheet = null; 193 try 194 { 195 //申明对象 196 objExcel = new Microsoft.Office.Interop.Excel.Application(); 197 objWorkbook = objExcel.Workbooks.Add(Missing.Value); 198 objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet; 199 //设置EXCEL不可见 200 objExcel.Visible = false; 201 202 //向Excel中写入表格的表头 203 int displayColumnsCount = 1; 204 for (int i = 0; i <= dgv.ColumnCount - 1; i++) 205 { 206 if (dgv.Columns[i].Visible == true) 207 { 208 objExcel.Cells[1, displayColumnsCount] = dgv.Columns[i].HeaderText.Trim(); 209 displayColumnsCount++; 210 } 211 } 212 //设置进度条 213 //tempProgressBar.Refresh(); 214 //tempProgressBar.Visible = true; 215 //tempProgressBar.Minimum=1; 216 //tempProgressBar.Maximum=dgv.RowCount; 217 //tempProgressBar.Step=1; 218 //向Excel中逐行逐列写入表格中的数据 219 for (int row = 0; row <= dgv.RowCount - 1; row++) 220 { 221 //tempProgressBar.PerformStep(); 222 223 displayColumnsCount = 1; 224 for (int col = 0; col < colscount; col++) 225 { 226 if (dgv.Columns[col].Visible == true) 227 { 228 try 229 { 230 objExcel.Cells[row + 2, displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim(); 231 displayColumnsCount++; 232 } 233 catch (Exception) 234 { 235 236 } 237 238 } 239 } 240 } 241 //隐藏进度条 242 //tempProgressBar.Visible = false; 243 //保存文件 244 objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value, 245 Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value, 246 Missing.Value, Missing.Value); 247 } 248 catch (Exception error) 249 { 250 MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning); 251 return; 252 } 253 finally 254 { 255 //关闭Excel应用 256 if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value); 257 if (objExcel.Workbooks != null) objExcel.Workbooks.Close(); 258 if (objExcel != null) objExcel.Quit(); 259 260 objsheet = null; 261 objWorkbook = null; 262 objExcel = null; 263 } 264 MessageBox.Show(fileNameString + "\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information); 265 266 } 267 268 #endregion

 

 1         #region DataGridView导出到Excel解决打开时报后缀名不一致的问题
 2         /// <summary>
 3         /// DataGridView导出至Excel,解决问题:打开Excel文件格式与扩展名指定格式不一致
 4         /// </summary>
 5         /// <param name="dataGridView">数据源表格</param>
 6         /// <param name="isShowExcle">导出时是否显示excel界面</param>
 7         /// <returns></returns>
 8         public static bool DcExcel(DataGridView dataGridView, bool isShowExcle = true)
 9         {
10             int FormatNum;//保存excel文件的格式
11             Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
12             string excelVersion = excel.Version;//获取你使用的excel 的版本号 
13 
14             //声明保存对话框 
15             SaveFileDialog saveFileDialog = new SaveFileDialog();
16             //默然文件后缀 
17             saveFileDialog.DefaultExt = "xls";
18 
19             if (Convert.ToDouble(excelVersion) < 12)//You use Excel 97-2003
20             {
21                 FormatNum = -4143;
22                 //文件后缀列表 
23                 saveFileDialog.Filter = "Excel(*.xls)|*.xls";
24             }
25             else//you use excel 2007 or later
26             {
27                 FormatNum = 56;
28                 //文件后缀列表 
29                 saveFileDialog.Filter = "Excel(*.xls)|*.xls|Excel(2007-2016)(*.xlsx)|*.xlsx";
30             }
31             Form fr = dataGridView.Parent as Form;
32             if (fr != null)//默认文件名
33             {
34                 saveFileDialog.FileName = fr.Text;
35             }
36             //默然路径是系统当前路径 
37             saveFileDialog.InitialDirectory = Directory.GetCurrentDirectory();
38             //打开保存对话框 
39             if (saveFileDialog.ShowDialog() == DialogResult.Cancel)
40                 return false;
41             //返回文件路径 
42             string fileName = saveFileDialog.FileName;
43             if (string.IsNullOrEmpty(fileName.Trim()))
44             { return false; }
45             if (dataGridView.Rows.Count == 0)
46                 return false;
47             //建立Excel对象      
48 
49             var objWorkbook = excel.Application.Workbooks.Add(true);
50             excel.Visible = isShowExcle;
51             //生成字段名称      
52             for (int i = 0; i < dataGridView.ColumnCount; i++)
53             {
54                 excel.Cells[1, i + 1] = dataGridView.Columns[i].HeaderText;
55                 excel.Cells[1, i + 1].Font.Bold = true;
56             }
57             //填充数据      
58             for (int i = 0; i < dataGridView.RowCount - 1; i++)
59             {
60                 for (int j = 0; j < dataGridView.ColumnCount; j++)
61                 {
62                     if (dataGridView[j, i].ValueType == typeof(string))
63                     {
64                         excel.Cells[i + 2, j + 1] = "'" + dataGridView[j, i].Value.ToString();
65                     }
66                     else
67                     {
68                         excel.Cells[i + 2, j + 1] = dataGridView[j, i].Value.ToString();
69                     }
70                 }
71             }
72             //Excel.XlFileFormat.xlOpenXMLWorkbook(.xlsx)
73             //Excel.XlFileFormat.xlExcel8(Excel97 - 2003, .xls)
74             //判断excel文件的保存格式是xls还是xlsx
75             var format = fileName.EndsWith(".xls") ? Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8 : Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook;
76             objWorkbook.SaveAs(fileName, format, Missing.Value, Missing.Value, Missing.Value,
77                 Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
78                 Missing.Value, Missing.Value);
79             return true;
80         }
81         #endregion

 


鲜花

握手

雷人

路过

鸡蛋
该文章已有0人参与评论

请发表评论

全部评论

专题导读
上一篇:
C#汉字的字符串截取指定字节的长度发布时间:2022-07-13
下一篇:
C#-继承的优点与缺点发布时间:2022-07-13
热门推荐
热门话题
阅读排行榜

扫描微信二维码

查看手机版网站

随时了解更新最新资讯

139-2527-9053

在线客服(服务时间 9:00~18:00)

在线QQ客服
地址:深圳市南山区西丽大学城创智工业园
电邮:jeky_zhao#qq.com
移动电话:139-2527-9053

Powered by 互联科技 X3.4© 2001-2213 极客世界.|Sitemap