导出Excel

如何将数据导入到Excel，方法大体分为两种。一是以数据流的形式写入到文件，另外一种就是调用Microsoft的Excel.dll。 今天主要介绍后者， 如何根据传入数据和显示格式自动的生成Excel。首先看源代码

 

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Collections;
using System.IO;
using System.Diagnostics;
using Excel;
using System.Runtime.InteropServices;

namespace Gauss.Common
...{
    public class GaussExcel
    ...{
                // Fields
        private DateTime afterTime;
        private DateTime beforeTime;
        private int titleColorindex;
        private int topPargin;
        private int leftPargin;
        private bool showBlank;
        public bool ShowBlank
        ...{
            get ...{ return showBlank; }
            set ...{ showBlank = value; }
        }
    
        public int LeftPargin
        ...{
            get ...{ return leftPargin; }
            set ...{ leftPargin = value; }
        }

        public int TopPargin
        ...{
            get ...{ return topPargin; }
            set ...{ topPargin = value; }
        }
    
        public GaussExcel()
        ...{
            this.titleColorindex = 15;
            this.leftPargin = 2;
            this.topPargin = 2;
            this.showBlank = true;
        }

        private void ClearFile(string FilePath)
        ...{
            string[] textArray1 = Directory.GetFiles(FilePath);
            if (textArray1.Length > 10)
            ...{
                for (int num1 = 0; num1 < 10; num1++)
                ...{
                    try
                    ...{
                        File.Delete(textArray1[num1]);
                    }
                    catch
                    ...{
                    }
                }
            }
        }
        public string ProduceExcel(System.Data.DataTable dt, string strTitle, string FilePath, Hashtable nameList) 
        ...{
            string fileName=null;
            ClearFile(FilePath);
            fileName = OutputExcel(dt, strTitle, FilePath, nameList);
            KillExcelProcess();
            return fileName;
        }   
        public void KillExcelProcess()
        ...{
            Process[] processArray1 = Process.GetProcessesByName("Excel");
            foreach (Process process1 in processArray1)
            ...{
                DateTime time1 = process1.StartTime;
                if ((time1 > this.beforeTime) && (time1 < this.afterTime))
                ...{
                    process1.Kill();
                }
            }
        }
        public string OutputExcel(System.Data.DataTable dt, string strTitle, string FilePath, Hashtable nameList)
        ...{
            this.beforeTime = DateTime.Now;
            //FiledName
            int num1 = this.topPargin+1;
            if (this.showBlank) num1 += 1;
            int numTitle = num1;
            int num2 = this.leftPargin-1;
            Application application1 = new ApplicationClass();
            _Workbook workbook1 = application1.Workbooks.Add(true);
            _Worksheet worksheet1 = (_Worksheet)workbook1.ActiveSheet;

           
            foreach (DataColumn column1 in dt.Columns)
            ...{
                bool myFlag = false;                
                string text5 = column1.ColumnName.Trim();
                if (nameList.ContainsKey(text5))
                ...{
                    num2++;
                    application1.Cells[num1, num2] = nameList[text5];
                    worksheet1.get_Range(application1.Cells[num1, num2], application1.Cells[num1, num2]).Font.Bold = true;
                    worksheet1.get_Range(application1.Cells[num1, num2], application1.Cells[num1, num2]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
                    worksheet1.get_Range(application1.Cells[num1, num2], application1.Cells[num1, num2]).Select();
                    worksheet1.get_Range(application1.Cells[num1, num2], application1.Cells[num1, num2]).Interior.ColorIndex = this.titleColorindex;  
                    
                }
              /**//*  IDictionaryEnumerator enumerator1 = nameList.GetEnumerator();
                while (enumerator1.MoveNext())
                {
                    if (enumerator1.Key.ToString().Trim() == text5)
                    {
                        text5 = enumerator1.Value.ToString() ;
                        myFlag = true;
                    }
                }
                if (myFlag) {
                    num2++;
                    application1.Cells[num1, num2] = text5;
                    worksheet1.get_Range(application1.Cells[num1, num2], application1.Cells[num1, num2]).Font.Bold = true;
                    worksheet1.get_Range(application1.Cells[num1, num2], application1.Cells[num1, num2]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
                    worksheet1.get_Range(application1.Cells[num1, num2], application1.Cells[num1, num2]).Select();
                    worksheet1.get_Range(application1.Cells[num1, num2], application1.Cells[num1, num2]).Interior.ColorIndex = this.titleColorindex;  
                }    */                         
            }
            //Fill In Data To Excel