哪位大侠有现成得用Excel模板生成报表得程序

切换到代码窗口中，在文件头添加下面两个引用：
using System.IO;
using System.Reflection;
再添加一个二维数组来表示数据表：
private string [,] myData=
{
{"车牌号","类型","品牌","型号","颜色","附加费证号","车架号"},
{"浙KA3676","危险品","货车","铁风SZG9220YY","白","1110708900","022836"},
{"浙KA4109","危险品","货车","解放CA4110P1K2","白","223132","010898"},
{"浙KA0001A","危险品","货车","南明LSY9190WS","白","1110205458","0474636"},
{"浙KA0493","上普货","货车","解放LSY9190WS","白","1110255971","0094327"},
{"浙KA1045","普货","货车","解放LSY9171WCD","蓝","1110391226","0516003"},
{"浙KA1313","普货","货车","解放9190WCD","蓝","1110315027","0538701"},
{"浙KA1322","普货","货车","解放LSY9190WS","蓝","24323332","0538716"},
{"浙KA1575","普货","货车","解放LSY9181WCD","蓝","1110314149","0113018"},
{"浙KA1925","普货","货车","解放LSY9220WCD","蓝","1110390626","00268729"},
{"浙KA2258","普货","货车","解放LSY9220WSP","蓝","111048152","00320"}
};
切换回设计窗口，双击“普通报表”按钮，设计普通报表，代码如下：
private void btnNormal_Click(object sender, System.EventArgs e)
{
//创建一个Excel文件
Excel.Application myExcel = new Excel.Application ( ) ;
myExcel.Application.Workbooks.Add ( true ) ;
//让Excel文件可见
myExcel.Visible=true;
//第一行为报表名称
myExcel.Cells[1,4]="普通报表";
//逐行写入数据，
for(int i=0;i<11;i++)
{
for(int j=0;j<7;j++)
{
//以单引号开头，表示该单元格为纯文本
myExcel.Cells[2+i,1+j]="'"+myData[i,j];
}
} }

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;using System.IO;
using System.Reflection;namespace MyExcel
{
/// <summary>
/// Form1 的摘要说明。
/// </summary>
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.Button btnNormal;
private System.Windows.Forms.Button btnAdvace;
/// <summary>
/// 必需的设计器变量。
/// </summary>
private System.ComponentModel.Container components = null; public Form1()
{
//
// Windows 窗体设计器支持所必需的
//
InitializeComponent(); //
// TODO: 在 InitializeComponent 调用后添加任何构造函数代码
//
} /// <summary>
/// 清理所有正在使用的资源。
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
} #region Windows Form Designer generated code
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.btnNormal = new System.Windows.Forms.Button();
this.btnAdvace = new System.Windows.Forms.Button();
this.SuspendLayout();
//
// btnNormal
//
this.btnNormal.Location = new System.Drawing.Point(49, 55);
this.btnNormal.Name = "btnNormal";
this.btnNormal.TabIndex = 0;
this.btnNormal.Text = "普通报表";
this.btnNormal.Click += new System.EventHandler(this.btnNormal_Click);
//
// btnAdvace
//
this.btnAdvace.Location = new System.Drawing.Point(169, 55);
this.btnAdvace.Name = "btnAdvace";
this.btnAdvace.TabIndex = 1;
this.btnAdvace.Text = "高级报表";
this.btnAdvace.Click += new System.EventHandler(this.btnAdvace_Click);
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
this.ClientSize = new System.Drawing.Size(292, 133);
this.Controls.AddRange(new System.Windows.Forms.Control[] {
this.btnAdvace,
this.btnNormal});
this.Name = "Form1";
this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
this.Text = "Form1";
this.ResumeLayout(false); }
#endregion /// <summary>
/// 应用程序的主入口点。
/// </summary>
[STAThread]
static void Main()
{
Application.Run(new Form1());
} private string [,] myData=
{
{"车牌号","类型","品牌","型号","颜色","附加费证号","车架号"},
{"浙KA3676","危险品","货车","铁风SZG9220YY","白","1110708900","022836"},
{"浙KA4109","危险品","货车","解放CA4110P1K2","白","223132","010898"},
{"浙KA0001A","危险品","货车","南明LSY9190WS","白","1110205458","0474636"},
{"浙KA0493","上普货","货车","解放LSY9190WS","白","1110255971","0094327"},
{"浙KA1045","普货","货车","解放LSY9171WCD","蓝","1110391226","0516003"},
{"浙KA1313","普货","货车","解放9190WCD","蓝","1110315027","0538701"},
{"浙KA1322","普货","货车","解放LSY9190WS","蓝","24323332","0538716"},
{"浙KA1575","普货","货车","解放LSY9181WCD","蓝","1110314149","0113018"},
{"浙KA1925","普货","货车","解放LSY9220WCD","蓝","1110390626","00268729"},
{"浙KA2258","普货","货车","解放LSY9220WSP","蓝","111048152","00320"}
}; //普通报表，即单纯的文件导出功能
private void btnNormal_Click(object sender, System.EventArgs e)
{
//创建一个Excel文件
Excel.Application myExcel = new Excel.Application ( ) ;
myExcel.Application.Workbooks.Add ( true ) ;
//让Excel文件可见
myExcel.Visible=true;
//第一行为报表名称
myExcel.Cells[1,4]="普通报表";
//逐行写入数据，
for(int i=0;i<11;i++)
{
for(int j=0;j<7;j++)
{
//以单引号开头，表示该单元格为纯文本
myExcel.Cells[2+i,1+j]="'"+myData[i,j];
}
} }
//高级报表，根据模板生成的报表
private void btnAdvace_Click(object sender, System.EventArgs e)
{
string filename="";
//将模板文件复制到一个新文件中
SaveFileDialog mySave=new SaveFileDialog();
mySave.Filter="Excel文件(*.XLS)|*.xls|所有文件(*.*)|*.*";
if(mySave.ShowDialog()!=DialogResult.OK)
{
return;
}
else
{
filename=mySave.FileName;
//将模板文件copy到新位置，建议实际开发时用相对路径，如Application.StartupPath.Trim()+"\\report\\normal.xls"

string filenameold=mySave.FileName;
FileInfo mode=new FileInfo("d:\\normal.xls");
try
{
mode.CopyTo(filename,true);
}
catch(Exception ee)
{
MessageBox.Show(ee.Message);
return;
} } //打开复制后的文件
object missing=Missing.Value;
Excel.Application myExcel=new Excel.Application ( );
//打开新文件
myExcel.Application.Workbooks.Open(filename,
missing,
missing,
missing,
missing,
missing,
missing,
missing,
missing,
missing,
missing,
missing,
missing);
//将Excel显示出来
myExcel.Visible=true; //逐行写入数据，数组中第一行我列标题，忽略
for(int i=1;i<11;i++)
{
for(int j=0;j<7;j++)
{
//以单引号开头，表示该单元格为纯文本
myExcel.Cells[4+i,1+j]="'"+myData[i,j];
}
}
//将列标题和实际内容选中
Excel.Workbook myBook=myExcel.Workbooks[1];
Excel.Worksheet mySheet=(Excel.Worksheet)myBook.Worksheets[1];
Excel.Range r=mySheet.get_Range(mySheet.Cells[3,1],mySheet.Cells[14,7]);
r.Select();
//=====通过执行宏来格表格加边框=======//
try
{
myExcel.Run("宏1",missing,missing, missing,missing,missing,missing,missing,missing,missing, missing,missing,missing,missing,missing,missing,missing, missing,missing,missing,missing,missing,missing,missing, missing,missing,missing,missing,missing,missing,missing);
}
catch
{
}
//保存修改
myBook.Save();
}

}//end of form
}

