#region 导入、导出方法 /// <summary> /// DataGridView导出Excel（工作区域保护） /// </summary> /// <param name="strCaption">Excel文件中的标题</param> /// <param name="fileName">文件名</param> /// <param name="noLockColumnNames">不需要保护的列明</param> /// <returns>返回信息，空为成功</returns> public string ExportExcel(string strCaption, string fileName, string[] noLockColumnNames) { string result = ""; // 列索引，行索引，总列数，总行数 int ColIndex = 0; int RowIndex = 0; int ColCount = this.ColumnCount; int RowCount = this.RowCount; if (this.RowCount == 0) { result = "无记录"; } // 创建Excel对象 Excel.Application xlApp = new Excel.ApplicationClass(); if (xlApp == null) { result = "Excel无法启动"; } try { // 创建Excel工作薄 Excel.Workbook xlBook = xlApp.Workbooks.Add(true); Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Worksheets[1]; // 设置标题 Excel.Range range = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, ColCount]); //标题所占的单元格数与DataGridView中的列数相同 range.MergeCells = true; xlApp.ActiveCell.FormulaR1C1 = strCaption; xlApp.ActiveCell.Font.Size = 20; xlApp.ActiveCell.Font.Bold = true; xlApp.ActiveCell.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; // 创建缓存数据 object[,] objData = new object[RowCount + 1, ColCount]; //获取列标题 foreach (DataGridViewColumn col in this.Columns) { objData[RowIndex, ColIndex++] = col.HeaderText; } // 获取数据 for (RowIndex = 1; RowIndex <= RowCount; RowIndex++) { for (ColIndex = 0; ColIndex < ColCount; ColIndex++) { if (noLockColumnNames != null ) { if (noLockColumnNames.Length != 0) { Array.Sort(noLockColumnNames); int num = Array.BinarySearch(noLockColumnNames, this.Columns[ColIndex].Name); if (num >= 0) { xlSheet.get_Range(xlApp.Cells[3, ColIndex + 1], xlApp.Cells[RowCount + 2, ColIndex + 1]).Locked = false; } } } if (this[ColIndex, RowIndex - 1].ValueType == typeof(string) || this[ColIndex, RowIndex - 1].ValueType == typeof(DateTime))//这里就是验证DataGridView单元格中的类型,如果是string或是DataTime类型,则在放入缓存时在该内容前加入" "; { objData[RowIndex, ColIndex] = "" + this[ColIndex, RowIndex - 1].Value; } else { objData[RowIndex, ColIndex] = this[ColIndex, RowIndex - 1].Value; } //设置隐藏列 if (this[ColIndex, RowIndex - 1].Visible == false) { Excel.Range range2 = xlSheet.get_Range(xlApp.Cells[1, ColIndex + 1], xlApp.Cells[100, ColIndex + 1]); range2.EntireColumn.Hidden = true; //range2.EntireColumn.ColumnWidth = 0; } else { //Excel.Range range2 = xlSheet.get_Range(xlApp.Cells[1, ColIndex + 1], xlApp.Cells[100, ColIndex + 1]); //range2.Columns.EntireColumn.AutoFit(); //xlApp.Columns.EntireColumn.AutoFit(); } } System.Windows.Forms.Application.DoEvents(); } // 写入Excel range = xlSheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[RowCount + 2, ColCount]); range.Value2 = objData; range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, null);//加黑框 if (this.Rows.Count > 0) { range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic; range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous; range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin; } if (this.Columns.Count > 1) { range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic; range.Borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous; range.Borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin; } range.Select(); xlSheet.Columns.EntireColumn.AutoFit(); // 获取数据 for (RowIndex = 1; RowIndex <= RowCount; RowIndex++) { for (ColIndex = 0; ColIndex < ColCount; ColIndex++) { //设置隐藏列 if (this[ColIndex, RowIndex - 1].Visible == false) { Excel.Range range2 = xlSheet.get_Range(xlApp.Cells[1, ColIndex + 1], xlApp.Cells[100, ColIndex + 1]); range2.EntireColumn.Hidden = true; //range2.EntireColumn.ColumnWidth = 0; } } } xlSheet.Protect("scmapp@cppei",//1Password true, //2保护形状 true, //3 true, //4 Type.Missing, //5 true, //6 true, //7 true,//8 false, //9 false, //10 false,//11 false,//12 false, //13 false, //14 false, //15 true //16 ); xlBook.Saved = true; xlBook.SaveCopyAs(fileName); } catch (Exception err) { result = err.ToString(); } finally { xlApp.Quit(); GC.Collect(); //强制回收 } return result; } /// <summary> /// DataGridView导出Excel（无保护） /// </summary> /// <param name="strCaption">Excel文件中的标题</param> /// <param name="fileName">文件名</param> /// <returns>返回信息，空为成功</returns> public string ExportExcel(string strCaption, string fileName) { string[] colList = { }; return ExportExcel(strCaption, fileName, colList); } /// <summary> /// 导入方法 /// </summary> /// <returns></returns> public string ImportExcel(DataTable dt) { string fileName; string _ReturnMessage = ""; OpenFileDialog fd = new OpenFileDialog(); fd.Filter = "xls files (*.xls)|*.xls|xlsx files (*.xlsx)|*.xlsx"; fd.Multiselect = false; //每次选择一个 fd.RestoreDirectory = true; //保存上一次的路径 fd.ValidateNames = true; //检查文件名的有效性 fd.CheckFileExists = true; //检查文件存在否 fd.CheckPathExists = true; //检查文件路径存在否 if (fd.ShowDialog() != DialogResult.OK) { return null; } fileName = fd.FileName; #region 准备EXCEL //判断是否安装EXCEL Excel.Application xlApp = new Excel.ApplicationClass(); if (xlApp == null) { _ReturnMessage = "无法创建Excel对象，可能您的计算机未安装Excel"; return null; } //判断文件是否被其他进程使用 Excel.Workbook workbook; try { workbook = xlApp.Workbooks.Open(fileName, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0); } catch { _ReturnMessage = "Excel文件处于打开状态，请保存关闭"; return null; } //获得所有Sheet名称 int n = workbook.Worksheets.Count; string[] SheetSet = new string[n]; System.Collections.ArrayList al = new System.Collections.ArrayList(); for (int i = 1; i <= n; i++) { SheetSet[i - 1] = ((Excel.Worksheet)workbook.Worksheets[i]).Name; } //释放Excel相关对象 workbook.Close(null, null, null); xlApp.Quit(); if (workbook != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); workbook = null; } if (xlApp != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); xlApp = null; } GC.Collect(); #endregion //把EXCEL导入到DataSet DataSet ds = new DataSet(); for (int i = 0; i < this.Columns.Count; i++) { try { dt.Columns.Add(this.Columns[i].DataPropertyName); } catch (System.Exception ex) { } } bool openconnsuccess = true; string connStr = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + fileName + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';"; //HDR=YES 有两个值:YES/NO,表示第一行是否字段名,默认是YES,第一行是字段名 . IMEX=1 解决数字与字符混合时,识别不正常的情况=1表示所有数据按照字符处理 //string connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HDR=YES\";";//打开2007 OleDbConnection conn = new OleDbConnection(connStr); try { conn.Open(); } catch (System.Exception ex) { openconnsuccess = false; } if (!openconnsuccess) { try { connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1';"; conn = new OleDbConnection(connStr); conn.Open(); openconnsuccess = true; } catch (System.Exception ex1) { } } if (!openconnsuccess) { try { connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 14.0 Xml;HDR=YES;IMEX=1';"; conn = new OleDbConnection(connStr); conn.Open(); openconnsuccess = true; } catch (System.Exception ex1) { } } if (!openconnsuccess) { MessageBox.Show("与excel连接失败，请检查是否正确安装了OFFICE excel"); return null; } using (conn) { //OleDbDataAdapter da; int row = 0; //for(int i=1; i<=n; i++) //{ int[] indx = new int[dt.Columns.Count]; OleDbCommand cmd = conn.CreateCommand(); cmd.CommandText = "select * from [" + SheetSet[0] + "$] ";//从第一个表中读取数据 OleDbDataReader odr = cmd.ExecuteReader(); while (odr.Read()) { row++; if (row == 1) continue; if (odr[0].ToString() == string.Empty && odr[1].ToString() == string.Empty) continue; if (row > this.Rows.Count + 1) break; ; object[] drow = new object[dt.Columns.Count]; for (int j = 0; j < dt.Columns.Count; j++) { //drow[j] = odr[indx[j]]; drow[j] = odr[j]; } dt.Rows.Add(drow); } odr.Close(); conn.Close(); conn.Dispose(); } return _ReturnMessage; } #endregion
