Biegral Blog

/// <summary>
        /// 导出Excel文件
        /// </summary>
        /// /// <param name="dataSet"></param>
        /// <param name="dataTable">数据集</param>
        /// <param name="isShowExcle">导出后是否打开文件</param>
        /// <returns></returns>
        public static bool DataTableToExcel(string filePath, System.Data.DataTable dataTable, bool isShowExcle)
        {
            //System.Data.DataTable dataTable = dataSet.Tables[0];
            int rowNumber = dataTable.Rows.Count;
            int columnNumber = dataTable.Columns.Count;
            int colIndex = 0;

            if (rowNumber == 0)
            {
                return false;
            }

            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
            excel.Visible = isShowExcle;
            Microsoft.Office.Interop.Excel.Range range;


            foreach (DataColumn col in dataTable.Columns)
            {
                colIndex++;
                excel.Cells[1, colIndex] = col.ColumnName;
            }

            object[,] objData = new object[rowNumber, columnNumber];

            for (int r = 0; r < rowNumber; r++)
            {
                for (int c = 0; c < columnNumber; c++)
                {
                    objData[r, c] =dataTable.Rows[r][c];
                }
            }

            range = worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1, columnNumber]);

            range.Value2 = objData;

            range.NumberFormatLocal = "@";

            worksheet.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

           //excel.Quit();
            return true;
        }

  /// <summary>
        /// 读取Excel文件数据到DataTable
        /// </summary>
        /// <param name="filePath">Excel文件路径</param>
        private void Import_Excel(string filePath)
        {
            string sqlconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";

            string sql = @"select * from [Sheet1$]";

            try
            {
                using (OleDbConnection conn = new OleDbConnection(sqlconn))
                {
                    using (OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn))
                    {
                        System.Data.DataTable dt = new System.Data.DataTable();
                        adapter.Fill(dt);

                        this.LoadDataGridView(dt);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("打开文件出错，错误信息：" + ex.Message.ToString(), "提示");
            }
        }