登 录
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Export3.aspx.cs" Inherits="XML2Excel.Export3" %></p> <p><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"></p> <p><html xmlns="http://www.w3.org/1999/xhtml" ><br /> <head runat="server"><br /> <title>汇总统计</title><br /> </head><br /> <body><br /> <form id="form1" runat="server"><br /> <div></p> <p> <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Button" /></p> <p> <br /><br /> <asp:Label ID="Label1" runat="server" Text="统计仪号"></asp:Label><br /> <asp:TextBox ID="txtDevNo" runat="server"></asp:TextBox><br /> <asp:Label ID="Label2" runat="server" Text="站点名称"></asp:Label><br /> <asp:TextBox ID="txtname" runat="server"></asp:TextBox><br /> <br /><br /> <asp:Label ID="Label3" runat="server" Text="额定电压"></asp:Label><br /> <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox><br /> <asp:Label ID="Label4" runat="server" Text="打印日期"></asp:Label><br /> <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox><br /> <asp:Label ID="Label5" runat="server" Text="统计期限"></asp:Label><br /> <asp:TextBox ID="TextBox5" runat="server"></asp:TextBox></p> <p> <br /></p> <p> <br /><br /> <asp:Label ID="Label6" runat="server" Text="主管"></asp:Label><br /> <asp:TextBox ID="txtzhuguan" runat="server"></asp:TextBox><br /> <asp:Label ID="Label7" runat="server" Text="负责人"></asp:Label><br /> <asp:TextBox ID="txtfuzeren" runat="server"></asp:TextBox><br /> <asp:Label ID="Label8" runat="server" Text="制表"></asp:Label><br /> <asp:TextBox ID="txtzhibiaoren" runat="server"></asp:TextBox><br /> </div><br /> </form><br /> </body><br /> </html>
设计图:
using System;<br /> using System.Collections;<br /> using System.Configuration;<br /> using System.Data;<br /> using System.Linq;<br /> using System.Web;<br /> using System.Web.Security;<br /> using System.Web.UI;<br /> using System.Web.UI.HtmlControls;<br /> using System.Web.UI.WebControls;<br /> using System.Web.UI.WebControls.WebParts;<br /> using System.Xml.Linq;</p> <p>using System.Runtime.InteropServices;<br /> using System.IO;<br /> using Microsoft.Office.Interop.Excel;<br /> using System.Reflection;<br /> using System.Data.SqlClient;</p> <p>namespace XML2Excel<br /> {<br /> public partial class Export3 : System.Web.UI.Page<br /> {<br /> ExcelOperate excelOperate = new ExcelOperate();</p> <p> public static readonly string ConnectionStrings = ConfigurationManager.ConnectionStrings["DLTDBConnectionString"].ConnectionString;</p> <p> protected void Page_Load(object sender, EventArgs e)<br /> {</p> <p> }</p> <p> void InportExcel()<br /> {<br /> string save_path = "", tick = "";<br /> ExcelOperate excelOperate = new ExcelOperate();<br /> string temp_path = Server.MapPath("xls_files");//生成的文件存放路径<br /> string template_path = Server.MapPath("xls_template");//模板路径<br /> string url = ConfigurationManager.AppSettings["path"].ToString();</p> <p> if (!Directory.Exists(temp_path))<br /> {<br /> Directory.CreateDirectory(temp_path);// 创建指定路径中的所有目录。<br /> Directory.CreateDirectory(template_path);<br /> }<br /> try<br /> {<br /> DataSet ds = bindGrid();<br /> //DataTable dt = ds.Tables[0];<br /> //DataView dv = ds.Tables[0] as DataView;<br /> //如果使用不采用任何参数的构造函数来创建 DataView，那么在设置 Table 属性之前，将无法使用 DataView<br /> DataView dv = ds.Tables[0].DefaultView;</p> <p> //建立一个Excel.Application的新进程<br /> Application app = new Application();<br /> if (app == null)<br /> {<br /> return;<br /> }<br /> app.Visible = false;<br /> app.UserControl = true;<br /> Workbooks workbooks = app.Workbooks;<br /> //_Workbook workbook = workbooks.Add(template_path + "//EXCEL测试模板.xls");//这里的Add方法里的参数就是模板的路径<br /> _Workbook workbook = workbooks.Add(template_path + "//TotalHZ.xls");<br /> Sheets sheets = workbook.Worksheets;<br /> _Worksheet worksheet = (_Worksheet)sheets.get_Item(1);//模板只有一个sheet表<br /> if (worksheet == null)<br /> {<br /> return;<br /> }</p> <p> string str="";//截取年月<br /> string temp = "";<br /> string strMonth = "";</p> <p> worksheet.Cells[2, 2] = txtDevNo.Text;//统计仪编号<br /> worksheet.Cells[2, 5] = txtname.Text;//站点名称<br /> worksheet.Cells[2, 8] = TextBox3.Text;//额定电压<br /> worksheet.Cells[2, 10] = TextBox4.Text;//打印日期<br /> worksheet.Cells[2, 13] = TextBox5.Text;//统计日期</p> <p> for (int i = 0; i < dv.Table.Columns.Count; i++)//列的总行数<br /> {</p> <p> for (int j = 0; j < dv.Table.Rows.Count; j++)//行的总行数<br /> {<br /> str = dv[j].Row[1].ToString();//获取月统计表的time<br /> temp = str.Remove(0, str.IndexOf("-") + 1);<br /> strMonth = temp.Substring(0, temp.IndexOf("-"));//获得月份</p> <p> if (i > 1)<br /> {//前面有两列不要显示，所以开始列是从2行，2列开始记录<br /> worksheet.Cells[2 + i, Convert.ToInt16(strMonth) + 1] = dv[0].Row[i].ToString();<br /> }</p> <p> }<br /> }</p> <p> worksheet.Cells[28, 2] = txtzhuguan.Text;<br /> worksheet.Cells[28, 5] = txtfuzeren.Text;<br /> worksheet.Cells[28, 8] = txtzhibiaoren.Text;</p> <p> //if (i > 1)<br /> //{<br /> // //for (int j = 2; j < dv.Table.Rows.Count+2; j++)<br /> // //{//总记录行数</p> <p> // worksheet.Cells[2 + i, Convert.ToInt16(strMonth)+1] = dv[0].Row[i].ToString();<br /> // //}<br /> // //前面有两列不要显示，所以开始列是从2行，2列开始记录<br /> // //worksheet.Cells[2 + i, 2] = dv[0].Row[i].ToString();//Cells[几行，几列]<br /> // ////原始数据正规格式从4行，2列开始<br /> // //worksheet.Cells[2 + i, 3] = dv[1].Row[i].ToString();<br /> // //worksheet.Cells[2 + i, 4] = dv[2].Row[i].ToString();<br /> // //worksheet.Cells[2 + i, 5] = dv[3].Row[i].ToString();<br /> // //worksheet.Cells[2 + i, 6] = dv[4].Row[i].ToString();<br /> // //worksheet.Cells[2 + i, 7] = dv[5].Row[i].ToString();<br /> // //worksheet.Cells[2 + i, 8] = dv[6].Row[i].ToString();<br /> // //worksheet.Cells[2 + i, 9] = dv[7].Row[i].ToString();<br /> // //worksheet.Cells[2 + i, 10] = dv[8].Row[i].ToString();<br /> // //worksheet.Cells[2 + i, 11] = dv[9].Row[i].ToString();<br /> // //worksheet.Cells[2 + i, 12] = dv[10].Row[i].ToString();<br /> // //worksheet.Cells[2 + i, 13] = dv[11].Row[i].ToString();<br /> //}</p> <p> //excelOperate.SetBold(worksheet, worksheet.Cells[4 + i, 1], worksheet.Cells[4 + i, 1]); //黑体<br /> //excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[4 + i, 1], worksheet.Cells[4 + i, 3]);//居中<br /> //worksheet.get_Range(worksheet.Cells[3 + i, 1], worksheet.Cells[3 + i, 3]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);</p> <p> tick = DateTime.Now.Ticks.ToString();<br /> save_path = temp_path + "//" + tick + ".xls";<br /> workbook.SaveAs(save_path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);<br /> excelOperate.Dispose(worksheet, workbook, app);//关闭Excel进程<br /> }<br /> catch<br /> {<br /> Response.Write("Error");<br /> }<br /> finally<br /> {<br /> Response.End();<br /> }</p> <p> }</p> <p> protected void Button1_Click(object sender, EventArgs e)<br /> {<br /> InportExcel();<br /> }<br /> DataSet bindGrid()<br /> {<br /> SqlConnection sqlconn = new SqlConnection(ConnectionStrings);<br /> sqlconn.Open();<br /> //where DevNo=" + txtDevNo.Text + "<br /> string sql = "select * from MonthStats where DevNo=" + txtDevNo.Text + " order by [Time] asc ";//这里一定按顺序对号入座<br /> SqlCommand cmd = new SqlCommand(sql, sqlconn);<br /> SqlDataAdapter adpater = new SqlDataAdapter(cmd);<br /> DataSet ds = new DataSet();<br /> adpater.Fill(ds);<br /> return ds;<br /> }<br /> }<br /> }<br />
效果图
源码下载地址：http://d.download.csdn.net/down/3049225/suntanyong88
抱歉!评论已关闭.
