Excel 导出-CSDN博客

//调用

dgvRecord.DataSource = dt;

DAL.DataToExcel exp =new DAL.DataToExcel();

            int flag = exp.ExistsRegedit();
            if (flag == 0)
            {
                MessageBox.Show("请先安装office或者wps,再进行导出！");
            }
            else
            {
                if (dt != null && dt.Rows.Count > 0)
                {
                    if (flag == 1 || flag == 2 || flag == 3)
                    {
                        ExportExcel(dt, flag);
                    }
                    else if (flag == 4 || flag == 5 || flag == 6 || flag == 7)
                    {
                        ExportExcel(dt, flag);
                    }
                }
                else
                {
                    MessageBox.Show("没有要导出的数据！");
                }
            }

//方法

#region 查询注册表,判断本机是否安装office2003,2007和wps
        public int ExistsRegedit()
        {
            int ifused = 0;
            RegistryKey rk = Registry.LocalMachine;
            RegistryKey akey = rk.OpenSubKey(@"SOFTWARE\Microsoft\Office\11.0\Excel\InstallRoot\");//查询2003

            RegistryKey akey07 = rk.OpenSubKey(@"SOFTWARE\Microsoft\Office\12.0\Excel\InstallRoot\");//查询2007
            RegistryKey akeytwo = rk.OpenSubKey(@"SOFTWARE\Kingsoft\Office\6.0\common\");//查询wps
            //检查本机是否安装Office2003
            if (akey != null)
            {
                string file03 = akey.GetValue("Path").ToString();
                if (File.Exists(file03 + "Excel.exe"))
                {
                    ifused += 1;
                }
            }

//检查本机是否安装Office2007

            if (akey07 != null)
            {
                string file07 = akey07.GetValue("Path").ToString();
                if (File.Exists(file07 + "Excel.exe"))
                {
                    ifused += 2;
                }
            }

            //检查本机是否安装wps
            if (akeytwo != null)
            {
                string filewps = akeytwo.GetValue("InstallRoot").ToString();
                if (File.Exists(filewps + @"\office6\et.exe"))
                {
                    ifused += 4;
                }
            }
            return ifused;
        }
        #endregion

#region 导出excel
        public void ExportExcel(System.Data.DataTable dt, int flag)
        {
            //-***************获取excel对象***************
            string saveFileName = "";
            bool fileSaved = false;
            SaveFileDialog saveDialog = new SaveFileDialog();
            saveDialog.DefaultExt = "xls";
            if (flag == 2)
            {
                saveDialog.Filter = "Excel文件.xlsx|*.xlsx";
            }
            else if (flag == 4 || flag == 5 || flag == 6 || flag == 7)
            {
                saveDialog.Filter = "wps文件.et|*.et";
            }
            else
            {
                saveDialog.Filter = "Excel文件.xls|*.xls";
            }
            saveDialog.FileName = "导出普查信息表 " + DateTime.Today.ToString("yyyy-MM-dd");
            saveDialog.ShowDialog();
            saveFileName = saveDialog.FileName;
            if (saveFileName.IndexOf(":") < 0) return; //被点了取消
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook workbook = xlApp.Workbooks.Add(true);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
            Microsoft.Office.Interop.Excel.Range range;

            // 列索引，行索引，总列数，总行数
            int colIndex = 0;
            int RowIndex = 0;
            int colCount = dgvRecord.ColumnCount;
            int RowCount = dt.Rows.Count;

            // *****************获取数据*********************
            // 创建缓存数据
            object[,] objData = new object[RowCount + 1, colCount];

            // 获取具体数据
            for (RowIndex = 0; RowIndex < RowCount; RowIndex++)
            {
                for (colIndex = 0; colIndex < colCount; colIndex++)
                {
                    objData[RowIndex, colIndex] = dt.Rows[RowIndex][colIndex];
                }

            }
            //********************* 把数值型转成字符型l*******************
            range = worksheet.get_Range(xlApp.Cells[3, 2], xlApp.Cells[RowCount + 2, 2]);
            range.NumberFormatLocal = "@";
            range = worksheet.get_Range(xlApp.Cells[3, 3], xlApp.Cells[RowCount + 2, 3]);
            range.NumberFormatLocal = "@";
            range = worksheet.get_Range(xlApp.Cells[3, 7], xlApp.Cells[RowCount + 2, 7]);
            range.NumberFormatLocal = "@";
            range = worksheet.get_Range(xlApp.Cells[3, 8], xlApp.Cells[RowCount + 2, 8]);
            range.NumberFormatLocal = "@";

            //********************* 写入Excel*******************
            range = worksheet.get_Range(xlApp.Cells[3, 1], xlApp.Cells[RowCount + 2, colCount - 2]);
            range.Value2 = objData;
            System.Windows.Forms.Application.DoEvents();

            //特殊数字格式
            range = worksheet.get_Range(xlApp.Cells[3, colCount - 5], xlApp.Cells[RowCount + 2, colCount - 5]);
            range.NumberFormat = "yyyy-MM-dd hh:mm:ss";

            //********************* 设置报表表格样式*******************
            //设置为横向打印
            //xlApp.ActiveSheet.PageSetup.Orientation=2;

            //设置报表表格为最适应宽度
            worksheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colCount]).Select();
            worksheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colCount]).Columns.AutoFit();

            //绘制边框
            worksheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[RowCount + 2, colCount]).Borders.LineStyle = 1;
            //设置左边线加粗
            worksheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[RowCount, 1]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
            //设置上边线加粗
            worksheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colCount]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
            //设置右边线加粗
            worksheet.get_Range(xlApp.Cells[1, colCount], xlApp.Cells[RowCount, colCount]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;
            //设置下边线加粗
            worksheet.get_Range(xlApp.Cells[RowCount + 2, 1], xlApp.Cells[RowCount + 2, colCount]).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThick;

            xlApp.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
            range = worksheet.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colCount]);
            //range.Interior.ColorIndex = 1;//背景色
            range.Font.Bold = true;
            range.RowHeight = 20;
            ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, 1]).ColumnWidth = 25;
            ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, 2]).ColumnWidth = 13;
            ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, 3]).ColumnWidth = 15;
            ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, 4]).ColumnWidth = 30;
            ((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, 5]).ColumnWidth = 22;

            //合并单元格
            Microsoft.Office.Interop.Excel.Range excelRange = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 29]);
            excelRange.Merge(excelRange.MergeCells);

            //获取列标题
            for (int i = 0; i < colCount; i++)
            {
                worksheet.Cells[2, i + 1] = dgvRecord.Columns[i].HeaderText;
            }

            //文档标题
            range = worksheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, 1]);
            range.Font.Bold = true;
            range.Font.Size = 20;
            worksheet.Cells[1, 1] = "普查信息表";

//***************************保存**********************

            if (saveFileName != "")
            {
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(saveFileName);
                    fileSaved = true;
                }
                catch (Exception ex)
                {
                    fileSaved = false;
                    MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
                }
            }
            else
            {
                fileSaved = false;
            }
            xlApp.Quit();
            GC.Collect();//强行销毁
            if (fileSaved && File.Exists(saveFileName))
            {
                System.Diagnostics.Process.Start(saveFileName);
            }