学习笔记：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 }}