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