我要做一个桌面程序。程序要求大体是这样的:通过一个已有excel表的数据,经过计算,生成另一个excel表(该表有一定的表头格式)。读excel表没有问题,我现在是想问一下如何生成一个带有表头的excel表。
还有一个问题,有没有方法取得一个excel表里的所有表名!
还有一个问题,有没有方法取得一个excel表里的所有表名!
解决方案 »
- 数据库配置和程序配置
- DATAGRIDVIEW显示问题 急急!!
- 如何选中dataset中具体的某一单元格
- C#串口通信
- 在java中,输入一个类的名作为字符串传递过去。然后就能得到该类的实体。请问该功能在C#中如何实现?能否给个实例?
- 有什么控件可以做出个文本框,上面还有很多word的那些按钮(控制文本格式)的么?
- Formview控件的
- 不能引入System.management的问题
- 请问picturebox和button控件的backcolor属性如何设置成为“无”
- 什么情况下会出现这类的异常?
- .Net2005 WebBrowser类自动提交表单源程序
- 征集:VS2005的优点和缺点
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Configuration;namespace HrVote
{
/// <summary>
/// tongji 的摘要说明。
/// </summary>
public class tongji : System.Web.UI.Page
{
protected System.Web.UI.WebControls.DataGrid dgExcel;
protected System.Web.UI.WebControls.Button btExcel;
private void Page_Load(object sender, System.EventArgs e)
{
// 在此处放置用户代码以初始化页面
if(!IsPostBack)
{
BindDataTemp();
//需要重新设计一个dategrid;
}
} #region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.dgExcel.ItemCreated += new System.Web.UI.WebControls.DataGridItemEventHandler(this.dgExcel_ItemCreated);
this.dgExcel.ItemCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.dgExcel_ItemCommand);
this.btExcel.Click += new System.EventHandler(this.btExcel_Click);
this.Load += new System.EventHandler(this.Page_Load); }
#endregion private void btExcel_Click(object sender, System.EventArgs e)
{
string FileName= DateTime.Now.ToString("yyyyMMddHHmmssfffff").ToString();
ToExcel(dgExcel,FileName);
// ToExcel(Table2,FileName);
} private void BindData()
{//// dgExcel.DataSource = new string[] {"a","b","c"};
//// dgExcel.DataBind();
// DataTable myTable=new DataTable();
// DataColumn myColumn = new DataColumn();
// myColumn.Caption = "Price";
// myColumn.ColumnName = "Price";
// myColumn.DefaultValue = 25;
// myTable.Columns.Add(myColumn);
// DataRow myRow;
// for(int i = 0; i < 10; i++)
// {
// myRow = myTable.NewRow(); myRow["Price"] = i + 1;
// myTable.Rows.Add(myRow);
// } DataSet ds= new common.clsDataAccess().RetrieveBySql("select * from hrvote order by id");
this.dgExcel.DataSource=ds.Tables[0].DefaultView;
this.dgExcel.DataBind();
} private void BindDataTemp()
{
DataSet ds= new common.clsDataAccess().RetrieveBySql("select * from hrvote order by id");
DataTable myTable=new DataTable();
if(ds.Tables[0].Rows.Count>0)
{
DataColumn myColumn = new DataColumn();
myColumn.AllowDBNull = false;
myColumn.Caption = "ID";
myColumn.ColumnName = "ID";
myTable.Columns.Add(myColumn);
DataColumn myColumn1 = new DataColumn();
myColumn1.AllowDBNull = false;
myColumn1.Caption = "性别";
myColumn1.ColumnName = "性别";
DataColumn myColumn2 = new DataColumn();
myColumn2.AllowDBNull = false;
myColumn2.Caption = "公司";
myColumn2.ColumnName = "公司"; DataColumn myColumn3 = new DataColumn();
myColumn3.AllowDBNull = false;
myColumn3.Caption = "投票日期";
myColumn3.ColumnName = "投票日期";
myColumn3.MaxLength =20; myTable.Columns.Add(myColumn1);
myTable.Columns.Add(myColumn2);
myTable.Columns.Add(myColumn3);
DataRow dr;
string strTemp=string.Empty;
for(int i=1;i<78;i++)
{
if(i!=2&&i!=9)
{
myTable.Columns.Add(new DataColumn("第"+i+"题" , typeof(string)));
}
} for(int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
myColumn.DefaultValue = ds.Tables[0].Rows[i]["id"].ToString();
myColumn1.DefaultValue = ds.Tables[0].Rows[i]["Sex"].ToString();
myColumn3.DefaultValue = ds.Tables[0].Rows[i]["VoteDate"].ToString();
strTemp=ds.Tables[0].Rows[i]["result"].ToString();
DataRow myRow;
myRow = myTable.NewRow();
string srt2=strTemp.Substring(strTemp.LastIndexOf(",")); //公司名称
myRow["公司"] = srt2.Remove(0,3);
if(strTemp!=string.Empty)
{
string[] str1=strTemp.Split(','); for(int j=0;j<str1.Length-1;j++)
{
myRow[j+4] = str1[j].Substring(str1[j].LastIndexOf(':')+1);
}
}
myTable.Rows.Add(myRow);
//myTable.Rows.Add(myRow);
}
}
this.dgExcel.DataSource=myTable.DefaultView;
this.dgExcel.DataBind();
} public void ToExcel(System.Web.UI.Control ctl,string FileName)
{
HttpContext.Current.Response.Charset ="gb2312";
HttpContext.Current.Response.ContentEncoding =System.Text.Encoding.UTF8;
HttpContext.Current.Response.ContentType ="application/ms-excel";
HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename="+""+FileName+".xls");
ctl.Page.EnableViewState =false;
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
ctl.RenderControl(hw);
HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.End();
} private void dgExcel_ItemCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
} private void dgExcel_ItemCreated(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{
if ( ( e.Item.ItemType == ListItemType.Item ) || ( e.Item.ItemType == ListItemType.AlternatingItem ) || ( e.Item.ItemType == ListItemType.SelectedItem ) )
{
e.Item.Attributes.Add( "OnMouseOver", "this.style.backgroundColor = '#BED3E9';this.style.cursor='hand'");
e.Item.Attributes.Add( "OnMouseOut", "this.style.backgroundColor='white';");
}
} }
}
int i=1,j=1,k;
Excel.Application excel = new Excel.ApplicationClass();
excel.DefaultFilePath = System.Windows.Forms.Application.StartupPath+@"\file"; Excel.Workbook workbook = excel.Workbooks.Add(Type.Missing);
//设置表头
excel.Cells[i,j]=head;
i++;
excel.Cells[i,j]="姓名:"+name;
j=(dataSet.Tables["st_personal_b"].Columns.Count/2)+(dataSet.Tables["st_personal_b"].Columns.Count%2);
excel.Cells[i,j]="部门:"+department;
j=dataSet.Tables["st_personal_b"].Columns.Count;
excel.Cells[i,j]="房间号:"+room;
i++;
k=i;
for(j=1;j<=columnhead.Length;j++)
{
excel.Cells[i,j]=columnhead[j-1];
}
i++;
foreach(System.Data.DataRow dr in dataSet.Tables["st_personal_b"].Rows)
{
for (j=1;j<=dataSet.Tables["st_personal_b"].Columns.Count;j++)
{
excel.Cells[i,j] = dr[j-1];
}
i++;
} ///画线
string cell = "A"+k.ToString()+":"+((char)(j+63)).ToString()+(i-1).ToString();
Excel.Range range = excel.get_Range(cell,Type.Missing);
range.Select();
Excel.Borders borders=range.Borders;
borders[Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
borders[Excel.XlBordersIndex.xlDiagonalUp].LineStyle = Excel.XlLineStyle.xlLineStyleNone; borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;
borders[Excel.XlBordersIndex.xlEdgeLeft].ColorIndex= Excel.XlColorIndex.xlColorIndexAutomatic; borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;
borders[Excel.XlBordersIndex.xlEdgeTop].ColorIndex= Excel.XlColorIndex.xlColorIndexAutomatic; borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;
borders[Excel.XlBordersIndex.xlEdgeBottom].ColorIndex= Excel.XlColorIndex.xlColorIndexAutomatic; borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;
borders[Excel.XlBordersIndex.xlEdgeRight].ColorIndex= Excel.XlColorIndex.xlColorIndexAutomatic; borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex= Excel.XlColorIndex.xlColorIndexAutomatic; borders[Excel.XlBordersIndex.xlInsideVertical].LineStyle = Excel.XlLineStyle.xlContinuous;
borders[Excel.XlBordersIndex.xlInsideVertical].Weight = Excel.XlBorderWeight.xlThin;
borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex= Excel.XlColorIndex.xlColorIndexAutomatic;
///画线结束
j=1;
string [] tempbuttom = bottom.Split('|');
excel.Cells[i,j]=tempbuttom[0];
j=(dataSet.Tables["st_personal_b"].Columns.Count/2)+(dataSet.Tables["st_personal_b"].Columns.Count%2);
excel.Cells[i,j]=tempbuttom[1];
j=dataSet.Tables["st_personal_b"].Columns.Count;
excel.Cells[i,j]=tempbuttom[2]; //调整格式
range = excel.get_Range("A1:F1",Type.Missing);
range.Select();
range.Font.Bold = true;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
range.VerticalAlignment = Excel.XlVAlign.xlVAlignBottom;
range.WrapText = false;
range.Orientation = 0;
range.AddIndent = false;
range.ShrinkToFit = false;
range.MergeCells = false;
range.Merge(Type.Missing); //保存
workbook.SaveAs(name+"("+currentTime.ToLongDateString()+")",Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Excel.XlSaveAsAccessMode.xlNoChange,Excel.XlSaveConflictResolution.xlLocalSessionChanges,Type.Missing,Type.Missing,Type.Missing);
workbook.Close(false,Type.Missing,Type.Missing);
Excel.ApplicationClass objExcel = new Excel.ApplicationClass();
Excel.Workbooks workBook= objExcel .Workbooks;
Excel.Workbook book= workBook.Open(strFilePath,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing); for (int i = 1; i<=book.Worksheets.Count;i++)
{
string strSheetName=(((Excel.Worksheet)book.Worksheets[i]).Name).ToString();
Console.WriteLine(strSheetName);
}