请问这个东西是那里来得d:\\normal.xls

一个一个单元格赋值速度比较慢，您可以考虑使用系统剪切板，粘贴到Excel里，再用Excel COM 组件里的Excel对象进行画线处理，或者通过IOStream直接导出.xls文件，再通过调用Excel宏进行格式处理。
这里是VB.NET利用系统剪切板的例子            Return stringBuffer
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            oleReader.Close()
            conn.Close()
        End Try
    End Function    Private Sub CreateExcelWorkbook()
        xlApp = New Excel.Application
        xlApp.Visible = True
        xlWorkbook = xlApp.Workbooks.Add()
        xlWorkSheet = xlWorkbook.Sheets(1)
    End Sub    Private Sub Form_Closing(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
        If Not xlApp Is Nothing Then
            xlApp.Quit()
        End If
    End Sub
End Class

不好意思，没贴全，再贴一遍。
Imports Excel
Imports System.Data
Imports System.Data.OleDb
Imports System.IOPublic Class Form1
    Inherits System.Windows.Forms.Form#Region
    Public Sub New()
        MyBase.New()                InitializeComponent()
    End Sub
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub        Private components As System.ComponentModel.IContainer     Friend WithEvents btnPrint As System.Windows.Forms.Button
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.btnPrint = New System.Windows.Forms.Button
        Me.SuspendLayout()
        '
        'btnPrint
        '
        Me.btnPrint.Location = New System.Drawing.Point(112, 232)
        Me.btnPrint.Name = "btnPrint"
        Me.btnPrint.TabIndex = 0
        Me.btnPrint.Text = "Print"
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 12)
        Me.ClientSize = New System.Drawing.Size(292, 273)
        Me.Controls.Add(Me.btnPrint)
        Me.Name = "Form1"
        Me.Text = "Form1"
        Me.ResumeLayout(False)    End Sub#End Region    Private xlApp As Excel.Application
    Private xlWorkbook As Excel.Workbook
    Private xlWorkSheet As Excel.Worksheet
    Private Sub btnPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrint.Click
        Dim appPath As String = System.Environment.CurrentDirectory
        Dim dbPath As String = appPath.Substring(0, appPath.LastIndexOf("\")) + "\db\"
        Dim dataSource As String = dbPath + "data.mdb"
        Dim tableName As String = "data"
        'GetDataFromDB(dataSource, tableName)
        CreateExcelWorkbook()
        CopyToClipboard(GetDataFromDB(dataSource, tableName))
    End Sub    Public Sub CopyToClipboard(ByVal strData As String)
        Try
            Clipboard.SetDataObject("")
            Clipboard.SetDataObject(strData)
            Dim oneCell As Range = xlWorkSheet.Cells(1, 1)
            oneCell.Select()
            xlWorkSheet.Paste()
            xlWorkSheet.UsedRange.Columns.AutoFit()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            Clipboard.SetDataObject("")
        End Try
    End Sub    Public Function GetDataFromDB(ByVal dataSource As String, ByVal tabName As String) As String
        Dim strConn As String = "Provider=Microsoft.Jet.OleDb.4.0;" + "Data Source=" + dataSource
        Dim conn As OleDbConnection = New OleDbConnection(strConn)
        Dim strSql As String = "select * from " + tabName
        Dim oleComm As OleDbCommand = New OleDbCommand(strSql, conn)
        Dim oleReader As OleDbDataReader
        Dim stringBuffer As String = ""
        Try
            conn.Open()
            oleReader = oleComm.ExecuteReader()
            While oleReader.Read
                For i As Integer = 0 To oleReader.FieldCount - 1
                    If stringBuffer.Length = 0 Then 'Write header to excel
                        stringBuffer += oleReader.GetName(i).ToString() + Chr(9) 'vbtab
                    End If
                    If IsDBNull(oleReader.GetValue(i)) Then
                        stringBuffer += " "
                    Else
                        stringBuffer += oleReader.GetString(i) + Chr(9)
                    End If
                    If i = oleReader.FieldCount - 1 Then
                        stringBuffer += Chr(13)
                    End If
                Next
            End While
            Return stringBuffer
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            oleReader.Close()
            conn.Close()
        End Try
    End Function    Private Sub CreateExcelWorkbook()
        xlApp = New Excel.Application
        xlApp.Visible = True
        xlWorkbook = xlApp.Workbooks.Add()
        xlWorkSheet = xlWorkbook.Sheets(1)
    End Sub    Private Sub Form_Closing(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing
        If Not xlApp Is Nothing Then
            xlApp.Quit()
        End If
    End Sub
End Class

调试易

哪位大侠有现成得用Excel模板生成报表得程序

解决方案 »