WinForm中导出Excel（动态导出列）

主要代码：

            Excel.Application app = new Excel.ApplicationClass();
            app.Visible = false;
            afterTime = DateTime.Now;
            string workbookPath = System.Windows.Forms.Application.StartupPath + "//Excel//对账明细表.xls";
            //打开模板文件，得到WorkBook对象
            Excel.Workbook workBook = app.Workbooks.Open(workbookPath, new object[] { 3 }, new object[] { false }, new object[] { 5 },new object[] { "" }, new object[] { "" }, new object[] { false }, Excel.XlPlatform.xlWindows, null, new object[] { false }, new object[] { true }, Excel.XlFileFormat.xlExcel9795, new object[] { true }, Missing.Value, Missing.Value);
            if (workBook == null)
            {
                throw new Exception("请确定是否已经有此Excel模板文件!");
            }

            Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1);
            //找模板字段
              Excel.Range range = (Excel.Range)workSheet.Rows[7, Missing.Value];
            List<string> excelColName = new List<string>();
            List<string> excelChargeColName = new List<string>();
            for (int j = 2; j < 55; j++)
            {
                range = (Excel.Range)workSheet.Cells[6, j];
                    
                if (range.Text.ToString().IndexOf(']') >= 0 || range.Text.ToString().IndexOf('$') >= 0)
                {
                     string name = range.Text.ToString().Substring(1, range.Text.ToString().Length - 2);
                     excelColName.Add(name);     //此处List集合增加需要显示列的列名             
                }
                if (range.Text.ToString().IndexOf('$') >= 0)
                {
                     string name = range.Text.ToString().Substring(1, range.Text.ToString().Length - 2);
                     excelChargeColName.Add(name);//此处List集合增加需要显示的费用列列名
                }
            }

然后找数据源，可以通过存储过程取得一个数据源：假设已经得到数据源DataTabe dtNow；

 

            if (dtNow.Rows.Count == 0)
            {
                throw new Exception("您没有取到数据，请重新选择查询条件!");
            }
            //dtNow.Columns.Add(new DataColumn("空", typeof(System.String)));
            if (!isDynamic)//这里是我定义的一个私有变量。是否动态显示列
            {
                if (!dtNow.Columns.Contains(totalChargeColumn))
                {
                    dtNow.Columns.Add(totalChargeColumn, typeof(decimal));
                    dtNow.Columns[totalChargeColumn].DefaultValue = 0.0000;
                }
            }
            range = (Excel.Range)workSheet.Rows[7, Missing.Value];
            for (int i = 0; i < dtNow.Rows.Count - 1; i++)
            {
                range.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Missing.Value);
            }
            //已知费用
            List<string> charges = new List<string>();
            int otherChargeIndex = 0;
            decimal totalAmount = 0;
            //找到其它费用列
            Excel.Range oRange = ((Excel.Range)workSheet.UsedRange).Find("其它费", Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Excel.XlSearchDirection.xlNext, Missing.Value, Missing.Value, Missing.Value);
            if (oRange != null)
            {
                otherChargeIndex = oRange.Column;
            }
            //合计
            if (otherChargeIndex == 0)
            {
                throw new Exception("模板中必须有其它费!");
            }
           //找到已知费用名
            for (int j = 2; j < 55; j++)
            {
                range = (Excel.Range)workSheet.Cells[6, j];
                if (range.Text.ToString().IndexOf('$') >= 0)
                {
                    string name = range.Text.ToString().Substring(1, range.Text.ToString().Length - 2);
                    charges.Add(name);//已知列名
                    decimal total = 0;
                    if (dtNow.Columns.Contains(name))
                    {
                        foreach (DataRow dr1 in dtNow.Rows)
                        {
                            total += DataTypeHelper.GetDecimal(dr1[name], 0);
                        }
                    }
                    totalAmount += total;
                    workSheet.Cells[6 + dtNow.Rows.Count, j] = total;//求得费用的和
                }
            }
           //添加序号列
            dtNow.Columns.Add(new DataColumn("序号", typeof(System.Int32)));
            for (int m = 0; m < dtNow.Rows.Count; m++)
            {
                dtNow.Rows[m]["序号"] = m + 1;
            }
            //添加不为零的列
            int currentColumnIndex = 0;
            foreach (DataColumn dc in dtNow.Columns)
            {
                if (dc.ColumnName.LastIndexOf('费') > 0)//是费用项目
                {
                    if (!charges.Contains(dc.ColumnName))//如果没有列出，看是否有费用
                    {
                        DataRow[] drs = dtNow.Select(string.Format("{0} > 0", dc.ColumnName));
                        if (drs.Length > 0)//有大于费用
                        {
                            if (isDynamic)//如果是动态添加列，则动态添加
                            {
                                range = (Excel.Range)workSheet.Columns[otherChargeIndex, Missing.Value];
                                range.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, Missing.Value);
                                currentColumnIndex = currentColumnIndex == 0 ? otherChargeIndex : currentColumnIndex;
                                workSheet.Cells[5, currentColumnIndex] = dc.ColumnName;
                                workSheet.Cells[6, currentColumnIndex] = "$" + dc.ColumnName + "$";
                                int sindex = excelColName.FindIndex(delegate(string s) { return s == "其它费"; });
                                excelColName.Insert(sindex, dc.ColumnName);
                                decimal total = 0;
                                foreach (DataRow dr1 in dtNow.Rows)
                                {
                                    total += DataTypeHelper.GetDecimal(dr1[dc], 0);
                                }
                                totalAmount += total;
                                workSheet.Cells[6 + dtNow.Rows.Count, currentColumnIndex] = total;
                            }
                            else//否则将费用汇总到某一列中
                            {
                                foreach (DataRow dr1 in dtNow.Rows)
                                {
                                    //加到合计列中
                                    dr1[totalChargeColumn] = DataTypeHelper.GetDecimal(dr1[totalChargeColumn], 0) + DataTypeHelper.GetDecimal(dr1[dc], 0);
                                    if (DataTypeHelper.GetDecimal(dr1[dc], 0) > 0 && dtNow.Columns.Contains("备注"))
                                    {
                                        dr1["备注"] = DataTypeHelper.GetString(dr1["备注"]) + dc.ColumnName + DataTypeHelper.GetString(DataTypeHelper.GetDecimal(dr1[dc], 0).ToString("0")) + "/";
                                    }
                                    totalAmount += DataTypeHelper.GetDecimal(dr1[dc], 0);
                                }
                            }
                        }
                    }
                }
            }
           if (!isDynamic)//如果是合计列，则将重新计算合计
            {
                decimal total = 0;
                foreach (DataRow dr1 in dtNow.Rows)
                {
                    total += DataTypeHelper.GetDecimal(dr1[totalChargeColumn], 0);
                    string remark = DataTypeHelper.GetString(dr1["备注"]);
                    if (remark.EndsWith("/"))
                    {
                        dr1["备注"] = remark.Substring(0, remark.Length - 1);
                    }
                }
                //找到合计列
                Excel.Range totalRange = ((Excel.Range)workSheet.UsedRange).Find(totalChargeColumn, Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value, Excel.XlSearchDirection.xlNext, Missing.Value, Missing.Value, Missing.Value);
                if (totalRange == null)
                {
                    throw new BusinessException(string.Format("模板中必须有{0}!", totalChargeColumn));
                }
                workSheet.Cells[6 + dtNow.Rows.Count, totalRange.Column] = total;
            }

