//原文出处：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 finally105 {106 sw.Close();107 myStream.Close();108 }109 }110 } 111 #endregion112 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 try180 {181 file.Delete();182 }183 catch (Exception error)184 {185 MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);186 return;187 }188 }189 #endregion190 Excel.Application objExcel = null;191 Excel.Workbook objWorkbook = null;192 Excel.Worksheet objsheet = null;193 try194 {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 try229 {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 finally254 {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-200320 {21 FormatNum = -4143;22 //文件后缀列表 23 saveFileDialog.Filter = "Excel(*.xls)|*.xls";24 }25 else//you use excel 2007 or later26 {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 else67 {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还是xlsx75 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
联系客服
