数据导出到excel模板 100分 求救啊!!!
要将数据库中的数据导出到excel模板中 C#2005 SQL 2005 经理要做成通用的组件,。。。。
而且数据可能不在一个表中 比如一个表里存的是一个数据的代码而另一个表里存的是汉字
哪位仁兄 给个完整的例子啊 !!!!!!
[解决办法]
将数据库中的数据导出到excel模板中 这个还知道,但是要做成通用的组件,。。。。
而且数据可能不在一个表中 比如一个表里存的是一个数据的代码而另一个表里存的是汉字
这句话偶看不懂,你是要什么样。。的
[解决办法]
把我后面发的这些写到一个类里面,引用一下,前面的那段就能用了~~~
[解决办法]
俺不会表达,只能这样乱发一通了
[解决办法]
给他发邮箱不得了
[解决办法]
先用一个表存放要导入到excel的字段,
设计表字段如下:
报表名称,视图(表)名,字段名,中文名,行号,列号,对齐方式,小数位数,汇总行,是否列表,表尾汇总,等等你想要设置的
在写一个方法去读相应的添到excel中,如下
方法getDnum()为保留小数位数格式设定,自己根据情况可以自己写一个
/// <summary>
/// 打印导出Excel
/// </summary>
/// <param name="dt"> 要打印的Datatable</param>
/// <param name="rptname">模版中的Excel的名称</param>
public static void printexcel(System.Data.DataTable dt, string rptname)
{
string adress = System.Web.HttpContext.Current.Server.MapPath("~\\REPORT" + "\\" + rptname + ".xls");
if (!File.Exists(adress))
{
System.Web.HttpContext.Current.Response.Write("<script>alert('模版中不存在要打印的模版,请先设置要打印的模版')</script>");
}
else
{
Excel.ApplicationClass myApp = null;
Excel.Workbook myBook = null;
Excel.Worksheet mySheet = null;
myApp = new ApplicationClass();
myApp.Visible = false;
//如果文件不存在,则将模板文件拷贝一份作为输出文件
if (File.Exists(outFilePath))
{
try
{
File.Delete(outFilePath);
}
catch
{
throw;
}
}
File.Copy(adress, outFilePath, true);
File.SetAttributes("C:\\myexcel.xls", FileAttributes.Normal);
//写入Excel时,要用到的。
//myBook = (Excel.WorkbookClass)myApp.Workbooks.Add(System.Reflection.Missing.Value);
object oMissiong = System.Reflection.Missing.Value;
myApp.Workbooks.Open(outFilePath, oMissiong, oMissiong, oMissiong, oMissiong,
oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong,
oMissiong, oMissiong, oMissiong);
myBook = myApp.Workbooks[1];
mySheet = (Worksheet)myBook.ActiveSheet;
ClothesERP.SQLServerDAL.Excelset exc = new ClothesERP.SQLServerDAL.Excelset();
int j = 0;
IList<ClothesERP.Model.Excelset> list = exc.GetListArray("rptname='" + rptname + "'");
foreach (ClothesERP.Model.Excelset ex in list)
{
j = 0;
string countstring = "";
Double countnum = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
if (ex.Is_list && i == 0)
{
if (dt.Rows[i][ex.Field_name.ToString()].GetType() == System.Type.GetType("System.String"))
{
mySheet.Cells[Convert.ToInt32(ex.ROW_NO), Convert.ToInt32(ex.COL_NO)] = "'" + dt.Rows[i][ex.Field_name.ToString()].ToString();
}
else
{
mySheet.Cells[Convert.ToInt32(ex.ROW_NO), Convert.ToInt32(ex.COL_NO)] = dt.Rows[i][ex.Field_name.ToString()].ToString();
}
j = Convert.ToInt32(ex.ROW_NO);
switch (ex.Alignment_way)
{
case "中":
mySheet.get_Range(mySheet.Cells[Convert.ToInt32(ex.ROW_NO), Convert.ToInt32(ex.COL_NO)], mySheet.Cells[Convert.ToInt32(ex.ROW_NO), Convert.ToInt32(ex.COL_NO)]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
break;
case "右":
mySheet.get_Range(mySheet.Cells[Convert.ToInt32(ex.ROW_NO), Convert.ToInt32(ex.COL_NO)], mySheet.Cells[Convert.ToInt32(ex.ROW_NO), Convert.ToInt32(ex.COL_NO)]).HorizontalAlignment = XlHAlign.xlHAlignRight;
break;
case "左":
mySheet.get_Range(mySheet.Cells[Convert.ToInt32(ex.ROW_NO), Convert.ToInt32(ex.COL_NO)], mySheet.Cells[Convert.ToInt32(ex.ROW_NO), Convert.ToInt32(ex.COL_NO)]).HorizontalAlignment = XlHAlign.xlHAlignLeft;
break;
default:
break;
}
if (ex.Decimal_num != 0)
{
mySheet.get_Range(mySheet.Cells[Convert.ToInt32(ex.ROW_NO), Convert.ToInt32(ex.COL_NO)], mySheet.Cells[Convert.ToInt32(ex.ROW_NO), Convert.ToInt32(ex.COL_NO)]).NumberFormatLocal = ("'" + getDnum(ex.Decimal_num) + "'");
}
}
[解决办法]
直接给你按钮的源码吧..
string Sql = "";
string condition = "";
Sql = "SELECT rank() OVER( ORDER BY a.terminalnum ) AS id ,a.terminalnum,case when a.terminaltype='1' then 'VIP终端' when a.terminaltype='0' then '普通终端' end as terminaltype,";
Sql += " c.terminaltype as HolderType,a.terminalopentime FROM terminalInfo a,terminaltype c WHERE a.holdertype=c.typeid ";
if (this.ChkTelphone.Checked)
{
if (this.TxtPhone.Text.Length != 0)
{
condition += " and a.terminalnum='" + this.TxtPhone.Text + "' ";
}
}
if (this.ChkID.Checked)
{
if (this.TxtID.Text.Length != 0)
{
condition += "and a.Terminalid='" + this.TxtID.Text + "'";
// Sql += condition;
//Response.Write(Sql);
}
}
if (this.ChkLogic.Checked)
{
if (this.TxtLogic.Text.Length != 0)
{
condition += "and a.Terminalbankno='" + this.TxtLogic.Text + "'";
}
}
if (this.ChkPsam.Checked)
{
if (this.TxtPsam.Text.Length != 0)
{
condition += " and a.TerminalPSAMID='" + this.TxtPsam.Text + "' ";
}
}
Sql += condition;
DataTable DT = pstnpay.DBOperate.SelectFormForum(Sql);
string newFileName = DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
//Response.Write(newFileName);
newFileName = Server.MapPath("Temp/" + newFileName);
File.Copy(Server.MapPath("Module02.xls"), newFileName, true);
string Strconn = "provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + newFileName + ";Extended Properties='Excel 8.0'";
OleDbConnection Conn = new OleDbConnection(Strconn);
Conn.Open();
OleDbCommand Cmd = new OleDbCommand("", Conn);
foreach (DataRow DR in DT.Rows)
{
string XSqlString = "INSERT INTO [Sheet1$]";
XSqlString += "([序号],[电话号码],[用户类型],[终端类型],[开通时间]) values (";
XSqlString += "'" + DR["id"] + "',";
XSqlString += "'" + DR["terminalnum"] + "',";
XSqlString += "'" + DR["terminaltype"] + "',";
XSqlString += "'" + DR["HolderType"] + "',";
XSqlString += "'" + DR["terminalopentime"] + "')";
Cmd.CommandText = XSqlString;
Cmd.ExecuteNonQuery();
}
Conn.Close();
System.IO.FileStream Reader = System.IO.File.OpenRead(newFileName);
long Length = Reader.Length;
Response.Buffer = true;
Response.AppendHeader("Connection", "Keep-Alive");
Response.AppendHeader("Content-Disposition", "attachment;filename=" + Server.UrlEncode("终端查询.xls"));
Response.AppendHeader("Content-Length", Length.ToString());
byte[] Buffer = new byte[10000];
int ByteToRead;
while (Length > 0)
{
if (Response.IsClientConnected)
{
ByteToRead = Reader.Read(Buffer, 0, 10000);
Response.OutputStream.Write(Buffer, 0, ByteToRead);
Response.Flush();
Length -= ByteToRead;
}
else
{
Length = -1;
}
}
Reader.Close();
File.Delete(newFileName);
}不过你需要在目录里建立文件夹,并且建立一个excel文档
[解决办法]
ding