MyXls是用C#开源项目,可以应用于asp.net 或者 .net应用程序上。它根据微软公开的Excle文档格式文件(BIFF),以二进制格式直接生成excel文档,支持Excel versions 97 - 2007. 。这意味着可以不用在服务器上安装office就能够以excle格式输出数据库中存储的数据了。这对于许多项目来说都是很有用的。
目前MyXls已经实现了单元格(cell)的格式设置,包括文本颜色、文本大小、字体、单位格边框、底色、列宽、行高,合并单元格,多个sheet页等功能。
目前MyXls还不支持在excel文档中生成对象(如、文本框、按钮等)。MyXls主页称即将实现对excel文件的读取功能,个人认为读取的功能的用处还不是很多。
MyXls主页: http://myxls.in2bits.org/wiki/MainPage.ashx
目前MyXls已经实现了单元格(cell)的格式设置,包括文本颜色、文本大小、字体、单位格边框、底色、列宽、行高,合并单元格,多个sheet页等功能。
目前MyXls还不支持在excel文档中生成对象(如、文本框、按钮等)。MyXls主页称即将实现对excel文件的读取功能,个人认为读取的功能的用处还不是很多。
MyXls主页: http://myxls.in2bits.org/wiki/MainPage.ashx
下载页面:http://myxls.in2bits.org/wiki/Downloads.ashx
- using System;
- using System.Data;
- using System.Configuration;
- 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.Data.SqlClient;
- using org.in2bits.MyXls;
- public partial class _Default : System.Web.UI.Page
- {
- protected void Page_Load(object sender, EventArgs e)
- {
- if (!this.IsPostBack)
- {
- BindData();
- }
- }
- public void BindData()
- {
- gridPolice.DataSource = GetPoliceMan();
- gridPolice.DataBind();
- gridCar.DataSource = GetCar();
- gridCar.DataBind();
- }
- public string GetConnectionString()
- {
- return ConfigurationManager.AppSettings["ConnectionString"].ToString();
- }
- public DataTable GetPoliceMan()
- {
- SqlConnection con = new SqlConnection(GetConnectionString());
- SqlDataAdapter da = new SqlDataAdapter("select * from tblPolice", con);
- DataTable dt = new DataTable();
- da.Fill(dt);
- return dt;
- }
- public DataTable GetCarByPoliceID(int ID)
- {
- string strSql =string.Format("select * from tblCar where ID={0}",ID);
- SqlConnection con = new SqlConnection(GetConnectionString());
- SqlDataAdapter da = new SqlDataAdapter(strSql, con);
- DataTable dt = new DataTable();
- da.Fill(dt);
- return dt;
- }
- public DataTable GetCar()
- {
- SqlConnection con = new SqlConnection(GetConnectionString());
- SqlDataAdapter da = new SqlDataAdapter("select * from tblCar", con);
- DataTable dt = new DataTable();
- da.Fill(dt);
- return dt;
- }
- protected void btnCreateExcel_Click(object sender, EventArgs e)
- {
- //System.Data.DataTable exceldt = new System.Data.DataTable();
- //exceldt = Session["Reports"] as System.Data.DataTable;
- XlsDocument xls = new XlsDocument();
- xls.FileName = "Demo.xls";
- //Add file Attribute
- xls.SummaryInformation.Author = "Tim Erickson"; //author
- xls.SummaryInformation.Subject = "A wacky display of Excel file generation";
- xls.DocumentSummaryInformation.Company = "in2bits.org";
- int rowMin = 3;
- int colMin = 1;
- //获取警员资料记录
- DataTable dtPolice = GetPoliceMan();
- DataTable dtCar = GetCar();
- Worksheet sheet = xls.Workbook.Worksheets.AddNamed("Demo");
- MergeArea ma = new MergeArea(1, 2, 1, dtCar.Columns.Count);
- sheet.AddMergeArea(ma);
- Cells cells = sheet.Cells;
- Cell cellTitle = cells.Add(1, 1, "湖南省交警大队报表");
- //设置边框
- //cellTitle.UseBorder = true;
- //cellTitle.TopLineColor = Colors.Red;
- //cellTitle.TopLineStyle = 5;
- //cellTitle.BottomLineColor = Colors.Red;
- //cellTitle.BottomLineStyle = 5;
- //文字居中
- cellTitle.HorizontalAlignment = HorizontalAlignments.Centered;
- cellTitle.VerticalAlignment = VerticalAlignments.Centered;
- cellTitle.Font.Bold = true;
- //cellTitle.Pattern = 1;
- //cellTitle.PatternBackgroundColor = Colors.White;//填充的底色
- //cellTitle.PatternColor = Colors.White;//设定填充线条的颜色
- //设置列属性
- ColumnInfo cinfo = new ColumnInfo(xls, sheet);
- cinfo.Collapsed = true;
- cinfo.ColumnIndexStart =(ushort)colMin;
- cinfo.ColumnIndexEnd = (ushort)(dtCar.Columns.Count-1);
- cinfo.Collapsed = true;
- cinfo.Width = 90*60;
- sheet.AddColumnInfo(cinfo);
- //警员资料
- int num = 0;
- for (int i = 0; i < dtPolice.Rows.Count ; i++)
- {
- int policeID = Convert.ToInt32(dtPolice.Rows[i]["ID"].ToString());
- DataTable dtCarByID = GetCarByPoliceID(policeID);
- for (int r = 0; r < (dtCarByID.Rows.Count + 1); r++)
- {
- if (r == 0)
- {
- //添加列名:
- for (int c = 0; c < dtCar.Columns.Count; c++)
- {
- Cell cellHeader = cells.Add(rowMin + r, colMin + c, dtCar.Columns[c].ColumnName);
- cellHeader.Font.Bold = true;
- cellHeader.HorizontalAlignment = HorizontalAlignments.Centered;
- cellHeader.Pattern = 1;
- cellHeader.PatternColor = Colors.Silver;
- cellHeader.UseBorder = true;
- cellHeader.TopLineStyle = 1;
- cellHeader.TopLineColor = Colors.Silver;
- cellHeader.BottomLineStyle = 1;
- cellHeader.BottomLineColor = Colors.Silver;
- cellHeader.LeftLineStyle = 1;
- cellHeader.LeftLineColor = Colors.Silver;
- cellHeader.RightLineStyle = 1;
- cellHeader.RightLineColor = Colors.Silver;
- }
- }
- else
- {
- for (int c = 0; c < dtCar.Columns.Count; c++)
- {
- Cell cellBody = cells.Add(rowMin + r, colMin + c, dtCarByID.Rows[r + 1 - 2][c].ToString());
- cellBody.UseBorder = true;
- cellBody.TopLineStyle = 1;
- cellBody.TopLineColor = Colors.Silver;
- cellBody.BottomLineStyle = 1;
- cellBody.BottomLineColor = Colors.Silver;
- cellBody.LeftLineStyle = 1;
- cellBody.LeftLineColor = Colors.Silver;
- cellBody.RightLineStyle = 1;
- cellBody.RightLineColor = Colors.Silver;
- }
- }
- }
- //控制行数
- num += dtCarByID.Rows.Count + 2;
- //控制行数
- rowMin += dtCarByID.Rows.Count + 2;
- //添加所属警察
- ma = new MergeArea(num + 2, num + 2, 1, dtCar.Columns.Count);
- sheet.AddMergeArea(ma);
- Cell cellPoliceMan = cells.Add(num + 2, 1, dtPolice.Rows[i]["PoliceName"].ToString() +" " +dtPolice.Rows[i]["Position"].ToString());
- cellPoliceMan.Font.Bold = true;
- }
- xls.Send();
- }
- }
效果图: