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
}
}
学习笔记:Excel导出宏
最新推荐文章于 2021-01-26 10:08:03 发布