Excel文件读写操作(二)

        /// <summary>
        /// 利用com组件读取excel文件
        /// </summary>
        /// <param name="fileName">文件路径</param>
        /// <returns></returns>
        private System.Data.DataTable ExcelToDataSet(string fileName)
        {
            //创建Excel对象
            Microsoft.Office.Interop.Excel.Application myExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
            //获取工作表集合
            Microsoft.Office.Interop.Excel.Workbooks myBooks = myExcel.Application.Workbooks;
            object oMissing = System.Reflection.Missing.Value;

            //打开指定路径的excel文件
            Microsoft.Office.Interop.Excel.Workbook myBook = myBooks.Open(fileName, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);

            //得到工作表的数量
            int sheetint = myBook.Worksheets.Count;

            Microsoft.Office.Interop.Excel.Worksheet mySheet = (Microsoft.Office.Interop.Excel.Worksheet)myBook.Worksheets[1];
            //得到行数
            int rowsint = mySheet.UsedRange.Cells.Rows.Count;
            //得到列数
            int columnsint = mySheet.UsedRange.Cells.Columns.Count;
            System.Data.DataTable dt = new System.Data.DataTable("mytable");
            for (int i = 1; i <=columnsint; i++)
            {
                Microsoft.Office.Interop.Excel.Range r = (Microsoft.Office.Interop.Excel.Range)mySheet.Cells[1, i];
                dt.Columns.Add(r.Text.ToString(), System.Type.GetType("System.String"));
            }
            DataSet myDs = new DataSet();
            myDs.Tables.Add(dt);
            DataRow myRow;
            myDs.Clear();

            //第一行以作为列标题，从第二行开始读取
            for (int i = 2; i < rowsint; i++)
            {
                myRow = myDs.Tables["mytable"].NewRow();
                for (int j = 1; j <= columnsint; j++)
                {
                    Microsoft.Office.Interop.Excel.Range r = (Microsoft.Office.Interop.Excel.Range)mySheet.Cells[i, j];
                    string strValue = r.Text.ToString();
                    string aa = strValue;
                    string columnname = "F" + j.ToString();
                    myRow[j-1] = strValue;

                }
                myDs.Tables["mytable"].Rows.Add(myRow);
            }

            return myDs.Tables["mytable"];


        }

        /// <summary>
        /// 获取datagridviews的数据
        /// </summary>
        /// <param name="dataGridView"></param>
        /// <returns></returns>
        private System.Data.DataTable GetExcelTable(DataGridView dgv)
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            //添加列
            for (int count = 0; count < dgv.Columns.Count; count++)
            {
                DataColumn dc = new DataColumn(dgv.Columns[count].Name.ToString());
                dt.Columns.Add(dc);
            }
            //添加行
            for (int count = 0; count < dgv.Rows.Count; count++)
            {
                DataRow dr = dt.NewRow();
                for (int countsub = 0; countsub < dgv.Columns.Count; countsub++)
                {
                    dr[countsub] = dgv.Rows[count].Cells[countsub].Value.ToString();
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }

        /// <summary>
        /// 写入excel内容
        /// </summary>
        /// <param name="excelTable">导入excel的table表</param>
        /// <param name="filePath">保存路径</param>
        /// <returns></returns>
        public static bool SaveDataTableToExcel(System.Data.DataTable excelTable, string filePath)
        {
            //判读网格中是否有数据
            if (excelTable.Rows.Count == 0 || filePath == string.Empty)   
            {   
                return false;   
            }

            //创建Excel操作对象
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();  
            //行号
            int rowindex = 1;
            //列号
            int colindex = 0;   
            Workbook work = excel.Workbooks.Add(true);   
            //遍历列，设置excel的列标题
            foreach (DataColumn col in excelTable.Columns)   
            {   
                colindex++;   
                excel.Cells[1, colindex] = col.ColumnName;   
            }
            foreach (DataRow row in excelTable.Rows)   
            {   
                rowindex++;   
                colindex = 0;
                foreach (DataColumn col in excelTable.Columns)   
                {   
                    colindex++;   
                    excel.Cells[rowindex, colindex] = row[col.ColumnName].ToString();   
                }   
            }   
            excel.Visible = false;     
            //保存Excel
            excel.ActiveWorkbook.SaveAs(filePath, XlFileFormat.xlExcel9795, null, null, false, false,XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);   
            excel.Quit();   
            excel = null;   
            GC.Collect();
            MessageBox.Show("数据已成功导出Excel表中");
            return true; 

        }