DataSet导出到Excel （c#）

来源：互联网发布：淘宝网中老年棉鞋编辑：程序博客网时间：2024/05/21 23:32

private bool outExcel(string fn, DataSet ds)
        {
            const int START_ROW = 2; //开始行
            const int START_COL = 1; //开始列

            int iRows = 0;
            int iColumns = 0;
            System.Data.DataTable dt = new System.Data.DataTable();
            dt = (System.Data.DataTable)ds.Tables["t1"];

            iRows = dt.Rows.Count; //获得记录行数
            iColumns = dt.Columns.Count; //获得记录列数
            if (iRows <= 0) { MessageBox.Show("无记录，无法导出报表！"); return false; }

            try
            {

                Excel.Application excel = new Excel.ApplicationClass();
                Excel.Workbooks Wbs = (Excel.Workbooks)excel.Workbooks;
                Excel.Workbook xBk = (Excel.Workbook)(Wbs.Add(miss));
                Excel.Worksheet xSt = (Excel.Worksheet)xBk.ActiveSheet;
                Excel.Range excelCell = null;
                excel.ScreenUpdating = false; //关屏幕更新
                excel.Visible = false; //禁止显示excel
                excel.DisplayAlerts = false; //禁止警告

　　　　　　　//添加列
                for (int lc = 0; lc <= iColumns - 1; lc++)
                {
                    ((Excel.Range)xSt.Cells[START_ROW - 1, lc + START_COL]).FormulaR1C1 = dt.Columns[lc].ToString();
                    ((Excel.Range)xSt.Cells[START_ROW - 1, lc + START_COL]).Font.Bold = true; //粗体
                    ((Excel.Range)xSt.Cells[START_ROW - 1, lc + START_COL]).Borders.LineStyle = 1; //设置边框
                    ((Excel.Range)xSt.Cells[START_ROW - 1, lc + START_COL]).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; //水平居中
                    ((Excel.Range)xSt.Cells[START_ROW - 1, lc + START_COL]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //垂直居中
                }

                  //添加行记录　　　　　　　　　　
                for (int i = 0; i <= iRows - 1; i++)
                    for (int j = 0; j <= iColumns - 1; j++)
                    {
                        //((Excel.Range)xSt.Cells[i + START_ROW, j + START_COL]).NumberFormat = "#";
                        ((Excel.Range)xSt.Cells[i + START_ROW, j + START_COL]).FormulaR1C1 = dt.Rows[i].ItemArray.GetValue(j);
                        ((Excel.Range)xSt.Cells[i + START_ROW, j + START_COL]).Borders.LineStyle = 1;
                    }
                excelCell = xSt.get_Range(excel.Cells[START_ROW, START_COL], excel.Cells[iRows + START_ROW, START_COL + 2]); //get_Range方法为区域选择
                excelCell.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                excelCell.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

                excelCell = xSt.get_Range(excel.Cells[1, 1], excel.Cells[iRows, iColumns]);
                excelCell.Columns.AutoFit();

                excel.ScreenUpdating = true;
                xBk.SaveAs(fn, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlLocalSessionChanges, miss, miss, miss, miss); //另存为

                KillSpecialExcel(excel); //结束excel进程
                return true;

            }
            catch (Exception exp)
            {
                MessageBox.Show(exp.Message);
                return false;
            }

        }

Kill Special Excel Process#region Kill Special Excel Process
        [DllImport("user32.dll", SetLastError = true)]
        static extern int GetWindowThreadProcessId(IntPtr hWnd, out int lpdwProcessId);


        public void KillSpecialExcel(Excel.Application m_objExcel)
        {
            try
            {
                if (m_objExcel != null)
                {
                    int lpdwProcessId;
                    GetWindowThreadProcessId(new IntPtr(m_objExcel.Hwnd), out lpdwProcessId);

                    System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Delete Excel Process Error:" + ex.Message);
            }
        }
        #endregion

0 0