DataTableToExcel 的方法

using System;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Collections;
using System.Data;
using System.Runtime.InteropServices;
using System.Reflection;

namespace BeiJing.ISS.Common
{
    public class Table_ToExcel
    {
        public Excel.Application m_xlApp = null;

        #region 外部接口
        /// <summary>
        /// 将一个DataTable的数据导出多个Excel文件（每一个Excel文件的数据行数由函数控制）

        /// </summary>
        /// <param name="tempDataTable">数据源</param>
        /// <param name="PathFileName">保存excel的路径</param>
        /// <param name="ExcelRows">每一个Excel文件的行数</param>
        /// <param name="ExcelVersion">导出Excel的版本(2003,2007)</param>
        public void u_DataTableToExcel1(DataTable tempDataTable, string filepath, string filename, long ExcelRows, string ExcelVersion)
        {
            if (tempDataTable == null)
            {
                return;
            }
            long rowNum = tempDataTable.Rows.Count;//导出数据的行数  
            int columnNum = tempDataTable.Columns.Count;//导出数据的列数  
            string sFileName = "";
            if (rowNum > ExcelRows)
            {
                long excelRows = ExcelRows;//定义个excel文件显示的行数，最大的行数为65535，不能超过65535                    
                int scount = (int)(rowNum / excelRows);//生成excel文件的个数  
                if (scount * excelRows < rowNum)//当总行数不被excelRows整除时，经过四舍五入可能excel的个数不准  
                {
                    scount = scount + 1;
                }
                for (int sc = 1; sc <= scount; sc++)
                {
                    int init = int.Parse(((sc - 1) * excelRows).ToString());
                    sFileName = filepath + sc.ToString();
                    long start = init;
                    long end = sc * excelRows - 1;
                    if (sc == scount)
                        end = rowNum - 1;
                    u_OutExcel(tempDataTable, start, end, filepath, filename, ExcelVersion);
                }
            }
            else
            {
                u_OutExcel(tempDataTable, 0, rowNum - 1, filepath, filename, ExcelVersion);
            }
            tempDataTable = null;
        }
        /// <summary>
        /// 将一个DataTable的数据导出一个Excel文件:可能包含多个sheet文件，由sheet行数决定 (每一个sheet文件的行数由函数控制)
        /// </summary>
        /// <param name="tempDataTable">数据源</param>
        /// <param name="PathFileName">导出excel的路径</param>
        /// <param name="SheetRows">excel的文件中sheet的行数</param>
        /// <param name="ExcelVersion">导出Excel的版本</param>
        public void u_DataTableToExcel2(DataTable tempDataTable, string Path, string FileName, long SheetRows, string ExcelVersion, bool BudgetStatistics=false)
        {
            if (tempDataTable == null)
            {
                return;
            }
            long rowNum = tempDataTable.Rows.Count;//行数  
            int columnNum = tempDataTable.Columns.Count;//列数  
            Excel.Application m_xlApp = new Excel.Application();
            m_xlApp.DisplayAlerts = false;//不显示更改提示  
            m_xlApp.Visible = false;
            m_xlApp.UserControl = true;
            m_xlApp.EnableLargeOperationAlert = false;
            m_xlApp.EnableLivePreview = false;

            Excel.Workbooks workbooks = m_xlApp.Workbooks;
            Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
            try
            {
                if (rowNum > SheetRows)//单张Sheet表格最大行数  
                {
                    long sheetRows = SheetRows;//定义每页显示的行数,行数必须小于65536  
                    int scount = (int)(rowNum / sheetRows);//导出数据生成的表单数  
                    if (scount * sheetRows < rowNum)//当总行数不被sheetRows整除时，经过四舍五入可能页数不准  
                    {
                        scount = scount + 1;
                    }
                    for (int sc = 1; sc <= scount; sc++)
                    {
                        if (sc > 1)
                        {
                            object missing = System.Reflection.Missing.Value;
                            worksheet = (Excel.Worksheet)workbook.Worksheets.Add(missing, missing, missing, missing);//添加一个sheet  
                        }
                        else
                        {
                            worksheet = (Excel.Worksheet)workbook.Worksheets[sc];//取得sheet1  
                        }
                        string[,] datas = new string[sheetRows + 1, columnNum];

                        for (int i = 0; i < columnNum; i++) //写入字段  
                        {
                            datas[0, i] = tempDataTable.Columns[i].Caption;//表头信息  
                        }
                        Excel.Range range = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]];
                        range.Interior.ColorIndex = 15;//15代表灰色  
                        range.Font.Bold = true;
                        range.Font.Size = 9;
                        int init = int.Parse(((sc - 1) * sheetRows).ToString());
                        int r = 0;
                        int index = 0;
                        int result;
                        if (sheetRows * sc >= rowNum)
                        {
                            result = (int)rowNum;
                        }
                        else
                        {
                            result = int.Parse((sheetRows * sc).ToString());
                        }
                        for (r = init; r < result; r++)
                        {
                            index = index + 1;
                            for (int i = 0; i < columnNum; i++)
                            {
                                object obj = tempDataTable.Rows[r][tempDataTable.Columns[i].ToString()];
                                datas[index, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式  
                            }
                        }
                        Excel.Range fchR = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[index + 1, columnNum]];
                        fchR.Value2 = datas;
                        worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。  
                        m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;//Sheet表最大化  
                        range = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[index + 1, columnNum]];
                        //range.Interior.ColorIndex = 15;//15代表灰色  
                        range.Font.Size = 9;
                        range.RowHeight = 14.25;
                        range.Borders.LineStyle = 1;
                        range.HorizontalAlignment = 1;
                    }
                }
                else
                {
                    string[,] datas = new string[rowNum + 1, columnNum];
                    for (int i = 0; i < columnNum; i++) //写入字段  
                    {
                        datas[0, i] = tempDataTable.Columns[i].Caption;
                    }
                    Excel.Range range = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]];
                    range.Interior.ColorIndex = 15;//15代表灰色  
                    range.Font.Bold = true;
                    range.Font.Size = 9;
                    int r = 0;
                    for (r = 0; r < rowNum; r++)
                    {
                        if (BudgetStatistics && r == rowNum - 1)//预算表统计导出 最后一行
                        {
                            for (int i = 0; i < columnNum; i++)
                            {
                                if (i == 0)
                                {
                                    datas[r + 1, i] = "'总计：";
                                }
                                else
                                {
                                    object obj = tempDataTable.Rows[r][tempDataTable.Columns[i].ToString()];
                                    if (obj==null || obj == DBNull.Value)
                                    {
                                        //obj = "0.00";
                                    }
                                    datas[r + 1, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式  
                                }
                            }
                        }
                        else
                        {
                            for (int i = 0; i < columnNum; i++)
                            {
                                object obj = tempDataTable.Rows[r][tempDataTable.Columns[i].ToString()];
                                datas[r + 1, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式  
                            }
                        }
                    }
                    Excel.Range fchR = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]];
                    fchR.Value2 = datas;
                    worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。  
                    m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;
                    range = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]];
                    //range.Interior.ColorIndex = 15;//15代表灰色  
                    range.Font.Size = 9;
                    range.RowHeight = 14.25;
                    range.Borders.LineStyle = 1;
                    range.HorizontalAlignment = 1;
                }
                workbook.Saved = true;
                switch (ExcelVersion)
                {
                    case "2003":
                        object ob = System.Reflection.Missing.Value;
                        workbook.SaveAs(Path + FileName, Excel.XlFileFormat.xlExcel7, ob, ob, ob, ob, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, ob, ob, ob, ob, ob);
                        workbook.Save();
                        //workbook = null;
                        //worksheet = null;
                        //workbooks = null;
                        //m_xlApp.Quit();
                        //m_xlApp = null;
                        workbook.Close(null, null, null);
                        m_xlApp.Workbooks.Close();
                        m_xlApp.Quit();
                        Marshal.ReleaseComObject((object)m_xlApp);
                        Marshal.ReleaseComObject((object)workbook);
                        Marshal.ReleaseComObject((object)worksheet);
                        break;
                    case "2007":
                        workbook.SaveCopyAs(Path + FileName);
                        workbook.Save();
                        //workbook = null;
                        //worksheet = null;
                        //workbooks = null;
                        //m_xlApp.Quit();
                        //m_xlApp = null;
                        workbook.Close(null, null, null);
                        m_xlApp.Workbooks.Close();
                        m_xlApp.Quit();
                        Marshal.ReleaseComObject((object)m_xlApp);
                        Marshal.ReleaseComObject((object)workbook);
                        Marshal.ReleaseComObject((object)worksheet);
                        break;
                    default: break;
                }
                KillProcess("EXCEL");//杀死excel进程
                tempDataTable = null;
            }
            catch (Exception ex)
            {
                tempDataTable = null;
                throw new Exception(ex.Message.ToString());
            }
            finally
            {
                KillProcess("EXCEL");//杀死excel进程
                tempDataTable = null;
            }
        }

        public void u_DataTableToExcel3(DataTable tempDataTable, string TemplatePath, int StartNum, List<SetExcelContent> Eclist, string tableDesigners, string Path, string FileName, long SheetRows, string ExcelVersion)
        {
            if (tempDataTable == null)
            {
                return;
            }
            object missing = Missing.Value;
            long rowNum = tempDataTable.Rows.Count;//行数  
            int columnNum = tempDataTable.Columns.Count;//列数  
            Excel.Application m_xlApp = new Excel.Application();
            m_xlApp.DisplayAlerts = false;//不显示更改提示  
            m_xlApp.Visible = false;
            m_xlApp.UserControl = true;
            Excel.Workbook workbook = m_xlApp.Workbooks.Open(TemplatePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);//取得第一个工作簿
            Excel.Worksheet sheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);//取得第一个工作簿
            //sheet.Name = "Sheet1";
            if (sheet == null)//工作簿中没有工作表
                return;

            //设置模板中的表头
            foreach (SetExcelContent ec in Eclist)
            {
                sheet.Cells[ec.X, ec.Y] = ec.Content.ToString();
            }

            try
            {
                int cell_r = 0, cell_c = 0;
                for (int i = 1; i <= rowNum; i++)
                {
                    cell_r = i + StartNum;
                    int row_index = i - 1;//DataTable的行是从0开始的
                    for (int j = 0; j < columnNum; j++)
                    {
                        cell_c = j + 1;//Excel表格的列
                        object obj = tempDataTable.Rows[row_index][tempDataTable.Columns[j].ToString()];
                        sheet.Cells[cell_r, cell_c] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式  
                    }
                }

                //添加表底信息
                if (!string.IsNullOrEmpty(tableDesigners))
                {
                    string [] arrDesigner=tableDesigners.Split(new string [] { "~|~" },StringSplitOptions.RemoveEmptyEntries);
                    sheet.Cells[cell_r + 2, 1] = arrDesigner[0];
                    sheet.Cells[cell_r + 2, 2] = arrDesigner[1];
                    sheet.Cells[cell_r + 2, 3] = arrDesigner[2];
                    sheet.Cells[cell_r + 2, 4] = arrDesigner[3];

                    //调整Excel的样式
                    Excel.Range range = sheet.Range[worksheet.Cells[StartNum + 1, 1], sheet.Cells[rowNum + StartNum + 2, columnNum]];//结束的要加上表底
                    range.Borders.LineStyle = 1;//单元格加边框
                }
                else
                {
                    //调整Excel的样式
                    Excel.Range range = sheet.Range[worksheet.Cells[StartNum + 1, 1], sheet.Cells[rowNum + StartNum, columnNum]];
                    range.Borders.LineStyle = 1;//单元格加边框
                }

                workbook.Saved = true;
                switch (ExcelVersion)
                {
                    case "2003":
                        object ob = System.Reflection.Missing.Value;
                        workbook.SaveCopyAs(Path + FileName);
                        workbook.Save();
                        workbook.Close(null, null, null);
                        m_xlApp.Workbooks.Close();
                        m_xlApp.Quit();
                        Marshal.ReleaseComObject((object)m_xlApp);
                        Marshal.ReleaseComObject((object)workbook);
                        Marshal.ReleaseComObject((object)worksheet);
                        break;
                    case "2007":
                        workbook.SaveCopyAs(Path + FileName);
                        workbook.Save();
                        workbook.Close(null, null, null);
                        m_xlApp.Workbooks.Close();
                        m_xlApp.Quit();
                        Marshal.ReleaseComObject((object)m_xlApp);
                        Marshal.ReleaseComObject((object)workbook);
                        Marshal.ReleaseComObject((object)worksheet);
                        break;
                    default: break;
                }
                KillProcess("EXCEL");//杀死excel进程
                tempDataTable = null;
            }
            catch (Exception ex)
            {
                tempDataTable = null;
                throw new Exception(ex.Message.ToString());
            }
            finally
            {
                KillProcess("EXCEL");//杀死excel进程
                tempDataTable = null;
            }

        }

        //派发勘察，生成楼层格式表，楼宇格式表
        public void u_DataTableToExcel33(DataTable tempDataTable, string TemplatePath, int StartNum, string Path, string FileName, long SheetRows, string ExcelVersion)
        {
            if (tempDataTable == null)
            {
                return;
            }
            object missing = Missing.Value;
            long rowNum = tempDataTable.Rows.Count;//行数  
            int columnNum = tempDataTable.Columns.Count;//列数  
            Excel.Application m_xlApp = new Excel.Application();
            m_xlApp.DisplayAlerts = false;//不显示更改提示  
            m_xlApp.Visible = false;
            m_xlApp.UserControl = true;
            Excel.Workbook workbook = m_xlApp.Workbooks.Open(TemplatePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);//取得第一个工作簿
            Excel.Worksheet sheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);//取得第一个工作簿
            //sheet.Name = "Sheet1";
            if (sheet == null)//工作簿中没有工作表
                return;

            try
            {
                int cell_r = 0, cell_c = 0;
                for (int i = 1; i <= rowNum; i++)
                {
                    cell_r = i + StartNum;
                    int row_index = i - 1;//DataTable的行是从0开始的
                    for (int j = 0; j < columnNum; j++)
                    {
                        cell_c = j + 1;//Excel表格的列
                        object obj = tempDataTable.Rows[row_index][tempDataTable.Columns[j].ToString()];
                        if (cell_c == 1)
                        {
                            sheet.Cells[cell_r, cell_c] = i;//第一列为序号
                        }
                        else
                        {
                            sheet.Cells[cell_r, cell_c] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式  
                        }
                    }
                }

                //调整Excel的样式
                Excel.Range range = sheet.Range[worksheet.Cells[StartNum + 1, 1], sheet.Cells[rowNum + StartNum, columnNum]];
                range.Borders.LineStyle = 1;//单元格加边框
                
                workbook.Saved = true;
                switch (ExcelVersion)
                {
                    case "2003":
                        object ob = System.Reflection.Missing.Value;
                        workbook.SaveCopyAs(Path + FileName);
                        workbook.Save();
                        workbook.Close(null, null, null);
                        m_xlApp.Workbooks.Close();
                        m_xlApp.Quit();
                        Marshal.ReleaseComObject((object)m_xlApp);
                        Marshal.ReleaseComObject((object)workbook);
                        Marshal.ReleaseComObject((object)worksheet);
                        break;
                    case "2007":
                        workbook.SaveCopyAs(Path + FileName);
                        workbook.Save();
                        workbook.Close(null, null, null);
                        m_xlApp.Workbooks.Close();
                        m_xlApp.Quit();
                        Marshal.ReleaseComObject((object)m_xlApp);
                        Marshal.ReleaseComObject((object)workbook);
                        Marshal.ReleaseComObject((object)worksheet);
                        break;
                    default: break;
                }
                KillProcess("EXCEL");//杀死excel进程
                tempDataTable = null;
            }
            catch (Exception ex)
            {
                tempDataTable = null;
                throw new Exception(ex.Message.ToString());
            }
            finally
            {
                KillProcess("EXCEL");//杀死excel进程
                tempDataTable = null;
            }

        }

        /// <summary>
        /// 物料表导出专用
        /// </summary>
        public void u_DataTableToExcel4(DataTable tempDataTable, string TemplatePath, int StartNum, List<SetExcelContent> Eclist, string tableDesigners, string Path, string FileName, long SheetRows, string ExcelVersion)
        {
            if (tempDataTable == null)
            {
                return;
            }
            object missing = Missing.Value;
            long rowNum = tempDataTable.Rows.Count;//行数  
            int columnNum = tempDataTable.Columns.Count;//列数  
            Excel.Application m_xlApp = new Excel.Application();
            m_xlApp.DisplayAlerts = false;//不显示更改提示  
            m_xlApp.Visible = false;
            m_xlApp.UserControl = true;
            Excel.Workbook workbook = m_xlApp.Workbooks.Open(TemplatePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);//取得第一个工作簿
            Excel.Worksheet sheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);//取得第一个工作簿
            sheet.Name = "Sheet1";
            if (sheet == null)//工作簿中没有工作表
                return;

            //设置模板中的表头
            foreach (SetExcelContent ec in Eclist)
            {
                sheet.Cells[ec.X, ec.Y] = ec.Content.ToString();
            }
            int count = 0;
            try
            {
                string preDeviceSort = "", curDeviceSort = "";

                int cell_r = 0, cell_c = 0;
                for (int i = 1; i <= rowNum; i++)
                {
                    cell_r = i + StartNum;
                    int row_index = i - 1;//DataTable的行是从0开始的
                    if (!string.IsNullOrEmpty(preDeviceSort) && preDeviceSort.Equals(curDeviceSort))//同一个分类
                    {
                        cell_r = cell_r + count;
                    }
                    for (int j = 0; j < columnNum; j++)
                    {
                        cell_c = j + 1;//Excel表格的列
                        object obj = tempDataTable.Rows[row_index][tempDataTable.Columns[j].ToString()];
                        string cellValue = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式  

                        if (tempDataTable.Columns[j].ColumnName.Equals("DeviceSort"))//分类
                        {
                            curDeviceSort = cellValue;
                            if (preDeviceSort != curDeviceSort)//不同分类
                            {
                                sheet.Cells[cell_r, 1] = curDeviceSort;

                                sheet.Cells.get_Range("A" + cell_r, "F" + cell_r).MergeCells = true;//合并单元格
                                sheet.Cells.get_Range("A" + cell_r, "F" + cell_r).Font.Size = "12";//设置字体
                                sheet.Cells.get_Range("A" + cell_r, "F" + cell_r).Interior.Color = System.Drawing.Color.LightGray;//设置单元格背景色

                                cell_r = cell_r + 1;//另起一行

                                count++;//累计不同分类数
                            }
                            else
                            {
                                cell_c = cell_c + 1;//单元格数加1下面才能减
                            }
                            continue;
                        }
                        cell_c = cell_c - 1;
                        sheet.Cells[cell_r, cell_c] = cellValue;//数据的第一行
                    }
                    preDeviceSort = curDeviceSort;
                }

                //添加表底信息
                if (!string.IsNullOrEmpty(tableDesigners))
                {
                    string[] arrDesigner = tableDesigners.Split(new string[] { "~|~" }, StringSplitOptions.RemoveEmptyEntries);
                    sheet.Cells[cell_r + 2, 1] = arrDesigner[0];
                    sheet.Cells[cell_r + 2, 2] = arrDesigner[1];
                    sheet.Cells[cell_r + 2, 3] = arrDesigner[2];
                    sheet.Cells[cell_r + 2, 4] = arrDesigner[3];

                    //调整Excel的样式
                    Excel.Range range = sheet.Range[worksheet.Cells[StartNum + 1, 1], sheet.Cells[cell_r + StartNum + 2, columnNum-1]];//结束的要加上表底
                    range.Borders.LineStyle = 1;//单元格加边框
                }
                else
                {
                    //调整Excel的样式
                    Excel.Range range = sheet.Range[worksheet.Cells[StartNum + 1, 1], sheet.Cells[cell_r + StartNum, columnNum-1]];
                    range.Borders.LineStyle = 1;//单元格加边框
                }

                workbook.Saved = true;
                switch (ExcelVersion)
                {
                    case "2003":
                        object ob = System.Reflection.Missing.Value;
                        workbook.SaveCopyAs(Path + FileName);
                        workbook.Save();
                        workbook.Close(null, null, null);
                        m_xlApp.Workbooks.Close();
                        m_xlApp.Quit();
                        Marshal.ReleaseComObject((object)m_xlApp);
                        Marshal.ReleaseComObject((object)workbook);
                        Marshal.ReleaseComObject((object)worksheet);
                        break;
                    case "2007":
                        workbook.SaveCopyAs(Path + FileName);
                        workbook.Save();
                        workbook.Close(null, null, null);
                        m_xlApp.Workbooks.Close();
                        m_xlApp.Quit();
                        Marshal.ReleaseComObject((object)m_xlApp);
                        Marshal.ReleaseComObject((object)workbook);
                        Marshal.ReleaseComObject((object)worksheet);
                        break;
                    default: break;
                }
                KillProcess("EXCEL");//杀死excel进程
                tempDataTable = null;
            }
            catch (Exception ex)
            {
                tempDataTable = null;
                throw new Exception(ex.Message.ToString());
            }
            finally
            {
                KillProcess("EXCEL");//杀死excel进程
                tempDataTable = null;
            }

        }

        /// <summary>
        /// 预算批量导出
        /// </summary>
        public void u_DataTableToExcel5(List<BudgetExport> listBudget, string TemplatePath, string Path, string FileName, long SheetRows, string ExcelVersion)
        {
            object missing = Missing.Value;
            Excel.Application m_xlApp = new Excel.Application();
            m_xlApp.DisplayAlerts = false;//不显示更改提示  
            m_xlApp.Visible = false;
            m_xlApp.UserControl = true;
            Excel.Workbook workbook = m_xlApp.Workbooks.Open(TemplatePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
            Excel.Worksheet worksheet = null;

            int bookCount = workbook.Sheets.Count;

            DataTable tempDataTable = null;
            int StartNum = 0;
            List<SetExcelContent> Eclist=null;
            string tableDesigners = string.Empty;
            string SheetName = string.Empty;
            for (int z = 0; z < listBudget.Count; z++)
            { 
                BudgetExport be=listBudget[z];
                tempDataTable = be.tempDataTable;
                StartNum = be.StartNum;
                Eclist = be.Eclist;
                tableDesigners = be.tableDesigners;
                SheetName = be.SheetName;

                if (tempDataTable == null)
                {
                    continue;
                }
                long rowNum = tempDataTable.Rows.Count;//行数  
                int columnNum = tempDataTable.Columns.Count;//列数  
                
                worksheet = (Excel.Worksheet)workbook.Sheets.get_Item(z+1);//取得第一个工作簿
                Excel.Worksheet sheet = (Excel.Worksheet)workbook.Sheets.get_Item(z+1);//取得第一个工作簿
                sheet.Name = SheetName;
                if (sheet == null)//工作簿中没有工作表
                    return;

                //设置模板中的表头
                if (Eclist != null && Eclist.Count > 0)//表头不为空
                {
                    foreach (SetExcelContent ec in Eclist)
                    {
                        sheet.Cells[ec.X, ec.Y] = ec.Content.ToString();
                    }
                }

                try
                {
                    int cell_r = 0, cell_c = 0;
                    for (int i = 1; i <= rowNum; i++)
                    {
                        cell_r = i + StartNum;
                        int row_index = i - 1;//DataTable的行是从0开始的
                        for (int j = 0; j < columnNum; j++)
                        {
                            cell_c = j + 1;//Excel表格的列
                            object obj = tempDataTable.Rows[row_index][tempDataTable.Columns[j].ToString()];
                            sheet.Cells[cell_r, cell_c] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式  
                        }
                    }

                    //添加表底信息
                    if (!string.IsNullOrEmpty(tableDesigners))
                    {
                        string[] arrDesigner = tableDesigners.Split(new string[] { "~|~" }, StringSplitOptions.RemoveEmptyEntries);
                        sheet.Cells[cell_r + 2, 1] = arrDesigner[0];
                        sheet.Cells[cell_r + 2, 2] = arrDesigner[1];
                        sheet.Cells[cell_r + 2, 3] = arrDesigner[2];
                        sheet.Cells[cell_r + 2, 4] = arrDesigner[3];

                        //调整Excel的样式
                        Excel.Range range = sheet.Range[worksheet.Cells[StartNum + 1, 1], sheet.Cells[rowNum + StartNum + 2, columnNum]];//结束的要加上表底
                        range.Borders.LineStyle = 1;//单元格加边框
                    }
                    else
                    {
                        //调整Excel的样式
                        Excel.Range range = sheet.Range[worksheet.Cells[StartNum + 1, 1], sheet.Cells[rowNum + StartNum, columnNum]];
                        range.Borders.LineStyle = 1;//单元格加边框
                    }
                }
                catch (Exception ex)
                {
                    tempDataTable = null;
                    throw new Exception(ex.Message.ToString());
                }
                finally
                {
                    tempDataTable = null;
                }
            }

            workbook.Saved = true;
            switch (ExcelVersion)
            {
                case "2003":
                    object ob = System.Reflection.Missing.Value;
                    workbook.SaveCopyAs(Path + FileName);
                    workbook.Save();
                    workbook.Close(null, null, null);
                    m_xlApp.Workbooks.Close();
                    m_xlApp.Quit();
                    Marshal.ReleaseComObject((object)m_xlApp);
                    Marshal.ReleaseComObject((object)workbook);
                    if (worksheet != null)
                    {
                        Marshal.ReleaseComObject((object)worksheet);
                    }
                    break;
                case "2007":
                    workbook.SaveCopyAs(Path + FileName);
                    workbook.Save();
                    workbook.Close(null, null, null);
                    m_xlApp.Workbooks.Close();
                    m_xlApp.Quit();
                    Marshal.ReleaseComObject((object)m_xlApp);
                    Marshal.ReleaseComObject((object)workbook);
                    if (worksheet != null)
                    {
                        Marshal.ReleaseComObject((object)worksheet);
                    }
                    break;
                default: break;
            }
            KillProcess("EXCEL");//杀死excel进程
            tempDataTable = null;
        }

        /// <summary>
        /// 按项目-信源统计
        /// </summary>
        public void u_DataTableToExcel6(DataTable tempDataTable, string Path, string FileName, long SheetRows, string ExcelVersion, bool Statistics = false)
        {
            if (tempDataTable == null)
            {
                return;
            }
            object missing = Missing.Value;
            long rowNum = tempDataTable.Rows.Count;//行数  
            int columnNum = tempDataTable.Columns.Count;//列数  
            Excel.Application m_xlApp = new Excel.Application();
            m_xlApp.DisplayAlerts = false;//不显示更改提示  
            m_xlApp.Visible = false;
            m_xlApp.UserControl = true;

            Excel.Workbooks workbooks = m_xlApp.Workbooks;
            Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel.Worksheet sheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
            sheet.Name = "Sheet1";
            if (sheet == null)//工作簿中没有工作表
                return;

            try
            {
                string SerialNum = "", StationNum = "", StationName = "", lastSerialNum = "", lastStationNum = "", lastStationName = "";

                for (int i = 0; i < columnNum; i++) //写入字段  
                {
                    sheet.Cells[1, i + 1] = tempDataTable.Columns[i].Caption;
                }
                Excel.Range range = sheet.Range[sheet.Cells[1, 1], sheet.Cells[1, columnNum]];
                range.Interior.ColorIndex = 15;//15代表灰色  
                range.Font.Bold = true;
                range.Font.Size = 9;

                int cell_r = 0, cell_c = 0;
                int sameCount = 0;
                bool firstMergeFlag = true;
                for (int i = 1; i <= rowNum; i++)
                {
                    cell_r = i + 1;//表头算1列
                    int row_index = i - 1;//DataTable的行是从0开始的
                    for (int j = 0; j < columnNum; j++)
                    {
                        cell_c = j + 1;//Excel表格的列
                        object obj = tempDataTable.Rows[row_index][tempDataTable.Columns[j].ToString()];
                        sheet.Cells[cell_r, cell_c] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式  

                        if (j < 3)
                        {
                            if (tempDataTable.Columns[j].Caption == "序号")
                            {
                                SerialNum = obj.ToString().Trim();
                            }
                            else if (tempDataTable.Columns[j].Caption == "站号")
                            {
                                StationNum = obj.ToString().Trim();
                            }
                            else if (tempDataTable.Columns[j].Caption == "站名")
                            {
                                StationName = obj.ToString().Trim();
                            }
                        }
                    }
                    if (SerialNum != lastSerialNum && StationNum != lastStationNum && StationName != lastStationName)//不一样时才赋值
                    {
                        if (cell_r > 2)
                        {
                            int startRowIndex = 0;
                            int endRowIndex = 0;
                            if (firstMergeFlag)//第一次合并
                            {
                                startRowIndex = 2;
                                endRowIndex = startRowIndex + sameCount;
                            }
                            else
                            {
                                startRowIndex = cell_r - sameCount-1;
                                endRowIndex = cell_r-1;
                            }
                            //合并单元格
                            //sheet.Cells.get_Range("A" + startRowIndex, "A" + endRowIndex).MergeCells = true;
                            //sheet.Cells.get_Range("B" + startRowIndex, "B" + endRowIndex).MergeCells = true;
                            //sheet.Cells.get_Range("C" + startRowIndex, "C" + endRowIndex).MergeCells = true;

                            range = sheet.Cells.get_Range("A" + startRowIndex, "A" + endRowIndex);//get_Range("起始单元格，如A1","结束单元格，如G10"); 即表示一块区域
                            range.Clear();
                            range.Font.Size = 12;//设置字体大小
                            range.Font.Bold = true;//设置字体加粗
                            range.Merge(0);//单元格合并
                            range.Value = "'" + lastSerialNum;//设置单元格内容
                            range.Borders.LineStyle = 1;//加边框
                            range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                            range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                            range = null;
                            range = sheet.Cells.get_Range("B" + startRowIndex, "B" + endRowIndex);
                            range.Clear();
                            range.Font.Size = 12;//设置字体大小
                            range.Font.Bold = true;//设置字体加粗
                            range.Merge(0);//单元格合并
                            range.Value = "'" + lastStationNum;//设置单元格内容
                            range.Borders.LineStyle = 1;//加边框
                            range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                            range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                            range = null;
                            range = sheet.Cells.get_Range("C" + startRowIndex, "C" + endRowIndex);
                            range.Clear();
                            range.Font.Size = 12;//设置字体大小
                            range.Font.Bold = true;//设置字体加粗
                            range.Merge(0);//单元格合并
                            range.Value = "'" + lastStationName;//设置单元格内容
                            range.Borders.LineStyle = 1;//加边框
                            range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                            range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                            range = null;

                            sameCount = 0;
                            firstMergeFlag = false;
                        }

                        lastSerialNum = SerialNum;
                        lastStationNum = StationNum;
                        lastStationName = StationName;
                    }
                    else
                    {
                        if (firstMergeFlag && string.IsNullOrEmpty(lastSerialNum) && string.IsNullOrEmpty(lastStationNum) && string.IsNullOrEmpty(lastStationName))
                        {
                            lastSerialNum = SerialNum;
                            lastStationNum = StationNum;
                            lastStationName = StationName;
                        }
                        if (cell_r > 2 && SerialNum == lastSerialNum && StationNum == lastStationNum && StationName == lastStationName)
                        {
                            sameCount++;//序号，站号，站名相同的个数
                        }
                        else
                        {
                            //处理空值合并
                            if (cell_r > 2)
                            {
                                int startRowIndex = 0;
                                int endRowIndex = 0;
                                if (firstMergeFlag)//第一次合并
                                {
                                    startRowIndex = 2;
                                    endRowIndex = startRowIndex + sameCount;
                                }
                                else
                                {
                                    startRowIndex = cell_r - sameCount - 1;
                                    endRowIndex = cell_r - 1;
                                }
                                //合并单元格
                                //sheet.Cells.get_Range("A" + startRowIndex, "A" + endRowIndex).MergeCells = true;
                                //sheet.Cells.get_Range("B" + startRowIndex, "B" + endRowIndex).MergeCells = true;
                                //sheet.Cells.get_Range("C" + startRowIndex, "C" + endRowIndex).MergeCells = true;

                                range = sheet.Cells.get_Range("A" + startRowIndex, "A" + endRowIndex);//get_Range("起始单元格，如A1","结束单元格，如G10"); 即表示一块区域
                                range.Clear();
                                range.Font.Size = 12;//设置字体大小
                                range.Font.Bold = true;//设置字体加粗
                                range.Merge(0);//单元格合并
                                range.Value = "'" + lastSerialNum;//设置单元格内容
                                range.Borders.LineStyle = 1;//加边框
                                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                                range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                                range = null;
                                range = sheet.Cells.get_Range("B" + startRowIndex, "B" + endRowIndex);
                                range.Clear();
                                range.Font.Size = 12;//设置字体大小
                                range.Font.Bold = true;//设置字体加粗
                                range.Merge(0);//单元格合并
                                range.Value = "'" + lastStationNum;//设置单元格内容
                                range.Borders.LineStyle = 1;//加边框
                                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                                range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                                range = null;
                                range = sheet.Cells.get_Range("C" + startRowIndex, "C" + endRowIndex);
                                range.Clear();
                                range.Font.Size = 12;//设置字体大小
                                range.Font.Bold = true;//设置字体加粗
                                range.Merge(0);//单元格合并
                                range.Value = "'" + lastStationName;//设置单元格内容
                                range.Borders.LineStyle = 1;//加边框
                                range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                                range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                                range = null;

                                sameCount = 0;
                                firstMergeFlag = false;
                            }

                            lastSerialNum = SerialNum;
                            lastStationNum = StationNum;
                            lastStationName = StationName;
                        }
                    }

                    if (cell_r == (rowNum + 1) && sameCount > 0)//最后一行，还有未合并的
                    {
                        int startRowIndex = cell_r - sameCount;
                        int endRowIndex = cell_r;

                        //合并单元格
                        //sheet.Cells.get_Range("A" + startRowIndex, "A" + endRowIndex).MergeCells = true;
                        //sheet.Cells.get_Range("B" + startRowIndex, "B" + endRowIndex).MergeCells = true;
                        //sheet.Cells.get_Range("C" + startRowIndex, "C" + endRowIndex).MergeCells = true;

                        range = sheet.get_Range("A" + startRowIndex, "A" + endRowIndex);//get_Range("起始单元格，如A1","结束单元格，如G10"); 即表示一块区域
                        range.Clear();
                        range.Font.Size = 12;//设置字体大小
                        range.Font.Bold = true;//设置字体加粗
                        range.Merge(0);//单元格合并
                        range.Value = "'" + lastSerialNum;//设置单元格内容
                        range.Borders.LineStyle = 1;//加边框
                        range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                        range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                        range = null;
                        range = sheet.get_Range("B" + startRowIndex, "B" + endRowIndex);
                        range.Clear();
                        range.Font.Size = 12;//设置字体大小
                        range.Font.Bold = true;//设置字体加粗
                        range.Merge(0);//单元格合并
                        range.Value = "'" + lastStationNum;//设置单元格内容
                        range.Borders.LineStyle = 1;//加边框
                        range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                        range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                        range = null;
                        range = sheet.get_Range("C" + startRowIndex, "C" + endRowIndex);
                        range.Clear();
                        range.Font.Size = 12;//设置字体大小
                        range.Font.Bold = true;//设置字体加粗
                        range.Merge(0);//单元格合并
                        range.Value = "'" + lastStationName;//设置单元格内容
                        range.Borders.LineStyle = 1;//加边框
                        range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                        range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
                        range = null;

                        sameCount = 0;
                    }
                    if (Statistics && cell_r == (rowNum + 1))//最后一行是汇总信息合并单元格
                    {
                        sheet.Cells.get_Range("A" + cell_r, "F" + cell_r).MergeCells = true;
                    }
                }

                //调整Excel的样式
                range = sheet.Range[sheet.Cells[1, 1], sheet.Cells[rowNum + 1, columnNum]];
                range.Borders.LineStyle = 1;//单元格加边框

                sheet.Columns.EntireColumn.AutoFit();//列宽自适应。  

                workbook.Saved = true;
                switch (ExcelVersion)
                {
                    case "2003":
                        object ob = System.Reflection.Missing.Value;
                        workbook.SaveCopyAs(Path + FileName);
                        workbook.Save();
                        workbook.Close(null, null, null);
                        m_xlApp.Workbooks.Close();
                        m_xlApp.Quit();
                        Marshal.ReleaseComObject((object)m_xlApp);
                        Marshal.ReleaseComObject((object)workbook);
                        Marshal.ReleaseComObject((object)sheet);
                        break;
                    case "2007":
                        workbook.SaveCopyAs(Path + FileName);
                        workbook.Save();
                        workbook.Close(null, null, null);
                        m_xlApp.Workbooks.Close();
                        m_xlApp.Quit();
                        Marshal.ReleaseComObject((object)m_xlApp);
                        Marshal.ReleaseComObject((object)workbook);
                        Marshal.ReleaseComObject((object)sheet);
                        break;
                    default: break;
                }
                KillProcess("EXCEL");//杀死excel进程
                tempDataTable = null;
            }
            catch (Exception ex)
            {
                tempDataTable = null;
                throw new Exception(ex.Message.ToString());
            }
            finally
            {
                KillProcess("EXCEL");//杀死excel进程
                tempDataTable = null;
            }
        }

        //调用
        //int excelCount = sheet.UsedRange.CurrentRegion.Rows.Count;//获得记录的行数
        //MergeCell(ref sheet, 2, excelCount, "A");//合并单元格
        //MergeCell(ref sheet, 2, excelCount, "B");//合并单元格
        //MergeCell(ref sheet, 2, excelCount, "C");//合并单元格

        /// <summary>
        /// 合并指定EXCEL的单元格
        /// </summary>
        /// <param name="mySheet">指定的EXCEL工作表</param>
        /// <param name="startLine">起始行</param>
        /// <param name="recCount">总行数</param>
        /// <param name="col">要合并的列</param>
        private void MergeCell(ref Microsoft.Office.Interop.Excel.Worksheet mySheet, int startLine, int recCount, string col)
        {
            //获得起始行合并列单元格的填充内容
            string qy1 = mySheet.get_Range(col + startLine.ToString(), col + startLine.ToString()).Text.ToString();

            Microsoft.Office.Interop.Excel.Range rg1;
            string strtemp = "";
            bool endCycle = false;

            //从起始行到终止行做循环
            for (int i = startLine; i <= recCount + startLine - 1 && !endCycle; )
            {
                for (int j = i + 1; j <= recCount + startLine - 1; j++)
                {
                    rg1 = mySheet.get_Range(col + j.ToString(), col + j.ToString());//获得下一行的填充内容
                    strtemp = rg1.Text.ToString().Trim();
                    //最后一行时，标记循环结束
                    if (j == recCount + startLine - 1)
                        endCycle = true;
                    if (strtemp.Trim() == qy1.Trim())//内容等于初始内容
                    {
                        rg1 = mySheet.get_Range(col + i.ToString(), col + j.ToString());//选取上条合并位置和当前行的合并区域
                        rg1.ClearContents();//清空要合并的区域
                        rg1.MergeCells = true;
                        if (col == "A")
                            mySheet.Cells[i, 1] = qy1;
                        else if (col == "B")
                            mySheet.Cells[i, 2] = qy1;
                        else if (col == "C")
                            mySheet.Cells[i, 3] = qy1;
                    }
                    else//内容不等于初始内容
                    {
                        i = j;//i获取新值
                        qy1 = mySheet.get_Range(col + j.ToString(), col + j.ToString()).Text.ToString();
                        break;
                    }
                }
            }
        }

        //室分类需求网建反馈情况统计
        public void u_DataTableToExcel7(DataTable tempDataTable, string TemplatePath, int percent, string Path, string FileName, long SheetRows, string ExcelVersion)
        {
            if (tempDataTable == null)
            {
                return;
            }
            object missing = Missing.Value;
            long rowNum = tempDataTable.Rows.Count;//行数  
            int columnNum = tempDataTable.Columns.Count;//列数  
            Excel.Application m_xlApp = new Excel.Application();
            m_xlApp.DisplayAlerts = false;//不显示更改提示  
            m_xlApp.Visible = false;
            m_xlApp.UserControl = true;
            Excel.Workbook workbook = m_xlApp.Workbooks.Open(TemplatePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);//取得第一个工作簿
            if (worksheet == null)//工作簿中没有工作表
                return;

            try
            {
                worksheet.Cells[2, 4] = "非0前" + percent.ToString() + "%";

                for (int i = 0; i < rowNum; i++)
                {
                    for (int j = 1; j < columnNum; j++)
                    {
                        object obj = tempDataTable.Rows[i][tempDataTable.Columns[j].ToString()];
                        worksheet.Cells[i + 3, j + 1] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式  
                    }
                }

                workbook.Saved = true;
                switch (ExcelVersion)
                {
                    case "2003":
                        object ob = System.Reflection.Missing.Value;
                        workbook.SaveCopyAs(Path + FileName);
                        workbook.Save();
                        workbook.Close(null, null, null);
                        m_xlApp.Workbooks.Close();
                        m_xlApp.Quit();
                        Marshal.ReleaseComObject((object)m_xlApp);
                        Marshal.ReleaseComObject((object)workbook);
                        Marshal.ReleaseComObject((object)worksheet);
                        break;
                    case "2007":
                        workbook.SaveCopyAs(Path + FileName);
                        workbook.Save();
                        workbook.Close(null, null, null);
                        m_xlApp.Workbooks.Close();
                        m_xlApp.Quit();
                        Marshal.ReleaseComObject((object)m_xlApp);
                        Marshal.ReleaseComObject((object)workbook);
                        Marshal.ReleaseComObject((object)worksheet);
                        break;
                    default: break;
                }
                KillProcess("EXCEL");//杀死excel进程
                tempDataTable = null;
            }
            catch (Exception ex)
            {
                tempDataTable = null;
                throw new Exception(ex.Message.ToString());
            }
            finally
            {
                KillProcess("EXCEL");//杀死excel进程
                tempDataTable = null;
            }

        }

        /// <summary>
        /// 泰和 楼宇格式表生成
        /// </summary>
        public void u_DataTableToExcel8(DataTable dt, string TemplatePath, string Path, string FileName, long SheetRows, string ExcelVersion)
        {
            object missing = Missing.Value;
            Excel.Application m_xlApp = new Excel.Application();
            m_xlApp.DisplayAlerts = false;//不显示更改提示  
            m_xlApp.Visible = false;
            m_xlApp.UserControl = true;
            Excel.Workbook workbook = m_xlApp.Workbooks.Open(TemplatePath, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
            Excel.Worksheet worksheet = null;

            int bookCount = workbook.Sheets.Count;

            int StartNum = 3;//从第三行开始 第一行、第二行为列填写要求说明
            string SheetName = "楼宇格式表";
            long rowNum = dt.Rows.Count;//行数  
            int columnNum = dt.Columns.Count;//列数  

            Excel.Worksheet sheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);//取得第一个工作簿
            sheet.Name = SheetName;
            if (sheet == null)//工作簿中没有工作表
                return;

            for (int z = 0; z < dt.Rows.Count; z++)
            {
                try
                {
                    sheet.Cells[z + StartNum, 1] = z + 1;//第一列 序号列
                    sheet.Cells[z + StartNum, 2] = dt.Rows[z][0] == null ? "" : "'" + dt.Rows[z][0].ToString().Trim();//楼宇名称列    在obj.ToString()前加单引号是为了防止自动转化格式  
                    sheet.Cells[z + StartNum, 6] = dt.Rows[z][1] == null ? "" : "'" + dt.Rows[z][1].ToString().Trim();//所属分公司列
                    sheet.Cells[z + StartNum, 10] = dt.Rows[z][2] == null ? "" : "'" + dt.Rows[z][2].ToString().Trim();//经度
                    sheet.Cells[z + StartNum, 11] = dt.Rows[z][3] == null ? "" : "'" + dt.Rows[z][3].ToString().Trim();//纬度
                    
                }
                catch (Exception ex)
                {
                    dt = null;
                    throw new Exception(ex.Message.ToString());
                }
                finally
                {
                    
                }
            }

            workbook.Saved = true;
            switch (ExcelVersion)
            {
                case "2003":
                    object ob = System.Reflection.Missing.Value;
                    workbook.SaveCopyAs(Path + FileName);
                    workbook.Save();
                    workbook.Close(null, null, null);
                    m_xlApp.Workbooks.Close();
                    m_xlApp.Quit();
                    Marshal.ReleaseComObject((object)m_xlApp);
                    Marshal.ReleaseComObject((object)workbook);
                    if (worksheet != null)
                    {
                        Marshal.ReleaseComObject((object)worksheet);
                    }
                    break;
                case "2007":
                    workbook.SaveCopyAs(Path + FileName);
                    workbook.Save();
                    workbook.Close(null, null, null);
                    m_xlApp.Workbooks.Close();
                    m_xlApp.Quit();
                    Marshal.ReleaseComObject((object)m_xlApp);
                    Marshal.ReleaseComObject((object)workbook);
                    if (worksheet != null)
                    {
                        Marshal.ReleaseComObject((object)worksheet);
                    }
                    break;
                default: break;
            }
            KillProcess("EXCEL");//杀死excel进程
            dt = null;
        }

        #endregion
        #region 内部接口
        //作用将dt的（startindex到endindex的数据导出到filename）---用于将海量数据导出到多个excel文件
        private void u_OutExcel(DataTable dt, long startindex, long endindex, string filepath, string filename, string ExcelVersion)
        {
            long columnNum = dt.Columns.Count;
            long excelRows = endindex - startindex - 1;
            Excel.Application m_xlApp = new Excel.Application();
            m_xlApp.DisplayAlerts = false;//不显示更改提示  
            m_xlApp.Visible = false;
            Excel.Workbooks workbooks = m_xlApp.Workbooks;
            Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 
            try
            {
                string[,] datas = new string[excelRows + 1, columnNum];
                for (int i = 0; i < columnNum; i++) //写入表头字段  
                {
                    string sTitle = dt.Columns[i].ColumnName;
                    datas[0, i] = sTitle;//表头信息  
                }
                Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]);
                range.Interior.ColorIndex = 15;//15代表灰色  
                range.Font.Bold = true;
                range.Font.Size = 9;
                int r = 0;
                int row = 0;
                for (r = Convert.ToInt32(startindex); r < endindex - 1; r++)
                {
                    row++;
                    for (int i = 0; i < columnNum; i++)
                    {
                        string sname = dt.Columns[i].ToString().Trim();
                        object obj = dt.Rows[r][sname];
                        datas[row, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式  
                    }
                }

                Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[excelRows + 1, columnNum]);
                fchR.Value2 = datas;
                worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。  
                m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;
                range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[excelRows + 1, columnNum]);
                //range.Interior.ColorIndex = 15;//15代表灰色  
                range.Font.Size = 9;
                range.RowHeight = 14.25;
                range.Borders.LineStyle = 1;//1边框为实线 0为excel样式
                range.HorizontalAlignment = 1;
                workbook.Saved = true;
                switch (ExcelVersion)
                {
                    case "2003":
                        object ob = System.Reflection.Missing.Value;
                        workbook.SaveAs(filepath + filename, Excel.XlFileFormat.xlExcel7, ob, ob, ob, ob, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, ob, ob, ob, ob, ob);
                        break;
                    case "2007":
                        workbook.SaveCopyAs(filepath + filename);
                        break;
                    default: break;
                }
                KillProcess("EXCEL");//杀死excel进程
            }
            catch (Exception ex)
            {
                KillProcess("EXCEL");
                throw new Exception(ex.Message.ToString());
            }
            finally
            {
                workbook.Close(null, null, null);
                m_xlApp.Workbooks.Close();
                m_xlApp.Quit();
                Marshal.ReleaseComObject((object)m_xlApp);
                Marshal.ReleaseComObject((object)workbook);
                Marshal.ReleaseComObject((object)worksheet);
                KillProcess("EXCEL");
            }

        }
        //关闭进程
        private void KillProcess(string processName)
        {
            //System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)

            System.Diagnostics.Process myproc = new System.Diagnostics.Process();
            try
            {
                foreach (System.Diagnostics.Process thisproc in System.Diagnostics.Process.GetProcessesByName(processName))
                {
                    thisproc.Kill();
                    GC.Collect();
                }
            }
            catch
            {

            }
        }
        #endregion
    }

    public class SetExcelContent
    {
        public int X{set;get;}
        public int Y{set;get;}
        public string Content{set;get;}

        public SetExcelContent()
        {
        }

        public SetExcelContent(int _x,int _y,string _content)
        {
            this.X=_x;
            this.Y=_y;
            this.Content=_content;
        }
    }

    //预算导出类
    public class BudgetExport
    {
        public DataTable tempDataTable { get; set; }
        public int StartNum { get; set; }
        public List<SetExcelContent> Eclist { get; set; }
        public string tableDesigners { get; set; }
        public string SheetName { get; set; }

        public BudgetExport()
        { 
        
        }

        public BudgetExport(DataTable _tempDataTable, int _StartNum, List<SetExcelContent> _Eclist,
            string _tableDesigners, string _SheetName)
        {
            this.tempDataTable = _tempDataTable;
            this.StartNum = _StartNum;
            this.Eclist = _Eclist;
            this.tableDesigners = _tableDesigners;
            this.SheetName = _SheetName;
        }
    }
}