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导出宏
热度:257 发布时间:2013-09-28 10:01:20.0
相关解决方案
- 导出 Excel 资料怎样命名
- word excel pdf在ie浏览器中打开,该如何解决
- 在浏览器中展示word,excel.ppt,pdf等各种文件
- POI 处置 Excel,读取Excel中的格式 如表格框,背景色
- jxls 怎么导出图片到 excel 中
- poi excel 如何合并单元格?
- 用Java EXCEL API 是否能从Excel中导出表格,该如何处理
- java excel 問題 求大神 速解解决方案
- java 处理 excel 相关有关问题
- 求Microsoft.Office.Interop.Excel.dll 11.0.0.0解决方法
- Microsoft.Office.Interop.Excel.dll 导出的文件版本的有关问题
- VC 2010 怎么 操作 excel 了
- 小弟我用水晶报表9新建一个报表,采用PULL模式,在创建新的连接中选用access/Excel(DAO),选择数据库之后,总是弹出对话框说登录失败
- [Crystal Reports]在ASP.net web中导出 有关问题[Excel、Word、PDF]
- Excel 图表解决方法
- asp.net 导出 包孕样式的 excel
- 使用FlashPrinter将word,excel,pdf转换成swf失败的有关问题
- DataSet 读写 Excel.该怎么处理
- excel 导入数据到sql。就出有关问题了
- asp.net 导出(上载)Excel xp、win7 兼容
- .net 操作office(excel、word)咋搞?解决方法
- 怎的把Microsoft.Office.Interop.Excel.ApplicationClass excel创建的文件保存到数据流中
- asp.net上载并保存 excel 格式表格数据
- 64位操作系统能否使用 Microsoft.ACE.OLEDB.12.0 查询2003及以上的 Excel
- 后台下传文档,不限类型(doc,excel,txt都可以),前台上载的时候转换为PDF
- 高分分提问,excel 导出,提示CSS丢失有关问题
- 怎么实现在线显示word,excel,pdf,而不允许下载
- 怎么将gridview中的图片导出到word/excel?现在导出了其他数据列就差图片列了了
- 关于“Microsoft.Office.Interop.Excel.dll”的警告解决办法
- Excel 导入到DataSet,该如何处理