再接下来就是批量填充到Excel中：

 

           //专成二维数组                
            Object[,] objects = new object[dtNow.Rows.Count, excelColName.Count];
            int kk = 0;
            foreach (string columnName in excelColName)
            {
                for (int j = 0; j < dtNow.Rows.Count; j++)
                {
                    if (dtNow.Columns.Contains(columnName))
                    {
                        objects[j, kk] = dtNow.Rows[j][columnName];
                    }
                    else
                    {
                        objects[j, kk] = 0.00;
                    }
                }
                kk++;
            }
            Excel.Range objRange = workSheet.get_Range("B6", Missing.Value);
            objRange = objRange.get_Resize(dtNow.Rows.Count, excelColName.Count);
            objRange.set_Value(Missing.Value, objects);
            foreach (DataColumn dc in dtNow.Columns)
            {
                Excel.Range oRange1 = ((Excel.Range)workSheet.UsedRange).Find("[" + dc.ColumnName + "]", Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value, Excel.XlSearchDirection.xlNext, Missing.Value, Missing.Value, Missing.Value);
                if (oRange1 != null)
                {
                    workSheet.Cells[oRange1.Row, oRange1.Column] = oRange1.Text.ToString().Replace("[" + dc.ColumnName + "]",
                        dtNow.Rows[0][dc].ToString());
                }
            }

再把Excel保存:

 

            saveFileDialog.FileName = "对账明细表" + "(" + DataTypeHelper.GetDateTime(dtNow.Rows[0]["StartTime"]).ToString("M-d")
                + "到" + DataTypeHelper.GetDateTime(dtNow.Rows[0]["EndTime"]).ToString("M-d") + ")";
            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                workBook.SaveAs(saveFileDialog.FileName
                 , Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange,
                 Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                workBook.Close(false, null, null);
                app.Workbooks.Close();
                app.Quit();
                if (app != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                    app = null;
                }
                GC.Collect();
            }           
            KillExcelProcess(); 

        /// <summary>
        /// 结束Excel进程
         /// </summary>
        public void KillExcelProcess()
        {
            Process[] myProcesses;
            DateTime startTime;
            myProcesses = Process.GetProcessesByName("Excel");

            //得不到Excel进程ID，暂时只能判断进程启动时间
            foreach (Process myProcess in myProcesses)
            {
                startTime = myProcess.StartTime;

                if (startTime > beforeTime && startTime < afterTime)
                {
                    myProcess.Kill();
                }
            }
        }
        private void DeleteRows(Excel.Worksheet sheet, int rowIndex)
        {
            Range range = (Range)sheet.Rows[rowIndex, Missing.Value];
            range.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
        }

 

以上会引用到：

using System.Diagnostics;
using Excel;
using System.Reflection;

Excel模板中： [列的名称]，$费用名称$