对.NET中导出数据到EXCEL的几种方法探讨 C1TrueDBGrid
来源:互联网 发布:深圳咫尺网络怎么样 编辑:程序博客网 时间:2024/05/22 02:23
最近在做一个报表系统的时候,需要把DATASET中的数据导到EXCEL当中,于是在网上找了一遍,发现了好几种方法,本来以为应该差不多,但后来经过一一试用后,发现在性能上真的差别很大,现在就介绍一下,同时有不对的希望可以指正:
1. 原理:利用office组件把dataset中的数据填充到excel文件当中。
这里我不贴出全部代码了,只把关键部分贴出来:
///<summary>
///方法,导出C1TrueDBGrid中的数据到Excel文件
///</summary>
///<param name="c1grid">C1TrueDBGrid</param>
///<param name="FileName">Excel文件名</param>
public void ExportToExcel(C1.Win.C1TrueDBGrid.C1TrueDBGrid c1grid,string FileName)
{
if(FileName.Trim() == "") return; //验证strFileName是否为空或值无效
int rowscount = c1grid.Splits[0].Rows.Count; //定义表格内数据的行数
int colscount = c1grid.Columns.Count; //定义表格内数据的列数
//行数必须大于0
if (rowscount <= 0)
{
MessageBox.Show("没有数据可供保存","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
return;
}
//列数必须大于0
if (colscount <= 0)
{
MessageBox.Show("没有数据可供保存","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
return;
}
//行数不可以大于65536
if (rowscount > 65536)
{
MessageBox.Show("数据记录数太多(最多不能超过65536条),不能保存","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
return;
}
//列数不可以大于255
if (colscount > 255)
{
MessageBox.Show("数据记录行数太多,不能保存","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
return;
}
//将文件保存到工作路径的子目录“/Excel”下,如果路径不存在创建它
string n_path = Directory.GetCurrentDirectory() + "//Excel";
if (Directory.Exists(n_path) == false)
{
Directory.CreateDirectory(n_path);
}
//验证以strFileName命名的文件是否存在,如果存在删除它
FileInfo fi = new FileInfo(n_path + "//" + FileName + ".xls");
if(fi.Exists)
{
try
{
fi.Delete();
}
catch(Exception fie)
{
MessageBox.Show(fie.Message,"删除失败", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
}
Excel.ApplicationClass excel = null;
try
{
//显示进度条
KP.PublicForm.ProgressBar pb = new PublicForm.ProgressBar("导出进度");
pb.Show();
pb.Refresh();
//新建Excel应用,新建Workbook文件
excel = new Excel.ApplicationClass ( ) ;
Excel.XlSaveAsAccessMode savemode = new Excel.XlSaveAsAccessMode();
excel.Application.Workbooks.Add (true) ;
//向Excel中写入表格的表头
int i = 1;
for(int c = 0;c < colscount; c++)
{
if(c1grid.Splits[0].DisplayColumns[c].Visible)
{
excel.Cells[1,i] = c1grid.Columns[c].Caption;
i++;
}
}
//向Excel中逐行逐列写入表格中的数据
for(int r = 0; r < rowscount; r++)
{
Application.DoEvents();
pb.SetProgressBarValue(r+1, rowscount);
if(pb.Cancel)
{
break;
}
int j = 1;
for(int c = 0;c < colscount; c++)
{
if(c1grid.Splits[0].DisplayColumns[c].Visible)
{
excel.Cells[r + 2,j] = c1grid.Columns[c].CellText(r);
j++;
}
}
}
//向Excel中写入表格的脚
if(c1grid.ColumnFooters)
{
int col = 1;
for(int c = 0;c < colscount; c++)
{
if(c1grid.Splits[0].DisplayColumns[c].Visible)
{
if(c1grid.Columns[c].FooterText != null && c1grid.Columns[c].FooterText.Trim() != "")
{
excel.Cells[rowscount + 2,col] = c1grid.Columns[c].FooterText;
}
col++;
}
}
}
//关闭进度条
pb.Close();
//设置Excel的默认保存路径为当前路径下的Excel子文件夹
excel.DefaultFilePath = n_path;
//保存文件
excel.ActiveWorkbook.SaveAs(FileName + ".xls",excel.ActiveWorkbook.FileFormat,"","",excel.ActiveWorkbook.ReadOnlyRecommended,excel.ActiveWorkbook.CreateBackup,savemode,excel.ActiveWorkbook.ConflictResolution,false,"","");
}
catch(Exception e1)
{
MessageBox.Show(e1.Message, "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
finally
{
//关闭Excel应用
if(excel != null) excel.Quit();
}
MessageBox.Show(FileName + "导出完毕,在" + Application.StartupPath + "//Excel文件夹下","提示", MessageBoxButtons.OK,MessageBoxIcon.Information);
// }
}
总结:这个方法是可以解决问题,但效率最差,3000条长点的record就用了6分钟,晕~~~~
2. 原理:利用office组件,同时把dataset的数据导到Clipboard中,然后通过粘贴到excel中。
Excel.XlSaveAsAccessMode savemode = new Excel.XlSaveAsAccessMode();
xlApp.Application.Workbooks.Add (true) ;
xlApp.DefaultFilePath = @"c:/";
xlApp.ActiveWorkbook.SaveAs("exportExcel.xls",xlApp.ActiveWorkbook.FileFormat,"","",xlApp.ActiveWorkbook.ReadOnlyRecommended,xlApp.ActiveWorkbook.CreateBackup,savemode,xlApp.ActiveWorkbook.ConflictResolution,false,"","","");
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filePath, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing,oMissing,oMissing);
Excel.Worksheet xlWorksheet;
// 循环所有DataTable
for( int i=0; i<ds.Tables.Count; i++ )
{
xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.Add(oMissing,oMissing,1,oMissing);
// 以TableName作为新加的Sheet页名。
xlWorksheet.Name = ds.Tables[i].TableName;
// 取出这个DataTable中的所有值,暂存于stringBuffer中。
string stringBuffer = "";
//向Excel中写入表格的表头
if(node != null)
{
XmlNode nodec=node.SelectSingleNode("./Method/ShowField");
int ii = 1;
foreach(XmlNode xnode in nodec.ChildNodes )
{
xlApp.Cells[1,ii] =xnode.Attributes["displayname"].Value;
ii++;
}
for( int j=0; j<ds.Tables[i].Rows.Count; j++ )
{
for( int k=0; k<ds.Tables[i].Columns.Count; k++ )
{
stringBuffer += ds.Tables[i].Rows[j][k].ToString();
if( k < ds.Tables[i].Columns.Count - 1 )
stringBuffer += "/t";
}
stringBuffer += "/n";
}
}
else
{
int ii = 1;
for(int c = 0;c<ds.Tables[i].Columns.Count; c++)
{
xlApp.Cells[1,ii] = ds.Tables[i].Columns[c].Caption;
ii++;
}
for( int j=0; j<ds.Tables[i].Rows.Count; j++ )
{
for( int k=0; k<ds.Tables[i].Columns.Count; k++ )
{
stringBuffer += ds.Tables[i].Rows[j][k].ToString();
if( k < ds.Tables[i].Columns.Count - 1 )
stringBuffer += "/t";
}
stringBuffer += "/n";
}
}
System.Windows.Forms.Clipboard.SetDataObject("");
// 将stringBuffer放入剪切板。
System.Windows.Forms.Clipboard.SetDataObject(stringBuffer);
// 选中这个sheet页中的第一个单元格
((Excel.Range)xlWorksheet.Cells[2,1]).Select();
// 粘贴!
xlWorksheet.Paste(oMissing,oMissing);
// 清空系统剪切板。
System.Windows.Forms.Clipboard.SetDataObject("");
}
// 保存并关闭这个工作簿。
xlApp.ActiveWorkbook.Close( Excel.XlSaveAction.xlSaveChanges, oMissing, oMissing );
// xlWorkbook.Close( Excel.XlSaveAction.xlSaveChanges, oMissing, oMissing );
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);
xlWorkbook = null;
MessageBox.Show(@"Excel文件:C:/exportExcel.xls 导出成功!");
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
// 释放...
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
GC.Collect();
}
总结:这个方法比上面的方法性能好点,但还是很不好用,比原来的提高了2倍左右。
3. 原理:利用OLEDB,以excel为数据库,把dataset中的数据导入到excel文件中
public static void exportToExcelByDataset(string filePath, DataSet ds,XmlNode node)
{
string sqlstr;
if(fi.Exists)
{
fi.Delete();
// throw new Exception("文件删除失败");
}
else
{
fi.Create();
}
string sqlcon=@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended ProPerties=Excel 8.0;";
OleDbConnection olecon = new OleDbConnection(sqlcon);
OleDbCommand olecmd = new OleDbCommand();
olecmd.Connection = olecon;
olecmd.CommandType = CommandType.Text;
try
{
olecon.Open();
XmlNode nodec=node.SelectSingleNode("./Method/ShowField");
int ii = 0;
sqlstr = "CREATE TABLE sheet1(";
foreach(XmlNode xnode in nodec.ChildNodes )
{
if(ii == nodec.ChildNodes.Count - 1)
{
if(xnode.Attributes["type"].Value.ToLower() == "int"||xnode.Attributes["type"].Value.ToLower() == "decimal")
{
sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " number)";
}
else
{
sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " text)";
}
// sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " text)";
}
else
{
if(xnode.Attributes["type"].Value.ToLower() == "int"||xnode.Attributes["type"].Value.ToLower() == "decimal")
{
sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " number,";
}
else
{
sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " text,";
}
}
// sqlstr =sqlstr + xnode.Attributes["displayname"].Value + " text";
ii++;
}
olecmd.CommandText = sqlstr;
olecmd.ExecuteNonQuery();
for(int i=0;i<ds.Tables[0].Rows.Count;i++)
{
sqlstr = "INSERT INTO sheet1 VALUES(";
int jj=0;
foreach(XmlNode inode in nodec.ChildNodes )
{
if(jj == nodec.ChildNodes.Count-1)
{
if(inode.Attributes["type"].Value.ToLower() == "int"||inode.Attributes["type"].Value.ToLower() == "decimal")
{
sqlstr = sqlstr + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString()) + ")" ;
}
else
{
sqlstr = sqlstr + "'" + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString().Replace("'","''")) + "')" ;
}
}
else
{
if(inode.Attributes["type"].Value.ToLower() == "int"||inode.Attributes["type"].Value.ToLower() == "decimal")
{
sqlstr = sqlstr + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString()) + "," ;
}
else
{
sqlstr = sqlstr + "'" + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString().Replace("'","''")) + "'," ;
}
}
jj++;
}
olecmd.CommandText = sqlstr;
olecmd.ExecuteNonQuery();
}
MessageBox.Show(@"Excel文件:" + filePath + " 导出成功!");
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
olecmd.Dispose();
olecon.Close();
olecon.Dispose();
}
}
/// <summary>
/// change to string "null" if input is null
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
private static string isnull(string obj)
{
if(obj.Length >0)
{
return obj;
}
else
{
return "null";
}
}
总结:这个方法是最好的,速度飞快,比上面两种提高不止10倍,而且关键是不需要用到office组件,所以我正在用着这种方法,客户也满意。当然这个也有它不好的地方,有时候会受到导入的数据不符的异常困扰,而且为了赶时间,代码写的不好,一句话,能用但要改进的地方很多:)
///2007-03-02
最近发现几个导出到EXCEL的方法,这里先记录下来
4.本示例是用于将ListView中的内容倒入到Excel 与常用的逐单元格写不同的是,本例子采用数据写入到range的方法。该方法效率明显较高
Excel.Application app = new Excel.ApplicationClass();
if( app == null)
{
MessageBox.Show("Excel无法启动");
return;
}
app.Visible = true;
Excel.Workbooks wbs = app.Workbooks;
Excel.Workbook wb = wbs.Add(Missing.Value);
Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];
Excel.Range r = ws.get_Range("A1","H1");
object [] objHeader = {"标题1","标题2","标题3","标题4","标题5","标题6","标题7","标题8"};
r.Value = objHeader;
if (lv.Items.Count >0)
{
r = ws.get_Range("A2",Missing.Value);
object [,] objData = new Object[this.lv.Items.Count,8];
foreach(ListViewItem lvi in lv.Items)
{
objData[lvi.Index,0] = lvi.Text;
objData[lvi.Index,1] = lvi.SubItems[1].Text;
objData[lvi.Index,2] = lvi.SubItems[2].Text;
objData[lvi.Index,3] = lvi.SubItems[3].Text;
objData[lvi.Index,4] = lvi.SubItems[4].Text;
objData[lvi.Index,5] = lvi.SubItems[5].Text;
objData[lvi.Index,6] = lvi.SubItems[6].Text;
objData[lvi.Index,7] = lvi.SubItems[7].Text;
}
r = r.get_Resize(lv.Items.Count,8);
r.Value = objData;
r.EntireColumn.AutoFit();
}
app = null;
5.由XML文件导出为EXCEL文件
目录下kfcccer.xml为原始数据XML文件,点击生成后会在同级目录下生成kfcccer.xls文件
页面代码如下:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>XML转换Excel演示</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<div>
<asp:Button ID="btnChange" runat="server" Font-Bold="True" Font-Size="18pt" ForeColor="Black"
Height="38px" OnClick="btnChange_Click" Text="开始转换" Width="203px" /></div>
</div>
</form>
</body>
</html>
后台代码:
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnChange_Click(object sender, EventArgs e)
{
try
{
//要转换的XML文件
string XMLFileName = Path.Combine(Request.PhysicalApplicationPath, "kfcccer.xml");
DataSet dsBook = new DataSet();
dsBook.ReadXml(XMLFileName);
int rows = dsBook.Tables[0].Rows.Count + 1;
int cols = dsBook.Tables[0].Columns.Count;
//将要生成的Excel文件
string ExcelFileName = Path.Combine(Request.PhysicalApplicationPath, "kfcccer.xls");
if (File.Exists(ExcelFileName))
{
File.Delete(ExcelFileName);
}
StreamWriter writer = new StreamWriter(ExcelFileName, false);
writer.WriteLine("<?xml version="1.0"?>");
writer.WriteLine("<?mso-application progid="Excel.Sheet"?>");
writer.WriteLine("<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"");
writer.WriteLine(" xmlns:o="urn:schemas-microsoft-com:office:office"");
writer.WriteLine(" xmlns:x="urn:schemas-microsoft-com:office:excel"");
writer.WriteLine(" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"");
writer.WriteLine(" xmlns:html="http://www.w3.org/TR/REC-html40/">");
writer.WriteLine(" <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">");
writer.WriteLine(" <Author>Automated Report Generator Example</Author>");
writer.WriteLine(string.Format(" <Created>{0}T{1}Z</Created>", DateTime.Now.ToString("yyyy-mm-dd"), DateTime.Now.ToString("HH:MM:SS")));
writer.WriteLine(" <Company>51aspx.com</Company>");
writer.WriteLine(" <Version>11.6408</Version>");
writer.WriteLine(" </DocumentProperties>");
writer.WriteLine(" <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">");
writer.WriteLine(" <WindowHeight>8955</WindowHeight>");
writer.WriteLine(" <WindowWidth>11355</WindowWidth>");
writer.WriteLine(" <WindowTopX>480</WindowTopX>");
writer.WriteLine(" <WindowTopY>15</WindowTopY>");
writer.WriteLine(" <ProtectStructure>False</ProtectStructure>");
writer.WriteLine(" <ProtectWindows>False</ProtectWindows>");
writer.WriteLine(" </ExcelWorkbook>");
writer.WriteLine(" <Styles>");
writer.WriteLine(" <Style ss:ID="Default" ss:Name="Normal">");
writer.WriteLine(" <Alignment ss:Vertical="Bottom"/>");
writer.WriteLine(" <Borders/>");
writer.WriteLine(" <Font/>");
writer.WriteLine(" <Interior/>");
writer.WriteLine(" <Protection/>");
writer.WriteLine(" </Style>");
writer.WriteLine(" <Style ss:ID="s21">");
writer.WriteLine(" <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>");
writer.WriteLine(" </Style>");
writer.WriteLine(" </Styles>");
writer.WriteLine(" <Worksheet ss:Name="MyReport">");
writer.WriteLine(string.Format(" <Table ss:ExpandedColumnCount="{0}" ss:ExpandedRowCount="{1}" x:FullColumns="1"", cols.ToString(), rows.ToString()));
writer.WriteLine(" x:FullRows="1">");
//生成标题
writer.WriteLine("<Row>");
foreach (DataColumn eachCloumn in dsBook.Tables[0].Columns)
{
writer.Write("<Cell ss:StyleID="s21"><Data ss:Type="String">");
writer.Write(eachCloumn.ColumnName.ToString());
writer.WriteLine("</Data></Cell>");
}
writer.WriteLine("</Row>");
//生成数据记录
foreach (DataRow eachRow in dsBook.Tables[0].Rows)
{
writer.WriteLine("<Row>");
for (int currentRow = 0; currentRow != cols; currentRow++)
{
writer.Write("<Cell ss:StyleID="s21"><Data ss:Type="String">");
writer.Write(eachRow[currentRow].ToString());
writer.WriteLine("</Data></Cell>");
}
writer.WriteLine("</Row>");
}
writer.WriteLine(" </Table>");
writer.WriteLine(" <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">");
writer.WriteLine(" <Selected/>");
writer.WriteLine(" <Panes>");
writer.WriteLine(" <Pane>");
writer.WriteLine(" <Number>3</Number>");
writer.WriteLine(" <ActiveRow>1</ActiveRow>");
writer.WriteLine(" </Pane>");
writer.WriteLine(" </Panes>");
writer.WriteLine(" <ProtectObjects>False</ProtectObjects>");
writer.WriteLine(" <ProtectScenarios>False</ProtectScenarios>");
writer.WriteLine(" </WorksheetOptions>");
writer.WriteLine(" </Worksheet>");
writer.WriteLine(" <Worksheet ss:Name="Sheet2">");
writer.WriteLine(" <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">");
writer.WriteLine(" <ProtectObjects>False</ProtectObjects>");
writer.WriteLine(" <ProtectScenarios>False</ProtectScenarios>");
writer.WriteLine(" </WorksheetOptions>");
writer.WriteLine(" </Worksheet>");
writer.WriteLine(" <Worksheet ss:Name="Sheet3">");
writer.WriteLine(" <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">");
writer.WriteLine(" <ProtectObjects>False</ProtectObjects>");
writer.WriteLine(" <ProtectScenarios>False</ProtectScenarios>");
writer.WriteLine(" </WorksheetOptions>");
writer.WriteLine(" </Worksheet>");
writer.WriteLine("</Workbook>");
writer.Close();
Response.Write("<script language="javascript">" + "alert('" + "转换成功! 转换后的Excel文件名为: kfcccer.xls')" + "</script>");
}
catch (Exception ex)
{
Response.Write("<script language="javascript">" + "alert('" + "操作失败! 出错信息: " + ex.Message + "')" + "</script>");
}
}
}
- 对.NET中导出数据到EXCEL的几种方法探讨 C1TrueDBGrid
- 对.NET中导出数据到EXCEL的几种方法探讨 C1TrueDBGrid
- 对.NET中导出数据到EXCEL的几种方法探讨
- 对.NET中导出数据到EXCEL的几种方法探讨
- 对.NET中导出数据到EXCEL的几种方法探讨[转]。http://hi.baidu.com/lanxmail/blog/item/82b3e1bf8608ca0b18d81f12.html
- asp.net导出数据到Excel的几种方法(1/3)
- asp.net导出数据到Excel的几种方法(2/3)
- asp.net导出数据到Excel的几种方法(3/3)
- asp.net导出数据到Excel的几种方法(3/3)
- ASP.NET 数据导出到excel文件 浏览器客户端下载的几种方法
- 导出数据到Excel的几种方法
- 导出数据到Excel的几种方法
- 导出数据到Excel的几种方法
- 导出数据到Excel的几种方法
- 把数据导出到Excel的几种方法说明
- C#导出数据到Excel的几种方法
- [转]导出数据到Excel的几种方法
- C#导出数据到Excel的几种方法
- 循环缓冲区
- hdoj 2156
- 雅虎员工总数达1.8万人
- 指针 一
- 学习java多线程的笔记5--初体验
- 对.NET中导出数据到EXCEL的几种方法探讨 C1TrueDBGrid
- Spring IoC 使用详解
- Eclipse NDK 配置 -无需cygwin
- 3.0 的 U方法 {:U('/Index/Content/id/'.$Ts[0]['list'][0]['id'])}
- JdbcTemplate.queryForList(String sql)
- JfreeChart的使用
- c/c++中const与static的区别
- 浏览器窗口关闭事件的监听搜集
- 【IOS】自己写的一个舒尔特方格app