22,298
社区成员
发帖
与我相关
我的任务
分享关于导出到EXCEL文件的问题
用SQL语句实现数据表导出到EXCEL文件,我用BCP试过,但导出的只有数据,没有字段名,而且导出的EXCEL文件格式是不标准的.
能否用别的方法,保证效率的同时又保证格式的正确呢?
能否用别的方法,保证效率的同时又保证格式的正确呢?
...全文
请发表友善的回复…
发表回复
ocen713 2007-11-14
- 打赏
- 举报
ExcelLib exce = new ExcelLib();
try
{
string savepath = SaveDia.FileName;//导出路径
exce.doExport(this.ds, savepath, "导出实际销售表");
MessageBox.Show("文件导出成功!请查看!", "提示");
}
catch (Exception err)
{
throw (new Exception("导出操作失败!" + err.Message));
}
应该是这个
上面有些东西是我自己程序里面的
删除就好了
ocen713 2007-11-14
- 打赏
- 举报
以上一个类
实现excel导出
请查看summary正确调用参数就可以实现导出
一下是例子
实现excel导出
请查看summary正确调用参数就可以实现导出
一下是例子
ExcelLib exce = new ExcelLib();
try
{
string savepath = SaveDia.FileName;
exce.doExport(this.ds, savepath, "导出实际销售表");
frm.Dispose();
MessageBox.Show("文件导出成功!请查看!", "提示");
}
catch (Exception err)
{
frm.Dispose();
father.TopMost = true;
throw (new Exception("导出操作失败!" + err.Message));
}
ocen713 2007-11-14
- 打赏
- 举报
[code=C#]//1.添加引用-〉com-〉microsoft excel 11.0
//2.若出现错误:命名空间“Microsoft.Office”中不存在类型或命名空间名称“Interop”(是缺少程序集引用吗?)
//解决方法：先删除引用中的Excel,然后找到文件Microsoft.Office.Interop.Excel.dll，手动添加该文件的引用
using System;
using System.Data;
using System.Reflection;
using System.IO;
using Microsoft.Office.Core;
using System.Windows.Forms;
using Excel;
namespace Excel.Common
{
/// <summary>
/// 功能描述：对Excel报表进行操作
/// 说明：在工程中需要添加 Excel11.0对象库的引用（Office 2000为Excel9.0，Office XP为Excel10.0）；
/// 需要在Dcom中配置Excel应用程序的权限；
/// 服务器需要安装Office2003
/// </summary>
public class ExcelLib
{
#region Variables
private Excel.Application excelApplication = null;
private Excel.Workbooks excelWorkBooks = null;
private Excel.Workbook excelWorkBook = null;
private Excel.Worksheet excelWorkSheet = null;
private Excel.Range excelRange = null;//Excel Range Object,多种用途
private int excelActiveWorkSheetIndex; //活动工作表索引
private string excelOpenFileName = ""; //操作Excel的路径
private string excelSaveFileName = ""; //保存Excel的路径
#endregion
#region Properties
public int ActiveSheetIndex
{
get
{
return excelActiveWorkSheetIndex;
}
set
{
excelActiveWorkSheetIndex = value;
}
}
public string OpenFileName
{
get
{
return excelOpenFileName;
}
set
{
excelOpenFileName = value;
}
}
public string SaveFileName
{
get
{
return excelSaveFileName;
}
set
{
excelSaveFileName = value;
}
}
#endregion
//
//---------------------------------------------------------------------------------------
/// <summary>
/// 构造函数；
/// </summary>
public ExcelLib()
{
excelApplication = null;//Excel Application Object
excelWorkBooks = null;//Workbooks
excelWorkBook = null;//Excel Workbook Object
excelWorkSheet = null;//Excel Worksheet Object
ActiveSheetIndex = 1;//默认值活动工作簿为第一个；设置活动工作簿请参阅SetActiveWorkSheet()
}
/// <summary>
/// 以excelOpenFileName为模板新建Excel文件
/// </summary>
public bool OpenExcelFile(string excelOpenFileName)
{
if (excelApplication != null) CloseExcelApplication();
//检查文件是否存在
if (excelOpenFileName == "")
{
throw new Exception("请选择文件！");
}
if (!File.Exists(excelOpenFileName))
{
throw new Exception(excelOpenFileName + "该文件不存在！");//该异常如何处理，由什么处理？？？？
}
try
{
excelApplication = new Excel.ApplicationClass();
excelWorkBooks = excelApplication.Workbooks;
excelWorkBook = ((Excel.Workbook)excelWorkBooks.Open(excelOpenFileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
excelWorkSheet = (Excel.Worksheet)excelWorkBook.Worksheets[excelActiveWorkSheetIndex];
excelApplication.Visible = false;
return true;
}
catch (Exception e)
{
CloseExcelApplication();
MessageBox.Show("(1)没有安装Excel 2003；(2)或没有安装Excel 2003 .NET 可编程性支持；\n详细信息："
+e.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
//throw new Exception(e.Message);
return false;
}
}
/// <summary>
/// 关闭Excel文件，释放对象；最后一定要调用此函数，否则会引起异常
/// </summary>
/// <param></param>
public void CloseExcelApplication()
{
excelWorkBook.Save();
try
{
excelWorkBooks = null;
excelWorkBook = null;
excelWorkSheet = null;
excelRange = null;
if (excelApplication != null)
{
excelApplication.Workbooks.Close();
excelApplication.Quit();
excelApplication = null;
}
}
finally
{
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}
}
/// <summary>
/// 执行导出 ,请在项目中先引用Mircorsoft Excel library 11.0
/// <param name="ds">要导出的DataSet</param>
/// <param name="strExcelFileName">要导出的文件名</param>
/// </summary>
public void doExport(DataSet ds, string strExcelFileName,string tablename)
{
Excel.Application excel = new Excel.Application();
int rowIndex = 1;
int colIndex = 0;
excel.Application.Workbooks.Add(true);
System.Data.DataTable table = ds.Tables[tablename];
foreach (DataColumn col in table.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}
foreach (DataRow row in table.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in table.Columns)
{
colIndex++;
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
excel.Visible = false;
excel.ActiveWorkbook.SaveAs(strExcelFileName, Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
excel.Quit();
excel = null;
GC.Collect();//垃圾回收
}
}
}
fa_ge 2007-11-14
- 打赏
- 举报
哪不是废话吗?
我都说这个我用过了!!!!!!
-----------------
楼主，就你这态度也来问题
我都说这个我用过了!!!!!!
-----------------
楼主，就你这态度也来问题
supersupper 2007-11-14
- 打赏
- 举报
哪不是废话吗?
我都说这个我用过了!!!!!!
导出的EXCEL文件格式是不标准的
我都说这个我用过了!!!!!!
导出的EXCEL文件格式是不标准的
中国风 2007-11-14
- 打赏
- 举报
EXEC master..xp_cmdshell 'bcp "select ID,Name from test.dbo.ta " queryout d:\temp1.xls -c -q -S"roy" -U"sa" -P"111"'
---用语句导时用queryout
---用语句导时用queryout
中国风 2007-11-14
- 打赏
- 举报
EXEC master..xp_cmdshell 'bcp test.dbo.admin out d:\temp1.xls -c -q -S"roy" -U"sa" -P"111"'
