/// <summary> /// DataGridView导出至Excel,解决问题:打开Excel文件格式与扩展名指定格式不一致 /// </summary> /// <param name="dataGridView">数据源表格</param> /// <param name="isShowExcle">导出时是否显示excel界面</param> /// <returns></returns> public static bool DcExcel(DataGridView dataGridView, bool isShowExcle = true) { int FormatNum;//保存excel文件的格式 Excel.Application excel = new Excel.Application(); string excelVersion = excel.Version;//获取你使用的excel 的版本号 //声明保存对话框 SaveFileDialog saveFileDialog = new SaveFileDialog(); //默然文件后缀 saveFileDialog.DefaultExt = "xls"; if (Convert.ToDouble(excelVersion) < 12)//You use Excel 97-2003 { FormatNum = -4143; //文件后缀列表 saveFileDialog.Filter = "Excel(*.xls)|*.xls"; } else//you use excel 2007 or later { FormatNum = 56; //文件后缀列表 saveFileDialog.Filter = "Excel(*.xls)|*.xls|Excel(2007-2016)(*.xlsx)|*.xlsx"; } Form fr = dataGridView.Parent as Form; if (fr != null)//默认文件名 { saveFileDialog.FileName = fr.Text; } //默然路径是系统当前路径 saveFileDialog.InitialDirectory = Directory.GetCurrentDirectory(); //打开保存对话框 if (saveFileDialog.ShowDialog() == DialogResult.Cancel) return false; //返回文件路径 string fileName = saveFileDialog.FileName; if (string.IsNullOrEmpty(fileName.Trim())) { return false; } if (dataGridView.Rows.Count == 0) return false; //建立Excel对象 var objWorkbook = excel.Application.Workbooks.Add(true); excel.Visible = isShowExcle; //生成字段名称 for (int i = 0; i < dataGridView.ColumnCount; i++) { excel.Cells[1, i + 1] = dataGridView.Columns[i].HeaderText; excel.Cells[1, i + 1].Font.Bold = true; } //填充数据 for (int i = 0; i < dataGridView.RowCount - 1; i++) { for (int j = 0; j < dataGridView.ColumnCount; j++) { if (dataGridView[j, i].ValueType == typeof(string)) { excel.Cells[i + 2, j + 1] = "'" + dataGridView[j, i].Value.ToString(); } else { excel.Cells[i + 2, j + 1] = dataGridView[j, i].Value.ToString(); } } } //Excel.XlFileFormat.xlOpenXMLWorkbook(.xlsx) //Excel.XlFileFormat.xlExcel8(Excel97 - 2003, .xls)
//判断excel文件的保存格式是xls还是xlsx var format = fileName.EndsWith(".xls") ? Excel.XlFileFormat.xlExcel8 : Excel.XlFileFormat.xlOpenXMLWorkbook; objWorkbook.SaveAs(fileName, format, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); return true; }