导出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
            string customerName1 = "";
            string customerName2 = "";
            int rowColor = 6;
            foreach (DataRow row1 in dt.Rows)
            ...{
                num1++;
                
                num2 = this.leftPargin-1;
                customerName2 = row1["customer"].ToString();
                if (!customerName1.Equals(customerName2))
                ...{
                    rowColor = rowColor - 1;
                    customerName1 = customerName2;
                }
                foreach (DataColumn column2 in dt.Columns)
                ...{
                    string colName = column2.ColumnName.Trim();
                    if (nameList.ContainsKey(colName))
                    ...{
                        num2++;

                        worksheet1.get_Range(application1.Cells[num1, num2], application1.Cells[num1, num2]).Interior.ColorIndex = rowColor;
                        if (column2.DataType == Type.GetType("System.DateTime"))
                        ...{
                            application1.Cells[num1, num2] = Convert.ToDateTime(row1[column2.ColumnName].ToString()).ToString("yyyy-MM-dd");
                            worksheet1.get_Range(application1.Cells[num1, num2], application1.Cells[num1, num2]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
                        }
                        else
                        ...{
                            if (column2.DataType == Type.GetType("System.String"))
                            ...{
                                application1.Cells[num1, num2] = "'" + row1[column2.ColumnName].ToString();
                                worksheet1.get_Range(application1.Cells[num1, num2], application1.Cells[num1, num2]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
                                continue;
                            }
                            application1.Cells[num1, num2] = row1[column2.ColumnName].ToString();
                        }
                    }
                }
            }
            //Write Total
            int num3 = num1 + 1;
            int num4 = this.leftPargin;
            string testValue = "总计";
            application1.Cells[num3, this.leftPargin] = testValue;
            worksheet1.get_Range(application1.Cells[num3, num4], application1.Cells[num3, num4]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
            worksheet1.get_Range(application1.Cells[num3, num4], application1.Cells[num3, num2]).Select();
            //Title
            application1.Cells[leftPargin, topPargin] = strTitle;
            worksheet1.get_Range(application1.Cells[leftPargin,topPargin], application1.Cells[leftPargin,topPargin]).Font.Bold = true;
            worksheet1.get_Range(application1.Cells[leftPargin,topPargin], application1.Cells[leftPargin,topPargin]).Font.Size = 0x16;
            worksheet1.get_Range(application1.Cells[numTitle, this.leftPargin], application1.Cells[num3, num2]).Select();
            worksheet1.get_Range(application1.Cells[numTitle, this.leftPargin], application1.Cells[num3, num2]).Columns.AutoFit();
            worksheet1.get_Range(application1.Cells[leftPargin,topPargin], application1.Cells[this.leftPargin, num2]).Select();
            worksheet1.get_Range(application1.Cells[leftPargin,topPargin], application1.Cells[this.leftPargin, num2]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
            worksheet1.get_Range(application1.Cells[numTitle, this.leftPargin], application1.Cells[num3, num2]).Borders.LineStyle = 1;
            worksheet1.get_Range(application1.Cells[numTitle, this.leftPargin], application1.Cells[num3, this.leftPargin]).Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;
            worksheet1.get_Range(application1.Cells[numTitle, this.leftPargin], application1.Cells[numTitle, num2]).Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;
            worksheet1.get_Range(application1.Cells[numTitle, num2], application1.Cells[num3, num2]).Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;
            worksheet1.get_Range(application1.Cells[num3, this.leftPargin], application1.Cells[num3, num2]).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;
            //Native
            if (this.ShowBlank)
            ...{
                application1.Cells[topPargin + 1, leftPargin] = "'" + DateTime.Now.ToString("yyyy/MM/dd");
            }            

            this.afterTime = DateTime.Now;
            this.ClearFile(FilePath);
            string text1 =DateTime.Now.ToString("yyyyMMddHHmmssff") + ".xls";
            application1.ActiveWorkbook.SaveAs(FilePath + text1, XlFileFormat.xlExcel9795, null, null, false, false, XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
            workbook1.Close(null, null, null);
            application1.Workbooks.Close();
            application1.Quit();
            if (worksheet1 != null)
            ...{
                Marshal.ReleaseComObject(worksheet1);
                worksheet1 = null;
            }
            if (workbook1 != null)
            ...{
                Marshal.ReleaseComObject(workbook1);
                workbook1 = null;
            }
            if (application1 != null)
            ...{
                Marshal.ReleaseComObject(application1);
                application1 = null;
            }
            GC.Collect();
            return text1;
        }
        public int TitleColorIndex
        ...{
            get
            ...{
                return this.titleColorindex;
            }
            set
            ...{
                this.titleColorindex = value;
            }
        }
    }
}

 主要说明：

1、功能

根据传入的数据和列名，以及题目名称，是否显示title，上距，左距的多少生成Excel。同时可以根据数据的不同进行着色。下面是我生成的Excel的样式：

图片上传不了，遗憾

2、调用fang

 

            DataSet ds = (DataSet)Session["DayList"];
            string ExcelFolder = ConfigHelper.GetConfigString("ExcelFolder");
            string FilePath = Server.MapPath(".") + "/" + ExcelFolder + "/";

            //前面是Tabel的列名，后面是Excel中要显示的名称

            Hashtable nameList = new Hashtable();
            nameList.Add("customer", "Customer");
            nameList.Add("projecType", "Project Type");
            nameList.Add("projectID", "Project ID");
            nameList.Add("title", "Title");
            nameList.Add("requestor", "Requestor");
            nameList.Add("userID", "Developer");
            nameList.Add("totalME", "Total Mandays Estimated");
            nameList.Add("totalMU", "Total Mandays Used");
            nameList.Add("manIRP", "Mandays In Reporting Period");
            nameList.Add("actSD", "Actual Start Date");
            nameList.Add("agreeED", "Agreed End Date");
            nameList.Add("actDD", "Actual Delivery Date");
            nameList.Add("status", "Status");
            //利用excel对象
           // DataToExcel dte = new DataToExcel();
            GaussExcel gassEx = new GaussExcel();

              // Tilte的颜色
            gassEx.TitleColorIndex = 10;

// 左边距
            gassEx.LeftPargin = 1;

// 上边距
            gassEx.TopPargin = 1;
// 是否显示生成的日期，位于题目与内容之间

            gassEx.ShowBlank = true;
            string filename = "";
            try
            ...{
                if (ds.Tables[0].Rows.Count > 0)
                ...{
                    filename = gassEx.ProduceExcel(ds.Tables[0], "Weekly Report Of Softact-NSS-BSS", FilePath, nameList);
                }
            }
            catch
            ...{
                gassEx.KillExcelProcess();
            }

            if (filename != "")
            ...{
                Response.Redirect(ExcelFolder + "/" + filename, true);
            }