C#Winform实现高效率导入和导出Excel文件

来源：互联网发布：linux下配置环境变量编辑：程序博客网时间：2024/05/22 06:57

/// <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(), "提示");            }        }

0 0