学习札记：Excel导出宏 _Web前端

学习笔记：Excel导出宏
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Drawing;
using System.IO;
using System.Xml;
using System.CodeDom.Compiler;
using Microsoft.CSharp;
using System.Reflection;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Formatters.Binary;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;

namespace TianMeiLab.CommonHelp
{
    public static class DCOMHelp
    {

        #region 导入导出Excel操作

        /// <summary>
        /// 得到excel的数据
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="sheetid"></param>
        /// <param name="sqlwhere"></param>
        /// <returns></returns>
        public static DataSet GetExcelData(string fileName, string sheetid, string sqlwhere)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";" + "Extended Properties='Excel 8.0'";
            DataSet ds = new DataSet();
            OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetid + "$] where 1=1 " + sqlwhere, strConn);
            oada.Fill(ds);
            return ds;
        }

        /// <summary>
        /// 得到页面名称
        /// </summary>
        /// <param name="FileName"></param>
        /// <returns></returns>
        public static string[] GetTableNames(string FileName)
        {
            Microsoft.Office.Interop.Excel.ApplicationClass objExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
            string[] strArray = null;
            List<string> list = new List<string>();
            try
            {
                Microsoft.Office.Interop.Excel.Workbook objWorkBook = objExcel.Workbooks.Open(FileName, Type.Missing,
                                                                                              Type.Missing, Type.Missing,
                                                                                              Type.Missing,
                                                                                              Type.Missing, Type.Missing,
                                                                                              Type.Missing, Type.Missing,
                                                                                              Type.Missing,
                                                                                              Type.Missing, Type.Missing,
                                                                                              Type.Missing, Type.Missing,
                                                                                              Type.Missing);
                foreach (Microsoft.Office.Interop.Excel.Worksheet sheet in objWorkBook.Sheets)
                {
                    string str = sheet.Name.ToLower();
                    list.Add(str);
                }
                objWorkBook.Close(false, Type.Missing, Type.Missing);
                objExcel.Quit();

                strArray = new string[list.Count];
                for (int i = 0; i < list.Count; i++)
                {
                    strArray[i] = list[i];
                }
                return strArray;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
                return strArray;
            }
        }

        /// <summary>
        /// 导出DataGridView数据到文本
        /// </summary>
        /// <param name="FileName"></param>
        /// <returns></returns>
        public static void ExportDataFromDataGridViewToText(DataGridView dgv, string fileName)
        {
            StreamWriter sw = new StreamWriter(fileName);
            string columnTitle = "";
            try
            {
                //写入列标题   
                for (int i = 0; i < dgv.ColumnCount; i++)
                {
                    if (i > 0)
                    {
                        columnTitle += "\t";
                    }
                    columnTitle += dgv.Columns[i].HeaderText;
                }
                sw.WriteLine(columnTitle);
                //写入列内容   
                for (int j = 0; j < dgv.Rows.Count; j++)
                {
                    string columnValue = "";
                    for (int k = 0; k < dgv.Columns.Count; k++)
                    {
                        if (k > 0)
                        {
                            columnValue += "\t";
                        }
                        if (dgv.Rows[j].Cells[k].Value == null)
                            columnValue += "";
                        else
                            columnValue += dgv.Rows[j].Cells[k].Value.ToString().Trim();
                    }
                    sw.WriteLine(columnValue);
                }
                sw.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
            }
            finally
            {
                sw.Close();
            }
        }

        /// <summary>
        /// 导出excel
        /// </summary>
        /// <param name="dgv"></param>
        /// <returns></returns>
        public static bool ExportExcelFromDataGridView(DataGridView dgv)
        {
            return ExportExcelFromDataGridView(dgv, 1, "", "");
        }

        /// <summary>
        /// 导出excel到指定文件的指定数index
        /// </summary>
        /// <param name="dgv"></param>
        /// <param name="sheetIndex"></param>
        /// <param name="filename"></param>
        /// <returns></returns>
        public static bool ExportExcelFromDataGridView(DataGridView dgv, int sheetIndex, string filename)
        {
            return ExportExcelFromDataGridView(dgv, sheetIndex, filename, "");
        }

        /// <summary>
        /// 导出excel到指定文件的指定数index,并执行宏
        /// </summary>
        /// <param name="dgv"></param>
        /// <param name="sheetIndex"></param>
        /// <param name="filename"></param>
        /// <param name="mathname"></param>
        /// <param name="mathparams"></param>
        /// <returns></returns>
        public static bool ExportExcelFromDataGridView(DataGridView dgv, int sheetIndex, string filename, string mathname, params object[] mathparams)
        {

            //实例化一个Excel对象
            Microsoft.Office.Interop.Excel.Application myexcel = new Microsoft.Office.Interop.Excel.Application();
            myexcel.Visible = true; //显示

            if (myexcel == null)
            {
                throw new Exception("EXCEL无法启动！");
            }


            try
            {
                //
                if (filename != "")
                {
                    Object oMissing = System.Reflection.Missing.Value;
                    myexcel.Application.Workbooks.Open(filename, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
                    Microsoft.Office.Interop.Excel.Worksheet wsheet = (Microsoft.Office.Interop.Excel.Worksheet)myexcel.Worksheets[sheetIndex];
                    //wsheet.Activate();
                    //wsheet.Rows.AutoFill();
                    //wsheet.Columns.AutoFill();
                    //wsheet.Cells.AutoFit(); 
                    // myexcel.Rows.AutoFit();
                }
                else
                {
                    myexcel.Application.Workbooks.Add(true);
                }

                //写列名
                int i = 1;
                foreach (DataGridViewColumn dgvcolumn in dgv.Columns)
                {
                    myexcel.Cells[1, i] = dgvcolumn.HeaderText;
                    i++;
                }

                ////写数据  
                int m = 0;
                i = 0;
                object[,] objData = new object[dgv.Rows.Count + 1, dgv.Columns.Count + 1];
                foreach (DataGridViewRow dr in dgv.Rows)
                {
                    foreach (DataGridViewCell dgrcell in dr.Cells)
                    {
                        if (dgrcell.Value == null)
                        {
                            objData[i, m] = "";
                        }
                        else
                        {
                            objData[i, m] = dgrcell.Value.ToString();
                        }
                        m++;
                    }
                    i++;
                    m = 0;
                }

                Microsoft.Office.Interop.Excel.Range range = myexcel.get_Range(myexcel.Cells[2, 1], myexcel.Cells[objData.GetLength(0), objData.GetLength(1)]);
                range.Value2 = objData;
                //Application.DoEvents();   

                if (mathname != "")
                {
                    //定义
                    Object oMissing = System.Reflection.Missing.Value;
                    object[] obpa = { oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing };
                    for (i = 0; i < mathparams.Length; i++)
                    {
                        obpa[i] = mathparams[i];
                    }
                    //调用宏 
                    myexcel.Run(mathname, obpa[0], obpa[1], obpa[2], obpa[3], obpa[4], obpa[5], obpa[6], obpa[7], obpa[8], obpa[9], obpa[10], obpa[11], obpa[12], obpa[13], obpa[14], obpa[15], obpa[16], obpa[17], obpa[18], obpa[19], obpa[20], obpa[21], obpa[22], obpa[23], obpa[24], obpa[25], obpa[26], obpa[27], obpa[28], obpa[29]);

                }
                Application.DoEvents();
                myexcel = null;
                GC.Collect();
                return true;
            }
            catch (Exception ee)
            {
                throw ee;
            }

            //将数据写入Excel;
        }



        /// <summary>
        /// 导出DataTable到Text文本
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="fileName"></param>
        public static void ExportDataFromDataTableToText(DataTable dt, string fileName)
        {
            StreamWriter sw = new StreamWriter(fileName);
            string columnTitle = "";
            try
            {
                //写入列标题   
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    if (i > 0)
                    {
                        columnTitle += "\t";
                    }
                    columnTitle += dt.Columns[i].ColumnName;
                }
                sw.WriteLine(columnTitle);

                //写入列内容   
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    string columnValue = "";
                    for (int k = 0; k < dt.Columns.Count; k++)
                    {
                        if (k > 0)
                        {
                            columnValue += "\t";
                        }
                        if (dt.Rows[j][k] == null)
                            columnValue += "";
                        else
                            columnValue += dt.Rows[j][k].ToString().Trim();
                    }
                    sw.WriteLine(columnValue);
                }
                sw.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
            }
            finally
            {
                sw.Close();
            }
        }


        /// <summary>
        /// 导出excel
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static bool ExportExcelFromDataTable(DataTable dt)
        {
            return ExportExcelFromDataTable(dt, 1, "", "");
        }

        /// <summary>
        /// 导出excel到指定文件的指定数index 
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="sheetIndex"></param>
        /// <param name="filename"></param>
        /// <returns></returns>
        public static bool ExportExcelFromDataTable(DataTable dt, int sheetIndex, string filename)
        {
            return ExportExcelFromDataTable(dt, 1, filename, "");
        }


        /// <summary>
        /// 导出excel到指定文件的指定数index,并执行宏
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="sheetIndex"></param>
        /// <param name="filename"></param>
        /// <param name="mathname"></param>
        /// <param name="mathparams"></param>
        /// <returns></returns>
        public static bool ExportExcelFromDataTable(DataTable dt, int sheetIndex, string filename, string mathname, params object[] mathparams)
        {

            //实例化一个Excel对象
            Microsoft.Office.Interop.Excel.Application myexcel = new Microsoft.Office.Interop.Excel.Application();
            myexcel.Visible = true; //显示

            if (myexcel == null)
            {
                throw new Exception("EXCEL无法启动！");
            }


            try
            {
                //
                if (filename != "")
                {
                    Object oMissing = System.Reflection.Missing.Value;
                    myexcel.Application.Workbooks.Open(filename, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
                    Microsoft.Office.Interop.Excel.Worksheet wsheet = (Microsoft.Office.Interop.Excel.Worksheet)myexcel.Worksheets[sheetIndex];
                    //wsheet.Activate();
                    //wsheet.Rows.AutoFill();
                    //wsheet.Columns.AutoFill();
                    //wsheet.Cells.AutoFit(); 
                    // myexcel.Rows.AutoFit();
                }
                else
                {
                    myexcel.Application.Workbooks.Add(true);
                }

                //写列名
                int i = 1;
                foreach (DataColumn dtcolumn in dt.Columns)
                {
                    myexcel.Cells[1, i] = dtcolumn.ColumnName;
                    i++;
                }


                ////写数据  
                int m = 0;
                i = 0;
                object[,] objData = new object[dt.Rows.Count + 1, dt.Columns.Count + 1];
                foreach (DataRow dr in dt.Rows)
                {
                    foreach (object cellvalue in dr.ItemArray)
                    {
                        if (cellvalue == null)
                        {
                            objData[i, m] = "";
                        }
                        else
                        {
                            objData[i, m] = cellvalue.ToString();
                        }
                        m++;
                    }
                    i++;
                    m = 0;
                }

                Microsoft.Office.Interop.Excel.Range range = myexcel.get_Range(myexcel.Cells[2, 1], myexcel.Cells[objData.GetLength(0), objData.GetLength(1)]);
                range.Value2 = objData;



                if (mathname != "")
                {
                    //定义
                    Object oMissing = System.Reflection.Missing.Value;
                    object[] obpa = { oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing };
                    for (i = 0; i < mathparams.Length; i++)
                    {
                        obpa[i] = mathparams[i];
                    }
                    //调用宏 
                    myexcel.Run(mathname, obpa[0], obpa[1], obpa[2], obpa[3], obpa[4], obpa[5], obpa[6], obpa[7], obpa[8], obpa[9], obpa[10], obpa[11], obpa[12], obpa[13], obpa[14], obpa[15], obpa[16], obpa[17], obpa[18], obpa[19], obpa[20], obpa[21], obpa[22], obpa[23], obpa[24], obpa[25], obpa[26], obpa[27], obpa[28], obpa[29]);

                }
                //  myexcel.DoEvents(); 
                myexcel = null;
                GC.Collect();
                return true;
            }
            catch (Exception ee)
            {
                throw ee;
            }

            //将数据写入Excel;
        }


        #endregion

        #region 导入导出Word

        public static void ExportWordFromDataGridView(DataGridView dgv, string filename)
        { 
            try
            {
                object _filename = (object)filename;
                Microsoft.Office.Interop.Word.Document mydoc = new Microsoft.Office.Interop.Word.Document();//实例化Word文档对象 
                if (dgv.Rows.Count == 0)
                    return;

                Object oMissing = System.Reflection.Missing.Value;

                //建立Word对象 并打开 
                Microsoft.Office.Interop.Word.Application word = new Microsoft.Office.Interop.Word.Application();
                Object myobj = System.Reflection.Missing.Value;
                if (filename != "")
                {
                    mydoc = word.Documents.Open(ref _filename, ref  oMissing, ref oMissing,
                         ref oMissing, ref oMissing, ref oMissing, ref  oMissing,
                         ref  oMissing, ref  oMissing, ref  oMissing, ref oMissing,
                         ref oMissing, ref oMissing, ref oMissing, ref  oMissing, ref  oMissing);
                }
                else
                {
                    mydoc = word.Documents.Add(ref myobj, ref myobj, ref myobj, ref myobj);
                }
                word.Visible = true;
                mydoc.Select();

                //声明Word选择区域 
                Microsoft.Office.Interop.Word.Selection mysel = word.Selection ;
                //将数据生成Word表格文件   声明Word表格
                Microsoft.Office.Interop.Word.Table mytable = mydoc.Tables.Add(mysel.Range, dgv.RowCount, dgv.ColumnCount, ref myobj, ref myobj);

                //设置列宽
                //  mytable.Columns.SetWidth(80, Microsoft.Office.Interop.Word.WdRulerStyle.wdAdjustNone);
                mytable.Columns.AutoFit();
                //输出列标题数据
                for (int i = 0; i < dgv.ColumnCount; i++)
                {
                    mytable.Cell(1, i + 1).Range.InsertAfter(dgv.Columns[i].HeaderText);
                }
                //输出控件中的记录
                for (int i = 0; i < dgv.RowCount - 1; i++)
                {
                    for (int j = 0; j < dgv.ColumnCount; j++)
                    {
                        mytable.Cell(i + 2, j + 1).Range.InsertAfter(dgv[j, i].Value.ToString());
                    }
                }
                mydoc = null;
                word = null;
                _filename = null;
                GC.Collect();
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally {
                GC.Collect();
            }
        }
        #endregion

    }
}