DataTable中数据导出成Excel文件

旧城等待， 2022-08-06 15:11 212阅读 0赞

/// <summary>
            /// 要素属性信息导出
            /// </summary>
            /// <param name="dtFeature">虚拟表</param>
    	/// <param name="LayerName">图层名称</param>
            /// <returns></returns>
            public static bool ExportToExcel(DataTable dtFeature，string LayerName)
            {
                bool bResult = false;
                string sFileName = string.Empty;
    
                try
                {
    		 判断虚拟表是否为空，且是否有数据
                    if (dtFeature != null && dtFeature.Rows.Count > 0)
                    {
    		     获得系统时间
                        string sTime = System.DateTime.Now.ToString("yyyyMMddHHmmss");
                        SaveFileDialog SaveDlg = new SaveFileDialog();
                        SaveDlg.Filter = "Excel(*.xlsx)|*.xlsx";
    		     设置导出的Excel文件的文件名
                        SaveDlg.FileName = LayerName + "图层的要素属性信息(" + sTime + ")";
    
                        if (SaveDlg.ShowDialog() == DialogResult.OK)
                        {
                            sFileName = SaveDlg.FileName;
                            object missing = System.Reflection.Missing.Value;
    			 定义Excel接口
                            Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
    			 定义工作簿
                            Microsoft.Office.Interop.Excel._Workbook workbook;
    			 定义工作页
                            Microsoft.Office.Interop.Excel._Worksheet worksheet;
    			 定义活动范围
                            Microsoft.Office.Interop.Excel.Range range;
    			 添加工作簿
                            ExcelApp.Application.Workbooks.Add(true);
    			 激活工作簿
                            workbook = ExcelApp.ActiveWorkbook;
    			 激活工作页
                            worksheet = (Microsoft.Office.Interop.Excel._Worksheet)workbook.ActiveSheet;
                       
                             Excel中添加列名称
                            for (int k = 0; k < dtFeature.Columns.Count; k++)
                            {
                                worksheet.Cells[1, k + 1] = dtFeature.Columns[k].ColumnName;
                            }
    
                             Excel中添加要素信息
                            for (int i = 0; i < dtFeature.Rows.Count; i++)
                            {
    
                                for (int j = 0; j < dtFeature.Columns.Count; j++)
                                {
                                    worksheet.Cells[i + 2, j + 1] = dtFeature.Rows[i][j];
                                }
                            }
    
                             自适应列宽
                            range = worksheet.Columns;
                            range.AutoFit();
    
                            workbook.SaveCopyAs(SaveDlg.FileName);
                            workbook.Close(false, missing, missing);
                            ExcelApp.Quit();
                            bResult = true;
    
                        }
                    }
                }
                catch (Exception ex)
                {
                    LogOperation.WriteLog("要素属性信息导出成Excel失败", ex.ToString());
                    bResult = false;
                }
                finally
                {
                    if (bResult)
                    {
                        if (GtMap.GxDlgHelper.DevMessageBox.ShowConfirmation("要素信息导出成功，是否打开所在文件？") == DialogResult.Yes)
                        {
                            System.Diagnostics.Process.Start(sFileName);
                        }
                    }
                }
    
                return bResult;
            }