C#操作Excel

首先添加引用(add refference)

com->Microsoft Excel 12.0 Object Library

 

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

namespace BusinessProgramming
{
    class ExcelExporter
    {
        #region "Constructors"
        public ExcelExporter()
        {
        }

        public ExcelExporter(DataTable data)
        {
            this.Results = data;
        }
        #endregion

        #region "Property"
        public DataTable Results
        {
            get;
            set;
        }
        #endregion

        #region "Methods"
        #region "GeneralExport"
        public void GeneralExport()
        {
            if (Results != null)
            {
                ExcelExporter.GeneralExport(Results);
            }
        }

        public static void GeneralExport(DataTable dt)
        {
            Excel.Application xlApp;
            Excel.Workbook xlBook;
            Excel.Workbooks xlBooks;
            Excel.Sheets xlsheets;
            Excel.Worksheet xlSheet;
            try
            {
                Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
                xlApp = new Excel.Application();
                xlBooks = xlApp.Workbooks;
                xlBook = xlBooks.Add(Missing.Value);
                xlsheets = xlBook.Worksheets;
                xlSheet = (Excel.Worksheet)xlsheets.get_Item(1);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        xlSheet.Cells[i + 1, j + 1] = dt.Rows[i][j].ToString();
                    }
                }
                xlBook.SaveAs(GlobalVariableHolder.ExcelFilePath, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                    Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value);
                xlBook.Close(false, Missing.Value, Missing.Value);
            }
            catch
            {
            }
            finally
            {
                xlSheet = null;
                xlBook = null;
                xlApp = null;
            }
        }
        #endregion

        #region "ExportUnAdjustedTrialBalance"
        public static void ExportUnAdjustedTrialBalance(Hashtable ht)
        {
            Excel.Application xlApp;
            Excel.Workbook xlBook;
            Excel.Sheets xlsheets;
            Excel.Worksheet xlSheet;
            try
            {
                Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
                xlApp = new Excel.Application();
                xlBook = xlApp.Workbooks._Open(GlobalVariableHolder.UnAdjustedTrialBalanceTemplateFilePath,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                xlsheets = xlBook.Worksheets;
                xlSheet = (Excel.Worksheet)xlsheets.get_Item(1);
                xlSheet.Cells[2, 2] = ht["Month"].ToString();
                xlSheet.Cells[3, 2] = ht["Year"].ToString();
                xlSheet.Cells[4, 2] = ht["Client"].ToString();
                xlSheet.Cells[7, 6] = ht["110"].ToString();
                xlSheet.Cells[8, 6] = ht["120"].ToString();
                xlSheet.Cells[9, 6] = ht["140"].ToString();
                xlSheet.Cells[10, 6] = ht["145"].ToString();
                xlSheet.Cells[11, 6] = ht["150"].ToString();
                xlSheet.Cells[12, 6] = ht["155"].ToString();
                xlSheet.Cells[13, 6] = ht["160"].ToString();
                xlSheet.Cells[14, 6] = ht["161"].ToString();
                xlSheet.Cells[15, 6] = ht["165"].ToString();
                xlSheet.Cells[16, 6] = ht["166"].ToString();
                xlSheet.Cells[17, 7] = ht["220"].ToString();
                xlSheet.Cells[18, 7] = ht["230"].ToString();
                xlSheet.Cells[19, 7] = ht["240"].ToString();
                xlSheet.Cells[20, 7] = ht["300"].ToString();
                xlSheet.Cells[21, 6] = ht["301"].ToString();
                xlSheet.Cells[22, 7] = ht["400"].ToString();
                xlSheet.Cells[23, 6] = ht["620"].ToString();
                xlSheet.Cells[24, 6] = ht["660"].ToString();
                xlSheet.Cells[25, 6] = ht["665"].ToString();
                xlSheet.Cells[26, 6] = ht["682"].ToString();
                xlSheet.Cells[27, 6] = ht["688"].ToString();
                xlSheet.Cells[28, 6] = ht["689"].ToString();
                xlSheet.Cells[29, 6] = ht["690"].ToString();
                xlSheet.Cells[30, 6] = ht["692"].ToString();
                xlSheet.Cells[31, 6] = ht["695"].ToString();
                xlSheet.Cells[32, 6] = "=SUM(F7:F31)";
                xlSheet.Cells[32, 7] = "=SUM(G7:G31)";
                xlBook.SaveAs(GlobalVariableHolder.ExcelFilePath, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                    Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value);
                xlBook.Close(false, Missing.Value, Missing.Value);
            }
            catch
            {
            }
            finally
            {
                xlSheet = null;
                xlBook = null;
                xlApp = null;
            }
        }
        #endregion

        #region "ExportAdjustedTrialBalance"
        public static void ExportAdjustedTrialBalance(Hashtable ht)
        {
            Excel.Application xlApp;
            Excel.Workbook xlBook;
            Excel.Sheets xlsheets;
            Excel.Worksheet xlSheet;
            try
            {
                Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
                xlApp = new Excel.Application();
                xlBook = xlApp.Workbooks._Open(GlobalVariableHolder.AdjustedTrialBalanceTemplateFilePath,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                xlsheets = xlBook.Worksheets;
                xlSheet = (Excel.Worksheet)xlsheets.get_Item(1);
                xlSheet.Cells[2, 2] = ht["Month"].ToString();
                xlSheet.Cells[3, 2] = ht["Year"].ToString();
                xlSheet.Cells[4, 2] = ht["Client"].ToString();
                xlSheet.Cells[7, 6] = ht["110"].ToString();
                xlSheet.Cells[8, 6] = ht["120"].ToString();
                xlSheet.Cells[9, 6] = ht["140"].ToString();
                xlSheet.Cells[10, 6] = ht["145"].ToString();
                xlSheet.Cells[11, 6] = ht["150"].ToString();
                xlSheet.Cells[12, 6] = ht["155"].ToString();
                xlSheet.Cells[13, 6] = ht["160"].ToString();
                xlSheet.Cells[14, 6] = ht["161"].ToString();
                xlSheet.Cells[15, 6] = ht["165"].ToString();
                xlSheet.Cells[16, 6] = ht["166"].ToString();
                xlSheet.Cells[17, 7] = ht["220"].ToString();
                xlSheet.Cells[18, 7] = ht["230"].ToString();
                xlSheet.Cells[19, 7] = ht["240"].ToString();
                xlSheet.Cells[20, 7] = ht["300"].ToString();
                xlSheet.Cells[21, 6] = ht["301"].ToString();
                xlSheet.Cells[22, 7] = ht["400"].ToString();
                xlSheet.Cells[23, 6] = ht["620"].ToString();
                xlSheet.Cells[24, 6] = ht["660"].ToString();
                xlSheet.Cells[25, 6] = ht["665"].ToString();
                xlSheet.Cells[26, 6] = ht["682"].ToString();
                xlSheet.Cells[27, 6] = ht["688"].ToString();
                xlSheet.Cells[28, 6] = ht["689"].ToString();
                xlSheet.Cells[29, 6] = ht["690"].ToString();
                xlSheet.Cells[30, 6] = ht["692"].ToString();
                xlSheet.Cells[31, 6] = ht["695"].ToString();
                xlSheet.Cells[32, 6] = "=SUM(F7:F31)";
                xlSheet.Cells[32, 7] = "=SUM(G7:G31)";
                xlBook.SaveAs(GlobalVariableHolder.ExcelFilePath, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                    Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value);
                xlBook.Close(false, Missing.Value, Missing.Value);
            }
            catch
            {
            }
            finally
            {
                xlSheet = null;
                xlBook = null;
                xlApp = null;
            }
        }
        #endregion

        #region "ExportIncomeStatement"
        public static void ExportIncomeStatement(Hashtable ht)
        {
            Excel.Application xlApp;
            Excel.Workbook xlBook;
            Excel.Sheets xlsheets;
            Excel.Worksheet xlSheet;
            try
            {
                Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
                xlApp = new Excel.Application();
                xlBook = xlApp.Workbooks._Open(GlobalVariableHolder.IncomeStatementTemplateFilePath,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                xlsheets = xlBook.Worksheets;
                xlSheet = (Excel.Worksheet)xlsheets.get_Item(1);
                xlSheet.Cells[2, 2] = ht["Month"].ToString();
                xlSheet.Cells[3, 2] = ht["Year"].ToString();
                xlSheet.Cells[4, 2] = ht["Client"].ToString();
                xlSheet.Cells[7, 5] = ht["400"].ToString();
                xlSheet.Cells[11, 5] = ht["620"].ToString();
                xlSheet.Cells[12, 5] = ht["660"].ToString();
                xlSheet.Cells[13, 5] = ht["665"].ToString();
                xlSheet.Cells[14, 5] = ht["682"].ToString();
                xlSheet.Cells[15, 5] = ht["688"].ToString();
                xlSheet.Cells[16, 5] = ht["689"].ToString();
                xlSheet.Cells[17, 5] = ht["690"].ToString();
                xlSheet.Cells[18, 5] = ht["692"].ToString();
                xlSheet.Cells[19, 5] = ht["695"].ToString();
                xlSheet.Cells[21, 5] = "=SUM(E11:E19)";
                xlSheet.Cells[24, 5] = "=E7-E21";
                xlBook.SaveAs(GlobalVariableHolder.ExcelFilePath, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                    Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value);
                xlBook.Close(false, Missing.Value, Missing.Value);
            }
            catch
            {
            }
            finally
            {
                xlSheet = null;
                xlBook = null;
                xlApp = null;
            }
        }
        #endregion

        #region "ExportOwnersEquity"
        public static void ExportOwnersEquity(Hashtable ht)
        {
            Excel.Application xlApp;
            Excel.Workbook xlBook;
            Excel.Sheets xlsheets;
            Excel.Worksheet xlSheet;
            try
            {
                Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
                xlApp = new Excel.Application();
                xlBook = xlApp.Workbooks._Open(GlobalVariableHolder.StatementofOwnersEquityTemplateFilePath,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                xlsheets = xlBook.Worksheets;
                xlSheet = (Excel.Worksheet)xlsheets.get_Item(1);
                xlSheet.Cells[2, 2] = ht["Month"].ToString();
                xlSheet.Cells[3, 2] = ht["Year"].ToString();
                xlSheet.Cells[4, 2] = ht["Client"].ToString();
                xlSheet.Cells[7, 5] = ht["300"].ToString();
                xlSheet.Cells[8, 5] = ht["NetIncome"].ToString();
                xlSheet.Cells[10, 5] = "=E7+E8";
                xlSheet.Cells[14, 5] = ht["301"].ToString();
                xlSheet.Cells[16, 5] = "=E10-E14";
                xlBook.SaveAs(GlobalVariableHolder.ExcelFilePath, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                    Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value);
                xlBook.Close(false, Missing.Value, Missing.Value);
            }
            catch
            {
            }
            finally
            {
                xlSheet = null;
                xlBook = null;
                xlApp = null;
            }
        }
        #endregion

        #region "ExportBalanceSheet"
        public static void ExportBalanceSheet(Hashtable ht)
        {
            Excel.Application xlApp;
            Excel.Workbook xlBook;
            Excel.Sheets xlsheets;
            Excel.Worksheet xlSheet;
            try
            {
                Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
                xlApp = new Excel.Application();
                xlBook = xlApp.Workbooks._Open(GlobalVariableHolder.StatementofOwnersEquityTemplateFilePath,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                xlsheets = xlBook.Worksheets;
                xlSheet = (Excel.Worksheet)xlsheets.get_Item(1);
                xlSheet.Cells[2, 2] = ht["Month"].ToString();
                xlSheet.Cells[3, 2] = ht["Year"].ToString();
                xlSheet.Cells[4, 2] = ht["Client"].ToString();
                xlSheet.Cells[8, 5] = ht["110"].ToString();
                xlSheet.Cells[9, 5] = ht["120"].ToString();
                xlSheet.Cells[10, 5] = ht["140"].ToString();
                xlSheet.Cells[11, 5] = ht["145"].ToString();
                xlSheet.Cells[12, 5] = ht["150"].ToString();
                xlSheet.Cells[13, 5] = ht["155"].ToString();
                xlSheet.Cells[14, 5] = "=SUM(E8:E13)";
                xlSheet.Cells[17, 5] = ht["160"].ToString();
                xlSheet.Cells[18, 5] = ht["161"].ToString();
                xlSheet.Cells[19, 5] = ht["165"].ToString();
                xlSheet.Cells[20, 5] = ht["166"].ToString();
                xlSheet.Cells[21, 5] = "=E17-E18+E19-E20";
                xlSheet.Cells[24, 5] = "=E14+E21";
                xlSheet.Cells[29, 5] = ht["220"].ToString();
                xlSheet.Cells[30, 5] = ht["230"].ToString();
                xlSheet.Cells[31, 5] = ht["240"].ToString();
                xlSheet.Cells[32, 5] = "=SUM(E29:E31)";
                xlSheet.Cells[36, 5] = ht["300"].ToString();
                xlSheet.Cells[37, 5] = "=E36";
                xlSheet.Cells[40, 5] = "=E32+E27";
                xlBook.SaveAs(GlobalVariableHolder.ExcelFilePath, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                    Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value, Missing.Value);
                xlBook.Close(false, Missing.Value, Missing.Value);
            }
            catch
            {
            }
            finally
            {
                xlSheet = null;
                xlBook = null;
                xlApp = null;
            }
        }
        #endregion
        #endregion
    }
}