导出报表的几种方式

C#导出Excel报表

在使用前首先需要添加引用：Microsoft.Office.Interop.Excel，然后在调用页添加如下语句：

using Excel = Microsoft.Office.Interop.Excel;

/**//// <summary>
        /// 导出为Excel格式文件
        /// </summary>
        /// <param name="dt">作为数据源的DataTable</param>
        /// <param name="saveFile">带路径的保存文件名</param>
        /// <param name="title">一个Excel sheet的标题</param>
        private void exportExcel(DataTable dt,string saveFile,string title)
        ...{
            rptExcel = new Microsoft.Office.Interop.Excel.Application();
            if (rptExcel == null)
            ...{
                MessageBox.Show("无法打开EXcel，请检查Excel是否可用或者是否安装好Excel", "系统提示");
                return;
            }
            int rowCount = dt.Rows.Count;//行数
            int columnCount = dt.Columns.Count;//列数
            float percent = 0;//导出进度

            this.Cursor = Cursors.WaitCursor;
            //保存文化环境
            System.Globalization.CultureInfo currentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

            Excel.Workbook workbook = rptExcel.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);
            worksheet.Name = "报表";//一个sheet的名称

            //rptExcel.Visible = true;//打开导出的Excel文件

            worksheet.Cells[1, 1] = title;//表标题

            //填充列标题
            for (int i = 0; i < columnCount; i++)
            ...{
                worksheet.Cells[2, i + 1] = dt.Columns[i].ColumnName;
            }
          
            //创建对象数组存储DataTable的数据，这样的效率比直接将Datateble的数据填充worksheet.Cells[row,col]高
            object[,] objData = new object[rowCount, columnCount];
          
            //填充内容到对象数组
            for (int r = 0; r < rowCount; r++)
            ...{
                for (int col = 0; col < columnCount; col++)
                ...{
                    objData[r, col] = dt.Rows[r][col].ToString();
                }

                percent = ((float)(r+1) * 100) / rowCount;
                this.panelProgress.Visible = true;//显示进度条
                this.lblPercents.Text=percent.ToString("n") + "%";
                this.progressBar1.Value = Convert.ToInt32(percent);

                System.Windows.Forms.Application.DoEvents();
            }
            //将对象数组的值赋给Excel对象
            Excel.Range range = worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[rowCount + 2, columnCount]);
            range.NumberFormat = "@";//设置数字文本格式
            range.Value2 = objData;

            //设置格式
            worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]).MergeCells = true;//合并单元格
            worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]).HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//居中对齐
            worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]).RowHeight = 38;
            worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]).Font.Bold = true;
            worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]).Font.Name = "黑体";
            worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]).Font.Size = 16;
            worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[rowCount + 2, columnCount]).Borders.LineStyle = 1;//设置边框
            worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[rowCount, columnCount]).Columns.AutoFit();//设置单元格宽度为自适应
           
            //恢复文化环境
            System.Threading.Thread.CurrentThread.CurrentCulture = currentCI;
            try
            ...{
                //rptExcel.Save(saveFile); //自动创建一个新的Excel文档保存在“我的文档”里，如果不用SaveFileDialog就可用这种方法
                workbook.Saved=true;
                workbook.SaveCopyAs(saveFile);//以复制的形式保存在已有的文档里
                this.Cursor = Cursors.Default;
                this.panelProgress.Visible = false;//隐藏进度条
                MessageBox.Show("恭喜，数据已经成功导出为Excel文件！", "成功导出");
            }
            catch (Exception ex)
            ...{
                MessageBox.Show("导出文件出错，文件可能正被打开，具体原因：" + ex.Message, "出错信息");
            }
            finally
            ...{
                dt.Dispose();
                rptExcel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(rptExcel);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                GC.Collect();
                KillAllExcel();
            }
        }
        /**//// <summary>
        /// 获得所有的Excel进程
        /// </summary>
        /// <returns>所有的Excel进程</returns>
        private List<Process> GetExcelProcesses()
            ...{
            Process[] processes = Process.GetProcesses();
            List<Process> excelProcesses = new List<Process>();

            for (int i = 0; i < processes.Length; i++)
            ...{
                if (processes[i].ProcessName.ToUpper() == "EXCEL")
                    excelProcesses.Add(processes[i]);
            }

            return excelProcesses;
        }
        private void KillAllExcel()
        ...{
            List<Process> excelProcess = GetExcelProcesses();
            for (int i = 0; i < excelProcess.Count; i++)
            ...{
                excelProcess[i].Kill();
            }
        }