Winform导入导出方法总结

  #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